Example usage for org.hibernate SQLQuery list

List of usage examples for org.hibernate SQLQuery list

  1. HOME
  2. Java
  3. org.hibernate
  4. org.hibernate.*
  5. SQLQuery
  6. list

Introduction

In this page you can find the example usage for org.hibernate SQLQuery list.

Prototype

List<R> list();

Source Link

Document

Return the query results as a List.

Usage

From source file:com.vertec.daoimpl.StockDAOImpl.java

/**
 * call from InvoiceController case "ToInvoice" call from StockController
 * case "ToBranch" and "ToVehicle"// w  ww .j  av  a 2s. c  o m
 *
 * @param branchId
 * @return
 */
public List<Object[]> loadProductFromBranchStock(int branchId) {
    Session session = NewHibernateUtil.getSessionFactory().openSession();
    Transaction transaction = session.beginTransaction();

    if (session != null) {
        try {
            SQLQuery query = session.createSQLQuery(
                    "Select p.product_id,p.product_code,p.product_name From branch_stock bs inner join product p on bs.product_id=p.product_id where bs.branch_id=:branchId group by p.product_id order by p.product_code asc");
            query.setParameter("branchId", branchId);
            List<Object[]> prList = query.list();
            return prList;

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (session != null && session.isOpen()) {
                session.close();
            }
        }
    }

    return null;
}

From source file:com.vertec.daoimpl.StockDAOImpl.java

public List<Object[]> loadProductFromWarehouseStock() {
    Session session = NewHibernateUtil.getSessionFactory().openSession();

    if (session != null) {
        try {/*from   w ww . j  ava  2 s.  c  o m*/
            SQLQuery query = session.createSQLQuery(
                    "Select p.product_id,p.product_code,p.product_name From warehouse_stock ws inner join product_master pm on pm.product_master_id=ws.product_master_product_master_id inner join product p on pm.product_id=p.product_id group by p.product_id order by p.product_code asc");

            List<Object[]> prList = query.list();
            return prList;

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (session != null && session.isOpen()) {
                session.close();
            }
        }
    }

    return null;
}

From source file:com.vertec.daoimpl.StockDAOImpl.java

/**
 * from StockController case "ToBranchPM"
 *
 * @param arr/*w  w w.j  a v a 2 s  .com*/
 * @return
 */
public List<Object[]> loadProductMasterFromBPM(int[] arr) {
    Session session = NewHibernateUtil.getSessionFactory().openSession();
    Transaction transaction = session.beginTransaction();

    System.out.println("AAA");
    if (session != null) {
        try {
            SQLQuery query = session.createSQLQuery(
                    "SELECT bp.bpm_id,bp.product_master_id,p.product_id,p.product_code,p.product_name,bp.quantity,pm.purchased_price,pm.selling_price\n"
                            + "FROM branch_productmaster bp inner join product_master pm on bp.product_master_id=pm.product_master_id\n"
                            + "inner join product p on pm.product_id=p.product_id where bp.branch_id=:branch_id and p.product_id=:product_id");
            query.setParameter("branch_id", arr[0]);
            query.setParameter("product_id", arr[1]);

            List<Object[]> prList = query.list();
            return prList;

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (session != null && session.isOpen()) {
                session.close();
            }
        }
    }

    return null;
}

From source file:com.vertec.daoimpl.StockDAOImpl.java

/**
 * from StockController case "ToAddBPM"/*from  ww  w . j  av  a 2  s.com*/
 *
 * @param arr
 * @return
 */
public List<Object[]> loadProductMasterToAddBPM(int[] arr) {
    Session session = NewHibernateUtil.getSessionFactory().openSession();
    Transaction transaction = session.beginTransaction();

    if (session != null) {
        try {
            SQLQuery query = session.createSQLQuery(
                    "SELECT * FROM product_master pm where pm.product_id=:product_id and pm.product_master_id not in (SELECT product_master_id FROM branch_productmaster where branch_id=:branchId);");
            //                SQLQuery query = session.createSQLQuery("SELECT pm.product_master_id,pm.product_id,pm.purchased_price,pm.selling_price,pm.is_available FROM product_master pm inner join branch_productmaster bpm on bpm.product_master_id=pm.product_master_id where pm.product_id:product_id and bpm.branch_id=:branchId ;");

            query.setParameter("branchId", arr[0]);
            query.setParameter("product_id", arr[1]);
            List<Object[]> prList = query.list();
            return prList;

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (session != null && session.isOpen()) {
                session.close();
            }
        }
    }

    return null;
}

From source file:com.vertec.daoimpl.StockDAOImpl.java

/**
 * call from StockController case "ToAddNewVehicleStock"
 *
 * @param arr//from  w  w  w.  j a va  2 s.c  o m
 * @return
 */
public List<Object[]> loadToAddVehicleStock(int[] arr) {
    Session session = NewHibernateUtil.getSessionFactory().openSession();
    Transaction transaction = session.beginTransaction();

    if (session != null) {
        try {
            SQLQuery query = session.createSQLQuery(
                    "SELECT bp.bpm_id,bp.product_master_id,pmr.purchased_price,pmr.selling_price,bp.quantity FROM branch_productmaster bp\n"
                            + "inner join product_master pmr on bp.product_master_id=pmr.product_master_id\n"
                            + "where pmr.product_id=:product_id and bp.branch_id=:branch_id and pmr.is_available=:is_available and bp.bpm_id not in (SELECT vs.bpm_id\n"
                            + "FROM vehicle_stock vs\n" + "inner join vehicle v on vs.vehicle_id=v.vehicle_id\n"
                            + "inner join branch_productmaster bpm on vs.bpm_id=bpm.bpm_id\n"
                            + "inner join product_master pm on bpm.product_master_id=pm.product_master_id where pm.product_id=:product_id and v.branch_id=:branch_id and vs.vehicle_id=:vehicle_id)");
            query.setParameter("branch_id", arr[0]);
            query.setParameter("product_id", arr[1]);
            query.setParameter("vehicle_id", arr[2]);
            query.setParameter("is_available", true);
            List<Object[]> prList = query.list();
            return prList;

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (session != null && session.isOpen()) {
                session.close();
            }
        }
    }

    return null;
}

From source file:com.vertec.daoimpl.StockDAOImpl.java

/**
 * call from StockController case "ToUpdateVehicleStock"
 *
 * @param arr/*  www .j  a  v a2s .c  om*/
 * @return
 */
public List<Object[]> loadToUpdateVehicleStock(int[] arr) {
    Session session = NewHibernateUtil.getSessionFactory().openSession();
    Transaction transaction = session.beginTransaction();

    if (session != null) {
        try {
            SQLQuery query = session.createSQLQuery(
                    "SELECT vs.vehicle_stock_id,vs.bpm_id,vs.quantity as vquan,pm.purchased_price,pm.selling_price,bpm.quantity FROM vehicle_stock vs\n"
                            + "inner join branch_productmaster bpm on vs.bpm_id=bpm.bpm_id\n"
                            + "inner join product_master pm on bpm.product_master_id=pm.product_master_id\n"
                            + "where pm.product_id=:product_id and vs.vehicle_id=:vehicle_id and pm.is_available=:is_available");
            query.setParameter("product_id", arr[1]);
            query.setParameter("vehicle_id", arr[2]);
            query.setParameter("is_available", true);
            List<Object[]> prList = query.list();
            return prList;

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (session != null && session.isOpen()) {
                session.close();
            }
        }
    }

    return null;
}

From source file:com.vertec.daoimpl.StockDAOImpl.java

/**
 * call from InvoiceController case "LoadBPMToInvoice"
 *
 * @param arr/* ww w .  jav  a 2 s .  c om*/
 * @return
 */
public List<Object[]> loadBPMForInvoice(int[] arr) {
    Session session = NewHibernateUtil.getSessionFactory().openSession();
    Transaction transaction = session.beginTransaction();

    if (session != null) {
        try {
            SQLQuery query = session.createSQLQuery(
                    "SELECT bpm.bpm_id,bpm.product_master_id,bpm.quantity as branchquan,pm.purchased_price,pm.selling_price\n"
                            + "FROM branch_productmaster bpm inner join product_master pm on (bpm.product_master_id=pm.product_master_id)\n"
                            + "where pm.product_id=:product_id and bpm.branch_id=:branch_id and pm.is_available=:is_available");
            query.setParameter("product_id", arr[1]);
            query.setParameter("branch_id", arr[0]);
            query.setParameter("is_available", true);
            List<Object[]> bpmList = query.list();
            return bpmList;

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (session != null && session.isOpen()) {
                session.close();
            }
        }
    }

    return null;
}

From source file:com.vertec.daoimpl.StockDAOImpl.java

public List<Object[]> loadWSForInvoice(int pro) {
    Session session = NewHibernateUtil.getSessionFactory().openSession();

    if (session != null) {
        try {//from  w ww. j  a va2 s. co  m

            SQLQuery query2 = session.createSQLQuery(
                    "select ws.warehouse_stock_id,ws.qty,pm.purchased_price,pm.selling_price from warehouse_stock ws inner join product_master pm on pm.product_master_id=ws.product_master_product_master_id where pm.product_id=:product_id and ws.qty>0");

            //                SQLQuery query = session.createSQLQuery("SELECT bpm.bpm_id,bpm.product_master_id,bpm.quantity as branchquan,pm.purchased_price,pm.selling_price\n"
            //                        + "FROM branch_productmaster bpm inner join product_master pm on (bpm.product_master_id=pm.product_master_id)\n"
            //                        + "where pm.product_id=:product_id and bpm.branch_id=:branch_id and pm.is_available=:is_available");
            query2.setParameter("product_id", pro);
            //                query.setParameter("is_available", true);
            List<Object[]> bpmList = query2.list();
            return bpmList;

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (session != null && session.isOpen()) {
                session.close();
            }
        }
    }

    return null;
}

From source file:com.vertec.daoimpl.StockDAOImpl.java

/**
 * call from InvoiceController case "LoadBPMToInvoice"
 *
 * @param arr//from   www . ja v a  2 s  .  co m
 * @return
 */
public List<Object[]> loadVSForInvoice(int[] arr) {
    Session session = NewHibernateUtil.getSessionFactory().openSession();
    Transaction transaction = session.beginTransaction();

    if (session != null) {
        try {
            SQLQuery query = session.createSQLQuery(
                    "select vs.bpm_id,pm.product_master_id,vs.quantity as vquan,pm.purchased_price,pm.selling_price from vehicle_stock vs\n"
                            + "inner join branch_productmaster bpm on vs.bpm_id=bpm.bpm_id\n"
                            + "inner join product_master pm on bpm.product_master_id=pm.product_master_id\n"
                            + "inner join vehicle v on vs.vehicle_id=v.vehicle_id\n"
                            + "where pm.product_id=:product_id and v.vehicle_id=:vehicle_id and pm.is_available=:is_available");
            query.setParameter("product_id", arr[1]);
            query.setParameter("vehicle_id", arr[0]);
            query.setParameter("is_available", true);
            List<Object[]> bpmList = query.list();
            return bpmList;

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (session != null && session.isOpen()) {
                session.close();
            }
        }
    }

    return null;
}

From source file:com.viettel.hqmc.DAO.ReportDAO.java

public void reportVT() {
    try {// w  w w  . jav  a2s . c om
        String templateFile = "/WEB-INF/reportTemplate/reportDayKT.xls";
        //List<FilesNoClob> data;
        List<FeePaymentFileForm> data;
        List<FeePaymentFileForm> data3;
        List<FeePaymentFileForm> data2;
        Long spTM, spTM1, spTM2, spTM3, spCK, spCK1, spCK2, spCK3, spOL, spOL1, spOL2, spOL3, valueTM, valueTM1,
                valueTM2, valueTM3, valueCK, valueCK1, valueCK2, valueCK3, valueOL, valueOL1, valueOL2,
                valueOL3;
        Long value1, value2, value3, value4;
        Long total1, total2, total3, total4;
        Long checkCountTM = 0L;
        Long checkCountCK = 0L;
        Long checkCountOL = 0L;
        List lstParam = new ArrayList();
        ConcurrentHashMap bean = new ConcurrentHashMap();
        //data = fileDao.onsearchDayReportClerical();
        String sql = "";
        SimpleDateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy");
        String header;

        if (searchFeeForm.getDateFrom() != null) {
            header = "Thng k t ngy: " + dateFormat.format(searchFeeForm.getDateFrom());
            if (searchFeeForm.getDateTo() != null) {
                header = header + " - ?n ngy: " + dateFormat.format(searchFeeForm.getDateTo());
            }
        } else if (searchFeeForm.getDateTo() != null) {
            header = "Tip nhn n ngy: " + dateFormat.format(searchFeeForm.getDateTo());
        } else {
            header = "Thng k ton b";
        }

        if (searchFeeForm.getDateFrom() != null) {
            sql += " and fpi.payment_date >= to_date( ? ,'dd/MM/yyyy hh24:mi:ss') ";
            String param = "" + DateTimeUtils.convertDateToString(searchFeeForm.getDateFrom(), "dd/MM/yyyy")
                    + " 00:00:00";
            lstParam.add(param);
        }

        if (searchFeeForm.getDateTo() != null) {
            sql += " and fpi.payment_date <= to_date( ?,'dd/MM/yyyy hh24:mi:ss') ";
            String param = "" + DateTimeUtils.convertDateToString(searchFeeForm.getDateTo(), "dd/MM/yyyy")
                    + " 23:59:59";
            lstParam.add(param);
        }
        // thuc pham thuong
        SQLQuery query1 = (SQLQuery) getSession().createSQLQuery(
                "select wm_concat(fpi.payment_date) as payment_date ,wm_concat(fpi.payment_code) as payment_code,wm_concat(f.file_code) as file_code,f.business_name,wm_concat(fpi.fee_payment_type_id) as fee_payment_type_id, count(*) as sp,sum(fpi.cost) as total,ROW_NUMBER() OVER (ORDER BY f.business_name) idx from fee_payment_info fpi inner join files f  on fpi.file_id = f.file_id\n"
                        + "where (f.is_active = 1 or f.is_active = 2) and fpi.status = 1 and fpi.is_active = 1 and fpi.cost = 500000 "
                        + sql + " group by f.business_name");

        // thuc pham chuc nang
        SQLQuery query2 = (SQLQuery) getSession().createSQLQuery(
                "select wm_concat(fpi.payment_date) as payment_date ,wm_concat(fpi.payment_code) as payment_code,wm_concat(f.file_code) as file_code,f.business_name,wm_concat(fpi.fee_payment_type_id) as fee_payment_type_id, count(*) as sp,sum(fpi.cost) as total,ROW_NUMBER() OVER (ORDER BY f.business_name) idx from fee_payment_info fpi inner join files f  on fpi.file_id = f.file_id\n"
                        + "where (f.is_active = 1 or f.is_active = 2) and fpi.status = 1 and fpi.is_active = 1 and fpi.cost = 1500000 "
                        + sql + " group by f.business_name");
        // le phi cap so

        SQLQuery query3 = (SQLQuery) getSession().createSQLQuery(
                "select wm_concat(fpi.payment_date) as payment_date ,wm_concat(fpi.payment_code) as payment_code,wm_concat(f.file_code) as file_code,f.business_name,wm_concat(fpi.fee_payment_type_id) as fee_payment_type_id, count(*) as sp,sum(fpi.cost) as total,ROW_NUMBER() OVER (ORDER BY f.business_name) idx from fee_payment_info fpi inner join files f  on fpi.file_id = f.file_id \n"
                        + "where (f.is_active = 1 or f.is_active = 2) and fpi.status = 1 and fpi.is_active = 1  and fpi.fee_id = (select e.fee_id from fee e where e.fee_type = 1) "
                        + sql + " group by f.business_name");

        for (int i = 0; i < lstParam.size(); i++) {
            query1.setParameter(i, lstParam.get(i));
            query2.setParameter(i, lstParam.get(i));
            query3.setParameter(i, lstParam.get(i));
        }

        // thuc pham thuong
        List lstResult = query1.list();
        FeePaymentFileForm item = new FeePaymentFileForm();
        List result = new ArrayList<FeePaymentFileForm>();
        if (lstResult != null && lstResult.size() > 0) {
            for (int i = 0; i < lstResult.size(); i++) {
                Object[] row = (Object[]) lstResult.get(i);
                if (row.length > 0) {
                    //paymentDate
                    if (row[0] != null && !"".equals(row[0])) {
                        String paymentDate = row[0].toString();
                        String paymentDateNew = "";
                        List<String> myList = new ArrayList<>(Arrays.asList(paymentDate.split(",")));
                        if (myList.size() == 1) {
                            SimpleDateFormat sdf = new SimpleDateFormat("dd-MMM-yy");
                            Date convertedCurrentDate = sdf.parse(paymentDate);
                            paymentDateNew = DateTimeUtils.convertDateToString(convertedCurrentDate,
                                    "dd/MM/yyyy");
                        } else {
                            for (int j = 0; j < myList.size(); j++) {
                                SimpleDateFormat sdf = new SimpleDateFormat("dd-MMM-yy");
                                Date convertedCurrentDate = sdf.parse(myList.get(j).toString());
                                paymentDateNew += DateTimeUtils.convertDateToString(convertedCurrentDate,
                                        "dd/MM/yyyy") + "\n";

                            }
                        }
                        item.setPaymentDate(paymentDateNew);
                    }
                    //paymentCode
                    if (row[1] != null && !"".equals(row[1])) {
                        String paymentCode = row[1].toString();
                        String paymentCodeNew = "";
                        List<String> myList = new ArrayList<>(Arrays.asList(paymentCode.split(",")));
                        if (myList.size() == 1) {
                            paymentCodeNew = paymentCode;
                        } else {
                            for (int j = 0; j < myList.size(); j++) {
                                paymentCodeNew += myList.get(j).toString() + "\n";
                            }
                        }
                        item.setPaymentCode(paymentCodeNew);
                    }
                    //fileCode
                    if (row[2] != null && !"".equals(row[2])) {
                        String fileCode = row[2].toString();
                        String fileCodeNew = "";
                        List<String> myList = new ArrayList<>(Arrays.asList(fileCode.split(",")));
                        if (myList.size() == 1) {
                            fileCodeNew = fileCode;
                        } else {
                            for (int j = 0; j < myList.size(); j++) {
                                fileCodeNew += myList.get(j).toString() + "\n";
                            }
                        }
                        item.setFileCode(fileCodeNew);
                    }
                    // businessName
                    if (row[3] != null && !"".equals(row[3])) {
                        item.setBusinessName(row[3].toString());
                    }
                    // feepaymentType
                    if (row[4] != null && !"".equals(row[4])) {
                        String feePaymentType = row[4].toString();
                        Long countTM = 0l, countCK = 0l, countOL = 0l, totalTM = 0l, totalCK = 0l, totalOL = 0l;
                        List<String> myList = new ArrayList<>(Arrays.asList(feePaymentType.split(",")));
                        for (int j = 0; j < myList.size(); j++) {
                            if ("1".equals(myList.get(j).toString())) {
                                countOL++;
                                checkCountOL++;
                            }
                            if ("2".equals(myList.get(j).toString())) {
                                countTM++;
                                checkCountTM++;
                            }
                            if ("3".equals(myList.get(j).toString())) {
                                countCK++;
                                checkCountCK++;
                            }
                        }

                        if (countOL != null) {
                            totalOL = 500000 * countOL;
                        }
                        if (countCK != null) {
                            totalCK = 500000 * countCK;
                        }
                        if (countTM != null) {
                            totalTM = 500000 * countTM;
                        }
                        item.setCountCK(countCK);
                        item.setCountOL(countOL);
                        item.setCountTM(countTM);
                        item.setTotalCK(totalCK);
                        item.setTotalOL(totalOL);
                        item.setTotalTM(totalTM);
                    }
                    if (row[7] != null && !"".equals(row[7])) {
                        item.setIndex(Long.parseLong(row[7].toString()));
                    }

                }
                result.add(item);
                item = new FeePaymentFileForm();
            }
        }

        spTM = checkCountTM;
        spOL = checkCountOL;
        spCK = checkCountCK;
        valueTM = 500000 * spTM;
        valueCK = 500000 * spCK;
        valueOL = 500000 * spOL;
        total1 = spTM + spOL + spCK;
        value1 = valueTM + valueCK + valueOL;

        //reset checkCount chay tiep nhung lan sau
        checkCountTM = 0l;
        checkCountCK = 0l;
        checkCountOL = 0l;

        // thuc pham chuc nang
        List lstResult2 = query2.list();
        FeePaymentFileForm item2 = new FeePaymentFileForm();
        List result2 = new ArrayList<FeePaymentFileForm>();
        if (lstResult2 != null && lstResult2.size() > 0) {
            for (int i = 0; i < lstResult2.size(); i++) {
                Object[] row = (Object[]) lstResult2.get(i);
                if (row.length > 0) {
                    //paymentDate
                    if (row[0] != null && !"".equals(row[0])) {
                        String paymentDate = row[0].toString();
                        String paymentDateNew = "";
                        List<String> myList = new ArrayList<>(Arrays.asList(paymentDate.split(",")));
                        if (myList.size() == 1) {
                            SimpleDateFormat sdf = new SimpleDateFormat("dd-MMM-yy");
                            Date convertedCurrentDate = sdf.parse(paymentDate);
                            paymentDateNew = DateTimeUtils.convertDateToString(convertedCurrentDate,
                                    "dd/MM/yyyy");
                        } else {
                            for (int j = 0; j < myList.size(); j++) {
                                SimpleDateFormat sdf = new SimpleDateFormat("dd-MMM-yy");
                                Date convertedCurrentDate = sdf.parse(myList.get(j).toString());
                                paymentDateNew += DateTimeUtils.convertDateToString(convertedCurrentDate,
                                        "dd/MM/yyyy") + "\n";
                            }
                        }
                        item2.setPaymentDate(paymentDateNew);
                    }
                    //paymentCode
                    if (row[1] != null && !"".equals(row[1])) {
                        String paymentCode = row[1].toString();
                        String paymentCodeNew = "";
                        List<String> myList = new ArrayList<>(Arrays.asList(paymentCode.split(",")));
                        if (myList.size() == 1) {
                            paymentCodeNew = paymentCode;
                        } else {
                            for (int j = 0; j < myList.size(); j++) {
                                paymentCodeNew += myList.get(j).toString() + "\n";
                            }
                        }
                        item2.setPaymentCode(paymentCodeNew);
                    }
                    //fileCode
                    if (row[2] != null && !"".equals(row[2])) {
                        String fileCode = row[2].toString();
                        String fileCodeNew = "";
                        List<String> myList = new ArrayList<>(Arrays.asList(fileCode.split(",")));
                        if (myList.size() == 1) {
                            fileCodeNew = fileCode;
                        } else {
                            for (int j = 0; j < myList.size(); j++) {
                                fileCodeNew += myList.get(j).toString() + "\n";
                            }
                        }
                        item2.setFileCode(fileCodeNew);
                    }
                    // businessName
                    if (row[3] != null && !"".equals(row[3])) {
                        item2.setBusinessName(row[3].toString());
                    }
                    // feepaymentType
                    if (row[4] != null && !"".equals(row[4])) {
                        String feePaymentType = row[4].toString();
                        Long countTM = 0l, countCK = 0l, countOL = 0l, totalTM = 0l, totalCK = 0l, totalOL = 0l;
                        List<String> myList = new ArrayList<>(Arrays.asList(feePaymentType.split(",")));
                        for (int j = 0; j < myList.size(); j++) {
                            if ("1".equals(myList.get(j).toString())) {
                                countOL++;
                                checkCountOL++;
                            }
                            if ("2".equals(myList.get(j).toString())) {
                                countTM++;
                                checkCountTM++;
                            }
                            if ("3".equals(myList.get(j).toString())) {
                                countCK++;
                                checkCountCK++;
                            }
                        }
                        if (countOL != null) {
                            totalOL = 1500000 * countOL;
                        }
                        if (countCK != null) {
                            totalCK = 1500000 * countCK;
                        }
                        if (countTM != null) {
                            totalTM = 1500000 * countTM;
                        }
                        item2.setCountCK(countCK);
                        item2.setCountOL(countOL);
                        item2.setCountTM(countTM);
                        item2.setTotalCK(totalCK);
                        item2.setTotalOL(totalOL);
                        item2.setTotalTM(totalTM);
                    }
                    if (row[7] != null && !"".equals(row[7])) {
                        item2.setIndex(Long.parseLong(row[7].toString()));
                    }

                }
                result2.add(item2);
                item2 = new FeePaymentFileForm();
            }
        }

        spTM1 = checkCountTM;
        spCK1 = checkCountCK;
        spOL1 = checkCountOL;
        valueTM1 = 1500000 * spTM1;
        valueCK1 = 1500000 * spCK1;
        valueOL1 = 1500000 * spOL1;
        total2 = spTM1 + spOL1 + spCK1;
        value2 = valueTM1 + valueCK1 + valueOL1;
        //reset checkCount chay tiep nhung lan sau
        checkCountTM = 0l;
        checkCountCK = 0l;
        checkCountOL = 0l;
        // le phi cap so
        List lstResult3 = query3.list();
        FeePaymentFileForm item3 = new FeePaymentFileForm();
        List result3 = new ArrayList<FeePaymentFileForm>();
        if (lstResult3 != null && lstResult3.size() > 0) {
            for (int i = 0; i < lstResult3.size(); i++) {
                Object[] row = (Object[]) lstResult3.get(i);
                if (row.length > 0) {
                    //paymentDate
                    if (row[0] != null && !"".equals(row[0])) {
                        String paymentDate = row[0].toString();
                        String paymentDateNew = "";
                        List<String> myList = new ArrayList<>(Arrays.asList(paymentDate.split(",")));
                        if (myList.size() == 1) {
                            SimpleDateFormat sdf = new SimpleDateFormat("dd-MMM-yy");
                            Date convertedCurrentDate = sdf.parse(paymentDate);
                            paymentDateNew = DateTimeUtils.convertDateToString(convertedCurrentDate,
                                    "dd/MM/yyyy");
                        } else {
                            for (int j = 0; j < myList.size(); j++) {
                                SimpleDateFormat sdf = new SimpleDateFormat("dd-MMM-yy");
                                Date convertedCurrentDate = sdf.parse(myList.get(j).toString());
                                paymentDateNew += DateTimeUtils.convertDateToString(convertedCurrentDate,
                                        "dd/MM/yyyy") + "\n";
                            }
                        }
                        item3.setPaymentDate(paymentDateNew);
                    }
                    //paymentCode
                    if (row[1] != null && !"".equals(row[1])) {
                        String paymentCode = row[1].toString();
                        String paymentCodeNew = "";
                        List<String> myList = new ArrayList<>(Arrays.asList(paymentCode.split(",")));
                        if (myList.size() == 1) {
                            paymentCodeNew = paymentCode;
                        } else {
                            for (int j = 0; j < myList.size(); j++) {
                                paymentCodeNew += myList.get(j).toString() + "\n";
                            }
                        }
                        item3.setPaymentCode(paymentCodeNew);
                    }
                    //fileCode
                    if (row[2] != null && !"".equals(row[2])) {
                        String fileCode = row[2].toString();
                        String fileCodeNew = "";
                        List<String> myList = new ArrayList<>(Arrays.asList(fileCode.split(",")));
                        if (myList.size() == 1) {
                            fileCodeNew = fileCode;
                        } else {
                            for (int j = 0; j < myList.size(); j++) {
                                fileCodeNew += myList.get(j).toString() + "\n";
                            }
                        }
                        item3.setFileCode(fileCodeNew);
                    }
                    // businessName
                    if (row[3] != null && !"".equals(row[3])) {
                        item3.setBusinessName(row[3].toString());
                    }
                    // feepaymentType
                    if (row[4] != null && !"".equals(row[4])) {
                        String feePaymentType = row[4].toString();
                        Long countTM = 0l, countCK = 0l, countOL = 0l, totalTM = 0l, totalCK = 0l, totalOL = 0l;
                        List<String> myList = new ArrayList<>(Arrays.asList(feePaymentType.split(",")));
                        for (int j = 0; j < myList.size(); j++) {
                            if ("1".equals(myList.get(j).toString())) {
                                countOL++;
                                checkCountOL++;
                            }
                            if ("2".equals(myList.get(j).toString())) {
                                countTM++;
                                checkCountTM++;
                            }
                            if ("3".equals(myList.get(j).toString())) {
                                countCK++;
                                checkCountCK++;
                            }
                        }
                        if (countOL != null) {
                            totalOL = 150000 * countOL;
                        }
                        if (countCK != null) {
                            totalCK = 150000 * countCK;
                        }
                        if (countTM != null) {
                            totalTM = 150000 * countTM;
                        }
                        item3.setCountCK(countCK);
                        item3.setCountOL(countOL);
                        item3.setCountTM(countTM);
                        item3.setTotalCK(totalCK);
                        item3.setTotalOL(totalOL);
                        item3.setTotalTM(totalTM);
                    }
                    if (row[7] != null && !"".equals(row[7])) {
                        item3.setIndex(Long.parseLong(row[7].toString()));
                    }
                }
                result3.add(item3);
                item3 = new FeePaymentFileForm();
            }
        }
        spTM2 = checkCountTM;
        spCK2 = checkCountCK;
        spOL2 = checkCountOL;
        valueTM2 = 150000 * spTM2;
        valueCK2 = 150000 * spCK2;
        valueOL2 = 150000 * spOL2;
        total3 = spTM2 + spOL2 + spCK2;
        value3 = valueTM2 + valueCK2 + valueOL2;
        //reset checkCount chay tiep nhung lan sau
        spTM3 = spTM + spTM1 + spTM2;
        spCK3 = spCK + spCK1 + spCK2;
        spOL3 = spOL + spOL1 + spOL2;
        valueTM3 = valueTM + valueTM1 + valueTM2;
        valueCK3 = valueCK + valueCK1 + valueCK2;
        valueOL3 = valueOL + valueOL1 + valueOL2;
        total4 = total1 + total2 + total3;
        value4 = value1 + value2 + value3;
        data = result;
        data2 = result2;
        data3 = result3;

        if (data == null) {
            data = new ArrayList<FeePaymentFileForm>();
        }
        if (data2 == null) {
            data2 = new ArrayList<FeePaymentFileForm>();
        }
        if (data3 == null) {
            data3 = new ArrayList<FeePaymentFileForm>();
        }

        bean.put("header", header);
        bean.put("data", data);
        bean.put("data3", data3);
        bean.put("data2", data2);
        bean.put("dateFormat", dateFormat);
        bean.put("spTM", spTM);
        bean.put("spTM1", spTM1);
        bean.put("spTM2", spTM2);
        bean.put("spTM3", spTM3);
        bean.put("spCK", spCK);
        bean.put("spCK1", spCK1);
        bean.put("spCK2", spCK2);
        bean.put("spCK3", spCK3);
        bean.put("spOL", spOL);
        bean.put("spOL1", spOL1);
        bean.put("spOL2", spOL2);
        bean.put("spOL3", spOL3);
        bean.put("valueTM", valueTM);
        bean.put("valueTM1", valueTM1);
        bean.put("valueTM2", valueTM2);
        bean.put("valueTM3", valueTM3);
        bean.put("valueOL", valueOL);
        bean.put("valueOL1", valueOL1);
        bean.put("valueOL2", valueOL2);
        bean.put("valueOL3", valueOL3);
        bean.put("valueCK", valueCK);
        bean.put("valueCK1", valueCK1);
        bean.put("valueCK2", valueCK2);
        bean.put("valueCK3", valueCK3);
        bean.put("total1", total1);
        bean.put("total2", total2);
        bean.put("total3", total3);
        bean.put("total4", total4);
        bean.put("value1", value1);
        bean.put("value2", value2);
        bean.put("value3", value3);
        bean.put("value4", value4);
        ReportUtil.exportReport(getRequest(), bean, templateFile, getResponse());
    } catch (Exception ex) {
        LogUtil.addLog(ex);//binhnt sonar a160901
        //            log.error(e);
    }
}