Example usage for org.hibernate SQLQuery setMaxResults

List of usage examples for org.hibernate SQLQuery setMaxResults

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

Introduction

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

Prototype

@Override
    Query<R> setMaxResults(int maxResult);

Source Link

Usage

From source file:com.gp.cong.logisoft.hibernate.dao.lcl.LclBookingSegregationDao.java

public Boolean isCheckedSegregationDr(Long fileId) throws Exception {
    StringBuilder queryStr = new StringBuilder();
    queryStr.append("SELECT IF(COUNT(*)>0,true,false) as result FROM lcl_booking_segregation");
    queryStr.append(" WHERE child_file_number_id=:fileId");
    SQLQuery query = getCurrentSession().createSQLQuery(queryStr.toString());
    query.setParameter("fileId", fileId);
    query.addScalar("result", BooleanType.INSTANCE);
    return (Boolean) query.setMaxResults(1).uniqueResult();
}

From source file:com.itrus.ca.common.persistence.BaseDaoImpl.java

License:Open Source License

public SQLQuery findBySQL(final String sql, final int pageNo, final int numPerPage) {
    return (SQLQuery) (T) getSession().doReturningWork(new ReturningWork() {
        public SQLQuery execute(Connection connection) throws SQLException {
            SQLQuery sqlQuery = getSession().createSQLQuery(sql);
            if (pageNo > 0) {
                sqlQuery.setFirstResult((pageNo - 1) * numPerPage);
                sqlQuery.setMaxResults(numPerPage);
            }//from w  w w .j a va 2 s . co m

            return sqlQuery;
        }
    });
}

From source file:com.liusoft.dlog4j.dao.DlogDAO.java

License:Open Source License

/**
 * ?,(site=-1)//from  w  w w  .j a  v a2 s  .  co m
 * @param site
 * @param count
 * @return
 */
public static List listHotTags(int site, int count) {
    Session ssn = getSession();
    StringBuffer sql = new StringBuffer("SELECT tag_name,COUNT(*) FROM dlog_tag");
    if (site > 0)
        sql.append(" WHERE site_id=?");
    sql.append(" GROUP BY tag_name ORDER BY 2 DESC");
    SQLQuery query = ssn.createSQLQuery(sql.toString());
    if (site > 0)
        query.setInteger(0, site);
    query.setMaxResults(count);
    List tags = new ArrayList();
    List results = query.list();
    for (int i = 0; results != null && i < results.size(); i++) {
        tags.add(((Object[]) results.get(i))[0]);
    }
    return tags;
}

From source file:com.mothsoft.alexis.dao.DocumentDaoImpl.java

License:Apache License

@SuppressWarnings("unchecked")
public Graph getRelatedTerms(final String queryString, final Long userId, final int howMany) {
    final StopWatch stopWatch = new StopWatch();
    stopWatch.start();//from   w  w w. j a  va 2  s.  co m

    final FullTextQuery fullTextQuery = this.buildFullTextQuery(queryString, userId, NO_DATE, NO_DATE, false,
            DocumentState.MATCHED_TO_TOPICS, FullTextQuery.ID);

    // find the specified number of terms from the most recent 100 documents
    // that match the query
    final Sort sort = new Sort(new SortField("creationDate", SortField.LONG, true));
    fullTextQuery.setSort(sort);
    fullTextQuery.setFirstResult(0);
    fullTextQuery.setMaxResults(100);

    final List<Long> documentIds = new ArrayList<Long>(100);
    final List<Long> termIds = new ArrayList<Long>(100);

    final List<Object[]> results = fullTextQuery.list();

    for (final Object[] ith : results) {
        final Long id = (Long) ith[0];
        documentIds.add(id);
    }

    final Map<String, Node> nodes = new LinkedHashMap<String, Node>();
    final Node root = new Node(queryString, Boolean.TRUE);
    nodes.put(queryString, root);

    final Map<String, Edge> edges = new HashMap<String, Edge>();

    if (!documentIds.isEmpty()) {
        final Session session = (Session) this.em.getDelegate();
        final org.hibernate.SQLQuery termsQuery = session.createSQLQuery("SELECT term.id "
                + "        FROM document_term dt INNER JOIN term on term.id = dt.term_id "
                + "        WHERE dt.document_id IN (:documentIds) GROUP BY term.id ORDER BY SUM(dt.tf_idf) DESC");
        termsQuery.setParameterList("documentIds", documentIds);
        termsQuery.setMaxResults(100);
        termIds.addAll((List<Long>) termsQuery.list());
    }

    if (!documentIds.isEmpty() && !termIds.isEmpty()) {

        final Session session = (Session) this.em.getDelegate();
        final org.hibernate.SQLQuery associationsQuery = session.createSQLQuery(
                "SELECT CONCAT(a.term_value) term_a_value, CONCAT(b.term_value) term_b_value, SUM(da.association_weight) sum_weight "
                        + "      FROM document_association da "
                        + "      INNER JOIN term a ON da.term_a_id = a.id "
                        + "        AND a.part_of_speech NOT IN (1, 3, 18, 19, 25, 39, 40) "
                        + "        AND length(a.term_value) > 2 "
                        + "      INNER JOIN term b ON da.term_b_id = b.id "
                        + "        AND b.part_of_speech NOT IN (1, 3, 18, 19, 25, 39, 40) "
                        + "        AND length(b.term_value) > 2 "
                        + "      WHERE da.document_id IN (:documentIds) AND (da.term_a_id IN (:termIds) OR da.term_b_id IN (:termIds)) "
                        + "      GROUP BY a.id, b.id ORDER BY sum_weight DESC");
        associationsQuery.setParameterList("documentIds", documentIds);
        associationsQuery.setParameterList("termIds", termIds);
        associationsQuery.setMaxResults(howMany);

        final List<Object[]> relatedTermsResults = associationsQuery.list();

        final Set<String> aNodeKeys = new HashSet<String>();
        final Set<String> bNodeKeys = new HashSet<String>();

        for (final Object[] ith : relatedTermsResults) {
            final String a = (String) ith[0];
            final String b = (String) ith[1];

            if (!nodes.containsKey(a)) {
                final Node node = new Node(a);
                nodes.put(a, node);
            }

            if (!nodes.containsKey(b)) {
                final Node node = new Node(b);
                nodes.put(b, node);
            }

            if (a.equals(b)) {
                continue;
            }

            final String edgeKey = a + "||" + b;
            final String edgeKeyInverse = b + "||" + a;
            if (!edges.containsKey(edgeKey) && !edges.containsKey(edgeKeyInverse)) {
                final Node nodeA = nodes.get(a);
                final Node nodeB = nodes.get(b);

                aNodeKeys.add(a);
                bNodeKeys.add(b);

                final Edge edge = new Edge(nodeA, nodeB);
                edges.put(edgeKey, edge);
            }
        }

        // "orphan" handling, any b that is not also an a needs an edge from
        // root
        final Set<String> orphanKeys = new HashSet<String>();
        orphanKeys.addAll(bNodeKeys);
        orphanKeys.removeAll(aNodeKeys);

        for (final String orphanKey : orphanKeys) {
            final Node orphan = nodes.get(orphanKey);
            final Edge orphanToParent = new Edge(root, orphan);
            edges.put(root.getName() + "||" + orphan.getName(), orphanToParent);
        }
    }

    final List<Node> nodeList = new ArrayList<Node>(nodes.size());
    // keep root as first element
    nodes.remove(root.getName());
    nodeList.add(root);
    nodeList.addAll(nodes.values());

    final Graph graph = new Graph(nodeList, new ArrayList<Edge>(edges.values()));

    stopWatch.stop();
    logger.info("Related terms search took: " + stopWatch.toString());

    return graph;
}

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

/**
 * ? materialGrid.js ?(?)// w  w  w  .j av  a  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 ??(?)//from w  ww.  ja  v a 2  s. co 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

/**
 * ? ?//  ww w  . j ava 2s.  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;/*from  w ww .  j  a v a2s .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 w w .j a v a2s . 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 . ja v  a 2 s  .  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;
}