Example usage for org.hibernate SQLQuery setResultTransformer

List of usage examples for org.hibernate SQLQuery setResultTransformer

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

Introduction

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

Prototype

@Deprecated
Query<R> setResultTransformer(ResultTransformer transformer);

Source Link

Document

Set a strategy for handling the query results.

Usage

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 ");
    }//  w  w w.j a va  2  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 ");
    }// www. ja va  2 s. 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("             ) ");
    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.CategoryListDAO.java

License:Open Source License

public List<CategoryListDTO> getCategoryListFromStaffCode(String staffCode, String service) {
    List<CategoryListDTO> lstCategoryList = null;
    StringBuilder sb = new StringBuilder();
    sb.append("    SELECT cl.code code, ");
    sb.append("      cl.id id, ");
    sb.append("      cl.name name, ");
    sb.append("      to_char(cl.RECEIVED_DATE,'dd/MM/yyyy') receivedDate, ");
    sb.append("      to_char(cl.END_DATE,'dd/MM/yyyy') endDate, ");
    sb.append("      cl.DESCRIPTION description, ");
    sb.append("      cl.CREATOR creator ");
    sb.append("    FROM category_list cl ");
    sb.append("    WHERE cl.ID IN ");
    sb.append("      (SELECT DISTINCT mine_name FROM customer_status WHERE 1 = 1 ");
    if (!DataUtil.isStringNullOrEmpty(staffCode)) {
        sb.append("      AND staff_code= :staffCode ");
    }//from  w  w  w . j  av  a  2  s  .  c  om
    sb.append("      ) ");
    if (!DataUtil.isStringNullOrEmpty(service)) {
        sb.append("      AND cl.service= :service ");
    }
    sb.append("      AND cl.cust_quantity > 0 ");
    try {
        SQLQuery query = getSession().createSQLQuery(sb.toString());
        if (!DataUtil.isStringNullOrEmpty(staffCode)) {
            query.setParameter("staffCode", staffCode);
        }
        if (!DataUtil.isStringNullOrEmpty(service)) {
            query.setParameter("service", service);
        }
        query.setResultTransformer(Transformers.aliasToBean(CategoryListDTO.class));
        query.addScalar("code", new StringType());
        query.addScalar("id", new StringType());
        query.addScalar("name", new StringType());
        query.addScalar("receivedDate", new StringType());
        query.addScalar("endDate", new StringType());
        query.addScalar("description", new StringType());
        query.addScalar("creator", new StringType());
        lstCategoryList = query.list();
    } catch (Exception e) {
        e.printStackTrace();
    }
    return lstCategoryList;
}

From source file:com.cms.dao.CustomerDAO.java

License:Open Source License

/**
 * QuyenDM getCustomerUserInfoDTO/*from   w  w  w .j  av  a 2  s.  c o m*/
 *
 * @param userCode
 * @return
 */
public CustomerUserInfoDTO getCustUserInforDTO(String userCode) {
    //Doi tuong tra ve
    CustomerUserInfoDTO CustUserInforDTO = new CustomerUserInfoDTO();
    StringBuilder sql = new StringBuilder();
    //Cau lenh truy van du lieu        
    sql.append("SELECT a.cust_id custId, a.code custCode,a.name custName, ");
    sql.append("       a.cust_type custType,b.code userCode,b.name userName, ");
    sql.append("       b.cust_user_type custUserType, b.email userEmail, b.tel_number userTelNumber ");
    sql.append("FROM   customer a, customer_user b ");
    sql.append("WHERE b.cust_id = a.cust_id ");
    sql.append("AND a.status <> 6 AND b.status = 1 ");
    sql.append("AND LOWER(b.code) = LOWER(?)");
    //Su dung SQLQuery tao cau truy van
    SQLQuery query = getSession().createSQLQuery(sql.toString());
    query.setResultTransformer(Transformers.aliasToBean(CustomerUserInfoDTO.class));
    query.addScalar("custId", new StringType());
    query.addScalar("custCode", new StringType());
    query.addScalar("custName", new StringType());
    query.addScalar("custType", new StringType());
    query.addScalar("userCode", new StringType());
    query.addScalar("userName", new StringType());
    query.addScalar("custUserType", new StringType());
    query.addScalar("userEmail", new StringType());
    query.addScalar("userTelNumber", new StringType());
    //Truyen tham so vao cau query
    query.setParameter(0, userCode);
    List<CustomerUserInfoDTO> listCustUserInforDTO = query.list();
    //Neu danh sach tra ve co du lieu thi gan doi tuong tra ve        
    if (listCustUserInforDTO != null && listCustUserInforDTO.size() > 0) {
        CustUserInforDTO = listCustUserInforDTO.get(0);
    }
    return CustUserInforDTO;
}

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, ");
    //        }//ww w .j  a v a  2s .co 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;/*from   w  ww .  j ava 2 s  . co 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;
    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;/* 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  w w w .j ava  2 s . c  o m
 *
 * @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;
}

From source file:com.cms.dao.CustomerDAO.java

License:Open Source License

public List<CustomerDTO> getCustomerFromIHTKK(List<String> lstTaxCodes) {
    List<CustomerDTO> lstCustomers = null;
    StringBuilder sqlQuery = new StringBuilder();
    sqlQuery.append("       SELECT DISTINCT a.tin taxCode, a.norm_name name,  a.tran_prov taxAuthority   ");
    sqlQuery.append("       FROM IHTKK_MV_PAYER a WHERE 1=1 ");
    if (!DataUtil.isListNullOrEmpty(lstTaxCodes)) {
        sqlQuery.append("       AND   a.tin 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.tin IN ");
                sqlQuery.append("( :idx").append(String.valueOf(index)).append(" )");

            }/* w  w w.  j a va 2s.com*/
        }
        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("name", 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;
}

From source file:com.cms.dao.CustomerDAO.java

License:Open Source License

public List<CustomerDTO> getListCustomerOfMineName(String mineName) {
    List<CustomerDTO> lstCustomerOfMineName = null;
    if (DataUtil.isStringNullOrEmpty(mineName)) {
        return null;
    } else {/*w ww .ja  va 2s  . c  o  m*/
        SQLQuery query;
        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("               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.mine_name = :mineName ");

        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("taxAuthority", new StringType());

            //Danh sach khai thac                
            query.setParameter("mineName", Long.parseLong(mineName));
            lstCustomerOfMineName = query.list();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return lstCustomerOfMineName;
    }
}