Example usage for org.hibernate SQLQuery uniqueResult

List of usage examples for org.hibernate SQLQuery uniqueResult

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

Introduction

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

Prototype

R uniqueResult();

Source Link

Document

Convenience method to return a single instance that matches the query, or null if the query returns no results.

Usage

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  www . ja  va 2 s . c  om
    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 .  ja  v a2 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  ww  .  j a v a2  s.c  om*/
 * @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;
}

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

/**
 *
 * @param userId//w  w w.  j  av a 2 s . co m
 * @param status
 * @param type
 * @return
 */
public int getCountFileToProcessFeePayManage(Long userId, Long status, Long type) {
    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 = 2 and f.is_active = 1 ) "
            + "and fpi.is_active = 1 " + "and f.agency_id = ? " + "and f.is_active = 1 "
            + "and (f.user_Signed is not null or f.status = 18) " + "and f.status <> 0 ";

    List lstParam = new ArrayList();
    lstParam.add(user.getDeptId());
    if (status == 0) {
        sql += " and fpi.status > 2";
    } else {
        //sql += "and fpi.status = " + searchFeeFormNew.getStatus(); // 11/11/2014 viethd
        sql += "and fpi.status = 1 ";

    }
    // hieptq them nhom san pham 17.11.14
    if (type == 1) {
        sql += " and fpi.cost = 1500000";
    } else {
        sql += " and fpi.cost = 500000 ";
    }

    sql += " order by fpi.payment_date desc ";
    SQLQuery countQuery = (SQLQuery) getSession()
            .createSQLQuery("select count (distinct fpi.payment_info_id) " + sql);
    //hieptq update them agencyId 28.11.14
    int paramSize = lstParam.size();
    for (int i = 0; i < paramSize; i++) {
        countQuery.setParameter(i, lstParam.get(i));
    }
    int total = Integer.parseInt(countQuery.uniqueResult().toString());
    return total;
}

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

/**
 *
 * @param userId//from   www  .j  av a2  s .co m
 * @param status
 * @return
 */
// hieptq - 15.11.14 dem le phi man hinh ke toan
public int getCountFileToProcessFeeManage(Long userId, Long status) {
    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 fpi.is_active=1 " + "and f.is_active = 1 " + "and fpi.status <> 0 ";

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

    if (status == 0) {
        sql += " and fpi.status > 2";
    } else {
        //sql += "and fpi.status = " + searchFeeFormNew.getStatus(); // 11/11/2014 viethd
        sql += "and fpi.status = 1 ";
    }
    sql += " order by fpi.payment_date desc ";
    SQLQuery countQuery = (SQLQuery) getSession()
            .createSQLQuery("select count (distinct fpi.payment_info_id) " + sql);
    int paramSize = lstParam.size();
    for (int i = 0; i < paramSize; i++) {
        countQuery.setParameter(i, lstParam.get(i));
    }
    int total = Integer.parseInt(countQuery.uniqueResult().toString());
    return total;
}

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

/**
 * danh sach hoa don doanh nghiep/*from   ww  w .  j av a2  s. co m*/
 *
 * @param fileId
 * @param start
 * @param count
 * @param sortField
 * @return
 */
public GridResult getLstPayment(Long fileId, int start, int count, String sortField) {

    //        String sql = "from Fee f left join Fee_Payment_Info fpi on f.fee_Id = fpi.fee_Id where f.fee_Id "
    //                     + "in (select fp.fee_Id from Fee_Procedure fp where fp.procedure_Id = ((select fi.file_Type from Files fi where fi.file_Id = ?)) union "
    //                     + "select ff.fee_Id from Fee_File ff where ff.file_Id = ? )  and f.is_Active = 1";
    String 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";
    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  "
                    + sql);

    query.setLong(0, fileId);
    //query.setLong(1, fileId);
    countQuery.setParameter(0, fileId);
    //countQuery.setParameter(1, fileId);
    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>();

    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());
            }
            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());
            }
        }
        result.add(item);
        item = new FeePaymentFileForm();
    }
    GridResult gr = new GridResult(total, result);
    return gr;
}

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

/**
 * quan ly nop phi/*w ww.  j  ava2s  .c o  m*/
 *
 * @param start
 * @param count
 * @param sortField
 * @return
 */
public GridResult getLstFeeManage(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 = 1 )";
    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 "
                    + sql);
    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>();

    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])) {
                item.setPaymentDate(row[2].toString());
            }
            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()));
            }

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

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

public int getCountSelectRECEIVE_PAYHomePage() {
    try {//from w  w w. ja v a 2 s.  c o  m
        String sql = "select count(distinct fpi.PAYMENT_INFO_ID)" + " 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"
                + " and (f.user_Signed is not null or f.status = 18)" + " and f.status <> 0"
                + " and fpi.status > 2 ";
        //            String sql = "select count(distinct fpi.PAYMENT_INFO_ID) from Files f "
        //                    + "inner join "
        //                    + "fee_payment_info fpi on f.file_id = fpi.file_id "
        //                    + "inner join "
        //                    + "fee fe on fe.fee_id = fpi.fee_id and fe.fee_type = 2 and fe.is_active = 1 "
        //                    + "where fpi.is_active = 1 and fpi.status <> 1 "
        //                    + "and f.is_active = 1 "
        //                    + "and (f.is_temp is null or f.is_temp = 0) "
        //                    + "and (f.user_Signed is not null or f.status = 18) "
        //                    + "and f.status <> 0 ";
        SQLQuery countQuery = (SQLQuery) getSession().createSQLQuery(sql);
        int total = Integer.parseInt(countQuery.uniqueResult().toString());
        return total;
    } catch (NumberFormatException | HibernateException ex) {
        LogUtil.addLog(ex);//binhnt sonar a160901
        return 0;
    }
}

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

public int getCountSelectAPPROVE_PAYHomePage() {
    try {/*from  ww  w.  j a  v a2s  . c  om*/
        String sql = "select count(distinct fpi.PAYMENT_INFO_ID) from Files f " + "inner join "
                + "fee_payment_info fpi on f.file_id = fpi.file_id " + "inner join "
                + "fee fe on fe.fee_id = fpi.fee_id and fe.fee_type = 1 and fe.is_active = 1 "
                + "where fpi.is_active = 1 " + "and f.is_active = 1 "
                + "and (f.is_temp is null or f.is_temp = 0) " + "and f.status = "
                + Constants.FILE_STATUS.APPROVED + "and fpi.status > 2 ";
        SQLQuery countQuery = (SQLQuery) getSession().createSQLQuery(sql);
        int total = Integer.parseInt(countQuery.uniqueResult().toString());
        return total;
    } catch (NumberFormatException | HibernateException ex) {
        LogUtil.addLog(ex);//binhnt sonar a160901
        return 0;
    }
}

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

/**
 * ho so cho tra/*w w w.j  a  v  a 2  s  . c  om*/
 *
 * @return
 */
public int getCountSelectWaitGiveBackHomePage() {
    try {
        List lstParam = new ArrayList();
        /*
         binhnt update 150211 ti u truy vn
         */
        String sql = "select count(f.file_id) from Files f " + "where f.is_active = 1"
                + " and (f.is_temp is null or f.is_temp = 0) " + " and f.status in (?,?,?,?,?)";
        lstParam.add(Constants.FILE_STATUS.ALERT_COMPARISON);
        lstParam.add(Constants.FILE_STATUS.REVIEW_COMPARISON);
        lstParam.add(Constants.FILE_STATUS.REVIEW_COMPARISON_FAIL);
        lstParam.add(Constants.FILE_STATUS.COMPARED_FAIL);
        lstParam.add(Constants.FILE_STATUS.COMPARED);
        SQLQuery countQuery = (SQLQuery) getSession().createSQLQuery(sql);
        for (int i = 0; i < lstParam.size(); i++) {
            countQuery.setParameter(i, lstParam.get(i));
        }
        int total = Integer.parseInt(countQuery.uniqueResult().toString());
        return total;
    } catch (NumberFormatException | HibernateException ex) {
        LogUtil.addLog(ex);//binhnt sonar a160901
        return 0;
    }
}