adminsalerecorddao.java

基于struts的网上商店源码

package com.mole.struts.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Iterator;

import com.mole.struts.bean.MerchantGoodsBean;
import com.mole.struts.bean.MerchantGoodsSaleRecordBean;
import com.mole.struts.bean.MerchantGoodsTypeBean;
import com.mole.struts.bean.MerchantStoreInfoBean;

/**
 * @author ruzhenchao Creation date: 12-11-2007
 */
public class AdminSaleRecordDAO extends AbstractPageDAO {

	public AdminSaleRecordDAO() {
		super();
	}

	// 获取所有的商店
	public MerchantStoreInfoBean[] getAllStoreName() {
		String sql = "select ID,Name from Store ";
		ArrayList<Object[]> al = this.executeQuery(sql);
		MerchantStoreInfoBean[] beanList = new MerchantStoreInfoBean[al.size()];
		Iterator<Object[]> it = al.iterator();
		int i = 0;
		while (it.hasNext()) {
			Object[] obj = it.next();
			MerchantStoreInfoBean bean = new MerchantStoreInfoBean();
			bean.setStoreID(obj[0].toString());
			bean.setStoreName(obj[1].toString());
			beanList[i++] = bean;
		}
		return beanList;
	}

	// 获取所有商品类型
	public MerchantGoodsTypeBean[] getAllGoodsType() {
		String sql = "select ID,Name,StoreID from GoodsType";
		ArrayList<Object[]> al = this.executeQuery(sql);
		MerchantGoodsTypeBean[] beanList = new MerchantGoodsTypeBean[al.size()];
		Iterator<Object[]> it = al.iterator();
		int i = 0;
		while (it.hasNext()) {
			Object[] obj = it.next();
			MerchantGoodsTypeBean bean = new MerchantGoodsTypeBean();
			bean.setId(obj[0].toString());
			bean.setStoreId(obj[1].toString());
			bean.setName(obj[2].toString());
			beanList[i++] = bean;
		}
		return beanList;
	}

	// 获取所有商品
	public MerchantGoodsBean[] getAllGoodsName() {
		String sql = "select ID,GoodsType,Name from Goods";
		ArrayList<Object[]> al = this.executeQuery(sql);
		MerchantGoodsBean[] beanList = new MerchantGoodsBean[al.size()];
		Iterator<Object[]> it = al.iterator();
		int i = 0;
		while (it.hasNext()) {
			Object[] obj = it.next();
			MerchantGoodsBean bean = new MerchantGoodsBean();
			bean.setId(obj[0].toString());
			bean.setGoodsType(obj[1].toString());
			bean.setName(obj[2].toString());
			beanList[i++] = bean;
		}
		return beanList;
	}

	// 获取页面信息
	public int getPageInfo(String storeID, String goodsType, String goodsID,
			String goodsName, String startDate, String endDate, int pageSize) {
		this.conn = this.getConn();

		int count = 0;
		this.pageSize = pageSize;
		String sqlHeader = "select count(*) from [v_GoodsSaleRecord] a ";
		String sqlCondition = " where a.[id]=a.[id]";
		if (!"".equals(goodsName) && "0".equals(goodsID)) {
			sqlCondition += " and a.[Name] LIKE '%" + goodsName + "%' ";
			if (!"0".equals(goodsType)) {
				sqlCondition += " and a.[GoodsType]='" + goodsType + "' ";
			} else if (!"0".equals(storeID)) {
				sqlCondition += " and a.[StoreID]='" + storeID + "' ";
			}
		} else if (goodsID != null && !"0".equals(goodsID)) {
			sqlCondition += " and a.[ID]='" + goodsID + "' ";

		} else if (goodsType != null && !"0".equals(goodsType)) {
			sqlCondition += " and a.[GoodsType]='" + goodsType + "' ";

		} else if (storeID != null && !"0".equals(storeID)) {
			sqlCondition += " and a.[StoreID]='" + storeID + "' ";

		}

		if (startDate != null && !"".equals(startDate)) {
			sqlCondition += " and a.[dealtime]>='" + startDate + "' ";
		}
		if (endDate != null && !"".equals(endDate)) {
			sqlCondition += " and a.[dealtime]<='" + endDate + "' ";
		}

		String sql = sqlHeader + sqlCondition;

		this.whereCondition = sqlCondition;

		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			if (rs.next())
				count = rs.getInt(1);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return count;
	}

	// 获取当前页商品信息
	public MerchantGoodsSaleRecordBean[] getGoodsInfo(int currentPage) {
		String sqlFirst = "select top "
				+ this.pageSize
				+ " a.[ID],a.[Name],a.[goodsNumber],a.[amount],a.[amount]*a.[price],b.[Name],c.[Name] from [v_GoodsSaleRecord] a,[GoodsType] b,[Store] c  ";
		String sqlNext = " and a.[ID] not in(select top " + this.pageSize
				* (currentPage - 1) + " a.[ID] from [v_GoodsSaleRecord] a ";
		String sqlLast = " ) order by a.[ID]";
		String sqlextracondition = " and b.[ID]=a.[GoodsType] and c.[ID]=a.[storeID] ";
		String sql = sqlFirst + this.whereCondition + sqlextracondition
				+ sqlNext + this.whereCondition + sqlLast;

		ArrayList<Object[]> al = this.executeQuery(sql);
		MerchantGoodsSaleRecordBean[] beanList = new MerchantGoodsSaleRecordBean[al
				.size()];
		Iterator<Object[]> it = al.iterator();
		int i = 0;
		while (it.hasNext()) {
			Object[] obj = it.next();
			MerchantGoodsSaleRecordBean bean = new MerchantGoodsSaleRecordBean();
			bean.setId(obj[0].toString());
			bean.setName(obj[1].toString());
			bean.setGoodsNumber(obj[2].toString());
			bean.setAmount((Integer) obj[3]);
			bean.setPrice(Double.parseDouble(obj[4].toString()));
			bean.setGoodsType(obj[5].toString());
			bean.setStoreName(obj[6].toString());
			beanList[i++] = bean;
		}
		return beanList;
	}

}