Example usage for org.hibernate SQLQuery list

List of usage examples for org.hibernate SQLQuery list

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

Introduction

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

Prototype

List<R> list();

Source Link

Document

Return the query results as a List.

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  2 s  . 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 List<ExportVoyageSearchModel> getViewAllList(Integer pooId, Integer fdId, String serviceType,
        String transMode) throws Exception {
    StringBuilder queryBuilder = new StringBuilder();
    queryBuilder.append(/*from   w  ww  . ja va2 s.  c o  m*/
            " 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(//from   ww w .j  av  a 2s. c  o  m
            " 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(/*from  www.ja 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   www .ja  v  a2s .c o  m
            "  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 List<ExportVoyageSearchModel> searchByUnCompleteUnit(Integer pooId, Integer fdId, String serviceType)
        throws Exception {
    StringBuilder queryStr = new StringBuilder();
    queryStr.append(this.selectvoyageSearchQuery());
    queryStr.append("   FROM ");
    queryStr.append(" lcl_ss_header lsh ");
    queryStr.append(" JOIN un_location pod ON lsh.destination_id=pod.id ");
    queryStr.append(" JOIN lcl_ss_detail lsd ");
    queryStr.append(" ON (lsh.id = lsd.ss_header_id ");
    queryStr.append(" AND lsh.status <> 'V' ");
    queryStr.append(" AND lsd.`id` = (SELECT ls.id FROM ");
    queryStr.append(" lcl_ss_detail ls ");
    queryStr.append("  WHERE ls.`ss_header_id` = lsh.id ");
    queryStr.append(" ORDER BY id DESC LIMIT 1)) ");
    queryStr.append("  JOIN lcl_unit_ss lus ");
    queryStr.append(" ON (lus.ss_header_id = lsh.id ) ");
    queryStr.append(/*from www . ja v  a 2  s  .c o m*/
            "  JOIN lcl_unit lu ON (lu.id=lus.unit_id) JOIN unit_type ut ON (ut.id = lu.unit_type_id) ");
    queryStr.append(" WHERE lsh.service_type=:serviceType and  lus.status = 'E'  ");
    if (CommonUtils.isNotEmpty(pooId)) {
        queryStr.append(" AND lsh.origin_id =:pooId ");
    }
    if (CommonUtils.isNotEmpty(fdId)) {
        queryStr.append(" AND lsh.destination_id =:fdId ");
    }
    queryStr.append(" ORDER BY pod.un_loc_code ,lsh.schedule_no ASC ");
    SQLQuery query = getCurrentSession().createSQLQuery(queryStr.toString());
    if (CommonUtils.isNotEmpty(pooId)) {
        query.setInteger("pooId", pooId);
    }
    if (CommonUtils.isNotEmpty(fdId)) {
        query.setInteger("fdId", fdId);
    }
    query.setString("serviceType", serviceType);
    query.setResultTransformer(Transformers.aliasToBean(ExportVoyageSearchModel.class));
    query.addScalar("ssHeaderId", 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);
    return query.list();
}

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

public List<ManifestBean> getPickedDrList(Long unitSSId) throws Exception {
    StringBuilder queryStr = new StringBuilder();
    queryStr.append(//from   w  w  w. ja  va  2 s  .  c  o m
            " SELECT DISTINCT fn.fileId as fileId,fn.fileNo as fileNo,fn.blNumber AS blNo,fn.bkgPieceId AS bkgPieceId  FROM  ");
    queryStr.append(" (SELECT DISTINCT bkp.file_number_id AS fileId,lfn.file_number AS fileNo,  ");
    queryStr.append(" CONCAT_WS('-',(SELECT IF(t.unlocationcode1 <> '',RIGHT(t.unlocationcode1,3),t.trmnum)  ");
    queryStr.append(" AS terminal FROM terminal t WHERE t.trmnum=lb.billing_terminal),  ");
    queryStr.append(
            " IF(dest.bl_numbering = 'Y',RIGHT(dest.un_loc_code, 3),dest.un_loc_code),lfn.file_number) AS blNumber,bkp.id as bkgPieceId   ");
    queryStr.append(
            "  FROM lcl_booking_piece bkp JOIN  lcl_booking_piece_unit bpu ON  bpu.booking_piece_id = bkp.id ");
    queryStr.append(
            " JOIN lcl_file_number lfn ON lfn.id=bkp.file_number_id  JOIN lcl_booking lb ON lb.file_number_id=lfn.id  ");
    queryStr.append(" JOIN un_location dest ON dest.id=lb.fd_id ");
    queryStr.append(
            " JOIN lcl_unit_ss luss ON luss.id = bpu.lcl_unit_ss_id WHERE luss.id =:unitSSId GROUP BY bkp.file_number_id) fn ");
    SQLQuery query = getCurrentSession().createSQLQuery(queryStr.toString());
    query.setLong("unitSSId", unitSSId);
    query.setResultTransformer(Transformers.aliasToBean(ManifestBean.class));
    query.addScalar("fileId", LongType.INSTANCE);
    query.addScalar("fileNo", StringType.INSTANCE);
    query.addScalar("blNo", StringType.INSTANCE);
    query.addScalar("bkgPieceId", LongType.INSTANCE);
    return query.list();
}

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 va2s . co  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);
    }//  w w w . j  a  v a2s .c  o m
    return null;
}