Ejemplo n.º 1
0
  @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;
  }
Ejemplo n.º 2
0
  @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;
  }