List of usage examples for org.hibernate SQLQuery setParameterList
@Override
NativeQuery<T> setParameterList(String name, Object[] values);
From source file:com.cms.dao.AppParamsDAO.java
License:Open Source License
public List<AppParamsDTO> getListProviderFromMineName(String mineName, Map<String, String> map) { List<AppParamsDTO> listAppParams = null; String taxAuthority = map.get("taxAuthority"); String startFromDate = map.get("startFromDate"); String endFromDate = map.get("endFromDate"); String startToDate = map.get("startToDate"); String endToDate = map.get("endToDate"); String fromDateRegister = map.get("fromDateRegister"); String toDateRegister = map.get("toDateRegister"); StringBuilder sb = new StringBuilder(); sb.append(" SELECT PAR_ID parId, "); sb.append(" DESCRIPTION description, "); sb.append(" PAR_CODE parCode, "); sb.append(" PAR_NAME parName, "); sb.append(" PAR_ORDER parOrder, "); sb.append(" PAR_TYPE parType, "); sb.append(" STATUS status "); sb.append(" FROM APP_PARAMS a "); sb.append(" WHERE a.PAR_TYPE = 'PROVIDER' "); sb.append(" AND lower(a.PAR_CODE) = ANY "); sb.append(" (SELECT DISTINCT lower(ti.PROVIDER) "); sb.append(" FROM TERM_INFORMATION ti "); sb.append(//from w w w .j av a2s . com " LEFT JOIN CUSTOMER_STATUS cs on ti.TAX_CODE=cs.TAX_CODE and ti.MINE_NAME = cs.MINE_NAME "); sb.append(" JOIN CUSTOMER c on c.TAX_CODE = ti.TAX_CODE "); sb.append(" WHERE ti.IS_CONTACT_INFO is null AND cs.TAX_CODE IS NULL "); sb.append(" AND ti.MINE_NAME = ANY (:mineName) "); if (!DataUtil.isStringNullOrEmpty(taxAuthority)) { sb.append(" and c.TAX_AUTHORITY = ANY(:taxAuthority) "); } if (!DataUtil.isStringNullOrEmpty(startFromDate)) { sb.append(" and ti.START_TIME >= TO_DATE(:startFromDate,'dd/MM/yyyy') - 1 "); } if (!DataUtil.isStringNullOrEmpty(endFromDate)) { sb.append(" and ti.START_TIME <= TO_DATE(:endFromDate,'dd/MM/yyyy') + 1 "); } if (!DataUtil.isStringNullOrEmpty(startToDate)) { sb.append(" and ti.END_TIME >= TO_DATE(:startToDate,'dd/MM/yyyy') - 1 "); } if (!DataUtil.isStringNullOrEmpty(endToDate)) { sb.append(" and ti.END_TIME <= TO_DATE(:endToDate,'dd/MM/yyyy') + 1 "); } if (!DataUtil.isStringNullOrEmpty(fromDateRegister)) { sb.append(" AND ti.DATE_REGISTER >= TO_DATE(:fromDateRegister,'dd/MM/yyyy') - 1 "); } if (!DataUtil.isStringNullOrEmpty(toDateRegister)) { sb.append(" AND ti.DATE_REGISTER <= TO_DATE(:toDateRegister,'dd/MM/yyyy') + 1"); } sb.append(" ) "); sb.append(" ORDER BY a.PAR_CODE asc "); SQLQuery query; try { query = getSession().createSQLQuery(sb.toString()); //Thuc hien chuyen du lieu lay ve thanh thanh doi tuong query.setResultTransformer(Transformers.aliasToBean(AppParamsDTO.class)); query.addScalar("parId", new StringType()); query.addScalar("description", new StringType()); query.addScalar("parCode", new StringType()); query.addScalar("parName", new StringType()); query.addScalar("parOrder", new StringType()); query.addScalar("parType", new StringType()); query.addScalar("status", new StringType()); query.setParameterList("mineName", DataUtil.parseInputListString(mineName)); if (!DataUtil.isStringNullOrEmpty(taxAuthority)) { query.setParameterList("taxAuthority", DataUtil.parseInputListString(taxAuthority)); } if (!DataUtil.isStringNullOrEmpty(startFromDate)) { query.setString("startFromDate", startFromDate); } if (!DataUtil.isStringNullOrEmpty(endFromDate)) { query.setString("endFromDate", endFromDate); } if (!DataUtil.isStringNullOrEmpty(startToDate)) { query.setString("startToDate", startToDate); } if (!DataUtil.isStringNullOrEmpty(endToDate)) { query.setString("endToDate", endToDate); } if (!DataUtil.isStringNullOrEmpty(fromDateRegister)) { query.setString("fromDateRegister", fromDateRegister); } if (!DataUtil.isStringNullOrEmpty(toDateRegister)) { query.setString("toDateRegister", toDateRegister); } listAppParams = query.list(); } catch (Exception e) { e.printStackTrace(); } return listAppParams; }
From source file:com.cms.dao.AppParamsDAO.java
License:Open Source License
public List<AppParamsDTO> getProviderFromStaffCode(String staffCode, String mineName) { List<AppParamsDTO> lstCategoryList = null; StringBuilder sb = new StringBuilder(); sb.append(" SELECT PAR_ID parId, "); sb.append(" DESCRIPTION description, "); sb.append(" PAR_CODE parCode, "); sb.append(" PAR_NAME parName, "); sb.append(" PAR_ORDER parOrder, "); sb.append(" PAR_TYPE parType, "); sb.append(" STATUS status "); sb.append(" FROM APP_PARAMS a "); sb.append(" WHERE a.PAR_TYPE = 'PROVIDER' "); sb.append(" AND a.STATUS = '1' "); sb.append(" AND lower(a.PAR_CODE) IN "); sb.append(" ( SELECT DISTINCT lower(ti.provider) "); sb.append(" FROM TERM_INFORMATION ti "); sb.append(" WHERE ti.IS_CONTACT_INFO is NULL AND exists "); sb.append(" ( SELECT DISTINCT cs.tax_code "); sb.append(" FROM customer_status cs "); sb.append(" WHERE 1 = 1 "); sb.append(" AND cs.tax_code = ti.tax_code "); if (!StringUtils.isStringNullOrEmpty(staffCode)) { sb.append(" AND cs.staff_code = :staffCode "); } else { //Add 15/04/2017 Them dieu kien neu la admin return getProviderFromMineName(mineName); }/*from w ww. j a va 2s . co m*/ if (!StringUtils.isStringNullOrEmpty(mineName)) { if (mineName.contains(",")) { sb.append(" AND cs.mine_name IN (:mineName) "); } else { sb.append(" AND cs.mine_name = :mineName "); } } sb.append(" ) "); sb.append(" ) "); try { SQLQuery query = getSession().createSQLQuery(sb.toString()); // if (!DataUtil.isStringNullOrEmpty(mineName)) { // // query.setParameter("mineName", mineName); // } if (!DataUtil.isStringNullOrEmpty(mineName)) { if (mineName.contains(",")) { query.setParameterList("mineName", DataUtil.parseInputListString(mineName)); } else { query.setParameter("mineName", mineName); } } if (!StringUtils.isStringNullOrEmpty(staffCode)) { query.setParameter("staffCode", staffCode); } query.setResultTransformer(Transformers.aliasToBean(AppParamsDTO.class)); query.addScalar("parId", new StringType()); query.addScalar("description", new StringType()); query.addScalar("parCode", new StringType()); query.addScalar("parName", new StringType()); query.addScalar("parOrder", new StringType()); query.addScalar("parType", new StringType()); query.addScalar("status", new StringType()); lstCategoryList = query.list(); } catch (Exception e) { e.printStackTrace(); } return lstCategoryList; }
From source file:com.cms.dao.AppParamsDAO.java
License:Open Source License
public List<AppParamsDTO> getProviderFromStaffCodeAndConditions(String staffCode, Map<String, String> map) { List<AppParamsDTO> lstCategoryList = null; String mineName = map.get("mineName"); String taxAuthority = map.get("taxAuthority"); String status = map.get("status"); if (StringUtils.isStringNullOrEmpty(staffCode)) { //Add 17/04/2017 Them dieu kien neu la admin + cac dieu kien status, tinh return getProviderFromOtherConditions(mineName, taxAuthority, status); }/* ww w . j a v a 2s .com*/ StringBuilder sb = new StringBuilder(); sb.append(" SELECT PAR_ID parId, "); sb.append(" DESCRIPTION description, "); sb.append(" PAR_CODE parCode, "); sb.append(" PAR_NAME parName, "); sb.append(" PAR_ORDER parOrder, "); sb.append(" PAR_TYPE parType, "); sb.append(" STATUS status "); sb.append(" FROM APP_PARAMS a "); sb.append(" WHERE a.PAR_TYPE = 'PROVIDER' "); sb.append(" AND a.STATUS = '1' "); sb.append(" AND lower(a.PAR_CODE) IN "); sb.append(" ( SELECT DISTINCT lower(ti.provider) "); sb.append(" FROM TERM_INFORMATION ti "); if (!StringUtils.isStringNullOrEmpty(taxAuthority)) { sb.append(" INNER JOIN CUSTOMER c ON c.TAX_CODE = ti.TAX_CODE "); } sb.append(" WHERE 1= 1 "); if (!StringUtils.isStringNullOrEmpty(taxAuthority)) { sb.append(" AND c.TAX_AUTHORITY = ANY(:taxAuthority) "); } sb.append(" AND exists "); sb.append(" ( SELECT DISTINCT cs.tax_code "); sb.append(" FROM customer_status cs "); sb.append(" WHERE 1 = 1 "); sb.append(" AND cs.tax_code = ti.tax_code "); if (!StringUtils.isStringNullOrEmpty(status)) { sb.append(" AND cs.status = ANY (:status) "); } if (!StringUtils.isStringNullOrEmpty(staffCode)) { sb.append(" AND cs.staff_code = :staffCode "); } else { //Add 17/04/2017 Them dieu kien neu la admin + cac dieu kien status, tinh return getProviderFromOtherConditions(mineName, taxAuthority, status); } if (!StringUtils.isStringNullOrEmpty(mineName)) { if (mineName.contains(",")) { sb.append(" AND cs.mine_name IN (:mineName) "); } else { sb.append(" AND cs.mine_name = :mineName "); } } sb.append(" ) "); sb.append(" ) "); try { SQLQuery query = getSession().createSQLQuery(sb.toString()); if (!DataUtil.isStringNullOrEmpty(mineName)) { if (mineName.contains(",")) { query.setParameterList("mineName", DataUtil.parseInputListString(mineName)); } else { query.setParameter("mineName", mineName); } } if (!StringUtils.isStringNullOrEmpty(staffCode)) { query.setParameter("staffCode", staffCode); } if (!StringUtils.isStringNullOrEmpty(status)) { query.setParameterList("status", DataUtil.parseInputListString(status)); } if (!StringUtils.isStringNullOrEmpty(taxAuthority)) { query.setParameterList("taxAuthority", DataUtil.parseInputListString(taxAuthority)); } query.setResultTransformer(Transformers.aliasToBean(AppParamsDTO.class)); query.addScalar("parId", new StringType()); query.addScalar("description", new StringType()); query.addScalar("parCode", new StringType()); query.addScalar("parName", new StringType()); query.addScalar("parOrder", new StringType()); query.addScalar("parType", new StringType()); query.addScalar("status", new StringType()); lstCategoryList = query.list(); } catch (Exception e) { e.printStackTrace(); } return lstCategoryList; }
From source file:com.cms.dao.AppParamsDAO.java
License:Open Source License
public List<AppParamsDTO> getProviderFromMineName(String mineName) { List<AppParamsDTO> lstCategoryList = null; StringBuilder sb = new StringBuilder(); sb.append(" SELECT ap.PAR_ID parId, "); sb.append(" ap.DESCRIPTION description, "); sb.append(" ap.PAR_CODE parCode, "); sb.append(" ap.PAR_NAME parName, "); sb.append(" ap.PAR_ORDER parOrder, "); sb.append(" ap.PAR_TYPE parType, "); sb.append(" ap.STATUS status "); sb.append(" FROM APP_PARAMS ap "); sb.append(" WHERE PAR_CODE IN "); sb.append(" (SELECT DISTINCT provider "); sb.append(" FROM term_information ti "); sb.append(" WHERE ti.IS_CONTACT_INFO is NULL AND lower(ti.PROVIDER) LIKE (lower(ap.par_code)||'%') "); if (!StringUtils.isStringNullOrEmpty(mineName)) { if (mineName.contains(",")) { sb.append(" AND ti.mine_name IN (:mineName) "); } else {/* w w w .j a v a 2 s . c o m*/ sb.append(" AND ti.mine_name = :mineName "); } // sb.append(" AND ti.MINE_NAME = :mineName "); } sb.append(" ) "); sb.append(" ORDER BY ap.PAR_ORDER "); try { SQLQuery query = getSession().createSQLQuery(sb.toString()); if (!DataUtil.isStringNullOrEmpty(mineName)) { if (mineName.contains(",")) { query.setParameterList("mineName", DataUtil.parseInputListString(mineName)); } else { query.setParameter("mineName", mineName); } } query.setResultTransformer(Transformers.aliasToBean(AppParamsDTO.class)); query.addScalar("parId", new StringType()); query.addScalar("description", new StringType()); query.addScalar("parCode", new StringType()); query.addScalar("parName", new StringType()); query.addScalar("parOrder", new StringType()); query.addScalar("parType", new StringType()); query.addScalar("status", new StringType()); lstCategoryList = query.list(); } catch (Exception e) { e.printStackTrace(); } return lstCategoryList; }
From source file:com.cms.dao.AppParamsDAO.java
License:Open Source License
public List<AppParamsDTO> getProviderFromOtherConditions(String mineName, String taxAuthority, String status) { List<AppParamsDTO> lstCategoryList = null; StringBuilder sb = new StringBuilder(); sb.append(" SELECT ap.PAR_ID parId, "); sb.append(" ap.DESCRIPTION description, "); sb.append(" ap.PAR_CODE parCode, "); sb.append(" ap.PAR_NAME parName, "); sb.append(" ap.PAR_ORDER parOrder, "); sb.append(" ap.PAR_TYPE parType, "); sb.append(" ap.STATUS status "); sb.append(" FROM APP_PARAMS ap "); sb.append(" WHERE lower(PAR_CODE) IN "); sb.append(" (SELECT DISTINCT lower(provider) "); sb.append(" FROM term_information ti "); if (!StringUtils.isStringNullOrEmpty(taxAuthority)) { sb.append(" INNER JOIN CUSTOMER c ON c.TAX_CODE = ti.TAX_CODE "); }/*from w w w . j a v a2 s . c om*/ if (!StringUtils.isStringNullOrEmpty(status)) { sb.append(" INNER JOIN CUSTOMER_STATUS cs ON cs.TAX_CODE = ti.TAX_CODE "); } sb.append(" WHERE ti.IS_CONTACT_INFO is NULL AND lower(ti.PROVIDER) = (lower(ap.par_code))"); if (!StringUtils.isStringNullOrEmpty(mineName)) { if (mineName.contains(",")) { sb.append(" AND ti.mine_name IN (:mineName) "); } else { sb.append(" AND ti.mine_name = :mineName "); } } if (!StringUtils.isStringNullOrEmpty(taxAuthority)) { sb.append(" AND c.TAX_AUTHORITY = ANY (:taxAuthority) "); } if (!StringUtils.isStringNullOrEmpty(status)) { sb.append(" AND cs.STATUS = :status "); } sb.append(" ) "); sb.append(" ORDER BY ap.PAR_ORDER "); try { SQLQuery query = getSession().createSQLQuery(sb.toString()); if (!DataUtil.isStringNullOrEmpty(mineName)) { if (mineName.contains(",")) { query.setParameterList("mineName", DataUtil.parseInputListString(mineName)); } else { query.setParameter("mineName", mineName); } } if (!StringUtils.isStringNullOrEmpty(taxAuthority)) { query.setParameterList("taxAuthority", DataUtil.parseInputListString(taxAuthority)); } if (!StringUtils.isStringNullOrEmpty(status)) { query.setParameter("status", status); } query.setResultTransformer(Transformers.aliasToBean(AppParamsDTO.class)); query.addScalar("parId", new StringType()); query.addScalar("description", new StringType()); query.addScalar("parCode", new StringType()); query.addScalar("parName", new StringType()); query.addScalar("parOrder", new StringType()); query.addScalar("parType", new StringType()); query.addScalar("status", new StringType()); lstCategoryList = query.list(); } catch (Exception e) { e.printStackTrace(); } return lstCategoryList; }
From source file:com.cms.dao.AppParamsDAO.java
License:Open Source License
public List<AppParamsDTO> getStatusFromStaffCode(String staffCode, String mineName) { List<AppParamsDTO> lstCategoryList = null; StringBuilder sb = new StringBuilder(); sb.append(" SELECT PAR_ID parId, "); sb.append(" DESCRIPTION description, "); sb.append(" PAR_CODE parCode, "); sb.append(" PAR_NAME parName, "); sb.append(" PAR_ORDER parOrder, "); sb.append(" PAR_TYPE parType, "); sb.append(" STATUS status "); sb.append(" FROM APP_PARAMS a "); sb.append(" WHERE a.PAR_TYPE = 'CUSTOMER_SERVICE_STATUS' "); sb.append(" AND a.STATUS = '1' "); sb.append(" AND a.PAR_CODE IN "); sb.append(" ( SELECT DISTINCT cs.status "); sb.append(" FROM customer_status cs "); sb.append(" WHERE 1 = 1 "); if (!StringUtils.isStringNullOrEmpty(staffCode)) { sb.append(" AND cs.staff_code = :staffCode "); }//from w ww .j a v a 2 s . c om if (!StringUtils.isStringNullOrEmpty(mineName)) { if (mineName.contains(",")) { sb.append(" AND cs.mine_name IN (:mineName) "); } else { sb.append(" AND cs.mine_name = :mineName "); } } sb.append(" ) "); try { SQLQuery query = getSession().createSQLQuery(sb.toString()); if (!DataUtil.isStringNullOrEmpty(mineName)) { if (mineName.contains(",")) { query.setParameterList("mineName", DataUtil.parseInputListString(mineName)); } else { query.setParameter("mineName", mineName); } } if (!StringUtils.isStringNullOrEmpty(staffCode)) { query.setParameter("staffCode", staffCode); } query.setResultTransformer(Transformers.aliasToBean(AppParamsDTO.class)); query.addScalar("parId", new StringType()); query.addScalar("description", new StringType()); query.addScalar("parCode", new StringType()); query.addScalar("parName", new StringType()); query.addScalar("parOrder", new StringType()); query.addScalar("parType", new StringType()); query.addScalar("status", new StringType()); lstCategoryList = query.list(); } catch (Exception e) { e.printStackTrace(); } return lstCategoryList; }
From source file:com.cms.dao.CustomerDAO.java
License:Open Source License
public List<CustomerDTO> searchCustomers(CustomerDTO customer, int maxResult) { List<CustomerDTO> lstCustomers; List params = new ArrayList(); StringBuilder sqlQuery = new StringBuilder(); sqlQuery.append(" SELECT DISTINCT a.TAX_CODE taxCode, "); sqlQuery.append(" a.NAME name, "); sqlQuery.append(" a.REPRESENTATIVE_NAME representativeName, "); sqlQuery.append(" a.TEL_NUMBER telNumber, "); sqlQuery.append(" a.EMAIL email, "); sqlQuery.append(" a.OFFICE_ADDRESS officeAddress, "); sqlQuery.append(" a.TAX_AUTHORITY taxAuthority, "); //Neu truong so dien thoai duoc dien vao thi them dieu kien // if (!DataUtil.isStringNullOrEmpty(customer.getStaffId()) // || !DataUtil.isStringNullOrEmpty(customer.getStatus())) { // sqlQuery.append(" b.MINE_NAME mineName, "); // } else { sqlQuery.append(" d.MINE_NAME mineName, "); // }//w ww .java 2 s. c o m // sqlQuery.append(" e.notes notes, "); // sqlQuery.append(" to_char(e.create_date,'dd/MM/yyyy HH24:Mi:ss') createDate, "); // sqlQuery.append(" e.status status "); //25/04/2017: Lay thong tin tu bang customer status sqlQuery.append(" d.service service, "); sqlQuery.append(" b.last_notes notes, "); sqlQuery.append(" to_char(b.last_updated,'dd/MM/yyyy HH24:Mi:ss') createDate, "); sqlQuery.append(" b.status status "); sqlQuery.append(" FROM CUSTOMER a "); sqlQuery.append(" JOIN TERM_INFORMATION d "); sqlQuery.append(" ON d.TAX_CODE = a.TAX_CODE "); //Dich vu if (!DataUtil.isStringNullOrEmpty(customer.getService())) { sqlQuery.append(" AND d.Service = :service "); } //Danh sach khai thac if (!DataUtil.isStringNullOrEmpty(customer.getMineName())) { sqlQuery.append(" AND d.MINE_NAME = :mineName "); } //Nha cung cap if (!DataUtil.isStringNullOrEmpty(customer.getProvider())) { sqlQuery.append(" AND lower(d.PROVIDER) in (:provider) "); } if (!DataUtil.isStringNullOrEmpty(customer.getStaffId()) || !DataUtil.isStringNullOrEmpty(customer.getStatus()) || !DataUtil.isStringNullOrEmpty(customer.getCustCareHistoryCreatedDate())) { sqlQuery.append(" JOIN Customer_status b "); sqlQuery.append(" ON b.TAX_CODE = a.TAX_CODE "); } else { sqlQuery.append(" LEFT JOIN Customer_status b "); sqlQuery.append(" ON b.TAX_CODE = a.TAX_CODE "); } sqlQuery.append(" AND b.TAX_CODE = d.TAX_CODE "); // if (!DataUtil.isStringNullOrEmpty(customer.getService())) { sqlQuery.append(" AND b.Service = d.service "); // } // if (!DataUtil.isStringNullOrEmpty(customer.getMineName())) { sqlQuery.append(" AND b.MINE_NAME = d.mine_name "); // } //Trang thai cua bang khach hang - dich vu if (!DataUtil.isStringNullOrEmpty(customer.getStatus())) { sqlQuery.append(" AND b.STATUS = :status "); } //Them ngay cham soc khach hang if (!DataUtil.isStringNullOrEmpty(customer.getCustCareHistoryCreatedDate())) { sqlQuery.append(" AND TO_CHAR(b.LAST_UPDATED,'dd/MM/yyyy') = :createDate "); } // //Neu truong trang thai duoc dien vao thi them dieu kien // if (!DataUtil.isStringNullOrEmpty(customer.getStatus()) // || !DataUtil.isStringNullOrEmpty(customer.getStaffId()) // || !DataUtil.isStringNullOrEmpty(customer.getMineName())) { // } //Neu truong email duoc dien vao thi them dieu kien if (!DataUtil.isStringNullOrEmpty(customer.getEmail()) || !DataUtil.isStringNullOrEmpty(customer.getTelNumber())) { sqlQuery.append(" LEFT JOIN CUSTOMER_CONTACT c "); sqlQuery.append(" ON c.TAX_CODE = a.TAX_CODE "); } sqlQuery.append(" WHERE 1=1 "); //MST if (!DataUtil.isStringNullOrEmpty(customer.getTaxCode())) { sqlQuery.append(" AND lower(a.TAX_CODE) LIKE lower(:taxCode) "); } //Staff id if (!DataUtil.isStringNullOrEmpty(customer.getStaffId())) { sqlQuery.append(" AND b.STAFF_ID = :staffId "); } //Ten cong ty if (!DataUtil.isStringNullOrEmpty(customer.getName())) { sqlQuery.append(" AND lower(a.NAME) LIKE lower(:custName) "); } //Dia chi dang ky kinh doanh if (!DataUtil.isStringNullOrEmpty(customer.getOfficeAddress())) { sqlQuery.append(" AND lower(a.OFFICE_ADDRESS) LIKE lower(:officeAdd) "); } if (!DataUtil.isStringNullOrEmpty(customer.getEmail())) { sqlQuery.append( " AND ( (lower(a.EMAIL) like :email1) OR (lower(c.EMAIL) like :email2) OR (lower(d.EMAIL) like :email3))"); } if (!DataUtil.isStringNullOrEmpty(customer.getTelNumber())) { sqlQuery.append( " AND ( (lower(a.TEL_NUMBER) like :tel1) OR (lower(c.TEL_NUMBER) LIKE :tel2 ) OR (lower(d.PHONE) LIKE :tel3 ))"); } //Neu ma tinh duoc dien tim kiem theo ma tinh if (!DataUtil.isStringNullOrEmpty(customer.getTaxAuthority())) { sqlQuery.append(" AND a.TAX_AUTHORITY in (:taxAuthority) "); } sqlQuery.append(" ORDER BY To_date(createDate,'dd/MM/yyyy HH24:Mi:ss') desc, taxCode"); try { SQLQuery query = getSession().createSQLQuery(sqlQuery.toString()); //Thuc hien chuyen du lieu lay ve thanh thanh doi tuong query.setResultTransformer(Transformers.aliasToBean(CustomerDTO.class)); query.addScalar("taxCode", new StringType()); query.addScalar("name", new StringType()); query.addScalar("representativeName", new StringType()); query.addScalar("telNumber", new StringType()); query.addScalar("email", new StringType()); query.addScalar("officeAddress", new StringType()); query.addScalar("taxAuthority", new StringType()); query.addScalar("mineName", new StringType()); query.addScalar("notes", new StringType()); query.addScalar("createDate", new StringType()); query.addScalar("status", new StringType()); query.addScalar("service", new StringType()); // query.addScalar("provider", new StringType()); //MST if (!DataUtil.isStringNullOrEmpty(customer.getTaxCode())) { query.setParameter("taxCode", "%" + customer.getTaxCode() + "%"); } //Staff id if (!DataUtil.isStringNullOrEmpty(customer.getStaffId())) { query.setParameter("staffId", customer.getStaffId()); } //Ten cong ty if (!DataUtil.isStringNullOrEmpty(customer.getName())) { query.setParameter("custName", "%" + customer.getName() + "%"); } //Dia chi dang ky kinh doanh if (!DataUtil.isStringNullOrEmpty(customer.getOfficeAddress())) { query.setParameter("officeAdd", "%" + customer.getOfficeAddress() + "%"); } //Danh sach khai thac if (!DataUtil.isStringNullOrEmpty(customer.getMineName())) { query.setParameter("mineName", customer.getMineName()); } //Dich vu if (!DataUtil.isStringNullOrEmpty(customer.getService())) { query.setParameter("service", customer.getService()); } //Nha cung cap if (!DataUtil.isStringNullOrEmpty(customer.getProvider())) { query.setParameterList("provider", DataUtil.parseInputListString(customer.getProvider().toLowerCase())); } //Trang thai cua bang khach hang - dich vu if (!DataUtil.isStringNullOrEmpty(customer.getStatus())) { query.setParameter("status", customer.getStatus()); } if (!DataUtil.isStringNullOrEmpty(customer.getEmail())) { query.setParameter("email1", "%" + customer.getEmail().toLowerCase() + "%"); query.setParameter("email2", "%" + customer.getEmail().toLowerCase() + "%"); query.setParameter("email3", "%" + customer.getEmail().toLowerCase() + "%"); } //So dien thoai cua bang lich su giao dich if (!DataUtil.isStringNullOrEmpty(customer.getTelNumber())) { query.setParameter("tel1", "%" + customer.getTelNumber() + "%"); query.setParameter("tel2", "%" + customer.getTelNumber() + "%"); query.setParameter("tel3", "%" + customer.getTelNumber() + "%"); } //Neu ma tinh duoc dien tim kiem theo ma tinh if (!DataUtil.isStringNullOrEmpty(customer.getTaxAuthority())) { query.setParameterList("taxAuthority", DataUtil.parseInputListString(customer.getTaxAuthority())); } //Them ngay cham soc khach hang if (!DataUtil.isStringNullOrEmpty(customer.getCustCareHistoryCreatedDate())) { query.setParameter("createDate", customer.getCustCareHistoryCreatedDate()); } if (maxResult != Integer.MAX_VALUE) { query.setMaxResults(maxResult); } //Day du lieu ra danh sach doi tuong lstCustomers = query.list(); } catch (Exception e) { e.printStackTrace(); lstCustomers = null; } return lstCustomers; }
From source file:com.cms.dao.CustomerDAO.java
License:Open Source License
public List<CustomerDTO> getListCustomerWithTermInfo(List<ConditionBean> lstConditions) { String fromStartTime = null;//w ww. j a v a 2s. c om String toStartTime = null; String fromEndTime = null; String toEndTime = null; String fromDateRegister = null; String toDateRegister = null; String provider = null; String service = null; String mineName = null; String taxAuthority = null; String maxSearch = null; SQLQuery query; for (ConditionBean c : lstConditions) { if ("service".equalsIgnoreCase(c.getField())) { service = c.getValue(); } if ("provider".equalsIgnoreCase(c.getField())) { provider = c.getValue(); } if ("taxAuthority".equalsIgnoreCase(c.getField())) { taxAuthority = c.getValue(); } if ("mineName".equals(c.getField())) { mineName = c.getValue(); } if ("maxSearch".equals(c.getField())) { maxSearch = c.getValue(); } if ("startTime".equals(c.getField())) { if (String.valueOf(ConditionBean.Operator.NAME_GREATER_EQUAL).equalsIgnoreCase(c.getOperator())) { fromStartTime = c.getValue(); } if (String.valueOf(ConditionBean.Operator.NAME_LESS_EQUAL).equalsIgnoreCase(c.getOperator())) { toStartTime = c.getValue(); } } if ("endTime".equals(c.getField())) { if (String.valueOf(ConditionBean.Operator.NAME_GREATER_EQUAL).equalsIgnoreCase(c.getOperator())) { fromEndTime = c.getValue(); } if (String.valueOf(ConditionBean.Operator.NAME_LESS_EQUAL).equalsIgnoreCase(c.getOperator())) { toEndTime = c.getValue(); } } if ("dateRegister".equals(c.getField())) { if (String.valueOf(ConditionBean.Operator.NAME_GREATER_EQUAL).equalsIgnoreCase(c.getOperator())) { fromDateRegister = c.getValue(); } if (String.valueOf(ConditionBean.Operator.NAME_LESS_EQUAL).equalsIgnoreCase(c.getOperator())) { toDateRegister = c.getValue(); } } } List<CustomerDTO> lstCustomers = null; List params = new ArrayList(); StringBuilder sqlQuery = new StringBuilder(); sqlQuery.append(" SELECT DISTINCT a.TAX_CODE taxCode, "); sqlQuery.append(" a.NAME name, "); sqlQuery.append(" a.REPRESENTATIVE_NAME representativeName, "); sqlQuery.append(" a.TEL_NUMBER telNumber, "); sqlQuery.append(" a.EMAIL email, "); sqlQuery.append(" a.STATUS status, "); sqlQuery.append(" a.OFFICE_ADDRESS officeAddress, "); sqlQuery.append(" d.MINE_NAME mineName, "); sqlQuery.append(" d.SERVICE service, "); sqlQuery.append(" to_char(d.END_TIME,'dd/MM/yyyy') endTime, "); sqlQuery.append(" to_char(d.Start_time,'dd/MM/yyyy') startTime, "); sqlQuery.append(" a.TAX_AUTHORITY taxAuthority "); sqlQuery.append(" FROM CUSTOMER a "); // if (!DataUtil.isStringNullOrEmpty(provider) // || !DataUtil.isStringNullOrEmpty(fromStartTime) // || !DataUtil.isStringNullOrEmpty(toStartTime) // || !DataUtil.isStringNullOrEmpty(fromEndTime) // || !DataUtil.isStringNullOrEmpty(toEndTime) // || !DataUtil.isStringNullOrEmpty(mineName)) { // } sqlQuery.append(" JOIN TERM_INFORMATION d "); sqlQuery.append(" ON d.TAX_CODE = a.TAX_CODE "); sqlQuery.append(" LEFT JOIN CUSTOMER_STATUS b "); sqlQuery.append(" ON b.TAX_CODE = a.TAX_CODE "); sqlQuery.append(" WHERE 1=1 "); sqlQuery.append(" AND b.TAX_CODE IS NULL "); if (!DataUtil.isStringNullOrEmpty(taxAuthority)) { sqlQuery.append(" AND a.TAX_AUTHORITY in (:tax) "); } if (!DataUtil.isStringNullOrEmpty(service)) { sqlQuery.append(" AND d.SERVICE = :service "); // params.add(service); } if (!DataUtil.isStringNullOrEmpty(mineName)) { sqlQuery.append(" AND d.MINE_NAME = :mineName "); // params.add(mineName); } //Nha cung cap if (!DataUtil.isStringNullOrEmpty(provider)) { sqlQuery.append(" AND lower(d.PROVIDER) in (:provider) "); } if (!DataUtil.isStringNullOrEmpty(fromStartTime)) { sqlQuery.append(" AND d.START_TIME >= TO_DATE(:fromStartTime,'dd/MM/yyyy') - 1 "); // params.add(fromStartTime); } if (!DataUtil.isStringNullOrEmpty(toStartTime)) { sqlQuery.append(" AND d.START_TIME <= TO_DATE(:toStartTime,'dd/MM/yyyy') + 1 "); // params.add(toStartTime); } if (!DataUtil.isStringNullOrEmpty(fromEndTime)) { sqlQuery.append(" AND d.END_TIME >= TO_DATE(:fromEndTime,'dd/MM/yyyy') - 1 "); // params.add(fromEndTime); } if (!DataUtil.isStringNullOrEmpty(toEndTime)) { sqlQuery.append(" AND d.END_TIME <= TO_DATE(:toEndTime,'dd/MM/yyyy') + 1"); // params.add(toEndTime); } if (!DataUtil.isStringNullOrEmpty(fromDateRegister)) { sqlQuery.append(" AND d.DATE_REGISTER >= TO_DATE(:fromDateRegister,'dd/MM/yyyy') - 1 "); // params.add(fromDateRegister); } if (!DataUtil.isStringNullOrEmpty(toDateRegister)) { sqlQuery.append(" AND d.DATE_REGISTER <= TO_DATE(:toDateRegister,'dd/MM/yyyy') + 1 "); // params.add(toDateRegister); } sqlQuery.append(" ORDER BY endTime desc, startTime desc "); try { query = getSession().createSQLQuery(sqlQuery.toString()); //Thuc hien chuyen du lieu lay ve thanh thanh doi tuong query.setResultTransformer(Transformers.aliasToBean(CustomerDTO.class)); query.addScalar("taxCode", new StringType()); query.addScalar("name", new StringType()); query.addScalar("representativeName", new StringType()); query.addScalar("telNumber", new StringType()); query.addScalar("email", new StringType()); query.addScalar("status", new StringType()); query.addScalar("officeAddress", new StringType()); query.addScalar("mineName", new StringType()); query.addScalar("service", new StringType()); query.addScalar("startTime", new StringType()); query.addScalar("endTime", new StringType()); query.addScalar("taxAuthority", new StringType()); // for (int i = 0; i < params.size(); i++) { // query.setParameter(i, params.get(i)); // } //Danh sach khai thac if (!DataUtil.isStringNullOrEmpty(mineName)) { query.setParameter("mineName", Long.parseLong(mineName)); } //Nha cung cap if (!DataUtil.isStringNullOrEmpty(provider)) { query.setParameterList("provider", DataUtil.parseInputListString(provider.toLowerCase())); } //Truyen cac tham so truyen vao de thuc hien tim kiem if (!DataUtil.isStringNullOrEmpty(taxAuthority)) { query.setParameterList("tax", DataUtil.parseInputListString(taxAuthority)); } //Truyen cac tham so truyen vao de thuc hien tim kiem if (!DataUtil.isStringNullOrEmpty(service)) { query.setParameter("service", Long.parseLong(service)); } //Truyen cac tham so truyen vao de thuc hien tim kiem if (!DataUtil.isStringNullOrEmpty(fromStartTime)) { query.setParameter("fromStartTime", fromStartTime); } //Truyen cac tham so truyen vao de thuc hien tim kiem if (!DataUtil.isStringNullOrEmpty(toStartTime)) { query.setParameter("toStartTime", toStartTime); } //Truyen cac tham so truyen vao de thuc hien tim kiem if (!DataUtil.isStringNullOrEmpty(fromEndTime)) { query.setParameter("fromEndTime", fromEndTime); } //Truyen cac tham so truyen vao de thuc hien tim kiem if (!DataUtil.isStringNullOrEmpty(toEndTime)) { query.setParameter("toEndTime", toEndTime); } //Truyen cac tham so truyen vao de thuc hien tim kiem if (!DataUtil.isStringNullOrEmpty(fromDateRegister)) { query.setParameter("fromDateRegister", fromDateRegister); } //Truyen cac tham so truyen vao de thuc hien tim kiem if (!DataUtil.isStringNullOrEmpty(toDateRegister)) { query.setParameter("toDateRegister", toDateRegister); } //Day du lieu ra danh sach doi tuong if (!DataUtil.isStringNullOrEmpty(maxSearch)) { if (DataUtil.isInteger(maxSearch)) { query.setMaxResults(Integer.parseInt(maxSearch)); } } lstCustomers = query.list(); } catch (Exception e) { e.printStackTrace(); } return lstCustomers; }
From source file:com.cms.dao.CustomerDAO.java
License:Open Source License
public List<CustomerDTO> getListCustomerFromTermInfoWithoutTaxCodes(List<ConditionBean> lstConditions, List<String> taxCodesExecuted) { String fromStartTime = null;//from ww w . j a v a 2s . c o m String toStartTime = null; String fromEndTime = null; String toEndTime = null; String fromDateRegister = null; String toDateRegister = null; String provider = null; String service = null; String mineName = null; String taxAuthority = null; String maxSearch = null; SQLQuery query; for (ConditionBean c : lstConditions) { if ("service".equalsIgnoreCase(c.getField())) { service = c.getValue(); } if ("provider".equalsIgnoreCase(c.getField())) { provider = c.getValue(); } if ("taxAuthority".equalsIgnoreCase(c.getField())) { taxAuthority = c.getValue(); } if ("mineName".equals(c.getField())) { mineName = c.getValue(); } if ("maxSearch".equals(c.getField())) { maxSearch = c.getValue(); } if ("startTime".equals(c.getField())) { if (String.valueOf(ConditionBean.Operator.NAME_GREATER_EQUAL).equalsIgnoreCase(c.getOperator())) { fromStartTime = c.getValue(); } if (String.valueOf(ConditionBean.Operator.NAME_LESS_EQUAL).equalsIgnoreCase(c.getOperator())) { toStartTime = c.getValue(); } } if ("endTime".equals(c.getField())) { if (String.valueOf(ConditionBean.Operator.NAME_GREATER_EQUAL).equalsIgnoreCase(c.getOperator())) { fromEndTime = c.getValue(); } if (String.valueOf(ConditionBean.Operator.NAME_LESS_EQUAL).equalsIgnoreCase(c.getOperator())) { toEndTime = c.getValue(); } } if ("dateRegister".equals(c.getField())) { if (String.valueOf(ConditionBean.Operator.NAME_GREATER_EQUAL).equalsIgnoreCase(c.getOperator())) { fromDateRegister = c.getValue(); } if (String.valueOf(ConditionBean.Operator.NAME_LESS_EQUAL).equalsIgnoreCase(c.getOperator())) { toDateRegister = c.getValue(); } } } List<CustomerDTO> lstCustomers = null; StringBuilder sqlQuery = new StringBuilder(); sqlQuery.append(" SELECT e.taxCode, " + " e.name, " + " e.representativeName, " + " e.telNumber," + " e.email, " + " e.status, " + " e.officeAddress, " + " e.mineName, " + " e.service, " + " to_char(max(e.endTime),'dd/MM/yyyy') endTime, " + " to_char(min(e.startTime),'dd/MM/yyyy') startTime, " + " e.taxAuthority " + "FROM " + " ( "); sqlQuery.append(" SELECT a.TAX_CODE taxCode, "); sqlQuery.append(" a.NAME name, "); sqlQuery.append(" a.REPRESENTATIVE_NAME representativeName, "); sqlQuery.append(" a.TEL_NUMBER telNumber, "); sqlQuery.append(" a.EMAIL email, "); sqlQuery.append(" a.STATUS status, "); sqlQuery.append(" a.OFFICE_ADDRESS officeAddress, "); sqlQuery.append(" d.MINE_NAME mineName, "); sqlQuery.append(" d.SERVICE service, "); sqlQuery.append(" d.END_TIME endTime, "); sqlQuery.append(" d.START_TIME startTime, "); sqlQuery.append(" a.TAX_AUTHORITY taxAuthority "); sqlQuery.append(" FROM CUSTOMER a "); sqlQuery.append(" JOIN TERM_INFORMATION d "); sqlQuery.append(" ON d.TAX_CODE = a.TAX_CODE "); sqlQuery.append(" WHERE 1=1 "); sqlQuery.append(" AND d.IS_CONTACT_INFO is null "); sqlQuery.append(" AND NOT EXISTS (select c.tax_code, c.MINE_NAME "); sqlQuery.append(" from customer_status c "); sqlQuery.append(" where c.tax_code = d.tax_code "); sqlQuery.append(" AND c.mine_name = d.mine_name) "); if (!DataUtil.isStringNullOrEmpty(taxAuthority)) { sqlQuery.append(" AND a.TAX_AUTHORITY in (:tax) "); } if (!DataUtil.isStringNullOrEmpty(service)) { sqlQuery.append(" AND d.SERVICE = :service "); } if (!DataUtil.isStringNullOrEmpty(mineName)) { sqlQuery.append(" AND d.MINE_NAME = :mineName "); } //Nha cung cap if (!DataUtil.isStringNullOrEmpty(provider)) { sqlQuery.append(" AND lower(d.PROVIDER) in (:provider) "); } if (!DataUtil.isStringNullOrEmpty(fromStartTime)) { sqlQuery.append(" AND d.START_TIME >= TO_DATE(:fromStartTime,'dd/MM/yyyy') - 1 "); } if (!DataUtil.isStringNullOrEmpty(toStartTime)) { sqlQuery.append(" AND d.START_TIME <= TO_DATE(:toStartTime,'dd/MM/yyyy') + 1 "); } if (!DataUtil.isStringNullOrEmpty(fromEndTime)) { sqlQuery.append(" AND d.END_TIME >= TO_DATE(:fromEndTime,'dd/MM/yyyy') - 1 "); } if (!DataUtil.isStringNullOrEmpty(toEndTime)) { sqlQuery.append(" AND d.END_TIME <= TO_DATE(:toEndTime,'dd/MM/yyyy') + 1"); } if (!DataUtil.isStringNullOrEmpty(fromDateRegister)) { sqlQuery.append(" AND d.DATE_REGISTER >= TO_DATE(:fromDateRegister,'dd/MM/yyyy') - 1 "); } if (!DataUtil.isStringNullOrEmpty(toDateRegister)) { sqlQuery.append(" AND d.DATE_REGISTER <= TO_DATE(:toDateRegister,'dd/MM/yyyy') + 1 "); } //Them viec bo nhung tax code da phan bo if (!DataUtil.isListNullOrEmpty(taxCodesExecuted)) { for (int i = 0; i < taxCodesExecuted.size(); i++) { sqlQuery.append(" AND a.tax_code not in (:taxCode").append(i).append(" ) "); } } // sqlQuery.append(" ORDER BY endTime desc, startTime desc "); sqlQuery.append( " ) e GROUP BY taxCode, name,representativeName,telNumber,email,status,officeAddress,mineName,service,taxAuthority\n" + " ORDER BY taxCode, name,representativeName,telNumber,email,status,officeAddress,mineName,service,taxAuthority"); try { query = getSession().createSQLQuery(sqlQuery.toString()); //Thuc hien chuyen du lieu lay ve thanh thanh doi tuong query.setResultTransformer(Transformers.aliasToBean(CustomerDTO.class)); query.addScalar("taxCode", new StringType()); query.addScalar("name", new StringType()); query.addScalar("representativeName", new StringType()); query.addScalar("telNumber", new StringType()); query.addScalar("email", new StringType()); query.addScalar("status", new StringType()); query.addScalar("officeAddress", new StringType()); query.addScalar("mineName", new StringType()); query.addScalar("service", new StringType()); query.addScalar("startTime", new StringType()); query.addScalar("endTime", new StringType()); query.addScalar("taxAuthority", new StringType()); //Danh sach khai thac if (!DataUtil.isStringNullOrEmpty(mineName)) { query.setParameter("mineName", Long.parseLong(mineName)); } //Nha cung cap if (!DataUtil.isStringNullOrEmpty(provider)) { query.setParameterList("provider", DataUtil.parseInputListString(provider.toLowerCase())); } //Truyen cac tham so truyen vao de thuc hien tim kiem if (!DataUtil.isStringNullOrEmpty(taxAuthority)) { query.setParameterList("tax", DataUtil.parseInputListString(taxAuthority)); } //Truyen cac tham so truyen vao de thuc hien tim kiem if (!DataUtil.isStringNullOrEmpty(service)) { query.setParameter("service", Long.parseLong(service)); } //Truyen cac tham so truyen vao de thuc hien tim kiem if (!DataUtil.isStringNullOrEmpty(fromStartTime)) { query.setParameter("fromStartTime", fromStartTime); } //Truyen cac tham so truyen vao de thuc hien tim kiem if (!DataUtil.isStringNullOrEmpty(toStartTime)) { query.setParameter("toStartTime", toStartTime); } //Truyen cac tham so truyen vao de thuc hien tim kiem if (!DataUtil.isStringNullOrEmpty(fromEndTime)) { query.setParameter("fromEndTime", fromEndTime); } //Truyen cac tham so truyen vao de thuc hien tim kiem if (!DataUtil.isStringNullOrEmpty(toEndTime)) { query.setParameter("toEndTime", toEndTime); } //Truyen cac tham so truyen vao de thuc hien tim kiem if (!DataUtil.isStringNullOrEmpty(fromDateRegister)) { query.setParameter("fromDateRegister", fromDateRegister); } //Truyen cac tham so truyen vao de thuc hien tim kiem if (!DataUtil.isStringNullOrEmpty(toDateRegister)) { query.setParameter("toDateRegister", toDateRegister); } //Them viec bo nhung tax code da phan bo if (!DataUtil.isListNullOrEmpty(taxCodesExecuted)) { for (int i = 0; i < taxCodesExecuted.size(); i++) { query.setParameterList("taxCode" + i, DataUtil.parseInputListString(taxCodesExecuted.get(i))); } } //Day du lieu ra danh sach doi tuong if (!DataUtil.isStringNullOrEmpty(maxSearch)) { if (DataUtil.isInteger(maxSearch)) { query.setMaxResults(Integer.parseInt(maxSearch)); } } lstCustomers = query.list(); } catch (Exception e) { e.printStackTrace(); } return lstCustomers; }
From source file:com.cms.dao.CustomerDAO.java
License:Open Source License
/** * Get list customer existed//from ww w . j a va2s.c om * * @param lstTaxCodes * @return */ public List<CustomerDTO> getCustomerExisted(List<String> lstTaxCodes) { List<CustomerDTO> lstCustomers = null; StringBuilder sqlQuery = new StringBuilder(); sqlQuery.append( " SELECT DISTINCT a.TAX_CODE taxCode, a.cust_id custId, a.tax_authority taxAuthority "); sqlQuery.append(" FROM CUSTOMER a WHERE 1=1 "); if (!DataUtil.isListNullOrEmpty(lstTaxCodes)) { sqlQuery.append(" AND a.TAX_CODE IN "); sqlQuery.append("( :idx").append(String.valueOf(0)).append(" )"); if (lstTaxCodes.size() > 1) { for (int index = 1; index < lstTaxCodes.size(); index++) { sqlQuery.append(" OR a.TAX_CODE IN "); sqlQuery.append("( :idx").append(String.valueOf(index)).append(" )"); } } SQLQuery query; query = getSession().createSQLQuery(sqlQuery.toString()); try { //Thuc hien chuyen du lieu lay ve thanh thanh doi tuong query.setResultTransformer(Transformers.aliasToBean(CustomerDTO.class)); query.addScalar("taxCode", new StringType()); query.addScalar("custId", new StringType()); query.addScalar("taxAuthority", new StringType()); for (int index = 0; index < lstTaxCodes.size(); index++) { query.setParameterList("idx" + String.valueOf(index), DataUtil.parseInputListString(lstTaxCodes.get(index))); } lstCustomers = query.list(); } catch (Exception e) { e.printStackTrace(); lstCustomers = null; } } return lstCustomers; }