USE Resources01_Dat
GO

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE  TABLE_NAME = 'tblDts' AND COLUMN_NAME = 'ICCID' )
BEGIN
	ALTER TABLE [dbo].[tblDts] ADD [ICCID] [nvarchar](100) NULL
END

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE  TABLE_NAME = 'tblDts' AND COLUMN_NAME = 'Contract_End_Date' )
BEGIN
	ALTER TABLE [dbo].[tblDts] ADD [Contract_End_Date] [datetime] NULL
END

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE  TABLE_NAME = 'tblDts' AND COLUMN_NAME = 'Screen_Size' )
BEGIN
	ALTER TABLE [dbo].[tblDts] ADD [Screen_Size] [nvarchar](50) NULL
END

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE  TABLE_NAME = 'tblDts' AND COLUMN_NAME = 'License_Server' )
BEGIN
	ALTER TABLE [dbo].[tblDts] ADD [License_Server] [int] NULL
END

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE  TABLE_NAME = 'tblDts' AND COLUMN_NAME = 'Previous_Owner' )
BEGIN
	ALTER TABLE [dbo].[tblDts] ADD [Previous_Owner] [int] NULL
END
GO

-- Number of repairs for assets in repair status.
UPDATE [dbo].[tblDts]
	SET [Number_of_Repairs]=1
	WHERE ([Number_of_Repairs] IS NULL  OR [Number_of_Repairs] <1)
		AND [Asset_Sub_status]=5
		AND [nUsageType]=1 AND [nID]<10000
		
-- Is Lemon for when repairs > 2 (by default)
UPDATE [dbo].[tblDts]
	SET [Is_Lemon]=1
	WHERE ([Is_Lemon] IS NULL  OR [Is_Lemon] <>1)
		AND [Number_of_Repairs]>2
		AND [nUsageType]=1 AND [nID]<10000

-- And the not-lemons
UPDATE [dbo].[tblDts]
	SET [Is_Lemon]=2
	WHERE ([Is_Lemon] IS NULL  OR [Is_Lemon] <>2)
		AND ( [Number_of_Repairs]<=2 OR [Number_of_Repairs] IS NULL)
		AND [nUsageType]=1 AND [nID]<10000


		
GO

--change the Invoice_Reference field to a numeric field (it's defined as single choice in tblDtsFields)--
UPDATE tblDts SET Invoice_Reference=(SELECT TOP(1) nID FROM Users_mdb.dbo.PurchaseLineItem WHERE tName=Invoice_Reference)
WHERE ISNUMERIC(Invoice_Reference)=0
AND NOT Invoice_Reference IS NULL
GO

ALTER TABLE tblDts ALTER COLUMN Invoice_Reference int
GO

