syain_kyutuketu.java

jsp考勤

/*
 *	Copyright (C) 2000, TOA-System Co.  All rights reserved.
 */
package test;

import java.sql.*;
import java.text.*;
import exce.*;
import def.*;
import util.*;
import entity.*;
import dbcommon.*;

/**
 *	偙偺僋儔僗偼丄儘僌僀儞娭楢偺僋儔僗偱偡丅<br>
 *	@version	2.00
 *	@author		TOA
 *
 *	峏怴棜楌
 *	<ul>
 *		<li>僐儊儞僩傪捛壛
 *	</ul>
 */
public class Syain_Kyutuketu {
	public Syain_Kyutuketu() {}
	CommonUtil cu = new CommonUtil();
	ChangeField cf = new ChangeField();
	Status st = new Status();
	SimpleDateFormat sdf = new SimpleDateFormat(SISnet.DATE_FMT);

	/**
	 *	幮堳弌寚婰榐偺庢摼傪峴偆
	 *	@param	conn		DB僪儔僀僶偺URL側偳
	 *	@return	
	 */
	public Syutuketu_Jouhou_003Entity[] search(Connection conn,String bumenNo,String useidfrom,String useidto,String datefrom,String dateto) {							    
		String		where	= "";
		String		and	= "0";
		
		try {
			// Syutuketu_Jouhou_003DataAccess僆僽僕僃僋僩偺findSyutuketu_Jouhou_003DataAccess()儊僜僢僪傪幚峴偟傑偡丅
			Syutuketu_Jouhou_003DataAccess sjda = new Syutuketu_Jouhou_003DataAccess();

			if(bumenNo.equals("")&&useidfrom.equals("")&&useidto.equals("")&&datefrom.equals("")&&dateto.equals("")){
			        where = "";
			}
			else{
					where+=" WHERE ";
					if(!bumenNo.equals("")){
						where+="BUMON_CD = '"+ bumenNo + "'";
						and	= "1";
					}
					if(!useidfrom.equals("")){
						if(and.equals("1")){
							where+=" AND ";
						}
						where+="USER_CD >= '"+ useidfrom + "'";
						and	= "1";
					}
					if(!useidto.equals("")){
						if(and.equals("1")){
							where+=" AND ";
						}
						where+="USER_CD <= '"+ useidto + "'";
						and	= "1";
					}
					if(!datefrom.equals("")){
						if(and.equals("1")){
							where+=" AND ";
						}
						where+="DATE_TIME >=to_date('" + sdf.format(cf.strToDate(datefrom))+"','yyyy/mm/dd')";
						and	= "1";
					}
					if(!dateto.equals("")){
						if(and.equals("1")){
							where+=" AND ";
						}
						where+="DATE_TIME <=to_date('" + sdf.format(cf.strToDate(dateto))+"','yyyy/mm/dd')";
					}
			}

			where+="ORDER BY BUMON_CD,USER_CD,DATE_TIME";


			Syutuketu_Jouhou_003Entity[] sjte = sjda.findSyutuketu_Jouhou_003(conn, where);


		       return sjte;
			

		} catch (ApplicationException ae) {
			st = ae.getStatus();
			ae.printStackTrace();
			throw new ApplicationException(st.usermsg, st.errno);
		} catch (OracleException orae) {
			st = orae.getStatus();
			orae.printStackTrace();
			throw new OracleException(st);
		} catch (Exception e) {
			e.printStackTrace();
			throw new OriginalException(e);
		}
	}

	public Syutuketu_Jouhou_003Entity[] tongjisearch(Connection con,String bumenid,String useridfrom,String useridto,String datefrom,String dateto)
	{
		String where = "";
		String length = "0";
		try
		{	
			if (!bumenid.equals(""))
			{
				where = " where BUMON_CD='"+bumenid+"'";
				length = "1";
			}
			
			if (!useridfrom.equals(""))
			{
				if (length.equals("0"))
				{
					where = " where USER_CD>='"+useridfrom+"'";
					length = "1";
				}
				else
				{
					where = where + " and USER_CD>='"+useridfrom+"'";
				}
			}	
			if (!useridto.equals(""))
			{
				if (length.equals("0"))
				{
					where = " where USER_CD<='"+useridto+"'";
					length = "1";
				}
				else
				{
					where = where + " and USER_CD<='"+useridto+"'";
				}
			}
			if (!datefrom.equals(""))
			{
				if (length.equals("0"))
				{
					where = " where DATE_TIME>=to_date('"+datefrom+"','yyyy-mm-dd')";
					length = "1";
				}
				else
				{
					where = where + " and DATE_TIME>=to_date('"+datefrom+"','yyyy-mm-dd')";
				}
			}
			if (!dateto.equals(""))
			{
				if (length.equals("0"))
				{
					where = " where DATE_TIME<to_date('"+dateto+"','yyyy-mm-dd')";
					length = "1";
				}
				else
				{
					where = where + " and DATE_TIME<to_date('"+dateto+"','yyyy-mm-dd')";
				}
			}
			where = where+" order by BUMON_CD,USER_CD,DATE_TIME";
			Syutuketu_Jouhou_003DataAccess sjda = new Syutuketu_Jouhou_003DataAccess();
			Syutuketu_Jouhou_003Entity[] ees = sjda.findSyutuketu_Jouhou_003(con,where);
			return ees;
		}
		catch (ApplicationException ae) {
			st = ae.getStatus();
			ae.printStackTrace();
			throw new ApplicationException(st.usermsg, st.errno);
		} catch (OracleException orae) {
			st = orae.getStatus();
			orae.printStackTrace();
			throw new OracleException(st);
		} catch (Exception e) {
			e.printStackTrace();
			throw new OriginalException(e);
		}
	}

	/**
	 *	幮堳弌寚摑寁婰榐偺庢摼傪峴偆
	 *	@param	conn		DB僪儔僀僶偺URL側偳
	 *	@return	
	 */
	public double[] heji_search(Connection conn,String bumenNo,String useid,String datefrom,String dateto) {
		ResultSet rs 			= null;
		PreparedStatement pstmt = null;
		DataAccess dace = new DataAccess();

		String		where	= " WHERE BUMON_CD = '"+ bumenNo + "'" + " AND USER_CD = '"+ useid + "'";
		double[] heji =new double[6];
		
		try {
			
				if(!datefrom.equals("")){
					where+=" AND DATE_TIME >=to_date('" + sdf.format(cf.strToDate(datefrom))+"','yyyy/mm/dd')";
				}
				if(!dateto.equals("")){
					where+=" AND DATE_TIME <=to_date('" + sdf.format(cf.strToDate(dateto))+"','yyyy/mm/dd')";
				}

				String sql = "SELECT DISTINCT SUM(TIKOKU) AS TIKOKU,SUM(SOUTAI) AS SOUTAI,SUM(ZANGYOU) AS ZANGYOU,SUM(KETUKIN) AS KETUKIN,SUM(XIUJIA) AS XIUJIA,SUM(SYUTYOU) AS SYUTYOU FROM SYUTUKETU_JOUHOU_003";
                sql = sql+where;

				pstmt = dace.sqlSet(conn,sql);
			    rs = dace.exeQuery(pstmt);

				while (rs.next()) {
					
						heji[0]                 =rs.getDouble("TIKOKU");           //抶崗     
						heji[1]                 =rs.getDouble("SOUTAI");           //憗戅
						heji[2]			        =rs.getDouble("ZANGYOU");          //巆嬈丂
						heji[3]                 =rs.getDouble("XIUJIA");           //巹帠媥橈
						heji[4]                 =rs.getDouble("SYUTYOU");          //弌挘 
						heji[5]			        =rs.getDouble("KETUKIN");          //寚嬑
			}



		       return heji;
			

		} catch (ApplicationException ae) {
			st = ae.getStatus();
			ae.printStackTrace();
			throw new ApplicationException(st.usermsg, st.errno);
		} catch (OracleException orae) {
			st = orae.getStatus();
			orae.printStackTrace();
			throw new OracleException(st);
		} catch (Exception e) {
			e.printStackTrace();
			throw new OriginalException(e);
		}
	}


	/** 幮堳弌寚婰榐偺曄峏傪峴偆
	 *	
	 *	@param	conn		DB僪儔僀僶偺URL側偳
	 *			pr		
	 */
	public void updata(Connection conn,Syutuketu_Jouhou_003Entity sjee) {							
		
		try {

			// Syutuketu_Jouhou_003DataAccess僆僽僕僃僋僩偺updateSyutuketu_Jouhou_003()儊僜僢僪傪幚峴偟傑偡丅
			Syutuketu_Jouhou_003DataAccess sjda = new Syutuketu_Jouhou_003DataAccess();

	        sjda.updateSyutuketu_Jouhou_003(conn, sjee);


		} catch (ApplicationException ae) {
			st = ae.getStatus();
			ae.printStackTrace();
			throw new ApplicationException(st.usermsg, st.errno);
		} catch (OracleException orae) {
			st = orae.getStatus();
			orae.printStackTrace();
			throw new OracleException(st);
		} catch (Exception e) {
			e.printStackTrace();
			throw new OriginalException(e);
		}
	}


	/**
	 *	幮堳弌寚婰榐偺嶍彍傪峴偆
	 *	@param	conn		DB僪儔僀僶偺URL側偳
	 *			
	 *			
	 */
	public void delet(Connection conn,Syutuketu_Jouhou_003Entity sjee) {	
		String		where	= "";
		
		try {
			// Syutuketu_Jouhou_003DataAccess僆僽僕僃僋僩偺deleteSyutuketu_Jouhou_003()儊僜僢僪傪幚峴偟傑偡丅
			Syutuketu_Jouhou_003DataAccess sjda = new Syutuketu_Jouhou_003DataAccess();
			where	= "WHERE USER_CD = '" + sjee._user_cd + "' AND BUMON_CD = '" + sjee._bumon_cd + "'" + " AND DATE_TIME = to_date('" + sdf.format(sjee._date_time) +"','yyyy/mm/dd')";
			
	        sjda.deleteSyutuketu_Jouhou_003(conn, where);

		} catch (ApplicationException ae) {
			st = ae.getStatus();
			ae.printStackTrace();
			throw new ApplicationException(st.usermsg, st.errno);
		} catch (OracleException orae) {
			st = orae.getStatus();
			orae.printStackTrace();
			throw new OracleException(st);
		} catch (Exception e) {
			e.printStackTrace();
			throw new OriginalException(e);
		}
	}

}