Setting up the DB Tables for the Master Controller

['Neal Singh]

 

Setting up the DB Tables for the Master Controller

These scripts will create the tables for managing of the Master Controller via the MQTT. There are probably more tables required, but these are the ones required to manage the devices on site

----------------------------------------------------------------------------------------------------------------------------------

USE [MC_MemoryMaps]

GO

/****** Object:  Schema [FRam]    Script Date: 24/06/2019 09:57:22 PM ******/

CREATE SCHEMA [FRam]

GO

 

/****** Object:  Table [FRam].[INITIALIZATION_TABLE]    Script Date: 23/06/2019 03:03:39 PM ******/

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

/* This table creates that would be assigned the SITEID for the 16 BYTE UNIQUE ID uploaded from the Master Controller */

CREATE TABLE [FRam].[SITE_REGISTRATION](

               [UNIQUE_SITE_ID] [nchar](16) NULL,

                [SiteID] [int] NULL,

                [MapVersion] [nchar](10) NULL,

               [REGISTRATION_Date] [datetime] NOT NULL,

               [SITEID_INIT_Date] [datetime] NOT NULL,

               [USER_ID] [nvarchar](50) NULL,

               [INSTALLER_ID] [nvarchar](50) NULL,

                [Descriptor] [nvarchar](50) NULL,

) ON [PRIMARY]

GO

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

/* This is the Master Table that holds the INIT MAP for all new Master Controller */

CREATE TABLE [FRam].[MASTER_INITIALIZATION_TABLE](

                [MapVersion] [nchar](10) NULL,

                [SeqNoPer Version] [int] NULL,

                [MemoryADR] [int] NULL,

                [Descriptor] [nvarchar](50) NULL,

                [BytesStored] [int] NULL,

                [DataStored] [int] NULL,

) ON [PRIMARY]

GO

 

 

/* This is the Table that gets loaded with the default setup from Master Table that holds the INIT MAP for all new Master Controller. Here adjustments can be made and pushed to Site */

CREATE TABLE [FRam].[SITE_INITIALIZATION_TABLE](

                [MapVersion] [nchar](10) NULL,

                [SiteID] [int] NULL,

                [SeqNoPer Version] [int] NULL,

                [MemoryADR] [int] NULL,

                [Descriptor] [nvarchar](50) NULL,

                [BytesStored] [int] NULL,

                [DataStored] [int] NULL,

                [Change Status] [nchar](10) NULL,

                [ActionStatus] [nchar](10) NULL

) ON [PRIMARY]

GO

 

 

 

 

 

 

 

 

 

 

 

 

 

 

USE [MC_MemoryMaps]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

/* This is the Master Table that holds the Addresses for all new Command Code */

CREATE TABLE [FRam].[MASTER_COMMAND_ADDR](

                [MapVersion] [nchar](10) NULL,

                [Command_Code] [int] NULL,

                [SeqNOperCommand] [int] NULL,

                [MemoryADR] [int] NULL,           

                [BytesStored] [int] NULL,

                [DataADDRStored] [int] NULL,

                ) ON [PRIMARY]

GO

GO

 

/* This table is loaded from the Master Table that holds the Addresses for all new Command Code */

 

CREATE TABLE [FRam].[SITE_COMMAND_ADDR](

                [MapVersion] [nchar](10) NULL,

                [Command_Code] [int] NULL,

                [SeqNOperCommand] [int] NULL,

                [MemoryADR] [int] NULL,           

                [BytesStored] [int] NULL,

                [DataADDRStored] [int] NULL,

                [Change Status] [nchar](10) NULL,

                [ActionStatus] [nchar](10) NULL

                ) ON [PRIMARY]

GO

 

 

 

 

 

 

 

 

 

 

 

/* This table that holds the data relating to the business and basic setup data */

 

CREATE TABLE [FRam].[SITE_DETAIL_0x20](

                [MapVersion] [nchar](10) NULL,

                [SiteID] [int] NULL,

                [SITE_NAME] [nvarchar](20) NULL,

                [TAX_NO] [nvarchar](12) NULL,               

              [PHONE_NO] [nvarchar](15) NULL,

                [MASTER_CONTROLLER_No] [int] NULL,

                [SINGLE_GROUP] [int] NULL,

                [DEVICE_MODE] [int] NULL,

                [LANGUAGE] [int] NULL,

                [PacketLen] [int] NULL,

                [Descriptor] [nvarchar](50) NULL,

                [Change Status] [nchar](10) NULL,

                [ActionStatus] [nchar](10) NULL

) ON [PRIMARY]

GO                        

               

/* Holds the start address the messages displayed on the LCD: Allows for multiple languages */

 

CREATE TABLE [FRam].[LANGUAGE_0x21](

                [MapVersion] [nchar](10) NULL,

                [SiteID] [int] NULL,

                [Command_Code] [int] NULL,

                [SeqNoPerEntity] [int] NULL,

                [LANGUAGE_ADR] [int] NULL,

                [PacketLen] [int] NULL,

                [Descriptor] [nvarchar](50) NULL,

                [Change Status] [nchar](10) NULL,

                [ActionStatus] [nchar](10) NULL

) ON [PRIMARY]

GO

 

 

 

 

 

 

 

 

 

 

 

 

/* Each DeviceCode represents a Uart Device per Sequence Uart# */

 

CREATE TABLE [FRam].[UARTDEVICE_0x22](

                [MapVersion] [nchar](10) NULL,

                [SiteID] [int] NULL,

                [Command_Code] [int] NULL,

                [SeqNoPerEntity] [int] NULL,

                [UARTDEVICE_CODE] [int] NULL,

                [PacketLen] [int] NULL,

                [Descriptor] [nvarchar](50) NULL,

                [Change Status] [nchar](10) NULL,

                [ActionStatus] [nchar](10) NULL

) ON [PRIMARY]

GO

 

/* Each DeviceCode has settings to control the Uart */

CREATE TABLE [FRam].[DEVICE_PARAMETER_0x23](

                [MapVersion] [nchar](10) NULL,

                [SiteID] [int] NULL,

                [Command_Code] [int] NULL,

                [SeqNoPerEntity] [int] NULL,

                [UARTDEVICE_CODE] [int] NULL,

                [BAUDCODE] [int] NULL,

                [SERIALTYPE] [int] NULL,

                [POLLING_DELAY] [int] NULL,

                [READ_DELAY] [int] NULL,

                [RESPONSE_LENGTH] [int] NULL,

                [SPARECtrl1] [int] NULL,

                [SPARECtrl2] [int] NULL,

                [SPARECtrl3] [int] NULL,

                [PacketLen] [int] NULL,

                [Descriptor] [nvarchar](50) NULL,

                [Change Status] [nchar](10) NULL,

                [ActionStatus] [nchar](10) NULL

) ON [PRIMARY]

GO

 

 

 

 

 

 

 

 

 

/* General Device Setup*/

CREATE TABLE [FRam].[PERFORMANCE_PARAMETER_0x24](

                [MapVersion] [nchar](10) NULL,

                [SiteID] [int] NULL,

                [Command_Code] [int] NULL,

                [SeqNoPerEntity] [int] NULL,

                [MIDS_CONX_TYPE] [int] NULL,

                [No_MIDS] [int] NULL,

                [No_PUMPS] [int] NULL,

                [No_NOZZLES] [int] NULL,

                [INST_PRINTER] [int] NULL,

                [INST_RFID_TAGGING] [int] NULL,

                [INST_NRT] [int] NULL,

                [INST_UHF_RFID] [int] NULL,

                [CAM_REC_SYS] [int] NULL,

                [ELECT_MAG_SYS] [int] NULL,

                [OTHER] [int] NULL,

                [HEARTBEAT_PERIOD_GEN] [int] NULL,

                [HEARTBEAT_PERIOD_ATG] [int] NULL,

                [HEARTBEAT_PERIOD_GPS] [int] NULL,

                [ATG_CHANGE_UPDATE] [int] NULL,

                [GPS_CHANGE_UPDATE] [int] NULL,

                [PacketLen] [int] NULL,

                [Descriptor] [nvarchar](50) NULL,

                [Change Status] [nchar](10) NULL,

                [ActionStatus] [nchar](10) NULL

) ON [PRIMARY]

GO

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

/* Configuration of the Pumps and the  control of peripherals devices*/

CREATE TABLE [FRam].[PUMP_SETUP _0x25](

                [MapVersion] [nchar](10) NULL,

                [SiteID] [int] NULL,

                [Command_Code] [int] NULL,

                [SeqNoPerEntity] [int] NULL,

                [LOGICAL_NOZ_No] [int] NULL,

                [MID_PHY_No] [int] NULL,

               [PUMP_PHY_No] [int] NULL,

               [PUMPTYPE] [int] NULL,

               [PROTOCOL_WRAPPED] [int] NULL,

               [NOZZLE_No] [int] NULL,

               [TAGGING] [int] NULL,

               [TAGGING_ACTIVE] [int] NULL,

               [UHF_TAGGING] [int] NULL,

               [UHF_ACTIVE] [int] NULL,

               [NRT] [int] NULL,

               [NRT_ACTIVE] [int] NULL,

               [CAM_REC_TECH] [int] NULL,

               [CRT_ACTIVE] [int] NULL,

               [CAMERA_OVERLAY] [int] NULL,

               [CAMERA_ACTIVE] [int] NULL,

               [COMPOSITE_TYPE] [int] NULL,

               [TANK] [int] NULL,

                [PacketLen] [int] NULL,

                [Descriptor] [nvarchar](50) NULL,

                [Change Status] [nchar](10) NULL,

                [ActionStatus] [nchar](10) NULL

) ON [PRIMARY]

GO

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

/*Pump Control Parameters */

CREATE TABLE [FRam].[PUMP_CONTROL_PARAMETER_0x26](

                [MapVersion] [nchar](10) NULL,

                [SiteID] [int] NULL,

                [Command_Code] [int] NULL,

                [SeqNoPerEntity] [int] NULL,

                [LOGICAL_NOZ_No] [int] NULL,

                [SALES_VALUE_LEFT] [int] NULL,

               [SALES_VALUE_RIGHT] [int] NULL,

               [SALES_LITER_LEFT] [int] NULL,

               [SALES_LITER_RIGHT] [int] NULL,

                [PREAUTHx10] [int] NULL,

                [PREAUTH/10] [int] NULL,

                [PRICESETx10] [int] NULL,

                [PRICESET/10] [int] NULL,

                [SALES_TOTALIZER_RETRY] [int] NULL,

                [PULSEFACTOR] [int] NULL,

                [PULSERTIMEOUT] [int] NULL,

                [PacketLen] [int] NULL,

                [Descriptor] [nvarchar](50) NULL,

                [Change Status] [nchar](10) NULL,

                [ActionStatus] [nchar](10) NULL

) ON [PRIMARY]

GO

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

/* Tank and ATG setup Properties*/

CREATE TABLE [FRam].[TANK_SETUP_0x27](

                [MapVersion] [nchar](10) NULL,

                [SiteID] [int] NULL,

                [Command_Code] [int] NULL,

                [SeqNoPerEntity] [int] NULL,

                [TANK_ID] [int] NULL,

                [TANK_SIZE] [int] NULL,

               [REORDER_LEVEL] [int] NULL,

               [FUEL_TYPE] [int] NULL,

               [FUEL_GRADE] [int] NULL,

               [ATG_TYPE] [int] NULL,

               [ATG_ID] [int] NULL,

               [ATG_POSITION] [int] NULL,

                [PacketLen] [int] NULL,

                [Descriptor] [nvarchar](50) NULL,

                [Change Status] [nchar](10) NULL,

                [ActionStatus] [nchar](10) NULL

) ON [PRIMARY]

GO

 

 

/* Fuel Price Control */

CREATE TABLE [FRam].[GRADE_PRICE_0x28](

                [MapVersion] [nchar](10) NULL,

                [SiteID] [int] NULL,

                [Command_Code] [int] NULL,

                [SeqNoPerEntity] [int] NULL,

                [GRADECODE] [int] NULL,

                [PRICE] [int] NULL,

               [FUEL_GRADE] [int] NULL,

               [FUEL_TYPE] [int] NULL,

                [PacketLen] [int] NULL,

                [Descriptor] [nvarchar](50) NULL,

                [Change Status] [nchar](10) NULL,

                [ActionStatus] [nchar](10) NULL

) ON [PRIMARY]

GO

 

 

 

 

 

 

 

/* The Tasks in uC can be controlled via these parameters */

CREATE TABLE [FRam].[TASK_MANAGEMENT_0x29](

                [MapVersion] [nchar](10) NULL,

                [SiteID] [int] NULL,

                [Command_Code] [int] NULL,

                [SeqNoPerEntity] [int] NULL,

                [TASK_ID] [int] NULL,

                [TASK_DELAY] [int] NULL,

               [TASK_PRIORITY] [int] NULL,

                [PacketLen] [int] NULL,

                [Descriptor] [nvarchar](50) NULL,

                [Change Status] [nchar](10) NULL,

                [ActionStatus] [nchar](10) NULL

) ON [PRIMARY]

GO

 

/* Controls the Process and sequence of Fuelling */

CREATE TABLE [FRam].[FUELLING_SEQUENCE_0x2A](

                [MapVersion] [nchar](10) NULL,

                [SiteID] [int] NULL,

                [Command_Code] [int] NULL,

                [SeqNoPerEntity] [int] NULL,

                [FUEL_SEQ_No] [int] NULL,

                [GET_NRT_TAG] [int] NULL,

               [GET_CAM_LIC_PLATE] [int] NULL,

                [GET_UHF] [int] NULL,

                [GET_DRIVER_TAG] [int] NULL,

                [GET_DRIVER_PIN] [int] NULL,

                [DRIVER_SEQ] [int] NULL,

                [GET_ODOMETER] [int] NULL,

                [ODOMETER_SEQ] [int] NULL,

                [GET_HOUR_METER] [int] NULL,

                [HOUR_METER_SEQ] [int] NULL,

                [GET_ATT_TAG] [int] NULL,

                [ATT_TAG_SEQ] [int] NULL,

              [GET_PURPOSE_CODE] [int] NULL,

                [PURPOSE_CODE__SEQ] [int] NULL,

                [PacketLen] [int] NULL,

                [Descriptor] [nvarchar](50) NULL,

                [Change Status] [nchar](10) NULL,

                [ActionStatus] [nchar](10) NULL

) ON [PRIMARY]

GO

 

 

/* Loads the Attendant Details and the Tag Number assigned*/

 

CREATE TABLE [FRam].[ATTENDANT_INFO_0x51](

       [MapVersion] [nchar](10) NULL,

       [SiteID] [int] NULL,

       [Command_Code] [int] NULL,

       [SeqNoPerEntity] [int] NULL,

       [ATT_INDEX] [int] NULL,

       [TAG_NUMBER] [nchar](12) NULL,

       [TAG_TYPE] [nchar](2) NULL,

       [TAG_STATUS] [int] NULL,

       [ATTENDANT_NAME] [nchar](12) NULL,

       [EMPLOYEE_No] [int] NULL,

       [RIGHTS] [int] NULL,

       [PacketLen] [int] NULL,

       [Descriptor] [nvarchar](50) NULL,

       [Change Status] [nchar](10) NULL,

       [ActionStatus] [nchar](10) NULL

) ON [PRIMARY]

GO

 

/* Loads the Account Details */

 

CREATE TABLE [FRam].[ACCOUNT_INFO_0x52](

       [MapVersion] [nchar](10) NULL,

       [SiteID] [int] NULL,

       [Command_Code] [int] NULL,

       [SeqNoPerEntity] [int] NULL,

       [ACC_INDEX] [int] NULL,

       [ACCOUNT_CODE] [int] NULL,

       [ACCOUNT_NAME] [nchar](15) NULL,

       [ACCOUNT_TYPE] [int] NULL,

       [AUTH_LEVEL] [int] NULL,

       [PacketLen] [int] NULL,

       [Descriptor] [nvarchar](50) NULL,

       [Change Status] [nchar](10) NULL,

       [ActionStatus] [nchar](10) NULL

) ON [PRIMARY]

GO

 

 

/* Loads the Account Balance */

CREATE TABLE [FRam].[ACCOUNT_BALANCE_0x53](

       [MapVersion] [nchar](10) NULL,

       [SiteID] [int] NULL,

       [Command_Code] [int] NULL,

       [SeqNoPerEntity] [int] NULL,

       [ACC_INDEX] [int] NULL,

       [BALANCE_AVAILABLE] [int] NULL,

       [AMOUNT_USED] [int] NULL,

       [LITER_OR_VALUE] [int] NULL,

       [PacketLen] [int] NULL,

       [Descriptor] [nvarchar](50) NULL,

       [Change Status] [nchar](10) NULL,

       [ActionStatus] [nchar](10) NULL

) ON [PRIMARY]

GO

 

 

/* Loads the Driver Details and the Tag Number assigned*/

CREATE TABLE [FRam].[DRIVER_INFO_0x54](

       [MapVersion] [nchar](10) NULL,

       [SiteID] [int] NULL,

       [Command_Code] [int] NULL,

       [SeqNoPerEntity] [int] NULL,

       [DRIVER_INDEX] [int] NULL,

       [TAG_NUMBER] [nchar](12) NULL,

       [TAG_TYPE] [nchar](2) NULL,

       [TAG_STATUS] [int] NULL,

       [DRIVER_LOGICAL_No] [int] NULL,

       [ACCOUNT_CODE] [int] NULL,

       [EMPLOYEE_No] [int] NULL,

       [DRIVER_NAME] [nchar](12) NULL,

       [DRIVER_PIN] [int] NULL,

       [FUELLING_RIGHTS] [int] NULL,

       [ACC_INDEX] [int] NULL,

       [PacketLen] [int] NULL,

       [Descriptor] [nvarchar](50) NULL,

       [Change Status] [nchar](10) NULL,

       [ActionStatus] [nchar](10) NULL

) ON [PRIMARY]

GO

 

/* Loads the Driver Sub-account Balance*/

CREATE TABLE [FRam].[DRIVER_BALANCE_0x55](

       [MapVersion] [nchar](10) NULL,

       [SiteID] [int] NULL,

       [Command_Code] [int] NULL,

       [SeqNoPerEntity] [int] NULL,

       [DRIVER_INDEX] [int] NULL,

       [BALANCE_AVAILABLE] [int] NULL,

       [AMOUNT_USED] [int] NULL,

       [LITER_OR_VALUE] [int] NULL,

       [ACC_INDEX] [int] NULL,

       [PacketLen] [int] NULL,

       [Descriptor] [nvarchar](50) NULL,

       [Change Status] [nchar](10) NULL,

       [ActionStatus] [nchar](10) NULL

) ON [PRIMARY]

GO

 

 

 

 

 

 

 

 

 

/* Loads the Vehicle Details and the Tag Number assigned*/

 

CREATE TABLE [FRam].[VEHICLE_INFO_0x56](

       [MapVersion] [nchar](10) NULL,

       [SiteID] [int] NULL,

       [Command_Code] [int] NULL,

       [SeqNoPerEntity] [int] NULL,

       [VEHICLE_INDEX] [int] NULL,

       [TAG_NUMBER] [nchar](12) NULL,

       [TAG_TYPE] [nchar](2) NULL,

       [TAG_STATUS] [int] NULL,

       [ACCOUNT_CODE] [int] NULL,

       [REGISTRATION] [nchar](12) NULL,

       [DESCRIPTION] [nchar](15) NULL,

       [FUELLING_MODE] [int] NULL,

       [FUELLING_RIGHTS] [int] NULL,

       [FUEL_GRADE] [int] NULL,

       [FUEL_TYPE] [int] NULL,

       [STATUS] [int] NULL,

       [LITERSPer100KM] [int] NULL,

       [MIN_HOURS_BEFORE_FILL] [int] NULL,

       [MIN_KMs_BEFORE_FILL] [int] NULL,

       [TANK_CAPACITY] [int] NULL,

       [ACC_INDEX] [int] NULL,

       [PacketLen] [int] NULL,

       [Descriptor] [nvarchar](50) NULL,

       [Change Status] [nchar](10) NULL,

       [ActionStatus] [nchar](10) NULL

) ON [PRIMARY]

GO

 

/* Loads the Vehicle Sub-account Balance*/

CREATE TABLE [FRam].[VEHICLE_BALANCE_0x57](

       [MapVersion] [nchar](10) NULL,

       [SiteID] [int] NULL,

       [Command_Code] [int] NULL,

       [SeqNoPerEntity] [int] NULL,

       [VEHICLE_INDEX] [int] NULL,

       [BALANCE_AVAILABLE] [int] NULL,

       [AMOUNT_USED] [int] NULL,

       [LITER_OR_VALUE] [int] NULL,

       [LAST_ODOMETER] [int] NULL,

       [ACC_INDEX] [int] NULL,

       [PacketLen] [int] NULL,

       [Descriptor] [nvarchar](50) NULL,

       [Change Status] [nchar](10) NULL,

       [ActionStatus] [nchar](10) NULL

) ON [PRIMARY]

GO