---- 1. alxe congfiguration table defeinition changed
--delete from tms.tlAxleConfiguration
--DBCC CHECKIDENT ('tms.tlAxleConfiguration', RESEED, 0)

--drop table tms.tlAxleConfiguration
--CREATE TABLE [TMS].[tlAxleConfiguration]
--(
--	[ID] [int] primary key IDENTITY(1,1) NOT NULL,
--	[Code] [int] NOT NULL,
--	[NoofTyres] [int] NOT NULL,
--	[NoofLoads] [int] NULL,
--	[FormulaToBeUsed] [nvarchar](max) NULL,
--	[Status] [bit] NULL,
--	[IsDeleted] [bit] NULL,
--	[AddedBy] [nvarchar](50) NULL,
--	[AddedOn] [datetime] NULL,
--	[ModifiedBy] [nvarchar](50) NULL,
--	[ModifiedOn] [datetime] NULL,
--	[clr] [nvarchar](10) NULL,
--	[iconpath] [nvarchar](250) NULL
--)

---- =====================================================

---- created new table tms.tlStationType
--create table TMS.tlStationType
--(
--	ID int primary key identity(1,1)
--	, Code nvarchar(10) NOT NULL
--	, [Name] nvarchar(100) NOT NULL
--	, [Description] nvarchar(max) NOT NULL
--	, [Status] bit default(1) NULL
--	, [IsDeleted] bit default(0) NULL
--	, AddedBy nvarchar(100) NULL
--	, AddedOn datetime NULL
--	, ModifiedBy nvarchar(100) NULL
--	, ModifiedOn datetime NULL
--)
--insert into TMS.tlStationType(Code, [Name], [Description]) values('TVC-M', 'TVC-Manual', 'Manual Counting of TVC')
--insert into TMS.tlStationType(Code, [Name], [Description]) values('ATCC', 'TVC-Automatic', 'Automatic Counting of TVC')
--insert into TMS.tlStationType(Code, [Name], [Description]) values('Axle-M', 'Axle-Manual', 'Manual Counting of Axle')
--insert into TMS.tlStationType(Code, [Name], [Description]) values('WB', 'Axle-Weighbridge', 'Automatic Counting of Axle')

--ALTER TABLE [TMS].[tdTrafficStation] ADD  CONSTRAINT [DF__tdTraffic__Stati__695FCAFD]  DEFAULT ((1)) FOR [StationTypeID]

---- =======================================

---- added new column SurveyInterval in the TVC table and we need to fetch this data from sysadm.tsTimePeriod table
--ALTER TABLE [TMS].[tdTrafficVolumeCount]  
--drop  CONSTRAINT [FK_tdTrafficVolumeCount_tsTimePeriod]
--drop table sysadm.tsTimePeriod

--create TABLE [TMS].[tlTimePeriod]
--(
--	[ID] [int] IDENTITY(1,1) NOT NULL,
--	[Interval] int NOT NULL,
--	[Code] [nvarchar](13) NOT NULL,
--	[Status] [bit] NULL,
--	[IsDeleted] [bit] NULL,
--	[AddedBy] [nvarchar](100) NULL,
--	[AddedOn] [datetime] NULL,
--	[ModifiedBy] [nvarchar](100) NULL,
--	[ModifiedOn] [datetime] NULL,
--	CONSTRAINT [PK_tlTimePeriod] PRIMARY KEY CLUSTERED 
--(
--	[Interval] ASC,
--	[Code] ASC
--)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
--) ON [PRIMARY]

--insert into TMS.tlTimePeriod(Interval, Code) values (60, '00:00 - 01:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (60, '01:00 - 02:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (60, '02:00 - 03:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (60, '03:00 - 04:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (60, '04:00 - 05:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (60, '05:00 - 06:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (60, '06:00 - 07:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (60, '07:00 - 08:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (60, '08:00 - 09:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (60, '09:00 - 10:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (60, '10:00 - 11:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (60, '11:00 - 12:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (60, '12:00 - 13:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (60, '13:00 - 14:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (60, '14:00 - 15:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (60, '15:00 - 16:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (60, '16:00 - 17:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (60, '17:00 - 18:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (60, '18:00 - 19:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (60, '19:00 - 20:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (60, '20:00 - 21:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (60, '21:00 - 22:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (60, '22:00 - 23:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (60, '23:00 - 00:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '00:00 - 00:30')
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '00:30 - 01:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '01:00 - 01:30')
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '01:30 - 02:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '02:00 - 02:30')
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '02:30 - 03:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '03:00 - 03:30')
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '03:30 - 04:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '04:00 - 04:30')
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '04:30 - 05:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '05:00 - 05:30')
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '05:30 - 06:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '06:00 - 06:30')
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '06:30 - 07:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '07:00 - 07:30')
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '07:30 - 08:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '08:00 - 08:30')
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '08:30 - 09:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '09:00 - 09:30')
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '09:30 - 10:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '10:00 - 10:30')
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '10:30 - 11:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '11:00 - 11:30')
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '11:30 - 12:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '12:00 - 12:30')
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '12:30 - 13:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '13:00 - 13:30')
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '13:30 - 14:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '14:00 - 14:30')
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '14:30 - 15:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '15:00 - 15:30')
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '15:30 - 16:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '16:00 - 16:30')
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '16:30 - 17:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '17:00 - 17:30')
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '17:30 - 18:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '18:00 - 18:30')
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '18:30 - 19:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '19:00 - 19:30')
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '19:30 - 20:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '20:00 - 20:30')
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '20:30 - 21:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '21:00 - 21:30')
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '21:30 - 22:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '22:00 - 22:30')
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '22:30 - 23:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '23:00 - 23:30')
--insert into TMS.tlTimePeriod(Interval, Code) values (30, '23:30 - 00:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '00:00 - 00:15')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '00:15 - 00:30')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '00:30 - 00:45')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '00:45 - 01:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '01:00 - 01:15')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '01:15 - 01:30')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '01:30 - 01:45')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '01:45 - 02:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '02:00 - 02:15')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '02:15 - 02:30')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '02:30 - 02:45')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '02:45 - 03:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '03:00 - 03:15')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '03:15 - 03:30')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '03:30 - 03:45')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '03:45 - 04:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '04:00 - 04:15')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '04:15 - 04:30')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '04:30 - 04:45')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '04:45 - 05:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '05:00 - 05:15')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '05:15 - 05:30')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '05:30 - 05:45')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '05:45 - 06:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '06:00 - 06:15')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '06:15 - 06:30')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '06:30 - 06:45')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '06:45 - 07:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '07:00 - 07:15')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '07:15 - 07:30')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '07:30 - 07:45')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '07:45 - 08:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '08:00 - 08:15')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '08:15 - 08:30')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '08:30 - 08:45')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '08:45 - 09:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '09:00 - 09:15')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '09:15 - 09:30')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '09:30 - 09:45')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '09:45 - 10:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '10:00 - 10:15')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '10:15 - 10:30')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '10:30 - 10:45')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '10:45 - 11:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '11:00 - 11:15')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '11:15 - 11:30')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '11:30 - 11:45')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '11:45 - 12:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '12:00 - 12:15')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '12:15 - 12:30')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '12:30 - 12:45')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '12:45 - 13:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '13:00 - 13:15')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '13:15 - 13:30')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '13:30 - 13:45')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '13:45 - 14:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '14:00 - 14:15')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '14:15 - 14:30')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '14:30 - 14:45')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '14:45 - 15:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '15:00 - 15:15')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '15:15 - 15:30')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '15:30 - 15:45')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '15:45 - 16:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '16:00 - 16:15')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '16:15 - 16:30')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '16:30 - 16:45')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '16:45 - 17:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '17:00 - 17:15')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '17:15 - 17:30')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '17:30 - 17:45')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '17:45 - 18:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '18:00 - 18:15')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '18:15 - 18:30')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '18:30 - 18:45')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '18:45 - 19:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '19:00 - 19:15')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '19:15 - 19:30')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '19:30 - 19:45')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '19:45 - 20:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '20:00 - 20:15')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '20:15 - 20:30')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '20:30 - 20:45')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '20:45 - 21:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '21:00 - 21:15')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '21:15 - 21:30')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '21:30 - 21:45')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '21:45 - 22:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '22:00 - 22:15')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '22:15 - 22:30')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '22:30 - 22:45')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '22:45 - 23:00')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '23:00 - 23:15')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '23:15 - 23:30')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '23:30 - 23:45')
--insert into TMS.tlTimePeriod(Interval, Code) values (15, '23:45 - 00:00')

--alter table TMS.tdTrafficVolumeCount
--add SurveyInterval int default(60) NOT NULL

--ALTER TABLE [TMS].[tdTrafficVolumeCount]  WITH CHECK 
--ADD  CONSTRAINT [FK_tdTrafficVolumeCount_tdTrafficVolumeCount] FOREIGN KEY([SurveyInterval], [Time])
--REFERENCES [TMS].[tlTimePeriod] ([Interval], [Code])

---- ======================================

---- change the data in commoditytype table
--delete from tms.tlCommodityType
--DBCC CHECKIDENT ('tms.tlCommodityType', RESEED, 0)

--insert into tms.tlCommodityType(Code, [Name], [Description]) values('TEX', 'Textiles & clothing', 'Textiles & clothing')
--insert into tms.tlCommodityType(Code, [Name], [Description]) values('FnV', 'Fruits and vegetables', 'Fruits and vegetables')
--insert into tms.tlCommodityType(Code, [Name], [Description]) values('BOK', 'Books', 'Books')
--insert into tms.tlCommodityType(Code, [Name], [Description]) values('BOT', 'Bottles', 'Bottles')
--insert into tms.tlCommodityType(Code, [Name], [Description]) values('BLD', 'Building materials', 'Building materials')
--insert into tms.tlCommodityType(Code, [Name], [Description]) values('FDG', 'Food grains', 'Food grains')
--insert into tms.tlCommodityType(Code, [Name], [Description]) values('CRA', 'Craters containing drinks', 'Craters containing drinks')
--insert into tms.tlCommodityType(Code, [Name], [Description]) values('ANI', 'Animals carrying', 'Animals carrying')
--insert into tms.tlCommodityType(Code, [Name], [Description]) values('FUL', 'Fuel related like Pretoleum products, oil, gas, etc.', 'Fuel related like Pretoleum products, oil, gas, etc.')
--insert into tms.tlCommodityType(Code, [Name], [Description]) values('FRN', 'Furnitures or wood products', 'Furnitures or wood products')
--insert into tms.tlCommodityType(Code, [Name], [Description]) values('IRN', 'Iron & steel', 'Iron & steel')
--insert into tms.tlCommodityType(Code, [Name], [Description]) values('MED', 'Medicines or Pharmaceutical Products', 'Medicines or Pharmaceutical Products')
--insert into tms.tlCommodityType(Code, [Name], [Description]) values('PAS', 'Passengers or Passenger vehicles', 'Passengers or Passenger vehicles')
--insert into tms.tlCommodityType(Code, [Name], [Description]) values('MAT', 'Materials like plastic, paint, solar equipments, stones, etc.', 'Materials like plastic, paint, solar equipments, stones, etc.')
--insert into tms.tlCommodityType(Code, [Name], [Description]) values('WTR', 'Water related items', 'Water related items')
--insert into tms.tlCommodityType(Code, [Name], [Description]) values('MIN', 'Minerals & ores', 'Minerals & ores')
--insert into tms.tlCommodityType(Code, [Name], [Description]) values('FERT', 'Fertilizers / Chemicals', 'Fertilizers / Chemicals')
--insert into tms.tlCommodityType(Code, [Name], [Description]) values('EMP', 'Empty', 'Empty')
--insert into tms.tlCommodityType(Code, [Name], [Description]) values('OTH', 'Others', 'Others')

---- =======================================

---- changes in the legalmaxload table
--truncate table tms.tlLegalMaxLoad
--drop table tms.tlLegalMaxLoad
--create table TMS.tlLegalMaxLoad
--(
--	ID int identity(1,1) primary key not null,
--	Code nvarchar(10) not null,
--	[Name] nvarchar(100) not null,
--	AxleConf int not null,
--	PermAxleLoad float not null,
--	NoofTyres int null,
--	NoofLoads int null,
--	DmgFactFormula nvarchar(max) null,
--	Remarks nvarchar(max) null,
--	[Status] [bit] NULL,
--	[IsDeleted] [bit] NULL,
--	[AddedBy] [nvarchar](50) NULL,
--	[AddedOn] [datetime] NULL,
--	[ModifiedBy] [nvarchar](50) NULL,
--	[ModifiedOn] [datetime] NULL,
--)

--insert into tms.tlLegalMaxLoad(Code, [Name], AxleConf, PermAxleLoad, NoofTyres, NoofLoads, DmgFactFormula) values('SASW', 'Single Axle - Single Wheel', 1, 8, 2, 1, '((2*Axle Load)/6626)^4')
--insert into tms.tlLegalMaxLoad(Code, [Name], AxleConf, PermAxleLoad, NoofTyres, NoofLoads, DmgFactFormula) values('SADW', 'Single Axle - Dual Wheel', 2, 10, 4, 1, '((2*Axle Load)/8155)^4')
--insert into tms.tlLegalMaxLoad(Code, [Name], AxleConf, PermAxleLoad, NoofTyres, NoofLoads, DmgFactFormula) values('TASW', 'Tandem Axle - Single Wheel', 11, 16, 2, 2, '((2* 1st Axle Load)/6626)^4 + ((2* 2nd Axle Load)/6626)^4')
--insert into tms.tlLegalMaxLoad(Code, [Name], AxleConf, PermAxleLoad, NoofTyres, NoofLoads, DmgFactFormula) values('TADW', 'Tandem Axle - Dual Wheel', 22, 18, 8, 2, '((2*(1st Axle Load + 2nd Axle Load))/15086)^4')
--insert into tms.tlLegalMaxLoad(Code, [Name], AxleConf, PermAxleLoad, NoofTyres, NoofLoads, DmgFactFormula) values('TRSW', 'Tridem Axle - Single Wheel', 111, 22.5, 6, 3, '((2* 1st Axle Load)/6626)^4 + ((2* 2nd Axle Load)/6626)^4 + ((2* 3rd Axle Load)/6626)^4')
--insert into tms.tlLegalMaxLoad(Code, [Name], AxleConf, PermAxleLoad, NoofTyres, NoofLoads, DmgFactFormula) values('TRDW', 'Tridem Axle - Dual Wheel', 222, 24, 12, 3, '((2* (1st Axle Load + 2nd Axle Load + 3rd Axle Load))/22834)^4')
--insert into tms.tlLegalMaxLoad(Code, [Name], AxleConf, PermAxleLoad, NoofTyres, NoofLoads, DmgFactFormula) values('SASW_SADW', '2 Axle', 12, 18, 6, 2, '((2* 1st Axle Load)/6626)^4 + ((2* 2nd Axle Load)/8155)^4')
--insert into tms.tlLegalMaxLoad(Code, [Name], AxleConf, PermAxleLoad, NoofTyres, NoofLoads, DmgFactFormula) values('SADW_SASW', '2 Axle', 21, 18, 6, 2, '((2* 1st Axle Load)/8155)^4 + ((2 * 2nd Axle Load)/6626)^4')
--insert into tms.tlLegalMaxLoad(Code, [Name], AxleConf, PermAxleLoad, NoofTyres, NoofLoads, DmgFactFormula) values('SASW_TADW', '3 Axle', 122, 26, 10, 3, '((2* 1st Axle Load)/6626)^4 + ((2*(2nd Axle Load + 3rd Axle Load))/15086)^4')
--insert into tms.tlLegalMaxLoad(Code, [Name], AxleConf, PermAxleLoad, NoofTyres, NoofLoads, DmgFactFormula) values('TADW_SASW', '3 Axle', 221, 26, 10, 3, '((2* (1st Axle Load + 2nd Axle Load))/15086)^4 + ((2* 3rd Axle Load)/6626)^4')
--insert into tms.tlLegalMaxLoad(Code, [Name], AxleConf, PermAxleLoad, NoofTyres, NoofLoads, DmgFactFormula) values('LIFTS', 'Liftable Single ', 1, 8.5, NULL, NULL, '')
--insert into tms.tlLegalMaxLoad(Code, [Name], AxleConf, PermAxleLoad, NoofTyres, NoofLoads, DmgFactFormula) values('LIFTD', 'Liftable Single ', 2, 10, NULL, NULL, '')

---- =======================================

---- changes in axleconfig table
--drop table tms.tlAxleConfiguration
--create table TMS.tlAxleConfiguration
--(
--	ID int identity(1,1) primary key NOT NULL,
--	Code nvarchar(100) NOT NULL,
--	PermVehLoadDesc nvarchar(100) NOT NULL,
--	PermVehLoadVal float NOT NULL,
--	Tolerance float NOT NULL,
--	Remarks nvarchar(max) null,
--	[Status] [bit] NULL,
--	[IsDeleted] [bit] NULL,
--	[AddedBy] [nvarchar](50) NULL,
--	[AddedOn] [datetime] NULL,
--	[ModifiedBy] [nvarchar](50) NULL,
--	[ModifiedOn] [datetime] NULL,
--)

--insert into TMS.tlAxleConfiguration(Code, PermVehLoadDesc, PermVehLoadVal, Tolerance) values('1_2', '8+10', 18, 5)
--insert into TMS.tlAxleConfiguration(Code, PermVehLoadDesc, PermVehLoadVal, Tolerance) values('1_22', '8+18', 26, 5)
--insert into TMS.tlAxleConfiguration(Code, PermVehLoadDesc, PermVehLoadVal, Tolerance) values('1_22_222', '8+18+24', 50, 5)
--insert into TMS.tlAxleConfiguration(Code, PermVehLoadDesc, PermVehLoadVal, Tolerance) values('1_12_111', '8+8+10+22.5', 48.5, 5)
--insert into TMS.tlAxleConfiguration(Code, PermVehLoadDesc, PermVehLoadVal, Tolerance) values('1_22_111', '8+18+22.5', 48.5, 5)
--insert into TMS.tlAxleConfiguration(Code, PermVehLoadDesc, PermVehLoadVal, Tolerance) values('1_21', '8+10+8', 26, 5)
--insert into TMS.tlAxleConfiguration(Code, PermVehLoadDesc, PermVehLoadVal, Tolerance) values('1_1_222', '8+8+24', 40, 5)
--insert into TMS.tlAxleConfiguration(Code, PermVehLoadDesc, PermVehLoadVal, Tolerance) values('1_12_222', '8+8+10+24', 50, 5)
--insert into TMS.tlAxleConfiguration(Code, PermVehLoadDesc, PermVehLoadVal, Tolerance) values('1_2_222', '8+10+24', 42, 5)
--insert into TMS.tlAxleConfiguration(Code, PermVehLoadDesc, PermVehLoadVal, Tolerance) values('1_1_22', '8+8+18', 34, 5)
--insert into TMS.tlAxleConfiguration(Code, PermVehLoadDesc, PermVehLoadVal, Tolerance) values('1_11_2_1', '8+16', 24, 5)
--insert into TMS.tlAxleConfiguration(Code, PermVehLoadDesc, PermVehLoadVal, Tolerance) values('1_11_222', '8+16', 24, 5)
--insert into TMS.tlAxleConfiguration(Code, PermVehLoadDesc, PermVehLoadVal, Tolerance) values('1_12', '8+18', 26, 5)
--insert into TMS.tlAxleConfiguration(Code, PermVehLoadDesc, PermVehLoadVal, Tolerance) values('1_12_22', '8+18', 26, 5)
--insert into TMS.tlAxleConfiguration(Code, PermVehLoadDesc, PermVehLoadVal, Tolerance) values('1_2_11', '8+10+16', 34, 5)
--insert into TMS.tlAxleConfiguration(Code, PermVehLoadDesc, PermVehLoadVal, Tolerance) values('1_2_111', '8+10+22.5', 40.5, 5)
--insert into TMS.tlAxleConfiguration(Code, PermVehLoadDesc, PermVehLoadVal, Tolerance) values('1_21_111', '8+18+22.5', 48.5, 5)
--insert into TMS.tlAxleConfiguration(Code, PermVehLoadDesc, PermVehLoadVal, Tolerance) values('1_22_2_22', '8+18+10+18', 54, 5)
--insert into TMS.tlAxleConfiguration(Code, PermVehLoadDesc, PermVehLoadVal, Tolerance) values('1_22_11', '8+18+16', 42, 5)
--insert into TMS.tlAxleConfiguration(Code, PermVehLoadDesc, PermVehLoadVal, Tolerance) values('1_22_1_22', '8+18+8+18', 52, 5)
--insert into TMS.tlAxleConfiguration(Code, PermVehLoadDesc, PermVehLoadVal, Tolerance) values('1_222', '8+24', 32, 5)
--insert into TMS.tlAxleConfiguration(Code, PermVehLoadDesc, PermVehLoadVal, Tolerance) values('1_2_22', '8+10+18', 36, 5)
--insert into TMS.tlAxleConfiguration(Code, PermVehLoadDesc, PermVehLoadVal, Tolerance) values('1_22_2_111', '8+18+10+22.5', 58.5, 5)
--insert into TMS.tlAxleConfiguration(Code, PermVehLoadDesc, PermVehLoadVal, Tolerance) values('1_22_22', '8+18+18', 44, 5)
--insert into TMS.tlAxleConfiguration(Code, PermVehLoadDesc, PermVehLoadVal, Tolerance) values('1_22_2_2', '8+18+10+10', 46, 5)
--insert into TMS.tlAxleConfiguration(Code, PermVehLoadDesc, PermVehLoadVal, Tolerance) values('1_2_2_22', '8+10+10+18', 46, 5)
--insert into TMS.tlAxleConfiguration(Code, PermVehLoadDesc, PermVehLoadVal, Tolerance) values('1_2_22_22', '8+10+18+18', 54, 5)
--insert into TMS.tlAxleConfiguration(Code, PermVehLoadDesc, PermVehLoadVal, Tolerance) values('1_22_22_22', '8+18+18+18', 62, 5)

---- ===============================================

---- changes in axle load survey table
--drop table tms.tdAxleLoadSurvey
--CREATE TABLE [TMS].[tdAxleLoadSurvey]
--(
--	[ID] [int] IDENTITY(1,1) NOT NULL,
--	[SurveyDate] [datetime] NOT NULL,
--	[StationID] [int] NOT NULL,
--	[TrfDir] [nvarchar](15) NOT NULL,
--	[VehicleRegNo] [nvarchar](50) NULL,
--	[VehicleName] [nvarchar](100) NULL,
--	[CommodityID] [int] NOT NULL,
--	[Origin] [nvarchar](50) NULL,
--	[Destination] [nvarchar](50) NULL,
--	[AxleConfigID] [int] NOT NULL,
--	[Axle1Load] [float] NULL,
--	[Axle2Load] [float] NULL,
--	[Axle3Load] [float] NULL,
--	[Axle4Load] [float] NULL,
--	[Axle5Load] [float] NULL,
--	[Axle6Load] [float] NULL,
--	[Axle7Load] [float] NULL,
--	[Remarks] [nvarchar](1000) NULL,
--	[SurveyYear] [nvarchar](10) NOT NULL,
--	[NetworkYear] [nvarchar](10) NULL,
--	[Status] [bit] NULL,
--	[IsDeleted] [bit] NULL,
--	[isRetire] [bit] NULL,
--	[AddedBy] [nvarchar](50) NULL,
--	[AddedOn] [datetime] NULL,
--	[ModifiedBy] [nvarchar](50) NULL,
--	[ModifiedOn] [datetime] NULL,
-- CONSTRAINT [PK__tdAxleLo__3214EC27FFABF186] 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

--ALTER TABLE [TMS].[tdAxleLoadSurvey] ADD  CONSTRAINT [DF__tdAxleLoa__Statu__2BC97F7C]  DEFAULT ((1)) FOR [Status]
--GO

--ALTER TABLE [TMS].[tdAxleLoadSurvey] ADD  CONSTRAINT [DF__tdAxleLoa__IsDel__2CBDA3B5]  DEFAULT ((0)) FOR [IsDeleted]
--GO

--ALTER TABLE [TMS].[tdAxleLoadSurvey] ADD  CONSTRAINT [DF__tdAxleLoa__isRet__2AD55B43]  DEFAULT ((0)) FOR [isRetire]
--GO

--ALTER TABLE [TMS].[tdAxleLoadSurvey]  WITH NOCHECK ADD  CONSTRAINT [FK_tdAxleLoadSurvey_tdTrafficStation] FOREIGN KEY([StationID])
--REFERENCES [TMS].[tdTrafficStation] ([ID])
--GO

--ALTER TABLE [TMS].[tdAxleLoadSurvey] CHECK CONSTRAINT [FK_tdAxleLoadSurvey_tdTrafficStation]
--GO

--ALTER TABLE [TMS].[tdAxleLoadSurvey]  WITH CHECK ADD  CONSTRAINT [FK_tdAxleLoadSurvey_tlAxleConfiguration] FOREIGN KEY([AxleConfigID])
--REFERENCES [TMS].[tlAxleConfiguration] ([ID])
--GO

--ALTER TABLE [TMS].[tdAxleLoadSurvey] CHECK CONSTRAINT [FK_tdAxleLoadSurvey_tlAxleConfiguration]
--GO

--ALTER TABLE [TMS].[tdAxleLoadSurvey]  WITH NOCHECK ADD  CONSTRAINT [FK_tdAxleLoadSurvey_tlCommodityType] FOREIGN KEY([CommodityID])
--REFERENCES [TMS].[tlCommodityType] ([ID])
--GO

--ALTER TABLE [TMS].[tdAxleLoadSurvey] CHECK CONSTRAINT [FK_tdAxleLoadSurvey_tlCommodityType]
--GO

---- ========================================

---- changes in axle load counts table - this table is not needed so hiding the menu
--select * from admn.tsMenuDefinition where code like 'TMS_MGDT%'
--update admn.tsMenuDefinition set [status]=0 where id=381
--drop table tms.tdAxleLoadCounts
--CREATE TABLE [TMS].[tdAxleLoadCounts]
--(
--	[ID] [int] IDENTITY(1,1) primary key NOT NULL,
--	[SurveyDate] [datetime] NOT NULL,
--	[StationID] [int] NOT NULL,
--	[TrfDir] [nvarchar](15) NOT NULL,
--	[SurveyInterval] [int] default(60) NOT NULL,
--	[Time] [nvarchar](15) NOT NULL,
--	[MiniBus] [int] NULL,
--	[Bus] [int] NULL,
--	[LGV] [int] NULL,
--	[Axle2] [int] NULL,
--	[Axle3] [int] NULL,
--	[SemiTrailer] [int] NULL,
--	[TruckTrailer] [int] NULL,
--	[Coasters] [int] NULL,
--	[Others] [int] NULL,
--	[SurveyYear] [nvarchar](10) NOT NULL,
--	[NetworkYear] [nvarchar](10) NULL,
--	[isRetire] [bit] NULL,
--	[Status] [bit] NULL,
--	[IsDeleted] [bit] NULL,
--	[AddedBy] [nvarchar](50) NULL,
--	[AddedOn] [datetime] NULL,
--	[ModifiedBy] [nvarchar](50) NULL,
--	[ModifiedOn] [datetime] NULL
--)

--ALTER TABLE [TMS].[tdAxleLoadCounts]  WITH CHECK ADD  CONSTRAINT [FK_tdAxleLoadCounts_tdTrafficStation] FOREIGN KEY([StationID])
--REFERENCES [TMS].[tdTrafficStation] ([ID])
--GO

--ALTER TABLE [TMS].[tdAxleLoadCounts] CHECK CONSTRAINT [FK_tdAxleLoadCounts_tdTrafficStation]
--GO

--ALTER TABLE [TMS].[tdAxleLoadCounts]  WITH CHECK ADD  CONSTRAINT [FK_tdAxleLoadCounts_tlTimePeriod] FOREIGN KEY([SurveyInterval], [Time])
--REFERENCES [TMS].[tlTimePeriod] ([Interval], [Code])
--GO

--ALTER TABLE [TMS].[tdAxleLoadCounts] CHECK CONSTRAINT [FK_tdAxleLoadCounts_tlTimePeriod]
--GO