Example usage for org.hibernate SQLQuery setParameter

List of usage examples for org.hibernate SQLQuery setParameter

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

Introduction

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

Prototype

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

Source Link

Usage

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

public List getLstFpiId(String lstObjectId) {
    //        FilesDAOHE fdhe = new FilesDAOHE();
    List lstParam = new ArrayList();
    String[] lstObjectIdSplit = lstObjectId.split(",");
    int countObj = lstObjectIdSplit.length;
    String sql = "from fee f inner join fee_payment_info fpi on f.fee_id = fpi.fee_id " + "where f.is_Active=1 "
            + "and fpi.is_Active=1 and f.fee_type = 2 and (";

    if (countObj > 0) {
        for (int i = 0; i < countObj; i++) {
            if (i == countObj - 1) {
                sql += " fpi.file_id = ? )";
            } else {
                sql += " fpi.file_id = ? or ";
            }/*  w  ww . j  a  va2 s .  c  o  m*/
            lstParam.add(lstObjectIdSplit[i]);
        }
    }

    SQLQuery query = (SQLQuery) getSession().createSQLQuery("select fpi.payment_info_id " + sql);
    for (int i = 0; i < lstParam.size(); i++) {
        query.setParameter(i, lstParam.get(i));
    }
    List lstResult = query.list();
    return lstResult;
}

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

public Long getAmountKeyPay(String lstObjectId) {
    //        FilesDAOHE fdhe = new FilesDAOHE();
    List lstParam = new ArrayList();
    String[] lstObjectIdSplit = lstObjectId.split(",");
    int countObj = lstObjectIdSplit.length;
    Long amount = 0l;//from ww  w .  j  av a 2s. co  m
    String sql = "from fee f inner join fee_payment_info fpi on f.fee_id = fpi.fee_id " + "where f.is_Active=1 "
            + "and fpi.is_Active=1 and f.fee_type = 2 and (";

    if (countObj > 0) {
        for (int i = 0; i < countObj; i++) {
            if (i == countObj - 1) {
                sql += " fpi.file_id = ? )";
            } else {
                sql += " fpi.file_id = ? or ";
            }
            lstParam.add(lstObjectIdSplit[i]);
        }
    }

    SQLQuery query = (SQLQuery) getSession().createSQLQuery("select fpi.cost " + sql);
    for (int i = 0; i < lstParam.size(); i++) {
        query.setParameter(i, lstParam.get(i));
    }
    List lstResult = query.list();
    for (int i = 0; i < lstResult.size(); i++) {
        amount += Long.parseLong(lstResult.get(i).toString());
    }
    return amount;
}

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

/**
 *
 * @param searchFeeFormNew//from  w ww  .j  av  a2s .co  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//from w w  w  .  ja v  a 2s.  com
 * @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  ww .j av a2 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  w w  w . j  ava 2 s  . com
 * @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  w w w .  ja  v  a 2s . c  o  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.FilesNoClobDAOHE.java

/**
 * ho so cho tra//from w w  w. j  a v a  2  s .c  o m
 *
 * @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;
    }
}

From source file:com.viettel.logistic.wms.dao.GoodsDAO.java

License:Open Source License

@Transactional
public ResultDTO synchListGoods(List<GoodsDTO> lstGoods, Session session) {
    ResultDTO result = new ResultDTO();
    String message = ParamUtils.SUCCESS;
    result.setMessage(ParamUtils.SUCCESS);
    StringBuffer sql = new StringBuffer();
    sql.append(" UPDATE Goods a ");
    sql.append(/*ww  w . j  a  va  2  s  .c  o  m*/
            " SET a.name =?, a.status=?, a.goods_group=?, a.unit_type=?, a.is_serial=? ,a.is_serial_strip=?, a.origin_price=to_number(?), a.origin_size=?, a.weight=?, a.volume_origin=?, a.volume_real=?, a.description=?, a.goods_type=? ");
    sql.append(" WHERE  a.code IN (:idx0) AND a.cust_id IN (:idx1)");
    SQLQuery query = getSession().createSQLQuery(sql.toString());
    for (GoodsDTO lstGood : lstGoods) {
        List params = new ArrayList();
        ArrayList<String> arrCode = new ArrayList<>();
        ArrayList<Integer> arrName = new ArrayList<>();
        arrCode.add(lstGood.getCode());
        arrName.add(Integer.parseInt(lstGood.getCustId()));
        params.add(lstGood.getName());
        params.add(lstGood.getStatus());
        params.add(lstGood.getGoodsGroup());
        params.add(lstGood.getUnitType());
        params.add(lstGood.getIsSerial());
        params.add(lstGood.getIsSerialStrip());
        params.add(DataUtil.getStringNullOrZero(lstGood.getOriginPrice()));
        params.add(DataUtil.getStringNullOrZero(lstGood.getOriginSize()));
        params.add(DataUtil.getStringNullOrZero(lstGood.getWeight()));
        params.add(DataUtil.getStringNullOrZero(lstGood.getVolumeOrigin()));
        params.add(DataUtil.getStringNullOrZero(lstGood.getVolumeReal()));
        params.add(DataUtil.getStringNullOrZero(lstGood.getDescription()));
        params.add(lstGood.getGoodsType());
        for (int idx = 0; idx < params.size(); idx++) {
            query.setParameter(idx, params.get(idx));
        }
        query.setParameterList("idx0", arrCode);
        query.setParameterList("idx1", arrName);

        try {
            int i = query.executeUpdate();
            if (i == 0) {
                List paramsInsert = new ArrayList();
                StringBuffer sqlInsert = new StringBuffer();
                SQLQuery queryInsert;
                sqlInsert.append(
                        " INSERT INTO Goods (goods_id,cust_id,code,name,status,goods_group,unit_type,is_serial,is_serial_strip,origin_price,origin_size,weight,volume_origin,volume_real,description,goods_type) ");
                sqlInsert.append(
                        " VALUES (GOODS_SEQ.nextval,TO_NUMBER(?),?,?,?,?,?,?,?,TO_NUMBER(?),?,?,?,?,?,?) ");
                paramsInsert.clear();
                paramsInsert.add(lstGood.getCustId());
                paramsInsert.add(lstGood.getCode());
                paramsInsert.add(lstGood.getName());
                paramsInsert.add(lstGood.getStatus());
                paramsInsert.add(lstGood.getGoodsGroup());
                paramsInsert.add(lstGood.getUnitType());
                paramsInsert.add(lstGood.getIsSerial());
                paramsInsert.add(lstGood.getIsSerialStrip());
                paramsInsert.add(DataUtil.getStringNullOrZero(lstGood.getOriginPrice()));
                paramsInsert.add(DataUtil.getStringNullOrZero(lstGood.getOriginSize()));
                paramsInsert.add(DataUtil.getStringNullOrZero(lstGood.getWeight()));
                paramsInsert.add(DataUtil.getStringNullOrZero(lstGood.getVolumeOrigin()));
                paramsInsert.add(DataUtil.getStringNullOrZero(lstGood.getVolumeReal()));
                paramsInsert.add(DataUtil.getStringNullOrZero(lstGood.getDescription()));
                paramsInsert.add(lstGood.getGoodsType());
                queryInsert = getSession().createSQLQuery(sqlInsert.toString());
                for (int idx = 0; idx < paramsInsert.size(); idx++) {
                    queryInsert.setParameter(idx, paramsInsert.get(idx));
                }
                try {
                    int is = queryInsert.executeUpdate();
                } catch (Exception e) {
                    e.printStackTrace();
                    message = ParamUtils.FAIL;
                    result.setMessage(message);
                    return result;
                }

            }
        } catch (Exception e) {
            e.printStackTrace();
            message = ParamUtils.FAIL;
            result.setMessage(message);
            return result;
        }
    }
    result.setMessage(message);
    return result;
}

From source file:com.viettel.logistic.wms.dao.GoodsDAO.java

License:Open Source License

public List<GoodsDTO> getListGoods(GoodsDTO goodsDTO) {
    List<GoodsDTO> lstGoodsDTO = new ArrayList();
    List lstParams = new ArrayList();
    //        /*  w ww  . j av  a  2s.  co  m*/

    StringBuffer sql = new StringBuffer();
    //        
    sql.append("SELECT   g.cust_id custId,");
    sql.append("         g.goods_id goodsId,");
    sql.append("  FROM   goods g, stock_goods_total sg");
    sql.append(" WHERE   s.stock_id = sg.owner_id");
    sql.append("     AND g.goods_id = sg.goods_id");
    sql.append("     AND sg.amount_issue > 0");

    //Tim kiem theo khach hang
    if (!StringUtils.isStringNullOrEmpty(goodsDTO.getCustId())) {
        sql.append("         AND sg.cust_id = ?");
        sql.append("         AND g.cust_id = ?");
        lstParams.add(goodsDTO.getCustId());
        lstParams.add(goodsDTO.getCustId());
    }

    //
    sql.append(" GROUP BY   sg.cust_id");
    sql.append(" ORDER BY   sg.cust_id");
    //Map DTO
    SQLQuery query = getSession().createSQLQuery(sql.toString());
    query.setResultTransformer(Transformers.aliasToBean(StockGoodsInforDTO.class));
    query.addScalar("custId", new StringType());
    query.addScalar("ownerId", new StringType());
    query.addScalar("ownerType", new StringType());
    query.addScalar("ownerCode", new StringType());
    query.addScalar("ownerName", new StringType());
    query.addScalar("goodsType", new StringType());
    query.addScalar("goodsId", new StringType());
    query.addScalar("goodsCode", new StringType());
    query.addScalar("goodsName", new StringType());
    query.addScalar("goodsState", new StringType());
    query.addScalar("amount", new StringType());
    query.addScalar("amountIssue", new StringType());
    query.addScalar("unitType", new StringType());
    //Set cac gia tri tham so
    for (int i = 0; i < lstParams.size(); i++) {
        query.setParameter(i, lstParams.get(i));
    }

    //
    return query.list();
}