Example usage for org.hibernate SQLQuery setParameter

List of usage examples for org.hibernate SQLQuery setParameter

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

Introduction

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

Prototype

@Override
    NativeQuery<T> setParameter(int position, Object val);

Source Link

Usage

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

License:Open Source License

public int updateMineNameForCustomer(String mineName) {
    StringBuilder sqlProvider = new StringBuilder();
    sqlProvider/*from w  w w  .j  ava 2  s  .c om*/
            .append("   update customer c set c.MINE_NAME = :mineName where MINE_NAME IS NULL AND EXISTS ( ");
    sqlProvider.append(
            "   select TAX_CODE from TERM_INFORMATION where MINE_NAME = :mineName AND c.tax_code = tax_code) ");
    int result = -1;
    try {
        SQLQuery query = getSession().createSQLQuery(sqlProvider.toString());
        query.setParameter("mineName", mineName);
        result = query.executeUpdate();
    } catch (Exception e) {
        e.printStackTrace();
    }
    return result;
}

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 ");
    }//  w  ww  .  j  av  a2  s .com
    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//w  w  w. j av  a2  s.co 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, ");
    //        }/*from w w  w .  j av  a2 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  w  w.j av  a2 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;/*from  w ww . j  ava  2 s. 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;
    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

public List<CustomerDTO> getListCustomerOfMineName(String mineName) {
    List<CustomerDTO> lstCustomerOfMineName = null;
    if (DataUtil.isStringNullOrEmpty(mineName)) {
        return null;
    } else {/*from  w  ww  . j av  a 2s . com*/
        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;
    }
}

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

License:Open Source License

public List<CustomerDTO> getListDevidedCustomerOfMineName(String mineName) {
    List<CustomerDTO> lstCustomerOfMineName = null;
    if (DataUtil.isStringNullOrEmpty(mineName)) {
        return null;
    } else {//from  ww w.j  a  v a 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("               d.STATUS customerStatus ");
        sqlQuery.append("       FROM CUSTOMER a ");
        sqlQuery.append("       JOIN CUSTOMER_STATUS 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());
            query.addScalar("customerStatus", new StringType());

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

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

License:Open Source License

/**
 * Thong ke trang thai cua khach hang/*w  w w  .  j a  v  a  2 s  . c o m*/
 *
 * @param service dich vu
 * @param staffCode ma nhan vien
 * @param categoryId id cua danh sach
 * @param beginLastUpdated tu ngay cap nhat lan cuoi
 * @param endLastUpdated den ngay cap nhat lan cuoi
 * @return
 */
public List<StatisticsCategoryListDTO> getStatisticsCategoryListByStaff(String service, String staffCode,
        String categoryId, String beginLastUpdated, String endLastUpdated) {
    List<StatisticsCategoryListDTO> lstStatisticsCategoryList = null;
    StringBuilder sqlQuery = new StringBuilder();
    List params = new ArrayList();
    sqlQuery.append("        SELECT code, ");
    sqlQuery.append("         status1, ");
    sqlQuery.append("         status2, ");
    sqlQuery.append("         status3, ");
    sqlQuery.append("         status4, ");
    sqlQuery.append("         status5, ");
    sqlQuery.append("         status6, ");
    sqlQuery.append("         status7, ");
    sqlQuery.append("         status8, ");
    sqlQuery.append("         status9, ");
    sqlQuery.append("         status10, ");
    sqlQuery.append("         status11, ");
    sqlQuery.append("         status12, ");
    sqlQuery.append("         status13, ");
    sqlQuery.append("         status14, ");
    sqlQuery.append("         status15 ");
    sqlQuery.append("       FROM ");
    sqlQuery.append("         (SELECT cl.code    AS code, ");
    sqlQuery.append("           cs.status        AS status, ");
    sqlQuery.append("           cs.tax_code ");
    sqlQuery.append("         FROM CUSTOMER_STATUS cs ");
    sqlQuery.append("             JOIN category_list cl ");
    sqlQuery.append("                ON cs.mine_name = cl.id ");
    sqlQuery.append("         WHERE           1         = 1 ");
    if (!DataUtil.isStringNullOrEmpty(service)) {
        sqlQuery.append("         AND cs.SERVICE      = ? ");
        params.add(service);
    }
    if (!DataUtil.isStringNullOrEmpty(categoryId)) {
        sqlQuery.append("         AND cs.MINE_NAME      = ? ");
        params.add(categoryId);
    }
    if (!DataUtil.isStringNullOrEmpty(beginLastUpdated)) {
        sqlQuery.append("         AND cs.LAST_UPDATED  >= to_date( ? ,'dd/MM/yyyy') ");
        params.add(beginLastUpdated);
    }
    if (!DataUtil.isStringNullOrEmpty(endLastUpdated)) {
        sqlQuery.append("         AND cs.LAST_UPDATED  <= to_date( ? ,'dd/MM/yyyy') + 1");
        params.add(endLastUpdated);
    }
    if (!DataUtil.isStringNullOrEmpty(staffCode)) {
        sqlQuery.append("         AND cs.staff_code    = ? ");
        params.add(staffCode);
    }
    sqlQuery.append("         ) PIVOT (COUNT(DISTINCT tax_code) ");
    sqlQuery.append("           FOR (status) IN ('1' AS status1,'2' AS status2,'3' AS status3,");
    sqlQuery.append("                            '4' AS status4,'5' AS status5,'6' AS status6,");
    sqlQuery.append("                            '7' AS status7,'8' AS status8,'9' AS status9,");
    sqlQuery.append("                            '10' AS status10,'11' AS status11,'12' AS status12,");
    sqlQuery.append("                            '13' AS status13,'14' AS status14, '15' AS status15)) ");
    sqlQuery.append("         ORDER BY code asc ");

    SQLQuery query;
    try {
        query = getSession().createSQLQuery(sqlQuery.toString());
        //Thuc hien chuyen du lieu lay ve thanh thanh doi tuong            
        query.setResultTransformer(Transformers.aliasToBean(StatisticsCategoryListDTO.class));
        query.addScalar("code", new StringType());
        query.addScalar("status1", new StringType());
        query.addScalar("status2", new StringType());
        query.addScalar("status3", new StringType());
        query.addScalar("status4", new StringType());
        query.addScalar("status5", new StringType());
        query.addScalar("status6", new StringType());
        query.addScalar("status7", new StringType());
        query.addScalar("status8", new StringType());
        query.addScalar("status9", new StringType());
        query.addScalar("status10", new StringType());
        query.addScalar("status11", new StringType());
        query.addScalar("status12", new StringType());
        query.addScalar("status13", new StringType());
        query.addScalar("status14", new StringType());
        query.addScalar("status15", new StringType());

        //Truyen cac tham so truyen vao de thuc hien tim kiem
        if (!DataUtil.isListNullOrEmpty(params)) {
            for (int i = 0; i < params.size(); i++) {
                query.setParameter(i, params.get(i));
            }
        }

        lstStatisticsCategoryList = query.list();
    } catch (Exception e) {
        e.printStackTrace();
    }

    return lstStatisticsCategoryList;
}

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

License:Open Source License

public List<StatusQuantityDTO> getStatusQuantity(String staffCode) {
    List<StatusQuantityDTO> lstStatusQuantity = null;
    StringBuilder sqlQuery = new StringBuilder();
    List params = new ArrayList();

    sqlQuery.append("        SELECT cs.STATUS status,  ");
    sqlQuery.append("          COUNT(*) quantity ");
    sqlQuery.append("        FROM customer_status cs ");
    sqlQuery.append("        WHERE cs.STATUS IN ");
    sqlQuery.append("          (SELECT par_code ");
    sqlQuery.append("          FROM APP_PARAMS ");
    sqlQuery.append("          WHERE PAR_TYPE = 'CUSTOMER_SERVICE_STATUS' ");
    sqlQuery.append("          AND STATUS     = 1 ");
    sqlQuery.append("          ) ");

    if (!DataUtil.isStringNullOrEmpty(staffCode)) {
        sqlQuery.append("        AND cs.STAFF_CODE = ? ");
        params.add(staffCode);//from www  . jav  a 2 s. com
    }
    sqlQuery.append("        GROUP BY cs.STATUS ");
    sqlQuery.append("        ORDER BY cs.STATUS ");

    SQLQuery query;
    try {
        query = getSession().createSQLQuery(sqlQuery.toString());
        //Thuc hien chuyen du lieu lay ve thanh thanh doi tuong            
        query.setResultTransformer(Transformers.aliasToBean(StatusQuantityDTO.class));
        query.addScalar("status", new StringType());
        query.addScalar("quantity", new StringType());
        //Truyen cac tham so truyen vao de thuc hien tim kiem
        if (!DataUtil.isListNullOrEmpty(params)) {
            for (int i = 0; i < params.size(); i++) {
                query.setParameter(i, params.get(i));
            }
        }

        lstStatusQuantity = query.list();
    } catch (Exception e) {
        e.printStackTrace();
    }

    return lstStatusQuantity;
}