package com.dut.logic.db;

import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.dut.dao.BatchDBConnectionUtil;
import com.dut.logic.util.DataTypeCheck;
import com.dut.logic.util.StringUtil;
import com.dut.struts.form.SeikyuLoginActionForm;
import com.dut.struts.form.SeikyuPassChangeActionForm;

public class Kyokusyo {
	
	private int ken_id;			//smallint
	private String kyoku_id;	//char
	private int zipcd_mae;		//smallint	
	private int zipcd_ato;		//smallint
	private String kyokusyo_mei;	//varchar	
	private String jusyo;		//varchar	
	private String tel;		//varchar	
	private String password;		//varchar	
	private Date insert_date; 
	private Date update_date; 	//datetime	
	private String ma;

	public String getMa() {
		return ma;
	}
	public void setMa(String ma) {
		this.ma = ma;
	}
	public String getJusyo() {
		return jusyo;
	}
	public void setJusyo(String jusyo) {
		this.jusyo = jusyo;
	}
	public int getKen_id() {
		return ken_id;
	}
	public void setKen_id(int ken_id) {
		this.ken_id = ken_id;
	}
	public String getKyoku_id() {
		return kyoku_id;
	}
	public void setKyoku_id(String kyoku_id) {
		this.kyoku_id = kyoku_id;
	}
	public String getKyokusyo_mei() {
		return kyokusyo_mei;
	}
	public void setKyokusyo_mei(String kyokusyo_mei) {
		this.kyokusyo_mei = kyokusyo_mei;
	}
	public String getPassword() {
		return password;
	}
	public void setPassword(String password) {
		this.password = password;
	}
	public String getTel() {
		return tel;
	}
	public void setTel(String tel) {
		this.tel = tel;
	}
	public int getZipcd_ato() {
		return zipcd_ato;
	}
	public void setZipcd_ato(int zipcd_ato) {
		this.zipcd_ato = zipcd_ato;
	}
	public int getZipcd_mae() {
		return zipcd_mae;
	}
	public void setZipcd_mae(int zipcd_mae) {
		this.zipcd_mae = zipcd_mae;
	}
	public Date getInsert_date() {
		return insert_date;
	}
	public void setInsert_date(Date insert_date) {
		this.insert_date = insert_date;
	}
	public Date getUpdate_date() {
		return update_date;
	}
	public void setUpdate_date(Date update_date) {
		this.update_date = update_date;
	}
	
	
	/**
	 * 該函數用于通過ken_id和kyoku_id查詢郵局名稱??????????ǐ??????
	 * @param ken_id?,kyuku_id
	 * @return String
	 * @author ?楊麗娟 2006-04-11
	 */
	public static String selectKyokusyo_mei(int ken_id,String kyoku_id){
		String mei = null;
		String Sqlstr = "SELECT KYOKUSYO_MEI FROM KYOKUSYO "
						+ "WHERE KEN_ID = " + ken_id
						+ " AND KYOKU_ID = '" + kyoku_id + "'";
		System.out.println("selectKyokusyo_mei is ###!!!~~&>>" + Sqlstr);
		BatchDBConnectionUtil con = new BatchDBConnectionUtil();
		ResultSet rs = con.executeQuery(Sqlstr);
		try {
			if(rs.next()){
				mei = StringUtil.trans_date(rs.getString("KYOKUSYO_MEI"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		con.close();
		return mei;
	}
	
	/**
	 * ?????????該函數用于獲得Kyokusyo表的List
	 * @param sqlstr????????????
	 * @return String
	 * @author ?楊麗娟 2006-04-11
	 */
	public static List getKyokuData(String sqlstr){
		List KyokuList = new ArrayList();
		BatchDBConnectionUtil con = new BatchDBConnectionUtil();
		ResultSet rs = con.executeQuery(sqlstr);
		try {
			while(rs.next()){
				Kyokusyo KKy = new Kyokusyo();
				int ken_id = Integer.parseInt(rs.getString("KEN_ID"));
				KKy.setKen_id(ken_id);
				
				String kyoku_id = StringUtil
									.trans_date(rs.getString("KYOKU_ID"));
				KKy.setKyoku_id(kyoku_id);
				
				int zipcd_mae = Integer.parseInt(rs.getString("ZIPCD_MAE"));
				KKy.setZipcd_mae(zipcd_mae);
				
				int zipcd_ato = Integer.parseInt(rs.getString("ZIPCD_ATO"));
				KKy.setZipcd_ato(zipcd_ato);
				
				String kyokusyo_mei = StringUtil
										.trans_date(rs.getString("KYOKUSYO_MEI"));
				KKy.setKyokusyo_mei(kyokusyo_mei);
				
				String jusyo = StringUtil.trans_date(rs.getString("JUSYO"));
				KKy.setJusyo(jusyo);
				
				String tel = StringUtil.trans_date(rs.getString("TEL"));
				KKy.setTel(tel);
				
				String password = StringUtil.trans_date(rs.getString("PASSWORD"));
				KKy.setPassword(password);
				
				Date insert_date = DataTypeCheck
									.StringtoDate(rs.getString("INSERT_DATE"));
				KKy.setInsert_date(insert_date);
				
				Date update_date = DataTypeCheck
										.StringtoDate(rs.getString("UPDATE_DATE"));
				KKy.setUpdate_date(update_date);
				
				KyokuList.add(KKy);

			}
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return KyokuList;
	}
	
	
	/**
	 * 該函數用于郵局登錄時
	 * 驗證?????郵局號及密碼???ǐ???
	 * @param SeikyuLoginActionForm
	 * @return List
	 * @author 楊麗娟 2006-04-11
	 */
	public List login(SeikyuLoginActionForm form){
		List Login = new ArrayList();
		int ken_id = Integer
						.parseInt(form.getJusuokodo().substring(0,2));
		String kyoku_id = form.getJusuokodo().substring(2);
		String password = form.getPassword();
		String Sqlstr = "SELECT * FROM KYOKUSYO "
						+ "WHERE KEN_ID = " + ken_id
						+ " AND KYOKU_ID = '" + kyoku_id + "'"
						+ " AND PASSWORD = '" + password + "'";
		BatchDBConnectionUtil con = new BatchDBConnectionUtil();
		ResultSet rs = con.executeQuery(Sqlstr);
		try {
			if(rs.next()){
				ken_id = Integer.parseInt(rs.getString("KEN_ID"));
				kyoku_id = StringUtil.trans_date(rs.getString("KYOKU_ID"));
				String jusuokodo = Integer.toString(ken_id) + kyoku_id;
				password  = StringUtil
									.trans_date(rs.getString("PASSWORD"));
				String kyokusyo_mei = StringUtil
										.trans_date(rs.getString("KYOKUSYO_MEI"));
				Login.add(0,jusuokodo);
				Login.add(1,password);
				Login.add(2,kyokusyo_mei);
			}
			else{
				con.close();
				return null;
			}
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		con.close();
		return Login;
	}
	
	/**
	 * ?????????該函數用于修改密碼時驗證原密碼是否正確??????
	 * 正確則返回???true,否則返回?????false
	 * @param SeikyuPassChangeActionForm,String
	 * @return boolean
	 * @author ?楊麗娟 2006-04-11
	 */
	public boolean checkOldpwd(SeikyuPassChangeActionForm form,String jusuokodo){
		boolean flag = false;
		int ken_id = Integer
						.parseInt(jusuokodo.substring(0,2));
		String kyoku_id = jusuokodo.substring(2);
		String password = form.getOldpwd();
		String Sqlstr = "SELECT * FROM KYOKUSYO "
						+ "WHERE KEN_ID = " + ken_id
						+ " AND KYOKU_ID = '" + kyoku_id + "'"
						+ " AND PASSWORD = '" + password + "'";
		BatchDBConnectionUtil con = new BatchDBConnectionUtil();
		ResultSet rs = con.executeQuery(Sqlstr);
		try {
			if(rs.next()){
				flag = true;
			}
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		con.close();
		return flag;
	}
	
	/**
	 * ?????????該函數用于修改密碼
	 * 操作成功返回null?????
	 * @param SeikyuPassChangeActionForm form
	 * @param String jusuokodo
	 * @return String
	 * @author 楊麗娟 2006-04-11
	 */
	public String changPassword(SeikyuPassChangeActionForm form,String jusuokodo){
		String flag = null;
		String newpwd = form.getNewpwd();
		int ken_id = Integer
						.parseInt(jusuokodo.substring(0,2));
		String kyoku_id = jusuokodo.substring(2);
		String Sqlstr = "UPDATE KYOKUSYO SET PASSWORD = "
				+ newpwd + " WHERE KEN_ID = " + ken_id
				+ " AND KYOKU_ID = '" + kyoku_id + "'";
		BatchDBConnectionUtil con = new BatchDBConnectionUtil();
		flag = con.executeUpdate(Sqlstr);
		con.close();
		return flag;
	}

}