Example usage for org.hibernate SQLQuery addScalar

List of usage examples for org.hibernate SQLQuery addScalar

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

Introduction

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

Prototype

SQLQuery<T> addScalar(String columnAlias, Type type);

Source Link

Document

Declare a scalar query result.

Usage

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  w w.  ja  v  a2s. 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 2  s  .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("               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/*from w  ww. j a  v a  2s .  com*/
 *
 * @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  w  ww .ja v  a  2 s  . c o m
    }
    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;
}

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

License:Open Source License

public List<ObjectsDTO> getLstObjectsDTOByStaffId(String staffId) {
    List<ObjectsDTO> lstObjects;
    StringBuilder sql = new StringBuilder();

    sql.append("     SELECT DISTINCT a.OBJECT_ID objectId, ");
    sql.append("                     a.CODE code, ");
    sql.append("                     a.NAME name, ");
    sql.append("                     a.DESCRIPTION description, ");
    sql.append("                     a.OBJECT_TYPE objectType, ");
    sql.append("                     a.STATUS status, ");
    sql.append("                     a.URL url ");
    sql.append("        FROM OBJECTS a ");
    sql.append("        JOIN ROLE_OBJECTS b ");
    sql.append("           ON b.OBJECT_ID = a.OBJECT_ID ");
    sql.append("        AND b.ROLE_ID IN ");
    sql.append("              (SELECT a.ROLE_ID ");
    sql.append("              FROM roles a ");
    sql.append("              JOIN MAP_STAFF_ROLES b ");
    sql.append("              ON b.ROLE_ID   = a.ROLE_ID ");
    sql.append("              AND b.STAFF_ID = ? )");
    try {/* w  w  w  .j ava  2  s .c  o  m*/
        SQLQuery query = getSession().createSQLQuery(sql.toString());
        query.setResultTransformer(Transformers.aliasToBean(ObjectsDTO.class));
        query.addScalar("objectId", new StringType());
        query.addScalar("code", new StringType());
        query.addScalar("name", new StringType());
        query.addScalar("description", new StringType());
        query.addScalar("objectType", new StringType());
        query.addScalar("status", new StringType());
        query.addScalar("url", new StringType());
        //
        query.setParameter(0, staffId);

        lstObjects = query.list();
    } catch (Exception e) {
        e.printStackTrace();
        lstObjects = null;
    }
    if (DataUtil.isListNullOrEmpty(lstObjects)) {
        return null;
    } else {
        return lstObjects;
    }
}

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

License:Open Source License

public List<ObjectsDTO> getListObjectByRole(RolesDTO role) {
    List<ObjectsDTO> lstObjects;
    StringBuilder sql = new StringBuilder();
    //        List lstParams = new ArrayList();
    sql.append("     SELECT DISTINCT a.OBJECT_ID objectId, ");
    sql.append("                     a.CODE code, ");
    sql.append("                     a.NAME name,");
    sql.append("                     a.DESCRIPTION description, ");
    sql.append("                     a.OBJECT_TYPE objectType, ");
    sql.append("                     a.STATUS status, ");
    sql.append("                     a.URL url, ");
    sql.append("                     b.ID roleObjectId ");
    sql.append("        FROM OBJECTS a ");
    sql.append("        JOIN ROLE_OBJECTS b ");
    sql.append("             ON b.OBJECT_ID = a.OBJECT_ID ");
    sql.append("            AND b.ROLE_ID = ? ");
    try {//from w  ww.  ja va2s .  c o  m
        SQLQuery query = getSession().createSQLQuery(sql.toString());
        query.setResultTransformer(Transformers.aliasToBean(ObjectsDTO.class));
        query.addScalar("objectId", new StringType());
        query.addScalar("code", new StringType());
        query.addScalar("name", new StringType());
        query.addScalar("description", new StringType());
        query.addScalar("objectType", new StringType());
        query.addScalar("status", new StringType());
        query.addScalar("url", new StringType());
        query.addScalar("roleObjectId", new StringType());

        query.setParameter(0, role.getRoleId());

        lstObjects = query.list();
    } catch (Exception e) {
        e.printStackTrace();
        return null;
    }
    return lstObjects;
}

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

License:Open Source License

/**
 * Lay danh sach vai tro du nhan vien/*  w  w w  . j  a v a2  s  .  com*/
 *
 * @param staffId
 * @return
 */
public List<RolesDTO> getListRolesByStaffId(String staffId) {
    List<RolesDTO> lstRoles;
    StringBuilder sql = new StringBuilder();
    sql.append("     SELECT DISTINCT a.ROLE_ID roleId, ");
    sql.append("                     a.CODE code, ");
    sql.append("                     a.NAME name, ");
    sql.append("                     a.DESCRIPTION description, ");
    sql.append("                     a.STATUS status, ");
    sql.append("                     b.MAP_ID mapId ");
    sql.append("        FROM roles a ");
    sql.append("        JOIN MAP_STAFF_ROLES b ");
    sql.append("                  ON b.ROLE_ID   = a.ROLE_ID ");
    sql.append("                 AND b.STAFF_ID = ? ");
    try {
        SQLQuery query = getSession().createSQLQuery(sql.toString());
        query.setResultTransformer(Transformers.aliasToBean(RolesDTO.class));
        query.addScalar("roleId", new StringType());
        query.addScalar("code", new StringType());
        query.addScalar("name", new StringType());
        query.addScalar("description", new StringType());
        query.addScalar("status", new StringType());
        query.addScalar("mapId", new StringType());

        query.setParameter(0, staffId);

        lstRoles = query.list();
    } catch (Exception e) {
        e.printStackTrace();
        return null;
    }
    return lstRoles;
}

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

License:Open Source License

public List<TaxAuthorityDTO> getListProvinces() {
    List<TaxAuthorityDTO> lstTaxAuthorities;
    //        List params = new ArrayList();
    StringBuilder sqlQuery = new StringBuilder();
    sqlQuery.append("       SELECT a.MA_CQT maCqt, ");
    sqlQuery.append("         a.TEN_CQT tenCqt, ");
    sqlQuery.append("         a.MA_QUAN_HUYEN maQuanHuyen, ");
    sqlQuery.append("         a.MA_TINH maTinh, ");
    sqlQuery.append("         a.STATUS status, ");
    sqlQuery.append("         a.MA_MST maMST ");
    sqlQuery.append("       FROM tax_authority a ");
    sqlQuery.append("       WHERE SUBSTR(a.MA_CQT,4,2) ='00' ");

    try {/*from   w w w  .java  2  s . c o m*/
        SQLQuery query = getSession().createSQLQuery(sqlQuery.toString());
        //Thuc hien chuyen du lieu lay ve thanh thanh doi tuong            
        query.setResultTransformer(Transformers.aliasToBean(TaxAuthorityDTO.class));
        query.addScalar("maCqt", new StringType());
        query.addScalar("tenCqt", new StringType());
        query.addScalar("maQuanHuyen", new StringType());
        query.addScalar("maTinh", new StringType());
        query.addScalar("status", new StringType());
        query.addScalar("maMST", new StringType());

        //Truyen cac tham so truyen vao de thuc hien tim kiem
        //            for (int i = 0; i < params.size(); i++) {
        //                query.setParameter(i, params.get(i));
        //            }
        //Day du lieu ra danh sach doi tuong
        lstTaxAuthorities = query.list();
    } catch (Exception e) {
        e.printStackTrace();
        lstTaxAuthorities = null;
    }
    return lstTaxAuthorities;
}

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

License:Open Source License

public List<TaxAuthorityDTO> getListTaxAuthorityFromMineName(String mineName, Map<String, String> map) {
    String startFromDate = map.get("startFromDate");
    String provider = map.get("provider");
    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");
    List<TaxAuthorityDTO> lstTaxAuthorities = null;
    StringBuilder sqlQuery = new StringBuilder();
    sqlQuery.append("       SELECT ID id,   ");
    sqlQuery.append("         MA_CQT maCqt,   ");
    sqlQuery.append("         MA_QUAN_HUYEN maQuanHuyen,   ");
    sqlQuery.append("         MA_TINH maTinh,   ");
    sqlQuery.append("         STATUS status,   ");
    sqlQuery.append("         TEN_CQT tenCqt   ");
    sqlQuery.append("       FROM TAX_AUTHORITY ta   ");
    sqlQuery.append("       WHERE ta.MA_CQT = ANY   ");
    sqlQuery.append("         ( SELECT DISTINCT c.TAX_AUTHORITY   ");
    sqlQuery.append("         FROM CUSTOMER c   ");
    sqlQuery.append("         JOIN    TERM_INFORMATION ti ON ti.TAX_CODE = c.TAX_CODE ");
    sqlQuery.append("           WHERE ti.IS_CONTACT_INFO is NULL AND ti.MINE_NAME = ANY (:mineName)   ");
    if (!DataUtil.isStringNullOrEmpty(provider)) {
        sqlQuery.append("         and lower(ti.PROVIDER) = ANY (:provider)   ");
    }// w w w  .j ava  2  s.c o m
    if (!DataUtil.isStringNullOrEmpty(startFromDate)) {
        sqlQuery.append("         and ti.START_TIME >= TO_DATE(:startFromDate,'dd/MM/yyyy') - 1   ");
    }
    if (!DataUtil.isStringNullOrEmpty(endFromDate)) {
        sqlQuery.append("         and ti.START_TIME <= TO_DATE(:endFromDate,'dd/MM/yyyy') + 1    ");
    }
    if (!DataUtil.isStringNullOrEmpty(startToDate)) {
        sqlQuery.append("         and ti.END_TIME >= TO_DATE(:startToDate,'dd/MM/yyyy') - 1   ");
    }
    if (!DataUtil.isStringNullOrEmpty(endToDate)) {
        sqlQuery.append("         and ti.END_TIME <= TO_DATE(:endToDate,'dd/MM/yyyy')   + 1   ");
    }
    if (!DataUtil.isStringNullOrEmpty(fromDateRegister)) {
        sqlQuery.append("   AND ti.DATE_REGISTER >= TO_DATE(:fromDateRegister,'dd/MM/yyyy') - 1 ");
    }
    if (!DataUtil.isStringNullOrEmpty(toDateRegister)) {
        sqlQuery.append("   AND ti.DATE_REGISTER <= TO_DATE(:toDateRegister,'dd/MM/yyyy')  + 1");
    }
    sqlQuery.append("       AND NOT " + "                EXISTS ( " + "                    SELECT "
            + "                        cs.tax_code, " + "                        cs.mine_name "
            + "                    FROM " + "                        customer_status cs"
            + "                    WHERE " + "                            cs.tax_code = ti.tax_code "
            + "                        AND " + "                            cs.mine_name = ti.mine_name "
            + "                ) ");
    sqlQuery.append("       )  ");
    sqlQuery.append("       ORDER BY ta.TEN_CQT asc ");
    SQLQuery query;
    try {
        query = getSession().createSQLQuery(sqlQuery.toString());
        //Thuc hien chuyen du lieu lay ve thanh thanh doi tuong            
        query.setResultTransformer(Transformers.aliasToBean(TaxAuthorityDTO.class));
        query.addScalar("id", new StringType());
        query.addScalar("maCqt", new StringType());
        query.addScalar("maQuanHuyen", new StringType());
        query.addScalar("maTinh", new StringType());
        query.addScalar("status", new StringType());
        query.addScalar("tenCqt", new StringType());
        query.setParameterList("mineName", DataUtil.parseInputListString(mineName));
        if (!DataUtil.isStringNullOrEmpty(provider)) {
            query.setParameterList("provider", DataUtil.parseInputListString(provider.toLowerCase()));
        }
        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);
        }
        lstTaxAuthorities = query.list();
    } catch (Exception e) {
        e.printStackTrace();
    }
    return lstTaxAuthorities;
}

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

License:Open Source License

public List<TaxAuthorityDTO> getListTaxAuthorityFromMineName(String mineName, String staffCode) {
    List<TaxAuthorityDTO> lstTaxAuthorities = null;
    if (DataUtil.isStringNullOrEmpty(staffCode)) {
        return getListTaxAuthorityFromMineName(mineName);
    }//  w  w  w  . j a  va  2 s .c  o  m
    StringBuilder sqlQuery = new StringBuilder();
    sqlQuery.append("              SELECT ID id,   ");
    sqlQuery.append("                MA_CQT maCqt,  ");
    sqlQuery.append("                MA_QUAN_HUYEN maQuanHuyen,  ");
    sqlQuery.append("                MA_TINH maTinh,  ");
    sqlQuery.append("                STATUS status,  ");
    sqlQuery.append("                TEN_CQT tenCqt  ");
    sqlQuery.append("              FROM TAX_AUTHORITY  ");
    sqlQuery.append("              WHERE MA_CQT IN  ");
    sqlQuery.append("                ( SELECT DISTINCT cs.TAX_AUTHORITY  ");
    sqlQuery.append("                FROM customer_status cs  ");
    sqlQuery.append("                WHERE 1=1 ");
    if (!DataUtil.isStringNullOrEmpty(mineName)) {
        if (mineName.contains(",")) {
            sqlQuery.append("             AND cs.mine_name IN (:mineName) ");
        } else {
            sqlQuery.append("             AND cs.mine_name = :mineName ");
        }
        //            sqlQuery.append("            AND cs.mine_name = :mineName  ");
    }
    if (!DataUtil.isStringNullOrEmpty(staffCode)) {
        sqlQuery.append("                AND cs.staff_code  = :staffCode ");
    }
    sqlQuery.append("             ) ORDER BY maCqt");
    SQLQuery query;
    try {
        query = getSession().createSQLQuery(sqlQuery.toString());
        //Thuc hien chuyen du lieu lay ve thanh thanh doi tuong            
        query.setResultTransformer(Transformers.aliasToBean(TaxAuthorityDTO.class));
        query.addScalar("id", new StringType());
        query.addScalar("maCqt", new StringType());
        query.addScalar("maQuanHuyen", new StringType());
        query.addScalar("maTinh", new StringType());
        query.addScalar("status", new StringType());
        query.addScalar("tenCqt", new StringType());

        if (!DataUtil.isStringNullOrEmpty(mineName)) {
            if (mineName.contains(",")) {
                query.setParameterList("mineName", DataUtil.parseInputListString(mineName));
            } else {
                query.setParameter("mineName", mineName);
            }
        }
        if (!DataUtil.isStringNullOrEmpty(staffCode)) {
            query.setParameter("staffCode", staffCode);
        }

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