List of usage examples for org.hibernate.type StringType INSTANCE
StringType INSTANCE
To view the source code for org.hibernate.type StringType INSTANCE.
Click Source Link
From source file:com.formkiq.core.dao.UserDaoImpl.java
License:Apache License
@SuppressWarnings("resource") @Override/*from www . j av a 2s . c o m*/ public UserDTO findUserDTO(final String email) { String sql = "select u.user_id as userid, u.email as email, " + " u.password as password, " + " u.status as status, u.role as role, " + " u.lastlogin_date as lastlogin, " + " u.last_user_agent as lastuseragent, " + " u.login_token as logintoken " + " from Users u" + " where u.email=:email"; Session session = getEntityManager().unwrap(Session.class); UserDTO dto = (UserDTO) session.createSQLQuery(sql).addScalar("userid", StringType.INSTANCE) .addScalar("email", StringType.INSTANCE).addScalar("password", StringType.INSTANCE) .addScalar("status", StringType.INSTANCE).addScalar("role", StringType.INSTANCE) .addScalar("lastlogin", TimestampType.INSTANCE).addScalar("lastuseragent", StringType.INSTANCE) .addScalar("logintoken", StringType.INSTANCE).setParameter("email", email) .setResultTransformer(new AliasToBeanResultTransformer(UserDTO.class)).uniqueResult(); if (dto != null) { List<FolderDTO> folders = this.folderDao.findFoldersDTO(email, FolderStatus.ACTIVE); dto.setFolders(folders); UUID userid = dto.getUUID(); Collection<UserSetting> settings = findUserSettings(userid); Map<String, String> map = new HashMap<>(); for (UserSetting us : settings) { map.put(us.getSetting().name(), us.getValue()); } dto.setSettings(map); } return dto; }
From source file:com.formkiq.core.dao.UserDaoImpl.java
License:Apache License
@SuppressWarnings({ "unchecked", "resource" })
@Override//from w ww .j ava 2s . co m
public UserListDTO findUsers(final String token, final String text) {
int offset = Strings.getOffset(token);
int max = Strings.getMaxResults(token, DEFAULT_MAX_RESULTS);
StringBuilder sql = new StringBuilder("select u.user_id as userid, " + " u.email as email, "
+ " u.status as status, " + " u.role as role, " + " u.lastlogin_date as lastlogin, "
+ " u.last_user_agent as lastuseragent " + " from Users u ");
if (StringUtils.hasText(text)) {
sql.append("where u.email like :text ");
}
sql.append("order by u.email");
sql.append(" OFFSET " + offset + " FETCH FIRST " + (max + 1) + " ROWS ONLY");
Session session = getEntityManager().unwrap(Session.class);
org.hibernate.Query query = session.createSQLQuery(sql.toString()).addScalar("userid", StringType.INSTANCE)
.addScalar("email", StringType.INSTANCE).addScalar("status", StringType.INSTANCE)
.addScalar("role", StringType.INSTANCE).addScalar("lastlogin", TimestampType.INSTANCE)
.addScalar("lastuseragent", StringType.INSTANCE)
.setResultTransformer(new AliasToBeanResultTransformer(UserDTO.class));
if (StringUtils.hasText(text)) {
query.setParameter("text", "%" + text + "%");
}
List<UserDTO> list = query.list();
UserListDTO dto = new UserListDTO();
List<UserDTO> truncated = updatePagination(dto, offset, max, list);
dto.setUsers(truncated);
return dto;
}
From source file:com.googlecode.sarasvati.example.hib.HibExampleConsole.java
License:Open Source License
@SuppressWarnings("unchecked") public static HibGraph getGraph(final HibEngine engine) { HibGraph graph = null;/*from w w w .j a v a 2 s.com*/ while (graph == null) { List<String> graphNames = engine.getSession() .createSQLQuery("select distinct name from wf_graph order by name") .addScalar("name", StringType.INSTANCE).list(); List<HibGraph> graphs = new ArrayList<HibGraph>(graphNames.size()); for (String graphName : graphNames) { graphs.add(engine.getRepository().getLatestGraph(graphName)); } int count = 0; for (HibGraph g : graphs) { System.out.println((++count) + ": " + g.getName() + ": version " + g.getVersion()); } System.out.print("> "); String input = readLine(); if ("log".equals(input)) { log = !log; if (log) { engine.addExecutionListener(LoggingExecutionListener.class, ExecutionEventType.values()); } else { engine.removeExecutionListener(LoggingExecutionListener.class); } System.out.println("Logging set to: " + log); continue; } try { int line = Integer.parseInt(input); if (line > 0 && line <= graphs.size()) { graph = graphs.get(line - 1); } else { System.out.println("Please enter a valid number"); } } catch (NumberFormatException nfe) { System.out.println("Please enter a valid number"); } } return graph; }
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 . j a va2s . 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()); }/* w ww . j a v a 2 s. c om*/ 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 . ja va 2 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 w w w. j a va2s .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(/* www .j av 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(/* w w w .ja v a 2s.co 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(); }