List of usage examples for org.hibernate SQLQuery setParameter
@Override
NativeQuery<T> setParameter(int position, Object val);
From source file:com.vertec.daoimpl.StockDAOImpl.java
/** * call from InvoiceController case "LoadBPMToInvoice" * * @param arr//from w ww . j ava 2s. c o m * @return */ public List<Object[]> loadVSForInvoice(int[] arr) { Session session = NewHibernateUtil.getSessionFactory().openSession(); Transaction transaction = session.beginTransaction(); if (session != null) { try { SQLQuery query = session.createSQLQuery( "select vs.bpm_id,pm.product_master_id,vs.quantity as vquan,pm.purchased_price,pm.selling_price from vehicle_stock vs\n" + "inner join branch_productmaster bpm on vs.bpm_id=bpm.bpm_id\n" + "inner join product_master pm on bpm.product_master_id=pm.product_master_id\n" + "inner join vehicle v on vs.vehicle_id=v.vehicle_id\n" + "where pm.product_id=:product_id and v.vehicle_id=:vehicle_id and pm.is_available=:is_available"); query.setParameter("product_id", arr[1]); query.setParameter("vehicle_id", arr[0]); query.setParameter("is_available", true); List<Object[]> bpmList = query.list(); return bpmList; } catch (Exception e) { e.printStackTrace(); } finally { if (session != null && session.isOpen()) { session.close(); } } } return null; }
From source file:com.vertec.daoimpl.SupplierDAOImpl.java
public String removeSupplier(int supplierId) { Session session = NewHibernateUtil.getSessionFactory().openSession(); Transaction transaction = session.beginTransaction(); if (session != null) { try {//from w w w . j a v a 2 s.c o m SQLQuery query = session .createSQLQuery("Update supplier set is_active=:is_active where supplier_id=:supplier_id"); query.setParameter("supplier_id", supplierId); query.setParameter("is_active", false); query.executeUpdate(); transaction.commit(); return VertecConstants.UPDATED; } catch (Exception e) { e.printStackTrace(); return VertecConstants.ERROR; } finally { if (session != null && session.isOpen()) { session.close(); } } } return null; }
From source file:com.vertec.daoimpl.SupplierDAOImpl.java
public String updateSupplier(Supplier supplier) { Session session = NewHibernateUtil.getSessionFactory().openSession(); Transaction transaction = session.beginTransaction(); if (session != null) { try {// w ww . java2 s . c o m SQLQuery query = session.createSQLQuery( "Update supplier set supplier_name=:supplier_name,company_name=:company_name,address=:address,fax=:fax,land=:land,mobile=:mobile,email=:email,type=:type where supplier_id=:supplier_id"); System.out.println("GOT QUERY...."); query.setParameter("supplier_name", supplier.getSupplierName()); query.setParameter("company_name", supplier.getCompanyName()); query.setParameter("email", supplier.getEmail()); query.setParameter("mobile", supplier.getMobile()); query.setParameter("land", supplier.getLand()); query.setParameter("fax", supplier.getFax()); query.setParameter("address", supplier.getAddress()); query.setParameter("supplier_id", supplier.getSupplierId()); query.setParameter("type", supplier.getType()); query.executeUpdate(); transaction.commit(); return VertecConstants.UPDATED; } catch (Exception e) { e.printStackTrace(); return VertecConstants.ERROR; } finally { if (session != null && session.isOpen()) { session.close(); } } } return null; }
From source file:com.vertec.daoimpl.UserDAOImpl.java
@Override public String removeUser(int userId) { Session session = NewHibernateUtil.getSessionFactory().openSession(); Transaction transaction = session.beginTransaction(); if (session != null) { try {//from ww w .j av a 2 s. c om SQLQuery query = session .createSQLQuery("Update sys_user set is_active=:is_active where sysuser_id=:sysuser_id"); query.setParameter("sysuser_id", userId); query.setParameter("is_active", false); query.executeUpdate(); transaction.commit(); return VertecConstants.UPDATED; } catch (Exception e) { e.printStackTrace(); return VertecConstants.ERROR; } finally { if (session != null && session.isOpen()) { session.close(); } } } return null; }
From source file:com.vertec.daoimpl.UserDAOImpl.java
@Override public String updateUser(SysUser sysUser) { Session session = NewHibernateUtil.getSessionFactory().openSession(); Transaction transaction = session.beginTransaction(); if (session != null) { try {//from www . j a v a 2 s.com SQLQuery query = session.createSQLQuery( "Update sys_user set first_name=:first_name,last_name=:last_name,email_add=:email_add,contact_no=:contact_no,nic_no=:nic_no,dob=:dob,last_updated_date=:last_updated_date,last_updated_by=:last_updated_by,user_group_id=:user_group_id,company_id=:com where sysuser_id=:sysuser_id"); query.setParameter("user_group_id", sysUser.getUserGroupId()); query.setParameter("sysuser_id", sysUser.getSysuserId()); query.setParameter("first_name", sysUser.getFirstName()); query.setParameter("last_name", sysUser.getLastName()); query.setParameter("email_add", sysUser.getEmailAdd()); query.setParameter("contact_no", sysUser.getContactNo()); query.setParameter("nic_no", sysUser.getNicNo()); query.setParameter("dob", sysUser.getDob()); query.setParameter("last_updated_date", sysUser.getLastUpdatedDate()); query.setParameter("last_updated_by", sysUser.getLastUpdatedBy()); query.setParameter("com", sysUser.getCompanyId()); query.executeUpdate(); transaction.commit(); return VertecConstants.UPDATED; } catch (Exception e) { e.printStackTrace(); return VertecConstants.ERROR; } finally { if (session != null && session.isOpen()) { session.close(); } } } return null; }
From source file:com.vertec.util.CheckAuth.java
public Object[] checkUserAuth(String code, int gId) { Session session = NewHibernateUtil.getSessionFactory().openSession(); Transaction transaction = session.beginTransaction(); if (session != null) { try {/*w w w .j a va 2 s. com*/ SQLQuery query = session.createSQLQuery( "SELECT ugpi_id, user_group_id,privilege_item_code FROM user_group_privilege_item inner join privilege_item on user_group_privilege_item.privilege_item_id=privilege_item.privilege_item_id where user_group_privilege_item.user_group_id=:groupId and privilege_item.privilege_item_code=:itemCode"); query.setParameter("groupId", gId); query.setParameter("itemCode", code); Object[] ob = (Object[]) query.uniqueResult(); return ob; } catch (Exception e) { e.printStackTrace(); } finally { if (session != null && session.isOpen()) { session.close(); } } } return null; }
From source file:com.viettel.hqmc.DAO.ReportDAO.java
public void reportVT() { try {/*from w w w . ja v a2 s . c om*/ String templateFile = "/WEB-INF/reportTemplate/reportDayKT.xls"; //List<FilesNoClob> data; List<FeePaymentFileForm> data; List<FeePaymentFileForm> data3; List<FeePaymentFileForm> data2; Long spTM, spTM1, spTM2, spTM3, spCK, spCK1, spCK2, spCK3, spOL, spOL1, spOL2, spOL3, valueTM, valueTM1, valueTM2, valueTM3, valueCK, valueCK1, valueCK2, valueCK3, valueOL, valueOL1, valueOL2, valueOL3; Long value1, value2, value3, value4; Long total1, total2, total3, total4; Long checkCountTM = 0L; Long checkCountCK = 0L; Long checkCountOL = 0L; List lstParam = new ArrayList(); ConcurrentHashMap bean = new ConcurrentHashMap(); //data = fileDao.onsearchDayReportClerical(); String sql = ""; SimpleDateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy"); String header; if (searchFeeForm.getDateFrom() != null) { header = "Thng k t ngy: " + dateFormat.format(searchFeeForm.getDateFrom()); if (searchFeeForm.getDateTo() != null) { header = header + " - ?n ngy: " + dateFormat.format(searchFeeForm.getDateTo()); } } else if (searchFeeForm.getDateTo() != null) { header = "Tip nhn n ngy: " + dateFormat.format(searchFeeForm.getDateTo()); } else { header = "Thng k ton b"; } if (searchFeeForm.getDateFrom() != null) { sql += " and fpi.payment_date >= to_date( ? ,'dd/MM/yyyy hh24:mi:ss') "; String param = "" + DateTimeUtils.convertDateToString(searchFeeForm.getDateFrom(), "dd/MM/yyyy") + " 00:00:00"; lstParam.add(param); } if (searchFeeForm.getDateTo() != null) { sql += " and fpi.payment_date <= to_date( ?,'dd/MM/yyyy hh24:mi:ss') "; String param = "" + DateTimeUtils.convertDateToString(searchFeeForm.getDateTo(), "dd/MM/yyyy") + " 23:59:59"; lstParam.add(param); } // thuc pham thuong SQLQuery query1 = (SQLQuery) getSession().createSQLQuery( "select wm_concat(fpi.payment_date) as payment_date ,wm_concat(fpi.payment_code) as payment_code,wm_concat(f.file_code) as file_code,f.business_name,wm_concat(fpi.fee_payment_type_id) as fee_payment_type_id, count(*) as sp,sum(fpi.cost) as total,ROW_NUMBER() OVER (ORDER BY f.business_name) idx from fee_payment_info fpi inner join files f on fpi.file_id = f.file_id\n" + "where (f.is_active = 1 or f.is_active = 2) and fpi.status = 1 and fpi.is_active = 1 and fpi.cost = 500000 " + sql + " group by f.business_name"); // thuc pham chuc nang SQLQuery query2 = (SQLQuery) getSession().createSQLQuery( "select wm_concat(fpi.payment_date) as payment_date ,wm_concat(fpi.payment_code) as payment_code,wm_concat(f.file_code) as file_code,f.business_name,wm_concat(fpi.fee_payment_type_id) as fee_payment_type_id, count(*) as sp,sum(fpi.cost) as total,ROW_NUMBER() OVER (ORDER BY f.business_name) idx from fee_payment_info fpi inner join files f on fpi.file_id = f.file_id\n" + "where (f.is_active = 1 or f.is_active = 2) and fpi.status = 1 and fpi.is_active = 1 and fpi.cost = 1500000 " + sql + " group by f.business_name"); // le phi cap so SQLQuery query3 = (SQLQuery) getSession().createSQLQuery( "select wm_concat(fpi.payment_date) as payment_date ,wm_concat(fpi.payment_code) as payment_code,wm_concat(f.file_code) as file_code,f.business_name,wm_concat(fpi.fee_payment_type_id) as fee_payment_type_id, count(*) as sp,sum(fpi.cost) as total,ROW_NUMBER() OVER (ORDER BY f.business_name) idx from fee_payment_info fpi inner join files f on fpi.file_id = f.file_id \n" + "where (f.is_active = 1 or f.is_active = 2) and fpi.status = 1 and fpi.is_active = 1 and fpi.fee_id = (select e.fee_id from fee e where e.fee_type = 1) " + sql + " group by f.business_name"); for (int i = 0; i < lstParam.size(); i++) { query1.setParameter(i, lstParam.get(i)); query2.setParameter(i, lstParam.get(i)); query3.setParameter(i, lstParam.get(i)); } // thuc pham thuong List lstResult = query1.list(); FeePaymentFileForm item = new FeePaymentFileForm(); List result = new ArrayList<FeePaymentFileForm>(); if (lstResult != null && lstResult.size() > 0) { for (int i = 0; i < lstResult.size(); i++) { Object[] row = (Object[]) lstResult.get(i); if (row.length > 0) { //paymentDate if (row[0] != null && !"".equals(row[0])) { String paymentDate = row[0].toString(); String paymentDateNew = ""; List<String> myList = new ArrayList<>(Arrays.asList(paymentDate.split(","))); if (myList.size() == 1) { SimpleDateFormat sdf = new SimpleDateFormat("dd-MMM-yy"); Date convertedCurrentDate = sdf.parse(paymentDate); paymentDateNew = DateTimeUtils.convertDateToString(convertedCurrentDate, "dd/MM/yyyy"); } else { for (int j = 0; j < myList.size(); j++) { SimpleDateFormat sdf = new SimpleDateFormat("dd-MMM-yy"); Date convertedCurrentDate = sdf.parse(myList.get(j).toString()); paymentDateNew += DateTimeUtils.convertDateToString(convertedCurrentDate, "dd/MM/yyyy") + "\n"; } } item.setPaymentDate(paymentDateNew); } //paymentCode if (row[1] != null && !"".equals(row[1])) { String paymentCode = row[1].toString(); String paymentCodeNew = ""; List<String> myList = new ArrayList<>(Arrays.asList(paymentCode.split(","))); if (myList.size() == 1) { paymentCodeNew = paymentCode; } else { for (int j = 0; j < myList.size(); j++) { paymentCodeNew += myList.get(j).toString() + "\n"; } } item.setPaymentCode(paymentCodeNew); } //fileCode if (row[2] != null && !"".equals(row[2])) { String fileCode = row[2].toString(); String fileCodeNew = ""; List<String> myList = new ArrayList<>(Arrays.asList(fileCode.split(","))); if (myList.size() == 1) { fileCodeNew = fileCode; } else { for (int j = 0; j < myList.size(); j++) { fileCodeNew += myList.get(j).toString() + "\n"; } } item.setFileCode(fileCodeNew); } // businessName if (row[3] != null && !"".equals(row[3])) { item.setBusinessName(row[3].toString()); } // feepaymentType if (row[4] != null && !"".equals(row[4])) { String feePaymentType = row[4].toString(); Long countTM = 0l, countCK = 0l, countOL = 0l, totalTM = 0l, totalCK = 0l, totalOL = 0l; List<String> myList = new ArrayList<>(Arrays.asList(feePaymentType.split(","))); for (int j = 0; j < myList.size(); j++) { if ("1".equals(myList.get(j).toString())) { countOL++; checkCountOL++; } if ("2".equals(myList.get(j).toString())) { countTM++; checkCountTM++; } if ("3".equals(myList.get(j).toString())) { countCK++; checkCountCK++; } } if (countOL != null) { totalOL = 500000 * countOL; } if (countCK != null) { totalCK = 500000 * countCK; } if (countTM != null) { totalTM = 500000 * countTM; } item.setCountCK(countCK); item.setCountOL(countOL); item.setCountTM(countTM); item.setTotalCK(totalCK); item.setTotalOL(totalOL); item.setTotalTM(totalTM); } if (row[7] != null && !"".equals(row[7])) { item.setIndex(Long.parseLong(row[7].toString())); } } result.add(item); item = new FeePaymentFileForm(); } } spTM = checkCountTM; spOL = checkCountOL; spCK = checkCountCK; valueTM = 500000 * spTM; valueCK = 500000 * spCK; valueOL = 500000 * spOL; total1 = spTM + spOL + spCK; value1 = valueTM + valueCK + valueOL; //reset checkCount chay tiep nhung lan sau checkCountTM = 0l; checkCountCK = 0l; checkCountOL = 0l; // thuc pham chuc nang List lstResult2 = query2.list(); FeePaymentFileForm item2 = new FeePaymentFileForm(); List result2 = new ArrayList<FeePaymentFileForm>(); if (lstResult2 != null && lstResult2.size() > 0) { for (int i = 0; i < lstResult2.size(); i++) { Object[] row = (Object[]) lstResult2.get(i); if (row.length > 0) { //paymentDate if (row[0] != null && !"".equals(row[0])) { String paymentDate = row[0].toString(); String paymentDateNew = ""; List<String> myList = new ArrayList<>(Arrays.asList(paymentDate.split(","))); if (myList.size() == 1) { SimpleDateFormat sdf = new SimpleDateFormat("dd-MMM-yy"); Date convertedCurrentDate = sdf.parse(paymentDate); paymentDateNew = DateTimeUtils.convertDateToString(convertedCurrentDate, "dd/MM/yyyy"); } else { for (int j = 0; j < myList.size(); j++) { SimpleDateFormat sdf = new SimpleDateFormat("dd-MMM-yy"); Date convertedCurrentDate = sdf.parse(myList.get(j).toString()); paymentDateNew += DateTimeUtils.convertDateToString(convertedCurrentDate, "dd/MM/yyyy") + "\n"; } } item2.setPaymentDate(paymentDateNew); } //paymentCode if (row[1] != null && !"".equals(row[1])) { String paymentCode = row[1].toString(); String paymentCodeNew = ""; List<String> myList = new ArrayList<>(Arrays.asList(paymentCode.split(","))); if (myList.size() == 1) { paymentCodeNew = paymentCode; } else { for (int j = 0; j < myList.size(); j++) { paymentCodeNew += myList.get(j).toString() + "\n"; } } item2.setPaymentCode(paymentCodeNew); } //fileCode if (row[2] != null && !"".equals(row[2])) { String fileCode = row[2].toString(); String fileCodeNew = ""; List<String> myList = new ArrayList<>(Arrays.asList(fileCode.split(","))); if (myList.size() == 1) { fileCodeNew = fileCode; } else { for (int j = 0; j < myList.size(); j++) { fileCodeNew += myList.get(j).toString() + "\n"; } } item2.setFileCode(fileCodeNew); } // businessName if (row[3] != null && !"".equals(row[3])) { item2.setBusinessName(row[3].toString()); } // feepaymentType if (row[4] != null && !"".equals(row[4])) { String feePaymentType = row[4].toString(); Long countTM = 0l, countCK = 0l, countOL = 0l, totalTM = 0l, totalCK = 0l, totalOL = 0l; List<String> myList = new ArrayList<>(Arrays.asList(feePaymentType.split(","))); for (int j = 0; j < myList.size(); j++) { if ("1".equals(myList.get(j).toString())) { countOL++; checkCountOL++; } if ("2".equals(myList.get(j).toString())) { countTM++; checkCountTM++; } if ("3".equals(myList.get(j).toString())) { countCK++; checkCountCK++; } } if (countOL != null) { totalOL = 1500000 * countOL; } if (countCK != null) { totalCK = 1500000 * countCK; } if (countTM != null) { totalTM = 1500000 * countTM; } item2.setCountCK(countCK); item2.setCountOL(countOL); item2.setCountTM(countTM); item2.setTotalCK(totalCK); item2.setTotalOL(totalOL); item2.setTotalTM(totalTM); } if (row[7] != null && !"".equals(row[7])) { item2.setIndex(Long.parseLong(row[7].toString())); } } result2.add(item2); item2 = new FeePaymentFileForm(); } } spTM1 = checkCountTM; spCK1 = checkCountCK; spOL1 = checkCountOL; valueTM1 = 1500000 * spTM1; valueCK1 = 1500000 * spCK1; valueOL1 = 1500000 * spOL1; total2 = spTM1 + spOL1 + spCK1; value2 = valueTM1 + valueCK1 + valueOL1; //reset checkCount chay tiep nhung lan sau checkCountTM = 0l; checkCountCK = 0l; checkCountOL = 0l; // le phi cap so List lstResult3 = query3.list(); FeePaymentFileForm item3 = new FeePaymentFileForm(); List result3 = new ArrayList<FeePaymentFileForm>(); if (lstResult3 != null && lstResult3.size() > 0) { for (int i = 0; i < lstResult3.size(); i++) { Object[] row = (Object[]) lstResult3.get(i); if (row.length > 0) { //paymentDate if (row[0] != null && !"".equals(row[0])) { String paymentDate = row[0].toString(); String paymentDateNew = ""; List<String> myList = new ArrayList<>(Arrays.asList(paymentDate.split(","))); if (myList.size() == 1) { SimpleDateFormat sdf = new SimpleDateFormat("dd-MMM-yy"); Date convertedCurrentDate = sdf.parse(paymentDate); paymentDateNew = DateTimeUtils.convertDateToString(convertedCurrentDate, "dd/MM/yyyy"); } else { for (int j = 0; j < myList.size(); j++) { SimpleDateFormat sdf = new SimpleDateFormat("dd-MMM-yy"); Date convertedCurrentDate = sdf.parse(myList.get(j).toString()); paymentDateNew += DateTimeUtils.convertDateToString(convertedCurrentDate, "dd/MM/yyyy") + "\n"; } } item3.setPaymentDate(paymentDateNew); } //paymentCode if (row[1] != null && !"".equals(row[1])) { String paymentCode = row[1].toString(); String paymentCodeNew = ""; List<String> myList = new ArrayList<>(Arrays.asList(paymentCode.split(","))); if (myList.size() == 1) { paymentCodeNew = paymentCode; } else { for (int j = 0; j < myList.size(); j++) { paymentCodeNew += myList.get(j).toString() + "\n"; } } item3.setPaymentCode(paymentCodeNew); } //fileCode if (row[2] != null && !"".equals(row[2])) { String fileCode = row[2].toString(); String fileCodeNew = ""; List<String> myList = new ArrayList<>(Arrays.asList(fileCode.split(","))); if (myList.size() == 1) { fileCodeNew = fileCode; } else { for (int j = 0; j < myList.size(); j++) { fileCodeNew += myList.get(j).toString() + "\n"; } } item3.setFileCode(fileCodeNew); } // businessName if (row[3] != null && !"".equals(row[3])) { item3.setBusinessName(row[3].toString()); } // feepaymentType if (row[4] != null && !"".equals(row[4])) { String feePaymentType = row[4].toString(); Long countTM = 0l, countCK = 0l, countOL = 0l, totalTM = 0l, totalCK = 0l, totalOL = 0l; List<String> myList = new ArrayList<>(Arrays.asList(feePaymentType.split(","))); for (int j = 0; j < myList.size(); j++) { if ("1".equals(myList.get(j).toString())) { countOL++; checkCountOL++; } if ("2".equals(myList.get(j).toString())) { countTM++; checkCountTM++; } if ("3".equals(myList.get(j).toString())) { countCK++; checkCountCK++; } } if (countOL != null) { totalOL = 150000 * countOL; } if (countCK != null) { totalCK = 150000 * countCK; } if (countTM != null) { totalTM = 150000 * countTM; } item3.setCountCK(countCK); item3.setCountOL(countOL); item3.setCountTM(countTM); item3.setTotalCK(totalCK); item3.setTotalOL(totalOL); item3.setTotalTM(totalTM); } if (row[7] != null && !"".equals(row[7])) { item3.setIndex(Long.parseLong(row[7].toString())); } } result3.add(item3); item3 = new FeePaymentFileForm(); } } spTM2 = checkCountTM; spCK2 = checkCountCK; spOL2 = checkCountOL; valueTM2 = 150000 * spTM2; valueCK2 = 150000 * spCK2; valueOL2 = 150000 * spOL2; total3 = spTM2 + spOL2 + spCK2; value3 = valueTM2 + valueCK2 + valueOL2; //reset checkCount chay tiep nhung lan sau spTM3 = spTM + spTM1 + spTM2; spCK3 = spCK + spCK1 + spCK2; spOL3 = spOL + spOL1 + spOL2; valueTM3 = valueTM + valueTM1 + valueTM2; valueCK3 = valueCK + valueCK1 + valueCK2; valueOL3 = valueOL + valueOL1 + valueOL2; total4 = total1 + total2 + total3; value4 = value1 + value2 + value3; data = result; data2 = result2; data3 = result3; if (data == null) { data = new ArrayList<FeePaymentFileForm>(); } if (data2 == null) { data2 = new ArrayList<FeePaymentFileForm>(); } if (data3 == null) { data3 = new ArrayList<FeePaymentFileForm>(); } bean.put("header", header); bean.put("data", data); bean.put("data3", data3); bean.put("data2", data2); bean.put("dateFormat", dateFormat); bean.put("spTM", spTM); bean.put("spTM1", spTM1); bean.put("spTM2", spTM2); bean.put("spTM3", spTM3); bean.put("spCK", spCK); bean.put("spCK1", spCK1); bean.put("spCK2", spCK2); bean.put("spCK3", spCK3); bean.put("spOL", spOL); bean.put("spOL1", spOL1); bean.put("spOL2", spOL2); bean.put("spOL3", spOL3); bean.put("valueTM", valueTM); bean.put("valueTM1", valueTM1); bean.put("valueTM2", valueTM2); bean.put("valueTM3", valueTM3); bean.put("valueOL", valueOL); bean.put("valueOL1", valueOL1); bean.put("valueOL2", valueOL2); bean.put("valueOL3", valueOL3); bean.put("valueCK", valueCK); bean.put("valueCK1", valueCK1); bean.put("valueCK2", valueCK2); bean.put("valueCK3", valueCK3); bean.put("total1", total1); bean.put("total2", total2); bean.put("total3", total3); bean.put("total4", total4); bean.put("value1", value1); bean.put("value2", value2); bean.put("value3", value3); bean.put("value4", value4); ReportUtil.exportReport(getRequest(), bean, templateFile, getResponse()); } catch (Exception ex) { LogUtil.addLog(ex);//binhnt sonar a160901 // log.error(e); } }
From source file:com.viettel.hqmc.DAO.ReportDAO.java
public void reportStaff() { try {/*w w w .j a v a 2 s . c o m*/ 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 w ww . j a va2 s .c o m 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;/*from ww w.j a v a 2 s . c o 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; }