List of usage examples for org.hibernate SQLQuery list
List<R> list();
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(); }