<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <%@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="TAVAC001_EXCEL" scope="page" class="com.csc.library.system.Task" /> <html ng-app="myHR"> <% CheckNull chk = new CheckNull(); CscCalendar cs = new CscCalendar(); TAVAC001_EXCEL.setChannel(request ,response); HelpEntry screen = (HelpEntry) TAVAC001_EXCEL.process("HelpEntry", "TAVAC001_REPORT"); screen.setMaxLine(1000000); //screen.getInquiry().setFilter("(start_period='1' and end_period='1' and year='2015') OR (start_period='2' and end_period='2' and year='2015')"); DecimalFormat df = new DecimalFormat("###0.00"); CscTime csc_time = new CscTime(); int year = cs.getYear(); String calYos = cs.getYYYYMMDD(); String getLeaveType = chk.chkNullString(request.getParameter("__getLeavetype"),""); String leavetype = chk.chkNullString(request.getParameter("__leavetype"),""); String cmd = chk.chkNullString(TAVAC001_EXCEL.getParameter("__cmd"),""); String bu1 = chk.chkNullString(TAVAC001_EXCEL.getParameter("__bu1")); String bu2 = chk.chkNullString(TAVAC001_EXCEL.getParameter("__bu2")); String bu3 = chk.chkNullString(TAVAC001_EXCEL.getParameter("__bu3")); String bu4 = chk.chkNullString(TAVAC001_EXCEL.getParameter("__bu4")); String bu5 = chk.chkNullString(TAVAC001_EXCEL.getParameter("__bu5")); String emp1 = chk.chkNullString(TAVAC001_EXCEL.getParameter("__emp1")); String emp2 = chk.chkNullString(TAVAC001_EXCEL.getParameter("__emp2")); String period1 = chk.chkNullString(TAVAC001_EXCEL.getParameter("__period1")); String period1_date = chk.chkNullString(TAVAC001_EXCEL.getParameter("__period1_date")); String period1_month = chk.chkNullString(TAVAC001_EXCEL.getParameter("__period1_month")); String period2 = chk.chkNullString(TAVAC001_EXCEL.getParameter("__period2")); String period2_date = chk.chkNullString(TAVAC001_EXCEL.getParameter("__period2_date")); String period2_month = chk.chkNullString(TAVAC001_EXCEL.getParameter("__period2_month")); String periodgroup = chk.chkNullString(TAVAC001_EXCEL.getParameter("__period_group")); String __year = chk.chkNullString(TAVAC001_EXCEL.getParameter("__year")); String __eventgrp_list = chk.chkNullString(TAVAC001_EXCEL.getParameter("__eventgrp_list")); String __period_list = chk.chkNullString(TAVAC001_EXCEL.getParameter("__period_list")); String group_by = chk.chkNullString(TAVAC001_EXCEL.getParameter("__groupby_vac")); String language = screen.getUProfile().get("lang"); //String[] arr_period = {"1","2"}; String[] arr_period = __period_list.split(","); //String[] arr_vac_profile = {"ANL#Vacation","CPDO#Compensate Day Off","CPOT#Compensate OT"}; String[] arr_vac_profile = __eventgrp_list.split(","); String filter = "1=1 "; if(!emp1.equals("")&&!emp2.equals("")){ filter += " and employeeid between '"+emp1+"' and '"+emp2+"' "; }else if(!emp1.equals("")&&emp2.equals("")){ filter += " and employeeid = '"+emp1+"' "; }else if(emp1.equals("")&&!emp2.equals("")){ filter += " and employeeid = '"+emp2+"' "; } if(!periodgroup.equals("")){ filter += " and periodgroup = '"+periodgroup+"' "; } if(!bu1.equals("")){ filter += " and bu1 = '"+bu1+"' "; } if(!bu2.equals("")){ filter += " and bu2 = '"+bu2+"' "; } if(!bu3.equals("")){ filter += " and bu3 = '"+bu3+"' "; } if(!bu4.equals("")){ filter += " and bu4 = '"+bu4+"' "; } if(!bu5.equals("")){ filter += " and bu5 = '"+bu5+"' "; } if(!arr_period[0].equals("")){ String filter_period =" AND ("; for(int i=0;i<arr_period.length;i++){ if(!__year.equals("")){ filter_period += "(start_period='"+arr_period[i]+"' and end_period='"+arr_period[i]+"' and year='"+__year+"') OR"; }else{ filter_period += "(start_period='"+arr_period[i]+"' and end_period='"+arr_period[i]+"' ) OR"; } } filter_period = filter_period.substring(0,filter_period.length()-2); filter_period +=" )"; filter += filter_period; } if(!arr_vac_profile[0].equals("")){ String filter_eventgrp = " AND EVENTGRP IN ("; for(int i=0;i<arr_vac_profile.length;i++){ filter_eventgrp += "'"+arr_vac_profile[i].substring(0,arr_vac_profile[i].indexOf("#"))+"',"; } filter_eventgrp = filter_eventgrp.substring(0,filter_eventgrp.length()-1); filter_eventgrp +=" )"; filter += filter_eventgrp; } screen.getInquiry().setGroupBy("employeeid"); if(!group_by.equals("")){ screen.getInquiry().setOrderBy(group_by); } //out.print(filter); screen.getInquiry().setFilter(filter); screen.process(); %> <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.indexOf("__emp") > -1){ helpReturn = inputName+":employeeid" fixcon = "employeeid in(select employeeid from tvacation_accrual)"; linkHelp_Return(helpName,helpReturn,fixcon); }else if(inputName=="__period_group"){ helpReturn = inputName+":groupid" linkHelp_Return(helpName,helpReturn,fixcon); }else if(inputName=="__period1"){ helpReturn = inputName+":period,"+inputName+"_date:period_date,"+inputName+"_month:period_month"; var getPeriodGroup = __period_group.value; if(getPeriodGroup != ""){ fixcon = "groupid ='"+getPeriodGroup+"'"; linkHelp_Return(helpName,helpReturn,fixcon); }else{ alert(MyCode[219]); } }else if(inputName=="__period2"){ helpReturn = inputName+":period,"+inputName+"_date:period_date,"+inputName+"_month:period_month"; var getPeriodGroup = __period_group.value; if(getPeriodGroup != ""){ fixcon = "groupid ='"+getPeriodGroup+"'"; linkHelp_Return(helpName,helpReturn,fixcon); }else{ alert(MyCode[219]); } } } } 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){ setEventgrp(); setPeriod(); __cmd.value = 'search'; if($("[name=__eventgrp_list]").val()==""){ alert(MyCode[220]); }else if($("[name=__period_list]").val()==""){ alert(MyCode[219]); }else{ submit(); } } } function setEventgrp(){ var list_of_eventgrp =''; var option = $("select[name=__eventgrp]").find("option"); option.each(function(){ list_of_eventgrp += $(this).val()+'#'+$(this).text()+','; }); list_of_eventgrp = list_of_eventgrp.substring(0,list_of_eventgrp.length-1); $("[name=__eventgrp_list]").val(list_of_eventgrp); } function setPeriod(){ var list_of_period =''; var $start_period = $("[name=__period1]").val(); var $end_period = $("[name=__period2]").val(); if($start_period!=''&&$end_period!=''&&$start_period<=$end_period){ for (var i = $start_period;i<=$end_period ;i++ ){ list_of_period += i+','; } }else if($start_period!=''&&$end_period==''){ list_of_period = $start_period; }else if($start_period==''&&$end_period!=''){ list_of_period = $end_period; } list_of_period = list_of_period.substring(0,list_of_period.length-1); $("[name=__period_list]").val(list_of_period); } function delOption(){ var option = $("select[name=__eventgrp]").find("option"); option.each(function(){ var se = $(this).prop("selected"); if(se){ $(this).remove(); } }); } function AddLeave(){ var sel = $("select[name=__eventgrp]"); var option = $("select[name=__eventgrp]").find("option"); var leavetype = $("[name='__leavetype']").find("option:selected"); var isDup; option.each(function(){ if($(this).val()==leavetype.val()){ isDup = true; } }); if(!isDup&&leavetype.val()!=''&&leavetype.val()!='null'){ sel.append($('<option>', { value: leavetype.val(), text : leavetype.text() })); } } var tableToExcel = (function() { var uri = 'data:application/vnd.ms-excel;base64,' , template = '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"><head><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>{worksheet}</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head><body><table>{table}</table></body></html>' , base64 = function(s) { return window.btoa(unescape(encodeURIComponent(s))) } , format = function(s, c) { return s.replace(/{(\w+)}/g, function(m, p) { return c[p]; }) } return function(table, name) { if (!table.nodeType) table = document.getElementById(table) var ctx = {worksheet: name || 'Worksheet', table: table.innerHTML} window.location.href = uri + base64(format(template, ctx)) } })() </script> </head> <body leftmargin="0" topmargin="0" marginwidth="0" marginheight="0"> <form name="cscform" method="post" action ="TAVAC001_EXCEL.jsp" ng-controller="Swaplang as sw" > <%=screen.InitialVariable()%> <input type="hidden" name="__cmd" value="<%=TAVAC001_EXCEL.getParameter("__cmd")%>"> <input type="hidden" name="__screen" value="TAVAC001_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">Vacation Accrual,<span swlang code='SW011656'></span></td> </tr> <tr> <td class="blankspace" colspan="4"></td> </tr> <td align="right" swlang code='SW000350'></td> <td colspan="3"><input type="text" name="__emp1" size="15" value="<%=emp1%>"> <a href="javascript:goHelp('MEMPLOYEEHELP','__emp1')" onMouseOut="MM_swapImgRestore()" onMouseOver="MM_swapImage('Image2','','../IMAGES/BUTTON/SWAP/SEARCH_SWAP20.gif',1)"><img src="../IMAGES/BUTTON/MAIN/SEARCH20.gif" alt="Help" name="Image2" border="0" align="absmiddle"></a> - <input type="text" name="__emp2" size="15" value="<%=emp2%>"> <a href="javascript:goHelp('MEMPLOYEEHELP','__emp2')" onMouseOut="MM_swapImgRestore()" onMouseOver="MM_swapImage('Image3','','../IMAGES/BUTTON/SWAP/SEARCH_SWAP20.gif',1)"><img src="../IMAGES/BUTTON/MAIN/SEARCH20.gif" alt="Help" name="Image3" border="0" align="absmiddle"></a> </td> </tr> <tr> <td align="right" swlang code='SW000309'></td> <td colspan="3"><input type="text" name="__bu1" size="15" value="<%=bu1%>"> <a href="javascript:goHelpBu('MBU1HELP','__bu1')" onMouseOut="MM_swapImgRestore()" onMouseOver="MM_swapImage('imgBu1','','../IMAGES/BUTTON/SWAP/SEARCH_SWAP20.gif',1)"><img src="../IMAGES/BUTTON/MAIN/SEARCH20.gif" alt="Help" name="imgBu1" border="0" align="absmiddle"></a> <input name="__bu1_desc" type="text" value="<%=chk.chkNullString(TAVAC001_EXCEL.getParameter("__bu1_desc"))%>" size="50" readonly=""> </td> </tr> <tr> <td align="right" swlang code='SW000310'></td> <td colspan="3"><input type="text" name="__bu2" size="15" value="<%=bu2%>"> <a href="javascript:goHelpBu('MBU2HELP','__bu2')" onMouseOut="MM_swapImgRestore()" onMouseOver="MM_swapImage('imgBu2','','../IMAGES/BUTTON/SWAP/SEARCH_SWAP20.gif',1)"><img src="../IMAGES/BUTTON/MAIN/SEARCH20.gif" alt="Help" name="imgBu2" border="0" align="absmiddle"></a> <input name="__bu2_desc" type="text" value="<%=chk.chkNullString(TAVAC001_EXCEL.getParameter("__bu2_desc"))%>" size="50" readonly=""> </td> </tr> <tr> <td align="right" swlang code='SW000311'></td> <td colspan="3"><input type="text" name="__bu3" size="15" value="<%=bu3%>"> <a href="javascript:goHelpBu('MBU3HELP','__bu3')" onMouseOut="MM_swapImgRestore()" onMouseOver="MM_swapImage('imgBu3','','../IMAGES/BUTTON/SWAP/SEARCH_SWAP20.gif',1)"><img src="../IMAGES/BUTTON/MAIN/SEARCH20.gif" alt="Help" name="imgBu3" border="0" align="absmiddle"></a> <input name="__bu3_desc" type="text" value="<%=chk.chkNullString(TAVAC001_EXCEL.getParameter("__bu3_desc"))%>" size="50" readonly=""> </td> </tr> <tr> <td align="right" swlang code='SW000312'></td> <td colspan="3"><input type="text" name="__bu4" size="15" value="<%=bu4%>"> <a href="javascript:goHelpBu('MBU4HELP','__bu4')" onMouseOut="MM_swapImgRestore()" onMouseOver="MM_swapImage('imgBu4','','../IMAGES/BUTTON/SWAP/SEARCH_SWAP20.gif',1)"><img src="../IMAGES/BUTTON/MAIN/SEARCH20.gif" alt="Help" name="imgBu4" border="0" align="absmiddle"></a> <input name="__bu4_desc" type="text" value="<%=chk.chkNullString(TAVAC001_EXCEL.getParameter("__bu4_desc"))%>" size="50" readonly=""> </td> </tr> <tr> <td align="right" swlang code='SW000313'></td> <td colspan="3"><input type="text" name="__bu5" size="15" value="<%=bu5%>"> <a href="javascript:goHelpBu('MBU5HELP','__bu5')" onMouseOut="MM_swapImgRestore()" onMouseOver="MM_swapImage('imgBu5','','../IMAGES/BUTTON/SWAP/SEARCH_SWAP20.gif',1)"><img src="../IMAGES/BUTTON/MAIN/SEARCH20.gif" alt="Help" name="imgBu5" border="0" align="absmiddle"></a> <input name="__bu5_desc" type="text" value="<%=chk.chkNullString(TAVAC001_EXCEL.getParameter("__bu5_desc"))%>" size="50" readonly=""> </td> </tr> <tr> <td align="right" swlang code='SW011708'></td> <td align="left" colspan='3'> <input type="text" name="__period_group" size="5" value="<%=periodgroup%>"> <a href="javascript:goHelp('MVACATION_PERIOD_GROUPHELP','__period_group')" onMouseOut="MM_swapImgRestore()" onMouseOver="MM_swapImage('imgPeriodGroup','','../IMAGES/BUTTON/SWAP/SEARCH_SWAP20.gif',1)"><img src="../IMAGES/BUTTON/MAIN/SEARCH20.gif" alt="Help" name="imgPeriodGroup" border="0" align="absmiddle"></a> </span> </td> </tr> <tr> <td align="right" swlang code='SW011709'>Start Period</td> <td align="left" colspan='3'> <input type="text" name="__period1" size="2" value="<%=period1%>" readonly> <a href="javascript:goHelp('MVACATION_PERIODHELP','__period1')" onMouseOut="MM_swapImgRestore()" onMouseOver="MM_swapImage('imgBu5','','../IMAGES/BUTTON/SWAP/SEARCH_SWAP20.gif',1)"><img src="../IMAGES/BUTTON/MAIN/SEARCH20.gif" alt="Help" name="imgBu5" border="0" align="absmiddle"></a> <span swlang code="SW000002">Date</span><input type="text" name="__period1_date" size="1" value="<%=period1_date%>" readonly> <span swlang code="SW000800">Month</span><input type="text" name="__period1_month" size="1" value="<%=period1_month%>" readonly> </td> </tr> <tr> <td align="right" swlang code='SW011710'>End Period</td> <td align="left" colspan='3'> <input type="text" name="__period2" size="2" value="<%=period2%>" readonly> <a href="javascript:goHelp('MVACATION_PERIODHELP','__period2')" onMouseOut="MM_swapImgRestore()" onMouseOver="MM_swapImage('imgBu5','','../IMAGES/BUTTON/SWAP/SEARCH_SWAP20.gif',1)"><img src="../IMAGES/BUTTON/MAIN/SEARCH20.gif" alt="Help" name="imgBu5" border="0" align="absmiddle"></a> <span swlang code="SW000002">Date</span><input type="text" name="__period2_date" size="1" value="<%=period2_date%>" readonly> <span swlang code="SW000800">Month</span><input type="text" name="__period2_month" size="1" value="<%=period2_month%>" readonly> </td> </tr> <tr> <td align="right" swlang code='SW000717'>Year</td> <td align="left" colspan='3'> <select name="__year"> <option value=""></option> <% String sql1 = "select distinct(year) from tvacation_accrual order by year;"; DataSet ds1 = new DataSet(sql1, screen.getUProfile().get("dbname"), "tvacation_accrual"); ds1.initConnection(screen.getUProfile()); while (ds1.next()) {%> <option value="<%=ds1.getString("year")%>" <%=(__year.equals(ds1.getString("year")))?"selected":""%>><%=ds1.getString("year")%></option> <%} ds1.closeConnection(); %> </select> </td> </tr> <tr> <td><div align="right" swlang code='SW000300'></div></td> <td colspan='3'> <select multiple="multiple" class="chkfrom" name="__eventgrp" style="width:200px;height:120px;"> <% if(!arr_vac_profile[0].equals("")){ for(int i=0;i<arr_vac_profile.length;i++){ String[] temp_eventgrp = arr_vac_profile[i].split("#"); %> <option value="<%=temp_eventgrp[0]%>"><%=temp_eventgrp[1]%></option> <% } } %> </select> <a href="javascript:delOption();" onmouseout="MM_swapImgRestore()" onmouseover="MM_swapImage('Image422','','../IMAGES/BUTTON/SWAP/DEL_SWAP20.gif',1)"><img src="../IMAGES/BUTTON/MAIN/DEL20.gif" align="top" ></a> <select name="__leavetype"> <% DbInquiry inq_vacation_profile = new InitialInquiry(screen.getUProfile()).getDbInquiry("VACATION_PROFILE"); inq_vacation_profile.setColumn("VAC_CODE,VACID,TDESC,EDESC"); inq_vacation_profile.refresh(); while(inq_vacation_profile.next()){%> <option value="<%=inq_vacation_profile.getString("VACID")%>"<%if(leavetype.equals(inq_vacation_profile.getString("VACID"))){%>selected<%}%>><%=(language.equals("eng"))?inq_vacation_profile.getString("EDESC"):inq_vacation_profile.getString("TDESC")%></option> <%}%> </select> <button type="button" class="btn btn-primary width100" swlang code="SW013162" onClick="AddLeave();">ADD</button> </td> </tr> <tr> <% ListBox listbox1 = new ListBox(); listbox1.setUProfile(screen.getUProfile()); listbox1.setName("groupby_vac"); listbox1.setValidCode("GROUP_BY_VAC"); %> <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');">EXPORT</button> </div> </td> </tr> <% if(cmd.equalsIgnoreCase("search")){ int no = 1; int col_abount_emp = 5; int sub_total_no = 0; double sub_total_salary = 0.00; int grand_total_no = 0; double grand_total_salary = 0.00; int column_per_eventgrp = (arr_period.length*4)+4; int total_col = (column_per_eventgrp*arr_vac_profile.length)+col_abount_emp; double[] arr_sub_total = new double[(column_per_eventgrp*arr_vac_profile.length)]; double[] arr_grand_total = new double[(column_per_eventgrp*arr_vac_profile.length)]; %> <TABLE align="center" cellpadding="0" cellspacing="0" border="1" class="largest" style="margin-top:10px;" id="export_table"> <style> td { padding:2px; mso-number-format:\@; } .sub-total{ font-weight: bold; border:1px solid black; background:#C4D79B; } .grand-total{ font-weight: bold; border:1px solid black; } .acc-column-head{ font-weight: bold; border:1px solid black; color:white; background:#9BBB59; } .acc-data { background:#EBF1DE; border:1px solid black; } </style> <TR> <TD colspan="5" align='left' style="font-weight: bold;background:white;"><%=screen.getUProfile().get("companyname")%></TD> <TD colspan="<%=total_col-5%>" align='left' style="background:white;"></TD> </TR> <TR> <TD colspan="5" align='left' style="font-weight: bold;background:white;">Vacation Accrual Report</TD> <TD colspan="<%=total_col-5%>" align='left' style="background:white;"></TD> </TR> <TR> <% String period1_ddmmyyyy = new CscCalendar(period1_date+"-"+period1_month+"-"+__year).getDDMMYYYY(); String period3_ddmmyyyy = ""; Integer period3 = Integer.parseInt(period2)+1; DbInquiry inq_vacation_period = new InitialInquiry(screen.getUProfile()).getDbInquiry("Mvacation_period"); inq_vacation_period.setColumn("*"); inq_vacation_period.setFilter("period='"+period3+"' and groupid ='"+periodgroup+"'"); //out.print(inq_vacation_period.getFilter()); inq_vacation_period.refresh(); if(inq_vacation_period.next()){ period3_ddmmyyyy = new CscCalendar(inq_vacation_period.getString("period_date")+"-"+inq_vacation_period.getString("period_month")+"-"+__year).decDate().getDDMMYYYY(); } inq_vacation_period.closeConnection(); %> <TD colspan="5" align='left' style="background:white;">Period : <%=period1%> (<%=period1_ddmmyyyy%>) to <%=period2%> (<%=period3_ddmmyyyy%>) year <%=__year%></TD> <TD colspan="<%=total_col-5%>" align='left' style="background:white;"></TD> </TR> <TR> <TD colspan="3" align='left' style="background:white;">Printed Date : <%=cs.getDDMMYYYYHHMMSS()%></TD> <TD colspan="2" align='left' style="background:white;">Print by: <%=screen.getUProfile().get("fullname")%></TD> <TD colspan="<%=total_col-5%>" align='left' style="background:white;"></TD> </TR> <TR> <TD rowspan="2" class="acc-column-head" align='center'>No.</TD> <TD rowspan="2" class="acc-column-head" align='center' style="min-width:80px;">ID.</TD> <TD rowspan="2" class="acc-column-head" align='center' style="min-width:200px;">Name</TD> <TD rowspan="2" class="acc-column-head" align='center' style="min-width:150px;">Position</TD> <TD rowspan="2" class="acc-column-head" align='center' style="min-width:100px;">Salary</TD> <% for(int a=0;a<arr_vac_profile.length;a++){ %> <TD colspan="<%=column_per_eventgrp%>" align="center" class="acc-column-head"><%=arr_vac_profile[a].substring(arr_vac_profile[a].indexOf("#")+1,arr_vac_profile[a].length())%></TD> <% } %> </TR> <TR > <% for(int aa=0;aa<arr_vac_profile.length;aa++){ %> <TD align='left' class="acc-column-head">Inventory</TD> <TD align='left' class="acc-column-head">Used</TD> <TD align='left' class="acc-column-head">Balance</TD> <TD align='left' class="acc-column-head">Accrual</TD> <% for(int ab=0;ab<arr_period.length;ab++){ %> <TD align='left' class="acc-column-head">Period: <%=arr_period[ab]%></TD> <TD align='left' class="acc-column-head">Amount</TD> <TD align='left' class="acc-column-head">Outstanding <%=ab+1%></TD> <TD align='left' class="acc-column-head">Amount</TD> <% } } %> </TR> <% String Temp_Group_By = "initial group by" ; screen.nextRec(); ////////////////////////////////////////////////////////////////////////////////////////////////// DO ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// do{ String employeeid = screen.getString("employeeid"); String This_Group_By = screen.getString(group_by); if(!Temp_Group_By.equalsIgnoreCase(This_Group_By)){ if(!Temp_Group_By.equals("initial group by")){ %> <TR > <TD colspan="3" class="sub-total" style="border:none;">Sub-Total</TD> <TD colspan="1" align="right" class="sub-total" style="border:none;"><%=sub_total_no%></TD> <TD colspan="1" align="right" class="sub-total" style="border:none;"><%=sub_total_salary%></TD> <% for(int zz=0;zz<arr_sub_total.length;zz++){ %> <TD colspan="1" align="right" class="sub-total"><%=df.format(arr_sub_total[zz])%></TD> <% } %> </TR> <% sub_total_no = 0; sub_total_salary = 0.00; arr_sub_total = new double[(column_per_eventgrp*arr_vac_profile.length)]; //arr_grand_total = new double[(column_per_eventgrp*arr_vac_profile.length)]; } String text_group_by = ""; if(group_by.equalsIgnoreCase("COSTCENTER")){ text_group_by = screen.getInquiry().getChild("mcostcenter0").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 colspan="5" class="acc-data" style="border:none;" ><%=text_group_by%></TD> <% for(int zz=0;zz<arr_sub_total.length;zz++){ %> <TD colspan="1" class="acc-data" style="background:white;"></TD> <% } %> </TR> <% Temp_Group_By = This_Group_By; } Double salary = chk.chkNullStringToDouble(screen.getInquiry().getChild("memployee").getString("salary")); %> <TR > <TD align='center' class="acc-data" style="border:none;"><%=no%></TD> <TD align='left' class="acc-data" style="border:none;"><%=employeeid%></TD> <TD align='left' class="acc-data" style="border:none;"><%=screen.getInquiry().getChild("memployee").getString("fullname")%></TD> <TD align='left' class="acc-data" style="border:none;"><%=screen.getInquiry().getChild("memployee").getString("positionname")%></TD> <TD align='right' class="acc-data" style="border:none;"><%=salary%></TD> <% /////////////////////////////////////////////////////////////////////////////////////////////// DATA ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// for(int i=0;i<arr_vac_profile.length;i++){ int count_arr_sub_total = 0; int count_arr_grand_total = 0; Double hour_d = 0.00; Double inventory = 0.00; Double used = 0.00; Double balance = 0.00; Double accrual = 0.00; Double this_period = 0.00; Double this_period_amount = 0.00; Double outstanding = 0.00; Double outstanding_amount = 0.00; Boolean hasFirstPeriod = false; String eventgrp = arr_vac_profile[i].substring(0,arr_vac_profile[i].indexOf("#")); DbInquiry inq_tvac1 = new InitialInquiry(screen.getUProfile()).getDbInquiry("Tvacation_accrual", false); inq_tvac1.setColumn("*"); inq_tvac1.setFilter("employeeid='"+employeeid+"' and periodgroup='"+periodgroup+"' and start_period='"+arr_period[0]+"' and end_period='"+arr_period[0]+"' and eventgrp='"+eventgrp+"' and year='"+__year+"' "); inq_tvac1.refresh(); if(inq_tvac1.next()){ hasFirstPeriod = true; hour_d = chk.chkNullStringToDouble(inq_tvac1.getString("hour_d")); inventory = csc_time.convertDDHHMMToDDHH(chk.chkNullString(inq_tvac1.getString("this_year")),hour_d.toString()); used = csc_time.convertDDHHMMToDDHH(chk.chkNullString(inq_tvac1.getString("taken")),hour_d.toString()); balance = inventory-used; accrual = (salary/30)*balance; arr_sub_total[count_arr_sub_total] += inventory; count_arr_sub_total++; arr_sub_total[count_arr_sub_total] += used; count_arr_sub_total++; arr_sub_total[count_arr_sub_total] += balance; count_arr_sub_total++; arr_sub_total[count_arr_sub_total] += accrual; count_arr_sub_total++; arr_grand_total[count_arr_grand_total] += inventory; count_arr_grand_total++; arr_grand_total[count_arr_grand_total] += used; count_arr_grand_total++; arr_grand_total[count_arr_grand_total] += balance; count_arr_grand_total++; arr_grand_total[count_arr_grand_total] += accrual; count_arr_grand_total++; %> <TD align='right' class="acc-data" style="background:white;"><%=df.format(inventory)%></TD> <TD align='right' class="acc-data" style="background:white;"><%=df.format(used)%></TD> <TD align='right' class="acc-data" style="background:white;"><%=df.format(balance)%></TD> <TD align='right' class="acc-data" style="background:white;"><%=df.format(accrual)%></TD> <% }else{ %> <TD align='center' class="acc-data" style="background:white;"></TD> <TD align='center' class="acc-data" style="background:white;"></TD> <TD align='center' class="acc-data" style="background:white;"></TD> <TD align='center' class="acc-data" style="background:white;"></TD> <% } for(int j=0;j<arr_period.length;j++){ String period = arr_period[j]; DbInquiry inq_tvac2 = new InitialInquiry(screen.getUProfile()).getDbInquiry("Tvacation_accrual", false); inq_tvac2.setColumn("*"); inq_tvac2.setFilter("employeeid='"+employeeid+"' and periodgroup='"+periodgroup+"' and start_period='"+period+"' and end_period='"+period+"' and eventgrp='"+eventgrp+"' and year='"+__year+"' "); inq_tvac2.refresh(); //out.println(inq_tvac2.getFilter()); if(inq_tvac2.next()&&hasFirstPeriod){ this_period = csc_time.convertDDHHMMToDDHH(chk.chkNullString(inq_tvac2.getString("used")),hour_d.toString()); this_period_amount = (salary/30)*this_period; outstanding = inventory-this_period; outstanding_amount = (salary/30)*outstanding; arr_sub_total[count_arr_sub_total] += this_period; count_arr_sub_total++; arr_sub_total[count_arr_sub_total] += this_period_amount; count_arr_sub_total++; arr_sub_total[count_arr_sub_total] += outstanding; count_arr_sub_total++; arr_sub_total[count_arr_sub_total] += outstanding_amount; count_arr_sub_total++; arr_grand_total[count_arr_grand_total] += this_period; count_arr_grand_total++; arr_grand_total[count_arr_grand_total] += this_period_amount; count_arr_grand_total++; arr_grand_total[count_arr_grand_total] += outstanding; count_arr_grand_total++; arr_grand_total[count_arr_grand_total] += outstanding_amount; count_arr_grand_total++; %> <TD align='right' class="acc-data" style="background:white;"><%=df.format(this_period)%></TD> <TD align='right' class="acc-data" style="background:white;"><%=df.format(this_period_amount)%></TD> <TD align='right' class="acc-data" style="background:white;"><%=df.format(outstanding)%></TD> <TD align='right' class="acc-data" style="background:white;"><%=df.format(outstanding_amount)%></TD> <% }else{ %> <TD align='left' class="acc-data" style="background:white;"></TD> <TD align='left' class="acc-data" style="background:white;"></TD> <TD align='left' class="acc-data" style="background:white;"></TD> <TD align='left' class="acc-data" style="background:white;"></TD> <% } %> <% } } ////grand %> </TR> <% sub_total_no++; sub_total_salary+=salary; grand_total_no++; grand_total_salary+=salary; no++; }while(screen.nextRec()); ///////////////////////////////////////////////////////////////////////// WHILE & END DATA //////////////////////////////////////////////////////////////////////////////////////////////////// %> <TR > <TD colspan="3" class="sub-total" style="border:none;">Sub-Total</TD> <TD colspan="1" align="right" class="sub-total" style="border:none;"><%=sub_total_no%></TD> <TD colspan="1" align="right" class="sub-total" style="border:none;"><%=sub_total_salary%></TD> <% for(int zz=0;zz<arr_sub_total.length;zz++){ %> <TD colspan="1" align="right" class="sub-total"><%=df.format(arr_sub_total[zz])%></TD> <% } %> </TR> <TR > <TD colspan="3" class="grand-total" style="border:none;">Grand Total</TD> <TD colspan="1" align="right" class="grand-total" style="border:none;"><%=grand_total_no%></TD> <TD colspan="1" align="right" class="grand-total" style="border:none;"><%=grand_total_salary%></TD> <% for(int zz=0;zz<arr_grand_total.length;zz++){ %> <TD colspan="1" align="right" class="grand-total"><%=df.format(arr_grand_total[zz])%></TD> <% } %> </TR> </TABLE> <%}%> <tr> <td class="blankspace" colspan="4"></td> </tr> </table> </div> </form> </body> </html>