Print 'Doing pre run Checks to ensure script not already run';
Select * from Script_History where Script_Name='StockAgeFix03012025';
if @@ROWCOUNT!=0
	BEGIN
		RAISERROR('THIS SCRIPT HAS ALREADY BEEN RUN ON THIS SERVER, CANNOT CONTINUE, PLEASE CONTACT DEV',11,1);
		RETURN;
	END
GO

declare @StoreId int;

set @StoreId = (select default_value from system_default where Default_Name = 'Main_store_id');

If(@StoreId in (2,3,6,17,24,25,26,27,38,41,50,54,62,67,68,69,70,75,76,82,85,86,90,93,96,100,106,110,111,113,118,121,124,126,127,128,129,131,135,142,146,149,150,155,156,158,162,164,165,173,174,177,182,183,185,199,215,219,220,227,242,246,251,262,276,9,35,43,101,186,243))
begin

update stock_item set Last_Stock_Age_Change=
(select top 1 movement_date from Stock_Movement sm where sm.Stock_Item_ID =  Stock_Item.Stock_Item_ID and sm.Stock_Holding_Group_ID=1 and sm.Stock_Out >0 order by Movement_Date desc),
Target_Release_Date = (select top 1 movement_date from Stock_Movement sm where sm.Stock_Item_ID =  Stock_Item.Stock_Item_ID and sm.Stock_Holding_Group_ID=1 and sm.Stock_Out >0 order by Movement_Date desc)
where Stock_Item_ID in ( select si.Stock_Item_ID from Stock_Item_Count sic
left join stock_item si on si.Stock_Item_ID =  sic.Stock_Item_ID
where sic.Stock_Holding_Group_ID = 2 and Stock_Count>0 and si.Item_Stock_Type_ID in (1,2,7) and si.Original_Purchase_Date <'2024-11-01' )

end 
/****** Object:  StoredProcedure [dbo].[GetStockAge]    Script Date: 2025-01-03 14:18:19 ******/
DROP PROCEDURE [dbo].[GetStockAge]


/****** Object:  StoredProcedure [dbo].[GetStockAge]    Script Date: 2025-01-03 14:18:19 ******/
SET ANSI_NULLS ON


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(((
					(SELECT ROUND(a.Current_RRP / (1 + ROUND(CAST(Default_Value AS DECIMAL(10, 2)), 2) / 100), 2) FROM System_Default WHERE Default_Name = ''Vat_Percentage'') - a.Item_Cost) / 
					(SELECT ROUND(a.Current_RRP / (1 + ROUND(CAST(Default_Value AS DECIMAL(10, 2)), 2) / 100), 2) FROM System_Default WHERE Default_Name = ''Vat_Percentage'')) * 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 sa.RRP > 0 AND ' + @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.buyshop_trans_line_id = si.buyshop_trans_line_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



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