List of usage examples for org.hibernate.transform Transformers aliasToBean
public static ResultTransformer aliasToBean(Class target)
From source file:com.gisgraphy.hibernate.criterion.ProjectionOrderTest.java
License:Open Source License
@SuppressWarnings("unchecked") @Test//from ww w.j a v a 2s. c om public void testProjectionOrderShouldSortDesc() { final City p1 = GisgraphyTestHelper.createCity("paris", 48.86667F, 2.3333F, 1L); City p2 = GisgraphyTestHelper.createCity("bordeaux", 44.83333F, -0.56667F, 3L); City p3 = GisgraphyTestHelper.createCity("goussainville", 49.01667F, 2.46667F, 2L); this.cityDao.save(p1); this.cityDao.save(p2); this.cityDao.save(p3); HibernateCallback hibernateCallback = new HibernateCallback() { public Object doInHibernate(Session session) throws PersistenceException { Criteria testCriteria = session.createCriteria(City.class); List<String> fieldList = new ArrayList<String>(); fieldList.add("name"); Projection projection = Projections.property("featureId").as("featureId"); testCriteria.setProjection(projection).addOrder(new ProjectionOrder("featureId", false)) .setResultTransformer(Transformers.aliasToBean(_CityDTO.class)); List<_CityDTO> results = testCriteria.list(); return results; } }; List<_CityDTO> cities = (List<_CityDTO>) testDao.testCallback(hibernateCallback); assertEquals(3, cities.size()); assertEquals("3", cities.get(0).getFeatureId().toString()); assertEquals("2", cities.get(1).getFeatureId().toString()); assertEquals("1", cities.get(2).getFeatureId().toString()); }
From source file:com.gisgraphy.hibernate.projection.ProjectionBeanTest.java
License:Open Source License
@SuppressWarnings("unchecked") @Test// w w w .java 2s.c o m public void testPropertiesList() { City p1 = GisgraphyTestHelper.createCity("paris", 48.86667F, 2.3333F, 1L); this.cityDao.save(p1); HibernateCallback hibernateCallback = new HibernateCallback() { public Object doInHibernate(Session session) throws PersistenceException { Criteria testCriteria = session.createCriteria(City.class); List<String> fieldList = new ArrayList<String>(); fieldList.add("name"); fieldList.add("featureId"); ProjectionList projection = ProjectionBean.fieldList(fieldList, true); testCriteria.setProjection(projection); testCriteria.setResultTransformer(Transformers.aliasToBean(City.class)); List<City> results = testCriteria.list(); return results; } }; List<City> cities = (List<City>) testDao.testCallback(hibernateCallback); assertEquals(1, cities.size()); assertEquals("paris", cities.get(0).getName()); assertEquals("1", cities.get(0).getFeatureId() + ""); }
From source file:com.gisgraphy.hibernate.projection.ProjectionBeanTest.java
License:Open Source License
@SuppressWarnings("unchecked") @Test//from www .ja v a 2 s .com public void testBeanFieldList() { City p1 = GisgraphyTestHelper.createCity("paris", 48.86667F, 2.3333F, 1L); this.cityDao.save(p1); HibernateCallback hibernateCallback = new HibernateCallback() { public Object doInHibernate(Session session) throws PersistenceException { try { Criteria testCriteria = session.createCriteria(City.class); String[] ignoreFields = { "distance" }; ProjectionList projection = ProjectionBean.beanFieldList(_CityDTO.class, ignoreFields, true); testCriteria.setProjection(projection); testCriteria.setResultTransformer(Transformers.aliasToBean(_CityDTO.class)); List<_CityDTO> results = testCriteria.list(); return results; } catch (HibernateException e) { fail("An exception has occured : maybe ignoreFields are not taken into account if the error is 'could not resolve property: distance... :" + e); throw e; } } }; List<_CityDTO> cities = (List<_CityDTO>) testDao.testCallback(hibernateCallback); assertEquals(1, cities.size()); assertEquals("paris", cities.get(0).getName()); assertEquals("1", cities.get(0).getFeatureId() + ""); }
From source file:com.gisgraphy.hibernate.projection.SpatialProjectionTest.java
License:Open Source License
@SuppressWarnings("unchecked") @Test/* ww w . j a v a 2 s . c o m*/ public void testdistance_pointToLine() { LineString shape = GeolocHelper.createLineString("LINESTRING (6.9416088 50.9154239,6.9410001 50.9154734)"); OpenStreetMap streetOSM = GisgraphyTestHelper.createOpenStreetMapForPeterMartinStreet(); streetOSM.setShape(shape); openStreetMapDao.save(streetOSM); assertNotNull(openStreetMapDao.get(streetOSM.getId())); final Point p1 = GeolocHelper.createPoint(6.9412748F, 50.9155829F); HibernateCallback hibernateCallback = new HibernateCallback() { public Object doInHibernate(Session session) throws PersistenceException { Criteria testCriteria = session.createCriteria(OpenStreetMap.class); ProjectionList projection = Projections.projectionList() .add(Projections.property("name").as("name")).add(SpatialProjection .distance_pointToLine(p1, OpenStreetMap.SHAPE_COLUMN_NAME).as("distance")) .add(Projections.property("shape").as("shape")); // remove the from point testCriteria.setProjection(projection); testCriteria.setResultTransformer(Transformers.aliasToBean(_OpenstreetmapDTO.class)); List<_OpenstreetmapDTO> results = testCriteria.list(); return results; } }; List<_OpenstreetmapDTO> streets = (List<_OpenstreetmapDTO>) testDao.testCallback(hibernateCallback); assertEquals(1, streets.size()); Double calculatedDist = 14.76D; Double retrieveDistance = streets.get(0).getDistance(); double percent = (Math.abs(calculatedDist - retrieveDistance) * 100) / Math.min(retrieveDistance, calculatedDist); assertTrue("There is more than one percent of error beetween the calculated distance (" + calculatedDist + ") and the retrieved one (" + retrieveDistance + ")", percent < 1); }
From source file:com.gisgraphy.hibernate.projection.SpatialProjectionTest.java
License:Open Source License
@SuppressWarnings("unchecked") @Test//from ww w .j a va 2 s . c om @Ignore public void testDistance_sphere() { final City p1 = GisgraphyTestHelper.createCity("paris", 48.86667F, 2.3333F, 1L); City p2 = GisgraphyTestHelper.createCity("bordeaux", 44.83333F, -0.56667F, 3L); this.cityDao.save(p1); this.cityDao.save(p2); HibernateCallback hibernateCallback = new HibernateCallback() { public Object doInHibernate(Session session) throws PersistenceException { Criteria testCriteria = session.createCriteria(City.class); ProjectionList projection = Projections.projectionList() .add(Projections.property("name").as("name")).add(SpatialProjection .distance_sphere(p1.getLocation(), GisFeature.LOCATION_COLUMN_NAME).as("distance")); // remove the from point testCriteria.add(Restrictions.ne("id", p1.getId())).setProjection(projection); testCriteria.setResultTransformer(Transformers.aliasToBean(_CityDTO.class)); List<_CityDTO> results = testCriteria.list(); return results; } }; List<_CityDTO> cities = (List<_CityDTO>) testDao.testCallback(hibernateCallback); assertEquals(1, cities.size()); assertEquals("bordeaux", cities.get(0).getName()); Double calculatedDist = p1.distanceTo(p2.getLocation()); Double retrieveDistance = cities.get(0).getDistance(); double percent = (Math.abs(calculatedDist - retrieveDistance) * 100) / Math.min(retrieveDistance, calculatedDist); assertTrue("There is more than one percent of error beetween the calculated distance (" + calculatedDist + ") and the retrieved one (" + retrieveDistance + ")", percent < 1); }
From source file:com.gisgraphy.hibernate.projection.SpatialProjectionTest.java
License:Open Source License
@SuppressWarnings("unchecked") @Test/*from w ww. j av a 2 s .c om*/ @Ignore public void testDistance() { float x1 = -2f; float y1 = 2f; float x2 = 2f; float y2 = 2f; Point locationParis = GeolocHelper.createPoint(x1, y1); //locationParis.setSRID(-1); Point locationbordeaux = GeolocHelper.createPoint(x2, y2); //locationbordeaux.setSRID(-1); final City p1 = GisgraphyTestHelper.createCity("paris", 0F, 0F, 1L); p1.setLocation(locationParis); City p2 = GisgraphyTestHelper.createCity("bordeaux", 0F, 0F, 3L); p2.setLocation(locationbordeaux); this.cityDao.save(p1); this.cityDao.save(p2); HibernateCallback hibernateCallback = new HibernateCallback() { public Object doInHibernate(Session session) throws PersistenceException { Criteria testCriteria = session.createCriteria(City.class); ProjectionList projection = Projections.projectionList() .add(Projections.property("name").as("name")).add(SpatialProjection .distance(p1.getLocation(), GisFeature.LOCATION_COLUMN_NAME).as("distance")); // remove the from point testCriteria.add(Restrictions.ne("id", p1.getId())).setProjection(projection); testCriteria.setResultTransformer(Transformers.aliasToBean(_CityDTO.class)); List<_CityDTO> results = testCriteria.list(); return results; } }; List<_CityDTO> cities = (List<_CityDTO>) testDao.testCallback(hibernateCallback); assertEquals(1, cities.size()); assertEquals("bordeaux", cities.get(0).getName()); Double calculatedDist = Math.sqrt(Math.pow(x2 - x1, 2) + Math.pow(y2 - y1, 2));//cartesian distance Double retrieveDistance = cities.get(0).getDistance(); double percent = (Math.abs(calculatedDist - retrieveDistance) * 100) / Math.min(retrieveDistance, calculatedDist); assertTrue("There is more than one percent of error beetween the calculated distance (" + calculatedDist + ") and the retrieved one (" + retrieveDistance + ")", percent < 1); }
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 .ja v a 2s . co 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 ww w .jav a 2 s.c o m*/ query.setResultTransformer(Transformers.aliasToBean(ExportVoyageSearchModel.class)); query.addScalar("ssHeaderId", StringType.INSTANCE); query.addScalar("voyageStatus", StringType.INSTANCE); query.addScalar("scheduleNo", StringType.INSTANCE); query.addScalar("serviceType", StringType.INSTANCE); query.addScalar("serviceType", StringType.INSTANCE); query.addScalar("fdUnLocCode", StringType.INSTANCE); query.addScalar("fdName", StringType.INSTANCE); query.addScalar("departPierUnloc", StringType.INSTANCE); query.addScalar("departPier", StringType.INSTANCE); query.addScalar("arrivalPierUnloc", StringType.INSTANCE); query.addScalar("arrivalPier", StringType.INSTANCE); query.addScalar("loadingDeadLineDate", StringType.INSTANCE); query.addScalar("etaSailDate", StringType.INSTANCE); query.addScalar("etaPodDate", StringType.INSTANCE); query.addScalar("totaltransPod", StringType.INSTANCE); query.addScalar("totaltransFd", StringType.INSTANCE); query.addScalar("vesselName", StringType.INSTANCE); query.addScalar("ssVoyage", StringType.INSTANCE); query.addScalar("carrierName", StringType.INSTANCE); query.addScalar("carrierAcctNo", StringType.INSTANCE); query.addScalar("sealNo", StringType.INSTANCE); query.addScalar("unitNo", StringType.INSTANCE); query.addScalar("unitSize", StringType.INSTANCE); query.addScalar("numberDrs", StringType.INSTANCE); query.addScalar("loadedBy", StringType.INSTANCE); query.addScalar("doorLocation", StringType.INSTANCE); query.addScalar("dispoCode", StringType.INSTANCE); query.addScalar("dispoDesc", StringType.INSTANCE); query.addScalar("isInbond", StringType.INSTANCE); query.addScalar("dispoDesc", StringType.INSTANCE); query.addScalar("isHazmat", StringType.INSTANCE); query.addScalar("totalWeightMetric", BigDecimalType.INSTANCE); query.addScalar("totalVolumeMetric", BigDecimalType.INSTANCE); query.addScalar("unitSizeShortDesc", StringType.INSTANCE); return query.list(); }
From source file:com.gp.cong.lcl.common.constant.ExportUnitQueryUtils.java
public List<ExportVoyageSearchModel> getViewAllList(Integer pooId, Integer fdId, String serviceType, String transMode) throws Exception { StringBuilder queryBuilder = new StringBuilder(); queryBuilder.append(/*from www. j a v a 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(// w ww . ja v a 2 s .c o 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(); }