@GlobalDBOpenCloseAndUserPrivilages public Map LPCPrint( Session session, HttpServletRequest request, HttpServletResponse response, String LoggedInRegion, String LoggedInUser, String epfno, String month, String year, String billtype) { Map map = new HashMap(); Map cardDetails = null; Map reportMap = new HashMap(); String regionname = ""; String employeename = ""; String designationname = ""; String billingtype = ""; LinkedList dupslipprintlist = new LinkedList(); LinkedList dupslipprintloanlist = new LinkedList(); StringBuffer dupslipquery = new StringBuffer(); try { if (!epfno.trim().equalsIgnoreCase("null") && epfno.trim().length() > 0) { regionname = getRegionmaster(session, LoggedInRegion).getRegionname(); employeename = getEmployeemaster(session, epfno, LoggedInRegion).getEmployeename(); designationname = getDesignationMater( session, getEmployeemaster(session, epfno, LoggedInRegion).getDesignation()) .getDesignation(); if (billtype.equalsIgnoreCase("1")) { dupslipquery.append( " select paycode,paycodename,coalesce(earnings,'0') as earnings,coalesce(deduction,'0') as deduction,loan as loanbalance,nthinstallment,totalinstallment,loanamount from ( "); dupslipquery.append( " (select pm.paycode,pm.paycodename,cast(et.amount as text) as earnings,'' as deduction,'' as loan ,'' as nthinstallment ,'' as totalinstallment,'' as loanamount "); dupslipquery.append(" from payrollprocessingdetails ppd "); dupslipquery.append( " left join employeeearningstransactions et on et.payrollprocessingdetailsid=ppd.id "); dupslipquery.append(" join paycodemaster pm on pm.paycode=et.earningmasterid "); dupslipquery.append( " where ppd.employeeprovidentfundnumber='" + epfno + "' and ppd.month =" + month + " and ppd.year=" + year + " "); dupslipquery.append(" and et.cancelled is false "); dupslipquery.append( " group by pm.paycode,pm.paycodename,earnings,deduction,loan,nthinstallment,totalinstallment "); dupslipquery.append(" order by pm.paycode) "); dupslipquery.append(" union all( "); dupslipquery.append( " select pm.paycode,pm.paycodename,'' as earnings,cast(dt.amount as text) as deduction,coalesce(cast(el.loanbalance as text),'') as loan "); dupslipquery.append(" ,coalesce(cast(el.nthinstallment as text),'') as nthinstallment "); dupslipquery.append( " ,coalesce(cast(el.totalinstallment as text),'') as totalinstallment "); dupslipquery.append(" ,coalesce(cast(el.loanamount as text),'') as loanamount "); dupslipquery.append(" from payrollprocessingdetails ppd "); dupslipquery.append( " join employeedeductionstransactions dt on dt.payrollprocessingdetailsid=ppd.id "); dupslipquery.append(" join paycodemaster pm on pm.paycode=dt.deductionmasterid "); dupslipquery.append( " left join (select payrollprocessingdetailsid,deductioncode,nthinstallment,totalinstallment,eld.loanbalance,la.loanamount from employeeloansandadvancesdetails eld "); dupslipquery.append( " join employeeloansandadvances as la on la.id=eld.employeeloansandadvancesid "); dupslipquery.append( " where la.employeeprovidentfundnumber='" + epfno + "') as el on el.payrollprocessingdetailsid=ppd.id and el.deductioncode=dt.deductionmasterid "); dupslipquery.append( " where ppd.employeeprovidentfundnumber='" + epfno + "' and ppd.month =" + month + " and ppd.year=" + year + " "); dupslipquery.append(" and dt.cancelled is false "); dupslipquery.append( " group by pm.paycode,pm.paycodename,earnings,deduction,loan,el.nthinstallment,el.totalinstallment,el.loanamount "); dupslipquery.append(" order by pm.paycode)) as x "); } else if (billtype.equalsIgnoreCase("2")) { billingtype = "SUPLEMENTARYBILL"; dupslipquery.append( " select paycode,paycodename,earnings,deduction,loan as loanbalance,nthinstallment,totalinstallment,loanamount from ( "); dupslipquery.append( " (select pm.paycode,pm.paycodename,cast(sum(et.amount) as text) as earnings,'' as deduction,'' as loan ,'' as nthinstallment "); dupslipquery.append( " ,'' as totalinstallment,'' as loanamount from supplementatypaybill sb "); dupslipquery.append( " join supplementarypayrollprocessingdetails sppd on sb.id= sppd.supplementatypaybillid and sppd.cancelled is false "); dupslipquery.append( " left join supplementaryemployeeearningstransactions et on et.supplementarypayrollprocessingdetailsid=sppd.id "); dupslipquery.append(" join paycodemaster pm on pm.paycode=et.earningmasterid "); dupslipquery.append( " where sb.employeeprovidentfundnumber='" + epfno + "' and sb.type='" + billingtype + "' and EXTRACT(MONTH FROM date)=" + month + " and EXTRACT(YEAR FROM date)=" + year + " "); dupslipquery.append( " group by pm.paycode,pm.paycodename,et.amount,deduction,loan,nthinstallment,totalinstallment "); dupslipquery.append(" order by pm.paycode) "); dupslipquery.append(" union all( "); dupslipquery.append( " select pm.paycode,pm.paycodename,'' as earnings,cast(sum(dt.amount) as text) as deduction,coalesce(cast(el.loanbalance as text),'') as loan "); dupslipquery.append(" ,coalesce(cast(el.nthinstallment as text),'') as nthinstallment "); dupslipquery.append( " ,coalesce(cast(el.totalinstallment as text),'') as totalinstallment "); dupslipquery.append(" ,coalesce(cast(el.loanamount as text),'') as loanamount "); dupslipquery.append(" from supplementatypaybill sb "); dupslipquery.append( " join supplementarypayrollprocessingdetails sppd on sb.id= sppd.supplementatypaybillid and sppd.cancelled is false "); dupslipquery.append( " left join supplementaryemployeedeductionstransactions dt on dt.supplementarypayrollprocessingdetailsid=sppd.id "); dupslipquery.append(" join paycodemaster pm on pm.paycode=dt.deductionmasterid "); dupslipquery.append( " left join (select supplementarypayrollprocessingdetailsid,deductioncode,nthinstallment,totalinstallment,eld.loanbalance ,la.loanamount "); dupslipquery.append( " from supplementaryemployeeloansandadvancesdetails eld join employeeloansandadvances as la on la.id=eld.employeeloansandadvancesid "); dupslipquery.append( " where la.employeeprovidentfundnumber='" + epfno + "') as el on el.supplementarypayrollprocessingdetailsid=sppd.id and el.deductioncode=dt.deductionmasterid "); dupslipquery.append( " where sb.employeeprovidentfundnumber='" + epfno + "' and sb.type='" + billingtype + "' and EXTRACT(MONTH FROM date)=" + month + " and EXTRACT(YEAR FROM date)=" + year + " "); dupslipquery.append( " group by pm.paycode,pm.paycodename,earnings,dt.amount,loan,el.nthinstallment,el.totalinstallment,el.loanamount "); dupslipquery.append(" order by pm.paycode)) as x "); } else if (billtype.equalsIgnoreCase("3")) { dupslipquery.append( " select paycode,paycodename,earnings,deduction,loan as loanbalance,nthinstallment,totalinstallment,loanamount from ( "); dupslipquery.append( " (select pm.paycode,pm.paycodename,cast(sum(et.amount) as text) as earnings,'' as deduction,'' as loan ,'' as nthinstallment "); dupslipquery.append( " ,'' as totalinstallment,'' as loanamount from supplementatypaybill sb "); dupslipquery.append( " join supplementarypayrollprocessingdetails sppd on sb.id= sppd.supplementatypaybillid and sppd.cancelled is false "); dupslipquery.append( " left join supplementaryemployeeearningstransactions et on et.supplementarypayrollprocessingdetailsid=sppd.id "); dupslipquery.append(" join paycodemaster pm on pm.paycode=et.earningmasterid "); dupslipquery.append( " where sb.employeeprovidentfundnumber='" + epfno + "' and sb.type='LEAVESURRENDER' and EXTRACT(MONTH FROM date)=" + month + " and EXTRACT(YEAR FROM date)=" + year + " "); dupslipquery.append( " group by pm.paycode,pm.paycodename,et.amount,deduction,loan,nthinstallment,totalinstallment "); dupslipquery.append(" order by pm.paycode) "); dupslipquery.append(" union all( "); dupslipquery.append( " select pm.paycode,pm.paycodename,'' as earnings,cast(sum(dt.amount) as text) as deduction,coalesce(cast(el.loanbalance as text),'') as loan "); dupslipquery.append(" ,coalesce(cast(el.nthinstallment as text),'') as nthinstallment "); dupslipquery.append( " ,coalesce(cast(el.totalinstallment as text),'') as totalinstallment "); dupslipquery.append(" ,coalesce(cast(el.loanamount as text),'') as loanamount "); dupslipquery.append(" from supplementatypaybill sb "); dupslipquery.append( " join supplementarypayrollprocessingdetails sppd on sb.id= sppd.supplementatypaybillid and sppd.cancelled is false "); dupslipquery.append( " left join supplementaryemployeedeductionstransactions dt on dt.supplementarypayrollprocessingdetailsid=sppd.id "); dupslipquery.append(" join paycodemaster pm on pm.paycode=dt.deductionmasterid "); dupslipquery.append( " left join (select supplementarypayrollprocessingdetailsid,deductioncode,nthinstallment,totalinstallment,eld.loanbalance,la.loanamount "); dupslipquery.append( " from supplementaryemployeeloansandadvancesdetails eld join employeeloansandadvances as la on la.id=eld.employeeloansandadvancesid "); dupslipquery.append( " where la.employeeprovidentfundnumber='" + epfno + "') as el on el.supplementarypayrollprocessingdetailsid=sppd.id and el.deductioncode=dt.deductionmasterid "); dupslipquery.append( " where sb.employeeprovidentfundnumber='" + epfno + "' and sb.type='LEAVESURRENDER' and EXTRACT(MONTH FROM date)=" + month + " and EXTRACT(YEAR FROM date)=" + year + " "); dupslipquery.append( " group by pm.paycode,pm.paycodename,earnings,dt.amount,loan,el.nthinstallment,el.totalinstallment,el.loanamount "); dupslipquery.append(" order by pm.paycode)) as x "); } Query ledgerQuery = session.createSQLQuery(dupslipquery.toString()); List ledgerList = ledgerQuery.list(); int earrowid = 1; int dedrowid = 1; int lonrowid = 1; Map earDetails = new HashMap(); Map dedDetails = new HashMap(); Map lonDetails = new HashMap(); for (ListIterator its = ledgerList.listIterator(); its.hasNext(); ) { Object[] rows = (Object[]) its.next(); String paycode = (String) rows[0]; cardDetails = new HashMap(); if (paycode.startsWith("E")) { earDetails.put("row" + earrowid, (String) rows[1] + "~" + (String) rows[2]); earrowid++; } else if (paycode.startsWith("D")) { dedDetails.put("row" + dedrowid, (String) rows[1] + "~" + (String) rows[3]); dedrowid++; } else if (paycode.startsWith("L")) { lonDetails.put( "row" + lonrowid, (String) rows[0] + "~" + (String) rows[1] + "~" + (String) rows[2] + "~" + (String) rows[3] + "~" + (String) rows[4] + "~" + (String) rows[5] + "~" + (String) rows[6] + "~" + (String) rows[7]); lonrowid++; } } int earsize = earDetails.size(); int dedsize = dedDetails.size(); int size = 0; if (earsize <= dedsize) { size = dedsize; } else { size = earsize; } for (int loop = 1; loop < size; loop++) { cardDetails = new HashMap(); if (earDetails.get("row" + loop) != null) { String e[] = ((String) earDetails.get("row" + loop)).split("~"); cardDetails.put("paycodenameear", e[0]); cardDetails.put("earnings", e[1]); } if (dedDetails.get("row" + loop) != null) { String d[] = ((String) dedDetails.get("row" + loop)).split("~"); cardDetails.put("paycodenameded", d[0]); cardDetails.put("deduction", d[1]); } cardDetails.put("regionname", regionname); cardDetails.put("empname", employeename); cardDetails.put("designation", designationname); dupslipprintlist.add(cardDetails); } for (int loop = 1; loop <= lonDetails.size(); loop++) { if (lonDetails.get("row" + loop) != null) { cardDetails = new HashMap(); String l[] = ((String) lonDetails.get("row" + loop)).split("~"); cardDetails.put("paycodenamelon", l[1]); cardDetails.put("deductionlon", l[2]); cardDetails.put("loanbalance", Double.parseDouble(l[3])); cardDetails.put("nthinstallment", Double.parseDouble(l[4])); cardDetails.put("totalinstallment", Double.parseDouble(l[5])); cardDetails.put("totalinstallment1", Double.parseDouble(l[6])); cardDetails.put("loanamount", Double.parseDouble(l[7])); cardDetails.put("regionname", regionname); cardDetails.put("empname", employeename); cardDetails.put("designation", designationname); dupslipprintloanlist.add(cardDetails); } } reportMap.put("list", dupslipprintlist); reportMap.put("loanmap", dupslipprintloanlist); reportMap.put("regionname", regionname); } } catch (Exception ex) { map.put("ERROR", "duplicatePaySlipPrint Report Generated Error"); ex.printStackTrace(); } return reportMap; }
@GlobalDBOpenCloseAndUserPrivilages public LinkedList duplicatePaySlipPrint( Session session, HttpServletRequest request, HttpServletResponse response, String LoggedInRegion, String LoggedInUser, String epfno, String month, String year, String billtype) { // System.out.println("***************************** UserTypeServiceImpl class // duplicatePaySlipPrint method is calling ********************************"); Map map = new HashMap(); Map cardDetails = null; String regionname = ""; String employeename = ""; String designationname = ""; String billingtype = ""; LinkedList dupslipprintlist = new LinkedList(); StringBuffer dupslipquery = new StringBuffer(); try { if (!epfno.trim().equalsIgnoreCase("null") && epfno.trim().length() > 0) { regionname = getRegionmaster(session, LoggedInRegion).getRegionname(); employeename = getEmployeemaster(session, epfno, LoggedInRegion).getEmployeename(); designationname = getDesignationMater( session, getEmployeemaster(session, epfno, LoggedInRegion).getDesignation()) .getDesignation(); if (billtype.equalsIgnoreCase("1")) { dupslipquery.append( " select paycode,paycodename,earnings,deduction,loan as loanbalance,nthinstallment,totalinstallment from ( "); dupslipquery.append( " (select pm.paycode,pm.paycodename,cast(et.amount as text) as earnings,'' as deduction,'' as loan ,'' as nthinstallment ,'' as totalinstallment "); dupslipquery.append(" from payrollprocessingdetails ppd "); dupslipquery.append( " left join employeeearningstransactions et on et.payrollprocessingdetailsid=ppd.id "); dupslipquery.append(" join paycodemaster pm on pm.paycode=et.earningmasterid "); dupslipquery.append( " where ppd.employeeprovidentfundnumber='" + epfno + "' and ppd.month =" + month + " and ppd.year=" + year + " "); dupslipquery.append(" and et.cancelled is false "); dupslipquery.append( " group by pm.paycode,pm.paycodename,earnings,deduction,loan,nthinstallment,totalinstallment "); dupslipquery.append(" order by pm.paycode) "); dupslipquery.append(" union all( "); dupslipquery.append( " select pm.paycode,pm.paycodename,'' as earnings,cast(dt.amount as text) as deduction,coalesce(cast(el.loanbalance as text),'') as loan "); dupslipquery.append(" ,coalesce(cast(el.nthinstallment as text),'') as nthinstallment "); dupslipquery.append( " ,coalesce(cast(el.totalinstallment as text),'') as totalinstallment "); dupslipquery.append(" from payrollprocessingdetails ppd "); dupslipquery.append( " join employeedeductionstransactions dt on dt.payrollprocessingdetailsid=ppd.id "); dupslipquery.append(" join paycodemaster pm on pm.paycode=dt.deductionmasterid "); dupslipquery.append( " left join (select payrollprocessingdetailsid,deductioncode,nthinstallment,totalinstallment,eld.loanbalance from employeeloansandadvancesdetails eld "); dupslipquery.append( " join employeeloansandadvances as la on la.id=eld.employeeloansandadvancesid "); dupslipquery.append( " where la.employeeprovidentfundnumber='" + epfno + "') as el on el.payrollprocessingdetailsid=ppd.id and el.deductioncode=dt.deductionmasterid "); // dupslipquery.append(" left join employeeloansandadvances ln on // ln.employeeprovidentfundnumber='"+epfno+"' and ln.deductioncode=dt.deductionmasterid // and ln.id!='R01334' "); // dupslipquery.append(" left join employeeloansandadvancesdetails ld on // ld.employeeloansandadvancesid=ln.id and ld.payrollprocessingdetailsid=ppd.id "); dupslipquery.append( " where ppd.employeeprovidentfundnumber='" + epfno + "' and ppd.month =" + month + " and ppd.year=" + year + " "); dupslipquery.append(" and dt.cancelled is false "); dupslipquery.append( " group by pm.paycode,pm.paycodename,earnings,deduction,loan,el.nthinstallment,el.totalinstallment "); dupslipquery.append(" order by pm.paycode)) as x "); } else if (billtype.equalsIgnoreCase("2")) { billingtype = "SUPLEMENTARYBILL"; dupslipquery.append( " select paycode,paycodename,earnings,deduction,loan as loanbalance,nthinstallment,totalinstallment from ( "); dupslipquery.append( " (select pm.paycode,pm.paycodename,cast(sum(et.amount) as text) as earnings,'' as deduction,'' as loan ,'' as nthinstallment "); dupslipquery.append(" ,'' as totalinstallment from supplementatypaybill sb "); dupslipquery.append( " join supplementarypayrollprocessingdetails sppd on sb.id= sppd.supplementatypaybillid and sppd.cancelled is false "); dupslipquery.append( " left join supplementaryemployeeearningstransactions et on et.supplementarypayrollprocessingdetailsid=sppd.id "); dupslipquery.append(" join paycodemaster pm on pm.paycode=et.earningmasterid "); dupslipquery.append( " where sb.employeeprovidentfundnumber='" + epfno + "' and sb.type='" + billingtype + "' and EXTRACT(MONTH FROM date)=" + month + " and EXTRACT(YEAR FROM date)=" + year + " "); dupslipquery.append( " group by pm.paycode,pm.paycodename,et.amount,deduction,loan,nthinstallment,totalinstallment "); dupslipquery.append(" order by pm.paycode) "); dupslipquery.append(" union all( "); dupslipquery.append( " select pm.paycode,pm.paycodename,'' as earnings,cast(sum(dt.amount) as text) as deduction,coalesce(cast(el.loanbalance as text),'') as loan "); dupslipquery.append(" ,coalesce(cast(el.nthinstallment as text),'') as nthinstallment "); dupslipquery.append( " ,coalesce(cast(el.totalinstallment as text),'') as totalinstallment "); dupslipquery.append(" from supplementatypaybill sb "); dupslipquery.append( " join supplementarypayrollprocessingdetails sppd on sb.id= sppd.supplementatypaybillid and sppd.cancelled is false "); dupslipquery.append( " left join supplementaryemployeedeductionstransactions dt on dt.supplementarypayrollprocessingdetailsid=sppd.id "); dupslipquery.append(" join paycodemaster pm on pm.paycode=dt.deductionmasterid "); dupslipquery.append( " left join (select supplementarypayrollprocessingdetailsid,deductioncode,nthinstallment,totalinstallment,eld.loanbalance "); dupslipquery.append( " from supplementaryemployeeloansandadvancesdetails eld join employeeloansandadvances as la on la.id=eld.employeeloansandadvancesid "); dupslipquery.append( " where la.employeeprovidentfundnumber='" + epfno + "') as el on el.supplementarypayrollprocessingdetailsid=sppd.id and el.deductioncode=dt.deductionmasterid "); dupslipquery.append( " where sb.employeeprovidentfundnumber='" + epfno + "' and sb.type='" + billingtype + "' and EXTRACT(MONTH FROM date)=" + month + " and EXTRACT(YEAR FROM date)=" + year + " "); dupslipquery.append( " group by pm.paycode,pm.paycodename,earnings,dt.amount,loan,el.nthinstallment,el.totalinstallment "); dupslipquery.append(" order by pm.paycode)) as x "); } else if (billtype.equalsIgnoreCase("3")) { dupslipquery.append( " select paycode,paycodename,earnings,deduction,loan as loanbalance,nthinstallment,totalinstallment from ( "); dupslipquery.append( " (select pm.paycode,pm.paycodename,cast(sum(et.amount) as text) as earnings,'' as deduction,'' as loan ,'' as nthinstallment "); dupslipquery.append(" ,'' as totalinstallment from supplementatypaybill sb "); dupslipquery.append( " join supplementarypayrollprocessingdetails sppd on sb.id= sppd.supplementatypaybillid and sppd.cancelled is false "); dupslipquery.append( " left join supplementaryemployeeearningstransactions et on et.supplementarypayrollprocessingdetailsid=sppd.id "); dupslipquery.append(" join paycodemaster pm on pm.paycode=et.earningmasterid "); dupslipquery.append( " where sb.employeeprovidentfundnumber='" + epfno + "' and sb.type='LEAVESURRENDER' and EXTRACT(MONTH FROM date)=" + month + " and EXTRACT(YEAR FROM date)=" + year + " "); dupslipquery.append( " group by pm.paycode,pm.paycodename,et.amount,deduction,loan,nthinstallment,totalinstallment "); dupslipquery.append(" order by pm.paycode) "); dupslipquery.append(" union all( "); dupslipquery.append( " select pm.paycode,pm.paycodename,'' as earnings,cast(sum(dt.amount) as text) as deduction,coalesce(cast(el.loanbalance as text),'') as loan "); dupslipquery.append(" ,coalesce(cast(el.nthinstallment as text),'') as nthinstallment "); dupslipquery.append( " ,coalesce(cast(el.totalinstallment as text),'') as totalinstallment "); dupslipquery.append(" from supplementatypaybill sb "); dupslipquery.append( " join supplementarypayrollprocessingdetails sppd on sb.id= sppd.supplementatypaybillid and sppd.cancelled is false "); dupslipquery.append( " left join supplementaryemployeedeductionstransactions dt on dt.supplementarypayrollprocessingdetailsid=sppd.id "); dupslipquery.append(" join paycodemaster pm on pm.paycode=dt.deductionmasterid "); dupslipquery.append( " left join (select supplementarypayrollprocessingdetailsid,deductioncode,nthinstallment,totalinstallment,eld.loanbalance "); dupslipquery.append( " from supplementaryemployeeloansandadvancesdetails eld join employeeloansandadvances as la on la.id=eld.employeeloansandadvancesid "); dupslipquery.append( " where la.employeeprovidentfundnumber='" + epfno + "') as el on el.supplementarypayrollprocessingdetailsid=sppd.id and el.deductioncode=dt.deductionmasterid "); dupslipquery.append( " where sb.employeeprovidentfundnumber='" + epfno + "' and sb.type='LEAVESURRENDER' and EXTRACT(MONTH FROM date)=" + month + " and EXTRACT(YEAR FROM date)=" + year + " "); dupslipquery.append( " group by pm.paycode,pm.paycodename,earnings,dt.amount,loan,el.nthinstallment,el.totalinstallment "); dupslipquery.append(" order by pm.paycode)) as x "); // billingtype="LEAVESURRENDER"; } Query ledgerQuery = session.createSQLQuery(dupslipquery.toString()); List ledgerList = ledgerQuery.list(); for (ListIterator its = ledgerList.listIterator(); its.hasNext(); ) { Object[] rows = (Object[]) its.next(); cardDetails = new HashMap(); cardDetails.put("paycode", (String) rows[0]); cardDetails.put("paycodename", (String) rows[1]); cardDetails.put("earnings", (String) rows[2]); cardDetails.put("deduction", (String) rows[3]); cardDetails.put("loanbalance", (String) rows[4]); cardDetails.put("nthinstallment", (String) rows[5]); cardDetails.put("totalinstallment", (String) rows[6]); cardDetails.put("regionname", regionname); cardDetails.put("empname", employeename); cardDetails.put("designation", designationname); dupslipprintlist.add(cardDetails); } } } catch (Exception ex) { map.put("ERROR", "duplicatePaySlipPrint Report Generated Error"); ex.printStackTrace(); } return dupslipprintlist; }