Using the Database Importer

Introduction

The database importer allows the admin to import their existing information into the OPS-COM system. Given a CSV file, the importer will transfer the data into the selected destination table.

The database importer only allows data to be imported into a select number of tables.

The currently allowed tables are as follows:

UserProfile
OffenceLocations
Vehicle
Permits
Violations

Support for additional tables will continue to be added in the future.

Initial Setup

The database importer can be found listed under the tools tab.

If the database importer page is not visible, the user may not have the proper permissions. Before a user can import data, they must have the necessary permissions enabled on their account.

There are two permissions associated to table imports.

Manage tables - allows the user to view the manage tables page, and use the uploads functionality, but NOT the ability to reset tables
Reset tables - allows the user to reset the tables (empty them). Won't allow them to see the manage tables page.

The permissions can be found under the systems tab of the permissions management page. Enable them to allow a user access to the page.

For more information on permissions, refer to this article: https://wiki.ops-com.com/x/B4DjAQ

Importing Data into a Table

To start a new import, click on the upload button next to the destination table the information will be imported into.

This will open a modal window prompting the user to select the CSV file they will be importing into the destination table. The imported CSV file can be comma-differentiated or semi-colon-differentiated.

Once a file has been selected, press the submit button to begin the process.

Once the CSV file has been uploaded, the table import screen will appear.

Column Matching

On this page, the columns in the imported file are matched to the columns existing in the destination table.

This is to let the program know where the data from the CSV file belongs in the destination table.

If the CSV file contained recognized column names, they will be automatically selected.

For more information on what each column is used for, or whether a column is required or optional, refer to this guide here: https://opscom.wiki/books/for-administrators-staff/page/importer-field-descriptions

One the columns have been properly matched and the process button has been pressed, the user will be redirected back to the main page while the import is completed in the background.

If an email address is setup on the importing user's account, they will be informed on the progress of the import by emails sent to their email address.

Another import cannot be started until the current one is either completed or has been terminated. Terminating the progressing import will delete the temp tables created during the import, and isn’t recommended unless the import has become stuck.

Post-Import Processing

After the initial import of the data into the base table, some tables run additional processing on the information.

These processes run separately from the import process, and are not affected by the duplicate settings chosen for the import.

After this process has been completed, the user will be sent a second email containing the final details of the import, including how many records were imported, updated, and any post-processing details.

Permits

If the user association column is matched, the created permit will have a booking automatically created for the associated user through the creation of a PermitJoin record.
If the option was selected, the newly-created permits that were booked to users will be automatically marked as paid. Otherwise, they will be located in the users' carts and the users must pay for them.

UserProfile

When users are imported, if they aren't provided a LoginSource, it will be automatically set to OPSCOM.
Users that are created are set to enabled automatically.

Vehicle

If the user association column is matched, the created vehicle will be automatically associated to the user through the creation of a VehicleJoin record.
If the alert column is matched, an alert will be created and automatically attached to the associated vehicle through the creation of an AlertComments records. The vehicle will also be flagged.

Violations

Any violations created that don’t have an Issued date and Due date will have one created for them at the time of the import.

OffenseLocations

This table has no post-processing.

Additional Settings

Additional details on import settings.

Order of Operations

Imports that contain related information have a general order they should be done in, as some of the tables contain information from another table.

The imports are able to be done out of order, but the records cannot be associated correctly to each other when done out of order.

In general, the imports the table requires should be imported before it is.

Table Import	Requires	Notes
UserProfile		Should be the first thing imported.
Vehicles	UserProfile	Having the user record created before the vehicle is what allows the user to be associated to the vehicle by a vehicleJoin record created during the post-import processing.
OffenceLocations		Doesn’t require any data beforehand, but should be included when creating a violation.
Permits	Vehicles UserProfile	Having a vehicle record created before the permit is what allows the permit to be associated to the vehicle by a permitJoin created during the post-import processing. If there is also an associated user record, the permit is able to be marked as paid by joining the user and permit through a payment record.
Violations	Vehicles UserProfile OffenceLocations	A violation requires the existence of a vehicle record beforehand to have the violation be created at all. Offence locations should be imported before violations in order for the violation to have its location properly marked.

User-Association Settings

Some tables have additional processing that is run after the import is completed to associate the newly-created records to existing users.

For this, the record identifying the uniqueness of the user must be selected.

It can either be UserUUID or Email, so make sure the same value is selected for both sections.

The tables that currently have this as an option are:

Vehicles
Permits

Mark Permits as Paid

Permit importing has an additional option for if you would like to have the booked permits that are created automatically marked as paid.

Otherwise, when they are created, they will be located in the associated users' carts.

Vehicle Alerts

Vehicle importing has an additional option for setting up an alert on a vehicle automatically when they are created.

Include a column containing vehicle alert comments within the imported CSV file and select the column in the alert section of the vehicle import page.

Alerts will be automatically associated to the proper vehicle after import.

The list of vehicles being marked with alerts may also be called a hotlist.

Unique Identifiers & Duplicate Settings

This is the column that is used to tell if the information in the record is unique. If the information in this column is duplicated in the file or already exists in the system, it will be ignored or overwritten based on the selected setting.

Ignoring a duplicate will have the system do nothing with the record.
Overwrite existing will take the information in the new record and replace the existing record with it.

The number of columns that mark a record as unique varies from table to table.

UserProfile uses one identifier, which can either be UserUUID or Email. For this table, it is one or the other, with UserUUID taking precedence over Email if both columns are supplied.
OffenceLocations uses one identifier, which is LocationName.
Vehicle uses one identifier, which is Plate.
Permits uses two primary identifiers, PermitNo and LotNameID. Both are required to make a unique record, meaning identical PermitNo can be in use if they have different LotNameIDs.
Violations uses one identifier, which is Ticket.

Foreign Lookup Columns

Some information in one table comes from another table and is stored as an ID in the destination table.

The data in these columns should be entered as normal, then the system will automatically look to see if there is a match in the corresponding table and input the correct ID value into the destination table.

For instance, one of the foreign keys for the Vehicle table is Colour, stored in the VehicleColour table. If any of the values in the column imported for Colour exist in the VehicleColour table, it will match them by name and store the proper ID in the Vehicle table.

If it doesn't find a matching value in the lookup table, it will enter a Null as the value instead.

A list of the import tables are listed below.

UserProfile Table

Inserting Into	Sourced From Table
prov	Provinces
UserTypeID	UserTypes

Vehicle Table

Inserting Into	Sourced From Table
ProvID	Provinces
ColourID	VehicleColours
MakeID	VehicleMake
TypeID	VehicleType
PlateTypeID	VehiclePlateType

Permits Table

Inserting Into	Sourced From Table
LotNameID	LotName
StateID	PermitState

Violations Table

Inserting Into	Sourced From Table
VehicleID	Vehicle
TicketType	TicketCategory
LocationID	OffenceLocations

Status Emails

Over the course of the import process, a total of two emails will be sent to the user who initiated the import:

The first email is sent after the data from the CSV has been inserted into the temp table, and records the number of rows that were successful, and the rows that failed to be imported because they were malformed.
The second email is sent after the data has been inserted into the base table. It contains the number of records that were updated in the base table, the number of records inserted into the base table, and any relevant information from the post-import processing that happened.

Table Reset

In addition to table imports, the page also allows for the purging of a table.

Click on the button that says Reset next to the table to be emptied.

This will open a new modal window, prompting the user to confirm they wish to delete the contents of the table. This action is permanent, and once the data is deleted, it cannot be recovered.

If it is certain the data should be deleted, enter DELETE into the confirmation text field and press the delete button.

The selected table will then be purged of all records.

Some tables are closely connected to the records of a related table, and removing the data they contain will also purge the contents of the related table.

Purging the Vehicle table will also purge the VehicleJoin table.
Purging the Permits table will also purge the PermitJoin table.

Note that only the table contents are deleted, and the table itself remains intact.

https://opscom.wiki/books/for-administrators-staff/page/importer-field-descriptions