Java tutorial
/**<pre> *========================================================================== * * Copyright: (C) IBM Corporation 2010 -- IBM Internal Use Only * *========================================================================== * * FILE: AlertHistoryDataServiceImpl.java * CREATOR: Eldon Barrows * DEPT: GBS PAK * DATE: 27/05/2011 * *-PURPOSE----------------------------------------------------------------- * Implementation for IAlertHistoryDataService * *------------------------------------------------------------------------- * * *-CHANGE LOG-------------------------------------------------------------- * 27/05/2011 Eldon Initial coding. *========================================================================== * </pre> */ package com.rta.vsd.data.service.impl; import java.math.BigDecimal; import java.text.SimpleDateFormat; import java.util.Calendar; import java.util.Date; import java.util.List; import org.apache.log4j.Logger; import org.hibernate.Criteria; import org.hibernate.Hibernate; import org.hibernate.Query; import org.hibernate.Session; import org.hibernate.criterion.Restrictions; import org.hibernate.transform.AliasToBeanResultTransformer; import com.rta.vsd.business.service.utility.scheduler.IScheduler; import com.rta.vsd.data.DataServiceContext; import com.rta.vsd.data.service.IAlertHistoryDataService; import com.rta.vsd.data.service.IDataService; import com.rta.vsd.dto.VsdAlertHistory; import com.rta.vsd.dto.custom.AlertStatusSearchCriteria; import com.rta.vsd.dto.custom.AlertStatusSearchResult; import com.rta.vsd.dto.custom.PaginationParam; import com.rta.vsd.exception.data.VSDDataAccessException; import com.rta.vsd.utility.Constant; import com.rta.vsd.utility.VSDLogger; /** * Implementation for IAlertHistoryDataService * * @author Eldon Barrows * */ public class AlertHistoryDataServiceImpl extends DataServiceImpl implements IAlertHistoryDataService { private static Logger logger = VSDLogger.init(AlertHistoryDataServiceImpl.class.getName()); /** * * Search the alert statuses based on the criteria(See AlertStatusSearchCriteria DTO) provided. * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param searchCriteria * @return List<AlertStatusSearchResult> * @throws VSDDataAccessException */ public List<AlertStatusSearchResult> searchAlertStatus(DataServiceContext dsContext, boolean retrieveArabicData, AlertStatusSearchCriteria searchCriteria) throws VSDDataAccessException { /*Select A.SEND_STATUS as "alertStatus", VAT.ALERT_TYPE_NAME as "alertType", VAT.ALERT_TYPE_NAME_A as "alertTypeA", A.SEND_TIMESTAMP as "alertDateTime", CC.MODE_NAME as "communicationChannel", V.VIOLATION_TICKET_CODE as "violation", A.PLATE_CATEGORY as "plateCategory", A.PLATE_CATEGORY_CODE as "plateCode", A.VEHICLE_NUMBER as "plateNumber", VO.OWNER_NAME as "operatorName", VO.OWNER_NAME_A as "operatorNameA" , A.TRAFFIC_FILE_NUMBER as "trafficFileNumber", VO.VEHICLE_OWNER_ID as "vehicleOwnerId" from VSDSTDEV.VSD_ALERT_HISTORY A left outer join VSDSTDEV.VSD_INSPECTION I on A.INSPECTION_ID = I.INSPECTION_ID and I.IS_DELETED = 'F' left outer join VSDSTDEV.VSD_VIOLATION V on I.VIOLATION_ID = V.VIOLATION_ID and V.IS_DELETED = 'F' left outer join VSDSTDEV.VSD_VEHICLE_OWNER VO on A.TRAFFIC_FILE_NUMBER = VO.TRAFFIC_FILE_NUMBER and VO.IS_DELETED = 'F' inner join VSDSTDEV.VSD_ALERT_RULE AR on A.ALERT_RULE_ID = AR.ALERT_RULE_ID and AR.IS_DELETED = 'F' inner join VSDSTDEV.VSD_ALERT_TYPE VAT on AR.ALERT_TYPE_ID = VAT.ALERT_TYPE_ID and VAT.IS_DELETED = 'F' inner join VSDSTDEV.VSD_ALERT_TEMPLATE_INFO ati on ati.ALERT_TEMPLATE_INFO_ID = A.ALERT_TEMPLATE_INFO_ID inner join VSDSTDEV.VSD_COM_CHANNEL CC on ati.COM_CHANNEL_ID = CC.COM_CHANNEL_ID and CC.IS_DELETED = 'F' where A.IS_DELETED = 'F' */ logger.info("searchViolations -- START"); List<AlertStatusSearchResult> results = null; try { Session session = (Session) dsContext.getInternalContext(); String defaultSchema = DataServiceImpl.DEFAULT_SCHEMA_NAME; boolean hasOperatorname = false; boolean hasOperatorNameA = false; boolean hasTradeLicenseNumber = false; boolean hasTrafficeFileNumber = false; boolean hasPlateCodeId = false; boolean hasPlateCategoryId = false; boolean hasPlateNumber = false; boolean hasStartEndDate = false; boolean hasAlertStatus = false; boolean hasAlertTypeId = false; boolean hasComChannelId = false; //TODO: Fix the query StringBuffer query = new StringBuffer( "Select A.SEND_STATUS as \"alertStatus\", VAT.ALERT_TYPE_NAME as \"alertType\", VAT.ALERT_TYPE_NAME_A as \"alertTypeA\", A.SEND_TIMESTAMP as \"alertDateTime\", CC.MODE_NAME as \"communicationChannel\", CC.MODE_NAME_A as \"communicationChannelA\", V.VIOLATION_TICKET_CODE as \"violation\", A.PLATE_CATEGORY as \"plateCategory\", A.PLATE_CATEGORY_CODE as \"plateCode\", A.VEHICLE_NUMBER as \"plateNumber\", A.PLATE_SOURCE as \"emirateName\",VO.OWNER_NAME as \"operatorName\", VO.OWNER_NAME_A as \"operatorNameA\", A.TRAFFIC_FILE_NUMBER as \"trafficFileNumber\", VO.VEHICLE_OWNER_ID as \"vehicleOwnerId\" " + " from " + defaultSchema + ".VSD_ALERT_HISTORY A left outer join " + defaultSchema + ".VSD_INSPECTION I on A.INSPECTION_ID = I.INSPECTION_ID and I.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " left outer join " + defaultSchema + ".VSD_VIOLATION V on I.VIOLATION_ID = V.VIOLATION_ID and V.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " left outer join " + defaultSchema + ".VSD_VEHICLE_OWNER VO on A.TRAFFIC_FILE_NUMBER = VO.TRAFFIC_FILE_NUMBER and VO.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " inner join " + defaultSchema + ".VSD_ALERT_RULE AR on A.ALERT_RULE_ID = AR.ALERT_RULE_ID and AR.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " inner join " + defaultSchema + ".VSD_ALERT_TYPE VAT on AR.ALERT_TYPE_ID = VAT.ALERT_TYPE_ID and VAT.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " inner join " + defaultSchema + ".VSD_ALERT_TEMPLATE_INFO ATI on A.ALERT_TEMPLATE_INFO_ID = ATI.ALERT_TEMPLATE_INFO_ID and ATI.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " inner join " + defaultSchema + ".VSD_COM_CHANNEL CC on ATI.COM_CHANNEL_ID = CC.COM_CHANNEL_ID and CC.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " where A.IS_DELETED = '" + IDataService.BOOL_FALSE + "'"); if (searchCriteria.getOperatorName() != null && !searchCriteria.getOperatorName().equals("")) { hasOperatorname = true; query.append(" and UPPER(VO.OWNER_NAME) like '%" + searchCriteria.getOperatorName().replaceAll("'", "''").toUpperCase() + "%'"); } if (searchCriteria.getOperatorNameA() != null && !searchCriteria.getOperatorNameA().equals("")) { hasOperatorNameA = true; query.append(" and VO.OWNER_NAME_A like '%" + searchCriteria.getOperatorNameA().replaceAll("'", "''") + "%'"); } if (searchCriteria.getTradeLicenseNumber() != null && !searchCriteria.getTradeLicenseNumber().equals("")) { hasTradeLicenseNumber = true; query.append(" and VO.TRADE_LICENSE_NUMBER = '" + searchCriteria.getTradeLicenseNumber().replaceAll("'", "''") + "'"); } if (searchCriteria.getTrafficFileNumber() != null && !searchCriteria.getTrafficFileNumber().equals("")) { hasTrafficeFileNumber = true; query.append(" and A.TRAFFIC_FILE_NUMBER = '" + searchCriteria.getTrafficFileNumber().replaceAll("'", "''") + "'"); } if (searchCriteria.getPlateCodeId() != null) { hasPlateCodeId = true; query.append(" and UPPER(A.PLATE_CATEGORY_CODE) = (Select UPPER(VP.CATEGORY_NAME) from " + defaultSchema + ".VSD_VEH_PLATE_CAT_CODE VP where VP.VEH_PLATE_CAT_CODE_ID = " + searchCriteria.getPlateCodeId() + " ) "); } if (searchCriteria.getPlateCategoryId() != null) { hasPlateCategoryId = true; query.append(" and UPPER(A.PLATE_CATEGORY) = (Select UPPER(VP.CATEGORY_NAME) from " + defaultSchema + ".VSD_VEH_PLATE_CAT_CODE VP where VP.VEH_PLATE_CAT_CODE_ID = " + searchCriteria.getPlateCategoryId() + " ) "); } if (searchCriteria.getPlateNumber() != null && !searchCriteria.getPlateNumber().equals("")) { hasPlateNumber = true; query.append( " and A.VEHICLE_NUMBER = '" + searchCriteria.getPlateNumber().replaceAll("'", "''") + "'"); } if (searchCriteria.getStartDate() != null && searchCriteria.getEndDate() != null) { hasStartEndDate = true; SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); query.append(" and to_date(to_char(A.SEND_TIMESTAMP,'YYYY-MM-DD'),'YYYY-MM-DD') between to_date('" + dateFormat.format(searchCriteria.getStartDate()) + "','YYYY-MM-DD') and to_date('" + dateFormat.format(searchCriteria.getEndDate()) + "','YYYY-MM-DD')"); } if (searchCriteria.getAlertStatus() != null && !searchCriteria.getAlertStatus().equals("")) { hasAlertStatus = true; query.append(" and A.SEND_STATUS = '" + searchCriteria.getAlertStatus() + "' "); } if (searchCriteria.getAlertTypeId() != null) { hasAlertTypeId = true; query.append(" and VAT.ALERT_TYPE_ID = " + searchCriteria.getAlertTypeId()); } if (searchCriteria.getComChannelId() != null) { hasComChannelId = true; query.append(" and CC.COM_CHANNEL_ID = " + searchCriteria.getComChannelId()); } if (searchCriteria.getPlateSource() != null && !searchCriteria.getPlateSource().equals("")) { if (searchCriteria.getAlertTypeId() != null) { String alertTypeNameQuery = "SELECT ALERT_TYPE_NAME FROM " + defaultSchema + ".VSD_ALERT_TYPE WHERE ALERT_TYPE_ID = " + searchCriteria.getAlertTypeId(); String alertTypeName = (String) session.createSQLQuery(alertTypeNameQuery).uniqueResult(); logger.info("alertTypeName is " + alertTypeName); if (alertTypeName.equalsIgnoreCase(IScheduler.VIOLATION) || alertTypeName.equalsIgnoreCase(IScheduler.VIOLATION_TEST_RESULT) || alertTypeName.equalsIgnoreCase(IScheduler.PLATE_CONFISCATION)) { query.append(" and UPPER(A.PLATE_SOURCE) = '" + searchCriteria.getPlateSource().toUpperCase() + "' "); } } else if (!hasOperatorname == true && !hasOperatorNameA == true && !hasTradeLicenseNumber == true && !hasTrafficeFileNumber == true && !hasPlateCodeId == true && !hasPlateCategoryId == true && !hasPlateNumber == true && !hasStartEndDate == true && !hasAlertStatus == true && !hasAlertTypeId == true && !hasComChannelId == true) { query.append(" and UPPER(A.PLATE_SOURCE) = '" + searchCriteria.getPlateSource().toUpperCase() + "' or A.PLATE_SOURCE IS NULL"); } else if (hasPlateCodeId == true || hasPlateCategoryId == true || hasPlateNumber == true) { query.append(" and UPPER(A.PLATE_SOURCE) = '" + searchCriteria.getPlateSource().toUpperCase() + "' "); } } results = session.createSQLQuery(query.toString()).addScalar("alertStatus").addScalar("alertType") .addScalar("alertTypeA").addScalar("alertDateTime", Hibernate.DATE) .addScalar("communicationChannel").addScalar("communicationChannelA").addScalar("violation") .addScalar("plateCategory").addScalar("plateCode").addScalar("plateNumber") .addScalar("emirateName").addScalar("operatorName").addScalar("operatorNameA") .addScalar("trafficFileNumber").addScalar("vehicleOwnerId", Hibernate.LONG) .setResultTransformer(new AliasToBeanResultTransformer(AlertStatusSearchResult.class)).list(); } catch (Exception ex) { logger.error("An error occured in searchViolations()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("searchViolations -- END"); return results; } /** * * Search the alert statuses based on the criteria(See AlertStatusSearchCriteria DTO) provided. * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param searchCriteria * @param paginationParam * @return List<AlertStatusSearchResult> * @throws VSDDataAccessException */ public List<AlertStatusSearchResult> searchAlertStatus(DataServiceContext dsContext, boolean retrieveArabicData, AlertStatusSearchCriteria searchCriteria, PaginationParam paginationParam) throws VSDDataAccessException { logger.info("searchViolations -- START"); List<AlertStatusSearchResult> results = null; try { Session session = (Session) dsContext.getInternalContext(); String defaultSchema = DataServiceImpl.DEFAULT_SCHEMA_NAME; boolean hasOperatorname = false; boolean hasOperatorNameA = false; boolean hasTradeLicenseNumber = false; boolean hasTrafficeFileNumber = false; boolean hasPlateCodeId = false; boolean hasPlateCategoryId = false; boolean hasPlateNumber = false; boolean hasStartEndDate = false; boolean hasAlertStatus = false; boolean hasAlertTypeId = false; boolean hasComChannelId = false; //TODO: Fix the query StringBuffer query = new StringBuffer( "Select A.SEND_STATUS as \"alertStatus\", VAT.ALERT_TYPE_NAME as \"alertType\", VAT.ALERT_TYPE_NAME_A as \"alertTypeA\", A.SEND_TIMESTAMP as \"alertDateTime\", CC.MODE_NAME as \"communicationChannel\", CC.MODE_NAME_A as \"communicationChannelA\", V.VIOLATION_TICKET_CODE as \"violation\", A.PLATE_CATEGORY as \"plateCategory\", A.PLATE_CATEGORY_CODE as \"plateCode\", A.VEHICLE_NUMBER as \"plateNumber\", A.PLATE_SOURCE as \"emirateName\",VO.OWNER_NAME as \"operatorName\", VO.OWNER_NAME_A as \"operatorNameA\", A.TRAFFIC_FILE_NUMBER as \"trafficFileNumber\", VO.VEHICLE_OWNER_ID as \"vehicleOwnerId\" " + " from " + defaultSchema + ".VSD_ALERT_HISTORY A left outer join " + defaultSchema + ".VSD_INSPECTION I on A.INSPECTION_ID = I.INSPECTION_ID and I.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " left outer join " + defaultSchema + ".VSD_VIOLATION V on I.VIOLATION_ID = V.VIOLATION_ID and V.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " left outer join " + defaultSchema + ".VSD_VEHICLE_OWNER VO on A.TRAFFIC_FILE_NUMBER = VO.TRAFFIC_FILE_NUMBER and VO.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " inner join " + defaultSchema + ".VSD_ALERT_RULE AR on A.ALERT_RULE_ID = AR.ALERT_RULE_ID and AR.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " inner join " + defaultSchema + ".VSD_ALERT_TYPE VAT on AR.ALERT_TYPE_ID = VAT.ALERT_TYPE_ID and VAT.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " inner join " + defaultSchema + ".VSD_ALERT_TEMPLATE_INFO ATI on A.ALERT_TEMPLATE_INFO_ID = ATI.ALERT_TEMPLATE_INFO_ID and ATI.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " inner join " + defaultSchema + ".VSD_COM_CHANNEL CC on ATI.COM_CHANNEL_ID = CC.COM_CHANNEL_ID and CC.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " where A.IS_DELETED = '" + IDataService.BOOL_FALSE + "'"); if (searchCriteria.getOperatorName() != null && !searchCriteria.getOperatorName().equals("")) { hasOperatorname = true; query.append(" and UPPER(VO.OWNER_NAME) like '%" + searchCriteria.getOperatorName().replaceAll("'", "''").toUpperCase() + "%'"); } if (searchCriteria.getOperatorNameA() != null && !searchCriteria.getOperatorNameA().equals("")) { hasOperatorNameA = true; query.append(" and VO.OWNER_NAME_A like '%" + searchCriteria.getOperatorNameA().replaceAll("'", "''") + "%'"); } if (searchCriteria.getTradeLicenseNumber() != null && !searchCriteria.getTradeLicenseNumber().equals("")) { hasTradeLicenseNumber = true; query.append(" and VO.TRADE_LICENSE_NUMBER = '" + searchCriteria.getTradeLicenseNumber().replaceAll("'", "''") + "'"); } if (searchCriteria.getTrafficFileNumber() != null && !searchCriteria.getTrafficFileNumber().equals("")) { hasTrafficeFileNumber = true; query.append(" and A.TRAFFIC_FILE_NUMBER = '" + searchCriteria.getTrafficFileNumber().replaceAll("'", "''") + "'"); } if (searchCriteria.getPlateCodeId() != null) { hasPlateCodeId = true; query.append(" and UPPER(A.PLATE_CATEGORY_CODE) = (Select UPPER(VP.CATEGORY_NAME) from " + defaultSchema + ".VSD_VEH_PLATE_CAT_CODE VP where VP.VEH_PLATE_CAT_CODE_ID = " + searchCriteria.getPlateCodeId() + " ) "); } if (searchCriteria.getPlateCategoryId() != null) { hasPlateCategoryId = true; query.append(" and UPPER(A.PLATE_CATEGORY) = (Select UPPER(VP.CATEGORY_NAME) from " + defaultSchema + ".VSD_VEH_PLATE_CAT_CODE VP where VP.VEH_PLATE_CAT_CODE_ID = " + searchCriteria.getPlateCategoryId() + " ) "); } if (searchCriteria.getPlateNumber() != null && !searchCriteria.getPlateNumber().equals("")) { hasPlateNumber = true; query.append( " and A.VEHICLE_NUMBER = '" + searchCriteria.getPlateNumber().replaceAll("'", "''") + "'"); } if (searchCriteria.getStartDate() != null && searchCriteria.getEndDate() != null) { hasStartEndDate = true; SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); query.append(" and to_date(to_char(A.SEND_TIMESTAMP,'YYYY-MM-DD'),'YYYY-MM-DD') between to_date('" + dateFormat.format(searchCriteria.getStartDate()) + "','YYYY-MM-DD') and to_date('" + dateFormat.format(searchCriteria.getEndDate()) + "','YYYY-MM-DD')"); } if (searchCriteria.getAlertStatus() != null && !searchCriteria.getAlertStatus().equals("")) { hasAlertStatus = true; query.append(" and A.SEND_STATUS = '" + searchCriteria.getAlertStatus() + "' "); } if (searchCriteria.getAlertTypeId() != null) { hasAlertTypeId = true; query.append(" and VAT.ALERT_TYPE_ID = " + searchCriteria.getAlertTypeId()); } if (searchCriteria.getComChannelId() != null) { hasComChannelId = true; query.append(" and CC.COM_CHANNEL_ID = " + searchCriteria.getComChannelId()); } if (searchCriteria.getPlateSource() != null && !searchCriteria.getPlateSource().equals("")) { if (searchCriteria.getAlertTypeId() != null) { String alertTypeNameQuery = "SELECT ALERT_TYPE_NAME FROM " + defaultSchema + ".VSD_ALERT_TYPE WHERE ALERT_TYPE_ID = " + searchCriteria.getAlertTypeId(); String alertTypeName = (String) session.createSQLQuery(alertTypeNameQuery).uniqueResult(); logger.info("alertTypeName is " + alertTypeName); if (alertTypeName.equalsIgnoreCase(IScheduler.VIOLATION) || alertTypeName.equalsIgnoreCase(IScheduler.VIOLATION_TEST_RESULT) || alertTypeName.equalsIgnoreCase(IScheduler.PLATE_CONFISCATION)) { query.append(" and UPPER(A.PLATE_SOURCE) = '" + searchCriteria.getPlateSource().toUpperCase() + "' "); } } else if (!hasOperatorname == true && !hasOperatorNameA == true && !hasTradeLicenseNumber == true && !hasTrafficeFileNumber == true && !hasPlateCodeId == true && !hasPlateCategoryId == true && !hasPlateNumber == true && !hasStartEndDate == true && !hasAlertStatus == true && !hasAlertTypeId == true && !hasComChannelId == true) { query.append(" and UPPER(A.PLATE_SOURCE) = '" + searchCriteria.getPlateSource().toUpperCase() + "' or A.PLATE_SOURCE IS NULL"); } else if (hasPlateCodeId == true || hasPlateCategoryId == true || hasPlateNumber == true) { query.append(" and UPPER(A.PLATE_SOURCE) = '" + searchCriteria.getPlateSource().toUpperCase() + "' "); } } if (paginationParam.getPageLocale().equalsIgnoreCase(Constant.LOCALE_ENGLISH)) { query.append(" ORDER BY "); query.append( " LOWER(VAT.ALERT_TYPE_NAME) asc, LOWER(A.SEND_TIMESTAMP) asc, LOWER(CC.MODE_NAME) asc, LOWER(A.SEND_STATUS) asc, LOWER(VO.OWNER_NAME) asc, LOWER(A.VEHICLE_NUMBER) asc, LOWER(A.PLATE_CATEGORY) asc, LOWER(A.PLATE_CATEGORY_CODE) asc "); } else { query.append(" ORDER BY "); query.append( " LOWER(VAT.ALERT_TYPE_NAME_A) asc, LOWER(A.SEND_TIMESTAMP) asc, LOWER(CC.MODE_NAME) asc, LOWER(A.SEND_STATUS) asc, LOWER(VO.OWNER_NAME_A) asc, LOWER(A.VEHICLE_NUMBER) asc, LOWER(A.PLATE_CATEGORY) asc, LOWER(A.PLATE_CATEGORY_CODE) asc "); } results = session.createSQLQuery(query.toString()).addScalar("alertStatus").addScalar("alertType") .addScalar("alertTypeA").addScalar("alertDateTime", Hibernate.DATE) .addScalar("communicationChannel").addScalar("communicationChannelA").addScalar("violation") .addScalar("plateCategory").addScalar("plateCode").addScalar("plateNumber") .addScalar("emirateName").addScalar("operatorName").addScalar("operatorNameA") .addScalar("trafficFileNumber").addScalar("vehicleOwnerId", Hibernate.LONG) .setResultTransformer(new AliasToBeanResultTransformer(AlertStatusSearchResult.class)) .setFirstResult(paginationParam.getFirstResult().intValue()) .setMaxResults(paginationParam.getFetchedSize().intValue()).list(); } catch (Exception ex) { logger.error("An error occured in searchViolations()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("searchViolations -- END"); return results; } /** * * Search the alert statuses based on the criteria(See AlertStatusSearchCriteria DTO) provided. * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param searchCriteria * @return Long * @throws VSDDataAccessException */ public Long getCountForSearchAlertStatus(DataServiceContext dsContext, boolean retrieveArabicData, AlertStatusSearchCriteria searchCriteria) throws VSDDataAccessException { logger.info("searchViolations -- START"); Long resultSize = null; try { Session session = (Session) dsContext.getInternalContext(); String defaultSchema = DataServiceImpl.DEFAULT_SCHEMA_NAME; boolean hasOperatorname = false; boolean hasOperatorNameA = false; boolean hasTradeLicenseNumber = false; boolean hasTrafficeFileNumber = false; boolean hasPlateCodeId = false; boolean hasPlateCategoryId = false; boolean hasPlateNumber = false; boolean hasStartEndDate = false; boolean hasAlertStatus = false; boolean hasAlertTypeId = false; boolean hasComChannelId = false; //TODO: Fix the query StringBuffer query = new StringBuffer( "Select count(*) from (Select A.SEND_STATUS as \"alertStatus\", VAT.ALERT_TYPE_NAME as \"alertType\", VAT.ALERT_TYPE_NAME_A as \"alertTypeA\", A.SEND_TIMESTAMP as \"alertDateTime\", CC.MODE_NAME as \"communicationChannel\", CC.MODE_NAME_A as \"communicationChannelA\",V.VIOLATION_TICKET_CODE as \"violation\", A.PLATE_CATEGORY as \"plateCategory\", A.PLATE_CATEGORY_CODE as \"plateCode\", A.VEHICLE_NUMBER as \"plateNumber\", A.PLATE_SOURCE as \"emirateName\",VO.OWNER_NAME as \"operatorName\", VO.OWNER_NAME_A as \"operatorNameA\", A.TRAFFIC_FILE_NUMBER as \"trafficFileNumber\", VO.VEHICLE_OWNER_ID as \"vehicleOwnerId\" " + " from " + defaultSchema + ".VSD_ALERT_HISTORY A left outer join " + defaultSchema + ".VSD_INSPECTION I on A.INSPECTION_ID = I.INSPECTION_ID and I.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " left outer join " + defaultSchema + ".VSD_VIOLATION V on I.VIOLATION_ID = V.VIOLATION_ID and V.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " left outer join " + defaultSchema + ".VSD_VEHICLE_OWNER VO on A.TRAFFIC_FILE_NUMBER = VO.TRAFFIC_FILE_NUMBER and VO.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " inner join " + defaultSchema + ".VSD_ALERT_RULE AR on A.ALERT_RULE_ID = AR.ALERT_RULE_ID and AR.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " inner join " + defaultSchema + ".VSD_ALERT_TYPE VAT on AR.ALERT_TYPE_ID = VAT.ALERT_TYPE_ID and VAT.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " inner join " + defaultSchema + ".VSD_ALERT_TEMPLATE_INFO ATI on A.ALERT_TEMPLATE_INFO_ID = ATI.ALERT_TEMPLATE_INFO_ID and ATI.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " inner join " + defaultSchema + ".VSD_COM_CHANNEL CC on ATI.COM_CHANNEL_ID = CC.COM_CHANNEL_ID and CC.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " where A.IS_DELETED = '" + IDataService.BOOL_FALSE + "'"); if (searchCriteria.getOperatorName() != null && !searchCriteria.getOperatorName().equals("")) { hasOperatorname = true; query.append(" and UPPER(VO.OWNER_NAME) like '%" + searchCriteria.getOperatorName().replaceAll("'", "''").toUpperCase() + "%'"); } if (searchCriteria.getOperatorNameA() != null && !searchCriteria.getOperatorNameA().equals("")) { hasOperatorNameA = true; query.append(" and VO.OWNER_NAME_A like '%" + searchCriteria.getOperatorNameA().replaceAll("'", "''") + "%'"); } if (searchCriteria.getTradeLicenseNumber() != null && !searchCriteria.getTradeLicenseNumber().equals("")) { hasTradeLicenseNumber = true; query.append(" and VO.TRADE_LICENSE_NUMBER = '" + searchCriteria.getTradeLicenseNumber().replaceAll("'", "''") + "'"); } if (searchCriteria.getTrafficFileNumber() != null && !searchCriteria.getTrafficFileNumber().equals("")) { hasTrafficeFileNumber = true; query.append(" and A.TRAFFIC_FILE_NUMBER = '" + searchCriteria.getTrafficFileNumber().replaceAll("'", "''") + "'"); } if (searchCriteria.getPlateCodeId() != null) { hasPlateCodeId = true; query.append(" and UPPER(A.PLATE_CATEGORY_CODE) = (Select UPPER(VP.CATEGORY_NAME) from " + defaultSchema + ".VSD_VEH_PLATE_CAT_CODE VP where VP.VEH_PLATE_CAT_CODE_ID = " + searchCriteria.getPlateCodeId() + " ) "); } if (searchCriteria.getPlateCategoryId() != null) { hasPlateCategoryId = true; query.append(" and UPPER(A.PLATE_CATEGORY) = (Select UPPER(VP.CATEGORY_NAME) from " + defaultSchema + ".VSD_VEH_PLATE_CAT_CODE VP where VP.VEH_PLATE_CAT_CODE_ID = " + searchCriteria.getPlateCategoryId() + " ) "); } if (searchCriteria.getPlateNumber() != null && !searchCriteria.getPlateNumber().equals("")) { hasPlateNumber = true; query.append( " and A.VEHICLE_NUMBER = '" + searchCriteria.getPlateNumber().replaceAll("'", "''") + "'"); } if (searchCriteria.getStartDate() != null && searchCriteria.getEndDate() != null) { hasStartEndDate = true; SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); query.append(" and to_date(to_char(A.SEND_TIMESTAMP,'YYYY-MM-DD'),'YYYY-MM-DD') between to_date('" + dateFormat.format(searchCriteria.getStartDate()) + "','YYYY-MM-DD') and to_date('" + dateFormat.format(searchCriteria.getEndDate()) + "','YYYY-MM-DD')"); } if (searchCriteria.getAlertStatus() != null && !searchCriteria.getAlertStatus().equals("")) { hasAlertStatus = true; query.append(" and A.SEND_STATUS = '" + searchCriteria.getAlertStatus() + "' "); } if (searchCriteria.getAlertTypeId() != null) { hasAlertTypeId = true; query.append(" and VAT.ALERT_TYPE_ID = " + searchCriteria.getAlertTypeId()); } if (searchCriteria.getComChannelId() != null) { hasComChannelId = true; query.append(" and CC.COM_CHANNEL_ID = " + searchCriteria.getComChannelId()); } if (searchCriteria.getPlateSource() != null && !searchCriteria.getPlateSource().equals("")) { if (searchCriteria.getAlertTypeId() != null) { String alertTypeNameQuery = "SELECT ALERT_TYPE_NAME FROM " + defaultSchema + ".VSD_ALERT_TYPE WHERE ALERT_TYPE_ID = " + searchCriteria.getAlertTypeId(); String alertTypeName = (String) session.createSQLQuery(alertTypeNameQuery).uniqueResult(); logger.info("alertTypeName is " + alertTypeName); if (alertTypeName.equalsIgnoreCase(IScheduler.VIOLATION) || alertTypeName.equalsIgnoreCase(IScheduler.VIOLATION_TEST_RESULT) || alertTypeName.equalsIgnoreCase(IScheduler.PLATE_CONFISCATION)) { query.append(" and UPPER(A.PLATE_SOURCE) = '" + searchCriteria.getPlateSource().toUpperCase() + "' "); } } else if (!hasOperatorname == true && !hasOperatorNameA == true && !hasTradeLicenseNumber == true && !hasTrafficeFileNumber == true && !hasPlateCodeId == true && !hasPlateCategoryId == true && !hasPlateNumber == true && !hasStartEndDate == true && !hasAlertStatus == true && !hasAlertTypeId == true && !hasComChannelId == true) { query.append(" and UPPER(A.PLATE_SOURCE) = '" + searchCriteria.getPlateSource().toUpperCase() + "' or A.PLATE_SOURCE IS NULL"); } else if (hasPlateCodeId == true || hasPlateCategoryId == true || hasPlateNumber == true) { query.append(" and UPPER(A.PLATE_SOURCE) = '" + searchCriteria.getPlateSource().toUpperCase() + "' "); } } query.append(" ) "); BigDecimal bigDecimalCount = (BigDecimal) session.createSQLQuery(query.toString()).uniqueResult(); resultSize = bigDecimalCount.longValue(); } catch (Exception ex) { logger.error("An error occured in searchViolations()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("searchViolations -- END"); return resultSize; } /** * * Save Alert History. * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param vsdAlertHistory * @return vsdAlertHistory: Assigned with generated Id * @throws VSDDataAccessException */ public VsdAlertHistory saveAlertHistory(DataServiceContext dsContext, boolean retrieveArabicData, VsdAlertHistory vsdAlertHistory) throws VSDDataAccessException { logger.info("saveAlertHistory -- START"); try { Session session = (Session) dsContext.getInternalContext(); Long id = (Long) session.save(vsdAlertHistory); vsdAlertHistory.setAlertHistoryId(id); logger.info("saveAlertHistory -- END"); return vsdAlertHistory; } catch (Exception ex) { logger.error("An error occured in the saveAlertHistory():" + ex.getMessage()); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } } /** * * Gets the history of an alert from the inspection id, alert template id, alert rule id and timestamp provided. * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param status * @param inspectionId * @param alertRuleId * @param createdTimeStamp * @return VsdAlertHistory * @throws VSDDataAccessException */ public VsdAlertHistory getAlertHistoryByInspectionAlertRuleStatusTimeStamp(DataServiceContext dsContext, boolean retrieveArabicData, String status, Long inspectionId, Long alertRuleId, Date createdTimeStamp) throws VSDDataAccessException { logger.info("getAlertHistoryByInspectionTemplateRuleTimeStamp -- START"); logger.info("getAlertHistoryByInspectionAlertRuleStatusTimeStamp -- START"); VsdAlertHistory alertHistory = null; try { Calendar startDate = Calendar.getInstance(); startDate.setTime(createdTimeStamp); 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(createdTimeStamp); 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(); alertHistory = (VsdAlertHistory) session.createCriteria(VsdAlertHistory.class, "ah") .add(Restrictions.eq("ah.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("ah.sendStatus", status)) .add(Restrictions.between("ah.createdTimestamp", startDate.getTime(), endDate.getTime())) .createCriteria("ah.vsdInspection", "i", Criteria.INNER_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("i.inspectionId", inspectionId)) .createCriteria("ah.vsdAlertRule", "ar", Criteria.INNER_JOIN, Restrictions.eq("ar.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("ar.alertRuleId", alertRuleId)).uniqueResult(); logger.info("getAlertHistoryByInspectionAlertRuleStatusTimeStamp -- END"); return alertHistory; } catch (Exception ex) { logger.error("An error occured in getAlertHistoryByInspectionAlertRuleStatusTimeStamp()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } } /** * * Updates Alert History. * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param vsdAlertHistory * @return vsdAlertHistory * @throws VSDDataAccessException */ public VsdAlertHistory updateAlertHistory(DataServiceContext dsContext, boolean retrieveArabicData, VsdAlertHistory vsdAlertHistory) throws VSDDataAccessException { logger.info("updateAlertHistory -- START"); try { Query query = createDynamicUpdateQuery(vsdAlertHistory, dsContext); query.executeUpdate(); logger.info("updateAlertHistory -- END"); return vsdAlertHistory; } catch (Exception ex) { logger.error("An error occured in the updateAlertHistory():" + ex.getMessage()); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } } /** * * Gets a specific alert history by the alert history id provided. * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param alertHistoryId * @return VsdAlertHistory * @throws VSDDataAccessException */ public VsdAlertHistory getAlertHistoryById(DataServiceContext dsContext, boolean retrieveArabicData, Long alertHistoryId) throws VSDDataAccessException { logger.info("getAlertHistoryById -- START"); VsdAlertHistory alertHistory = null; try { Session session = (Session) dsContext.getInternalContext(); alertHistory = (VsdAlertHistory) session.createCriteria(VsdAlertHistory.class, "ah") .add(Restrictions.eq("ah.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("ah.alertHistoryId", alertHistoryId)).uniqueResult(); logger.info("getAlertHistoryById -- END"); return alertHistory; } catch (Exception ex) { logger.error("An error occured in getAlertHistoryById()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } } /** * * Gets a list of alert history with the alert template info, communication channel, supported language, vehicle owner contact details by the status provided. * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param sendStatus * @return List<VsdAlertHistory> * @throws VSDDataAccessException */ public List<VsdAlertHistory> getAlertHistoriesByStatus(DataServiceContext dsContext, boolean retrieveArabicData, String sendStatus) throws VSDDataAccessException { logger.info("getAlertHistoriesByStatus -- START"); List<VsdAlertHistory> alertHistories = null; try { Session session = (Session) dsContext.getInternalContext(); alertHistories = session.createCriteria(VsdAlertHistory.class, "ah") .add(Restrictions.eq("ah.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("ah.sendStatus", sendStatus).ignoreCase()) .createCriteria("ah.vsdVOCDetComCha", "vdcc", Criteria.LEFT_JOIN, Restrictions.eq("vdcc.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vdcc.vsdVehOwnConDetail", "vocd", Criteria.LEFT_JOIN, Restrictions.eq("vocd.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("ah.vsdAlertTemplateInfo", "ati", Criteria.LEFT_JOIN, Restrictions.eq("ati.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("ati.vsdSupportedLanguage", "sl", Criteria.LEFT_JOIN, Restrictions.eq("sl.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("ati.vsdComChannel", "cc", Criteria.LEFT_JOIN, Restrictions.eq("cc.isDeleted", IDataService.BOOL_FALSE)) .list(); logger.info("getAlertHistoriesByStatus -- END"); return alertHistories; } catch (Exception ex) { logger.error("An error occured in getAlertHistoriesByStatus()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } } }