SET NOCOUNT ON
DECLARE @SQL NVARCHAR(2000)
DECLARE @Eskimo VARCHAR(20)


PRINT '^<RUNN> Executing Post-Update statements.' 

UPDATE StyleRef SET StyleRef.DefaultAssignedCustomer = '000-000000'
WHERE (((StyleRef.DefaultAssignedCustomer)='')) OR (((StyleRef.DefaultAssignedCustomer) Is Null));

--INSERT INTO dbo.Deposits
--        ( DepositID ,
--          DepositStore ,
--          DepositAmount ,
--          DepositDate ,
--          DepositOperator ,
--          DepositCusOrderID ,
--          DepositType 
--        )

--SELECT		ROW_NUMBER() OVER (ORDER BY dbo.CusOrder.CusOrderID) + (SELECT ISNULL(MAX(DepositID),0) FROM dbo.Deposits WHERE DepositStore = dbo.GetStoreNumber()) AS DepID,
--			CusOrderStore,
--			SUM(CusOrderDetailDepositPaid) AS DepSum,
--			CusOrderDate,
--			CusOrderProcessedBy,
--			dbo.CusOrder.CusOrderID,
--			1
--FROM dbo.CusOrder 
--INNER JOIN dbo.CusOrderDetails ON dbo.CusOrder.CusOrderID = dbo.CusOrderDetails.CusOrderID
--AND CusOrderStore = CusOrderDetailStore
--WHERE CusOrderDetailItemStatus < 6
--AND NOT EXISTS(SELECT * FROM dbo.Deposits WHERE DepositCusOrderID = CusOrder.CusOrderID AND DepositStore = CusOrderDetailStore)
--GROUP BY CusOrderStore,CusOrderDate,
--			CusOrderProcessedBy,
--			dbo.CusOrder.CusOrderID
--HAVING SUM(CusOrderDetailDepositPaid) > 0

IF NOT EXISTS(SELECT * FROM dbo.Definitions)
	BEGIN
		INSERT INTO dbo.Definitions (DefTradeCustomer)
		VALUES ('Trade Customer')
	END


EXEC spCustomerCollectionTagInsert


IF NOT EXISTS(SELECT id FROM dbo.CoreFormulaFieldsResults)
	 BEGIN
		  UPDATE GlobalTillConfig SET ProductSearchDemandCalculation = ProductSearchDemandCalculation,
												ProductSearchAvailabilityCalculation = ProductSearchAvailabilityCalculation
	 END

IF NOT EXISTS(SELECT * FROM dbo.OrderStatuses)
	BEGIN
		INSERT INTO dbo.OrderStatuses
					( StatusID ,
					  StatusDesc ,
					  StatusActive ,
					  StatusCreated ,
					  StatusDefault ,
					  StatusOrderBy
					)
		SELECT        30 , -- StatusID - int
					  '{Default}' , -- StatusDesc - varchar(100)
					  1 , -- StatusActive - bit
					  CURRENT_TIMESTAMP , -- StatusCreated - datetime
					  1 , -- StatusDefault - bit
					  0  -- StatusOrderBy - int
	END
		
IF NOT EXISTS(SELECT * FROM dbo.Color)
	BEGIN
		INSERT INTO dbo.Color
					( ColorID ,
					  ColorName ,
					  ColorOLE 
					)
		VALUES      ( '-1' , -- ColorID - varchar(6)
					  'N/A' , -- ColorName - varchar(50)
					  -1  -- ColorOLE - int
					)
	END

IF NOT EXISTS(SELECT * FROM GlobalTillConfig) 
	BEGIN
		INSERT INTO GlobalTillConfig (AllowGiftReceipts) SELECT 0 
	END


IF NOT EXISTS(SELECT	* FROM dbo.Operator WHERE OperatorID = 'SYSTEM')
	BEGIN
		INSERT INTO dbo.Operator
					( 
						OperatorID ,
						OperatorName ,
						OperatorFullName ,
						Administrator ,
						[Password] ,
						OperatorActive,
						DisplayOnEPOS
					)
		SELECT			N'SYSTEM' , -- OperatorID - nvarchar(12)
						N'Eskimo System' , -- OperatorName - nvarchar(20)
						N'Eskimo System' , -- OperatorFullName - nvarchar(60)
						0 , -- Administrator - bit
						N'' , -- Password - nvarchar(20)
						0 , -- OperatorActive - bit
						0  -- DisplayOnEPOS - bit		
	END

IF NOT EXISTS(SELECT * FROM dbo.MachiningDefinitions)
	BEGIN
		INSERT INTO dbo.MachiningDefinitions ( MachiningSetupID	) VALUES ( 1)	
	END


	
		
SELECT @Eskimo = table_catalog	FROM  INFORMATION_SCHEMA.tables

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE table_name = 'QRYMailMerge_Suppliers') AND EXISTS(SELECT * FROM sysusers WHERE NAME ='MailMergeLogin')
	BEGIN
		PRINT 'Ensuring Granted Access to QRYMailMerge_Suppliers'
		SELECT @SQL = 'GRANT SELECT ON OBJECT::' + @Eskimo + '.dbo.[QRYMailMerge_Suppliers] TO MailMergeLogin'
		exec sp_executesql @SQL

	END
	
	
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE table_name = 'QRYMailMerge_Customers_Counter') AND EXISTS(SELECT * FROM sysusers WHERE NAME ='MailMergeLogin')
	BEGIN
		PRINT 'Ensuring Granted Access to QRYMailMerge_Customers_Counter'
		SELECT @SQL = 'GRANT SELECT ON OBJECT::' + @Eskimo + '.dbo.[QRYMailMerge_Customers_Counter] TO MailMergeLogin'
		exec sp_executesql @SQL

	END
	
	
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE table_name = 'QRYMailMerge_Customers_Trade') AND EXISTS(SELECT * FROM sysusers WHERE NAME ='MailMergeLogin')
	BEGIN
		PRINT 'Ensuring Granted Access to QRYMailMerge_Customers_Trade'
		SELECT @SQL = 'GRANT SELECT ON OBJECT::' + @Eskimo + '.dbo.[QRYMailMerge_Customers_Trade] TO MailMergeLogin'
		exec sp_executesql @SQL

	END
	
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE table_name = 'QRYMailMerge_Product_General') AND EXISTS(SELECT * FROM sysusers WHERE NAME ='MailMergeLogin')
	BEGIN
		PRINT 'Ensuring Granted Access to QRYMailMerge_Product_General'
		SELECT @SQL = 'GRANT SELECT ON OBJECT::' + @Eskimo + '.dbo.[QRYMailMerge_Product_General] TO MailMergeLogin'
		exec sp_executesql @SQL

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

--Refresh all views
DECLARE @sqlcmd NVARCHAR(MAX) = ''
SELECT @sqlcmd = @sqlcmd +  'EXEC sp_refreshview ''' + name + ''';'
FROM sys.objects AS so 
WHERE so.type = 'V' 

EXEC(@sqlcmd) 


--IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'WebImageProductLocations')
--	BEGIN
--		exec sp_executesql N'EXEC sys.sp_refreshview  @viewname = ''WebImageProductLocations'''
--	END
	
	
	
	
UPDATE  Transactions
SET   TranShippingAmountNet = ShippingVAT.ShippingRatesNetValueToCustomer, 
   TranShippingVATRate = VatRate, 
   TranShippingVATID = ShippingVAT.ShippingRatesVATID
   --TranShippingVATAmount = TranShippingAmount - ShippingVAT.ShippingRatesNetValueToCustomer
FROM  Transactions 
INNER JOIN
   ( SELECT ShippingRates.ShippingRatesNetValueToCustomer, ShippingRates.ShippingRatesVATID, VATCodes.VatRate, 
      ShippingRates.ShippingRatesNetValueToCustomer * VATCodes.VATDecimalRate AS GrossShippingToCustomer
    FROM ShippingRates 
    INNER JOIN
      VATCodes ON ShippingRates.ShippingRatesVATID = VATCodes.VatID
   ) AS ShippingVAT ON Transactions.TranShippingAmount = ShippingVAT.GrossShippingToCustomer
WHERE  (Transactions.TranShippingAmount <> 0)
AND (TranShippingAmountNet = 0)

-- Updating Indexes

DECLARE @tablename VARCHAR (128)
DECLARE @execstr VARCHAR (255)
DECLARE @objectid INT
DECLARE @objectowner VARCHAR(255)
DECLARE @indexid INT
DECLARE @frag DECIMAL
DECLARE @indexname CHAR(255)
DECLARE @dbname sysname
DECLARE @tableid INT
DECLARE @tableidchar VARCHAR(255)
DECLARE @maxfrag DECIMAL

SELECT @maxfrag = 99

--check this is being run in a user database
SELECT @dbname = db_name()
IF @dbname IN ('master', 'msdb', 'model', 'tempdb')
BEGIN
PRINT 'This procedure should not be run in system databases.'
RETURN
END

--begin Stage 1: checking fragmentation
-- Declare cursor
DECLARE tables CURSOR FOR
SELECT convert(varchar,so.id)
FROM sysobjects so
JOIN sysindexes si
ON so.id = si.id
WHERE so.type ='U'
AND si.indid < 2
AND si.rows > 0

-- Create the temporary table to hold fragmentation information
CREATE TABLE #fraglist (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)

-- Open the cursor
OPEN tables

-- Loop through all the tables in the database running dbcc showcontig on each one
FETCH NEXT
FROM tables
INTO @tableidchar

WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist 
EXEC ('DBCC SHOWCONTIG (' + @tableidchar + ') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT
FROM tables
INTO @tableidchar
END

-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables

-- Report the ouput of showcontig for results checking
--SELECT * FROM #fraglist

-- Begin Stage 2: (defrag) declare cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectOwner = user_name(so.uid), ObjectId, IndexName, ScanDensity
FROM #fraglist f
JOIN sysobjects so ON f.ObjectId=so.id
WHERE ScanDensity <= @maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0

-- Write to output start time for information purposes
PRINT 'Started defragmenting indexes at ' + CONVERT(VARCHAR,GETDATE())

-- Open the cursor
OPEN indexes

-- Loop through the indexes
FETCH NEXT
FROM indexes
INTO @tablename, @objectowner, @objectid, @indexname, @frag

WHILE @@FETCH_STATUS = 0
BEGIN
--SET QUOTED_IDENTIFIER ON

SELECT @execstr = 'DBCC DBREINDEX (' + CHAR(39) +RTRIM(@objectowner) + '.' + RTRIM(@tablename) + CHAR(39) + ', [' + RTRIM(@indexname) + ']) WITH NO_INFOMSGS'
PRINT 'Now executing: ' + @execstr
EXEC (@execstr)

--SET QUOTED_IDENTIFIER OFF

FETCH NEXT
FROM indexes
INTO @tablename, @objectowner, @objectid, @indexname, @frag
END

-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes

-- Report on finish time for information purposes
PRINT 'Finished defragmenting indexes at ' + CONVERT(VARCHAR,GETDATE())

-- Delete the temporary table
DROP TABLE #fraglist
GO


--this part inserts a deposit for each line that is relevant, where one doesn't exist already.
DELETE FROM  Deposits
FROM         Deposits 
INNER JOIN   CusOrder 
ON		     Deposits.DepositCusOrderID = CusOrder.CusOrderID AND Deposits.DepositStore = CusOrder.CusOrderStore
WHERE		(Deposits.DepositType = 1) 
AND			(Deposits.DepositCusOrderDetailID IS NULL)
AND			(CusOrderWebsiteID = 0)

INSERT INTO dbo.Deposits
        ( DepositID ,
          DepositStore ,
          DepositTill ,
          DepositReceipt ,
          DepositAmount ,
          DepositDate ,
          DepositOperator ,
          DepositCusOrderID ,
          DepositType,
          DepositCusOrderDetailID
        )

SELECT 
ROW_NUMBER() OVER(ORDER BY hdr.CusOrderID) + (SELECT ISNULL(MAX(DepositID),0) FROM dbo.Deposits) AS rownum ,
dbo.GetStoreNumber() AS store,
CusOrderTill,
CusOrderTransaction,
SUM(CusOrderDetailDepositPaid) AS depositamount,
CusOrderDate,
CusOrderProcessedBy,
hdr.CusOrderID,
1, --inital deposit
CusOrderDetailID
FROM dbo.CusOrder hdr
INNER JOIN dbo.CusOrderDetails ON hdr.CusOrderID = dbo.CusOrderDetails.CusOrderID
AND CusOrderStore = CusOrderDetailStore
WHERE CusOrderFinalised = 0
AND NOT EXISTS(SELECT * FROM dbo.Deposits WHERE DepositCusOrderID = hdr.CusOrderID AND DepositType IN ( 1,7 ))
GROUP BY CusOrderTill,
		hdr.CusOrderID,
		CusOrderTransaction,
		CusOrderDate,
		CusOrderProcessedBy,
		CusOrderDetailID
HAVING 	SUM(CusOrderDetailDepositPaid) <> 0


--now we insert the deposits for collected items
INSERT INTO dbo.Deposits
        ( DepositID ,
          DepositStore ,
          DepositTill ,
          DepositReceipt ,
          DepositAmount ,
          DepositDate ,
          DepositOperator ,
          DepositCusOrderID ,
          DepositType,
          DepositCusOrderDetailID
        )

SELECT 
ROW_NUMBER() OVER(ORDER BY hdr.CusOrderID) + (SELECT ISNULL(MAX(DepositID),0) FROM dbo.Deposits) AS rownum ,
dbo.GetStoreNumber() AS store,
CusOrderDetailTill,
CusOrderDetailTransaction,
SUM(CusOrderDetailDepositPaid) * -1 AS depositamount,
CusOrderDetailCollected,
CusOrderProcessedBy,
hdr.CusOrderID,
4, --item collection
CusOrderDetailID
FROM dbo.CusOrder hdr
INNER JOIN dbo.CusOrderDetails ON hdr.CusOrderID = dbo.CusOrderDetails.CusOrderID
AND CusOrderStore = CusOrderDetailStore

INNER JOIN dbo.GlobalConfig 
ON	CusOrderStore = StoreNumber
WHERE CusOrderFinalised = 0
AND CusOrderDetailItemStatus = 6 --collected
AND [CusOrderDetailTill] <> 0
AND NOT EXISTS(SELECT * FROM dbo.Deposits WHERE DepositCusOrderDetailID = CusOrderDetailID AND DepositType = 4)

GROUP BY CusOrderDetailTill,
		hdr.CusOrderID,
		CusOrderDetailTransaction,
		CusOrderDetailCollected,
		CusOrderProcessedBy,
		CusOrderDetailID
HAVING SUM(CusOrderDetailDepositPaid) <> 0
ORDER BY CusOrderDetailTill , CusOrderDetailTransaction


--now we insert the deposits for refunded items
INSERT INTO dbo.Deposits
        ( DepositID ,
          DepositStore ,
          DepositTill ,
          DepositReceipt ,
          DepositAmount ,
          DepositDate ,
          DepositOperator ,
          DepositCusOrderID ,
          DepositType,
          DepositCusOrderDetailID
        )

SELECT 
ROW_NUMBER() OVER(ORDER BY hdr.CusOrderID) + (SELECT ISNULL(MAX(DepositID),0) FROM dbo.Deposits) AS rownum ,
dbo.GetStoreNumber() AS store,
CusOrderdetailTill,
CusOrderdetailTransaction,
SUM(CusOrderDetailDepositPaid) * -1 AS depositamount,
CusOrderDate,
CusOrderProcessedBy,
hdr.CusOrderID,
3, --refunded deposit
CusOrderDetailID
FROM dbo.CusOrder hdr
INNER JOIN dbo.CusOrderDetails ON hdr.CusOrderID = dbo.CusOrderDetails.CusOrderID
AND CusOrderStore = CusOrderDetailStore
WHERE CusOrderFinalised = 0
AND CusOrderDetailItemStatus = 7
AND NOT EXISTS(SELECT * FROM dbo.Deposits WHERE DepositCusOrderDetailID = CusOrderDetailID AND DepositType IN (3,5))
GROUP BY CusOrderdetailTill,
		hdr.CusOrderID,
		CusOrderdetailTransaction,
		CusOrderDate,
		CusOrderProcessedBy,
		CusOrderDetailID
HAVING 	SUM(CusOrderDetailDepositPaid) <> 0

IF NOT EXISTS(SELECT * FROM dbo.Transactions WHERE TranReadPeriod IS NOT NULL)
	BEGIN
		UPDATE dbo.Transactions 
		SET TranReadPeriod = 1
		WHERE TranType IN (4,5)
		AND	TranReadPeriod IS NULL
		AND EXISTS(SELECT * FROM dbo.ReceiptData WHERE ReceiptType IN (4,5) AND ReceiptNumber = TranReceipt AND ReceiptTill = TranTill AND ReceiptStore = TranStore AND ReceiptData LIKE '%5uC' + (SELECT Period1Name FROM dbo.GlobalTillConfig) + '%')

		UPDATE dbo.Transactions 
		SET TranReadPeriod = 2
		WHERE TranType IN (4,5)
		AND	TranReadPeriod IS NULL
		AND EXISTS(SELECT * FROM dbo.ReceiptData WHERE ReceiptType IN (4,5) AND ReceiptNumber = TranReceipt AND ReceiptTill = TranTill AND ReceiptStore = TranStore AND ReceiptData LIKE '%5uC' + (SELECT Period2Name FROM dbo.GlobalTillConfig) + '%')

		UPDATE dbo.Transactions 
		SET TranReadPeriod = 3
		WHERE TranType IN (4,5)
		AND	TranReadPeriod IS NULL
		AND EXISTS(SELECT * FROM dbo.ReceiptData WHERE ReceiptType IN (4,5) AND ReceiptNumber = TranReceipt AND ReceiptTill = TranTill AND ReceiptStore = TranStore AND ReceiptData LIKE '%5uC' + (SELECT Period3Name FROM dbo.GlobalTillConfig) + '%')
	END
	
UPDATE dbo.Stock SET	StockQTY1 =	CASE	WHEN StockQTY1 < 0 THEN 
												0
											ELSE 
												StockQTY1
									END,
						StockQTY2 =	CASE	WHEN StockQTY2 < 0 THEN 
												0
											ELSE 
												StockQTY2
									END,
						StockQTY3 =	CASE	WHEN StockQTY3 < 0 THEN 
												0
											ELSE 
												StockQTY3
									END, TempRequired = 1
WHERE StoreID = dbo.GetStoreNumber() AND AllowMinusFigure = 0		

IF NOT EXISTS(SELECT * FROM AgentStatus WHERE AgentStatusID = 0)
	BEGIN
		INSERT INTO AgentStatus (AgentStatusID, AgentStatusDescription, AgentStatusColour)
		VALUES (0, 'Live', 0)

	END

IF NOT EXISTS(SELECT * FROM AgentStatus WHERE AgentStatusID = 1)
	BEGIN
		INSERT INTO AgentStatus (AgentStatusID, AgentStatusDescription, AgentStatusColour)
		VALUES (1, 'Inactive', 6579300)

	END

if exists(select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'PLUAttributeLinks' and COLUMN_NAME = 'PLUAttributeLinkToCustomer')
	BEGIN
		--used to fire the trigger
		set QUOTED_IDENTIFIER ON
		update PLUAttributeLinks set PLUAttributeLinkedPLU = PLUAttributeLinkedPLU
	end	

--stops customers being search for straight away when you open the form in epos
UPDATE GlobalTillConfig SET CusFormShowCustomerListOnOpen = 0

--https://productforums.google.com/forum/#!topic/gmail/riiRSnKbYNI
DELETE FROM dbo.KeyPadQuickText WHERE KeyPadQuickText = '@gmail.co.uk'

--populate the Carriage Gross Figure since the VAT Rate changed from 17.5%
UPDATE			dbo.CusOrder
SET				CusOrderCarriageSellPriceGross = ROUND(CusOrderCarriageSellPrice * VATDecimalRate,2)
FROM			dbo.CusOrder
INNER JOIN		dbo.VATCodes 
ON				CusOrderCarriageVATRate = VatID
WHERE			CusOrderCarriageSellPriceGross = 0 AND CusOrderCarriageSellPrice > 0
AND				DATEPART(YEAR,CusOrderDate) > 2011