Example usage for org.hibernate SQLQuery 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.gp.cong.lcl.common.constant.ExportUnitQueryUtils.java

public List<ExportVoyageSearchModel> getVoyageSearch(LclUnitsScheduleForm lclUnitsScheduleForm)
        throws Exception {
    StringBuilder queryBuilder = new StringBuilder();
    queryBuilder.append(this.selectvoyageSearchQuery());
    queryBuilder.append(this.fromVoyageSearchQuery(lclUnitsScheduleForm));
    SQLQuery query = getCurrentSession().createSQLQuery(queryBuilder.toString());
    query.setParameter("originId", lclUnitsScheduleForm.getPortOfOriginId());
    if (CommonUtils.isNotEmpty(lclUnitsScheduleForm.getFinalDestinationId())) {
        query.setParameter("destinationId", lclUnitsScheduleForm.getFinalDestinationId());
    }//  w w w  .j  a  va 2s .  c o m
    query.setResultTransformer(Transformers.aliasToBean(ExportVoyageSearchModel.class));
    query.addScalar("ssHeaderId", StringType.INSTANCE);
    query.addScalar("voyageStatus", StringType.INSTANCE);
    query.addScalar("scheduleNo", StringType.INSTANCE);
    query.addScalar("serviceType", StringType.INSTANCE);
    query.addScalar("serviceType", StringType.INSTANCE);
    query.addScalar("fdUnLocCode", StringType.INSTANCE);
    query.addScalar("fdName", StringType.INSTANCE);
    query.addScalar("departPierUnloc", StringType.INSTANCE);
    query.addScalar("departPier", StringType.INSTANCE);
    query.addScalar("arrivalPierUnloc", StringType.INSTANCE);
    query.addScalar("arrivalPier", StringType.INSTANCE);
    query.addScalar("loadingDeadLineDate", StringType.INSTANCE);
    query.addScalar("etaSailDate", StringType.INSTANCE);
    query.addScalar("etaPodDate", StringType.INSTANCE);
    query.addScalar("totaltransPod", StringType.INSTANCE);
    query.addScalar("totaltransFd", StringType.INSTANCE);
    query.addScalar("vesselName", StringType.INSTANCE);
    query.addScalar("ssVoyage", StringType.INSTANCE);
    query.addScalar("carrierName", StringType.INSTANCE);
    query.addScalar("carrierAcctNo", StringType.INSTANCE);
    query.addScalar("sealNo", StringType.INSTANCE);
    query.addScalar("unitNo", StringType.INSTANCE);
    query.addScalar("unitSize", StringType.INSTANCE);
    query.addScalar("numberDrs", StringType.INSTANCE);
    query.addScalar("loadedBy", StringType.INSTANCE);
    query.addScalar("doorLocation", StringType.INSTANCE);
    query.addScalar("dispoCode", StringType.INSTANCE);
    query.addScalar("dispoDesc", StringType.INSTANCE);
    query.addScalar("isInbond", StringType.INSTANCE);
    query.addScalar("dispoDesc", StringType.INSTANCE);
    query.addScalar("isHazmat", StringType.INSTANCE);
    query.addScalar("totalWeightMetric", BigDecimalType.INSTANCE);
    query.addScalar("totalVolumeMetric", BigDecimalType.INSTANCE);
    query.addScalar("unitSizeShortDesc", StringType.INSTANCE);
    return query.list();
}

---

From source file:com.gp.cong.lcl.common.constant.ExportUnitQueryUtils.java

public void updateUnitSsAssociatedwithMasterBl(Long headerId, String currrentspBookingNo,
        String previousspBookingNo) throws Exception {
    SQLQuery query = getCurrentSession().createSQLQuery(
            "UPDATE  lcl_unit_ss lus SET lus.sp_booking_no =:currrentspBookingNo WHERE lus.ss_header_id =:headerId  AND  "
                    + "lus.`sp_booking_no` =:previousspBookingNo");
    query.setLong("headerId", headerId);
    query.setParameter("currrentspBookingNo", currrentspBookingNo.toUpperCase());
    query.setParameter("previousspBookingNo", previousspBookingNo);
    query.executeUpdate();//from   w w w .j  a  v  a  2 s .c om
}

---

From source file:com.gp.cong.lcl.common.constant.ExportUnitQueryUtils.java

public List<ExportVoyageSearchModel> getViewAllList(Integer pooId, Integer fdId, String serviceType,
        String transMode) throws Exception {
    StringBuilder queryBuilder = new StringBuilder();
    queryBuilder.append(/*w ww  .ja  v  a2 s  .c om*/
            " SELECT unit.id as ssHeaderId,unit.scheduleNo as scheduleNo,unit.dataSource as dataSource, ");
    queryBuilder.append(" unit.unitNo as unitNo,unit.origin as pooName,unit.destn as fdName, ");
    queryBuilder.append(" unit.dispoDesc as dispoDesc,unit.dispoCode as dispoCode,unit.inBond as isInbond , ");
    queryBuilder.append(
            " unit.hazNo as isHazmat,unit.polLrd as polLrdDate,unit.etdSail as etaSailDate,unit.unitSize,  ");
    queryBuilder.append(" unit.unitTrackingNotes ");
    queryBuilder.append(" FROM (SELECT  ");
    queryBuilder.append(" ss.id AS id,ss.schedule_no AS scheduleNo, ");
    queryBuilder.append(" ss.datasource AS dataSource,u.unit_no AS unitNo, ");
    queryBuilder.append(" UnLocationGetNameStateCntryByID (ss.origin_id) AS origin, ");
    queryBuilder.append(" UnLocationGetNameStateCntryByID (ss.destination_id) AS destn, ");
    queryBuilder.append(" dispo.description AS dispoDesc,dispo.elite_code AS dispoCode, ");
    queryBuilder.append(" inb.inbond_no AS inBond,bhz.un_hazmat_no AS hazNo, ");
    queryBuilder.append(" DATE_FORMAT(lssd.general_lrdt, '%d-%b-%Y %h:%i %p') AS polLrd, ");
    queryBuilder.append(" DATE_FORMAT(lssd.std, '%d-%b-%Y') AS etdSail,  ");
    queryBuilder.append(
            " (SELECT description FROM unit_type WHERE id=u.unit_type_id) AS unitSize,u.remarks AS unitTrackingNotes ");
    queryBuilder.append(" FROM lcl_ss_header ss  ");
    queryBuilder.append(" JOIN lcl_ss_detail lssd ON (ss.id = lssd.ss_header_id) ");
    queryBuilder.append(" LEFT JOIN lcl_unit_ss us ON (ss.id = us.ss_header_id) ");
    queryBuilder.append(" LEFT JOIN lcl_unit u ON (us.unit_id = u.id) ");
    queryBuilder.append(" LEFT JOIN lcl_unit_ss_dispo ssd ON (u.id = ssd.unit_id) ");
    queryBuilder.append(" LEFT JOIN disposition dispo ON (ssd.disposition_id = dispo.id) ");
    queryBuilder.append(" LEFT JOIN lcl_booking_piece_unit bpu ON (us.id = bpu.lcl_unit_ss_id) ");
    queryBuilder.append(" LEFT JOIN lcl_booking_piece bp ON (bpu.booking_piece_id = bp.id) ");
    queryBuilder.append(" LEFT JOIN lcl_inbond inb ON (bp.file_number_id = inb.file_number_id)  ");
    queryBuilder.append(" LEFT JOIN lcl_booking_hazmat bhz ON (bp.file_number_id = bhz.file_number_id) ");
    // queryBuilder.append("  us.status <> 'C' AND   ");
    queryBuilder.append(" WHERE ss.datasource = 'L' and ss.status <> 'V' AND ss.service_type = :serviceType ");
    queryBuilder.append(" AND ss.trans_mode = :transMode ");
    if (CommonUtils.isNotEmpty(pooId)) {
        queryBuilder.append(" AND ss.origin_id=:pooId  ");
    }
    if (CommonUtils.isNotEmpty(fdId)) {
        queryBuilder.append(" AND ss.destination_id=:fdId ");
    }
    // queryBuilder.append(" GROUP BY unitNo  ");
    queryBuilder.append(" ORDER BY lssd.std DESC) unit  ");
    SQLQuery queryObj = getCurrentSession().createSQLQuery(queryBuilder.toString());
    if (CommonUtils.isNotEmpty(pooId)) {
        queryObj.setParameter("pooId", pooId);
    }
    if (CommonUtils.isNotEmpty(fdId)) {
        queryObj.setParameter("fdId", fdId);
    }
    queryObj.setParameter("serviceType", serviceType);
    queryObj.setParameter("transMode", transMode);
    queryObj.setResultTransformer(Transformers.aliasToBean(ExportVoyageSearchModel.class));
    queryObj.addScalar("ssHeaderId", StringType.INSTANCE);
    queryObj.addScalar("scheduleNo", StringType.INSTANCE);
    queryObj.addScalar("dataSource", StringType.INSTANCE);
    queryObj.addScalar("unitNo", StringType.INSTANCE);
    queryObj.addScalar("pooName", StringType.INSTANCE);
    queryObj.addScalar("fdName", StringType.INSTANCE);
    queryObj.addScalar("dispoDesc", StringType.INSTANCE);
    queryObj.addScalar("dispoCode", StringType.INSTANCE);
    queryObj.addScalar("isInbond", StringType.INSTANCE);
    queryObj.addScalar("isHazmat", StringType.INSTANCE);
    queryObj.addScalar("polLrdDate", StringType.INSTANCE);
    queryObj.addScalar("etaSailDate", StringType.INSTANCE);
    queryObj.addScalar("unitSize", StringType.INSTANCE);
    queryObj.addScalar("unitTrackingNotes", StringType.INSTANCE);
    return queryObj.list();
}

---

From source file:com.gp.cong.lcl.common.constant.ExportUnitQueryUtils.java

public List<ExportVoyageSearchModel> searchByVoyageList(LclUnitsScheduleForm lclUnitsScheduleForm)
        throws Exception {
    List<ExportVoyageSearchModel> voyageList = new ArrayList<ExportVoyageSearchModel>();
    StringBuilder queryStr = new StringBuilder();
    queryStr.append(/*  w w w.j av a2 s  .c om*/
            " SELECT lclssh.id AS ssHeaderId,lclssh.service_type as serviceType,lclssd.id AS ssDeatailId, ");
    queryStr.append("  lclssh.schedule_no AS scheduleNo,lclssd.sp_acct_no AS carrierAcctNo, ");
    queryStr.append(
            "  (SELECT acct_name FROM trading_partner WHERE acct_no = lclssd.sp_acct_no LIMIT 1) AS carrierName, ");
    queryStr.append(
            "  (SELECT COUNT(*) FROM lcl_unit lu LEFT JOIN lcl_unit_ss luss ON luss.unit_id = lu.id WHERE lclssh.id = luss.ss_header_id) AS units, ");
    queryStr.append("  (SELECT COUNT(*) FROM lcl_unit lu LEFT JOIN lcl_unit_ss luss ON luss.unit_id = lu.id ");
    queryStr.append("   WHERE lclssh.id = luss.ss_header_id and luss.status ='M') as manifestUnitCount, ");
    queryStr.append("  (SELECT COUNT(*) FROM lcl_unit lu LEFT JOIN lcl_unit_ss luss ON luss.unit_id = lu.id ");
    queryStr.append("   WHERE lclssh.id = luss.ss_header_id and luss.cob = 1 ) as cobUnitCount, ");
    queryStr.append(getAppendQueryForUnitNo());
    queryStr.append("  lclssd.sp_reference_name AS vesselName,lclssd.sp_reference_no AS ssVoyage, ");
    queryStr.append("  UnLocationGetCodeByID (lclssd.departure_id) AS departPierUnloc,");
    queryStr.append("  UnLocationGetNameStateCntryByID (lclssd.departure_id) AS departPier,");
    queryStr.append("  UnLocationGetCodeByID (lclssd.arrival_id) AS arrivalPierUnloc,");
    queryStr.append("  UnLocationGetNameStateCntryByID (lclssd.arrival_id) AS arrivalPier,");
    queryStr.append(
            "  lclssd.relay_lrd_override AS lrdOverride,lclssd.relay_lrd_override AS lrdOverrideDays, ");
    queryStr.append(
            "  DATE_FORMAT(lclssd.general_lrdt, '%d-%b-%Y') AS generalLrdt,lclssd.general_lrdt as polLrdDates,");
    queryStr.append("  DATE_FORMAT(lclssd.std, '%d-%b-%Y') AS etaSailDate,");
    queryStr.append("  DATE_FORMAT(lclssd.sta, '%d-%b-%Y') AS etaPodDate,lclssd.sta as etaPodDates,");
    queryStr.append(
            "  IF(lclssd.relay_tt_override IS NOT NULL AND lclssd.relay_tt_override > 0 ,lclssd.relay_tt_override,lr.transit_time) AS LRT,lclssh.datasource,");
    queryStr.append("  UserDetailsGetLoginNameByID (lclssh.entered_by_user_id) AS createdBy,");
    queryStr.append("  UserDetailsGetLoginNameByID (lclssh.owner_user_id) AS voyOwner,");
    queryStr.append("  IF(lclssh.closed_by_user_id IS NOT NULL,'CL','') AS closedStatus,   ");
    queryStr.append("  IF(lclssh.audited_by_user_id IS NOT NULL,'AU','') AS auditedStatus,   ");
    queryStr.append(
            "  IF(lclssh.closed_by_user_id IS NOT NULL,UserDetailsGetLoginNameByID (lclssh.closed_by_user_id),'') AS closedBy,   ");
    queryStr.append(
            "  IF(lclssh.audited_by_user_id IS NOT NULL,UserDetailsGetLoginNameByID (lclssh.audited_by_user_id),'') AS auditedBy,   ");
    queryStr.append(
            "  IF(lclssh.closed_datetime IS NOT NULL,DATE_FORMAT(lclssh.closed_datetime, '%d-%b-%Y %T'),'') AS closedOn,   ");
    queryStr.append(
            "  IF(lclssh.audited_datetime IS NOT NULL,DATE_FORMAT(lclssh.audited_datetime, '%d-%b-%Y %T'),'') AS auditedOn,   ");
    queryStr.append("  IF(lclssh.closed_remarks IS NOT NULL,lclssh.closed_remarks,'') AS closedRemarks,   ");
    queryStr.append(
            "  IF(lclssh.audited_remarks IS NOT NULL,lclssh.audited_remarks,'') AS auditedRemarks,lclssd.std as etaSailDates,   ");
    queryStr.append("  lrp.transit_time AS LRPT,lrp.co_dow,lrf.transit_time AS LFT, ");
    queryStr.append("  (SELECT MAX(CASE WHEN lclssd.sta = luss.cob_datetime THEN 1 ");
    queryStr.append("  WHEN (SELECT COUNT(*) FROM lcl_unit_ss lus  WHERE lus.`ss_header_id` = lclssh.id)  =  ");
    queryStr.append(
            "  (SELECT COUNT(*) FROM lcl_unit_ss lus WHERE lus.`ss_header_id` = lclssh.id AND lus.cob_datetime IS NULL) THEN 2");
    queryStr.append("  WHEN (SELECT COUNT(*) FROM lcl_unit_ss lus ");
    queryStr.append("  WHERE lus.`ss_header_id` = lclssh.id)>1 AND luss.cob_datetime IS NULL THEN 3 ");
    queryStr.append("  WHEN lclssd.sta <> luss.cob_datetime THEN 4 ELSE 0 END ");
    queryStr.append("  ) AS vETA FROM lcl_unit_ss luss WHERE luss.ss_header_id = lclssh.id) AS verifiedEta ");
    queryStr.append("  FROM  lcl_ss_header lclssh ");
    queryStr.append("  JOIN lcl_ss_detail lclssd ON (lclssh.id = lclssd.ss_header_id) ");
    if (CommonUtils.isNotEmpty(lclUnitsScheduleForm.getUnitNo())) {
        queryStr.append("  LEFT JOIN lcl_unit_ss luss ON (luss.ss_header_id = lclssh.id) ");
        queryStr.append("  LEFT JOIN lcl_unit lu ON (lu.id = luss.unit_id) ");
    }
    if (CommonUtils.isNotEmpty(lclUnitsScheduleForm.getCfclAcctNo())) {
        queryStr.append(" join lcl_ss_exports lsexp  on  ");
        queryStr.append("(lsexp.ss_header_id = lclssh.id and  lsexp.export_agent_acct_no =:cfclAcct)");
    }
    queryStr.append(
            " LEFT JOIN lcl_relay lr ON (lclssh.origin_id = lr.pol_id AND lclssh.destination_id = lr.pod_id) ");
    queryStr.append(
            " LEFT JOIN lcl_relay_poo lrp ON (lrp.poo_id = lclssh.origin_id AND lrp.relay_id = lr.id) ");
    queryStr.append(
            " LEFT JOIN lcl_relay_fd lrf ON (lrf.fd_id = lclssh.destination_id AND lrf.relay_id = lr.id) ");
    queryStr.append(" WHERE ");
    queryStr.append(" lclssh.trans_mode = 'V'  ");
    queryStr.append(" AND lclssh.status <> 'V'  ");
    if (CommonUtils.isNotEmpty(lclUnitsScheduleForm.getServiceType())) {
        queryStr.append(" AND lclssh.service_type =:serviceType ");
    }
    if (CommonUtils.isNotEmpty(lclUnitsScheduleForm.getPortOfOriginId())) {
        queryStr.append(" AND lclssh.origin_id=:originId ");
    }
    if (CommonUtils.isNotEmpty(lclUnitsScheduleForm.getFinalDestinationId())) {
        queryStr.append(" AND lclssh.destination_id=:destinationId ");
    }
    if (CommonUtils.isNotEmpty(lclUnitsScheduleForm.getVoyageNo())) {
        queryStr.append(" AND lclssh.schedule_no=:scheduleNo");
    }
    if (CommonUtils.isNotEmpty(lclUnitsScheduleForm.getUnitNo())) {
        queryStr.append(" AND lu.unit_no=:unitNo ");
    }
    queryStr.append(" GROUP BY lclssh.id order by  ");

    if (CommonUtils.isNotEmpty(lclUnitsScheduleForm.getColumnName())) {
        if ("up".equals(lclUnitsScheduleForm.getSortBy())) {
            queryStr.append(lclUnitsScheduleForm.getColumnName()).append(" asc");
        } else {
            queryStr.append(lclUnitsScheduleForm.getColumnName()).append(" desc");
        }
    } else {
        queryStr.append(" lclssd.std DESC  ");
    }
    queryStr.append("  LIMIT ");
    if (CommonUtils.isNotEmpty(lclUnitsScheduleForm.getLimit())) {
        queryStr.append(lclUnitsScheduleForm.getLimit());
    } else {
        queryStr.append("50");
    }
    SQLQuery queryObj = getSession().createSQLQuery(queryStr.toString());
    if (CommonUtils.isNotEmpty(lclUnitsScheduleForm.getServiceType())) {
        queryObj.setParameter("serviceType", lclUnitsScheduleForm.getServiceType());
    }
    if (CommonUtils.isNotEmpty(lclUnitsScheduleForm.getPortOfOriginId())) {
        queryObj.setParameter("originId", lclUnitsScheduleForm.getPortOfOriginId());
    }
    if (CommonUtils.isNotEmpty(lclUnitsScheduleForm.getFinalDestinationId())) {
        queryObj.setParameter("destinationId", lclUnitsScheduleForm.getFinalDestinationId());
    }
    if (CommonUtils.isNotEmpty(lclUnitsScheduleForm.getVoyageNo())) {
        queryObj.setParameter("scheduleNo", lclUnitsScheduleForm.getVoyageNo());
    }
    if (CommonUtils.isNotEmpty(lclUnitsScheduleForm.getUnitNo())) {
        queryObj.setParameter("unitNo", lclUnitsScheduleForm.getUnitNo());
    }
    if (CommonUtils.isNotEmpty(lclUnitsScheduleForm.getCfclAcctNo())) {
        queryObj.setParameter("cfclAcct", lclUnitsScheduleForm.getCfclAcctNo());
    }
    List queryList = queryObj.list();
    for (Object obj : queryList) {
        Object[] row = (Object[]) obj;
        ExportVoyageSearchModel model = new ExportVoyageSearchModel(row, lclUnitsScheduleForm);
        voyageList.add(model);
    }
    return voyageList;
}

---

From source file:com.gp.cong.lcl.common.constant.ExportUnitQueryUtils.java

public List<ExportVoyageSearchModel> getInandVoyageList(LclUnitsScheduleForm lclUnitsScheduleForm)
        throws Exception {
    StringBuilder queryStr = new StringBuilder();
    queryStr.append(//w ww . j  a  v  a 2 s . c om
            " SELECT lclssh.id AS ssHeaderId,lclssd.id AS ssDetailId,lclssh.service_type as serviceType,");
    queryStr.append(" lclssh.schedule_no AS scheduleNo,lclssd.sp_acct_no AS carrierAcctNo,");
    queryStr.append(
            " (SELECT acct_name FROM trading_partner WHERE acct_no = lclssd.sp_acct_no LIMIT 1) AS carrierName,");
    queryStr.append(" (SELECT COUNT(*) FROM lcl_unit lu LEFT JOIN lcl_unit_ss luss ON luss.unit_id = lu.id ");
    queryStr.append(" WHERE lclssh.id = luss.ss_header_id) AS unitcount,");
    queryStr.append(getAppendQueryForUnitNo());
    queryStr.append(" lclssd.sp_reference_name AS vesselName,lclssd.sp_reference_no AS ssVoyage,");
    queryStr.append(" UnLocationGetCodeByID (lclssd.departure_id) AS departPierUnloc,");
    queryStr.append(" UnLocationGetNameStateCntryByID (lclssd.departure_id) AS departPier,");
    queryStr.append(" UnLocationGetCodeByID (lclssd.arrival_id) AS arrivalPierUnloc,");
    queryStr.append(" UnLocationGetNameStateCntryByID (lclssd.arrival_id) AS arrivalPier,");
    queryStr.append(
            " DATE_FORMAT(lclssd.general_lrdt, '%d-%b-%Y') AS polLrdDate,lclssd.general_lrdt as polLrdDates,");
    queryStr.append(" lclssd.sta as etaPodDates,lclssd.std as etaSailDates,");
    queryStr.append(
            " DATE_FORMAT(lclssd.std, '%d-%b-%Y') AS etaSailDate,DATE_FORMAT(lclssd.sta, '%d-%b-%Y') AS etaPodDate,");
    queryStr.append(" lclssh.datasource AS dataSource,");
    queryStr.append(" UserDetailsGetLoginNameByID (lclssh.entered_by_user_id) AS createdBy,");
    queryStr.append(" UserDetailsGetLoginNameByID (lclssh.owner_user_id) AS voyOwner,");
    queryStr.append("  (SELECT COUNT(*) FROM lcl_unit lu LEFT JOIN lcl_unit_ss luss ON luss.unit_id = lu.id ");
    queryStr.append("   WHERE lclssh.id = luss.ss_header_id and luss.status ='M') as manifestUnitCount, ");
    queryStr.append("  (SELECT COUNT(*) FROM lcl_unit lu LEFT JOIN lcl_unit_ss luss ON luss.unit_id = lu.id ");
    queryStr.append("   WHERE lclssh.id = luss.ss_header_id and luss.cob = 1 ) as cobUnitCount, ");
    queryStr.append("  (SELECT MAX(CASE WHEN lclssd.sta = luss.cob_datetime THEN 1 ");
    queryStr.append("  WHEN (SELECT COUNT(*) FROM lcl_unit_ss lus  WHERE lus.`ss_header_id` = lclssh.id)  =  ");
    queryStr.append(
            "  (SELECT COUNT(*) FROM lcl_unit_ss lus WHERE lus.`ss_header_id` = lclssh.id AND lus.cob_datetime IS NULL) THEN 2");
    queryStr.append("  WHEN (SELECT COUNT(*) FROM lcl_unit_ss lus ");
    queryStr.append("  WHERE lus.`ss_header_id` = lclssh.id)>1 AND luss.cob_datetime IS NULL THEN 3 ");
    queryStr.append("  WHEN lclssd.sta <> luss.cob_datetime THEN 4 ELSE 0 END ");
    queryStr.append("  ) AS vETA FROM lcl_unit_ss luss WHERE luss.ss_header_id = lclssh.id) AS verifiedEta ");
    queryStr.append(" FROM lcl_ss_header lclssh ");
    queryStr.append(" JOIN lcl_ss_detail lclssd ON (lclssh.id = lclssd.ss_header_id)");
    queryStr.append(" WHERE lclssh.trans_mode = 'T' AND lclssh.service_type = 'N' ");
    queryStr.append(" AND lclssh.status <> 'V'  ");
    if (CommonUtils.isNotEmpty(lclUnitsScheduleForm.getPortOfOriginId())) {
        queryStr.append(" AND lclssh.origin_id=:originId ");
    }
    if (CommonUtils.isNotEmpty(lclUnitsScheduleForm.getFinalDestinationId())) {
        queryStr.append(" AND lclssh.destination_id=:destinationId ");
    }
    if (CommonUtils.isNotEmpty(lclUnitsScheduleForm.getVoyageNo())) {
        queryStr.append(" AND lclssh.schedule_no=:scheduleNo");
    }
    queryStr.append(" GROUP BY lclssh.id ORDER BY ");
    if (CommonUtils.isNotEmpty(lclUnitsScheduleForm.getColumnName())) {
        if ("up".equals(lclUnitsScheduleForm.getSortBy())) {
            queryStr.append(lclUnitsScheduleForm.getColumnName()).append(" asc");
        } else {
            queryStr.append(lclUnitsScheduleForm.getColumnName()).append(" desc");
        }
    } else {
        queryStr.append(" lclssd.std DESC  ");
    }
    queryStr.append("  LIMIT ");
    if (CommonUtils.isNotEmpty(lclUnitsScheduleForm.getLimit())) {
        queryStr.append(lclUnitsScheduleForm.getLimit());
    } else {
        queryStr.append("50");
    }
    SQLQuery query = getSession().createSQLQuery(queryStr.toString());
    if (CommonUtils.isNotEmpty(lclUnitsScheduleForm.getPortOfOriginId())) {
        query.setParameter("originId", lclUnitsScheduleForm.getPortOfOriginId());
    }
    if (CommonUtils.isNotEmpty(lclUnitsScheduleForm.getFinalDestinationId())) {
        query.setParameter("destinationId", lclUnitsScheduleForm.getFinalDestinationId());
    }
    if (CommonUtils.isNotEmpty(lclUnitsScheduleForm.getVoyageNo())) {
        query.setParameter("scheduleNo", lclUnitsScheduleForm.getVoyageNo());
    }
    query.setResultTransformer(Transformers.aliasToBean(ExportVoyageSearchModel.class));
    query.addScalar("ssHeaderId", StringType.INSTANCE);
    query.addScalar("ssDetailId", StringType.INSTANCE);
    query.addScalar("serviceType", StringType.INSTANCE);
    query.addScalar("scheduleNo", StringType.INSTANCE);
    query.addScalar("carrierName", StringType.INSTANCE);
    query.addScalar("carrierAcctNo", StringType.INSTANCE);
    query.addScalar("unitcount", StringType.INSTANCE);
    query.addScalar("unitNo", StringType.INSTANCE);
    query.addScalar("vesselName", StringType.INSTANCE);
    query.addScalar("ssVoyage", StringType.INSTANCE);
    query.addScalar("departPierUnloc", StringType.INSTANCE);
    query.addScalar("departPier", StringType.INSTANCE);
    query.addScalar("arrivalPierUnloc", StringType.INSTANCE);
    query.addScalar("arrivalPier", StringType.INSTANCE);
    query.addScalar("polLrdDate", StringType.INSTANCE);
    query.addScalar("etaSailDate", StringType.INSTANCE);
    query.addScalar("etaPodDate", StringType.INSTANCE);
    query.addScalar("createdBy", StringType.INSTANCE);
    query.addScalar("voyOwner", StringType.INSTANCE);
    query.addScalar("dataSource", StringType.INSTANCE);
    query.addScalar("manifestUnitCount", StringType.INSTANCE);
    query.addScalar("cobUnitCount", StringType.INSTANCE);
    query.addScalar("verifiedEta", IntegerType.INSTANCE);
    return query.list();
}

---

From source file:com.gp.cong.lcl.common.constant.ExportUnitQueryUtils.java

public List<ExportVoyageSearchModel> getMultiUnitSearchList(LclUnitsScheduleForm lclUnitsScheduleForm)
        throws Exception {
    StringBuilder queryStr = new StringBuilder();
    queryStr.append("SELECT lsh.id AS ssHeaderId,lsh.schedule_no as scheduleNo,");
    queryStr.append("  UnLocationGetCodeByID (lsh.destination_id) AS departPierUnloc,");
    queryStr.append("UnLocationGetCodeByID(lsh.origin_id) AS arrivalPierUnloc,");
    queryStr.append("UnLocationGetNameStateCntryByID (lsh.origin_id) AS arrivalPier,");
    queryStr.append("UnLocationGetNameStateCntryByID (lsh.destination_id) AS departPier,");
    queryStr.append("lsh.origin_id as pooId,lsh.destination_id as fdId ");
    queryStr.append(" FROM lcl_unit lu  ");
    queryStr.append(" JOIN lcl_unit_ss luss ON luss.unit_id = lu.id  ");
    queryStr.append("JOIN lcl_ss_header lsh ON lsh.id=luss.ss_header_id");
    queryStr.append(" WHERE luss.unit_id=:unitId ");
    //queryStr.append("AND lsh.service_type =:serviceType");
    SQLQuery query = getSession().createSQLQuery(queryStr.toString());
    query.setParameter("unitId", lclUnitsScheduleForm.getUnitId());
    //query.setParameter("serviceType", lclUnitsScheduleForm.getServiceType());
    query.setResultTransformer(Transformers.aliasToBean(ExportVoyageSearchModel.class));
    query.addScalar("ssHeaderId", StringType.INSTANCE);
    query.addScalar("scheduleNo", StringType.INSTANCE);
    query.addScalar("departPierUnloc", StringType.INSTANCE);
    query.addScalar("departPier", StringType.INSTANCE);
    query.addScalar("arrivalPierUnloc", StringType.INSTANCE);
    query.addScalar("arrivalPier", StringType.INSTANCE);
    query.addScalar("pooId", StringType.INSTANCE);
    query.addScalar("fdId", StringType.INSTANCE);
    return query.list();
}

---

From source file:com.gp.cong.lcl.common.constant.ExportUnitQueryUtils.java

public List<ExportVoyageSearchModel> searchByUnAssignUnit(String wareHouseId) throws Exception {
    StringBuilder queryStr = new StringBuilder();
    queryStr.append("select lu.id AS unitId, lu.`unit_no` as unitNo,  ");
    queryStr.append(" un.description as unitSize,wa.warehsname as warehouseName, ");
    queryStr.append(" lu.hazmat_permitted AS isHazmat, lu.remarks AS unitTrackingNotes, ");
    queryStr.append(//from   w w w  .j av  a 2  s . c om
            "  lu.`volume_imperial`  AS totalVolumeImperial , lu.`volume_metric` AS totalWeightImperial, ");
    queryStr.append(" UserDetailsGetLoginNameByID(lu.entered_by_user_id) as createdBy, ");
    queryStr.append(" DATE_FORMAT(lu.entered_datetime, '%d-%b-%Y') AS createdDate,lu.comments AS comments ");
    queryStr.append(" from lcl_unit lu join lcl_unit_whse luw on luw.unit_id=lu.id ");
    queryStr.append(" JOIN unit_type un  ON un.id=lu.`unit_type_id` ");
    queryStr.append(" join  warehouse wa on luw.warehouse_id=wa.id  where luw.warehouse_id = :warehouseId ");
    queryStr.append(
            " and luw.id = (SELECT id FROM lcl_unit_whse WHERE unit_id = luw.unit_id ORDER BY id DESC LIMIT 1) ");
    queryStr.append(" AND luw.ss_header_id IS NULL  GROUP BY lu.id Desc ");
    SQLQuery query = getCurrentSession().createSQLQuery(queryStr.toString());
    query.setParameter("warehouseId", wareHouseId);
    query.setResultTransformer(Transformers.aliasToBean(ExportVoyageSearchModel.class));
    query.addScalar("unitId", StringType.INSTANCE);
    query.addScalar("unitNo", StringType.INSTANCE);
    query.addScalar("unitSize", StringType.INSTANCE);
    query.addScalar("warehouseName", StringType.INSTANCE);
    query.addScalar("isHazmat", StringType.INSTANCE);
    query.addScalar("unitTrackingNotes", StringType.INSTANCE);
    query.addScalar("totalVolumeImperial", BigDecimalType.INSTANCE);
    query.addScalar("totalWeightImperial", BigDecimalType.INSTANCE);
    query.addScalar("createdBy", StringType.INSTANCE);
    query.addScalar("createdDate", StringType.INSTANCE);
    query.addScalar("comments", StringType.INSTANCE);
    return query.list();
}

---

From source file:com.gp.cong.lcl.common.constant.ExportUnitQueryUtils.java

public boolean isDrAvailableInVoyage(String headerId, String unitSsId) throws Exception {
    String sb = " select if(count(*)>0,true,false) as result  from lcl_unit_ss lus join lcl_booking_piece_unit bu "
            + " on bu.lcl_unit_ss_id = lus.id join lcl_booking_piece b  on b.id= bu.booking_piece_id "
            + " join lcl_file_number f on f.id = b.file_number_id "
            + " join lcl_ss_header lsh on lsh.id = lus.ss_header_id where f.state not in ('B') and lsh.id =:headerId";
    if (!"0".equalsIgnoreCase(unitSsId)) {
        sb += " and lus.id=" + unitSsId;
    }/*from  www .  ja  v a2  s.  c o  m*/
    SQLQuery query = getCurrentSession().createSQLQuery(sb);
    query.setParameter("headerId", headerId);
    query.addScalar("result", BooleanType.INSTANCE);
    return (Boolean) query.uniqueResult();
}

---

From source file:com.gp.cong.lcl.common.constant.ExportUnitQueryUtils.java

public List<Long> getPickedFileId(Long unitSsId) throws Exception {
    List<Long> resultList = new ArrayList<Long>();
    String sb = "select bp.file_number_id from lcl_booking_piece_unit bpu join lcl_booking_piece bp on bp.id=bpu.booking_piece_id where bpu.lcl_unit_ss_id=:unitSsId ";
    SQLQuery query = getCurrentSession().createSQLQuery(sb);
    query.setParameter("unitSsId", unitSsId);
    for (Object obj : query.list()) {
        resultList.add(Long.parseLong(obj.toString()));
    }// ww w. j a va 2  s. c o m
    return resultList;
}

---

From source file:com.gp.cong.lcl.common.constant.ExportUnitQueryUtils.java

public LclModel getPickedVoyageByFileId(Long fileId, String serviceType) throws Exception {
    StringBuilder queryStr = new StringBuilder();
    queryStr.append(" SELECT lsh.schedule_no as scheduleNo,lu.unit_no as unitNo ");
    queryStr.append(" FROM lcl_booking_piece lbp ");
    queryStr.append(" JOIN lcl_booking_piece_unit lbpu ON lbpu.booking_piece_id=lbp.id ");
    queryStr.append(" JOIN lcl_unit_ss lus ON lus.id=lbpu.lcl_unit_ss_id");
    queryStr.append(" JOIN lcl_unit lu ON lu.id=lus.unit_id");
    queryStr.append(" JOIN lcl_ss_header lsh ON lsh.id=lus.ss_header_id");
    queryStr.append(" WHERE lbp.file_number_id=:fileId");
    queryStr.append(" AND lsh.service_type=:serviceType");
    SQLQuery query = getSession().createSQLQuery(queryStr.toString());
    query.setParameter("fileId", fileId);
    query.setParameter("serviceType", serviceType);
    query.setResultTransformer(Transformers.aliasToBean(LclModel.class));
    query.addScalar("scheduleNo", StringType.INSTANCE);
    query.addScalar("unitNo", StringType.INSTANCE);
    List<LclModel> pickedList = query.list();
    if (pickedList != null && !pickedList.isEmpty()) {
        return pickedList.get(0);
    }/*from   w  w  w. j ava2 s.  co m*/
    return null;
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• »