Java tutorial
/**<pre> *========================================================================== * * Copyright: (C) IBM Corporation 2010 -- IBM Internal Use Only * *========================================================================== * * FILE: ViolationDataServiceImpl.java * CREATOR: Eldon Barrows * DEPT: GBS PAK * DATE: 01/05/2011 * *-PURPOSE----------------------------------------------------------------- * This is the implementation of IViolationDataService * *------------------------------------------------------------------------- * * *-CHANGE LOG-------------------------------------------------------------- * 01/05/2011 Eldon Initial coding. *========================================================================== * </pre> */ package com.rta.vsd.data.service.impl; import java.math.BigDecimal; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Calendar; import java.util.Date; import java.util.HashSet; import java.util.Iterator; import java.util.LinkedHashSet; import java.util.List; import java.util.Set; import org.apache.log4j.Logger; import org.hibernate.Criteria; import org.hibernate.Hibernate; import org.hibernate.Query; import org.hibernate.Session; import org.hibernate.criterion.Order; import org.hibernate.criterion.ProjectionList; import org.hibernate.criterion.Projections; import org.hibernate.criterion.Property; 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.IViolationDataService; import com.rta.vsd.dto.VsdViolation; import com.rta.vsd.dto.custom.CustomTestStatusDTO; import com.rta.vsd.dto.custom.DefectFineAmount; import com.rta.vsd.dto.custom.PaginationParam; import com.rta.vsd.dto.custom.VehiclePlate; import com.rta.vsd.dto.custom.ViolationDefectFine; import com.rta.vsd.dto.custom.ViolationSearchCriteria; import com.rta.vsd.dto.custom.ViolationSearchResult; import com.rta.vsd.exception.VSDException; import com.rta.vsd.exception.data.VSDDataAccessException; import com.rta.vsd.utility.Constant; import com.rta.vsd.utility.SystemProperties; import com.rta.vsd.utility.VSDLogger; /** * This is the implementation of IViolationDataService * * @author Eldon Barrows * */ public class ViolationDataServiceImpl extends DataServiceImpl implements IViolationDataService { private static Logger logger = VSDLogger.init(ViolationDataServiceImpl.class.getName()); public static enum SORT_COLUMN { VIOLATION_ID { public String toString() { return "VIOLATION_ID"; } }, VIOLATION_TICKET_CODE { public String toString() { return "VIOLATION_TICKET_CODE"; } }, REPORTED_DATE { public String toString() { return "REPORTED_DATE"; } }, SEVERITY_LEVEL_NAME { public String toString() { return "SEVERITY_LEVEL_NAME"; } }, SEVERITY_LEVEL_NAME_A { public String toString() { return "SEVERITY_LEVEL_NAME_A"; } }, STATUS_NAME { public String toString() { return "STATUS_NAME"; } }, STATUS_NAME_A { public String toString() { return "STATUS_NAME_A"; } }, CHANNEL_NAME { public String toString() { return "CHANNEL_NAME"; } }, CHANNEL_NAME_A { public String toString() { return "CHANNEL_NAME_A"; } }, VEHICLE_PLATE_CATEGORY { public String toString() { return "VEHICLE_PLATE_CATEGORY"; } }, VEHICLE_PLATE_CODE { public String toString() { return "VEHICLE_PLATE_CODE"; } }, VEHICLE_PLATE_NUMBER { public String toString() { return "VEHICLE_PLATE_NUMBER"; } }, VEHICLE_PLATE_SOURCE { public String toString() { return "VEHICLE_PLATE_SOURCE"; } }, OWNER_NAME { public String toString() { return "OWNER_NAME"; } }, OWNER_NAME_A { public String toString() { return "OWNER_NAME_A"; } }, TRAFFIC_FILE_NUMBER { public String toString() { return "TRAFFIC_FILE_NUMBER"; } } } /** * * Gets a list of all violations * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @return List<VsdViolation> * @throws VSDException */ public List<VsdViolation> getViolations(DataServiceContext dsContext, boolean retrieveArabicData) throws VSDDataAccessException { logger.info("getViolationList -- START"); List<VsdViolation> violationList = null; try { Session session = (Session) dsContext.getInternalContext(); violationList = (List<VsdViolation>) session.createCriteria(VsdViolation.class) .add(Restrictions.eq("isDeleted", IDataService.BOOL_FALSE)).list(); } catch (Exception ex) { logger.error("An error occured in getViolationList()"); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getViolationList -- END"); return violationList; } /** * * Gets a list of violations with the specified size * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param firstResult * @param FetchSize * @return List<VsdViolation> * @throws VSDDataAccessException */ public List<VsdViolation> getViolations(DataServiceContext dsContext, boolean retrieveArabicData, int firstResult, int maxResults) throws VSDDataAccessException { logger.info("getViolationList -- START"); List<VsdViolation> violationList = null; try { Session session = (Session) dsContext.getInternalContext(); // violationList = (List<VsdViolation>) session.createCriteria(VsdViolation.class) // .add(Restrictions.eq("isDeleted", IDataService.BOOL_FALSE)) // .setFirstResult(firstResult) // .setMaxResults(maxResults) // .list(); violationList = (List<VsdViolation>) session.createCriteria(VsdViolation.class) .add(Restrictions.eq("isDeleted", IDataService.BOOL_FALSE)).setFirstResult(firstResult) .setMaxResults(maxResults).list(); } catch (Exception ex) { logger.error("An error occured in getViolationList()"); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getViolationList -- END"); return violationList; } /** * * Get the number of violations in the table * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @return Long * @throws VSDDataAccessException */ public Long getViolationsSize(DataServiceContext dsContext, boolean retrieveArabicData) throws VSDDataAccessException { logger.info("getViolationsSize -- START"); try { Session session = (Session) dsContext.getInternalContext(); Long totalResult = (Long) session.createCriteria(VsdViolation.class) .add(Restrictions.eq("isDeleted", IDataService.BOOL_FALSE)) .setProjection(Projections.rowCount()).uniqueResult(); return totalResult; } catch (Exception ex) { logger.error("An error occured in getViolationList()"); throw new VSDDataAccessException(ex.getMessage(), ex); } } /** * * Gets a violation by the id provided * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param violationId * @return VsdViolation * @throws VSDDataAccessException */ public VsdViolation getViolationByViolationId(DataServiceContext dsContext, boolean retrieveArabicData, Long violationId) throws VSDDataAccessException { logger.info("getViolationByViolationId -- START"); VsdViolation violation = null; try { Session session = (Session) dsContext.getInternalContext(); violation = (VsdViolation) session.createCriteria(VsdViolation.class) .add(Restrictions.eq("isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("violationId", violationId)).uniqueResult(); } catch (Exception ex) { logger.error("An error occured in getViolationByViolationId()"); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getViolationByViolationId -- END"); return violation; } /** * * Gets a violation by the provided violation ticket code * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param violationTicketCode * @return VsdViolation * @throws VSDDataAccessException */ public VsdViolation getViolationByViolationTicketCode(DataServiceContext dsContext, boolean retrieveArabicData, String violationTicketCode) throws VSDDataAccessException { logger.info("getViolationByViolationTicketCode -- START"); VsdViolation violation = null; try { Session session = (Session) dsContext.getInternalContext(); violation = (VsdViolation) session.createCriteria(VsdViolation.class) .add(Restrictions.eq("isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("violationTicketCode", violationTicketCode)).uniqueResult(); } catch (Exception ex) { logger.error("An error occured in getViolationByViolationTicketCode()"); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getViolationByViolationTicketCode -- END"); return violation; } /** * * Gets a list of all the violations based on the plate details provided * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param vehiclePlateDetails * @return List<VsdViolation> * @throws VSDDataAccessException */ public List<VsdViolation> getViolationsByPlateDetails(DataServiceContext dsContext, boolean retrieveArabicData, VehiclePlate vehiclePlateDetails) throws VSDDataAccessException { logger.info("getViolationsByPlateDetails -- START"); List<VsdViolation> violations = null; try { Session session = (Session) dsContext.getInternalContext(); // ProjectionList projectionList = Projections.projectionList(); // projectionList.add(Projections.property("violationId"),"violationId"); // projectionList.add(Projections.property("vsdViolationStatus"),"vsdViolationStatus"); // projectionList.add(Projections.property("vsdSeverityLevel"),"vsl.vsdSeverityLevelId"); // projectionList.add(Projections.property("vsdSeverityLevel"),"vsl.vsdSeverityLevel"); // projectionList.add(Projections.property("violationTicketCode"),"violationTicketCode"); // projectionList.add(Projections.property("offlineViolTicketCode"),"offlineViolTicketCode"); // projectionList.add(Projections.property("testAttempts"),"testAttempts"); // projectionList.add(Projections.property("testTypeName"),"testTypeName"); // projectionList.add(Projections.property("testTypeNameA"),"testTypeNameA"); // projectionList.add(Projections.property("testTypeCode"),"testTypeCode"); // projectionList.add(Projections.property("dueDate"),"dueDate"); // projectionList.add(Projections.property("reportedDate"),"reportedDate"); // projectionList.add(Projections.property("vehicleSuspensionDate"),"vehicleSuspensionDate"); // projectionList.add(Projections.property("ownerSuspensionDate"),"ownerSuspensionDate"); // projectionList.add(Projections.property("vehRegBlockDate"),"vehRegBlockDate"); // projectionList.add(Projections.property("comments"),"comments"); // projectionList.add(Projections.property("commentsA"),"commentsA"); // projectionList.add(Projections.property("fineAmount"),"fineAmount"); // projectionList.add(Projections.property("penaltyAmount"),"penaltyAmount"); // projectionList.add(Projections.property("isOverriddin"),"isOverriddin"); // projectionList.add(Projections.property("overriddenByUserId"),"overriddenByUserId"); // projectionList.add(Projections.property("overriddenReason"),"overriddenReason"); // projectionList.add(Projections.property("overriddenReasonA"),"overriddenReasonA"); // projectionList.add(Projections.property("isDisabled"),"isDisabled"); // projectionList.add(Projections.property("isDeleted"),"isDeleted"); // projectionList.add(Projections.property("createdBy"),"createdBy"); // projectionList.add(Projections.property("createdTimestamp"),"createdTimestamp"); // projectionList.add(Projections.property("lastUpdatedBy"),"lastUpdatedBy"); // projectionList.add(Projections.property("upatedTimestamp"),"upatedTimestamp"); violations = session.createCriteria(VsdViolation.class, "violaton") // .setProjection(projectionList) .add(Restrictions.eq("violaton.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vsdInspections", "inspections", Criteria.LEFT_JOIN, Restrictions.eq("inspections.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vsdVehicleInfo", "vehicleInfo", Criteria.LEFT_JOIN, Restrictions.eq("vehicleInfo.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("vehicleInfo.vehiclePlateCategory", vehiclePlateDetails.getPlateCategory()) .ignoreCase()) .add(Restrictions.eq("vehicleInfo.vehiclePlateCode", vehiclePlateDetails.getPlateCode()) .ignoreCase()) .add(Restrictions.eq("vehicleInfo.vehiclePlateSource", vehiclePlateDetails.getPlateSource()) .ignoreCase()) .add(Restrictions.eq("vehicleInfo.vehiclePlateNumber", vehiclePlateDetails.getPlateNumber()) .ignoreCase()) .addOrder(Order.desc("violaton.reportedDate")) .createCriteria("violaton.vsdSeverityLevel", "vsl", Criteria.LEFT_JOIN, Restrictions.eq("vsl.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violaton.vsdViolationStatus", "vs", Criteria.LEFT_JOIN, Restrictions.eq("vs.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vehicleInfo.vsdDriverInfos", "di", Criteria.LEFT_JOIN, Restrictions.eq("di.isDeleted", IDataService.BOOL_FALSE)) // .setResultTransformer(new AliasToBeanResultTransformer(VsdViolation.class)) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list(); } catch (Exception ex) { logger.error("An error occured in getViolationsByPlateDetails"); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getViolationsByPlateDetails -- END"); return violations; } /** * * Gets a list of all the violations based on the plate details provided * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param vehiclePlateDetails * @param paginationValues * @return List<VsdViolation> * @throws VSDDataAccessException */ public List<VsdViolation> getViolationsByPlateDetails(final DataServiceContext dsContext, boolean retrieveArabicData, VehiclePlate vehiclePlateDetails, PaginationParam paginationValues) throws VSDDataAccessException { logger.info("getViolationsByPlateDetails -- START"); List<VsdViolation> violations = null; try { Session session = (Session) dsContext.getInternalContext(); ProjectionList projectionList = Projections.projectionList(); projectionList.add(Projections.property("violaton.violationId"), "violationId"); projectionList.add(Projections.property("violaton.reportedDate"), "reportedDate"); Criteria crit = session.createCriteria(VsdViolation.class, "violaton") .add(Restrictions.eq("violaton.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vsdInspections", "inspections", Criteria.LEFT_JOIN, Restrictions.eq("inspections.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vsdVehicleInfo", "vehicleInfo", Criteria.LEFT_JOIN, Restrictions.eq("vehicleInfo.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("vehicleInfo.vehiclePlateCategory", vehiclePlateDetails.getPlateCategory()) .ignoreCase()) .add(Restrictions.eq("vehicleInfo.vehiclePlateCode", vehiclePlateDetails.getPlateCode()) .ignoreCase()) .add(Restrictions.eq("vehicleInfo.vehiclePlateSource", vehiclePlateDetails.getPlateSource()) .ignoreCase()) .add(Restrictions.eq("vehicleInfo.vehiclePlateNumber", vehiclePlateDetails.getPlateNumber()) .ignoreCase()) .createCriteria("violaton.vsdSeverityLevel", "vsl", Criteria.LEFT_JOIN, Restrictions.eq("vsl.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violaton.vsdViolationStatus", "vs", Criteria.LEFT_JOIN, Restrictions.eq("vs.isDeleted", IDataService.BOOL_FALSE)) .addOrder(Order.desc("violaton.reportedDate")) .setProjection(Projections.distinct(projectionList)); crit.setResultTransformer(new AliasToBeanResultTransformer(VsdViolation.class)); if (paginationValues.getFirstResult() != null && paginationValues.getFirstResult().longValue() != -1) { crit.setFirstResult(paginationValues.getFirstResult().intValue()); } if (paginationValues.getFetchedSize() != null && paginationValues.getFetchedSize().longValue() != -1) { crit.setMaxResults(paginationValues.getFetchedSize().intValue()); } List list = crit.list(); logger.debug("list.size() : " + list.size()); Set resultSet = new HashSet(list); logger.debug("resultSet.size() : " + resultSet.size()); if (resultSet.size() == 0) return new ArrayList(); Iterator iterator = resultSet.iterator(); ArrayList innerQueryList = new ArrayList<Long>(); while (iterator.hasNext()) { VsdViolation violation = (VsdViolation) iterator.next(); innerQueryList.add(violation.getViolationId()); } Criteria main = session.createCriteria(VsdViolation.class, "violaton") .add(Property.forName("violaton.violationId").in(innerQueryList)) .add(Restrictions.eq("violaton.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vsdInspections", "inspections", Criteria.LEFT_JOIN, Restrictions.eq("inspections.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vsdVehicleInfo", "vehicleInfo", Criteria.LEFT_JOIN, Restrictions.eq("vehicleInfo.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("vehicleInfo.vehiclePlateCategory", vehiclePlateDetails.getPlateCategory())) .add(Restrictions.eq("vehicleInfo.vehiclePlateCode", vehiclePlateDetails.getPlateCode())) .add(Restrictions.eq("vehicleInfo.vehiclePlateSource", vehiclePlateDetails.getPlateSource())) .add(Restrictions.eq("vehicleInfo.vehiclePlateNumber", vehiclePlateDetails.getPlateNumber())) .createCriteria("violaton.vsdSeverityLevel", "vsl", Criteria.LEFT_JOIN, Restrictions.eq("vsl.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violaton.vsdViolationStatus", "vs", Criteria.LEFT_JOIN, Restrictions.eq("vs.isDeleted", IDataService.BOOL_FALSE)) .addOrder(Order.desc("violaton.reportedDate")); violations = main.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list(); logger.info("getViolationsByPlateDetails -- END"); return violations; } catch (Exception ex) { logger.error("An error occured in getViolationsByPlateDetails()"); throw new VSDDataAccessException(ex.getMessage(), ex); } } /** * * Gets the count for getViolationsByPlateDetails * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param vehiclePlateDetails * @return Long * @throws VSDDataAccessException */ public Long getCountForGetViolationsByPlateDetails(final DataServiceContext dsContext, boolean retrieveArabicData, VehiclePlate vehiclePlateDetails) throws VSDDataAccessException { logger.info("getCountForGetViolationsByPlateDetails -- START"); try { Session session = (Session) dsContext.getInternalContext(); Criteria crit = session.createCriteria(VsdViolation.class, "violaton") .add(Restrictions.eq("violaton.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vsdInspections", "inspections", Criteria.LEFT_JOIN, Restrictions.eq("inspections.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vsdVehicleInfo", "vehicleInfo", Criteria.LEFT_JOIN, Restrictions.eq("vehicleInfo.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("vehicleInfo.vehiclePlateCategory", vehiclePlateDetails.getPlateCategory()) .ignoreCase()) .add(Restrictions.eq("vehicleInfo.vehiclePlateCode", vehiclePlateDetails.getPlateCode()) .ignoreCase()) .add(Restrictions.eq("vehicleInfo.vehiclePlateSource", vehiclePlateDetails.getPlateSource()) .ignoreCase()) .add(Restrictions.eq("vehicleInfo.vehiclePlateNumber", vehiclePlateDetails.getPlateNumber()) .ignoreCase()) .addOrder(Order.desc("violaton.violationId")) .setProjection(Projections.countDistinct("violaton.violationId")); Long count = (Long) crit.uniqueResult(); logger.info("getCountForGetViolationsByPlateDetails -- END"); return count; } catch (Exception ex) { logger.error("An error occured in getCountForGetViolationsByPlateDetails()"); throw new VSDDataAccessException(ex.getMessage(), ex); } } // public List<VsdViolation> getOpenViolation(DataServiceContext dsContext, boolean retrieveArabicData, VehiclePlate vehiclePlateDetails) throws VSDDataAccessException { // logger.info("getOpenViolation -- START"); // List<VsdViolation> violations = null; // // try { // Session session = (Session) dsContext.getInternalContext(); // violations = session.createCriteria(VsdViolation.class, "violaton") // .add(Restrictions.eq("violaton.isDeleted", IDataService.BOOL_FALSE)) // .createCriteria("vsdInspections","inspections",Criteria.LEFT_JOIN).add(Restrictions.eq("inspections.isDeleted", IDataService.BOOL_FALSE)) // .createCriteria("vsdVehicleInfo","vehicleInfo",Criteria.LEFT_JOIN).add(Restrictions.eq("vehicleInfo.isDeleted", IDataService.BOOL_FALSE)) // .add(Restrictions.eq("vehicleInfo.vehiclePlateCategory", vehiclePlateDetails.getPlateCategory())) // .add(Restrictions.eq("vehicleInfo.vehiclePlateCode", vehiclePlateDetails.getPlateCode())) // .add(Restrictions.eq("vehicleInfo.vehiclePlateSource", vehiclePlateDetails.getPlateSouce())) // .add(Restrictions.eq("vehicleInfo.vehiclePlateNumber", vehiclePlateDetails.getPlateNumber())) // .createCriteria("vsdViolationStatus",Criteria.LEFT_JOIN) // .add(Restrictions.eq("", )) // .list(); // } // catch(Exception ex) { // logger.error("An error occured in getOpenViolation()"); // throw new VSDDataAccessException(ex.getMessage()); // } // logger.info("getOpenViolation -- END"); // return violations; // } /** * * Saves a violation * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param vsdViolation * @return VsdViolation * @throws VSDDataAccessException */ public VsdViolation saveViolation(DataServiceContext dsContext, boolean retrieveArabicData, VsdViolation vsdViolation) throws VSDDataAccessException { logger.info("saveViolation -- START"); try { Session session = (Session) dsContext.getInternalContext(); session.save(vsdViolation); } catch (Exception ex) { logger.error("An error occured in saveViolation()"); throw new VSDDataAccessException(ex.getMessage()); } logger.info("saveViolation -- END"); return vsdViolation; } /** * * Gets a list of violations by the status id and plate details provided * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param violationStatusId * @param vehiclePlateDetails * @return List<VsdViolation> * @throws VSDDataAccessException */ public List<VsdViolation> getViolationsByStatusIdAndVehiclePlateDetails(DataServiceContext dsContext, boolean retrieveArabicData, Long violationStatusId, VehiclePlate vehiclePlateDetails) throws VSDDataAccessException { logger.info("getViolationsByStatusIdAndVehiclePlateDetails -- START"); List<VsdViolation> violations = null; try { Session session = (Session) dsContext.getInternalContext(); violations = session.createCriteria(VsdViolation.class, "violations") .add(Restrictions.eq("violations.vsdViolationStatus.violationStatusId", violationStatusId)) .add(Restrictions.eq("violations.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vsdInspections", "inspections", Criteria.LEFT_JOIN, Restrictions.eq("inspections.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vsdVehicleInfo", "vehicleInfo", Criteria.LEFT_JOIN, Restrictions.eq("vehicleInfo.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("vehicleInfo.vehiclePlateCategory", vehiclePlateDetails.getPlateCategory()) .ignoreCase()) .add(Restrictions.eq("vehicleInfo.vehiclePlateCode", vehiclePlateDetails.getPlateCode()) .ignoreCase()) .add(Restrictions.eq("vehicleInfo.vehiclePlateSource", vehiclePlateDetails.getPlateSource()) .ignoreCase()) .add(Restrictions.eq("vehicleInfo.vehiclePlateNumber", vehiclePlateDetails.getPlateNumber()) .ignoreCase()) .createCriteria("violations.vsdChannelDefects", "channelDefects", Criteria.LEFT_JOIN, Restrictions.eq("channelDefects.isDeleted", IDataService.BOOL_FALSE)) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list(); } catch (Exception ex) { logger.error("An error occured in getViolationsByStatusIdAndVehiclePlateDetails()"); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getViolationsByStatusIdAndVehiclePlateDetails -- END"); return violations; } /** * * Gets a list of violations by the statusIds and plate details provided * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param List<violationStatusId> * @param vehiclePlateDetails * @return List<VsdViolation> * @throws VSDDataAccessException */ public List<VsdViolation> getViolationsByStatusIdAndVehiclePlateDetails(DataServiceContext dsContext, boolean retrieveArabicData, List<Long> violationStatusIds, VehiclePlate vehiclePlateDetails) throws VSDDataAccessException { logger.info("getViolationsByStatusIdAndVehiclePlateDetails -- START"); List<VsdViolation> violations = null; try { Session session = (Session) dsContext.getInternalContext(); violations = session.createCriteria(VsdViolation.class, "violations") .add(Restrictions.in("violations.vsdViolationStatus.violationStatusId", violationStatusIds)) .add(Restrictions.eq("violations.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vsdInspections", "inspections", Criteria.LEFT_JOIN, Restrictions.eq("inspections.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vsdVehicleInfo", "vehicleInfo", Criteria.LEFT_JOIN, Restrictions.eq("vehicleInfo.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("vehicleInfo.vehiclePlateCategory", vehiclePlateDetails.getPlateCategory()) .ignoreCase()) .add(Restrictions.eq("vehicleInfo.vehiclePlateCode", vehiclePlateDetails.getPlateCode()) .ignoreCase()) .add(Restrictions.eq("vehicleInfo.vehiclePlateSource", vehiclePlateDetails.getPlateSource()) .ignoreCase()) .add(Restrictions.eq("vehicleInfo.vehiclePlateNumber", vehiclePlateDetails.getPlateNumber()) .ignoreCase()) .addOrder(Order.desc("violations.reportedDate")) .createCriteria("violations.vsdSeverityLevel", "sl", Criteria.LEFT_JOIN) .add(Restrictions.eq("sl.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violations.vsdChannelDefects", "channelDefects", Criteria.LEFT_JOIN, Restrictions.eq("channelDefects.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("channelDefects.vsdDefect", "defect", Criteria.LEFT_JOIN, Restrictions.eq("defect.isDeleted", IDataService.BOOL_FALSE)) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list(); } catch (Exception ex) { logger.error("An error occured in getViolationsByStatusIdAndVehiclePlateDetails()"); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getViolationsByStatusIdAndVehiclePlateDetails -- END"); return violations; } /** * * Gets a list of violations based on the size details and sorting order * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param firstResult * @param FetchSize * @param sortColumnAttribute * @param sort * @return List<VsdViolation> * @throws VSDDataAccessException */ public List<VsdViolation> getViolations(DataServiceContext dsContext, boolean retrieveArabicData, int firstResult, int maxResults, String sortColumnAttribute, Sort sort) throws VSDDataAccessException { logger.info("getViolationList -- START"); List<VsdViolation> violationList = null; if (firstResult < 0 || maxResults < 1) { throw new IllegalArgumentException(); } try { Session session = (Session) dsContext.getInternalContext(); Criteria criteria = session.createCriteria(VsdViolation.class, "v") .add(Restrictions.eq("v.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdViolationStatus", "vs", Criteria.LEFT_JOIN) .add(Restrictions.eq("vs.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdSeverityLevel", "sl", Criteria.LEFT_JOIN) .add(Restrictions.eq("sl.isDeleted", IDataService.BOOL_FALSE)); criteria.setFirstResult(firstResult); criteria.setMaxResults(maxResults); sortColumnAttribute = "v." + sortColumnAttribute; if (sort == IDataService.Sort.DESCENDING) criteria.addOrder(Order.desc(sortColumnAttribute)); else criteria.addOrder(Order.asc(sortColumnAttribute)); violationList = (List<VsdViolation>) criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY) .list(); } catch (Exception ex) { logger.error("An error occured in getViolationList()"); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getViolationList -- END"); return violationList; } /** * * Gets a list of violations with violationStatus And ViolationSeverityLevel based on VilationStatusName * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param statusName * @return List<VsdViolation> * @throws VSDDataAccessException */ public List<VsdViolation> getViolationsWithViolationStatusAndViolationSeverityLevelByViolationStatusName( DataServiceContext dsContext, boolean retrieveArabicData, String statusName) throws VSDDataAccessException { logger.info("getViolationsWithViolationStatusAndViolationSeverityLevelByViolationStatusName -- START"); List<VsdViolation> violationList = null; try { Session session = (Session) dsContext.getInternalContext(); Criteria criteria = session.createCriteria(VsdViolation.class, "v") .add(Restrictions.eq("v.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdViolationStatus", "vs", Criteria.LEFT_JOIN) .add(Restrictions.eq("vs.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("vs.statusName", statusName)) .createCriteria("v.vsdSeverityLevel", "sl", Criteria.LEFT_JOIN) .add(Restrictions.eq("sl.isDeleted", IDataService.BOOL_FALSE)) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY); violationList = (List<VsdViolation>) criteria.list(); } catch (Exception ex) { logger.error( "An error occured in getViolationsWithViolationStatusAndViolationSeverityLevelByViolationStatusName()"); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getViolationsWithViolationStatusAndViolationSeverityLevelByViolationStatusName -- END"); return violationList; } /** * * Updates a violation * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param vsdViolation * @return VsdViolation * @throws VSDDataAccessException */ public VsdViolation updateViolation(DataServiceContext dsContext, boolean retrieveArabicData, VsdViolation vsdViolation) throws VSDDataAccessException { logger.info("updateViolation -- START"); try { Query query = createDynamicUpdateQuery(vsdViolation, dsContext); query.executeUpdate(); // session.update(vsdViolation); } catch (Exception ex) { logger.error("An error occured in updateViolation()"); throw new VSDDataAccessException(ex.getMessage()); } logger.info("updateViolation -- END"); return vsdViolation; } /** * * Gets a list of violations with open defects based on the status id and the plate details provided * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param violationStatusId * @param vehiclePlateDetails * @return List<VsdViolation> * @throws VSDDataAccessException */ public List<VsdViolation> getViolationsWithOpenDefectsByStatusIdAndVehiclePlateDetails( DataServiceContext dsContext, boolean retrieveArabicData, Long violationStatusId, VehiclePlate vehiclePlateDetails) throws VSDDataAccessException { logger.info("getViolationsWithOpenDefectsByStatusIdAndVehiclePlateDetails -- START"); List<VsdViolation> violations = null; try { Session session = (Session) dsContext.getInternalContext(); violations = session.createCriteria(VsdViolation.class, "violations") .add(Restrictions.eq("violations.vsdViolationStatus.violationStatusId", violationStatusId)) .add(Restrictions.eq("violations.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vsdInspections", "inspections", Criteria.LEFT_JOIN, Restrictions.eq("inspections.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vsdVehicleInfo", "vehicleInfo", Criteria.LEFT_JOIN, Restrictions.eq("vehicleInfo.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("vehicleInfo.vehiclePlateCategory", vehiclePlateDetails.getPlateCategory()) .ignoreCase()) .add(Restrictions.eq("vehicleInfo.vehiclePlateCode", vehiclePlateDetails.getPlateCode()) .ignoreCase()) .add(Restrictions.eq("vehicleInfo.vehiclePlateSource", vehiclePlateDetails.getPlateSource()) .ignoreCase()) .add(Restrictions.eq("vehicleInfo.vehiclePlateNumber", vehiclePlateDetails.getPlateNumber()) .ignoreCase()) .createCriteria("violations.vsdChannelDefects", "channelDefects") .add(Restrictions.eq("channelDefects.channelDefectStatus", SystemProperties.get(SystemProperties.DEFECT_STATUS_OPEN))) .add(Restrictions.eq("channelDefects.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("channelDefects.vsdDefect", "defects", Criteria.LEFT_JOIN, Restrictions.eq("defects.isDeleted", IDataService.BOOL_FALSE)) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list(); } catch (Exception ex) { logger.error("An error occured in getViolationsWithOpenDefectsByStatusIdAndVehiclePlateDetails()"); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getViolationsWithOpenDefectsByStatusIdAndVehiclePlateDetails -- END"); return violations; } /** * * Searches violations with the specified criteria * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param violationSearchCriteria * @return List<ViolationSearchResult> * @throws VSDDataAccessException */ public List<ViolationSearchResult> searchViolations(DataServiceContext dsContext, boolean retrieveArabicData, ViolationSearchCriteria violationSearchCriteria) throws VSDDataAccessException { logger.info("searchViolations -- START"); List<ViolationSearchResult> results = null; try { Session session = (Session) dsContext.getInternalContext(); String defaultSchema = DataServiceImpl.DEFAULT_SCHEMA_NAME; StringBuffer query = new StringBuffer( "Select distinct v.VIOLATION_ID as \"violationTicketCode\",v.VIOLATION_TICKET_CODE as \"violationId\", v.REPORTED_DATE as \"violationDate\", sl.SEVERITY_LEVEL_NAME as \"violationType\", sl.SEVERITY_LEVEL_NAME_A as \"violationTypeA\"," + " vs.STATUS_NAME as \"violationStatus\", vs.STATUS_NAME_A as \"violationStatusA\", c.CHANNEL_NAME as \"recordingChannel\", c.CHANNEL_NAME_A as \"recordingChannelA\", vi.VEHICLE_PLATE_CATEGORY as \"plateCategory\", vi.VEHICLE_PLATE_CODE as \"plateCode\", vi.VEHICLE_PLATE_NUMBER as \"plateNumber\", " + " vi.VEHICLE_PLATE_SOURCE as \"emirateName\", oi.OWNER_NAME as \"operatorName\", oi.OWNER_NAME_A as \"operatorNameA\", oi.TRAFFIC_FILE_NUMBER as \"trafficFileNumber\" " + " FROM " + defaultSchema + ".VSD_VIOLATION v, " + defaultSchema + ".VSD_SEVERITY_LEVEL sl, " + defaultSchema + ".VSD_VIOLATION_STATUS vs, " + defaultSchema + ".VSD_INSPECTION i, " + defaultSchema + ".VSD_CHANNEL_PART_INST cpi, " + defaultSchema + ".VSD_CHANNEL_PARTNER cp, " + defaultSchema + ".VSD_CHANNEL ch, " + defaultSchema + ".VSD_VEHICLE_INFO vi, " + defaultSchema + ".VSD_OWNER_INFO oi, " + defaultSchema + ".VSD_LOCATION l, " + defaultSchema + ".VSD_COUNTRY c" + " where v.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " and v.SEVERITY_LEVEL_ID = sl.SEVERITY_LEVEL_ID and sl.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " and v.VIOLATION_STATUS_ID = vs.VIOLATION_STATUS_ID and vs.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " and v.VIOLATION_ID = i.VIOLATION_ID and i.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " and i.CHANNEL_PART_INST_ID = cpi.CHANNEL_PART_INST_ID and cpi.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " and cpi.CHANNEL_PARTNER_ID = cp.CHANNEL_PARTNER_ID and cp.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " and cp.CHANNEL_ID = ch.CHANNEL_ID and ch.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " and i.VEHICLE_INFO_ID = vi.VEHICLE_INFO_ID and vi.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " and vi.VEHICLE_INFO_ID = oi.VEHICLE_INFO_ID and oi.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " and i.LOCATION_ID = l.LOCATION_ID and l.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " and l.COUNTRY_ID = c.COUNTRY_ID and c.IS_DELETED = '" + IDataService.BOOL_FALSE + "' "); if (violationSearchCriteria.getOperatorName() != null && !violationSearchCriteria.getOperatorName().equals("")) { query.append(" and UPPER(oi.OWNER_NAME) like '%" + violationSearchCriteria.getOperatorName().toUpperCase() + "%'"); } if (violationSearchCriteria.getOperatorNameA() != null && !violationSearchCriteria.getOperatorNameA().equals("")) { query.append(" and oi.OWNER_NAME_A like '%" + violationSearchCriteria.getOperatorNameA() + "%'"); } if (violationSearchCriteria.getTrafficFileNumber() != null && !violationSearchCriteria.getTrafficFileNumber().equals("")) { query.append( " and oi.TRAFFIC_FILE_NUMBER = '" + violationSearchCriteria.getTrafficFileNumber() + "'"); } if (violationSearchCriteria.getTradeLicenseNumber() != null && !violationSearchCriteria.getTradeLicenseNumber().equals("")) { query.append( " and oi.TRADE_LICENSE_NUMBER = '" + violationSearchCriteria.getTradeLicenseNumber() + "'"); } if (violationSearchCriteria.getPlateCategory() != null && !violationSearchCriteria.getPlateCategory().equals("")) { query.append(" and UPPER(vi.VEHICLE_PLATE_CATEGORY) = (Select UPPER(vpcc.CATEGORY_NAME) from " + defaultSchema + ".VSD_VEH_PLATE_CAT_CODE vpcc where vpcc.VEH_PLATE_CAT_CODE_ID = " + violationSearchCriteria.getPlateCategory() + " )"); } if (violationSearchCriteria.getPlateCode() != null && !violationSearchCriteria.getPlateCode().equals("")) { query.append(" and UPPER(vi.VEHICLE_PLATE_CODE) = (Select UPPER(vpcc.CATEGORY_NAME) from " + defaultSchema + ".VSD_VEH_PLATE_CAT_CODE vpcc where vpcc.VEH_PLATE_CAT_CODE_ID = " + violationSearchCriteria.getPlateCode() + " )"); } if (violationSearchCriteria.getPlateNumber() != null && !violationSearchCriteria.getPlateNumber().equals("")) { query.append(" and UPPER(vi.VEHICLE_PLATE_NUMBER) = '" + violationSearchCriteria.getPlateNumber().toUpperCase() + "'"); } if (violationSearchCriteria.getSeverityLevelId() != null) { query.append(" and sl.SEVERITY_LEVEL_ID = " + violationSearchCriteria.getSeverityLevelId()); } if (violationSearchCriteria.getViolationId() != null && !violationSearchCriteria.getViolationId().equals("")) { query.append(" and v.VIOLATION_TICKET_CODE = '" + violationSearchCriteria.getViolationId() + "'"); } if (violationSearchCriteria.getRecordingChannelId() != null) { query.append(" and ch.CHANNEL_ID = " + violationSearchCriteria.getRecordingChannelId()); } if (violationSearchCriteria.getStartDate() != null && violationSearchCriteria.getEndDate() != null) { SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); query.append(" and to_date(to_char(v.REPORTED_DATE,'YYYY-MM-DD'),'YYYY-MM-DD') between TO_DATE ('" + dateFormat.format(violationSearchCriteria.getStartDate()) + "','YYYY-MM-DD') and TO_DATE ('" + dateFormat.format(violationSearchCriteria.getEndDate()) + "','YYYY-MM-DD')"); } if (violationSearchCriteria.getLocationId() != null) { query.append(" and i.LOCATION_ID = " + violationSearchCriteria.getLocationId()); } if (violationSearchCriteria.getAreaId() != null) { query.append(" and l.PARENT_LOCATION_ID = " + violationSearchCriteria.getAreaId()); } if (violationSearchCriteria.getEmirateId() != null) { query.append(" and c.COUNTRY_ID = " + violationSearchCriteria.getEmirateId()); } results = session.createSQLQuery(query.toString()) .setResultTransformer(new AliasToBeanResultTransformer(ViolationSearchResult.class)).list(); } catch (Exception ex) { logger.error("An error occured in searchViolations()"); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("searchViolations -- END"); return results; } /** * * Searches violations with the specified criteria * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param violationSearchCriteria * @return List<ViolationSearchResult> * @throws VSDDataAccessException */ public List<ViolationSearchResult> searchViolations(DataServiceContext dsContext, boolean retrieveArabicData, ViolationSearchCriteria violationSearchCriteria, PaginationParam paginationParam) throws VSDDataAccessException { logger.info("searchViolations -- START"); List<ViolationSearchResult> results = null; try { Session session = (Session) dsContext.getInternalContext(); String defaultSchema = DataServiceImpl.DEFAULT_SCHEMA_NAME; String columnAttrib = ""; String inspectionTable = "(SELECT a.inspection_id, a.IS_DELETED, a.CHANNEL_PART_INST_ID, a.VEHICLE_INFO_ID, a.LOCATION_ID, a.VIOLATION_ID FROM " + defaultSchema + ".VSD_INSPECTION a WHERE a.inspection_id NOT IN (SELECT b.inspection_id FROM " + defaultSchema + ".VSD_INSPECTION b, " + defaultSchema + ".VSD_INSPECTION c WHERE b.violation_id = c.violation_id AND b.inspection_id > c.inspection_id)) i, ";//One violation can have multiple inspections StringBuffer query = new StringBuffer( "Select distinct v.VIOLATION_ID as \"violationTicketCode\",v.VIOLATION_TICKET_CODE as \"violationId\", v.REPORTED_DATE as \"violationDate\", sl.SEVERITY_LEVEL_NAME as \"violationType\", sl.SEVERITY_LEVEL_NAME_A as \"violationTypeA\"," + " vs.STATUS_NAME as \"violationStatus\", vs.STATUS_NAME_A as \"violationStatusA\", ch.CHANNEL_NAME as \"recordingChannel\", ch.CHANNEL_NAME_A as \"recordingChannelA\", vi.VEHICLE_PLATE_CATEGORY as \"plateCategory\", vi.VEHICLE_PLATE_CODE as \"plateCode\", vi.VEHICLE_PLATE_NUMBER as \"plateNumber\", " + " vi.VEHICLE_PLATE_SOURCE as \"emirateName\", oi.OWNER_NAME as \"operatorName\", oi.OWNER_NAME_A as \"operatorNameA\", oi.TRAFFIC_FILE_NUMBER as \"trafficFileNumber\" " + " FROM " + defaultSchema + ".VSD_VIOLATION v, " + defaultSchema + ".VSD_SEVERITY_LEVEL sl, " + defaultSchema + ".VSD_VIOLATION_STATUS vs, " + inspectionTable + defaultSchema + ".VSD_CHANNEL_PART_INST cpi, " + defaultSchema + ".VSD_CHANNEL_PARTNER cp, " + defaultSchema + ".VSD_CHANNEL ch, " + defaultSchema + ".VSD_VEHICLE_INFO vi, " + defaultSchema + ".VSD_OWNER_INFO oi, " + defaultSchema + ".VSD_LOCATION l, " + defaultSchema + ".VSD_COUNTRY c" + " where v.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " and v.SEVERITY_LEVEL_ID = sl.SEVERITY_LEVEL_ID and sl.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " and v.VIOLATION_STATUS_ID = vs.VIOLATION_STATUS_ID and vs.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " and v.VIOLATION_ID = i.VIOLATION_ID and i.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " and i.CHANNEL_PART_INST_ID = cpi.CHANNEL_PART_INST_ID and cpi.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " and cpi.CHANNEL_PARTNER_ID = cp.CHANNEL_PARTNER_ID and cp.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " and cp.CHANNEL_ID = ch.CHANNEL_ID and ch.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " and i.VEHICLE_INFO_ID = vi.VEHICLE_INFO_ID and vi.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " and vi.VEHICLE_INFO_ID = oi.VEHICLE_INFO_ID and oi.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " and i.LOCATION_ID = l.LOCATION_ID and l.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " and l.COUNTRY_ID = c.COUNTRY_ID and c.IS_DELETED = '" + IDataService.BOOL_FALSE + "' "); if (violationSearchCriteria.getOperatorName() != null && !violationSearchCriteria.getOperatorName().equals("")) { query.append(" and UPPER(oi.OWNER_NAME) like '%" + violationSearchCriteria.getOperatorName().replaceAll("'", "''").toUpperCase() + "%'"); } if (violationSearchCriteria.getOperatorNameA() != null && !violationSearchCriteria.getOperatorNameA().equals("")) { query.append(" and oi.OWNER_NAME_A like '%" + violationSearchCriteria.getOperatorNameA().replaceAll("'", "''") + "%'"); } if (violationSearchCriteria.getTrafficFileNumber() != null && !violationSearchCriteria.getTrafficFileNumber().equals("")) { query.append(" and oi.TRAFFIC_FILE_NUMBER = '" + violationSearchCriteria.getTrafficFileNumber().replaceAll("'", "''") + "'"); } if (violationSearchCriteria.getTradeLicenseNumber() != null && !violationSearchCriteria.getTradeLicenseNumber().equals("")) { query.append(" and oi.TRADE_LICENSE_NUMBER = '" + violationSearchCriteria.getTradeLicenseNumber().replaceAll("'", "''") + "'"); } if (violationSearchCriteria.getPlateCategory() != null && !violationSearchCriteria.getPlateCategory().equals("")) { query.append(" and UPPER(vi.VEHICLE_PLATE_CATEGORY) = (Select UPPER(vpcc.CATEGORY_NAME) from " + defaultSchema + ".VSD_VEH_PLATE_CAT_CODE vpcc where vpcc.VEH_PLATE_CAT_CODE_ID = " + violationSearchCriteria.getPlateCategory() + " )"); } if (violationSearchCriteria.getPlateCode() != null && !violationSearchCriteria.getPlateCode().equals("")) { query.append(" and UPPER(vi.VEHICLE_PLATE_CODE) = (Select UPPER(vpcc.CATEGORY_NAME) from " + defaultSchema + ".VSD_VEH_PLATE_CAT_CODE vpcc where vpcc.VEH_PLATE_CAT_CODE_ID = " + violationSearchCriteria.getPlateCode() + " )"); } if (violationSearchCriteria.getPlateNumber() != null && !violationSearchCriteria.getPlateNumber().equals("")) { query.append(" and UPPER(vi.VEHICLE_PLATE_NUMBER) = '" + violationSearchCriteria.getPlateNumber().replaceAll("'", "''").toUpperCase() + "'"); } if (violationSearchCriteria.getSeverityLevelId() != null) { query.append(" and sl.SEVERITY_LEVEL_ID = " + violationSearchCriteria.getSeverityLevelId()); } if (violationSearchCriteria.getViolationId() != null && !violationSearchCriteria.getViolationId().equals("")) { query.append(" and v.VIOLATION_TICKET_CODE = '" + violationSearchCriteria.getViolationId().replaceAll("'", "''") + "'"); } if (violationSearchCriteria.getRecordingChannelId() != null) { query.append(" and ch.CHANNEL_ID = " + violationSearchCriteria.getRecordingChannelId()); } if (violationSearchCriteria.getStartDate() != null && violationSearchCriteria.getEndDate() != null) { SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); query.append(" and to_date(to_char(v.REPORTED_DATE,'YYYY-MM-DD'),'YYYY-MM-DD') between TO_DATE ('" + dateFormat.format(violationSearchCriteria.getStartDate()) + "','YYYY-MM-DD') and TO_DATE ('" + dateFormat.format(violationSearchCriteria.getEndDate()) + "','YYYY-MM-DD')"); } if (violationSearchCriteria.getLocationId() != null) { query.append(" and i.LOCATION_ID = " + violationSearchCriteria.getLocationId()); } if (violationSearchCriteria.getAreaId() != null) { query.append(" and l.PARENT_LOCATION_ID = " + violationSearchCriteria.getAreaId()); } if (violationSearchCriteria.getEmirateId() != null) { query.append(" and c.COUNTRY_ID = " + violationSearchCriteria.getEmirateId()); } if (paginationParam.getOrderBy() != null && !paginationParam.getOrderBy().equals("")) { if (paginationParam.getSortOrder() != null && !paginationParam.getSortOrder().equals("")) { if (paginationParam.getOrderBy().equalsIgnoreCase(SORT_COLUMN.VIOLATION_ID.toString())) { columnAttrib = "v." + SORT_COLUMN.VIOLATION_ID; } else if (paginationParam.getOrderBy() .equalsIgnoreCase(SORT_COLUMN.VIOLATION_TICKET_CODE.toString())) { columnAttrib = "v." + SORT_COLUMN.VIOLATION_TICKET_CODE; } else if (paginationParam.getOrderBy() .equalsIgnoreCase(SORT_COLUMN.REPORTED_DATE.toString())) { columnAttrib = "v." + SORT_COLUMN.REPORTED_DATE; } else if (paginationParam.getOrderBy() .equalsIgnoreCase(SORT_COLUMN.SEVERITY_LEVEL_NAME.toString())) { columnAttrib = "sl." + SORT_COLUMN.SEVERITY_LEVEL_NAME; } else if (paginationParam.getOrderBy() .equalsIgnoreCase(SORT_COLUMN.SEVERITY_LEVEL_NAME_A.toString())) { columnAttrib = "sl." + SORT_COLUMN.SEVERITY_LEVEL_NAME_A; } else if (paginationParam.getOrderBy().equalsIgnoreCase(SORT_COLUMN.STATUS_NAME.toString())) { columnAttrib = "vs." + SORT_COLUMN.STATUS_NAME; } else if (paginationParam.getOrderBy() .equalsIgnoreCase(SORT_COLUMN.STATUS_NAME_A.toString())) { columnAttrib = "vs." + SORT_COLUMN.STATUS_NAME_A; } else if (paginationParam.getOrderBy().equalsIgnoreCase(SORT_COLUMN.CHANNEL_NAME.toString())) { columnAttrib = "ch." + SORT_COLUMN.CHANNEL_NAME; } else if (paginationParam.getOrderBy() .equalsIgnoreCase(SORT_COLUMN.CHANNEL_NAME_A.toString())) { columnAttrib = "ch." + SORT_COLUMN.CHANNEL_NAME_A; } else if (paginationParam.getOrderBy() .equalsIgnoreCase(SORT_COLUMN.VEHICLE_PLATE_CATEGORY.toString())) { columnAttrib = "vi." + SORT_COLUMN.VEHICLE_PLATE_CATEGORY; } else if (paginationParam.getOrderBy() .equalsIgnoreCase(SORT_COLUMN.VEHICLE_PLATE_CODE.toString())) { columnAttrib = "vi." + SORT_COLUMN.VEHICLE_PLATE_CODE; } else if (paginationParam.getOrderBy() .equalsIgnoreCase(SORT_COLUMN.VEHICLE_PLATE_NUMBER.toString())) { columnAttrib = "vi." + SORT_COLUMN.VEHICLE_PLATE_NUMBER; } else if (paginationParam.getOrderBy() .equalsIgnoreCase(SORT_COLUMN.VEHICLE_PLATE_SOURCE.toString())) { columnAttrib = "vi." + SORT_COLUMN.VEHICLE_PLATE_SOURCE; } else if (paginationParam.getOrderBy().equalsIgnoreCase(SORT_COLUMN.OWNER_NAME_A.toString())) { columnAttrib = "oi." + SORT_COLUMN.OWNER_NAME_A; } else if (paginationParam.getOrderBy() .equalsIgnoreCase(SORT_COLUMN.TRAFFIC_FILE_NUMBER.toString())) { columnAttrib = "oi." + SORT_COLUMN.TRAFFIC_FILE_NUMBER; } else if (paginationParam.getOrderBy().equalsIgnoreCase(SORT_COLUMN.OWNER_NAME.toString())) { columnAttrib = "oi." + SORT_COLUMN.OWNER_NAME; } } } else { if (paginationParam.getPageLocale().equalsIgnoreCase(Constant.LOCALE_ENGLISH)) columnAttrib = "v." + SORT_COLUMN.REPORTED_DATE; else columnAttrib = "v." + SORT_COLUMN.REPORTED_DATE; } if (paginationParam.getSortOrder().equalsIgnoreCase(IDataService.Sort.DESCENDING.toString())) { query.append(" ORDER BY UPPER(" + columnAttrib + ") DESC"); } else { query.append(" ORDER BY UPPER(" + columnAttrib + ") ASC"); } results = session.createSQLQuery(query.toString()) .setResultTransformer(new AliasToBeanResultTransformer(ViolationSearchResult.class)) .setFirstResult(paginationParam.getFirstResult().intValue()) .setMaxResults(paginationParam.getFetchedSize().intValue()).list(); } catch (Exception ex) { logger.error("An error occured in searchViolations()"); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("searchViolations -- END"); return results; } /** * * get count for the Searches violations with the specified criteria * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param violationSearchCriteria * @return Long * @throws VSDDataAccessException */ public Long getCountForSearchViolations(DataServiceContext dsContext, boolean retrieveArabicData, ViolationSearchCriteria violationSearchCriteria) throws VSDDataAccessException { logger.info("searchViolations -- START"); Long resultSize = null; try { Session session = (Session) dsContext.getInternalContext(); String defaultSchema = DataServiceImpl.DEFAULT_SCHEMA_NAME; String inspectionTable = "(SELECT a.inspection_id, a.IS_DELETED, a.CHANNEL_PART_INST_ID, a.VEHICLE_INFO_ID, a.LOCATION_ID, a.VIOLATION_ID FROM " + defaultSchema + ".VSD_INSPECTION a WHERE a.inspection_id NOT IN (SELECT b.inspection_id FROM " + defaultSchema + ".VSD_INSPECTION b, " + defaultSchema + ".VSD_INSPECTION c WHERE b.violation_id = c.violation_id AND b.inspection_id > c.inspection_id)) i, ";//One violation can have multiple inspections StringBuffer query = new StringBuffer("Select count (distinct v.VIOLATION_ID) " + " FROM " + defaultSchema + ".VSD_VIOLATION v, " + defaultSchema + ".VSD_SEVERITY_LEVEL sl, " + defaultSchema + ".VSD_VIOLATION_STATUS vs, " + inspectionTable + defaultSchema + ".VSD_CHANNEL_PART_INST cpi, " + defaultSchema + ".VSD_CHANNEL_PARTNER cp, " + defaultSchema + ".VSD_CHANNEL ch, " + defaultSchema + ".VSD_VEHICLE_INFO vi, " + defaultSchema + ".VSD_OWNER_INFO oi, " + defaultSchema + ".VSD_LOCATION l, " + defaultSchema + ".VSD_COUNTRY c" + " where v.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " and v.SEVERITY_LEVEL_ID = sl.SEVERITY_LEVEL_ID and sl.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " and v.VIOLATION_STATUS_ID = vs.VIOLATION_STATUS_ID and vs.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " and v.VIOLATION_ID = i.VIOLATION_ID and i.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " and i.CHANNEL_PART_INST_ID = cpi.CHANNEL_PART_INST_ID and cpi.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " and cpi.CHANNEL_PARTNER_ID = cp.CHANNEL_PARTNER_ID and cp.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " and cp.CHANNEL_ID = ch.CHANNEL_ID and ch.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " and i.VEHICLE_INFO_ID = vi.VEHICLE_INFO_ID and vi.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " and vi.VEHICLE_INFO_ID = oi.VEHICLE_INFO_ID and oi.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " and i.LOCATION_ID = l.LOCATION_ID and l.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " and l.COUNTRY_ID = c.COUNTRY_ID and c.IS_DELETED = '" + IDataService.BOOL_FALSE + "' "); if (violationSearchCriteria.getOperatorName() != null && !violationSearchCriteria.getOperatorName().equals("")) { query.append(" and UPPER(oi.OWNER_NAME) like '%" + violationSearchCriteria.getOperatorName().replaceAll("'", "''").toUpperCase() + "%'"); } if (violationSearchCriteria.getOperatorNameA() != null && !violationSearchCriteria.getOperatorNameA().equals("")) { query.append(" and oi.OWNER_NAME_A like '%" + violationSearchCriteria.getOperatorNameA().replaceAll("'", "''") + "%'"); } if (violationSearchCriteria.getTrafficFileNumber() != null && !violationSearchCriteria.getTrafficFileNumber().equals("")) { query.append(" and oi.TRAFFIC_FILE_NUMBER = '" + violationSearchCriteria.getTrafficFileNumber().replaceAll("'", "''") + "'"); } if (violationSearchCriteria.getTradeLicenseNumber() != null && !violationSearchCriteria.getTradeLicenseNumber().equals("")) { query.append(" and oi.TRADE_LICENSE_NUMBER = '" + violationSearchCriteria.getTradeLicenseNumber().replaceAll("'", "''") + "'"); } if (violationSearchCriteria.getPlateCategory() != null && !violationSearchCriteria.getPlateCategory().equals("")) { query.append(" and UPPER(vi.VEHICLE_PLATE_CATEGORY) = (Select UPPER(vpcc.CATEGORY_NAME) from " + defaultSchema + ".VSD_VEH_PLATE_CAT_CODE vpcc where vpcc.VEH_PLATE_CAT_CODE_ID = " + violationSearchCriteria.getPlateCategory() + " )"); } if (violationSearchCriteria.getPlateCode() != null && !violationSearchCriteria.getPlateCode().equals("")) { query.append(" and UPPER(vi.VEHICLE_PLATE_CODE) = (Select UPPER(vpcc.CATEGORY_NAME) from " + defaultSchema + ".VSD_VEH_PLATE_CAT_CODE vpcc where vpcc.VEH_PLATE_CAT_CODE_ID = " + violationSearchCriteria.getPlateCode() + " )"); } if (violationSearchCriteria.getPlateNumber() != null && !violationSearchCriteria.getPlateNumber().equals("")) { query.append(" and UPPER(vi.VEHICLE_PLATE_NUMBER) = '" + violationSearchCriteria.getPlateNumber().replaceAll("'", "''").toUpperCase() + "'"); } if (violationSearchCriteria.getSeverityLevelId() != null) { query.append(" and sl.SEVERITY_LEVEL_ID = " + violationSearchCriteria.getSeverityLevelId()); } if (violationSearchCriteria.getViolationId() != null && !violationSearchCriteria.getViolationId().equals("")) { query.append(" and v.VIOLATION_TICKET_CODE = '" + violationSearchCriteria.getViolationId().replaceAll("'", "''") + "'"); } if (violationSearchCriteria.getRecordingChannelId() != null) { query.append(" and ch.CHANNEL_ID = " + violationSearchCriteria.getRecordingChannelId()); } if (violationSearchCriteria.getStartDate() != null && violationSearchCriteria.getEndDate() != null) { SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); query.append(" and to_date(to_char(v.REPORTED_DATE,'YYYY-MM-DD'),'YYYY-MM-DD') between TO_DATE ('" + dateFormat.format(violationSearchCriteria.getStartDate()) + "','YYYY-MM-DD') and TO_DATE ('" + dateFormat.format(violationSearchCriteria.getEndDate()) + "','YYYY-MM-DD')"); } if (violationSearchCriteria.getLocationId() != null) { query.append(" and i.LOCATION_ID = " + violationSearchCriteria.getLocationId()); } if (violationSearchCriteria.getAreaId() != null) { query.append(" and l.PARENT_LOCATION_ID = " + violationSearchCriteria.getAreaId()); } if (violationSearchCriteria.getEmirateId() != null) { query.append(" and c.COUNTRY_ID = " + violationSearchCriteria.getEmirateId()); } BigDecimal bigDecimalCount = (BigDecimal) session.createSQLQuery(query.toString()).uniqueResult(); resultSize = bigDecimalCount.longValue(); } catch (Exception ex) { logger.error("An error occured in searchViolations()"); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("searchViolations -- END"); return resultSize; } /** * * Get violation Details by ViolationStatusCode * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param violationStatusCode * @return List <VsdViolation> * @throws VSDDataAccessException */ public List<VsdViolation> getViolationDetailsByViolationStatusCode(DataServiceContext dsContext, boolean retrieveArabicData, String violationStatusCode) throws VSDDataAccessException { logger.info("getViolationDetailsByViolationStatusCode -- START"); List<VsdViolation> violations = null; try { Session session = (Session) dsContext.getInternalContext(); violations = (List) session.createCriteria(VsdViolation.class, "v") .add(Restrictions.eq("v.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdViolationStatus", "vs", Criteria.LEFT_JOIN, Restrictions.eq("vs.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("vs.statusCode", violationStatusCode)) .createCriteria("v.vsdSeverityLevel", "sl", Criteria.LEFT_JOIN, Restrictions.eq("sl.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdChannelDefects", "cd", Criteria.LEFT_JOIN, Restrictions.eq("cd.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdPlateConditionByFrontPlateConditionId", "pcbfpc", Criteria.LEFT_JOIN, Restrictions.eq("pcbfpc.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdChannelPartInst", "cpi", Criteria.LEFT_JOIN, Restrictions.eq("cpi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdLocation", "l", Criteria.LEFT_JOIN, Restrictions.eq("l.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdPlateConditionByRearPlateConditionId", "pcbrpc", Criteria.LEFT_JOIN, Restrictions.eq("pcbrpc.isDeleted", IDataService.BOOL_FALSE)) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list(); } catch (Exception ex) { logger.error("An error occured in getViolationDetailsByViolationStatusCode()"); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getViolationDetailsByViolationStatusCode -- END"); return violations; } /** * * Get violation Details By ViolationId * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param violationId * @return VsdViolation * @throws VSDDataAccessException */ public VsdViolation getViolationDetailsByViolationId(DataServiceContext dsContext, boolean retrieveArabicData, Long violationId) throws VSDDataAccessException { logger.info("getViolationDetailsByViolationId -- START"); VsdViolation violation = null; try { Session session = (Session) dsContext.getInternalContext(); violation = (VsdViolation) session.createCriteria(VsdViolation.class, "v") .add(Restrictions.eq("v.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("v.violationId", violationId)) .createCriteria("v.vsdViolationStatus", "vs", Criteria.LEFT_JOIN, Restrictions.eq("vs.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdSeverityLevel", "sl", Criteria.LEFT_JOIN, Restrictions.eq("sl.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("sl.vsdSeverityLevelProps", "slp", Criteria.LEFT_JOIN, Restrictions.eq("slp.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdChannelDefects", "cd", Criteria.LEFT_JOIN, Restrictions.eq("cd.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdDefect", "d", Criteria.LEFT_JOIN, Restrictions.eq("d.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("d.vsdDefectCategory", "dc", Criteria.LEFT_JOIN, Restrictions.eq("dc.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vi.vsdDriverInfos", "di", Criteria.LEFT_JOIN, Restrictions.eq("di.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vi.vsdOwnerInfos", "oi", Criteria.LEFT_JOIN, Restrictions.eq("oi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdPlateConditionByFrontPlateConditionId", "pcbfpc", Criteria.LEFT_JOIN, Restrictions.eq("pcbfpc.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdChannelPartInst", "cpi", Criteria.LEFT_JOIN, Restrictions.eq("cpi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdLocation", "l", Criteria.LEFT_JOIN, Restrictions.eq("l.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdPlateConditionByRearPlateConditionId", "pcbrpc", Criteria.LEFT_JOIN, Restrictions.eq("pcbrpc.isDeleted", IDataService.BOOL_FALSE)) .uniqueResult(); } catch (Exception ex) { logger.error("An error occured in getViolationDetailsByViolationId()"); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getViolationDetailsByViolationId -- END"); return violation; } /** * * Gets a list of all violations and its associated inspections * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @return List<VsdViolation> * @throws VSDException */ public List<VsdViolation> getViolationsWithInspections(final DataServiceContext dsContext, boolean retrieveArabicData) throws VSDDataAccessException { logger.info("getViolationsWithInspections -- START"); List<VsdViolation> violations = null; try { Session session = (Session) dsContext.getInternalContext(); violations = session.createCriteria(VsdViolation.class, "v") .add(Restrictions.eq("v.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdViolationStatus", "vs", Criteria.LEFT_JOIN, Restrictions.eq("vs.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdSeverityLevel", "vSev", Criteria.LEFT_JOIN, Restrictions.eq("vSev.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdChannelPartInst", "cpi", Criteria.LEFT_JOIN, Restrictions.eq("cpi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdLocation", "l", Criteria.LEFT_JOIN, Restrictions.eq("l.isDeleted", IDataService.BOOL_FALSE)) .addOrder(Order.desc("v.violationId")).setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY) .list(); } catch (Exception ex) { logger.error("An error occured in getViolationsWithInspections()"); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getViolationsWithInspections -- END"); return violations; } /** * * Gets a list of all violations and its associated inspections * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param paginationValues * @return List<VsdViolation> * @throws VSDException */ public List<VsdViolation> getViolationsWithInspections(final DataServiceContext dsContext, boolean retrieveArabicData, PaginationParam paginationValues) throws VSDDataAccessException { logger.info("getViolationsWithInspections -- START"); List<VsdViolation> violations = null; try { Session session = (Session) dsContext.getInternalContext(); ProjectionList projectionList = Projections.projectionList(); projectionList.add(Projections.property("v.violationId"), "violationId"); Criteria crit = session.createCriteria(VsdViolation.class, "v") .add(Restrictions.eq("v.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdViolationStatus", "vs", Criteria.LEFT_JOIN, Restrictions.eq("vs.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdSeverityLevel", "vSev", Criteria.LEFT_JOIN, Restrictions.eq("vSev.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdChannelPartInst", "cpi", Criteria.LEFT_JOIN, Restrictions.eq("cpi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdLocation", "l", Criteria.LEFT_JOIN, Restrictions.eq("l.isDeleted", IDataService.BOOL_FALSE)); if (paginationValues.getFirstResult() != null && paginationValues.getFirstResult().longValue() != -1) { crit.setFirstResult(paginationValues.getFirstResult().intValue()); } if (paginationValues.getFetchedSize() != null && paginationValues.getFetchedSize().longValue() != -1) { crit.setMaxResults(paginationValues.getFetchedSize().intValue()); } if (paginationValues.getOrderBy() != null && !paginationValues.getOrderBy().equals("")) { if (paginationValues.getSortOrder() != null && !paginationValues.getSortOrder().equals("")) { String columnAttrib = ""; if (paginationValues.getOrderBy().equalsIgnoreCase( SORT_COLUMN_GET_VIOLATIONS_WITH_INSPECTIONS.VIOLATION_ID.toString())) { columnAttrib = "v." + SORT_COLUMN_GET_VIOLATIONS_WITH_INSPECTIONS.VIOLATION_ID; projectionList.add(Projections.property(columnAttrib), "violationTicketCode"); } else if (paginationValues.getOrderBy() .equalsIgnoreCase(SORT_COLUMN_GET_VIOLATIONS_WITH_INSPECTIONS.SEVERITY.toString())) { columnAttrib = "vSev." + SORT_COLUMN_GET_VIOLATIONS_WITH_INSPECTIONS.SEVERITY; projectionList.add(Projections.property(columnAttrib), "violationTicketCode"); } else if (paginationValues.getOrderBy() .equalsIgnoreCase(SORT_COLUMN_GET_VIOLATIONS_WITH_INSPECTIONS.STATUS.toString())) { columnAttrib = "vs." + SORT_COLUMN_GET_VIOLATIONS_WITH_INSPECTIONS.STATUS; projectionList.add(Projections.property(columnAttrib), "violationTicketCode"); } else if (paginationValues.getOrderBy() .equalsIgnoreCase(SORT_COLUMN_GET_VIOLATIONS_WITH_INSPECTIONS.DATE_TIME.toString())) { columnAttrib = "i." + SORT_COLUMN_GET_VIOLATIONS_WITH_INSPECTIONS.DATE_TIME; projectionList.add(Projections.property(columnAttrib), "ownerSuspensionDate"); } else if (paginationValues.getOrderBy().equalsIgnoreCase( SORT_COLUMN_GET_VIOLATIONS_WITH_INSPECTIONS.PLATE_DETAIL.toString())) { //columnAttrib = "vSev." + SORT_COLUMN_GET_VIOLATIONS_WITH_INSPECTIONS.SEVERITY; } else if (paginationValues.getOrderBy() .equalsIgnoreCase(SORT_COLUMN_GET_VIOLATIONS_WITH_INSPECTIONS.SEVERITY_A.toString())) { columnAttrib = "vSev." + SORT_COLUMN_GET_VIOLATIONS_WITH_INSPECTIONS.SEVERITY_A; projectionList.add(Projections.property(columnAttrib), "violationTicketCode"); } else if (paginationValues.getOrderBy() .equalsIgnoreCase(SORT_COLUMN_GET_VIOLATIONS_WITH_INSPECTIONS.STATUS_A.toString())) { columnAttrib = "vs." + SORT_COLUMN_GET_VIOLATIONS_WITH_INSPECTIONS.STATUS_A; projectionList.add(Projections.property(columnAttrib), "violationTicketCode"); } else { columnAttrib = "v.violationId"; projectionList.add(Projections.property(columnAttrib), "violationId"); } if (paginationValues.getSortOrder().equalsIgnoreCase(IDataService.Sort.DESCENDING.toString())) { if (paginationValues.getOrderBy().equalsIgnoreCase( SORT_COLUMN_GET_VIOLATIONS_WITH_INSPECTIONS.PLATE_DETAIL.toString())) { crit.addOrder(Order.desc("vi.vehiclePlateNumber").ignoreCase()); crit.addOrder(Order.desc("vi.vehiclePlateCategory").ignoreCase()); crit.addOrder(Order.desc("vi.vehiclePlateCode").ignoreCase()); crit.addOrder(Order.desc("vi.vehiclePlateSource").ignoreCase()); projectionList.add(Projections.property("vi.vehiclePlateNumber"), "violationTicketCode"); projectionList.add(Projections.property("vi.vehiclePlateCategory"), "violationTicketCode"); projectionList.add(Projections.property("vi.vehiclePlateCode"), "violationTicketCode"); projectionList.add(Projections.property("vi.vehiclePlateSource"), "violationTicketCode"); } else crit.addOrder(Order.desc(columnAttrib).ignoreCase()); } else { if (paginationValues.getOrderBy().equalsIgnoreCase( SORT_COLUMN_GET_VIOLATIONS_WITH_INSPECTIONS.PLATE_DETAIL.toString())) { crit.addOrder(Order.asc("vi.vehiclePlateNumber").ignoreCase()); crit.addOrder(Order.asc("vi.vehiclePlateCategory").ignoreCase()); crit.addOrder(Order.asc("vi.vehiclePlateCode").ignoreCase()); crit.addOrder(Order.asc("vi.vehiclePlateSource").ignoreCase()); projectionList.add(Projections.property("vi.vehiclePlateNumber"), "violationTicketCode"); projectionList.add(Projections.property("vi.vehiclePlateCategory"), "violationTicketCode"); projectionList.add(Projections.property("vi.vehiclePlateCode"), "violationTicketCode"); projectionList.add(Projections.property("vi.vehiclePlateSource"), "violationTicketCode"); } else crit.addOrder(Order.asc(columnAttrib).ignoreCase()); } } } else { crit.addOrder(Order.desc("v.violationId").ignoreCase()); } List list = crit.setProjection(Projections.distinct(projectionList)) .setResultTransformer(new AliasToBeanResultTransformer(VsdViolation.class)).list(); logger.debug("list.size() : " + list.size()); Set resultSet = new HashSet(list); logger.debug("resultSet.size() : " + resultSet.size()); if (resultSet.size() == 0) return new ArrayList(); Iterator iterator = resultSet.iterator(); ArrayList innerQueryList = new ArrayList<Long>(); while (iterator.hasNext()) { VsdViolation violation = (VsdViolation) iterator.next(); innerQueryList.add(violation.getViolationId()); } Criteria main = session.createCriteria(VsdViolation.class, "v") .add(Restrictions.eq("v.isDeleted", IDataService.BOOL_FALSE)) .add(Property.forName("v.violationId").in(innerQueryList)) .createCriteria("v.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdViolationStatus", "vs", Criteria.LEFT_JOIN, Restrictions.eq("vs.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdSeverityLevel", "vSev", Criteria.LEFT_JOIN, Restrictions.eq("vSev.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdChannelPartInst", "cpi", Criteria.LEFT_JOIN, Restrictions.eq("cpi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdLocation", "l", Criteria.LEFT_JOIN, Restrictions.eq("l.isDeleted", IDataService.BOOL_FALSE)); if (paginationValues.getOrderBy() != null && !paginationValues.getOrderBy().equals("")) { if (paginationValues.getSortOrder() != null && !paginationValues.getSortOrder().equals("")) { String columnAttrib = ""; if (paginationValues.getOrderBy().equalsIgnoreCase( SORT_COLUMN_GET_VIOLATIONS_WITH_INSPECTIONS.VIOLATION_ID.toString())) { columnAttrib = "v." + SORT_COLUMN_GET_VIOLATIONS_WITH_INSPECTIONS.VIOLATION_ID; } else if (paginationValues.getOrderBy() .equalsIgnoreCase(SORT_COLUMN_GET_VIOLATIONS_WITH_INSPECTIONS.SEVERITY.toString())) { columnAttrib = "vSev." + SORT_COLUMN_GET_VIOLATIONS_WITH_INSPECTIONS.SEVERITY; } else if (paginationValues.getOrderBy() .equalsIgnoreCase(SORT_COLUMN_GET_VIOLATIONS_WITH_INSPECTIONS.STATUS.toString())) { columnAttrib = "vs." + SORT_COLUMN_GET_VIOLATIONS_WITH_INSPECTIONS.STATUS; } else if (paginationValues.getOrderBy() .equalsIgnoreCase(SORT_COLUMN_GET_VIOLATIONS_WITH_INSPECTIONS.DATE_TIME.toString())) { columnAttrib = "i." + SORT_COLUMN_GET_VIOLATIONS_WITH_INSPECTIONS.DATE_TIME; } else if (paginationValues.getOrderBy().equalsIgnoreCase( SORT_COLUMN_GET_VIOLATIONS_WITH_INSPECTIONS.PLATE_DETAIL.toString())) { //columnAttrib = "vSev." + SORT_COLUMN_GET_VIOLATIONS_WITH_INSPECTIONS.SEVERITY; } else if (paginationValues.getOrderBy() .equalsIgnoreCase(SORT_COLUMN_GET_VIOLATIONS_WITH_INSPECTIONS.SEVERITY_A.toString())) { columnAttrib = "vSev." + SORT_COLUMN_GET_VIOLATIONS_WITH_INSPECTIONS.SEVERITY_A; } else if (paginationValues.getOrderBy() .equalsIgnoreCase(SORT_COLUMN_GET_VIOLATIONS_WITH_INSPECTIONS.STATUS_A.toString())) { columnAttrib = "vs." + SORT_COLUMN_GET_VIOLATIONS_WITH_INSPECTIONS.STATUS_A; } else { columnAttrib = "v.violationId"; } if (paginationValues.getSortOrder().equalsIgnoreCase(IDataService.Sort.DESCENDING.toString())) { if (paginationValues.getOrderBy().equalsIgnoreCase( SORT_COLUMN_GET_VIOLATIONS_WITH_INSPECTIONS.PLATE_DETAIL.toString())) { main.addOrder(Order.desc("vi.vehiclePlateNumber").ignoreCase()); main.addOrder(Order.desc("vi.vehiclePlateCategory").ignoreCase()); main.addOrder(Order.desc("vi.vehiclePlateCode").ignoreCase()); main.addOrder(Order.desc("vi.vehiclePlateSource").ignoreCase()); } else main.addOrder(Order.desc(columnAttrib).ignoreCase()); } else { if (paginationValues.getOrderBy().equalsIgnoreCase( SORT_COLUMN_GET_VIOLATIONS_WITH_INSPECTIONS.PLATE_DETAIL.toString())) { main.addOrder(Order.asc("vi.vehiclePlateNumber").ignoreCase()); main.addOrder(Order.asc("vi.vehiclePlateCategory").ignoreCase()); main.addOrder(Order.asc("vi.vehiclePlateCode").ignoreCase()); main.addOrder(Order.asc("vi.vehiclePlateSource").ignoreCase()); } else main.addOrder(Order.asc(columnAttrib).ignoreCase()); } } } else { main.addOrder(Order.desc("v.violationId").ignoreCase()); } violations = main.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list(); logger.info("getViolationsWithInspections -- END"); return violations; } catch (Exception ex) { logger.error("An error occured in getViolationsWithInspections()"); throw new VSDDataAccessException(ex.getMessage(), ex); } } /** * * Gets count of getViolationsWithInspections results * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @return Long * @throws VSDDataAccessException */ public Long getCountForGetViolationsWithInspections(DataServiceContext dsContext, boolean retrieveArabicData) throws VSDDataAccessException { logger.info("getCountForGetViolationsWithInspections -- START"); try { Session session = (Session) dsContext.getInternalContext(); Criteria crit = session.createCriteria(VsdViolation.class, "v") .add(Restrictions.eq("v.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdViolationStatus", "vs", Criteria.LEFT_JOIN, Restrictions.eq("vs.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdSeverityLevel", "vSev", Criteria.LEFT_JOIN, Restrictions.eq("vSev.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdChannelPartInst", "cpi", Criteria.LEFT_JOIN, Restrictions.eq("cpi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdLocation", "l", Criteria.LEFT_JOIN, Restrictions.eq("l.isDeleted", IDataService.BOOL_FALSE)) .addOrder(Order.desc("v.violationId")) .setProjection(Projections.countDistinct("v.violationId")); Long count = (Long) crit.uniqueResult(); logger.info("getCountForGetViolationsWithInspections -- END"); return count; } catch (Exception ex) { logger.error("An error occured in getCountForGetViolationsWithInspections()"); throw new VSDDataAccessException(ex.getMessage(), ex); } } /** * * get List of OpenViolations By InspectionDate Sorted By SeverityLevelId * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @return List<VsdViolation> * @throws VSDException */ public List<VsdViolation> getOpenViolationsByInspectionDateSortedBySeverityLevelId( final DataServiceContext dsContext, boolean retrieveArabicData, Date inspectionDate) throws VSDDataAccessException { logger.info("getOpenViolationsByInspectionDateSortedBySeverityLevelId -- START"); List<VsdViolation> violations = null; try { Calendar startDate = Calendar.getInstance(); startDate.setTime(inspectionDate); 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(inspectionDate); 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(); violations = session.createCriteria(VsdViolation.class, "v") .add(Restrictions.eq("v.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("v.vsdViolationStatus.violationStatusId", Constant.VIOLATION_STATUS_OPEN)) .createCriteria("v.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.between("i.inspectionTimestamp", startDate.getTime(), endDate.getTime())) .createCriteria("i.vsdPlateConditionByFrontPlateConditionId", "fpc", Criteria.LEFT_JOIN, Restrictions.eq("fpc.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdPlateConditionByRearPlateConditionId", "rpc", Criteria.LEFT_JOIN, Restrictions.eq("rpc.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vi.vsdDriverInfos", "di", Criteria.LEFT_JOIN, Restrictions.eq("di.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vi.vsdOwnerInfos", "oi", Criteria.LEFT_JOIN, Restrictions.eq("oi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdChannelDefects", "cd", Criteria.LEFT_JOIN, Restrictions.eq("cd.isDeleted", IDataService.BOOL_FALSE)) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY) .addOrder(Order.desc("v.vsdSeverityLevel.severityLevelId")).list(); } catch (Exception ex) { logger.error("An error occured in getOpenViolationsByInspectionDateSortedBySeverityLevelId()"); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getOpenViolationsByInspectionDateSortedBySeverityLevelId -- END"); return violations; } /** * * get List of OpenViolations By InspectionDate and SeverityLevelId * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param startDate * @param endDate * @param severityLevelId * @return List<VsdViolation> * @throws VSDException */ public List<VsdViolation> getOpenViolationsByInspectionDateAndSeverityLevelId( final DataServiceContext dsContext, boolean retrieveArabicData, Date startDate, Date endDate, Long severityLevelId) throws VSDDataAccessException { logger.info("getOpenViolationsByInspectionDateAndSeverityLevelId -- START"); List<VsdViolation> violations = null; try { // Calendar startDate = Calendar.getInstance(); // startDate.setTime(inspectionDate); // 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(inspectionDate); // 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(); violations = session.createCriteria(VsdViolation.class, "v") .add(Restrictions.eq("v.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdViolationStatus", "vs", Criteria.LEFT_JOIN, Restrictions.eq("vs.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("vs.violationStatusId", Constant.VIOLATION_STATUS_OPEN)) .createCriteria("v.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.between("i.inspectionTimestamp", startDate, endDate)) .createCriteria("i.vsdPlateConditionByFrontPlateConditionId", "fpc", Criteria.LEFT_JOIN, Restrictions.eq("fpc.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdPlateConditionByRearPlateConditionId", "rpc", Criteria.LEFT_JOIN, Restrictions.eq("rpc.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vi.vsdDriverInfos", "di", Criteria.LEFT_JOIN, Restrictions.eq("di.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vi.vsdOwnerInfos", "oi", Criteria.LEFT_JOIN, Restrictions.eq("oi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdChannelDefects", "cd", Criteria.LEFT_JOIN, Restrictions.eq("cd.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdSeverityLevel", "sl", Criteria.LEFT_JOIN, Restrictions.eq("sl.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("sl.severityLevelId", severityLevelId)) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list(); } catch (Exception ex) { logger.error("An error occured in getOpenViolationsByInspectionDateAndSeverityLevelId()"); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getOpenViolationsByInspectionDateAndSeverityLevelId -- END"); return violations; } /** * * get List of OpenViolations By its inspectionTimestamp and SeverityLevelId * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param startDate * @param endDate * @param severityLevelId * @return List<VsdViolation> * @throws VSDException */ public List<VsdViolation> getOpenViolationsByUpdatedTimestampAndSeverityLevelId( final DataServiceContext dsContext, boolean retrieveArabicData, Date startDate, Date endDate, Long severityLevelId) throws VSDDataAccessException { logger.info("getOpenViolationsByUpdatedTimestampAndSeverityLevelId -- START"); List<VsdViolation> violations = null; try { // Calendar startDate = Calendar.getInstance(); // startDate.setTime(inspectionDate); // 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(inspectionDate); // 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(); violations = session.createCriteria(VsdViolation.class, "v") .add(Restrictions.eq("v.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.between("v.upatedTimestamp", startDate, endDate)) .createCriteria("v.vsdViolationStatus", "vs", Criteria.LEFT_JOIN, Restrictions.eq("vs.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("vs.violationStatusId", Constant.VIOLATION_STATUS_OPEN)) .createCriteria("v.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdPlateConditionByFrontPlateConditionId", "fpc", Criteria.LEFT_JOIN, Restrictions.eq("fpc.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdPlateConditionByRearPlateConditionId", "rpc", Criteria.LEFT_JOIN, Restrictions.eq("rpc.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vi.vsdDriverInfos", "di", Criteria.LEFT_JOIN, Restrictions.eq("di.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vi.vsdOwnerInfos", "oi", Criteria.LEFT_JOIN, Restrictions.eq("oi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdChannelDefects", "cd", Criteria.LEFT_JOIN, Restrictions.eq("cd.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdSeverityLevel", "sl", Criteria.LEFT_JOIN, Restrictions.eq("sl.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("sl.severityLevelId", severityLevelId)) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list(); } catch (Exception ex) { logger.error("An error occured in getOpenViolationsByUpdatedTimestampAndSeverityLevelId()"); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getOpenViolationsByUpdatedTimestampAndSeverityLevelId -- END"); return violations; } /** * * get List of cancel Violations By InspectionDate and SeverityLevelId * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param startDate * @param endDate * @param severityLevelId * @return List<VsdViolation> * @throws VSDException */ public List<VsdViolation> getCancelViolationsByInspectionDateAndSeverityLevelId( final DataServiceContext dsContext, boolean retrieveArabicData, Date startDate, Date endDate, Long severityLevelId) throws VSDDataAccessException { logger.info("getCancelViolationsByInspectionDateAndSeverityLevelId -- START"); List<VsdViolation> violations = null; try { Session session = (Session) dsContext.getInternalContext(); violations = session.createCriteria(VsdViolation.class, "v") .add(Restrictions.eq("v.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdViolationStatus", "vs", Criteria.LEFT_JOIN, Restrictions.eq("vs.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("vs.violationStatusId", Constant.VIOLATION_STATUS_CANCELLED)) .createCriteria("v.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.between("i.inspectionTimestamp", startDate, endDate)) .createCriteria("i.vsdPlateConditionByFrontPlateConditionId", "fpc", Criteria.LEFT_JOIN, Restrictions.eq("fpc.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdPlateConditionByRearPlateConditionId", "rpc", Criteria.LEFT_JOIN, Restrictions.eq("rpc.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vi.vsdDriverInfos", "di", Criteria.LEFT_JOIN, Restrictions.eq("di.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vi.vsdOwnerInfos", "oi", Criteria.LEFT_JOIN, Restrictions.eq("oi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdChannelDefects", "cd", Criteria.LEFT_JOIN, Restrictions.eq("cd.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdSeverityLevel", "sl", Criteria.LEFT_JOIN, Restrictions.eq("sl.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("sl.severityLevelId", severityLevelId)) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list(); } catch (Exception ex) { logger.error("An error occured in getCancelViolationsByInspectionDateAndSeverityLevelId()"); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getCancelViolationsByInspectionDateAndSeverityLevelId -- END"); return violations; } /** * * get List of cancel Violations By its updated timestamp and SeverityLevelId * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param startDate * @param endDate * @return List<VsdViolation> * @throws VSDException */ public List<VsdViolation> getCancelViolationsByUpdatedTimestamp(final DataServiceContext dsContext, boolean retrieveArabicData, Date startDate, Date endDate) throws VSDDataAccessException { logger.info("getCancelViolationsByUpdatedTimestamp -- START"); List<VsdViolation> violations = null; try { Session session = (Session) dsContext.getInternalContext(); violations = session.createCriteria(VsdViolation.class, "v") .add(Restrictions.eq("v.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.between("v.upatedTimestamp", startDate, endDate)) .createCriteria("v.vsdViolationStatus", "vs", Criteria.LEFT_JOIN, Restrictions.eq("vs.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("vs.violationStatusId", Constant.VIOLATION_STATUS_CANCELLED)) .createCriteria("v.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdPlateConditionByFrontPlateConditionId", "fpc", Criteria.LEFT_JOIN, Restrictions.eq("fpc.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdPlateConditionByRearPlateConditionId", "rpc", Criteria.LEFT_JOIN, Restrictions.eq("rpc.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vi.vsdDriverInfos", "di", Criteria.LEFT_JOIN, Restrictions.eq("di.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vi.vsdOwnerInfos", "oi", Criteria.LEFT_JOIN, Restrictions.eq("oi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdChannelDefects", "cd", Criteria.LEFT_JOIN, Restrictions.eq("cd.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdSeverityLevel", "sl", Criteria.LEFT_JOIN, Restrictions.eq("sl.isDeleted", IDataService.BOOL_FALSE)) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list(); } catch (Exception ex) { logger.error("An error occured in getCancelViolationsByUpdatedTimestamp()"); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getCancelViolationsByUpdatedTimestamp -- END"); return violations; } /** * * Get violations by tradeLicenceNumber and chassisNumber * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param vehicleChassisNumber * @param tradeLicenseNumber * @return List<VsdViolation> * @throws VSDException */ public List<VsdViolation> getViolationsByVehicleChassisNumberAndTradeLicenseNumber( final DataServiceContext dsContext, boolean retrieveArabicData, String vehicleChassisNumber, String tradeLicenseNumber) throws VSDDataAccessException { logger.info("getViolationsByVehicleChassisNumberAndTradeLicenseNumber -- START"); List<VsdViolation> violations = null; try { Session session = (Session) dsContext.getInternalContext(); violations = session.createCriteria(VsdViolation.class, "v") .add(Restrictions.eq("v.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("vi.vehicleChassisNumber", vehicleChassisNumber).ignoreCase()) .createCriteria("vi.vsdOwnerInfos", "oi", Criteria.LEFT_JOIN, Restrictions.eq("oi.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("oi.tradeLicenseNumber", tradeLicenseNumber).ignoreCase()) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list(); } catch (Exception ex) { logger.error("An error occured in getViolationsByVehicleChassisNumberAndTradeLicenseNumber()"); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getViolationsByVehicleChassisNumberAndTradeLicenseNumber -- END"); return violations; } /** * * Get violations by tradeLicenceNumber and chassisNumberList * only Violations dto is populated. * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param vehicleChassisNumber * @param tradeLicenseNumber * @return List<VsdViolation> * @throws VSDException */ public List<VsdViolation> getViolationsByTradeLicenseNumberAndVehicleChassisNumberList( final DataServiceContext dsContext, boolean retrieveArabicData, String tradeLicenseNumber, List<String> vehicleChassisNumberList) throws VSDDataAccessException { logger.info("getViolationsByVehicleChassisNumberAndTradeLicenseNumber -- START"); List<VsdViolation> violations = null; try { Session session = (Session) dsContext.getInternalContext(); // Making HQL because ignoreCase not avaiable for IN // Criteria criteria = session.createCriteria(VsdViolation.class, "v") // .add(Restrictions.eq("v.isDeleted", IDataService.BOOL_FALSE)) // .createCriteria("v.vsdInspections", "i", Criteria.INNER_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) // .createCriteria("i.vsdVehicleInfo", "vi", Criteria.INNER_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) // .add(Restrictions.in("vi.vehicleChassisNumber", vehicleChassisNumberList).ignoreCase()) //ignoreCase not avaiable for IN // .createCriteria("vi.vsdOwnerInfos", "oi", Criteria.LEFT_JOIN, Restrictions.eq("oi.isDeleted", IDataService.BOOL_FALSE)) // .add(Restrictions.eq("oi.tradeLicenseNumber", tradeLicenseNumber).ignoreCase()); if (vehicleChassisNumberList.size() < 1) throw new IllegalArgumentException(); StringBuffer commaSeperatedInClause = new StringBuffer(128); commaSeperatedInClause.append("("); Iterator iterator = vehicleChassisNumberList.iterator(); while (iterator.hasNext()) { String vehicleChassisNumber = (String) iterator.next(); commaSeperatedInClause.append("'"); commaSeperatedInClause.append(vehicleChassisNumber.toLowerCase()); commaSeperatedInClause.append("'"); if (iterator.hasNext()) commaSeperatedInClause.append(", "); } commaSeperatedInClause.append(")"); StringBuffer hql = new StringBuffer(128); hql.append("SELECT v FROM VsdViolation v ") .append("INNER JOIN v.vsdInspections as i WITH i.isDeleted = '").append(IDataService.BOOL_FALSE) .append("' ").append("INNER JOIN i.vsdVehicleInfo as vi WITH vi.isDeleted = '") .append(IDataService.BOOL_FALSE).append("' ") .append("INNER JOIN vi.vsdOwnerInfos as oi WITH oi.isDeleted = '") .append(IDataService.BOOL_FALSE).append("' ").append("WHERE v.isDeleted = '") .append(IDataService.BOOL_FALSE).append("' ").append("AND lower(oi.tradeLicenseNumber) = '") .append(tradeLicenseNumber.toLowerCase()).append("' ") .append("AND lower(vi.vehicleChassisNumber) IN ").append(commaSeperatedInClause).append(" "); violations = session.createQuery(hql.toString()).list(); violations = new ArrayList(new LinkedHashSet(violations)); } catch (Exception ex) { logger.error("An error occured in getViolationsByVehicleChassisNumberAndTradeLicenseNumber()"); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getViolationsByVehicleChassisNumberAndTradeLicenseNumber -- END"); return violations; } /** * * Get violations with details by tradeLicenceNumber and chassisNumber * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param vehicleChassisNumber * @param tradeLicenseNumber * @return List<VsdViolation> * @throws VSDException */ public List<VsdViolation> getViolationsWithDetailsByVehicleChassisNumberAndTradeLicenseNumber( final DataServiceContext dsContext, boolean retrieveArabicData, String vehicleChassisNumber, String tradeLicenseNumber) throws VSDDataAccessException { logger.info("getViolationsWithDetailsByVehicleChassisNumberAndTradeLicenseNumber -- START"); List<VsdViolation> violations = null; try { Session session = (Session) dsContext.getInternalContext(); violations = session.createCriteria(VsdViolation.class, "v") .add(Restrictions.eq("v.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("vi.vehicleChassisNumber", vehicleChassisNumber).ignoreCase()) .createCriteria("vi.vsdOwnerInfos", "oi", Criteria.LEFT_JOIN, Restrictions.eq("oi.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("oi.tradeLicenseNumber", tradeLicenseNumber).ignoreCase()) .createCriteria("v.vsdViolationStatus", "vs", Criteria.LEFT_JOIN, Restrictions.eq("vs.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdChannelDefects", "cd", Criteria.LEFT_JOIN, Restrictions.eq("cd.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdChannelDefectTests", "cdt", Criteria.LEFT_JOIN, Restrictions.eq("cdt.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cdt.vsdChannelTest", "ct", Criteria.LEFT_JOIN, Restrictions.eq("ct.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("ct.vsdChannelPartInst", "cpi", Criteria.LEFT_JOIN, Restrictions.eq("cpi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("ct.vsdTestType", "tt", Criteria.LEFT_JOIN, Restrictions.eq("tt.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdDefect", "d", Criteria.LEFT_JOIN, Restrictions.eq("d.isDeleted", IDataService.BOOL_FALSE)) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list(); } catch (Exception ex) { logger.error( "An error occured in getViolationsWithDetailsByVehicleChassisNumberAndTradeLicenseNumber()"); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getViolationsWithDetailsByVehicleChassisNumberAndTradeLicenseNumber -- END"); return violations; } /** * * Gets all the violations for vehicles owned by the owner with the specified trade license number * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param tradeLicenseNumber * @return List<VsdViolation> * @throws VSDDataAccessException */ public List<VsdViolation> getViolationsByTradeLicenseNumber(final DataServiceContext dsContext, boolean retrieveArabicData, String tradeLicenseNumber) throws VSDDataAccessException { logger.info("getViolationsByTradeLicenseNumber -- START"); List<VsdViolation> violations = null; try { Session session = (Session) dsContext.getInternalContext(); violations = session.createCriteria(VsdViolation.class, "v") .add(Restrictions.eq("v.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vi.vsdOwnerInfos", "oi", Criteria.LEFT_JOIN, Restrictions.eq("oi.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("oi.tradeLicenseNumber", tradeLicenseNumber).ignoreCase()) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list(); logger.info("getViolationsByTradeLicenseNumber -- END"); return violations; } catch (Exception ex) { logger.error("An error occured in getViolationsByTradeLicenseNumber()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } } /** * * Gets all the violations for vehicles owned by the owner with the specified trade license number * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param tradeLicenseNumber * @return List<VsdViolation> * @throws VSDDataAccessException */ public List<VsdViolation> getViolationsByTraficFileNumber(final DataServiceContext dsContext, boolean retrieveArabicData, String traficFileNo) throws VSDDataAccessException { logger.info("getViolationsByTradeLicenseNumber -- START"); List<VsdViolation> violations = null; try { Session session = (Session) dsContext.getInternalContext(); violations = session.createCriteria(VsdViolation.class, "v") .add(Restrictions.eq("v.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vi.vsdOwnerInfos", "oi", Criteria.LEFT_JOIN, Restrictions.eq("oi.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("oi.trafficFileNumber", traficFileNo).ignoreCase()) .addOrder(Order.desc("v.reportedDate")).setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY) .list(); logger.info("getViolationsByTradeLicenseNumber -- END"); return violations; } catch (Exception ex) { logger.error("An error occured in getViolationsByTradeLicenseNumber()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } } /** * * Gets count for all the violations for vehicles owned by the owner with the specified trade license number * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param traficFileNo * @return Long * @throws VSDDataAccessException */ public Long getCountForViolationsByTraficFileNumber(final DataServiceContext dsContext, boolean retrieveArabicData, String traficFileNo) throws VSDDataAccessException { logger.info("getCountForViolationsByTraficFileNumber -- START"); Long violationsCount = null; try { Session session = (Session) dsContext.getInternalContext(); violationsCount = (Long) session.createCriteria(VsdViolation.class, "v") .add(Restrictions.eq("v.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vi.vsdOwnerInfos", "oi", Criteria.LEFT_JOIN, Restrictions.eq("oi.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("oi.trafficFileNumber", traficFileNo).ignoreCase()) .setProjection(Projections.countDistinct("v.violationId")).uniqueResult(); logger.info("getCountForViolationsByTraficFileNumber -- END"); return violationsCount; } catch (Exception ex) { logger.error("An error occured in getCountForViolationsByTraficFileNumber()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } } /** * * Gets all the violations for vehicles owned by the owner with the specified trade license number * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param param * @return List<VsdViolation> * @throws VSDDataAccessException */ public List<VsdViolation> getViolationsByTraficFileNumber(final DataServiceContext dsContext, boolean retrieveArabicData, String traficFileNo, PaginationParam param) throws VSDDataAccessException { logger.info("getViolationsByTraficFileNumber -- START"); List<VsdViolation> violations = null; try { Session session = (Session) dsContext.getInternalContext(); ProjectionList projectionList = Projections.projectionList(); projectionList.add(Projections.property("v.violationId"), "violationId"); projectionList.add(Projections.property("v.reportedDate"), "reportedDate"); violations = session.createCriteria(VsdViolation.class, "v") .add(Restrictions.eq("v.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vi.vsdOwnerInfos", "oi", Criteria.LEFT_JOIN, Restrictions.eq("oi.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("oi.trafficFileNumber", traficFileNo).ignoreCase()) .addOrder(Order.desc("v.reportedDate")).setProjection(Projections.distinct(projectionList)) .setFirstResult(param.getFirstResult().intValue()) .setMaxResults(param.getFetchedSize().intValue()) .setResultTransformer(new AliasToBeanResultTransformer(VsdViolation.class)).list(); if (violations == null || violations.size() == 0) return new ArrayList(); Iterator iterator = violations.iterator(); ArrayList innerQueryList = new ArrayList<Long>(); while (iterator.hasNext()) { VsdViolation violation = (VsdViolation) iterator.next(); innerQueryList.add(violation.getViolationId()); } violations = session.createCriteria(VsdViolation.class, "v") .add(Restrictions.eq("v.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vi.vsdOwnerInfos", "oi", Criteria.LEFT_JOIN, Restrictions.eq("oi.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("oi.trafficFileNumber", traficFileNo).ignoreCase()) .add(Restrictions.in("v.violationId", innerQueryList)).addOrder(Order.desc("v.reportedDate")) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list(); logger.info("getViolationsByTraficFileNumber -- END"); return violations; } catch (Exception ex) { logger.error("An error occured in getViolationsByTraficFileNumber()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } } /** * * Gets all the violations for vehicles owned by the owner with the specified trade license number * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param tradeLicenseNumber * @param paginationValues * @return List<VsdViolation> * @throws VSDDataAccessException */ public List<VsdViolation> getViolationsByTradeLicenseNumber(final DataServiceContext dsContext, boolean retrieveArabicData, String tradeLicenseNumber, PaginationParam paginationValues) throws VSDDataAccessException { logger.info("getViolationsByTradeLicenseNumber -- START"); List<VsdViolation> violations = null; try { Session session = (Session) dsContext.getInternalContext(); ProjectionList projectionList = Projections.projectionList(); projectionList.add(Projections.property("v.violationId"), "violationId"); Criteria crit = session.createCriteria(VsdViolation.class, "v") .add(Restrictions.eq("v.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vi.vsdOwnerInfos", "oi", Criteria.LEFT_JOIN, Restrictions.eq("oi.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("oi.tradeLicenseNumber", tradeLicenseNumber).ignoreCase()) .addOrder(Order.desc("v.violationId")).setProjection(Projections.distinct(projectionList)); crit.setResultTransformer(new AliasToBeanResultTransformer(VsdViolation.class)); if (paginationValues.getFirstResult() != null && paginationValues.getFirstResult().longValue() != -1) { crit.setFirstResult(paginationValues.getFirstResult().intValue()); } if (paginationValues.getFetchedSize() != null && paginationValues.getFetchedSize().longValue() != -1) { crit.setMaxResults(paginationValues.getFetchedSize().intValue()); } List list = crit.list(); logger.debug("list.size() : " + list.size()); Set resultSet = new HashSet(list); logger.debug("resultSet.size() : " + resultSet.size()); if (resultSet.size() == 0) return new ArrayList(); Iterator iterator = resultSet.iterator(); ArrayList innerQueryList = new ArrayList<Long>(); while (iterator.hasNext()) { VsdViolation violation = (VsdViolation) iterator.next(); innerQueryList.add(violation.getViolationId()); } Criteria main = session.createCriteria(VsdViolation.class, "v") .add(Property.forName("v.violationId").in(innerQueryList)) .add(Restrictions.eq("v.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vi.vsdOwnerInfos", "oi", Criteria.LEFT_JOIN, Restrictions.eq("oi.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("oi.tradeLicenseNumber", tradeLicenseNumber).ignoreCase()) .addOrder(Order.desc("v.violationId")); violations = main.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list(); logger.info("getViolationsByTradeLicenseNumber -- END"); return violations; } catch (Exception ex) { logger.error("An error occured in getViolationsByTradeLicenseNumber()"); throw new VSDDataAccessException(ex.getMessage(), ex); } } /** * * Gets the count for getViolationsByTradeLicenseNumber * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param tradeLicenseNumber * @return Long * @throws VSDDataAccessException */ public Long getCountForGetViolationsByTradeLicenseNumber(final DataServiceContext dsContext, boolean retrieveArabicData, String tradeLicenseNumber) throws VSDDataAccessException { logger.info("getCountForGetViolationsByTradeLicenseNumber -- START"); try { Session session = (Session) dsContext.getInternalContext(); Criteria crit = session.createCriteria(VsdViolation.class, "v") .add(Restrictions.eq("v.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vi.vsdOwnerInfos", "oi", Criteria.LEFT_JOIN, Restrictions.eq("oi.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("oi.tradeLicenseNumber", tradeLicenseNumber).ignoreCase()) .addOrder(Order.desc("v.violationId")) .setProjection(Projections.countDistinct("v.violationId")); Long count = (Long) crit.uniqueResult(); logger.info("getCountForGetViolationsByTradeLicenseNumber -- END"); return count; } catch (Exception ex) { logger.error("An error occured in getCountForGetViolationsByTradeLicenseNumber()"); throw new VSDDataAccessException(ex.getMessage(), ex); } } /** * * get Violations By ChassisNumber * associated overloaded method * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param trafficFileNumber * @param vehicleChassisNumber * @return List<Violation> * @throws VSDDataAccessException */ public List<VsdViolation> getViolationsByChassisNumber(final DataServiceContext dsContext, boolean retrieveArabicData, String vehicleChassisNumber, int maxResults) throws VSDDataAccessException { logger.info("getViolationsByChassisNumber -- START"); List<VsdViolation> violations; try { Session session = (Session) dsContext.getInternalContext(); ProjectionList projectionList = Projections.projectionList(); projectionList.add(Projections.property("violation.violationId"), "violationId"); projectionList.add(Projections.property("violation.reportedDate"), "reportedDate"); Criteria projectionCriteria = session.createCriteria(VsdViolation.class, "violation") .add(Restrictions.eq("violation.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violation.vsdViolationStatus", "vstat", Criteria.LEFT_JOIN, Restrictions.eq("vstat.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violation.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("vi.vehicleChassisNumber", vehicleChassisNumber).ignoreCase()) .createCriteria("vi.vsdOwnerInfos", "oi", Criteria.LEFT_JOIN, Restrictions.eq("oi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdLocation", "loc", Criteria.LEFT_JOIN, Restrictions.eq("loc.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("loc.vsdLocation", "loc2", Criteria.LEFT_JOIN, Restrictions.eq("loc2.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violation.vsdChannelDefects", "cd", Criteria.LEFT_JOIN, Restrictions.eq("cd.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdDefect", "d", Criteria.LEFT_JOIN, Restrictions.eq("d.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdChannelDefectFines", "cdf", Criteria.LEFT_JOIN, Restrictions.eq("cdf.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdChannelPartInst", "cpi", Criteria.LEFT_JOIN, Restrictions.eq("cpi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cpi.vsdChannelPartner", "cp", Criteria.LEFT_JOIN, Restrictions.eq("cp.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cp.vsdPartner", "p", Criteria.LEFT_JOIN, Restrictions.eq("p.isDeleted", IDataService.BOOL_FALSE)) .setProjection(Projections.distinct(projectionList)) .addOrder(Order.desc("violation.reportedDate")) .setResultTransformer(new AliasToBeanResultTransformer(VsdViolation.class)) .setMaxResults(maxResults); List list = projectionCriteria.list(); logger.debug("list.size() : " + list.size()); Set resultSet = new HashSet(list); logger.debug("resultSet.size() : " + resultSet.size()); if (resultSet.size() == 0) return new ArrayList(); Iterator iterator = resultSet.iterator(); ArrayList innerQueryList = new ArrayList<Long>(); while (iterator.hasNext()) { VsdViolation vsdViolation = (VsdViolation) iterator.next(); innerQueryList.add(vsdViolation.getViolationId()); } violations = session.createCriteria(VsdViolation.class, "violation") .add(Restrictions.eq("violation.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violation.vsdViolationStatus", "vstat", Criteria.LEFT_JOIN, Restrictions.eq("vstat.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violation.vsdSeverityLevel", "sl", Criteria.LEFT_JOIN, Restrictions.eq("sl.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violation.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("vi.vehicleChassisNumber", vehicleChassisNumber).ignoreCase()) .createCriteria("vi.vsdOwnerInfos", "oi", Criteria.LEFT_JOIN, Restrictions.eq("oi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdLocation", "loc", Criteria.LEFT_JOIN, Restrictions.eq("loc.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("loc.vsdLocation", "loc2", Criteria.LEFT_JOIN, Restrictions.eq("loc2.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violation.vsdChannelDefects", "cd", Criteria.LEFT_JOIN, Restrictions.eq("cd.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdDefect", "d", Criteria.LEFT_JOIN, Restrictions.eq("d.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdChannelDefectFines", "cdf", Criteria.LEFT_JOIN, Restrictions.eq("cdf.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdChannelPartInst", "cpi", Criteria.LEFT_JOIN, Restrictions.eq("cpi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cpi.vsdChannelPartner", "cp", Criteria.LEFT_JOIN, Restrictions.eq("cp.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cp.vsdPartner", "p", Criteria.LEFT_JOIN, Restrictions.eq("p.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.in("violation.violationId", innerQueryList)) .addOrder(Order.desc("violation.reportedDate")) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list(); logger.debug("violations.size() : " + violations.size()); } catch (Exception ex) { logger.error("An error occured in getViolationsByChassisNumber()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getViolationsByChassisNumber -- END"); return violations; } /** * * get Violations By VehiclePlate for webservice * associated overloaded method * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param trafficFileNumber * @param VehiclePlate * @return List<Violation> * @throws VSDDataAccessException */ public List<VsdViolation> getViolationsByVehiclePlateForWS(final DataServiceContext dsContext, boolean retrieveArabicData, VehiclePlate vehiclePlate, int maxResults) throws VSDDataAccessException { logger.info("getViolationsByVehiclePlateForWS -- START"); List<VsdViolation> violations; try { Session session = (Session) dsContext.getInternalContext(); ProjectionList projectionList = Projections.projectionList(); projectionList.add(Projections.property("violation.violationId"), "violationId"); projectionList.add(Projections.property("violation.reportedDate"), "reportedDate"); Criteria projectionCriteria = session.createCriteria(VsdViolation.class, "violation") .add(Restrictions.eq("violation.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violation.vsdViolationStatus", "vstat", Criteria.LEFT_JOIN, Restrictions.eq("vstat.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violation.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("vi.vehiclePlateCategory", vehiclePlate.getPlateCategory()).ignoreCase()) .add(Restrictions.eq("vi.vehiclePlateCode", vehiclePlate.getPlateCode()).ignoreCase()) .add(Restrictions.eq("vi.vehiclePlateSource", vehiclePlate.getPlateSource()).ignoreCase()) .add(Restrictions.eq("vi.vehiclePlateNumber", vehiclePlate.getPlateNumber()).ignoreCase()) .createCriteria("vi.vsdOwnerInfos", "oi", Criteria.LEFT_JOIN, Restrictions.eq("oi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdLocation", "loc", Criteria.LEFT_JOIN, Restrictions.eq("loc.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("loc.vsdLocation", "loc2", Criteria.LEFT_JOIN, Restrictions.eq("loc2.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violation.vsdChannelDefects", "cd", Criteria.LEFT_JOIN, Restrictions.eq("cd.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdDefect", "d", Criteria.LEFT_JOIN, Restrictions.eq("d.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdChannelDefectFines", "cdf", Criteria.LEFT_JOIN, Restrictions.eq("cdf.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdChannelPartInst", "cpi", Criteria.LEFT_JOIN, Restrictions.eq("cpi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cpi.vsdChannelPartner", "cp", Criteria.LEFT_JOIN, Restrictions.eq("cp.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cp.vsdPartner", "p", Criteria.LEFT_JOIN, Restrictions.eq("p.isDeleted", IDataService.BOOL_FALSE)) .setProjection(Projections.distinct(projectionList)) .addOrder(Order.desc("violation.reportedDate")) .setResultTransformer(new AliasToBeanResultTransformer(VsdViolation.class)) .setMaxResults(maxResults); List list = projectionCriteria.list(); logger.debug("list.size() : " + list.size()); Set resultSet = new HashSet(list); logger.debug("resultSet.size() : " + resultSet.size()); if (resultSet.size() == 0) return new ArrayList(); Iterator iterator = resultSet.iterator(); ArrayList innerQueryList = new ArrayList<Long>(); while (iterator.hasNext()) { VsdViolation vsdViolation = (VsdViolation) iterator.next(); innerQueryList.add(vsdViolation.getViolationId()); } violations = session.createCriteria(VsdViolation.class, "violation") .add(Restrictions.eq("violation.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violation.vsdViolationStatus", "vstat", Criteria.LEFT_JOIN, Restrictions.eq("vstat.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violation.vsdSeverityLevel", "sl", Criteria.LEFT_JOIN, Restrictions.eq("sl.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violation.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("vi.vehiclePlateCategory", vehiclePlate.getPlateCategory()).ignoreCase()) .add(Restrictions.eq("vi.vehiclePlateCode", vehiclePlate.getPlateCode()).ignoreCase()) .add(Restrictions.eq("vi.vehiclePlateSource", vehiclePlate.getPlateSource()).ignoreCase()) .add(Restrictions.eq("vi.vehiclePlateNumber", vehiclePlate.getPlateNumber()).ignoreCase()) .createCriteria("vi.vsdOwnerInfos", "oi", Criteria.LEFT_JOIN, Restrictions.eq("oi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdLocation", "loc", Criteria.LEFT_JOIN, Restrictions.eq("loc.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("loc.vsdLocation", "loc2", Criteria.LEFT_JOIN, Restrictions.eq("loc2.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violation.vsdChannelDefects", "cd", Criteria.LEFT_JOIN, Restrictions.eq("cd.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdDefect", "d", Criteria.LEFT_JOIN, Restrictions.eq("d.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdChannelDefectFines", "cdf", Criteria.LEFT_JOIN, Restrictions.eq("cdf.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdChannelPartInst", "cpi", Criteria.LEFT_JOIN, Restrictions.eq("cpi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cpi.vsdChannelPartner", "cp", Criteria.LEFT_JOIN, Restrictions.eq("cp.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cp.vsdPartner", "p", Criteria.LEFT_JOIN, Restrictions.eq("p.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.in("violation.violationId", innerQueryList)) .addOrder(Order.desc("violation.reportedDate")) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list(); logger.debug("violations.size() : " + violations.size()); } catch (Exception ex) { logger.error("An error occured in getViolationsByVehiclePlateForWS()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getViolationsByVehiclePlateForWS -- END"); return violations; } /** * * get Violations By VehiclePlate for webservice * associated overloaded method * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param trafficFileNumber * @param VehiclePlate * @param firstResult * @param maxResults * @return List<Violation> * @throws VSDDataAccessException */ public List<VsdViolation> getViolationsByVehiclePlateForWS(final DataServiceContext dsContext, boolean retrieveArabicData, VehiclePlate vehiclePlate, int firstResult, int maxResults) throws VSDDataAccessException { logger.info("getViolationsByVehiclePlateForWS -- START"); List<VsdViolation> violations; try { Session session = (Session) dsContext.getInternalContext(); ProjectionList projectionList = Projections.projectionList(); projectionList.add(Projections.property("violation.violationId"), "violationId"); projectionList.add(Projections.property("violation.reportedDate"), "reportedDate"); Criteria projectionCriteria = session.createCriteria(VsdViolation.class, "violation") .add(Restrictions.eq("violation.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violation.vsdViolationStatus", "vstat", Criteria.LEFT_JOIN, Restrictions.eq("vstat.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violation.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("vi.vehiclePlateCategory", vehiclePlate.getPlateCategory()).ignoreCase()) .add(Restrictions.eq("vi.vehiclePlateCode", vehiclePlate.getPlateCode()).ignoreCase()) .add(Restrictions.eq("vi.vehiclePlateSource", vehiclePlate.getPlateSource()).ignoreCase()) .add(Restrictions.eq("vi.vehiclePlateNumber", vehiclePlate.getPlateNumber()).ignoreCase()) .createCriteria("vi.vsdOwnerInfos", "oi", Criteria.LEFT_JOIN, Restrictions.eq("oi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdLocation", "loc", Criteria.LEFT_JOIN, Restrictions.eq("loc.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("loc.vsdLocation", "loc2", Criteria.LEFT_JOIN, Restrictions.eq("loc2.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violation.vsdChannelDefects", "cd", Criteria.LEFT_JOIN, Restrictions.eq("cd.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdDefect", "d", Criteria.LEFT_JOIN, Restrictions.eq("d.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdChannelDefectFines", "cdf", Criteria.LEFT_JOIN, Restrictions.eq("cdf.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdChannelPartInst", "cpi", Criteria.LEFT_JOIN, Restrictions.eq("cpi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cpi.vsdChannelPartner", "cp", Criteria.LEFT_JOIN, Restrictions.eq("cp.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cp.vsdPartner", "p", Criteria.LEFT_JOIN, Restrictions.eq("p.isDeleted", IDataService.BOOL_FALSE)) .setProjection(Projections.distinct(projectionList)) .addOrder(Order.desc("violation.reportedDate")) .setResultTransformer(new AliasToBeanResultTransformer(VsdViolation.class)) .setFirstResult(firstResult).setMaxResults(maxResults); List list = projectionCriteria.list(); logger.debug("list.size() : " + list.size()); Set resultSet = new HashSet(list); logger.debug("resultSet.size() : " + resultSet.size()); if (resultSet.size() == 0) return new ArrayList(); Iterator iterator = resultSet.iterator(); ArrayList innerQueryList = new ArrayList<Long>(); while (iterator.hasNext()) { VsdViolation vsdViolation = (VsdViolation) iterator.next(); innerQueryList.add(vsdViolation.getViolationId()); } violations = session.createCriteria(VsdViolation.class, "violation") .add(Restrictions.eq("violation.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violation.vsdViolationStatus", "vstat", Criteria.LEFT_JOIN, Restrictions.eq("vstat.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violation.vsdSeverityLevel", "sl", Criteria.LEFT_JOIN, Restrictions.eq("sl.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violation.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("vi.vehiclePlateCategory", vehiclePlate.getPlateCategory()).ignoreCase()) .add(Restrictions.eq("vi.vehiclePlateCode", vehiclePlate.getPlateCode()).ignoreCase()) .add(Restrictions.eq("vi.vehiclePlateSource", vehiclePlate.getPlateSource()).ignoreCase()) .add(Restrictions.eq("vi.vehiclePlateNumber", vehiclePlate.getPlateNumber()).ignoreCase()) .createCriteria("vi.vsdOwnerInfos", "oi", Criteria.LEFT_JOIN, Restrictions.eq("oi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdLocation", "loc", Criteria.LEFT_JOIN, Restrictions.eq("loc.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("loc.vsdLocation", "loc2", Criteria.LEFT_JOIN, Restrictions.eq("loc2.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violation.vsdChannelDefects", "cd", Criteria.LEFT_JOIN, Restrictions.eq("cd.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdDefect", "d", Criteria.LEFT_JOIN, Restrictions.eq("d.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdChannelDefectFines", "cdf", Criteria.LEFT_JOIN, Restrictions.eq("cdf.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdChannelPartInst", "cpi", Criteria.LEFT_JOIN, Restrictions.eq("cpi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cpi.vsdChannelPartner", "cp", Criteria.LEFT_JOIN, Restrictions.eq("cp.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cp.vsdPartner", "p", Criteria.LEFT_JOIN, Restrictions.eq("p.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.in("violation.violationId", innerQueryList)) .addOrder(Order.desc("violation.reportedDate")) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list(); logger.debug("violations.size() : " + violations.size()); } catch (Exception ex) { logger.error("An error occured in getViolationsByVehiclePlateForWS()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getViolationsByVehiclePlateForWS -- END"); return violations; } /** * * get count for Violations By ChassisNumber * associated overloaded method * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param vehicleChassisNumber * @return Long * @throws VSDDataAccessException */ public Long getCountForViolationsByChassisNumber(final DataServiceContext dsContext, boolean retrieveArabicData, String vehicleChassisNumber) throws VSDDataAccessException { logger.info("getCountForViolationsByChassisNumber -- START"); Long violationsCount; try { Session session = (Session) dsContext.getInternalContext(); Criteria projectionCriteria = session.createCriteria(VsdViolation.class, "violation") .add(Restrictions.eq("violation.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violation.vsdViolationStatus", "vstat", Criteria.LEFT_JOIN, Restrictions.eq("vstat.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violation.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("vi.vehicleChassisNumber", vehicleChassisNumber).ignoreCase()) .createCriteria("vi.vsdOwnerInfos", "oi", Criteria.LEFT_JOIN, Restrictions.eq("oi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdLocation", "loc", Criteria.LEFT_JOIN, Restrictions.eq("loc.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("loc.vsdLocation", "loc2", Criteria.LEFT_JOIN, Restrictions.eq("loc2.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violation.vsdChannelDefects", "cd", Criteria.LEFT_JOIN, Restrictions.eq("cd.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdDefect", "d", Criteria.LEFT_JOIN, Restrictions.eq("d.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdChannelDefectFines", "cdf", Criteria.LEFT_JOIN, Restrictions.eq("cdf.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdChannelPartInst", "cpi", Criteria.LEFT_JOIN, Restrictions.eq("cpi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cpi.vsdChannelPartner", "cp", Criteria.LEFT_JOIN, Restrictions.eq("cp.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cp.vsdPartner", "p", Criteria.LEFT_JOIN, Restrictions.eq("p.isDeleted", IDataService.BOOL_FALSE)) .setProjection(Projections.countDistinct("violation.violationId")); violationsCount = (Long) projectionCriteria.uniqueResult(); logger.debug("violationsCount : " + violationsCount); } catch (Exception ex) { logger.error("An error occured in getCountForViolationsByChassisNumber()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getCountForViolationsByChassisNumber -- END"); return violationsCount; } /** * * get count for Violations By VehiclePlate (For Webservice) * associated overloaded method * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param vehiclePlate * @return Long * @throws VSDDataAccessException */ public Long getCountForViolationsByVehiclePlateForWS(final DataServiceContext dsContext, boolean retrieveArabicData, VehiclePlate vehiclePlate) throws VSDDataAccessException { logger.info("getCountForViolationsByVehiclePlateForWS -- START"); Long violationsCount; try { Session session = (Session) dsContext.getInternalContext(); Criteria projectionCriteria = session.createCriteria(VsdViolation.class, "violation") .add(Restrictions.eq("violation.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violation.vsdViolationStatus", "vstat", Criteria.LEFT_JOIN, Restrictions.eq("vstat.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violation.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("vi.vehiclePlateCategory", vehiclePlate.getPlateCategory()).ignoreCase()) .add(Restrictions.eq("vi.vehiclePlateCode", vehiclePlate.getPlateCode()).ignoreCase()) .add(Restrictions.eq("vi.vehiclePlateSource", vehiclePlate.getPlateSource()).ignoreCase()) .add(Restrictions.eq("vi.vehiclePlateNumber", vehiclePlate.getPlateNumber()).ignoreCase()) .createCriteria("vi.vsdOwnerInfos", "oi", Criteria.LEFT_JOIN, Restrictions.eq("oi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdLocation", "loc", Criteria.LEFT_JOIN, Restrictions.eq("loc.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("loc.vsdLocation", "loc2", Criteria.LEFT_JOIN, Restrictions.eq("loc2.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violation.vsdChannelDefects", "cd", Criteria.LEFT_JOIN, Restrictions.eq("cd.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdDefect", "d", Criteria.LEFT_JOIN, Restrictions.eq("d.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdChannelDefectFines", "cdf", Criteria.LEFT_JOIN, Restrictions.eq("cdf.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdChannelPartInst", "cpi", Criteria.LEFT_JOIN, Restrictions.eq("cpi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cpi.vsdChannelPartner", "cp", Criteria.LEFT_JOIN, Restrictions.eq("cp.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cp.vsdPartner", "p", Criteria.LEFT_JOIN, Restrictions.eq("p.isDeleted", IDataService.BOOL_FALSE)) .setProjection(Projections.countDistinct("violation.violationId")); violationsCount = (Long) projectionCriteria.uniqueResult(); logger.debug("violationsCount : " + violationsCount); } catch (Exception ex) { logger.error("An error occured in getCountForViolationsByVehiclePlateForWS()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getCountForViolationsByVehiclePlateForWS -- END"); return violationsCount; } /** * * get Violations By ChassisNumber * associated overloaded method * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param vehicleChassisNumber * @return List<Violation> * @throws VSDDataAccessException */ public List<VsdViolation> getViolationsByChassisNumber(final DataServiceContext dsContext, boolean retrieveArabicData, String vehicleChassisNumber) throws VSDDataAccessException { logger.info("getViolationsByChassisNumber -- START"); List<VsdViolation> violations; try { Session session = (Session) dsContext.getInternalContext(); Criteria criteria = session.createCriteria(VsdViolation.class, "violation") .add(Restrictions.eq("violation.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violation.vsdViolationStatus", "vstat", Criteria.LEFT_JOIN, Restrictions.eq("vstat.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violation.vsdSeverityLevel", "sl", Criteria.LEFT_JOIN, Restrictions.eq("sl.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violation.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("vi.vehicleChassisNumber", vehicleChassisNumber).ignoreCase()) .createCriteria("vi.vsdOwnerInfos", "oi", Criteria.LEFT_JOIN, Restrictions.eq("oi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdLocation", "loc", Criteria.LEFT_JOIN, Restrictions.eq("loc.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("loc.vsdLocation", "loc2", Criteria.LEFT_JOIN, Restrictions.eq("loc2.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violation.vsdChannelDefects", "cd", Criteria.LEFT_JOIN, Restrictions.eq("cd.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdDefect", "d", Criteria.LEFT_JOIN, Restrictions.eq("d.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdChannelDefectFines", "cdf", Criteria.LEFT_JOIN, Restrictions.eq("cdf.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdChannelPartInst", "cpi", Criteria.LEFT_JOIN, Restrictions.eq("cpi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cpi.vsdChannelPartner", "cp", Criteria.LEFT_JOIN, Restrictions.eq("cp.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cp.vsdPartner", "p", Criteria.LEFT_JOIN, Restrictions.eq("p.isDeleted", IDataService.BOOL_FALSE)) .addOrder(Order.desc("violation.reportedDate")) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY); violations = criteria.list(); logger.debug("violations.size() : " + violations.size()); } catch (Exception ex) { logger.error("An error occured in getViolationsByChassisNumber()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getViolationsByChassisNumber -- END"); return violations; } /** * * get Violations By PlateDetails (For Webservice) * associated overloaded method * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param VehiclePlate * @return List<Violation> * @throws VSDDataAccessException */ public List<VsdViolation> getViolationsByVehiclePlateForWS(final DataServiceContext dsContext, boolean retrieveArabicData, VehiclePlate vehiclePlate) throws VSDDataAccessException { logger.info("getViolationsByVehiclePlateForWS -- START"); List<VsdViolation> violations; try { Session session = (Session) dsContext.getInternalContext(); Criteria criteria = session.createCriteria(VsdViolation.class, "violation") .add(Restrictions.eq("violation.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violation.vsdViolationStatus", "vstat", Criteria.LEFT_JOIN, Restrictions.eq("vstat.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violation.vsdSeverityLevel", "sl", Criteria.LEFT_JOIN, Restrictions.eq("sl.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violation.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("vi.vehiclePlateCategory", vehiclePlate.getPlateCategory()).ignoreCase()) .add(Restrictions.eq("vi.vehiclePlateCode", vehiclePlate.getPlateCode()).ignoreCase()) .add(Restrictions.eq("vi.vehiclePlateSource", vehiclePlate.getPlateSource()).ignoreCase()) .add(Restrictions.eq("vi.vehiclePlateNumber", vehiclePlate.getPlateNumber()).ignoreCase()) .createCriteria("vi.vsdOwnerInfos", "oi", Criteria.LEFT_JOIN, Restrictions.eq("oi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdLocation", "loc", Criteria.LEFT_JOIN, Restrictions.eq("loc.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("loc.vsdLocation", "loc2", Criteria.LEFT_JOIN, Restrictions.eq("loc2.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violation.vsdChannelDefects", "cd", Criteria.LEFT_JOIN, Restrictions.eq("cd.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdDefect", "d", Criteria.LEFT_JOIN, Restrictions.eq("d.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdChannelDefectFines", "cdf", Criteria.LEFT_JOIN, Restrictions.eq("cdf.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdChannelPartInst", "cpi", Criteria.LEFT_JOIN, Restrictions.eq("cpi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cpi.vsdChannelPartner", "cp", Criteria.LEFT_JOIN, Restrictions.eq("cp.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cp.vsdPartner", "p", Criteria.LEFT_JOIN, Restrictions.eq("p.isDeleted", IDataService.BOOL_FALSE)) .addOrder(Order.desc("violation.reportedDate")) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY); violations = criteria.list(); logger.debug("violations.size() : " + violations.size()); } catch (Exception ex) { logger.error("An error occured in getViolationsByVehiclePlateForWS()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getViolationsByVehiclePlateForWS -- END"); return violations; } /** * * get Violations By TrafficFileNumber * getViolationsByTrafficFileNumber(dsContext, retrieveArabicData, trafficFileNumber) * getCountForViolationsByTrafficFileNumber(dsContext, retrieveArabicData, trafficFileNumber) * getViolationsByTrafficFileNumber(dsContext, retrieveArabicData, trafficFileNumber, maxResults, firstResult) * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param trafficFileNumber * @param maxResults * @param firstResult * @return List<Violation> * @throws VSDDataAccessException */ public List<VsdViolation> getViolationsByTrafficFileNumber(final DataServiceContext dsContext, boolean retrieveArabicData, String trafficFileNumber, int maxResults, int firstResult) throws VSDDataAccessException { logger.info("getViolationsByTrafficFileNumber -- START"); List<VsdViolation> violations; try { Session session = (Session) dsContext.getInternalContext(); ProjectionList projectionList = Projections.projectionList(); projectionList.add(Projections.property("violation.violationId"), "violationId"); projectionList.add(Projections.property("violation.reportedDate"), "reportedDate"); Criteria projectionCriteria = session.createCriteria(VsdViolation.class, "violation") .add(Restrictions.eq("violation.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violation.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vi.vsdOwnerInfos", "oi", Criteria.LEFT_JOIN, Restrictions.eq("oi.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("oi.trafficFileNumber", trafficFileNumber).ignoreCase()) .createCriteria("violation.vsdViolationStatus", "vs", Criteria.LEFT_JOIN, Restrictions.eq("vs.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdLocation", "l", Criteria.LEFT_JOIN, Restrictions.eq("l.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("l.vsdLocation", "pl", Criteria.LEFT_JOIN, Restrictions.eq("pl.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdChannelPartInst", "cpi", Criteria.LEFT_JOIN, Restrictions.eq("cpi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cpi.vsdChannelPartner", "cp", Criteria.LEFT_JOIN, Restrictions.eq("cp.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cp.vsdPartner", "p", Criteria.LEFT_JOIN, Restrictions.eq("p.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violation.vsdChannelDefects", "cd", Criteria.LEFT_JOIN, Restrictions.eq("cd.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdDefect", "d", Criteria.LEFT_JOIN, Restrictions.eq("d.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("d.vsdDefectCategory", "dc", Criteria.LEFT_JOIN, Restrictions.eq("dc.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdChannelDefectFines", "cdf", Criteria.LEFT_JOIN, Restrictions.eq("cdf.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cdf.vsdVCatDefSevFine", "vcdsf", Criteria.LEFT_JOIN, Restrictions.eq("vcdsf.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vcdsf.vsdPartnerFine", "pf", Criteria.LEFT_JOIN, Restrictions.eq("pf.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("pf.vsdPartner", "partner", Criteria.LEFT_JOIN, Restrictions.eq("partner.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("pf.vsdFine", "vsdFine", Criteria.LEFT_JOIN, Restrictions.eq("vsdFine.isDeleted", IDataService.BOOL_FALSE)) .setProjection(Projections.distinct(projectionList)) .addOrder(Order.desc("violation.reportedDate")) .setResultTransformer(new AliasToBeanResultTransformer(VsdViolation.class)) .setMaxResults(maxResults).setFirstResult(firstResult); List list = projectionCriteria.list(); logger.debug("list.size() : " + list.size()); Set resultSet = new HashSet(list); logger.debug("resultSet.size() : " + resultSet.size()); if (resultSet.size() == 0) return new ArrayList(); Iterator iterator = resultSet.iterator(); ArrayList innerQueryList = new ArrayList<Long>(); while (iterator.hasNext()) { VsdViolation vsdViolation = (VsdViolation) iterator.next(); innerQueryList.add(vsdViolation.getViolationId()); } violations = session.createCriteria(VsdViolation.class, "violation") .add(Restrictions.eq("violation.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violation.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vi.vsdOwnerInfos", "oi", Criteria.LEFT_JOIN, Restrictions.eq("oi.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("oi.trafficFileNumber", trafficFileNumber).ignoreCase()) .createCriteria("violation.vsdViolationStatus", "vs", Criteria.LEFT_JOIN, Restrictions.eq("vs.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdLocation", "l", Criteria.LEFT_JOIN, Restrictions.eq("l.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("l.vsdLocation", "pl", Criteria.LEFT_JOIN, Restrictions.eq("pl.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdChannelPartInst", "cpi", Criteria.LEFT_JOIN, Restrictions.eq("cpi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cpi.vsdChannelPartner", "cp", Criteria.LEFT_JOIN, Restrictions.eq("cp.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cp.vsdPartner", "p", Criteria.LEFT_JOIN, Restrictions.eq("p.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violation.vsdChannelDefects", "cd", Criteria.LEFT_JOIN, Restrictions.eq("cd.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdDefect", "d", Criteria.LEFT_JOIN, Restrictions.eq("d.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("d.vsdDefectCategory", "dc", Criteria.LEFT_JOIN, Restrictions.eq("dc.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdChannelDefectFines", "cdf", Criteria.LEFT_JOIN, Restrictions.eq("cdf.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cdf.vsdVCatDefSevFine", "vcdsf", Criteria.LEFT_JOIN, Restrictions.eq("vcdsf.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vcdsf.vsdPartnerFine", "pf", Criteria.LEFT_JOIN, Restrictions.eq("pf.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("pf.vsdPartner", "partner", Criteria.LEFT_JOIN, Restrictions.eq("partner.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("pf.vsdFine", "vsdFine", Criteria.LEFT_JOIN, Restrictions.eq("vsdFine.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.in("violation.violationId", innerQueryList)) .addOrder(Order.desc("violation.reportedDate")) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list(); logger.debug("violations.size() : " + violations.size()); } catch (Exception ex) { logger.error("An error occured in getViolationsByTrafficFileNumber()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getViolationsByTrafficFileNumber -- END"); return violations; } /** * * get Violations By TrafficFileNumber * getViolationsByTrafficFileNumber(dsContext, retrieveArabicData, trafficFileNumber) * getCountForViolationsByTrafficFileNumber(dsContext, retrieveArabicData, trafficFileNumber) * getViolationsByTrafficFileNumber(dsContext, retrieveArabicData, trafficFileNumber, maxResults, firstResult) * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param trafficFileNumber * @param maxResults * @param firstResult * @return List<Violation> * @throws VSDDataAccessException */ public Long getCountForViolationsByTrafficFileNumber(final DataServiceContext dsContext, boolean retrieveArabicData, String trafficFileNumber) throws VSDDataAccessException { logger.info("getCountForViolationsByTrafficFileNumber -- START"); Long violationsCount = new Long(0); try { Session session = (Session) dsContext.getInternalContext(); Criteria projectionCriteria = session.createCriteria(VsdViolation.class, "violation") .add(Restrictions.eq("violation.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violation.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vi.vsdOwnerInfos", "oi", Criteria.LEFT_JOIN, Restrictions.eq("oi.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("oi.trafficFileNumber", trafficFileNumber).ignoreCase()) .createCriteria("violation.vsdViolationStatus", "vs", Criteria.LEFT_JOIN, Restrictions.eq("vs.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdLocation", "l", Criteria.LEFT_JOIN, Restrictions.eq("l.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("l.vsdLocation", "pl", Criteria.LEFT_JOIN, Restrictions.eq("pl.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdChannelPartInst", "cpi", Criteria.LEFT_JOIN, Restrictions.eq("cpi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cpi.vsdChannelPartner", "cp", Criteria.LEFT_JOIN, Restrictions.eq("cp.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cp.vsdPartner", "p", Criteria.LEFT_JOIN, Restrictions.eq("p.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violation.vsdChannelDefects", "cd", Criteria.LEFT_JOIN, Restrictions.eq("cd.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdDefect", "d", Criteria.LEFT_JOIN, Restrictions.eq("d.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("d.vsdDefectCategory", "dc", Criteria.LEFT_JOIN, Restrictions.eq("dc.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdChannelDefectFines", "cdf", Criteria.LEFT_JOIN, Restrictions.eq("cdf.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cdf.vsdVCatDefSevFine", "vcdsf", Criteria.LEFT_JOIN, Restrictions.eq("vcdsf.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vcdsf.vsdPartnerFine", "pf", Criteria.LEFT_JOIN, Restrictions.eq("pf.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("pf.vsdPartner", "partner", Criteria.LEFT_JOIN, Restrictions.eq("partner.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("pf.vsdFine", "vsdFine", Criteria.LEFT_JOIN, Restrictions.eq("vsdFine.isDeleted", IDataService.BOOL_FALSE)) .setProjection(Projections.countDistinct("violation.violationId")) .addOrder(Order.desc("violation.reportedDate")); violationsCount = (Long) projectionCriteria.uniqueResult(); logger.debug("violationsCount : " + violationsCount); } catch (Exception ex) { logger.error("An error occured in getCountForViolationsByTrafficFileNumber()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getCountForViolationsByTrafficFileNumber -- END"); return violationsCount; } /** * * get Violations By TrafficFileNumber * Following three methods are associated * getViolationsByTrafficFileNumber(dsContext, retrieveArabicData, trafficFileNumber) * getCountForViolationsByTrafficFileNumber(dsContext, retrieveArabicData, trafficFileNumber) * getViolationsByTrafficFileNumber(dsContext, retrieveArabicData, trafficFileNumber, maxResults, firstResult) * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param trafficFileNumber * @return List<Violation> * @throws VSDDataAccessException */ public List<VsdViolation> getViolationsByTrafficFileNumber(final DataServiceContext dsContext, boolean retrieveArabicData, String trafficFileNumber) throws VSDDataAccessException { logger.info("getViolationsByTrafficFileNumber -- START"); List<VsdViolation> violations; try { Session session = (Session) dsContext.getInternalContext(); Criteria criteria = session.createCriteria(VsdViolation.class, "violation") .add(Restrictions.eq("violation.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violation.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vi.vsdOwnerInfos", "oi", Criteria.LEFT_JOIN, Restrictions.eq("oi.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("oi.trafficFileNumber", trafficFileNumber).ignoreCase()) .createCriteria("violation.vsdViolationStatus", "vs", Criteria.LEFT_JOIN, Restrictions.eq("vs.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdLocation", "l", Criteria.LEFT_JOIN, Restrictions.eq("l.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("l.vsdLocation", "pl", Criteria.LEFT_JOIN, Restrictions.eq("pl.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdChannelPartInst", "cpi", Criteria.LEFT_JOIN, Restrictions.eq("cpi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cpi.vsdChannelPartner", "cp", Criteria.LEFT_JOIN, Restrictions.eq("cp.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cp.vsdPartner", "p", Criteria.LEFT_JOIN, Restrictions.eq("p.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violation.vsdChannelDefects", "cd", Criteria.LEFT_JOIN, Restrictions.eq("cd.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdDefect", "d", Criteria.LEFT_JOIN, Restrictions.eq("d.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("d.vsdDefectCategory", "dc", Criteria.LEFT_JOIN, Restrictions.eq("dc.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdChannelDefectFines", "cdf", Criteria.LEFT_JOIN, Restrictions.eq("cdf.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cdf.vsdVCatDefSevFine", "vcdsf", Criteria.LEFT_JOIN, Restrictions.eq("vcdsf.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vcdsf.vsdPartnerFine", "pf", Criteria.LEFT_JOIN, Restrictions.eq("pf.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("pf.vsdPartner", "partner", Criteria.LEFT_JOIN, Restrictions.eq("partner.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("pf.vsdFine", "vsdFine", Criteria.LEFT_JOIN, Restrictions.eq("vsdFine.isDeleted", IDataService.BOOL_FALSE)) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY) .addOrder(Order.desc("violation.reportedDate")); violations = criteria.list(); } catch (Exception ex) { logger.error("An error occured in getViolationsByTrafficFileNumber()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getViolationsByTrafficFileNumber -- END"); return violations; } /** * * get Violations By TrafficFileNumber * Following three methods are associated * getViolationsByTrafficFileNumber(dsContext, retrieveArabicData, trafficFileNumber) * getCountForViolationsByTrafficFileNumber(dsContext, retrieveArabicData, trafficFileNumber) * getViolationsByTrafficFileNumber(dsContext, retrieveArabicData, trafficFileNumber, maxResults, firstResult) * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param trafficFileNumber * @param maxResults * @return List<Violation> * @throws VSDDataAccessException */ public List<VsdViolation> getViolationsByTrafficFileNumber(final DataServiceContext dsContext, boolean retrieveArabicData, String trafficFileNumber, int maxResults) throws VSDDataAccessException { logger.info("getViolationsByTrafficFileNumber -- START"); List<VsdViolation> violations; try { Session session = (Session) dsContext.getInternalContext(); Criteria criteria = session.createCriteria(VsdViolation.class, "violation") .add(Restrictions.eq("violation.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violation.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vi.vsdOwnerInfos", "oi", Criteria.LEFT_JOIN, Restrictions.eq("oi.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("oi.trafficFileNumber", trafficFileNumber).ignoreCase()) .createCriteria("violation.vsdViolationStatus", "vs", Criteria.LEFT_JOIN, Restrictions.eq("vs.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdLocation", "l", Criteria.LEFT_JOIN, Restrictions.eq("l.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("l.vsdLocation", "pl", Criteria.LEFT_JOIN, Restrictions.eq("pl.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdChannelPartInst", "cpi", Criteria.LEFT_JOIN, Restrictions.eq("cpi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cpi.vsdChannelPartner", "cp", Criteria.LEFT_JOIN, Restrictions.eq("cp.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cp.vsdPartner", "p", Criteria.LEFT_JOIN, Restrictions.eq("p.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violation.vsdChannelDefects", "cd", Criteria.LEFT_JOIN, Restrictions.eq("cd.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdDefect", "d", Criteria.LEFT_JOIN, Restrictions.eq("d.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("d.vsdDefectCategory", "dc", Criteria.LEFT_JOIN, Restrictions.eq("dc.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdChannelDefectFines", "cdf", Criteria.LEFT_JOIN, Restrictions.eq("cdf.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cdf.vsdVCatDefSevFine", "vcdsf", Criteria.LEFT_JOIN, Restrictions.eq("vcdsf.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vcdsf.vsdPartnerFine", "pf", Criteria.LEFT_JOIN, Restrictions.eq("pf.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("pf.vsdPartner", "partner", Criteria.LEFT_JOIN, Restrictions.eq("partner.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("pf.vsdFine", "vsdFine", Criteria.LEFT_JOIN, Restrictions.eq("vsdFine.isDeleted", IDataService.BOOL_FALSE)) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY) .addOrder(Order.desc("violation.reportedDate")).setMaxResults(maxResults); violations = criteria.list(); } catch (Exception ex) { logger.error("An error occured in getViolationsByTrafficFileNumber()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getViolationsByTrafficFileNumber -- END"); return violations; } /** * * get Violations with tests By TrafficFileNumber And ChassisNumber * associated overloaded method * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param trafficFileNumber * @param vehicleChassisNumber * @return List<Violation> * @throws VSDDataAccessException */ public List<VsdViolation> getViolationsWithTestsByTrafficFileNumberAndChassisNumber( final DataServiceContext dsContext, boolean retrieveArabicData, String trafficFileNumber, String vehicleChassisNumber, int maxResults) throws VSDDataAccessException { logger.info("getViolationsWithTestsByTrafficFileNumberAndChassisNumber -- START"); List<VsdViolation> violations; try { Session session = (Session) dsContext.getInternalContext(); ProjectionList projectionList = Projections.projectionList(); projectionList.add(Projections.property("violation.violationId"), "violationId"); projectionList.add(Projections.property("violation.reportedDate"), "reportedDate"); Criteria projectionCriteria = session.createCriteria(VsdViolation.class, "violation") .add(Restrictions.eq("violation.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violation.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("vi.vehicleChassisNumber", vehicleChassisNumber).ignoreCase()) .createCriteria("vi.vsdOwnerInfos", "oi", Criteria.LEFT_JOIN, Restrictions.eq("oi.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("oi.trafficFileNumber", trafficFileNumber).ignoreCase()) .createCriteria("violation.vsdChannelDefects", "cd", Criteria.LEFT_JOIN, Restrictions.eq("cd.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdChannelDefectTests", "cdt", Criteria.LEFT_JOIN, Restrictions.eq("cdt.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cdt.vsdChannelTest", "ct", Criteria.LEFT_JOIN, Restrictions.eq("ct.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.isNotNull("ct.createdTimestamp")) .createCriteria("ct.vsdChannelPartInst", "cpi", Criteria.LEFT_JOIN, Restrictions.eq("cpi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cpi.vsdChannelPartner", "cp", Criteria.LEFT_JOIN, Restrictions.eq("cp.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cp.vsdPartner", "p", Criteria.LEFT_JOIN, Restrictions.eq("p.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("ct.vsdTestType", "tt", Criteria.LEFT_JOIN, Restrictions.eq("tt.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdChannelDefectFines", "cdf", Criteria.LEFT_JOIN, Restrictions.eq("cdf.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cdf.vsdVCatDefSevFine", "vcdsf", Criteria.LEFT_JOIN, Restrictions.eq("vcdsf.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vcdsf.vsdPartnerFine", "pf", Criteria.LEFT_JOIN, Restrictions.eq("pf.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("pf.vsdPartner", "part", Criteria.LEFT_JOIN, Restrictions.eq("part.isDeleted", IDataService.BOOL_FALSE)) .setProjection(Projections.distinct(projectionList)) .addOrder(Order.desc("violation.reportedDate")) .setResultTransformer(new AliasToBeanResultTransformer(VsdViolation.class)) .setMaxResults(maxResults); List list = projectionCriteria.list(); logger.debug("list.size() : " + list.size()); Set resultSet = new HashSet(list); logger.debug("resultSet.size() : " + resultSet.size()); if (resultSet.size() == 0) return new ArrayList(); Iterator iterator = resultSet.iterator(); ArrayList innerQueryList = new ArrayList<Long>(); while (iterator.hasNext()) { VsdViolation vsdViolation = (VsdViolation) iterator.next(); innerQueryList.add(vsdViolation.getViolationId()); } violations = session.createCriteria(VsdViolation.class, "violation") .add(Restrictions.eq("violation.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violation.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("vi.vehicleChassisNumber", vehicleChassisNumber).ignoreCase()) .createCriteria("vi.vsdOwnerInfos", "oi", Criteria.LEFT_JOIN, Restrictions.eq("oi.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("oi.trafficFileNumber", trafficFileNumber).ignoreCase()) .createCriteria("violation.vsdChannelDefects", "cd", Criteria.LEFT_JOIN, Restrictions.eq("cd.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdChannelDefectTests", "cdt", Criteria.LEFT_JOIN, Restrictions.eq("cdt.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cdt.vsdChannelTest", "ct", Criteria.LEFT_JOIN, Restrictions.eq("ct.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.isNotNull("ct.createdTimestamp")) .createCriteria("ct.vsdChannelPartInst", "cpi", Criteria.LEFT_JOIN, Restrictions.eq("cpi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cpi.vsdChannelPartner", "cp", Criteria.LEFT_JOIN, Restrictions.eq("cp.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cp.vsdPartner", "p", Criteria.LEFT_JOIN, Restrictions.eq("p.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("ct.vsdTestType", "tt", Criteria.LEFT_JOIN, Restrictions.eq("tt.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdChannelDefectFines", "cdf", Criteria.LEFT_JOIN, Restrictions.eq("cdf.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cdf.vsdVCatDefSevFine", "vcdsf", Criteria.LEFT_JOIN, Restrictions.eq("vcdsf.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vcdsf.vsdPartnerFine", "pf", Criteria.LEFT_JOIN, Restrictions.eq("pf.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("pf.vsdPartner", "part", Criteria.LEFT_JOIN, Restrictions.eq("part.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.in("violation.violationId", innerQueryList)) .addOrder(Order.desc("violation.reportedDate")) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list(); logger.debug("violations.size() : " + violations.size()); } catch (Exception ex) { logger.error("An error occured in getViolationsWithTestsByTrafficFileNumberAndChassisNumber()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getViolationsWithTestsByTrafficFileNumberAndChassisNumber -- END"); return violations; } /** * * get Violations with tests By TrafficFileNumber And ChassisNumber * associated overloaded method * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param trafficFileNumber * @param vehicleChassisNumber * @return List<Violation> * @throws VSDDataAccessException */ public List<VsdViolation> getViolationsWithTestsByTrafficFileNumberAndChassisNumber( final DataServiceContext dsContext, boolean retrieveArabicData, String trafficFileNumber, String vehicleChassisNumber) throws VSDDataAccessException { logger.info("getViolationsWithTestsByTrafficFileNumberAndChassisNumber -- START"); List<VsdViolation> violations; try { Session session = (Session) dsContext.getInternalContext(); Criteria criteria = session.createCriteria(VsdViolation.class, "violation") .add(Restrictions.eq("violation.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violation.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("vi.vehicleChassisNumber", vehicleChassisNumber).ignoreCase()) .createCriteria("vi.vsdOwnerInfos", "oi", Criteria.LEFT_JOIN, Restrictions.eq("oi.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("oi.trafficFileNumber", trafficFileNumber).ignoreCase()) .createCriteria("violation.vsdChannelDefects", "cd", Criteria.LEFT_JOIN, Restrictions.eq("cd.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdChannelDefectTests", "cdt", Criteria.LEFT_JOIN, Restrictions.eq("cdt.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cdt.vsdChannelTest", "ct", Criteria.LEFT_JOIN, Restrictions.eq("ct.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.isNotNull("ct.createdTimestamp")) .createCriteria("ct.vsdChannelPartInst", "cpi", Criteria.LEFT_JOIN, Restrictions.eq("cpi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cpi.vsdChannelPartner", "cp", Criteria.LEFT_JOIN, Restrictions.eq("cp.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cp.vsdPartner", "p", Criteria.LEFT_JOIN, Restrictions.eq("p.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("ct.vsdTestType", "tt", Criteria.LEFT_JOIN, Restrictions.eq("tt.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdChannelDefectFines", "cdf", Criteria.LEFT_JOIN, Restrictions.eq("cdf.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cdf.vsdVCatDefSevFine", "vcdsf", Criteria.LEFT_JOIN, Restrictions.eq("vcdsf.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vcdsf.vsdPartnerFine", "pf", Criteria.LEFT_JOIN, Restrictions.eq("pf.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("pf.vsdPartner", "part", Criteria.LEFT_JOIN, Restrictions.eq("part.isDeleted", IDataService.BOOL_FALSE)) .addOrder(Order.desc("violation.reportedDate")) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY); violations = criteria.list(); logger.debug("violations.size() : " + violations.size()); } catch (Exception ex) { logger.error("An error occured in getViolationsWithTestsByTrafficFileNumberAndChassisNumber()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getViolationsWithTestsByTrafficFileNumberAndChassisNumber -- END"); return violations; } /** * * get Violations with tests By TrafficFileNumber * getViolationsWithTestsByTrafficFileNumber(dsContext, retrieveArabicData, trafficFileNumber) * getCountForViolationsWithTestsByTrafficFileNumber(dsContext, retrieveArabicData, trafficFileNumber) * getViolationsWithTestsByTrafficFileNumber(dsContext, retrieveArabicData, trafficFileNumber, maxResults, firstResult) * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param trafficFileNumber * @param maxResults * @param firstResult * @return List<Violation> * @throws VSDDataAccessException */ public List<VsdViolation> getViolationsWithTestsByTrafficFileNumber(final DataServiceContext dsContext, boolean retrieveArabicData, String trafficFileNumber, int maxResults, int firstResult) throws VSDDataAccessException { logger.info("getViolationsWithTestsByTrafficFileNumber -- START"); List<VsdViolation> violations; try { Session session = (Session) dsContext.getInternalContext(); ProjectionList projectionList = Projections.projectionList(); projectionList.add(Projections.property("violation.violationId"), "violationId"); projectionList.add(Projections.property("violation.reportedDate"), "reportedDate"); Criteria projectionCriteria = session.createCriteria(VsdViolation.class, "violation") .add(Restrictions.eq("violation.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violation.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vi.vsdOwnerInfos", "oi", Criteria.LEFT_JOIN, Restrictions.eq("oi.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("oi.trafficFileNumber", trafficFileNumber).ignoreCase()) .createCriteria("violation.vsdChannelDefects", "cd", Criteria.LEFT_JOIN, Restrictions.eq("cd.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdChannelDefectTests", "cdt", Criteria.LEFT_JOIN, Restrictions.eq("cdt.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cdt.vsdChannelTest", "ct", Criteria.LEFT_JOIN, Restrictions.eq("ct.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("ct.vsdChannelPartInst", "cpi", Criteria.LEFT_JOIN, Restrictions.eq("cpi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("ct.vsdTestType", "tt", Criteria.LEFT_JOIN, Restrictions.eq("tt.isDeleted", IDataService.BOOL_FALSE)) .setProjection(Projections.distinct(projectionList)) .addOrder(Order.desc("violation.reportedDate")) .setResultTransformer(new AliasToBeanResultTransformer(VsdViolation.class)) .setMaxResults(maxResults).setFirstResult(firstResult); List list = projectionCriteria.list(); logger.debug("list.size() : " + list.size()); Set resultSet = new HashSet(list); logger.debug("resultSet.size() : " + resultSet.size()); if (resultSet.size() == 0) return new ArrayList(); Iterator iterator = resultSet.iterator(); ArrayList innerQueryList = new ArrayList<Long>(); while (iterator.hasNext()) { VsdViolation vsdViolation = (VsdViolation) iterator.next(); innerQueryList.add(vsdViolation.getViolationId()); } violations = session.createCriteria(VsdViolation.class, "violation") .add(Restrictions.eq("violation.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violation.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vi.vsdOwnerInfos", "oi", Criteria.LEFT_JOIN, Restrictions.eq("oi.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("oi.trafficFileNumber", trafficFileNumber).ignoreCase()) .createCriteria("violation.vsdChannelDefects", "cd", Criteria.LEFT_JOIN, Restrictions.eq("cd.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdChannelDefectTests", "cdt", Criteria.LEFT_JOIN, Restrictions.eq("cdt.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cdt.vsdChannelTest", "ct", Criteria.LEFT_JOIN, Restrictions.eq("ct.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("ct.vsdChannelPartInst", "cpi", Criteria.LEFT_JOIN, Restrictions.eq("cpi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("ct.vsdTestType", "tt", Criteria.LEFT_JOIN, Restrictions.eq("tt.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.in("violation.violationId", innerQueryList)) .addOrder(Order.desc("violation.reportedDate")) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list(); logger.debug("violations.size() : " + violations.size()); } catch (Exception ex) { logger.error("An error occured in getViolationsWithTestsByTrafficFileNumber()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getViolationsWithTestsByTrafficFileNumber -- END"); return violations; } /** * * get Count For Violations with tests By TrafficFileNumber * getViolationsWithTestsByTrafficFileNumber(dsContext, retrieveArabicData, trafficFileNumber) * getCountForViolationsWithTestsByTrafficFileNumber(dsContext, retrieveArabicData, trafficFileNumber) * getViolationsWithTestsByTrafficFileNumber(dsContext, retrieveArabicData, trafficFileNumber, maxResults, firstResult) * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param trafficFileNumber * @return List<Violation> * @throws VSDDataAccessException */ public Long getCountForViolationsWithTestsByTrafficFileNumber(final DataServiceContext dsContext, boolean retrieveArabicData, String trafficFileNumber) throws VSDDataAccessException { logger.info("getCountForViolationsWithTestsByTrafficFileNumber -- START"); Long violationsCount = new Long(0); try { Session session = (Session) dsContext.getInternalContext(); Criteria projectionCriteria = session.createCriteria(VsdViolation.class, "violation") .add(Restrictions.eq("violation.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violation.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vi.vsdOwnerInfos", "oi", Criteria.LEFT_JOIN, Restrictions.eq("oi.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("oi.trafficFileNumber", trafficFileNumber).ignoreCase()) .createCriteria("violation.vsdChannelDefects", "cd", Criteria.LEFT_JOIN, Restrictions.eq("cd.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdChannelDefectTests", "cdt", Criteria.LEFT_JOIN, Restrictions.eq("cdt.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cdt.vsdChannelTest", "ct", Criteria.LEFT_JOIN, Restrictions.eq("ct.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("ct.vsdChannelPartInst", "cpi", Criteria.LEFT_JOIN, Restrictions.eq("cpi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("ct.vsdTestType", "tt", Criteria.LEFT_JOIN, Restrictions.eq("tt.isDeleted", IDataService.BOOL_FALSE)) .setProjection(Projections.countDistinct("violation.violationId")) .addOrder(Order.desc("violation.reportedDate")); violationsCount = (Long) projectionCriteria.uniqueResult(); logger.debug("violationsCount : " + violationsCount); } catch (Exception ex) { logger.error("An error occured in getCountForViolationsWithTestsByTrafficFileNumber()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getCountForViolationsWithTestsByTrafficFileNumber -- END"); return violationsCount; } /** * * get Violations with tests By TrafficFileNumber * Following three methods are associated * getViolationsWithTestsByTrafficFileNumber(dsContext, retrieveArabicData, trafficFileNumber) * getCountForViolationsWithTestsByTrafficFileNumber(dsContext, retrieveArabicData, trafficFileNumber) * getViolationsWithTestsByTrafficFileNumber(dsContext, retrieveArabicData, trafficFileNumber, maxResults, firstResult) * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param trafficFileNumber * @return List<Violation> * @throws VSDDataAccessException */ public List<VsdViolation> getViolationsWithTestsByTrafficFileNumber(final DataServiceContext dsContext, boolean retrieveArabicData, String trafficFileNumber) throws VSDDataAccessException { logger.info("getViolationsWithTestsByTrafficFileNumber -- START"); List<VsdViolation> violations; try { Session session = (Session) dsContext.getInternalContext(); Criteria criteria = session.createCriteria(VsdViolation.class, "violation") .add(Restrictions.eq("violation.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violation.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vi.vsdOwnerInfos", "oi", Criteria.LEFT_JOIN, Restrictions.eq("oi.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("oi.trafficFileNumber", trafficFileNumber).ignoreCase()) .createCriteria("violation.vsdChannelDefects", "cd", Criteria.LEFT_JOIN, Restrictions.eq("cd.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdChannelDefectTests", "cdt", Criteria.LEFT_JOIN, Restrictions.eq("cdt.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cdt.vsdChannelTest", "ct", Criteria.LEFT_JOIN, Restrictions.eq("ct.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("ct.vsdChannelPartInst", "cpi", Criteria.LEFT_JOIN, Restrictions.eq("cpi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("ct.vsdTestType", "tt", Criteria.LEFT_JOIN, Restrictions.eq("tt.isDeleted", IDataService.BOOL_FALSE)) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY) .addOrder(Order.desc("violation.reportedDate")); violations = criteria.list(); } catch (Exception ex) { logger.error("An error occured in getViolationsWithTestsByTrafficFileNumber()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getViolationsWithTestsByTrafficFileNumber -- END"); return violations; } /** * * Gets the total violations with the status specified (null if all violations are required) between the dates provided. * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param fromDate * @param toDate * @param statusId * @return Long * @throws VSDDataAccessException */ public Long getTotalViolationsByDateRangeAndStatus(DataServiceContext dsContext, boolean retrieveArabicData, Date fromDate, Date toDate, Long statusId) throws VSDDataAccessException { logger.info("getTotalViolationsByDateRangeAndStatus -- START"); /*SELECT COUNT(v.VIOLATION_ID) AS "violationClosedCount" FROM VSD_VIOLATION v JOIN VSD_VIOLATION_STATUS vs ON v.VIOLATION_STATUS_ID = vs.VIOLATION_STATUS_ID AND vs.IS_DELETED = 'F' WHERE v.IS_DELETED = 'F' AND v.VIOLATION_STATUS_ID = 1 AND v.REPORTED_DATE BETWEEN to_date('2011-05-15','YYYY-MM-DD') AND to_date('2011-11-30', 'YYYY-MM-DD')*/ /*SELECT COUNT(v.VIOLATION_ID) AS "violationsRecordedCount" FROM VSD_VIOLATION WHERE v.IS_DELETED = 'F' AND v.REPORTED_DATE BETWEEN to_date('2011-05-15','YYYY-MM-DD') AND to_date('2011-11-30', 'YYYY-MM-DD')*/ try { Session session = (Session) dsContext.getInternalContext(); String defaultSchema = DataServiceImpl.DEFAULT_SCHEMA_NAME; StringBuffer query; if (statusId != null) { query = new StringBuffer("SELECT COUNT(v.VIOLATION_ID) AS \"violationClosedCount\" FROM " + defaultSchema + ".VSD_VIOLATION v JOIN " + defaultSchema + ".VSD_VIOLATION_STATUS vs " + " ON v.VIOLATION_STATUS_ID = vs.VIOLATION_STATUS_ID AND vs.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " WHERE v.IS_DELETED = '" + IDataService.BOOL_FALSE + "' AND v.VIOLATION_STATUS_ID = " + statusId); if (fromDate != null && toDate != null) { SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); query.append( " AND to_date(to_char(v.REPORTED_DATE, 'YYYY-MM-DD'),'YYYY-MM-DD') BETWEEN to_date('" + dateFormat.format(fromDate) + "','YYYY-MM-DD') AND to_date('" + dateFormat.format(toDate) + "','YYYY-MM-DD') "); } } else { query = new StringBuffer( "SELECT COUNT(v.VIOLATION_ID) AS \"violationsRecordedCount\" FROM " + defaultSchema + ".VSD_VIOLATION v" + " WHERE v.IS_DELETED = '" + IDataService.BOOL_FALSE + "' "); if (fromDate != null && toDate != null) { SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); query.append( " AND to_date(to_char(v.REPORTED_DATE,'YYYY-MM-DD'),'YYYY-MM-DD') BETWEEN to_date('" + dateFormat.format(fromDate) + "','YYYY-MM-DD') AND to_date('" + dateFormat.format(toDate) + "','YYYY-MM-DD') "); } } Long violationsRecordedCount = Long .valueOf(((BigDecimal) session.createSQLQuery(query.toString()).uniqueResult()).longValue()); logger.info("getTotalViolationsByDateRangeAndStatus -- END"); return violationsRecordedCount; } catch (Exception ex) { logger.error("An error occured in getTotalViolationsByDateRangeAndStatus()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } } /** * * Gets the total of the fines for the violations between the given dates. * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param fromDate * @param toDate * @param partnerCode * @return Long * @throws VSDDataAccessException */ public Long getTotalViolationFinesByDateRange(DataServiceContext dsContext, boolean retrieveArabicData, Date fromDate, Date toDate, String partnerCode) throws VSDDataAccessException { logger.info("getTotalViolationFinesByDateRange -- START"); /*SELECT SUM(cdf.FINE_AMOUNT) AS "totalViolationsFine" FROM VSD_VIOLATION v JOIN VSD_CHANNEL_DEFECT cd ON cd.VIOLATION_ID = v.VIOLATION_ID AND cd.IS_DELETED = 'F' JOIN VSD_CHANNEL_DEFECT_FINE cdf ON cdf.CHANNEL_DEFECT_ID = cd.CHANNEL_DEFECT_ID AND cdf.IS_DELETED = 'F' JOIN VSD_V_CAT_DEF_SEV_FINE vcdsf ON vcdsf.V_CAT_DEF_SEV_FINE_ID = cdf.V_CAT_DEF_SEV_FINE_ID AND vcdsf.IS_DELETED = 'F' JOIN VSD_PARTNER_FINE pf ON vcdsf.PARTNER_FINE_ID = pf.PARTNER_FINE_ID AND IS_DELETED = 'F' JOIN VSD_PARTNER p ON pf.PARTNER_ID = p.PARTNER_ID WHERE v.IS_DELETED = 'F' AND v.REPORTED_DATE BETWEEN to_date('2011-05-15','YYYY-MM-DD') AND to_date('2011-11-30', 'YYYY-MM-DD') AND PARTNER_CODE = 'adsf'*/ try { Session session = (Session) dsContext.getInternalContext(); String defaultSchema = DataServiceImpl.DEFAULT_SCHEMA_NAME; StringBuffer query = new StringBuffer("SELECT SUM(cdf.FINE_AMOUNT) AS \"totalViolationsFine\" FROM " + defaultSchema + ".VSD_VIOLATION v JOIN " + defaultSchema + ".VSD_CHANNEL_DEFECT cd ON cd.VIOLATION_ID = v.VIOLATION_ID AND cd.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " JOIN " + defaultSchema + ".VSD_CHANNEL_DEFECT_FINE cdf ON cdf.CHANNEL_DEFECT_ID = cd.CHANNEL_DEFECT_ID AND cdf.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " JOIN " + defaultSchema + ".VSD_V_CAT_DEF_SEV_FINE vcdsf ON vcdsf.V_CAT_DEF_SEV_FINE_ID = cdf.V_CAT_DEF_SEV_FINE_ID AND vcdsf.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " JOIN " + defaultSchema + ".VSD_PARTNER_FINE pf ON vcdsf.PARTNER_FINE_ID = pf.PARTNER_FINE_ID AND pf.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " JOIN VSD_PARTNER p ON pf.PARTNER_ID = p.PARTNER_ID AND p.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " WHERE v.IS_DELETED = '" + IDataService.BOOL_FALSE + "' AND p.PARTNER_CODE = '" + partnerCode + "' "); if (fromDate != null && toDate != null) { SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); query.append(" AND to_date(to_char(v.REPORTED_DATE,'YYYY-MM-DD'),'YYYY-MM-DD') BETWEEN to_date('" + dateFormat.format(fromDate) + "','YYYY-MM-DD') AND to_date('" + dateFormat.format(toDate) + "','YYYY-MM-DD') "); } Long fineAmount = (Long) session.createSQLQuery(query.toString()) .addScalar("totalViolationsFine", Hibernate.LONG).uniqueResult(); logger.info("getTotalViolationFinesByDateRange -- END"); return fineAmount; } catch (Exception ex) { logger.error("An error occured in getTotalViolationFinesByDateRange()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } } /** * * Gets a list of violations with their associated defects(VsdChannelDefect) by their statuses * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param statuses * @param testStatus * @return List<VsdViolation> * @throws VSDDataAccessException */ public List<VsdViolation> getViolationsWithDefectsByStatuses(DataServiceContext dsContext, boolean retrieveArabicData, List<Long> violationStatusIds, String testStatus) throws VSDDataAccessException { logger.info("getViolationsWithDefectsByStatuses -- START"); List<VsdViolation> violations = null; try { Session session = (Session) dsContext.getInternalContext(); Criteria criteria = session.createCriteria(VsdViolation.class, "violations") .add(Restrictions.eq("violations.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violations.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdPlateConditionByFrontPlateConditionId", "fpc", Criteria.LEFT_JOIN, Restrictions.eq("fpc.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdPlateConditionByRearPlateConditionId", "rpc", Criteria.LEFT_JOIN, Restrictions.eq("rpc.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vi.vsdDriverInfos", "di", Criteria.LEFT_JOIN, Restrictions.eq("di.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vi.vsdOwnerInfos", "oi", Criteria.LEFT_JOIN, Restrictions.eq("oi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violations.vsdSeverityLevel", "s", Criteria.LEFT_JOIN, Restrictions.eq("s.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violations.vsdChannelDefects", "cd", Criteria.LEFT_JOIN, Restrictions.eq("cd.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdChannelDefectTests", "cdt", Criteria.LEFT_JOIN, Restrictions.eq("cdt.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cdt.vsdChannelTest", "ct", Criteria.LEFT_JOIN, Restrictions.eq("ct.isDeleted", IDataService.BOOL_FALSE)); if (testStatus != null) { criteria.add(Restrictions.eq("ct.testStatus", testStatus)); } criteria.createCriteria("violations.vsdViolationStatus", "vs", Criteria.LEFT_JOIN, Restrictions.eq("vs.isDeleted", IDataService.BOOL_FALSE)) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY) .add(Restrictions.in("vs.violationStatusId", violationStatusIds)); violations = criteria.list(); logger.info("getViolationsWithDefectsByStatuses -- END"); return violations; } catch (Exception ex) { logger.error("An error occured in getViolationsWithDefectsByStatuses()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } } /** * * Gets a list of violations with their associated defects(VsdChannelDefect) by their statuses * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param statuses * @param testStatus * @param isViolationDefect * @return List<VsdViolation> * @throws VSDDataAccessException */ public List<VsdViolation> getViolationsWithDefectsByStatuses(DataServiceContext dsContext, boolean retrieveArabicData, List<Long> violationStatusIds, String testStatus, Character isViolationDefect) throws VSDDataAccessException { logger.info("getViolationsWithDefectsByStatuses -- START"); List<VsdViolation> violations = null; try { Session session = (Session) dsContext.getInternalContext(); Criteria criteria = session.createCriteria(VsdViolation.class, "violations") .add(Restrictions.eq("violations.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violations.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdPlateConditionByFrontPlateConditionId", "fpc", Criteria.LEFT_JOIN, Restrictions.eq("fpc.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdPlateConditionByRearPlateConditionId", "rpc", Criteria.LEFT_JOIN, Restrictions.eq("rpc.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vi.vsdDriverInfos", "di", Criteria.LEFT_JOIN, Restrictions.eq("di.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vi.vsdOwnerInfos", "oi", Criteria.LEFT_JOIN, Restrictions.eq("oi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violations.vsdSeverityLevel", "s", Criteria.LEFT_JOIN, Restrictions.eq("s.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violations.vsdChannelDefects", "cd", Criteria.LEFT_JOIN, Restrictions.eq("cd.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("cd.isViolationDefect", isViolationDefect)) .createCriteria("cd.vsdDefect", "def", Criteria.LEFT_JOIN, Restrictions.eq("def.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdChannelDefectTests", "cdt", Criteria.LEFT_JOIN, Restrictions.eq("cdt.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cdt.vsdChannelTest", "ct", Criteria.LEFT_JOIN, Restrictions.eq("ct.isDeleted", IDataService.BOOL_FALSE)); if (testStatus != null) { criteria.add(Restrictions.eq("ct.testStatus", testStatus)); } criteria.createCriteria("violations.vsdViolationStatus", "vs", Criteria.LEFT_JOIN, Restrictions.eq("vs.isDeleted", IDataService.BOOL_FALSE)) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY) .add(Restrictions.in("vs.violationStatusId", violationStatusIds)); violations = criteria.list(); logger.info("getViolationsWithDefectsByStatuses -- END"); return violations; } catch (Exception ex) { logger.error("An error occured in getViolationsWithDefectsByStatuses()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } } /** * * Gets a list of violations with their associated defects(VsdChannelDefect) by their statuses * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param statuses * @param testStatus * @param reportedDate * @return List<VsdViolation> * @throws VSDDataAccessException */ public List<VsdViolation> getViolationsWithDefectsByStatuses(DataServiceContext dsContext, boolean retrieveArabicData, List<Long> violationStatusIds, String testStatus, Date reportedDate) throws VSDDataAccessException { logger.info("getViolationsWithDefectsByStatuses -- START"); List<VsdViolation> violations = null; try { Session session = (Session) dsContext.getInternalContext(); Criteria criteria = session.createCriteria(VsdViolation.class, "violations") .add(Restrictions.eq("violations.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("violations.reportedDate", reportedDate)) .createCriteria("violations.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdPlateConditionByFrontPlateConditionId", "fpc", Criteria.LEFT_JOIN, Restrictions.eq("fpc.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdPlateConditionByRearPlateConditionId", "rpc", Criteria.LEFT_JOIN, Restrictions.eq("rpc.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vi.vsdDriverInfos", "di", Criteria.LEFT_JOIN, Restrictions.eq("di.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vi.vsdOwnerInfos", "oi", Criteria.LEFT_JOIN, Restrictions.eq("oi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violations.vsdSeverityLevel", "s", Criteria.LEFT_JOIN, Restrictions.eq("s.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violations.vsdChannelDefects", "cd", Criteria.LEFT_JOIN, Restrictions.eq("cd.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdChannelDefectTests", "cdt", Criteria.LEFT_JOIN, Restrictions.eq("cdt.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cdt.vsdChannelTest", "ct", Criteria.LEFT_JOIN, Restrictions.eq("ct.isDeleted", IDataService.BOOL_FALSE)); if (testStatus != null) { criteria.add(Restrictions.eq("ct.testStatus", testStatus)); } criteria.createCriteria("violations.vsdViolationStatus", "vs", Criteria.LEFT_JOIN, Restrictions.eq("vs.isDeleted", IDataService.BOOL_FALSE)) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY) .add(Restrictions.in("vs.violationStatusId", violationStatusIds)); violations = criteria.list(); logger.info("getViolationsWithDefectsByStatuses -- END"); return violations; } catch (Exception ex) { logger.error("An error occured in getViolationsWithDefectsByStatuses()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } } /** * * Gets a list of violations with their associated defects(VsdChannelDefect) by their statuses * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param statuses * @param testStatus * @param reportedDate * @param isViolationDefect * @return List<VsdViolation> * @throws VSDDataAccessException */ public List<VsdViolation> getViolationsWithDefectsByStatuses(DataServiceContext dsContext, boolean retrieveArabicData, List<Long> violationStatusIds, String testStatus, Date reportedDate, Character isViolationDefect) throws VSDDataAccessException { logger.info("getViolationsWithDefectsByStatuses -- START"); List<VsdViolation> violations = null; try { Session session = (Session) dsContext.getInternalContext(); Criteria criteria = session.createCriteria(VsdViolation.class, "violations") .add(Restrictions.eq("violations.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("violations.reportedDate", reportedDate)) .createCriteria("violations.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdPlateConditionByFrontPlateConditionId", "fpc", Criteria.LEFT_JOIN, Restrictions.eq("fpc.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdPlateConditionByRearPlateConditionId", "rpc", Criteria.LEFT_JOIN, Restrictions.eq("rpc.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vi.vsdDriverInfos", "di", Criteria.LEFT_JOIN, Restrictions.eq("di.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vi.vsdOwnerInfos", "oi", Criteria.LEFT_JOIN, Restrictions.eq("oi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violations.vsdSeverityLevel", "s", Criteria.LEFT_JOIN, Restrictions.eq("s.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violations.vsdChannelDefects", "cd", Criteria.LEFT_JOIN, Restrictions.eq("cd.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("cd.isViolationDefect", isViolationDefect)) .createCriteria("cd.vsdDefect", "def", Criteria.LEFT_JOIN, Restrictions.eq("def.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdChannelDefectTests", "cdt", Criteria.LEFT_JOIN, Restrictions.eq("cdt.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cdt.vsdChannelTest", "ct", Criteria.LEFT_JOIN, Restrictions.eq("ct.isDeleted", IDataService.BOOL_FALSE)); if (testStatus != null) { criteria.add(Restrictions.eq("ct.testStatus", testStatus)); } criteria.createCriteria("violations.vsdViolationStatus", "vs", Criteria.LEFT_JOIN, Restrictions.eq("vs.isDeleted", IDataService.BOOL_FALSE)) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY) .add(Restrictions.in("vs.violationStatusId", violationStatusIds)); violations = criteria.list(); logger.info("getViolationsWithDefectsByStatuses -- END"); return violations; } catch (Exception ex) { logger.error("An error occured in getViolationsWithDefectsByStatuses()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } } /** * * Gets a list of violations with their associated defects(VsdChannelDefect) by their statuses and due date * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param statuses * @param testStatus * @param dueDate * @return List<VsdViolation> * @throws VSDDataAccessException */ public List<VsdViolation> getViolationsWithDefectsByStatusesAndDueDate(DataServiceContext dsContext, boolean retrieveArabicData, List<Long> violationStatusIds, String testStatus, Date dueDate) throws VSDDataAccessException { logger.info("getViolationsWithDefectsByStatusesAndDueDate -- START"); List<VsdViolation> violations = null; try { Session session = (Session) dsContext.getInternalContext(); Criteria criteria = session.createCriteria(VsdViolation.class, "violations") .add(Restrictions.eq("violations.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("violations.dueDate", dueDate)) .createCriteria("violations.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdPlateConditionByFrontPlateConditionId", "fpc", Criteria.LEFT_JOIN, Restrictions.eq("fpc.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdPlateConditionByRearPlateConditionId", "rpc", Criteria.LEFT_JOIN, Restrictions.eq("rpc.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vi.vsdDriverInfos", "di", Criteria.LEFT_JOIN, Restrictions.eq("di.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vi.vsdOwnerInfos", "oi", Criteria.LEFT_JOIN, Restrictions.eq("oi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violations.vsdSeverityLevel", "s", Criteria.LEFT_JOIN, Restrictions.eq("s.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violations.vsdChannelDefects", "cd", Criteria.LEFT_JOIN, Restrictions.eq("cd.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdChannelDefectTests", "cdt", Criteria.LEFT_JOIN, Restrictions.eq("cdt.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cdt.vsdChannelTest", "ct", Criteria.LEFT_JOIN, Restrictions.eq("ct.isDeleted", IDataService.BOOL_FALSE)); if (testStatus != null) { criteria.add(Restrictions.eq("ct.testStatus", testStatus)); } criteria.createCriteria("violations.vsdViolationStatus", "vs", Criteria.LEFT_JOIN, Restrictions.eq("vs.isDeleted", IDataService.BOOL_FALSE)) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY) .add(Restrictions.in("vs.violationStatusId", violationStatusIds)); violations = criteria.list(); logger.info("getViolationsWithDefectsByStatusesAndDueDate -- END"); return violations; } catch (Exception ex) { logger.error("An error occured in getViolationsWithDefectsByStatusesAndDueDate()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } } /** * * Gets a specific violation with its associated defects(VsdChannelDefect) by id * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param violationId * @return VsdViolation * @throws VSDDataAccessException */ public VsdViolation getViolationWithDefectsById(DataServiceContext dsContext, boolean retrieveArabicData, Long violationId) throws VSDDataAccessException { logger.info("getViolationWithDefectsById -- START"); VsdViolation violation = null; try { Session session = (Session) dsContext.getInternalContext(); violation = (VsdViolation) session.createCriteria(VsdViolation.class, "violations") .add(Restrictions.eq("violations.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("violations.violationId", violationId)) .createCriteria("violations.vsdChannelDefects", "cd", Criteria.LEFT_JOIN, Restrictions.eq("cd.isDeleted", IDataService.BOOL_FALSE)) .uniqueResult(); logger.info("getViolationWithDefectsById -- END"); return violation; } catch (Exception ex) { logger.error("An error occured in getViolationWithDefectsById()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } } /** * * get Violation With Inspections And ChannelDefects By ViolationTicketCode * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param violationTicketCode * @return VsdViolation * @throws VSDDataAccessException */ public VsdViolation getViolationWithInspectionsAndChannelDefectsByViolationTicketCode( DataServiceContext dsContext, boolean retrieveArabicData, String violationTicketCode, List<Long> violationStatusIds) throws VSDDataAccessException { logger.info("getViolationWithInspectionsAndChannelDefectsByViolationTicketCode -- START"); VsdViolation violation = null; try { Session session = (Session) dsContext.getInternalContext(); violation = (VsdViolation) session.createCriteria(VsdViolation.class, "violations") .add(Restrictions.eq("violations.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("violations.violationTicketCode", violationTicketCode).ignoreCase()) .createCriteria("violations.vsdChannelDefects", "cd", Criteria.LEFT_JOIN, Restrictions.eq("cd.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdDefect", "d", Criteria.LEFT_JOIN, Restrictions.eq("d.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violations.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.in("violations.vsdViolationStatus.violationStatusId", violationStatusIds)) .uniqueResult(); logger.info("getViolationWithInspectionsAndChannelDefectsByViolationTicketCode -- END"); return violation; } catch (Exception ex) { logger.error("An error occured in getViolationWithInspectionsAndChannelDefectsByViolationTicketCode()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } } /** * * get Violation With Inspections And ChannelDefects By ViolationTicketCode * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param violationTicketCode * @param violationStatusIds * @param isViolationDefect * @return VsdViolation * @throws VSDDataAccessException */ public VsdViolation getViolationWithInspectionsAndChannelDefectsByViolationTicketCode( DataServiceContext dsContext, boolean retrieveArabicData, String violationTicketCode, List<Long> violationStatusIds, Character isViolationDefect) throws VSDDataAccessException { logger.info("getViolationWithInspectionsAndChannelDefectsByViolationTicketCode -- START"); VsdViolation violation = null; try { Session session = (Session) dsContext.getInternalContext(); violation = (VsdViolation) session.createCriteria(VsdViolation.class, "violations") .add(Restrictions.eq("violations.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("violations.violationTicketCode", violationTicketCode).ignoreCase()) .createCriteria("violations.vsdChannelDefects", "cd", Criteria.LEFT_JOIN, Restrictions.eq("cd.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("cd.isViolationDefect", isViolationDefect)) .createCriteria("cd.vsdDefect", "d", Criteria.LEFT_JOIN, Restrictions.eq("d.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violations.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.in("violations.vsdViolationStatus.violationStatusId", violationStatusIds)) .uniqueResult(); logger.info("getViolationWithInspectionsAndChannelDefectsByViolationTicketCode -- END"); return violation; } catch (Exception ex) { logger.error("An error occured in getViolationWithInspectionsAndChannelDefectsByViolationTicketCode()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } } /** * * get Violation With Inspections And ChannelDefects By ViolationTicketCode * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param violationTicketCode * @return VsdViolation * @throws VSDDataAccessException */ public VsdViolation getViolationWithInspectionsAndChannelDefectsByViolationTicketCode( DataServiceContext dsContext, boolean retrieveArabicData, String violationTicketCode) throws VSDDataAccessException { logger.info("getViolationWithInspectionsAndChannelDefectsByViolationTicketCode -- START"); VsdViolation violation = null; try { Session session = (Session) dsContext.getInternalContext(); violation = (VsdViolation) session.createCriteria(VsdViolation.class, "violations") .add(Restrictions.eq("violations.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("violations.violationTicketCode", violationTicketCode).ignoreCase()) .createCriteria("violations.vsdChannelDefects", "cd", Criteria.LEFT_JOIN, Restrictions.eq("cd.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdDefect", "d", Criteria.LEFT_JOIN, Restrictions.eq("d.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violations.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdLocation", "l", Criteria.LEFT_JOIN, Restrictions.eq("l.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdChannelPartInst", "cpi", Criteria.LEFT_JOIN, Restrictions.eq("cpi.isDeleted", IDataService.BOOL_FALSE)) .uniqueResult(); logger.info("getViolationWithInspectionsAndChannelDefectsByViolationTicketCode -- END"); return violation; } catch (Exception ex) { logger.error("An error occured in getViolationWithInspectionsAndChannelDefectsByViolationTicketCode()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } } /** * * get Violation With Inspections And ChannelDefects By ViolationTicketCode * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param violationTicketCode * @param isViolationDefect * @return VsdViolation * @throws VSDDataAccessException */ public VsdViolation getViolationWithInspectionsAndChannelDefectsByViolationTicketCode( DataServiceContext dsContext, boolean retrieveArabicData, String violationTicketCode, Character isViolationDefect) throws VSDDataAccessException { logger.info("getViolationWithInspectionsAndChannelDefectsByViolationTicketCode -- START"); VsdViolation violation = null; try { Session session = (Session) dsContext.getInternalContext(); violation = (VsdViolation) session.createCriteria(VsdViolation.class, "violations") .add(Restrictions.eq("violations.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("violations.violationTicketCode", violationTicketCode).ignoreCase()) .createCriteria("violations.vsdChannelDefects", "cd", Criteria.LEFT_JOIN, Restrictions.eq("cd.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("cd.isViolationDefect", isViolationDefect)) .createCriteria("cd.vsdDefect", "d", Criteria.LEFT_JOIN, Restrictions.eq("d.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violations.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdLocation", "l", Criteria.LEFT_JOIN, Restrictions.eq("l.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdChannelPartInst", "cpi", Criteria.LEFT_JOIN, Restrictions.eq("cpi.isDeleted", IDataService.BOOL_FALSE)) .uniqueResult(); logger.info("getViolationWithInspectionsAndChannelDefectsByViolationTicketCode -- END"); return violation; } catch (Exception ex) { logger.error("An error occured in getViolationWithInspectionsAndChannelDefectsByViolationTicketCode()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } } /** * * get List of OpenViolations By ChassisNumber * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param chassisNumber * @param violationStatusIds * @return List<VsdViolation> * @throws VSDException */ public List<VsdViolation> getOpenViolationsByChassisNumber(final DataServiceContext dsContext, boolean retrieveArabicData, String vehicleChassisNumber, List<Long> violationStatusIds) throws VSDDataAccessException { logger.info("getOpenViolationsByChassisNumber -- START"); List<VsdViolation> violations = null; try { Session session = (Session) dsContext.getInternalContext(); violations = session.createCriteria(VsdViolation.class, "v") .add(Restrictions.eq("v.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdChannelDefects", "cd", Criteria.LEFT_JOIN, Restrictions.eq("cd.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdDefect", "d", Criteria.LEFT_JOIN, Restrictions.eq("d.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("vi.vehicleChassisNumber", vehicleChassisNumber).ignoreCase()) .add(Restrictions.in("v.vsdViolationStatus.violationStatusId", violationStatusIds)) .addOrder(Order.asc("v.createdTimestamp")).setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY) .list(); } catch (Exception ex) { ex.printStackTrace(); logger.error(ex.getMessage()); logger.error("An error occured in getOpenViolationsByChassisNumber()"); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getOpenViolationsByChassisNumber -- END"); return violations; } /** * * get List of OpenViolations By VehiclePlate * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param vehiclePlate * @param violationStatusIds * @return List<VsdViolation> * @throws VSDException */ public List<VsdViolation> getOpenViolationsByVehiclePlate(final DataServiceContext dsContext, boolean retrieveArabicData, VehiclePlate vehiclePlate, List<Long> violationStatusIds) throws VSDDataAccessException { logger.info("getOpenViolationsByVehiclePlate -- START"); List<VsdViolation> violations = null; try { Session session = (Session) dsContext.getInternalContext(); violations = session.createCriteria(VsdViolation.class, "v") .add(Restrictions.eq("v.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdChannelDefects", "cd", Criteria.LEFT_JOIN, Restrictions.eq("cd.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdDefect", "d", Criteria.LEFT_JOIN, Restrictions.eq("d.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("vi.vehiclePlateCategory", vehiclePlate.getPlateCategory()).ignoreCase()) .add(Restrictions.eq("vi.vehiclePlateCode", vehiclePlate.getPlateCode()).ignoreCase()) .add(Restrictions.eq("vi.vehiclePlateSource", vehiclePlate.getPlateSource()).ignoreCase()) .add(Restrictions.eq("vi.vehiclePlateNumber", vehiclePlate.getPlateNumber()).ignoreCase()) .add(Restrictions.in("v.vsdViolationStatus.violationStatusId", violationStatusIds)) .addOrder(Order.asc("v.createdTimestamp")).setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY) .list(); } catch (Exception ex) { logger.error("An error occured in getOpenViolationsByVehiclePlate()"); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getOpenViolationsByVehiclePlate -- END"); return violations; } /** * * get List of OpenViolations By VehiclePlate * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param vehiclePlate * @param violationStatusIds * @return List<VsdViolation> * @throws VSDException */ public List<VsdViolation> getViolationsByVehiclePlate(final DataServiceContext dsContext, boolean retrieveArabicData, VehiclePlate vehiclePlate) throws VSDDataAccessException { logger.info("getViolationsByVehiclePlate -- START"); List<VsdViolation> violations = null; try { Session session = (Session) dsContext.getInternalContext(); violations = session.createCriteria(VsdViolation.class, "v") .add(Restrictions.eq("v.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdLocation", "l", Criteria.LEFT_JOIN, Restrictions.eq("l.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdChannelPartInst", "cpi", Criteria.LEFT_JOIN, Restrictions.eq("cpi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdSeverityLevel", "sl", Criteria.LEFT_JOIN, Restrictions.eq("sl.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdChannelDefects", "cd", Criteria.LEFT_JOIN, Restrictions.eq("cd.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdDefect", "d", Criteria.LEFT_JOIN, Restrictions.eq("d.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("vi.vehiclePlateCategory", vehiclePlate.getPlateCategory()).ignoreCase()) .add(Restrictions.eq("vi.vehiclePlateCode", vehiclePlate.getPlateCode()).ignoreCase()) .add(Restrictions.eq("vi.vehiclePlateSource", vehiclePlate.getPlateSource()).ignoreCase()) .add(Restrictions.eq("vi.vehiclePlateNumber", vehiclePlate.getPlateNumber()).ignoreCase()) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list(); } catch (Exception ex) { logger.error("An error occured in getViolationsByVehiclePlate()"); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getViolationsByVehiclePlate -- END"); return violations; } /** * * get List of OpenViolations By VehiclePlate * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param vehiclePlate * @param violationStatusIds * @param isViolationDefect * @return List<VsdViolation> * @throws VSDException */ public List<VsdViolation> getViolationsByVehiclePlate(final DataServiceContext dsContext, boolean retrieveArabicData, VehiclePlate vehiclePlate, Character isViolationDefect) throws VSDDataAccessException { logger.info("getViolationsByVehiclePlate -- START"); List<VsdViolation> violations = null; try { Session session = (Session) dsContext.getInternalContext(); violations = session.createCriteria(VsdViolation.class, "v") .add(Restrictions.eq("v.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdLocation", "l", Criteria.LEFT_JOIN, Restrictions.eq("l.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdChannelPartInst", "cpi", Criteria.LEFT_JOIN, Restrictions.eq("cpi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdSeverityLevel", "sl", Criteria.LEFT_JOIN, Restrictions.eq("sl.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdChannelDefects", "cd", Criteria.LEFT_JOIN, Restrictions.eq("cd.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("cd.isViolationDefect", isViolationDefect)) .createCriteria("cd.vsdDefect", "d", Criteria.LEFT_JOIN, Restrictions.eq("d.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("vi.vehiclePlateCategory", vehiclePlate.getPlateCategory()).ignoreCase()) .add(Restrictions.eq("vi.vehiclePlateCode", vehiclePlate.getPlateCode()).ignoreCase()) .add(Restrictions.eq("vi.vehiclePlateSource", vehiclePlate.getPlateSource()).ignoreCase()) .add(Restrictions.eq("vi.vehiclePlateNumber", vehiclePlate.getPlateNumber()).ignoreCase()) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list(); } catch (Exception ex) { logger.error("An error occured in getViolationsByVehiclePlate()"); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getViolationsByVehiclePlate -- END"); return violations; } /** * * get List of OpenViolations By VehiclePlate * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param vehiclePlate * @param vehicleChassisNumber * @return List<VsdViolation> * @throws VSDException */ public List<VsdViolation> getViolationsByChassissNumber(final DataServiceContext dsContext, boolean retrieveArabicData, String vehicleChassisNumber) throws VSDDataAccessException { logger.info("getOpenViolationsByVehiclePlate -- START"); List<VsdViolation> violations = null; try { Session session = (Session) dsContext.getInternalContext(); violations = session.createCriteria(VsdViolation.class, "v") .add(Restrictions.eq("v.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdLocation", "l", Criteria.LEFT_JOIN, Restrictions.eq("l.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdChannelPartInst", "cpi", Criteria.LEFT_JOIN, Restrictions.eq("cpi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdSeverityLevel", "sl", Criteria.LEFT_JOIN, Restrictions.eq("sl.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdChannelDefects", "cd", Criteria.LEFT_JOIN, Restrictions.eq("cd.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdDefect", "d", Criteria.LEFT_JOIN, Restrictions.eq("d.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("vi.vehicleChassisNumber", vehicleChassisNumber).ignoreCase()) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list(); } catch (Exception ex) { logger.error("An error occured in getOpenViolationsByVehiclePlate()"); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getOpenViolationsByVehiclePlate -- END"); return violations; } /** * * get List of OpenViolations By VehiclePlate * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param vehiclePlate * @param vehicleChassisNumber * @param isViolationDefect * @return List<VsdViolation> * @throws VSDException */ public List<VsdViolation> getViolationsByChassissNumber(final DataServiceContext dsContext, boolean retrieveArabicData, String vehicleChassisNumber, Character isViolationDefect) throws VSDDataAccessException { logger.info("getOpenViolationsByVehiclePlate -- START"); List<VsdViolation> violations = null; try { Session session = (Session) dsContext.getInternalContext(); violations = session.createCriteria(VsdViolation.class, "v") .add(Restrictions.eq("v.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdLocation", "l", Criteria.LEFT_JOIN, Restrictions.eq("l.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdChannelPartInst", "cpi", Criteria.LEFT_JOIN, Restrictions.eq("cpi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdSeverityLevel", "sl", Criteria.LEFT_JOIN, Restrictions.eq("sl.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdChannelDefects", "cd", Criteria.LEFT_JOIN, Restrictions.eq("cd.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("cd.isViolationDefect", isViolationDefect)) .createCriteria("cd.vsdDefect", "d", Criteria.LEFT_JOIN, Restrictions.eq("d.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("vi.vehicleChassisNumber", vehicleChassisNumber).ignoreCase()) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list(); } catch (Exception ex) { logger.error("An error occured in getOpenViolationsByVehiclePlate()"); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getOpenViolationsByVehiclePlate -- END"); return violations; } /** * * Gets a list of violations by the violation status ids, test status and test date provided. * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param testStatus * @param startDate * @param endDate * @param violationStatusIds * @return List<VsdViolation> * @throws VSDDataAccessException */ public List<VsdViolation> getViolationsByTestStatusTestDateAndViolationStatusId(DataServiceContext dsContext, boolean retrieveArabicData, String testStatus, Date startDate, Date endDate, List<Long> violationStatusIds) throws VSDDataAccessException { logger.info("getViolationsByTestStatusTestDateAndViolationStatusId -- START"); List<VsdViolation> violations = null; try { // Calendar startDate = Calendar.getInstance(); // startDate.setTime(testDate); // 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(testDate); // 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(); violations = session.createCriteria(VsdViolation.class, "v") .add(Restrictions.eq("v.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdViolationStatus", "vs", Criteria.LEFT_JOIN, Restrictions.eq("vs.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.in("vs.violationStatusId", violationStatusIds)) .createCriteria("v.vsdViolationTestFees", "vtf", Criteria.LEFT_JOIN, Restrictions.eq("vtf.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vtf.vsdTestFee", "tf", Criteria.LEFT_JOIN, Restrictions.eq("tf.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("tf.vsdChannelTests", "ct", Criteria.LEFT_JOIN, Restrictions.eq("ct.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("ct.testStatus", testStatus)) .add(Restrictions.between("ct.createdTimestamp", startDate, endDate)) .createCriteria("v.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vi.vsdOwnerInfos", "oi", Criteria.LEFT_JOIN, Restrictions.eq("oi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdSeverityLevel", "svl", Criteria.LEFT_JOIN, Restrictions.eq("svl.isDeleted", IDataService.BOOL_FALSE)) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list(); logger.info("getViolationsByTestStatusTestDateAndViolationStatusId -- END"); return violations; } catch (Exception ex) { logger.error("An error occured in getViolationsByTestStatusTestDateAndViolationStatusId()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } } /** * * Gets a list of violations that have the status and severity Level provided and are the specified amount of days since reported and the date passed to it. * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param violationStatusId * @param severityLevelId * @param compareDate * @param issueDays * @return List<VsdViolation> * @throws VSDDataAccessException */ public List<VsdViolation> getViolationsByIssueDays(DataServiceContext dsContext, boolean retrieveArabicData, Long violationStatusId, Long severityLevelId, Date compareDate, int issueDays) throws VSDDataAccessException { logger.info("getViolationsByIssueDays -- START"); List<VsdViolation> violations = null; try { SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); Session session = (Session) dsContext.getInternalContext(); violations = session.createCriteria(VsdViolation.class, "v") .add(Restrictions.eq("v.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.sqlRestriction("to_date('" + dateFormat.format(compareDate) + "','yyyy-MM-dd') - {alias}.REPORTED_DATE = " + issueDays)) .createCriteria("v.vsdViolationStatus", "vs", Criteria.LEFT_JOIN, Restrictions.eq("vs.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("vs.violationStatusId", violationStatusId)) .createCriteria("v.vsdSeverityLevel", "s", Criteria.LEFT_JOIN, Restrictions.eq("s.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("s.severityLevelId", severityLevelId)) .createCriteria("v.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vi.vsdOwnerInfos", "oi", Criteria.LEFT_JOIN, Restrictions.eq("oi.isDeleted", IDataService.BOOL_FALSE)) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list(); logger.info("getViolationsByIssueDays -- END"); return violations; } catch (Exception ex) { logger.error("An error occured in getViolationsByIssueDays()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } } /** * * Gets a list of violations for those vehicles whose plate has been confiscated and the violation has the status provided and the inspection occurred on the specified date. * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param violationStatusIds * @param startDate * @param endDate * @return List<VsdViolation> * @throws VSDDataAccessException */ public List<VsdViolation> getPlateConfiscatedViolationsByViolationStatusIdAndInspectionDate( DataServiceContext dsContext, boolean retrieveArabicData, List<Long> violationStatusIds, Long severityLevelId, Date startDate, Date endDate) throws VSDDataAccessException { logger.info("getPlateConfiscatedViolationsByViolationStatusIdAndInspectionDate -- START"); List<VsdViolation> violations = null; try { // Calendar startDate = Calendar.getInstance(); // startDate.setTime(inspectionDate); // 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(inspectionDate); // 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(); violations = session.createCriteria(VsdViolation.class, "v") .add(Restrictions.eq("v.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdSeverityLevel", "sl", Criteria.LEFT_JOIN, Restrictions.eq("sl.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("sl.severityLevelId", severityLevelId)) .createCriteria("v.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.between("i.inspectionTimestamp", startDate, endDate)) .add(Restrictions.eq("i.isPlateConfiscated", IDataService.BOOL_TRUE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vi.vsdOwnerInfos", "oi", Criteria.LEFT_JOIN, Restrictions.eq("oi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdViolationStatus", "vs", Criteria.LEFT_JOIN, Restrictions.eq("vs.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.in("vs.violationStatusId", violationStatusIds)) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list(); logger.info("getPlateConfiscatedViolationsByViolationStatusIdAndInspectionDate -- END"); return violations; } catch (Exception ex) { logger.error("An error occured in getPlateConfiscatedViolationsByViolationStatusIdAndInspectionDate()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } } /** * * Gets a list of violations which have the specified block status and occured on the provided date. * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param blockStatus * @param startDate * @param endDate * @return List<VsdViolation> * @throws VSDDataAccessException */ public List<VsdViolation> getViolationsByBlockStatusAndBlockDate(DataServiceContext dsContext, boolean retrieveArabicData, Character blockStatus, Date startDate, Date endDate) throws VSDDataAccessException { logger.info("getViolationsByBlockStatusAndBlockDate -- START"); List<VsdViolation> violations = null; try { // Calendar startDate = Calendar.getInstance(); // startDate.setTime(blockDate); // 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(blockDate); // 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(); violations = session.createCriteria(VsdViolation.class, "v") .add(Restrictions.eq("v.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdViolServiceBlocks", "vsb", Criteria.LEFT_JOIN, Restrictions.eq("vsb.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("vsb.blockStatus", blockStatus)) .add(Restrictions.between("vsb.blockedTimestamp", startDate, endDate)) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list(); logger.info("getViolationsByBlockStatusAndBlockDate -- END"); return violations; } catch (Exception ex) { logger.error("An error occured in getViolationsByBlockStatusAndBlockDate()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } } /** * * Gets a list of total violations by month with their test attempts that have the specified test and violation status and occurred between the provided dates. * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param testStatus * @param violationStatusIds * @param startDate * @param endDate * @return List<CustomTestStatusDTO> * @throws VSDDataAccessException */ public List<CustomTestStatusDTO> getMonthlyViolationCountWithTestAttemptsByViolationTestStatusAndDates( DataServiceContext dsContext, boolean retrieveArabicData, String testStatus, List<Long> violationStatusIds, Date startDate, Date endDate) throws VSDDataAccessException { logger.info("getMonthlyViolationCountWithTestAttemptsByViolationTestStatusAndDates -- START"); /*SELECT COUNT(v.VIOLATION_ID), to_char(v.DUE_DATE,'mm') , ct.TEST_ATTEMPT FROM VSD_VIOLATION v JOIN VSD_VIOLATION_TEST_FEE vtf ON v.VIOLATION_ID = vtf.VIOLATION_ID AND vtf.IS_DELETED = 'F' JOIN VSD_TEST_FEE tf ON vtf.TEST_FEE_ID = tf.TEST_FEE_ID AND tf.IS_DELETED = 'F' JOIN VSD_CHANNEL_TEST ct ON tf.TEST_FEE_ID = ct.TEST_FEE_ID AND ct.IS_DELETED = 'F' AND ct.TEST_STATUS = 'Pass' AND ct.TEST_ATTEMPT < 3 Where v.IS_DELETED = 'F' AND v.VIOLATION_STATUS_ID = 1 AND v.DUE_DATE BETWEEN '' AND '' GROUP BY to_char(v.DUE_DATE,'mm'), ct.TEST_ATTEMPT*/ try { // Calendar start = Calendar.getInstance(); // Calendar end = Calendar.getInstance(); // if(startDate != null && endDate != null ) { // start = Calendar.getInstance(); // start.setTime(startDate); // int year = start.get(Calendar.YEAR); // int month = start.get(Calendar.MONTH); // start.clear(); // start.set(year, month,1); // end = Calendar.getInstance(); // end.setTime(endDate); // year = end.get(Calendar.YEAR); // month = end.get(Calendar.MONTH); // end.clear(); // if(month == Calendar.APRIL || month == Calendar.JUNE || month == Calendar.SEPTEMBER || month == Calendar.NOVEMBER) { // end.set(year, month,30); // } // else if(month == Calendar.FEBRUARY) { // if(year%4==0) { // end.set(year, month,29); // } // else { // end.set(year, month,28); // } // // } // else { // end.set(year, month,31); // } // } String violationStatuses = null; if (violationStatusIds != null) { Iterator<Long> iter = violationStatusIds.iterator(); while (iter.hasNext()) { Long violationStatusId = (Long) iter.next(); if (violationStatuses == null) { violationStatuses = new String(); violationStatuses = violationStatusId.toString(); } else { violationStatuses = violationStatuses + "," + violationStatusId.toString(); } } } Session session = (Session) dsContext.getInternalContext(); String defaultSchema = DataServiceImpl.DEFAULT_SCHEMA_NAME; StringBuffer query = new StringBuffer( "SELECT COUNT(v.VIOLATION_ID) AS \"violationCount\", to_char(v.DUE_DATE,'mm') AS \"month\", ct.TEST_ATTEMPT as \"testAttempt\" " + " FROM " + defaultSchema + ".VSD_VIOLATION v " + " JOIN " + defaultSchema + ".VSD_VIOLATION_TEST_FEE vtf ON v.VIOLATION_ID = vtf.VIOLATION_ID AND vtf.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " JOIN " + defaultSchema + ".VSD_TEST_FEE tf ON vtf.TEST_FEE_ID = tf.TEST_FEE_ID AND tf.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " JOIN " + defaultSchema + ".VSD_CHANNEL_TEST ct ON tf.TEST_FEE_ID = ct.TEST_FEE_ID AND ct.IS_DELETED = '" + IDataService.BOOL_FALSE + "' AND ct.TEST_STATUS = '" + testStatus + "' AND ct.TEST_ATTEMPT < 3 " + " WHERE v.IS_DELETED = '" + IDataService.BOOL_FALSE + "' AND v.VIOLATION_STATUS_ID in (" + violationStatuses + ") "); if (startDate != null && endDate != null) { SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); query.append(" AND to_date(to_char(v.DUE_DATE,'YYYY-MM-DD'),'YYYY-MM-DD') BETWEEN to_date('" + dateFormat.format(startDate) + "','YYYY-MM-DD') AND to_date('" + dateFormat.format(endDate) + "','YYYY-MM-DD') "); } query.append(" GROUP BY to_char(v.DUE_DATE,'mm'), ct.TEST_ATTEMPT ORDER BY to_char(v.DUE_DATE,'mm') "); List<CustomTestStatusDTO> returnObj = session.createSQLQuery(query.toString()) .addScalar("violationCount", Hibernate.LONG).addScalar("month", Hibernate.INTEGER) .addScalar("testAttempt", Hibernate.LONG) .setResultTransformer(new AliasToBeanResultTransformer(CustomTestStatusDTO.class)).list(); logger.info("getMonthlyViolationCountWithTestAttemptsByViolationTestStatusAndDates -- END"); return returnObj; } catch (Exception ex) { logger.error( "An error occured in getMonthlyViolationCountWithTestAttemptsByViolationTestStatusAndDates()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } } /** * * Gets a list of total violations by month with more than 3 test attempts that have the specified test and violation status and occurred between the provided dates. * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param testStatus * @param violationStatusIds * @param startDate * @param endDate * @return List<CustomTestStatusDTO> * @throws VSDDataAccessException */ public List<CustomTestStatusDTO> getMonthlyViolationCountWithMoreThanThreeTestAttemptsByViolationTestStatusAndDates( DataServiceContext dsContext, boolean retrieveArabicData, String testStatus, List<Long> violationStatusIds, Date startDate, Date endDate) throws VSDDataAccessException { logger.info("getMonthlyViolationCountWithMoreThanThreeTestAttemptsByViolationTestStatusAndDates -- START"); /*SELECT COUNT(v.VIOLATION_ID), to_char(v.DUE_DATE,'mm') , ct.TEST_ATTEMPT FROM VSD_VIOLATION v JOIN VSD_VIOLATION_TEST_FEE vtf ON v.VIOLATION_ID = vtf.VIOLATION_ID AND vtf.IS_DELETED = 'F' JOIN VSD_TEST_FEE tf ON vtf.TEST_FEE_ID = tf.TEST_FEE_ID AND tf.IS_DELETED = 'F' JOIN VSD_CHANNEL_TEST ct ON tf.TEST_FEE_ID = ct.TEST_FEE_ID AND ct.IS_DELETED = 'F' AND ct.TEST_STATUS = 'Pass' AND ct.TEST_ATTEMPT >= 3 Where v.IS_DELETED = 'F' AND v.VIOLATION_STATUS_ID = 1 AND v.DUE_DATE BETWEEN '' AND '' GROUP BY to_char(v.DUE_DATE,'mm'), ct.TEST_ATTEMPT*/ try { // Calendar start = Calendar.getInstance(); // Calendar end = Calendar.getInstance(); // if(startDate != null && endDate != null ) { // start = Calendar.getInstance(); // start.setTime(startDate); // int year = start.get(Calendar.YEAR); // int month = start.get(Calendar.MONTH); // start.clear(); // start.set(year, month,1); // end = Calendar.getInstance(); // end.setTime(endDate); // year = end.get(Calendar.YEAR); // month = end.get(Calendar.MONTH); // end.clear(); // if(month == Calendar.APRIL || month == Calendar.JUNE || month == Calendar.SEPTEMBER || month == Calendar.NOVEMBER) { // end.set(year, month,30); // } // else if(month == Calendar.FEBRUARY) { // if(year%4==0) { // end.set(year, month,29); // } // else { // end.set(year, month,28); // } // // } // else { // end.set(year, month,31); // } // } String violationStatuses = null; if (violationStatusIds != null) { Iterator<Long> iter = violationStatusIds.iterator(); while (iter.hasNext()) { Long violationStatusId = (Long) iter.next(); if (violationStatuses == null) { violationStatuses = new String(); violationStatuses = violationStatusId.toString(); } else { violationStatuses = violationStatuses + "," + violationStatusId.toString(); } } } Session session = (Session) dsContext.getInternalContext(); String defaultSchema = DataServiceImpl.DEFAULT_SCHEMA_NAME; StringBuffer query = new StringBuffer( "SELECT COUNT(v.VIOLATION_ID) AS \"violationCount\", to_char(v.DUE_DATE,'mm') AS \"month\", ct.TEST_ATTEMPT as \"testAttempt\" " + " FROM " + defaultSchema + ".VSD_VIOLATION v " + " JOIN " + defaultSchema + ".VSD_VIOLATION_TEST_FEE vtf ON v.VIOLATION_ID = vtf.VIOLATION_ID AND vtf.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " JOIN " + defaultSchema + ".VSD_TEST_FEE tf ON vtf.TEST_FEE_ID = tf.TEST_FEE_ID AND tf.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " JOIN " + defaultSchema + ".VSD_CHANNEL_TEST ct ON tf.TEST_FEE_ID = ct.TEST_FEE_ID AND ct.IS_DELETED = '" + IDataService.BOOL_FALSE + "' AND ct.TEST_STATUS = '" + testStatus + "' AND ct.TEST_ATTEMPT >= 3" + " WHERE v.IS_DELETED = '" + IDataService.BOOL_FALSE + "' AND v.VIOLATION_STATUS_ID in (" + violationStatuses + ") "); if (startDate != null && endDate != null) { SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); query.append(" AND to_date(to_char(v.DUE_DATE,'YYYY-MM-DD'),'YYYY-MM-DD') BETWEEN to_date('" + dateFormat.format(startDate) + "','YYYY-MM-DD') AND to_date('" + dateFormat.format(endDate) + "','YYYY-MM-DD') "); } query.append(" GROUP BY to_char(v.DUE_DATE,'mm'), ct.TEST_ATTEMPT ORDER BY to_char(v.DUE_DATE,'mm') "); List<CustomTestStatusDTO> returnObj = session.createSQLQuery(query.toString()) .addScalar("violationCount", Hibernate.LONG).addScalar("month", Hibernate.INTEGER) .addScalar("testAttempt", Hibernate.LONG) .setResultTransformer(new AliasToBeanResultTransformer(CustomTestStatusDTO.class)).list(); logger.info( "getMonthlyViolationCountWithMoreThanThreeTestAttemptsByViolationTestStatusAndDates -- END"); return returnObj; } catch (Exception ex) { logger.error( "An error occured in getMonthlyViolationCountWithMoreThanThreeTestAttemptsByViolationTestStatusAndDates()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } } /** * * Gets a list of total violations by month that have the specified violation status and occurred between the provided dates. * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param violationStatusId * @param startDate * @param endDate * @return List<CustomTestStatusDTO> * @throws VSDDataAccessException */ public List<CustomTestStatusDTO> getMonthlyViolationCountByViolationStatusAndDates(DataServiceContext dsContext, boolean retrieveArabicData, Long violationStatusId, Date startDate, Date endDate) throws VSDDataAccessException { logger.info("getMonthlyViolationCountByViolationStatusAndDates -- START"); /*SELECT COUNT(v.VIOLATION_ID) AS \"violationCount\", to_char(v.DUE_DATE,'mm') AS \"month\" FROM VSD_VIOLATION v JOIN VSD_VIOLATION_TEST_FEE vtf ON v.VIOLATION_ID = vtf.VIOLATION_ID AND vtf.IS_DELETED = 'F' JOIN VSD_TEST_FEE tf ON vtf.TEST_FEE_ID = tf.TEST_FEE_ID AND tf.IS_DELETED = 'F' join VSD_CHANNEL_TEST ct ON tf.TEST_FEE_ID = ct.TEST_FEE_ID AND ct.IS_DELETED = 'F' Where v.IS_DELETED = 'F' AND v.VIOLATION_STATUS_ID = 3 AND v.DUE_DATE BETWEEN '' AND '' GROUP BY to_char(v.DUE_DATE,'mm')*/ try { // Calendar start = Calendar.getInstance(); // Calendar end = Calendar.getInstance(); // if(startDate != null && endDate != null ) { // start = Calendar.getInstance(); // start.setTime(startDate); // int year = start.get(Calendar.YEAR); // int month = start.get(Calendar.MONTH); // start.clear(); // start.set(year, month,1); // end = Calendar.getInstance(); // end.setTime(endDate); // year = end.get(Calendar.YEAR); // month = end.get(Calendar.MONTH); // end.clear(); // if(month == Calendar.APRIL || month == Calendar.JUNE || month == Calendar.SEPTEMBER || month == Calendar.NOVEMBER) { // end.set(year, month,30); // } // else if(month == Calendar.FEBRUARY) { // if(year%4==0) { // end.set(year, month,29); // } // else { // end.set(year, month,28); // } // // } // else { // end.set(year, month,31); // } // } Session session = (Session) dsContext.getInternalContext(); String defaultSchema = DataServiceImpl.DEFAULT_SCHEMA_NAME; StringBuffer query = new StringBuffer( "SELECT COUNT(v.VIOLATION_ID) AS \"violationCount\", to_char(v.DUE_DATE,'mm') AS \"month\" " + " FROM " + defaultSchema + ".VSD_VIOLATION v " + " JOIN " + defaultSchema + ".VSD_VIOLATION_TEST_FEE vtf ON v.VIOLATION_ID = vtf.VIOLATION_ID AND vtf.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " JOIN " + defaultSchema + ".VSD_TEST_FEE tf ON vtf.TEST_FEE_ID = tf.TEST_FEE_ID AND tf.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " JOIN " + defaultSchema + ".VSD_CHANNEL_TEST ct ON tf.TEST_FEE_ID = ct.TEST_FEE_ID AND ct.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " WHERE v.IS_DELETED = '" + IDataService.BOOL_FALSE + "' AND v.VIOLATION_STATUS_ID = " + violationStatusId); if (startDate != null && endDate != null) { SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); query.append(" AND to_date(to_char(v.DUE_DATE,'YYYY-MM-DD'),'YYYY-MM-DD') BETWEEN to_date('" + dateFormat.format(startDate) + "','YYYY-MM-DD') AND to_date('" + dateFormat.format(endDate) + "','YYYY-MM-DD') "); } query.append(" GROUP BY to_char(v.DUE_DATE,'mm') ORDER BY to_char(v.DUE_DATE,'mm') "); List<CustomTestStatusDTO> returnObj = session.createSQLQuery(query.toString()) .addScalar("violationCount", Hibernate.LONG).addScalar("month", Hibernate.INTEGER) .setResultTransformer(new AliasToBeanResultTransformer(CustomTestStatusDTO.class)).list(); logger.info("getMonthlyViolationCountByViolationStatusAndDates -- END"); return returnObj; } catch (Exception ex) { logger.error("An error occured in getMonthlyViolationCountByViolationStatusAndDates()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } } /** * * Gets a list of total violations by month which are no shows for tests and that have the specified violation status and occurred between the provided dates. * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param violationStatusId * @param startDate * @param endDate * @return List<CustomTestStatusDTO> * @throws VSDDataAccessException */ public List<CustomTestStatusDTO> getMonthlyNoShowViolationCountByViolationStatusAndDates( DataServiceContext dsContext, boolean retrieveArabicData, Long violationStatusId, Date startDate, Date endDate) throws VSDDataAccessException { logger.info("getMonthlyNoShowViolationCountByViolationStatusAndDates -- START"); /*SELECT COUNT(v.VIOLATION_ID) AS \"violationCount\", to_char(v.DUE_DATE,'mm') AS \"month\" FROM VSD_VIOLATION v --JOIN VSD_VIOLATION_TEST_FEE vtf ON v.VIOLATION_ID <> vtf.VIOLATION_ID Where v.IS_DELETED = 'F' AND v.VIOLATION_STATUS_ID = 3 AND v.TEST_ATTEMPTS = 0 AND v.DUE_DATE BETWEEN '' AND '' GROUP BY to_char(v.DUE_DATE,'mm')*/ try { // Calendar start = Calendar.getInstance(); // Calendar end = Calendar.getInstance(); // if(startDate != null && endDate != null ) { // start = Calendar.getInstance(); // start.setTime(startDate); // int year = start.get(Calendar.YEAR); // int month = start.get(Calendar.MONTH); // start.clear(); // start.set(year, month,1); // end = Calendar.getInstance(); // end.setTime(endDate); // year = end.get(Calendar.YEAR); // month = end.get(Calendar.MONTH); // end.clear(); // if(month == Calendar.APRIL || month == Calendar.JUNE || month == Calendar.SEPTEMBER || month == Calendar.NOVEMBER) { // end.set(year, month,30); // } // else if(month == Calendar.FEBRUARY) { // if(year%4==0) { // end.set(year, month,29); // } // else { // end.set(year, month,28); // } // // } // else { // end.set(year, month,31); // } // } Session session = (Session) dsContext.getInternalContext(); String defaultSchema = DataServiceImpl.DEFAULT_SCHEMA_NAME; StringBuffer query = new StringBuffer( "SELECT COUNT(v.VIOLATION_ID) AS \"violationCount\", to_char(v.DUE_DATE,'mm') AS \"month\" " + " FROM " + defaultSchema + ".VSD_VIOLATION v " + " WHERE v.IS_DELETED = '" + IDataService.BOOL_FALSE + "' AND v.TEST_ATTEMPTS = 0 AND v.VIOLATION_STATUS_ID = " + violationStatusId); if (startDate != null && endDate != null) { SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); query.append(" AND to_date(to_char(v.DUE_DATE,'YYYY-MM-DD'),'YYYY-MM-DD') BETWEEN to_date('" + dateFormat.format(startDate) + "','YYYY-MM-DD') AND to_date('" + dateFormat.format(endDate) + "','YYYY-MM-DD') "); } query.append(" GROUP BY to_char(v.DUE_DATE,'mm') ORDER BY to_char(v.DUE_DATE,'mm') "); List<CustomTestStatusDTO> returnObj = session.createSQLQuery(query.toString()) .addScalar("violationCount", Hibernate.LONG).addScalar("month", Hibernate.INTEGER) .setResultTransformer(new AliasToBeanResultTransformer(CustomTestStatusDTO.class)).list(); logger.info("getMonthlyNoShowViolationCountByViolationStatusAndDates -- END"); return returnObj; } catch (Exception ex) { logger.error("An error occured in getMonthlyNoShowViolationCountByViolationStatusAndDates()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } } /** * * Gets a list of defect fine amount from the violation id and partner code provided. * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param violationId * @param partnerCode * @return List<DefectFineAmount> * @throws VSDDataAccessException */ public List<DefectFineAmount> getDefectAndFineAmountByViolationIdPartnerCode(DataServiceContext dsContext, boolean retrieveArabicData, Long violationId, String partnerCode) throws VSDDataAccessException { logger.info("getDefectAndFineAmountByViolationIdPartnerCode -- START"); List<DefectFineAmount> defectFineAmounts = null; try { Session session = (Session) dsContext.getInternalContext(); ProjectionList projectionList = Projections.projectionList(); projectionList.add(Projections.property("d.defectName"), "defectName"); projectionList.add(Projections.property("cdf.fineAmount"), "fineAmount"); defectFineAmounts = session.createCriteria(VsdViolation.class, "v") .add(Restrictions.eq("v.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("v.violationId", violationId)) .createCriteria("v.vsdChannelDefects", "cd", Criteria.INNER_JOIN, Restrictions.eq("cd.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdChannelDefectFines", "cdf", Criteria.INNER_JOIN, Restrictions.eq("cdf.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cdf.vsdVCatDefSevFine", "vcdsf", Criteria.INNER_JOIN, Restrictions.eq("vcdsf.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vcdsf.vsdPartnerFine", "pf", Criteria.INNER_JOIN, Restrictions.eq("pf.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("pf.vsdPartner", "p", Criteria.INNER_JOIN, Restrictions.eq("p.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("p.partnerCode", partnerCode).ignoreCase()) .createCriteria("cd.vsdDefect", "d", Criteria.INNER_JOIN, Restrictions.eq("d.isDeleted", IDataService.BOOL_FALSE)) .setProjection(projectionList) .setResultTransformer(new AliasToBeanResultTransformer(DefectFineAmount.class)).list(); logger.info("getDefectAndFineAmountByViolationIdPartnerCode -- END"); return defectFineAmounts; } catch (Exception ex) { logger.error("An error occured in getDefectAndFineAmountByViolationIdPartnerCode()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } } /** * * Gets a list of defect fine amount from the violation id provided. * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param violationId * @param partnerCode * @return List<DefectFineAmount> * @throws VSDDataAccessException */ public List<ViolationDefectFine> getDefectAndFineAmountByViolationId(DataServiceContext dsContext, boolean retrieveArabicData, Long violationId) throws VSDDataAccessException { logger.info("getDefectAndFineAmountByViolationId -- START"); try { Session session = (Session) dsContext.getInternalContext(); String query = "select D.DEFECT_ID as \"defectId\", D.DEFECT_NAME AS \"defectName\", D.DEFECT_TYPE_A as \"defectNameA\", SUM( C.FINE_AMOUNT ) as \"fineAmount\"" + " FROM VSD_VIOLATION A, VSD_CHANNEL_DEFECT B, VSD_CHANNEL_DEFECT_FINE C, VSD_DEFECT D" + " WHERE A.VIOLATION_ID = B.VIOLATION_ID AND B.IS_DELETED = '" + IDataService.BOOL_FALSE + "'" + " AND B.CHANNEL_DEFECT_ID = C.CHANNEL_DEFECT_ID AND C.IS_DELETED = '" + IDataService.BOOL_FALSE + "'" + " AND B.DEFECT_ID = D.DEFECT_ID AND D.IS_DELETED = '" + IDataService.BOOL_FALSE + "'" + " AND A.VIOLATION_ID = " + violationId + " " + " AND A.IS_DELETED = '" + IDataService.BOOL_FALSE + "'" + " GROUP BY D.DEFECT_ID, D.DEFECT_NAME , D.DEFECT_TYPE_A"; List<ViolationDefectFine> violationDefectFines = session.createSQLQuery(query) .addScalar("defectId", Hibernate.LONG).addScalar("defectName").addScalar("defectNameA") .addScalar("fineAmount", Hibernate.LONG) .setResultTransformer(new AliasToBeanResultTransformer(ViolationDefectFine.class)).list(); logger.info("getDefectAndFineAmountByViolationId -- END"); return violationDefectFines; } catch (Exception ex) { logger.error("An error occured in getDefectAndFineAmountByViolationId()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } } /** * * Gets a violation with its status, inspections, location, vehicle info, driver info, owner info, channel partner instance, channel partner instance, channel defect, severity level and channel defect fine * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param violationId * @return VsdViolation * @throws VSDDataAccessException */ public VsdViolation getViolationWithInspectionsVehDriverOwnerInfoChannelDefectFineById( DataServiceContext dsContext, boolean retrieveArabicData, Long violationId) throws VSDDataAccessException { logger.info("getViolationByViolationId -- START"); VsdViolation violation = null; try { Session session = (Session) dsContext.getInternalContext(); violation = (VsdViolation) session.createCriteria(VsdViolation.class, "v") .add(Restrictions.eq("v.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("v.violationId", violationId)) .createCriteria("v.vsdViolationStatus", "vs", Criteria.LEFT_JOIN, Restrictions.eq("vs.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdSeverityLevel", "sl", Criteria.LEFT_JOIN, Restrictions.eq("sl.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdLocation", "loc", Criteria.LEFT_JOIN, Restrictions.eq("loc.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("loc.vsdLocation", "area", Criteria.LEFT_JOIN, Restrictions.eq("area.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vi.vsdDriverInfos", "di", Criteria.LEFT_JOIN, Restrictions.eq("di.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vi.vsdOwnerInfos", "oi", Criteria.LEFT_JOIN, Restrictions.eq("oi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdChannelDefects", "cd", Criteria.LEFT_JOIN, Restrictions.eq("cd.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdChannelPartInst", "cpi", Criteria.LEFT_JOIN, Restrictions.eq("cpi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdChannelDefectFines", "cdf", Criteria.LEFT_JOIN, Restrictions.eq("cdf.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdDefect", "d", Criteria.LEFT_JOIN, Restrictions.eq("d.isDeleted", IDataService.BOOL_FALSE)) .uniqueResult(); logger.info("getViolationByViolationId -- END"); return violation; } catch (Exception ex) { logger.error("An error occured in getViolationByViolationId()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } } /** * * Gets a violation with its status, inspections, location, vehicle info, driver info, owner info, channel partner instance, channel partner instance, channel defect, severity level and channel defect fine * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param violationId * @param isViolationDefect * @return VsdViolation * @throws VSDDataAccessException */ public VsdViolation getViolationWithInspectionsVehDriverOwnerInfoChannelDefectFineById( DataServiceContext dsContext, boolean retrieveArabicData, Long violationId, Character isViolationDefect) throws VSDDataAccessException { logger.info("getViolationByViolationId -- START"); VsdViolation violation = null; try { Session session = (Session) dsContext.getInternalContext(); violation = (VsdViolation) session.createCriteria(VsdViolation.class, "v") .add(Restrictions.eq("v.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("v.violationId", violationId)) .createCriteria("v.vsdViolationStatus", "vs", Criteria.LEFT_JOIN, Restrictions.eq("vs.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdSeverityLevel", "sl", Criteria.LEFT_JOIN, Restrictions.eq("sl.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdLocation", "loc", Criteria.LEFT_JOIN, Restrictions.eq("loc.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("loc.vsdLocation", "area", Criteria.LEFT_JOIN, Restrictions.eq("area.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vi.vsdDriverInfos", "di", Criteria.LEFT_JOIN, Restrictions.eq("di.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vi.vsdOwnerInfos", "oi", Criteria.LEFT_JOIN, Restrictions.eq("oi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdChannelDefects", "cd", Criteria.LEFT_JOIN, Restrictions.eq("cd.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("cd.isViolationDefect", isViolationDefect)) .createCriteria("cd.vsdChannelPartInst", "cpi", Criteria.LEFT_JOIN, Restrictions.eq("cpi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdChannelDefectFines", "cdf", Criteria.LEFT_JOIN, Restrictions.eq("cdf.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdDefect", "d", Criteria.LEFT_JOIN, Restrictions.eq("d.isDeleted", IDataService.BOOL_FALSE)) .uniqueResult(); logger.info("getViolationByViolationId -- END"); return violation; } catch (Exception ex) { logger.error("An error occured in getViolationByViolationId()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } } /** * * get Violations with tests By TrafficFileNumber And ChassisNumber * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param trafficFileNumber * @param List<vehicleChassisNumber> * @return List<Violation> * @throws VSDDataAccessException */ public List<VsdViolation> getViolationsWithTestsByTrafficFileNumberAndChassisNumber( final DataServiceContext dsContext, boolean retrieveArabicData, String trafficFileNumber, List<String> vehicleChassisNumber, int maxResults) throws VSDDataAccessException { logger.info("getViolationsWithTestsByTrafficFileNumberAndChassisNumber -- START"); StringBuffer vehicleChassisNumberString = new StringBuffer(128); vehicleChassisNumberString.append("("); Iterator iterator = vehicleChassisNumber.iterator(); while (iterator.hasNext()) { String chassissNumber = (String) iterator.next(); vehicleChassisNumberString.append("'").append(chassissNumber.toLowerCase()).append("'"); if (iterator.hasNext()) vehicleChassisNumberString.append(", "); } vehicleChassisNumberString.append(")"); List<VsdViolation> violations; try { Session session = (Session) dsContext.getInternalContext(); ProjectionList projectionList = Projections.projectionList(); projectionList.add(Projections.property("violation.violationId"), "violationId"); projectionList.add(Projections.property("violation.reportedDate"), "reportedDate"); Criteria projectionCriteria = session.createCriteria(VsdViolation.class, "violation") .add(Restrictions.eq("violation.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violation.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.sqlRestriction( "lower ({alias}.VEHICLE_CHASSIS_NUMBER) IN " + vehicleChassisNumberString)) .createCriteria("vi.vsdOwnerInfos", "oi", Criteria.LEFT_JOIN, Restrictions.eq("oi.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("oi.trafficFileNumber", trafficFileNumber).ignoreCase()) .createCriteria("violation.vsdChannelDefects", "cd", Criteria.LEFT_JOIN, Restrictions.eq("cd.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdChannelDefectTests", "cdt", Criteria.LEFT_JOIN, Restrictions.eq("cdt.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cdt.vsdChannelTest", "ct", Criteria.LEFT_JOIN, Restrictions.eq("ct.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.isNotNull("ct.createdTimestamp")) .createCriteria("ct.vsdChannelPartInst", "cpi", Criteria.LEFT_JOIN, Restrictions.eq("cpi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cpi.vsdChannelPartner", "cp", Criteria.LEFT_JOIN, Restrictions.eq("cp.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cp.vsdPartner", "p", Criteria.LEFT_JOIN, Restrictions.eq("p.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("ct.vsdTestType", "tt", Criteria.LEFT_JOIN, Restrictions.eq("tt.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdChannelDefectFines", "cdf", Criteria.LEFT_JOIN, Restrictions.eq("cdf.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cdf.vsdVCatDefSevFine", "vcdsf", Criteria.LEFT_JOIN, Restrictions.eq("vcdsf.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vcdsf.vsdPartnerFine", "pf", Criteria.LEFT_JOIN, Restrictions.eq("pf.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("pf.vsdPartner", "part", Criteria.LEFT_JOIN, Restrictions.eq("part.isDeleted", IDataService.BOOL_FALSE)) .setProjection(Projections.distinct(projectionList)) .addOrder(Order.desc("violation.reportedDate")) .setResultTransformer(new AliasToBeanResultTransformer(VsdViolation.class)) .setMaxResults(maxResults); List list = projectionCriteria.list(); logger.debug("list.size() : " + list.size()); Set resultSet = new HashSet(list); logger.debug("resultSet.size() : " + resultSet.size()); if (resultSet.size() == 0) return new ArrayList(); iterator = resultSet.iterator(); ArrayList innerQueryList = new ArrayList<Long>(); while (iterator.hasNext()) { VsdViolation vsdViolation = (VsdViolation) iterator.next(); innerQueryList.add(vsdViolation.getViolationId()); } violations = session.createCriteria(VsdViolation.class, "violation") .add(Restrictions.eq("violation.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violation.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.sqlRestriction( "lower ({alias}.VEHICLE_CHASSIS_NUMBER) IN " + vehicleChassisNumberString)) .createCriteria("vi.vsdOwnerInfos", "oi", Criteria.LEFT_JOIN, Restrictions.eq("oi.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("oi.trafficFileNumber", trafficFileNumber).ignoreCase()) .createCriteria("violation.vsdChannelDefects", "cd", Criteria.LEFT_JOIN, Restrictions.eq("cd.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdChannelDefectTests", "cdt", Criteria.LEFT_JOIN, Restrictions.eq("cdt.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cdt.vsdChannelTest", "ct", Criteria.LEFT_JOIN, Restrictions.eq("ct.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.isNotNull("ct.createdTimestamp")) .createCriteria("ct.vsdChannelPartInst", "cpi", Criteria.LEFT_JOIN, Restrictions.eq("cpi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cpi.vsdChannelPartner", "cp", Criteria.LEFT_JOIN, Restrictions.eq("cp.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cp.vsdPartner", "p", Criteria.LEFT_JOIN, Restrictions.eq("p.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("ct.vsdTestType", "tt", Criteria.LEFT_JOIN, Restrictions.eq("tt.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdChannelDefectFines", "cdf", Criteria.LEFT_JOIN, Restrictions.eq("cdf.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cdf.vsdVCatDefSevFine", "vcdsf", Criteria.LEFT_JOIN, Restrictions.eq("vcdsf.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vcdsf.vsdPartnerFine", "pf", Criteria.LEFT_JOIN, Restrictions.eq("pf.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("pf.vsdPartner", "part", Criteria.LEFT_JOIN, Restrictions.eq("part.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.in("violation.violationId", innerQueryList)) .addOrder(Order.desc("violation.reportedDate")) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list(); logger.debug("violations.size() : " + violations.size()); } catch (Exception ex) { logger.error("An error occured in getViolationsWithTestsByTrafficFileNumberAndChassisNumber()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getViolationsWithTestsByTrafficFileNumberAndChassisNumber -- END"); return violations; } /** * * get Violations with tests By TrafficFileNumber And ChassisNumber * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param trafficFileNumber * @param List<vehicleChassisNumber> * @return List<Violation> * @throws VSDDataAccessException */ public List<VsdViolation> getViolationsWithTestsByTrafficFileNumberAndChassisNumber( final DataServiceContext dsContext, boolean retrieveArabicData, String trafficFileNumber, List<String> vehicleChassisNumber) throws VSDDataAccessException { logger.info("getViolationsWithTestsByTrafficFileNumberAndChassisNumber -- START"); StringBuffer vehicleChassisNumberString = new StringBuffer(128); vehicleChassisNumberString.append("("); Iterator iterator = vehicleChassisNumber.iterator(); while (iterator.hasNext()) { String chassissNumber = (String) iterator.next(); vehicleChassisNumberString.append("'").append(chassissNumber.toLowerCase()).append("'"); if (iterator.hasNext()) vehicleChassisNumberString.append(", "); } vehicleChassisNumberString.append(")"); List<VsdViolation> violations; try { Session session = (Session) dsContext.getInternalContext(); Criteria criteria = session.createCriteria(VsdViolation.class, "violation") .add(Restrictions.eq("violation.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("violation.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.sqlRestriction( "lower ({alias}.VEHICLE_CHASSIS_NUMBER) IN " + vehicleChassisNumberString)) .createCriteria("vi.vsdOwnerInfos", "oi", Criteria.LEFT_JOIN, Restrictions.eq("oi.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("oi.trafficFileNumber", trafficFileNumber).ignoreCase()) .createCriteria("violation.vsdChannelDefects", "cd", Criteria.LEFT_JOIN, Restrictions.eq("cd.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdChannelDefectTests", "cdt", Criteria.LEFT_JOIN, Restrictions.eq("cdt.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cdt.vsdChannelTest", "ct", Criteria.LEFT_JOIN, Restrictions.eq("ct.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.isNotNull("ct.createdTimestamp")) .createCriteria("ct.vsdChannelPartInst", "cpi", Criteria.LEFT_JOIN, Restrictions.eq("cpi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cpi.vsdChannelPartner", "cp", Criteria.LEFT_JOIN, Restrictions.eq("cp.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cp.vsdPartner", "p", Criteria.LEFT_JOIN, Restrictions.eq("p.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("ct.vsdTestType", "tt", Criteria.LEFT_JOIN, Restrictions.eq("tt.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdChannelDefectFines", "cdf", Criteria.LEFT_JOIN, Restrictions.eq("cdf.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cdf.vsdVCatDefSevFine", "vcdsf", Criteria.LEFT_JOIN, Restrictions.eq("vcdsf.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vcdsf.vsdPartnerFine", "pf", Criteria.LEFT_JOIN, Restrictions.eq("pf.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("pf.vsdPartner", "part", Criteria.LEFT_JOIN, Restrictions.eq("part.isDeleted", IDataService.BOOL_FALSE)) .addOrder(Order.desc("violation.reportedDate")) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY); violations = criteria.list(); logger.debug("violations.size() : " + violations.size()); } catch (Exception ex) { logger.error("An error occured in getViolationsWithTestsByTrafficFileNumberAndChassisNumber()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getViolationsWithTestsByTrafficFileNumberAndChassisNumber -- END"); return violations; } /** * * Gets a violation with their channel defect, channel defect test and channel test information * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param violationId * @return VsdViolation * @throws VSDDataAccessException */ public VsdViolation getViolationWithChannelDefectTestAndChannelTestById(DataServiceContext dsContext, boolean retrieveArabicData, Long violationId) throws VSDDataAccessException { logger.info("getViolationWithChannelDefectTestAndChannelTestById -- START"); VsdViolation violation = null; try { Session session = (Session) dsContext.getInternalContext(); violation = (VsdViolation) session.createCriteria(VsdViolation.class, "v") .add(Restrictions.eq("v.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("v.violationId", violationId)) .createCriteria("v.vsdChannelDefects", "cd", Criteria.LEFT_JOIN, Restrictions.eq("cd.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdDefect", "d", Criteria.LEFT_JOIN, Restrictions.eq("d.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdChannelDefectTests", "cdt", Criteria.LEFT_JOIN, Restrictions.eq("cdt.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cdt.vsdChannelTest", "cht", Criteria.LEFT_JOIN, Restrictions.eq("cht.isDeleted", IDataService.BOOL_FALSE)) .uniqueResult(); logger.info("getViolationWithChannelDefectTestAndChannelTestById -- END"); return violation; } catch (Exception ex) { logger.error("An error occured in getViolationWithChannelDefectTestAndChannelTestById()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } } /** * * Gets a list of violations that occurred between the given dates for the specified vehicle plate details * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param plateDetails * @param startDate * @param endDate * @return List<VsdViolation> * @throws VSDDataAccessException */ public List<VsdViolation> getViolationsByVehiclePlateAndReportedDate(DataServiceContext dsContext, boolean retrieveArabicData, VehiclePlate plateDetails, Date startDate, Date endDate) throws VSDDataAccessException { logger.info("getViolationsByVehiclePlateAndDate -- START"); List<VsdViolation> violations = null; try { Session session = (Session) dsContext.getInternalContext(); violations = session.createCriteria(VsdViolation.class, "v") .add(Restrictions.eq("v.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.between("v.reportedDate", startDate, endDate)) .createCriteria("v.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("vi.vehiclePlateCategory", plateDetails.getPlateCategory()).ignoreCase()) .add(Restrictions.eq("vi.vehiclePlateCode", plateDetails.getPlateCode()).ignoreCase()) .add(Restrictions.eq("vi.vehiclePlateSource", plateDetails.getPlateSource()).ignoreCase()) .add(Restrictions.eq("vi.vehiclePlateNumber", plateDetails.getPlateNumber()).ignoreCase()) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list(); logger.info("getViolationsByVehiclePlateAndDate -- END"); return violations; } catch (Exception ex) { logger.error("An error occured in getViolationsByVehiclePlateAndDate()"); throw new VSDDataAccessException(ex.getMessage(), ex); } } /** * * Get a list of violations from the vehicle plate details, severity level and violations status ids provided * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param plateDetails * @param severityLevels * @param violationStatusId * @return Long * @throws VSDDataAccessException */ public Long getViolationsByVehiclePlateSeverityLevelsAndViolationStatuses(DataServiceContext dsContext, boolean retrieveArabicData, VehiclePlate plateDetails, List<Long> severityLevels, List<Long> violationStatusIds) throws VSDDataAccessException { logger.info("getViolationsByVehiclePlateSeverityLevelsAndViolationStatuses -- START"); try { Session session = (Session) dsContext.getInternalContext(); Criteria crit = session.createCriteria(VsdViolation.class, "v") .add(Restrictions.eq("v.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("vi.vehiclePlateCategory", plateDetails.getPlateCategory()).ignoreCase()) .add(Restrictions.eq("vi.vehiclePlateCode", plateDetails.getPlateCode()).ignoreCase()) .add(Restrictions.eq("vi.vehiclePlateSource", plateDetails.getPlateSource()).ignoreCase()) .add(Restrictions.eq("vi.vehiclePlateNumber", plateDetails.getPlateNumber()).ignoreCase()) .createCriteria("v.vsdSeverityLevel", "sl", Criteria.LEFT_JOIN, Restrictions.eq("sl.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.in("sl.severityLevel", severityLevels)) .createCriteria("v.vsdViolationStatus", "vs", Criteria.LEFT_JOIN, Restrictions.eq("vs.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.in("vs.violationStatusId", violationStatusIds)) .setProjection(Projections.countDistinct("v.violationId")); Long count = (Long) crit.uniqueResult(); logger.info("getViolationsByVehiclePlateSeverityLevelsAndViolationStatuses -- END"); return count; } catch (Exception ex) { logger.error("An error occured in getViolationsByVehiclePlateSeverityLevelsAndViolationStatuses()"); throw new VSDDataAccessException(ex.getMessage(), ex); } } /** * * Gets a list of all the violations Without PlateConsfication based on the plate details provided * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param vehiclePlateDetails * @return List<VsdViolation> * @throws VSDDataAccessException */ public List<VsdViolation> getViolationsWithoutPlateConsficationInspectionsByPlateDetails( DataServiceContext dsContext, boolean retrieveArabicData, VehiclePlate vehiclePlateDetails) throws VSDDataAccessException { logger.info("getViolationsWithoutPlateConsficationInspectionsByPlateDetails -- START"); List<VsdViolation> violations = null; try { Session session = (Session) dsContext.getInternalContext(); violations = session.createCriteria(VsdViolation.class, "violaton") .add(Restrictions.eq("violaton.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vsdInspections", "inspections", Criteria.LEFT_JOIN, Restrictions.eq("inspections.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vsdVehicleInfo", "vehicleInfo", Criteria.LEFT_JOIN, Restrictions.eq("vehicleInfo.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("vehicleInfo.vehiclePlateCategory", vehiclePlateDetails.getPlateCategory()) .ignoreCase()) .add(Restrictions.eq("vehicleInfo.vehiclePlateCode", vehiclePlateDetails.getPlateCode()) .ignoreCase()) .add(Restrictions.eq("vehicleInfo.vehiclePlateSource", vehiclePlateDetails.getPlateSource()) .ignoreCase()) .add(Restrictions.eq("vehicleInfo.vehiclePlateNumber", vehiclePlateDetails.getPlateNumber()) .ignoreCase()) .add(Restrictions.eq("inspections.isPlateConfiscated", Constant.FALSE)) .addOrder(Order.desc("violaton.reportedDate")) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list(); } catch (Exception ex) { logger.error("An error occured in getViolationsWithoutPlateConsficationInspectionsByPlateDetails"); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getViolationsWithoutPlateConsficationInspectionsByPlateDetails -- END"); return violations; } /** * * Gets a list of all the violations WithoutPlateConsficationInspections based on the plate details provided * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param vehiclePlateDetails * @param paginationValues * @return List<VsdViolation> * @throws VSDDataAccessException */ public List<VsdViolation> getViolationsWithoutPlateConsficationInspectionsByPlateDetails( final DataServiceContext dsContext, boolean retrieveArabicData, VehiclePlate vehiclePlateDetails, PaginationParam paginationValues) throws VSDDataAccessException { logger.info("getViolationsWithoutPlateConsficationInspectionsByPlateDetails -- START"); List<VsdViolation> violations = null; try { Session session = (Session) dsContext.getInternalContext(); ProjectionList projectionList = Projections.projectionList(); projectionList.add(Projections.property("violaton.violationId"), "violationId"); projectionList.add(Projections.property("violaton.reportedDate"), "reportedDate"); Criteria crit = session.createCriteria(VsdViolation.class, "violaton") .add(Restrictions.eq("violaton.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vsdInspections", "inspections", Criteria.LEFT_JOIN, Restrictions.eq("inspections.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vsdVehicleInfo", "vehicleInfo", Criteria.LEFT_JOIN, Restrictions.eq("vehicleInfo.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("vehicleInfo.vehiclePlateCategory", vehiclePlateDetails.getPlateCategory()) .ignoreCase()) .add(Restrictions.eq("vehicleInfo.vehiclePlateCode", vehiclePlateDetails.getPlateCode()) .ignoreCase()) .add(Restrictions.eq("vehicleInfo.vehiclePlateSource", vehiclePlateDetails.getPlateSource()) .ignoreCase()) .add(Restrictions.eq("vehicleInfo.vehiclePlateNumber", vehiclePlateDetails.getPlateNumber()) .ignoreCase()) .add(Restrictions.eq("inspections.isPlateConfiscated", Constant.FALSE)) .addOrder(Order.desc("violaton.reportedDate")) .setProjection(Projections.distinct(projectionList)); crit.setResultTransformer(new AliasToBeanResultTransformer(VsdViolation.class)); if (paginationValues.getFirstResult() != null && paginationValues.getFirstResult().longValue() != -1) { crit.setFirstResult(paginationValues.getFirstResult().intValue()); } if (paginationValues.getFetchedSize() != null && paginationValues.getFetchedSize().longValue() != -1) { crit.setMaxResults(paginationValues.getFetchedSize().intValue()); } List list = crit.list(); logger.debug("list.size() : " + list.size()); Set resultSet = new HashSet(list); logger.debug("resultSet.size() : " + resultSet.size()); if (resultSet.size() == 0) return new ArrayList(); Iterator iterator = resultSet.iterator(); ArrayList innerQueryList = new ArrayList<Long>(); while (iterator.hasNext()) { VsdViolation violation = (VsdViolation) iterator.next(); innerQueryList.add(violation.getViolationId()); } Criteria main = session.createCriteria(VsdViolation.class, "violaton") .add(Property.forName("violaton.violationId").in(innerQueryList)) .add(Restrictions.eq("violaton.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vsdInspections", "inspections", Criteria.LEFT_JOIN, Restrictions.eq("inspections.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vsdVehicleInfo", "vehicleInfo", Criteria.LEFT_JOIN, Restrictions.eq("vehicleInfo.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("vehicleInfo.vehiclePlateCategory", vehiclePlateDetails.getPlateCategory())) .add(Restrictions.eq("vehicleInfo.vehiclePlateCode", vehiclePlateDetails.getPlateCode())) .add(Restrictions.eq("vehicleInfo.vehiclePlateSource", vehiclePlateDetails.getPlateSource())) .add(Restrictions.eq("vehicleInfo.vehiclePlateNumber", vehiclePlateDetails.getPlateNumber())) .add(Restrictions.eq("inspections.isPlateConfiscated", Constant.FALSE)) .addOrder(Order.desc("violaton.reportedDate")); violations = main.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list(); logger.info("getViolationsWithoutPlateConsficationInspectionsByPlateDetails -- END"); return violations; } catch (Exception ex) { logger.error("An error occured in getViolationsWithoutPlateConsficationInspectionsByPlateDetails()"); throw new VSDDataAccessException(ex.getMessage(), ex); } } /** * * Gets all the violations WithoutPlateConsfication for vehicles owned by the owner with the specified trade license number * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param param * @return List<VsdViolation> * @throws VSDDataAccessException */ public List<VsdViolation> getViolationsWithoutPlateConsficationByTraficFileNumber( final DataServiceContext dsContext, boolean retrieveArabicData, String traficFileNo, PaginationParam param) throws VSDDataAccessException { logger.info("getViolationsWithoutPlateConsficationByTraficFileNumber -- START"); List<VsdViolation> violations = null; try { Session session = (Session) dsContext.getInternalContext(); ProjectionList projectionList = Projections.projectionList(); projectionList.add(Projections.property("v.violationId"), "violationId"); projectionList.add(Projections.property("v.reportedDate"), "reportedDate"); violations = session.createCriteria(VsdViolation.class, "v") .add(Restrictions.eq("v.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("i.isPlateConfiscated", Constant.FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vi.vsdOwnerInfos", "oi", Criteria.LEFT_JOIN, Restrictions.eq("oi.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("oi.trafficFileNumber", traficFileNo).ignoreCase()) .createCriteria("v.vsdSeverityLevel", "vsl", Criteria.LEFT_JOIN, Restrictions.eq("vsl.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdViolationStatus", "vs", Criteria.LEFT_JOIN, Restrictions.eq("vs.isDeleted", IDataService.BOOL_FALSE)) .addOrder(Order.desc("v.reportedDate")).setProjection(Projections.distinct(projectionList)) .setFirstResult(param.getFirstResult().intValue()) .setMaxResults(param.getFetchedSize().intValue()) .setResultTransformer(new AliasToBeanResultTransformer(VsdViolation.class)).list(); if (violations == null || violations.size() == 0) return new ArrayList(); Iterator iterator = violations.iterator(); ArrayList innerQueryList = new ArrayList<Long>(); while (iterator.hasNext()) { VsdViolation violation = (VsdViolation) iterator.next(); innerQueryList.add(violation.getViolationId()); } violations = session.createCriteria(VsdViolation.class, "v") .add(Restrictions.eq("v.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("i.isPlateConfiscated", Constant.FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vi.vsdOwnerInfos", "oi", Criteria.LEFT_JOIN, Restrictions.eq("oi.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("oi.trafficFileNumber", traficFileNo).ignoreCase()) .createCriteria("v.vsdSeverityLevel", "vsl", Criteria.LEFT_JOIN, Restrictions.eq("vsl.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdViolationStatus", "vs", Criteria.LEFT_JOIN, Restrictions.eq("vs.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.in("v.violationId", innerQueryList)).addOrder(Order.desc("v.reportedDate")) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list(); logger.info("getViolationsWithoutPlateConsficationByTraficFileNumber -- END"); return violations; } catch (Exception ex) { logger.error("An error occured in getViolationsWithoutPlateConsficationByTraficFileNumber()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } } /** * * Gets all the violations WithoutPlateConsfication for vehicles owned by the owner with the specified trade license number * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param tradeLicenseNumber * @return List<VsdViolation> * @throws VSDDataAccessException */ public List<VsdViolation> getViolationsWithoutPlateConsficationByTraficFileNumber( final DataServiceContext dsContext, boolean retrieveArabicData, String traficFileNo) throws VSDDataAccessException { logger.info("getViolationsWithoutPlateConsficationByTraficFileNumber -- START"); List<VsdViolation> violations = null; try { Session session = (Session) dsContext.getInternalContext(); violations = session.createCriteria(VsdViolation.class, "v") .add(Restrictions.eq("v.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("i.isPlateConfiscated", Constant.FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vi.vsdOwnerInfos", "oi", Criteria.LEFT_JOIN, Restrictions.eq("oi.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("oi.trafficFileNumber", traficFileNo).ignoreCase()) .createCriteria("v.vsdSeverityLevel", "vsl", Criteria.LEFT_JOIN, Restrictions.eq("vsl.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdViolationStatus", "vs", Criteria.LEFT_JOIN, Restrictions.eq("vs.isDeleted", IDataService.BOOL_FALSE)) .addOrder(Order.desc("v.reportedDate")).setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY) .list(); logger.info("getViolationsWithoutPlateConsficationByTraficFileNumber -- END"); return violations; } catch (Exception ex) { logger.error("An error occured in getViolationsByTradeLicenseNumber()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } } /** * * Get a list of violations by the status id provided and the start and end date provided * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param startDate * @param endDate * @param violationStatusIds * @return List<VsdViolation> * @throws VSDDataAccessException */ public List<VsdViolation> getViolationsByViolationStatusIdAndUpdatedTimestamp( final DataServiceContext dsContext, boolean retrieveArabicData, Date startDate, Date endDate, List<Long> violationStatusIds) throws VSDDataAccessException { logger.info("getViolationsByViolationStatusIdAndUpdatedTimestamp -- START"); List<VsdViolation> violations = null; try { // Calendar sDate = Calendar.getInstance(); // sDate.setTime(startDate); // sDate.set(Calendar.HOUR_OF_DAY, 0); // sDate.set(Calendar.MINUTE, 0); // sDate.set(Calendar.SECOND, 0); // sDate.set(Calendar.MILLISECOND, 0); // // Calendar eDate = Calendar.getInstance(); // eDate.setTime(endDate); // eDate.set(Calendar.HOUR_OF_DAY, 23); // eDate.set(Calendar.MINUTE, 59); // eDate.set(Calendar.SECOND, 59); // eDate.set(Calendar.MILLISECOND, 999); Session session = (Session) dsContext.getInternalContext(); violations = session.createCriteria(VsdViolation.class, "v") .add(Restrictions.eq("v.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.between("v.upatedTimestamp", startDate, endDate)) .createCriteria("v.vsdViolationStatus", "vs", Criteria.LEFT_JOIN, Restrictions.eq("vs.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.in("vs.violationStatusId", violationStatusIds)) .createCriteria("v.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vi.vsdOwnerInfos", "oi", Criteria.LEFT_JOIN, Restrictions.eq("oi.isDeleted", IDataService.BOOL_FALSE)) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list(); } catch (Exception ex) { logger.error("An error occured in getViolationsByViolationStatusIdAndUpdatedTimestamp()"); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getViolationsByViolationStatusIdAndUpdatedTimestamp -- END"); return violations; } /** * * Gets a violation with their channel defect, channel defect test and channel test information * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param violationId * @param isViolationDefect * @return VsdViolation * @throws VSDDataAccessException */ public VsdViolation getViolationWithChannelDefectTestAndChannelTestById(DataServiceContext dsContext, boolean retrieveArabicData, Long violationId, Character isViolationDefect) throws VSDDataAccessException { logger.info("getViolationWithChannelDefectTestAndChannelTestById -- START"); VsdViolation violation = null; try { Session session = (Session) dsContext.getInternalContext(); violation = (VsdViolation) session.createCriteria(VsdViolation.class, "v") .add(Restrictions.eq("v.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("v.violationId", violationId)) .createCriteria("v.vsdChannelDefects", "cd", Criteria.LEFT_JOIN, Restrictions.eq("cd.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("cd.isViolationDefect", isViolationDefect)) .createCriteria("cd.vsdDefect", "d", Criteria.LEFT_JOIN, Restrictions.eq("d.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdChannelDefectTests", "cdt", Criteria.LEFT_JOIN, Restrictions.eq("cdt.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cdt.vsdChannelTest", "cht", Criteria.LEFT_JOIN, Restrictions.eq("cht.isDeleted", IDataService.BOOL_FALSE)) .uniqueResult(); logger.info("getViolationWithChannelDefectTestAndChannelTestById -- END"); return violation; } catch (Exception ex) { logger.error("An error occured in getViolationWithChannelDefectTestAndChannelTestById()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } } public static enum SORT_COLUMN_GET_VIOLATIONS_WITH_INSPECTIONS { VIOLATION_ID { public String toString() { return "violationTicketCode"; } }, SEVERITY { public String toString() { return "severityLevelName"; } }, STATUS { public String toString() { return "statusName"; } }, DATE_TIME { public String toString() { return "inspectionTimestamp"; } }, PLATE_DETAIL { public String toString() { return "PLATE_DETAIL"; } }, SEVERITY_A { public String toString() { return "severityLevelNameA"; } }, STATUS_A { public String toString() { return "statusNameA"; } } } }