Print 'Doing pre run Checks to ensure script not already run';
Select * from Script_History where Script_Name='ReleaseSept2024W3';

if @@ROWCOUNT!=0
	BEGIN
		RAISERROR('THIS SCRIPT HAS ALREADY BEEN RUN ON THIS SERVER, CANNOT CONTINUE, PLEASE CONTACT DEV',11,1);
		RETURN;
	END
GO

IF (OBJECT_ID('GetPendingCbucksAndConsumableStockReceipts', 'P')) IS NOT NULL
BEGIN
DROP PROCEDURE GetPendingCbucksAndConsumableStockReceipts
END
GO
CREATE PROCEDURE GetPendingCbucksAndConsumableStockReceipts
AS
BEGIN
	SET NOCOUNT ON;

SELECT 
Created, 
'Crusaders Corporate Wholesale' as Supplier, 
Invoice_Number, 
'Pending' as [Status],
'Consumable' as ReceiptType, 
Online_Invoice_ID
FROM ConsumableReceipt
WHERE 
	Stock_Receipt_Status_ID = 1 AND 
	Online_Invoice_ID IS NOT NULL AND 
	Supplier_ID = 1 AND 
	Created >= '2024-10-01'
UNION
SELECT 
Created, 
'Crusaders Corporate Wholesale' as Supplier, 
Invoice_Number, 
'Pending' as [Status],
'CBucks' as ReceiptType, 
Online_Invoice_ID
FROM Stock_Receipt
WHERE 
	Supplier_ID = 1 AND 
	Stock_Receipt_Status_ID = 1 AND 
	Online_Invoice_ID IS NOT NULL AND 
	IsCbucksInvoice = 1 	AND 
	Created >= '2024-10-01'
END
GO

CREATE TABLE [dbo].[StockReceiptSkipReason](
	[StockReceiptSkipReasonId] [bigint] IDENTITY(1,1) NOT NULL,
	[SystemUserID] [bigint] NOT NULL,
	[Reason] [varchar](200) NOT NULL,
	[Created] [datetime] NOT NULL,
	[Completed] [datetime] NULL,
 CONSTRAINT [PK_StockReceiptSkipReason] PRIMARY KEY CLUSTERED 
(
	[StockReceiptSkipReasonId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

IF (OBJECT_ID('SetStockReceiptSkipReason', 'P')) IS NOT NULL
BEGIN
DROP PROCEDURE SetStockReceiptSkipReason
END
GO
CREATE PROCEDURE SetStockReceiptSkipReason 
	-- Add the parameters for the stored procedure here
	@SystemUserID bigint,
	@Reason varchar(200)
AS
BEGIN
	SET NOCOUNT ON;

INSERT INTO StockReceiptSkipReason 
(SystemUserID, Reason, Created) 
VALUES 
(@SystemUserID, @Reason, GETDATE())
END
GO

IF (OBJECT_ID('GetStockReceiptSkipReasonCount', 'P')) IS NOT NULL
BEGIN
DROP PROCEDURE GetStockReceiptSkipReasonCount
END
GO
CREATE PROCEDURE GetStockReceiptSkipReasonCount
AS
BEGIN
	SET NOCOUNT ON;

SELECT COUNT(0) FROM StockReceiptSkipReason WHERE Completed IS NULL
END
GO

IF (OBJECT_ID('SetClearStockReceiptSkipReason', 'P')) IS NOT NULL
BEGIN
DROP PROCEDURE SetClearStockReceiptSkipReason
END
GO
CREATE PROCEDURE SetClearStockReceiptSkipReason
AS
BEGIN
	SET NOCOUNT ON;

UPDATE StockReceiptSkipReason SET Completed = GETDATE()
END
GO

IF (select count(1) from SystemSetting where SettingKey = 'StockReceiptSkipMaxCount') = 0
BEGIN
INSERT INTO SystemSetting 
(SettingKey, SettingCategoryId, SettingValueInt, TypeFlag) 
VALUES
('StockReceiptSkipMaxCount', 1, 4, 4)
END

IF OBJECT_ID('spGetActiveProducts', 'P') IS NOT NULL
BEGIN
	DROP PROCEDURE [dbo].[spGetActiveProducts]	
END
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[spGetActiveProducts]    
AS
BEGIN
    select [Product_ID]
          ,[Product_Code]
          ,[Product_Category_ID]
          ,[Product_Brand_ID]
          ,[Description]
          ,[Third_Descriptor]
          ,[Accessories_List]
          ,[New_Warranty_Period_ID]
          ,[2nd_Warranty_Period_ID] AS C2nd_Warranty_Period_ID
          ,[Last_Par_Level]
          ,[HQ_Product_ID]
          ,[Store_ID]
          ,[Pilot_Bar_Code]
          ,[Active]
          ,[HODisabled]
          ,[Uploaded]
          ,[Master_Replacement_ID]
          ,[HQ_Processed]
          ,[Store_ReProcessed]
          ,[ForHQReview]
          ,[Created]
          ,[System_User_ID]
          ,[Master_Product_ID]
          ,[LowMargin]
          ,[LowMarginStart]
          ,[LowMarginEnd]
          ,[RegionalRanking_ID]
          ,[NationalRanking_ID]
          ,[Master_Supplier_ID]
          ,[ItemMonsterId]
          ,[InstantExchange]
		  ,[SerialRequired]
		  from product where Active = 1
END
GO

Print 'Insert into script history if everything else is cool';

	Insert into Script_History (RunDate,Script_Name) Values (CURRENT_TIMESTAMP,'ReleaseSept2024W3');
	IF @@ERROR != 0
		BEGIN
			RAISERROR ('UNABLE TO CONTINUE WITH SCRIPT', 11, 1);
			RETURN;
		END
GO