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

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('Stock_Receipt', 'IsCbucksInvoice') IS NULL
BEGIN
ALTER TABLE Stock_Receipt ADD IsCbucksInvoice bit not null DEFAULT 0; 
END
Go
IF COL_LENGTH('Stock_Item', 'IsCbucks') IS NULL
BEGIN
ALTER TABLE Stock_Item ADD IsCbucks bit not null DEFAULT 0; 
END
GO
IF (select count(1) from SystemSetting where SettingKey = 'CbucksDiscount') = 0
BEGIN
INSERT INTO SystemSetting (SettingKey, SettingCategoryId, SettingValueDecimal, TypeFlag) VALUES ('CbucksDiscount', 1, 50, 4)
END
GO
IF COL_LENGTH('Lay_Bye', 'TermId') IS NULL
BEGIN
    ALTER TABLE Lay_Bye
    ADD TermId int
END
GO

IF OBJECT_ID('GetCBucksPurchaseReport ' ,'P') IS NOT NULL
BEGIN
    DROP PROCEDURE GetCBucksPurchaseReport
END
GO

CREATE PROCEDURE [dbo].[GetCBucksPurchaseReport] 
@fromDate datetime,
@toDate datetime
AS
BEGIN

SET NOCOUNT ON;

SELECT
*, 
CAST(Qty * (UnitCostExVat - (UnitCostExVat * SupplierDiscount / 100)) AS DECIMAL(18, 2)) AS LineCost,
CAST((Qty * (UnitCostExVat - (UnitCostExVat* SupplierDiscount / 100)) + Vat) AS DECIMAL(18, 2)) AS Total
FROM
    (SELECT
    *, 
    ISNULL(UnitPrice - UnitVat, 2) AS UnitCostExVat
    FROM
        (SELECT 
        si.Stock_Code AS Make, 
        p.Product_Code AS ModelNo, 
        si.Item_Description AS ItemDescription, 
        CONCAT(vw.Category, ', ', vw.Type) AS Category, 
        srl.Quantity_Received AS Qty, 
        srl.Unit_Price AS UnitPrice, 
        srl.Unit_Vat AS UnitVat, 
        ISNULL(srl.Supplier_Line_Disc, 0) AS SupplierDiscount, 
        srl.Vat, 
        sr.Invoice_Number AS InvoiceNumber
        FROM Stock_Receipt_Line AS srl 
            INNER JOIN Stock_Receipt AS sr ON sr.Stock_Receipt_ID = srl.Stock_Receipt_ID 
            INNER JOIN Stock_Item AS si ON si.Stock_Item_ID = srl.Stock_Item_ID 
            INNER JOIN Product AS p ON p.Product_ID = si.Product_ID 
            INNER JOIN Product_Brand AS pb ON pb.Product_Brand_ID = p.Product_Brand_ID 
            INNER JOIN vwProductCategory AS vw ON vw.Product_Category_ID = p.Product_Category_ID
        WHERE (sr.Stock_Receipt_Status_ID = 2) AND (sr.Authorised_Date >= @fromDate) AND (sr.Authorised_Date <= @toDate) AND sr.IsCbucksInvoice = 1) 
    AS a) 
AS b
order by b.ModelNo

END
GO

IF OBJECT_ID('GetCBucksSalesItems' ,'P') IS NOT NULL
BEGIN
    DROP PROCEDURE GetCBucksSalesItems
END
GO

CREATE PROCEDURE [dbo].[GetCBucksSalesItems] 
@fromDate datetime,
@toDate datetime
AS
BEGIN

SET NOCOUNT ON;

SELECT
td.Trading_Date AS TradingDate, 
p.Product_Code AS Model, 
p.Description, 
si.Stock_Code AS StockCode, 
stl.Unit_Cost AS UnitCost, 
stl.RPP AS RRP, 
stl.Quantity, 
ISNULL(stl.Quantity * stl.RPP, 0) AS LineTotalIncVat, 
ISNULL(stl.RPP - stl.Vat, 0) * stl.Quantity AS LineTotalExVat, 
ISNULL(stl.Vat * stl.Quantity, 0) AS TotalVat
FROM 
Sales_Transaction_Line AS stl 
    INNER JOIN Sales_Transaction AS st ON st.Sales_Transaction_ID = stl.Sales_Transaction_ID 
    INNER JOIN Cashier_Session AS cs ON cs.Cashier_Session_ID = st.Cashier_Session_ID 
    INNER JOIN Trading_Day AS td ON td.Trading_Day_ID = cs.Trading_Day_ID 
    INNER JOIN Stock_Item AS si ON si.Stock_Item_ID = stl.Stock_Item_ID 
    INNER JOIN Product AS p ON p.Product_ID = si.Product_ID
WHERE (td.Trading_Date >= @fromDate) AND (td.Trading_Date <= @toDate) AND si.IsCbucks = 1 AND st.Sales_Status_ID = 4
ORDER BY td.Trading_Date

END
GO

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

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