<!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"> <%=showcount%></td> </tr> <% } %> <tr> <td colspan="25"> <%=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%> <%=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"> <%=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; } %>