//張建
package com.mole.struts.dao;

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

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

import com.mole.struts.bean.CustomerInfoBean;

public class AdminQueryAssociatorsDAO {
	private Connection conn;
	private int pageSize;

	public AdminQueryAssociatorsDAO() {
		System.out.println("Data source init...");
		try {
			Context ctx = new InitialContext();
			if (ctx == null)
				throw new Exception("Failed to initial context!");
			DataSource ds = (DataSource) ctx
					.lookup("java:comp/env/jdbc/crmdata");
			conn = ds.getConnection();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// 獲取所有記錄
	public int getAllRecordPageInfo(int pageSize) {
		int count = 0;
		this.pageSize = pageSize;
		String sql = "SELECT COUNT(*) FROM [Customer]";
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			if (rs.next())
				count = rs.getInt(1);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return count;
	}

	// 查詢所有會(huì)員
	public Hashtable<String, CustomerInfoBean> queryAllAssociators(
			int currentPage) throws Exception {
		ResultSet rs = null;
		PreparedStatement ps = null;
		Hashtable<String, CustomerInfoBean> hash = new Hashtable<String, CustomerInfoBean>();
		String sql = "SELECT TOP "
				+ pageSize
				+ " [ID],[LoginName],[State],[Nickname],[Name],[Gender],[Email],[Telephone],"
				+ "[Cellphone],[Address],[Company],[FaceImage],[FaceWidth],[FaceHeight],[CreateDate],[LastLogin],[Interest],[Description]"
				+ " FROM [Customer] WHERE ID NOT IN (SELECT TOP "
				+ (currentPage - 1) * pageSize + " [ID] FROM [Customer])";
		try {
			conn.setAutoCommit(true);
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			DBConnector dbc = new DBConnector();
			while (rs.next()) {
				CustomerInfoBean Associators = new CustomerInfoBean();

				Associators.setID(rs.getString(1));
				Associators.setLoginName(rs.getString(2));
				String state = rs.getString(3);
				if ("0".equals(state)) {
					state = "正常";
				} else {
					state = "禁用";
				}
				Associators.setState(state);
				Associators.setNickname(rs.getString(4));
				Associators.setName(rs.getString(5));
				Associators.setGender(rs.getString(6));
				Associators.setEmail(rs.getString(7));
				Associators.setTelephone(rs.getString(8));
				Associators.setCellphone(rs.getString(9));
				Associators.setAddress(rs.getString(10));
				Associators.setCompany(rs.getString(11));
				Associators.setFaceImage(rs.getString(12));
				Associators.setFaceWidth(rs.getString(13));
				Associators.setFaceHeight(rs.getString(14));
				Associators.setCreateDate(rs.getString(15).substring(0, 19));
				Associators.setLastLogin(rs.getString(16).substring(0, 19));
				Associators.setInterest(rs.getString(17));
				Associators.setDescription(rs.getString(18));
				ArrayList<Object[]> al = dbc
						.executeQuery("select [ID],[StoreID] from [Card] where CustomerID='"
								+ rs.getString(1) + "' and State='0'");
				if (al.size() == 0) {
					Associators.setCardID("無");
					Associators.setCardFrom("無");
				} else {
					ArrayList<Object[]> a = dbc
							.executeQuery("select [LoginName] from [Merchant] where [StoreID]='"
									+ al.get(0)[1].toString() + "'");
					Associators.setCardID((al.get(0)[0] == null) ? "" : (al
							.get(0)[0]).toString());
					Associators.setCardFrom((al.get(0)[1] == null) ? "" : (a
							.get(0)[0]).toString());
				}

				hash.put(rs.getString(1), Associators);
			}
			return hash;
		} finally {
			if (ps != null)
				ps.close();
		}
	}

	// 獲取頁信息
	public int getPageInfo(String cusName, String merName, int pageSize) {
		int count = 0;
		this.pageSize = pageSize;
		String sql = "";
		if (cusName != null && merName == null) {
			sql = "select count(*) from [Customer] where LoginName='" + cusName
					+ "'";
		} else if (cusName == null && merName != null) {
			sql = "select count(*) from [v_CustomerCard] where MerchantLoginName='"
					+ merName + "'";
		} else if (cusName != null && merName != null) {
			sql = "select count(*) from [v_CustomerCard] where CustomerLoginName='"
					+ cusName + "' and MerchantLoginName='" + merName + "'";
		}
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			if (rs.next())
				count = rs.getInt(1);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return count;
	}

	// 查詢會(huì)員
	public Hashtable<String, CustomerInfoBean> queryAssociators(String cusName,
			String merName, int currentPage) throws Exception {
		ResultSet rs = null;
		PreparedStatement ps = null;
		Hashtable<String, CustomerInfoBean> hash = new Hashtable<String, CustomerInfoBean>();
		String sql = "";
		// 會(huì)員名不為空且商家名為空
		if (cusName != null && merName == null) {
			sql = "select top "
					+ pageSize
					+ " [ID],[LoginName],[State],[Nickname],[Name],[Gender],[Email],[Telephone],"
					+ "[Cellphone],[Address],[Company],[FaceImage],[FaceWidth],[FaceHeight],[CreateDate],[LastLogin],[Interest],[Description]"
					+ " from [Customer] where LoginName='" + cusName
					+ "' and id not in(select top " + (currentPage - 1)
					* pageSize + " [ID] from [Customer] where LoginName='"
					+ cusName + "')";

			try {
				conn.setAutoCommit(true);
				ps = conn.prepareStatement(sql);
				rs = ps.executeQuery();
				DBConnector dbc = new DBConnector();
				while (rs.next()) {
					CustomerInfoBean Associators = new CustomerInfoBean();

					Associators.setID(rs.getString(1));
					Associators.setLoginName(rs.getString(2));
					String state = rs.getString(3);
					if ("0".equals(state)) {
						state = "正常";
					} else {
						state = "禁用";
					}
					Associators.setState(state);
					Associators.setNickname(rs.getString(4));
					Associators.setName(rs.getString(5));
					Associators.setGender(rs.getString(6));
					Associators.setEmail(rs.getString(7));
					Associators.setTelephone(rs.getString(8));
					Associators.setCellphone(rs.getString(9));
					Associators.setAddress(rs.getString(10));
					Associators.setCompany(rs.getString(11));
					Associators.setFaceImage(rs.getString(12));
					Associators.setFaceWidth(rs.getString(13));
					Associators.setFaceHeight(rs.getString(14));
					Associators
							.setCreateDate(rs.getString(15).substring(0, 19));
					Associators.setLastLogin(rs.getString(16).substring(0, 19));
					Associators.setInterest(rs.getString(17));
					Associators.setDescription(rs.getString(18));
					ArrayList<Object[]> al = dbc
							.executeQuery("select [ID],[StoreID] from [Card] where CustomerID='"
									+ rs.getString(1) + "' and State='0'");
					if (al.size() == 0) {
						Associators.setCardID("無");
						Associators.setCardFrom("無");
					} else {
						ArrayList<Object[]> a = dbc
								.executeQuery("select [LoginName] from [Merchant] where [StoreID]='"
										+ al.get(0)[1].toString() + "'");
						Associators.setCardID((al.get(0)[0] == null) ? "" : (al
								.get(0)[0]).toString());
						Associators.setCardFrom((al.get(0)[1] == null) ? ""
								: (a.get(0)[0]).toString());
					}

					hash.put(rs.getString(1), Associators);
				}
				return hash;
			} finally {
				if (ps != null)
					ps.close();
			}
		}
		// 會(huì)員名為空且商家名不為空
		else if (cusName == null && merName != null) {
			sql = "SELECT TOP "
					+ pageSize
					+ " [ID],[CustomerLoginName],[CustomerState],[Nickname],[CustomerName],[Gender],[Email],[Telephone],"
					+ "[Cellphone],[Address],[Company],[FaceImage],[FaceWidth],[FaceHeight],[CreateDate],[LastLogin],[Interest],[Description],[CardID],[MerchantLoginName]"
					+ " FROM [v_CustomerCard] WHERE MerchantLoginName='"
					+ merName
					+ "' and CardState='0' AND ID NOT IN (SELECT TOP "
					+ (currentPage - 1) * pageSize
					+ " [ID] FROM [v_CustomerCard] WHERE MerchantLoginName='"
					+ merName + "' and CardState='0')";
		}
		// 會(huì)員名不為空且商家名不為空
		else if (cusName != null && merName != null) {
			sql = "SELECT TOP "
					+ pageSize
					+ " [ID],[CustomerLoginName],[CustomerState],[Nickname],[CustomerName],[Gender],[Email],[Telephone],"
					+ "[Cellphone],[Address],[Company],[FaceImage],[FaceWidth],[FaceHeight],[CreateDate],[LastLogin],[Interest],[Description],[CardID],[MerchantLoginName]"
					+ " FROM [v_CustomerCard] WHERE CustomerLoginName='"
					+ cusName + "' and CardState='0' and MerchantLoginName='"
					+ merName + "' AND ID NOT IN (SELECT TOP "
					+ (currentPage - 1) * pageSize
					+ " [ID] FROM [v_CustomerCard] WHERE CustomerLoginName='"
					+ cusName + "' and CardState='0' and MerchantLoginName='"
					+ merName + "')";
		}
		System.out.print(sql);
		try {
			conn.setAutoCommit(true);
			ps = conn.prepareStatement(sql);
			System.out.println(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				CustomerInfoBean Associators = new CustomerInfoBean();

				Associators.setID(rs.getString(1));
				Associators.setLoginName(rs.getString(2));
				String state = rs.getString(3);
				if ("0".equals(state)) {
					state = "正常";
				} else {
					state = "禁用";
				}
				Associators.setState(state);
				Associators.setNickname(rs.getString(4));
				Associators.setName(rs.getString(5));
				Associators.setGender(rs.getString(6));
				Associators.setEmail(rs.getString(7));
				Associators.setTelephone(rs.getString(8));
				Associators.setCellphone(rs.getString(9));
				Associators.setAddress(rs.getString(10));
				Associators.setCompany(rs.getString(11));
				Associators.setFaceImage(rs.getString(12));
				Associators.setFaceWidth(rs.getString(13));
				Associators.setFaceHeight(rs.getString(14));
				Associators.setCreateDate(rs.getString(15));
				Associators.setLastLogin(rs.getString(16));
				Associators.setInterest(rs.getString(17));
				Associators.setDescription(rs.getString(18));
				Associators.setCardID(rs.getString(19));
				Associators.setCardFrom(rs.getString(20));

				hash.put(rs.getString(1), Associators);
			}
			return hash;
		} finally {
			if (ps != null)
				ps.close();
		}
	}
}