Print 'Doing pre run Checks to ensure script not already run';
Select * from Script_History where Script_Name='ReleaseNov2024Week4';
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 (SELECT COUNT(0) FROM SystemSetting WHERE SettingKey = 'SageAutoReversal') = 0
BEGIN
    INSERT INTO SystemSetting(SettingKey, SettingCategoryId, SettingValueBit, TypeFlag) VALUES('SageAutoReversal', 1, 0, 4)
END
GO


IF OBJECT_ID('GetStockAge', 'P') IS NOT NULL
BEGIN
    DROP PROCEDURE [dbo].[GetStockAge]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetStockAge]
    @GoldOnly bigint
AS
BEGIN
    DECLARE @Filter VARCHAR(200)
    DECLARE @SQL VARCHAR(MAX)
    IF(@GoldOnly = 0)
        BEGIN
        SET @Filter = '(sa.category NOT LIKE ''Gold%'' and sa.category NOT LIKE ''%Silver Jewellery%'')'
        END
    ELSE
        BEGIN
        SET @Filter = '(sa.category LIKE ''Gold%'' or sa.category LIKE ''%Silver Jewellery%'')'
        END
    END
    SET @SQL = 'SELECT     
                CONCAT(su.First_Name, SPACE(1), su.Surname) AS Buyer, 
                a.Stock_Item_ID, 
                a.Stock_Code, 
                a.Product, 
                a.Description, 
                a.Category, 
                a.Serial, 
                a.Stock_Age_Date, 
                a.Item_Cost, 
                a.Last_RRP_Change, 
                a.Prev_RRP, 
                a.Current_RRP, 
                a.Age, 
                a.Qty, 
                a.OriginalPurchaseDate, 
                a.Last_Stock_Age_Change, 
                a.LastChangeReason, 
                a.StockAgeDays, 
                a.DaysSinceLastPriceChange, 
                a.PricePerc, 
                a.MarkDownRank, 
                a.PriceAgeBracket, 
                a.RankColourValue, 
                a.TooltipDescription, 
                a.MarkdownRankId, 
                a.Last_Change, 
                a.LastKnownReturn,
                CAST(ROUND(ISNULL(((a.Current_RRP - a.Item_Cost) / a.Current_RRP) * 100, 0), 2) AS decimal(18, 2)) AS GP
                FROM
                (SELECT 
                    sa.Stock_Item_ID,
                    sa.Stock_Code,
                    sa.Product,             
                    sa.[Description],
                    sa.Category,
                    sa.Serial_Number AS Serial,
                    ISNULL(sa.Last_Stock_Age_Change, sa.Original_Purchase_Date) AS Stock_Age_Date,
                    sa.Unit_Cost AS Item_Cost,              
                    sa.Last_Change AS Last_RRP_Change,
                    0.00 AS Prev_RRP,           
                    sa.RRP AS Current_RRP,
                    '''' Age,
                    sa.Quantity AS Qty, 
                    sa.Original_Purchase_Date AS OriginalPurchaseDate,
                    sa.Last_Stock_Age_Change,
                    ISNULL(sa.Last_Stock_Age_Reason, '''') AS LastChangeReason,
                    0 AS StockAgeDays,
                    0 AS DaysSinceLastPriceChange,
                    0.00 AS PricePerc, 
                    '''' AS MarkDownRank,
                    '''' AS PriceAgeBracket,
                    '''' AS RankColourValue,
                    '''' AS TooltipDescription,
                    0 AS MarkdownRankId,
                    sa.Last_Change,
                    sa.Last_Known_Return as LastKnownReturn
                FROM vw_Stock_Age AS sa 
                WHERE ' + @Filter + ') AS a
                LEFT OUTER JOIN Stock_Item AS si ON si.Stock_Item_ID = a.Stock_Item_ID 
                LEFT OUTER JOIN Product AS p ON p.Product_ID = si.Product_ID 
                LEFT OUTER JOIN Buyshop_Trans_Line AS btl ON btl.Product_ID = p.Product_ID 
                LEFT OUTER JOIN Buyshop_Transaction AS bt ON bt.Buyshop_Transaction_ID = btl.Buyshop_Transaction_ID 
                LEFT OUTER JOIN [System_User] AS su ON su.System_User_ID = bt.System_User_ID
                ORDER BY a.OriginalPurchaseDate ASC'
    EXEC(@SQL)
GO

IF (SELECT COUNT(0) FROM Day_End_Summary_Category WHERE Description = 'SSBSellerTotal') = 0
BEGIN
    SET IDENTITY_INSERT Day_End_Summary_Category ON;
    INSERT INTO Day_End_Summary_Category(Day_End_Summary_Category_ID, Description, Type, FriendlyDescription, CalculationTypeId)
    VALUES(252, 'SSBSellerTotal', 1, 'Active SSB Seller Count', 4)
    SET IDENTITY_INSERT Day_End_Summary_Category OFF;
END
GO

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