Example usage for org.hibernate SQLQuery addScalar

List of usage examples for org.hibernate SQLQuery addScalar

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

Introduction

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

Prototype

SQLQuery<T> addScalar(String columnAlias, Type type);

Source Link

Document

Declare a scalar query result.

Usage

From source file:com.formkiq.core.dao.ReportDaoImpl.java

License:Apache License

/**
 * Add Scalars./* w w w .jav a 2s.  c o  m*/
 * @param q {@link SQLQuery}
 * @param input {@link ReportInput}
 */
private void addScalars(final SQLQuery q, final ReportInput input) {
    for (ReportColumnInput c : input.getColumns()) {
        q.addScalar(c.getTitle(), StringType.INSTANCE);
    }
}

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

public List<ManifestBean> getUnitViewDrList(Long unitSSId, HttpServletRequest request) throws Exception { // to show DR or Bl list in View Dr page
    User user = (User) request.getSession().getAttribute("loginuser");
    StringBuilder queryBuilder = new StringBuilder();
    queryBuilder// w  w  w  .  j a  v  a 2  s  .com
            .append(" SELECT fn.fileId AS fileId, fn.fileNo AS fileNo, fn.STATUS AS STATUS,fn.state as state,");
    queryBuilder.append(
            "(SELECT CONCAT_WS('~~~',ar.invoice_number,ar.status) FROM ar_red_invoice ar WHERE ar.bl_number = fn.fileNo ORDER BY ar.id DESC LIMIT 1) AS arInvoiceNumber,");
    queryBuilder.append(
            " getDisposion_UnLoc(fn.fileId) AS disposition, IF(fn.state ='BL' , BlNumberSystemForLclExports(bl.file_number_id),'') AS blNo,");
    queryBuilder.append(
            " piece.total_piece as totalPieceCount, piece.total_weight_imperial as totalWeightImperial ,piece.total_volume_imperial as totalVolumeImperial, ");
    queryBuilder.append(
            " blPiece.blCft as blCft ,blPiece.blCbm as blCbm,blPiece.blLbs as blLbs ,blPiece.blKgs as blKgs, ");
    queryBuilder.append(" IF(fn.state = 'BL',bl.rate_type,'') AS rateType,  ");
    queryBuilder.append(
            " (SELECT SUM(chg.ap_amount) FROM lcl_booking_ac chg WHERE chg.file_number_id = fn.fileId AND deleted = '0' AND chg.ap_gl_mapping_id IN (SELECT id FROM gl_mapping WHERE charge_code = 'FFCOMM' AND shipment_type='LCLE')) AS ffComm,  ");
    queryBuilder.append(
            " IF(fn.state = 'BL',TradingPartnerAcctName(bl.ship_acct_no),TradingPartnerAcctName(b.ship_acct_no)) AS shipperName,  ");
    queryBuilder.append(
            " IF(fn.state = 'BL',TradingPartnerAcctName(bl.cons_acct_no),TradingPartnerAcctName(b.cons_acct_no)) AS consigneeName,  ");
    queryBuilder.append(
            " IF(fn.state = 'BL',TradingPartnerAcctName(bl.fwd_acct_no),TradingPartnerAcctName(b.fwd_acct_no)) AS forwarderName,  ");
    queryBuilder.append(" UnLocationGetCodeByID(b.poo_id) AS origin,");
    queryBuilder.append(" UnLocationGetCodeByID (b.fd_id) AS destination,");
    queryBuilder.append(" UnLocationGetCodeByID (b.pol_id) AS pol,");
    queryBuilder.append(" UnLocationGetCodeByID (b.pod_id) AS pod,");
    queryBuilder.append(
            " IF(fn.state = 'BL',TerminalGetLocationByNo(bl.billing_terminal),TerminalGetLocationByNo(b.billing_terminal))AS terminalLocation,");
    queryBuilder.append(
            " IF(b.poo_pickup,(SELECT pickup_city FROM lcl_booking_pad WHERE file_number_id =fn.fileId),'')  AS pickupCity, ");
    queryBuilder
            .append(" UnLocationGetNameByID(b.poo_id) AS originName,getStateCode(b.poo_id) AS originState,");
    queryBuilder.append(
            " UnLocationGetNameByID(b.pol_id) AS polName,getStateCode(b.pol_id) AS polState,UnLocationGetNameByID(b.pod_id) AS podName,");
    queryBuilder.append(
            " getStateCode(b.pod_id) AS podCountry,UnLocationGetNameByID(b.fd_id) AS destinationName,getStateCode(b.fd_id) AS destinationCountry,");
    queryBuilder.append(
            " bl.billing_type AS billingType,IF(b.client_pwk_recvd = 1, 'Y', '') AS doc,(SELECT invoice_number FROM TRANSACTION WHERE drcpt = fn.fileNo LIMIT 1) AS blInvoiceNo,");
    queryBuilder.append(
            "(SELECT schedule_no FROM lcl_ss_header WHERE id = b.booked_ss_header_id) AS bookedVoyageNo,bl.posted_by_user_id AS postedByUserId,fn.haz AS hazmat,");
    queryBuilder.append(
            "(SELECT CONCAT_WS('~~~',GROUP_CONCAT(htc.code SEPARATOR '<br>'),COUNT(htc.code),GROUP_CONCAT(LEFT(htc.code, INSTR(htc.code, '/') - 1)))");
    queryBuilder.append(" FROM lcl_booking_hot_code htc WHERE htc.file_number_id = fn.fileId) AS hotCodes, ");
    queryBuilder.append(
            " IF(bl.file_number_id != fn.fileId , null ,IF(fn.state = 'BL',(SELECT SUM(chg.ar_amount)+ SUM(chg.adjustment_amount) FROM lcl_bl_ac chg WHERE chg.file_number_id = bl.file_number_id AND chg.ar_bill_to_party = 'A'),(SELECT SUM(chg.ar_amount)+ SUM(chg.adjustment_amount) FROM lcl_booking_ac chg WHERE chg.file_number_id = fn.fileId AND chg.ar_bill_to_party = 'A'))) AS colCharge, ");
    queryBuilder.append(
            " IF(bl.file_number_id != fn.fileId , null ,IF(fn.state = 'BL',(SELECT SUM(chg.ar_amount)+ SUM(chg.adjustment_amount) FROM lcl_bl_ac chg WHERE chg.file_number_id = bl.file_number_id AND chg.ar_bill_to_party != 'A'),(SELECT SUM(chg.ar_amount)+ SUM(chg.adjustment_amount) FROM lcl_booking_ac chg WHERE chg.file_number_id = fn.fileId AND chg.ar_bill_to_party != 'A'))) AS ppdCharge, ");
    queryBuilder.append(
            " IF(bl.file_number_id != fn.fileId , '' ,IF(fn.state = 'BL',(SELECT GROUP_CONCAT(DISTINCT chg.ar_bill_to_party SEPARATOR '/') FROM lcl_bl_ac chg WHERE chg.file_number_id = bl.file_number_id AND chg.ar_bill_to_party != 'A'),(SELECT GROUP_CONCAT(DISTINCT chg.ar_bill_to_party SEPARATOR '/') FROM lcl_booking_ac chg WHERE chg.file_number_id = fn.fileId AND chg.ar_bill_to_party != 'A'))) AS ppdParties, ");
    // checking condition for  status and classname label in list
    queryBuilder.append(" CASE   ");
    Boolean isManifest_Posted_Bl = new RoleDutyDAO().getRoleDetails("lcl_manifest_postedbl",
            user.getRole().getRoleId());
    if (isManifest_Posted_Bl) {
        queryBuilder.append(" WHEN fn.state <> 'BL' AND lbe.no_bl_required ='1'  THEN 'purpleBold'  ");
    }
    queryBuilder.append(" WHEN fn.state <> 'BL' THEN 'fileNo' ");
    queryBuilder.append(" WHEN bl.file_number_id != fn.fileId THEN 'greenBold14px' ");
    queryBuilder.append(
            " WHEN (bl.posted_by_user_id <> '' OR  bl.posted_by_user_id IS NOT NULL ) AND  fn.status <> 'M' THEN  'purpleBold' ");
    queryBuilder.append(" WHEN fn.status ='M' THEN 'greenBold14px' ");
    queryBuilder.append(
            " WHEN fn.state = 'BL' AND (bl.posted_by_user_id='' OR  bl.posted_by_user_id IS NULL) THEN 'fileNo' END  AS className, ");
    // ---------------------------------------------------------------STATUS LABEL-----------------------------------------------------------------------------
    queryBuilder.append(" CASE  ");
    if (isManifest_Posted_Bl) {
        queryBuilder.append("  WHEN fn.state <> 'BL' AND lbe.no_bl_required ='1'  THEN 'NO B/L Required'   ");
    }
    queryBuilder.append(" WHEN fn.state <> 'BL' THEN 'NoBL'");
    queryBuilder.append(" WHEN bl.file_number_id != fn.fileId THEN 'CONS'");
    queryBuilder.append(
            " WHEN (bl.posted_by_user_id <> '' OR  bl.posted_by_user_id IS NOT NULL ) AND  fn.status <> 'M' THEN  'POSTED' ");
    queryBuilder.append(" WHEN fn.status ='M' THEN 'MANIFESTED' ");
    queryBuilder.append(
            " WHEN fn.state = 'BL' AND (bl.posted_by_user_id = '' OR  bl.posted_by_user_id IS NULL) THEN 'POOL' END  AS statusLabel,");
    queryBuilder.append(" IF(lc.id IS NOT NULL,TRUE,FALSE) AS isCorrection, ");
    queryBuilder.append(" IF(lc.id IS NOT NULL,(SELECT SUM(ch.new_amount) FROM lcl_correction_Charge ch  ");
    queryBuilder.append(" JOIN gl_mapping gl ON gl.id = ch.gl_mapping_id WHERE  ");
    queryBuilder.append(" ch.correction_id=lc.id AND gl.charge_code='FTFFEE'),  ");
    queryBuilder.append(
            " (SELECT SUM(blac.ar_amount) FROM lcl_bl_Ac blac JOIN gl_mapping gl ON gl.id = blac.ar_gl_mapping_id  ");
    queryBuilder.append(" WHERE file_number_id = bl.file_number_id AND gl.charge_code='FTFFEE')) AS ftfFee  ");

    // --------------------------------------------------------MAIN SUB QUERY------------------------------------------------------------------------------------
    queryBuilder.append(" from ( ");
    queryBuilder.append(
            " SELECT f.id AS fileId, f.file_number AS fileNo, f.state AS state, f.status AS STATUS, lbp.hazmat AS haz ");
    queryBuilder.append(
            " FROM lcl_file_number f JOIN lcl_booking_piece lbp ON lbp.file_number_id = f.id JOIN lcl_booking_piece_unit u ON u.booking_piece_id = lbp.id ");
    queryBuilder.append(" WHERE u.lcl_unit_ss_id =:unitSdId ) fn");
    // ---------------------------------------------------------JOIN STATS HERE-----------------------------------------------------------------------------------
    queryBuilder.append(" LEFT JOIN lcl_booking b  ON fn.fileId = b.file_number_id ");
    // ------------------------------------- Conslidation logic is applied please verify ----------------------------------------------------------------
    queryBuilder
            .append("  LEFT JOIN lcl_bl bl  ON (bl.file_number_id = getHouseBLForConsolidateDr(fn.fileId))");
    queryBuilder.append(
            "  LEFT JOIN lcl_correction lc ON (lc.file_number_id = bl.file_number_id AND lc.status = 'A') ");

    if (isManifest_Posted_Bl) {
        queryBuilder.append(" JOIN lcl_booking_export lbe ON fn.fileId = lbe.file_number_id  ");
    }
    queryBuilder.append(getSumOfCommodityValues());
    queryBuilder.append(getSumOfBlCommodityValues());
    queryBuilder.append(" GROUP BY fn.fileId ");
    queryBuilder.append(" ORDER BY fn.fileNo ");

    SQLQuery query = getCurrentSession().createSQLQuery(queryBuilder.toString());
    query.setParameter("unitSdId", unitSSId);
    query.setResultTransformer(Transformers.aliasToBean(ManifestBean.class));
    query.addScalar("fileId", LongType.INSTANCE);
    query.addScalar("fileNo", StringType.INSTANCE);
    query.addScalar("status", StringType.INSTANCE);
    query.addScalar("state", StringType.INSTANCE);
    query.addScalar("arInvoiceNumber", StringType.INSTANCE);
    query.addScalar("disposition", StringType.INSTANCE);
    query.addScalar("blNo", StringType.INSTANCE);
    query.addScalar("totalPieceCount", IntegerType.INSTANCE);
    query.addScalar("totalWeightImperial", BigDecimalType.INSTANCE);
    query.addScalar("totalVolumeImperial", BigDecimalType.INSTANCE);
    query.addScalar("blCft", BigDecimalType.INSTANCE);
    query.addScalar("blCbm", BigDecimalType.INSTANCE);
    query.addScalar("blKgs", BigDecimalType.INSTANCE);
    query.addScalar("blLbs", BigDecimalType.INSTANCE);
    query.addScalar("rateType", StringType.INSTANCE);
    query.addScalar("ffComm", BigDecimalType.INSTANCE);
    query.addScalar("shipperName", StringType.INSTANCE);
    query.addScalar("consigneeName", StringType.INSTANCE);
    query.addScalar("forwarderName", StringType.INSTANCE);
    query.addScalar("origin", StringType.INSTANCE);
    query.addScalar("destination", StringType.INSTANCE);
    query.addScalar("pol", StringType.INSTANCE);
    query.addScalar("pod", StringType.INSTANCE);
    query.addScalar("terminalLocation", StringType.INSTANCE);
    query.addScalar("pickupCity", StringType.INSTANCE);
    query.addScalar("originName", StringType.INSTANCE);
    query.addScalar("originState", StringType.INSTANCE);
    query.addScalar("polName", StringType.INSTANCE);
    query.addScalar("polState", StringType.INSTANCE);
    query.addScalar("podName", StringType.INSTANCE);
    query.addScalar("podCountry", StringType.INSTANCE);
    query.addScalar("destinationName", StringType.INSTANCE);
    query.addScalar("destinationCountry", StringType.INSTANCE);
    query.addScalar("billingType", StringType.INSTANCE);
    query.addScalar("doc", StringType.INSTANCE);
    query.addScalar("blInvoiceNo", StringType.INSTANCE);
    query.addScalar("bookedVoyageNo", StringType.INSTANCE);
    query.addScalar("hotCodes", StringType.INSTANCE);
    query.addScalar("postedByUserId", IntegerType.INSTANCE);
    query.addScalar("hazmat", BooleanType.INSTANCE);
    query.addScalar("colCharge", BigDecimalType.INSTANCE);
    query.addScalar("ppdCharge", BigDecimalType.INSTANCE);
    query.addScalar("ppdParties", StringType.INSTANCE);
    query.addScalar("className", StringType.INSTANCE);
    query.addScalar("statusLabel", StringType.INSTANCE);
    query.addScalar("isCorrection", BooleanType.INSTANCE);
    query.addScalar("ftfFee", BigDecimalType.INSTANCE);
    List<ManifestBean> drList = query.list();
    return drList;
}

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());
    }/*from  w  ww  . java2  s .  com*/
    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.  j  a va  2 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> getInandVoyageList(LclUnitsScheduleForm lclUnitsScheduleForm)
        throws Exception {
    StringBuilder queryStr = new StringBuilder();
    queryStr.append(/*from  www  .  j a v a 2  s  .  co  m*/
            " 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 ww. j  a  v  a 2  s.  co 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(// ww  w .ja  v  a  2s  . c om
            "  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 .j a va2  s .  co  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 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;
    }//  ww w . j a va  2s  .co  m
    SQLQuery query = getCurrentSession().createSQLQuery(sb);
    query.setParameter("headerId", headerId);
    query.addScalar("result", BooleanType.INSTANCE);
    return (Boolean) query.uniqueResult();
}