package com.mole.struts.dao;

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

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

import com.mole.struts.bean.CustomerStoreBean;

public class searchStoreDAO {

	private Connection conn;
	private int pageSize;

	public Connection getConn() {
		if (conn == null) {
			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();
			}
		}
		return conn;
	}

	public int getPageInfo(String where, int pageSize) {
		int count = 0;
		this.pageSize = pageSize;
		String sql = "SELECT COUNT(*) FROM [v_searchstore] " + where;
		if (conn == null)
			conn = getConn();
		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 getSearchStoreResult(String where, int pageSize,
			int currentPage) throws Exception {
		ResultSet rs = null;
		PreparedStatement ps = null;
		ArrayList al = new ArrayList();

		String sql = "SELECT TOP "
				+ pageSize
				+ " a.[id], a.[StoreType],a.[StoreName],a.[StoreAddress] ,a.[image]"
				+ "FROM v_searchStore a " + where + " AND a.[ID] NOT IN("
				+ "SELECT TOP " + (currentPage - 1) * pageSize
				+ " [ID] FROM v_searchStore " + where
				+ " order by storename) order by storename";

		try {
			conn.setAutoCommit(true);
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				CustomerStoreBean tempBean = new CustomerStoreBean();
				tempBean.setId(rs.getString(1));
				tempBean.setStoreName(rs.getString(2));
				tempBean.setStoreType(rs.getString(3));
				tempBean.setStoreAddress(rs.getString(4));
				tempBean.setImage(rs.getString(5));
				al.add(tempBean);
			}
			return al;
		} finally {
			if (ps != null)
				ps.close();
		}
	}

	public String[] getProviences() {
		Connection conn = null;
		ResultSet rs = null;
		PreparedStatement ps = null;
		ArrayList al = new ArrayList();
		String[] proviences = new String[34];
		if (conn == null)
			conn = getConn();
		try {
			conn.setAutoCommit(true);
			ps = conn.prepareStatement("select name from Province");
			rs = ps.executeQuery();
			int i = 0;
			while (rs.next()) {
				proviences[i] = rs.getString(1);
				i++;

			}
			return proviences;

		} catch (Exception e) {
			System.out.println("Data base eror");
			return null;
		} finally {
			if (ps != null)
				try {
					ps.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}

	}

	public ArrayList getStoreType() {
		Connection conn = null;
		ResultSet rs = null;
		PreparedStatement ps = null;
		ArrayList al = new ArrayList();
		if (conn == null)
			conn = getConn();
		try {
			conn.setAutoCommit(true);
			ps = conn
					.prepareStatement("select distinct storeType from v_searchStore");
			rs = ps.executeQuery();
			while (rs.next()) {
				al.add(rs.getString(1));
			}
			return al;

		} catch (Exception e) {
			System.out.println("Data base eror");
			return null;
		} finally {
			if (ps != null)
				try {
					ps.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}

	}

}