Example usage for org.hibernate SQLQuery list

List of usage examples for org.hibernate SQLQuery 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.viettel.hqmc.DAO.ReportDAO.java

public void reportStaff() {
    try {/*from  w  w w  .j  ava2  s .  c  om*/
        String templateFile = "/WEB-INF/reportTemplate/reportStaff.xls";
        List<FilesNoClob> data;
        ConcurrentHashMap bean = new ConcurrentHashMap();
        String sql = "";
        SimpleDateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy");
        String header;
        List lstParam = new ArrayList();
        if (searchForm.getReceivedDate() != null) {
            header = "K t ngy: " + dateFormat.format(searchForm.getReceivedDate());
            if (searchForm.getReceivedDateTo() != null) {
                header = header + " - ?n ngy: " + dateFormat.format(searchForm.getReceivedDateTo());
            }
        } else if (searchForm.getReceivedDateTo() != null) {
            header = "K n ngy: " + dateFormat.format(searchForm.getReceivedDateTo());
        } else {
            header = "Thng k ton b";
        }

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

        if (searchForm.getReceivedDateTo() != null) {
            sql += " and arp.sign_date <= to_date( ?,'dd/MM/yyyy hh24:mi:ss') ";
            String param = "" + DateTimeUtils.convertDateToString(searchForm.getReceivedDateTo(), "dd/MM/yyyy")
                    + " 23:59:59";
            lstParam.add(param);
        }

        // hieptq update 160415
        if (searchForm.getProductTypeId() != null && searchForm.getProductTypeId() != -1l) {
            sql += " and f.product_type_id = ? ";
            Long param = searchForm.getProductTypeId();
            lstParam.add(param);
        }

        if (searchForm.getIs30() != null && searchForm.getIs30() == 1) {
            sql += " and f.is_30 = 1  ";
        }
        // thuc pham thuong
        SQLQuery query = (SQLQuery) getSession()
                .createSQLQuery("select f.business_name," + " f.file_code," + " f.business_address,"
                        + " b.business_telephone," + " f.product_name," + " f.product_type_name,"
                        + " f.nation_name," + " arp.receipt_no," + " arp.sign_date," + " dp.packate_material,"
                        + " dp.object_use," + " ROW_NUMBER() OVER (ORDER BY arp.sign_date) idx  from files f,"
                        + " announcement_receipt_paper arp," + " business b," + " detail_product dp"
                        + " where b.business_id = f.dept_id"
                        + " and f.announcement_receipt_paper_id = arp.announcement_receipt_paper_id"
                        + " and f.detail_product_id = dp.detail_product_id" + " and f.is_active = 1 " + sql
                        + " order by arp.sign_date ");

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

        // thuc pham thuong
        List lstResult = query.list();
        FilesForm item = new FilesForm();
        List result = new ArrayList<FilesForm>();
        if (lstResult != null && lstResult.size() > 0) {
            for (int i = 0; i < lstResult.size(); i++) {
                Object[] row = (Object[]) lstResult.get(i);
                if (row.length > 0) {
                    //businessName
                    if (row[0] != null && !"".equals(row[0])) {
                        String businessName = row[0].toString();
                        item.setBusinessName(businessName);
                    }
                    //fileCode
                    if (row[1] != null && !"".equals(row[1])) {
                        String fileCode = row[1].toString();
                        item.setFileCode(fileCode);
                    }
                    //busAnd
                    if (row[2] != null && !"".equals(row[2])) {
                        String businessAddress = row[2].toString();
                        item.setBusinessAddress(businessAddress);
                    }
                    // telephone
                    if (row[3] != null && !"".equals(row[3])) {
                        String telephone = row[3].toString();
                        item.setTelephone(telephone);
                    }
                    // productName
                    if (row[4] != null && !"".equals(row[4])) {
                        String productName = row[4].toString();
                        item.setProductName(productName);
                    }
                    // productTypeName
                    if (row[5] != null && !"".equals(row[5])) {
                        String productTypeName = row[5].toString();
                        item.setProductTypeName(productTypeName);
                    }
                    //nation
                    if (row[6] != null && !"".equals(row[6])) {
                        String nation = row[6].toString();
                        item.setNationName(nation);
                    }
                    // annoucementNo
                    if (row[7] != null && !"".equals(row[7])) {
                        String annoucementNo = row[7].toString();
                        item.setAnnoucementNo(annoucementNo);
                    }
                    // signdate
                    if (row[8] != null && !"".equals(row[8])) {
                        String signDate = row[8].toString();
                        item.setSignDateNew(signDate);
                    }
                    // packageMaterial
                    if (row[9] != null && !"".equals(row[9])) {
                        String pakageMaterial = row[9].toString();
                        item.setPackageMaterial(pakageMaterial);
                    }
                    // objectUse
                    if (row[10] != null && !"".equals(row[10])) {
                        String objectUse = row[10].toString();
                        item.setObjectUse(objectUse);
                    }
                    // index
                    if (row[11] != null && !"".equals(row[11])) {
                        Long index = Long.parseLong(row[11].toString());
                        item.setIndex(index);
                    }
                }
                result.add(item);
                item = new FilesForm();
            }
        }
        data = result;
        if (data == null) {
            data = new ArrayList<FilesNoClob>();
        }
        bean.put("header", header);
        bean.put("data", data);
        bean.put("dateFormat", dateFormat);
        DateTimeUtils dateUtil = new DateTimeUtils();
        bean.put("ConvertTime", dateUtil);
        ReportUtil.exportReport(getRequest(), bean, templateFile, getResponse());
        //            ReportUtil.exportReportSaveFileTemp(getRequest(), bean, templateFile);
    } catch (Exception ex) {
        LogUtil.addLog(ex);//binhnt sonar a160901
    }
}

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

public void reportStaffOnRequest() {
    try {//from  ww  w .j  a  v  a 2s  .  c om
        String templateFile = "/WEB-INF/reportTemplate/reportStaffOnRequest.xls";
        List<FilesNoClob> data;
        ConcurrentHashMap bean = new ConcurrentHashMap();
        String sql;
        SimpleDateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy");
        String header = "";
        List lstParam = new ArrayList();
        int check = 0;
        // co so cong bo, ngay ky, nguoi ky 
        if (searchForm.getSignerNameCheck() != null || searchForm.getAnnouncementNoCheck() != null
                || searchForm.getApproveDateFrom() != null || searchForm.getApproveDateTo() != null) {
            sql = "select distinct f.file_code,f.send_date,f.business_name,f.business_licence, f.product_type_name,f.nation_name,arp.SIGN_DATE,b.business_province\n"
                    + ",f.name_staff_process,f.file_type_name,f.display_status,b.business_address,arp.RECEIPT_NO,f.product_name,f.MANUFACTURE_NAME,arp.SIGNER_NAME "
                    + "from Files f, Process p, Detail_Product d, Business b , Announcement_Receipt_Paper arp \n"
                    + "where  f.detail_Product_Id = d.detail_Product_Id and f.file_Id = p.object_Id and f.dept_Id = b.business_Id \n"
                    + "and f.is_Active = 1 and p.receive_Group_Id = 3103 and (f.is_Temp is null or f.is_Temp = 0 ) and f.announcement_Receipt_Paper_Id = arp.announcement_Receipt_Paper_Id \n";
            if (searchForm.getApproveDateFrom() != null) {
                sql += " and arp.sign_date >= to_date( ? ,'dd/MM/yyyy hh24:mi:ss') ";
                String param = ""
                        + DateTimeUtils.convertDateToString(searchForm.getApproveDateFrom(), "dd/MM/yyyy")
                        + " 00:00:00";
                lstParam.add(param);
            }

            if (searchForm.getApproveDateTo() != null) {
                sql += " and arp.sign_date <= to_date( ?,'dd/MM/yyyy hh24:mi:ss') ";
                String param = ""
                        + DateTimeUtils.convertDateToString(searchForm.getApproveDateTo(), "dd/MM/yyyy")
                        + " 23:59:59";
                lstParam.add(param);
            }
            if (searchForm.getSendDateFrom() != null) {
                sql += " and f.send_date >= to_date( ? ,'dd/MM/yyyy hh24:mi:ss') ";
                String param = ""
                        + DateTimeUtils.convertDateToString(searchForm.getSendDateFrom(), "dd/MM/yyyy")
                        + " 00:00:00";
                lstParam.add(param);
            }

            if (searchForm.getSendDateTo() != null) {
                sql += " and f.send_date <= to_date( ?,'dd/MM/yyyy hh24:mi:ss') ";
                String param = "" + DateTimeUtils.convertDateToString(searchForm.getSendDateTo(), "dd/MM/yyyy")
                        + " 23:59:59";
                lstParam.add(param);
            }
            if (searchForm.getIs30() != null && searchForm.getIs30() == 1) {
                sql += " and f.is_30 = 1  ";
            }
            // hieptq update 190515
            //fileCode
            if (searchForm.getFileCode() != null && searchForm.getFileCode().length() > 0) {
                sql += " and lower(f.file_code) like ? ESCAPE '/'";
                lstParam.add(StringUtils.toLikeString(searchForm.getFileCode().toLowerCase().trim()));
            }
            //fileType
            if (searchForm.getFileType() != -1l) {
                sql += " and f.file_type = ? ";
                lstParam.add(searchForm.getFileType());
            }
            //tinh thanh pho
            if (searchForm.getBusinessProvinceId() != -1l) {
                sql += " and b.business_province_id = ? ";
                lstParam.add(searchForm.getBusinessProvinceId());
            }
            //trang thai
            if (searchForm.getStatus() != -1l) {
                sql += " and f.status = ? ";
                lstParam.add(searchForm.getStatus());
            }
            // businessName
            if (searchForm.getBusinessName() != null && searchForm.getBusinessName().length() > 0) {
                sql += " and lower(f.dept_name) like ? ESCAPE '/' ";
                lstParam.add(StringUtils.toLikeString(searchForm.getBusinessName().toLowerCase().trim()));
            }
            //bus address
            if (searchForm.getBusinessAddress() != null && searchForm.getBusinessAddress().length() > 0) {
                sql += " and lower(b.business_address) like ? ESCAPE '/' ";
                lstParam.add(StringUtils.toLikeString(searchForm.getBusinessAddress().toLowerCase().trim()));
            }
            // bus licence
            if (searchForm.getBusinessLicence() != null && searchForm.getBusinessLicence().length() > 0) {
                sql += " and lower(b.business_license) like ? ESCAPE '/' ";
                lstParam.add(StringUtils.toLikeString(searchForm.getBusinessLicence().toLowerCase().trim()));
            }
            //annoucementNo
            if (searchForm.getAnnoucementNo() != null && searchForm.getAnnoucementNo().length() > 0) {
                sql += " and lower(arp.receipt_no) like ? ESCAPE '/' ";
                lstParam.add(StringUtils.toLikeString(searchForm.getAnnoucementNo().toLowerCase().trim()));
            }
            // productTypeId
            if (searchForm.getProductTypeId() != -1l) {
                sql += " and f.product_type_id = ? ";
                lstParam.add(searchForm.getProductTypeId());
            }
            //    productName
            if (searchForm.getProductName() != null && searchForm.getProductName().length() > 0) {
                sql += " and lower(f.product_name) like ? ESCAPE '/' ";
                lstParam.add(StringUtils.toLikeString(searchForm.getProductName().toLowerCase().trim()));
            }
            // nationName
            if (searchForm.getNationName() != null && searchForm.getNationName().length() > 0) {
                sql += " and lower(f.nation_name) like ? ESCAPE '/' ";
                lstParam.add(StringUtils.toLikeString(searchForm.getNationName().toLowerCase().trim()));
            }
            //manufactureName
            if (searchForm.getManufactureName() != null && searchForm.getManufactureName().length() > 0) {
                sql += " and lower(f.manufacture_name) like ? ESCAPE '/' ";
                lstParam.add(StringUtils.toLikeString(searchForm.getManufactureName().toLowerCase().trim()));
            }
            //signerName
            if (searchForm.getSignerName() != null && searchForm.getSignerName().length() > 0) {
                sql += " and lower(arp.signer_name) like ? ESCAPE '/' ";
                lstParam.add(StringUtils.toLikeString(searchForm.getSignerName().toLowerCase().trim()));
            }
            //nameStaffProcess
            if (searchForm.getNameStaffProcess() != null && searchForm.getNameStaffProcess().length() > 0) {
                sql += " and lower(f.name_staff_process) like ? ESCAPE '/' ";
                lstParam.add(StringUtils.toLikeString(searchForm.getNameStaffProcess().toLowerCase().trim()));
            }

            sql += " order by f.send_Date ASC";
        } // khong co
        else {
            sql = "select distinct f.file_code,f.send_date,f.business_name,f.business_licence, f.product_type_name,f.nation_name,b.business_province\n"
                    + ",f.name_staff_process,f.file_type_name,f.display_status,b.business_address,f.product_name,f.MANUFACTURE_NAME  from Files f, Process p, Detail_Product d, Business b  where 1=1 \n"
                    + "and f.file_Id = p.object_Id and  f.detail_Product_Id = d.detail_Product_Id and f.dept_Id = b.business_Id \n"
                    + "and f.is_Active = 1 and f.file_Id = p.object_Id  and (f.is_Temp is null or f.is_Temp = 0 )\n"
                    + "and p.receive_Group_Id = 3103";
            if (searchForm.getSendDateFrom() != null) {
                sql += " and f.send_date >= to_date( ? ,'dd/MM/yyyy hh24:mi:ss') ";
                String param = ""
                        + DateTimeUtils.convertDateToString(searchForm.getSendDateFrom(), "dd/MM/yyyy")
                        + " 00:00:00";
                lstParam.add(param);
            }

            if (searchForm.getSendDateTo() != null) {
                sql += " and f.send_date <= to_date( ?,'dd/MM/yyyy hh24:mi:ss') ";
                String param = "" + DateTimeUtils.convertDateToString(searchForm.getSendDateTo(), "dd/MM/yyyy")
                        + " 23:59:59";
                lstParam.add(param);
            }

            if (searchForm.getIs30() != null && searchForm.getIs30() == 1) {
                sql += " and f.is_30 = 1  ";
            }

            //fileCode
            if (searchForm.getFileCode() != null && searchForm.getFileCode().length() > 0) {
                sql += " and lower(f.file_code) like ? ESCAPE '/'";
                lstParam.add(StringUtils.toLikeString(searchForm.getFileCode().toLowerCase().trim()));
            }
            //fileType
            if (searchForm.getFileType() != -1l) {
                sql += " and f.file_type = ? ";
                lstParam.add(searchForm.getFileType());
            }
            //tinh thanh pho
            if (searchForm.getBusinessProvinceId() != -1l) {
                sql += " and b.business_province_id = ? ";
                lstParam.add(searchForm.getBusinessProvinceId());
            }
            //trang thai
            if (searchForm.getStatus() != -1l) {
                sql += " and f.status = ? ";
                lstParam.add(searchForm.getStatus());
            }
            // businessName
            if (searchForm.getBusinessName() != null && searchForm.getBusinessName().length() > 0) {
                sql += " and lower(f.dept_name) like ? ESCAPE '/' ";
                lstParam.add(StringUtils.toLikeString(searchForm.getBusinessName().toLowerCase().trim()));
            }
            //bus address
            if (searchForm.getBusinessAddress() != null && searchForm.getBusinessAddress().length() > 0) {
                sql += " and lower(b.business_address) like ? ESCAPE '/' ";
                lstParam.add(StringUtils.toLikeString(searchForm.getBusinessAddress().toLowerCase().trim()));
            }
            // bus licence
            if (searchForm.getBusinessLicence() != null && searchForm.getBusinessLicence().length() > 0) {
                sql += " and lower(b.business_license) like ? ESCAPE '/' ";
                lstParam.add(StringUtils.toLikeString(searchForm.getBusinessLicence().toLowerCase().trim()));
            }
            //annoucementNo
            if (searchForm.getAnnoucementNo() != null && searchForm.getAnnoucementNo().length() > 0) {
                sql += " and lower(arp.receipt_no) like ? ESCAPE '/' ";
                lstParam.add(StringUtils.toLikeString(searchForm.getAnnoucementNo().toLowerCase().trim()));
            }
            // productTypeId
            if (searchForm.getProductTypeId() != -1l) {
                sql += " and f.product_type_id = ? ";
                lstParam.add(searchForm.getProductTypeId());
            }
            //    productName
            if (searchForm.getProductName() != null && searchForm.getProductName().length() > 0) {
                sql += " and lower(f.product_name) like ? ESCAPE '/' ";
                lstParam.add(StringUtils.toLikeString(searchForm.getProductName().toLowerCase().trim()));
            }
            // nationName
            if (searchForm.getNationName() != null && searchForm.getNationName().length() > 0) {
                sql += " and lower(f.nation_name) like ? ESCAPE '/' ";
                lstParam.add(StringUtils.toLikeString(searchForm.getNationName().toLowerCase().trim()));
            }
            //manufactureName
            if (searchForm.getManufactureName() != null && searchForm.getManufactureName().length() > 0) {
                sql += " and lower(f.manufacture_name) like ? ESCAPE '/' ";
                lstParam.add(StringUtils.toLikeString(searchForm.getManufactureName().toLowerCase().trim()));
            }
            //signerName
            if (searchForm.getSignerName() != null && searchForm.getSignerName().length() > 0) {
                sql += " and lower(arp.signer_name) like ? ESCAPE '/' ";
                lstParam.add(StringUtils.toLikeString(searchForm.getSignerName().toLowerCase().trim()));
            }
            //nameStaffProcess
            if (searchForm.getNameStaffProcess() != null && searchForm.getNameStaffProcess().length() > 0) {
                sql += " and lower(f.name_staff_process) like ? ESCAPE '/' ";
                lstParam.add(StringUtils.toLikeString(searchForm.getNameStaffProcess().toLowerCase().trim()));
            }

            sql += " order by f.send_Date ASC";
            check = 1;
        }

        SQLQuery query = (SQLQuery) getSession().createSQLQuery(sql);

        for (int i = 0; i < lstParam.size(); i++) {

            query.setParameter(i, lstParam.get(i));

        }

        List lstResult = query.list();
        FilesForm item = new FilesForm();
        List result = new ArrayList<FilesForm>();
        if (lstResult != null && lstResult.size() > 0) {
            for (int i = 0; i < lstResult.size(); i++) {
                Object[] row = (Object[]) lstResult.get(i);
                if (row.length > 0) {
                    if (check == 1) {
                        //fileCode
                        if (row[0] != null && !"".equals(row[0])) {
                            String fileCode = row[0].toString();
                            item.setFileCode(fileCode);
                        }
                        //sendDate
                        if (row[1] != null && !"".equals(row[1])) {
                            String sendDate = row[1].toString();
                            item.setSendDateNew(sendDate);
                        }
                        //businessName
                        if (row[2] != null && !"".equals(row[2])) {
                            String businessName = row[2].toString();
                            item.setBusinessName(businessName);
                        }

                        //buslicense
                        if (row[3] != null && !"".equals(row[3])) {
                            String businessLicense = row[3].toString();
                            item.setBusinessLicence(businessLicense);
                        }
                        //productTypeName
                        if (row[4] != null && !"".equals(row[4])) {
                            String productTypeName = row[4].toString();
                            item.setProductTypeName(productTypeName);
                        }
                        //nationName
                        if (row[5] != null && !"".equals(row[5])) {
                            String nationName = row[5].toString();
                            item.setNationName(nationName);
                        }

                        //bus province
                        if (row[6] != null && !"".equals(row[6])) {
                            String businessProvince = row[6].toString();
                            item.setBusinessProvince(businessProvince);
                        }
                        // name staff process
                        if (row[7] != null && !"".equals(row[7])) {
                            String nameStaftProcess = row[7].toString();
                            item.setNameStaffProcess(nameStaftProcess);
                        }
                        // fileTypeName
                        if (row[8] != null && !"".equals(row[8])) {
                            String fileTypeName = row[8].toString();
                            item.setFileTypeName(fileTypeName);
                        }
                        //display status
                        if (row[9] != null && !"".equals(row[9])) {
                            String displayStatus = row[9].toString();
                            item.setDisplayStatus(displayStatus);
                        }
                        //businessAddress
                        if (row[10] != null && !"".equals(row[10])) {
                            String businessAddress = row[10].toString();
                            item.setBusinessAddress(businessAddress);
                        }
                        // productName
                        if (row[11] != null && !"".equals(row[11])) {
                            String productName = row[11].toString();
                            item.setProductName(productName);
                        }
                        // manufactureName
                        if (row[12] != null && !"".equals(row[12])) {
                            String manufactureName = row[12].toString();
                            item.setManufactureName(manufactureName);
                        }
                    } else {
                        //fileCode
                        if (row[0] != null && !"".equals(row[0])) {
                            String fileCode = row[0].toString();
                            item.setFileCode(fileCode);
                        }
                        //sendDate
                        if (row[1] != null && !"".equals(row[1])) {
                            String sendDate = row[1].toString();
                            item.setSendDateNew(sendDate);
                        }
                        //businessName
                        if (row[2] != null && !"".equals(row[2])) {
                            String businessName = row[2].toString();
                            item.setBusinessName(businessName);
                        }

                        //buslicense
                        if (row[3] != null && !"".equals(row[3])) {
                            String businessLicense = row[3].toString();
                            item.setBusinessLicence(businessLicense);
                        }
                        //productTypeName
                        if (row[4] != null && !"".equals(row[4])) {
                            String productTypeName = row[4].toString();
                            item.setProductTypeName(productTypeName);
                        }
                        //nationName
                        if (row[5] != null && !"".equals(row[5])) {
                            String nationName = row[5].toString();
                            item.setNationName(nationName);
                        }
                        //signDateNew
                        if (row[6] != null && !"".equals(row[6])) {
                            String signDateNew = row[6].toString();
                            item.setSignDateNew(signDateNew);
                        }
                        //bus province
                        if (row[7] != null && !"".equals(row[7])) {
                            String businessProvince = row[7].toString();
                            item.setBusinessProvince(businessProvince);
                        }
                        // name staff process
                        if (row[8] != null && !"".equals(row[8])) {
                            String nameStaftProcess = row[8].toString();
                            item.setNameStaffProcess(nameStaftProcess);
                        }
                        // fileTypeName
                        if (row[9] != null && !"".equals(row[9])) {
                            String fileTypeName = row[9].toString();
                            item.setFileTypeName(fileTypeName);
                        }
                        //display status
                        if (row[10] != null && !"".equals(row[10])) {
                            String displayStatus = row[10].toString();
                            item.setDisplayStatus(displayStatus);
                        }
                        //businessAddress
                        if (row[11] != null && !"".equals(row[11])) {
                            String businessAddress = row[11].toString();
                            item.setBusinessAddress(businessAddress);
                        }
                        // receiptNo
                        if (row[12] != null && !"".equals(row[12])) {
                            String receiptNo = row[12].toString();
                            item.setReceiptNo(receiptNo);
                        }

                        // productName
                        if (row[13] != null && !"".equals(row[13])) {
                            String productName = row[13].toString();
                            item.setProductName(productName);
                        }
                        // manufactureName
                        if (row[14] != null && !"".equals(row[14])) {
                            String manufactureName = row[14].toString();
                            item.setManufactureName(manufactureName);
                        }
                        //signer
                        if (row[15] != null && !"".equals(row[15])) {
                            String signerName = row[15].toString();
                            item.setSignerName(signerName);
                        }
                    }
                    // index
                    //                        if (row[16] != null && !"".equals(row[16])) {
                    //                            Long index = Long.parseLong(row[16].toString());
                    //                            item.setIndex(index);
                    //                        }

                }
                result.add(item);
                item = new FilesForm();
            }
        }
        data = result;
        if (data == null) {
            data = new ArrayList<FilesNoClob>();
        }
        bean.put("header", header);
        bean.put("data", data);
        bean.put("dateFormat", dateFormat);
        DateTimeUtils dateUtil = new DateTimeUtils();
        bean.put("ConvertTime", dateUtil);
        String fileTemp = ReportUtil.exportReportSaveFileTemp(getRequest(), bean, templateFile);
        InputStream myxls = new FileInputStream(fileTemp);//get file excel
        Date newDate = new Date();
        //fix sonar
        //            ResourceBundle rb = ResourceBundle.getBundle("config");
        //            String filePath = rb.getString("report_excel_temp");

        //            String fullFilePath = filePath + "report_" + newDate.getTime() + ".xls";
        //            File file = new File(fullFilePath);
        //            FileOutputStream fop = new FileOutputStream(file);;
        HSSFWorkbook wb = new HSSFWorkbook(myxls);
        HSSFSheet sheet = wb.getSheetAt(0);
        // check hien thi cot
        if (searchForm.getSignerNameCheck() != null || searchForm.getAnnouncementNoCheck() != null
                || searchForm.getApproveDateFrom() != null || searchForm.getApproveDateTo() != null) {
            sheet.setColumnHidden((short) 0, true);
            if (searchForm.getFileCodeCheck() == null) {
                sheet.setColumnHidden((short) 1, true);
            }
            if (searchForm.getSendDateFrom() == null && searchForm.getSendDateTo() == null) {
                sheet.setColumnHidden((short) 2, true);
            }
            if (searchForm.getBusinessNameCheck() == null) {
                sheet.setColumnHidden((short) 3, true);
            }
            if (searchForm.getBusinessLicenceCheck() == null) {
                sheet.setColumnHidden((short) 4, true);
            }
            if (searchForm.getProductTypeNameCheck() == null) {
                sheet.setColumnHidden((short) 5, true);
            }
            if (searchForm.getNationNameCheck() == null) {
                sheet.setColumnHidden((short) 6, true);
            }
            if (searchForm.getApproveDateFrom() == null && searchForm.getApproveDateTo() == null) {
                sheet.setColumnHidden((short) 7, true);
            }
            if (searchForm.getBusinessProvinceCheck() == null) {
                sheet.setColumnHidden((short) 8, true);
            }
            if (searchForm.getNameStaffProcessCheck() == null) {
                sheet.setColumnHidden((short) 9, true);
            }
            if (searchForm.getFileTypeNameCheck() == null) {
                sheet.setColumnHidden((short) 10, true);
            }
            if (searchForm.getDisplayStatusCheck() == null) {
                sheet.setColumnHidden((short) 11, true);
            }
            if (searchForm.getBusinessAddressCheck() == null) {
                sheet.setColumnHidden((short) 12, true);
            }
            if (searchForm.getAnnouncementNoCheck() == null) {
                sheet.setColumnHidden((short) 13, true);
            }
            if (searchForm.getProductNameCheck() == null) {
                sheet.setColumnHidden((short) 14, true);
            }
            if (searchForm.getManufactureNameCheck() == null) {
                sheet.setColumnHidden((short) 15, true);
            }
            if (searchForm.getSignerNameCheck() == null) {
                sheet.setColumnHidden((short) 16, true);
            }

        } else {
            sheet.setColumnHidden((short) 0, true);
            sheet.setColumnHidden((short) 7, true);
            sheet.setColumnHidden((short) 13, true);
            sheet.setColumnHidden((short) 16, true);
            if (searchForm.getFileCodeCheck() == null) {
                sheet.setColumnHidden((short) 1, true);
            }
            if (searchForm.getSendDateFrom() == null && searchForm.getSendDateTo() == null) {
                sheet.setColumnHidden((short) 2, true);
            }
            if (searchForm.getBusinessNameCheck() == null) {
                sheet.setColumnHidden((short) 3, true);
            }
            if (searchForm.getBusinessLicenceCheck() == null) {
                sheet.setColumnHidden((short) 4, true);
            }
            if (searchForm.getProductTypeNameCheck() == null) {
                sheet.setColumnHidden((short) 5, true);
            }
            if (searchForm.getNationNameCheck() == null) {
                sheet.setColumnHidden((short) 6, true);
            }
            if (searchForm.getBusinessProvinceCheck() == null) {
                sheet.setColumnHidden((short) 8, true);
            }
            if (searchForm.getNameStaffProcessCheck() == null) {
                sheet.setColumnHidden((short) 9, true);
            }
            if (searchForm.getFileTypeNameCheck() == null) {
                sheet.setColumnHidden((short) 10, true);
            }
            if (searchForm.getDisplayStatusCheck() == null) {
                sheet.setColumnHidden((short) 11, true);
            }
            if (searchForm.getBusinessAddressCheck() == null) {
                sheet.setColumnHidden((short) 12, true);
            }
            if (searchForm.getProductNameCheck() == null) {
                sheet.setColumnHidden((short) 14, true);
            }
            if (searchForm.getManufactureNameCheck() == null) {
                sheet.setColumnHidden((short) 15, true);
            }
        }

        HttpServletResponse res = getResponse();
        res.setContentType("application/vnd.ms-excel");
        res.setHeader("Content-Disposition", "attachment; filename=report_" + newDate.getTime() + ".xls");
        res.setHeader("Content-Type", "application/vnd.ms-excel");
        wb.write(res.getOutputStream());
        res.getOutputStream().flush();
        //fop.close();
    } catch (Exception ex) {
        LogUtil.addLog(ex);//binhnt sonar a160901
        //            log.error(e);
    }
}

From source file:com.viettel.hqmc.DAOHE.FeeDAOHE.java

public GridResult getLstPayment(FeeForm searchFeeForm, Long fileId, int start, int count, String sortField) {

    FilesDAOHE fdhe = new FilesDAOHE();
    Files filesBo = fdhe.findById(fileId);
    String sql;// ww  w.j  a va  2 s .  co m
    if (filesBo != null
            && (filesBo.getStatus().equals(Constants.FILE_STATUS.APPROVED)
                    || filesBo.getStatus().equals(Constants.FILE_STATUS.COMPARED)
                    || filesBo.getStatus().equals(Constants.FILE_STATUS.COMPARED_FAIL)
                    || filesBo.getStatus().equals(Constants.FILE_STATUS.ALERT_COMPARISON))
            && filesBo.getIsSignPdf() != null && (filesBo.getIsSignPdf() != 0)) {
        sql = "from fee f inner join fee_payment_info fpi " + "on f.fee_id = fpi.fee_id "
                + "where fpi.file_id = ? " + "and f.is_Active=1 " + "and fpi.is_Active=1";
    } else if (filesBo != null && filesBo.getStatus().equals(Constants.FILE_STATUS.GIVE_BACK)
            && filesBo.getIsSignPdf() != null && (filesBo.getIsSignPdf() != 0)) {
        sql = "from fee f inner join fee_payment_info fpi on f.fee_id = fpi.fee_id " + "where fpi.file_id = ? "
                + "and f.is_Active=1 " + "and fpi.is_Active=1 " + "and (f.fee_type = 2 or f.fee_type = 1)";
    } else {
        sql = "from fee f inner join fee_payment_info fpi on f.fee_id = fpi.fee_id " + "where fpi.file_id = ? "
                + "and f.is_Active=1 " + "and fpi.is_Active=1 and f.fee_type = 2";
    }

    List lstParam = new ArrayList();
    lstParam.add(fileId);

    if (searchFeeForm.getFeeName() != null && !"".equals(searchFeeForm.getFeeName().trim())) {
        sql += "and  f.fee_name like ? ";
        String param = "%" + searchFeeForm.getFeeName() + "%";
        lstParam.add(param);
    }
    if (searchFeeForm.getPrice() != null && !"".equals(searchFeeForm.getPrice())) {
        sql += " and f.price = ?";
        lstParam.add(searchFeeForm.getPrice());
    }
    if (searchFeeForm.getFeeType() != null && searchFeeForm.getFeeType() != -1) {
        sql += " and f.fee_type = ?";
        lstParam.add(searchFeeForm.getFeeType());
    }
    if (searchFeeForm.getStatus() != null && searchFeeForm.getStatus() != -1) {
        sql += " and fpi.status = ?";
        lstParam.add(searchFeeForm.getStatus());
    }
    SQLQuery countQuery = (SQLQuery) getSession().createSQLQuery("select count(*) " + sql);
    SQLQuery query = (SQLQuery) getSession().createSQLQuery("select f.fee_Id," + "f.fee_Name,"
            + "f.description," + "fpi.cost," + "f.fee_Type," + "fpi.status," + "fpi.fee_Payment_Type_Id, "
            + "f.price," + "fpi.payment_Person," + "fpi.payment_Date," + "fpi.payment_Info," + "fpi.bill_path,"
            + "fpi.payment_info_id," + "fpi.payment_code," + "fpi.payment_confirm," + "fpi.bill_code,"
            + "fpi.date_confirm," + "fpi.comment_reject  " + sql);
    for (int i = 0; i < lstParam.size(); i++) {
        countQuery.setParameter(i, lstParam.get(i));
        query.setParameter(i, lstParam.get(i));
    }

    query.setFirstResult(start);
    query.setMaxResults(count);
    int total = Integer.parseInt(countQuery.uniqueResult().toString());
    List lstResult = query.list();
    FeePaymentFileForm item = new FeePaymentFileForm();
    List result = new ArrayList<FeePaymentFileForm>();

    //Hiepvv 4Star
    boolean isHaveFee = false;
    if (filesBo != null && filesBo.getFileType() != null && filesBo.getFileType() > 0L) {
        ProcedureDAOHE pDAO = new ProcedureDAOHE();
        Procedure p = pDAO.findById(filesBo.getFileType());
        if (p != null) {
            if (p.getDescription() != null
                    && p.getDescription().equals(Constants.FILE_DESCRIPTION.ANNOUNCEMENT_4STAR)) {
                isHaveFee = true;
            }
            if (p.getDescription() != null
                    && p.getDescription().equals(Constants.FILE_DESCRIPTION.ANNOUNCEMENT_FILE05)) {
                isHaveFee = true;
            }
        }
    }
    for (int i = 0; i < lstResult.size(); i++) {
        Object[] row = (Object[]) lstResult.get(i);
        if (row.length > 0) {
            if (row[0] != null && !"".equals(row[0])) {
                item.setFeeId(Long.parseLong(row[0].toString()));
            }
            if (row[1] != null && !"".equals(row[1])) {
                item.setFeeName(row[1].toString());
            }
            if (row[2] != null && !"".equals(row[2])) {
                item.setDescription(row[2].toString());
            }
            //Hiepvv 4Star
            if (isHaveFee) {
                if (row[3] != null && !"".equals(row[3])) {
                    item.setPrice(Long.parseLong(row[3].toString()));
                }
            } else if (row[7] != null && !"".equals(row[7])) {
                item.setPrice(Long.parseLong(row[7].toString()));
            }
            if (row[4] != null && !"".equals(row[4])) {
                item.setFeeType(Long.parseLong(row[4].toString()));
            }
            if (row[5] != null && !"".equals(row[5])) {
                item.setStatus(Long.parseLong(row[5].toString()));
            }
            if (row[6] != null && !"".equals(row[6])) {
                item.setFeePaymentType(Long.parseLong(row[6].toString()));
            }
            if (row[8] != null && !"".equals(row[8])) {
                item.setPaymentPerson(row[8].toString());
            }
            if (row[9] != null && !"".equals(row[9])) {
                item.setPaymentDate(row[9].toString());
            }
            if (row[10] != null && !"".equals(row[10])) {
                item.setPaymentInfo(row[10].toString());
            }
            if (row[11] != null && !"".equals(row[11])) {
                item.setBillPath(row[11].toString());
            }
            if (row[12] != null && !"".equals(row[12])) {
                item.setPaymentInfoId(Long.parseLong(row[12].toString()));
            }
            if (row[13] != null && !"".equals(row[13])) {
                item.setPaymentCode(row[13].toString());
            }
            if (row[14] != null && !"".equals(row[14])) {
                item.setPaymentConfirm(row[14].toString());
            }
            if (row[15] != null && !"".equals(row[15])) {
                item.setBillCode(row[15].toString());
            }
            if (row[16] != null && !"".equals(row[16])) {
                Date confirmDate = (Date) row[16];
                item.setDateConfirm(DateTimeUtils.convertDateToString(confirmDate, "dd/MM/yyyy"));
            }
            if (row[17] != null && !"".equals(row[17])) {
                item.setCommentReject(row[17].toString());
            }
        }
        result.add(item);
        item = new FeePaymentFileForm();
    }
    GridResult gr = new GridResult(total, result);
    return gr;
}

From source file:com.viettel.hqmc.DAOHE.FeeDAOHE.java

public List getLstFpiId(String lstObjectId) {
    //        FilesDAOHE fdhe = new FilesDAOHE();
    List lstParam = new ArrayList();
    String[] lstObjectIdSplit = lstObjectId.split(",");
    int countObj = lstObjectIdSplit.length;
    String sql = "from fee f inner join fee_payment_info fpi on f.fee_id = fpi.fee_id " + "where f.is_Active=1 "
            + "and fpi.is_Active=1 and f.fee_type = 2 and (";

    if (countObj > 0) {
        for (int i = 0; i < countObj; i++) {
            if (i == countObj - 1) {
                sql += " fpi.file_id = ? )";
            } else {
                sql += " fpi.file_id = ? or ";
            }/*ww  w  . j  a v  a 2 s. com*/
            lstParam.add(lstObjectIdSplit[i]);
        }
    }

    SQLQuery query = (SQLQuery) getSession().createSQLQuery("select fpi.payment_info_id " + sql);
    for (int i = 0; i < lstParam.size(); i++) {
        query.setParameter(i, lstParam.get(i));
    }
    List lstResult = query.list();
    return lstResult;
}

From source file:com.viettel.hqmc.DAOHE.FeeDAOHE.java

public Long getAmountKeyPay(String lstObjectId) {
    //        FilesDAOHE fdhe = new FilesDAOHE();
    List lstParam = new ArrayList();
    String[] lstObjectIdSplit = lstObjectId.split(",");
    int countObj = lstObjectIdSplit.length;
    Long amount = 0l;/*from   www.j a  v a  2  s  .  c  o m*/
    String sql = "from fee f inner join fee_payment_info fpi on f.fee_id = fpi.fee_id " + "where f.is_Active=1 "
            + "and fpi.is_Active=1 and f.fee_type = 2 and (";

    if (countObj > 0) {
        for (int i = 0; i < countObj; i++) {
            if (i == countObj - 1) {
                sql += " fpi.file_id = ? )";
            } else {
                sql += " fpi.file_id = ? or ";
            }
            lstParam.add(lstObjectIdSplit[i]);
        }
    }

    SQLQuery query = (SQLQuery) getSession().createSQLQuery("select fpi.cost " + sql);
    for (int i = 0; i < lstParam.size(); i++) {
        query.setParameter(i, lstParam.get(i));
    }
    List lstResult = query.list();
    for (int i = 0; i < lstResult.size(); i++) {
        amount += Long.parseLong(lstResult.get(i).toString());
    }
    return amount;
}

From source file:com.viettel.hqmc.DAOHE.FeeDAOHE.java

/**
 *
 * @param searchFeeFormNew/* w  w w .  j  av  a  2  s  .  c om*/
 * @param userId
 * @param start
 * @param count
 * @param sortField
 * @return
 */
public GridResult getLstFeeManage(FeePaymentFileForm searchFeeFormNew, Long userId, int start, int count,
        String sortField) {

    UsersDAOHE udhe = new UsersDAOHE();
    Users user = udhe.findById(userId);

    String sql = "from files f inner join fee_payment_info fpi on f.file_id = fpi.file_id "
            + "where fpi.fee_id in (select f.fee_id from fee f "
            + "                         where f.fee_type = 1 and f.is_active = 1 ) "
            //+ "and f.agency_id =" + user.getDeptId() + " " // comment 11/11/2014 viethd
            + "and f.agency_id = ? "
            + "and (f.status = 15 or f.status=16 or f.status=6 or f.status=22 or f.status = 23) "
            //+ "and (f.status=6) "
            + "and fpi.is_active=1 " + "and f.is_active = 1 ";
    //+ "and fpi.status <> 0 ";

    List lstParam = new ArrayList();
    lstParam.add(user.getDeptId());

    if (searchFeeFormNew.getFileCode() != null && !"".equals(searchFeeFormNew.getFileCode().trim())) {
        //sql += " and lower(f.file_code) like '%" + searchFeeFormNew.getFileCode().toLowerCase().trim().replace("/", "//").replace("_", "/_").replace("%", "/%") + "%'"; // comment 11/11/2014 viethd
        sql += " and lower(f.file_code) like ? ";
        String param = "%" + searchFeeFormNew.getFileCode().toLowerCase().trim() + "%";
        lstParam.add(param);
    }
    if (searchFeeFormNew.getFilesCode() != null && !"".equals(searchFeeFormNew.getFilesCode())) {
        // comment 11/11/2014 viethd
        sql += " and lower(fpi.files_code) like ? ";
        String param = "%" + searchFeeFormNew.getFilesCode().toString().toLowerCase().trim() + "%";
        lstParam.add(param);
        //lstParam.add(searchFeeFormNew.getFilesCode());
    }
    if (searchFeeFormNew.getProductName() != null && !"".equals(searchFeeFormNew.getProductName().trim())) {
        //sql += " and lower(f.product_name) like '%" + searchFeeFormNew.getProductName().toLowerCase().trim().replace("/", "//").replace("_", "/_").replace("%", "/%") + "%'"; // comment 11/1/2014 viethd
        sql += " and lower(f.product_name) like ? ";
        String param = "%" + searchFeeFormNew.getProductName().toLowerCase().trim() + "%";
        lstParam.add(param);
    }
    if (searchFeeFormNew.getCost() != null && !"".equals(searchFeeFormNew.getCost())) {
        //sql += " and lower(fpi.cost) like '%" + searchFeeFormNew.getCost().toString().toLowerCase().trim().replace("/", "//").replace("_", "/_").replace("%", "/%") + "%'";
        sql += " and lower(fpi.cost) like ? ";
        String param = "%" + searchFeeFormNew.getCost().toString().toLowerCase().trim() + "%";
        lstParam.add(param);
    }
    if (searchFeeFormNew.getStatus() != null && searchFeeFormNew.getStatus() != -1) {
        if (searchFeeFormNew.getSearchType() == null) {
            if (searchFeeFormNew.getStatus() == 0) {
                sql += " and fpi.status > 2";
            } else {
                //sql += "and fpi.status = " + searchFeeFormNew.getStatus(); // 11/11/2014 viethd
                sql += "and fpi.status = ? ";
                lstParam.add(searchFeeFormNew.getStatus());
            }
        }
    }

    if (searchFeeFormNew.getBusinessName() != null && !"".equals(searchFeeFormNew.getBusinessName().trim())) {
        //sql += " and lower(f.business_name) like '%" + searchFeeFormNew.getBusinessName().toLowerCase().trim().replace("/", "//").replace("_", "/_").replace("%", "/%") + "%'";
        sql += " and lower(f.business_name) like ? ";
        String param = "%" + searchFeeFormNew.getBusinessName().toLowerCase().trim() + "%";
        lstParam.add(param);
    }
    if (searchFeeFormNew.getFeePaymentType() != null && searchFeeFormNew.getFeePaymentType() != -1) {
        // 11/11/2014 viethd
        //sql += " and fpi.fee_Payment_type_id = " + searchFeeFormNew.getFeePaymentType();
        sql += " and fpi.fee_Payment_type_id = ? ";
        lstParam.add(searchFeeFormNew.getFeePaymentType());

    }
    if (searchFeeFormNew.getPaymentConfirm() != null
            && !"".equals(searchFeeFormNew.getPaymentConfirm().trim())) {
        // comment 11/11/2014 viethd
        //sql += " and lower(f.product_name) like '%" + searchFeeFormNew.getProductName().toLowerCase().trim().replace("/", "//").replace("_", "/_").replace("%", "/%") + "%'";
        sql += " and lower(fpi.payment_confirm) like ? ";
        String param = "%" + searchFeeFormNew.getPaymentConfirm().toLowerCase().trim() + "%";
        lstParam.add(param);
    }

    if (searchFeeFormNew.getDateFrom() != null) {
        // 11/11/2014 viethd
        //sql += " and fpi.payment_date >= to_date('" + DateTimeUtils.convertDateToString(searchFeeFormNew.getDateFrom(), "dd/MM/yyyy") + " 00:00:00','dd/MM/yyyy hh24:mi:ss') ";
        sql += " and fpi.payment_date >= to_date( ? ,'dd/MM/yyyy hh24:mi:ss') ";
        String param = "" + DateTimeUtils.convertDateToString(searchFeeFormNew.getDateFrom(), "dd/MM/yyyy")
                + " 00:00:00";
        lstParam.add(param);
    }
    if (searchFeeFormNew.getDateTo() != null) {
        // 11/11/2014 viethd
        //sql += " and fpi.payment_date <= to_date('" + DateTimeUtils.convertDateToString(searchFeeFormNew.getDateTo(), "dd/MM/yyyy") + " 23:59:59','dd/MM/yyyy hh24:mi:ss') ";
        sql += " and fpi.payment_date <= to_date( ?,'dd/MM/yyyy hh24:mi:ss') ";
        String param = "" + DateTimeUtils.convertDateToString(searchFeeFormNew.getDateTo(), "dd/MM/yyyy")
                + " 23:59:59";
        lstParam.add(param);
    }
    // ngay xac nhan
    if (searchFeeFormNew.getDateConfirmSearchFrom() != null) {
        // 11/11/2014 viethd
        //sql += " and fpi.date_confirm >= to_date('" + DateTimeUtils.convertDateToString(searchFeeFormNew.getDateConfirmSearchFrom(), "dd/MM/yyyy") + " 00:00:00','dd/MM/yyyy hh24:mi:ss') ";
        sql += " and fpi.date_confirm >= to_date( ?,'dd/MM/yyyy hh24:mi:ss') ";
        String param = ""
                + DateTimeUtils.convertDateToString(searchFeeFormNew.getDateConfirmSearchFrom(), "dd/MM/yyyy")
                + " 00:00:00";
        lstParam.add(param);
    }
    if (searchFeeFormNew.getDateConfirmSearchTo() != null) {
        // 11/11/2014 viethd
        //sql += " and fpi.date_confirm <= to_date('" + DateTimeUtils.convertDateToString(searchFeeFormNew.getDateConfirmSearchTo(), "dd/MM/yyyy") + " 23:59:59','dd/MM/yyyy hh24:mi:ss') ";
        sql += " and fpi.date_confirm <= to_date( ?,'dd/MM/yyyy hh24:mi:ss') ";
        String param = ""
                + DateTimeUtils.convertDateToString(searchFeeFormNew.getDateConfirmSearchTo(), "dd/MM/yyyy")
                + " 23:59:59";
        lstParam.add(param);
    }

    if (searchFeeFormNew.getSearchType() != null && searchFeeFormNew.getSearchType() == 5) {
        sql += " and fpi.status = 1 ";
    }
    if (searchFeeFormNew.getSearchType() != null && searchFeeFormNew.getSearchType() == 6) {
        sql += " and fpi.status > 2 ";
    }

    //sql += " order by fpi.payment_date desc ";
    //sql += " order by fpi.payment_date asc ";
    sql += " order by f.send_date asc ";
    SQLQuery countQuery = (SQLQuery) getSession()
            .createSQLQuery("select count (distinct fpi.payment_info_id) " + sql);
    SQLQuery query = (SQLQuery) getSession().createSQLQuery("select distinct f.file_code,"
            + "f.product_name,fpi.payment_date,fpi.cost,fpi.bill_path,"
            + "fpi.fee_payment_type_id,fpi.status,fpi.fee_id,fpi.file_id,fpi.payment_info_id,"
            + "fpi.payment_person,f.business_name,fpi.payment_code,fpi.payment_confirm,"
            + "fpi.bill_code,fpi.date_confirm,fpi.comment_reject,f.business_address,f.send_date,fpi.files_code "
            + sql);
    query.setFirstResult(start);
    query.setMaxResults(count);
    int paramSize = lstParam.size();
    for (int i = 0; i < paramSize; i++) {
        countQuery.setParameter(i, lstParam.get(i));
        query.setParameter(i, lstParam.get(i));
    }
    int total = Integer.parseInt(countQuery.uniqueResult().toString());
    List lstResult = query.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) {
                if (row[0] != null && !"".equals(row[0])) {
                    item.setFileCode(row[0].toString());
                }
                if (row[1] != null && !"".equals(row[1])) {
                    item.setProductName(row[1].toString());
                }
                if (row[2] != null && !"".equals(row[2])) {
                    Date paymentDate = (Date) row[2];
                    item.setPaymentDate(DateTimeUtils.convertDateToString(paymentDate, "dd/MM/yyyy"));
                }
                if (row[3] != null && !"".equals(row[3])) {
                    item.setCost(Long.parseLong(row[3].toString()));
                }
                if (row[7] != null && !"".equals(row[7])) {
                    item.setFeeId(Long.parseLong(row[7].toString()));
                }
                if (row[4] != null && !"".equals(row[4])) {
                    item.setBillPath(row[4].toString());
                }
                if (row[5] != null && !"".equals(row[5])) {
                    item.setFeePaymentType(Long.parseLong(row[5].toString()));
                }
                if (row[6] != null && !"".equals(row[6])) {
                    item.setStatus(Long.parseLong(row[6].toString()));
                }
                if (row[8] != null && !"".equals(row[8])) {
                    item.setFileId(Long.parseLong(row[8].toString()));
                }
                if (row[9] != null && !"".equals(row[9])) {
                    item.setPaymentInfoId(Long.parseLong(row[9].toString()));
                }
                if (row[10] != null && !"".equals(row[10])) {
                    item.setPaymentPerson((row[10].toString()));
                }
                if (row[11] != null && !"".equals(row[11])) {
                    item.setBusinessName((row[11].toString()));
                }
                if (row[12] != null && !"".equals(row[12])) {
                    item.setPaymentCode(((row[12].toString())));
                }
                if (row[13] != null && !"".equals(row[13])) {
                    item.setPaymentConfirm((row[13].toString()));
                }
                if (row[14] != null && !"".equals(row[14])) {
                    item.setBillCode((row[14].toString()));
                }
                if (row[15] != null && !"".equals(row[15])) {
                    Date confirmDate = (Date) row[15];
                    item.setDateConfirm(DateTimeUtils.convertDateToString(confirmDate, "dd/MM/yyyy"));
                }
                if (row[16] != null && !"".equals(row[16])) {
                    item.setCommentReject(row[16].toString());
                }
                if (row[17] != null && !"".equals(row[17])) {
                    item.setBusinessAddress(row[17].toString());
                }
                if (row[19] != null && !"".equals(row[19])) {
                    item.setBusinessAddress(row[19].toString());
                }

            }
            result.add(item);
            item = new FeePaymentFileForm();
        }
    }
    GridResult gr = new GridResult(total, result);
    return gr;
}

From source file:com.viettel.hqmc.DAOHE.FeeDAOHE.java

/**
 *
 * @param searchFeeFormNew//w  ww  . j a  v a 2s . co  m
 * @param userId
 * @param start
 * @param count
 * @param sortField
 * @return
 */
public GridResult getLstFeePayManage(FeePaymentFileForm searchFeeFormNew, Long userId, int start, int count,
        String sortField) {
    String sql = "from files f inner join fee_payment_info fpi on f.file_id = fpi.file_id "
            + "where fpi.fee_id in (select f.fee_id from fee f where f.fee_type = 2 and f.is_active = 1 ) "
            + "and fpi.is_active = 1 " + "and ((f.is_active = 1) or (f.is_active=2))  "
            //+ "and f.agency_id = ? "
            + "and (f.user_Signed is not null or f.status = 18) " + "and f.status <> -1";
    UsersDAOHE udhe = new UsersDAOHE();
    //        Users user = udhe.findById(userId);
    List lstParam = new ArrayList();
    //        lstParam.add(user.getDeptId());
    if (searchFeeFormNew.getBusinessName() != null && !"".equals(searchFeeFormNew.getBusinessName().trim())) {
        // comment 11/11/2014 viethd
        //sql += " and lower(f.business_name) like '%" + searchFeeFormNew.getBusinessName().toLowerCase().trim().replace("/", "//").replace("_", "/_").replace("%", "/%") + "%'";
        sql += " and lower(f.business_name) like ? ";
        String param = "%" + searchFeeFormNew.getBusinessName().toLowerCase().trim() + "%";
        lstParam.add(param);
    }
    if (searchFeeFormNew.getFileCode() != null && !"".equals(searchFeeFormNew.getFileCode().trim())) {
        // comment 11/11/2014 viethd
        //sql += " and lower(f.file_code) like '%" + searchFeeFormNew.getFileCode().toLowerCase().trim().replace("/", "//").replace("_", "/_").replace("%", "/%") + "%'";
        sql += " and lower(f.file_code) like ? ";
        String param = "%" + searchFeeFormNew.getFileCode().toLowerCase().trim() + "%";
        lstParam.add(param);
    }
    if (searchFeeFormNew.getProductName() != null && !"".equals(searchFeeFormNew.getProductName().trim())) {
        // comment 11/11/2014 viethd
        //sql += " and lower(f.product_name) like '%" + searchFeeFormNew.getProductName().toLowerCase().trim().replace("/", "//").replace("_", "/_").replace("%", "/%") + "%'";
        sql += " and lower(f.product_name) like ? ";
        String param = "%" + searchFeeFormNew.getProductName().toLowerCase().trim() + "%";
        lstParam.add(param);
    }
    if (searchFeeFormNew.getPaymentConfirm() != null
            && !"".equals(searchFeeFormNew.getPaymentConfirm().trim())) {
        sql += " and lower(fpi.payment_confirm) like ? ";
        String param = "%" + searchFeeFormNew.getPaymentConfirm().toLowerCase().trim() + "%";
        lstParam.add(param);
    }

    if (searchFeeFormNew.getCost() != null && !"".equals(searchFeeFormNew.getCost())) {
        // comment 11/11/2014 viethd
        //sql += " and lower(fpi.cost) like '%" + searchFeeFormNew.getCost().toString().toLowerCase().trim().replace("/", "//").replace("_", "/_").replace("%", "/%") + "%'";
        sql += " and lower(fpi.cost) like ? ";
        String param = "%" + searchFeeFormNew.getCost().toString().toLowerCase().trim() + "%";
        lstParam.add(param);
    }
    if (searchFeeFormNew.getFilesCode() != null && !"".equals(searchFeeFormNew.getFilesCode())) {
        // comment 11/11/2014 viethd
        sql += " and lower(fpi.files_code) like ? ";
        String param = "%" + searchFeeFormNew.getFilesCode().toString().toLowerCase().trim() + "%";
        lstParam.add(param);
        //lstParam.add(searchFeeFormNew.getFilesCode());
    }

    if (searchFeeFormNew.getStatus() != null && searchFeeFormNew.getStatus() != -1) {
        if (searchFeeFormNew.getSearchType() == null) {
            if (searchFeeFormNew.getStatus() == 0) {
                sql += " and fpi.status > 2";
            } else {
                //sql += "and fpi.status = " + searchFeeFormNew.getStatus(); // 11/11/2014 viethd
                sql += "and fpi.status = ? ";
                lstParam.add(searchFeeFormNew.getStatus());
            }
        }
    }
    if (searchFeeFormNew.getFeePaymentType() != null && searchFeeFormNew.getFeePaymentType() != -1) {
        // 11/11/2014 viethd
        //sql += " and fpi.fee_Payment_type_id = " + searchFeeFormNew.getFeePaymentType() + " ";
        sql += " and fpi.fee_Payment_type_id = ? ";
        lstParam.add(searchFeeFormNew.getFeePaymentType());
    }
    if (searchFeeFormNew.getDateFrom() != null) {
        // 11/11/2014 viethd
        //sql += " and fpi.payment_date >= to_date('" + DateTimeUtils.convertDateToString(searchFeeFormNew.getDateFrom(), "dd/MM/yyyy") + " 00:00:00','dd/MM/yyyy hh24:mi:ss') ";
        sql += " and fpi.payment_date >= to_date(?, 'dd/MM/yyyy hh24:mi:ss') ";
        lstParam.add("" + DateTimeUtils.convertDateToString(searchFeeFormNew.getDateFrom(), "dd/MM/yyyy")
                + " 00:00:00");
    }
    if (searchFeeFormNew.getDateTo() != null) {
        // 11/11/2014 viethd
        //sql += " and fpi.payment_date <= to_date('" + DateTimeUtils.convertDateToString(searchFeeFormNew.getDateTo(), "dd/MM/yyyy") + " 23:59:59','dd/MM/yyyy hh24:mi:ss') ";
        sql += " and fpi.payment_date <= to_date(?, 'dd/MM/yyyy hh24:mi:ss') ";
        lstParam.add("" + DateTimeUtils.convertDateToString(searchFeeFormNew.getDateTo(), "dd/MM/yyyy")
                + " 23:59:59");
    }
    // ngay xac nhan
    if (searchFeeFormNew.getDateConfirmSearchFrom() != null) {
        // 11/11/2014 viethd
        //sql += " and fpi.date_confirm >= to_date('" + DateTimeUtils.convertDateToString(searchFeeFormNew.getDateConfirmSearchFrom(), "dd/MM/yyyy") + " 00:00:00','dd/MM/yyyy hh24:mi:ss') ";
        sql += " and fpi.date_confirm >= to_date(?, 'dd/MM/yyyy hh24:mi:ss') ";
        lstParam.add(""
                + DateTimeUtils.convertDateToString(searchFeeFormNew.getDateConfirmSearchFrom(), "dd/MM/yyyy")
                + " 00:00:00");
    }
    if (searchFeeFormNew.getDateConfirmSearchTo() != null) {
        // 11/11/2014 viethd
        //sql += " and fpi.date_confirm <= to_date('" + DateTimeUtils.convertDateToString(searchFeeFormNew.getDateConfirmSearchTo(), "dd/MM/yyyy") + " 23:59:59','dd/MM/yyyy hh24:mi:ss') ";
        sql += " and fpi.date_confirm <= to_date(?, 'dd/MM/yyyy hh24:mi:ss') ";
        lstParam.add(
                "" + DateTimeUtils.convertDateToString(searchFeeFormNew.getDateConfirmSearchTo(), "dd/MM/yyyy")
                        + " 23:59:59");
    }
    // hieptq them nhom san pham 17.11.14
    if (searchFeeFormNew.getProductType() != null && searchFeeFormNew.getProductType() != -1) {
        if (searchFeeFormNew.getProductType() == 1) {
            sql += " and fpi.cost = 1500000 ";
        } else {
            sql += " and fpi.cost = 500000 ";
        }
    }

    //hieptq update searchType
    //searchType = 1 searchFeeFormNew.status=" + 1 + "&searchFeeFormNew.productType=" + 2 + "&searchFeeFormNew.feePaymentType=-1
    if (searchFeeFormNew.getSearchType() != null && searchFeeFormNew.getSearchType() == 1) {
        sql += " and fpi.status = 1 and fpi.cost = 500000 ";
    }
    //searchType = 2 searchFeeFormNew.status=" + 0 + "&searchFeeFormNew.productType=" + 2 + "&searchFeeFormNew.feePaymentType=-1
    if (searchFeeFormNew.getSearchType() != null && searchFeeFormNew.getSearchType() == 2) {
        sql += " and fpi.status > 2 and fpi.cost = 500000 ";
    }
    //searchType = 3 searchFeeFormNew.status=" + 1 + "&searchFeeFormNew.productType=" + 1 + "&searchFeeFormNew.feePaymentType=-1
    if (searchFeeFormNew.getSearchType() != null && searchFeeFormNew.getSearchType() == 3) {
        sql += " and fpi.status = 1 and fpi.cost = 1500000 ";
    }
    //searchType = 4 searchFeeFormNew.status=" + 0 + "&searchFeeFormNew.productType=" + 1 + "&searchFeeFormNew.feePaymentType=-1
    if (searchFeeFormNew.getSearchType() != null && searchFeeFormNew.getSearchType() == 4) {
        sql += " and fpi.status > 2 and fpi.cost = 1500000 ";
    }
    //sql += " order by fpi.payment_date desc ";
    //sql += " order by fpi.payment_date asc ";
    sql += " order by f.send_date asc ";
    SQLQuery countQuery = (SQLQuery) getSession()
            .createSQLQuery("select count (distinct fpi.payment_info_id) " + sql);
    SQLQuery query = (SQLQuery) getSession().createSQLQuery("select distinct f.business_name,f.product_name,"
            + "fpi.payment_date,fpi.cost,fpi.bill_path,fpi.fee_payment_type_id,fpi.status,"
            + "fpi.fee_id,fpi.file_id,fpi.payment_info_id,fpi.payment_person,f.file_code,"
            + "fpi.payment_code,fpi.payment_confirm,fpi.bill_code,fpi.date_confirm,"
            + "fpi.comment_reject,f.business_address,f.send_date,fpi.files_code  " + sql);
    query.setFirstResult(start);
    query.setMaxResults(count);
    int paramSize = lstParam.size();
    for (int i = 0; i < paramSize; i++) {
        countQuery.setParameter(i, lstParam.get(i));
        query.setParameter(i, lstParam.get(i));
    }
    int total = Integer.parseInt(countQuery.uniqueResult().toString());
    List lstResult = query.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) {
                if (row[0] != null && !"".equals(row[0])) {
                    item.setBusinessName(row[0].toString());
                }
                if (row[1] != null && !"".equals(row[1])) {
                    item.setProductName(row[1].toString());
                }
                if (row[2] != null && !"".equals(row[2])) {
                    Date paymentDate = (Date) row[2];
                    item.setPaymentDate(DateTimeUtils.convertDateToString(paymentDate, "dd/MM/yyyy"));
                }
                if (row[3] != null && !"".equals(row[3])) {
                    item.setCost(Long.parseLong(row[3].toString()));
                }
                if (row[7] != null && !"".equals(row[7])) {
                    item.setFeeId(Long.parseLong(row[7].toString()));
                }
                if (row[4] != null && !"".equals(row[4])) {
                    item.setBillPath(row[4].toString());
                }
                if (row[5] != null && !"".equals(row[5])) {
                    item.setFeePaymentType(Long.parseLong(row[5].toString()));
                }
                if (row[6] != null && !"".equals(row[6])) {
                    item.setStatus(Long.parseLong(row[6].toString()));
                }
                if (row[8] != null && !"".equals(row[8])) {
                    item.setFileId(Long.parseLong(row[8].toString()));
                }
                if (row[9] != null && !"".equals(row[9])) {
                    item.setPaymentInfoId(Long.parseLong(row[9].toString()));
                }
                if (row[10] != null && !"".equals(row[10])) {
                    item.setPaymentPerson((row[10].toString()));
                }
                if (row[11] != null && !"".equals(row[11])) {
                    item.setFileCode((row[11].toString()));
                }
                if (row[12] != null && !"".equals(row[12])) {
                    item.setPaymentCode((row[12].toString()));
                }
                if (row[13] != null && !"".equals(row[13])) {
                    item.setPaymentConfirm((row[13].toString()));
                }
                if (row[14] != null && !"".equals(row[14])) {
                    item.setBillCode((row[14].toString()));
                }
                if (row[15] != null && !"".equals(row[15])) {
                    Date confirmDate = (Date) row[15];
                    item.setDateConfirm(DateTimeUtils.convertDateToString(confirmDate, "dd/MM/yyyy"));
                }
                if (row[16] != null && !"".equals(row[16])) {
                    item.setCommentReject(row[16].toString());
                }
                if (row[17] != null && !"".equals(row[17])) {
                    item.setBusinessAddress(row[17].toString());
                }
                if (row[19] != null && !"".equals(row[19])) {
                    item.setFilesCode(row[19].toString());
                }
            }
            result.add(item);
            item = new FeePaymentFileForm();
        }
    }
    GridResult gr = new GridResult(total, result);
    return gr;
}

From source file:com.viettel.hqmc.DAOHE.FeePaymentInfoDAOHE.java

/**
 * danh sach hoa don doanh nghiep//from w  w w .  j  a va 2s.com
 *
 * @param fileId
 * @param start
 * @param count
 * @param sortField
 * @return
 */
public GridResult getLstPayment(Long fileId, int start, int count, String sortField) {

    //        String sql = "from Fee f left join Fee_Payment_Info fpi on f.fee_Id = fpi.fee_Id where f.fee_Id "
    //                     + "in (select fp.fee_Id from Fee_Procedure fp where fp.procedure_Id = ((select fi.file_Type from Files fi where fi.file_Id = ?)) union "
    //                     + "select ff.fee_Id from Fee_File ff where ff.file_Id = ? )  and f.is_Active = 1";
    String sql = "from fee f inner join fee_payment_info fpi on f.fee_id = fpi.fee_id where fpi.file_id = ? and f.is_Active=1 and fpi.is_Active=1";
    SQLQuery countQuery = (SQLQuery) getSession().createSQLQuery("select count(*) " + sql);
    SQLQuery query = (SQLQuery) getSession().createSQLQuery(
            "select f.fee_Id,f.fee_Name,f.description,fpi.cost,f.fee_Type,fpi.status,fpi.fee_Payment_Type_Id, f.price,fpi.payment_Person,fpi.payment_Date,fpi.payment_Info,fpi.bill_path  "
                    + sql);

    query.setLong(0, fileId);
    //query.setLong(1, fileId);
    countQuery.setParameter(0, fileId);
    //countQuery.setParameter(1, fileId);
    query.setFirstResult(start);
    query.setMaxResults(count);
    int total = Integer.parseInt(countQuery.uniqueResult().toString());
    List lstResult = query.list();
    FeePaymentFileForm item = new FeePaymentFileForm();
    List result = new ArrayList<FeePaymentFileForm>();

    for (int i = 0; i < lstResult.size(); i++) {
        Object[] row = (Object[]) lstResult.get(i);
        if (row.length > 0) {
            if (row[0] != null && !"".equals(row[0])) {
                item.setFeeId(Long.parseLong(row[0].toString()));
            }
            if (row[1] != null && !"".equals(row[1])) {
                item.setFeeName(row[1].toString());
            }
            if (row[2] != null && !"".equals(row[2])) {
                item.setDescription(row[2].toString());
            }
            if (row[7] != null && !"".equals(row[7])) {
                item.setPrice(Long.parseLong(row[7].toString()));
            }
            if (row[4] != null && !"".equals(row[4])) {
                item.setFeeType(Long.parseLong(row[4].toString()));
            }
            if (row[5] != null && !"".equals(row[5])) {
                item.setStatus(Long.parseLong(row[5].toString()));
            }
            if (row[6] != null && !"".equals(row[6])) {
                item.setFeePaymentType(Long.parseLong(row[6].toString()));
            }
            if (row[8] != null && !"".equals(row[8])) {
                item.setPaymentPerson(row[8].toString());
            }
            if (row[9] != null && !"".equals(row[9])) {
                item.setPaymentDate(row[9].toString());
            }
            if (row[10] != null && !"".equals(row[10])) {
                item.setPaymentInfo(row[10].toString());
            }
            if (row[11] != null && !"".equals(row[11])) {
                item.setBillPath(row[11].toString());
            }
        }
        result.add(item);
        item = new FeePaymentFileForm();
    }
    GridResult gr = new GridResult(total, result);
    return gr;
}

From source file:com.viettel.hqmc.DAOHE.FeePaymentInfoDAOHE.java

/**
 * quan ly nop phi/* w w w  .jav  a 2 s. c om*/
 *
 * @param start
 * @param count
 * @param sortField
 * @return
 */
public GridResult getLstFeeManage(int start, int count, String sortField) {
    String sql = "from files f inner join fee_payment_info fpi on f.file_id = fpi.file_id where fpi.fee_id in (select f.fee_id from fee f where f.fee_type = 1 )";
    SQLQuery countQuery = (SQLQuery) getSession()
            .createSQLQuery("select count (distinct fpi.payment_info_id) " + sql);
    SQLQuery query = (SQLQuery) getSession().createSQLQuery(
            "select distinct f.file_code,f.product_name,fpi.payment_date,fpi.cost,fpi.bill_path,fpi.fee_payment_type_id,fpi.status,fpi.fee_id,fpi.file_id,fpi.payment_info_id,fpi.payment_person "
                    + sql);
    query.setFirstResult(start);
    query.setMaxResults(count);
    int total = Integer.parseInt(countQuery.uniqueResult().toString());
    List lstResult = query.list();
    FeePaymentFileForm item = new FeePaymentFileForm();
    List result = new ArrayList<FeePaymentFileForm>();

    for (int i = 0; i < lstResult.size(); i++) {
        Object[] row = (Object[]) lstResult.get(i);
        if (row.length > 0) {
            if (row[0] != null && !"".equals(row[0])) {
                item.setFileCode(row[0].toString());
            }
            if (row[1] != null && !"".equals(row[1])) {
                item.setProductName(row[1].toString());
            }
            if (row[2] != null && !"".equals(row[2])) {
                item.setPaymentDate(row[2].toString());
            }
            if (row[3] != null && !"".equals(row[3])) {
                item.setCost(Long.parseLong(row[3].toString()));
            }
            if (row[7] != null && !"".equals(row[7])) {
                item.setFeeId(Long.parseLong(row[7].toString()));
            }
            if (row[4] != null && !"".equals(row[4])) {
                item.setBillPath(row[4].toString());
            }
            if (row[5] != null && !"".equals(row[5])) {
                item.setFeePaymentType(Long.parseLong(row[5].toString()));
            }
            if (row[6] != null && !"".equals(row[6])) {
                item.setStatus(Long.parseLong(row[6].toString()));
            }
            if (row[8] != null && !"".equals(row[8])) {
                item.setFileId(Long.parseLong(row[8].toString()));
            }
            if (row[9] != null && !"".equals(row[9])) {
                item.setPaymentInfoId(Long.parseLong(row[9].toString()));
            }
            if (row[10] != null && !"".equals(row[10])) {
                item.setPaymentPerson((row[10].toString()));
            }

        }
        result.add(item);
        item = new FeePaymentFileForm();
    }
    GridResult gr = new GridResult(total, result);
    return gr;
}

From source file:com.viettel.logistic.wms.dao.GoodsDAO.java

License:Open Source License

public List<GoodsDTO> getListGoods(GoodsDTO goodsDTO) {
    List<GoodsDTO> lstGoodsDTO = new ArrayList();
    List lstParams = new ArrayList();
    //        /*  w  w w. ja  v a  2  s .  co  m*/

    StringBuffer sql = new StringBuffer();
    //        
    sql.append("SELECT   g.cust_id custId,");
    sql.append("         g.goods_id goodsId,");
    sql.append("  FROM   goods g, stock_goods_total sg");
    sql.append(" WHERE   s.stock_id = sg.owner_id");
    sql.append("     AND g.goods_id = sg.goods_id");
    sql.append("     AND sg.amount_issue > 0");

    //Tim kiem theo khach hang
    if (!StringUtils.isStringNullOrEmpty(goodsDTO.getCustId())) {
        sql.append("         AND sg.cust_id = ?");
        sql.append("         AND g.cust_id = ?");
        lstParams.add(goodsDTO.getCustId());
        lstParams.add(goodsDTO.getCustId());
    }

    //
    sql.append(" GROUP BY   sg.cust_id");
    sql.append(" ORDER BY   sg.cust_id");
    //Map DTO
    SQLQuery query = getSession().createSQLQuery(sql.toString());
    query.setResultTransformer(Transformers.aliasToBean(StockGoodsInforDTO.class));
    query.addScalar("custId", new StringType());
    query.addScalar("ownerId", new StringType());
    query.addScalar("ownerType", new StringType());
    query.addScalar("ownerCode", new StringType());
    query.addScalar("ownerName", new StringType());
    query.addScalar("goodsType", new StringType());
    query.addScalar("goodsId", new StringType());
    query.addScalar("goodsCode", new StringType());
    query.addScalar("goodsName", new StringType());
    query.addScalar("goodsState", new StringType());
    query.addScalar("amount", new StringType());
    query.addScalar("amountIssue", new StringType());
    query.addScalar("unitType", new StringType());
    //Set cac gia tri tham so
    for (int i = 0; i < lstParams.size(); i++) {
        query.setParameter(i, lstParams.get(i));
    }

    //
    return query.list();
}