Print 'Doing pre run Checks to ensure script not already run';
Select * from Script_History where Script_Name='ReleaseFeb2025Week2';
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 COL_LENGTH('dbo.ConsumableReceipt', 'CCWInvoiceDate') IS NULL
ALTER TABLE ConsumableReceipt ADD CCWInvoiceDate date;
GO

IF COL_LENGTH('dbo.Stock_receipt', 'CCWInvoiceDate') IS NULL
ALTER TABLE Stock_receipt ADD CCWInvoiceDate date;
GO

update Stock_Receipt set CCWInvoiceDate = Created where IsCbucksInvoice = 1 and Stock_Receipt_Status_ID != 2;


update ConsumableReceipt set CCWInvoiceDate = Created where  Stock_Receipt_Status_ID != 2;


/****** Object:  StoredProcedure [dbo].[GetPendingCbucksAndConsumableStockReceipts]    Script Date: 2025-01-30 14:10:05 ******/
DROP PROCEDURE [dbo].[GetPendingCbucksAndConsumableStockReceipts]
GO

/****** Object:  StoredProcedure [dbo].[GetPendingCbucksAndConsumableStockReceipts]    Script Date: 2025-01-30 14:10:05 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[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'  and DATEDIFF(DAY,CCWInvoiceDate,getdate()) >= 13
        and online_invoice_id > 250451     
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' and DATEDIFF(DAY,CCWInvoiceDate,getdate()) >= 13
and online_invoice_id > 250451   
END
GO

Print 'Insert into script history if everything else is cool';
	Insert into Script_History (RunDate,Script_Name) Values (CURRENT_TIMESTAMP,'ReleaseFeb2025Week2');
	IF @@ERROR != 0
		BEGIN
			RAISERROR ('UNABLE TO CONTINUE WITH SCRIPT', 11, 1);
			RETURN;
		END
GO