Java tutorial
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.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.data.DataServiceContext; import com.rta.vsd.data.service.IChannelTestDataService; import com.rta.vsd.data.service.IDataService; import com.rta.vsd.dto.VsdChannelTest; import com.rta.vsd.dto.custom.CustomVehicleTestStatusDTO; import com.rta.vsd.dto.custom.PaginationParam; import com.rta.vsd.dto.custom.TestResult; import com.rta.vsd.dto.custom.TestResultSearchCriteria; import com.rta.vsd.dto.custom.TestResultSearchResult; import com.rta.vsd.dto.custom.VehiclePlate; import com.rta.vsd.exception.data.VSDDataAccessException; import com.rta.vsd.utility.Constant; import com.rta.vsd.utility.VSDLogger; public class ChannelTestDataServiceImpl extends DataServiceImpl implements IChannelTestDataService { private static Logger logger = VSDLogger.init(ChannelTestDataServiceImpl.class.getName()); private final int ANNUAL_TEST_TYPE_CODE = 2; public static enum SORT_COLUMN { CHANNEL_TEST_ID { public String toString() { return "CHANNEL_TEST_ID"; } }, TEST_TYPE_NAME { public String toString() { return "TEST_TYPE_NAME"; } }, TEST_TYPE_NAME_A { public String toString() { return "TEST_TYPE_NAME_A"; } }, TEST_STATUS { public String toString() { return "TEST_STATUS"; } }, CREATED_TIMESTAMP { public String toString() { return "CREATED_TIMESTAMP"; } }, CHANNEL_PART_INST_NAME { public String toString() { return "CHANNEL_PART_INST_NAME"; } }, CHANNEL_PART_INST_NAME_A { public String toString() { return "CHANNEL_PART_INST_NAME_A"; } }, VIOLATION_TICKET_CODE { public String toString() { return "VIOLATION_TICKET_CODE"; } }, VIOLATION_ID { public String toString() { return "VIOLATION_ID"; } }, OWNER_NAME { public String toString() { return "OWNER_NAME"; } }, OWNER_NAME_A { public String toString() { return "OWNER_NAME_A"; } }, VEHICLE_PLATE_CATEGORY { public String toString() { return "VEHICLE_PLATE_CATEGORY"; } }, VEHICLE_PLATE_CODE { public String toString() { return "VEHICLE_PLATE_CODE"; } }, VEHICLE_PLATE_NUMBER { public String toString() { return "VEHICLE_PLATE_NUMBER"; } }, VEHICLE_PLATE_SOURCE { public String toString() { return "VEHICLE_PLATE_SOURCE"; } } } public List<TestResultSearchResult> searchTestResult(DataServiceContext dsContext, boolean retrieveArabicData, TestResultSearchCriteria searchCriteria) throws VSDDataAccessException { logger.info("searchTestResult -- START"); /* SELECT distinct ct.CHANNEL_TEST_ID as channelTestId, tt.TEST_TYPE_NAME as testTypeNAME, ct.CREATED_TIMESTAMP createdTimeStamp, cpi.CHANNEL_PART_INST_NAME, v.VIOLATION_TICKET_CODE, oi.OWNER_NAME FROM VSD_CHANNEL_TEST ct, VSD_CHANNEL_PART_INST cpi, VSD_CHANNEL_DEFECT_TEST cdt , VSD_CHANNEL_DEFECT cd, VSD_VIOLATION v, VSD_INSPECTION i, VSD_VEHICLE_INFO vi, VSD_OWNER_INFO oi, VSD_TEST_TYPE tt where ct.CHANNEL_PART_INST_ID = cpi.CHANNEL_PART_INST_ID and ct.CHANNEL_TEST_ID = cdt.CHANNEL_TEST_ID and cdt.CHANNEL_DEFECT_ID = cd.CHANNEL_DEFECT_ID and cd.VIOLATION_ID = v.VIOLATION_ID and cd.VIOLATION_ID = i.VIOLATION_ID and i.VEHICLE_INFO_ID = vi.VEHICLE_INFO_ID and vi.VEHICLE_INFO_ID = oi.VEHICLE_INFO_ID and ct.TEST_TYPE_ID = tt.TEST_TYPE_ID and oi.OWNER_NAME = 'ELDON' and oi.TRADE_LICENSE_NUMBER = 'ABC' and oi.TRAFFIC_FILE_NUMBER = 'ABC' and vi.VEHICLE_PLATE_CATEGORY = '' and vi.VEHICLE_PLATE_CODE = '' and vi.VEHICLE_PLATE_NUMBER = '' and cpi.CHANNEL_PART_INST_NAME = '' and ct.TEST_TYPE_ID = 1 and ct.TEST_STATUS = 'PASS' */ List<TestResultSearchResult> results = null; try { Session session = (Session) dsContext.getInternalContext(); String defaultSchema = DataServiceImpl.DEFAULT_SCHEMA_NAME; StringBuffer query = new StringBuffer( "SELECT distinct ct.CHANNEL_TEST_ID as \"testId\", tt.TEST_TYPE_NAME as \"testType\", tt.TEST_TYPE_NAME_A as \"testTypeA\", ct.TEST_STATUS as \"testStatus\" , to_char(ct.CREATED_TIMESTAMP,'DD/MM/YYYY HH:MM') as \"testDateTime\", cpi.CHANNEL_PART_INST_NAME as \"testCenter\", cpi.CHANNEL_PART_INST_NAME_A as \"testCenterA\", v.VIOLATION_TICKET_CODE as \"violationTicketCode\", v.VIOLATION_ID as \"violationId\", oi.OWNER_NAME as \"operatorName\", oi.OWNER_NAME_A as \"operatorNameA\", vi.VEHICLE_PLATE_CATEGORY as \"plateCategory\", vi.VEHICLE_PLATE_CODE as \"plateCode\", vi.VEHICLE_PLATE_NUMBER as \"plateNumber\", " + " vi.VEHICLE_PLATE_SOURCE as \"emirateName\" " + " FROM " + defaultSchema + ".VSD_CHANNEL_TEST ct, " + defaultSchema + ".VSD_CHANNEL_PART_INST cpi, " + defaultSchema + ".VSD_CHANNEL_DEFECT_TEST cdt , " + defaultSchema + ".VSD_CHANNEL_DEFECT cd, " + defaultSchema + ".VSD_VIOLATION v, " + defaultSchema + ".VSD_INSPECTION i, " + defaultSchema + ".VSD_VEHICLE_INFO vi, " + defaultSchema + ".VSD_OWNER_INFO oi, " + defaultSchema + ".VSD_TEST_TYPE tt, " + defaultSchema + ".VSD_LOCATION l, " + defaultSchema + ".VSD_COUNTRY c " + " where ct.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " and ct.CHANNEL_PART_INST_ID = cpi.CHANNEL_PART_INST_ID and cpi.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " and ct.CHANNEL_TEST_ID = cdt.CHANNEL_TEST_ID and cdt.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " and cdt.CHANNEL_DEFECT_ID = cd.CHANNEL_DEFECT_ID and cd.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " and cd.VIOLATION_ID = v.VIOLATION_ID and v.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " and cd.VIOLATION_ID = i.VIOLATION_ID and i.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " and i.VEHICLE_INFO_ID = vi.VEHICLE_INFO_ID and vi.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " and vi.VEHICLE_INFO_ID = oi.VEHICLE_INFO_ID and oi.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " and ct.TEST_TYPE_ID = tt.TEST_TYPE_ID and tt.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " and i.LOCATION_ID = l.LOCATION_ID and l.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " and l.COUNTRY_ID = c.COUNTRY_ID and c.IS_DELETED = '" + IDataService.BOOL_FALSE + "' "); if (searchCriteria.getOperatorName() != null && !searchCriteria.getOperatorName().equals("")) { query.append(" and UPPER(oi.OWNER_NAME) like '%" + searchCriteria.getOperatorName().toUpperCase() + "%'"); } if (searchCriteria.getOperatorNameA() != null && !searchCriteria.getOperatorNameA().equals("")) { query.append(" and oi.OWNER_NAME_A like '%" + searchCriteria.getOperatorNameA() + "%'"); } if (searchCriteria.getTrafficFileNumber() != null && !searchCriteria.getTrafficFileNumber().equals("")) { query.append(" and oi.TRAFFIC_FILE_NUMBER = '" + searchCriteria.getTrafficFileNumber() + "'"); } if (searchCriteria.getTradeLicenseNumber() != null && !searchCriteria.getTradeLicenseNumber().equals("")) { query.append(" and oi.TRADE_LICENSE_NUMBER = '" + searchCriteria.getTradeLicenseNumber() + "'"); } if (searchCriteria.getPlateCategory() != null && !searchCriteria.getPlateCategory().equals("")) { query.append(" and UPPER(vi.VEHICLE_PLATE_CATEGORY) = (Select UPPER(vpcc.CATEGORY_NAME) from " + defaultSchema + ".VSD_VEH_PLATE_CAT_CODE vpcc where vpcc.VEH_PLATE_CAT_CODE_ID = " + searchCriteria.getPlateCategory() + " )"); } if (searchCriteria.getPlateCode() != null && !searchCriteria.getPlateCode().equals("")) { query.append(" and UPPER(vi.VEHICLE_PLATE_CODE) = (Select UPPER(vpcc.CATEGORY_NAME) from " + defaultSchema + ".VSD_VEH_PLATE_CAT_CODE vpcc where vpcc.VEH_PLATE_CAT_CODE_ID = " + searchCriteria.getPlateCode() + " )"); } if (searchCriteria.getPlateNumber() != null && !searchCriteria.getPlateNumber().equals("")) { query.append(" and UPPER(vi.VEHICLE_PLATE_NUMBER) = '" + searchCriteria.getPlateNumber().toUpperCase() + "'"); } if (searchCriteria.getTestCenterId() != null) { query.append(" and cpi.CHANNEL_PART_INST_ID = " + searchCriteria.getTestCenterId()); } if (searchCriteria.getTestTypeId() != null) { query.append(" and ct.TEST_TYPE_ID = " + searchCriteria.getTestTypeId()); } if (searchCriteria.getTestStatus() != null && !searchCriteria.getTestStatus().equals("")) { query.append(" and ct.TEST_STATUS = '" + searchCriteria.getTestStatus() + "'"); } if (searchCriteria.getStartDate() != null && searchCriteria.getEndDate() != null) { SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); query.append( " and TO_DATE(TO_CHAR(ct.CREATED_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.getEmirateId() != null) { query.append(" and l.COUNTRY_ID = " + searchCriteria.getEmirateId()); } if (searchCriteria.getCountryId() != null) { query.append(" and c.PARENT_COUNTRY_ID = " + searchCriteria.getCountryId()); } results = session.createSQLQuery(query.toString()).addScalar("testId", Hibernate.LONG) .addScalar("testType").addScalar("testTypeA").addScalar("testStatus") .addScalar("testDateTime", Hibernate.STRING).addScalar("testCenter").addScalar("testCenterA") .addScalar("violationTicketCode").addScalar("violationId", Hibernate.LONG) .addScalar("operatorName").addScalar("operatorNameA").addScalar("plateCategory") .addScalar("plateCode").addScalar("plateNumber").addScalar("emirateName") .setResultTransformer(new AliasToBeanResultTransformer(TestResultSearchResult.class)).list(); } catch (Exception ex) { logger.error("An error occured in searchTestResult()"); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("searchTestResult -- END"); return results; } public List<TestResultSearchResult> searchTestResult(DataServiceContext dsContext, boolean retrieveArabicData, TestResultSearchCriteria searchCriteria, PaginationParam paginationParam) throws VSDDataAccessException { logger.info("searchTestResult -- START"); /* SELECT distinct ct.CHANNEL_TEST_ID as channelTestId, tt.TEST7_TYPE_NAME as testTypeNAME, ct.CREATED_TIMESTAMP createdTimeStamp, cpi.CHANNEL_PART_INST_NAME, v.VIOLATION_TICKET_CODE, oi.OWNER_NAME FROM VSD_CHANNEL_TEST ct, VSD_CHANNEL_PART_INST cpi, VSD_CHANNEL_DEFECT_TEST cdt , VSD_CHANNEL_DEFECT cd, VSD_VIOLATION v, VSD_INSPECTION i, VSD_VEHICLE_INFO vi, VSD_OWNER_INFO oi, VSD_TEST_TYPE tt where ct.CHANNEL_PART_INST_ID = cpi.CHANNEL_PART_INST_ID and ct.CHANNEL_TEST_ID = cdt.CHANNEL_TEST_ID and cdt.CHANNEL_DEFECT_ID = cd.CHANNEL_DEFECT_ID and cd.VIOLATION_ID = v.VIOLATION_ID and cd.VIOLATION_ID = i.VIOLATION_ID and i.VEHICLE_INFO_ID = vi.VEHICLE_INFO_ID and vi.VEHICLE_INFO_ID = oi.VEHICLE_INFO_ID and ct.TEST_TYPE_ID = tt.TEST_TYPE_ID and oi.OWNER_NAME = 'ELDON' and oi.TRADE_LICENSE_NUMBER = 'ABC' and oi.TRAFFIC_FILE_NUMBER = 'ABC' and vi.VEHICLE_PLATE_CATEGORY = '' and vi.VEHICLE_PLATE_CODE = '' and vi.VEHICLE_PLATE_NUMBER = '' and cpi.CHANNEL_PART_INST_NAME = '' and ct.TEST_TYPE_ID = 1 and ct.TEST_STATUS = 'PASS' */ List<TestResultSearchResult> results = null; try { Session session = (Session) dsContext.getInternalContext(); String defaultSchema = DataServiceImpl.DEFAULT_SCHEMA_NAME; String columnAttrib = ""; StringBuffer query = new StringBuffer( "SELECT distinct ct.CHANNEL_TEST_ID as \"testId\", tt.TEST_TYPE_NAME as \"testType\", tt.TEST_TYPE_NAME_A as \"testTypeA\", ct.TEST_STATUS as \"testStatus\" , to_char(ct.CREATED_TIMESTAMP,'DD/MM/YYYY HH:MM') as \"testDateTime\", cpi.CHANNEL_PART_INST_NAME as \"testCenter\", cpi.CHANNEL_PART_INST_NAME_A as \"testCenterA\", v.VIOLATION_TICKET_CODE as \"violationTicketCode\", v.VIOLATION_ID as \"violationId\", oi.OWNER_NAME as \"operatorName\", oi.OWNER_NAME_A as \"operatorNameA\", vi.VEHICLE_PLATE_CATEGORY as \"plateCategory\", vi.VEHICLE_PLATE_CODE as \"plateCode\", vi.VEHICLE_PLATE_NUMBER as \"plateNumber\", " + " vi.VEHICLE_PLATE_SOURCE as \"emirateName\"" + " FROM " + defaultSchema + ".VSD_CHANNEL_TEST ct, " + defaultSchema + ".VSD_CHANNEL_PART_INST cpi, " + defaultSchema + ".VSD_CHANNEL_DEFECT_TEST cdt , " + defaultSchema + ".VSD_CHANNEL_DEFECT cd, " + defaultSchema + ".VSD_VIOLATION v, " + defaultSchema + ".VSD_INSPECTION i, " + defaultSchema + ".VSD_VEHICLE_INFO vi, " + defaultSchema + ".VSD_OWNER_INFO oi, " + defaultSchema + ".VSD_TEST_TYPE tt, " + defaultSchema + ".VSD_LOCATION l, " + defaultSchema + ".VSD_COUNTRY c " + " where ct.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " and ct.CHANNEL_PART_INST_ID = cpi.CHANNEL_PART_INST_ID and cpi.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " and ct.CHANNEL_TEST_ID = cdt.CHANNEL_TEST_ID and cdt.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " and cdt.CHANNEL_DEFECT_ID = cd.CHANNEL_DEFECT_ID and cd.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " and cd.VIOLATION_ID = v.VIOLATION_ID and v.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " and cd.VIOLATION_ID = i.VIOLATION_ID and i.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " and i.VEHICLE_INFO_ID = vi.VEHICLE_INFO_ID and vi.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " and vi.VEHICLE_INFO_ID = oi.VEHICLE_INFO_ID and oi.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " and ct.TEST_TYPE_ID = tt.TEST_TYPE_ID and tt.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " and i.LOCATION_ID = l.LOCATION_ID and l.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " and l.COUNTRY_ID = c.COUNTRY_ID and c.IS_DELETED = '" + IDataService.BOOL_FALSE + "' "); if (searchCriteria.getOperatorName() != null && !searchCriteria.getOperatorName().equals("")) { query.append(" and UPPER(oi.OWNER_NAME) like '%" + searchCriteria.getOperatorName().replaceAll("'", "''").toUpperCase() + "%'"); } if (searchCriteria.getOperatorNameA() != null && !searchCriteria.getOperatorNameA().equals("")) { query.append(" and oi.OWNER_NAME_A like '%" + searchCriteria.getOperatorNameA().replaceAll("'", "''") + "%'"); } if (searchCriteria.getTrafficFileNumber() != null && !searchCriteria.getTrafficFileNumber().equals("")) { query.append(" and oi.TRAFFIC_FILE_NUMBER = '" + searchCriteria.getTrafficFileNumber().replaceAll("'", "''") + "'"); } if (searchCriteria.getTradeLicenseNumber() != null && !searchCriteria.getTradeLicenseNumber().equals("")) { query.append(" and oi.TRADE_LICENSE_NUMBER = '" + searchCriteria.getTradeLicenseNumber().replaceAll("'", "''") + "'"); } if (searchCriteria.getPlateCategory() != null && !searchCriteria.getPlateCategory().equals("")) { query.append(" and UPPER(vi.VEHICLE_PLATE_CATEGORY) = (Select UPPER(vpcc.CATEGORY_NAME) from " + defaultSchema + ".VSD_VEH_PLATE_CAT_CODE vpcc where vpcc.VEH_PLATE_CAT_CODE_ID = " + searchCriteria.getPlateCategory() + " )"); } if (searchCriteria.getPlateCode() != null && !searchCriteria.getPlateCode().equals("")) { query.append(" and UPPER(vi.VEHICLE_PLATE_CODE) = (Select UPPER(vpcc.CATEGORY_NAME) from " + defaultSchema + ".VSD_VEH_PLATE_CAT_CODE vpcc where vpcc.VEH_PLATE_CAT_CODE_ID = " + searchCriteria.getPlateCode() + " )"); } if (searchCriteria.getPlateNumber() != null && !searchCriteria.getPlateNumber().equals("")) { query.append(" and UPPER(vi.VEHICLE_PLATE_NUMBER) = '" + searchCriteria.getPlateNumber().replaceAll("'", "''").toUpperCase() + "'"); } if (searchCriteria.getTestCenterId() != null) { query.append(" and cpi.CHANNEL_PART_INST_ID = " + searchCriteria.getTestCenterId()); } if (searchCriteria.getTestTypeId() != null) { query.append(" and ct.TEST_TYPE_ID = " + searchCriteria.getTestTypeId()); } if (searchCriteria.getTestStatus() != null && !searchCriteria.getTestStatus().equals("")) { query.append(" and ct.TEST_STATUS = '" + searchCriteria.getTestStatus() + "'"); } if (searchCriteria.getStartDate() != null && searchCriteria.getEndDate() != null) { SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); query.append( " and TO_DATE(TO_CHAR(ct.CREATED_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.getEmirateId() != null) { query.append(" and l.COUNTRY_ID = " + searchCriteria.getEmirateId()); } if (searchCriteria.getCountryId() != null) { query.append(" and c.PARENT_COUNTRY_ID = " + searchCriteria.getCountryId()); } if (paginationParam.getOrderBy() != null && !paginationParam.getOrderBy().equals("")) { if (paginationParam.getSortOrder() != null && !paginationParam.getSortOrder().equals("")) { if (paginationParam.getOrderBy().equalsIgnoreCase(SORT_COLUMN.CHANNEL_TEST_ID.toString())) { columnAttrib = "ct." + SORT_COLUMN.CHANNEL_TEST_ID; } else if (paginationParam.getOrderBy() .equalsIgnoreCase(SORT_COLUMN.TEST_TYPE_NAME.toString())) { columnAttrib = "tt." + SORT_COLUMN.TEST_TYPE_NAME; } else if (paginationParam.getOrderBy() .equalsIgnoreCase(SORT_COLUMN.TEST_TYPE_NAME_A.toString())) { columnAttrib = "tt." + SORT_COLUMN.TEST_TYPE_NAME_A; } else if (paginationParam.getOrderBy().equalsIgnoreCase(SORT_COLUMN.TEST_STATUS.toString())) { columnAttrib = "ct." + SORT_COLUMN.TEST_STATUS; } else if (paginationParam.getOrderBy() .equalsIgnoreCase(SORT_COLUMN.CREATED_TIMESTAMP.toString())) { columnAttrib = " to_char(ct." + SORT_COLUMN.CREATED_TIMESTAMP + ",'DD/MM/YYYY HH:MM') "; } else if (paginationParam.getOrderBy() .equalsIgnoreCase(SORT_COLUMN.CHANNEL_PART_INST_NAME.toString())) { columnAttrib = "cpi." + SORT_COLUMN.CHANNEL_PART_INST_NAME; } else if (paginationParam.getOrderBy() .equalsIgnoreCase(SORT_COLUMN.CHANNEL_PART_INST_NAME_A.toString())) { columnAttrib = "cpi." + SORT_COLUMN.CHANNEL_PART_INST_NAME_A; } else if (paginationParam.getOrderBy() .equalsIgnoreCase(SORT_COLUMN.VIOLATION_TICKET_CODE.toString())) { columnAttrib = "v." + SORT_COLUMN.VIOLATION_TICKET_CODE; } else if (paginationParam.getOrderBy().equalsIgnoreCase(SORT_COLUMN.VIOLATION_ID.toString())) { columnAttrib = "v." + SORT_COLUMN.VIOLATION_ID; } else if (paginationParam.getOrderBy().equalsIgnoreCase(SORT_COLUMN.OWNER_NAME.toString())) { columnAttrib = "oi." + SORT_COLUMN.OWNER_NAME; } else if (paginationParam.getOrderBy().equalsIgnoreCase(SORT_COLUMN.OWNER_NAME_A.toString())) { columnAttrib = "oi." + SORT_COLUMN.OWNER_NAME_A; } else if (paginationParam.getOrderBy() .equalsIgnoreCase(SORT_COLUMN.VEHICLE_PLATE_CATEGORY.toString())) { columnAttrib = "vi." + SORT_COLUMN.VEHICLE_PLATE_CATEGORY; } else if (paginationParam.getOrderBy() .equalsIgnoreCase(SORT_COLUMN.VEHICLE_PLATE_CODE.toString())) { columnAttrib = "vi." + SORT_COLUMN.VEHICLE_PLATE_CODE; } else if (paginationParam.getOrderBy() .equalsIgnoreCase(SORT_COLUMN.VEHICLE_PLATE_NUMBER.toString())) { columnAttrib = "vi." + SORT_COLUMN.VEHICLE_PLATE_NUMBER; } else if (paginationParam.getOrderBy() .equalsIgnoreCase(SORT_COLUMN.VEHICLE_PLATE_SOURCE.toString())) { columnAttrib = "vi." + SORT_COLUMN.VEHICLE_PLATE_SOURCE; } } } else { columnAttrib = " to_char(ct." + SORT_COLUMN.CREATED_TIMESTAMP + ",'DD/MM/YYYY HH:MM') "; } if (paginationParam.getSortOrder().equalsIgnoreCase(IDataService.Sort.DESCENDING.toString())) { query.append(" ORDER BY UPPER(" + columnAttrib + ") DESC"); } else { query.append(" ORDER BY UPPER(" + columnAttrib + ") ASC"); } results = session.createSQLQuery(query.toString()).addScalar("testId", Hibernate.LONG) .addScalar("testType").addScalar("testTypeA").addScalar("testStatus") .addScalar("testDateTime", Hibernate.STRING).addScalar("testCenter").addScalar("testCenterA") .addScalar("violationTicketCode").addScalar("violationId", Hibernate.LONG) .addScalar("operatorName").addScalar("operatorNameA").addScalar("plateCategory") .addScalar("plateCode").addScalar("plateNumber").addScalar("emirateName") .setFirstResult(paginationParam.getFirstResult().intValue()) .setMaxResults(paginationParam.getFetchedSize().intValue()) .setResultTransformer(new AliasToBeanResultTransformer(TestResultSearchResult.class)).list(); } catch (Exception ex) { logger.error("An error occured in searchTestResult()"); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("searchTestResult -- END"); return results; } public Long getCountForSearchTestResult(DataServiceContext dsContext, boolean retrieveArabicData, TestResultSearchCriteria searchCriteria) throws VSDDataAccessException { logger.info("getCountForSearchTestResult -- START"); /* SELECT distinct ct.CHANNEL_TEST_ID as channelTestId, tt.TEST_TYPE_NAME as testTypeNAME, ct.CREATED_TIMESTAMP createdTimeStamp, cpi.CHANNEL_PART_INST_NAME, v.VIOLATION_TICKET_CODE, oi.OWNER_NAME FROM VSD_CHANNEL_TEST ct, VSD_CHANNEL_PART_INST cpi, VSD_CHANNEL_DEFECT_TEST cdt , VSD_CHANNEL_DEFECT cd, VSD_VIOLATION v, VSD_INSPECTION i, VSD_VEHICLE_INFO vi, VSD_OWNER_INFO oi, VSD_TEST_TYPE tt where ct.CHANNEL_PART_INST_ID = cpi.CHANNEL_PART_INST_ID and ct.CHANNEL_TEST_ID = cdt.CHANNEL_TEST_ID and cdt.CHANNEL_DEFECT_ID = cd.CHANNEL_DEFECT_ID and cd.VIOLATION_ID = v.VIOLATION_ID and cd.VIOLATION_ID = i.VIOLATION_ID and i.VEHICLE_INFO_ID = vi.VEHICLE_INFO_ID and vi.VEHICLE_INFO_ID = oi.VEHICLE_INFO_ID and ct.TEST_TYPE_ID = tt.TEST_TYPE_ID and oi.OWNER_NAME = 'ELDON' and oi.TRADE_LICENSE_NUMBER = 'ABC' and oi.TRAFFIC_FILE_NUMBER = 'ABC' and vi.VEHICLE_PLATE_CATEGORY = '' and vi.VEHICLE_PLATE_CODE = '' and vi.VEHICLE_PLATE_NUMBER = '' and cpi.CHANNEL_PART_INST_NAME = '' and ct.TEST_TYPE_ID = 1 and ct.TEST_STATUS = 'PASS' */ Long resultSize = null; try { Session session = (Session) dsContext.getInternalContext(); String defaultSchema = DataServiceImpl.DEFAULT_SCHEMA_NAME; StringBuffer query = new StringBuffer( "SELECT count(*) from (SELECT distinct ct.CHANNEL_TEST_ID as \"testId\", tt.TEST_TYPE_NAME as \"testType\", tt.TEST_TYPE_NAME_A as \"testTypeA\", ct.TEST_STATUS as \"testStatus\" , to_char(ct.CREATED_TIMESTAMP,'DD MM YYYY') as \"testDateTime\", cpi.CHANNEL_PART_INST_NAME as \"testCenter\", cpi.CHANNEL_PART_INST_NAME_A as \"testCenterA\", v.VIOLATION_TICKET_CODE as \"violationTicketCode\", v.VIOLATION_ID as \"violationId\", oi.OWNER_NAME as \"operatorName\", oi.OWNER_NAME_A as \"operatorNameA\", vi.VEHICLE_PLATE_CATEGORY as \"plateCategory\", vi.VEHICLE_PLATE_CODE as \"plateCode\", vi.VEHICLE_PLATE_NUMBER as \"plateNumber\", " + " vi.VEHICLE_PLATE_SOURCE as \"emirateName\"" + " FROM " + defaultSchema + ".VSD_CHANNEL_TEST ct, " + defaultSchema + ".VSD_CHANNEL_PART_INST cpi, " + defaultSchema + ".VSD_CHANNEL_DEFECT_TEST cdt , " + defaultSchema + ".VSD_CHANNEL_DEFECT cd, " + defaultSchema + ".VSD_VIOLATION v, " + defaultSchema + ".VSD_INSPECTION i, " + defaultSchema + ".VSD_VEHICLE_INFO vi, " + defaultSchema + ".VSD_OWNER_INFO oi, " + defaultSchema + ".VSD_TEST_TYPE tt, " + defaultSchema + ".VSD_LOCATION l, " + defaultSchema + ".VSD_COUNTRY c " + " where ct.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " and ct.CHANNEL_PART_INST_ID = cpi.CHANNEL_PART_INST_ID and cpi.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " and ct.CHANNEL_TEST_ID = cdt.CHANNEL_TEST_ID and cdt.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " and cdt.CHANNEL_DEFECT_ID = cd.CHANNEL_DEFECT_ID and cd.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " and cd.VIOLATION_ID = v.VIOLATION_ID and v.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " and cd.VIOLATION_ID = i.VIOLATION_ID and i.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " and i.VEHICLE_INFO_ID = vi.VEHICLE_INFO_ID and vi.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " and vi.VEHICLE_INFO_ID = oi.VEHICLE_INFO_ID and oi.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " and ct.TEST_TYPE_ID = tt.TEST_TYPE_ID and tt.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " and i.LOCATION_ID = l.LOCATION_ID and l.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " and l.COUNTRY_ID = c.COUNTRY_ID and c.IS_DELETED = '" + IDataService.BOOL_FALSE + "' "); if (searchCriteria.getOperatorName() != null && !searchCriteria.getOperatorName().equals("")) { query.append(" and UPPER(oi.OWNER_NAME) like '%" + searchCriteria.getOperatorName().replaceAll("'", "''").toUpperCase() + "%'"); } if (searchCriteria.getOperatorNameA() != null && !searchCriteria.getOperatorNameA().equals("")) { query.append(" and oi.OWNER_NAME_A like '%" + searchCriteria.getOperatorNameA().replaceAll("'", "''") + "%'"); } if (searchCriteria.getTrafficFileNumber() != null && !searchCriteria.getTrafficFileNumber().equals("")) { query.append(" and oi.TRAFFIC_FILE_NUMBER = '" + searchCriteria.getTrafficFileNumber().replaceAll("'", "''") + "'"); } if (searchCriteria.getTradeLicenseNumber() != null && !searchCriteria.getTradeLicenseNumber().equals("")) { query.append(" and oi.TRADE_LICENSE_NUMBER = '" + searchCriteria.getTradeLicenseNumber().replaceAll("'", "''") + "'"); } if (searchCriteria.getPlateCategory() != null && !searchCriteria.getPlateCategory().equals("")) { query.append(" and UPPER(vi.VEHICLE_PLATE_CATEGORY) = (Select UPPER(vpcc.CATEGORY_NAME) from " + defaultSchema + ".VSD_VEH_PLATE_CAT_CODE vpcc where vpcc.VEH_PLATE_CAT_CODE_ID = " + searchCriteria.getPlateCategory() + " )"); } if (searchCriteria.getPlateCode() != null && !searchCriteria.getPlateCode().equals("")) { query.append(" and UPPER(vi.VEHICLE_PLATE_CODE) = (Select UPPER(vpcc.CATEGORY_NAME) from " + defaultSchema + ".VSD_VEH_PLATE_CAT_CODE vpcc where vpcc.VEH_PLATE_CAT_CODE_ID = " + searchCriteria.getPlateCode() + " )"); } if (searchCriteria.getPlateNumber() != null && !searchCriteria.getPlateNumber().equals("")) { query.append(" and UPPER(vi.VEHICLE_PLATE_NUMBER) = '" + searchCriteria.getPlateNumber().replaceAll("'", "''").toUpperCase() + "'"); } if (searchCriteria.getTestCenterId() != null) { query.append(" and cpi.CHANNEL_PART_INST_ID = " + searchCriteria.getTestCenterId()); } if (searchCriteria.getTestTypeId() != null) { query.append(" and ct.TEST_TYPE_ID = " + searchCriteria.getTestTypeId()); } if (searchCriteria.getTestStatus() != null && !searchCriteria.getTestStatus().equals("")) { query.append(" and ct.TEST_STATUS = '" + searchCriteria.getTestStatus() + "'"); } if (searchCriteria.getStartDate() != null && searchCriteria.getEndDate() != null) { SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); query.append( " and TO_DATE(TO_CHAR(ct.CREATED_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.getEmirateId() != null) { query.append(" and l.COUNTRY_ID = " + searchCriteria.getEmirateId()); } if (searchCriteria.getCountryId() != null) { query.append(" and c.PARENT_COUNTRY_ID = " + searchCriteria.getCountryId()); } query.append(" ) "); BigDecimal bigDecimalCount = (BigDecimal) session.createSQLQuery(query.toString()).uniqueResult(); resultSize = bigDecimalCount.longValue(); } catch (Exception ex) { logger.error("An error occured in getCountForSearchTestResult()"); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getCountForSearchTestResult -- END"); return resultSize; } // private List<TestResult> getAnnualVehicleTestResultsByPlateDetails(DataServiceContext dsContext, boolean retrieveArabicData, VehiclePlate plateDetails) throws VSDDataAccessException { // logger.info("getAnnualVehicleTestResultsByPlateDetails -- START"); // List<TestResult> testResults = null; // try { // // /*SELECT // CT.CREATED_TIMESTAMP AS "resultDateTime", // CPI.CHANNEL_PART_INST_NAME AS "testBranch", // CPI.CHANNEL_PART_INST_NAME_A AS "testBranchA", // P.PARTNER_NAME AS "testCenter", // P.PARTNER_NAME_A AS "testCenterA", // CT.TEST_STATUS AS "testResult", // TT.TEST_TYPE_NAME AS "testType", // TT.TEST_TYPE_NAME_A AS "testTypeA", // CT.TEST_STATUS_A AS "testResultA" // FROM // VSD_VEHICLE_INFO V, // VSD_CHANNEL_TEST CT, // VSD_TEST_TYPE TT, // VSD_CHANNEL_PART_INST CPI, // VSD_CHANNEL_PARTNER CP, // VSD_PARTNER P // WHERE // V.IS_DELETED = 'F' // AND V.VEHICLE_INFO_ID = CT.VEHICLE_INFO_ID // AND CT.IS_DELETED = 'F' // AND CT.TEST_TYPE_ID = TT.TEST_TYPE_ID // AND TT.IS_DELETED = 'F' // AND CT.CHANNEL_PART_INST_ID = CPI.CHANNEL_PART_INST_ID // AND CPI.IS_DELETED = 'F' // AND CPI.CHANNEL_PARTNER_ID = CP.CHANNEL_PARTNER_ID // AND CP.IS_DELETED = 'F' // AND CP.PARTNER_ID = P.PARTNER_ID // AND P.IS_DELETED = 'F' // AND TT.TEST_TYPE_CODE = 2*/ // // // Session session = (Session) dsContext.getInternalContext(); // String defaultSchema = DataServiceImpl.DEFAULT_SCHEMA_NAME; // StringBuffer query = new StringBuffer("SELECT CT.CREATED_TIMESTAMP AS \"resultDateTime\", CPI.CHANNEL_PART_INST_NAME AS \"testBranch\", CPI.CHANNEL_PART_INST_NAME_A AS \"testBranchA\", P.PARTNER_NAME AS \"testCenter\", P.PARTNER_NAME_A AS \"testCenterA\", CT.TEST_STATUS AS \"testResult\", TT.TEST_TYPE_NAME AS \"testType\", TT.TEST_TYPE_NAME_A AS \"testTypeA\", CT.TEST_STATUS_A AS \"testResultA\" " // + " FROM "+defaultSchema+".VSD_VEHICLE_INFO V, "+defaultSchema+".VSD_CHANNEL_TEST CT, "+defaultSchema+".VSD_TEST_TYPE TT, "+defaultSchema+".VSD_CHANNEL_PART_INST CPI, "+defaultSchema+".VSD_CHANNEL_PARTNER CP, "+defaultSchema+".VSD_PARTNER P " // + " WHERE V.IS_DELETED = '"+IDataService.BOOL_FALSE+"' " // + " AND V.VEHICLE_INFO_ID = CT.VEHICLE_INFO_ID " // + " AND CT.IS_DELETED = '"+IDataService.BOOL_FALSE+"' " // + " AND CT.TEST_TYPE_ID = TT.TEST_TYPE_ID " // + " AND TT.IS_DELETED = '"+IDataService.BOOL_FALSE+"' " // + " AND CT.CHANNEL_PART_INST_ID = CPI.CHANNEL_PART_INST_ID " // + " AND CPI.IS_DELETED = '"+IDataService.BOOL_FALSE+"' " // + " AND CPI.CHANNEL_PARTNER_ID = CP.CHANNEL_PARTNER_ID " // + " AND CP.IS_DELETED = '"+IDataService.BOOL_FALSE+"' " // + " AND CP.PARTNER_ID = P.PARTNER_ID " // + " AND P.IS_DELETED = '"+IDataService.BOOL_FALSE+"' " // + " AND TT.TEST_TYPE_CODE = 2 " // ); // // if(plateDetails.getPlateCategory() != null && !plateDetails.getPlateCategory().equals("")) { // query.append(" AND LOWER(V.VEHICLE_PLATE_CATEGORY) = '"+plateDetails.getPlateCategory().toLowerCase()+"'"); // } // if(plateDetails.getPlateCode() != null && !plateDetails.getPlateCode().equals("")) { // query.append(" AND LOWER(V.VEHICLE_PLATE_CODE) = '"+plateDetails.getPlateCode().toLowerCase()+"'"); // } // if(plateDetails.getPlateNumber() != null && !plateDetails.getPlateNumber().equals("")) { // query.append(" AND LOWER(V.VEHICLE_PLATE_NUMBER) = '"+plateDetails.getPlateNumber().toLowerCase()+"'"); // } // if(plateDetails.getPlateSource() != null && !plateDetails.getPlateSource().equals("")) { // query.append(" AND LOWER(V.VEHICLE_PLATE_SOURCE) = '"+plateDetails.getPlateSource().toLowerCase()+"'"); // } // query.append(" ORDER BY CT.CREATED_TIMESTAMP DESC "); // testResults = session.createSQLQuery(query.toString()).setResultTransformer(new AliasToBeanResultTransformer(TestResult.class)).list(); // // }catch(Exception ex) { // logger.error("An error occured in getAnnualVehicleTestResultsByPlateDetails()"); // throw new VSDDataAccessException(ex.getMessage(),ex); // } // logger.info("getAnnualVehicleTestResultsByPlateDetails -- END"); // return testResults; // } /** * * Gets a list of Test Results with their testing center and testing information by the vehicles plate details provided * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param plateDetails * @return List<TestResult> * @throws VSDDataAccessException */ public List<TestResult> getVehicleTestResultsByPlateDetails(DataServiceContext dsContext, boolean retrieveArabicData, VehiclePlate plateDetails) throws VSDDataAccessException { logger.info("getVehicleTestResultsByPlateDetails -- START"); List<TestResult> testResults = null; try { /*SELECT D.VIOLATION_TICKET_CODE VIOLATION_TICKET_CODE, D.REPORTED_DATE REPORTED_DATE, H.CHANNEL_PART_INST_NAME CHANNEL_PART_INST_NAME, K.PARTNER_NAME PARTNER_NAME, G.TEST_STATUS TEST_STATUS, I.TEST_TYPE_NAME TEST_TYPE_NAME FROM VSD_VEHICLE_INFO B, VSD_INSPECTION C, VSD_VIOLATION D, VSD_CHANNEL_DEFECT E, VSD_CHANNEL_DEFECT_TEST F, VSD_CHANNEL_TEST G, VSD_CHANNEL_PART_INST H, VSD_TEST_TYPE I, VSD_CHANNEL_PARTNER J, VSD_PARTNER K WHERE B.VEHICLE_INFO_ID = C.VEHICLE_INFO_ID AND C.VIOLATION_ID = D.VIOLATION_ID AND C.VIOLATION_ID = E.VIOLATION_ID AND E.CHANNEL_DEFECT_ID = F.CHANNEL_DEFECT_ID AND F.CHANNEL_TEST_ID = G.CHANNEL_TEST_ID AND E.CHANNEL_PART_INST_ID = H.CHANNEL_PART_INST_ID AND G.TEST_TYPE_ID = I.TEST_TYPE_ID AND H.CHANNEL_PARTNER_ID = J.CHANNEL_PARTNER_ID AND J.PARTNER_ID = K.PARTNER_ID ORDER BY G.CREATED_TIMESTAMP DESC*/ Session session = (Session) dsContext.getInternalContext(); String defaultSchema = DataServiceImpl.DEFAULT_SCHEMA_NAME; // StringBuffer query = new StringBuffer("SELECT D.VIOLATION_TICKET_CODE as \"violationId\", G.CREATED_TIMESTAMP as \"resultDateTime\", H.CHANNEL_PART_INST_NAME as \"testBranch\", H.CHANNEL_PART_INST_NAME_A as \"testBranchA\", K.PARTNER_NAME as \"testCenter\", K.PARTNER_NAME_A as \"testCenterA\"," // + " G.TEST_STATUS as \"testResult\", I.TEST_TYPE_NAME as \"testType\", I.TEST_TYPE_NAME_A as \"testTypeA\", G.TEST_STATUS_A as \"testResultA\"" // + " FROM "+defaultSchema+".VSD_VEHICLE_INFO B, "+defaultSchema+".VSD_INSPECTION C, "+defaultSchema+".VSD_VIOLATION D, "+defaultSchema+".VSD_VIOLATION_TEST_FEE E, "+defaultSchema+".VSD_TEST_FEE F, "+defaultSchema+".VSD_CHANNEL_TEST G, " // + " "+defaultSchema+".VSD_CHANNEL_PART_INST H, "+defaultSchema+".VSD_TEST_TYPE I, "+defaultSchema+".VSD_CHANNEL_PARTNER J, "+defaultSchema+".VSD_PARTNER K " // + " WHERE " // + " B.VEHICLE_INFO_ID = C.VEHICLE_INFO_ID " // + " AND C.VIOLATION_ID = D.VIOLATION_ID " // + " AND D.VIOLATION_ID = E.VIOLATION_ID " // + " AND E.TEST_FEE_ID = F.TEST_FEE_ID " // + " AND F.TEST_FEE_ID = G.TEST_FEE_ID " // + " AND G.TEST_TYPE_ID = I.TEST_TYPE_ID " // + " AND G.CHANNEL_PART_INST_ID = H.CHANNEL_PART_INST_ID " // + " AND H.CHANNEL_PARTNER_ID = J.CHANNEL_PARTNER_ID " // + " AND J.PARTNER_ID = K.PARTNER_ID " // + " AND B.IS_DELETED = '"+IDataService.BOOL_FALSE+"' " // + " AND C.IS_DELETED = '"+IDataService.BOOL_FALSE+"' " // + " AND D.IS_DELETED = '"+IDataService.BOOL_FALSE+"' " // + " AND E.IS_DELETED = '"+IDataService.BOOL_FALSE+"' " // + " AND F.IS_DELETED = '"+IDataService.BOOL_FALSE+"' " // + " AND G.IS_DELETED = '"+IDataService.BOOL_FALSE+"' " // + " AND H.IS_DELETED = '"+IDataService.BOOL_FALSE+"' " // + " AND I.IS_DELETED = '"+IDataService.BOOL_FALSE+"' " // + " AND J.IS_DELETED = '"+IDataService.BOOL_FALSE+"' " // + " AND K.IS_DELETED = '"+IDataService.BOOL_FALSE+"' " // ); // // if(plateDetails.getPlateCategory() != null && !plateDetails.getPlateCategory().equals("")) { // query.append(" AND LOWER(B.VEHICLE_PLATE_CATEGORY) = '"+plateDetails.getPlateCategory().toLowerCase()+"'"); // } // if(plateDetails.getPlateCode() != null && !plateDetails.getPlateCode().equals("")) { // query.append(" AND LOWER(B.VEHICLE_PLATE_CODE) = '"+plateDetails.getPlateCode().toLowerCase()+"'"); // } // if(plateDetails.getPlateNumber() != null && !plateDetails.getPlateNumber().equals("")) { // query.append(" AND LOWER(B.VEHICLE_PLATE_NUMBER) = '"+plateDetails.getPlateNumber().toLowerCase()+"'"); // } // if(plateDetails.getPlateSource() != null && !plateDetails.getPlateSource().equals("")) { // query.append(" AND LOWER(B.VEHICLE_PLATE_SOURCE) = '"+plateDetails.getPlateSource().toLowerCase()+"'"); // } // query.append(" ORDER BY G.CREATED_TIMESTAMP DESC "); StringBuffer query = new StringBuffer( "SELECT * FROM (SELECT D.VIOLATION_TICKET_CODE as \"violationId\", G.CREATED_TIMESTAMP as \"resultDateTime\", H.CHANNEL_PART_INST_NAME as \"testBranch\", H.CHANNEL_PART_INST_NAME_A as \"testBranchA\", K.PARTNER_NAME as \"testCenter\", K.PARTNER_NAME_A as \"testCenterA\", G.TEST_STATUS as \"testResult\", I.TEST_TYPE_NAME as \"testType\", I.TEST_TYPE_NAME_A as \"testTypeA\", G.TEST_STATUS_A as \"testResultA\" " + " FROM " + defaultSchema + ".VSD_VEHICLE_INFO B, " + defaultSchema + ".VSD_INSPECTION C, " + defaultSchema + ".VSD_VIOLATION D, " + defaultSchema + ".VSD_VIOLATION_TEST_FEE E, " + defaultSchema + ".VSD_TEST_FEE F, " + defaultSchema + ".VSD_CHANNEL_TEST G, " + defaultSchema + ".VSD_CHANNEL_PART_INST H, " + defaultSchema + ".VSD_TEST_TYPE I, " + defaultSchema + ".VSD_CHANNEL_PARTNER J, " + defaultSchema + ".VSD_PARTNER K " + " WHERE B.VEHICLE_INFO_ID = C.VEHICLE_INFO_ID " + " AND C.VIOLATION_ID = D.VIOLATION_ID " + " AND D.VIOLATION_ID = E.VIOLATION_ID " + " AND E.TEST_FEE_ID = F.TEST_FEE_ID " + " AND F.TEST_FEE_ID = G.TEST_FEE_ID " + " AND G.TEST_TYPE_ID = I.TEST_TYPE_ID " + " AND G.CHANNEL_PART_INST_ID = H.CHANNEL_PART_INST_ID " + " AND H.CHANNEL_PARTNER_ID = J.CHANNEL_PARTNER_ID " + " AND J.PARTNER_ID = K.PARTNER_ID " + " AND B.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND C.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND D.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND E.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND F.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND G.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND H.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND I.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND J.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND K.IS_DELETED = '" + IDataService.BOOL_FALSE + "' "); if (plateDetails.getPlateCategory() != null && !plateDetails.getPlateCategory().equals("")) { query.append(" AND LOWER(B.VEHICLE_PLATE_CATEGORY) = '" + plateDetails.getPlateCategory().toLowerCase() + "'"); } if (plateDetails.getPlateCode() != null && !plateDetails.getPlateCode().equals("")) { query.append( " AND LOWER(B.VEHICLE_PLATE_CODE) = '" + plateDetails.getPlateCode().toLowerCase() + "'"); } if (plateDetails.getPlateNumber() != null && !plateDetails.getPlateNumber().equals("")) { query.append(" AND LOWER(B.VEHICLE_PLATE_NUMBER) = '" + plateDetails.getPlateNumber().toLowerCase() + "'"); } if (plateDetails.getPlateSource() != null && !plateDetails.getPlateSource().equals("")) { query.append(" AND LOWER(B.VEHICLE_PLATE_SOURCE) = '" + plateDetails.getPlateSource().toLowerCase() + "'"); } query.append( " UNION ALL SELECT null as \"violationId\", CT.CREATED_TIMESTAMP AS \"resultDateTime\", CPI.CHANNEL_PART_INST_NAME AS \"testBranch\", CPI.CHANNEL_PART_INST_NAME_A AS \"testBranchA\", P.PARTNER_NAME AS \"testCenter\", P.PARTNER_NAME_A AS \"testCenterA\", CT.TEST_STATUS AS \"testResult\", TT.TEST_TYPE_NAME AS \"testType\", TT.TEST_TYPE_NAME_A AS \"testTypeA\", CT.TEST_STATUS_A AS \"testResultA\" " + " FROM " + defaultSchema + ".VSD_VEHICLE_INFO V, " + defaultSchema + ".VSD_CHANNEL_TEST CT, " + defaultSchema + ".VSD_TEST_TYPE TT, " + defaultSchema + ".VSD_CHANNEL_PART_INST CPI, " + defaultSchema + ".VSD_CHANNEL_PARTNER CP, " + defaultSchema + ".VSD_PARTNER P " + " WHERE V.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND V.VEHICLE_INFO_ID = CT.VEHICLE_INFO_ID " + " AND CT.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND CT.TEST_TYPE_ID = TT.TEST_TYPE_ID " + " AND TT.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND CT.CHANNEL_PART_INST_ID = CPI.CHANNEL_PART_INST_ID " + " AND CPI.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND CPI.CHANNEL_PARTNER_ID = CP.CHANNEL_PARTNER_ID " + " AND CP.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND CP.PARTNER_ID = P.PARTNER_ID " + " AND P.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND TT.TEST_TYPE_CODE = " + ANNUAL_TEST_TYPE_CODE); if (plateDetails.getPlateCategory() != null && !plateDetails.getPlateCategory().equals("")) { query.append(" AND LOWER(V.VEHICLE_PLATE_CATEGORY) = '" + plateDetails.getPlateCategory().toLowerCase() + "'"); } if (plateDetails.getPlateCode() != null && !plateDetails.getPlateCode().equals("")) { query.append( " AND LOWER(V.VEHICLE_PLATE_CODE) = '" + plateDetails.getPlateCode().toLowerCase() + "'"); } if (plateDetails.getPlateNumber() != null && !plateDetails.getPlateNumber().equals("")) { query.append(" AND LOWER(V.VEHICLE_PLATE_NUMBER) = '" + plateDetails.getPlateNumber().toLowerCase() + "'"); } if (plateDetails.getPlateSource() != null && !plateDetails.getPlateSource().equals("")) { query.append(" AND LOWER(V.VEHICLE_PLATE_SOURCE) = '" + plateDetails.getPlateSource().toLowerCase() + "'"); } query.append(" ) ORDER BY \"resultDateTime\" DESC"); testResults = session.createSQLQuery(query.toString()) .setResultTransformer(new AliasToBeanResultTransformer(TestResult.class)).list(); } catch (Exception ex) { logger.error("An error occured in getVehicleTestResultsByPlateDetails()"); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getVehicleTestResultsByPlateDetails -- END"); return testResults; } /** * * Gets a list of Test Results with their testing center and testing information by the vehicles plate details provided * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param plateDetails * @param paginationValues * @return List<TestResult> * @throws VSDDataAccessException */ public List<TestResult> getVehicleTestResultsByPlateDetails(DataServiceContext dsContext, boolean retrieveArabicData, VehiclePlate plateDetails, PaginationParam paginationValues) throws VSDDataAccessException { logger.info("getVehicleTestResultsByPlateDetails -- START"); List<TestResult> testResults = null; try { /*SELECT D.VIOLATION_TICKET_CODE VIOLATION_TICKET_CODE, D.REPORTED_DATE REPORTED_DATE, H.CHANNEL_PART_INST_NAME CHANNEL_PART_INST_NAME, K.PARTNER_NAME PARTNER_NAME, G.TEST_STATUS TEST_STATUS, I.TEST_TYPE_NAME TEST_TYPE_NAME FROM VSD_VEHICLE_INFO B, VSD_INSPECTION C, VSD_VIOLATION D, VSD_CHANNEL_DEFECT E, VSD_CHANNEL_DEFECT_TEST F, VSD_CHANNEL_TEST G, VSD_CHANNEL_PART_INST H, VSD_TEST_TYPE I, VSD_CHANNEL_PARTNER J, VSD_PARTNER K WHERE B.VEHICLE_INFO_ID = C.VEHICLE_INFO_ID AND C.VIOLATION_ID = D.VIOLATION_ID AND C.VIOLATION_ID = E.VIOLATION_ID AND E.CHANNEL_DEFECT_ID = F.CHANNEL_DEFECT_ID AND F.CHANNEL_TEST_ID = G.CHANNEL_TEST_ID AND E.CHANNEL_PART_INST_ID = H.CHANNEL_PART_INST_ID AND G.TEST_TYPE_ID = I.TEST_TYPE_ID AND H.CHANNEL_PARTNER_ID = J.CHANNEL_PARTNER_ID AND J.PARTNER_ID = K.PARTNER_ID ORDER BY G.CREATED_TIMESTAMP DESC*/ Session session = (Session) dsContext.getInternalContext(); String defaultSchema = DataServiceImpl.DEFAULT_SCHEMA_NAME; // StringBuffer query = new StringBuffer("SELECT D.VIOLATION_TICKET_CODE as \"violationId\", G.CREATED_TIMESTAMP as \"resultDateTime\", H.CHANNEL_PART_INST_NAME as \"testBranch\", H.CHANNEL_PART_INST_NAME_A as \"testBranchA\", K.PARTNER_NAME as \"testCenter\", K.PARTNER_NAME_A as \"testCenterA\"," // + " G.TEST_STATUS as \"testResult\", I.TEST_TYPE_NAME as \"testType\", I.TEST_TYPE_NAME_A as \"testTypeA\" , G.TEST_STATUS_A as \"testResultA\" " // + " FROM "+defaultSchema+".VSD_VEHICLE_INFO B, "+defaultSchema+".VSD_INSPECTION C, "+defaultSchema+".VSD_VIOLATION D, "+defaultSchema+".VSD_VIOLATION_TEST_FEE E, "+defaultSchema+".VSD_TEST_FEE F, "+defaultSchema+".VSD_CHANNEL_TEST G, " // + " "+defaultSchema+".VSD_CHANNEL_PART_INST H, "+defaultSchema+".VSD_TEST_TYPE I, "+defaultSchema+".VSD_CHANNEL_PARTNER J, "+defaultSchema+".VSD_PARTNER K " // + " WHERE " // + " B.VEHICLE_INFO_ID = C.VEHICLE_INFO_ID " // + " AND C.VIOLATION_ID = D.VIOLATION_ID " // + " AND D.VIOLATION_ID = E.VIOLATION_ID " // + " AND E.TEST_FEE_ID = F.TEST_FEE_ID " // + " AND F.TEST_FEE_ID = G.TEST_FEE_ID " // + " AND G.TEST_TYPE_ID = I.TEST_TYPE_ID " // + " AND G.CHANNEL_PART_INST_ID = H.CHANNEL_PART_INST_ID " // + " AND H.CHANNEL_PARTNER_ID = J.CHANNEL_PARTNER_ID " // + " AND J.PARTNER_ID = K.PARTNER_ID " // + " AND B.IS_DELETED = '"+IDataService.BOOL_FALSE+"' " // + " AND C.IS_DELETED = '"+IDataService.BOOL_FALSE+"' " // + " AND D.IS_DELETED = '"+IDataService.BOOL_FALSE+"' " // + " AND E.IS_DELETED = '"+IDataService.BOOL_FALSE+"' " // + " AND F.IS_DELETED = '"+IDataService.BOOL_FALSE+"' " // + " AND G.IS_DELETED = '"+IDataService.BOOL_FALSE+"' " // + " AND H.IS_DELETED = '"+IDataService.BOOL_FALSE+"' " // + " AND I.IS_DELETED = '"+IDataService.BOOL_FALSE+"' " // + " AND J.IS_DELETED = '"+IDataService.BOOL_FALSE+"' " // + " AND K.IS_DELETED = '"+IDataService.BOOL_FALSE+"' " // ); // // if(plateDetails.getPlateCategory() != null && !plateDetails.getPlateCategory().equals("")) { // query.append(" AND lower(B.VEHICLE_PLATE_CATEGORY) = '"+plateDetails.getPlateCategory().toLowerCase()+"'"); // } // if(plateDetails.getPlateCode() != null && !plateDetails.getPlateCode().equals("")) { // query.append(" AND lower(B.VEHICLE_PLATE_CODE) = '"+plateDetails.getPlateCode().toLowerCase()+"'"); // } // if(plateDetails.getPlateNumber() != null && !plateDetails.getPlateNumber().equals("")) { // query.append(" AND lower(B.VEHICLE_PLATE_NUMBER) = '"+plateDetails.getPlateNumber().toLowerCase()+"'"); // } // if(plateDetails.getPlateSource() != null && !plateDetails.getPlateSource().equals("")) { // query.append(" AND lower(B.VEHICLE_PLATE_SOURCE) = '"+plateDetails.getPlateSource().toLowerCase()+"'"); // } // query.append(" ORDER BY G.CREATED_TIMESTAMP DESC "); StringBuffer query = new StringBuffer( "SELECT * FROM (SELECT D.VIOLATION_TICKET_CODE as \"violationId\", G.CREATED_TIMESTAMP as \"resultDateTime\", H.CHANNEL_PART_INST_NAME as \"testBranch\", H.CHANNEL_PART_INST_NAME_A as \"testBranchA\", K.PARTNER_NAME as \"testCenter\", K.PARTNER_NAME_A as \"testCenterA\", G.TEST_STATUS as \"testResult\", I.TEST_TYPE_NAME as \"testType\", I.TEST_TYPE_NAME_A as \"testTypeA\", G.TEST_STATUS_A as \"testResultA\" " + " FROM " + defaultSchema + ".VSD_VEHICLE_INFO B, " + defaultSchema + ".VSD_INSPECTION C, " + defaultSchema + ".VSD_VIOLATION D, " + defaultSchema + ".VSD_VIOLATION_TEST_FEE E, " + defaultSchema + ".VSD_TEST_FEE F, " + defaultSchema + ".VSD_CHANNEL_TEST G, " + defaultSchema + ".VSD_CHANNEL_PART_INST H, " + defaultSchema + ".VSD_TEST_TYPE I, " + defaultSchema + ".VSD_CHANNEL_PARTNER J, " + defaultSchema + ".VSD_PARTNER K " + " WHERE B.VEHICLE_INFO_ID = C.VEHICLE_INFO_ID " + " AND C.VIOLATION_ID = D.VIOLATION_ID " + " AND D.VIOLATION_ID = E.VIOLATION_ID " + " AND E.TEST_FEE_ID = F.TEST_FEE_ID " + " AND F.TEST_FEE_ID = G.TEST_FEE_ID " + " AND G.TEST_TYPE_ID = I.TEST_TYPE_ID " + " AND G.CHANNEL_PART_INST_ID = H.CHANNEL_PART_INST_ID " + " AND H.CHANNEL_PARTNER_ID = J.CHANNEL_PARTNER_ID " + " AND J.PARTNER_ID = K.PARTNER_ID " + " AND B.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND C.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND D.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND E.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND F.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND G.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND H.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND I.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND J.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND K.IS_DELETED = '" + IDataService.BOOL_FALSE + "' "); if (plateDetails.getPlateCategory() != null && !plateDetails.getPlateCategory().equals("")) { query.append(" AND LOWER(B.VEHICLE_PLATE_CATEGORY) = '" + plateDetails.getPlateCategory().toLowerCase() + "'"); } if (plateDetails.getPlateCode() != null && !plateDetails.getPlateCode().equals("")) { query.append( " AND LOWER(B.VEHICLE_PLATE_CODE) = '" + plateDetails.getPlateCode().toLowerCase() + "'"); } if (plateDetails.getPlateNumber() != null && !plateDetails.getPlateNumber().equals("")) { query.append(" AND LOWER(B.VEHICLE_PLATE_NUMBER) = '" + plateDetails.getPlateNumber().toLowerCase() + "'"); } if (plateDetails.getPlateSource() != null && !plateDetails.getPlateSource().equals("")) { query.append(" AND LOWER(B.VEHICLE_PLATE_SOURCE) = '" + plateDetails.getPlateSource().toLowerCase() + "'"); } query.append( " UNION ALL SELECT null as \"violationId\", CT.CREATED_TIMESTAMP AS \"resultDateTime\", CPI.CHANNEL_PART_INST_NAME AS \"testBranch\", CPI.CHANNEL_PART_INST_NAME_A AS \"testBranchA\", P.PARTNER_NAME AS \"testCenter\", P.PARTNER_NAME_A AS \"testCenterA\", CT.TEST_STATUS AS \"testResult\", TT.TEST_TYPE_NAME AS \"testType\", TT.TEST_TYPE_NAME_A AS \"testTypeA\", CT.TEST_STATUS_A AS \"testResultA\" " + " FROM " + defaultSchema + ".VSD_VEHICLE_INFO V, " + defaultSchema + ".VSD_CHANNEL_TEST CT, " + defaultSchema + ".VSD_TEST_TYPE TT, " + defaultSchema + ".VSD_CHANNEL_PART_INST CPI, " + defaultSchema + ".VSD_CHANNEL_PARTNER CP, " + defaultSchema + ".VSD_PARTNER P " + " WHERE V.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND V.VEHICLE_INFO_ID = CT.VEHICLE_INFO_ID " + " AND CT.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND CT.TEST_TYPE_ID = TT.TEST_TYPE_ID " + " AND TT.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND CT.CHANNEL_PART_INST_ID = CPI.CHANNEL_PART_INST_ID " + " AND CPI.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND CPI.CHANNEL_PARTNER_ID = CP.CHANNEL_PARTNER_ID " + " AND CP.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND CP.PARTNER_ID = P.PARTNER_ID " + " AND P.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND TT.TEST_TYPE_CODE = " + ANNUAL_TEST_TYPE_CODE); if (plateDetails.getPlateCategory() != null && !plateDetails.getPlateCategory().equals("")) { query.append(" AND LOWER(V.VEHICLE_PLATE_CATEGORY) = '" + plateDetails.getPlateCategory().toLowerCase() + "'"); } if (plateDetails.getPlateCode() != null && !plateDetails.getPlateCode().equals("")) { query.append( " AND LOWER(V.VEHICLE_PLATE_CODE) = '" + plateDetails.getPlateCode().toLowerCase() + "'"); } if (plateDetails.getPlateNumber() != null && !plateDetails.getPlateNumber().equals("")) { query.append(" AND LOWER(V.VEHICLE_PLATE_NUMBER) = '" + plateDetails.getPlateNumber().toLowerCase() + "'"); } if (plateDetails.getPlateSource() != null && !plateDetails.getPlateSource().equals("")) { query.append(" AND LOWER(V.VEHICLE_PLATE_SOURCE) = '" + plateDetails.getPlateSource().toLowerCase() + "'"); } query.append(" ) ORDER BY \"resultDateTime\" DESC"); Query hQuery = session.createSQLQuery(query.toString()); if (paginationValues.getFirstResult() != null && paginationValues.getFirstResult().longValue() != -1) { hQuery = hQuery.setFirstResult(paginationValues.getFirstResult().intValue()); } if (paginationValues.getFetchedSize() != null && paginationValues.getFetchedSize().longValue() != -1) { hQuery = hQuery.setMaxResults(paginationValues.getFetchedSize().intValue()); } testResults = hQuery.setResultTransformer(new AliasToBeanResultTransformer(TestResult.class)).list(); } catch (Exception ex) { logger.error("An error occured in getVehicleTestResultsByPlateDetails()"); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getVehicleTestResultsByPlateDetails -- END"); return testResults; } /** * * Gets the count for getVehicleTestResultsByPlateDetails * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param plateDetails * @return List<TestResult> * @throws VSDDataAccessException */ public Long getCountForGetVehicleTestResultsByPlateDetails(DataServiceContext dsContext, boolean retrieveArabicData, VehiclePlate plateDetails) throws VSDDataAccessException { logger.info("getVehicleTestResultsByPlateDetails -- START"); List<TestResult> testResults = null; try { /*SELECT D.VIOLATION_TICKET_CODE VIOLATION_TICKET_CODE, D.REPORTED_DATE REPORTED_DATE, H.CHANNEL_PART_INST_NAME CHANNEL_PART_INST_NAME, K.PARTNER_NAME PARTNER_NAME, G.TEST_STATUS TEST_STATUS, I.TEST_TYPE_NAME TEST_TYPE_NAME FROM VSD_VEHICLE_INFO B, VSD_INSPECTION C, VSD_VIOLATION D, VSD_CHANNEL_DEFECT E, VSD_CHANNEL_DEFECT_TEST F, VSD_CHANNEL_TEST G, VSD_CHANNEL_PART_INST H, VSD_TEST_TYPE I, VSD_CHANNEL_PARTNER J, VSD_PARTNER K WHERE B.VEHICLE_INFO_ID = C.VEHICLE_INFO_ID AND C.VIOLATION_ID = D.VIOLATION_ID AND C.VIOLATION_ID = E.VIOLATION_ID AND E.CHANNEL_DEFECT_ID = F.CHANNEL_DEFECT_ID AND F.CHANNEL_TEST_ID = G.CHANNEL_TEST_ID AND E.CHANNEL_PART_INST_ID = H.CHANNEL_PART_INST_ID AND G.TEST_TYPE_ID = I.TEST_TYPE_ID AND H.CHANNEL_PARTNER_ID = J.CHANNEL_PARTNER_ID AND J.PARTNER_ID = K.PARTNER_ID ORDER BY G.CREATED_TIMESTAMP DESC*/ Session session = (Session) dsContext.getInternalContext(); String defaultSchema = DataServiceImpl.DEFAULT_SCHEMA_NAME; // StringBuffer query = new StringBuffer("SELECT count(\"violationId\") from (SELECT D.VIOLATION_TICKET_CODE as \"violationId\", D.REPORTED_DATE as \"resultDateTime\", H.CHANNEL_PART_INST_NAME as \"testBranch\", H.CHANNEL_PART_INST_NAME_A as \"testBranchA\", K.PARTNER_NAME as \"testCenter\", K.PARTNER_NAME_A as \"testCenterA\"," // + " G.TEST_STATUS as \"testResult\", I.TEST_TYPE_NAME as \"testType\", I.TEST_TYPE_NAME_A as \"testTypeA\" " // + " FROM "+defaultSchema+".VSD_VEHICLE_INFO B, "+defaultSchema+".VSD_INSPECTION C, "+defaultSchema+".VSD_VIOLATION D, "+defaultSchema+".VSD_VIOLATION_TEST_FEE E, "+defaultSchema+".VSD_TEST_FEE F, "+defaultSchema+".VSD_CHANNEL_TEST G, " // + " "+defaultSchema+".VSD_CHANNEL_PART_INST H, "+defaultSchema+".VSD_TEST_TYPE I, "+defaultSchema+".VSD_CHANNEL_PARTNER J, "+defaultSchema+".VSD_PARTNER K " // + " WHERE " // + " B.VEHICLE_INFO_ID = C.VEHICLE_INFO_ID " // + " AND C.VIOLATION_ID = D.VIOLATION_ID " // + " AND D.VIOLATION_ID = E.VIOLATION_ID " // + " AND E.TEST_FEE_ID = F.TEST_FEE_ID " // + " AND F.TEST_FEE_ID = G.TEST_FEE_ID " // + " AND G.TEST_TYPE_ID = I.TEST_TYPE_ID " // + " AND G.CHANNEL_PART_INST_ID = H.CHANNEL_PART_INST_ID " // + " AND H.CHANNEL_PARTNER_ID = J.CHANNEL_PARTNER_ID " // + " AND J.PARTNER_ID = K.PARTNER_ID " // + " AND B.IS_DELETED = '"+IDataService.BOOL_FALSE+"' " // + " AND C.IS_DELETED = '"+IDataService.BOOL_FALSE+"' " // + " AND D.IS_DELETED = '"+IDataService.BOOL_FALSE+"' " // + " AND E.IS_DELETED = '"+IDataService.BOOL_FALSE+"' " // + " AND F.IS_DELETED = '"+IDataService.BOOL_FALSE+"' " // + " AND G.IS_DELETED = '"+IDataService.BOOL_FALSE+"' " // + " AND H.IS_DELETED = '"+IDataService.BOOL_FALSE+"' " // + " AND I.IS_DELETED = '"+IDataService.BOOL_FALSE+"' " // + " AND J.IS_DELETED = '"+IDataService.BOOL_FALSE+"' " // + " AND K.IS_DELETED = '"+IDataService.BOOL_FALSE+"' " // ); // // if(plateDetails.getPlateCategory() != null && !plateDetails.getPlateCategory().equals("")) { // query.append(" AND UPPER(B.VEHICLE_PLATE_CATEGORY) = '"+plateDetails.getPlateCategory().toUpperCase()+"'"); // } // if(plateDetails.getPlateCode() != null && !plateDetails.getPlateCode().equals("")) { // query.append(" AND UPPER(B.VEHICLE_PLATE_CODE) = '"+plateDetails.getPlateCode().toUpperCase()+"'"); // } // if(plateDetails.getPlateNumber() != null && !plateDetails.getPlateNumber().equals("")) { // query.append(" AND UPPER(B.VEHICLE_PLATE_NUMBER) = '"+plateDetails.getPlateNumber().toUpperCase()+"'"); // } // if(plateDetails.getPlateSource() != null && !plateDetails.getPlateSource().equals("")) { // query.append(" AND UPPER(B.VEHICLE_PLATE_SOURCE) = '"+plateDetails.getPlateSource().toUpperCase()+"'"); // } // query.append(" ORDER BY G.CREATED_TIMESTAMP DESC )"); StringBuffer query = new StringBuffer( "SELECT Count(*) FROM (SELECT * FROM (SELECT D.VIOLATION_TICKET_CODE as \"violationId\", G.CREATED_TIMESTAMP as \"resultDateTime\", H.CHANNEL_PART_INST_NAME as \"testBranch\", H.CHANNEL_PART_INST_NAME_A as \"testBranchA\", K.PARTNER_NAME as \"testCenter\", K.PARTNER_NAME_A as \"testCenterA\", G.TEST_STATUS as \"testResult\", I.TEST_TYPE_NAME as \"testType\", I.TEST_TYPE_NAME_A as \"testTypeA\", G.TEST_STATUS_A as \"testResultA\" " + " FROM " + defaultSchema + ".VSD_VEHICLE_INFO B, " + defaultSchema + ".VSD_INSPECTION C, " + defaultSchema + ".VSD_VIOLATION D, " + defaultSchema + ".VSD_VIOLATION_TEST_FEE E, " + defaultSchema + ".VSD_TEST_FEE F, " + defaultSchema + ".VSD_CHANNEL_TEST G, " + defaultSchema + ".VSD_CHANNEL_PART_INST H, " + defaultSchema + ".VSD_TEST_TYPE I, " + defaultSchema + ".VSD_CHANNEL_PARTNER J, " + defaultSchema + ".VSD_PARTNER K " + " WHERE B.VEHICLE_INFO_ID = C.VEHICLE_INFO_ID " + " AND C.VIOLATION_ID = D.VIOLATION_ID " + " AND D.VIOLATION_ID = E.VIOLATION_ID " + " AND E.TEST_FEE_ID = F.TEST_FEE_ID " + " AND F.TEST_FEE_ID = G.TEST_FEE_ID " + " AND G.TEST_TYPE_ID = I.TEST_TYPE_ID " + " AND G.CHANNEL_PART_INST_ID = H.CHANNEL_PART_INST_ID " + " AND H.CHANNEL_PARTNER_ID = J.CHANNEL_PARTNER_ID " + " AND J.PARTNER_ID = K.PARTNER_ID " + " AND B.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND C.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND D.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND E.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND F.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND G.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND H.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND I.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND J.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND K.IS_DELETED = '" + IDataService.BOOL_FALSE + "' "); if (plateDetails.getPlateCategory() != null && !plateDetails.getPlateCategory().equals("")) { query.append(" AND LOWER(B.VEHICLE_PLATE_CATEGORY) = '" + plateDetails.getPlateCategory().toLowerCase() + "'"); } if (plateDetails.getPlateCode() != null && !plateDetails.getPlateCode().equals("")) { query.append( " AND LOWER(B.VEHICLE_PLATE_CODE) = '" + plateDetails.getPlateCode().toLowerCase() + "'"); } if (plateDetails.getPlateNumber() != null && !plateDetails.getPlateNumber().equals("")) { query.append(" AND LOWER(B.VEHICLE_PLATE_NUMBER) = '" + plateDetails.getPlateNumber().toLowerCase() + "'"); } if (plateDetails.getPlateSource() != null && !plateDetails.getPlateSource().equals("")) { query.append(" AND LOWER(B.VEHICLE_PLATE_SOURCE) = '" + plateDetails.getPlateSource().toLowerCase() + "'"); } query.append( " UNION ALL SELECT null as \"violationId\", CT.CREATED_TIMESTAMP AS \"resultDateTime\", CPI.CHANNEL_PART_INST_NAME AS \"testBranch\", CPI.CHANNEL_PART_INST_NAME_A AS \"testBranchA\", P.PARTNER_NAME AS \"testCenter\", P.PARTNER_NAME_A AS \"testCenterA\", CT.TEST_STATUS AS \"testResult\", TT.TEST_TYPE_NAME AS \"testType\", TT.TEST_TYPE_NAME_A AS \"testTypeA\", CT.TEST_STATUS_A AS \"testResultA\" " + " FROM " + defaultSchema + ".VSD_VEHICLE_INFO V, " + defaultSchema + ".VSD_CHANNEL_TEST CT, " + defaultSchema + ".VSD_TEST_TYPE TT, " + defaultSchema + ".VSD_CHANNEL_PART_INST CPI, " + defaultSchema + ".VSD_CHANNEL_PARTNER CP, " + defaultSchema + ".VSD_PARTNER P " + " WHERE V.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND V.VEHICLE_INFO_ID = CT.VEHICLE_INFO_ID " + " AND CT.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND CT.TEST_TYPE_ID = TT.TEST_TYPE_ID " + " AND TT.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND CT.CHANNEL_PART_INST_ID = CPI.CHANNEL_PART_INST_ID " + " AND CPI.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND CPI.CHANNEL_PARTNER_ID = CP.CHANNEL_PARTNER_ID " + " AND CP.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND CP.PARTNER_ID = P.PARTNER_ID " + " AND P.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND TT.TEST_TYPE_CODE = " + ANNUAL_TEST_TYPE_CODE); if (plateDetails.getPlateCategory() != null && !plateDetails.getPlateCategory().equals("")) { query.append(" AND LOWER(V.VEHICLE_PLATE_CATEGORY) = '" + plateDetails.getPlateCategory().toLowerCase() + "'"); } if (plateDetails.getPlateCode() != null && !plateDetails.getPlateCode().equals("")) { query.append( " AND LOWER(V.VEHICLE_PLATE_CODE) = '" + plateDetails.getPlateCode().toLowerCase() + "'"); } if (plateDetails.getPlateNumber() != null && !plateDetails.getPlateNumber().equals("")) { query.append(" AND LOWER(V.VEHICLE_PLATE_NUMBER) = '" + plateDetails.getPlateNumber().toLowerCase() + "'"); } if (plateDetails.getPlateSource() != null && !plateDetails.getPlateSource().equals("")) { query.append(" AND LOWER(V.VEHICLE_PLATE_SOURCE) = '" + plateDetails.getPlateSource().toLowerCase() + "'"); } query.append(" ) ORDER BY \"resultDateTime\" DESC)"); Long count = new Long(session.createSQLQuery(query.toString()).uniqueResult().toString()); logger.info("getVehicleTestResultsByPlateDetails -- END"); return count; } catch (Exception ex) { logger.error("An error occured in getVehicleTestResultsByPlateDetails()"); throw new VSDDataAccessException(ex.getMessage(), ex); } } /** * * Gets a list of Test Results with their testing center and testing information by the vehicle owners trade license number * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param tradeLicenseNumber * @return List<TestResult> * @throws VSDDataAccessException */ public List<TestResult> getVehicleTestResultsByTradeLicenseNumber(DataServiceContext dsContext, boolean retrieveArabicData, String tradeLicenseNumber) throws VSDDataAccessException { logger.info("getVehicleTestResultsByTradeLicenseNumber -- START"); List<TestResult> testResults = null; try { Session session = (Session) dsContext.getInternalContext(); String defaultSchema = DataServiceImpl.DEFAULT_SCHEMA_NAME; StringBuffer query = new StringBuffer( "SELECT D.VIOLATION_TICKET_CODE as \"violationId\", D.REPORTED_DATE as \"resultDateTime\", H.CHANNEL_PART_INST_NAME as \"testBranch\", H.CHANNEL_PART_INST_NAME_A as \"testBranchA\", K.PARTNER_NAME as \"testCenter\", K.PARTNER_NAME_A as \"testCenterA\"," + " G.TEST_STATUS as \"testResult\", I.TEST_TYPE_NAME as \"testType\", I.TEST_TYPE_NAME_A as \"testTypeA\" " + " FROM " + defaultSchema + ".VSD_OWNER_INFO A, " + defaultSchema + ".VSD_VEHICLE_INFO B, " + defaultSchema + ".VSD_INSPECTION C, " + defaultSchema + ".VSD_VIOLATION D, " + defaultSchema + ".VSD_VIOLATION_TEST_FEE E, " + defaultSchema + ".VSD_TEST_FEE F, " + defaultSchema + ".VSD_CHANNEL_TEST G, " + " " + defaultSchema + ".VSD_CHANNEL_PART_INST H, " + defaultSchema + ".VSD_TEST_TYPE I, " + defaultSchema + ".VSD_CHANNEL_PARTNER J, " + defaultSchema + ".VSD_PARTNER K " + " WHERE " + " A.VEHICLE_INFO_ID = B.VEHICLE_INFO_ID " + " AND B.VEHICLE_INFO_ID = C.VEHICLE_INFO_ID " + " AND C.VIOLATION_ID = D.VIOLATION_ID " + " AND D.VIOLATION_ID = E.VIOLATION_ID " + " AND E.TEST_FEE_ID = F.TEST_FEE_ID " + " AND F.TEST_FEE_ID = G.TEST_FEE_ID " + " AND G.TEST_TYPE_ID = I.TEST_TYPE_ID " + " AND G.CHANNEL_PART_INST_ID = H.CHANNEL_PART_INST_ID " + " AND H.CHANNEL_PARTNER_ID = J.CHANNEL_PARTNER_ID " + " AND J.PARTNER_ID = K.PARTNER_ID " + " AND A.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND B.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND C.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND D.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND E.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND F.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND G.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND H.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND I.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND J.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND K.IS_DELETED = '" + IDataService.BOOL_FALSE + "' "); if (tradeLicenseNumber != null && !tradeLicenseNumber.equals("")) { query.append(" AND A.TRADE_LICENSE_NUMBER = '" + tradeLicenseNumber + "'"); } query.append(" ORDER BY G.CREATED_TIMESTAMP DESC "); testResults = session.createSQLQuery(query.toString()) .setResultTransformer(new AliasToBeanResultTransformer(TestResult.class)).list(); } catch (Exception ex) { logger.error("An error occured in getVehicleTestResultsByTradeLicenseNumber()"); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getVehicleTestResultsByTradeLicenseNumber -- END"); return testResults; } /** * * Gets a list of Test Results with their testing center and testing information by the vehicle owners trade license number * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param tradeLicenseNumber * @return List<TestResult> * @throws VSDDataAccessException */ public List<TestResult> getVehicleTestResultsByTraficFileNumber(DataServiceContext dsContext, boolean retrieveArabicData, String traficFileNumber) throws VSDDataAccessException { logger.info("getVehicleTestResultsByTradeLicenseNumber -- START"); List<TestResult> testResults = null; try { Session session = (Session) dsContext.getInternalContext(); String defaultSchema = DataServiceImpl.DEFAULT_SCHEMA_NAME; // StringBuffer query = new StringBuffer("SELECT D.VIOLATION_TICKET_CODE as \"violationId\", D.REPORTED_DATE as \"resultDateTime\", H.CHANNEL_PART_INST_NAME as \"testBranch\", H.CHANNEL_PART_INST_NAME_A as \"testBranchA\", K.PARTNER_NAME as \"testCenter\", K.PARTNER_NAME_A as \"testCenterA\"," // + " G.TEST_STATUS as \"testResult\", I.TEST_TYPE_NAME as \"testType\", I.TEST_TYPE_NAME_A as \"testTypeA\", B.VEHICLE_PLATE_NUMBER||' '||B.VEHICLE_PLATE_CATEGORY||' '||B.VEHICLE_PLATE_CODE||' '||B.VEHICLE_PLATE_SOURCE as \"vehicleDetails\" " // + " FROM "+defaultSchema+".VSD_OWNER_INFO A, "+defaultSchema+".VSD_VEHICLE_INFO B, "+defaultSchema+".VSD_INSPECTION C, "+defaultSchema+".VSD_VIOLATION D, "+defaultSchema+".VSD_VIOLATION_TEST_FEE E, "+defaultSchema+".VSD_TEST_FEE F, "+defaultSchema+".VSD_CHANNEL_TEST G, " // + " "+defaultSchema+".VSD_CHANNEL_PART_INST H, "+defaultSchema+".VSD_TEST_TYPE I, "+defaultSchema+".VSD_CHANNEL_PARTNER J, "+defaultSchema+".VSD_PARTNER K " // + " WHERE " // + " A.VEHICLE_INFO_ID = B.VEHICLE_INFO_ID " // + " AND B.VEHICLE_INFO_ID = C.VEHICLE_INFO_ID " // + " AND C.VIOLATION_ID = D.VIOLATION_ID " // + " AND D.VIOLATION_ID = E.VIOLATION_ID " // + " AND E.TEST_FEE_ID = F.TEST_FEE_ID " // + " AND F.TEST_FEE_ID = G.TEST_FEE_ID " // + " AND G.TEST_TYPE_ID = I.TEST_TYPE_ID " // + " AND G.CHANNEL_PART_INST_ID = H.CHANNEL_PART_INST_ID " // + " AND H.CHANNEL_PARTNER_ID = J.CHANNEL_PARTNER_ID " // + " AND J.PARTNER_ID = K.PARTNER_ID " // + " AND A.IS_DELETED = '"+IDataService.BOOL_FALSE+"' " // + " AND B.IS_DELETED = '"+IDataService.BOOL_FALSE+"' " // + " AND C.IS_DELETED = '"+IDataService.BOOL_FALSE+"' " // + " AND D.IS_DELETED = '"+IDataService.BOOL_FALSE+"' " // + " AND E.IS_DELETED = '"+IDataService.BOOL_FALSE+"' " // + " AND F.IS_DELETED = '"+IDataService.BOOL_FALSE+"' " // + " AND G.IS_DELETED = '"+IDataService.BOOL_FALSE+"' " // + " AND H.IS_DELETED = '"+IDataService.BOOL_FALSE+"' " // + " AND I.IS_DELETED = '"+IDataService.BOOL_FALSE+"' " // + " AND J.IS_DELETED = '"+IDataService.BOOL_FALSE+"' " // + " AND K.IS_DELETED = '"+IDataService.BOOL_FALSE+"' " // ); // // if(traficFileNumber != null && !traficFileNumber.equals("")) { // query.append(" AND A.TRAFFIC_FILE_NUMBER = '"+traficFileNumber+"'"); // } // query.append(" ORDER BY G.CREATED_TIMESTAMP DESC "); StringBuffer query = new StringBuffer( "SELECT * FROM (SELECT D.VIOLATION_TICKET_CODE as \"violationId\", G.CREATED_TIMESTAMP as \"resultDateTime\", H.CHANNEL_PART_INST_NAME as \"testBranch\", H.CHANNEL_PART_INST_NAME_A as \"testBranchA\", K.PARTNER_NAME as \"testCenter\", K.PARTNER_NAME_A as \"testCenterA\", G.TEST_STATUS as \"testResult\", G.TEST_STATUS_A as \"testResultA\", I.TEST_TYPE_NAME as \"testType\", I.TEST_TYPE_NAME_A as \"testTypeA\", B.VEHICLE_PLATE_NUMBER||' '||B.VEHICLE_PLATE_CATEGORY||' '||B.VEHICLE_PLATE_CODE||' '||B.VEHICLE_PLATE_SOURCE as \"vehicleDetails\" " + " FROM " + defaultSchema + ".VSD_OWNER_INFO A, " + defaultSchema + ".VSD_VEHICLE_INFO B, " + defaultSchema + ".VSD_INSPECTION C, " + defaultSchema + ".VSD_VIOLATION D, " + defaultSchema + ".VSD_VIOLATION_TEST_FEE E, " + defaultSchema + ".VSD_TEST_FEE F, " + defaultSchema + ".VSD_CHANNEL_TEST G, " + defaultSchema + ".VSD_CHANNEL_PART_INST H, " + defaultSchema + ".VSD_TEST_TYPE I, " + defaultSchema + ".VSD_CHANNEL_PARTNER J, " + defaultSchema + ".VSD_PARTNER K " + " WHERE A.VEHICLE_INFO_ID = B.VEHICLE_INFO_ID " + " AND B.VEHICLE_INFO_ID = C.VEHICLE_INFO_ID " + " AND C.VIOLATION_ID = D.VIOLATION_ID " + " AND D.VIOLATION_ID = E.VIOLATION_ID " + " AND E.TEST_FEE_ID = F.TEST_FEE_ID " + " AND F.TEST_FEE_ID = G.TEST_FEE_ID " + " AND G.TEST_TYPE_ID = I.TEST_TYPE_ID " + " AND G.CHANNEL_PART_INST_ID = H.CHANNEL_PART_INST_ID " + " AND H.CHANNEL_PARTNER_ID = J.CHANNEL_PARTNER_ID " + " AND J.PARTNER_ID = K.PARTNER_ID " + " AND A.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND B.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND C.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND D.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND E.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND F.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND G.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND H.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND I.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND J.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND K.IS_DELETED = '" + IDataService.BOOL_FALSE + "' "); if (traficFileNumber != null && !traficFileNumber.equals("")) { query.append(" AND A.TRAFFIC_FILE_NUMBER = '" + traficFileNumber + "'"); } query.append( " UNION ALL SELECT null as \"violationId\", CT.CREATED_TIMESTAMP AS \"resultDateTime\", CPI.CHANNEL_PART_INST_NAME AS \"testBranch\", CPI.CHANNEL_PART_INST_NAME_A AS \"testBranchA\", P.PARTNER_NAME AS \"testCenter\", P.PARTNER_NAME_A AS \"testCenterA\", CT.TEST_STATUS AS \"testResult\", CT.TEST_STATUS_A as \"testResultA\", TT.TEST_TYPE_NAME AS \"testType\", TT.TEST_TYPE_NAME_A AS \"testTypeA\", V.VEHICLE_PLATE_NUMBER||' '||V.VEHICLE_PLATE_CATEGORY||' '||V.VEHICLE_PLATE_CODE||' '||V.VEHICLE_PLATE_SOURCE as \"vehicleDetails\" " + " FROM " + defaultSchema + ".VSD_OWNER_INFO O, " + defaultSchema + ".VSD_VEHICLE_INFO V, " + defaultSchema + ".VSD_CHANNEL_TEST CT, " + defaultSchema + ".VSD_TEST_TYPE TT, " + defaultSchema + ".VSD_CHANNEL_PART_INST CPI, " + defaultSchema + ".VSD_CHANNEL_PARTNER CP, " + defaultSchema + ".VSD_PARTNER P " + " WHERE O.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND O.VEHICLE_INFO_ID = V.VEHICLE_INFO_ID " + " AND V.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND V.VEHICLE_INFO_ID = CT.VEHICLE_INFO_ID " + " AND CT.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND CT.TEST_TYPE_ID = TT.TEST_TYPE_ID " + " AND TT.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND CT.CHANNEL_PART_INST_ID = CPI.CHANNEL_PART_INST_ID " + " AND CPI.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND CPI.CHANNEL_PARTNER_ID = CP.CHANNEL_PARTNER_ID " + " AND CP.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND CP.PARTNER_ID = P.PARTNER_ID " + " AND P.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND TT.TEST_TYPE_CODE = " + ANNUAL_TEST_TYPE_CODE); if (traficFileNumber != null && !traficFileNumber.equals("")) { query.append(" AND O.TRAFFIC_FILE_NUMBER = '" + traficFileNumber + "'"); } query.append(" ) ORDER BY \"resultDateTime\" DESC"); testResults = session.createSQLQuery(query.toString()) .setResultTransformer(new AliasToBeanResultTransformer(TestResult.class)).list(); } catch (Exception ex) { logger.error("An error occured in getVehicleTestResultsByTradeLicenseNumber()"); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getVehicleTestResultsByTradeLicenseNumber -- END"); return testResults; } /** * * Gets Count for a list of Test Results with their testing center and testing information by the vehicle owners trade license number * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param tradeLicenseNumber * @return Long * @throws VSDDataAccessException */ public Long getCountForVehicleTestResultsByTraficFileNumber(DataServiceContext dsContext, boolean retrieveArabicData, String traficFileNumber) throws VSDDataAccessException { logger.info("getVehicleTestResultsByTradeLicenseNumber -- START"); Long testResultsCount = null; try { Session session = (Session) dsContext.getInternalContext(); String defaultSchema = DataServiceImpl.DEFAULT_SCHEMA_NAME; // StringBuffer query = new StringBuffer("SELECT COUNT (G.CREATED_TIMESTAMP) " // + " FROM "+defaultSchema+".VSD_OWNER_INFO A, "+defaultSchema+".VSD_VEHICLE_INFO B, "+defaultSchema+".VSD_INSPECTION C, "+defaultSchema+".VSD_VIOLATION D, "+defaultSchema+".VSD_VIOLATION_TEST_FEE E, "+defaultSchema+".VSD_TEST_FEE F, "+defaultSchema+".VSD_CHANNEL_TEST G, " // + " "+defaultSchema+".VSD_CHANNEL_PART_INST H, "+defaultSchema+".VSD_TEST_TYPE I, "+defaultSchema+".VSD_CHANNEL_PARTNER J, "+defaultSchema+".VSD_PARTNER K " // + " WHERE " // + " A.VEHICLE_INFO_ID = B.VEHICLE_INFO_ID " // + " AND B.VEHICLE_INFO_ID = C.VEHICLE_INFO_ID " // + " AND C.VIOLATION_ID = D.VIOLATION_ID " // + " AND D.VIOLATION_ID = E.VIOLATION_ID " // + " AND E.TEST_FEE_ID = F.TEST_FEE_ID " // + " AND F.TEST_FEE_ID = G.TEST_FEE_ID " // + " AND G.TEST_TYPE_ID = I.TEST_TYPE_ID " // + " AND G.CHANNEL_PART_INST_ID = H.CHANNEL_PART_INST_ID " // + " AND H.CHANNEL_PARTNER_ID = J.CHANNEL_PARTNER_ID " // + " AND J.PARTNER_ID = K.PARTNER_ID " // + " AND A.IS_DELETED = '"+IDataService.BOOL_FALSE+"' " // + " AND B.IS_DELETED = '"+IDataService.BOOL_FALSE+"' " // + " AND C.IS_DELETED = '"+IDataService.BOOL_FALSE+"' " // + " AND D.IS_DELETED = '"+IDataService.BOOL_FALSE+"' " // + " AND E.IS_DELETED = '"+IDataService.BOOL_FALSE+"' " // + " AND F.IS_DELETED = '"+IDataService.BOOL_FALSE+"' " // + " AND G.IS_DELETED = '"+IDataService.BOOL_FALSE+"' " // + " AND H.IS_DELETED = '"+IDataService.BOOL_FALSE+"' " // + " AND I.IS_DELETED = '"+IDataService.BOOL_FALSE+"' " // + " AND J.IS_DELETED = '"+IDataService.BOOL_FALSE+"' " // + " AND K.IS_DELETED = '"+IDataService.BOOL_FALSE+"' " // ); // // if(traficFileNumber != null && !traficFileNumber.equals("")) { // query.append(" AND A.TRAFFIC_FILE_NUMBER = '"+traficFileNumber+"'"); // } // query.append(" ORDER BY G.CREATED_TIMESTAMP DESC "); StringBuffer query = new StringBuffer( "SELECT COUNT(*) from (SELECT * FROM (SELECT D.VIOLATION_TICKET_CODE as \"violationId\", G.CREATED_TIMESTAMP as \"resultDateTime\", H.CHANNEL_PART_INST_NAME as \"testBranch\", H.CHANNEL_PART_INST_NAME_A as \"testBranchA\", K.PARTNER_NAME as \"testCenter\", K.PARTNER_NAME_A as \"testCenterA\", G.TEST_STATUS as \"testResult\", I.TEST_TYPE_NAME as \"testType\", I.TEST_TYPE_NAME_A as \"testTypeA\", B.VEHICLE_PLATE_NUMBER||' '||B.VEHICLE_PLATE_CATEGORY||' '||B.VEHICLE_PLATE_CODE||' '||B.VEHICLE_PLATE_SOURCE as \"vehicleDetails\" " + " FROM " + defaultSchema + ".VSD_OWNER_INFO A, " + defaultSchema + ".VSD_VEHICLE_INFO B, " + defaultSchema + ".VSD_INSPECTION C, " + defaultSchema + ".VSD_VIOLATION D, " + defaultSchema + ".VSD_VIOLATION_TEST_FEE E, " + defaultSchema + ".VSD_TEST_FEE F, " + defaultSchema + ".VSD_CHANNEL_TEST G, " + defaultSchema + ".VSD_CHANNEL_PART_INST H, " + defaultSchema + ".VSD_TEST_TYPE I, " + defaultSchema + ".VSD_CHANNEL_PARTNER J, " + defaultSchema + ".VSD_PARTNER K " + " WHERE A.VEHICLE_INFO_ID = B.VEHICLE_INFO_ID " + " AND B.VEHICLE_INFO_ID = C.VEHICLE_INFO_ID " + " AND C.VIOLATION_ID = D.VIOLATION_ID " + " AND D.VIOLATION_ID = E.VIOLATION_ID " + " AND E.TEST_FEE_ID = F.TEST_FEE_ID " + " AND F.TEST_FEE_ID = G.TEST_FEE_ID " + " AND G.TEST_TYPE_ID = I.TEST_TYPE_ID " + " AND G.CHANNEL_PART_INST_ID = H.CHANNEL_PART_INST_ID " + " AND H.CHANNEL_PARTNER_ID = J.CHANNEL_PARTNER_ID " + " AND J.PARTNER_ID = K.PARTNER_ID " + " AND A.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND B.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND C.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND D.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND E.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND F.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND G.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND H.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND I.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND J.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND K.IS_DELETED = '" + IDataService.BOOL_FALSE + "' "); if (traficFileNumber != null && !traficFileNumber.equals("")) { query.append(" AND A.TRAFFIC_FILE_NUMBER = '" + traficFileNumber + "'"); } query.append( " UNION ALL SELECT null as \"violationId\", CT.CREATED_TIMESTAMP AS \"resultDateTime\", CPI.CHANNEL_PART_INST_NAME AS \"testBranch\", CPI.CHANNEL_PART_INST_NAME_A AS \"testBranchA\", P.PARTNER_NAME AS \"testCenter\", P.PARTNER_NAME_A AS \"testCenterA\", CT.TEST_STATUS AS \"testResult\", TT.TEST_TYPE_NAME AS \"testType\", TT.TEST_TYPE_NAME_A AS \"testTypeA\", V.VEHICLE_PLATE_NUMBER||' '||V.VEHICLE_PLATE_CATEGORY||' '||V.VEHICLE_PLATE_CODE||' '||V.VEHICLE_PLATE_SOURCE as \"vehicleDetails\" " + " FROM " + defaultSchema + ".VSD_OWNER_INFO O, " + defaultSchema + ".VSD_VEHICLE_INFO V, " + defaultSchema + ".VSD_CHANNEL_TEST CT, " + defaultSchema + ".VSD_TEST_TYPE TT, " + defaultSchema + ".VSD_CHANNEL_PART_INST CPI, " + defaultSchema + ".VSD_CHANNEL_PARTNER CP, " + defaultSchema + ".VSD_PARTNER P " + " WHERE O.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND O.VEHICLE_INFO_ID = V.VEHICLE_INFO_ID " + " AND V.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND V.VEHICLE_INFO_ID = CT.VEHICLE_INFO_ID " + " AND CT.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND CT.TEST_TYPE_ID = TT.TEST_TYPE_ID " + " AND TT.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND CT.CHANNEL_PART_INST_ID = CPI.CHANNEL_PART_INST_ID " + " AND CPI.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND CPI.CHANNEL_PARTNER_ID = CP.CHANNEL_PARTNER_ID " + " AND CP.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND CP.PARTNER_ID = P.PARTNER_ID " + " AND P.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND TT.TEST_TYPE_CODE = " + ANNUAL_TEST_TYPE_CODE); if (traficFileNumber != null && !traficFileNumber.equals("")) { query.append(" AND O.TRAFFIC_FILE_NUMBER = '" + traficFileNumber + "'"); } query.append(" ) ORDER BY \"resultDateTime\" DESC)"); BigDecimal bigDecimalCount = (BigDecimal) session.createSQLQuery(query.toString()).uniqueResult(); testResultsCount = bigDecimalCount.longValue(); } catch (Exception ex) { logger.error("An error occured in getVehicleTestResultsByTradeLicenseNumber()"); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getVehicleTestResultsByTradeLicenseNumber -- END"); return testResultsCount; } /** * * Gets a list of Test Results with their testing center and testing information by the vehicle owners trade license number * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param tradeLicenseNumber * @param paginationParam * @return List<TestResult> * @throws VSDDataAccessException */ public List<TestResult> getVehicleTestResultsByTraficFileNumber(DataServiceContext dsContext, boolean retrieveArabicData, String traficFileNumber, PaginationParam paginationParam) throws VSDDataAccessException { logger.info("getVehicleTestResultsByTradeLicenseNumber -- START"); List<TestResult> testResults = null; try { Session session = (Session) dsContext.getInternalContext(); String defaultSchema = DataServiceImpl.DEFAULT_SCHEMA_NAME; StringBuffer query = new StringBuffer( "SELECT * FROM (SELECT D.VIOLATION_TICKET_CODE as \"violationId\", G.CREATED_TIMESTAMP as \"resultDateTime\", H.CHANNEL_PART_INST_NAME as \"testBranch\", H.CHANNEL_PART_INST_NAME_A as \"testBranchA\", K.PARTNER_NAME as \"testCenter\", K.PARTNER_NAME_A as \"testCenterA\", G.TEST_STATUS as \"testResult\", G.TEST_STATUS_A as \"testResultA\", I.TEST_TYPE_NAME as \"testType\", I.TEST_TYPE_NAME_A as \"testTypeA\", B.VEHICLE_PLATE_NUMBER||' '||B.VEHICLE_PLATE_CATEGORY||' '||B.VEHICLE_PLATE_CODE||' '||B.VEHICLE_PLATE_SOURCE as \"vehicleDetails\" " + " FROM " + defaultSchema + ".VSD_OWNER_INFO A, " + defaultSchema + ".VSD_VEHICLE_INFO B, " + defaultSchema + ".VSD_INSPECTION C, " + defaultSchema + ".VSD_VIOLATION D, " + defaultSchema + ".VSD_VIOLATION_TEST_FEE E, " + defaultSchema + ".VSD_TEST_FEE F, " + defaultSchema + ".VSD_CHANNEL_TEST G, " + defaultSchema + ".VSD_CHANNEL_PART_INST H, " + defaultSchema + ".VSD_TEST_TYPE I, " + defaultSchema + ".VSD_CHANNEL_PARTNER J, " + defaultSchema + ".VSD_PARTNER K " + " WHERE A.VEHICLE_INFO_ID = B.VEHICLE_INFO_ID " + " AND B.VEHICLE_INFO_ID = C.VEHICLE_INFO_ID " + " AND C.VIOLATION_ID = D.VIOLATION_ID " + " AND D.VIOLATION_ID = E.VIOLATION_ID " + " AND E.TEST_FEE_ID = F.TEST_FEE_ID " + " AND F.TEST_FEE_ID = G.TEST_FEE_ID " + " AND G.TEST_TYPE_ID = I.TEST_TYPE_ID " + " AND G.CHANNEL_PART_INST_ID = H.CHANNEL_PART_INST_ID " + " AND H.CHANNEL_PARTNER_ID = J.CHANNEL_PARTNER_ID " + " AND J.PARTNER_ID = K.PARTNER_ID " + " AND A.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND B.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND C.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND D.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND E.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND F.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND G.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND H.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND I.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND J.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND K.IS_DELETED = '" + IDataService.BOOL_FALSE + "' "); if (traficFileNumber != null && !traficFileNumber.equals("")) { query.append(" AND A.TRAFFIC_FILE_NUMBER = '" + traficFileNumber + "'"); } query.append( " UNION ALL SELECT null as \"violationId\", CT.CREATED_TIMESTAMP AS \"resultDateTime\", CPI.CHANNEL_PART_INST_NAME AS \"testBranch\", CPI.CHANNEL_PART_INST_NAME_A AS \"testBranchA\", P.PARTNER_NAME AS \"testCenter\", P.PARTNER_NAME_A AS \"testCenterA\", CT.TEST_STATUS AS \"testResult\", CT.TEST_STATUS_A as \"testResultA\", TT.TEST_TYPE_NAME AS \"testType\", TT.TEST_TYPE_NAME_A AS \"testTypeA\", V.VEHICLE_PLATE_NUMBER||' '||V.VEHICLE_PLATE_CATEGORY||' '||V.VEHICLE_PLATE_CODE||' '||V.VEHICLE_PLATE_SOURCE as \"vehicleDetails\" " + " FROM " + defaultSchema + ".VSD_OWNER_INFO O, " + defaultSchema + ".VSD_VEHICLE_INFO V, " + defaultSchema + ".VSD_CHANNEL_TEST CT, " + defaultSchema + ".VSD_TEST_TYPE TT, " + defaultSchema + ".VSD_CHANNEL_PART_INST CPI, " + defaultSchema + ".VSD_CHANNEL_PARTNER CP, " + defaultSchema + ".VSD_PARTNER P " + " WHERE O.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND O.VEHICLE_INFO_ID = V.VEHICLE_INFO_ID " + " AND V.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND V.VEHICLE_INFO_ID = CT.VEHICLE_INFO_ID " + " AND CT.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND CT.TEST_TYPE_ID = TT.TEST_TYPE_ID " + " AND TT.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND CT.CHANNEL_PART_INST_ID = CPI.CHANNEL_PART_INST_ID " + " AND CPI.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND CPI.CHANNEL_PARTNER_ID = CP.CHANNEL_PARTNER_ID " + " AND CP.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND CP.PARTNER_ID = P.PARTNER_ID " + " AND P.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND TT.TEST_TYPE_CODE = " + ANNUAL_TEST_TYPE_CODE); if (traficFileNumber != null && !traficFileNumber.equals("")) { query.append(" AND O.TRAFFIC_FILE_NUMBER = '" + traficFileNumber + "'"); } query.append(" ) ORDER BY \"resultDateTime\" DESC"); testResults = session.createSQLQuery(query.toString()) .setFirstResult(paginationParam.getFirstResult().intValue()) .setMaxResults(paginationParam.getFetchedSize().intValue()) .setResultTransformer(new AliasToBeanResultTransformer(TestResult.class)).list(); } catch (Exception ex) { logger.error("An error occured in getVehicleTestResultsByTradeLicenseNumber()"); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getVehicleTestResultsByTradeLicenseNumber -- END"); return testResults; } /** * * Gets a list of Test Results with their testing center and testing information by the vehicle owners trade license number * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param tradeLicenseNumber * @param paginationValues * @return List<TestResult> * @throws VSDDataAccessException */ public List<TestResult> getVehicleTestResultsByTradeLicenseNumber(DataServiceContext dsContext, boolean retrieveArabicData, String tradeLicenseNumber, PaginationParam paginationValues) throws VSDDataAccessException { logger.info("getVehicleTestResultsByTradeLicenseNumber -- START"); List<TestResult> testResults = null; try { Session session = (Session) dsContext.getInternalContext(); String defaultSchema = DataServiceImpl.DEFAULT_SCHEMA_NAME; StringBuffer query = new StringBuffer( "SELECT D.VIOLATION_TICKET_CODE as \"violationId\", D.REPORTED_DATE as \"resultDateTime\", H.CHANNEL_PART_INST_NAME as \"testBranch\", H.CHANNEL_PART_INST_NAME_A as \"testBranchA\", K.PARTNER_NAME as \"testCenter\", K.PARTNER_NAME_A as \"testCenterA\"," + " G.TEST_STATUS as \"testResult\", I.TEST_TYPE_NAME as \"testType\", I.TEST_TYPE_NAME_A as \"testTypeA\" " + " FROM " + defaultSchema + ".VSD_OWNER_INFO A, " + defaultSchema + ".VSD_VEHICLE_INFO B, " + defaultSchema + ".VSD_INSPECTION C, " + defaultSchema + ".VSD_VIOLATION D, " + defaultSchema + ".VSD_VIOLATION_TEST_FEE E, " + defaultSchema + ".VSD_TEST_FEE F, " + defaultSchema + ".VSD_CHANNEL_TEST G, " + " " + defaultSchema + ".VSD_CHANNEL_PART_INST H, " + defaultSchema + ".VSD_TEST_TYPE I, " + defaultSchema + ".VSD_CHANNEL_PARTNER J, " + defaultSchema + ".VSD_PARTNER K " + " WHERE " + " A.VEHICLE_INFO_ID = B.VEHICLE_INFO_ID " + " AND B.VEHICLE_INFO_ID = C.VEHICLE_INFO_ID " + " AND C.VIOLATION_ID = D.VIOLATION_ID " + " AND D.VIOLATION_ID = E.VIOLATION_ID " + " AND E.TEST_FEE_ID = F.TEST_FEE_ID " + " AND F.TEST_FEE_ID = G.TEST_FEE_ID " + " AND G.TEST_TYPE_ID = I.TEST_TYPE_ID " + " AND G.CHANNEL_PART_INST_ID = H.CHANNEL_PART_INST_ID " + " AND H.CHANNEL_PARTNER_ID = J.CHANNEL_PARTNER_ID " + " AND J.PARTNER_ID = K.PARTNER_ID " + " AND A.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND B.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND C.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND D.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND E.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND F.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND G.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND H.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND I.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND J.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND K.IS_DELETED = '" + IDataService.BOOL_FALSE + "' "); if (tradeLicenseNumber != null && !tradeLicenseNumber.equals("")) { query.append(" AND A.TRADE_LICENSE_NUMBER = '" + tradeLicenseNumber + "'"); } query.append(" ORDER BY G.CREATED_TIMESTAMP DESC "); Query hQuery = session.createSQLQuery(query.toString()); if (paginationValues.getFirstResult() != null && paginationValues.getFirstResult().longValue() != -1) { hQuery = hQuery.setFirstResult(paginationValues.getFirstResult().intValue()); } if (paginationValues.getFetchedSize() != null && paginationValues.getFetchedSize().longValue() != -1) { hQuery = hQuery.setMaxResults(paginationValues.getFetchedSize().intValue()); } testResults = hQuery.setResultTransformer(new AliasToBeanResultTransformer(TestResult.class)).list(); } catch (Exception ex) { logger.error("An error occured in getVehicleTestResultsByTradeLicenseNumber()"); throw new VSDDataAccessException(ex.getMessage(), ex); } logger.info("getVehicleTestResultsByTradeLicenseNumber -- END"); return testResults; } /** * * Gets the count for getVehicleTestResultsByTradeLicenseNumber * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param tradeLicenseNumber * @return Long * @throws VSDDataAccessException */ public Long getCountForGetVehicleTestResultsByTradeLicenseNumber(DataServiceContext dsContext, boolean retrieveArabicData, String tradeLicenseNumber) throws VSDDataAccessException { logger.info("getVehicleTestResultsByTradeLicenseNumber -- START"); List<TestResult> testResults = null; try { Session session = (Session) dsContext.getInternalContext(); String defaultSchema = DataServiceImpl.DEFAULT_SCHEMA_NAME; StringBuffer query = new StringBuffer( "SELECT count(\"violationId\") from (SELECT D.VIOLATION_TICKET_CODE as \"violationId\", D.REPORTED_DATE as \"resultDateTime\", H.CHANNEL_PART_INST_NAME as \"testBranch\", H.CHANNEL_PART_INST_NAME_A as \"testBranchA\", K.PARTNER_NAME as \"testCenter\", K.PARTNER_NAME_A as \"testCenterA\"," + " G.TEST_STATUS as \"testResult\", I.TEST_TYPE_NAME as \"testType\", I.TEST_TYPE_NAME_A as \"testTypeA\" " + " FROM " + defaultSchema + ".VSD_OWNER_INFO A, " + defaultSchema + ".VSD_VEHICLE_INFO B, " + defaultSchema + ".VSD_INSPECTION C, " + defaultSchema + ".VSD_VIOLATION D, " + defaultSchema + ".VSD_VIOLATION_TEST_FEE E, " + defaultSchema + ".VSD_TEST_FEE F, " + defaultSchema + ".VSD_CHANNEL_TEST G, " + " " + defaultSchema + ".VSD_CHANNEL_PART_INST H, " + defaultSchema + ".VSD_TEST_TYPE I, " + defaultSchema + ".VSD_CHANNEL_PARTNER J, " + defaultSchema + ".VSD_PARTNER K " + " WHERE " + " A.VEHICLE_INFO_ID = B.VEHICLE_INFO_ID " + " AND B.VEHICLE_INFO_ID = C.VEHICLE_INFO_ID " + " AND C.VIOLATION_ID = D.VIOLATION_ID " + " AND D.VIOLATION_ID = E.VIOLATION_ID " + " AND E.TEST_FEE_ID = F.TEST_FEE_ID " + " AND F.TEST_FEE_ID = G.TEST_FEE_ID " + " AND G.TEST_TYPE_ID = I.TEST_TYPE_ID " + " AND G.CHANNEL_PART_INST_ID = H.CHANNEL_PART_INST_ID " + " AND H.CHANNEL_PARTNER_ID = J.CHANNEL_PARTNER_ID " + " AND J.PARTNER_ID = K.PARTNER_ID " + " AND A.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND B.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND C.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND D.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND E.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND F.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND G.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND H.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND I.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND J.IS_DELETED = '" + IDataService.BOOL_FALSE + "' " + " AND K.IS_DELETED = '" + IDataService.BOOL_FALSE + "' "); if (tradeLicenseNumber != null && !tradeLicenseNumber.equals("")) { query.append(" AND A.TRADE_LICENSE_NUMBER = '" + tradeLicenseNumber + "'"); } query.append(" ORDER BY G.CREATED_TIMESTAMP DESC )"); Long count = new Long(session.createSQLQuery(query.toString()).uniqueResult().toString()); logger.info("getVehicleTestResultsByTradeLicenseNumber -- END"); return count; } catch (Exception ex) { logger.error("An error occured in getVehicleTestResultsByTradeLicenseNumber()"); throw new VSDDataAccessException(ex.getMessage(), ex); } } /** * * get Channel Tests By ViolationTicketCode * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param violationTicketCode * @return List<VsdChannelTest> * @throws VSDDataAccessException */ public List<VsdChannelTest> getPreviousChannelTestByViolationTicketCode(DataServiceContext dsContext, boolean retrieveArabicData, String violationTicketCode) throws VSDDataAccessException { logger.info("getPreviousChannelTestByViolationTicketCode -- START"); List<VsdChannelTest> channelTests = null; try { Session session = (Session) dsContext.getInternalContext(); channelTests = session.createCriteria(VsdChannelTest.class, "ct") .add(Restrictions.eq("ct.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("ct.vsdChannelDefectTests", "cdt", Criteria.LEFT_JOIN, Restrictions.eq("cdt.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cdt.vsdChannelDefect", "cd", Criteria.LEFT_JOIN, Restrictions.eq("cd.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("ct.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vi.vsdOwnerInfos", "oi", Criteria.LEFT_JOIN, Restrictions.eq("oi.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("vi.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdViolation", "v", Criteria.LEFT_JOIN, Restrictions.eq("v.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("v.violationTicketCode", violationTicketCode).ignoreCase()) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list(); logger.info("getPreviousChannelTestByViolationTicketCode -- END"); return channelTests; } catch (Exception ex) { logger.error("An error occured in getPreviousChannelTestByViolationTicketCode()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } } /** * * * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param fromDate * @param toDate * @return List<CustomVehicleTestStatusDTO> * @throws VSDDataAccessException */ public List<CustomVehicleTestStatusDTO> getVehicleTestStatusByDateRange(DataServiceContext dsContext, boolean retrieveArabicData, Date fromDate, Date toDate) throws VSDDataAccessException { logger.info("getVehicleTestStatusByDateRange -- START"); try { return new ArrayList<CustomVehicleTestStatusDTO>(); } catch (Exception ex) { logger.error("An error occured in getVehicleTestStatusByDateRange()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } } /** * * Saves a VsdChannelTest * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param vsdChannelTest * @return VsdChannelTest * @throws VSDDataAccessException */ public VsdChannelTest saveChannelTest(DataServiceContext dsContext, boolean retrieveArabicData, VsdChannelTest vsdChannelTest) throws VSDDataAccessException { logger.info("saveChannelTest -- START"); try { Session session = (Session) dsContext.getInternalContext(); session.save(vsdChannelTest); } catch (Exception ex) { logger.error("An error occured in saveChannelTest()"); throw new VSDDataAccessException(ex.getMessage()); } logger.info("saveChannelTest -- END"); return vsdChannelTest; } /** * * Updates a channel test * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param vsdChannelTest * @throws VSDDataAccessException */ public VsdChannelTest updateChannelTest(final DataServiceContext dsContext, boolean retrieveArabicData, VsdChannelTest vsdChannelTest) throws VSDDataAccessException { logger.info("updateChannelTest -- START"); try { Query query = createDynamicUpdateQuery(vsdChannelTest, dsContext); query.executeUpdate(); logger.info("updateChannelTest -- END"); return vsdChannelTest; } catch (Exception ex) { logger.error("An error occured in updateChannelTest()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage()); } } /** * * Gets a list of channel tests between the date specified and for a particular vehicle * * @author Eldon Barrows * @param dsContext * @param retrieveArabicData * @param plateDetails * @param startDate * @param endDate * @return List<VsdChannelTest> * @throws VSDDataAccessException */ public List<VsdChannelTest> getChannelTestByVehiclePlateAndCreatedDate(DataServiceContext dsContext, boolean retrieveArabicData, VehiclePlate plateDetails, Date startDate, Date endDate) throws VSDDataAccessException { logger.info("getChannelTestByVehiclePlateAndDate -- START"); List<VsdChannelTest> channelTests = null; try { Session session = (Session) dsContext.getInternalContext(); channelTests = session.createCriteria(VsdChannelTest.class, "ct") .add(Restrictions.eq("ct.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.between("ct.createdTimestamp", startDate, endDate)) .createCriteria("ct.vsdChannelDefectTests", "cdt", Criteria.LEFT_JOIN, Restrictions.eq("cdt.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cdt.vsdChannelDefect", "cd", Criteria.LEFT_JOIN, Restrictions.eq("cd.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("cd.vsdViolation", "v", Criteria.LEFT_JOIN, Restrictions.eq("v.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("v.vsdInspections", "i", Criteria.LEFT_JOIN, Restrictions.eq("i.isDeleted", IDataService.BOOL_FALSE)) .createCriteria("i.vsdVehicleInfo", "vi", Criteria.LEFT_JOIN, Restrictions.eq("vi.isDeleted", IDataService.BOOL_FALSE)) .add(Restrictions.eq("vi.vehiclePlateCategory", plateDetails.getPlateCategory()).ignoreCase()) .add(Restrictions.eq("vi.vehiclePlateCode", plateDetails.getPlateCode()).ignoreCase()) .add(Restrictions.eq("vi.vehiclePlateSource", plateDetails.getPlateSource()).ignoreCase()) .add(Restrictions.eq("vi.vehiclePlateNumber", plateDetails.getPlateNumber()).ignoreCase()) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list(); logger.info("getChannelTestByVehiclePlateAndDate -- END"); return channelTests; } catch (Exception ex) { logger.error("An error occured in getChannelTestByVehiclePlateAndDate()"); logger.error(ex); throw new VSDDataAccessException(ex.getMessage(), ex); } } }