Payroll_Report_intelligence001_EXPORT.jsp

<%@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>