merchantvoucherdao.java

基于struts的网上商店源码

/*
 * 作者:
 * 时间:2007年11月22日
 * 功能:会员信息管理 -＞本店优惠券管理 
 */
package com.mole.struts.dao;

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

import com.mole.struts.bean.CustomerVoucherUsedInfoBean;
import com.mole.struts.bean.MerchantVoucherBean;

public class MerchantVoucherDAO extends AbstractDAO {

	private int pageSize;

	// 获取使用该优惠券的顾客的页数信息
	public int getVoucherUsedPageInfo(String voucherID, int pageSize) {
		Connection conn = getConn();
		int count = 0;
		this.pageSize = pageSize;
		String sql = "select count(*) from [VoucherRecord] where voucherID=?";
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, voucherID);
			ResultSet rs = ps.executeQuery();
			if (rs.next())
				count = rs.getInt(1);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return count;
	}

	// 获取使用该优惠券的顾客的相关信息
	public CustomerVoucherUsedInfoBean[] getVoucherUsedInfoInThisPage(
			String voucherID, int currentPage) throws Exception {

		String sql = "select Top "
				+ pageSize
				+ " a.[ID],b.[dealTime],c.[Name] from [BusinessRecord] a,[SwiftNumber] b, [Customer] c ,[VoucherRecord] d where d.[VoucherID]='"
				+ voucherID
				+ "' and b.[ID]=d.[SwiftID] and a.[SwiftID]=b.[ID] and c.[ID]=b.[CustomerID] and"
				+ " d.[ID] not in(select top " + pageSize * (currentPage - 1)
				+ " d.[ID] from [VoucherRecord] d where d.[VoucherID]='"
				+ voucherID + "' )" + " order by a.[ID]";

		ArrayList<Object[]> al = this.executeQuery(sql);
		CustomerVoucherUsedInfoBean[] beanlist = new CustomerVoucherUsedInfoBean[al
				.size()];
		Iterator<Object[]> it = al.iterator();
		int i = 0;
		while (it.hasNext()) {
			CustomerVoucherUsedInfoBean bean = new CustomerVoucherUsedInfoBean();
			Object[] obj = it.next();
			bean.setName(obj[2].toString());
			bean.setRecordID((Integer) obj[0]);
			bean.setUsedDate(obj[1].toString());
			beanlist[i++] = bean;
		}
		return beanlist;
	}

	// 获取所有的优惠圈信息
	/*
	 * 
	 * storeid:店铺ID type:店铺类型,<0查所有类型的,0:查本店铺的,1:查代发的
	 */
	public MerchantVoucherBean[] getAllVoucherInfo(String storeid, String type) {
		String sql = "select Name,Amount,Discount,Transfer,State,ID ,description,createDate,Type from MerchantVoucher where StoreID='"
				+ storeid + "'";

		if (!type.equals("2")) {
			sql += " and type='" + type + "'";
		}

		ArrayList<Object[]> arrayList = executeQuery(sql);
		if (arrayList == null)
			return null;

		MerchantVoucherBean[] allvoucherinfo = new MerchantVoucherBean[arrayList
				.size()];
		Iterator<Object[]> it = arrayList.iterator();
		int i = 0;
		while (it.hasNext()) {
			Object[] obj = it.next();
			String description = obj[6].toString();
			if (description.length() > 15)
				description = description.substring(0, 12) + "...";
			MerchantVoucherBean voucherinfo = new MerchantVoucherBean();
			voucherinfo.setName(obj[0].toString());
			voucherinfo.setAmount((Integer) obj[1]);
			voucherinfo.setDiscount((Double) obj[2]);
			voucherinfo.setTransfer((Integer) obj[3]);
			voucherinfo.setState((Integer) obj[4]);
			voucherinfo.setId(obj[5].toString());
			voucherinfo.setDescription(description);
			voucherinfo.setCreateDate(obj[7].toString().substring(0, 10));
			voucherinfo.setType((Integer) obj[8]);
			allvoucherinfo[i++] = voucherinfo;
		}
		return allvoucherinfo;
	}

	// 获取优惠圈详细信息
	public MerchantVoucherBean getDetailVoucherInfo(String ID) {
		String sql = "select Name,Amount,Description,Discount,Transfer,State,ID,Skin,Type,Image,maxAmount from MerchantVoucher where ID='"
				+ ID + "'";
		ArrayList<Object[]> arrayList = executeQuery(sql);
		if (arrayList == null)
			return null;
		Object[] obj = arrayList.get(0);
		MerchantVoucherBean detailinfo = new MerchantVoucherBean();
		detailinfo.setName(obj[0].toString());
		detailinfo.setAmount((Integer) obj[1]);
		detailinfo.setDescription(obj[2].toString());
		detailinfo.setDiscount((Double) obj[3]);
		detailinfo.setTransfer((Integer) obj[4]);
		detailinfo.setState((Integer) obj[5]);
		detailinfo.setId(obj[6].toString());
		detailinfo.setSkin((String) obj[7]);
		detailinfo.setType((Integer) obj[8]);
		detailinfo.setImage((String) obj[9]);
		detailinfo.setMaxAmount((Integer) obj[10]);
		return detailinfo;
	}

	// 获取用户持有数
	public int getCustomerAmount(String cid, String vid) {
		int n = 0;
		ResultSet rs = null;
		String sql = "SELECT [Amount] FROM [CustomerVoucher] WHERE [CustomerID]="
				+ cid + " AND [VoucherID]=" + vid;
		try {
			rs = conn.prepareStatement(sql).executeQuery();
			if (rs.next())
				n = rs.getInt(1);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return n;
	}

	// 根据优惠圈ID删除优惠圈
	public void deleteVoucherInfo(String id) {
		String sql = "DELETE FROM [MerchantVoucher] WHERE ID=" + id;
		executeUpdate(sql);
	}

	// 更新优惠圈数量
	public void updateBizGroup(Integer amount, String id, String skin,
			String image, Integer maxAmount) {
		if ("".equals(skin))
			skin = " ";
		String sql = "update merchantvoucher set amount=" + amount
				+ ",maxAmount=" + maxAmount + ",skin='" + skin + "',image='"
				+ image + "' " + "where id=" + id;
		executeUpdate(sql);
	}
}