List of usage examples for org.hibernate SQLQuery setResultTransformer
@Deprecated Query<R> setResultTransformer(ResultTransformer transformer);
From source file:com.ett.self.print.biz.PrintBizImpl.java
protected void searchFlowFromSql(String sql, String idcard) { SessionFactory sessionFactory = this.getBaseDaoDrv().getHibernateSessionFactory(); Session session = sessionFactory.openSession(); Transaction tx = session.beginTransaction(); String queryStr = MessageFormat.format(sql, idcard); log.debug("?" + queryStr.toString()); SQLQuery query = session.createSQLQuery(queryStr.toString()); query.addScalar("ywlx", new org.hibernate.type.StringType()); query.addScalar("fee", new org.hibernate.type.StringType()); query.addScalar("ywyy", new org.hibernate.type.StringType()); query.addScalar("yyrq", new org.hibernate.type.TimestampType()); query.addScalar("ksrq", new org.hibernate.type.TimestampType()); query.addScalar("slrq", new org.hibernate.type.TimestampType()); query.addScalar("lsh", new org.hibernate.type.StringType()); query.addScalar("xm", new org.hibernate.type.StringType()); query.addScalar("hphm", new org.hibernate.type.StringType()); query.addScalar("jdsbh", new org.hibernate.type.StringType()); query.addScalar("sfzmhm", new org.hibernate.type.StringType()); query.addScalar("zjcx", new org.hibernate.type.StringType()); query.addScalar("dabh", new org.hibernate.type.StringType()); query.setResultTransformer(Transformers.aliasToBean(DrvPersonFlowObject.class)); try {//from w w w . j a v a 2 s . c o m tx.begin(); //query.uniqueResult() this.lists = query.list(); if (log.isInfoEnabled()) { log.info("?==" + lists.size()); } if (this.lists == null || this.lists.size() == 0) { this.lists = new ArrayList(); //this.lists.add(co); } session.flush(); tx.commit(); } catch (Exception e) { e.printStackTrace(); log.info(e); if (tx != null) { tx.rollback(); } } finally { session.close(); } //return null; }
From source file:com.formkiq.core.dao.SystemDaoImpl.java
License:Apache License
@SuppressWarnings({ "unchecked", "resource" })
@Override/* ww w . j ava2 s.c o m*/
public List<SystemPropertyDTO> getProperties(final User user, final String key) {
String sql = "select p.key as key, p.value as value, " + "p.user_id as \"user.uuid\","
+ "u.email as \"user.email\"" + " from system_properties p "
+ " left join users u on u.user_id=p.user_id";
if (user != null || StringUtils.hasText(key)) {
sql += " where ";
}
if (user != null) {
sql += " p.user_id=:user";
}
if (StringUtils.hasText(key)) {
sql += user != null ? " and " : " ";
sql += "key=:key";
}
sql += " order by p.key ";
Session session = getEntityManager().unwrap(Session.class);
SQLQuery q = session.createSQLQuery(sql).addScalar("key", StringType.INSTANCE)
.addScalar("value", StringType.INSTANCE).addScalar("user.email", StringType.INSTANCE)
.addScalar("user.uuid", PostgresUUIDType.INSTANCE);
if (user != null) {
q.setParameter("user", user.getUserid(), PostgresUUIDType.INSTANCE);
}
if (StringUtils.hasText(key)) {
q.setParameter("key", key);
}
List<Map<String, Object>> maplist = q.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE)
.list();
List<SystemPropertyDTO> list = transformMapListToObject(maplist, SystemPropertyDTO.class);
return list;
}
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/*from w w w . jav a 2 s . c o m*/ .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 w w. j a v a 2 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(//www .j av 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> getInandVoyageList(LclUnitsScheduleForm lclUnitsScheduleForm) throws Exception { StringBuilder queryStr = new StringBuilder(); queryStr.append(/*from www .j a va2s .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(/* www . ja va2 s . 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 ww w .jav 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 av a2 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(); }