Java tutorial
/**<pre> *========================================================================== * * Copyright: (C) IBM Corporation 2010 -- IBM Internal Use Only * *========================================================================== * * FILE: IVehicleOwnerDataService.java * CREATOR: Eldon Barrows * DEPT: GBS PAK * DATE: 01/05/2011 * *-PURPOSE----------------------------------------------------------------- * This is the implementation for IVehicleOwnerDataTypeService * *------------------------------------------------------------------------- * * *-CHANGE LOG-------------------------------------------------------------- * 01/05/2011 Eldon Initial coding. *========================================================================== * </pre> */ package com.rta.vsd.data.service.impl; import java.math.BigDecimal; import java.util.Calendar; import java.util.Date; import java.util.List; import org.apache.log4j.Logger; import org.hibernate.Criteria; import org.hibernate.Hibernate; import org.hibernate.NonUniqueResultException; import org.hibernate.Query; import org.hibernate.Session; import org.hibernate.criterion.Order; import org.hibernate.criterion.Restrictions; import org.hibernate.transform.AliasToBeanResultTransformer; import com.rta.vsd.data.DataServiceContext; import com.rta.vsd.data.service.IDataService; import com.rta.vsd.data.service.IVehicleOwnerDataService; import com.rta.vsd.dto.VsdVehicleOwner; import com.rta.vsd.dto.custom.FleetSearchCriteria; import com.rta.vsd.dto.custom.FleetSearchResult; import com.rta.vsd.dto.custom.PaginationParam; import com.rta.vsd.dto.custom.VehiclePlate; import com.rta.vsd.exception.data.VSDDataAccessException; import com.rta.vsd.exception.data.VSDDuplicateDataException; import com.rta.vsd.utility.Constant; import com.rta.vsd.utility.VSDLogger; /** * This is the implementation for IVehicleOwnerDataService * * @author Eldon Barrows * */ public class VehicleOwnerDataServiceImpl extends DataServiceImpl implements IVehicleOwnerDataService { private static Logger logger = VSDLogger.init(VehicleOwnerDataServiceImpl.class.getName()); /** * * Gets vehicleOwner by trafficFileNumber * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param trafficFileNumber * @return VsdVehicleOwner * @throws VSDDataAccessException */ public VsdVehicleOwner getOwnerByTrafficFileNumber(DataServiceContext dsContext, boolean retrieveArabicData, String trafficFileNumber) throws VSDDataAccessException { logger.info("getOwnerByTrafficFileNumber -- START"); VsdVehicleOwner vsdVehicleOwner = null; try { Session session = (Session) dsContext.getInternalContext(); vsdVehicleOwner = (VsdVehicleOwner) session.createCriteria(VsdVehicleOwner.class, "vo") .add(Restrictions.eq("vo.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("vo.trafficFileNumber", trafficFileNumber).ignoreCase()).uniqueResult(); } catch (Exception ex) { logger.error("An error occured in getOwnerByTrafficFileNumber()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getOwnerByTrafficFileNumber -- END"); return vsdVehicleOwner; } /** * * Saves vehicleOwner rule * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param vsdVehicleOwner * @return VsdVehicleOwner * @throws VSDDataAccessException */ public VsdVehicleOwner saveVehicleOwner(DataServiceContext dsContext, boolean retrieveArabicData, VsdVehicleOwner vsdVehicleOwner) throws VSDDataAccessException { logger.info("saveVehicleOwner -- START"); try { Session session = (Session) dsContext.getInternalContext(); session.save(vsdVehicleOwner); } catch (Exception ex) { logger.error("An error occured in saveVehicleOwner()"); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("saveVehicleOwner -- START"); return vsdVehicleOwner; } /** * * Update vehicleOwner * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param vsdVehicleOwner * @return VsdVehicleOwner * @throws VSDDataAccessException */ public VsdVehicleOwner updateVehicleOwner(DataServiceContext dsContext, boolean retrieveArabicData, VsdVehicleOwner vsdVehicleOwner) throws VSDDataAccessException { logger.info("updateVehicleOwner -- START"); try { Query query = createDynamicUpdateQuery(vsdVehicleOwner, dsContext); query.executeUpdate(); logger.info("updateVehicleOwner -- END"); return vsdVehicleOwner; } catch (Exception ex) { logger.error("An error occured in updateVehicleOwner()"); throw new VSDDataAccessException(ex.getMessage()); } } /** * * Gets vehicleOwner by trafficFileNumber * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param trafficFileNumber * @return VsdVehicleOwner * @throws VSDDataAccessException */ public VsdVehicleOwner getVehicleOwnerByTrafficFileNumber(DataServiceContext dsContext, boolean retrieveArabicData, String trafficFileNumber) throws VSDDataAccessException, VSDDuplicateDataException { logger.info("getVehicleOwnerByTrafficFileNumber -- START"); VsdVehicleOwner vsdVehicleOwner = null; try { Session session = (Session) dsContext.getInternalContext(); logger.debug("DataService : trafficFileNumber:" + trafficFileNumber); vsdVehicleOwner = (VsdVehicleOwner) session.createCriteria(VsdVehicleOwner.class, "vo") .add(Restrictions.eq("vo.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vo.vsdVehicleOwnerType", "vot", Criteria.LEFT_JOIN, Restrictions.eq("vot.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vo.vsdVehicleOwnActivities", "voa", Criteria.LEFT_JOIN, Restrictions.eq("voa.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("voa.vsdVehicleActivity", "va", Criteria.LEFT_JOIN, Restrictions.eq("va.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vo.vsdRRProfilings", "rrp", Criteria.LEFT_JOIN, Restrictions.eq("rrp.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vo.vsdVehOwnConDetails", "vocd", Criteria.LEFT_JOIN, Restrictions.eq("vocd.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vocd.vsdSupportedLanguage", "sl", Criteria.LEFT_JOIN, Restrictions.eq("sl.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vocd.vsdVOCDetComChas", "vocdcc", Criteria.LEFT_JOIN, Restrictions.eq("vocdcc.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vocdcc.vsdComChannel", "cc", Criteria.LEFT_JOIN, Restrictions.eq("cc.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("vo.trafficFileNumber", trafficFileNumber)).uniqueResult(); logger.debug("DataService : vsdVehicleOwner:" + vsdVehicleOwner); if (vsdVehicleOwner != null) { logger.debug("DataService : vsdVehicleOwner.getTrafficFileNumber():" + vsdVehicleOwner.getTrafficFileNumber()); } } catch (NonUniqueResultException nEx) { logger.error("An error occured in getVehicleOwnerByTrafficFileNumber"); throw new VSDDuplicateDataException( "getVehicleOwnerByTrafficFileNumber() did not return a unique result"); } catch (Exception ex) { logger.error("An error occured in getVehicleOwnerByTrafficFileNumber()"); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getVehicleOwnerByTrafficFileNumber -- END"); return vsdVehicleOwner; } /** * * Gets vehicleOwner by trafficFileNumber * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param trafficFileNumber * @return VsdVehicleOwner * @throws VSDDataAccessException */ public VsdVehicleOwner getVehicleOwnerWithDetailsByTrafficFileNumber(DataServiceContext dsContext, boolean retrieveArabicData, String trafficFileNumber) throws VSDDataAccessException { logger.info("getVehicleOwnerWithDetailsByTrafficFileNumber -- START"); VsdVehicleOwner vsdVehicleOwner = null; try { Session session = (Session) dsContext.getInternalContext(); vsdVehicleOwner = (VsdVehicleOwner) session.createCriteria(VsdVehicleOwner.class, "vo") .add(Restrictions.eq("vo.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vo.vsdVehicleOwnActivities", "voa", Criteria.LEFT_JOIN, Restrictions.eq("voa.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("voa.vsdVehicleActivity", "va", Criteria.LEFT_JOIN, Restrictions.eq("va.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vo.vsdRRProfilings", "rrp", Criteria.LEFT_JOIN, Restrictions.eq("rrp.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("rrp.vsdRiskRating", "rr", Criteria.LEFT_JOIN, Restrictions.eq("rr.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("vo.trafficFileNumber", trafficFileNumber).ignoreCase()) .createCriteria("vo.vsdVehicleOwner", "pvo", Criteria.LEFT_JOIN, Restrictions.eq("pvo.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("pvo.vsdVehOwnConDetails", "pvocd", Criteria.LEFT_JOIN, Restrictions.eq("pvocd.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vo.vsdVehOwnConDetails", "vocd", Criteria.LEFT_JOIN, Restrictions.eq("vocd.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("pvocd.vsdSupportedLanguage", "sl", Criteria.LEFT_JOIN, Restrictions.eq("sl.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vocd.vsdSupportedLanguage", "sl1", Criteria.LEFT_JOIN, Restrictions.eq("sl1.isDeleted", IDataService.BOOL_FALSE)) .uniqueResult(); } catch (Exception ex) { logger.error("An error occured in getVehicleOwnerWithDetailsByTrafficFileNumber()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getVehicleOwnerWithDetailsByTrafficFileNumber -- END"); return vsdVehicleOwner; } /** * * Searches for the fleet operator based on the criteria provided * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param fleetSearchCriteria * @return List<FleetSearchResult> * @throws VSDDataAccessException */ public List<FleetSearchResult> searchFleetOperator(DataServiceContext dsContext, boolean retrieveArabicData, FleetSearchCriteria fleetSearchCriteria) throws VSDDataAccessException { logger.info("searchFleetOperator -- START"); List<FleetSearchResult> result = null; try { /* Select vinfoset.VEHICLE_OWNER_ID as "vehicleOwnerId", vinfoset.OWNER_NAME as "operatorName", vinfoset.OWNER_NAME_A as "operatorNameA", vinfoset.TRAFFIC_FILE_NUMBER as "trafficFileNumber", vinfoset.TRADE_LICENSE_NUMBER as "tradeLicenseNumber" , vinfoset.vehicleCount as "totalNumOfVehicles", decode(oi1.violationCount , NULL, 0 , oi1.violationCount) as "totalNumOfViolations" from ( SELECT vo.VEHICLE_OWNER_ID , vo.OWNER_NAME , vo.OWNER_NAME_A, vo.TRAFFIC_FILE_NUMBER, vo.TRADE_LICENSE_NUMBER, count (v.VEHICLE_ID) vehicleCount FROM VSD.VSD_VEHICLE_OWNER vo, VSD.VSD_VEHICLE v, VSD.VSD_VEH_PLATE_CAT_CODE vpcc where vo.VEHICLE_OWNER_ID = v.VEHICLE_OWNER_ID and v.VEH_PLATE_CAT_CODE_ID = vpcc.VEH_PLATE_CAT_CODE_ID and UPPER(vo.OWNER_NAME) like '%AL A%' and vpcc.COUNTRY_ID = 247 group by vo.VEHICLE_OWNER_ID , vo.OWNER_NAME, vo.OWNER_NAME_A, vo.TRAFFIC_FILE_NUMBER, vo.TRADE_LICENSE_NUMBER ) vinfoset left outer join ( SELECT oi1.TRADE_LICENSE_NUMBER, COUNT(DISTINCT(v1.VIOLATION_ID)) violationCount FROM VSD_VIOLATION v1, VSD_INSPECTION i1, VSD_VEHICLE_INFO vi1, VSD_OWNER_INFO oi1 WHERE v1.VIOLATION_ID = i1.VIOLATION_ID AND i1.VEHICLE_INFO_ID = vi1.VEHICLE_INFO_ID AND vi1.VEHICLE_INFO_ID = oi1.VEHICLE_INFO_ID GROUP BY oi1.TRADE_LICENSE_NUMBER ) oi1 on vinfoset.TRADE_LICENSE_NUMBER = oi1.TRADE_LICENSE_NUMBER */ Session session = (Session) dsContext.getInternalContext(); String defaultSchema = DataServiceImpl.DEFAULT_SCHEMA_NAME; StringBuffer query = new StringBuffer( "SELECT vinfoset.VEHICLE_OWNER_ID as \"vehicleOwnerId\", vinfoset.OWNER_NAME as \"operatorName\", vinfoset.OWNER_NAME_A as \"operatorNameA\", " + " vinfoset.TRAFFIC_FILE_NUMBER as \"trafficFileNumber\", vinfoset.TRADE_LICENSE_NUMBER as \"tradeLicenseNumber\" , " + " vinfoset.vehicleCount as \"totalNumOfVehicles\", decode(oi1.violationCount , NULL, 0 , oi1.violationCount) as \"totalNumOfViolations\" " + " FROM ( SELECT vo.VEHICLE_OWNER_ID , vo.OWNER_NAME , vo.OWNER_NAME_A, vo.TRAFFIC_FILE_NUMBER, vo.TRADE_LICENSE_NUMBER, count (v.VEHICLE_ID) vehicleCount " + " FROM " + defaultSchema + ".VSD_VEHICLE_OWNER vo, " + defaultSchema + ".VSD_VEHICLE v, " + defaultSchema + ".VSD_VEH_PLATE_CAT_CODE vpcc " + " WHERE vo.VEHICLE_OWNER_ID = v.VEHICLE_OWNER_ID and v.VEH_PLATE_CAT_CODE_ID = vpcc.VEH_PLATE_CAT_CODE_ID and vo.IS_DELETED = '" + IDataService.BOOL_FALSE + "' and v.IS_DELETED = '" + IDataService.BOOL_FALSE + "' and vpcc.IS_DELETED = '" + IDataService.BOOL_FALSE + "' "); if (fleetSearchCriteria.getOperatorName() != null && !fleetSearchCriteria.getOperatorName().equals("")) { query.append(" AND UPPER(vo.OWNER_NAME) like '%" + fleetSearchCriteria.getOperatorName().toUpperCase() + "%'"); } if (fleetSearchCriteria.getOperatorNameA() != null && !fleetSearchCriteria.getOperatorNameA().equals("")) { query.append(" AND vo.OWNER_NAME_A like '%" + fleetSearchCriteria.getOperatorNameA() + "%'"); } if (fleetSearchCriteria.getTrafficFileNumber() != null && !fleetSearchCriteria.getTrafficFileNumber().equals("")) { query.append(" AND vo.TRAFFIC_FILE_NUMBER = '" + fleetSearchCriteria.getTrafficFileNumber() + "'"); } if (fleetSearchCriteria.getTradeLicenseNumber() != null && !fleetSearchCriteria.getTradeLicenseNumber().equals("")) { query.append( " AND vo.TRADE_LICENSE_NUMBER = '" + fleetSearchCriteria.getTradeLicenseNumber() + "'"); } if (fleetSearchCriteria.getPlateNumber() != null && !fleetSearchCriteria.getPlateNumber().equals("")) { query.append(" AND v.VEHICLE_PLATE_NUMBER = '" + fleetSearchCriteria.getPlateNumber() + "'"); } if (fleetSearchCriteria.getPlateCodeId() != null) { query.append(" AND vpcc.VEH_PLATE_CAT_CODE_ID = " + fleetSearchCriteria.getPlateCodeId()); } if (fleetSearchCriteria.getPlateCategoryId() != null) { query.append(" AND vpcc.PAR_VEH_PLATE_CAT_CODE_ID = " + fleetSearchCriteria.getPlateCategoryId()); } if (fleetSearchCriteria.getPlateCategoryId() != null) { query.append(" AND vpcc.PAR_VEH_PLATE_CAT_CODE_ID = " + fleetSearchCriteria.getPlateCategoryId()); } if (fleetSearchCriteria.getEmirateId() != null) { query.append(" AND vpcc.COUNTRY_ID = " + fleetSearchCriteria.getEmirateId()); } query.append( " GROUP BY vo.VEHICLE_OWNER_ID, vo.OWNER_NAME, vo.OWNER_NAME_A, vo.TRAFFIC_FILE_NUMBER, vo.TRADE_LICENSE_NUMBER ) vinfoset left outer join " + " ( SELECT oi1.TRAFFIC_FILE_NUMBER, COUNT(DISTINCT(v1.VIOLATION_ID)) violationCount FROM " + defaultSchema + ".VSD_VIOLATION v1, " + defaultSchema + ".VSD_INSPECTION i1, " + defaultSchema + ".VSD_VEHICLE_INFO vi1, " + defaultSchema + ".VSD_OWNER_INFO oi1 " + " WHERE v1.VIOLATION_ID = i1.VIOLATION_ID AND i1.VEHICLE_INFO_ID = vi1.VEHICLE_INFO_ID AND vi1.VEHICLE_INFO_ID = oi1.VEHICLE_INFO_ID and v1.IS_DELETED = '" + IDataService.BOOL_FALSE + "' and i1.IS_DELETED = '" + IDataService.BOOL_FALSE + "' and oi1.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " GROUP BY oi1.TRAFFIC_FILE_NUMBER ) oi1 on vinfoset.TRAFFIC_FILE_NUMBER = oi1.TRAFFIC_FILE_NUMBER "); result = session.createSQLQuery(query.toString()).addScalar("vehicleOwnerId", Hibernate.LONG) .addScalar("operatorName").addScalar("operatorNameA").addScalar("trafficFileNumber") .addScalar("tradeLicenseNumber").addScalar("totalNumOfVehicles", Hibernate.LONG) .addScalar("totalNumOfViolations", Hibernate.LONG) .setResultTransformer(new AliasToBeanResultTransformer(FleetSearchResult.class)).list(); } catch (Exception ex) { logger.error("An error occured in searchFleetOperator()"); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("searchFleetOperator -- END"); return result; } /** * * Searches for the fleet operator based on the criteria provided * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param fleetSearchCriteria * @param paginationParam * @return List<FleetSearchResult> * @throws VSDDataAccessException */ public List<FleetSearchResult> searchFleetOperator(DataServiceContext dsContext, boolean retrieveArabicData, FleetSearchCriteria fleetSearchCriteria, PaginationParam paginationParam) throws VSDDataAccessException { logger.info("searchFleetOperator -- START"); List<FleetSearchResult> result = null; try { /* Select vinfoset.VEHICLE_OWNER_ID as "vehicleOwnerId", vinfoset.OWNER_NAME as "operatorName", vinfoset.OWNER_NAME_A as "operatorNameA", vinfoset.TRAFFIC_FILE_NUMBER as "trafficFileNumber", vinfoset.TRADE_LICENSE_NUMBER as "tradeLicenseNumber" , vinfoset.vehicleCount as "totalNumOfVehicles", decode(oi1.violationCount , NULL, 0 , oi1.violationCount) as "totalNumOfViolations" from ( SELECT vo.VEHICLE_OWNER_ID , vo.OWNER_NAME , vo.OWNER_NAME_A, vo.TRAFFIC_FILE_NUMBER, vo.TRADE_LICENSE_NUMBER, count (v.VEHICLE_ID) vehicleCount FROM VSD.VSD_VEHICLE_OWNER vo, VSD.VSD_VEHICLE v, VSD.VSD_VEH_PLATE_CAT_CODE vpcc where vo.VEHICLE_OWNER_ID = v.VEHICLE_OWNER_ID and v.VEH_PLATE_CAT_CODE_ID = vpcc.VEH_PLATE_CAT_CODE_ID and UPPER(vo.OWNER_NAME) like '%AL A%' and vpcc.COUNTRY_ID = 247 group by vo.VEHICLE_OWNER_ID , vo.OWNER_NAME, vo.OWNER_NAME_A, vo.TRAFFIC_FILE_NUMBER, vo.TRADE_LICENSE_NUMBER ) vinfoset left outer join ( SELECT oi1.TRADE_LICENSE_NUMBER, COUNT(DISTINCT(v1.VIOLATION_ID)) violationCount FROM VSD_VIOLATION v1, VSD_INSPECTION i1, VSD_VEHICLE_INFO vi1, VSD_OWNER_INFO oi1 WHERE v1.VIOLATION_ID = i1.VIOLATION_ID AND i1.VEHICLE_INFO_ID = vi1.VEHICLE_INFO_ID AND vi1.VEHICLE_INFO_ID = oi1.VEHICLE_INFO_ID GROUP BY oi1.TRADE_LICENSE_NUMBER ) oi1 on vinfoset.TRADE_LICENSE_NUMBER = oi1.TRADE_LICENSE_NUMBER */ Session session = (Session) dsContext.getInternalContext(); String defaultSchema = DataServiceImpl.DEFAULT_SCHEMA_NAME; StringBuffer query = new StringBuffer( "SELECT vinfoset.VEHICLE_OWNER_ID as \"vehicleOwnerId\", vinfoset.OWNER_NAME as \"operatorName\", vinfoset.OWNER_NAME_A as \"operatorNameA\", " + " vinfoset.TRAFFIC_FILE_NUMBER as \"trafficFileNumber\", vinfoset.TRADE_LICENSE_NUMBER as \"tradeLicenseNumber\" , " + " vinfoset.vehicleCount as \"totalNumOfVehicles\", decode(oi1.violationCount , NULL, 0 , oi1.violationCount) as \"totalNumOfViolations\" " + " FROM ( SELECT vo.VEHICLE_OWNER_ID , vo.OWNER_NAME , vo.OWNER_NAME_A, vo.TRAFFIC_FILE_NUMBER, vo.TRADE_LICENSE_NUMBER, count (v.VEHICLE_ID) vehicleCount " + " FROM " + defaultSchema + ".VSD_VEHICLE_OWNER vo, " + defaultSchema + ".VSD_VEHICLE v, " + defaultSchema + ".VSD_VEH_PLATE_CAT_CODE vpcc " + " WHERE vo.VEHICLE_OWNER_ID = v.VEHICLE_OWNER_ID and v.VEH_PLATE_CAT_CODE_ID = vpcc.VEH_PLATE_CAT_CODE_ID and vo.IS_DELETED = '" + IDataService.BOOL_FALSE + "' and v.IS_DELETED = '" + IDataService.BOOL_FALSE + "' and vpcc.IS_DELETED = '" + IDataService.BOOL_FALSE + "' "); if (fleetSearchCriteria.getOperatorName() != null && !fleetSearchCriteria.getOperatorName().equals("")) { query.append(" AND UPPER(vo.OWNER_NAME) like '%" + fleetSearchCriteria.getOperatorName().replaceAll("'", "''").toUpperCase() + "%'"); } if (fleetSearchCriteria.getOperatorNameA() != null && !fleetSearchCriteria.getOperatorNameA().equals("")) { query.append(" AND vo.OWNER_NAME_A like '%" + fleetSearchCriteria.getOperatorNameA().replaceAll("'", "''") + "%'"); } if (fleetSearchCriteria.getTrafficFileNumber() != null && !fleetSearchCriteria.getTrafficFileNumber().equals("")) { query.append(" AND vo.TRAFFIC_FILE_NUMBER = '" + fleetSearchCriteria.getTrafficFileNumber().replaceAll("'", "''") + "'"); } if (fleetSearchCriteria.getTradeLicenseNumber() != null && !fleetSearchCriteria.getTradeLicenseNumber().equals("")) { query.append(" AND vo.TRADE_LICENSE_NUMBER = '" + fleetSearchCriteria.getTradeLicenseNumber().replaceAll("'", "''") + "'"); } if (fleetSearchCriteria.getPlateNumber() != null && !fleetSearchCriteria.getPlateNumber().equals("")) { query.append(" AND v.VEHICLE_PLATE_NUMBER = '" + fleetSearchCriteria.getPlateNumber().replaceAll("'", "''") + "'"); } if (fleetSearchCriteria.getPlateCodeId() != null) { query.append(" AND vpcc.VEH_PLATE_CAT_CODE_ID = " + fleetSearchCriteria.getPlateCodeId()); } if (fleetSearchCriteria.getPlateCategoryId() != null) { query.append(" AND vpcc.PAR_VEH_PLATE_CAT_CODE_ID = " + fleetSearchCriteria.getPlateCategoryId()); } if (fleetSearchCriteria.getPlateCategoryId() != null) { query.append(" AND vpcc.PAR_VEH_PLATE_CAT_CODE_ID = " + fleetSearchCriteria.getPlateCategoryId()); } if (fleetSearchCriteria.getEmirateId() != null) { query.append(" AND vpcc.COUNTRY_ID = " + fleetSearchCriteria.getEmirateId()); } query.append( " GROUP BY vo.VEHICLE_OWNER_ID, vo.OWNER_NAME, vo.OWNER_NAME_A, vo.TRAFFIC_FILE_NUMBER, vo.TRADE_LICENSE_NUMBER ) vinfoset left outer join " + " ( SELECT oi1.TRAFFIC_FILE_NUMBER, COUNT(DISTINCT(v1.VIOLATION_ID)) violationCount FROM " + defaultSchema + ".VSD_VIOLATION v1, " + defaultSchema + ".VSD_INSPECTION i1, " + defaultSchema + ".VSD_VEHICLE_INFO vi1, " + defaultSchema + ".VSD_OWNER_INFO oi1 " + " WHERE v1.VIOLATION_ID = i1.VIOLATION_ID AND i1.VEHICLE_INFO_ID = vi1.VEHICLE_INFO_ID AND vi1.VEHICLE_INFO_ID = oi1.VEHICLE_INFO_ID and v1.IS_DELETED = '" + IDataService.BOOL_FALSE + "' and i1.IS_DELETED = '" + IDataService.BOOL_FALSE + "' and oi1.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " GROUP BY oi1.TRAFFIC_FILE_NUMBER ) oi1 on vinfoset.TRAFFIC_FILE_NUMBER = oi1.TRAFFIC_FILE_NUMBER "); if (paginationParam.getPageLocale().equalsIgnoreCase(Constant.LOCALE_ENGLISH)) { query.append( " ORDER BY LOWER(TRIM(vinfoset.OWNER_NAME)) asc, vinfoset.vehicleCount asc, decode(oi1.violationCount , NULL, 0 , oi1.violationCount)"); } else { query.append( " ORDER BY LOWER(vinfoset.OWNER_NAME_A) asc, vinfoset.vehicleCount asc , decode(oi1.violationCount , NULL, 0 , oi1.violationCount)"); } result = session.createSQLQuery(query.toString()).addScalar("vehicleOwnerId", Hibernate.LONG) .addScalar("operatorName").addScalar("operatorNameA").addScalar("trafficFileNumber") .addScalar("tradeLicenseNumber").addScalar("totalNumOfVehicles", Hibernate.LONG) .addScalar("totalNumOfViolations", Hibernate.LONG) .setResultTransformer(new AliasToBeanResultTransformer(FleetSearchResult.class)) .setFirstResult(paginationParam.getFirstResult().intValue()) .setMaxResults(paginationParam.getFetchedSize().intValue()).list(); } catch (Exception ex) { logger.error("An error occured in searchFleetOperator()"); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("searchFleetOperator -- END"); return result; } /** * * Searches for the fleet operator based on the criteria provided * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param fleetSearchCriteria * @return Long * @throws VSDDataAccessException */ public Long getCountForSearchFleetOperator(DataServiceContext dsContext, boolean retrieveArabicData, FleetSearchCriteria fleetSearchCriteria) throws VSDDataAccessException { logger.info("searchFleetOperator -- START"); Long resultSize = null; try { /* Select vinfoset.VEHICLE_OWNER_ID as "vehicleOwnerId", vinfoset.OWNER_NAME as "operatorName", vinfoset.OWNER_NAME_A as "operatorNameA", vinfoset.TRAFFIC_FILE_NUMBER as "trafficFileNumber", vinfoset.TRADE_LICENSE_NUMBER as "tradeLicenseNumber" , vinfoset.vehicleCount as "totalNumOfVehicles", decode(oi1.violationCount , NULL, 0 , oi1.violationCount) as "totalNumOfViolations" from ( SELECT vo.VEHICLE_OWNER_ID , vo.OWNER_NAME , vo.OWNER_NAME_A, vo.TRAFFIC_FILE_NUMBER, vo.TRADE_LICENSE_NUMBER, count (v.VEHICLE_ID) vehicleCount FROM VSD.VSD_VEHICLE_OWNER vo, VSD.VSD_VEHICLE v, VSD.VSD_VEH_PLATE_CAT_CODE vpcc where vo.VEHICLE_OWNER_ID = v.VEHICLE_OWNER_ID and v.VEH_PLATE_CAT_CODE_ID = vpcc.VEH_PLATE_CAT_CODE_ID and UPPER(vo.OWNER_NAME) like '%AL A%' and vpcc.COUNTRY_ID = 247 group by vo.VEHICLE_OWNER_ID , vo.OWNER_NAME, vo.OWNER_NAME_A, vo.TRAFFIC_FILE_NUMBER, vo.TRADE_LICENSE_NUMBER ) vinfoset left outer join ( SELECT oi1.TRADE_LICENSE_NUMBER, COUNT(DISTINCT(v1.VIOLATION_ID)) violationCount FROM VSD_VIOLATION v1, VSD_INSPECTION i1, VSD_VEHICLE_INFO vi1, VSD_OWNER_INFO oi1 WHERE v1.VIOLATION_ID = i1.VIOLATION_ID AND i1.VEHICLE_INFO_ID = vi1.VEHICLE_INFO_ID AND vi1.VEHICLE_INFO_ID = oi1.VEHICLE_INFO_ID GROUP BY oi1.TRADE_LICENSE_NUMBER ) oi1 on vinfoset.TRADE_LICENSE_NUMBER = oi1.TRADE_LICENSE_NUMBER */ Session session = (Session) dsContext.getInternalContext(); String defaultSchema = DataServiceImpl.DEFAULT_SCHEMA_NAME; StringBuffer query = new StringBuffer("SELECT count(vinfoset.VEHICLE_OWNER_ID) " + " FROM ( SELECT vo.VEHICLE_OWNER_ID , vo.OWNER_NAME , vo.OWNER_NAME_A, vo.TRAFFIC_FILE_NUMBER, vo.TRADE_LICENSE_NUMBER, count (v.VEHICLE_ID) vehicleCount " + " FROM " + defaultSchema + ".VSD_VEHICLE_OWNER vo, " + defaultSchema + ".VSD_VEHICLE v, " + defaultSchema + ".VSD_VEH_PLATE_CAT_CODE vpcc " + " WHERE vo.VEHICLE_OWNER_ID = v.VEHICLE_OWNER_ID and v.VEH_PLATE_CAT_CODE_ID = vpcc.VEH_PLATE_CAT_CODE_ID and vo.IS_DELETED = '" + IDataService.BOOL_FALSE + "' and v.IS_DELETED = '" + IDataService.BOOL_FALSE + "' and vpcc.IS_DELETED = '" + IDataService.BOOL_FALSE + "' "); if (fleetSearchCriteria.getOperatorName() != null && !fleetSearchCriteria.getOperatorName().equals("")) { query.append(" AND UPPER(vo.OWNER_NAME) like '%" + fleetSearchCriteria.getOperatorName().replaceAll("'", "''").toUpperCase() + "%'"); } if (fleetSearchCriteria.getOperatorNameA() != null && !fleetSearchCriteria.getOperatorNameA().equals("")) { query.append(" AND vo.OWNER_NAME_A like '%" + fleetSearchCriteria.getOperatorNameA().replaceAll("'", "''") + "%'"); } if (fleetSearchCriteria.getTrafficFileNumber() != null && !fleetSearchCriteria.getTrafficFileNumber().equals("")) { query.append(" AND vo.TRAFFIC_FILE_NUMBER = '" + fleetSearchCriteria.getTrafficFileNumber().replaceAll("'", "''") + "'"); } if (fleetSearchCriteria.getTradeLicenseNumber() != null && !fleetSearchCriteria.getTradeLicenseNumber().equals("")) { query.append(" AND vo.TRADE_LICENSE_NUMBER = '" + fleetSearchCriteria.getTradeLicenseNumber().replaceAll("'", "''") + "'"); } if (fleetSearchCriteria.getPlateNumber() != null && !fleetSearchCriteria.getPlateNumber().equals("")) { query.append(" AND v.VEHICLE_PLATE_NUMBER = '" + fleetSearchCriteria.getPlateNumber().replaceAll("'", "''") + "'"); } if (fleetSearchCriteria.getPlateCodeId() != null) { query.append(" AND vpcc.VEH_PLATE_CAT_CODE_ID = " + fleetSearchCriteria.getPlateCodeId()); } if (fleetSearchCriteria.getPlateCategoryId() != null) { query.append(" AND vpcc.PAR_VEH_PLATE_CAT_CODE_ID = " + fleetSearchCriteria.getPlateCategoryId()); } if (fleetSearchCriteria.getPlateCategoryId() != null) { query.append(" AND vpcc.PAR_VEH_PLATE_CAT_CODE_ID = " + fleetSearchCriteria.getPlateCategoryId()); } if (fleetSearchCriteria.getEmirateId() != null) { query.append(" AND vpcc.COUNTRY_ID = " + fleetSearchCriteria.getEmirateId()); } query.append( " GROUP BY vo.VEHICLE_OWNER_ID, vo.OWNER_NAME, vo.OWNER_NAME_A, vo.TRAFFIC_FILE_NUMBER, vo.TRADE_LICENSE_NUMBER ) vinfoset left outer join " + " ( SELECT oi1.TRAFFIC_FILE_NUMBER, COUNT(DISTINCT(v1.VIOLATION_ID)) violationCount FROM " + defaultSchema + ".VSD_VIOLATION v1, " + defaultSchema + ".VSD_INSPECTION i1, " + defaultSchema + ".VSD_VEHICLE_INFO vi1, " + defaultSchema + ".VSD_OWNER_INFO oi1 " + " WHERE v1.VIOLATION_ID = i1.VIOLATION_ID AND i1.VEHICLE_INFO_ID = vi1.VEHICLE_INFO_ID AND vi1.VEHICLE_INFO_ID = oi1.VEHICLE_INFO_ID and v1.IS_DELETED = '" + IDataService.BOOL_FALSE + "' and i1.IS_DELETED = '" + IDataService.BOOL_FALSE + "' and oi1.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " GROUP BY oi1.TRAFFIC_FILE_NUMBER ) oi1 on vinfoset.TRAFFIC_FILE_NUMBER = oi1.TRAFFIC_FILE_NUMBER "); BigDecimal count = (BigDecimal) session.createSQLQuery(query.toString()).uniqueResult(); resultSize = count.longValue(); } catch (Exception ex) { logger.error("An error occured in searchFleetOperator()"); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("searchFleetOperator -- END"); return resultSize; } /** * * Get Vehicle Owner and it contact details based on vehiclePlate. * * @author Tayyab * @param dsContext * @param retrieveArabicData * @param vehiclePlate * @return VsdVehicle * @throws VSDDataAccessException */ public VsdVehicleOwner getVehicleOwnerAndVehOwnConDetailsByVehiclePlate(DataServiceContext dsContext, boolean retrieveArabicData, VehiclePlate vehiclePlate) throws VSDDataAccessException { logger.info("getVehicleOwnerAndVehOwnConDetailsByVehiclePlate -- START"); VsdVehicleOwner vsdVehicleOwner = null; try { Session session = (Session) dsContext.getInternalContext(); vsdVehicleOwner = (VsdVehicleOwner) session.createCriteria(VsdVehicleOwner.class, "vo") .add(Restrictions.eq("vo.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vo.vsdVehicles", "v", Criteria.LEFT_JOIN, Restrictions.eq("v.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("v.vehiclePlateNumber", vehiclePlate.getPlateNumber()).ignoreCase()) .createCriteria("v.vsdVehPlateCatCode", "vpcc", Criteria.LEFT_JOIN, Restrictions.eq("vpcc.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("vpcc.categoryName", vehiclePlate.getPlateCode()).ignoreCase()) .createCriteria("vpcc.vsdVehPlateCatCode", "vpcc2", Criteria.LEFT_JOIN, Restrictions.eq("vpcc2.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("vpcc2.categoryName", vehiclePlate.getPlateCategory()).ignoreCase()) .createCriteria("vpcc2.vsdCountry", "c", Criteria.LEFT_JOIN, Restrictions.eq("c.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("c.countryCode", vehiclePlate.getPlateSource()).ignoreCase()) .createCriteria("vo.vsdVehOwnConDetails", "vocd", Criteria.LEFT_JOIN, Restrictions.eq("vocd.isDeleted", IDataService.BOOL_FALSE)) .uniqueResult(); } catch (Exception ex) { logger.error("An error occured in getVehicleOwnerAndVehOwnConDetailsByVehiclePlate()"); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getVehicleOwnerAndVehOwnConDetailsByVehiclePlate -- END"); return vsdVehicleOwner; } /** * * Get VehicleOwner By TradeLicenseNumber * populated DTO are vsdVehicleOwnActivities, vsdVehicleActivity, vsdRRProfilings * * @author Tayyab * @param dsContext * @param retrieveArabicData * @param tradeLicenseNumber * @return VsdVehicle * @throws VSDDataAccessException */ public VsdVehicleOwner getVehicleOwnerByTradeLicenseNumber(DataServiceContext dsContext, boolean retrieveArabicData, String tradeLicenseNumber) throws VSDDataAccessException { logger.info("getVehicleOwnerByTradeLicenseNumber -- START"); VsdVehicleOwner vsdVehicleOwner = null; try { Session session = (Session) dsContext.getInternalContext(); // DetachedCriteria innerCriteria = DetachedCriteria.forClass(VsdRRProfiling.class, "inner_rrp") // .setProjection(Projections.max("inner_rrp.createdTimestamp")) // .add(Restrictions.eq("inner_rrp.isDeleted", IDataService.BOOL_FALSE)) // .createCriteria("inner_rrp.vsdVehicleOwner", "inner_vo", Criteria.INNER_JOIN) // .add(Restrictions.eq("inner_vo.tradeLicenseNumber", tradeLicenseNumber)) // .add(Restrictions.eq("inner_vo.isDeleted", IDataService.BOOL_FALSE)); vsdVehicleOwner = (VsdVehicleOwner) session.createCriteria(VsdVehicleOwner.class, "vo") .add(Restrictions.eq("vo.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("vo.tradeLicenseNumber", tradeLicenseNumber).ignoreCase()) .createCriteria("vo.vsdVehicleOwnActivities", "voa", Criteria.LEFT_JOIN, Restrictions.eq("voa.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("voa.vsdVehicleActivity", "va", Criteria.LEFT_JOIN, Restrictions.eq("va.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vo.vsdRRProfilings", "rrp", Criteria.LEFT_JOIN, Restrictions.eq("rrp.isDeleted", IDataService.BOOL_FALSE)) // .add(Property.forName("rrp.createdTimestamp").eq(innerCriteria)) .uniqueResult(); } catch (Exception ex) { logger.error("An error occured in getVehicleOwnerByTradeLicenseNumber()"); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getVehicleOwnerByTradeLicenseNumber -- END"); return vsdVehicleOwner; } /** * * Get a vehicle owner along with their activities(VsdVehicleOwnActivity, VsdVehicleActivity), contact details(VsdVehOwnConDetails) and risk rating information(VsdRRProfiling, VsdRiskRating) by the vehicle owner id provided * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param vehicleOwnerId * @return VsdVehicleOwner * @throws VSDDataAccessException */ public VsdVehicleOwner getVehicleOwnerWithOwnerActivitiesContactDetailsAndRiskRatingById( DataServiceContext dsContext, boolean retrieveArabicData, Long vehicleOwnerId) throws VSDDataAccessException { logger.info("getVehicleOwnerWithOwnerActivitiesContactDetailsAndRiskRatingById -- START"); try { Session session = (Session) dsContext.getInternalContext(); VsdVehicleOwner vehicleOwner = (VsdVehicleOwner) session.createCriteria(VsdVehicleOwner.class, "vo") .add(Restrictions.eq("vo.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("vo.vehicleOwnerId", vehicleOwnerId)) .createCriteria("vo.vsdVehicleOwnActivities", "voa", Criteria.LEFT_JOIN, Restrictions.eq("voa.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("voa.vsdVehicleActivity", "va", Criteria.LEFT_JOIN, Restrictions.eq("va.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vo.vsdVehOwnConDetails", "vocd", Criteria.LEFT_JOIN, Restrictions.eq("vocd.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vo.vsdRRProfilings", "rrp", Criteria.LEFT_JOIN, Restrictions.eq("rrp.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("rrp.vsdRiskRating", "rr", Criteria.LEFT_JOIN, Restrictions.eq("rr.isDeleted", IDataService.BOOL_FALSE)) .uniqueResult(); logger.info("getVehicleOwnerWithOwnerActivitiesContactDetailsAndRiskRatingById -- END"); return vehicleOwner; } catch (Exception ex) { logger.error("An error occured in getVehicleOwnerWithOwnerActivitiesContactDetailsAndRiskRatingById()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } } /** * * gets VehicleOwner By ChassisNumber * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param chassisNumber * @return VsdVehicleOwner * @throws VSDDataAccessException */ public VsdVehicleOwner getVehicleOwnerByChassisNumber(DataServiceContext dsContext, boolean retrieveArabicData, String chassisNumber) throws VSDDataAccessException { logger.info("getVehicleOwnerByChassisNumber -- START"); try { Session session = (Session) dsContext.getInternalContext(); VsdVehicleOwner vehicleOwner = (VsdVehicleOwner) session.createCriteria(VsdVehicleOwner.class, "vo") .add(Restrictions.eq("vo.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vo.vsdVehicles", "v", Criteria.INNER_JOIN, Restrictions.eq("v.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("v.chassisNumber", chassisNumber).ignoreCase()).uniqueResult(); logger.info("getVehicleOwnerByChassisNumber -- END"); return vehicleOwner; } catch (Exception ex) { logger.error("An error occured in getVehicleOwnerByChassisNumber()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } } /** * * Gets a list of vehicle owners with their contact details(VsdVehOwnConDetails) whose trade license expires on given date. * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param tradeLicenseExpiry * @return List<VsdVehicleOwner> * @throws VSDDataAccessException */ public List<VsdVehicleOwner> getVehicleOwnersByTradeLicenseExpiryDate(DataServiceContext dsContext, boolean retrieveArabicData, Date tradeLicenseExpiry) throws VSDDataAccessException { logger.info("getVehicleOwnersByTradeLicenseExpiryDate -- START"); List<VsdVehicleOwner> vehicleOwners = null; try { Calendar startDate = Calendar.getInstance(); startDate.setTime(tradeLicenseExpiry); startDate.set(Calendar.HOUR_OF_DAY, 0); startDate.set(Calendar.MINUTE, 0); startDate.set(Calendar.SECOND, 0); startDate.set(Calendar.MILLISECOND, 0); Calendar endDate = Calendar.getInstance(); endDate.setTime(tradeLicenseExpiry); endDate.set(Calendar.HOUR_OF_DAY, 23); endDate.set(Calendar.MINUTE, 59); endDate.set(Calendar.SECOND, 59); endDate.set(Calendar.MILLISECOND, 999); Session session = (Session) dsContext.getInternalContext(); vehicleOwners = session.createCriteria(VsdVehicleOwner.class, "vo") .add(Restrictions.eq("vo.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.between("vo.tradeLicenseExpiry", startDate.getTime(), endDate.getTime())) .createCriteria("vo.vsdVehOwnConDetails", "cd", Criteria.LEFT_JOIN, Restrictions.eq("cd.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vo.vsdVehicles", "v", Criteria.LEFT_JOIN, Restrictions.eq("v.isDeleted", IDataService.BOOL_FALSE)) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list(); logger.info("getVehicleOwnersByTradeLicenseExpiryDate -- END"); return vehicleOwners; } catch (Exception ex) { logger.error("An error occured in getVehicleOwnersByTradeLicenseExpiryDate()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } } /** * * Gets a list of vehicle owners by risk rating type code and profiling date * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param riskRatingTypeCode * @param profilingDate * @return List<VsdVehicleOwner> * @throws VSDDataAccessException */ public List<VsdVehicleOwner> getVehicleOwnerByRiskRatingTypeCode(DataServiceContext dsContext, boolean retrieveArabicData, String riskRatingTypeCode, Date profilingDate) throws VSDDataAccessException { logger.info("getVehicleOwnerByRiskRatingTypeCode -- START"); List<VsdVehicleOwner> vehicleOwners = null; try { Calendar startDate = Calendar.getInstance(); startDate.setTime(profilingDate); startDate.set(Calendar.HOUR_OF_DAY, 0); startDate.set(Calendar.MINUTE, 0); startDate.set(Calendar.SECOND, 0); startDate.set(Calendar.MILLISECOND, 0); Calendar endDate = Calendar.getInstance(); endDate.setTime(profilingDate); endDate.set(Calendar.HOUR_OF_DAY, 23); endDate.set(Calendar.MINUTE, 59); endDate.set(Calendar.SECOND, 59); endDate.set(Calendar.MILLISECOND, 999); Session session = (Session) dsContext.getInternalContext(); vehicleOwners = session.createCriteria(VsdVehicleOwner.class, "vo") .add(Restrictions.eq("vo.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vo.vsdRRProfilings", "rrp", Criteria.LEFT_JOIN, Restrictions.eq("rrp.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.between("rrp.createdTimestamp", startDate.getTime(), endDate.getTime())) .createCriteria("rrp.vsdRiskRating", "rr", Criteria.LEFT_JOIN, Restrictions.eq("rr.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("rr.vsdRiskRatingType", "rrt", Criteria.LEFT_JOIN, Restrictions.eq("rrt.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("rrt.ratingTypeCode", riskRatingTypeCode)) .createCriteria("vo.vsdVehOwnConDetails", "vocd", Criteria.LEFT_JOIN, Restrictions.eq("vocd.isDeleted", IDataService.BOOL_FALSE)) .addOrder(Order.desc("rrp.createdTimestamp")) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list(); logger.info("getVehicleOwnerByRiskRatingTypeCode -- END"); return vehicleOwners; } catch (Exception ex) { logger.error("An error occured in getVehicleOwnerByRiskRatingTypeCode()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } } /** * * Gets a list of vehicle owners by risk rating profiling date * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param profilingDate * @return List<VsdVehicleOwner> * @throws VSDDataAccessException */ public List<VsdVehicleOwner> getVehiclesOwnerByProfilingDate(DataServiceContext dsContext, boolean retrieveArabicData, Date profilingDate) throws VSDDataAccessException { logger.info("getVehiclesOwnerByProfilingDate -- START"); List<VsdVehicleOwner> vehicleOwners = null; try { Calendar startDate = Calendar.getInstance(); startDate.setTime(profilingDate); startDate.set(Calendar.HOUR_OF_DAY, 0); startDate.set(Calendar.MINUTE, 0); startDate.set(Calendar.SECOND, 0); startDate.set(Calendar.MILLISECOND, 0); Calendar endDate = Calendar.getInstance(); endDate.setTime(profilingDate); endDate.set(Calendar.HOUR_OF_DAY, 23); endDate.set(Calendar.MINUTE, 59); endDate.set(Calendar.SECOND, 59); endDate.set(Calendar.MILLISECOND, 999); Session session = (Session) dsContext.getInternalContext(); vehicleOwners = session.createCriteria(VsdVehicleOwner.class, "vo") .add(Restrictions.eq("vo.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vo.vsdRRProfilings", "rrp", Criteria.LEFT_JOIN, Restrictions.eq("rrp.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.between("rrp.createdTimestamp", startDate.getTime(), endDate.getTime())) .createCriteria("rrp.vsdRiskRating", "rr", Criteria.LEFT_JOIN, Restrictions.eq("rr.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("rr.vsdRiskRatingType", "rrt", Criteria.LEFT_JOIN, Restrictions.eq("rrt.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vo.vsdVehOwnConDetails", "vocd", Criteria.LEFT_JOIN, Restrictions.eq("vocd.isDeleted", IDataService.BOOL_FALSE)) .addOrder(Order.desc("rrp.createdTimestamp")) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list(); logger.info("getVehiclesOwnerByProfilingDate -- END"); return vehicleOwners; } catch (Exception ex) { logger.error("An error occured in getVehiclesOwnerByProfilingDate()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } } /** * * Get VehicleOwner and Risk Rating by vehicleOwnerId * populated DTO are vsdVehicleOwnActivities, vsdVehicleActivity, vsdRRProfilings * * @author Tayyab * @param dsContext * @param retrieveArabicData * @param tradeLicenseNumber * @return VsdVehicle * @throws VSDDataAccessException */ public VsdVehicleOwner getVehicleOwnerAndRRProfilingByVehicleOwnerId(DataServiceContext dsContext, boolean retrieveArabicData, Long vehicleOwnerId, Long rrProfilingId) throws VSDDataAccessException { logger.info("getVehicleOwnerAndRRProfilingByVehicleOwnerId -- START"); VsdVehicleOwner vsdVehicleOwner = null; try { Session session = (Session) dsContext.getInternalContext(); vsdVehicleOwner = (VsdVehicleOwner) session.createCriteria(VsdVehicleOwner.class, "vo") .add(Restrictions.eq("vo.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("vo.vehicleOwnerId", vehicleOwnerId)) .createCriteria("vo.vsdVehicleOwnActivities", "voa", Criteria.LEFT_JOIN, Restrictions.eq("voa.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("voa.vsdVehicleActivity", "va", Criteria.LEFT_JOIN, Restrictions.eq("va.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vo.vsdVehOwnConDetails", "vocd", Criteria.LEFT_JOIN, Restrictions.eq("vocd.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vo.vsdRRProfilings", "rrp", Criteria.LEFT_JOIN, Restrictions.eq("rrp.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("rrp.RRProfilingId", rrProfilingId)) .createCriteria("rrp.vsdRiskRating", "rr", Criteria.LEFT_JOIN, Restrictions.eq("rr.isDeleted", IDataService.BOOL_FALSE)) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).uniqueResult(); } catch (Exception ex) { logger.error("An error occured in getVehicleOwnerAndRRProfilingByVehicleOwnerId()"); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getVehicleOwnerAndRRProfilingByVehicleOwnerId -- END"); return vsdVehicleOwner; } }