Example usage for org.hibernate SQLQuery setFirstResult

List of usage examples for org.hibernate SQLQuery setFirstResult

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

Introduction

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

Prototype

@Override
    Query<R> setFirstResult(int startPosition);

Source Link

Usage

From source file:com.poka.dao.impl.BaseDao.java

@Override
public List<T> findBySql(String sql, int page, int rows) {
    SQLQuery q = getCurrentSession().createSQLQuery(sql);
    return q.setFirstResult((page - 1) * rows).setMaxResults(rows)
            .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
}

From source file:com.poka.dao.impl.BaseDao.java

@Override
public List<T> findBySql(String sql, Map<String, Object> params, int page, int rows) {
    SQLQuery q = getCurrentSession().createSQLQuery(sql);
    if (params != null && !params.isEmpty()) {
        for (String key : params.keySet()) {
            q.setParameter(key, params.get(key));
        }//from   w  w w  . j a  va2 s  .  c  o  m
    }
    return q.setFirstResult((page - 1) * rows).setMaxResults(rows)
            .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
}

From source file:com.proper.uip.common.core.dao.HibernateDao.java

License:Apache License

@SuppressWarnings("unchecked")
public <K> Page<K> pageSQLQuery(PageConfig pageConfig, SQLQuery sqlQuery, Object... values) {
    Page<K> page = new Page<K>(pageConfig);
    String totalSql = "select count(*) as total from(" + sqlQuery.getQueryString() + ")";
    Query countQuery = this.getSession().createSQLQuery(totalSql);
    setParams(countQuery, values);/*from  w w w  .ja va2 s.c om*/
    Number total = (Number) countQuery.uniqueResult();
    if (total != null) {
        page.setTotal(total.longValue());
        sqlQuery.setFirstResult(page.getOffset()).setMaxResults(page.getPageSize());
        setParams(sqlQuery, values);
        List<K> list = sqlQuery.list();
        page.setRows(list);
    } else {
        page.setTotal(0);
        page.setRows(new ArrayList<K>());
    }
    return page;
}

From source file:com.sunesoft.ancon.core.saleContract.application.SaleContractServiceImpl.java

@Override
public PagedResult<SaleContractDtoModel> findPages(SaleContractCriteria contractCriteria) {
    Criteria criteria = dbSupport.getSession().createCriteria(SaleContract.class);

    //sql//  ww w.  java  2s.  c  o  m
    String querySql = "select y.*,sum(c.output_value) sum_output_value from (select x.*,sum(g.gathering_money) sum_gathering_money      "
            + "      from (select  s.*,sum(b.price_tax_count_money) sum_billing_money          "
            + "          from    ancon_sys_sale_contract s left join ancon_sys_billing b  on b.sale_contract_id=s.id             "
            + "       and  b.is_active=1  group by id) x       "
            + "     left join ancon_sys_gathering_management g on x.id=g.sale_contract_id and g.is_active=1   group by x.id) y "
            + " left join ancon_sys_construction_value c on y.id=c.sale_contract_id AND c.is_active=1 ";

    String countSql = "select count(*) from ancon_sys_sale_contract y";

    String group = " GROUP BY y.id  ";

    String order = " order by y.contract_begin_time desc ";

    StringBuffer whereCondition = new StringBuffer(" where y.is_active =1 ");
    //        criteria.add(Restrictions.eq("isActive",true));

    if (contractCriteria.getBeginTime() != null) {
        whereCondition.append(" and y.contract_begin_time >");
        //whereCondition.append("'");
        whereCondition.append(DateHelper.formatDate(contractCriteria.getBeginTime(), "yyyyMMdd"));
        //whereCondition.append("'");
    }
    if (contractCriteria.getEndTime() != null) {
        whereCondition.append(" and y.contract_begin_time <");
        //whereCondition.append("'");
        whereCondition.append(DateHelper.formatDate(contractCriteria.getEndTime(), "yyyyMMdd"));
        //  whereCondition.append("'");
    }

    if (contractCriteria.getCompanyId() != null) {
        whereCondition.append(" and y.companyId = ");
        whereCondition.append("'");
        whereCondition.append(contractCriteria.getCompanyId());
        whereCondition.append("'");
    }

    if (!StringUtils.isNullOrWhiteSpace(contractCriteria.getJiaFangName())) {

        whereCondition.append(" and y.jia_fang_name like ");
        whereCondition.append("'%");
        whereCondition.append(contractCriteria.getJiaFangName().trim());
        whereCondition.append("%'");
    }

    if (!StringUtils.isNullOrWhiteSpace(contractCriteria.getContractType())) {

        whereCondition.append(" and y.contract_type like ");
        whereCondition.append("'%");
        whereCondition.append(contractCriteria.getContractType().trim());
        whereCondition.append("%'");

    }
    if (!StringUtils.isNullOrWhiteSpace(contractCriteria.getProjectMajor())) {

        whereCondition.append(" and y.project_major like ");
        whereCondition.append("'%");
        whereCondition.append(contractCriteria.getProjectMajor().trim());
        whereCondition.append("%'");

    }
    if (!StringUtils.isNullOrWhiteSpace(contractCriteria.getContractStatus())) {

        whereCondition.append(" and y.contract_status like ");
        whereCondition.append("'%");
        whereCondition.append(contractCriteria.getContractStatus().trim());
        whereCondition.append("%'");

    }

    //?
    //
    if (!StringUtils.isNullOrWhiteSpace(contractCriteria.getBidNotice())
            || !StringUtils.isNullOrWhiteSpace(contractCriteria.getConstructLicense())
            || !StringUtils.isNullOrWhiteSpace(contractCriteria.getFinishCheck())
            || !StringUtils.isNullOrWhiteSpace(contractCriteria.getProjectSettlement())
            || !StringUtils.isNullOrWhiteSpace(contractCriteria.getContractIsReturn())
            || !StringUtils.isNullOrWhiteSpace(contractCriteria.getNumber())) {
        if (!StringUtils.isNullOrWhiteSpace(contractCriteria.getBidNotice())) {
            if (contractCriteria.getBidNotice().equals("a_bidNotice")) {
                order = " order by y.bid_notice ASC ";//?
            } else {
                order = " order by y.bid_notice DESC ";//?
            }
        }

        //?? CALL usp_dynamic_total_report();
        if (!StringUtils.isNullOrWhiteSpace(contractCriteria.getConstructLicense())) {
            if (contractCriteria.getConstructLicense().equals("a_constrctLicense")) {
                order = " order by y.construct_license ASC ";
            } else {
                order = " order by y.construct_license DESC ";
            }
        }

        //?
        if (!StringUtils.isNullOrWhiteSpace(contractCriteria.getFinishCheck())) {
            if (contractCriteria.getFinishCheck().equals("a_finishCheck")) {
                order = " order by y.finish_check ASC ";
            } else {
                order = " order by y.finish_check DESC ";
            }
        }

        //?
        if (!StringUtils.isNullOrWhiteSpace(contractCriteria.getProjectSettlement())) {
            if (contractCriteria.getProjectSettlement().equals("a_projectSettlement")) {
                order = " order by y.project_settlement ASC ";
            } else {
                order = " order by y.project_settlement DESC ";
            }
        }

        //???
        if (!StringUtils.isNullOrWhiteSpace(contractCriteria.getContractIsReturn())) {
            if (contractCriteria.getContractIsReturn().equals("a_contractIsReturn")) {
                order = " order by y.contract_is_return ASC ";
            } else {
                order = " order by y.contract_is_return DESC ";
            }
        }

        //???
        if (!StringUtils.isNullOrWhiteSpace(contractCriteria.getNumber())) {
            if (contractCriteria.getNumber().equals("a_number")) {
                order = " order by y.num ASC ";
            } else {
                order = " order by y.num DESC ";
            }
        }
    } else {
        order = " order by y.contract_begin_time DESC ";
    }

    querySql += whereCondition.toString() + group + order;
    //?
    Long totalCount = dbSupport.getCountBySql(countSql + whereCondition.toString(), null);

    //        int totalCount = ((Long) criteria.setProjection(Projections.rowCount()).uniqueResult()).intValue();
    //        criteria.setProjection(null);

    SQLQuery query = dbSupport.getSession().createSQLQuery(querySql);

    query.setFirstResult((contractCriteria.getPageNumber() - 1) * contractCriteria.getPageSize())
            .setMaxResults(contractCriteria.getPageSize());
    //??
    List<SaleContractDtoModel> result = dbSupport.queryForObjects(SaleContractDtoModel.class, query);

    for (SaleContractDtoModel dto : result) {
        // ??-?
        Date beginTime = new Date();
        Date endTime = dto.getContract_end_time();
        long intervalMilli = endTime.getTime() - beginTime.getTime();

        int days = (int) (intervalMilli / (24 * 60 * 60 * 1000));
        dto.setSurplus_day(days);
    }

    //        List<Map<String,Object>> result =query.list();

    return new PagedResult<>(result, contractCriteria.getPageNumber(), contractCriteria.getPageSize(),
            totalCount.intValue());
}

From source file:com.sysware.customize.hd.investment.baseData.vendor.VendorDaoImpl.java

/**
 * ? materialGrid.js ?(?)/*from   w ww  .java 2 s .c o m*/
 * @param materialVo
 * return gridData
 */
public List<MaterialVo> getAll(MaterialVo materialVo, Pager pager) {
    List<MaterialVo> returnList = new ArrayList<MaterialVo>();
    String sql = "FROM t_material a ,( \n" + "SELECT materialid,COUNT(distinct vendorid) AS materialNums\n"
            + "FROM t_vendor_material\n" + "WHERE EXISTS (SELECT 1 FROM T_VENDOR d\n"
            + "WHERE d.vendorid = vendorid\n" + "AND d.type IN ('1','2')\n"
            + "AND d.evaluation_status = '2') \n" + "GROUP BY materialid) b\n"
            + "WHERE a.materialid = b.materialid(+) \n";

    BigDecimal count = (BigDecimal) materialDao.getHibernateSession().createSQLQuery("select count(1) " + sql)
            .uniqueResult();
    pager.setRecordCount(count.intValue());

    sql = "SELECT  a.materialid,\n" + "a.materialitemname,\n" + "a.desingnation,\n" + "a.materialstandard,\n"
            + "a.techniccondition,\n" + "a.demension,\n"
            + "to_char(nvl(a.warningvalue,0),'FM9999990.000099'),\n" + "a.preserveperiod,\n"
            + "to_char(nvl(a.referenceprice,0),'FM9999990.000099'),\n" + "a.remarks,\n" + "a.parentid,\n"
            + "a.materialitemcode, \n" + "nvl(b.materialNums,0), \n"
            + "(SELECT c.materialtypename FROM t_materialcatalog c WHERE c.materialcatalogid = a.parentid and rownum = 1) \n"
            + sql;

    SQLQuery query = materialDao.getHibernateSession().createSQLQuery(sql);
    if (pager != null) {
        query.setFirstResult(pager.getStart());
        query.setMaxResults(pager.getPageSize());
    }
    //      listSize = query.list().size();//???

    if (count.intValue() > 0) {
        List<Object[]> list = query.list();
        for (Object[] obj : list) {
            MaterialVo vo = new MaterialVo();

            vo.setMaterialid(String.valueOf(obj[0]));
            vo.setMaterialItemName(String.valueOf(obj[1]));
            vo.setDesingnation(String.valueOf(obj[2]));
            vo.setMaterialStandard(String.valueOf(obj[3]));
            vo.setTechnicCondition(String.valueOf(obj[4]));
            vo.setDemension(String.valueOf(obj[5]));
            vo.setWarningValue(Double.parseDouble(String.valueOf(obj[6])));
            vo.setPreservePeriod(String.valueOf(obj[7]));
            vo.setReferencePrice(Double.parseDouble(String.valueOf(obj[8])));
            vo.setRemarks(String.valueOf(obj[9]));
            vo.setParentId(String.valueOf(obj[10]));
            vo.setMaterialitemcode(String.valueOf(obj[11]));
            vo.setMaterialLinkVendorNum(Integer.parseInt(String.valueOf(obj[12])));
            vo.setParentidName(String.valueOf(obj[13]));//(?)

            returnList.add(vo);
        }
    }
    return returnList;
}

From source file:com.sysware.customize.hd.investment.baseData.vendor.VendorDaoImpl.java

/**
 * ? materialGrid.js ??(?)/*  w  ww . jav a  2 s. c o m*/
 * @param materialVo
 * return gridData
 */
public List<MaterialVo> getSelect(MaterialVo materialVo, Pager pager) {
    List<MaterialVo> returnList = new ArrayList<MaterialVo>();

    String materialItemName = StringUtils.isBlank(materialVo.getMaterialItemName()) ? ""
            : materialVo.getMaterialItemName();
    String desingnation = StringUtils.isBlank(materialVo.getDesingnation()) ? "" : materialVo.getDesingnation();
    String materialStandard = StringUtils.isBlank(materialVo.getMaterialStandard()) ? ""
            : materialVo.getMaterialStandard();
    String demension = StringUtils.isBlank(materialVo.getDemension()) ? "" : materialVo.getDemension();
    String technicCondition = StringUtils.isBlank(materialVo.getTechnicCondition()) ? ""
            : materialVo.getTechnicCondition();
    String materialitemcode = StringUtils.isBlank(materialVo.getMaterialitemcode()) ? ""
            : materialVo.getMaterialitemcode();
    String parentidName = StringUtils.isBlank(materialVo.getParentidName()) ? "" : materialVo.getParentidName();
    String materialcatalogName = StringUtils.isBlank(materialVo.getMaterialcatalogName()) ? ""
            : materialVo.getMaterialcatalogName();

    String sql = "FROM t_material a ,( \n" + "SELECT materialid,COUNT(distinct vendorid) AS materialNums\n"
            + "FROM t_vendor_material\n" + "WHERE EXISTS (SELECT 1 FROM T_VENDOR d\n"
            + "WHERE d.vendorid = vendorid\n" + "AND d.type IN ('1','2')\n"
            + "AND d.evaluation_status = '2') \n" + "GROUP BY materialid) b\n"
            + "WHERE a.materialid = b.materialid(+) \n";
    if (!materialItemName.equals("")) {
        sql += " AND a.materialitemname like '%" + materialItemName + "%' \n";
    }
    if (!desingnation.equals("")) {
        sql += " AND a.desingnation LIKE '%" + desingnation + "%' \n";
    }
    if (!materialStandard.equals("")) {
        sql += " AND a.materialstandard LIKE '%" + materialStandard + "%' \n";
    }
    if (!demension.equals("")) {
        sql += " AND a.demension LIKE '%" + demension + "%' \n";
    }
    if (!technicCondition.equals("")) {
        sql += " AND a.techniccondition LIKE '%" + technicCondition + "%' \n";
    }
    if (!materialitemcode.equals("")) {
        sql += " AND a.materialitemcode LIKE '%" + materialitemcode + "%' \n";
    }
    //?
    if (!parentidName.equals("")) {
        //sql += " AND a.materialitemname = '"+materialItemName+"' \n";
        sql += " AND a.parentid IN (SELECT d.materialcatalogid  \n"
                + "                    FROM t_materialcatalog d  \n"
                + "                    WHERE d.materialtypename LIKE '%" + parentidName + "%' ) \n";
    }
    //
    if (!materialcatalogName.equals("")) {
        //sql += " AND a.materialitemname = '"+materialItemName+"' \n";
        sql += " AND a.parentid IN (SELECT d.materialcatalogid  \n"
                + "                    FROM t_materialcatalog d " + "    start with d.materialtypename LIKE '%"
                + materialcatalogName + "%' connect by prior d.materialcatalogid = d.parentid ) ";
    }
    BigDecimal count = (BigDecimal) materialDao.getHibernateSession().createSQLQuery("select count(1) " + sql)
            .uniqueResult();
    pager.setRecordCount(count.intValue());

    sql = "SELECT  a.materialid,\n" + "a.materialitemname,\n" + "a.desingnation,\n" + "a.materialstandard,\n"
            + "a.techniccondition,\n" + "a.demension,\n"
            + "to_char(nvl(a.warningvalue,0),'FM9999990.000099'),\n" + "a.preserveperiod,\n"
            + "to_char(nvl(a.referenceprice,0),'FM9999990.000099'),\n" + "a.remarks,\n" + "a.parentid,\n"
            + "a.materialitemcode, \n" + "nvl(b.materialNums,0), \n"
            + "(SELECT c.materialtypename FROM t_materialcatalog c WHERE c.materialcatalogid = a.parentid and rownum = 1) \n"
            + sql;

    SQLQuery query = materialDao.getHibernateSession().createSQLQuery(sql);
    if (pager != null) {
        query.setFirstResult(pager.getStart());
        query.setMaxResults(pager.getPageSize());
    }

    if (count.intValue() > 0) {
        List<Object[]> list = query.list();
        for (Object[] obj : list) {
            MaterialVo vo = new MaterialVo();

            vo.setMaterialid(String.valueOf(obj[0]));
            vo.setMaterialItemName(String.valueOf(obj[1]));
            vo.setDesingnation(String.valueOf(obj[2]));
            vo.setMaterialStandard(String.valueOf(obj[3]));
            vo.setTechnicCondition(String.valueOf(obj[4]));
            vo.setDemension(String.valueOf(obj[5]));
            vo.setWarningValue(Double.parseDouble(String.valueOf(obj[6])));
            vo.setPreservePeriod(String.valueOf(obj[7]));
            vo.setReferencePrice(Double.parseDouble(String.valueOf(obj[8])));
            vo.setRemarks(String.valueOf(obj[9]));
            vo.setParentId(String.valueOf(obj[10]));
            vo.setMaterialitemcode(String.valueOf(obj[11]));
            vo.setMaterialLinkVendorNum(Integer.parseInt(String.valueOf(obj[12])));
            vo.setParentidName(String.valueOf(obj[13]));//(?)

            returnList.add(vo);
        }
    }
    return returnList;
}

From source file:com.sysware.customize.hd.investment.engineeringProject.executiveManagement.EngineeringProjectExecutiveManagementDaoImp.java

/**
 * ? ?// w w w  .  j a v a 2  s. c  o m
 * @param vo
 * @param pager
 * @return
 */
public List<FixedAssetAcceptanceApplyModelVo> getVendorByGroup(FixedAssetAcceptanceApplyModelVo vo,
        Pager pager) {
    List<FixedAssetAcceptanceApplyModelVo> returnList = new ArrayList<FixedAssetAcceptanceApplyModelVo>();
    //String time = StringUtils.isBlank(vo.getTime()) ? "" : vo.getTime() ;//?????
    String fuzzyQueryString = StringUtils.isBlank(vo.getFuzzyQueryString()) ? "" : vo.getFuzzyQueryString();

    String sql = "SELECT\n" + "a.vendorid,\n" + "a.vendorname,\n" + "a.egal,\n" + "a.phone,\n" + "a.email,\n"
            + "a.zipcode\n" + "FROM t_vendor a \n" + "WHERE 1=1 \n" + "AND EVALUATION_STATUS=2 \n";

    if (fuzzyQueryString != "" && fuzzyQueryString != "null" && fuzzyQueryString != null) {
        sql += "AND a.vendorname LIKE '%" + fuzzyQueryString + "%' or a.egal LIKE '%" + fuzzyQueryString
                + "%' or a.phone LIKE '%" + fuzzyQueryString + "%' \n";
    }

    SQLQuery query = this.getHibernateSession().createSQLQuery(sql);
    int listSize = query.list().size();//?,
    pager.setRecordCount(listSize);//?
    if (pager != null) {
        query.setFirstResult(pager.getStart());
        query.setMaxResults(pager.getPageSize());
    }
    listSize = query.list().size();//???

    if (listSize > 0) {
        List<Object[]> list = query.list();
        for (Object[] obj : list) {
            FixedAssetAcceptanceApplyModelVo thisVo = new FixedAssetAcceptanceApplyModelVo();

            /*thisVo.setEngineeringContractId(String.valueOf(obj[0]));//id
            thisVo.setProjectCode(String.valueOf(obj[1]));//?
            thisVo.setProjectName(String.valueOf(obj[2]));//??
             */
            thisVo.setVendorId(String.valueOf(obj[0]));//id
            thisVo.setContractmanuFacturers(String.valueOf(obj[1]));//??
            thisVo.setContactPerson(String.valueOf(obj[2]));//
            thisVo.setContractmanuFacturersTel(String.valueOf(obj[3]));//?
            returnList.add(thisVo);
        }
    }
    return returnList;

}

From source file:com.viettel.hqmc.DAOHE.FeeDAOHE.java

public GridResult getLstPayment(FeeForm searchFeeForm, Long fileId, int start, int count, String sortField) {

    FilesDAOHE fdhe = new FilesDAOHE();
    Files filesBo = fdhe.findById(fileId);
    String sql;// ww w .  ja v a  2  s .c  o  m
    if (filesBo != null
            && (filesBo.getStatus().equals(Constants.FILE_STATUS.APPROVED)
                    || filesBo.getStatus().equals(Constants.FILE_STATUS.COMPARED)
                    || filesBo.getStatus().equals(Constants.FILE_STATUS.COMPARED_FAIL)
                    || filesBo.getStatus().equals(Constants.FILE_STATUS.ALERT_COMPARISON))
            && filesBo.getIsSignPdf() != null && (filesBo.getIsSignPdf() != 0)) {
        sql = "from fee f inner join fee_payment_info fpi " + "on f.fee_id = fpi.fee_id "
                + "where fpi.file_id = ? " + "and f.is_Active=1 " + "and fpi.is_Active=1";
    } else if (filesBo != null && filesBo.getStatus().equals(Constants.FILE_STATUS.GIVE_BACK)
            && filesBo.getIsSignPdf() != null && (filesBo.getIsSignPdf() != 0)) {
        sql = "from fee f inner join fee_payment_info fpi on f.fee_id = fpi.fee_id " + "where fpi.file_id = ? "
                + "and f.is_Active=1 " + "and fpi.is_Active=1 " + "and (f.fee_type = 2 or f.fee_type = 1)";
    } else {
        sql = "from fee f inner join fee_payment_info fpi on f.fee_id = fpi.fee_id " + "where fpi.file_id = ? "
                + "and f.is_Active=1 " + "and fpi.is_Active=1 and f.fee_type = 2";
    }

    List lstParam = new ArrayList();
    lstParam.add(fileId);

    if (searchFeeForm.getFeeName() != null && !"".equals(searchFeeForm.getFeeName().trim())) {
        sql += "and  f.fee_name like ? ";
        String param = "%" + searchFeeForm.getFeeName() + "%";
        lstParam.add(param);
    }
    if (searchFeeForm.getPrice() != null && !"".equals(searchFeeForm.getPrice())) {
        sql += " and f.price = ?";
        lstParam.add(searchFeeForm.getPrice());
    }
    if (searchFeeForm.getFeeType() != null && searchFeeForm.getFeeType() != -1) {
        sql += " and f.fee_type = ?";
        lstParam.add(searchFeeForm.getFeeType());
    }
    if (searchFeeForm.getStatus() != null && searchFeeForm.getStatus() != -1) {
        sql += " and fpi.status = ?";
        lstParam.add(searchFeeForm.getStatus());
    }
    SQLQuery countQuery = (SQLQuery) getSession().createSQLQuery("select count(*) " + sql);
    SQLQuery query = (SQLQuery) getSession().createSQLQuery("select f.fee_Id," + "f.fee_Name,"
            + "f.description," + "fpi.cost," + "f.fee_Type," + "fpi.status," + "fpi.fee_Payment_Type_Id, "
            + "f.price," + "fpi.payment_Person," + "fpi.payment_Date," + "fpi.payment_Info," + "fpi.bill_path,"
            + "fpi.payment_info_id," + "fpi.payment_code," + "fpi.payment_confirm," + "fpi.bill_code,"
            + "fpi.date_confirm," + "fpi.comment_reject  " + sql);
    for (int i = 0; i < lstParam.size(); i++) {
        countQuery.setParameter(i, lstParam.get(i));
        query.setParameter(i, lstParam.get(i));
    }

    query.setFirstResult(start);
    query.setMaxResults(count);
    int total = Integer.parseInt(countQuery.uniqueResult().toString());
    List lstResult = query.list();
    FeePaymentFileForm item = new FeePaymentFileForm();
    List result = new ArrayList<FeePaymentFileForm>();

    //Hiepvv 4Star
    boolean isHaveFee = false;
    if (filesBo != null && filesBo.getFileType() != null && filesBo.getFileType() > 0L) {
        ProcedureDAOHE pDAO = new ProcedureDAOHE();
        Procedure p = pDAO.findById(filesBo.getFileType());
        if (p != null) {
            if (p.getDescription() != null
                    && p.getDescription().equals(Constants.FILE_DESCRIPTION.ANNOUNCEMENT_4STAR)) {
                isHaveFee = true;
            }
            if (p.getDescription() != null
                    && p.getDescription().equals(Constants.FILE_DESCRIPTION.ANNOUNCEMENT_FILE05)) {
                isHaveFee = true;
            }
        }
    }
    for (int i = 0; i < lstResult.size(); i++) {
        Object[] row = (Object[]) lstResult.get(i);
        if (row.length > 0) {
            if (row[0] != null && !"".equals(row[0])) {
                item.setFeeId(Long.parseLong(row[0].toString()));
            }
            if (row[1] != null && !"".equals(row[1])) {
                item.setFeeName(row[1].toString());
            }
            if (row[2] != null && !"".equals(row[2])) {
                item.setDescription(row[2].toString());
            }
            //Hiepvv 4Star
            if (isHaveFee) {
                if (row[3] != null && !"".equals(row[3])) {
                    item.setPrice(Long.parseLong(row[3].toString()));
                }
            } else if (row[7] != null && !"".equals(row[7])) {
                item.setPrice(Long.parseLong(row[7].toString()));
            }
            if (row[4] != null && !"".equals(row[4])) {
                item.setFeeType(Long.parseLong(row[4].toString()));
            }
            if (row[5] != null && !"".equals(row[5])) {
                item.setStatus(Long.parseLong(row[5].toString()));
            }
            if (row[6] != null && !"".equals(row[6])) {
                item.setFeePaymentType(Long.parseLong(row[6].toString()));
            }
            if (row[8] != null && !"".equals(row[8])) {
                item.setPaymentPerson(row[8].toString());
            }
            if (row[9] != null && !"".equals(row[9])) {
                item.setPaymentDate(row[9].toString());
            }
            if (row[10] != null && !"".equals(row[10])) {
                item.setPaymentInfo(row[10].toString());
            }
            if (row[11] != null && !"".equals(row[11])) {
                item.setBillPath(row[11].toString());
            }
            if (row[12] != null && !"".equals(row[12])) {
                item.setPaymentInfoId(Long.parseLong(row[12].toString()));
            }
            if (row[13] != null && !"".equals(row[13])) {
                item.setPaymentCode(row[13].toString());
            }
            if (row[14] != null && !"".equals(row[14])) {
                item.setPaymentConfirm(row[14].toString());
            }
            if (row[15] != null && !"".equals(row[15])) {
                item.setBillCode(row[15].toString());
            }
            if (row[16] != null && !"".equals(row[16])) {
                Date confirmDate = (Date) row[16];
                item.setDateConfirm(DateTimeUtils.convertDateToString(confirmDate, "dd/MM/yyyy"));
            }
            if (row[17] != null && !"".equals(row[17])) {
                item.setCommentReject(row[17].toString());
            }
        }
        result.add(item);
        item = new FeePaymentFileForm();
    }
    GridResult gr = new GridResult(total, result);
    return gr;
}

From source file:com.viettel.hqmc.DAOHE.FeeDAOHE.java

/**
 *
 * @param searchFeeFormNew//from w ww.  j  a  va  2  s . c o m
 * @param userId
 * @param start
 * @param count
 * @param sortField
 * @return
 */
public GridResult getLstFeeManage(FeePaymentFileForm searchFeeFormNew, Long userId, int start, int count,
        String sortField) {

    UsersDAOHE udhe = new UsersDAOHE();
    Users user = udhe.findById(userId);

    String sql = "from files f inner join fee_payment_info fpi on f.file_id = fpi.file_id "
            + "where fpi.fee_id in (select f.fee_id from fee f "
            + "                         where f.fee_type = 1 and f.is_active = 1 ) "
            //+ "and f.agency_id =" + user.getDeptId() + " " // comment 11/11/2014 viethd
            + "and f.agency_id = ? "
            + "and (f.status = 15 or f.status=16 or f.status=6 or f.status=22 or f.status = 23) "
            //+ "and (f.status=6) "
            + "and fpi.is_active=1 " + "and f.is_active = 1 ";
    //+ "and fpi.status <> 0 ";

    List lstParam = new ArrayList();
    lstParam.add(user.getDeptId());

    if (searchFeeFormNew.getFileCode() != null && !"".equals(searchFeeFormNew.getFileCode().trim())) {
        //sql += " and lower(f.file_code) like '%" + searchFeeFormNew.getFileCode().toLowerCase().trim().replace("/", "//").replace("_", "/_").replace("%", "/%") + "%'"; // comment 11/11/2014 viethd
        sql += " and lower(f.file_code) like ? ";
        String param = "%" + searchFeeFormNew.getFileCode().toLowerCase().trim() + "%";
        lstParam.add(param);
    }
    if (searchFeeFormNew.getFilesCode() != null && !"".equals(searchFeeFormNew.getFilesCode())) {
        // comment 11/11/2014 viethd
        sql += " and lower(fpi.files_code) like ? ";
        String param = "%" + searchFeeFormNew.getFilesCode().toString().toLowerCase().trim() + "%";
        lstParam.add(param);
        //lstParam.add(searchFeeFormNew.getFilesCode());
    }
    if (searchFeeFormNew.getProductName() != null && !"".equals(searchFeeFormNew.getProductName().trim())) {
        //sql += " and lower(f.product_name) like '%" + searchFeeFormNew.getProductName().toLowerCase().trim().replace("/", "//").replace("_", "/_").replace("%", "/%") + "%'"; // comment 11/1/2014 viethd
        sql += " and lower(f.product_name) like ? ";
        String param = "%" + searchFeeFormNew.getProductName().toLowerCase().trim() + "%";
        lstParam.add(param);
    }
    if (searchFeeFormNew.getCost() != null && !"".equals(searchFeeFormNew.getCost())) {
        //sql += " and lower(fpi.cost) like '%" + searchFeeFormNew.getCost().toString().toLowerCase().trim().replace("/", "//").replace("_", "/_").replace("%", "/%") + "%'";
        sql += " and lower(fpi.cost) like ? ";
        String param = "%" + searchFeeFormNew.getCost().toString().toLowerCase().trim() + "%";
        lstParam.add(param);
    }
    if (searchFeeFormNew.getStatus() != null && searchFeeFormNew.getStatus() != -1) {
        if (searchFeeFormNew.getSearchType() == null) {
            if (searchFeeFormNew.getStatus() == 0) {
                sql += " and fpi.status > 2";
            } else {
                //sql += "and fpi.status = " + searchFeeFormNew.getStatus(); // 11/11/2014 viethd
                sql += "and fpi.status = ? ";
                lstParam.add(searchFeeFormNew.getStatus());
            }
        }
    }

    if (searchFeeFormNew.getBusinessName() != null && !"".equals(searchFeeFormNew.getBusinessName().trim())) {
        //sql += " and lower(f.business_name) like '%" + searchFeeFormNew.getBusinessName().toLowerCase().trim().replace("/", "//").replace("_", "/_").replace("%", "/%") + "%'";
        sql += " and lower(f.business_name) like ? ";
        String param = "%" + searchFeeFormNew.getBusinessName().toLowerCase().trim() + "%";
        lstParam.add(param);
    }
    if (searchFeeFormNew.getFeePaymentType() != null && searchFeeFormNew.getFeePaymentType() != -1) {
        // 11/11/2014 viethd
        //sql += " and fpi.fee_Payment_type_id = " + searchFeeFormNew.getFeePaymentType();
        sql += " and fpi.fee_Payment_type_id = ? ";
        lstParam.add(searchFeeFormNew.getFeePaymentType());

    }
    if (searchFeeFormNew.getPaymentConfirm() != null
            && !"".equals(searchFeeFormNew.getPaymentConfirm().trim())) {
        // comment 11/11/2014 viethd
        //sql += " and lower(f.product_name) like '%" + searchFeeFormNew.getProductName().toLowerCase().trim().replace("/", "//").replace("_", "/_").replace("%", "/%") + "%'";
        sql += " and lower(fpi.payment_confirm) like ? ";
        String param = "%" + searchFeeFormNew.getPaymentConfirm().toLowerCase().trim() + "%";
        lstParam.add(param);
    }

    if (searchFeeFormNew.getDateFrom() != null) {
        // 11/11/2014 viethd
        //sql += " and fpi.payment_date >= to_date('" + DateTimeUtils.convertDateToString(searchFeeFormNew.getDateFrom(), "dd/MM/yyyy") + " 00:00:00','dd/MM/yyyy hh24:mi:ss') ";
        sql += " and fpi.payment_date >= to_date( ? ,'dd/MM/yyyy hh24:mi:ss') ";
        String param = "" + DateTimeUtils.convertDateToString(searchFeeFormNew.getDateFrom(), "dd/MM/yyyy")
                + " 00:00:00";
        lstParam.add(param);
    }
    if (searchFeeFormNew.getDateTo() != null) {
        // 11/11/2014 viethd
        //sql += " and fpi.payment_date <= to_date('" + DateTimeUtils.convertDateToString(searchFeeFormNew.getDateTo(), "dd/MM/yyyy") + " 23:59:59','dd/MM/yyyy hh24:mi:ss') ";
        sql += " and fpi.payment_date <= to_date( ?,'dd/MM/yyyy hh24:mi:ss') ";
        String param = "" + DateTimeUtils.convertDateToString(searchFeeFormNew.getDateTo(), "dd/MM/yyyy")
                + " 23:59:59";
        lstParam.add(param);
    }
    // ngay xac nhan
    if (searchFeeFormNew.getDateConfirmSearchFrom() != null) {
        // 11/11/2014 viethd
        //sql += " and fpi.date_confirm >= to_date('" + DateTimeUtils.convertDateToString(searchFeeFormNew.getDateConfirmSearchFrom(), "dd/MM/yyyy") + " 00:00:00','dd/MM/yyyy hh24:mi:ss') ";
        sql += " and fpi.date_confirm >= to_date( ?,'dd/MM/yyyy hh24:mi:ss') ";
        String param = ""
                + DateTimeUtils.convertDateToString(searchFeeFormNew.getDateConfirmSearchFrom(), "dd/MM/yyyy")
                + " 00:00:00";
        lstParam.add(param);
    }
    if (searchFeeFormNew.getDateConfirmSearchTo() != null) {
        // 11/11/2014 viethd
        //sql += " and fpi.date_confirm <= to_date('" + DateTimeUtils.convertDateToString(searchFeeFormNew.getDateConfirmSearchTo(), "dd/MM/yyyy") + " 23:59:59','dd/MM/yyyy hh24:mi:ss') ";
        sql += " and fpi.date_confirm <= to_date( ?,'dd/MM/yyyy hh24:mi:ss') ";
        String param = ""
                + DateTimeUtils.convertDateToString(searchFeeFormNew.getDateConfirmSearchTo(), "dd/MM/yyyy")
                + " 23:59:59";
        lstParam.add(param);
    }

    if (searchFeeFormNew.getSearchType() != null && searchFeeFormNew.getSearchType() == 5) {
        sql += " and fpi.status = 1 ";
    }
    if (searchFeeFormNew.getSearchType() != null && searchFeeFormNew.getSearchType() == 6) {
        sql += " and fpi.status > 2 ";
    }

    //sql += " order by fpi.payment_date desc ";
    //sql += " order by fpi.payment_date asc ";
    sql += " order by f.send_date asc ";
    SQLQuery countQuery = (SQLQuery) getSession()
            .createSQLQuery("select count (distinct fpi.payment_info_id) " + sql);
    SQLQuery query = (SQLQuery) getSession().createSQLQuery("select distinct f.file_code,"
            + "f.product_name,fpi.payment_date,fpi.cost,fpi.bill_path,"
            + "fpi.fee_payment_type_id,fpi.status,fpi.fee_id,fpi.file_id,fpi.payment_info_id,"
            + "fpi.payment_person,f.business_name,fpi.payment_code,fpi.payment_confirm,"
            + "fpi.bill_code,fpi.date_confirm,fpi.comment_reject,f.business_address,f.send_date,fpi.files_code "
            + sql);
    query.setFirstResult(start);
    query.setMaxResults(count);
    int paramSize = lstParam.size();
    for (int i = 0; i < paramSize; i++) {
        countQuery.setParameter(i, lstParam.get(i));
        query.setParameter(i, lstParam.get(i));
    }
    int total = Integer.parseInt(countQuery.uniqueResult().toString());
    List lstResult = query.list();
    FeePaymentFileForm item = new FeePaymentFileForm();
    List result = new ArrayList<FeePaymentFileForm>();
    if (lstResult != null && lstResult.size() > 0) {
        for (int i = 0; i < lstResult.size(); i++) {
            Object[] row = (Object[]) lstResult.get(i);
            if (row.length > 0) {
                if (row[0] != null && !"".equals(row[0])) {
                    item.setFileCode(row[0].toString());
                }
                if (row[1] != null && !"".equals(row[1])) {
                    item.setProductName(row[1].toString());
                }
                if (row[2] != null && !"".equals(row[2])) {
                    Date paymentDate = (Date) row[2];
                    item.setPaymentDate(DateTimeUtils.convertDateToString(paymentDate, "dd/MM/yyyy"));
                }
                if (row[3] != null && !"".equals(row[3])) {
                    item.setCost(Long.parseLong(row[3].toString()));
                }
                if (row[7] != null && !"".equals(row[7])) {
                    item.setFeeId(Long.parseLong(row[7].toString()));
                }
                if (row[4] != null && !"".equals(row[4])) {
                    item.setBillPath(row[4].toString());
                }
                if (row[5] != null && !"".equals(row[5])) {
                    item.setFeePaymentType(Long.parseLong(row[5].toString()));
                }
                if (row[6] != null && !"".equals(row[6])) {
                    item.setStatus(Long.parseLong(row[6].toString()));
                }
                if (row[8] != null && !"".equals(row[8])) {
                    item.setFileId(Long.parseLong(row[8].toString()));
                }
                if (row[9] != null && !"".equals(row[9])) {
                    item.setPaymentInfoId(Long.parseLong(row[9].toString()));
                }
                if (row[10] != null && !"".equals(row[10])) {
                    item.setPaymentPerson((row[10].toString()));
                }
                if (row[11] != null && !"".equals(row[11])) {
                    item.setBusinessName((row[11].toString()));
                }
                if (row[12] != null && !"".equals(row[12])) {
                    item.setPaymentCode(((row[12].toString())));
                }
                if (row[13] != null && !"".equals(row[13])) {
                    item.setPaymentConfirm((row[13].toString()));
                }
                if (row[14] != null && !"".equals(row[14])) {
                    item.setBillCode((row[14].toString()));
                }
                if (row[15] != null && !"".equals(row[15])) {
                    Date confirmDate = (Date) row[15];
                    item.setDateConfirm(DateTimeUtils.convertDateToString(confirmDate, "dd/MM/yyyy"));
                }
                if (row[16] != null && !"".equals(row[16])) {
                    item.setCommentReject(row[16].toString());
                }
                if (row[17] != null && !"".equals(row[17])) {
                    item.setBusinessAddress(row[17].toString());
                }
                if (row[19] != null && !"".equals(row[19])) {
                    item.setBusinessAddress(row[19].toString());
                }

            }
            result.add(item);
            item = new FeePaymentFileForm();
        }
    }
    GridResult gr = new GridResult(total, result);
    return gr;
}

From source file:com.viettel.hqmc.DAOHE.FeeDAOHE.java

/**
 *
 * @param searchFeeFormNew/*w  w w.  j ava2s.  c o  m*/
 * @param userId
 * @param start
 * @param count
 * @param sortField
 * @return
 */
public GridResult getLstFeePayManage(FeePaymentFileForm searchFeeFormNew, Long userId, int start, int count,
        String sortField) {
    String sql = "from files f inner join fee_payment_info fpi on f.file_id = fpi.file_id "
            + "where fpi.fee_id in (select f.fee_id from fee f where f.fee_type = 2 and f.is_active = 1 ) "
            + "and fpi.is_active = 1 " + "and ((f.is_active = 1) or (f.is_active=2))  "
            //+ "and f.agency_id = ? "
            + "and (f.user_Signed is not null or f.status = 18) " + "and f.status <> -1";
    UsersDAOHE udhe = new UsersDAOHE();
    //        Users user = udhe.findById(userId);
    List lstParam = new ArrayList();
    //        lstParam.add(user.getDeptId());
    if (searchFeeFormNew.getBusinessName() != null && !"".equals(searchFeeFormNew.getBusinessName().trim())) {
        // comment 11/11/2014 viethd
        //sql += " and lower(f.business_name) like '%" + searchFeeFormNew.getBusinessName().toLowerCase().trim().replace("/", "//").replace("_", "/_").replace("%", "/%") + "%'";
        sql += " and lower(f.business_name) like ? ";
        String param = "%" + searchFeeFormNew.getBusinessName().toLowerCase().trim() + "%";
        lstParam.add(param);
    }
    if (searchFeeFormNew.getFileCode() != null && !"".equals(searchFeeFormNew.getFileCode().trim())) {
        // comment 11/11/2014 viethd
        //sql += " and lower(f.file_code) like '%" + searchFeeFormNew.getFileCode().toLowerCase().trim().replace("/", "//").replace("_", "/_").replace("%", "/%") + "%'";
        sql += " and lower(f.file_code) like ? ";
        String param = "%" + searchFeeFormNew.getFileCode().toLowerCase().trim() + "%";
        lstParam.add(param);
    }
    if (searchFeeFormNew.getProductName() != null && !"".equals(searchFeeFormNew.getProductName().trim())) {
        // comment 11/11/2014 viethd
        //sql += " and lower(f.product_name) like '%" + searchFeeFormNew.getProductName().toLowerCase().trim().replace("/", "//").replace("_", "/_").replace("%", "/%") + "%'";
        sql += " and lower(f.product_name) like ? ";
        String param = "%" + searchFeeFormNew.getProductName().toLowerCase().trim() + "%";
        lstParam.add(param);
    }
    if (searchFeeFormNew.getPaymentConfirm() != null
            && !"".equals(searchFeeFormNew.getPaymentConfirm().trim())) {
        sql += " and lower(fpi.payment_confirm) like ? ";
        String param = "%" + searchFeeFormNew.getPaymentConfirm().toLowerCase().trim() + "%";
        lstParam.add(param);
    }

    if (searchFeeFormNew.getCost() != null && !"".equals(searchFeeFormNew.getCost())) {
        // comment 11/11/2014 viethd
        //sql += " and lower(fpi.cost) like '%" + searchFeeFormNew.getCost().toString().toLowerCase().trim().replace("/", "//").replace("_", "/_").replace("%", "/%") + "%'";
        sql += " and lower(fpi.cost) like ? ";
        String param = "%" + searchFeeFormNew.getCost().toString().toLowerCase().trim() + "%";
        lstParam.add(param);
    }
    if (searchFeeFormNew.getFilesCode() != null && !"".equals(searchFeeFormNew.getFilesCode())) {
        // comment 11/11/2014 viethd
        sql += " and lower(fpi.files_code) like ? ";
        String param = "%" + searchFeeFormNew.getFilesCode().toString().toLowerCase().trim() + "%";
        lstParam.add(param);
        //lstParam.add(searchFeeFormNew.getFilesCode());
    }

    if (searchFeeFormNew.getStatus() != null && searchFeeFormNew.getStatus() != -1) {
        if (searchFeeFormNew.getSearchType() == null) {
            if (searchFeeFormNew.getStatus() == 0) {
                sql += " and fpi.status > 2";
            } else {
                //sql += "and fpi.status = " + searchFeeFormNew.getStatus(); // 11/11/2014 viethd
                sql += "and fpi.status = ? ";
                lstParam.add(searchFeeFormNew.getStatus());
            }
        }
    }
    if (searchFeeFormNew.getFeePaymentType() != null && searchFeeFormNew.getFeePaymentType() != -1) {
        // 11/11/2014 viethd
        //sql += " and fpi.fee_Payment_type_id = " + searchFeeFormNew.getFeePaymentType() + " ";
        sql += " and fpi.fee_Payment_type_id = ? ";
        lstParam.add(searchFeeFormNew.getFeePaymentType());
    }
    if (searchFeeFormNew.getDateFrom() != null) {
        // 11/11/2014 viethd
        //sql += " and fpi.payment_date >= to_date('" + DateTimeUtils.convertDateToString(searchFeeFormNew.getDateFrom(), "dd/MM/yyyy") + " 00:00:00','dd/MM/yyyy hh24:mi:ss') ";
        sql += " and fpi.payment_date >= to_date(?, 'dd/MM/yyyy hh24:mi:ss') ";
        lstParam.add("" + DateTimeUtils.convertDateToString(searchFeeFormNew.getDateFrom(), "dd/MM/yyyy")
                + " 00:00:00");
    }
    if (searchFeeFormNew.getDateTo() != null) {
        // 11/11/2014 viethd
        //sql += " and fpi.payment_date <= to_date('" + DateTimeUtils.convertDateToString(searchFeeFormNew.getDateTo(), "dd/MM/yyyy") + " 23:59:59','dd/MM/yyyy hh24:mi:ss') ";
        sql += " and fpi.payment_date <= to_date(?, 'dd/MM/yyyy hh24:mi:ss') ";
        lstParam.add("" + DateTimeUtils.convertDateToString(searchFeeFormNew.getDateTo(), "dd/MM/yyyy")
                + " 23:59:59");
    }
    // ngay xac nhan
    if (searchFeeFormNew.getDateConfirmSearchFrom() != null) {
        // 11/11/2014 viethd
        //sql += " and fpi.date_confirm >= to_date('" + DateTimeUtils.convertDateToString(searchFeeFormNew.getDateConfirmSearchFrom(), "dd/MM/yyyy") + " 00:00:00','dd/MM/yyyy hh24:mi:ss') ";
        sql += " and fpi.date_confirm >= to_date(?, 'dd/MM/yyyy hh24:mi:ss') ";
        lstParam.add(""
                + DateTimeUtils.convertDateToString(searchFeeFormNew.getDateConfirmSearchFrom(), "dd/MM/yyyy")
                + " 00:00:00");
    }
    if (searchFeeFormNew.getDateConfirmSearchTo() != null) {
        // 11/11/2014 viethd
        //sql += " and fpi.date_confirm <= to_date('" + DateTimeUtils.convertDateToString(searchFeeFormNew.getDateConfirmSearchTo(), "dd/MM/yyyy") + " 23:59:59','dd/MM/yyyy hh24:mi:ss') ";
        sql += " and fpi.date_confirm <= to_date(?, 'dd/MM/yyyy hh24:mi:ss') ";
        lstParam.add(
                "" + DateTimeUtils.convertDateToString(searchFeeFormNew.getDateConfirmSearchTo(), "dd/MM/yyyy")
                        + " 23:59:59");
    }
    // hieptq them nhom san pham 17.11.14
    if (searchFeeFormNew.getProductType() != null && searchFeeFormNew.getProductType() != -1) {
        if (searchFeeFormNew.getProductType() == 1) {
            sql += " and fpi.cost = 1500000 ";
        } else {
            sql += " and fpi.cost = 500000 ";
        }
    }

    //hieptq update searchType
    //searchType = 1 searchFeeFormNew.status=" + 1 + "&searchFeeFormNew.productType=" + 2 + "&searchFeeFormNew.feePaymentType=-1
    if (searchFeeFormNew.getSearchType() != null && searchFeeFormNew.getSearchType() == 1) {
        sql += " and fpi.status = 1 and fpi.cost = 500000 ";
    }
    //searchType = 2 searchFeeFormNew.status=" + 0 + "&searchFeeFormNew.productType=" + 2 + "&searchFeeFormNew.feePaymentType=-1
    if (searchFeeFormNew.getSearchType() != null && searchFeeFormNew.getSearchType() == 2) {
        sql += " and fpi.status > 2 and fpi.cost = 500000 ";
    }
    //searchType = 3 searchFeeFormNew.status=" + 1 + "&searchFeeFormNew.productType=" + 1 + "&searchFeeFormNew.feePaymentType=-1
    if (searchFeeFormNew.getSearchType() != null && searchFeeFormNew.getSearchType() == 3) {
        sql += " and fpi.status = 1 and fpi.cost = 1500000 ";
    }
    //searchType = 4 searchFeeFormNew.status=" + 0 + "&searchFeeFormNew.productType=" + 1 + "&searchFeeFormNew.feePaymentType=-1
    if (searchFeeFormNew.getSearchType() != null && searchFeeFormNew.getSearchType() == 4) {
        sql += " and fpi.status > 2 and fpi.cost = 1500000 ";
    }
    //sql += " order by fpi.payment_date desc ";
    //sql += " order by fpi.payment_date asc ";
    sql += " order by f.send_date asc ";
    SQLQuery countQuery = (SQLQuery) getSession()
            .createSQLQuery("select count (distinct fpi.payment_info_id) " + sql);
    SQLQuery query = (SQLQuery) getSession().createSQLQuery("select distinct f.business_name,f.product_name,"
            + "fpi.payment_date,fpi.cost,fpi.bill_path,fpi.fee_payment_type_id,fpi.status,"
            + "fpi.fee_id,fpi.file_id,fpi.payment_info_id,fpi.payment_person,f.file_code,"
            + "fpi.payment_code,fpi.payment_confirm,fpi.bill_code,fpi.date_confirm,"
            + "fpi.comment_reject,f.business_address,f.send_date,fpi.files_code  " + sql);
    query.setFirstResult(start);
    query.setMaxResults(count);
    int paramSize = lstParam.size();
    for (int i = 0; i < paramSize; i++) {
        countQuery.setParameter(i, lstParam.get(i));
        query.setParameter(i, lstParam.get(i));
    }
    int total = Integer.parseInt(countQuery.uniqueResult().toString());
    List lstResult = query.list();
    FeePaymentFileForm item = new FeePaymentFileForm();
    List result = new ArrayList<FeePaymentFileForm>();
    if (lstResult != null && lstResult.size() > 0) {
        for (int i = 0; i < lstResult.size(); i++) {
            Object[] row = (Object[]) lstResult.get(i);
            if (row.length > 0) {
                if (row[0] != null && !"".equals(row[0])) {
                    item.setBusinessName(row[0].toString());
                }
                if (row[1] != null && !"".equals(row[1])) {
                    item.setProductName(row[1].toString());
                }
                if (row[2] != null && !"".equals(row[2])) {
                    Date paymentDate = (Date) row[2];
                    item.setPaymentDate(DateTimeUtils.convertDateToString(paymentDate, "dd/MM/yyyy"));
                }
                if (row[3] != null && !"".equals(row[3])) {
                    item.setCost(Long.parseLong(row[3].toString()));
                }
                if (row[7] != null && !"".equals(row[7])) {
                    item.setFeeId(Long.parseLong(row[7].toString()));
                }
                if (row[4] != null && !"".equals(row[4])) {
                    item.setBillPath(row[4].toString());
                }
                if (row[5] != null && !"".equals(row[5])) {
                    item.setFeePaymentType(Long.parseLong(row[5].toString()));
                }
                if (row[6] != null && !"".equals(row[6])) {
                    item.setStatus(Long.parseLong(row[6].toString()));
                }
                if (row[8] != null && !"".equals(row[8])) {
                    item.setFileId(Long.parseLong(row[8].toString()));
                }
                if (row[9] != null && !"".equals(row[9])) {
                    item.setPaymentInfoId(Long.parseLong(row[9].toString()));
                }
                if (row[10] != null && !"".equals(row[10])) {
                    item.setPaymentPerson((row[10].toString()));
                }
                if (row[11] != null && !"".equals(row[11])) {
                    item.setFileCode((row[11].toString()));
                }
                if (row[12] != null && !"".equals(row[12])) {
                    item.setPaymentCode((row[12].toString()));
                }
                if (row[13] != null && !"".equals(row[13])) {
                    item.setPaymentConfirm((row[13].toString()));
                }
                if (row[14] != null && !"".equals(row[14])) {
                    item.setBillCode((row[14].toString()));
                }
                if (row[15] != null && !"".equals(row[15])) {
                    Date confirmDate = (Date) row[15];
                    item.setDateConfirm(DateTimeUtils.convertDateToString(confirmDate, "dd/MM/yyyy"));
                }
                if (row[16] != null && !"".equals(row[16])) {
                    item.setCommentReject(row[16].toString());
                }
                if (row[17] != null && !"".equals(row[17])) {
                    item.setBusinessAddress(row[17].toString());
                }
                if (row[19] != null && !"".equals(row[19])) {
                    item.setFilesCode(row[19].toString());
                }
            }
            result.add(item);
            item = new FeePaymentFileForm();
        }
    }
    GridResult gr = new GridResult(total, result);
    return gr;
}