Toll Analytics System (TAS) is a platform designed to collect, process, and analyze toll transaction data. It integrates data from multiple toll plazas, processes it through different stages, and generates structured reports for insights. The system primarily deals with two types of data sources:
- TMS Data - Toll transaction data recorded at the toll plazas.
- Bank Data - Financial transaction data provided by banks.
Each toll plaza has a dedicated system that records transactions. The system includes hardware such as Fastag scanners and cameras, with software running on a local database. The data collected from toll plazas is stored in a local SQL Server database at each site. Additionally, vehicle images are stored on a Network Attached Storage (NAS) drive.
To centralize this data, a remote machine in the same network is given access to:
- The local SQL Server database for reading transaction data.
- The NAS drive for accessing stored vehicle images.
-
TMS Data Extraction
- A Python script is scheduled to run daily at 2 AM.
- It extracts the last 24 hours of transaction data from the local database.
- The extracted data is saved as CSV files on AWS S3.
- Once the CSV files are stored, an AWS Glue Job is triggered.
- The Glue Job processes and transforms the data, storing it in the TMS Data at ODS Level on AWS Redshift.
-
Bank Data Extraction
- Bank transactions are fetched via an API that requires toll plaza ID and date.
- The API response contains transaction data in batches (pagination handled).
- A scheduled AWS Glue Job runs at 4 AM to fetch the last 24 hours of data.
- The raw bank data is saved as CSV files on AWS S3.
- Another Glue Job processes the data and loads it into the Raw Bank Data Table at ODS Level in Redshift.
Once both TMS and Bank data are stored at the ODS Level, further processing is required to clean, transform, and merge them for analytics.
-
Stored Procedure Execution
- Custom Stored Procedures (SPs) are executed in a step-by-step process.
- SPs integrate TMS and Bank data, perform validations, and apply business rules.
- The transformation happens in multiple stages (Level 1, Level 2, Level 3, etc.).
- At each level, data is processed and stored in temporary tables.
- The final processed data is stored in Datamart Transaction Table in Redshift.
-
Report Generation (Laravel Dashboard)
- A Laravel-based system is used to generate reports based on client requirements.
- Reports are generated by applying conditions on the Datamart Transaction Table.
- Users can access structured analytics and financial insights.
-
Dispute Transaction Identification
- A Stored Procedure runs on the Datamart Transaction Table to identify dispute transactions.
- Transactions flagged as disputes are stored in the Dispute Transactions Table.
- Each dispute transaction is assigned three image paths:
- IC (Image Capture)
- LPIC (License Plate Image Capture)
- IDV (Identification Verification)
-
Image Extraction & Upload
- A Python script runs daily at 9 AM to process dispute transactions.
- It reads image paths from the Dispute Transactions Table.
- Images are fetched from the NAS drive.
- Extracted images are uploaded to AWS S3.
- Once uploaded, the new S3 paths are updated in the Dispute Transactions Table.
- Laravel dashboard displays disputes with corresponding images.
- TMS and Bank data are extracted daily.
- Data is processed through AWS Glue and stored in Redshift at the ODS Level.
- Stored Procedures run step-by-step transformations, creating a final dataset in the Datamart Transaction Table.
- Laravel-based reports provide analytics on processed data.
- Dispute transactions are identified and flagged based on business rules.
- Vehicle images related to disputes are uploaded to AWS S3 and linked in reports.
This end-to-end system ensures efficient toll data management, allowing accurate financial analysis and dispute resolution.