/*
 * 作者:荊明君
 * 時間:2007年12月15日
 * 功能:平臺操作人員管理-添加刪除和瀏覽平臺操作人員(賬戶管理)（數據庫接口）。
 */
package com.mole.struts.dao;

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

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

import com.mole.struts.bean.AdminVoucherViewBean;

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

	public AdminVoucherViewDAO() {
		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 getPageInfo(int pageSize) {
		int count = 0;
		this.pageSize = pageSize;
		String sql = "SELECT COUNT(*) FROM [merchantvoucher]";
		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 ArrayList<AdminVoucherViewBean> allVoucherView(String line,
			int currentPage) throws Exception {
		ResultSet rs = null;
		PreparedStatement ps = null;
		String sql = "SELECT TOP "
				+ pageSize
				+ " a.name, a.description, a.amount, a.createdate,"
				+ "b.name, c.name, d.name, e.name  "
				+ "FROM  [merchantvoucher]a ,[store]b, [storetype]c, [city]d, [province]e "
				+ "WHERE b.id=a.storeId and c.id=b.typeId and d.id=SUBSTRING(B.[areaId],1,4) and e.id=d.provinceId "
				+ " and a.[ID] NOT IN (SELECT TOP " + (currentPage - 1)
				* pageSize + " a.[ID] "
				+ "FROM [merchantvoucher]a ) order by '" + line + "'";
		ArrayList<AdminVoucherViewBean> info = new ArrayList<AdminVoucherViewBean>();
		try {
			conn.setAutoCommit(true);
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				AdminVoucherViewBean voucherInfo = new AdminVoucherViewBean();
				voucherInfo.setVoucherName(rs.getString(1));
				voucherInfo.setVoucherDiscription(rs.getString(2));
				voucherInfo.setVoucherAmount(rs.getString(3));
				voucherInfo.setVoucherDate(rs.getString(4).toString()
						.substring(0, 10));
				voucherInfo.setStoreName(rs.getString(5));
				voucherInfo.setStoreType(rs.getString(6));
				voucherInfo.setStoreCity(rs.getString(7));
				voucherInfo.setStoreProvince(rs.getString(8));

				info.add(voucherInfo);
			}
		} finally {
			if (ps != null)
				ps.close();
		}
		return info;
	}

	// 根據 StoreName 查詢方法獲取頁面屬性及查詢結果
	public int getPageInfoByStoreName(String keyword, int pageSize) {
		int count = 0;
		this.pageSize = pageSize;
		String sql = "SELECT COUNT(*) FROM [merchantvoucher]a,[store]b WHERE b.id=a.storeId and b.name='"
				+ keyword + "'";
		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 ArrayList<AdminVoucherViewBean> getVoucherViewByStoreName(
			String storeName, String line, int currentPage) throws Exception {
		ResultSet rs = null;
		PreparedStatement ps = null;
		String sql = "SELECT TOP "
				+ pageSize
				+ " a.name, a.description, a.amount, a.createdate,"
				+ "b.name, c.name, d.name, e.name  "
				+ "FROM  [merchantvoucher]a ,[store]b, [storetype]c, [city]d, [province]e "
				+ "WHERE b.id=a.storeId and b.name='"
				+ storeName
				+ "' and c.id=b.typeId and d.id=SUBSTRING(B.[areaId],1,4) and e.id=d.provinceId "
				+ " and a.[ID] NOT IN (SELECT TOP " + (currentPage - 1)
				* pageSize + " a.[ID] "
				+ "FROM [merchantvoucher]a ) order by '" + line + "'";
		ArrayList<AdminVoucherViewBean> info = new ArrayList<AdminVoucherViewBean>();
		try {
			conn.setAutoCommit(true);
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				AdminVoucherViewBean voucherInfo = new AdminVoucherViewBean();
				voucherInfo.setVoucherName(rs.getString(1));
				voucherInfo.setVoucherDiscription(rs.getString(2));
				voucherInfo.setVoucherAmount(rs.getString(3));
				voucherInfo.setVoucherDate(rs.getString(4).toString()
						.substring(0, 10));
				voucherInfo.setStoreName(rs.getString(5));
				voucherInfo.setStoreType(rs.getString(6));
				voucherInfo.setStoreCity(rs.getString(7));
				voucherInfo.setStoreProvince(rs.getString(8));

				info.add(voucherInfo);
			}
		} finally {
			if (ps != null)
				ps.close();
		}
		return info;
	}

	// 根據 StoreType 查詢方法獲取頁面屬性及查詢結果
	public int getPageInfoByStoreType(String keyword, int pageSize) {
		int count = 0;
		this.pageSize = pageSize;
		String sql = "SELECT COUNT(*) FROM [merchantvoucher]a,[store]b,[storeType]c WHERE b.id=a.storeId and c.id=b.typeId and c.name='"
				+ keyword + "'";
		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 ArrayList<AdminVoucherViewBean> getVoucherViewByStoreType(
			String storeType, String line, int currentPage) throws Exception {
		ResultSet rs = null;
		PreparedStatement ps = null;
		String sql = "SELECT TOP "
				+ pageSize
				+ " a.name, a.description, a.amount, a.createdate,"
				+ "b.name, c.name, d.name, e.name  "
				+ "FROM  [merchantvoucher]a ,[store]b, [storetype]c, [city]d, [province]e "
				+ "WHERE b.id=a.storeId and c.id=b.typeId and c.name='"
				+ storeType
				+ "' and d.id=SUBSTRING(B.[areaId],1,4) and e.id=d.provinceId "
				+ " and a.[ID] NOT IN (SELECT TOP " + (currentPage - 1)
				* pageSize + " a.[ID] "
				+ "FROM [merchantvoucher]a ) order by '" + line + "'";
		ArrayList<AdminVoucherViewBean> info = new ArrayList<AdminVoucherViewBean>();
		try {
			conn.setAutoCommit(true);
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				AdminVoucherViewBean voucherInfo = new AdminVoucherViewBean();
				voucherInfo.setVoucherName(rs.getString(1));
				voucherInfo.setVoucherDiscription(rs.getString(2));
				voucherInfo.setVoucherAmount(rs.getString(3));
				voucherInfo.setVoucherDate(rs.getString(4).toString()
						.substring(0, 10));
				voucherInfo.setStoreName(rs.getString(5));
				voucherInfo.setStoreType(rs.getString(6));
				voucherInfo.setStoreCity(rs.getString(7));
				voucherInfo.setStoreProvince(rs.getString(8));

				info.add(voucherInfo);
			}
		} finally {
			if (ps != null)
				ps.close();
		}
		return info;
	}

	// 根據 Area 查詢方法獲取頁面屬性及查詢結果
	public int getPageInfoByArea(String keyword, int pageSize) {
		int count = 0;
		this.pageSize = pageSize;
		String sql = null;
		if (keyword.length() == 4) {
			sql = "SELECT COUNT(*) FROM [merchantvoucher]a,[store]b,[area]d WHERE b.id=a.storeId and d.id=b.areaId and d.cityId='"
					+ keyword + "'";
		} else {
			if (keyword.length() < 4) {
				sql = "SELECT COUNT(*) FROM [merchantvoucher]a,[store]b,[area]d WHERE b.id=a.storeId and d.id=b.areaId and d.provinceId='"
						+ keyword + "'";
			} else {
				if (keyword.length() == 6) {
					sql = "SELECT COUNT(*) FROM [merchantvoucher]a,[store]b WHERE b.id=a.storeId and b.areaId='"
							+ keyword + "'";
				}
			}
		}
		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 ArrayList<AdminVoucherViewBean> getVoucherViewByArea(String keyword,
			String line, int currentPage) throws Exception {
		ResultSet rs = null;
		PreparedStatement ps = null;
		String sql = null;
		int i = keyword.length();
		// 判斷輸入地址：市、省、區 進行不同查詢
		if (i == 4) {
			sql = "SELECT TOP "
					+ pageSize
					+ " a.name, a.description, a.amount, a.createdate,"
					+ "b.name, c.name, g.name, d.name  "
					+ "FROM  [merchantvoucher]a ,[store]b, [storetype]c, [city]d, [province]e, [area]g "
					+ "WHERE b.id=a.storeId and c.id=b.typeId and g.cityId='"
					+ keyword + "' and d.id=g.cityId and e.id=g.provinceId "
					+ " and a.[ID] NOT IN (SELECT TOP " + (currentPage - 1)
					* pageSize + " a.[ID] "
					+ "FROM [merchantvoucher]a ) order by '" + line + "'";
		} else {
			if (i < 4) {
				sql = "SELECT TOP "
						+ pageSize
						+ " a.name, a.description, a.amount, a.createdate,"
						+ "b.name, c.name, d.name, e.name  "
						+ "FROM  [merchantvoucher]a ,[store]b, [storetype]c, [city]d, [province]e, [area]g "
						+ "WHERE b.id=a.storeId and c.id=b.typeId and g.Id=b.areaId and g.provinceId='"
						+ keyword + "' and d.id=g.cityId and e.id='" + keyword
						+ "' " + " and a.[ID] NOT IN (SELECT TOP "
						+ (currentPage - 1) * pageSize + " a.[ID] "
						+ "FROM [merchantvoucher]a ) order by '" + line + "'";
			} else {
				if (i == 6) {
					sql = "SELECT TOP "
							+ pageSize
							+ " a.name, a.description, a.amount, a.createdate,"
							+ "b.name, c.name, g.name, d.name  "
							+ "FROM  [merchantvoucher]a ,[store]b, [storetype]c, [city]d, [province]e, [area]g "
							+ "WHERE b.id=a.storeId and b.areaId='"
							+ keyword
							+ "' and c.id=b.typeId and g.Id=b.areaId and d.id=g.cityId "
							+ " and a.[ID] NOT IN (SELECT TOP "
							+ (currentPage - 1) * pageSize + " a.[ID] "
							+ "FROM [merchantvoucher]a ) order by '" + line
							+ "'";
				}
			}
		}
		ArrayList<AdminVoucherViewBean> info = new ArrayList<AdminVoucherViewBean>();
		try {
			conn.setAutoCommit(true);
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				AdminVoucherViewBean voucherInfo = new AdminVoucherViewBean();
				voucherInfo.setVoucherName(rs.getString(1));
				voucherInfo.setVoucherDiscription(rs.getString(2));
				voucherInfo.setVoucherAmount(rs.getString(3));
				voucherInfo.setVoucherDate(rs.getString(4).toString()
						.substring(0, 10));
				voucherInfo.setStoreName(rs.getString(5));
				voucherInfo.setStoreType(rs.getString(6));
				voucherInfo.setStoreCity(rs.getString(7));
				voucherInfo.setStoreProvince(rs.getString(8));

				info.add(voucherInfo);
			}
		} finally {
			if (ps != null)
				ps.close();
		}
		return info;
	}
}