<!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.*,com.csc.library.components.*"%>
<%@page import="java.util.*,java.io.*"%>
<jsp:useBean id="PayrollReconcile" class="com.csc.library.system.Task" scope="page" />
<%
response.setContentType("application/vnd.ms-excel; UTF-8");
response.setHeader("Content-Disposition", "inline; filename=" + "export.xls");
PayrollReconcile.setChannel(request, response);
HelpEntry screen = (HelpEntry) PayrollReconcile.process("HelpEntry","PRR_Payrollreconcile");
CheckNull chk = new CheckNull();
String dbName = screen.getUProfile().getDbName();
String tableName1 = "payroll_group";
LinkedHashMap<String,String> getPGroupFormula = new LinkedHashMap<String,String>();
LinkedHashMap<String,String> getPGroupCaption = new LinkedHashMap<String,String>();
ListBox listPeriodGroup = new ListBox();
listPeriodGroup.setUProfile(screen.getUProfile());
listPeriodGroup.setName("group_option");
listPeriodGroup.setValidCode("GROUP_BY_VAC");
listPeriodGroup.setDisable("false");
listPeriodGroup.setChecked(chk.chkNullString(PayrollReconcile.getParameter("group_option"),"Bussiness Unit 1"));
String getOrderBy = chk.chkNullString(request.getParameter("__getOrderBy"));
String reconcileid = chk.chkNullString(request.getParameter("__reconcileid"));
String payrollGroup = chk.chkNullString(request.getParameter("__prgcode"));
String language = screen.getUProfile().get("lang");
%>
<html ng-app="myHR">
<title></title>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<script language="JavaScript" src="../JS/SCREEN.js"></script>
<script language="javascript">setLang('<%=screen.getUProfile().get("lang")%>');</script>
<script src="../JS/angular-1.3.15/angular.js"></script>
<script type='text/JavaScript' SRC='../JS/ANGULAR/SWAPLANG.js'></script>
<SCRIPT TYPE='text/JavaScript' SRC='../TA/JS/TA_SCREEN.js'></SCRIPT>
<!-- Call DWR -->
<script type='text/javascript' src='/hr/dwr/interface/CalculateReconcile.js'></script>
<script type='text/javascript' src='/hr/dwr/engine.js'></script>
<script type='text/javascript' src='/hr/dwr/util.js'></script>
<script src="../JS/jquery-1.7.2.js"></script>
<style>
td.result {
border: 1px solid black;
}
td.init{
height: 0px;
}
</style>
<script language="JavaScript" type="text/JavaScript">
function GoExport() {
with( document.cscform) {
//alert("Export");
__export.value = "1";
//target="_blank"
action = "PRR_PayrollReconcileEXCEL_EX.jsp";
submit();
action = "PRR_PayrollReconcileEXCEL.jsp";
}
}
function GoProcess(){
with(document.cscform){
submit();
}
}
function goHelp(helpName,inputName){
with(document.cscform){
var helpReturn;
//var fixcon=__fixCon.value;
//__fixCon.value="";
if (inputName=="__reconcileid") {
//__fixCon.value="";
__reconcileid.value ="";
helpReturn="__reconcileid:reconcileid";
linkHelp_Return(helpName,helpReturn);
//__fixCon.value=fixcon;
}
}
}
function linkHelp_Return(help,input){
with(document.cscform){
var param="PRU084.jsp?__helpName="+help+"&__helpReturn="+input+"&__pageCall="+__screen.value+"&__checkVerify=off";
window.open(param,"Help","left=150,top=150,width=600,height=400,toolbar=no,status=yes,scrollbars=yes,resizable=yes");
}
}
$(document).ready(function(){
$('select[name=__prgcode]').on("change",function(){
with(document.cscform){
var payrollGroup = $('select[name=__prgcode]').find(":selected").val();
var lang = "<%=screen.getUProfile().get("lang")%>";
CalculateReconcile.getAllFormulainPayrollGroup(payrollGroup,lang,function(data){
DWRUtil.setValue("__getformula",data);
});}
});
});
function getInitial(){
with(document.cscform){
var payrollGroup = $('select[name=__prgcode]').find(":selected").val();
var lang = "<%=screen.getUProfile().get("lang")%>";
CalculateReconcile.getAllFormulainPayrollGroup(payrollGroup,lang,function(data){
DWRUtil.setValue("__getformula",data);
});
}
}
</script>
</head>
<%
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" onload="getInitial()">
<form name="cscform" method="post" action="PRR_PayrollReconcileEXCEL.jsp">
<!----------BEGIN CSC INITIAL ZONE ------------------------>
<%=screen.InitialVariable()%>
<%!
public String generateZero(int num) {
String number = String.valueOf(num);
if (number.length() == 1) {
return "0" + number;
}else {
return number;
}
}
%>
<!----------END CSC INITIAL ZONE -------------------------->
<input type="hidden" name="__cmd" value="">
<input type="hidden" name="__help" value="">
<input type="hidden" name="__goPage" value="">
<input type="hidden" name="__language" value="<%=screen.getUProfile().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="__screen" value="PRR_PayrollReconcileEXCEL">
<input type="hidden" name="__prgcodeEX" value="<%=payrollGroup%>">
<input type="hidden" name="__reconcileIDEX" value="<%=reconcileid%>">
<input type="hidden" name="__getformula">
<br>
<div align="center">
<%if(reconcileid==null||reconcileid.equals("")){
//Do Nothing
}else{%>
<table class="maxsize" align="center" cellpadding="0" cellspacing="0" class="large" style="background-color:#FAFAFA;color:BLACK" border="1">
<%
DbInquiry inq = new InitialInquiry(screen.getUProfile()).getDbInquiry("Payroll_group");
inq.setColumn("*");
inq.setFilter("codeid='"+payrollGroup+"'");
inq.refresh();
while(inq.next()){
for(int i = 1;i<=30;i++){
String getFormula = inq.getString("formula"+generateZero(i));
String getCaption = inq.getString("caption"+generateZero(i));
if(getFormula.length() > 0){//Check null
getPGroupFormula.put(generateZero(i),getFormula);
getPGroupCaption.put(generateZero(i),getCaption);
}
}
}%>
<!--Create Table Header -->
<tr>
<td width="50" class="init"></td>
<td width="75" class="init"></td>
<td width="75" class="init"></td>
<td width="75" class="init"></td>
<td colspan="<%=getPGroupCaption.size()*3%>" class="init"></td>
</tr>
<tr>
<th rowspan="2" align="center" style="background-color:#FAFAFA;color:BLACK">No.</th>
<th rowspan="2" align="center" style="background-color:#FAFAFA;color:BLACK">Emp ID.</th>
<th rowspan="2" colspan="2" align="center" style="background-color:#FAFAFA;color:BLACK">Name</th>
<%
int countCaption = 0;
Iterator itr = getPGroupCaption.keySet().iterator();
while(itr.hasNext()){
String key = (String) itr.next();%>
<th colspan="3" align="center" style="background-color:#FAFAFA;color:BLACK"><%=getPGroupCaption.get(key)%></th>
<%
countCaption++;
}%>
</tr>
<tr>
<%for(int i = 0;i<countCaption;i++){%>
<th align="center" style="background-color:#FAFAFA;color:BLACK">Prev</th>
<th align="center" style="background-color:#FAFAFA;color:BLACK">Crr</th>
<th align="center" style="background-color:#FAFAFA;color:BLACK">Diff</th>
<%}%>
</tr>
<!-- End Table Header -->
<!-- Create Table Data -->
<%
String tempGroup = "initialcheckbu";
DbInquiry inqEmp = new InitialInquiry(screen.getUProfile()).getDbInquiry("Memployee");
inqEmp.setColumn("employeeid,fname,efname,lname,elname,fullname,bu1,bu1name,bu2,bu2name,bu3,bu3name,bu4,bu4name,bu5,bu5name,branch,branchname");
inqEmp.setFilter("employeeid in (select employeeid from tpayrollreconciliation1 where reconcileid = '"+reconcileid+"')");
inqEmp.setOrderBy(getOrderBy);
/*
DbInquiry inqPayRec = new InitialInquiry(screen.getUProfile()).getDbInquiry("Tpayrollreconciliation1");
inqPayRec.setColumn("*");
inqEmp.putChild(inqPayRec);
*/
inqEmp.refresh();
Double[][] SummaryEachBUPrev = new Double[inqEmp.recCount()][countCaption];
Double[][] SummaryEachBUCrr = new Double[inqEmp.recCount()][countCaption];
Double[][] SummaryEachBUDiff = new Double[inqEmp.recCount()][countCaption];
Double[][] SummaryGrandPrev = new Double[inqEmp.recCount()][countCaption];
Double[][] SummaryGrandCrr = new Double[inqEmp.recCount()][countCaption];
Double[][] SummaryGrandDiff = new Double[inqEmp.recCount()][countCaption];
int line_no = 0;
int group_count = 0;
while(inqEmp.next()){%>
<tr>
<%
String tempAllBU = "";
String tempbu1 = inqEmp.getString("Memployee","bu1name");
String tempbu2 = inqEmp.getString("Memployee","bu2name");
String tempbu3 = inqEmp.getString("Memployee","bu3name");
String tempbu4 = inqEmp.getString("Memployee","bu4name");
String tempbu5 = inqEmp.getString("Memployee","bu5name");
String tempBranch = inqEmp.getString("Memployee","branchname");
tempAllBU = tempbu5+","+tempbu4+","+tempbu3+","+tempbu2+","+tempbu1;
String print_bu ="";
if(getOrderBy.equalsIgnoreCase("bu5")){
print_bu = tempbu5+","+tempbu4+","+tempbu3+","+tempbu2+","+tempbu1;
}else if(getOrderBy.equalsIgnoreCase("bu4")){
print_bu = tempbu4+","+tempbu3+","+tempbu2+","+tempbu1;
}else if(getOrderBy.equalsIgnoreCase("bu3")){
print_bu = tempbu3+","+tempbu2+","+tempbu1;
}else if(getOrderBy.equalsIgnoreCase("bu2")){
print_bu = tempbu2+","+tempbu1;
}else if(getOrderBy.equalsIgnoreCase("bu1")){
print_bu = tempbu1;
}
if(!tempGroup.equalsIgnoreCase(inqEmp.getString(getOrderBy))){
if(!tempGroup.equalsIgnoreCase("initialcheckbu")){ //Create Sub-Total%>
<td class="result" colspan="3" align="left" style="background-color:#FAFAFA;color:BLACK">Sub-Total</td>
<td class="result" colspan="1" style="background-color:#FAFAFA;color:BLACK" align="right"><%=line_no%></td>
<%for(int i = 0;i<countCaption;i++){//Summary each group
Double totalEachCaptionReconcile = 0.0;
Double totalEachCaptionSource = 0.0;
Double totalEachCaptionDifference = 0.0;
for(int j = 0;j<line_no;j++){
totalEachCaptionReconcile = totalEachCaptionReconcile + SummaryEachBUPrev[j][i];
totalEachCaptionSource = totalEachCaptionSource + SummaryEachBUCrr[j][i];
totalEachCaptionDifference = totalEachCaptionDifference + SummaryEachBUDiff[j][i];
}%>
<td class="result" style="background-color:#FAFAFA;color:BLACK" align="center"><%=(totalEachCaptionReconcile==0.0)?"-":totalEachCaptionReconcile%></td>
<td class="result" style="background-color:#FAFAFA;color:BLACK" align="center"><%=(totalEachCaptionSource==0.0)?"-":totalEachCaptionSource%></td>
<td class="result" style="background-color:#FAFAFA;color:BLACK" align="center"><%=(totalEachCaptionDifference==0.0)?"-":totalEachCaptionDifference%></td>
<%
SummaryGrandPrev[group_count][i] = totalEachCaptionReconcile;
SummaryGrandCrr[group_count][i] = totalEachCaptionSource;
SummaryGrandDiff[group_count][i] = totalEachCaptionDifference;
}
group_count++;
%>
</tr>
<tr>
<td class="result" colspan="<%=4+(countCaption*3)%>" style="background-color:#FAFAFA;color:BLACK"></td>
</tr>
<%}
tempGroup = inqEmp.getString(getOrderBy);%>
<td class="result" align="left" style="background-color:#FAFAFA;color:BLACK">Site:</td>
<td class="result" align="left" colspan="<%=3+(countCaption*3)%>" style="background-color:#FAFAFA;color:BLACK"><%=print_bu%></td>
</tr>
<tr>
<%
line_no = 0; //Initial Count
}
//Create Data
line_no++;%>
<td class="result" align="right" style="background-color:#FAFAFA;color:BLACK"><%=line_no%></td>
<td class="result" align="right" style="background-color:#FAFAFA;color:BLACK"><%=inqEmp.getString("employeeid")%></td>
<td class="result" align="left" style="background-color:#FAFAFA;color:BLACK" colspan="2" ><%=inqEmp.getString("Memployee","Fullname")%></td>
<%
int indexItr2 = 0;
Iterator itr2 = getPGroupFormula.keySet().iterator();
while(itr2.hasNext()){
Double totalSource = 0.0;
Double totalReconcile = 0.0;
Double totalDifference = 0.0;
String key = (String) itr2.next();
String thisFormula = getPGroupFormula.get(key);
String thisEmployee = inqEmp.getString("employeeid");
if(thisFormula.indexOf(",")>-1){
thisFormula = thisFormula.replace(",", "','");
}
//get source_amount,reconcile_amount and difference_amount
DbInquiry recAmount = new InitialInquiry(screen.getUProfile()).getDbInquiry("Tpayrollreconciliation1");
recAmount.setColumn("*");
recAmount.setFilter("employeeid = '"+thisEmployee+"' and formulaid in ('"+thisFormula+"') and reconcileid = '"+reconcileid+"'");
recAmount.refresh();
while(recAmount.next()){
String getSource = recAmount.getString("source_amount");
String getReconcile = recAmount.getString("reconcile_amount");
String getDifference = recAmount.getString("difference_amount");
totalSource = totalSource + Double.valueOf(getSource);
totalReconcile = totalReconcile + Double.valueOf(getReconcile);
totalDifference = totalDifference + Double.valueOf(getDifference);
}%>
<td class="result" style="background-color:#FAFAFA;color:BLACK" align="center"><%=(totalReconcile==0.0)?"-":totalReconcile%></td>
<td class="result" style="background-color:#FAFAFA;color:BLACK" align="center"><%=(totalSource==0.0)?"-":totalSource%></td>
<td class="result" style="background-color:#FAFAFA;color:BLACK" align="center"><%=(totalDifference==0.0)?"-":totalDifference%></td>
<%//Save Value
SummaryEachBUPrev[line_no-1][indexItr2] = totalReconcile;
SummaryEachBUCrr[line_no-1][indexItr2] = totalSource;
SummaryEachBUDiff[line_no-1][indexItr2] = totalDifference;
indexItr2++;
}%>
</tr>
<%if(inqEmp.isLast()){%>
<tr>
<td class="result" colspan="3" align="left" style="background-color:#FAFAFA;color:BLACK">Sub-Total</td>
<td class="result" colspan="1" style="background-color:#FAFAFA;color:BLACK" align="right"><%=line_no%></td>
<%for(int i = 0;i<countCaption;i++){//Summary each group
Double totalEachCaptionReconcile = 0.0;
Double totalEachCaptionSource = 0.0;
Double totalEachCaptionDifference = 0.0;
for(int j = 0;j<line_no;j++){
totalEachCaptionReconcile = totalEachCaptionReconcile + SummaryEachBUPrev[j][i];
totalEachCaptionSource = totalEachCaptionSource + SummaryEachBUCrr[j][i];
totalEachCaptionDifference = totalEachCaptionDifference + SummaryEachBUDiff[j][i];
}%>
<td class="result" style="background-color:#FAFAFA;color:BLACK" align="center"><%=(totalEachCaptionReconcile==0.0)?"-":totalEachCaptionReconcile%></td>
<td class="result" style="background-color:#FAFAFA;color:BLACK" align="center"><%=(totalEachCaptionSource==0.0)?"-":totalEachCaptionSource%></td>
<td class="result" style="background-color:#FAFAFA;color:BLACK" align="center"><%=(totalEachCaptionDifference==0.0)?"-":totalEachCaptionDifference%></td>
<%
SummaryGrandPrev[group_count][i] = totalEachCaptionReconcile;
SummaryGrandCrr[group_count][i] = totalEachCaptionSource;
SummaryGrandDiff[group_count][i] = totalEachCaptionDifference;
}
group_count++;
%>
</tr>
<tr>
<td class="result" colspan="<%=4+(countCaption*3)%>" style="background-color:#FAFAFA;color:BLACK"></td>
</tr>
<tr>
<td class="result" colspan="3" align="left" style="background-color:#FAFAFA;color:BLACK">Grand Total</td>
<td class="result" colspan="1" style="background-color:#FAFAFA;color:BLACK" align="right"><%=inqEmp.recCount()%></td>
<%for(int i = 0;i<countCaption;i++){//Summary each group
Double totalEachCaptionReconcile = 0.0;
Double totalEachCaptionSource = 0.0;
Double totalEachCaptionDifference = 0.0;
for(int j = 0;j<group_count;j++){
totalEachCaptionReconcile = totalEachCaptionReconcile + SummaryGrandPrev[j][i];
totalEachCaptionSource = totalEachCaptionSource + SummaryGrandCrr[j][i];
totalEachCaptionDifference = totalEachCaptionDifference + SummaryGrandDiff[j][i];
}%>
<td class="result" style="background-color:#FAFAFA;color:BLACK" align="center"><%=(totalEachCaptionReconcile==0.0)?"-":totalEachCaptionReconcile%></td>
<td class="result" style="background-color:#FAFAFA;color:BLACK" align="center"><%=(totalEachCaptionSource==0.0)?"-":totalEachCaptionSource%></td>
<td class="result" style="background-color:#FAFAFA;color:BLACK" align="center"><%=(totalEachCaptionDifference==0.0)?"-":totalEachCaptionDifference%></td>
<%
}%>
</tr>
</tr>
<%}
}%>
</table>
<%}%>
</div>
</form>
</body>
</html>