Phosagro Weighbridge Connector (PWBC)

To run the installer, launch the setup file setup_pwbc.exe.

This will install a Windows service Pitram PWB Connector service, which will need to be manually started once installed, and configured.

The connector has two (default) installation locations:

1. The Program (C:\Program Files\Micromine\PWB Connector)
2. Data files (C:\ProgramData\Micromine\PWBC\_installs)

Data Files and Configuration, one time setup

The first time the setup is run, we have the following files installed into a temporary program data folder (for example, ProgramData\Micromine\PWBC\_installs).

1. GeneralConfiguration.xml (all general configuration including connection strings)
2. DatabaseMarkerConfiguration.xml (holds the value of the Id field in the PWB database that is the next to process)
3. PWBCLocalDatabase.sdf (default local compact database we use for processing if the connection string for the local database is left as default)
4. SSCERuntime_x64-ENU.exe is the installer for SQL CE (the default database)

The following files will need to be copied into the parent folder, ie ProgramData\Micromine\PWBC

GeneralConfiguration.xml DatabaseMarkerConfiguration.xml PWBCLocalDatabase.sdf

SQL Compact

If the connector is going to use the default SQL compact database, the file SSCERuntime_x64-ENU.exe may need to be run to install the native SQL CE files.

The connector requires two groups of reference data:

1. A new group Exciter.



This will map the ICA path_nearest_location_Id to the weighbridge scales:



2. The existing Equipment group with its MstID attribute set (this will map ICA mobile_entity_id to a Pitram equipment code).
3. The IsTrain attribute set to 1 (to identify the equipment as a train):

The equipment representing the trains must also have a function of LOADING and duplicate dumps must be disabled.

Along with the above database configurations, all general configuration is held in GeneralConfiguration.xml. This includes email configuration, however this is not used by this connector (yet).

Other fields are:

Field Name	Purpose
ICA_LookupTime_Tolerance_Seconds	When we have a record from the WB database, we use the time of the first wagon to look for a matching equipment in the ICA database. This setting defined the range of the total window we look for in the ICA database. So if this is 120 seconds, we look 60 seconds either side. If more than one row is found, we use the closest
Payload_Min_Valid_Value	If after a calculation, a payload (TONNE) is less than this, we log a message and then use this value as the payload (typically 0)
ICA_Database_ConnectionString	ICA (PostgreSQL) database connection string
ICA_Database_TableName	Table name in the ICA database
PWB_Database_ConnectionString	Phosagro Weighbridge database Connection string
PWB_Database_TableName	Table name for the WB database If blank, uses the default table (mvans)
Local_Database_ConnectionString	Local database Connection string. Can be SQL compact (default) or SQL server Example strings 1. SQL compact: Data Source=C:\ProgramData\Micromine\PWBC\PWBCLocalDatabase.sdf;Persist Security Info=False; 2. SQL Server: Server=localhost;Port=3306;Database= wdb_disk;Uid=root;Pwd=password;
Local_Database_TableName	Table name for the local database. If blank, uses the default table (localStore)
Local_Database_Hours_To_Keep_ProcessedData	How many hours to keep processed data in the local store (data marked as processed will be deleted after this time)
Polling_Frequency_Seconds	The connecting polling frequency
System_Errors_Timeout_Minutes	(Inherited from the "Base" system) The amount of time application tries to recover from system (for example connectivity) errors before managing them as unrecoverable. Default is 10 minutes
PRIS_Server	Location of PRIS
TraceLevel	Trace level. Possible settings: Off, Verbose (i.e. debug/all), Information, Warning, Error
Email (section)	Not used by this connector (at least yet)

The connector has three database connections:

1. Phosagro weighbridge database (WBDB) the primary source of data.
2. ImPact Communications Appliance (ICA) Database

(used to find what equipment is at a Weighbridge using the time from the Weigh Bridge database (WBDB) and an ICA identifier mapped to a “Scales” attribute in the reference data which identifies the Weighbridge.)



3. A Local database - used by the connector for persistent information processing.

These can be configured in GeneralConfiguration.xml. Here you can set the connection string and the table names to be used for each of the above.

Local Database

This has a single table, localStore (by default). This table is used to first copy in data from the weighbridge database, and then used as a "working scratchpad" to then collect data from ICA and Pitram.

This can be either a compact database (supplied with the installation) or any SQL database that the connection string is configured for. In this case the table will need to be created.

The script for this is as follows:

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo]. [localStore]( [Id] [int] NOT NULL, [Trnum] [int] NOT NULL, [Num] [int] NOT NULL, [Scales] [int] NOT NULL, [BwTime] [int] NOT NULL, [Brutto] [int] NOT NULL, [Status] [int] NOT NULL, [IcaSysId] [int] NOT NULL, [Equipment] [nvarchar] (50) NULL, [CycleStatus] [nvarchar] (50) NULL, [IcaExciterTime] [nvarchar] (50) NULL, [ProcessedState] [int] NULL, [TimeLogged] [nvarchar] (50) NULL, CONSTRAINT [PK_localStore] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF , IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY] ) ON [PRIMARY] GO

The fields within the local database are the relevant fields for the WBDB, plus some others as described below:

Field Name	Purpose
Id	Id from the PWB database
Trnum	Trnum from the PWB database (this identifies a single train instance at a weighbridge)
Num	Num from the PWB database (this identifies each wagon that belongs to trnum)
Scales	Scales from the PWB database (id of the weighbridge)
Bwtime	Bwtime from the PWB database (this is the time we use as the "weighing time"
Brutto	Brutto from the PWB database (weight in Kgs)
Status	Status from the PWB database
IcaSysId	Sys Id of an exciter location we read from the ICA database, via a mapping for the scales field (path_nearest_location_id)
Equipment	Equipment code queried from Pitram via themobile_entity_id from the ICA database
CycleStatus	Cycle status from Pitram of the (Train) Equipment
IcaExciterTime	The time of the first wagon was weighed for a train (i.e. time for num=1 for each trnum)
ProcessedState	Where the connector is up to with the processing (see later) 0 to 3: 0: Unprocessed, 1: WaitingSysIdMapping, 2: ReadyToMatch, 3: Processed
TimeLogged	The time the data is first copied from the Weighbridge database to this local database

Tracing can be configured via GeneralConfiguration.xml, via the <TraceLevel> node. Settings are as follows:

Trace Levels
Off
Verbose
Information
Warning
Error

For in-house testing, it is recommend that the level be set to Verbose using a filter as described below.

The tracing (at the moment) for some reason always prints out a header row "PWB-Pitram Connector Verbose: 0 ". Since our tool of choice (DebugView) can only filter on a single line, we need to include PWB-Pitram Connector on each line. The other header line is then just noise so we exclude it by configuring the DebugView filter as follows:

Include Exclude PWB-Pitram Connector PWB-Pitram Connector Verbose:0 :

For each step below, we use the value of the field ProcessedState to indicate where we are in the processing stage. Knowing this, we do the following to get data from the Weighbridge Database (WBDB), and then go on to try and fill out the other fields of the local database:

1. Get any new data from the WBDB.

Get new any new data from the Weighbridge database (where id >= id from marker file), and copy info local database. Fills in just the fields from the WB database, and logged time) ProcessedState field = Unprocessed (0)

2. Get any newly matching data from the ICA database.
1. Get Exciter Id

Try and match Unprocessed rows with ICA Data. From 1, we have Bwtime, and Scales. From Scales, we look in the reference group Exciters, and find an Exciter code that has an attribute = to the scales value. This exciters code will be the value of the ICA path_nearest_location_id.

2. Get weighing time of a trains first wagon

For each train in the WB data (ie for each trnum), we want the wagon with the ealiest bwtime (which is always going to be row with num = 1)

We now look for any rows in ICA database that match the following using results from a) and b)

ICA Rows = get rows where path_nearest_location_id = Exciter code found above AND position_time = time found in b) +/- ICA_LookupTime_Tolerance_Seconds (from general config)

If we find more than one row, we use the ones with the closest time to the bwtime time.

IF we find the above match we fill in IcaSysId (from mobile_entity_id from the ICA database) and IcaExciterTime (the position_time from the ICA database) in the local database data *for all records (wagons)* for that train. Set processedState to WaitingSysIdMapping (1)

3. Look for Equipment code and Cycle status data from Pitram (via PRIS)
1. From the mobile_entity_id found above, search for a Pitram equipment code via it's MstId attribute mapping (reference data).
2. Once we have the equipment code, look up the Cycle status of the equipment at the IcaExciterTime

If Cycle Status = Hauling or Traveling empty,

set processedState to ReadyToMatch (2)

else

processedState to Processed (3) (incorrect cycle status, we can do no more with these records - message logged)

4. Match Traveling Empty rows with Hauling Rows

For any rows where the ProcessedState = ReadyToMatch (2):

1. Get the each group of Traveling Empty Rows (sorted by ID hence time)
2. Using the equipment code for the Traveling empty rows, look for the most recent set of rows with the same equipment id and with cycle status Hauling

If no hauling rows,

mark all the traveling empty rows (for this train) processed (if there are no hauling rows, there never will be)

else

Check status codes and submit any payload events for matched rows (with measure always TONNE, as we convert kgs to TONNE)

Look for any previous hauling rows - mark as processed (as we don't want these to be matched with any later traveling empty rows)

Below is a list of messages that will be logged into the Windows Event Log (and will also be traced if tracing is enabled at the appropriate level):

• All messages have the PWBC_ prefix
• All status messages are in the 0200s
• Status messages will also be sent to a new pane in Data Acquisition via the general services message logging system

Code	Message	Comments
PWBC_0001	File not found: '{0}'.	{0}: path to file
PWBC_0002	Error reading marker file position information from the field '{0}'.	{0}: marker field name
PWBC_0003	Error writing the marker file position information to the file '{0}'. Error: {1}	{0}: file path {1}: exception message
PWBC_0004	Could not find a mapping of the following weighbridge scales to ICA exciter Ids: ‘{0}’.	{0}:list of unmapped scales
PWBC_0005	Could not find a mapping to Pitram equipment for the following ICA Sys Ids: ‘{0}’.	{0}:list of unmapped sys ids
PWBC_0006	Error while processing ICA information: {0}.	{0}: exception message
PWBC_0007	Error getting cycle status for equipment '{0}' from PRIS: '{1}'.	{0}: equipment code, {1} error from PRIS
PWBC_0008	PRIS reported the equipment '{0}' was not found in reference data while querying for it's cycle status. Equipment skipped and processing resumed for other equipment.	{0}: equipment code
PWBC_0009	Error getting cycles states for equipment from PRIS. Able to process {0}/{1} weighbridge data rows. Error: {2}.	{0}: number of equipment successfully processed, {1}: total number to process, {2} exception message
PWBC_0010	The following weighbridge records could not be processed due to an incorrect cycle status being set for the equipment…{0}{1}{0}	{0}: double line feed, {1} list
PWBC_0011	The following weighbridge records where the equipment were found to be in the traveling empty cycle status, could not be processed due no matching hauling records …{0}{1}{0}	{0}: double line feed, {1} list
PWBC_0012	The following weighbridge records where the equipment were found to be in the hauling cycle status, could not be processed due no matching traveling empty records …{0}{1}{0}	{0}: double line feed, {1} list
PWBC_0013	Not all rows for the equipment ‘{0}’ while traveling empty could be matched with hauling rows. {1} out of {2} rows could be processed. Id of first hauling row was {3}, Id of first traveling empty row is {4}. Unmatched traveling empty rows are: {5}{6}{5}	{0}: equipment code, {1}: rows hauling, {2}: rows travelling empty, {3}: WBDB Id of first hauling row, {4}: WBDB Id of first travelling empty row, {5}: double line feed, {6}: list of unmatched rows
PWBC_0014	Did not get expected number of movements to match payload event. Expected {0}, but found {1}.	{0}: expected movement count, {1} found movement count.
PWBC_0015	Calculated payload value of {0} {1} at {2} for equipment {3} was below the minimum value of {4}. Adjusted up to {4}.	{0}: payload value, {1}: measure code, {2}: formatted datetime, {3}: equipment, {4}: min allowed measure value
PWBC_0016	Database marker value {0} found to be the maximum Id or {1} in the local database. Marker position adjusted to {1}.	{0}:marker position, {1} local database max position (value of id field)
PWBC_0200	{0} {1}, wagon {2}: Not weighed.	{0}: formatted time string(s), {1}: equipment, {2}: wagon number
PWBC_0201	{0} {1}, wagon {2}: Data has been edited in editor.	{0}: formatted time string(s), {1}: equipment, {2}: wagon number
PWBC_0202	{0} {1}, wagon {2}: Long stop during weighing.	{0}: formatted time string(s), {1}: equipment, {2}: wagon number
PWBC_0203	{0} {1}, wagon {2}: Direction change during weighing.	{0}: formatted time string(s), {1}: equipment, {2}: wagon number
PWBC_0204	{0} {1}, wagon {2}: Speeding during weighing.	{0}: formatted time string(s), {1}: equipment, {2}: wagon number
PWBC_0205	{0} {1}, wagon {2}: Excess acceleration during weighing.	{0}: formatted time string(s), {1}: equipment, {2}: wagon number
PWBC_0206	{0} {1}, wagon {2}: Unsatisfactory quality of weighing.	{0}: formatted time string(s), {1}: equipment, {2}: wagon number
PWBC_0207	{0} {1}, wagon {2}: Exceeded scales weight limit.	{0}: formatted time string(s), {1}: equipment, {2}: wagon number
PWBC_0208	{0} {1}, wagon {2}: Overloaded.	{0}: formatted time string(s), {1}: equipment, {2}: wagon number
PWBC_0209	{0} {1}, wagon {2}: Underloaded.	{0}: formatted time string(s), {1}: equipment, {2}: wagon number