<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<%@page contentType="text/html; charset=UTF-8"%>
<%@page import="com.csc.library.entry.*,com.csc.library.system.*,com.csc.library.session.*,com.csc.library.utilities.*,java.text.DecimalFormat,com.csc.library.database.*"%>
<%@page import="java.util.*,java.io.*,java.sql.*"%>
<%@ include file="../CHECKPROFILE.jsp" %>
<%
response.setHeader("Cache-Control","must-revalidate");
response.setContentType("application/vnd.ms-excel; UTF-8");
response.setHeader("Content-Disposition", "inline; filename=" + "export.xls");

CheckNull chk = new CheckNull();
CscCalendar c = new CscCalendar();

DecimalFormat dmfm1 = new DecimalFormat("#,##0.00");
DecimalFormat dmfm2 = new DecimalFormat("#,###");

CscCalendar now = new CscCalendar();

String cmd = chk.chkNullString(request.getParameter("__cmd"));
String filter = chk.chkNullString(request.getParameter("__filter"));
String groupby =chk.chkNullString(request.getParameter("__groupby"));
DbRecord dbrec = new InitialRecord(uprofile).getDbRecord("memployee");
dbrec.setColumn("*");
String bu1desc = dbrec.getField("bu1").getDescription();
String bu2desc = dbrec.getField("bu2").getDescription();
String bu3desc = dbrec.getField("bu3").getDescription();
String bu4desc = dbrec.getField("bu4").getDescription();
String bu5desc = dbrec.getField("bu5").getDescription();
%>
<html>
	<head>
		<title></title>
		<meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/>
		<style>
		.num {
			mso-number-format:General;
		}
		.format_text{
			mso-number-format:"\@";/*force text*/
		}
		</style>
	</head>
	<body leftmargin="0" topmargin="0">
		<form name="cscform" method="post" action="TAR11126.jsp">
			<table class="maxsize" border="1" cellpadding="0" cellspacing="1">
				<tr bgcolor="#0099FF">
					<td  class="row1" rowspan=2><div align="center"><b><%=uprofile.get("lang").equalsIgnoreCase("tha")?"รหัสพนักงาน":"Employee ID"%></b></div></td>
					<td  class="row1" rowspan=2><div align="center"><b><%=uprofile.get("lang").equalsIgnoreCase("tha")?"ชื่อพนักงาน":"Full Name"%></b></div></td>
					<td  class="row1" rowspan=2><div align="center"><b><%=bu1desc%></b></div></td>
					<td  class="row1" rowspan=2><div align="center"><b><%=bu2desc%></b></div></td>
					<td  class="row1" rowspan=2><div align="center"><b><%=bu3desc%></b></div></td>
					<td  class="row1" rowspan=2><div align="center"><b><%=bu4desc%></b></div></td>
					<td  class="row1" rowspan=2><div align="center"><b><%=bu5desc%></b></div></td>
					<td  class="row1" rowspan=2><div align="center"><b><%=uprofile.get("lang").equalsIgnoreCase("tha")?"วันที่":"Date"%></b></div></td>
					<td  class="row1" rowspan=2><div align="center"><b><%=uprofile.get("lang").equalsIgnoreCase("tha")?"วัน":"day"%></b></div></td>
					<td  class="row1" rowspan=2><div align="center"><b><%=uprofile.get("lang").equalsIgnoreCase("tha")?"สถานะ":"Status"%></b></div></td>
					<td  class="row1" rowspan=2><div align="center"><b><%=uprofile.get("lang").equalsIgnoreCase("tha")?"จำนวนชม.ทำงานต่อวัน":"Work Hour Day"%></b></div></td>
					<td  class="row1" colspan=3><div align="center"><b><%=uprofile.get("lang").equalsIgnoreCase("tha")?"แผนการทำงาน":"Schedule"%></b></div></td>
					<td  class="row1" colspan=2><div align="center"><b><%=uprofile.get("lang").equalsIgnoreCase("tha")?"การปฏิบัติงาน":"Punch"%></b></div></td>
					<td  class="row1" rowspan=2><div align="center"><b><%=uprofile.get("lang").equalsIgnoreCase("tha")?"สาย":"Late"%></b></div></td>
					<td  class="row1" rowspan=2><div align="center"><b><%=uprofile.get("lang").equalsIgnoreCase("tha")?"ลล":"NP"%></b></div></td>
					<td  class="row1" rowspan=2><div align="center"><b><%=uprofile.get("lang").equalsIgnoreCase("tha")?"ขาด":"Absent"%></b></div></td>
					<td  class="row1" rowspan=2><div align="center"><b><%=uprofile.get("lang").equalsIgnoreCase("tha")?"ลา":"Leave"%></b></div></td>
					<td  class="row1" colspan=4><div align="center"><b><%=uprofile.get("lang").equalsIgnoreCase("tha")?"ค่าล่วงเวลา":"Overtime"%></b></div></td>
					<td  class="row1" rowspan=2 nowrap><div align="center"><b><%=uprofile.get("lang").equalsIgnoreCase("tha")?"ค่ากะ":"Shift allowance"%></b></div></td>
					<td  class="row1" rowspan=2><div align="center"><b><%=uprofile.get("lang").equalsIgnoreCase("tha")?"ลา":"Leave"%></b></div></td>
					<td  class="row1" rowspan=2><div align="center"><b><%=uprofile.get("lang").equalsIgnoreCase("tha")?"ไม่มีเข้า":"Not Punch In"%></b></div></td>
					<td  class="row1" rowspan=2><div align="center"><b><%=uprofile.get("lang").equalsIgnoreCase("tha")?"ไม่มีออก":"Not Punch Out"%></b></div></td>
				</tr>
				<tr bgcolor="#0099FF">
					<td  class="row1" ><div align="center"><b><%=uprofile.get("lang").equalsIgnoreCase("tha")?"กะการทำงาน":"Shift"%></b></div></td>
					<td  class="row1" ><div align="center"><b><%=uprofile.get("lang").equalsIgnoreCase("tha")?"เวลาเข้า":"IN"%></b></div></td>
					<td  class="row1" ><div align="center"><b><%=uprofile.get("lang").equalsIgnoreCase("tha")?"เวลาออก":"OUT"%></b></div></td>
					<td  class="row1" ><div align="center"><b><%=uprofile.get("lang").equalsIgnoreCase("tha")?"เวลาเข้า":"IN"%></b></div></td>
					<td  class="row1" ><div align="center"><b><%=uprofile.get("lang").equalsIgnoreCase("tha")?"เวลาออก":"OUT"%></b></div></td>
					<td  class="row1" ><div align="center"><b>1</b></div></td>
					<td  class="row1" ><div align="center"><b>1.5</b></div></td>
					<td  class="row1" ><div align="center"><b>2</b></div></td>
					<td  class="row1" ><div align="center"><b>3</b></div></td>
				</tr>
				<%
				int i=1;
				String empid ="";
				String prefix ="";
				String fname ="";
				String lname ="";
				String date ="";
				String tbu1 ="";
				String tbu2 ="";
				String tbu3 ="";
				String tbu4 ="";
				String tbu5 ="";
				String time0 ="";
				String C_TM_BG ="";
				String C_TM_EN ="";
				String M_TM_BG ="";
				String M_TM_EN ="";
				String lt ="";
				String absent ="";
				String TABSENT ="";
				String ot1 ="";
				String ot5 ="";
				String ot2 ="";
				String ot3 ="";
				String unused1 ="";
				String w0 ="";
				String w1 ="";
				String w5 ="";
				String eventgrp ="";
				String approvestatus ="";
				String eventgrp1desc = "";
				String empbranch = "";
				String hour_d = "";
				String showgroup = "";
				String chkgroup = "";
				String oldgroup = "";
				int countgroup = 0;
				boolean isfirstgroup = true;

				String V_sdesc = (uprofile.get("lang").equalsIgnoreCase("tha"))?"tsdesc":"esdesc";
				String V_desc = (uprofile.get("lang").equalsIgnoreCase("tha"))?"tdesc":"edesc";
				String V_fname = (uprofile.get("lang").equalsIgnoreCase("tha"))?"fname":"efname";
				String V_lname = (uprofile.get("lang").equalsIgnoreCase("tha"))?"lname":"elname";

				DbInquiry evdesc = new InitialInquiry(uprofile).getDbInquiry("MEVENTGRP");
				evdesc.setColumn("*");
				evdesc.setFilter("EVENTGRPID='EAL'");
				evdesc.refresh();
				evdesc.next();

				String evd = (uprofile.get("lang").equalsIgnoreCase("tha"))?evdesc.getString("tsdesc"):evdesc.getString("esdesc");

				StringBuffer sql = new StringBuffer();
				sql.append("select t1.employeeid as employeeid,et."+V_desc+" as emptype,p."+V_desc+" as prefix,e."+V_fname+" as fname,e."+V_lname+" as lname,bu1."+V_desc+" as tbu1,bu2."+V_desc+" as tbu2,bu3."+V_desc+" as tbu3,bu4."+V_desc+" as tbu4,bu5."+V_desc+" as tbu5,t1.DATEID as date ");
				sql.append(",t1.bu1 as bu1id,t1.bu2 as bu2id,t1.bu3 as bu3id,t1.bu4 as bu4id,t1.bu5 as bu5id ");
				sql.append(",t1.time0id as time0,t1.C_TM_BG as c_tm_bg,t1.C_TM_EN as c_tm_en,t1.M_TM_BG as m_tm_bg,t1.M_TM_EN as m_tm_en,t1.LT as lt,t1.SOURCE_IN,t1.SOURCE_OUT ");
				sql.append(",t1.M_LV as absent,t1.hour_d as hour_d ");
				sql.append(",t1.WARN00 as w0,t1.WARN01 as w1,ev."+V_sdesc+" as tabsent,t1.OT1,t1.OT5,t1.OT2,t1.OT3,t1.UNUSED1");
				sql.append(",t1.eventgrp as eventgrp,t1.forget_in,t1.forget_out,t1.m_lv,ts.approve as ApproveStatus,ev1."+V_desc+" as event1desc,e.branchid as branchid,t1.warn05 as w5 ");
				sql.append("from ttime_current1 t1 ");
				sql.append("left join memployee e on t1.employeeid=e.employeeid ");
				sql.append("left join memployment_type et on e.emp_type = et.codeid ");
				sql.append("left join mprefix p on e.emp_prefix=p.prefixid ");
				sql.append("left join mbu1 bu1 on t1.bu1=bu1.bu1id ");
				sql.append("left join mbu2 bu2 on t1.bu2=bu2.bu2id ");
				sql.append("left join mbu3 bu3 on t1.bu3=bu3.bu3id ");
				sql.append("left join mbu4 bu4 on t1.bu4=bu4.bu4id ");
				sql.append("left join mbu5 bu5 on t1.bu5=bu5.bu5id ");
				sql.append("left join MEVENTGRP ev on t1.EVENTGRP = ev.EVENTGRPID ");
				sql.append("left join MEVENTGRP1 ev1 on e.branchid = ev1.branchid and t1.EVENTGRP = ev1.EVENTGRPID  ");
				sql.append("left join TLEAVE_SUMMARY ts on t1.employeeid = ts.employeeid and t1.dateid = ts.dateid and ts.TABLE_NAME = 'TTIME_CURRENT1' and ts.doctype=t1.eventgrp where ");
				sql.append(filter);

				String dbName = uprofile.getDbName();
				String tableName = "ttime_current";
				DataSet ds = new DataSet(sql.toString(), dbName, tableName );
				ds.initConnection(uprofile);
				while(ds.next()){
					empid = chk.chkNullString(ds.getString("employeeid"));
					prefix = chk.chkNullString(ds.getString("prefix"));
					fname = chk.chkNullString(ds.getString("fname"));
					lname = chk.chkNullString(ds.getString("lname"));
					date = chk.chkNullString(ds.getString("date"));
					tbu1 = chk.chkNullString(ds.getString("tbu1"));
					tbu2 = chk.chkNullString(ds.getString("tbu2"));
					tbu3 = chk.chkNullString(ds.getString("tbu3"));
					tbu4 = chk.chkNullString(ds.getString("tbu4"));
					tbu5 = chk.chkNullString(ds.getString("tbu5"));
					time0 = chk.chkNullString(ds.getString("time0"));
					C_TM_BG = chk.chkNullString(ds.getString("c_tm_bg"));
					C_TM_EN = chk.chkNullString(ds.getString("c_tm_en"));
					M_TM_BG = chk.chkNullString(ds.getString("forget_in").equals("1")?"0.00":(ds.getString("source_in").equals("A"))?"-":ds.getString("m_tm_bg"));
					M_TM_EN = chk.chkNullString(ds.getString("forget_out").equals("1")?"0.00":(ds.getString("source_out").equals("A"))?"-":ds.getString("m_tm_en"));
					lt = chk.chkNullString(ds.getString("lt"),"0.00");
					absent = chk.chkNullString(ds.getString("absent"),"0.00");
					TABSENT = chk.chkNullString(ds.getString("tabsent"),"0.00");
					approvestatus = chk.chkNullString(ds.getString("ApproveStatus"));
					ot1 = chk.chkNullString(ds.getString("ot1"),"0.00");
					ot5 = chk.chkNullString(ds.getString("ot5"),"0.00");
					ot2 = chk.chkNullString(ds.getString("ot2"),"0.00");
					ot3 = chk.chkNullString(ds.getString("ot3"),"0.00");
					unused1 = chk.chkNullString(ds.getString("unused1"),"0.00");
					w0 = chk.chkNullString(ds.getString("w0"));
					w1 = chk.chkNullString(ds.getString("w1"));
					w5 = chk.chkNullString(ds.getString("w5"));
					eventgrp = chk.chkNullString(ds.getString("eventgrp"));
					eventgrp1desc = chk.chkNullString(ds.getString("event1desc"));
					empbranch = chk.chkNullString(ds.getString("branchid"));
					hour_d = chk.chkNullString(ds.getString("hour_d"));
					CscCalendar cscdate = new CscCalendar(date);

					DecimalFormat myFormatter = new DecimalFormat("#0.00");

					if(!groupby.equals("")) {
						if(groupby.equalsIgnoreCase("bu1")) {
							showgroup = chk.chkNullString(ds.getString("bu1id")) + " :: " + chk.chkNullString(ds.getString("tbu1"));
						} else if(groupby.equalsIgnoreCase("bu2")) {
							showgroup = chk.chkNullString(ds.getString("bu2id")) + " :: " + chk.chkNullString(ds.getString("tbu2"));
						} else if(groupby.equalsIgnoreCase("bu3")) {
							showgroup = chk.chkNullString(ds.getString("bu3id")) + " :: " + chk.chkNullString(ds.getString("tbu3"));
						} else if(groupby.equalsIgnoreCase("bu4")) {
							showgroup = chk.chkNullString(ds.getString("bu4id")) + " :: " + chk.chkNullString(ds.getString("tbu4"));
						} else if(groupby.equalsIgnoreCase("bu5")) {
							showgroup = chk.chkNullString(ds.getString("bu5id")) + " :: " + chk.chkNullString(ds.getString("tbu5"));
						}

						if(!showgroup.equals(chkgroup)) {
							oldgroup = chkgroup;
							chkgroup = showgroup;
							if(isfirstgroup){
								isfirstgroup = false;
							} else {
								String showcount = (uprofile.get("lang").equalsIgnoreCase("tha"))?"รวม " + countgroup + " รายการ":"Total " + countgroup + " Record(s)";
								%>
								<tr>
									<td colspan="25">&nbsp;&nbsp;<%=showcount%></td>
								</tr>
								<%
							}
							%>
							<tr>
								<td colspan="25">&nbsp;&nbsp;<%=showgroup%></td>
							</tr>
							<%
							countgroup = 0;
						}
					}
					countgroup++;
					%>
					<tr>
						<td  nowrap class="format_text"><div align="center"><%=empid%></div></td>
						<td  nowrap ><div align="center"><%=prefix%><%=fname%>&nbsp;&nbsp;&nbsp;<%=lname%></div></td>
						<td  nowrap ><div align="center"><%=tbu1%></div></td>
						<td  nowrap ><div align="center"><%=tbu2%></div></td>
						<td  nowrap ><div align="center"><%=tbu3%></div></td>
						<td  nowrap ><div align="center"><%=tbu4%></div></td>
						<td  nowrap ><div align="center"><%=tbu5%></div></td>
						<td  nowrap ><div align="center"><%=cscdate.getDD()+"/"+cscdate.getMM()+"/"+cscdate.getYear()%></div></td>
						<td  nowrap ><div align="center"><%=uprofile.get("lang").equalsIgnoreCase("tha")?cscdate.getShortThiDOW():cscdate.getShortEngDOW()%></div></td>
						<td  nowrap ><div align="center">
							<%
							if(eventgrp.equals("J") && !w5.equals("")) {
								out.print(evd);
							}else if(Double.parseDouble(lt)>0.00){
								out.print((uprofile.get("lang").equalsIgnoreCase("tha"))?"สาย":"Late");
							}else{
								out.print(TABSENT);
							}
							%>
						</div></td>
						<td  nowrap class="format_text"><div align="center"><%=hour_d%></div></td>
						<td  nowrap class="format_text"><div align="center"><%=time0%></div></td>
						<td  nowrap class="format_text"><div align="center"><%=C_TM_BG%></div></td>
						<td  nowrap class="format_text"><div align="center"><%=C_TM_EN%></div></td>
						<td  nowrap class="format_text"><div align="center">
							<%
							if(M_TM_BG.equals("0.00") && !eventgrp.equals("T")){
								out.print("-");
							}else{
								out.print(M_TM_BG);
							}
							%>
						</div></td>
						<td  nowrap class="format_text"><div align="center">
							<%
							if(M_TM_EN.equals("0.00") && !eventgrp.equals("T")){
								out.print("-");
							}else{
								out.print(M_TM_EN);
							}
							%>
						</div></td>
						<td class="format_text" nowrap ><div align="center">
							<%
							if(lt.equals("0.00")){
								out.print("");
							}else{
								out.println(myFormatter.format(Double.parseDouble(lt)));
							}
							%>
						</div></td>
						<td class="format_text" nowrap ><div align="center"><!--- np -->
							<%
							if(absent.equals("0.00")){
								out.print("");
							}else{
								if(eventgrp.equals("NP")){
									out.print(myFormatter.format(Double.parseDouble(absent)));
								}
							}
							%>
						</div></td>
						<td class="format_text" nowrap ><div align="center">
							<%
							if(absent.equals("0.00")){
								out.print("");
							}else{
								if(eventgrp.equals("J")){
									out.print(myFormatter.format(Double.parseDouble(absent)));
								}
							}
							%>
						</div></td>
						<td class="format_text" nowrap ><div align="center">
							<%
							if(absent.equals("0.00")){
								out.print("");
							}else{
								if(!eventgrp.equals("J") && !eventgrp.equals("NP")){
									out.print(myFormatter.format(Double.parseDouble(absent)));
								}
							}
							%>
						</div></td>
						<td class="format_text" nowrap ><div align="center"><%out.print(myFormatter.format(Double.parseDouble(ot1)));%></div></td>
						<td class="format_text" nowrap ><div align="center"><%out.print(myFormatter.format(Double.parseDouble(ot5)));%></div></td>
						<td class="format_text" nowrap ><div align="center"><%out.print(myFormatter.format(Double.parseDouble(ot2)));%></div></td>
						<td class="format_text" nowrap ><div align="center"><%out.print(myFormatter.format(Double.parseDouble(ot3)));%></div></td>
						<td class="format_text" nowrap ><div align="center"><%out.print(myFormatter.format(Double.parseDouble(unused1)));%></div></td>
						<td  nowrap ><div align="center"><%=notLeave(eventgrp,approvestatus)?"":getAbsent(empid,date,C_TM_BG,uprofile)%></div></td>
						<td  nowrap ><div align="center">
							<%
							if(!w0.equals("") && eventgrp.equalsIgnoreCase("NP")){
								out.print((uprofile.get("lang").equalsIgnoreCase("tha"))?"ลืมบันทึกเข้า":"Not Punch In");
							}
							%>
						</div></td>
						<td  nowrap ><div align="center">
							<%
							if(!w1.equals("") && eventgrp.equalsIgnoreCase("NP")){
								out.print((uprofile.get("lang").equalsIgnoreCase("tha"))?"ลืมบันทึกออก":"Not Punch Out");
							}
							%>
						</div></td>
					</tr>
					<%	i++;
				}

				if(!groupby.equals("")) {
					String showcount = (uprofile.get("lang").equalsIgnoreCase("tha"))?"รวม " + countgroup + " รายการ":"Total " + countgroup + " Record(s)";
					%>
					<tr>
						<td colspan="25">&nbsp;&nbsp;<%=showcount%></td>
					</tr>
					<%
				}
				%>
			</table>
		</form>
	</body>
</html>
<%!
public boolean notLeave(String eventgrp,String approvestatus){
	try {
		if(eventgrp.equals("T") || eventgrp.equals("I") || eventgrp.equals("H") || eventgrp.equals("J")
		|| eventgrp.equals("O") || approvestatus.equals("w")){
			return true ;
		}
	}catch(Exception e){
		e.printStackTrace();
	}
	return false ;
}

private String getAbsent(String employeeid,String date,String time,UProfile uprofile){
	String absent="";
	CheckNull chk = new CheckNull();
	CscCalendar date2 = new CscCalendar(date+" "+time.replace(".",":")+":00");
	String sql ="select ev.tdesc as tdesc,ev.edesc as edesc,tab.start_date , tab.end_date as end_date,tab.start_time,tab.end_time from TABSENT_NSTDA tab left join MEVENTGRP ev on tab.TYPE_ABSENT = ev.EVENTGRPID  where EMPLOYEEID='"+employeeid+"'";
	String dbName = uprofile.getDbName();
	String tableName = "TABSENT_NSTDA";
	try{
		DataSet ds = new DataSet(sql, dbName, tableName );
		ds.initConnection(uprofile);
		while(ds.next()){
			CscCalendar st = new CscCalendar(chk.chkNullString(ds.getString("start_date"))+" "+chk.chkNullString(ds.getString("start_time"),"0.00").replace(".",":")+":00");
			CscCalendar en = new CscCalendar(chk.chkNullString(ds.getString("end_date"))+" "+chk.chkNullString(ds.getString("end_time"),"0.00").replace(".",":")+":00");
			if(date2.afterEqualsDateTime(st)&&date2.beforeEqualsDateTime(en)){
				absent = chk.chkNullString((uprofile.get("lang").equalsIgnoreCase("tha"))?ds.getString("tdesc"):ds.getString("edesc"));
			}
		}
	}catch(Exception e){
		e.printStackTrace();
	}
	return absent;
}
%>