Java tutorial
/**<pre> *========================================================================== * * Copyright: (C) IBM Corporation 2010 -- IBM Internal Use Only * *========================================================================== * * FILE: InspectionDataServiceImpl.java * CREATOR: Eldon Barrows * DEPT: GBS PAK * DATE: 01/05/2011 * *-PURPOSE----------------------------------------------------------------- * This is the implementation of IInspectionDataService * *------------------------------------------------------------------------- * * *-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.Date; import java.util.HashSet; import java.util.Iterator; 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.IInspectionDataService; import com.rta.vsd.dto.VsdInspection; import com.rta.vsd.dto.custom.CustomInspectionAreaDTO; import com.rta.vsd.dto.custom.CustomInspectionDTO; import com.rta.vsd.dto.custom.CustomInspectionViolationMonthlyDTO; import com.rta.vsd.dto.custom.CustomInspectionViolationQuarterlyDTO; import com.rta.vsd.dto.custom.PaginationParam; import com.rta.vsd.dto.custom.VehiclePlate; import com.rta.vsd.exception.data.VSDDataAccessException; import com.rta.vsd.utility.VSDLogger; /** * This is the implementation of IInspectionDataService * * @author Eldon Barrows * */ public class InspectionDataServiceImpl extends DataServiceImpl implements IInspectionDataService { private static Logger logger = VSDLogger.init(InspectionDataServiceImpl.class.getName()); /** * * Saves an inspection * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param vsdInspection * @return VsdInspection * @throws VSDDataAccessException */ public VsdInspection saveInspection(DataServiceContext dsContext, boolean retrieveArabicData, VsdInspection vsdInspection) throws VSDDataAccessException { logger.info("saveInspection -- START"); try { Session session = (Session) dsContext.getInternalContext(); session.save(vsdInspection); } catch (Exception ex) { logger.error("An error occured in saveInspection()"); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("saveInspection -- END"); return vsdInspection; } /** * * Updates an inspection * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param vsdInspection * @return VsdInspection * @throws VSDDataAccessException */ public VsdInspection updateInspection(DataServiceContext dsContext, boolean retrieveArabicData, VsdInspection vsdInspection) throws VSDDataAccessException { logger.info("updateInspection -- START"); try { Query query = createDynamicUpdateQuery(vsdInspection, dsContext); query.executeUpdate(); } catch (Exception ex) { logger.error("An error occured in updateInspection()"); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("updateInspection -- END"); return vsdInspection; } /** * * Gets a list of inspection from the plate details specified * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param vehiclePlateDetails * @return List<VsdInspection> * @throws VSDDataAccessException */ public List<VsdInspection> getInspectionsByPlateDetails(final DataServiceContext dsContext, boolean retrieveArabicData, VehiclePlate vehiclePlateDetails) throws VSDDataAccessException { logger.info("getInspectionsByPlateDetails -- START"); List<VsdInspection> inspections; try { Session session = (Session) dsContext.getInternalContext(); inspections = session.createCriteria(VsdInspection.class, "inspections") .add(Restrictions.eq("inspections.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("inspections.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("inspections.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("inspections.createdTimestamp")) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list(); } catch (Exception ex) { logger.error("An error occured in getInspectionsByPlateDetails()"); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getInspectionsByPlateDetails -- END"); return inspections; } /** * * Gets a list of inspection from the plate details specified * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param vehiclePlateDetails * @param paginationValues * @return List<VsdInspection> * @throws VSDDataAccessException */ public List<VsdInspection> getInspectionsByPlateDetails(final DataServiceContext dsContext, boolean retrieveArabicData, VehiclePlate vehiclePlateDetails, PaginationParam paginationValues) throws VSDDataAccessException { logger.info("getInspectionsByPlateDetails -- START"); List<VsdInspection> inspections = null; try { Session session = (Session) dsContext.getInternalContext(); ProjectionList projectionList = Projections.projectionList(); projectionList.add(Projections.property("inspections.inspectionId"), "inspectionId"); Criteria crit = session.createCriteria(VsdInspection.class, "inspections") .add(Restrictions.eq("inspections.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("inspections.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("inspections.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("inspections.inspectionId")) .setProjection(Projections.distinct(projectionList)); crit.setResultTransformer(new AliasToBeanResultTransformer(VsdInspection.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()) { VsdInspection inspection = (VsdInspection) iterator.next(); innerQueryList.add(inspection.getInspectionId()); } Criteria main = session.createCriteria(VsdInspection.class, "inspections") .add(Property.forName("inspections.inspectionId").in(innerQueryList)) .add(Restrictions.eq("inspections.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("inspections.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("inspections.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("inspections.inspectionId")); inspections = main.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list(); logger.info("getInspectionsByPlateDetails -- END"); return inspections; } catch (Exception ex) { logger.error("An error occured in getInspectionsByPlateDetails()"); throw new VSDDataAccessException(ex.getMessage(), ex); } } /** * * Gets the count for getInspectionsByPlateDetails * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param vehiclePlateDetails * @return Long * @throws VSDDataAccessException */ public Long getCountForGetInspectionsByPlateDetails(final DataServiceContext dsContext, boolean retrieveArabicData, VehiclePlate vehiclePlateDetails) throws VSDDataAccessException { logger.info("getCountForGetInspectionsByPlateDetails -- START"); try { Session session = (Session) dsContext.getInternalContext(); Criteria crit = session.createCriteria(VsdInspection.class, "inspections") .add(Restrictions.eq("inspections.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("inspections.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("inspections.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("inspections.inspectionId")) .setProjection(Projections.countDistinct("inspections.inspectionId")); Long count = (Long) crit.uniqueResult(); logger.info("getCountForGetInspectionsByPlateDetails -- END"); return count; } catch (Exception ex) { logger.error("An error occured in getCountForGetInspectionsByPlateDetails()"); throw new VSDDataAccessException(ex.getMessage(), ex); } } /** * * Gets all the inspections for vehicles owned by the owner with the specified trade license number * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param tradeLicenseNumber * @return List<VsdInspection> * @throws VSDDataAccessException */ public List<VsdInspection> getInspectionsByTradeLicenseNumber(final DataServiceContext dsContext, boolean retrieveArabicData, String tradeLicenseNumber) throws VSDDataAccessException { logger.info("getInspectionsByTradeLicenseNumber -- START"); List<VsdInspection> inspections; try { Session session = (Session) dsContext.getInternalContext(); inspections = session.createCriteria(VsdInspection.class, "inspections") .add(Restrictions.eq("inspections.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("inspections.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("inspections.vsdVehicleInfo", "vehicleInfo", Criteria.LEFT_JOIN, Restrictions.eq("vehicleInfo.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vehicleInfo.vsdOwnerInfos", "ownerInfo", Criteria.LEFT_JOIN, Restrictions.eq("ownerInfo.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("ownerInfo.tradeLicenseNumber", tradeLicenseNumber).ignoreCase()) .addOrder(Order.desc("inspections.createdTimestamp")) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list(); } catch (Exception ex) { logger.error("An error occured in getInspectionsByTradeLicenseNumber()"); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getInspectionsByTradeLicenseNumber -- END"); return inspections; } /** * * Gets all the inspections for vehicles owned by the owner with the specified trade license number * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param tradeLicenseNumber * @return List<VsdInspection> * @throws VSDDataAccessException */ public List<VsdInspection> getInspectionsByTraficFileNumber(final DataServiceContext dsContext, boolean retrieveArabicData, String traficFileNo) throws VSDDataAccessException { logger.info("getInspectionsByTradeLicenseNumber -- START"); List<VsdInspection> inspections; try { Session session = (Session) dsContext.getInternalContext(); inspections = session.createCriteria(VsdInspection.class, "inspections") .add(Restrictions.eq("inspections.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("inspections.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("inspections.vsdVehicleInfo", "vehicleInfo", Criteria.LEFT_JOIN, Restrictions.eq("vehicleInfo.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vehicleInfo.vsdOwnerInfos", "ownerInfo", Criteria.LEFT_JOIN, Restrictions.eq("ownerInfo.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("ownerInfo.trafficFileNumber", traficFileNo).ignoreCase()) .addOrder(Order.desc("inspections.createdTimestamp")) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list(); } catch (Exception ex) { logger.error("An error occured in getInspectionsByTradeLicenseNumber()"); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getInspectionsByTradeLicenseNumber -- END"); return inspections; } /** * * Get count for all the inspections 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 getCountForInspectionsByTraficFileNumber(final DataServiceContext dsContext, boolean retrieveArabicData, String traficFileNo) throws VSDDataAccessException { logger.info("getCountForInspectionsByTraficFileNumber -- START"); Long inspectionsCount; try { Session session = (Session) dsContext.getInternalContext(); inspectionsCount = (Long) session.createCriteria(VsdInspection.class, "inspections") .add(Restrictions.eq("inspections.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("inspections.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("inspections.vsdVehicleInfo", "vehicleInfo", Criteria.LEFT_JOIN, Restrictions.eq("vehicleInfo.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vehicleInfo.vsdOwnerInfos", "ownerInfo", Criteria.LEFT_JOIN, Restrictions.eq("ownerInfo.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("ownerInfo.trafficFileNumber", traficFileNo).ignoreCase()) .setProjection(Projections.countDistinct("inspections.inspectionId")).uniqueResult(); } catch (Exception ex) { logger.error("An error occured in getCountForInspectionsByTraficFileNumber()"); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getCountForInspectionsByTraficFileNumber -- END"); return inspectionsCount; } /** * * Gets all the inspections for vehicles owned by the owner with the specified trade license number * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param traficFileNo * @param paginationParam * @return List<VsdInspection> * @throws VSDDataAccessException */ public List<VsdInspection> getInspectionsByTraficFileNumber(final DataServiceContext dsContext, boolean retrieveArabicData, String traficFileNo, PaginationParam param) throws VSDDataAccessException { logger.info("getInspectionsByTraficFileNumber -- START"); List<VsdInspection> inspections; try { Session session = (Session) dsContext.getInternalContext(); ProjectionList projectionList = Projections.projectionList(); projectionList.add(Projections.property("inspections.inspectionId"), "inspectionId"); projectionList.add(Projections.property("inspections.createdTimestamp"), "createdTimestamp"); inspections = session.createCriteria(VsdInspection.class, "inspections") .add(Restrictions.eq("inspections.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("inspections.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("inspections.vsdVehicleInfo", "vehicleInfo", Criteria.LEFT_JOIN, Restrictions.eq("vehicleInfo.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vehicleInfo.vsdOwnerInfos", "ownerInfo", Criteria.LEFT_JOIN, Restrictions.eq("ownerInfo.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("ownerInfo.trafficFileNumber", traficFileNo).ignoreCase()) .addOrder(Order.desc("inspections.createdTimestamp")) .setProjection(Projections.distinct(projectionList)) .setFirstResult(param.getFirstResult().intValue()) .setMaxResults(param.getFetchedSize().intValue()) .setResultTransformer(new AliasToBeanResultTransformer(VsdInspection.class)).list(); if (inspections.size() == 0) return new ArrayList(); Iterator iterator = inspections.iterator(); ArrayList innerQueryList = new ArrayList<Long>(); while (iterator.hasNext()) { VsdInspection inspection = (VsdInspection) iterator.next(); innerQueryList.add(inspection.getInspectionId()); } inspections = session.createCriteria(VsdInspection.class, "inspections") .add(Restrictions.eq("inspections.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("inspections.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("inspections.vsdVehicleInfo", "vehicleInfo", Criteria.LEFT_JOIN, Restrictions.eq("vehicleInfo.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vehicleInfo.vsdOwnerInfos", "ownerInfo", Criteria.LEFT_JOIN, Restrictions.eq("ownerInfo.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("ownerInfo.trafficFileNumber", traficFileNo).ignoreCase()) .add(Restrictions.in("inspections.inspectionId", innerQueryList)) .addOrder(Order.desc("inspections.createdTimestamp")).list(); } catch (Exception ex) { logger.error("An error occured in getInspectionsByTraficFileNumber()"); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getInspectionsByTraficFileNumber -- END"); return inspections; } /** * * Gets all the inspections 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<VsdInspection> * @throws VSDDataAccessException */ public List<VsdInspection> getInspectionsByTradeLicenseNumber(final DataServiceContext dsContext, boolean retrieveArabicData, String tradeLicenseNumber, PaginationParam paginationValues) throws VSDDataAccessException { logger.info("getInspectionsByTradeLicenseNumber -- START"); List<VsdInspection> inspections = null; try { Session session = (Session) dsContext.getInternalContext(); ProjectionList projectionList = Projections.projectionList(); projectionList.add(Projections.property("inspections.inspectionId"), "inspectionId"); Criteria crit = session.createCriteria(VsdInspection.class, "inspections") .add(Restrictions.eq("inspections.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("inspections.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("inspections.vsdVehicleInfo", "vehicleInfo", Criteria.LEFT_JOIN, Restrictions.eq("vehicleInfo.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vehicleInfo.vsdOwnerInfos", "ownerInfo", Criteria.LEFT_JOIN, Restrictions.eq("ownerInfo.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("ownerInfo.tradeLicenseNumber", tradeLicenseNumber).ignoreCase()) .addOrder(Order.desc("inspections.inspectionId")); crit.setResultTransformer(new AliasToBeanResultTransformer(VsdInspection.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()) { VsdInspection inspection = (VsdInspection) iterator.next(); innerQueryList.add(inspection.getInspectionId()); } Criteria main = session.createCriteria(VsdInspection.class, "inspections") .add(Property.forName("inspections.inspectionId").in(innerQueryList)) .add(Restrictions.eq("inspections.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("inspections.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("inspections.vsdVehicleInfo", "vehicleInfo", Criteria.LEFT_JOIN, Restrictions.eq("vehicleInfo.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vehicleInfo.vsdOwnerInfos", "ownerInfo", Criteria.LEFT_JOIN, Restrictions.eq("ownerInfo.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("ownerInfo.tradeLicenseNumber", tradeLicenseNumber).ignoreCase()) .addOrder(Order.desc("inspections.inspectionId")); inspections = main.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list(); logger.info("getInspectionsByTradeLicenseNumber -- END"); return inspections; } catch (Exception ex) { logger.error("An error occured in getInspectionsByTradeLicenseNumber()"); throw new VSDDataAccessException(ex.getMessage(), ex); } } /** * * Gets the count for getInspectionsByTradeLicenseNumber * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param tradeLicenseNumber * @return Long * @throws VSDDataAccessException */ public Long getCountForGetInspectionsByTradeLicenseNumber(final DataServiceContext dsContext, boolean retrieveArabicData, String tradeLicenseNumber) throws VSDDataAccessException { logger.info("getCountForGetInspectionsByTradeLicenseNumber -- START"); try { Session session = (Session) dsContext.getInternalContext(); Criteria crit = session.createCriteria(VsdInspection.class, "inspections") .add(Restrictions.eq("inspections.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("inspections.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("inspections.vsdVehicleInfo", "vehicleInfo", Criteria.LEFT_JOIN, Restrictions.eq("vehicleInfo.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vehicleInfo.vsdOwnerInfos", "ownerInfo", Criteria.LEFT_JOIN, Restrictions.eq("ownerInfo.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("ownerInfo.tradeLicenseNumber", tradeLicenseNumber).ignoreCase()) .addOrder(Order.desc("inspections.inspectionId")) .setProjection(Projections.countDistinct("inspections.inspectionId")); Long count = (Long) crit.uniqueResult(); logger.info("getCountForGetInspectionsByTradeLicenseNumber -- END"); return count; } catch (Exception ex) { logger.error("An error occured in getCountForGetInspectionsByTradeLicenseNumber()"); throw new VSDDataAccessException(ex.getMessage(), ex); } } /** * * Get the total number of inspections that occurred between the two dates specified. * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param fromDate * @param toDate * @return Long * @throws VSDDataAccessException */ public Long getTotalInspectionsByDateRange(DataServiceContext dsContext, boolean retrieveArabicData, Date fromDate, Date toDate) throws VSDDataAccessException { logger.info("getTotalInspectionsByDateRange -- START"); /*SELECT COUNT(INSPECTION_ID) AS "inspectionCount" FROM VSD_INSPECTION WHERE IS_DELETED = 'F' AND INSPECTION_TIMESTAMP BETWEEN to_date('2011-06-15','YYYY-MM-DD') AND to_date('2011-06-18', 'YYYY-MM-DD')*/ try { Session session = (Session) dsContext.getInternalContext(); String defaultSchema = DataServiceImpl.DEFAULT_SCHEMA_NAME; StringBuffer query = new StringBuffer("SELECT COUNT(INSPECTION_ID) AS \"inspectionCount\" FROM " + defaultSchema + ".VSD_INSPECTION" + " WHERE IS_DELETED = '" + IDataService.BOOL_FALSE + "' "); if (fromDate != null && toDate != null) { SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); query.append( " AND to_date(to_char(INSPECTION_TIMESTAMP,'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 inspectionCount = Long .valueOf(((BigDecimal) session.createSQLQuery(query.toString()).uniqueResult()).longValue()); logger.info("getTotalInspectionsByDateRange -- END"); return inspectionCount; } catch (Exception ex) { logger.error("An error occured in getTotalInspectionsByDateRange()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } } /** * * Get the total number of vehicles inspected that occurred between the two dates specified. * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param fromDate * @param toDate * @return Long * @throws VSDDataAccessException */ public Long getTotalVehiclesInspectedByDateRange(DataServiceContext dsContext, boolean retrieveArabicData, Date fromDate, Date toDate) throws VSDDataAccessException { logger.info("getTotalVehiclesInspectedByDateRange -- START"); /*SELECT COUNT(*) from ( select vi.VEHICLE_PLATE_CATEGORY, vi.VEHICLE_PLATE_CODE, vi.VEHICLE_PLATE_SOURCE, vi.VEHICLE_PLATE_NUMBER from VSD_VIOLATION v, VSD_INSPECTION i,VSD_VEHICLE_INFO vi where REPORTED_DATE between to_date('01-01-2011','dd-mm-yyyy') and to_date('26-08-2011','dd-mm-yyyy') and v.VIOLATION_ID = i.VIOLATION_ID and i.VEHICLE_INFO_ID =vi.VEHICLE_INFO_ID GROUP by vi.VEHICLE_PLATE_CATEGORY, vi.VEHICLE_PLATE_CODE, vi.VEHICLE_PLATE_SOURCE, vi.VEHICLE_PLATE_NUMBER) A*/ try { Session session = (Session) dsContext.getInternalContext(); String defaultSchema = DataServiceImpl.DEFAULT_SCHEMA_NAME; StringBuffer query = new StringBuffer( "SELECT COUNT(*) AS \"vehicleInspectedCount\" from ( select vi.VEHICLE_PLATE_CATEGORY, vi.VEHICLE_PLATE_CODE, vi.VEHICLE_PLATE_SOURCE, vi.VEHICLE_PLATE_NUMBER from " + DEFAULT_SCHEMA_NAME + ".VSD_INSPECTION i, " + DEFAULT_SCHEMA_NAME + ".VSD_VEHICLE_INFO vi where vi.IS_DELETED = '" + IDataService.BOOL_FALSE + "' "); if (fromDate != null && toDate != null) { SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); query.append( " AND to_date(to_char(i.INSPECTION_TIMESTAMP,'YYYY-MM-DD'),'YYYY-MM-DD') between to_date('" + dateFormat.format(fromDate) + "','YYYY-MM-DD') AND to_date('" + dateFormat.format(toDate) + "', 'YYYY-MM-DD') "); } query.append( " AND i.VEHICLE_INFO_ID = vi.VEHICLE_INFO_ID GROUP by vi.VEHICLE_PLATE_CATEGORY, vi.VEHICLE_PLATE_CODE, vi.VEHICLE_PLATE_SOURCE, vi.VEHICLE_PLATE_NUMBER) A"); Long vehicleInspectedCount = Long .valueOf(((BigDecimal) session.createSQLQuery(query.toString()).uniqueResult()).longValue()); logger.info("getTotalVehiclesInspectedByDateRange -- END"); return vehicleInspectedCount; } catch (Exception ex) { logger.error("An error occured in getTotalVehiclesInspectedByDateRange()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } } /** * * Gets a list of violations separated by the month and its total inspections * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param fromDate * @param toDate * @return List<CustomInspectionDTO> * @throws VSDDataAccessException */ public List<CustomInspectionDTO> getAverageInspectionsByDateRange(DataServiceContext dsContext, boolean retrieveArabicData, Date fromDate, Date toDate) throws VSDDataAccessException { logger.info("getAverageInspectionsByDateRange -- START"); /*SELECT COUNT(i.INSPECTION_ID)/decode(COUNT(DISTINCT(REPORTED_INSPECTOR_EMP_ID)) , 0,'1', COUNT(DISTINCT(REPORTED_INSPECTOR_EMP_ID))) AS \"inspectionCount\", TO_CHAR(i.INSPECTION_TIMESTAMP,'mm') AS \"month\"" FROM VSD_INSPECTION i WHERE i.IS_DELETED = 'F' AND i.INSPECTION_TIMESTAMP BETWEEN to_date('2011-05-15','YYYY-MM-DD') AND to_date( '2011-11-30', 'YYYY-MM-DD') AND i.IS_COMPLETE = 'T' GROUP BY TO_CHAR(i.INSPECTION_TIMESTAMP,'mm')*/ try { Session session = (Session) dsContext.getInternalContext(); String defaultSchema = DataServiceImpl.DEFAULT_SCHEMA_NAME; StringBuffer query = new StringBuffer( "SELECT COUNT(i.INSPECTION_ID)/decode(COUNT(DISTINCT(i.REPORTED_INSPECTOR_EMP_ID)) , 0,'1', COUNT(DISTINCT(i.REPORTED_INSPECTOR_EMP_ID))) AS \"inspectionCount\" , TO_CHAR(i.INSPECTION_TIMESTAMP,'mm') AS \"month\" " + " FROM " + defaultSchema + ".VSD_INSPECTION i " + " WHERE i.IS_DELETED = '" + IDataService.BOOL_FALSE + "' AND i.IS_COMPLETE = '" + IDataService.BOOL_TRUE + "' "); if (fromDate != null && toDate != null) { SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); query.append( " AND to_date(to_char(i.INSPECTION_TIMESTAMP,'YYYY-MM-DD'),'YYYY-MM-DD') BETWEEN to_date('" + dateFormat.format(fromDate) + "','YYYY-MM-DD') AND to_date('" + dateFormat.format(toDate) + "','YYYY-MM-DD') "); } query.append( " GROUP BY TO_CHAR(i.INSPECTION_TIMESTAMP,'mm') ORDER BY TO_CHAR(i.INSPECTION_TIMESTAMP,'mm') "); List<CustomInspectionDTO> results = session.createSQLQuery(query.toString()) .addScalar("inspectionCount", Hibernate.BIG_DECIMAL).addScalar("month", Hibernate.INTEGER) .setResultTransformer(new AliasToBeanResultTransformer(CustomInspectionDTO.class)).list(); logger.info("getAverageInspectionsByDateRange -- END"); return results; } catch (Exception ex) { logger.error("An error occured in getAverageInspectionsByDateRange()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } } /** * * Gets a list of inspections and violations separated by the quarter in which they occurred. * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param fromDate * @param toDate * @return List<CustomInspectionViolationQuarterlyDTO> * @throws VSDDataAccessException */ public List<CustomInspectionViolationQuarterlyDTO> getInspectionsViolationsQuarterlyByDateRange( DataServiceContext dsContext, boolean retrieveArabicData, Date fromDate, Date toDate) throws VSDDataAccessException { logger.info("getInspectionsViolationsQuarterlyByDateRange -- START"); /*SELECT COUNT(i.INSPECTION_ID) AS \"inspectionCount\", TO_CHAR(i.INSPECTION_TIMESTAMP,'Q') AS \"quarter\" FROM VSD_INSPECTION i WHERE i.IS_DELETED = 'F' AND i.IS_COMPLETE = 'T' AND i.INSPECTION_TIMESTAMP BETWEEN to_date('2011-05-15','YYYY-MM-DD') AND to_date('2011-11-30', 'YYYY-MM-DD') GROUP BY TO_CHAR(i.INSPECTION_TIMESTAMP,'Q') SELECT COUNT(v.VIOLATION_ID) AS \"violationCount\", TO_CHAR(v.REPORTED_DATE,'Q' AS \"quarter\") FROM VSD_VIOLATION v 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') GROUP BY TO_CHAR(v.REPORTED_DATE,'Q')*/ try { Session session = (Session) dsContext.getInternalContext(); String defaultSchema = DataServiceImpl.DEFAULT_SCHEMA_NAME; StringBuffer query = new StringBuffer( "SELECT COUNT(i.INSPECTION_ID) AS \"inspectionCount\", TO_CHAR(i.INSPECTION_TIMESTAMP,'Q') AS \"quarter\" " + " FROM " + defaultSchema + ".VSD_INSPECTION i " + " WHERE i.IS_DELETED = '" + IDataService.BOOL_FALSE + "' AND i.IS_COMPLETE = '" + IDataService.BOOL_TRUE + "' "); if (fromDate != null && toDate != null) { SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); query.append( " AND to_date(to_char(i.INSPECTION_TIMESTAMP,'YYYY-MM-DD'),'YYYY-MM-DD') BETWEEN to_date('" + dateFormat.format(fromDate) + "','YYYY-MM-DD') AND to_date('" + dateFormat.format(toDate) + "','YYYY-MM-DD') "); } query.append( " GROUP BY TO_CHAR(i.INSPECTION_TIMESTAMP,'Q') ORDER BY TO_CHAR(i.INSPECTION_TIMESTAMP,'Q') "); List<CustomInspectionViolationQuarterlyDTO> inspections = session.createSQLQuery(query.toString()) .addScalar("inspectionCount", Hibernate.LONG).addScalar("quarter", Hibernate.INTEGER) .setResultTransformer( new AliasToBeanResultTransformer(CustomInspectionViolationQuarterlyDTO.class)) .list(); StringBuffer query2 = new StringBuffer( "SELECT COUNT(v.VIOLATION_ID) AS \"violationCount\", TO_CHAR(v.REPORTED_DATE,'Q') AS \"quarter\" " + " FROM " + defaultSchema + ".VSD_VIOLATION v " + " WHERE v.IS_DELETED = '" + IDataService.BOOL_FALSE + "' "); if (fromDate != null && toDate != null) { SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); query2.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') "); } query2.append(" GROUP BY TO_CHAR(v.REPORTED_DATE,'Q') ORDER BY TO_CHAR(v.REPORTED_DATE,'Q') "); List<CustomInspectionViolationQuarterlyDTO> violations = session.createSQLQuery(query2.toString()) .addScalar("violationCount", Hibernate.LONG).addScalar("quarter", Hibernate.INTEGER) .setResultTransformer( new AliasToBeanResultTransformer(CustomInspectionViolationQuarterlyDTO.class)) .list(); List<CustomInspectionViolationQuarterlyDTO> results = new ArrayList<CustomInspectionViolationQuarterlyDTO>(); Iterator<CustomInspectionViolationQuarterlyDTO> iter = inspections.iterator(); while (iter.hasNext()) { CustomInspectionViolationQuarterlyDTO inspect = (CustomInspectionViolationQuarterlyDTO) iter.next(); CustomInspectionViolationQuarterlyDTO combinedDTO = new CustomInspectionViolationQuarterlyDTO(); combinedDTO.setQuarter(inspect.getQuarter()); combinedDTO.setInspectionCount(inspect.getInspectionCount()); Iterator<CustomInspectionViolationQuarterlyDTO> iter2 = violations.iterator(); while (iter2.hasNext()) { CustomInspectionViolationQuarterlyDTO viol = (CustomInspectionViolationQuarterlyDTO) iter2 .next(); if (viol.getQuarter().intValue() == combinedDTO.getQuarter().intValue()) { combinedDTO.setViolationCount(viol.getViolationCount()); } } results.add(combinedDTO); } logger.info("getInspectionsViolationsQuarterlyByDateRange -- END"); System.out.println("Result size is " + results.size()); Iterator<CustomInspectionViolationQuarterlyDTO> riter = results.iterator(); while (riter.hasNext()) { CustomInspectionViolationQuarterlyDTO dto = (CustomInspectionViolationQuarterlyDTO) riter.next(); System.out.println("Quater is " + dto.getQuarter()); System.out.println("Vioations " + dto.getViolationCount()); System.out.println("Inspection " + dto.getInspectionCount()); } return results; } catch (Exception ex) { logger.error("An error occured in getInspectionsViolationsQuarterlyByDateRange()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } } /** * * Gets a list of inspections and violations separated monthly by the dates provided. * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param fromDate * @param toDate * @return List<CustomInspectionViolationMonthlyDTO> * @throws VSDDataAccessException */ public List<CustomInspectionViolationMonthlyDTO> getInspectionsViolationsMonthlyByDateRange( DataServiceContext dsContext, boolean retrieveArabicData, Date fromDate, Date toDate) throws VSDDataAccessException { logger.info("getInspectionsViolationsMonthlyByDateRange -- START"); /*SELECT COUNT(i.INSPECTION_ID) AS \"inspectionCount\", TO_CHAR(i.INSPECTION_TIMESTAMP,'mm') AS \"month\" FROM VSD_INSPECTION i WHERE i.IS_DELETED = 'F' AND i.IS_COMPLETE = 'T' AND i.INSPECTION_TIMESTAMP BETWEEN to_date('2011-05-15','YYYY-MM-DD') AND to_date( '2011-11-30', 'YYYY-MM-DD') GROUP BY TO_CHAR(i.INSPECTION_TIMESTAMP,'mm') SELECT COUNT(v.VIOLATION_ID) AS \"violationCount\", TO_CHAR(v.REPORTED_DATE,'mm') AS \"month\" FROM VSD_VIOLATION v 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') GROUP BY TO_CHAR(v.REPORTED_DATE,'mm')*/ try { Session session = (Session) dsContext.getInternalContext(); String defaultSchema = DataServiceImpl.DEFAULT_SCHEMA_NAME; StringBuffer query = new StringBuffer( "SELECT COUNT(i.INSPECTION_ID) AS \"inspectionCount\", TO_CHAR(i.INSPECTION_TIMESTAMP,'mm') AS \"month\" " + " FROM " + defaultSchema + ".VSD_INSPECTION i " + " WHERE i.IS_DELETED = '" + IDataService.BOOL_FALSE + "' AND i.IS_COMPLETE = '" + IDataService.BOOL_TRUE + "' "); if (fromDate != null && toDate != null) { SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); query.append( " AND to_date(to_char(i.INSPECTION_TIMESTAMP,'YYYY-MM-DD'),'YYYY-MM-DD') BETWEEN to_date('" + dateFormat.format(fromDate) + "','YYYY-MM-DD') AND to_date('" + dateFormat.format(toDate) + "','YYYY-MM-DD') "); } query.append( " GROUP BY TO_CHAR(i.INSPECTION_TIMESTAMP,'mm') ORDER BY TO_CHAR(i.INSPECTION_TIMESTAMP,'mm') "); List<CustomInspectionViolationMonthlyDTO> inspections = session.createSQLQuery(query.toString()) .addScalar("inspectionCount", Hibernate.LONG).addScalar("month", Hibernate.INTEGER) .setResultTransformer( new AliasToBeanResultTransformer(CustomInspectionViolationMonthlyDTO.class)) .list(); StringBuffer query2 = new StringBuffer( "SELECT COUNT(v.VIOLATION_ID) AS \"violationCount\", TO_CHAR(v.REPORTED_DATE,'mm') AS \"month\" " + " FROM " + defaultSchema + ".VSD_VIOLATION v " + " WHERE v.IS_DELETED = '" + IDataService.BOOL_FALSE + "' "); if (fromDate != null && toDate != null) { SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); query2.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') "); } query2.append(" GROUP BY TO_CHAR(v.REPORTED_DATE,'mm') ORDER BY TO_CHAR(v.REPORTED_DATE,'mm') "); List<CustomInspectionViolationMonthlyDTO> violations = session.createSQLQuery(query2.toString()) .addScalar("violationCount", Hibernate.LONG).addScalar("month", Hibernate.INTEGER) .setResultTransformer( new AliasToBeanResultTransformer(CustomInspectionViolationMonthlyDTO.class)) .list(); List<CustomInspectionViolationMonthlyDTO> results = new ArrayList<CustomInspectionViolationMonthlyDTO>(); Iterator<CustomInspectionViolationMonthlyDTO> iter = inspections.iterator(); while (iter.hasNext()) { CustomInspectionViolationMonthlyDTO inspect = (CustomInspectionViolationMonthlyDTO) iter.next(); CustomInspectionViolationMonthlyDTO combinedDTO = new CustomInspectionViolationMonthlyDTO(); combinedDTO.setMonth(inspect.getMonth()); combinedDTO.setInspectionCount(inspect.getInspectionCount()); Iterator<CustomInspectionViolationMonthlyDTO> iter2 = violations.iterator(); while (iter2.hasNext()) { CustomInspectionViolationMonthlyDTO viol = (CustomInspectionViolationMonthlyDTO) iter2.next(); if (viol.getMonth().intValue() == combinedDTO.getMonth().intValue()) { combinedDTO.setViolationCount(viol.getViolationCount()); } } results.add(combinedDTO); } logger.info("getInspectionsViolationsMonthlyByDateRange -- END"); return results; } catch (Exception ex) { logger.error("An error occured in getInspectionsViolationsMonthlyByDateRange()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } } /** * * get Inspections By ChassisNumber * one overloaded method is associated method * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param vehicleChassisNumber * @return List<VsdInspection> * @throws VSDDataAccessException */ public List<VsdInspection> getInspectionsByChassisNumber(final DataServiceContext dsContext, boolean retrieveArabicData, String vehicleChassisNumber, int maxResults) throws VSDDataAccessException { logger.info("getInspectionsByChassisNumber -- START"); List<VsdInspection> inspections; try { Session session = (Session) dsContext.getInternalContext(); ProjectionList projectionList = Projections.projectionList(); projectionList.add(Projections.property("i.inspectionId"), "inspectionId"); projectionList.add(Projections.property("i.inspectionTimestamp"), "inspectionTimestamp"); Criteria projectionCriteria = session.createCriteria(VsdInspection.class, "i") .add(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.vsdViolation", "v", Criteria.LEFT_JOIN, Restrictions.eq("v.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("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.projectionList().add(Projections.property("i.inspectionId"))) .setProjection(Projections.distinct(projectionList)) .addOrder(Order.desc("i.inspectionTimestamp")) .setResultTransformer(new AliasToBeanResultTransformer(VsdInspection.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()) { VsdInspection vsdInspection = (VsdInspection) iterator.next(); innerQueryList.add(vsdInspection.getInspectionId()); } inspections = session.createCriteria(VsdInspection.class, "i") .add(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.vsdViolation", "v", Criteria.LEFT_JOIN, Restrictions.eq("v.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("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("i.inspectionTimestamp")) .add(Property.forName("i.inspectionId").in(innerQueryList)) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list(); logger.debug("inspections.size() : " + inspections.size()); } catch (Exception ex) { logger.error("An error occured in getInspectionsByChassisNumber()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getInspectionsByChassisNumber -- END"); return inspections; } /** * * get Inspections By VehiclePlate for webservice * one overloaded method is associated method * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param vehiclePlate * @return List<VsdInspection> * @throws VSDDataAccessException */ public List<VsdInspection> getInspectionsByVehiclePlateForWS(final DataServiceContext dsContext, boolean retrieveArabicData, VehiclePlate vehiclePlate, int maxResults) throws VSDDataAccessException { logger.info("getInspectionsByVehiclePlateForWS -- START"); List<VsdInspection> inspections; try { Session session = (Session) dsContext.getInternalContext(); ProjectionList projectionList = Projections.projectionList(); projectionList.add(Projections.property("i.inspectionId"), "inspectionId"); projectionList.add(Projections.property("i.inspectionTimestamp"), "inspectionTimestamp"); Criteria projectionCriteria = session.createCriteria(VsdInspection.class, "i") .add(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.vsdViolation", "v", Criteria.LEFT_JOIN, Restrictions.eq("v.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("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.projectionList().add(Projections.property("i.inspectionId"))) .setProjection(Projections.distinct(projectionList)) .addOrder(Order.desc("i.inspectionTimestamp")) .setResultTransformer(new AliasToBeanResultTransformer(VsdInspection.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()) { VsdInspection vsdInspection = (VsdInspection) iterator.next(); innerQueryList.add(vsdInspection.getInspectionId()); } inspections = session.createCriteria(VsdInspection.class, "i") .add(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.vsdViolation", "v", Criteria.LEFT_JOIN, Restrictions.eq("v.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("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("i.inspectionTimestamp")) .add(Property.forName("i.inspectionId").in(innerQueryList)) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list(); logger.debug("inspections.size() : " + inspections.size()); } catch (Exception ex) { logger.error("An error occured in getInspectionsByVehiclePlateForWS()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getInspectionsByVehiclePlateForWS -- END"); return inspections; } /** * * get Inspections By VehiclePlate for webservice * one overloaded method is associated method * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param vehiclePlate * @param firstResult * @param maxResults * @return List<VsdInspection> * @throws VSDDataAccessException */ public List<VsdInspection> getInspectionsByVehiclePlateForWS(final DataServiceContext dsContext, boolean retrieveArabicData, VehiclePlate vehiclePlate, int firstResult, int maxResults) throws VSDDataAccessException { logger.info("getInspectionsByVehiclePlateForWS -- START"); List<VsdInspection> inspections; try { Session session = (Session) dsContext.getInternalContext(); ProjectionList projectionList = Projections.projectionList(); projectionList.add(Projections.property("i.inspectionId"), "inspectionId"); projectionList.add(Projections.property("i.inspectionTimestamp"), "inspectionTimestamp"); Criteria projectionCriteria = session.createCriteria(VsdInspection.class, "i") .add(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.vsdViolation", "v", Criteria.LEFT_JOIN, Restrictions.eq("v.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("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.projectionList().add(Projections.property("i.inspectionId"))) .setProjection(Projections.distinct(projectionList)) .addOrder(Order.desc("i.inspectionTimestamp")) .setResultTransformer(new AliasToBeanResultTransformer(VsdInspection.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()) { VsdInspection vsdInspection = (VsdInspection) iterator.next(); innerQueryList.add(vsdInspection.getInspectionId()); } inspections = session.createCriteria(VsdInspection.class, "i") .add(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.vsdViolation", "v", Criteria.LEFT_JOIN, Restrictions.eq("v.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("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("i.inspectionTimestamp")) .add(Property.forName("i.inspectionId").in(innerQueryList)) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list(); logger.debug("inspections.size() : " + inspections.size()); } catch (Exception ex) { logger.error("An error occured in getInspectionsByVehiclePlateForWS()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getInspectionsByVehiclePlateForWS -- END"); return inspections; } /** * * get Count For Inspections By ChassisNumber * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param vehicleChassisNumber * @return Long * @throws VSDDataAccessException */ public Long getCountForInspectionsByChassisNumber(final DataServiceContext dsContext, boolean retrieveArabicData, String vehicleChassisNumber) throws VSDDataAccessException { logger.info("getCountForInspectionsByChassisNumber -- START"); Long inspectionsCount; try { Session session = (Session) dsContext.getInternalContext(); Criteria projectionCriteria = session.createCriteria(VsdInspection.class, "i") .add(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.vsdViolation", "v", Criteria.LEFT_JOIN, Restrictions.eq("v.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("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("i.inspectionId")); inspectionsCount = (Long) projectionCriteria.uniqueResult(); logger.debug("inspectionsCount : " + inspectionsCount); } catch (Exception ex) { logger.error("An error occured in getCountForInspectionsByChassisNumber()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getCountForInspectionsByChassisNumber -- END"); return inspectionsCount; } /** * * get Count For Inspections By VehiclePlate * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param vehiclePlate * @return Long * @throws VSDDataAccessException */ public Long getCountForInspectionsByVehiclePlateForWS(final DataServiceContext dsContext, boolean retrieveArabicData, VehiclePlate vehiclePlate) throws VSDDataAccessException { logger.info("getCountForInspectionsByVehiclePlateForWS -- START"); Long inspectionsCount; try { Session session = (Session) dsContext.getInternalContext(); Criteria projectionCriteria = session.createCriteria(VsdInspection.class, "i") .add(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.vsdViolation", "v", Criteria.LEFT_JOIN, Restrictions.eq("v.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("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("i.inspectionId")); inspectionsCount = (Long) projectionCriteria.uniqueResult(); logger.debug("inspectionsCount : " + inspectionsCount); } catch (Exception ex) { logger.error("An error occured in getCountForInspectionsByVehiclePlateForWS()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getCountForInspectionsByVehiclePlateForWS -- END"); return inspectionsCount; } /** * * get Inspections By ChassisNumber * one overloaded method is associated method * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param vehicleChassisNumber * @return List<VsdInspection> * @throws VSDDataAccessException */ public List<VsdInspection> getInspectionsByChassisNumber(final DataServiceContext dsContext, boolean retrieveArabicData, String vehicleChassisNumber) throws VSDDataAccessException { logger.info("getInspectionsByChassisNumber -- START"); List<VsdInspection> inspections; try { Session session = (Session) dsContext.getInternalContext(); Criteria criteria = session.createCriteria(VsdInspection.class, "i") .add(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.vsdViolation", "v", Criteria.LEFT_JOIN, Restrictions.eq("v.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("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.projectionList().add(Projections.property("i.inspectionId"))) .addOrder(Order.desc("i.inspectionTimestamp")) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY); inspections = criteria.list(); logger.debug("inspections.size() : " + inspections.size()); } catch (Exception ex) { logger.error("An error occured in getInspectionsByChassisNumber()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getInspectionsByChassisNumber -- END"); return inspections; } /** * * get Inspections By vehiclePlate * one overloaded method is associated method * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param vehiclePlate * @return List<VsdInspection> * @throws VSDDataAccessException */ public List<VsdInspection> getInspectionsByVehiclePlateForWS(final DataServiceContext dsContext, boolean retrieveArabicData, VehiclePlate vehiclePlate) throws VSDDataAccessException { logger.info("getInspectionsByVehiclePlateForWS -- START"); List<VsdInspection> inspections; try { Session session = (Session) dsContext.getInternalContext(); Criteria criteria = session.createCriteria(VsdInspection.class, "i") .add(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.vsdViolation", "v", Criteria.LEFT_JOIN, Restrictions.eq("v.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("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.projectionList().add(Projections.property("i.inspectionId"))) .addOrder(Order.desc("i.inspectionTimestamp")) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY); inspections = criteria.list(); logger.debug("inspections.size() : " + inspections.size()); } catch (Exception ex) { logger.error("An error occured in getInspectionsByVehiclePlateForWS()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getInspectionsByVehiclePlateForWS -- END"); return inspections; } /** * * get Inspections By TrafficFileNumber * Following three methods are associated. * getInspectionsByTrafficFileNumber(dsContext, retrieveArabicData, trafficFileNumber) * getCountForInspectionsByTrafficFileNumber(dsContext, retrieveArabicData, trafficFileNumber) * getInspectionsByTrafficFileNumber(dsContext, retrieveArabicData, trafficFileNumber, maxResults, firstResult) * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param trafficFileNumber * @return List<VsdInspection> * @throws VSDDataAccessException */ public List<VsdInspection> getInspectionsByTrafficFileNumber(final DataServiceContext dsContext, boolean retrieveArabicData, String trafficFileNumber, int maxResults, int firstResult) throws VSDDataAccessException { logger.info("getInspectionsByTrafficFileNumber -- START"); List<VsdInspection> inspections; try { Session session = (Session) dsContext.getInternalContext(); ProjectionList projectionList = Projections.projectionList(); projectionList.add(Projections.property("i.inspectionId"), "inspectionId"); projectionList.add(Projections.property("i.inspectionTimestamp"), "inspectionTimestamp"); Criteria projectionCriteria = session.createCriteria(VsdInspection.class, "i") .add(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("i.vsdViolation", "vio", Criteria.LEFT_JOIN, Restrictions.eq("vio.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)) .setProjection(Projections.projectionList().add(Projections.property("i.inspectionId"))) .setProjection(Projections.distinct(projectionList)) .addOrder(Order.desc("i.inspectionTimestamp")) .setResultTransformer(new AliasToBeanResultTransformer(VsdInspection.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()) { VsdInspection vsdInspection = (VsdInspection) iterator.next(); innerQueryList.add(vsdInspection.getInspectionId()); } inspections = session.createCriteria(VsdInspection.class, "i") .add(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("i.vsdViolation", "vio", Criteria.LEFT_JOIN, Restrictions.eq("vio.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)) .addOrder(Order.desc("i.inspectionTimestamp")) .add(Property.forName("i.inspectionId").in(innerQueryList)) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list(); logger.debug("inspections.size() : " + inspections.size()); } catch (Exception ex) { logger.error("An error occured in getInspectionsByTrafficFileNumber()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getInspectionsByTrafficFileNumber -- END"); return inspections; } /** * * get Count for Inspections By TrafficFileNumber * Following three methods are associated. * getInspectionsByTrafficFileNumber(dsContext, retrieveArabicData, trafficFileNumber) * getCountForInspectionsByTrafficFileNumber(dsContext, retrieveArabicData, trafficFileNumber) * getInspectionsByTrafficFileNumber(dsContext, retrieveArabicData, trafficFileNumber, maxResults, firstResult) * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param trafficFileNumber * @return List<VsdInspection> * @throws VSDDataAccessException */ public Long getCountForInspectionsByTrafficFileNumber(final DataServiceContext dsContext, boolean retrieveArabicData, String trafficFileNumber) throws VSDDataAccessException { logger.info("getCountForInspectionsByTrafficFileNumber -- START"); Long count = new Long(0); try { Session session = (Session) dsContext.getInternalContext(); ProjectionList projectionList = Projections.projectionList(); Criteria projectionCriteria = session.createCriteria(VsdInspection.class, "i") .add(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("i.vsdViolation", "vio", Criteria.LEFT_JOIN, Restrictions.eq("vio.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)) .setProjection(Projections.countDistinct("i.inspectionId")) .addOrder(Order.desc("i.inspectionTimestamp")); count = (Long) projectionCriteria.uniqueResult(); logger.debug("count : " + count); } catch (Exception ex) { logger.error("An error occured in getCountForInspectionsByTrafficFileNumber()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getCountForInspectionsByTrafficFileNumber -- END"); return count.longValue(); } /** * * get all Inspections By TrafficFileNumber * Following three methods are associated. * getInspectionsByTrafficFileNumber(dsContext, retrieveArabicData, trafficFileNumber) * getCountForInspectionsByTrafficFileNumber(dsContext, retrieveArabicData, trafficFileNumber) * getInspectionsByTrafficFileNumber(dsContext, retrieveArabicData, trafficFileNumber, maxResults, firstResult) * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param trafficFileNumber * @return List<VsdInspection> * @throws VSDDataAccessException */ public List<VsdInspection> getInspectionsByTrafficFileNumber(final DataServiceContext dsContext, boolean retrieveArabicData, String trafficFileNumber) throws VSDDataAccessException { logger.info("getInspectionsByTrafficFileNumber -- START"); List<VsdInspection> inspections = null; try { Session session = (Session) dsContext.getInternalContext(); Criteria criteria = session.createCriteria(VsdInspection.class, "i") .add(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("i.vsdViolation", "vio", Criteria.LEFT_JOIN, Restrictions.eq("vio.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)) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY) .addOrder(Order.desc("i.inspectionTimestamp")); inspections = criteria.list(); } catch (Exception ex) { logger.error("An error occured in getInspectionsByTrafficFileNumber()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getInspectionsByTrafficFileNumber -- END"); return inspections; } /** * * get all Inspections By TrafficFileNumber * Following three methods are associated. * getInspectionsByTrafficFileNumber(dsContext, retrieveArabicData, trafficFileNumber) * getCountForInspectionsByTrafficFileNumber(dsContext, retrieveArabicData, trafficFileNumber) * getInspectionsByTrafficFileNumber(dsContext, retrieveArabicData, trafficFileNumber, maxResults, firstResult) * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param trafficFileNumber * @param maxResults * @return List<VsdInspection> * @throws VSDDataAccessException */ public List<VsdInspection> getInspectionsByTrafficFileNumber(final DataServiceContext dsContext, boolean retrieveArabicData, String trafficFileNumber, int maxResults) throws VSDDataAccessException { logger.info("getInspectionsByTrafficFileNumber -- START"); List<VsdInspection> inspections = null; try { Session session = (Session) dsContext.getInternalContext(); Criteria criteria = session.createCriteria(VsdInspection.class, "i") .add(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("i.vsdViolation", "vio", Criteria.LEFT_JOIN, Restrictions.eq("vio.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)) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY) .addOrder(Order.desc("i.inspectionTimestamp")).setMaxResults(maxResults); inspections = criteria.list(); } catch (Exception ex) { logger.error("An error occured in getInspectionsByTrafficFileNumber()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getInspectionsByTrafficFileNumber -- END"); return inspections; } /** * * Gets list of inpections and the areas they occured in between the given dates. * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param fromDate * @param toDate * @param top * @return List<CustomInspectionAreaDTO> * @throws VSDDataAccessException */ public List<CustomInspectionAreaDTO> getInspectionAreasByDateRange(DataServiceContext dsContext, boolean retrieveArabicData, Date fromDate, Date toDate, int top) throws VSDDataAccessException { logger.info("getInspectionAreasByDateRange -- START"); /*SELECT * FROM ( SELECT COUNT(i.INSPECTION_ID) AS "AS \"inspectionCount\"", area.LOCATION_AREA_NAME AS "areaName", area.LOCATION_AREA_NAME_A AS \"areaNameA\", area.LOCATION_ID AS \"areaId\" FROM VSD_INSPECTION i JOIN VSD_LOCATION loc ON i.LOCATION_ID = loc.LOCATION_ID AND loc.IS_DELETED = 'F' JOIN VSD_LOCATION area ON loc.PARENT_LOCATION_ID = area.LOCATION_ID area.IS_DELETED = 'F' WHERE i.IS_DELETED = 'F' AND i.INSPECTION_TIMESTAMP BETWEEN to_date('2011-05-15','YYYY-MM-DD') AND to_date( '2011-11-30', 'YYYY-MM-DD') GROUP BY area.LOCATION_AREA_NAME, LOCATION_AREA_NAME_A, LOCATION_ID ORDER BY COUNT(i.INSPECTION_ID) DESC ) WHERE rownum < 11*/ try { Session session = (Session) dsContext.getInternalContext(); String defaultSchema = DataServiceImpl.DEFAULT_SCHEMA_NAME; StringBuffer query = new StringBuffer( "SELECT * FROM ( SELECT COUNT(i.INSPECTION_ID) AS \"inspectionCount\", area.LOCATION_AREA_NAME AS \"areaName\", area.LOCATION_AREA_NAME_A AS \"areaNameA\", area.LOCATION_ID AS \"areaId\" " + " FROM " + defaultSchema + ".VSD_INSPECTION i JOIN " + defaultSchema + ".VSD_LOCATION loc ON i.LOCATION_ID = loc.LOCATION_ID AND loc.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " JOIN " + defaultSchema + ".VSD_LOCATION area ON loc.PARENT_LOCATION_ID = area.LOCATION_ID AND area.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " WHERE i.IS_DELETED = '" + IDataService.BOOL_FALSE + "' "); if (fromDate != null && toDate != null) { SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); query.append( " AND to_date(to_char(i.INSPECTION_TIMESTAMP,'YYYY-MM-DD'),'YYYY-MM-DD') BETWEEN to_date('" + dateFormat.format(fromDate) + "','YYYY-MM-DD') AND to_date('" + dateFormat.format(toDate) + "','YYYY-MM-DD') "); } query.append( " GROUP BY area.LOCATION_AREA_NAME, LOCATION_AREA_NAME_A, LOCATION_ID ORDER BY COUNT(i.INSPECTION_ID) DESC ) WHERE rownum < " + (top + 1)); List<CustomInspectionAreaDTO> results = session.createSQLQuery(query.toString()) .addScalar("inspectionCount", Hibernate.LONG).addScalar("areaId", Hibernate.LONG) .addScalar("areaName").addScalar("areaNameA") .setResultTransformer(new AliasToBeanResultTransformer(CustomInspectionAreaDTO.class)).list(); logger.info("getInspectionAreasByDateRange -- END"); return results; } catch (Exception ex) { logger.error("An error occured in getInspectionAreasByDateRange()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } } /** * * Gets those inspections which occurred between the given dates that did not lead to a violation * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param plateDetails * @param startDate * @param endDate * @return List<VsdInspection> * @throws VSDDataAccessException */ public List<VsdInspection> getInspectionsWithNoViolationsByVehiclePlateAndReportedDate( DataServiceContext dsContext, boolean retrieveArabicData, VehiclePlate plateDetails, Date startDate, Date endDate) throws VSDDataAccessException { logger.info("getInspectionsWithNoViolationsByVehiclePlateAndReportedDate -- START"); List<VsdInspection> inspections = null; try { Session session = (Session) dsContext.getInternalContext(); inspections = session.createCriteria(VsdInspection.class, "i") .add(Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.between("i.inspectionTimestamp", startDate, endDate)) .add(Restrictions.isNull("i.vsdViolation")) .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("getInspectionsWithNoViolationsByVehiclePlateAndReportedDate -- END"); return inspections; } catch (Exception ex) { logger.error("An error occured in getInspectionsWithNoViolationsByVehiclePlateAndReportedDate()"); throw new VSDDataAccessException(ex.getMessage(), ex); } } }