<%@page contentType="text/html; charset=UTF-8"%> <%@ page import="com.csc.library.entry.*" %> <%@ page import="com.csc.library.utilities.*, java.util.*" %> <%@page import="com.csc.library.database.*"%> <%@ include file="../CHECKPROFILE.jsp" %> <% response.setContentType("application/vnd.ms-excel; UTF-8"); response.setHeader("Content-Disposition", "inline; filename=" + "export.xls"); CheckNull chk = new CheckNull(); CscCalendar cs = new CscCalendar(); int year = Integer.parseInt(chk.chkNullString(request.getParameter("__year"),cs.getYear()+"")); int last_year = year-1; String sql1 =""; String sql2 =""; String sql3 =""; String sql4 =""; String sql5 =""; String sql6 =""; String sql7 =""; String sql8 =""; String sql9 =""; String sql10 =""; String sql11 =""; String sql12 =""; String sql13 =""; String sql14 =""; String sql15 =""; String sql16 =""; String sql17 =""; String sql18 =""; String sql19 =""; String sql20 =""; String dbtype = uprofile.getDbType(); if(dbtype.equalsIgnoreCase("PGSQL")){ sql1 = "select getEmpInMonth('"+year+"-'||month::text) as num from month_list"; sql2 = "select (select count(employeeid) from memployee where startdate>=('"+year+"-'||(month)::TEXT||'-01')::TEXT and startdate<=('"+year+"-'||(month)::TEXT||'-31')::TEXT) as num from month_list"; sql3 = "select (select count(employeeid) from memployee where endworkdate>=('"+year+"-'||(month)::TEXT||'-01')::TEXT and endworkdate<=('"+year+"-'||(month)::TEXT||'-31')::TEXT) as num from month_list"; sql4 = "select (select count(employeeid) from memployee where startdate>=('"+last_year+"-'||(month)::TEXT||'-31')::TEXT and startdate<=('"+year+"-'||(month)::TEXT||'-31')::TEXT) as num from month_list"; sql5 = "select (select count(employeeid) from memployee where startdate <= ('"+last_year+"-'||(month)::TEXT||'-01')::TEXT) as num from month_list"; sql6 = "select (select sum(CASE M_lv WHEN '8.00' THEN 1 WHEN '4.00' THEN 0.5 ELSE 0 END) from tleave_summary where lv_type in ('SCL','SCW') and DATEID>=('"+year+"-'||month::TEXT||'-01')::TEXT and DATEID<=('"+year+"-'||month::TEXT||'-31')::TEXT) as num from month_list"; sql7 = "select (select sum(CASE M_lv WHEN '8.00' THEN 1 WHEN '4.00' THEN 0.5 ELSE 0 END) from tleave_summary where lv_type in ('OTW','PVL') and DATEID>=('"+year+"-'||month::TEXT||'-01')::TEXT and DATEID<=('"+year+"-'||month::TEXT||'-31')::TEXT) as num from month_list"; sql8 = "select (SELECT COUNT(HDATE) from mholiday1 where substring(hdate,6,2)=month and extract(dow from to_timestamp(HDATE, 'YYYY/MM/DD hh24:mi:ss')) not in ('0','6') and substring(hdate,1,4)='"+year+"') as num from month_list"; sql9 = "select (select count(employeeid) from memployee where RESIGNDATE>=('"+year+"-'||month::TEXT||'-01')::TEXT and RESIGNDATE<=('"+year+"-'||month::TEXT||'-31')::TEXT and status='Y') as num from month_list"; sql10 = "select (select count(employeeid) from memployee where RESIGNDATE>=('"+year+"-'||month::TEXT||'-01')::TEXT and RESIGNDATE<=('"+year+"-'||month::TEXT||'-31')::TEXT and status='X') as num from month_list"; sql11 = "select (select count(employeeid) from memployee where RESIGNDATE>=('"+year+"-'||month::TEXT||'-01')::TEXT and RESIGNDATE<=('"+year+"-'||month::TEXT||'-31')::TEXT and status='L') as num from month_list"; sql12 = "select (select count(employeeid) from memployee where RESIGNDATE>=('"+year+"-'||month::TEXT||'-01')::TEXT and RESIGNDATE<=('"+year+"-'||month::TEXT||'-31')::TEXT and status='U') as num from month_list"; sql13 = "select (select count(employeeid) from memployee where RESIGNDATE>=('"+year+"-'||month::TEXT||'-01')::TEXT and RESIGNDATE<=('"+year+"-'||month::TEXT||'-31')::TEXT and status='Z') as num from month_list"; sql14 = "select (select count(employeeid) from memployee where APPROVE_DATE>=('"+year+"-'||month::TEXT||'-01')::TEXT and APPROVE_DATE<=('"+year+"-'||month::TEXT||'-31')::TEXT and status='A') as num from month_list"; sql15 = "select getFullname(employeeid,'th') as fullname, EXTRACT(month FROM to_timestamp(startdate, 'YYYY/MM/DD hh24:mi:ss')) as month from memployee where EXTRACT(year FROM to_timestamp(startdate, 'YYYY/MM/DD hh24:mi:ss'))='"+year+"'"; sql16 = "select getFullname(employeeid,'th') as fullname, EXTRACT(month FROM to_timestamp(resigndate, 'YYYY/MM/DD hh24:mi:ss')) as month from memployee where EXTRACT(year FROM to_timestamp(resigndate, 'YYYY/MM/DD hh24:mi:ss'))='"+year+"' and status='Y'"; sql17 = "select getFullname(employeeid,'th') as fullname, EXTRACT(month FROM to_timestamp(resigndate, 'YYYY/MM/DD hh24:mi:ss')) as month from memployee where EXTRACT(year FROM to_timestamp(resigndate, 'YYYY/MM/DD hh24:mi:ss'))='"+year+"' and status='X'"; sql18 = "select getFullname(employeeid,'th') as fullname, EXTRACT(month FROM to_timestamp(resigndate, 'YYYY/MM/DD hh24:mi:ss')) as month from memployee where EXTRACT(year FROM to_timestamp(resigndate, 'YYYY/MM/DD hh24:mi:ss'))='"+year+"' and status='L'"; sql19 = "select getFullname(employeeid,'th') as fullname, EXTRACT(month FROM to_timestamp(resigndate, 'YYYY/MM/DD hh24:mi:ss')) as month from memployee where EXTRACT(year FROM to_timestamp(resigndate, 'YYYY/MM/DD hh24:mi:ss'))='"+year+"' and status='U'"; sql20 = "select getFullname(employeeid,'th') as fullname, EXTRACT(month FROM to_timestamp(resigndate, 'YYYY/MM/DD hh24:mi:ss')) as month from memployee where EXTRACT(year FROM to_timestamp(resigndate, 'YYYY/MM/DD hh24:mi:ss'))='"+year+"' and status='Z'"; }else if(dbtype.equalsIgnoreCase("MSSQL")){ sql1 = "select dbo.getEmpInMonth('"+year+"-'+CAST(month as varchar(2))) as 'num' from month_list"; sql2 = "select (select count(employeeid) from memployee where startdate>='"+year+"-'+month+'-01' and startdate<='"+year+"-'+month+'-31') as 'num' from month_list"; sql3 = "select (select count(employeeid) from memployee where endworkdate>='"+year+"-'+month+'-01' and endworkdate<='"+year+"-'+month+'-31') as 'num' from month_list"; sql4 = "select (select count(employeeid) from memployee where startdate>='"+last_year+"-'+month+'-31' and startdate<='"+year+"-'+month+'-31') as 'num' from month_list"; sql5 = "select (select count(employeeid) from memployee where startdate<='"+last_year+"-'+month+'-01') as 'num' from month_list"; sql6 = "select (select sum(CASE M_lv WHEN '8.00' THEN 1 WHEN '4.00' THEN 0.5 ELSE 0 END) from tleave_summary where lv_type in ('SCL','SCW') and DATEID>='"+year+"-'+month+'-01' and DATEID<='"+year+"-'+month+'-31') as 'num' from month_list"; sql7 = "select (select sum(CASE M_lv WHEN '8.00' THEN 1 WHEN '4.00' THEN 0.5 ELSE 0 END) from tleave_summary where lv_type in ('OTW','PVL') and DATEID>='"+year+"-'+month+'-01' and DATEID<='"+year+"-'+month+'-31') as 'num' from month_list"; sql8 = "select (SELECT COUNT(HDATE) from mholiday1 where substring(hdate,6,2)=month and datepart(dw,HDATE) not in ('1','7') and substring(hdate,1,4)='"+year+"') as 'num' from month_list"; sql9 = "select (select count(employeeid) from memployee where RESIGNDATE>='"+year+"-'+month+'-01' and RESIGNDATE<='"+year+"-'+month+'-31' and status='Y') as 'num' from month_list"; sql10 = "select (select count(employeeid) from memployee where RESIGNDATE>='"+year+"-'+month+'-01' and RESIGNDATE<='"+year+"-'+month+'-31' and status='X') as 'num' from month_list"; sql11 = "select (select count(employeeid) from memployee where RESIGNDATE>='"+year+"-'+month+'-01' and RESIGNDATE<='"+year+"-'+month+'-31' and status='L') as 'num' from month_list"; sql12 = "select (select count(employeeid) from memployee where RESIGNDATE>='"+year+"-'+month+'-01' and RESIGNDATE<='"+year+"-'+month+'-31' and status='U') as 'num' from month_list"; sql13 = "select (select count(employeeid) from memployee where RESIGNDATE>='"+year+"-'+month+'-01' and RESIGNDATE<='"+year+"-'+month+'-31' and status='Z') as 'num' from month_list"; sql14 = "select (select count(employeeid) from memployee where APPROVE_DATE>='"+year+"-'+month+'-01' and APPROVE_DATE<='"+year+"-'+month+'-31' and status='A') as 'num' from month_list"; sql15 = "select dbo.getFullname(employeeid,'th') as 'fullname',month(startdate) as 'month' from memployee where year(startdate)='"+year+"' "; sql16 = "select dbo.getFullname(employeeid,'th') as 'fullname',month(resigndate) as 'month' from memployee where year(resigndate)='"+year+"' and status='Y'"; sql17 = "select dbo.getFullname(employeeid,'th') as 'fullname',month(resigndate) as 'month' from memployee where year(resigndate)='"+year+"' and status='X'"; sql18 = "select dbo.getFullname(employeeid,'th') as 'fullname',month(resigndate) as 'month' from memployee where year(resigndate)='"+year+"' and status='L'"; sql19 = "select dbo.getFullname(employeeid,'th') as 'fullname',month(resigndate) as 'month' from memployee where year(resigndate)='"+year+"' and status='U'"; sql20 = "select dbo.getFullname(employeeid,'th') as 'fullname',month(resigndate) as 'month' from memployee where year(resigndate)='"+year+"' and status='Z'"; } %> <%! public String genEmployeeList(UProfile up,String sql,String header){ String text_header =""; int large_size=1; StringBuffer sb = new StringBuffer(); Map<String,ArrayList> map = new HashMap<String,ArrayList>(); for(int i=1;i<=12;i++){map.put("month"+i,new ArrayList());} try{ DataSet ds = new DataSet(sql,up.get("dbname"),"MEMPLOYEE"); ds.initConnection(up); while(ds.next()){ for(int i=1;i<=12;i++){ ArrayList temp_Arr = map.get("month"+i); if(Integer.parseInt(ds.getString("month"))==i){ temp_Arr.add(ds.getString("fullname")); } map.put("month"+i,temp_Arr); } } ds.closeConnection(); }catch(Exception e){} for(int i=1;i<=12;i++){ if(map.get("month"+i).size()>large_size){ large_size = map.get("month"+i).size(); } } for(int i=0;i<large_size;i++){ if(i==0){text_header=header;}else{text_header="";} sb.append("<tr>"); sb.append("<td rowspan='1' colspan='1' class='report_detail'>"+text_header+"</td>"); for(int j=1;j<=12;j++){ String fullname =""; try{ fullname = map.get("month"+j).get(i).toString(); }catch(Exception e){} sb.append("<td rowspan='1' colspan='1' class='report_detail'>"+fullname+"</td>"); } sb.append("</tr>"); } return sb.toString(); } public String genNumOfEmp(UProfile up,String sql,String header){ CheckNull chk = new CheckNull(); StringBuffer sb = new StringBuffer(); sb.append("<tr>"); sb.append("<td rowspan='1' colspan='1' class='report_detail'>"+header+"</td>"); try{ DataSet ds = new DataSet(sql,up.get("dbname"),"MEMPLOYEE"); ds.initConnection(up); while(ds.next()){ sb.append("<td rowspan='1' colspan='1' class='report_detail'>"+chk.chkNullString(ds.getString("num"),"0")+"</td>"); } sb.append("</tr>"); ds.closeConnection(); }catch(Exception e){} return sb.toString(); } %> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <script language="javascript">setLang('<%=uprofile.get("lang")%>');</script> <script > </script> <style> .report_detail{ text-align:center; border: 1px solid black; width:200px; } .report_header{ text-align:center; border: 1px solid black; width:200px; } </style> </head> <body leftmargin="0" topmargin="0" marginwidth="0" marginheight="0"> <form name="cscform" method="post" action='?'> <input type="hidden" name="__cmd" value=""> <input type="hidden" name="__language" value ="<%=uprofile.get("lang")%>"> <input type="hidden" name="__calendar"> <div align="left"> <table cellpadding="0" cellspacing="0" style='width:100%;'> <tr align='center'> <td rowspan='2' colspan='1' class='report_header'>รายละเอียด</td> <td rowspan='1' colspan='12' class='report_header'>ประจำเดือน</td> </tr> <tr align='center'> <% String[] shortMonth = new String[12]; String[] thaiShortMonth = { "ม.ค.", "ก.พ.", "มี.ค.", "เม.ย.", "พ.ค.", "มิ.ย.", "ก.ค.", "ส.ค.", "ก.ย.", "ต.ค.", "พ.ย.", "ธ.ค." }; String[] engShortMonth = { "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec" }; if(uprofile.get("lang").equalsIgnoreCase("THA")){ shortMonth = thaiShortMonth; }else{ shortMonth = engShortMonth; } for(int i=0;i<=11;i++){ %> <td rowspan='1' colspan='1' class='report_header'><%=shortMonth[i]%></td> <%}%> </tr> <%=genNumOfEmp(uprofile,sql1,"จำนวนพนักงาน")%> <%=genNumOfEmp(uprofile,sql2,"จำนวนคนเข้าใหม่")%> <%=genNumOfEmp(uprofile,sql3,"จำนวนคนลาออก")%> <tr> <td rowspan='1' colspan='13' class='report_detail'></td> </tr> <%=genNumOfEmp(uprofile,sql4,"อายุงานน้อยกว่า 1 ปี")%> <%=genNumOfEmp(uprofile,sql5,"อายุงานมากกว่า 1 ปี")%> <%=genNumOfEmp(uprofile,sql6,"ลาป่วย")%> <%=genNumOfEmp(uprofile,sql7,"ลากิจ")%> <tr> <% String[] data8 = new String[12]; int i8 =0; DataSet ds8 = new DataSet(sql8,uprofile.get("dbname"),"MEMPLOYEE"); ds8.initConnection(uprofile); while(ds8.next()){ data8[i8] = ds8.getString("num"); i8++; } ds8.closeConnection(); // data8[] จะได้วันหยุดที่ไม่ตรงกับเสาร์อาทิตย์ %> <td rowspan='1' colspan='1' class='report_detail'>จำนวนวันทำงาน</td> <td rowspan='1' colspan='1' class='report_detail'><%=new CscCalendar(year+"-01-01").getWorkDayInM()-Integer.parseInt(data8[0])%></td> <td rowspan='1' colspan='1' class='report_detail'><%=new CscCalendar(year+"-02-01").getWorkDayInM()-Integer.parseInt(data8[1])%></td> <td rowspan='1' colspan='1' class='report_detail'><%=new CscCalendar(year+"-03-01").getWorkDayInM()-Integer.parseInt(data8[2])%></td> <td rowspan='1' colspan='1' class='report_detail'><%=new CscCalendar(year+"-04-01").getWorkDayInM()-Integer.parseInt(data8[3])%></td> <td rowspan='1' colspan='1' class='report_detail'><%=new CscCalendar(year+"-05-01").getWorkDayInM()-Integer.parseInt(data8[4])%></td> <td rowspan='1' colspan='1' class='report_detail'><%=new CscCalendar(year+"-06-01").getWorkDayInM()-Integer.parseInt(data8[5])%></td> <td rowspan='1' colspan='1' class='report_detail'><%=new CscCalendar(year+"-07-01").getWorkDayInM()-Integer.parseInt(data8[6])%></td> <td rowspan='1' colspan='1' class='report_detail'><%=new CscCalendar(year+"-08-01").getWorkDayInM()-Integer.parseInt(data8[7])%></td> <td rowspan='1' colspan='1' class='report_detail'><%=new CscCalendar(year+"-09-01").getWorkDayInM()-Integer.parseInt(data8[8])%></td> <td rowspan='1' colspan='1' class='report_detail'><%=new CscCalendar(year+"-10-01").getWorkDayInM()-Integer.parseInt(data8[9])%></td> <td rowspan='1' colspan='1' class='report_detail'><%=new CscCalendar(year+"-11-01").getWorkDayInM()-Integer.parseInt(data8[10])%></td> <td rowspan='1' colspan='1' class='report_detail'><%=new CscCalendar(year+"-12-01").getWorkDayInM()-Integer.parseInt(data8[11])%></td> </tr> <tr> <td rowspan='1' colspan='13' class='report_detail'></td> </tr> <tr> <%=genNumOfEmp(uprofile,sql9,"ลาออก")%> <%=genNumOfEmp(uprofile,sql10,"ปลดออก")%> <%=genNumOfEmp(uprofile,sql11,"เลิกจ้าง")%> <%=genNumOfEmp(uprofile,sql12,"เสียชีวิต")%> <%=genNumOfEmp(uprofile,sql13,"เกษียณอายุ")%> <%=genNumOfEmp(uprofile,sql14,"บรรจุเป็นพนักงาน")%> <tr> <td rowspan='1' colspan='13' class='report_detail'></td> </tr> <tr> <td rowspan='1' colspan='1' class='report_detail'>ณ งวดจ่ายเงินเดือน</td> <td rowspan='1' colspan='12' class='report_detail'></td> </tr> <%=genEmployeeList(uprofile,sql15,"รายชื่อพนักงานเข้าใหม่")%> <%=genEmployeeList(uprofile,sql16,"รายชื่อพนักงานลาออก")%> <%=genEmployeeList(uprofile,sql17,"รายชื่อพนักงานปลดออก")%> <%=genEmployeeList(uprofile,sql18,"รายชื่อพนักงานเลิกจ้าง")%> <%=genEmployeeList(uprofile,sql19,"รายชื่อพนักงานเสียชีวิต")%> <%=genEmployeeList(uprofile,sql20,"รายชื่อพนักงานเกษียณอายุ")%> </table> </div> </form> </body> </html>