<!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();
String cmd = chk.chkNullString(request.getParameter("__cmd"));
String filter = chk.chkNullString(request.getParameter("filter"));
int nowyear = Integer.parseInt(request.getParameter("nowyear"));
int afteryear = Integer.parseInt(request.getParameter("afteryear"));
%>
<html ng-app="myHR">
<head>
<title></title>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<!-- <link href="../CSS/TISCO.css" rel="stylesheet" type="text/css"> -->
<script language="JavaScript" src="../JS/SCREEN.js"></script>
<script src="../JS/angular-1.3.15/angular.js" type="text/javascript"></script>
<script src="../JS/ANGULAR/SCREEN_ANGULAR.js" type="text/javascript"></script>
<SCRIPT TYPE='text/JavaScript' SRC='../JS/DATEINPUT.js'></SCRIPT>
<SCRIPT TYPE='text/JavaScript' SRC='../JS/VIEWCALENDAR.js'></SCRIPT>
<script language="javascript">setLang('<%=uprofile.get("lang")%>');</script>
<script language="JavaScript" type="text/JavaScript">
function goHelp(helpName,inputName){
var helpReturn;
if(inputName=="__bu1" ){
helpReturn="__bu1:bu1id,__bu1_desc:tdesc";
}else if(inputName=="__bu2" ){
helpReturn="__bu2:bu2id,__bu2_desc:tdesc";
}else if(inputName=="__bu3" ){
helpReturn="__bu3:bu3id,__bu3_desc:tdesc";
}else if(inputName=="__bu4" ){
helpReturn="__bu4:bu4id,__bu4_desc:tdesc";
}else if(inputName=="__bu5" ){
helpReturn="__bu5:bu5id,__bu5_desc:tdesc";
}else if(inputName=="__employeeid" ){
helpReturn="__employeeid:employeeid,__employeeid_desc:fullname";
}
linkHelp_Return(helpName, helpReturn);
}
//-->
function back(unit){
with(window.opener.document.cscform){
locateId.value =unit;
}
window.close();
}
//-->
function GoExport(){
with(document.cscform){
// alert(__filter.value);
var param = "MANPOWER_REPORT_EXP.jsp?__start_date="+__start_date.value+"&__end_date="+__end_date.value+"&__bu1="+__bu1.value+"&__bu2="+__bu2.value+"&__bu3="+__bu3.value+"&__bu4="+__bu4.value+"&__bu5="+__bu5.value;
window.open(param);
}
}
function GoProcess(){
with( document.cscform){
__cmd.value = "search"
submit();
}
}
</script>
</head>
<style>
td.rowex {
background: #3399cc;
border: #cccccc;
color: #000000;
font-weight: bold;
height: 45px;
}
</style>
<%
String helpReturn="";
if(request.getParameter("__helpReturn")!=null){
helpReturn=request.getParameter("__helpReturn");
}
String fieldSearch="";
if(request.getParameter("__fieldSearch")!=null){
fieldSearch=request.getParameter("__fieldSearch");
}
%>
<body leftmargin="0" topmargin="0">
<form name="cscform" method="post" action="MANPOWER_REPORT.jsp">
<!----------BEGIN CSC INITIAL ZONE ------------------------>
<%//=screen.InitialVariable()%>
<!----------END CSC INITIAL ZONE -------------------------->
<input type="hidden" name="__cmd" value="">
<input type="hidden" name="__screen" value="MANPOWER_REPORT">
<input type="hidden" name="__help" value="">
<input type="hidden" name="__goPage" value="">
<input type="hidden" name="__language" value="<%=uprofile.get("lang")%>">
<input type="hidden" name="__orderBy" value="<%=request.getParameter("__orderBy")%>">
<input type="hidden" name="__helpReturn" value="<%=helpReturn%>">
<input type="hidden" name="__fieldSearch" value="<%=fieldSearch%>">
<input type="hidden" name="__helpName" value="<%=request.getParameter("__helpName")%>">
<input type="hidden" name="__pageCall" value="<%=request.getParameter("__screen")%>">
<input type="hidden" name="__fixCon" value="<%=request.getParameter("__fixCon")%>">
<input type="hidden" name="__export" value="">
<input type="hidden" name="__calendar" value="">
<input type="hidden" name="__filter" value="<%=request.getParameter("__filter")%>">
<script language="javascript">getInputFormatDate()</script>
<br>
<div align="center">
<table class="large" cellpadding="0" cellspacing="1" >
<tr>
<td colspan=2>
<table border="0" cellpadding="0" cellspacing="1" align="center" class="largest">
<tr>
<td colspan=2 class="blankspace"></td>
</tr>
<%
int i=1;
String bu1tdesc ="";
String bu2tdesc ="";
String bu3tdesc ="";
String bu4tdesc ="";
String bu1edesc ="";
String bu2edesc ="";
String bu3edesc ="";
String bu4edesc ="";
String remark = "";
int cur_man = 0;
int userman = 0;
int totalman = 0;
int lastcur_man = 0;
int sum_lastcur_man = 0;
int sum_cur_man = 0;
int sum_userman = 0;
int sum_totalman = 0;
StringBuffer sql = new StringBuffer();
sql.append("WITH t1 as ( ");
sql.append("select m.jobcodeid ");
sql.append(",SUBSTRING(m0.plan_startdate,1,4)years ");
sql.append(",m0.planid ,m0.plan_startdate ,m0.plan_enddate ");
sql.append(",j.bu1 bid1,b1.tdesc bu1tdesc ,b1.edesc bu1edesc");
sql.append(",j.bu2 bid2,b2.tdesc bu2tdesc ,b2.edesc bu2edesc");
sql.append(",j.bu3 bid3,b3.tdesc bu3tdesc ,b3.edesc bu3edesc");
sql.append(",j.bu4 bid4,b4.tdesc bu4tdesc ,b4.edesc bu4edesc");
sql.append(",SUM(CAST(m.currentman as int))currentman ");
sql.append(",SUM(CAST(m.usedman as int))usedman ");
sql.append(",SUM(CAST(m.totalman as int))totalman ");
sql.append(",m.remarks ");
sql.append("from mmanpower1 m ");
sql.append("left join mmanpower0 m0 on m.planid=m0.planid ");
sql.append("left join mjobcode j on m.jobcodeid=j.jobcodeid ");
sql.append("left join mbu1 b1 on j.bu1=b1.bu1id ");
sql.append("left join mbu2 b2 on j.bu2=b2.bu2id ");
sql.append("left join mbu3 b3 on j.bu3=b3.bu3id ");
sql.append("left join mbu4 b4 on j.bu4=b4.bu4id ");
sql.append("where SUBSTRING(m0.plan_startdate,1,4)='"+nowyear+"' ");
sql.append("group by m.jobcodeid,SUBSTRING(m0.plan_startdate,1,4) ");
sql.append(",j.bu1,b1.tdesc,b1.edesc,j.bu2,b2.tdesc,b2.edesc,j.bu3,b3.tdesc,b3.edesc,j.bu4,b4.tdesc,b4.edesc,m.remarks ");
sql.append(",m0.planid ,m0.plan_startdate ,m0.plan_enddate )");
sql.append(", t2 as ( ");
sql.append("select m.jobcodeid ");
sql.append(",SUBSTRING(m0.plan_startdate,1,4)years2 ");
sql.append(",m0.planid ,m0.plan_startdate ,m0.plan_enddate ");
sql.append(",j.bu1 bid1,b1.tdesc bu1desc ");
sql.append(",j.bu2 bid2,b2.tdesc bu2desc ");
sql.append(",j.bu3 bid3,b3.tdesc bu3desc ");
sql.append(",j.bu4 bid4,b4.tdesc bu4desc ");
sql.append(",SUM(CAST(m.currentman as int))currentman ");
sql.append(",SUM(CAST(m.usedman as int))usedman ");
sql.append(",SUM(CAST(m.totalman as int))totalman ");
sql.append(",m.remarks ");
sql.append("from mmanpower1 m ");
sql.append("left join mmanpower0 m0 on m.planid=m0.planid ");
sql.append("left join mjobcode j on m.jobcodeid=j.jobcodeid ");
sql.append("left join mbu1 b1 on j.bu1=b1.bu1id ");
sql.append("left join mbu2 b2 on j.bu2=b2.bu2id ");
sql.append("left join mbu3 b3 on j.bu3=b3.bu3id ");
sql.append("left join mbu4 b4 on j.bu4=b4.bu4id ");
sql.append("where SUBSTRING(m0.plan_startdate,1,4)='"+afteryear+"' ");
sql.append("group by m.jobcodeid,SUBSTRING(m0.plan_startdate,1,4),j.bu1,b1.tdesc,j.bu2,b2.tdesc,j.bu3,b3.tdesc,j.bu4,b4.tdesc,m.remarks,m0.planid,m0.plan_startdate ,m0.plan_enddate ) ");
sql.append("select t1.years as years,t1.planid ,t1.plan_startdate ,t1.plan_enddate ,t1.bu1edesc as e1,t1.bu2edesc as e2,t1.bu3edesc as e3,t1.bu4edesc as e4");
sql.append(",t1.bu1tdesc as t1,t1.bu2tdesc as t2,t1.bu3tdesc as t3,t1.bu4edesc as t4");
sql.append(",t2.currentman as lastcurrentman,t1.currentman as cur1,t1.usedman as userm,t1.totalman as total,t1.remarks as rem,t1.bid1,t1.bid2,t1.bid3,t1.bid4 ");
sql.append("from t1 left join t2 on t1.jobcodeid=t2.jobcodeid "+filter+" ORDER BY bid1,bid2,bid3,bid4 ");
// out.print(sql);
String dbName = uprofile.getDbName();
String tableName = "mmanpower1";
HashMap hm_bu1 = new HashMap();
HashMap hm_bu2 = new HashMap();
HashMap hm_bu3 = new HashMap();
HashMap hm_bu4 = new HashMap();
String bu1 = "";
String bu2 = "";
String bu3 = "";
String bu4 = "";
String years = "";
DataSet dsdate = new DataSet(sql.toString(), dbName, tableName );
dsdate.initConnection(uprofile);
if(dsdate.next()){
years = chk.chkNullString(dsdate.getString("years")) +"-01-01";
}
dsdate.closeConnection();
%>
<table height="" align="center" cellpadding="0" cellspacing="1" class="maxsize" border="0" width="100%">
<tr bgcolor="#3366CC">
<td colspan=2>
<table class="maxsize" border="1" cellpadding="0" cellspacing="1" cols=11 bgcolor="#FFFFFF">
<tr>
<td colspan="9"><div align="center"><b><font size="20"><%=uprofile.get("lang").equalsIgnoreCase("tha")?"อัตรากำลัง":"Manpower"%></font></b></div></td>
</tr>
<%
DbInquiry memployee = new InitialInquiry(uprofile).getDbInquiry("memployee");
memployee.setColumn("employeeid,companyid,bu1,bu2,bu3,bu4,bu5");
memployee.refresh();
DbRecord mbu = memployee.getCurrentDbRecord();
%>
<tr>
<td class="rowex" height="10%" width="18%"><div align="center"><b><%=mbu.getField("bu1").getDescription()%></b></div></td>
<td class="rowex" width="18%"><div align="center"><b><%=mbu.getField("bu2").getDescription()%></b></div></td>
<td class="rowex" width="17%"><div align="center"><b><%=mbu.getField("bu3").getDescription()%></b></div></td>
<td class="rowex" width="17%"><div align="center"><b><%=mbu.getField("bu4").getDescription()%></b></div></td>
<td class="rowex" width="5%"><div align="center"><b><%=uprofile.get("lang").equalsIgnoreCase("tha")?"อัตรากำลัง":"Manpower"%>
<%=new CscCalendar(years).getYear()-1%></b></div></td>
<td class="rowex" width="5%"><div align="center"><b><%=uprofile.get("lang").equalsIgnoreCase("tha")?"อัตรากำลังที่ต้องการ":"Manpower Need'"%>
<%=String.valueOf(new CscCalendar(years).getYear()).substring(2)%></b></div></td>
<td class="rowex" width="5%"><div align="center"><b><%=uprofile.get("lang").equalsIgnoreCase("tha")?"อัตรากำลังจริง":"Actual"%>
<%=new CscCalendar(years).getYear()%></b></div></td>
<td class="rowex" width="5%"><div align="center"><b><%=uprofile.get("lang").equalsIgnoreCase("tha")?"ตำแหน่งว่าง":"Vacancy"%></b></div></td>
<td class="rowex" width="10%" ><div align="center"><b><%=uprofile.get("lang").equalsIgnoreCase("tha")?"หมายเหตุ":"Remark"%></b></div></td>
</tr>
<%DataSet ds = new DataSet(sql.toString(), dbName, tableName );
ds.initConnection(uprofile);
while(ds.next()){
bu1 = chk.chkNullString(ds.getString("bid1"));
bu2 = bu1+","+chk.chkNullString(ds.getString("bid2"));
bu3 = bu2+","+chk.chkNullString(ds.getString("bid3"));
bu4 = bu3+","+chk.chkNullString(ds.getString("bid4"));
int row_bu1 = 1;
if(hm_bu1.containsKey(bu1)){
row_bu1 = (Integer)hm_bu1.get(bu1);
row_bu1++;
}
hm_bu1.put(bu1,row_bu1);
///////////////////////////
int row_bu2 = 1;
if(hm_bu2.containsKey(bu2)){
row_bu2 = (Integer)hm_bu2.get(bu2);
row_bu2++;
}
hm_bu2.put(bu2,row_bu2);
///////////////////////////
int row_bu3 = 1;
if(hm_bu3.containsKey(bu3)){
row_bu3 = (Integer)hm_bu3.get(bu3);
row_bu3++;
}
hm_bu3.put(bu3,row_bu3);
///////////////////////////
int row_bu4 = 1;
if(hm_bu4.containsKey(bu4)){
row_bu4 = (Integer)hm_bu4.get(bu4);
row_bu4++;
}
hm_bu4.put(bu4,row_bu4);
///////////////////////////
}
ds.closeConnection();
String d1=null;
String d2=null;
String d3=null;
String d4=null;
ds.initConnection(uprofile);
while(ds.next()){
bu1 = chk.chkNullString(ds.getString("bid1"));
bu2 = bu1+","+chk.chkNullString(ds.getString("bid2"));
bu3 = bu2+","+chk.chkNullString(ds.getString("bid3"));
bu4 = bu3+","+chk.chkNullString(ds.getString("bid4"));
bu1edesc = chk.chkNullString(ds.getString("e1"));
bu2edesc = chk.chkNullString(ds.getString("e2"));
bu3edesc = chk.chkNullString(ds.getString("e3"));
bu4edesc = chk.chkNullString(ds.getString("e4"));
bu1tdesc = chk.chkNullString(ds.getString("t1"));
bu2tdesc = chk.chkNullString(ds.getString("t2"));
bu3tdesc = chk.chkNullString(ds.getString("t3"));
bu4tdesc = chk.chkNullString(ds.getString("t4"));
cur_man = Integer.parseInt(chk.chkNullString(ds.getString("cur1"),"0"));
lastcur_man = Integer.parseInt(chk.chkNullString(ds.getString("lastcurrentman"),"0"));
userman = Integer.parseInt(chk.chkNullString(ds.getString("userm"),"0"));
totalman = Integer.parseInt(chk.chkNullString(ds.getString("total"),"0"));
remark = chk.chkNullString(ds.getString("rem"));
%>
<tr>
<%
if(!bu1.equals(d1)){
%>
<td align="top" nowrap rowspan="<%=hm_bu1.get(bu1)%>" style="vertical-align: text-top;"><div>
<%=uprofile.get("lang").equalsIgnoreCase("tha")?bu1tdesc:bu1edesc%></div></td>
<%
}
if(!bu2.equals(d2)){
%>
<td align="top" nowrap rowspan="<%=hm_bu2.get(bu2)%>" style="vertical-align: text-top;"><div>
<%=uprofile.get("lang").equalsIgnoreCase("tha")?bu2tdesc:bu2edesc%></div></td>
<%
}
if(!bu3.equals(d3)){
%>
<td align="top" nowrap rowspan="<%=hm_bu3.get(bu3)%>" style="vertical-align: text-top;"><div>
<%=uprofile.get("lang").equalsIgnoreCase("tha")?bu3tdesc:bu3edesc%></div></td>
<%
}
if(!bu4.equals(d4)){
%>
<td align="top" nowrap rowspan="<%=hm_bu4.get(bu4)%>" style="vertical-align: text-top;"><div>
<%=uprofile.get("lang").equalsIgnoreCase("tha")?bu4tdesc:bu4edesc%></div></td>
<%
}
%>
<td nowrap ><div align="center"><%=(lastcur_man != 0)?lastcur_man:""%></div></td>
<td nowrap ><div align="center"><%=cur_man%></div></td>
<td nowrap ><div align="center"><%=userman%></div></td>
<td nowrap ><div align="center"><%=totalman%></div></td>
<td nowrap ><div align="center"><%=remark%></div></td>
</tr>
<%
d1 = bu1;
d2 = bu2;
d3 = bu3;
d4 = bu4;
sum_lastcur_man += lastcur_man;
sum_cur_man += cur_man;
sum_userman += userman;
sum_totalman += totalman;
}
ds.closeConnection();
String sum = "";
%>
<tr HSIDES="5">
<td nowrap colspan="4"> <b><%=uprofile.get("lang").equalsIgnoreCase("tha")?"รวม":"Total"%></b></td>
<td nowrap ><div align="center"><b><%=sum_lastcur_man%></b></div></td>
<td nowrap ><div align="center"><b><%=sum_cur_man%></b></div></td>
<td nowrap ><div align="center"><b><%=sum_userman%></b></div></td>
<td nowrap ><div align="center"><b><%=sum_totalman%></b></div></td>
<td nowrap ><div align="center"><%=remark%></div></td>
</tr>
</table>
</td>
</tr>
</table></td></tr>
</td>
</tr>
</table>
</td></tr>
<tr>
<td colspan=2 class="blankspace"></td>
</tr>
</table>
</form>
</body>
</html>