<!DOCTYPE html>
<%@page contentType="text/html; charset=UTF-8"%>
<%@ page import="com.csc.library.entry.HelpEntry,com.csc.library.system.*,com.csc.library.utilities.*,com.csc.library.session.*"%>
<%@ page import="com.csc.library.components.*,com.csc.library.report.*,java.util.*,java.text.*,com.csc.library.database.*,java.text.*"%>
<jsp:useBean id="TURNOVER_EXCEL" scope="page" class="com.csc.library.system.Task" />
<html ng-app="myHR">
<%
CheckNull chk = new CheckNull();
CscCalendar cs = new CscCalendar();
TURNOVER_EXCEL.setChannel(request ,response);
HelpEntry screen = (HelpEntry) TURNOVER_EXCEL.process("HelpEntry", "TURNOVER_EXCEL");
screen.setMaxLine(1000000);
DecimalFormat df = new DecimalFormat("###0.00");
String filter = "";
String filter2 = "";
String cmd = chk.chkNullString(TURNOVER_EXCEL.getParameter("__cmd"),"");
String group_by = chk.chkNullString(TURNOVER_EXCEL.getParameter("__groupby_bu"));
String month1 = chk.chkNullString(TURNOVER_EXCEL.getParameter("__month1"));
String year1 = chk.chkNullString(TURNOVER_EXCEL.getParameter("__year1"), cs.getYear()+"");
String month2 = chk.chkNullString(TURNOVER_EXCEL.getParameter("__month2"));
String year2 = chk.chkNullString(TURNOVER_EXCEL.getParameter("__year2"), cs.getYear()+"");
String branch = chk.chkNullString(TURNOVER_EXCEL.getParameter("__branch"),"");
String branchdesc = chk.chkNullString(TURNOVER_EXCEL.getParameter("__branchdesc"),"");
if(!branch.equals("")){
filter = "branch = '"+branch+"'";
filter2 = "and branch = '"+branch+"'";
}
screen.getInquiry().setFilter(filter);
screen.getInquiry().setGroupBy(group_by);
screen.getInquiry().setOrderBy(group_by);
screen.referLangOn();
screen.getInquiry().refresh();
//screen.nextRec();
%>
<head>
<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" type="text/css">
<link href="../CSS/bootstrap_csc_backend.css" rel="stylesheet" type="text/css">
<script type="text/javascript" src="../JS/jquery-2.1.3.min.js"></script>
<script type='text/javascript' src="../JS/bootstrap.js"></script>
<script src="../JS/jquery-1.7.2.js"></script>
<script src="../JS/SCREEN.js"></script>
<script src="../JS/angular-1.3.15/angular.js"></script>
<script type='text/JavaScript' SRC='../JS/ANGULAR/SCREEN_ANGULAR.js'></script>
<script type="text/javascript" src="/hr/dwr/interface/CscCalendar.js"></script>
<script type='text/javascript' src='../JS/ICONSMENU.js'></script>
<script >setLang('<%=screen.getUProfile().get("lang")%>');</script>
<script src="../JS/VIEWCALENDAR.js"></script>
<script src='../JS/ICONSMENU.js'></script>
<script src='../JS/HOTKEY.js'></script>
<script src="../JS/DATEINPUT.js"></script>
<script src="../JS/CscBase.js"></script>
<script src="../JS/CscCalendar.js"></script>
<script>getTitleName();</script>
<script TYPE='text/JavaScript' SRC='../JS/CscCalendarV3.js'></script>
<script type="text/JavaScript">
function goHelp(helpName,inputName){
with(document.cscform){
var helpReturn;
var fixcon;
var lang = '<%=screen.getUProfile().get("lang")%>';
if(inputName=="__branch" ){
helpReturn="__branch:branchid,__branchdesc:tdesc";
}
linkHelp_Return(helpName,helpReturn);
}
}
function goHelpBu(helpName,inputName){
var helpReturn;
var fixcon;
if(inputName=="__job" ){
helpReturn="__bu1:bu1,__bu2:bu2,__bu3:bu3,__bu4:bu4,__bu5:bu5,__bu1_desc,__bu2_desc,__bu3_desc,__bu4_desc,__bu5_desc";
}else if(inputName=="__bu1" ){
helpReturn="__bu1:bu1id,__bu1_desc:tdesc";
fixcon = "bu1 in(select bu1 from tvacation_accrual)";
}else if(inputName=="__bu2" ){
helpReturn="__bu2:bu2id,__bu2_desc:tdesc";
fixcon = "bu2 in(select bu2 from tvacation_accrual)";
}else if(inputName=="__bu3" ){
helpReturn="__bu3:bu3id,__bu3_desc:tdesc";
fixcon = "bu3 in(select bu3 from tvacation_accrual)";
}else if(inputName=="__bu4" ){
helpReturn="__bu4:bu4id,__bu4_desc:tdesc";
fixcon = "bu4 in(select bu4 from tvacation_accrual)";
}else if(inputName=="__bu5" ){
helpReturn="__bu5:bu5id,__bu5_desc:tdesc";
fixcon = "bu5 in(select bu5 from tvacation_accrual)";
}
linkHelp_Return(helpName,helpReturn);
}
function GoSearch(){
with(document.cscform){
__cmd.value = 'search';
submit();
}
}
var tableToExcel = (function() {
with(document.cscform){
var param = "TURNOVER_EXCEL_EXPORT.jsp?__cmd=search&__groupby_bu="+__groupby_bu.value+"&__month1="+__month1.value+"&__month2="+__month2.value+"&__year1="+__year1.value+"&__year2="+__year2.value+"&__branch="+__branch.value;
window.open(param);
}
});
</script>
</head>
<body leftmargin="0" topmargin="0" marginwidth="0" marginheight="0">
<form name="cscform" method="post" action ="TURNOVER_EXCEL.jsp" >
<%=screen.InitialVariable()%>
<input type="hidden" name="__cmd" value="<%=TURNOVER_EXCEL.getParameter("__cmd")%>">
<input type="hidden" name="__screen" value="TURNOVER_EXCEL">
<input type="hidden" name="__help" value="">
<input type="hidden" name="__calendar" value="">
<script language="javascript">getInputFormatDate();</script>
<input type="hidden" name="__companyid" value="<%=screen.getUProfile().get("companyid")%>">
<input type="hidden" name="__filterMemployee" value="companyid='<%=screen.getUProfile().get("companyid")%>'">
<input type="hidden" name="__referPage" value="">
<input type="hidden" name="__temp" value="">
<input type="hidden" name="__curDate" value="<%=new CscCalendar().getDate()%>">
<input type="hidden" name="__confirm" value="s">
<input type="hidden" name="__fixCon" value="">
<input type="hidden" name="__employeeid" value="<%=screen.getUProfile().get("employeeid")%>">
<input type="hidden" name="__language" value="<%=screen.getUProfile().get("lang")%>">
<input type="hidden" name="__eventgrp_list" value="">
<input type="hidden" name="__period_list" value="">
<div align="center">
<table height="" align="center" cellpadding="0" cellspacing="0" border="1" class="largest">
<tr bgcolor="#3366CC">
<td height="20" class="header" colspan="4">Turn Over,<span swlang code='SW011656'></span></td>
</tr>
<tr>
<td class="blankspace" colspan="4"></td>
</tr>
<tr>
<td align="right"><span swlang code="SW000314"> branch</span></td>
<td colspan="3">
<input type="text" name="__branch" size="5" maxlength="5" value="<%=branch%>" onkeypress="chkSpecialKey(event);" onkeyup="sysIsEdit();" onblur="trimValue(this);" onfocus="this.select();" alt="Company ID" onmousemver="toolTip('branch')" onmouseout="toolTip()">
<a href="javascript:goHelp('MBRANCHHELP','__branch');" onmouseout="MM_swapImgRestore()" onmouseover="MM_swapImage('__branch_image','','../IMAGES/BUTTON/SWAP/SEARCH_SWAP20.gif',1)"><img src="http://localhost:8082/hr/IMAGES/BUTTON/MAIN/SEARCH20.gif" name="__branch_image" width="20" height="20" border="0" align="absmiddle"></a>
<input type="text" name="__branchdesc" size="50" maxlength="100" value="<%=branchdesc%>" onkeypress="chkSpecialStr(event);" onkeyup="sysIsEdit();" onblur="trimValue(this);" onfocus="this.select();" alt="Desc. (Thai)" onmousemver="toolTip('Desc. (Thai)')" onmouseout="toolTip()">
</td>
</tr>
<tr>
<td align="right"><span swlang code="SW003560"> Period Month</span></td>
<td colspan="3">
<!-- <span>month</span> -->
<select name="__month1">
<%
for(int i=1;i<=12;i++){
%>
<option value="<%=i%>" <%=(month1.equals(i+""))?"selected":""%>><%=i%></option>
<%
}
%>
</select>
<!-- <span>year</span> --><span swlang code="SW000717">Year</span><input name="__year1" size="5" value="<%=year1%>">
-
<!-- <span>month</span> --><span swlang code="SW000800">Month</span>
<select name="__month2">
<%
for(int i=1;i<=12;i++){
%>
<option value="<%=i%>" <%=(month2.equals(i+""))?"selected":""%>><%=i%></option>
<%
}
%>
</select>
<!-- <span>year</span> --><span swlang code="SW000717">Year</span><input name="__year2" size="5" value="<%=year2%>">
</td>
</tr>
<tr>
<%
ListBox listbox1 = new ListBox();
listbox1.setUProfile(screen.getUProfile());
listbox1.setName("groupby_bu");
listbox1.setValidCode("GROUP_BY_BU");
listbox1.setChecked(group_by);
%>
<td><div align="right" swlang code='SW000122'>Group By</div></td>
<td colspan='3'><%=listbox1.process()%></td>
</tr>
<tr>
<td class="buttonline" colspan="7"><div align="center">
<button type="button" class="btn btn-primary width100" swlang code="SW003537" onClick="GoSearch();">SEARCH</button>
<button type="button" class="btn btn-primary width100" swlang code="SW013178" onClick="tableToExcel('export_table', 'Table');">BACK</button>
</div>
</td>
</tr>
</table>
<table class="largest" id="export_table">
<style>
td {
mso-number-format:\@;
}
.turnover{
border:1px solid black;
background:#efefef;
text-align:right;
}
.turnover_rate{
border:1px solid black;
background:#efefef;
text-align:right;
}
.bu_list{
border:1px solid black;
background:#D3EFFF;
}
.turnover_head{
border:1px solid black;
color:black;
background:#3399cc;
}
.turnover_rate_head{
border:1px solid black;
color:black;
background:#3399cc;
}
.bu_list_head{
border:1px solid black;
color:black;
background:#3399cc;
}
</style>
<%
if(cmd.equals("search")){
CscCalendar cs1 = new CscCalendar(year1+"-"+month1+"-1");
CscCalendar cs2 = new CscCalendar(year2+"-"+month2+"-1");
ArrayList total_month = new ArrayList();
CscCalendar cs1_clone = cs1;
CscCalendar cs2_clone = cs2;
if(cs1_clone.beforeDate(cs2_clone)){
while(cs1_clone.beforeEqualsDate(cs2_clone)){
total_month.add(cs1_clone.getYYYYMMDD());
cs1_clone.incMonth(1);
}
}
%>
<tr>
<td align="center" rowspan="2" width="35%" class="bu_list_head"><strong>BU</strong></td>
<td class="turnover_head" colspan="<%=total_month.size()%>" style="text-align:center;"><strong>Turnover</strong></td>
<td class="turnover_rate_head" colspan="<%=total_month.size()%>" style="text-align:center;"><strong>Turnover rate</strong></td>
</tr>
<tr>
<%
for(int i=0;i<total_month.size();i++){
String this_month = total_month.get(i).toString();
String month_head = new CscCalendar(this_month).getEngShortMonth()+" "+this_month.substring(0,4);
%>
<td class="turnover_head" style="text-align:center;"><strong><%=month_head%></strong></td>
<%
}
%>
<%
for(int i=0;i<total_month.size();i++){
String this_month = total_month.get(i).toString();
String month_head = new CscCalendar(this_month).getEngShortMonth()+" "+this_month.substring(0,4);
%>
<td class="turnover_rate_head" style="text-align:center;"><strong><%=month_head%></strong></td>
<%
}
%>
</tr>
<%
while(screen.getInquiry().next() ){ // While :::::::
String text_group_by = "";
if(group_by.equalsIgnoreCase("BRANCH")){
text_group_by = screen.getInquiry().getChild("mbranch").getString("tdesc");
}else if(group_by.equalsIgnoreCase("BU1")){
text_group_by = screen.getInquiry().getChild("mbu1").getString("tdesc")+","+screen.getInquiry().getChild("mbranch").getString("tdesc");
}else if(group_by.equalsIgnoreCase("BU2")){
text_group_by = screen.getInquiry().getChild("mbu2").getString("tdesc")+","+screen.getInquiry().getChild("mbu1").getString("tdesc")+","+screen.getInquiry().getChild("mbranch").getString("tdesc");
}else if(group_by.equalsIgnoreCase("BU3")){
text_group_by = screen.getInquiry().getChild("mbu3").getString("tdesc")+","+screen.getInquiry().getChild("mbu2").getString("tdesc")+","+screen.getInquiry().getChild("mbu1").getString("tdesc")+","+screen.getInquiry().getChild("mbranch").getString("tdesc");
}else if(group_by.equalsIgnoreCase("BU4")){
text_group_by = screen.getInquiry().getChild("mbu4").getString("tdesc")+","+screen.getInquiry().getChild("mbu3").getString("tdesc")+","+screen.getInquiry().getChild("mbu2").getString("tdesc")+","+screen.getInquiry().getChild("mbu1").getString("tdesc")+","+screen.getInquiry().getChild("mbranch").getString("tdesc");
}else if(group_by.equalsIgnoreCase("BU5")){
text_group_by = screen.getInquiry().getChild("mbu5").getString("tdesc")+","+screen.getInquiry().getChild("mbu4").getString("tdesc")+","+screen.getInquiry().getChild("mbu3").getString("tdesc")+","+screen.getInquiry().getChild("mbu2").getString("tdesc")+","+screen.getInquiry().getChild("mbu1").getString("tdesc")+","+screen.getInquiry().getChild("mbranch").getString("tdesc");
}
%>
<tr>
<td class="bu_list"><%=text_group_by%></td>
<%
ArrayList turnover = new ArrayList();
for(int i=0;i<total_month.size();i++){// for turnover
String this_month = total_month.get(i).toString();
String query = " select count(employeeid) from memployee where resigndate like '"+this_month.substring(0, 8)+"%' and "+group_by+" ='"+screen.getInquiry().getString(group_by)+"' ";
DataSet ds = new DataSet(query ,screen.getUProfile().get("dbname"), "MEMPLOYEE");
ds.initConnection(screen.getUProfile());
String data = "";
if(ds.next()){
data = ds.getString(0);
turnover.add(data);
}else{
turnover.add("0");
}
ds.closeConnection();
%>
<td class="turnover"><%=(data.equals("0"))?"":data%></td>
<%
}// End for turnover
%>
<%
for(int i=0;i<total_month.size();i++){// for turnover rate
String this_month = total_month.get(i).toString();
String data = "";
if(!turnover.get(i).toString().equals("0")){
String query = " select count(employeeid) from memployee "+
" where startdate <= '"+this_month.substring(0, 8)+"31' "+
" and resigndate >'"+this_month.substring(0, 8)+"-31' and "+group_by+" ='"+screen.getInquiry().getString(group_by)+"' ";
DataSet ds = new DataSet(query ,screen.getUProfile().get("dbname"), "MEMPLOYEE");
ds.initConnection(screen.getUProfile());
if(ds.next()){
if(!ds.getString(0).equals("0")){
Double turnover_emp = Double.parseDouble(turnover.get(i).toString());
Double total_emp = Double.parseDouble(ds.getString(0));
Double result = (turnover_emp/total_emp)*100;
data = df.format(result)+"%";
}
}
ds.closeConnection();
}
%>
<td class="turnover_rate"><%=data%></td>
<%
}// End for turnover rate
%>
</tr>
<%
}// End screen.getInquiry().next()
%>
<tr>
<td class="bu_list" style="text-align:center;"><strong>Total</strong></td>
<%
ArrayList sum_turnover = new ArrayList();
for(int i=0;i<total_month.size();i++){// for sum turnover
String this_month = total_month.get(i).toString();
String query = " select count(employeeid) from memployee where resigndate like '"+this_month.substring(0, 8)+"%'"+filter2;
DataSet ds = new DataSet(query ,screen.getUProfile().get("dbname"), "MEMPLOYEE");
ds.initConnection(screen.getUProfile());
String data = "";
if(ds.next()){
data = ds.getString(0);
sum_turnover.add(data);
}else{
sum_turnover.add("0");
}
ds.closeConnection();
%>
<td class="turnover" style="text-align:right;"><strong><%=(data.equals("0"))?"":data%></strong></td>
<%
}// End for sum turnover
%>
<%
for(int i=0;i<total_month.size();i++){// for sum turnover rate
String this_month = total_month.get(i).toString();
String data = "";
if(!sum_turnover.get(i).toString().equals("0")){
String query = " select count(employeeid) from memployee "+
" where startdate <= '"+this_month.substring(0, 8)+"31' "+
" and resigndate >'"+this_month.substring(0, 8)+"-31' "+filter2;
DataSet ds = new DataSet(query ,screen.getUProfile().get("dbname"), "MEMPLOYEE");
ds.initConnection(screen.getUProfile());
if(ds.next()){
if(!ds.getString(0).equals("0")){
Double turnover_emp = Double.parseDouble(sum_turnover.get(i).toString());
Double total_emp = Double.parseDouble(ds.getString(0));
Double result = (turnover_emp/total_emp)*100;
data = df.format(result)+"%";
}
}
ds.closeConnection();
}
%>
<td class="turnover_rate"><strong><%=data%></strong></td>
<%
}// End for sum turnover rate
%>
</tr>
<%
}// End cmd.equals("search")
%>
<tr>
<td class="bottom"></td>
</tr>
</table>
</div>
</form>
</body>
</html>