<!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.*,com.csc.library.components.*"%>
<%@page import="java.text.DecimalFormat,com.csc.library.database.*,com.csc.library.timeattendance.*,com.csc.library.databean.*"%>
<%@page import="java.util.*,java.io.*,java.sql.*"%>
<jsp:useBean id="TAR11128" class="com.csc.library.system.Task" scope="page"/>
<%
response.setHeader("Cache-Control","must-revalidate");
response.setContentType("application/vnd.ms-excel; UTF-8");
response.setHeader("Content-Disposition", "inline; filename=" + "export.xls");
TAR11128.setChannel(request, response);
HelpEntry screen = (HelpEntry) TAR11128.process("HelpEntry", "MEMPLOYEEHELP");
CheckNull chk = new CheckNull();
CscCalendar now = new CscCalendar();
String cmd = chk.chkNullString(request.getParameter("__cmd"));
String branch = chk.chkNullString(request.getParameter("__branch"));
String branch_desc = chk.chkNullString(request.getParameter("MBRANCH@TDESC"));
String workarea = chk.chkNullString(request.getParameter("__workarea"));
String workarea_desc = chk.chkNullString(request.getParameter("MWORKAREA@TDESC"));
String status = chk.chkNullString(request.getParameter("__status"));
String date = chk.chkNullString(request.getParameter("__date"),now.getDDMMYYYY());
String groupby = chk.chkNullString(request.getParameter("__groupby"),"bu1");
String view = chk.chkNullString(request.getParameter("__view"));
if(branch.equals("")){
branch_desc = "";
}
CscCalendar cal_date = new CscCalendar(date);
String select_date = cal_date.getYYYYMMDD();
String filter = "1=1";
String filteremp = " and employeeid in (select employeeid from ttimetemp where swipetype = '0' and swipedate = '"+select_date+"')";
if( !select_date.equals("") ){
filter += " and resigndate >= '"+select_date+"'";
}
if( !branch.equals("") ){
filter += " and branch = '"+branch+"'";
}
if( !workarea.equals("") ){
filter += " and workarea = '"+workarea+"'";
}
if( !status.equals("") ){
filter += " and status = '"+status+"'";
}
%>
<html>
<head>
<title></title>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<!-- <link href="../CSS/TISCO.css" rel="stylesheet" type="text/css">
<link href="../CSS/bootstrap.css" rel="stylesheet"> -->
<script src="../JS/jquery-1.11.3.min.js"></script>
<script src="../JS/bootstrap.min.js"></script>
<script language="JavaScript" src="../JS/SCREEN.js"></script>
<SCRIPT TYPE='text/JavaScript' SRC='../JS/DATEINPUT.js'></SCRIPT>
<SCRIPT TYPE='text/JavaScript' SRC='../JS/VIEWCALENDAR.js'></SCRIPT>
<script language="javascript">setLang('<%=screen.getUProfile().get("lang")%>');</script>
<style>
.table-condensed > tbody > tr > td {
padding: 4px;
border: 0px solid #ddd;
}
img[height="20"] {
padding: 0px;
}
.table-bordered th {
color: #ffffff;
text-align: center;
vertical-align: middle;
background-color: #336699;
}
.eiei {
overflow: scroll;
height: 100%;
}
</style>
</head>
<body leftmargin="0" topmargin="0">
<form name="cscform" method="post">
<input type="hidden" name="__language" value="<%=screen.getUProfile().get("lang")%>%>">
<input type="hidden" name="__fixCon" value="">
<input type="hidden" name="__cmd" value="">
<input type="hidden" name="__view" value="">
<input type="hidden" name="__screen" value="TAR11127">
<input type="hidden" name="__goPage" value ="">
<input type="hidden" name="__employeeid" value="">
<input type="hidden" name="__companyid" value="<%=screen.getUProfile().get("companyid")%>">
<input type="hidden" name="__orderBy" value="">
<input type="hidden" name="__empview" value="">
<input type="hidden" name="__calendar" value="">
<input type="hidden" name="__filter" value="">
<script language="javascript">getInputFormatDate();</script>
<%
DbInquiry inq = new InitialInquiry(screen.getUProfile()).getDbInquiry("memployment_type");
inq.setColumn("*");
inq.setOrderBy("codeid");
inq.refresh();
inq.referLangOn();
int col_num = inq.recCount();
String coltype = "";
HashMap<String,String> hashmap_type = new HashMap<String,String>();
Map<String, String> linkedHashMap_coltype = new LinkedHashMap<String, String>();
while(inq.next()){
hashmap_type.put(inq.getString("codeid"), inq.getString("tdesc"));
}
for(int i=1; i<=col_num; i++){
if(!chk.chkNullString(request.getParameter("__col_"+i)).equals("")){
linkedHashMap_coltype.put(String.valueOf(i),chk.chkNullString(request.getParameter("__col_"+i)));
coltype = chkdata(coltype) + chk.chkNullString(request.getParameter("__col_"+i));
}
}
int count_column = linkedHashMap_coltype.size();
%>
<div align="left"><b><u>Daily Attendance <%=select_date%></u><b></div>
<table class="table table-bordered maxsize">
<thead>
<tr>
<th style="vertical-align: middle;" rowspan="2"><%=screen.getLabel(groupby)%></th>
<th style="vertical-align: middle;" colspan="<%=count_column+1%>"><%=screen.getUProfile().get("lang").equalsIgnoreCase("tha")?"อัตราพนักงานที่มีอยู่จริง":"Working Plan"%></th>
<th style="vertical-align: middle;" colspan="<%=count_column+1%>"><%=screen.getUProfile().get("lang").equalsIgnoreCase("tha")?"อัตราพนักงานที่มาทำงานจริง":"Actual Working"%></th>
<th style="vertical-align: middle;" colspan="<%=count_column+1%>"><%=screen.getUProfile().get("lang").equalsIgnoreCase("tha")?"ผลต่าง":"Result Difference"%></th>
</tr>
<tr>
<%
for(int hcol=1; hcol<=3; hcol++){
for (String key : linkedHashMap_coltype.keySet()) {
%>
<th style="vertical-align: middle;"><%=hashmap_type.get(linkedHashMap_coltype.get(key)) %></th>
<%
}
%>
<th style="vertical-align: middle;"><%=screen.getUProfile().get("lang").equalsIgnoreCase("tha")?"รวม":"Total"%></th>
<%
}
%>
</tr>
</thead>
<tbody>
<%
String tablegroup = "m"+groupby;
String fieldid = groupby+"id";
int[] temp_all = new int[count_column+1];
int[] temp_true = new int[count_column+1];
int[] temp_dif = new int[count_column+1];
DbInquiry inqgroup = new InitialInquiry(screen.getUProfile()).getDbInquiry(tablegroup);
inqgroup.setColumn("*");
inqgroup.setFilter(fieldid+" in (select "+groupby+" from memployee where "+filter+" and emp_type in ('"+coltype+"'))");
inqgroup.setOrderBy(fieldid);
inqgroup.refresh();
inqgroup.referLangOn();
while(inqgroup.next()){
%>
<tr>
<td align="left"><%=inqgroup.getString(fieldid)+" : "+inqgroup.getString("tdesc")%></td>
<%
int[] emp_all = new int[count_column+1];
int[] emp_true = new int[count_column+1];
for(int dcol=1; dcol<=3; dcol++){
String mainsql = "select count(*) as cou from memployee where "+filter+" and "+groupby+" = '"+inqgroup.getString(fieldid)+"'";
int total = 0;
int colcount = 1;
for (String key : linkedHashMap_coltype.keySet()) {
String dcol_str = linkedHashMap_coltype.get(key);
int data = 0;
if(dcol == 1){
String round_sql = " and emp_type = '"+dcol_str+"'";
data = getCountEmp(mainsql+round_sql,screen.getUProfile());
emp_all[colcount] = data;
temp_all[colcount] += data;
} else if(dcol == 2){
String round_sql = " and emp_type = '"+dcol_str+"'"+filteremp;
data = getCountEmp(mainsql+round_sql,screen.getUProfile());
emp_true[colcount] = data;
temp_true[colcount] += data;
} else if(dcol == 3){
data = emp_true[colcount] - emp_all[colcount];
temp_dif[colcount] += data;
}
total += data;
colcount++;
%>
<td align="center"><%=data%></td>
<%
}
%>
<td align="center"><%=total%></td>
<%
}
%>
</tr>
<%
}
%>
<tr>
<td align="right"><%=screen.getUProfile().get("lang").equalsIgnoreCase("tha")?"รวม":"Total"%></td>
<%
for(int bcol=1; bcol<=3; bcol++){
int total = 0;
int tcolcount = 1;
for (String key : linkedHashMap_coltype.keySet()) {
int data = 0;
if(bcol == 1){
data = temp_all[tcolcount];
} else if(bcol == 2){
data = temp_true[tcolcount];
} else if(bcol == 3){
data = temp_dif[tcolcount];
}
total += data;
tcolcount++;
%>
<td align="center"><%=data%></td>
<%
}
%>
<td align="center"><%=total%></td>
<%
}
%>
</tr>
</tbody>
</table>
</form>
</body>
</html>
<%!
private int getCountEmp(String sql, UProfile up){
int count = 0;
try{
DataSet ds = new DataSet(sql,up.get("dbname"),"MEMPLOYEE");
ds.initConnection(up);
ds.next();
count = new CheckNull().chkNullStringToInt(ds.getString("cou"));
ds.closeConnection();
} catch(Exception e) {
e.printStackTrace();
}
return count;
}
private String chkdata(String str){
if(!str.equals("")){
str += "','";
}
return str;
}
%>