package com.glf.reportIE.dao;

import java.sql.*;
import java.util.*;
import com.glf.reportIE.commons.DB;

public class ArchivesInfoDBOperate {
	
	
	//查詢員工信息 (根據userid)
	@SuppressWarnings("unchecked")
	public HashMap getArchivesInfo(String userid){
		DB _DBOperate = new DB();
		HashMap archivesInfo = new HashMap();
		String sql_select="SELECT u.userid,ub.username,ub.name_en,ub.birthday," +
							"ub.age,ub.nation,ub.nativeplace,ub.maritalstatus,uc.married_id," +
							"uc.identification_id,uc.pass_id,uc.passport_id,uc.taiwan_id,ub.sex," +
							"ub.path,c.email,c.mobile,c.tel1,c.tel2,c.fax,ub.is_stay,ub.household_addr," +
							"uc.shack_id,ub.address,c.zipcode,c.emer_name,c.emer_rel,c.emer_phone,c.conn_address," +
							"com.companyname,dep.departmentname,du.dutyname,le.leavlname,u.userid2,e.workingage," +
							"e.enter_date,e.ruyuan_date,e.medal_date,e.eid,e.hmt_eid,uc.creditcard_id," +
							"e.assurance_status,e.iid,e.aid,e.channel,e.probation_startdate,e.probation_stopdate," +
							"e.probation_period,e.full_date,e.contract_startdate,e.contract_stopdate," +
							"e.contract_period,ub.interest,ub.academic,ub.graduate_sch,ub.speciality," +
							"co.training_school,co.politics,ub.fig,ub.disability_level,uc.disability_id,co.comment " +
							"FROM userinfo u,userbase ub,usercard uc,connect c,enterduty e,comment co," +
							"company com,department dep,duty du,leavl le " +
							"WHERE u.com_id=com.com_id AND u.dep_id=dep.dep_id AND u.duty_id=du.duty_id " +
							"AND u.leavl_id=le.leavl_id AND u.userid=ub.userid AND ub.userid=uc.userid " +
							"AND uc.userid=c.userid AND c.userid=e.userid AND e.userid=co.userid " +
							"AND u.userid='"+userid+"'";
		ResultSet rst=null;
		try{
			rst=_DBOperate.doSelect(sql_select);
			while(rst.next()){
				archivesInfo.put("userid", rst.getString("userid"));
				archivesInfo.put("username", rst.getString("username"));
				archivesInfo.put("name_en", rst.getString("name_en"));
				archivesInfo.put("birthday", rst.getString("birthday"));
				archivesInfo.put("age", rst.getString("age"));
				archivesInfo.put("nation", rst.getString("nation"));
				archivesInfo.put("nativeplace", rst.getString("nativeplace"));
				archivesInfo.put("maritalstatus", rst.getString("maritalstatus"));
				archivesInfo.put("married_id", rst.getString("married_id"));
				archivesInfo.put("identification_id", rst.getString("identification_id"));
				archivesInfo.put("pass_id", rst.getString("pass_id"));
				archivesInfo.put("passport_id", rst.getString("passport_id"));
				archivesInfo.put("taiwan_id", rst.getString("taiwan_id"));
				archivesInfo.put("sex", rst.getString("sex"));
				archivesInfo.put("path", rst.getString("path"));
				archivesInfo.put("email", rst.getString("email"));
				archivesInfo.put("mobile", rst.getString("mobile"));
				archivesInfo.put("tel1", rst.getString("tel1"));
				archivesInfo.put("tel2", rst.getString("tel2"));
				archivesInfo.put("fax", rst.getString("fax"));
				archivesInfo.put("is_stay", rst.getString("is_stay"));
				archivesInfo.put("household_addr", rst.getString("household_addr"));
				archivesInfo.put("shack_id", rst.getString("shack_id"));
				archivesInfo.put("address", rst.getString("address"));
				archivesInfo.put("zipcode", rst.getString("zipcode"));
				archivesInfo.put("emer_name", rst.getString("emer_name"));
				archivesInfo.put("emer_rel", rst.getString("emer_rel"));
				archivesInfo.put("emer_phone", rst.getString("emer_phone"));
				archivesInfo.put("conn_address", rst.getString("conn_address"));
				archivesInfo.put("companyname", rst.getString("companyname"));
				archivesInfo.put("departmentname", rst.getString("departmentname"));
				archivesInfo.put("dutyname", rst.getString("dutyname"));
				archivesInfo.put("leavlname", rst.getString("leavlname"));
				archivesInfo.put("userid2", rst.getString("userid2"));
				archivesInfo.put("workingage", rst.getString("workingage"));
				archivesInfo.put("enter_date", rst.getString("enter_date"));
				archivesInfo.put("ruyuan_date", rst.getString("ruyuan_date"));
				archivesInfo.put("medal_date", rst.getString("medal_date"));
				archivesInfo.put("eid", rst.getString("eid"));
				archivesInfo.put("hmt_eid", rst.getString("hmt_eid"));
				archivesInfo.put("creditcard_id", rst.getString("creditcard_id"));
				archivesInfo.put("assurance_status", rst.getString("assurance_status"));
				archivesInfo.put("iid", rst.getString("iid"));
				archivesInfo.put("aid", rst.getString("aid"));
				archivesInfo.put("channel", rst.getString("channel"));
				archivesInfo.put("probation_startdate", rst.getString("probation_startdate"));
				archivesInfo.put("probation_stopdate", rst.getString("probation_stopdate"));
				archivesInfo.put("probation_period", rst.getString("probation_period"));
				archivesInfo.put("full_date", rst.getString("full_date"));
				archivesInfo.put("contract_startdate", rst.getString("contract_startdate"));
				archivesInfo.put("contract_stopdate", rst.getString("contract_stopdate"));
				archivesInfo.put("contract_period", rst.getString("contract_period"));
				archivesInfo.put("academic", rst.getString("academic"));
				archivesInfo.put("graduate_sch", rst.getString("graduate_sch"));
				archivesInfo.put("speciality", rst.getString("speciality"));
				archivesInfo.put("training_school", rst.getString("training_school"));			
				archivesInfo.put("politics", rst.getString("politics"));
				archivesInfo.put("fig", rst.getString("fig"));
				archivesInfo.put("disability_level", rst.getString("disability_level"));
				archivesInfo.put("disability_id", rst.getString("disability_id"));
				archivesInfo.put("comment", rst.getString("comment"));
			}
			rst.close();
			_DBOperate.closeDB();
		}catch(Exception e){
			e.printStackTrace();
		}
		return archivesInfo;
	}
}