--Drop Triggers to Start
DECLARE @SQLCmd nvarchar(1000) 
DECLARE @Trig varchar(500)
DECLARE @sch varchar(500)

DECLARE TGCursor CURSOR FOR

SELECT ISNULL(tbl.name, vue.name) AS [schemaName]
     , trg.name AS triggerName
FROM sys.triggers trg
LEFT OUTER JOIN (SELECT tparent.object_id, ts.name 
                 FROM sys.tables tparent 
                 INNER JOIN sys.schemas ts ON TS.schema_id = tparent.SCHEMA_ID) 
                 AS tbl ON tbl.OBJECT_ID = trg.parent_id
LEFT OUTER JOIN (SELECT vparent.object_id, vs.name 
                 FROM sys.views vparent 
                 INNER JOIN sys.schemas vs ON vs.schema_id = vparent.SCHEMA_ID) 
                 AS vue ON vue.OBJECT_ID = trg.parent_id
 
OPEN TGCursor
FETCH NEXT FROM TGCursor INTO @sch,@Trig
WHILE @@FETCH_STATUS = 0
BEGIN

SET @SQLCmd = N'DROP TRIGGER [' + @sch + '].[' + @Trig + ']'
EXEC sp_executesql @SQLCmd
PRINT @SQLCmd

FETCH next FROM TGCursor INTO @sch,@Trig
END

CLOSE TGCursor
DEALLOCATE TGCursor

--Check to make sure SQL is 2008 or higher. 
--The compatibility level of the database must also be changed, if it is too low

--now handled in the UpdateSchemaFromSnapshot utility
/*
DECLARE @cl int
DECLARE @installed_sql INT
DECLARE @sql NVARCHAR(1000)

SELECT @cl = compatibility_level
FROM sys.databases WHERE name = DB_NAME();

select @installed_sql = convert(int,LEFT(CAST(serverproperty('productversion') as char), 
						convert(int,CHARINDEX('.',CAST(serverproperty('productversion') as char))) -1))

select @installed_sql = @installed_sql * 10

IF @installed_sql = 90
	BEGIN
		RAISERROR('SQL 2005 cannot be used for the Eskimo software. It must be upgraded to 2008.',16,1) 
		RETURN
	END

IF @cl < @installed_sql --less than sql version installed.
	BEGIN
		SELECT @sql = 'ALTER DATABASE ' + DB_NAME() + ' SET compatibility_level = ' + convert(varchar(10),@installed_sql)
		EXEC sys.sp_executesql @sql
		PRINT 'Compatibility level changed'
	END


*/


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

	-- Data Consistency Checking (from the Support Team)

------------------------------------------------------------------------------------
/*
Database Update Failed.
Backup failed for Server
System.Data.SqlClient.SqlError: Cannot open backup device 'C:\Eskimo\ESKIMO_PREUPDATE_BACKUP.BAK'. Operating system error 3(error not found).
*/
PRINT '^<RUNN> Checking Pre-Update Backup entry in Global Config, and adding if required' 

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'GlobalConfig' AND COLUMN_NAME = 'SuppressPreUpdateBackup')
		BEGIN
			ALTER TABLE dbo.GlobalConfig ADD SuppressPreUpdateBackup BIT NULL
		END
GO

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'GlobalConfig' AND COLUMN_NAME = 'PreUpdateBackupFilePath')
		BEGIN
			ALTER TABLE dbo.GlobalConfig ADD PreUpdateBackupFilePath VARCHAR(200) NULL
		END
GO
---------------------------------------------------------------------------------------------------------------------------------------------------------------------

/*
Database Update Failed.
The ALTER TABLE statement conflicted with the CHECK constraint "CK_TradeCustomerCompanyName". The conflict occurred in database "Eskimo", table "dbo.Customer".
The following text may help in diagnosing the fault as it was responsible for causing the error:
ALTER TABLE [dbo].[Customer] ADD CONSTRAINT [CK_TradeCustomerCompanyName] CHECK (([customertype]=(0) AND isnull([customercompanyname],'')<>'' OR [customertype]<>(0)))
*/
PRINT '^<RUNN> Setting Customer Company Name for Traded Customers that have a blank Company Name' 

IF EXISTS(SELECT * FROM dbo.Customer WHERE CustomerCompanyName IS NULL AND CustomerType = 0)
	BEGIN
		UPDATE dbo.Customer SET CustomerCompanyName = 'No Company Name' WHERE CustomerCompanyName IS NULL AND CustomerType = 0
	END
GO
---------------------------------------------------------------------------------------------------------------------------------------------------------------------

/*
Database Update Failed.
ExecuteNonQuery failed for Database 'Eskimo'. 
An exception occurred while executing a Transact-SQL statement or batch.
Customers are present which contain invalid Customer IDs
Have had to disable this section. There could be products that reference customers that just need to be pulled down from another store.
If the products are updated to be generic, all links to the customer ID will be lost. It will probably be easier to just find the customer and add it.


PRINT '^<RUNN> Editing PLUs and Styles to have valid CustomerIDs' 

IF Exists(SELECT PLU FROM dbo.PLU where AssignedCustomer not in (Select CustomerID from Customer))
	BEGIN
		UPDATE PLU SET AssignedCustomer = '000-000000' where AssignedCustomer not in (Select CustomerID from Customer)
	END
G-O
IF Exists(Select StyleID from dbo.styleRef where DefaultAssignedCustomer not in (Select CustomerID from Customer))
	BEGIN
		Update StyleRef set DefaultAssignedCustomer = N'000-000000' where DefaultAssignedCustomer not in (Select CustomerID from Customer)
	END
G-O
IF Exists(Select StyleID from dbo.styleRef WHERE LEFT(DefaultAssignedCustomer,4) <> dbo.GetStoreNumber()+'-' AND LEFT(DefaultAssignedCustomer,4) <> '000-')
	BEGIN
		UPDATE dbo.StyleRef SET DefaultAssignedCustomer = '000-000000' WHERE LEFT(DefaultAssignedCustomer,4) <> dbo.GetStoreNumber()+'-' AND LEFT(DefaultAssignedCustomer,4) <> '000-'
	END
G-O
IF Exists(SELECT * FROM dbo.CusOrder WHERE LEFT(CusOrderCustomerID,4) <> dbo.GetStoreNumber()+'-' AND LEFT(CusOrderCustomerID,4) <> '000-')
	BEGIN
		Delete from CusOrderDetails where CusOrderID IN (Select CusOrderID from CusOrder where LEFT(CusOrderCustomerID,4) <> dbo.GetStoreNumber()+'-' AND LEFT(CusOrderCustomerID,4) <> '000-')
		Delete from CusOrder WHERE LEFT(CusOrderCustomerID,4) <> dbo.GetStoreNumber()+'-' AND LEFT(CusOrderCustomerID,4) <> '000-'
	END
G-O
IF EXISTS(SELECT * FROM dbo.Customer WHERE LEFT(CustomerID,4) <> dbo.GetStoreNumber()+'-' AND LEFT(CustomerID,4) <> '000-')
	BEGIN
		DELETE FROM dbo.Customer WHERE LEFT(CustomerID,4) <> dbo.GetStoreNumber()+'-' AND LEFT(CustomerID,4) <> '000-'
	END
G-O
*/
---------------------------------------------------------------------------------------------------------------------------------------------------------------------

/*
Database Update Failed.
The ALTER TABLE statement conflicted with the CHECK constraint "CK_ReturnsDetails". The conflict occurred in database "Eskimo", table "dbo.ReturnsDetails", column 'Qty'.
The following text may help in diagnosing the fault as it was responsible for causing the error:
ALTER TABLE [dbo].[ReturnsDetails] ADD CONSTRAINT [CK_ReturnsDetails] CHECK (([qty]>(0)))
*/
PRINT '^<RUNN> Removing Invalid PLUs from Supplier Return Details' 

If Exists (SELECT * FROM dbo.ReturnsDetails INNER JOIN dbo.Returns ON dbo.ReturnsDetails.ReturnID = dbo.Returns.ReturnID WHERE PLU NOT IN (SELECT plu FROM dbo.PLU))
	BEGIN
		DELETE FROM dbo.ReturnsDetails WHERE PLU NOT IN (SELECT plu FROM dbo.PLU)
	END
GO
---------------------------------------------------------------------------------------------------------------------------------------------------------------------

/* 
Database Update Failed.
The ALTER TABLE statement conflicted with the CHECK constraint "CK_ReturnsDetails". The conflict occurred in database "Eskimo", table "dbo.ReturnsDetails", column 'Qty'.
The following text may help in diagnosing the fault as it was responsible for causing the error:
ALTER TABLE [dbo].[ReturnsDetails] ADD CONSTRAINT [CK_ReturnsDetails] CHECK (([qty]>(0)))
*/
PRINT '^<RUNN> Editing Supplier Returns that have an invalid return quantity' 

If Exists (SELECT * FROM dbo.ReturnsDetails INNER JOIN dbo.Returns ON dbo.ReturnsDetails.ReturnID = dbo.Returns.ReturnID WHERE Qty < 1)
	BEGIN
		UPDATE dbo.ReturnsDetails SET Qty = 1 WHERE Qty = 0
		UPDATE dbo.ReturnsDetails SET Qty = Qty * - 1 WHERE Qty < 0
	END
GO
---------------------------------------------------------------------------------------------------------------------------------------------------------------------

/*
Database Update Failed.
ExecuteNonQuery failed for Database 'Eskimo'. 
An exception occurred while executing a Transact-SQL statement or batch.
StyleRef entries are present which contain links to non-existant Customers

PRINT '^<RUNN> Editing Style References to have a valid Default Assigned Customer' 

IF Exists (SELECT DISTINCT DefaultAssignedCustomer FROM dbo.StyleRef WHERE DefaultAssignedCustomer NOT IN (SELECT CustomerID FROM dbo.Customer))
	BEGIN
		UPDATE dbo.StyleRef SET DefaultAssignedCustomer = '000-000000' WHERE DefaultAssignedCustomer NOT IN (SELECT CustomerID FROM dbo.Customer)
	END
G-O
*/
---------------------------------------------------------------------------------------------------------------------------------------------------------------------

/*
Database Update Failed.
ExecuteNonQuery failed for Database 'Eskimo'. 
An exception occurred while executing a Transact-SQL statement or batch.
OrderDetail entries are present which contain links to non-existant PLUs
*/
PRINT '^<RUNN> Removing Invalid PLUs from Purchase Order Details' 

IF Exists (SELECT DISTINCT PLU FROM dbo.OrderDetails WHERE PLU NOT in (SELECT plu FROM dbo.PLU))
	BEGIN
		DELETE FROM dbo.OrderDetails WHERE OrderDetails.PLU NOT in (SELECT plu FROM dbo.PLU)
	END
GO
---------------------------------------------------------------------------------------------------------------------------------------------------------------------

/*
Database Update Failed.
ExecuteNonQuery failed for Database 'Eskimo'. 
An exception occurred while executing a Transact-SQL statement or batch.
PLU entries are present which contain links to non-existant Customers

PRINT '^<RUNN> Editing PLUs to have Valid Customer IDs' 

IF Exists (SELECT DISTINCT AssignedCustomer FROM dbo.PLU WHERE AssignedCustomer NOT IN (SELECT CustomerID FROM dbo.Customer))
	BEGIN
		Alter Table dbo.PLU DISABLE TRIGGER trgPLUChanged
		Alter Table dbo.PLU DISABLE TRIGGER trgUpdateChangeLog
		Alter Table dbo.PLU DISABLE TRIGGER trgUseParentDefault
		UPDATE dbo.PLU SET AssignedCustomer = '000-000000' WHERE AssignedCustomer NOT IN (SELECT CustomerID FROM dbo.Customer)
		Alter Table dbo.PLU ENABLE TRIGGER trgPLUChanged
		Alter Table dbo.PLU ENABLE TRIGGER trgUpdateChangeLog
		Alter Table dbo.PLU ENABLE TRIGGER trgUseParentDefault
	END
G-O
*/
---------------------------------------------------------------------------------------------------------------------------------------------------------------------

/*
Database Update Failed.
The ALTER TABLE statement conflicted with the CHECK constraint "SecondaryPLUs". The conflict occurred in database "Eskimo", table "dbo.PLU", column 'SecondaryPLU'.
The following text may help in diagnosing the fault as it was responsible for causing the error:
ALTER TABLE [dbo].[PLU] ADD CONSTRAINT [SecondaryPLUs] CHECK (([dbo].[SecondaryPLUDupl]([secondaryPLU])=(0)))
*/
PRINT '^<RUNN> Removing Duplicate Secondary PLUs' 

If Exists (
SELECT SecondaryPLU, COUNT(SecondaryPLU) AS [Should be empty]
	FROM         PLU
	WHERE     (NOT (SecondaryPLU IS NULL)) 
	GROUP BY SecondaryPLU
	HAVING      (COUNT(SecondaryPLU) > 1) 
)
	BEGIN
		Update dbo.PLU SET SecondaryPLU = NULL where SecondaryPLU IN (
			SELECT SecondaryPLU
			FROM PLU
			WHERE (NOT (SecondaryPLU IS NULL))
			GROUP BY SecondaryPLU
			HAVING (COUNT(SecondaryPLU) > 1) 
		)
	END
GO

IF Exists (SELECT COUNT(plu) AS [Should be 0] FROM plu WHERE SecondaryPLU = 'NULL')
	BEGIN
		UPDATE dbo.PLU SET SecondaryPLU = NULL WHERE SecondaryPLU = 'NULL'
	END
GO
---------------------------------------------------------------------------------------------------------------------------------------------------------------------

	UPDATE dbo.PLU
	SET VatCode = (SELECT top(1) VatID FROM dbo.VATCodes ORDER BY VatRate DESC)
	FROM dbo.PLU WHERE VatCode NOT IN (SELECT VatID FROM dbo.VATCodes)

GO

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

/*
Database Update Failed.
Cannot insert the value NULL into column 'EntryID', table 'Eskimo.dbo.tmp_rg_xx_YesPay'; column does not allow nulls. INSERT fails.
The following text may help in diagnosing the fault as it was responsible for causing the error:
INSERT INTO [dbo].[tmp_rg_xx_YesPay]([EntryID], [YesPayID], [YesPayStoreID], [ReceiptTill], [ReceiptNumber], [CustomerCopy], [ShopCopy], [DateTimeCol]) SELECT [EntryID], [YesPayID], [YesPayStoreID], [ReceiptTill], [ReceiptNumber], [CustomerCopy], [ShopCopy], [DateTimeCol] FROM [dbo].[YesPay]
*/
PRINT '^<RUNN> Ensuring dbo.YesPay has a valid Identity column' 

IF Exists (SELECT [EntryID], [YesPayID], [YesPayStoreID], [ReceiptTill], [ReceiptNumber], [CustomerCopy], [ShopCopy], [DateTimeCol] FROM [dbo].[YesPay] WHERE [EntryID] = 0)
	BEGIN
		ALTER TABLE dbo.YesPay
		DROP COLUMN [EntryID]
		ALTER TABLE dbo.YesPay
		ADD [EntryID] INT IDENTITY(1,1)
	END
GO
---------------------------------------------------------------------------------------------------------------------------------------------------------------------

/*
Database Update Failed.
Cannot insert the value NULL into column 'ItemChannel', table 'Eskimo.dbo.tmp_rg_xx_ItemsSold'; column does not allow nulls. INSERT fails.
Handled Below - Item 10

PRINT '^<RUNN> Checking Items Sold table for Item Channel, and adding if required' 

IF Exists (SELECT * FROM dbo.ItemsSold WHERE itemchannel IS NULL)
	BEGIN
		UPDATE dbo.ItemsSold SET itemchannel = 0 WHERE itemchannel IS NULL
	END
G-O
*/
---------------------------------------------------------------------------------------------------------------------------------------------------------------------

/*
Database Update Failed.
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Stock_PLU". The conflict occurred in database "ESKIMO", table "dbo.PLU", column 'PLU'.
The following text may help in diagnosing the fault as it was responsible for causing the error:
ALTER TABLE [dbo].[Stock] ADD CONSTRAINT [FK_Stock_PLU] FOREIGN KEY ([StockID]) REFERENCES [dbo].[PLU] ([PLU]) ON UPDATE CASCADE
*/
PRINT '^<RUNN> Deleting entries from stock table, which dont have a corresponding entry in PLU table' 

If Exists (SELECT * FROM stock WHERE StockID NOT IN (SELECT PLU FROM dbo.PLU))
	BEGIN
		DELETE FROM dbo.Stock WHERE StockID NOT IN (SELECT PLU FROM dbo.PLU)
	END
GO
---------------------------------------------------------------------------------------------------------------------------------------------------------------------

/*
Check out ticket 6791 for an explanation of why this is needed.
*/
UPDATE dbo.PLU SET Size = RTRIM(LTRIM(PLU.Size)) WHERE 
DATALENGTH(RTRIM(LTRIM(PLU.Size))) <> DATALENGTH(Size) 

GO

/*
Database Update Failed.
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Customer_Countries". The conflict occurred in database "ESKIMO", table "dbo.Countries", column 'CountryID'.
The following text may help in diagnosing the fault as it was responsible for causing the error:
ALTER TABLE [dbo].[Customer] ADD CONSTRAINT [FK_Customer_Countries] FOREIGN KEY ([Country]) REFERENCES [dbo].[Countries] ([CountryID])
*/
PRINT '^<RUNN> Editing Customers without a country' 

IF Exists (SELECT Country FROM dbo.Customer WHERE Country NOT IN (SELECT CountryID FROM dbo.Countries))
	BEGIN
		UPDATE dbo.Customer SET Country = 1 WHERE Country IS NULL
	END
GO
---------------------------------------------------------------------------------------------------------------------------------------------------------------------

/*
Database Update Failed.
ExecuteNonQuery failed for Database 'Eskimo'. 
An exception occurred while executing a Transact-SQL statement or batch.
InwardDetail entries are present which contain links to non-existant PLUs
*/
PRINT '^<RUNN> Removing Invalid PLUs from Stock Inward Details' 

IF Exists (SELECT PLU FROM dbo.InwardDetails WHERE PLU NOT IN (SELECT PLU FROM dbo.PLU))
	BEGIN
		DELETE FROM dbo.InwardDetails WHERE PLU NOT IN (SELECT PLU FROM dbo.PLU)
	END
GO
---------------------------------------------------------------------------------------------------------------------------------------------------------------------

/*
Database Update Failed.
ExecuteNonQuery failed for Database 'Eskimo'. 
An exception occurred while executing a Transact-SQL statement or batch.
StyleRef entries are present which contain links to non-existant Seasons
*/
PRINT '^<RUNN> Setting Defaulty Season for PLUs and Styles' 

IF Exists (SELECT * FROM dbo.StyleRef WHERE DefaultSeason NOT IN (SELECT SeasonID FROM dbo.Season))
	BEGIN
		UPDATE dbo.StyleRef SET DefaultSeason = 1 WHERE DefaultSeason NOT IN (SELECT SeasonID FROM dbo.Season)
	END
GO

IF Exists (SELECT * FROM dbo.PLU WHERE Season NOT IN (SELECT SeasonID FROM dbo.Season))
	BEGIN
		UPDATE dbo.PLU SET Season = 1 WHERE Season NOT IN (SELECT SeasonID FROM dbo.Season)
	END
GO
---------------------------------------------------------------------------------------------------------------------------------------------------------------------

/*
Error linking to KeyPadQuickText
*/
PRINT '^<RUNN> Creating dbo.KeyPadQuickText and default entries if it doesnt exist' 

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'KeyPadQuickText')
	BEGIN
		CREATE TABLE [dbo].[KeyPadQuickText](
			[KeyPadQuickText] [varchar](50) NULL,
			[KeyPadQuickTextID] [int] NOT NULL,
		 CONSTRAINT [PK_KeyPadQuickText] PRIMARY KEY CLUSTERED 
		(
			[KeyPadQuickTextID] ASC
		)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
		) ON [PRIMARY]
		ALTER TABLE [dbo].[KeyPadQuickText] ADD  CONSTRAINT [DF_KeyPadQuickText_KeyPadQuickTextID]  DEFAULT ((0)) FOR [KeyPadQuickTextID]
	END
GO
IF NOT EXISTS(SELECT * From dbo.KeyPadQuickText)
	BEGIN
		INSERT INTO dbo.KeyPadQuickText ( KeyPadQuickText ,KeyPadQuickTextID )
		VALUES  ( '@btinternet.co.uk' , 1 )
		INSERT INTO dbo.KeyPadQuickText ( KeyPadQuickText ,KeyPadQuickTextID )
		VALUES  ( '@hotmail.co.uk' ,  2 )
		INSERT INTO dbo.KeyPadQuickText ( KeyPadQuickText ,KeyPadQuickTextID )
		VALUES  ( '@hotmail.com' , 3 )
		INSERT INTO dbo.KeyPadQuickText ( KeyPadQuickText ,KeyPadQuickTextID )
		VALUES  ( '@googlemail.com' , 4 )
		INSERT INTO dbo.KeyPadQuickText ( KeyPadQuickText ,KeyPadQuickTextID )
		VALUES  ( '@gmail.com' , 5 )
		INSERT INTO dbo.KeyPadQuickText ( KeyPadQuickText ,KeyPadQuickTextID )
		VALUES  ( '@yahoo.com' , 6 )
	END
GO
---------------------------------------------------------------------------------------------------------------------------------------------------------------------

/*
ExecuteNonQuery failed for Database 'Eskimo'. 
An exception occurred while executing a Transact-SQL statement or batch.
Invalid Styles present in StyleRef table
The point of this is to 'correct' the entries so they are valid
PRINT '^<RUNN> Creating Override for Styles with invalid Characters' 

IF Exists (SELECT * FROM dbo.StyleRef WHERE StyleID like '%[^A-Za-z0-9_\-]%' escape '\')
	BEGIN
		Update StyleRef Set StyleBadCharacterOverride = 1 where StyleID IN (SELECT StyleID FROM dbo.StyleRef WHERE StyleID like '%[^A-Za-z0-9_\-]%' escape '\')
	END 
G-O
*/
---------------------------------------------------------------------------------------------------------------------------------------------------------------------

/*
ExecuteNonQuery failed for Database 'Eskimo'. 
An exception occurred while executing a Transact-SQL statement or batch.
Invalid PLUs present in PLU table
As above
PRINT '^<RUNN> Creating Override for PLUs with invalid Characters' 

IF EXISTS(SELECT * FROM dbo.PLU WHERE PLU like '%[^A-Za-z0-9_\-]%' escape '\')
	BEGIN
		Update PLU Set BadPLUCharacterOverride = 1 where PLU IN (SELECT PLU FROM dbo.PLU WHERE PLU like '%[^A-Za-z0-9_\-]%' escape '\')
	END
G-O
*/
---------------------------------------------------------------------------------------------------------------------------------------------------------------------

/*
Cannot insert the value NULL into column 'StockMoveDate', table 'EskimoLocal.dbo.StockMove'; column does not allow nulls. UPDATE fails.
The following text may help in diagnosing the fault as it was responsible for causing the error:
ALTER TABLE [dbo].[StockMove] ALTER COLUMN [StockMoveDate] [datetime] NOT NULL
Handled below - Item 13

PRINT '^<RUNN> Altering entries in dbo.StockMove to have a valid DateTime' 

IF Exists (select * from StockMove where StockMoveDate is NULL)
	BEGIN
		update StockMove set StockMoveDate = dateadd(yy,-50,GETDATE()) where StockMoveDate is NULL
	END
G-O
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
*/
/*
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_PLU_Customer". 
The conflict occurred in database "Eskimo", table "dbo.Customer", column 'CustomerID'. 

PRINT '^<RUNN> Re-checking Assigned Customer in dbo.PLU' 

IF Exists (
	SELECT		AssignedCustomer
	FROM		PLU
	GROUP BY	AssignedCustomer
	HAVING		(NOT (AssignedCustomer IN (	SELECT CustomerID FROM Customer)))
)
	BEGIN
		update PLU
		set AssignedCustomer = '000-000000'
		where AssignedCustomer in (	SELECT		AssignedCustomer
									FROM		PLU
									GROUP BY	AssignedCustomer
									HAVING		(NOT (AssignedCustomer IN (	SELECT CustomerID FROM Customer))))
	END
G-O
*/

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

	-- 1.

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

PRINT '^<RUNN>Removing Duplicate PLUs with the same colour, size and style' 

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.table_constraints 
WHERE TABLE_NAME = 'PLU' AND CONSTRAINT_NAME = 'UniqueStyledProduct')
	BEGIN
		PRINT '^<SKIP>Duplicate PLUs with the same colour, size and style already updated, skipping'
		RETURN
	END
       
DECLARE @PLUsToKeep TABLE (PLU VARCHAR(50))

INSERT INTO	@PLUsToKeep ( PLU )
SELECT tmpTotal.PLU FROM	(

					SELECT				dbo.PLU.PLU, 
										dbo.PLU.StyleID, 
										dbo.PLU.ColorID, 
										dbo.PLU.Size, 
										PLUCreationDate, 
										tmpSold.QtySold,
										ItemsOrdered,
										ISNULL(QtySold,0) + ISNULL(ItemsOrdered,0) AS Relevance,
										ROW_NUMBER() OVER (	PARTITION BY	dbo.PLU.StyleID, 
																			dbo.PLU.ColorID, 
																			dbo.PLU.Size 
															ORDER BY		ISNULL(QtySold,0) + ISNULL(ItemsOrdered,0) DESC, PLUCreationDate
														  ) AS RowNum 
					
					FROM				dbo.PLU 
					
					INNER JOIN			(
											SELECT		StyleID, ColorID, Size
											FROM		dbo.PLU  
											WHERE		StyleID IS NOT NULL 
											GROUP BY	StyleID, ColorID, Size  
											HAVING		COUNT(*) > 1 
										)	tmpGrouped 
					ON					dbo.PLU.ColorID = tmpGrouped.ColorID 
					AND					dbo.PLU.StyleID = tmpGrouped.StyleID 
					AND					dbo.PLU.Size = tmpGrouped.Size
					
					LEFT OUTER JOIN		(
											SELECT		ItemPLU, SUM(CASE WHEN ItemQty < 0 THEN ItemQty * -1 ELSE ItemQty END) AS QtySold
											FROM		dbo.ItemsSold
											WHERE		ItemVoid = 0
											AND			TransVoid = 0
											GROUP BY	ItemPLU
										)	tmpSold
					ON					tmpSold.ItemPLU = dbo.PLU.PLU
					
					LEFT OUTER JOIN		(
											SELECT		PLU, SUM(UnitsToOrder) AS ItemsOrdered
											FROM		dbo.OrderDetails
											GROUP BY	PLU
										)	tmpOrdered
					ON					tmpOrdered.PLU = dbo.PLU.PLU					
										
				) tmpTotal
WHERE tmpTotal.RowNum = 1

UPDATE				dbo.PLU
SET					StyleID = NULL, PLUWebInclude = 0, PLUTag = plu.StyleID, PLUState = 4

FROM				dbo.PLU

INNER JOIN			(
											SELECT		StyleID, ColorID, ISNULL(Size,'') AS NSize
											FROM		dbo.PLU  
											WHERE		StyleID IS NOT NULL 
											GROUP BY	StyleID, ColorID, Size  
											HAVING		COUNT(*) > 1 
										)	tmpGrouped 
					ON					dbo.PLU.ColorID = tmpGrouped.ColorID 
					AND					dbo.PLU.StyleID = tmpGrouped.StyleID 
					AND					ISNULL(dbo.PLU.Size,'') = tmpGrouped.NSize
					
WHERE				dbo.PLU.PLU NOT IN (SELECT PLU FROM @PLUsToKeep)	
PRINT '^<COMP>Finished removing Duplicate PLUs with the same colour, size and style'

	
GO

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

	-- 2.

------------------------------------------------------------------------------------
PRINT '^<RUNN>Ensuring database has Snapshot Isolation Mode Enabled' 

DECLARE @Snapshot BIT
DECLARE @SQL NVARCHAR(MAX)

	SELECT @Snapshot = snapshot_isolation_state FROM sys.databases WHERE name = DB_NAME()

	IF @Snapshot = 0
		BEGIN
			SELECT @SQL = N'ALTER DATABASE [' + DB_NAME() + '] SET ALLOW_SNAPSHOT_ISOLATION ON'
			EXEC sp_executesql @SQL		
			PRINT '^<COMP>Snapshot Isolation mode enabled'
		END
	ELSE
		BEGIN
			PRINT '^<SKIP>No need to enable Snapshot Isolation mode - already enabled.'	
		END

GO

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

	-- 3.

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

PRINT '^<RUNN>Ensuring Arithmetic Abort Option is Enabled' 

DECLARE @ArithAbort BIT
DECLARE @SQL NVARCHAR(MAX)

	SELECT @ArithAbort = is_arithabort_on FROM sys.databases WHERE name = DB_NAME()

	IF @ArithAbort = 0
		BEGIN
			SELECT @SQL = 'ALTER DATABASE [' + DB_NAME() + '] SET ARITHABORT ON'
			EXEC sp_executesql @SQL		
			PRINT '^<COMP>Arithmetic Abort Option enabled'
		END
	ELSE
		BEGIN
			PRINT '^<SKIP>No need to enable Arithmetic Abort Option - already enabled.'	
		END

GO

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

	-- 4.

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

PRINT '^<RUNN>Checking orphaned Suppliers' 

DECLARE @tmpSupplierID VARCHAR(50)

	SELECT @tmpSupplierID = 'AUTOCREATEDSUPP'

	IF EXISTS(SELECT SupplierID FROM dbo.SupplierDetails GROUP BY SupplierID HAVING (NOT (SupplierID IN (SELECT [Supplier ID] FROM Suppliers))) )
	OR EXISTS(SELECT DefaultSupplier FROM dbo.StyleRef GROUP BY DefaultSupplier HAVING (NOT (DefaultSupplier IN (SELECT [Supplier ID] FROM Suppliers))))
	OR EXISTS(SELECT SupplierID FROM PLU GROUP BY SupplierID HAVING (NOT (SupplierID IN (SELECT [Supplier ID] FROM Suppliers))))
		BEGIN
			PRINT '^<RUNN>Creating Supplier Inconsistencies Supplier entry' 
			
			INSERT INTO dbo.Suppliers
					( 
						[Supplier ID] ,
						[Supplier Name] 
					)
			VALUES  ( 
						@tmpSupplierID,
						'Supplier Inconsistencies'
					)

			PRINT '^<RUNN>Creating Supplier Inconsistencies SupplierDetails entry'
			INSERT INTO dbo.SupplierDetails
					( 
						SupplierID ,
						SupplierDepartment 
					)
			VALUES	( 
						@tmpSupplierID,
						'Main Department'
					)

			PRINT '^<RUNN>Redirecting orphaned SupplierDetails records to Supplier Inconsistencies Supplier'					
			UPDATE					dbo.SupplierDetails
			SET						SupplierID = @tmpSupplierID
			WHERE					SupplierID NOT IN (SELECT [Supplier ID] FROM Suppliers)
			PRINT '^<COMP>Orphaned SupplierDetail records redirected to Supplier Inconsistencies Supplier'

			PRINT '^<RUNN>Redirecting orphaned StyleRef records to Supplier Inconsistencies Supplier'						
			UPDATE					StyleRef
			SET						DefaultSupplier = @tmpSupplierID
			WHERE					DefaultSupplier NOT IN (SELECT [Supplier ID] FROM Suppliers)
			PRINT '^<COMP>Orphaned StyleRef records redirected to Supplier Inconsistencies Supplier'
			
			PRINT '^<RUNN>Redirecting orphaned PLU records to Supplier Inconsistencies Supplier'			
			UPDATE					dbo.PLU
			SET						SupplierID = @tmpSupplierID
			WHERE					SupplierID NOT IN (SELECT [Supplier ID] FROM dbo.Suppliers)
			PRINT '^<COMP>Orphaned PLU records redirected to Supplier Inconsistencies Supplier'
							
		END
	ELSE
		BEGIN
			PRINT '^<SKIP>No orphaned supplier details to fix.'	
		END
GO

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

	-- 5.

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

	IF EXISTS(SELECT * FROM dbo.PLU WHERE StyleID NOT IN (SELECT StyleID FROM dbo.StyleRef) AND StyleID IS NOT NULL) OR EXISTS(SELECT * FROM dbo.CusOrderDetails WHERE CusOrderDetailPLU NOT IN (SELECT PLU FROM dbo.PLU))
		BEGIN
			--This means there are PLUs that reference styles that don't exist.
			--First we need to create a group and department we can use for these.
			
			DECLARE @ProductIncons VARCHAR(50)
			DECLARE @tmpSupplierID VARCHAR(50)
			
			SELECT @ProductIncons = 'Product Inconsistencies'
			SELECT @tmpSupplierID = 'AUTOCREATEDSUPP'
			
			IF NOT EXISTS(SELECT * FROM dbo.Groups WHERE GroupDescription = @ProductIncons)
				BEGIN
					INSERT INTO dbo.Groups
											( 
												GroupID ,
												GroupDescription 
											)
					SELECT						ISNULL(MAX(GroupID),0) + 1,
												@ProductIncons
					FROM						dbo.Groups 
					PRINT '^<COMP>Product Inconsistencies Group added'
				END	
			
			IF NOT EXISTS(SELECT * FROM dbo.Departments WHERE [Group] = (SELECT GroupID FROM dbo.Groups WHERE GroupDescription = @ProductIncons) AND Department = @ProductIncons)
				BEGIN
					INSERT INTO	dbo.Departments
											( 
												Department ,
												[Group] 
											)
					SELECT						@ProductIncons,
												MAX(GroupID)
					FROM						dbo.Groups		
					PRINT '^<COMP>Product Inconsistencies Department added'							
				END	
				
			INSERT INTO dbo.StyleRef
									( 
										StyleID ,
										DefaultDescription ,
										DefaultProductOutline ,
										DefaultDept ,
										DefaultSupplier 
									)
			SELECT						StyleID,
										MIN(ProductDescription),
										MIN(ProductOutline),
										MIN(DeptID),
										MIN(SupplierID)
			FROM						dbo.PLU
			WHERE						StyleID NOT IN (SELECT StyleID FROM dbo.StyleRef)
			AND							StyleID IS NOT NULL
			GROUP BY					StyleID
			PRINT '^<COMP>Product Inconsistencies Styles added'
						
			IF NOT EXISTS(SELECT * FROM Suppliers WHERE [Supplier Name] = 'Supplier Inconsistencies')
				BEGIN
					INSERT INTO dbo.Suppliers
							( 
								[Supplier ID] ,
								[Supplier Name] 
							)
					VALUES  ( 
								@tmpSupplierID,
								'Supplier Inconsistencies'
							)				
					PRINT '^<COMP>Product Inconsistencies Supplier added'
				END							
									
     
			INSERT INTO dbo.PLU
									( 
										PLU,
										ProductDescription,
										ProductOutline,
										SalePrice1,
										DeptID,
										SupplierID,
										ProductTypeID,
										vatcode
									)
			SELECT						CusOrderDetailPLU,
										'MISSING CUSORDERDETAILS ENTRY',
										'MISSING CUSORDERDETAILS ENTRY',
										AVG(CusOrderDetailOriginalItemGrossPrice),
										(SELECT [Dept ID] FROM dbo.Departments WHERE Department = @ProductIncons),
										(SELECT [Supplier ID] FROM dbo.Suppliers WHERE [Supplier Name] = 'Supplier Inconsistencies'),
										(SELECT TOP (1) ProductTypeID FROM dbo.ProductTypes),
										MIN(CusOrderDetailVatCode)
			FROM						dbo.CusOrderDetails 
			WHERE						CusOrderDetailPLU NOT IN (SELECT PLU FROM dbo.PLU)
			GROUP BY					CusOrderDetailPLU		
			PRINT '^<COMP>PLUs created for missing CusOrderDetails entries'								
		END
	ELSE	
		PRINT '^<SKIP>No PLUs referencing Styles that don''t exist.'	
		
GO

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

	-- 6.

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

	PRINT '^<RUNN>Deleting orphaned ItemsSold Entries'	
		
	IF NOT EXISTS(SELECT * FROM sys.objects WHERE name = 'FK_ItemsSold_Transactions' AND type = 'F')
		BEGIN
			DELETE FROM			dbo.ItemsSold
			FROM				dbo.ItemsSold 
			LEFT OUTER JOIN		dbo.Transactions 
			ON					TranStore = ItemStore 
			AND					TranTill = ItemTill 
			AND					TranReceipt = ItemReceipt
			WHERE				TranTill IS NULL		
	
			PRINT '^<COMP>Orphaned ItemsSold Entries deleted'	
		END
					
GO

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

	-- 7.

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

	IF EXISTS(SELECT * FROM dbo.TillInfo WHERE TillType = 3 AND TillNumber <> 99)
		BEGIN
			PRINT '^<RUNN>Updating Web Tills to till 99'	
			DECLARE @OldTill SMALLINT
			SELECT @OldTill = TillNumber FROM dbo.TillInfo WHERE TillType = 3
			UPDATE dbo.TillInfo SET TillNumber = 99, TillID = 99 WHERE TillType = 3
			UPDATE dbo.Transactions SET TranTill = 99 WHERE TranTill = @OldTill
			UPDATE dbo.ItemsSold SET ItemTill = 99 WHERE ItemTill = @OldTill
			UPDATE dbo.CusOrder SET CusOrderTill = 99 WHERE CusOrderTill = @OldTill
			UPDATE dbo.TenderAmounts SET TenderTillNumber = 99 WHERE TenderTillNumber = @OldTill
			UPDATE dbo.ReceiptData SET ReceiptTill = 99 WHERE ReceiptTill = @OldTill
			UPDATE dbo.ExportFile SET Till = 99 WHERE Till = @OldTill
			PRINT '^<COMP>Web Tills Updated'	
		END	
	ELSE
		PRINT '^<SKIP>No tills of Type 3 to update.'	

GO
------------------------------------------------------------------------------------

	-- 8.

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

	IF NOT EXISTS(SELECT * FROM dbo.WebSetup)
		BEGIN
			--we need one entry in here
			
			PRINT '^<RUNN>Adding WebSetup row'	
			INSERT INTO dbo.WebSetup
			        ( 
			          WebSetupID 
			        )
			VALUES  ( 
			          0
			        )
			        
			PRINT '^<COMP>WebSetup row added'	
					        
		END
	ELSE
		BEGIN
			PRINT '^<SKIP>WebSetup row already present.'	
		END

GO
------------------------------------------------------------------------------------

	-- 9.

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

	IF NOT EXISTS(SELECT * FROM dbo.CRC32Values)
		BEGIN
			--we need this populated.
	
			IF EXISTS(	select COLUMN_NAME, TABLE_NAME
						from INFORMATION_SCHEMA.COLUMNS
						where TABLE_SCHEMA = 'dbo'
						and TABLE_NAME = 'CRC32Values'
						and COLUMN_NAME = 'ID'
						and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
						 )
				BEGIN
					ALTER TABLE dbo.CRC32Values DROP COLUMN ID
					ALTER TABLE dbo.CRC32Values ADD ID INT NOT NULL
				END
			
			PRINT '^<RUNN>Adding CRC32 Values'	
	
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (0, 0)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (1, 1996959894)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (2, 3993919788)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (3, 2567524794)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (4, 124634137)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (5, 1886057615)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (6, 3915621685)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (7, 2657392035)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (8, 249268274)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (9, 2044508324)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (10, 3772115230)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (11, 2547177864)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (12, 162941995)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (13, 2125561021)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (14, 3887607047)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (15, 2428444049)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (16, 498536548)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (17, 1789927666)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (18, 4089016648)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (19, 2227061214)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (20, 450548861)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (21, 1843258603)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (22, 4107580753)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (23, 2211677639)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (24, 325883990)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (25, 1684777152)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (26, 4251122042)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (27, 2321926636)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (28, 335633487)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (29, 1661365465)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (30, 4195302755)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (31, 2366115317)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (32, 997073096)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (33, 1281953886)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (34, 3579855332)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (35, 2724688242)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (36, 1006888145)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (37, 1258607687)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (38, 3524101629)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (39, 2768942443)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (40, 901097722)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (41, 1119000684)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (42, 3686517206)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (43, 2898065728)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (44, 853044451)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (45, 1172266101)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (46, 3705015759)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (47, 2882616665)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (48, 651767980)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (49, 1373503546)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (50, 3369554304)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (51, 3218104598)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (52, 565507253)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (53, 1454621731)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (54, 3485111705)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (55, 3099436303)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (56, 671266974)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (57, 1594198024)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (58, 3322730930)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (59, 2970347812)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (60, 795835527)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (61, 1483230225)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (62, 3244367275)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (63, 3060149565)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (64, 1994146192)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (65, 31158534)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (66, 2563907772)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (67, 4023717930)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (68, 1907459465)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (69, 112637215)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (70, 2680153253)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (71, 3904427059)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (72, 2013776290)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (73, 251722036)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (74, 2517215374)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (75, 3775830040)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (76, 2137656763)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (77, 141376813)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (78, 2439277719)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (79, 3865271297)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (80, 1802195444)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (81, 476864866)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (82, 2238001368)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (83, 4066508878)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (84, 1812370925)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (85, 453092731)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (86, 2181625025)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (87, 4111451223)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (88, 1706088902)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (89, 314042704)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (90, 2344532202)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (91, 4240017532)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (92, 1658658271)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (93, 366619977)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (94, 2362670323)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (95, 4224994405)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (96, 1303535960)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (97, 984961486)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (98, 2747007092)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (99, 3569037538)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (100, 1256170817)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (101, 1037604311)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (102, 2765210733)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (103, 3554079995)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (104, 1131014506)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (105, 879679996)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (106, 2909243462)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (107, 3663771856)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (108, 1141124467)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (109, 855842277)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (110, 2852801631)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (111, 3708648649)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (112, 1342533948)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (113, 654459306)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (114, 3188396048)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (115, 3373015174)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (116, 1466479909)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (117, 544179635)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (118, 3110523913)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (119, 3462522015)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (120, 1591671054)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (121, 702138776)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (122, 2966460450)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (123, 3352799412)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (124, 1504918807)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (125, 783551873)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (126, 3082640443)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (127, 3233442989)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (128, 3988292384)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (129, 2596254646)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (130, 62317068)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (131, 1957810842)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (132, 3939845945)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (133, 2647816111)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (134, 81470997)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (135, 1943803523)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (136, 3814918930)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (137, 2489596804)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (138, 225274430)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (139, 2053790376)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (140, 3826175755)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (141, 2466906013)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (142, 167816743)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (143, 2097651377)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (144, 4027552580)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (145, 2265490386)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (146, 503444072)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (147, 1762050814)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (148, 4150417245)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (149, 2154129355)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (150, 426522225)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (151, 1852507879)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (152, 4275313526)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (153, 2312317920)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (154, 282753626)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (155, 1742555852)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (156, 4189708143)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (157, 2394877945)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (158, 397917763)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (159, 1622183637)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (160, 3604390888)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (161, 2714866558)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (162, 953729732)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (163, 1340076626)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (164, 3518719985)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (165, 2797360999)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (166, 1068828381)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (167, 1219638859)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (168, 3624741850)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (169, 2936675148)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (170, 906185462)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (171, 1090812512)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (172, 3747672003)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (173, 2825379669)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (174, 829329135)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (175, 1181335161)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (176, 3412177804)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (177, 3160834842)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (178, 628085408)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (179, 1382605366)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (180, 3423369109)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (181, 3138078467)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (182, 570562233)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (183, 1426400815)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (184, 3317316542)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (185, 2998733608)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (186, 733239954)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (187, 1555261956)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (188, 3268935591)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (189, 3050360625)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (190, 752459403)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (191, 1541320221)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (192, 2607071920)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (193, 3965973030)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (194, 1969922972)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (195, 40735498)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (196, 2617837225)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (197, 3943577151)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (198, 1913087877)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (199, 83908371)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (200, 2512341634)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (201, 3803740692)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (202, 2075208622)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (203, 213261112)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (204, 2463272603)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (205, 3855990285)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (206, 2094854071)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (207, 198958881)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (208, 2262029012)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (209, 4057260610)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (210, 1759359992)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (211, 534414190)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (212, 2176718541)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (213, 4139329115)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (214, 1873836001)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (215, 414664567)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (216, 2282248934)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (217, 4279200368)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (218, 1711684554)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (219, 285281116)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (220, 2405801727)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (221, 4167216745)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (222, 1634467795)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (223, 376229701)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (224, 2685067896)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (225, 3608007406)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (226, 1308918612)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (227, 956543938)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (228, 2808555105)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (229, 3495958263)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (230, 1231636301)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (231, 1047427035)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (232, 2932959818)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (233, 3654703836)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (234, 1088359270)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (235, 936918000)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (236, 2847714899)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (237, 3736837829)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (238, 1202900863)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (239, 817233897)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (240, 3183342108)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (241, 3401237130)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (242, 1404277552)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (243, 615818150)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (244, 3134207493)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (245, 3453421203)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (246, 1423857449)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (247, 601450431)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (248, 3009837614)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (249, 3294710456)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (250, 1567103746)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (251, 711928724)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (252, 3020668471)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (253, 3272380065)
			INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (254, 1510334235)
INSERT INTO [dbo].[CRC32Values] ([ID], [Value]) VALUES (255, 755167117)
	
			PRINT '^<COMP>CRC32 Values added'	
					        
		END
	ELSE
		BEGIN
			PRINT '^<SKIP>CRC32 values already present.'	
		END

GO

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

	-- 10.

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

	IF EXISTS(SELECT * FROM dbo.ItemsSold WHERE ItemChannel IS NULL)
		BEGIN
			PRINT '^<RUNN>Populating ItemsSold.ItemChannel field'	
		
			UPDATE				dbo.ItemsSold
			SET					ItemChannel = CASE	WHEN ItemTill = 99 THEN
														--New General Invoice 
														1
													WHEN ISNULL(cusorder.CusOrderType,-1) = 3 THEN
														0 --If the CusOrder entry is mail order, then we just enter 0 as Mail Order Items do not go into the ItemsSold table.
													ELSE
														ISNULL(cusorder.CusOrderType,0)
													END
			FROM				dbo.ItemsSold
			
			INNER JOIN			dbo.Transactions
			ON					TranStore = ItemStore
			AND					TranTill = ItemTill
			AND					TranReceipt = ItemReceipt
			
			LEFT OUTER JOIN		dbo.CusOrder 
			ON					TranStore = CusOrderStore
			AND					TranTill = CusOrderTill
			AND					TranReceipt = CusOrderTransaction
			
			LEFT OUTER JOIN		dbo.CusOrderDetails 
			ON					dbo.CusOrder.CusOrderID = dbo.CusOrderDetails.CusOrderID
			AND					CusOrderStore = CusOrderDetailStore
			AND					ItemPLU = CusOrderDetailPLU
			
			WHERE				ItemChannel IS NULL
		
			PRINT '^<COMP>ItemChannel field updated'
		END
	ELSE
		PRINT '^<SKIP>No ItemsSold entries present with null channel.'
		
GO		

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

	-- 11.

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

	IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[SizeOrder]') AND name = N'PK_SizeOrder_1')
		BEGIN
			PRINT '^<SKIP>SizeOrder entries already unique.'
		END
	ELSE
		BEGIN
			PRINT '^<RUNN>Deleting SizeOrder duplicate entries'				
		
			DELETE FROM dbo.SizeOrder
			FROM dbo.SizeOrder INNER JOIN
			(SELECT *, ROW_NUMBER() OVER   (PARTITION BY SizeOrderStyle, SizeOrderSize ORDER BY SizeOrderStyle, SizeOrderSize) AS RowNum FROM dbo.SizeOrder
				INNER JOIN
				
					(	SELECT     COUNT(SizeOrderSize) AS SizeCount, SizeOrderSize AS TheSize, SizeOrderStyle AS TheStyle
						FROM         SizeOrder
						GROUP BY SizeOrderSize, SizeOrderStyle
						HAVING      (COUNT(SizeOrderSize) > 1)
					) AS tmp	ON tmp.TheStyle = dbo.SizeOrder.SizeOrderStyle
								AND tmp.TheSize = dbo.SizeOrder.SizeOrderSize
								
				) tblGrouped	ON dbo.SizeOrder.SizeOrderBy = tblGrouped.SizeOrderBy
								AND dbo.SizeOrder.SizeOrderSize = tblGrouped.SizeOrderSize
								AND dbo.SizeOrder.SizeOrderStyle = tblGrouped.SizeOrderStyle
			WHERE RowNum > 1	
			
			PRINT '^<COMP>SizeOrder Duplicated removed'
		END
		
GO		

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

	-- 11.

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

	--This part is designed to throw up what might fail in the Redgate section:
	
	IF EXISTS(SELECT * FROM plu WHERE  plu like  '%[^A-Za-z0-9_\-]%' escape '\' AND ISNULL(BadPLUCharacterOverride,0) = 0)
		BEGIN
			RAISERROR(N'Invalid PLUs present in PLU table',16,1)
		END

GO

	IF EXISTS(SELECT * FROM dbo.StyleRef WHERE  StyleID like  '%[^A-Za-z0-9_\-]%' escape '\' AND ISNULL(StyleBadCharacterOverride,0) = 0)
		BEGIN
			RAISERROR(N'Invalid Styles present in StyleRef table',16,1)
		END

GO

	IF EXISTS(SELECT * FROM dbo.PLU WHERE  StyleID like  '%[^A-Za-z0-9_\-]%' escape '\' AND ISNULL(BadPLUCharacterOverride,0) = 0)
		BEGIN
			RAISERROR(N'Invalid Styles present in PLU table',16,1)
		END

GO

	IF EXISTS(SELECT * FROM dbo.PLU WHERE VatCode NOT IN (SELECT VatID FROM dbo.VATCodes))
		BEGIN
			RAISERROR(N'PLUs present which reference non-existant VAT IDs',16,1)
		END

GO

	IF EXISTS(SELECT * FROM dbo.Customer WHERE CHARINDEX('-',CustomerID) <> 4)
		BEGIN
			RAISERROR(N'Customers are present which contain invalid Customer IDs',16,1)
		END

GO
	IF EXISTS(SELECT CusOrderDetailID FROM dbo.CusOrderDetails WHERE CusOrderDetailPLU NOT IN (SELECT PLU FROM dbo.PLU))
		BEGIN
			RAISERROR(N'CusOrderDetail entries are present which contain links to non-existant PLUs',16,1)
		END

GO
	IF EXISTS(SELECT * FROM dbo.InwardDetails WHERE PLU NOT IN (SELECT PLU FROM dbo.PLU))
		BEGIN
			RAISERROR(N'InwardDetail entries are present which contain links to non-existant PLUs',16,1)
		END

GO
	IF EXISTS(SELECT * FROM dbo.OrderDetails WHERE PLU NOT IN (SELECT PLU FROM dbo.PLU))
		BEGIN
			RAISERROR(N'OrderDetail entries are present which contain links to non-existant PLUs',16,1)
		END

GO

	IF EXISTS(SELECT * FROM dbo.PLU WHERE AssignedCustomer NOT IN (SELECT CustomerID FROM dbo.Customer))
		BEGIN
			--RAISERROR(N'PLU entries are present which contain links to non-existant Customers',16,1)
			UPDATE PLU set AssignedCustomer = '000-000000' WHERE AssignedCustomer NOT IN (SELECT CustomerID FROM dbo.Customer)
		END

GO

	IF EXISTS(SELECT * FROM dbo.StyleRef WHERE DefaultAssignedCustomer NOT IN (SELECT CustomerID FROM dbo.Customer))
		BEGIN
			--RAISERROR(N'StyleRef entries are present which contain links to non-existant Customers',16,1)
			UPDATE StyleRef set DefaultAssignedCustomer = '000-000000' WHERE DefaultAssignedCustomer NOT IN (SELECT CustomerID FROM dbo.Customer)
		END

GO

	IF EXISTS(SELECT * FROM dbo.PLU WHERE Season NOT IN (SELECT SeasonID FROM dbo.Season))
		BEGIN
			RAISERROR(N'PLU entries are present which contain links to non-existant Seasons',16,1)
		END

GO

	IF EXISTS(SELECT * FROM dbo.StyleRef WHERE DefaultSeason NOT IN (SELECT SeasonID FROM dbo.Season))
		BEGIN
			RAISERROR(N'StyleRef entries are present which contain links to non-existant Seasons',16,1)
		END

GO
------------------------------------------------------------------------------------

	-- 11.

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

	IF NOT EXISTS(SELECT * FROM dbo.Countries)
		BEGIN
			PRINT '^<RUNN>Inserting United Kingdom Entry into Countries table'		
			INSERT INTO Countries (CountryDesc, CountryISOCode, CountryCurrencyCode) VALUES ('United Kingdom', 'GB', 'GBP')
			PRINT '^<COMP>United Kingdom Country entry added'
		END
	ELSE
		BEGIN
			IF EXISTS(SELECT * FROM dbo.Customer WHERE Country NOT IN (SELECT CountryID FROM dbo.Countries))
				BEGIN
					PRINT '^<RUNN>Updating NULL Country Customer records to United Kingdom'		
					UPDATE dbo.Customer SET Country = (SELECT CountryID FROM dbo.Countries WHERE CountryISOCode = 'GB') 
					WHERE Country NOT IN (SELECT CountryID FROM dbo.Countries)
					PRINT '^<COMP>Customers updated to GB'
				END
			ELSE
				BEGIN
					PRINT '^<SKIP>No customer entries with NULL country column.'	
				END
				
			
		END
GO
		
------------------------------------------------------------------------------------

	-- 12.

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

	IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.routines WHERE SPECIFIC_NAME = 'FullUnitCosts')
	AND EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'PLU' AND COLUMN_NAME = 'FullUnitCost')
		BEGIN
			PRINT '^<RUNN>Dropping FullUnitCost column from PLU table'		
			ALTER TABLE dbo.PLU DROP COLUMN FullUnitCost
			PRINT '^<COMP>FullUnitCost column dropped from PLU table'
		END
	ELSE
		BEGIN
			PRINT '^<SKIP>Full Unit Cost column already updated.'	
		END

GO
------------------------------------------------------------------------------------

	-- 12.

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

	IF EXISTS(SELECT * FROM dbo.Suppliers WHERE [Supplier ID] NOT IN (SELECT SupplierID FROM dbo.SupplierDetails))
		BEGIN
			PRINT '^<RUNN>Inserting Main Office for Suppliers that don''t have one'	
			INSERT INTO dbo.SupplierDetails
					( 
						UseForOrders ,
						SupplierID ,
						SupplierDepartment 
					)
			SELECT		1,
						[Supplier ID],
						'Main Office'
			FROM		dbo.Suppliers 

			WHERE [Supplier ID] NOT IN (SELECT SupplierID FROM dbo.SupplierDetails)
			PRINT '^<COMP>Supplier Detail entries added.'
		END
	ELSE
		BEGIN
			PRINT '^<SKIP>There are no suppliers missing a Main Office entry.'	
		END
	
GO
------------------------------------------------------------------------------------

	-- 13.

------------------------------------------------------------------------------------	
	UPDATE StockMove SET
		
	StockMoveDate = 0
	
	WHERE StockMoveDate IS NULL

GO
------------------------------------------------------------------------------------

	-- 13.

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

	INSERT INTO dbo.AddField04
	        ( AddField04ID, AddField04Description)
	SELECT  TOP (1)  0,			'(Empty)'
	WHERE	NOT EXISTS(SELECT * FROM dbo.AddField04 WHERE AddField04ID = 0)
	
	
	INSERT INTO dbo.AddField05
	        ( AddField05ID, AddField05Description)
	SELECT  TOP (1)  0,			'(Empty)'
	WHERE	NOT EXISTS(SELECT * FROM dbo.AddField05 WHERE AddField05ID = 0)
	
	UPDATE	PLU	
	SET		AddField04 = 0
	WHERE	AddField04 NOT IN (SELECT AddField04ID FROM dbo.AddField04)
	
	UPDATE	dbo.PLU
	SET		AddField05 = 0
	WHERE	AddField05 NOT IN (SELECT AddField05ID FROM dbo.AddField05)
	
   UPDATE	dbo.StyleRef	
	SET		AddField04 = 0
	WHERE	AddField04 NOT IN (SELECT AddField04ID FROM dbo.AddField04)
	
	UPDATE	dbo.StyleRef
	SET		AddField05 = 0
	WHERE	AddField05 NOT IN (SELECT AddField05ID FROM dbo.AddField05)

GO
	
------------------------------------------------------------------------------------

	-- 13.

------------------------------------------------------------------------------------		
		
	UPDATE dbo.PLU SET Size = 'N/A' WHERE NOT (
													styleid is null or (ltrim(rtrim(ISNULL(size,''))) <> '' AND StyleID IS NOT NULL)
												)
												
GO

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

	-- 14.

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

	DELETE FROM dbo.PLUAttributeValues WHERE PLUAttributeLinkedID NOT IN (SELECT  PLUAttributeID FROM dbo.PLUAttributes)												
	
GO
	
------------------------------------------------------------------------------------

	-- 15. Removes invalid (blank) sizes that are part of a StyleRef

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

	UPDATE dbo.PLU SET Size = '-' WHERE 
	NOT (
	(([styleid] IS NULL OR ltrim(rtrim(isnull([size],'')))<>'' AND [StyleID] IS NOT NULL))
	)	

GO

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

	-- 16. CusOrderDetails - Anything with a Dispatch Note ID has to have a status 6 or greater

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

	UPDATE dbo.CusOrderDetails SET
	CusOrderDetailItemStatus = 6
	WHERE CusOrderDetailDispatchNoteID > 0 and CusOrderDetailItemStatus < 6

GO

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

	-- 17. Menu (PLU Menu) - Check data for Check Constraint (NHD0006472)

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

	DELETE FROM dbo.Menu
	WHERE MenuPLU NOT IN (SELECT PLU FROM PLU) AND MenuItemType = 1

GO

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

	-- 18. Remove invalid Parent PLU References.

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

	UPDATE PLU SET ParentPLU = NULL where ParentPLU  NOT IN (SELECT PLU FROM PLU)
	UPDATE PLU Set ParentPLU = NULL where ParentPLU = PLU

GO

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

	-- 19. Create and populate the Machining Use Table.

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

PRINT '^<RUNN> Creating dbo.MachiningStatusUse and default entries if it doesnt exist' 

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'MachiningStatusUse')
	BEGIN
		 CREATE TABLE [dbo].[MachiningStatusUse](
			[MachiningStatusUseID] [smallint] NOT NULL CONSTRAINT [DF_MachiningStatusUse_MachiningStatusUseID]  DEFAULT ((0)),
			[MachiningStatusUseName] [varchar](50) NOT NULL,
			[MachiningStatusUseOutline] [varchar](max) NULL,
		 CONSTRAINT [PK_MachiningStatusUse] PRIMARY KEY CLUSTERED 
		(
			[MachiningStatusUseID] ASC
		)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
		) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
		--ALTER TABLE [dbo].[KeyPadQuickText] ADD  CONSTRAINT [DF_KeyPadQuickText_KeyPadQuickTextID]  DEFAULT ((0)) FOR [KeyPadQuickTextID]

		INSERT INTO MachiningStatusUse (MachiningStatusUseID, MachiningStatusUseName, MachiningStatusUseOutline)
		SELECT 1 AS MachiningStatusUseID, DefMachining AS MachiningStatusUseName, DefMachining AS MachiningStatusUseOutline
		FROM Definitions

	END
GO

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

	-- 20. Ensure all Loyalty Points have a valid value.

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

PRINT '^<RUNN> Creating dbo.MachiningStatusUse and default entries if it doesnt exist' 

	UPDATE Customer SET
	LoyaltyPoints = 0
	WHERE LoyaltyPoints IS NULL

GO

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

	-- 20. Ensure all Customers have a category

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

PRINT '^<RUNN> Ensuring all Customers have a category' 


	IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[trgCusCat]'))
		BEGIN
			DROP TRIGGER [dbo].[trgCusCat]		
		END
		

	INSERT INTO dbo.CustomerCategory
			( 
				CustomerCategoryID ,
				CustomerCategoryName 
			)

	SELECT		CustomerCategory,
				'[unset ' + CONVERT(VARCHAR(10),CustomerCategory) + ']'
				
	FROM		Customer
	WHERE		CustomerCategory NOT IN ( SELECT    CustomerCategoryID
									  FROM      CustomerCategory )
	GROUP BY	CustomerCategory

GO

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

	-- 21. Updating Tender AmountDescriptions where blank.

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

	PRINT '^<RUNN> Updating Tender AmountDescriptions where blank' 
	
	UPDATE		TenderAmounts
    SET			TenderAmounts.TenderDescription = TenderTypes.TenderName ,
				TenderAmounts.TenderType = TenderTypes.TenderType
    FROM		TenderAmounts
            
    INNER JOIN	TenderTypes 
    ON			TenderAmounts.TenderID = TenderTypes.TenderID
    WHERE   	TenderAmounts.TenderDescription IS NULL 

GO

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

	-- 22.

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

	IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.routines WHERE SPECIFIC_NAME = 'Final')
	AND EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Inwards' AND COLUMN_NAME = 'Final')
		BEGIN
			PRINT '^<RUNN>Dropping Final column from Inwards table'		
			ALTER TABLE dbo.Inwards DROP COLUMN Final
			PRINT '^<COMP>Final column dropped from Inwards table'
		END
	ELSE
		BEGIN
			PRINT '^<SKIP>Final column already updated.'	
		END

GO

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

	-- 23. Removing orphaned CusOrderDetail entries

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

	PRINT '^<RUNN> Removing orphaned CusOrderDetail entries' 

	DELETE FROM CusOrderDetails
	FROM         CusOrderDetails LEFT OUTER JOIN
						  CusOrder ON CusOrderDetails.CusOrderID = CusOrder.CusOrderID AND CusOrder.CusOrderStore = CusOrderDetails.CusOrderDetailStore
	WHERE     (CusOrder.CusOrderID IS NULL)
	
GO

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

	-- 24. Ensuring Customer Accounts are Active

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

	PRINT '^<RUNN> Ensuring Customer Accounts are Active' 

	UPDATE GlobalConfig SET RunningCustomerAccounts = 1
	
GO
	
------------------------------------------------------------------------------------

	-- 25. Ensuring Customer Category Web Use table is populated

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

	PRINT '^<RUNN> Ensuring Customer Category Web Use table is populated' 

	DECLARE @Def VARCHAR(200)
	
	SELECT @Def = DefTradeCustomer FROM dbo.Definitions 

	IF NOT EXISTS(SELECT * FROM dbo.CustomerCategoryWebUse)
		BEGIN
			INSERT INTO dbo.CustomerCategoryWebUse
			        ( CustomerCategoryWebUseID ,
			          CustomerCategoryWebUseName
			        )
			VALUES  ( 1 , -- CustomerCategoryWebUseID - smallint
			          ISNULL(@Def,'Trade Customer')  -- CustomerCategoryWebUseName - varchar(50)
			        )
		END
			
GO


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

	-- 26. Ensuring Customer Category Web Use is Valid

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

	PRINT '^<RUNN> Ensuring Category Web Use is Valid' 

	UPDATE CustomerCategory SET
	CustomerCategoryWebUse = 1
	WHERE CustomerCategoryWebUse < 1
	
GO

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

	-- 27. PLU Table Notes Check

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

	PRINT '^<RUNN> Altering a Constriant in the PLU Table for Global Config Retrieval' 


	IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_PLU_NotesArePublicC]') AND type = 'D')
		BEGIN
			ALTER TABLE [dbo].[PLU] DROP CONSTRAINT [DF_PLU_NotesArePublicC]
		END

GO

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

	-- 28. New Categories Insert

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

	PRINT '^<RUNN> Inserting default category' 

	--insert the unassigned category
	IF NOT EXISTS(SELECT * FROM Category)
		BEGIN
			INSERT INTO dbo.Category
					( 
						CategoryID ,
						CategoryName ,
						CategorySEOName
					)
			SELECT		top (1)
						0 , -- CategoryID - int
						N'Unassigned' , -- CategoryName - nvarchar(50)
						'unassigned'  -- CategorySEOName - varchar(70)
		END

GO

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

	-- 29. Clear the TempCategoryIDPaths table.

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

	PRINT '^<RUNN> Clearing the TempCategoryIDPaths table' 

	--insert the unassigned category
	IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TempCategoryIDPaths')
		BEGIN
			DELETE FROM TempCategoryIDPaths
		END

GO

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

	-- 30. Populate the CategoryPLULink table with entries from the old PLUAttributeLinks.
		--(disabled until after the Category work has been done in the BOTK)
------------------------------------------------------------------------------------

--	IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'PLUAttributeLinks' AND COLUMN_NAME = 'PLUAttributeLinkTradeCustomerID')
--		BEGIN
--			DECLARE @SQL NVARCHAR(4000)

--			SELECT @SQL = '

--			INSERT INTO			TempPLUAttributeLinks (PLU, CustomerID)
--			SELECT				PLUAttributeLinkedPLU,
--								PLUAttributeLinkTradeCustomerID
--			FROM				PLUAttributeLinks

--			LEFT OUTER JOIN		(
--									SELECT		*
--									FROM		TempPLUAttributeLinks
--								)	tmp	
--			ON					tmp.PLU = PLUAttributeLinkedPLU
--			AND					tmp.CustomerID = PLUAttributeLinkTradeCustomerID

--			WHERE				PLUAttributeLinkToCustomer = 1
--			AND					tmp.PLU IS NULL
 
--			GROUP BY			PLUAttributeLinkedPLU,
--								PLUAttributeLinkTradeCustomerID
--			'

--			EXEC sys.sp_executesql @SQL

--		END

--GO


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

	-- 31. Deleting Global Configs Dates for Sales Period Summary

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

	IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'GlobalConfig' AND COLUMN_NAME = 'ProductSearchShowSalesDateFrom')
		BEGIN
			    IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'GlobalConfig' AND COLUMN_NAME = 'ProductSearchShowSalesDateFrom1')
					BEGIN
						PRINT '^<RUNN>Deleting ProductSearchShowSalesDateFrom1 from GlobalConfig'
						ALTER TABLE GlobalConfig
						DROP COLUMN ProductSearchShowSalesDateFrom1
					END

			PRINT '^<RUNN>Renaming GlobalConfig Column ProductSearchShowSalesDateFrom to ProductSearchShowSalesDateFrom1'
			EXEC sp_RENAME 'GlobalConfig.ProductSearchShowSalesDateFrom', 'ProductSearchShowSalesDateFrom1', 'COLUMN'
			PRINT '^<COMP>GlobalConfig Column ProductSearchShowSalesDateFrom is now called ProductSearchShowSalesDateFrom1'
		END

	IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'GlobalConfig' AND COLUMN_NAME = 'ProductSearchShowSalesDateTo')
		BEGIN
			IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'GlobalConfig' AND COLUMN_NAME = 'ProductSearchShowSalesDateTo1')
					BEGIN
						PRINT '^<RUNN>Deleting ProductSearchShowSalesDateTo1 from GlobalConfig'
						ALTER TABLE GlobalConfig
						DROP COLUMN ProductSearchShowSalesDateTo1
					END

			PRINT '^<RUNN>Renaming GlobalConfig Column ProductSearchShowSalesDateTo to ProductSearchShowSalesDateTo1'
			EXEC sp_RENAME 'GlobalConfig.ProductSearchShowSalesDateTo', 'ProductSearchShowSalesDateTo1', 'COLUMN'
			PRINT '^<COMP>GlobalConfig Column ProductSearchShowSalesDateTo is now called ProductSearchShowSalesDateTo1'
		END

	


GO

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

	-- 32. Update the Product Menu Parents

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

	UPDATE dbo.Menu SET MenuParent = NULL WHERE MenuParent = 0
	
	UPDATE				dbo.Menu 
	SET					MenuParent = NULL

	FROM				dbo.Menu 
	LEFT OUTER JOIN		dbo.Menu ParentTbl
	ON					dbo.Menu.MenuParent = ParentTbl.MenuItemID

	WHERE				ParentTbl.MenuItemID IS NULL
	AND					Menu.MenuParent IS NOT NULL
	
GO


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

	-- 33. Add Customer Follow On Actions

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

	IF NOT EXISTS(SELECT * FROM CustFollowOnAction WHERE CustFollowOnActionID = 0)
		BEGIN
			INSERT INTO CustFollowOnAction(CustFollowOnActionID, CustFollowOnActionName)
			SELECT 0, 'No Action'
		END

	IF NOT EXISTS(SELECT * FROM CustFollowOnAction WHERE CustFollowOnActionID = 1)
		BEGIN
			INSERT INTO CustFollowOnAction(CustFollowOnActionID, CustFollowOnActionName)
			SELECT 1, 'Phone'
		END

	IF NOT EXISTS(SELECT * FROM CustFollowOnAction WHERE CustFollowOnActionID = 2)
		BEGIN
			INSERT INTO CustFollowOnAction(CustFollowOnActionID, CustFollowOnActionName)
			SELECT 2, 'Text/SMS'
		END

	IF NOT EXISTS(SELECT * FROM CustFollowOnAction WHERE CustFollowOnActionID = 3)
		BEGIN
			INSERT INTO CustFollowOnAction(CustFollowOnActionID, CustFollowOnActionName)
			SELECT 3, 'Email'
		END
	
	IF NOT EXISTS(SELECT * FROM CustFollowOnAction WHERE CustFollowOnActionID = 4)
		BEGIN
			INSERT INTO CustFollowOnAction(CustFollowOnActionID, CustFollowOnActionName)
			SELECT 4, 'Post'
		END
	
	
GO

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

	-- 34. Ensure CategoryParent column allows NULLs

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

	IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Category' AND COLUMN_NAME = 'CategoryParent' AND IS_NULLABLE = 'NO')
		BEGIN
			ALTER TABLE dbo.Category ALTER COLUMN CategoryParent INT NULL

			UPDATE Category set CategoryParent = NULL WHERE CategoryID = 0
		END
GO

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

	-- 35. Delete invalid PLU attributes

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

	DELETE FROM dbo.PLUAttributeLinks 
	FROM dbo.PLUAttributeLinks 
	INNER JOIN			dbo.PLU ON dbo.PLUAttributeLinks.PLUAttributeLinkedPLU = dbo.PLU.PLU
	LEFT OUTER JOIN		dbo.StyleRef ON dbo.PLU.StyleID = dbo.StyleRef.StyleID
	LEFT OUTER JOIN		dbo.Customer ON PLUAttributeLinkTradeCustomerID = CustomerID
	WHERE PLUAttributeLinkToCustomer = 1 
	AND (
			(PLU.PLU IS NULL)
		OR	(PLU.StyleID IS NOT NULL AND DefaultAssignedCustomer != '000-000000') 
		OR	(PLU.StyleID IS NULL AND AssignedCustomer != '000-000000') 
		OR	(ISNULL(CustomerType,1) <> 0)
		)

GO

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

	-- 36. Undate orphaned Departments

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

	UPDATE dbo.PLU SET DeptID = (SELECT MIN([Dept ID]) FROM dbo.Departments WHERE Department = 'Product Inconsistencies')
	FROM dbo.PLU LEFT OUTER JOIN dbo.Departments ON plu.DeptID = [Dept ID]
	WHERE [Dept ID] IS NULL

	UPDATE dbo.StyleRef SET DefaultDept = (SELECT MIN([Dept ID]) FROM dbo.Departments WHERE Department = 'Product Inconsistencies')
	FROM dbo.StyleRef LEFT OUTER JOIN dbo.Departments ON DefaultDept = [Dept ID]
	WHERE [Dept ID] IS NULL

GO
------------------------------------------------------------------------------------

	-- 37. Set the package Type column in the product Scan table

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

	UPDATE ProductScan SET PackageType = 0 WHERE PackageType IS NULL

GO

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

	-- 38. Delete duplicate PLUAttributeLink Entries

------------------------------------------------------------------------------------
         
	IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'PLUAttributeLinks' AND COLUMN_NAME = 'OLD_PLUAttributeLinkToCustomer')
		BEGIN
			EXEC sys.sp_executesql N'UPDATE	PLUAttributeLinks
			SET		PLUAttributeLinks.PLUAttributeLinkToCustomer = OLD_PLUAttributeLinkToCustomer,
					PLUAttributeLinkTradeCustomerID = OLD_PLUAttributeLinkTradeCustomerID
			WHERE	PLUAttributeLinkToCustomer IS NULL'	
		END

	delete from PLUAttributeLinks where PLUAttributeLinkToCustomer = 0 and PLUAttributeLinkedValueID = -1

	DELETE FROM PLUAttributeLinks
	FROM dbo.PLUAttributeLinks
	INNER JOIN (
					SELECT      PLUAttributeLinkToCustomer AS CustBit,
								PLUAttributeLinkTradeCustomerID AS CustID,
								PLUAttributeLinkedValueID AS Value,
								PLUAttributeLinkedPLU AS PLU,
								MIN(PLUAttributeLinkID) MinLinkID
					FROM        dbo.PLUAttributeLinks
					GROUP BY    PLUAttributeLinkToCustomer ,
								PLUAttributeLinkTradeCustomerID ,
								PLUAttributeLinkedValueID ,
								PLUAttributeLinkedPLU
					HAVING		COUNT(*) > 1							
				) tmp
	ON		dbo.PLUAttributeLinks.PLUAttributeLinkedPLU = plu	
	AND		dbo.PLUAttributeLinks.PLUAttributeLinkedValueID = Value
	AND		dbo.PLUAttributeLinks.PLUAttributeLinkToCustomer = CustBit
	AND		dbo.PLUAttributeLinks.PLUAttributeLinkTradeCustomerID = CustID
	WHERE	dbo.PLUAttributeLinks.PLUAttributeLinkID != MinLinkID

GO

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

	-- 39. Breakages - Remove invalid PLUs

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

	delete from Breakages where BreakPLU not in (select PLU from PLU)

GO

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

	-- 40. MachiningComponentLinks - Set zero-length entries to null

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

	update MachiningComponentLinks set MachiningComponentLinkPLU = NULL where MachiningComponentLinkPLU = ''
	update MachiningComponentLinks set MachiningComponentLinkStyle = NULL where MachiningComponentLinkStyle = ''

GO

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

	-- 41. Picking List Details - Remove invalid PLUs

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

	delete from PickingListDetails where PLU not in (select PLU from PLU)

GO

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

	-- 42. Correcting any invalid Online Product Availablity Options

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

	UPDATE      dbo.StyleRef
	SET         StyleWebOnlineProductAvailability = ISNULL(DefaultProductAvailabilityOption,1)
	FROM		dbo.StyleRef
	CROSS JOIN	dbo.WebSetup
	WHERE       NOT (StyleWebOnlineProductAvailability BETWEEN 1 AND 4)

	UPDATE      dbo.PLU
	SET         PLUWebOnlineProductAvailability = ISNULL(DefaultProductAvailabilityOption,1)
	FROM		dbo.PLU
	CROSS JOIN	dbo.WebSetup
	WHERE       NOT (PLUWebOnlineProductAvailability BETWEEN 1 AND 4)

GO

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

	-- 43. Machining Component Links.

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

	IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'MachiningComponentLinkPLU' AND TABLE_NAME = 'MachiningComponentLinks' AND IS_NULLABLE = 'NO')
		BEGIN
			ALTER TABLE dbo.MachiningComponentLinks DROP COLUMN MachiningComponentUniqueID
			ALTER TABLE dbo.MachiningComponentLinks ALTER COLUMN MachiningComponentLinkPLU nvarchar(35) NULL
			ALTER TABLE dbo.MachiningComponentLinks ALTER COLUMN MachiningComponentLinkStyle varchar(12) NULL
		
			UPDATE dbo.MachiningComponentLinks SET MachiningComponentLinkPLU = NULL WHERE MachiningComponentLinkPLU = ''
			UPDATE dbo.MachiningComponentLinks SET MachiningComponentLinkStyle = NULL WHERE MachiningComponentLinkStyle = ''	
		END

GO

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

	-- 44. Remove invalid layaway PLUs

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

	DELETE FROM dbo.LayawayDetails WHERE LAPLU NOT IN (SELECT PLU FROM dbo.PLU)

GO

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

	-- 45. Remove invalid Batch PLUs

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

	DELETE FROM PLUBatch WHERE PLUBatchPLU NOT IN (SELECT PLU FROM dbo.PLU)

GO

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

	-- 46. Remove invalid Purchase Order Items

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

	DELETE FROM dbo.OrderDetails WHERE OrderID NOT IN ( SELECT OrderID FROM dbo.Orders)

GO

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

	-- 47. Insert Shipping Zone

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

	IF NOT EXISTS(SELECT * FROM ShippingZone)
		BEGIN
			INSERT INTO dbo.ShippingZone
						( ShippingZoneName ,
						  ShippingZonesNotes ,
						  ShippingZonesActive ,
						  ShippingZonesChargeTax
						)
			VALUES      ( 'Main Shipping Zone' , -- ShippingZoneName - varchar(50)
						  '' , -- ShippingZonesNotes - varchar(400)
						  1 , -- ShippingZonesActive - bit
						  1  -- ShippingZonesChargeTax - bit
						)

			update Countries set CountryShippingZone = (select ShippingZoneID from ShippingZone)
		END

GO

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

	-- 48. Insert Agent Settings

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

	IF NOT EXISTS(SELECT * FROM AgentSetup)
		BEGIN
			INSERT INTO dbo.AgentSetup	( AgentSetupActive 	)
			VALUES      ( 0	)
		END
GO
------------------------------------------------------------------------------------

	-- 49. Size Order Style Constraint

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

	DELETE FROM SizeOrder WHERE SizeOrderStyle NOT IN (SELECT StyleID FROM StyleRef)

GO


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

	-- 50. Prep Customers in CustomerLookup table

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

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'CustomerLookup' AND COLUMN_NAME = 'CustomerLookupCustomerID' AND IS_NULLABLE = 'NO')
	BEGIN
		ALTER TABLE [dbo].[CustomerLookup] ALTER COLUMN [CustomerLookupCustomerID] [varchar] (10) NULL
		UPDATE CustomerLookup SET CustomerLookupCustomerID = NULL WHERE CustomerLookupCustomerID NOT IN (SELECT CustomerID FROM Customer)
	END
GO


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

	-- 51. PLUStore table

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

	DELETE from PLUStore
	WHERE PLUStorePLU NOT IN (SELECT PLU FROM PLU)

GO

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

	-- 52. SecondaryPLU cannot be the same as the PLU number

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

	UPDATE      PLU
	SET         SecondaryPLU = NULL
	WHERE       SecondaryPLU = PLU

GO

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

	-- 53. Orphaned Shipping Rates

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

	delete from ShippingRates where ShippingRatesShippingTypeID not in (select ShippingTypeID from ShippingTypes)

GO

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

	-- 54. Orphaned Stock Taking Entries

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

	DELETE FROM dbo.StockTakingEntries WHERE StockTakingEntryPLU NOT IN (SELECT PLU FROM dbo.PLU)

GO

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

	-- 55. Invalid Account Payments

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

	UPDATE dbo.AccountAdjustment
	SET AdjustmentCustomer = '000-000000'
	WHERE AdjustmentCustomer NOT IN (SELECT CustomerID FROM dbo.Customer)
	
	UPDATE dbo.AccountPayment
	SET PaymentCustomer = '000-000000'
	WHERE PaymentCustomer NOT IN (SELECT CustomerID FROM dbo.Customer)
	
	UPDATE dbo.AccountTransactions
	SET AccountCustID = '000-000000'
	WHERE AccountCustID NOT IN (SELECT CustomerID FROM dbo.Customer)
	

GO

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

	-- 56. Validate Stock Take Option

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

	update	globalconfig	set StockTakeCommence = null where StockTaking = 0 and StockTakeCommence is not null

GO

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

	-- 57. Set the new GlobalConfig.StockDefaultAllowZeroStock field to match the old GlobalConfig.StockAllowMinusOnNew

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

	IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'GlobalConfig' AND COLUMN_NAME = 'StockAllowMinusOnNew')
		BEGIN
			EXEC sys.sp_executesql N'UPDATE GlobalConfig SET StockDefaultAllowZeroStock = StockAllowMinusOnNew'
		END


GO


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

	-- 58. Set any Vouchers to a Minus Figure rather than a plus figure

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

	UPDATE CreditNotes SET
	CreditNoteAmount = CreditNoteAmount * -1
	where CreditNoteType = 1 AND CreditNoteAmount > 0

GO


	UPDATE CreditNotes SET
	CreditNoteAmount = CreditNoteAmount * -1
	where CreditNoteType = 1 AND CreditNoteAmount > 0

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

	-- 59. There is a constraint on the db to ensure that you must put a company name in for trade customers
    --	   so this just makes it manditory

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

	UPDATE GlobalConfig set CustReqDataTradeCompany = 1 where CustReqDataTradeCompany = 0

GO

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

	-- 60. The CusContactTitle now has a foreign key on CusTitle table. Allows Nulls

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

	IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Customer' AND COLUMN_NAME = 'CustomerContactTitle' AND IS_NULLABLE = 'NO')
		BEGIN
			ALTER TABLE [dbo].[Customer] ALTER COLUMN [CustomerContactTitle] [smallint] NULL
		END

	UPDATE dbo.Customer SET CustomerContactTitle = NULL WHERE CustomerContactTitle NOT IN (SELECT CusTitleID FROM dbo.CusTitle)
	
GO

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

	-- 61. The ItemsSold.ItemUnitPrice should always be positive.

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

	update ItemsSold set ItemUnitPrice = ItemUnitPrice * -1 where ItemUnitPrice < 0
	update ItemsSold set ItemRefund = 1 where ItemSaleValue < 0 and ItemNETValue < 0 and ItemQty < 0 and ItemRefund = 0
	update ItemsSold set ItemRefund = 0 where ItemSaleValue > 0 and ItemNETValue > 0 and ItemQty > 0 and ItemRefund = 1
	update ItemsSold set ItemRefund = 1 where ItemRefund = 0 and ItemQty < 0
	update ItemsSold set ItemRefund = 0 where ItemRefund = 1 and ItemQty > 0
	UPDATE ItemsSold SET ItemDiscount = ItemDiscount * -1 WHERE ItemDiscount > 0 AND ItemQty < 0

GO

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

	-- 62. Prepare for Dispatch/Invoice constraints

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


	IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'CusOrderDetails' AND COLUMN_NAME = 'CusOrderDetailDispatchNoteID' AND IS_NULLABLE = 'NO')
		BEGIN
			ALTER TABLE dbo.CusOrderDetails ALTER COLUMN CusOrderDetailDispatchNoteID INT NULL
		END

	UPDATE				dbo.CusOrderDetails 
	SET					CusOrderDetailDispatchNoteID = NULL 
	FROM				dbo.CusOrderDetails
	LEFT OUTER JOIN		dbo.CusDispatch
	ON					CusDispatchNoteID = CusOrderDetailDispatchNoteID
	AND					CusDispatchStoreID = CusOrderDetailStore
	WHERE				CusDispatchNoteID IS NULL
	AND					CusOrderDetailDispatchNoteID IS NOT NULL

	IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'CusOrderDetails' AND COLUMN_NAME = 'CusOrderDetailInvoiceID' AND IS_NULLABLE = 'NO')
		BEGIN
			ALTER TABLE dbo.CusOrderDetails ALTER COLUMN CusOrderDetailInvoiceID INT NULL
		END

	UPDATE				dbo.CusOrderDetails
	SET					CusOrderDetailInvoiceID = NULL
	FROM				dbo.CusOrderDetails
	LEFT OUTER JOIN		dbo.CusInvoice
	ON					CusOrderDetailInvoiceID = CusInvoiceID
	AND					CusOrderDetailStore = CusInvoiceStoreID
	WHERE				CusInvoiceID IS NULL
	AND					CusOrderDetailInvoiceID IS NOT NULL
	
GO

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

	-- 63. Remove slashes from category seo names

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

	update Category set CategorySEOName = REPLACE(CategorySEOName,'/','-')
	where CategorySEOName like '%/%'	

GO

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

	-- 64. Preparing for new Inward Constraints

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

	DELETE FROM			dbo.InwardDetails
	FROM				dbo.InwardDetails
	LEFT OUTER JOIN		dbo.Inwards 
	ON					dbo.InwardDetails.InwardID = dbo.Inwards.InwardID
	WHERE				Inwards.InwardID IS NULL
	
	IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'InwardDetails' AND COLUMN_NAME = 'InwardStore')
		BEGIN
			ALTER TABLE [dbo].[InwardDetails] ADD [InwardStore] [char] (3) NOT NULL
			 CONSTRAINT [DF_InwardDetails_InwardStore] DEFAULT ([dbo].[getstorenumber]())
		END
	
GO
	
	
	UPDATE				dbo.InwardDetails 
	SET					OrderID = NULL
	FROM				dbo.InwardDetails
	LEFT OUTER JOIN		dbo.OrderDetails ON dbo.InwardDetails.OrderID = DetailID
	AND					OrderStore = InwardStore
	WHERE				DetailID IS NULL
	AND					InwardDetails.OrderID IS NOT NULL
	
GO

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

	-- 65. Update Invalid To Recieve Quantities ready for constraint.

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

	update OrderDetails set IndUnitsToReceive = 0 where IndUnitsToReceive < 0

GO

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

	-- 66. Update Web Category Names/Outlines

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

--	if exists(select * from WebSetup where UsingOldGroupsAndDepts = 0)
--		BEGIN
--			update Category set CategoryWebTitle = left(CategoryName,50) where CategoryWebTitle is NULL
--			update Category set CategoryWebOutline = left(CategoryOutline,4000) where CategoryWebOutline is NULL
--		END
--GO

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

	-- 67. Insert Default Channels

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

	IF NOT EXISTS(SELECT * FROM dbo.ChannelTypes WHERE ChannelType = -1) INSERT INTO dbo.ChannelTypes (ChannelType , ChannelTypeDescription, ChannelTypeGenerateSale, ChannelTypeGenerateOrder, ChannelTypeStock) VALUES (-1 , 'Carry Out Sale' ,1, 0, 1)
	IF NOT EXISTS(SELECT * FROM dbo.ChannelTypes WHERE ChannelType = 0)  INSERT INTO dbo.ChannelTypes (ChannelType , ChannelTypeDescription, ChannelTypeGenerateSale, ChannelTypeGenerateOrder, ChannelTypeAcceptsDeposits, ChannelTypeStock) VALUES (0  , 'Customer Order' ,2, 0, 1, 2)
	IF NOT EXISTS(SELECT * FROM dbo.ChannelTypes WHERE ChannelType = 1)  INSERT INTO dbo.ChannelTypes (ChannelType , ChannelTypeDescription, ChannelTypeGenerateSale, ChannelTypeGenerateOrder, ChannelTypeStock) VALUES (1  , 'Customer Sale'  ,0, 1, 2)
	IF NOT EXISTS(SELECT * FROM dbo.ChannelTypes WHERE ChannelType = 2)  INSERT INTO dbo.ChannelTypes (ChannelType , ChannelTypeDescription, ChannelTypeGenerateSale, ChannelTypeGenerateOrder, ChannelTypeStock) VALUES (2  , 'Web Sale'       ,0, 2, 2)
	IF NOT EXISTS(SELECT * FROM dbo.ChannelTypes WHERE ChannelType = 3)  INSERT INTO dbo.ChannelTypes (ChannelType , ChannelTypeDescription, ChannelTypeGenerateSale, ChannelTypeGenerateOrder, ChannelTypeStock) VALUES (3  , 'Mail Order'     ,0, 1, 0)
	IF NOT EXISTS(SELECT * FROM dbo.ChannelTypes WHERE ChannelType = 4)  INSERT INTO dbo.ChannelTypes (ChannelType , ChannelTypeDescription, ChannelTypeGenerateSale, ChannelTypeGenerateOrder, ChannelTypeStock) VALUES (4  , 'eBay Sale'      ,0, 2, 2)
	IF NOT EXISTS(SELECT * FROM dbo.ChannelTypes WHERE ChannelType = 5)  INSERT INTO dbo.ChannelTypes (ChannelType , ChannelTypeDescription, ChannelTypeGenerateSale, ChannelTypeGenerateOrder, ChannelTypeStock) VALUES (5  , 'Amazon Order'   ,0, 2, 2)
	IF NOT EXISTS(SELECT * FROM dbo.ChannelTypes WHERE ChannelType = 6)  INSERT INTO dbo.ChannelTypes (ChannelType , ChannelTypeDescription, ChannelTypeGenerateSale, ChannelTypeGenerateOrder, ChannelTypeStock) VALUES (6  , 'Pupil Premium'  ,0, 0, 0)

	IF NOT EXISTS(SELECT * FROM dbo.Channels WHERE ChannelID = -1) INSERT INTO dbo.Channels (ChannelID , ChannelType , ChannelDescription) VALUES ( -1 , -1 , 'Carry Out Sales')
	IF NOT EXISTS(SELECT * FROM dbo.Channels WHERE ChannelID = 0)  INSERT INTO dbo.Channels (ChannelID , ChannelType , ChannelDescription) VALUES ( 0 , 0 , 'Customer Orders')
	IF NOT EXISTS(SELECT * FROM dbo.Channels WHERE ChannelID = 1)  INSERT INTO dbo.Channels (ChannelID , ChannelType , ChannelDescription) VALUES ( 1 , 1 , 'Customer Sales')
	IF NOT EXISTS(SELECT * FROM dbo.Channels WHERE ChannelID = 2)  INSERT INTO dbo.Channels (ChannelID , ChannelType , ChannelDescription) VALUES ( 2 , 2 , 'Web Sales')
	IF NOT EXISTS(SELECT * FROM dbo.Channels WHERE ChannelID = 3)  INSERT INTO dbo.Channels (ChannelID , ChannelType , ChannelDescription) VALUES ( 3 , 3 , 'Mail Orders')
	IF NOT EXISTS(SELECT * FROM dbo.Channels WHERE ChannelID = 4)  INSERT INTO dbo.Channels (ChannelID , ChannelType , ChannelDescription) VALUES ( 4 , 4 , 'eBay Sales')
	IF NOT EXISTS(SELECT * FROM dbo.Channels WHERE ChannelID = 5)  INSERT INTO dbo.Channels (ChannelID , ChannelType , ChannelDescription) VALUES ( 5 , 5 , 'Amazon Orders')
	IF NOT EXISTS(SELECT * FROM dbo.Channels WHERE ChannelID = 6)  INSERT INTO dbo.Channels (ChannelID , ChannelType , ChannelDescription) VALUES ( 6 , 6 , 'Pupil Premium')

	UPDATE dbo.ChannelTypes SET ChannelTypeGenerateSale = 0 WHERE ChannelTypeGenerateSale IS NULL AND ChannelType IN (1,2,3,4)
	UPDATE dbo.ChannelTypes SET ChannelTypeGenerateSale = 1 WHERE ChannelTypeGenerateSale IS NULL AND ChannelType IN (-1)
	UPDATE dbo.ChannelTypes SET ChannelTypeGenerateSale = 2 WHERE ChannelTypeGenerateSale IS NULL AND ChannelType IN (0,5)
	
	UPDATE dbo.ChannelTypes SET ChannelTypeGenerateOrder = 0 WHERE ChannelTypeGenerateOrder IS NULL AND ChannelType IN (-1,0)
	UPDATE dbo.ChannelTypes SET ChannelTypeGenerateOrder = 1 WHERE ChannelTypeGenerateOrder IS NULL AND ChannelType IN (1,3)
	UPDATE dbo.ChannelTypes SET ChannelTypeGenerateOrder = 2 WHERE ChannelTypeGenerateOrder IS NULL AND ChannelType IN (2,4,5)

	UPDATE dbo.ChannelTypes SET ChannelTypeAcceptsDeposits = 1 WHERE ChannelTypeAcceptsDeposits IS NULL AND ChannelType = 0

	UPDATE dbo.ChannelTypes SET ChannelTypeStock = 0 WHERE ChannelTypeStock IS NULL AND ChannelType IN (3)
	UPDATE dbo.ChannelTypes SET ChannelTypeStock = 1 WHERE ChannelTypeStock IS NULL AND ChannelType IN (-1)
	UPDATE dbo.ChannelTypes SET ChannelTypeStock = 2 WHERE ChannelTypeStock IS NULL AND ChannelType IN (0,1,2,4,5)
	
	IF NOT EXISTS	(SELECT * FROM dbo.TenderTypes tt WHERE tt.TenderID	= -8)
		BEGIN	
			INSERT INTO TenderTypes (TenderID, TenderName, TenderActive, TenderType, TenderAllowChange, TenderUseCashDrawer, TenderAllowRefunds)
			SELECT -8 As TenderID, 'Pupil Premium' As TenderName, 0 as TenderActive, 1 as TenderType,
						0 as TenderAllowChange, 0 as TenderUseCashDrawer, 0 as TenderAllowRefunds
		END	

GO

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

	-- 68. Delete the Web Category Links table

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

	IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'WebCategoryLinks')
		BEGIN
			TRUNCATE TABLE WebCategoryLinks
		END
GO

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

	-- 69. Insert Default Measure

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

IF NOT EXISTS(SELECT * FROM dbo.Measures)
	BEGIN
		INSERT INTO dbo.Measures
		            ( MeasuresCode ,
		              MeasuresDescription ,
					  MeasuresType
		            )
		VALUES      ( N'cm' , -- MeasuresCode - nvarchar(5)
		              N'Centimetres',  -- MeasuresDescription - nvarchar(50)
					  1
		            )
	END
	
GO	

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

	-- 70. Fix negative pricing.

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

	UPDATE dbo.PLU SET SalePrice1 = SalePrice1 * -1 WHERE SalePrice1 < 0
	UPDATE dbo.PLU SET SalePrice2 = SalePrice2 * -1 WHERE SalePrice2 < 0
	UPDATE dbo.PLU SET SalePrice3 = SalePrice3 * -1 WHERE SalePrice3 < 0
	UPDATE dbo.PLU SET SalePrice4 = SalePrice4 * -1 WHERE SalePrice4 < 0
	UPDATE dbo.PLU SET SalePrice5 = SalePrice5 * -1 WHERE SalePrice5 < 0
	UPDATE dbo.PLU SET SalePrice6 = SalePrice6 * -1 WHERE SalePrice6 < 0
	UPDATE dbo.PLU SET SalePrice7 = SalePrice7 * -1 WHERE SalePrice7 < 0
	UPDATE dbo.PLU SET SalePrice8 = SalePrice8 * -1 WHERE SalePrice8 < 0
	UPDATE dbo.PLU SET SalePrice9 = SalePrice9 * -1 WHERE SalePrice9 < 0
	UPDATE dbo.PLU SET SalePrice10 = SalePrice10 * -1 WHERE SalePrice10 < 0
	
GO

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

	-- 71. Prep for TenderType being NOT NULL.

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

	UPDATE		dbo.TenderAmounts
	SET			TenderType = 1,
				TenderDescription = 'Unknown'
	WHERE		TenderType IS NULL

GO

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

	-- 72. Orphaned Layaway Details

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

	DELETE	FROM dbo.LayawayDetails WHERE LANumber NOT IN ( SELECT l.LayawayID FROM dbo.Layaway l)

GO

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

	-- 72. Orphaned Layaway Details

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

	IF EXISTS	(SELECT	* FROM dbo.Slots s WHERE s.PLUBatchID IS NULL)
		BEGIN	
			TRUNCATE TABLE dbo.SlotSKUs
			TRUNCATE TABLE dbo.Slots
		END	
GO

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

	-- 73. Copy over web category titles
	
------------------------------------------------------------------------------------
	
	IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Category' AND COLUMN_NAME = 'CategoryWebTitle')
		BEGIN
			EXEC sys.sp_executesql N'	UPDATE				dbo.Category
										SET					CategoryName = CategoryWebTitle
										WHERE				RTRIM(LTRIM(ISNULL(CategoryWebTitle,''''))) <> ''''
									'
		END
	
	IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Category' AND COLUMN_NAME = 'CategoryWebOutline')
		BEGIN
			EXEC sys.sp_executesql N'	UPDATE				dbo.Category
										SET					CategoryOutline = CategoryWebOutline
										WHERE				RTRIM(LTRIM(ISNULL(CategoryWebOutline,''''))) <> ''''
									'
		END
GO


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

	-- 74. Fix Channel Text for eReceipts
	
------------------------------------------------------------------------------------

	 UPDATE	Channels SET Channels.ChannelEReceiptText = CASE channelid WHEN 0 THEN 'ORDERED ITEM'
																								WHEN 3 THEN 'MAIL ORDER ITEM'
																								ELSE '' end
	 WHERE	Channels.ChannelEReceiptText = '-'
	

GO

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

	-- 75. Remove invalid PLU entries from ImageBank
	
------------------------------------------------------------------------------------

	 DELETE FROM dbo.ImageBank WHERE PLUID NOT IN (SELECT PLU FROM dbo.PLU AS p)

GO

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

	-- 76. Populate Tender Dates
	
------------------------------------------------------------------------------------

	 UPDATE			  dbo.TenderAmounts
	 SET				  TenderDateTime = ap.PaymentDate
	 FROM				  dbo.TenderAmounts AS ta
	 INNER JOIN		  dbo.CusInvoice ci 
	 ON				  ta.TenderTillNumber = ci.CusInvoiceTill
	 AND				  ta.TenderStore = ci.CusInvoiceStoreID	
	 AND				  ta.TenderReceipt	  = ci.CusInvoiceReceipt 
	 INNER JOIN		  dbo.AccountPayment AS ap
	 ON				  ci.CusInvoiceStoreID	   = ap.PaymentStore 
	 AND				  ci.CusInvoiceTill	 = ap.PaymentTill 
	 AND				  ci.CusInvoiceID = ap.PaymentReceipt
	 WHERE			  1=1
	 AND				  TenderDateTime IS NULL
	 AND				  ta.TenderTillNumber = 99

	 UPDATE				dbo.TenderAmounts
	 SET				TenderDateTime = ap.PaymentDate
	 FROM				dbo.TenderAmounts AS ta
	 INNER JOIN			dbo.AccountPayment AS ap
	 ON					ta.TenderStore = ap.PaymentStore 
	 AND				ta.TenderTillNumber = ap.PaymentTill 
	 AND				ta.TenderReceipt = ap.PaymentReceipt
	 WHERE				1=1
	 AND				TenderDateTime IS NULL
	 AND			   ta.TenderTillNumber != 99

	 UPDATE				dbo.TenderAmounts
	 SET				TenderDateTime = TranDateTime
	 FROM				dbo.TenderAmounts AS ta
	 INNER JOIN			dbo.Transactions AS t
	 ON					ta.TenderStore = t.TranStore 
	 AND				ta.TenderTillNumber = t.TranTill 
	 AND				ta.TenderReceipt = t.TranReceipt
	 WHERE				1=1
	 AND				TenderDateTime IS NULL
	 

GO

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

	-- 77. Fix invalid stock deduction locations
	
------------------------------------------------------------------------------------

	 UPDATE dbo.GlobalTillConfig
	 SET	 DefaultStockLocation = 1 WHERE NOT DefaultStockLocation	 BETWEEN 1 AND 3

 GO

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

	-- 78. Insert new Inward report
	
------------------------------------------------------------------------------------
 
	 IF NOT EXISTS(SELECT * FROM dbo.IntDocument AS id WHERE IntDocumentType = 4 AND IntDocumentID = 2)
		 BEGIN
			 INSERT INTO dbo.IntDocument
						 ( IntDocumentType ,
							IntDocumentID ,
							IntDocumentName ,
							IntDocumentOutline ,
							IntDocumentActive
						 )
			 SELECT        4 , -- IntDocumentType - int
							2 , -- IntDocumentID - int
							N'Parent and Child Inward Report' , -- IntDocumentName - nvarchar(50)
							N'Breaks down the inward showing the child PLUs underneath each parent PLU' , -- IntDocumentOutline - nvarchar(max)
							1  -- IntDocumentActive - bit
		 END

GO

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

	-- 79. Remove carriage returns from SecondaryPLU (NHD0106352)
	
------------------------------------------------------------------------------------

	 UPDATE plu SET SecondaryPLU = REPLACE(SecondaryPLU,CHAR(10),'') WHERE SecondaryPLU LIKE '%' + CHAR(10) + '%'
	 UPDATE plu SET SecondaryPLU = REPLACE(SecondaryPLU,CHAR(13),'') WHERE SecondaryPLU LIKE '%' + CHAR(13) + '%'

GO

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

	-- 80. Upgrade to OPOS ver 3
	
------------------------------------------------------------------------------------

	 UPDATE dbo.GlobalTillConfig SET CurrencyChar = 8364 WHERE CurrencyChar = 213 --Euro
	 UPDATE dbo.GlobalTillConfig SET CurrencyChar = 163 WHERE CurrencyChar = 156 --Pound

GO

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

	-- 81. Nice button size for product menu buttons - see NHD0114353
	
------------------------------------------------------------------------------------

	if not exists (select SettingName from GlobalSettings where SettingName = 'EPOSDefaultMenuWidth')
		BEGIN
			Insert into GlobalSettings(SettingName, SettingDataType, SettingValue, SettingInserted, SettingLastUpdatedBy)
			VALUES ('EPOSDefaultMenuWidth', 6, '200', CURRENT_TIMESTAMP, 'ESKIMO')
		END

GO



	 IF NOT EXISTS	  (select table_view,
    object_type, 
    constraint_type,
    constraint_name,
    details
from (
    select schema_name(t.schema_id) + '.' + t.[name] as table_view, 
        case when t.[type] = 'U' then 'Table'
            when t.[type] = 'V' then 'View'
            end as [object_type],
        case when c.[type] = 'PK' then 'Primary key'
            when c.[type] = 'UQ' then 'Unique constraint'
            when i.[type] = 1 then 'Unique clustered index'
            when i.type = 2 then 'Unique index'
            end as constraint_type, 
        isnull(c.[name], i.[name]) as constraint_name,
        substring(column_names, 1, len(column_names)-1) as [details]
    from sys.objects t
        left outer join sys.indexes i
            on t.object_id = i.object_id
        left outer join sys.key_constraints c
            on i.object_id = c.parent_object_id 
            and i.index_id = c.unique_index_id
       cross apply (select col.[name] + ', '
                        from sys.index_columns ic
                            inner join sys.columns col
                                on ic.object_id = col.object_id
                                and ic.column_id = col.column_id
                        where ic.object_id = t.object_id
                            and ic.index_id = i.index_id
                                order by col.column_id
                                for xml path ('') ) D (column_names)
    where is_unique = 1
    and t.is_ms_shipped <> 1
    union all 
    select schema_name(fk_tab.schema_id) + '.' + fk_tab.name as foreign_table,
        'Table',
        'Foreign key',
        fk.name as fk_constraint_name,
        schema_name(pk_tab.schema_id) + '.' + pk_tab.name
    from sys.foreign_keys fk
        inner join sys.tables fk_tab
            on fk_tab.object_id = fk.parent_object_id
        inner join sys.tables pk_tab
            on pk_tab.object_id = fk.referenced_object_id
        inner join sys.foreign_key_columns fk_cols
            on fk_cols.constraint_object_id = fk.object_id
    union all
    select schema_name(t.schema_id) + '.' + t.[name],
        'Table',
        'Check constraint',
        con.[name] as constraint_name,
        con.[definition]
    from sys.check_constraints con
        left outer join sys.objects t
            on con.parent_object_id = t.object_id
        left outer join sys.all_columns col
            on con.parent_column_id = col.column_id
            and con.parent_object_id = col.object_id
    union all
    select schema_name(t.schema_id) + '.' + t.[name],
        'Table',
        'Default constraint',
        con.[name],
        col.[name] + ' = ' + con.[definition]
    from sys.default_constraints con
        left outer join sys.objects t
            on con.parent_object_id = t.object_id
        left outer join sys.all_columns col
            on con.parent_column_id = col.column_id
            and con.parent_object_id = col.object_id) a
WHERE 1=1
AND table_view= 'dbo.CustomerAddresses'
AND constraint_name = 'UniqueDefaultBilling')
and exists(SELECT * FROM INFORMATION_SCHEMA.columns WHERE INFORMATION_SCHEMA.columns.TABLE_NAME = 'CustomerAddresses' AND INFORMATION_SCHEMA.columns.COLUMN_NAME = 'DefaultBilling')
	 BEGIN
		  DECLARE @sql nvarchar( max)
		  SELECT @sql = '
		  DECLARE @tbl TABLE (CustId VARCHAR(10), IsBilling BIT, AddressRefToKeep VARCHAR(50))

		  INSERT INTO @tbl
						  ( CustId, IsBilling )
		  SELECT CustomerID, 1 FROM dbo.CustomerAddresses AS ca
		  WHERE DefaultBilling = 1
		  GROUP BY CustomerID
		  HAVING COUNT(*) > 1

		  INSERT INTO @tbl
						  ( CustId, IsBilling )
		  SELECT CustomerID, 0 FROM dbo.CustomerAddresses AS ca
		  WHERE DefaultShipping = 1
		  GROUP BY CustomerID
		  HAVING COUNT(*) > 1

		  UPDATE @tbl
		  SET AddressRefToKeep = AddressID
		  FROM @tbl AS t
		  INNER JOIN (	SELECT CustomerID, a.AddressID,ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY AddressLastModified DESC) AS myRow 
						  FROM dbo.CustomerAddresses AS ca 
						  INNER JOIN dbo.Addresses AS a ON ca.AddressID = a.AddressID
						  WHERE DefaultBilling = 1) tmp 
		  ON		t.CustId = tmp.CustomerID
		  WHERE	1=1
		  AND		t.IsBilling = 1
		  AND		myRow = 1

		  UPDATE @tbl
		  SET AddressRefToKeep = AddressID
		  FROM @tbl AS t
		  INNER JOIN (	SELECT CustomerID, a.AddressID,ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY AddressLastModified DESC) AS myRow 
						  FROM dbo.CustomerAddresses AS ca 
						  INNER JOIN dbo.Addresses AS a ON ca.AddressID = a.AddressID
						  WHERE DefaultShipping = 1) tmp 
		  ON		t.CustId = tmp.CustomerID
		  WHERE	1=1
		  AND		t.IsBilling = 0
		  AND		myRow = 1

		  UPDATE dbo.CustomerAddresses
		  SET DefaultBilling = 0
		  FROM dbo.CustomerAddresses AS ca
		  INNER JOIN @tbl AS t
		  ON t.CustId = ca.CustomerID
		  WHERE t.IsBilling = 1
		  AND DefaultBilling = 1
		  AND AddressID != AddressRefToKeep

		  UPDATE dbo.CustomerAddresses
		  SET DefaultShipping = 0
		  FROM dbo.CustomerAddresses AS ca
		  INNER JOIN @tbl AS t
		  ON t.CustId = ca.CustomerID
		  WHERE t.IsBilling = 0
		  AND DefaultShipping = 1
		  AND AddressID != AddressRefToKeep'

		  EXEC sp_executesql @sql

	 END 
GO
