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

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

ALTER FUNCTION [dbo].[tfnGetBulkingProjection] 
(
    -- Add the parameters for the function here
    @Product_ID bigint
)
RETURNS 
@ProjectionResults TABLE 
(
    -- Add the column definitions for the TABLE variable here
    Ratio decimal(18,2), 
    Projection int,
    SOH int,
    Recommended int,
    FromType bit

)
AS
BEGIN
    -- Fill the table variable with the rows for your result set


declare @P1_From datetime
declare @P1_To datetime

declare @P2_From datetime
declare @P2_To datetime

declare @P3_From datetime
declare @P3_To datetime

declare @FromType bit
declare @Detail varchar(100) 
declare @Result decimal(18,2)
declare @Ratio decimal(18,2)
declare @SOH int
declare @CalcDate datetime
declare @RoundingThreshold int
declare @Conversion int
declare @Recommended int

DECLARE @P1_Day int = 1
DECLARE @P1_Month int  = MONTH(DATEADD(MONTH,-1,GETDATE()))
DECLARE @P1_Year int = YEAR(DATEADD(Year,-1,GETDATE()))

DECLARE @P2_Day int = 1
DECLARE @P2_Month int  = 12
DECLARE @P2_Year int = YEAR(DATEADD(Year,-1,GETDATE()))

DECLARE @P3_Day int = 1
DECLARE @P3_Month int  = MONTH(DATEADD(MONTH,-1,GETDATE()))
DECLARE @P3_Year int = YEAR(GETDATE())


set @P1_From = cast(cast(@P1_Year*10000 + @P1_Month*100 + 1 as varchar(255)) as date)
set @P1_To = DATEADD(DAY, -1, DATEADD(MONTH, 1 ,@P1_From))

set @P2_From = cast(cast(@P2_Year*10000 + @P2_Month*100 + 1 as varchar(255)) as date)
set @P2_To = DATEADD(DAY, -1, DATEADD(MONTH, 1 ,@P2_From))

set @P3_From = cast(cast(@P3_Year*10000 + @P3_Month*100 + 1 as varchar(255)) as date)
set @P3_To = DATEADD(DAY, -1, DATEADD(MONTH, 1 ,@P3_From))

set @CalcDate = cast(getdate() as date)
-- set @CalcDate = '2018-06-29'
set @RoundingThreshold = (SELECT Default_Value FROM System_Default WHERE Default_Name = 'RoundingThreshold')



-- If no historical sales data exists, then use the category type sales data for last year to calculate par factor and projected sales factor.

IF (EXISTS ( SELECT TOP 1 * FROM Stock_Receipt_Line SRL
                            JOIN Stock_Receipt SR ON SRL.Stock_Receipt_ID = SR.Stock_Receipt_ID
                WHERE Product_ID = @Product_ID and SR.Authorised_Date <= @P1_From
                ORDER BY SR.Authorised_Date))
    begin

        select 

         @Result = CAST(CASE WHEN  ISNULL(P1.Units_Sold,0) != 0 and ISNULL(P2.Units_Sold,0) != 0 THEN  (CAST(ISNULL(P2.Units_Sold,0) AS decimal(10,2)) / ISNULL(P1.Units_Sold,0)) * ISNULL(P3.Units_Sold,0) ELSE ISNULL(P3.Units_Sold,0) END AS INT)

        , @Ratio = CAST(CASE WHEN  ISNULL(P1.Units_Sold,0) != 0 and ISNULL(P2.Units_Sold,0) != 0 THEN  (CAST(ISNULL(P2.Units_Sold,0) AS decimal(10,2)) / ISNULL(P1.Units_Sold,0)) ELSE ISNULL(P3.Units_Sold,0) END AS decimal(18,2))

        , @SOH = isnull((select sum(sic.Stock_Count) from Stock_Item_Count sic join Stock_Item si on sic.Stock_Item_ID = si.Stock_Item_ID where si.Product_ID = p.Product_ID and si.Item_Stock_Type_ID = 4 and sic.Stock_Holding_Group_ID = 2),0) 

        , @FromType = 0

        , @Conversion = (select TOP(1) conversion from HQ_Product_Stock where product_id = @Product_ID)

        FROM PRODUCT P
        JOIN Product_Catalogue PC ON P.Product_ID = PC.Product_ID
        JOIN product_category PCT ON P.Product_Category_ID = PCT.Product_Category_ID
        JOIN product_category PCC ON PCT.Parent_ID = PCC.Product_Category_ID
        JOIN product_category PCD ON PCC.Parent_ID = PCD.Product_Category_ID

        left join (SELECT sps.Product_ID, sum(sps.Units_Sold) as Units_Sold FROM Sales_Product_Summary SPS join Trading_Day TD on td.Trading_Day_ID = SPS.Trading_Day_ID where td.Trading_Date >= @P1_From and td.Trading_Date <= @P1_To group by sps.Product_ID) as P1 on p.Product_ID = P1.Product_ID
        left join (SELECT sps.Product_ID, sum(sps.Units_Sold) as Units_Sold FROM Sales_Product_Summary SPS join Trading_Day TD on td.Trading_Day_ID = SPS.Trading_Day_ID where td.Trading_Date >= @P2_From and td.Trading_Date <= @P2_To group by sps.Product_ID) as P2 on p.Product_ID = P2.Product_ID
        left join (SELECT sps.Product_ID, sum(sps.Units_Sold) as Units_Sold FROM Sales_Product_Summary SPS join Trading_Day TD on td.Trading_Day_ID = SPS.Trading_Day_ID where td.Trading_Date >= @P3_From and td.Trading_Date <= @P3_To group by sps.Product_ID) as P3 on p.Product_ID = P3.Product_ID


        WHERE PC.Supplier_ID = 1 AND P.Product_ID = @Product_ID

    end

ELSE
    begin

        select 


          @Result = CAST(CASE WHEN  ISNULL(P1.Units_Sold,0) != 0 and ISNULL(P2.Units_Sold,0) != 0 THEN  (CAST(ISNULL(P2.Units_Sold,0) AS decimal(10,2)) / ISNULL(P1.Units_Sold,0)) * ISNULL(P3.Units_Sold,0) ELSE ISNULL(P3.Units_Sold,0) END AS INT)
        , @Ratio = CAST(CASE WHEN  ISNULL(P1.Units_Sold,0) != 0 and ISNULL(P2.Units_Sold,0) != 0 THEN  (CAST(ISNULL(P2.Units_Sold,0) AS decimal(10,2)) / ISNULL(P1.Units_Sold,0)) ELSE ISNULL(P3.Units_Sold,0) END AS decimal(18,2))
        , @SOH = isnull((select sum(sic.Stock_Count) from Stock_Item_Count sic join Stock_Item si on sic.Stock_Item_ID = si.Stock_Item_ID where si.Product_ID = p.Product_ID and si.Item_Stock_Type_ID = 4 and sic.Stock_Holding_Group_ID = 2),0) 
        , @FromType = 1
        , @Conversion = (select TOP(1) conversion from HQ_Product_Stock where product_id = @Product_ID)
        FROM PRODUCT P
        JOIN Product_Catalogue PC ON P.Product_ID = PC.Product_ID
        JOIN product_category PCT ON P.Product_Category_ID = PCT.Product_Category_ID
        JOIN product_category PCC ON PCT.Parent_ID = PCC.Product_Category_ID
        JOIN product_category PCD ON PCC.Parent_ID = PCD.Product_Category_ID

        left join (SELECT spsp.Product_Category_ID,  sum(sps.Units_Sold) as Units_Sold FROM Sales_Product_Summary SPS join Trading_Day TD on td.Trading_Day_ID = SPS.Trading_Day_ID join Product spsp on sps.Product_ID = spsp.Product_ID where td.Trading_Date >= @P1_From and td.Trading_Date <= @P1_To group by spsp.Product_Category_ID) as P1 on p.Product_Category_ID = P1.Product_Category_ID
        left join (SELECT spsp.Product_Category_ID,  sum(sps.Units_Sold) as Units_Sold FROM Sales_Product_Summary SPS join Trading_Day TD on td.Trading_Day_ID = SPS.Trading_Day_ID join Product spsp on sps.Product_ID = spsp.Product_ID where td.Trading_Date >= @P2_From and td.Trading_Date <= @P2_To group by spsp.Product_Category_ID) as P2 on p.Product_Category_ID = P2.Product_Category_ID
        left join (SELECT spsp.Product_Category_ID,  sum(sps.Units_Sold) as Units_Sold FROM Sales_Product_Summary SPS join Trading_Day TD on td.Trading_Day_ID = SPS.Trading_Day_ID join Product spsp on sps.Product_ID = spsp.Product_ID where td.Trading_Date >= @P3_From and td.Trading_Date <= @P3_To group by spsp.Product_Category_ID) as P3 on p.Product_Category_ID = P3.Product_Category_ID

        WHERE PC.Supplier_ID = 1 AND P.Product_ID = @Product_ID 

    end

    If (exists(SELECT Product_Catalogue_Status_ID FROM Product_Catalogue PC WHERE PC.Product_ID = @Product_ID AND PC.Supplier_ID = 1 AND Product_Catalogue_Status_ID in (3,4,7) )) -- Untried Lines / Supplier Discontinued / Store Discontinued
    Begin
        SELECT @Result = 0
    End

    -- Check if item is a gaming item
    declare @IsGaming bit
    set @IsGaming = CASE WHEN EXISTS(
                                    select pc3.Description, pc2.Description, pc1.Description, Product_Code, p.Description 
                                    from product p
                                    inner join product_category pc1 on p.Product_Category_ID = pc1.Product_Category_ID
                                    inner join product_category pc2 on pc1.Parent_ID = pc2.Product_Category_ID
                                    inner join product_category pc3 on pc2.Parent_ID = pc3.Product_Category_ID
                                    where UPPER(pc3.Description) = 'GAMING' AND UPPER(PC2.Description) IN ('PC', 'PLAYSTATION', 'XBOX') AND p.Product_ID = @Product_ID
                                    ) THEN 1 ELSE 0 END 

    If (exists(SELECT Product_Catalogue_Status_ID FROM Product_Catalogue PC WHERE PC.Product_ID = @Product_ID AND PC.Supplier_ID = 1 AND Product_Catalogue_Status_ID in (1,6) And (@IsGaming = 1 or @FromType = 1))) -- Gaming Lines or From Type, then use Old Par
    Begin
        SELECT @Result = CalcPar from Reorder_Product_Data where Product_ID = @Product_ID

        --If (exists(select * 
        --          from Promotion p
        --          join Promotion_Item pi on p.Promotion_ID = pi.Promotion_ID
        --          where p.LockDownDate is not null and p.LockDownDate <= cast(GETDATE() as DATE) and p.Period_End >= cast(GETDATE() as DATE) and pi.Product_ID = @Product_ID and ParOverride > @Result))
        --begin
        --SELECT @Result = ParOverride from Promotion p
        --          join Promotion_Item pi on p.Promotion_ID = pi.Promotion_ID
        --          where p.LockDownDate is not null and p.LockDownDate <= cast(GETDATE() as DATE) and p.Period_End >= cast(GETDATE() as DATE) and pi.Product_ID = @Product_ID
        --end
    End


    If (exists(SELECT OverrideProj_Dec FROM Product_Catalogue PC WHERE PC.Product_ID = @Product_ID AND PC.Supplier_ID = 1 and OverrideProj_Dec is not null and OverrideExpires_Dec > GETDATE())) -- Overridden
    Begin
        select @Result = OverrideProj_Dec FROM Product_Catalogue PC WHERE PC.Product_ID = @Product_ID AND PC.Supplier_ID = 1
    End

    /*Promotion Par Override*/
    If (@Result = 0 AND exists(select 0 from Promotion_Item pit
                               inner join Promotion p on p.Promotion_ID = pit.Promotion_ID
                               where (p.LockDownDate <= GETDATE() AND p.Period_End >= GETDATE()) AND pit.Product_ID = @Product_ID))
    Begin
        select @Result = pit.ParOverride from Promotion_Item pit
                         inner join Promotion p on p.Promotion_ID = pit.Promotion_ID
                         where (p.LockDownDate <= GETDATE() AND p.Period_End >= GETDATE()) AND pit.Product_ID = @Product_ID
    End

    If (@Conversion > 0)
    Begin
        set @Result = CEILING (CAST(@Result AS float) / CAST(@Conversion AS float))
        set @SOH = CEILING (CAST(@SOH AS float) / CAST(@Conversion AS float))

    End
    set @Recommended = CASE WHEN @Result-@SOH < 0 THEN 0 ELSE @Result-@SOH END 

    INSERT INTO @ProjectionResults
    VALUES
    (@Ratio,@Result,@SOH, @Recommended, @FromType)

    RETURN 
END
GO

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

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