if (select count(*) from Script_History where Script_Name = '2025JulyWeek3') > 0
    begin
        -- message, severity, state
        raiserror (N'Script already executed', 16, 1);
        return;
    end

/*
=========================================
EXECUTING SCRIPT GOES BELOW THIS LINE
=========================================
*/


if col_length('dbo.hq_product_stock', 'IsExtendedLayby') is null
    begin
        alter table dbo.HQ_Product_Stock
            add IsExtendedLayby bit not null default 0;
    end
go

if (select count(0) from Day_End_Summary_Category where Description = 'LaybyBookValue') = 0
    begin
        insert into Day_End_Summary_Category(description, type, friendlydescription, calculationtypeid, vatincl, detail, reportingdivisionid, isamount)
        values ('LaybyBookValue', 1, 'CurrentActiveLaybyValues', 1, 0, 'Sum total of the current active laybys', null, 1)
    end
go

if object_id('spGetActiveLaybyValue', 'P') is not null
    begin
        drop procedure spGetActiveLaybyValue
    end
go

CREATE procedure spGetActiveLaybyValue
as
begin
    set nocount on;
    /*
    Gets the sum total of the current active laybys
    grouped by layby type
    */
    with cte as (select stl.Unit_Cost  AS CostExVat,
                        lb.LaybyTypeId AS Type
                 from Sales_Transaction_Line stl
                          inner join sales_transaction st on st.Sales_Transaction_ID = stl.Sales_Transaction_ID
                          inner join lay_bye lb on lb.Sales_Transaction_ID = st.Sales_Transaction_ID
                 where st.Sales_Transaction_Type_ID = 2
                   and lb.Lay_Bye_Status_ID = 1)
    select isnull(round(sum(cte.CostExVat), 2), 0) AS CostExVat,
           cte.Type
    from cte
    group by cte.Type;

end
go


if object_id('spGetCcwLaybys', 'P') is not null
    begin
        drop procedure spGetCcwLaybys
    end
go

CREATE procedure spGetCcwLaybys
as
begin
    set nocount on;

    select vw.Division,
           vw.Category,
           vw.Type,
           p.Description,
           p.Product_Code   AS Model,
           pb.Description   AS Make,
           hps.Current_Cost AS Cost,
           cast(0 as decimal(18, 2))                AS Deposit,
           cast(0 as decimal(18, 2))                AS Installment,
           pr.RRP
    from HQ_Product_Stock hps
             inner join product p on p.Product_ID = hps.Product_ID
             inner join Product_Brand pb on p.Product_Brand_ID = pb.Product_Brand_ID
             inner join vwproductcategory vw on vw.Product_Category_ID = p.Product_Category_ID
             outer apply (select top (1) pr.RRP
                          from Pricing pr
                          where pr.Product_ID = p.Product_ID
                          order by To_Date desc) pr
    where IsExtendedLayby = 1 and pr.RRP > 0

end
go

if (select count(*)
    from SystemSetting
    where SettingKey = 'CcwLaybyTaskTimerInterval') = 0
    begin
        INSERT INTO SystemSetting (SettingKey, SettingCategoryId, SettingValueInt, TypeFlag)
        VALUES ('CcwLaybyTaskTimerInterval', 1, 600, 4) /*10 minutes*/
    end
go

if object_id('CcwLaybyTaskList', 'U') is null
    begin
        CREATE TABLE [dbo].[CcwLaybyTaskList]
        (
            [CcwLaybyTaskListId]   [bigint] IDENTITY (1,1) NOT NULL,
            [Product_ID]           [bigint]                NOT NULL,
            [CcwLaybyActionTypeId] [bigint]                NOT NULL,
            [Approver]             [bigint]                NULL,
            [Completed]            [datetime]              NULL,
            CONSTRAINT [PK_CcwLaybyTaskList] PRIMARY KEY CLUSTERED
                (
                 [CcwLaybyTaskListId] ASC
                    ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
        ) ON [PRIMARY]
    end
GO

if object_id('CcwLaybyActionType', 'U') is null
    begin
        CREATE TABLE [dbo].[CcwLaybyActionType]
        (
            [CcwLaybyActionTypeId] [bigint] IDENTITY (1,1) NOT NULL,
            [Description]          [varchar](200)          NOT NULL,
            CONSTRAINT [PK_CcwLaybyActionType] PRIMARY KEY CLUSTERED
                (
                 [CcwLaybyActionTypeId] ASC
                    ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
        ) ON [PRIMARY]
    end
GO

if object_id('CcwLaybyActionType', 'U') is not null
    begin
        SET IDENTITY_INSERT [dbo].[CcwLaybyActionType] ON
        INSERT INTO [dbo].[CcwLaybyActionType] (CcwLaybyActionTypeId, Description)
        VALUES (1, 'Print the required CCW lay-by POP material and Confirm that the POP material has been physically placed on the product'),
               (2, 'Remove the CCW lay-by POP material and Confirm that the POP material has been physically removed from the product')
        SET IDENTITY_INSERT [dbo].[CcwLaybyActionType] OFF
    end
GO

IF (OBJECT_ID('spSetCcwLaybyTask', 'P')) IS NOT NULL
    BEGIN
        DROP PROCEDURE [dbo].[spSetCcwLaybyTask]
    END
GO

CREATE PROCEDURE spSetCcwLaybyTask 
    @Product_ID bigint,
    @CcwLaybyActionTypeId bigint
AS
BEGIN
    /*Clear out any incomplete task linked to this product, only 1 can be active at a time*/
    UPDATE CcwLaybyTaskList 
    SET 
        Approver = 2, 
        Completed = GETDATE()
    WHERE Product_ID = @Product_ID AND Completed IS NULL

    /*Create new task*/
    INSERT INTO CcwLaybyTaskList 
    (Product_ID, CcwLaybyActionTypeId) 
    VALUES 
    (@Product_ID, @CcwLaybyActionTypeId)
END
GO

IF (OBJECT_ID('spGetOpenCcwLaybyTasks', 'P')) IS NOT NULL
    BEGIN
        DROP PROCEDURE [dbo].[spGetOpenCcwLaybyTasks]
    END
GO

CREATE PROCEDURE spGetOpenCcwLaybyTasks
AS
BEGIN
    SELECT 
        CcwLaybyTaskListId, 
        CONCAT(pb.Description, ' ', pr.Product_Code, ' ', ccwat.Description) Description 
    FROM CcwLaybyTaskList ccwlt 
    INNER JOIN Product pr ON pr.Product_ID = ccwlt.Product_ID
    INNER JOIN Product_Brand pb ON pb.Product_Brand_ID = pr.Product_Brand_ID
    INNER JOIN CcwLaybyActionType ccwat ON ccwat.CcwLaybyActionTypeId = ccwlt.CcwLaybyActionTypeId
    WHERE ccwlt.Completed IS NULL
END
GO

IF (OBJECT_ID('spSetCcwLaybyTaskComplete', 'P')) IS NOT NULL
    BEGIN
        DROP PROCEDURE [dbo].[spSetCcwLaybyTaskComplete]
    END
GO

CREATE PROCEDURE spSetCcwLaybyTaskComplete
    @Approver bigint,
    @CcwLaybyTaskListId bigint
AS
BEGIN
    UPDATE CcwLaybyTaskList 
    SET 
        Approver = @Approver, 
        Completed = GETDATE()
    WHERE CcwLaybyTaskListId = @CcwLaybyTaskListId
END
GO

ALTER VIEW [dbo].[vwStock]
AS
SELECT        Product_ID, Product_Category_ID, Product_Brand_ID, Make, Product_Code, Description, Stock_Code, Item_Stock_Type_ID, Serial_Number, Condition, Unit_Cost, Notes, 
              LayByeDate, Target_Release_Date, Source_Contract, Stock_Holding_Group_ID, Stock_Count, stocktbldesc, Stock_Item_Count_ID, Category, Division, Type, Stock_Value, 
              HQ_Product_ID, (CASE WHEN PromoRRP > 0 THEN
              (SELECT CASE WHEN Soiled = 1 THEN Standard_RRP ELSE PromoRRP END) ELSE Standard_RRP END) AS RRP, (CASE WHEN PromoRRPExVat > 0 THEN
              (SELECT CASE WHEN Soiled = 1 THEN Standard_RRP_ExVat ELSE PromoRRPExVat END) ELSE Standard_RRP_ExVat END) - Unit_Cost AS GPRands,
                CAST(((( (CASE WHEN PromoRRPExVat > 0 THEN  (SELECT CASE WHEN Soiled = 1 THEN Standard_RRP_ExVat ELSE PromoRRPExVat END) ELSE Standard_RRP_ExVat END) - Unit_Cost)) /                       
                (case when (CASE WHEN PromoRRPExVat > 0 THEN  (SELECT CASE WHEN Soiled = 1 THEN Standard_RRP_ExVat ELSE PromoRRPExVat END) ELSE Standard_RRP_ExVat END) = 0 then 1 else 
                (CASE WHEN PromoRRPExVat > 0 THEN  (SELECT CASE WHEN Soiled = 1 THEN Standard_RRP_ExVat ELSE PromoRRPExVat END) ELSE Standard_RRP_ExVat END) end)) * 100 as numeric(38, 2)) as GPPerc            
              , Store_ReProcessed, HQ_Processed, 
              (CASE WHEN PromoRRP > 0 THEN 'ACTIVE' ELSE 'NONE' END) AS Promo, CCF_Funded, (CASE WHEN Supplier_ID IS NULL THEN 'Buyshop' ELSE Supplier_ID END) AS Supplier_ID, Division_ID, CategoryId, 
              Sales_Date AS Last_Sale_Date, Product_Catalogue_Status, Original_Purchase_Date, DATEDIFF(DAY, Target_Release_Date, Sales_Date) AS Days_To_Sell, DATEDIFF(DAY, Target_Release_Date, 
              (CASE WHEN Sales_Date IS NULL THEN GETDATE() ELSE Sales_Date END)) AS Days_In_Store, Returned, skuIn AS SKU, CAST((CASE WHEN ItemOnHold IS NULL THEN 0 ELSE 1 END) AS BIT) AS OnHold, 
              CAST((CASE WHEN Soiled IS NULL THEN 0 ELSE 1 END) AS BIT) AS Soiled, Standard_RRP, Standard_RRP_ExVat, 
              ISNULL(IsExtendedLayby, CAST(0 AS BIT)) AS 'Elegible_For_CCW'

FROM            (SELECT dbo.Product.Product_ID, dbo.Product.Product_Category_ID, dbo.Product.Product_Brand_ID, dbo.Product.Product_Code, dbo.Product.Description, dbo.Stock_Item.Stock_Code, dbo.Stock_Item.Item_Stock_Type_ID, 
                        dbo.Stock_Item.Serial_Number, ic.Description AS Condition, dbo.Stock_Item.Unit_Cost, ss.Notes, dbo.Lay_Bye.Expiry_Date AS LayByeDate, dbo.Stock_Item.Target_Release_Date, dbo.Stock_Item.Source_Contract, 
                        dbo.Stock_Item_Count.Stock_Holding_Group_ID, dbo.Stock_Item_Count.Stock_Count, dbo.Stock_Item.Item_Description AS stocktbldesc, dbo.Stock_Item_Count.Stock_Item_Count_ID, 
                        dbo.vwProductCategory.Category, dbo.vwProductCategory.Division, dbo.vwProductCategory.Type, 
                        (CASE WHEN stock_item.is_Client_Stock = 1 THEN 0 ELSE dbo.Stock_Item.Unit_Cost * dbo.Stock_Item_Count.Stock_Count END) AS Stock_Value, dbo.Product.HQ_Product_ID, 
                            ISNULL ((SELECT TOP (1) RRP
                                     FROM dbo.Pricing
                                     WHERE (Stock_Item_ID = dbo.Stock_Item.Stock_Item_ID)
                                     ORDER BY To_Date DESC), 0) AS Standard_RRP, 
                            ISNULL ((SELECT TOP (1) RRP - RRP_VAT AS Expr1
                                     FROM dbo.Pricing AS Pricing_1
                                     WHERE (Stock_Item_ID = dbo.Stock_Item.Stock_Item_ID)
                                     ORDER BY To_Date DESC), 0) AS Standard_RRP_ExVat, dbo.Product.Store_ReProcessed, dbo.Product.HQ_Processed, 
                            ISNULL ((SELECT TOP (1) pim.Promo_RRP
                                     FROM dbo.Promotion_Item AS pim INNER JOIN
                                          dbo.Promotion AS p ON pim.Promotion_ID = p.Promotion_ID
                                     WHERE (p.Period_Start <= CURRENT_TIMESTAMP) AND (p.Period_End >= CURRENT_TIMESTAMP) AND (pim.Product_ID = dbo.Stock_Item.Product_ID) AND (dbo.Stock_Item.Item_Stock_Type_ID IN (4, 5))
                                     ORDER BY p.Period_Start DESC), 0) AS PromoRRP, ISNULL
                            ((SELECT TOP (1) pim.Promo_RRP - pim.Promo_RRP_VAT AS Expr1
                              FROM dbo.Promotion_Item AS pim INNER JOIN
                                   dbo.Promotion AS p ON pim.Promotion_ID = p.Promotion_ID
                              WHERE (p.Period_Start <= CURRENT_TIMESTAMP) AND (p.Period_End >= CURRENT_TIMESTAMP) AND (pim.Product_ID = dbo.Stock_Item.Product_ID) AND (dbo.Stock_Item.Item_Stock_Type_ID IN (4, 5))
                              ORDER BY p.Period_Start DESC), 0) AS PromoRRPExVat, ISNULL(dbo.Stock_Item.CCF_Funded, 0) AS CCF_Funded,
                            (SELECT TOP (1) s.Name
                             FROM dbo.Product_Catalogue AS pc INNER JOIN
                                  dbo.Supplier AS s ON pc.Supplier_ID = s.Supplier_ID
                             WHERE (pc.Product_ID = dbo.Stock_Item.Product_ID)
                             ORDER BY pc.Product_Catalogue_ID) AS Supplier_ID, dbo.vwProductCategory.Division_ID, dbo.vwProductCategory.CategoryId, dbo.Stock_Item.Sales_Date, pb.Description AS Make,
                            (SELECT TOP (1) pcs.Description
                             FROM dbo.Product_Catalogue AS pc INNER JOIN
                                  dbo.Product_Catalogue_Status AS pcs ON pc.Product_Catalogue_Status_ID = pcs.Product_Catalogue_Status_ID
                             WHERE (pc.Product_ID = dbo.Stock_Item.Product_ID)
                             ORDER BY pc.Product_Catalogue_ID) AS Product_Catalogue_Status, dbo.Stock_Item.Original_Purchase_Date,
                            (SELECT TOP (1) Created
                             FROM dbo.Goods_Return AS GR
                             WHERE (Stock_Item_ID = dbo.Stock_Item.Stock_Item_ID)
                             ORDER BY Created DESC) AS Returned,
                            (SELECT TOP (1) UniqueCode
                             FROM dbo.HQ_Product_Stock AS hq
                             WHERE (Product_ID = dbo.Product.Product_ID) AND (HQ_Branch_ID = (SELECT TOP (1) Default_Value
                                                                                              FROM dbo.System_Default AS sd
                                                                                              WHERE (Default_Name = 'HQ_Store_Branch')))) AS skuIn, oh.ItemOnHold, dbo.Stock_Item.Soiled,
                            (SELECT TOP(1) IsExtendedLayby 
                             FROM dbo.HQ_Product_Stock 
                             WHERE Product_ID = dbo.Product.Product_ID) IsExtendedLayby

                             FROM            dbo.Stock_Item INNER JOIN
                                             dbo.Product ON dbo.Stock_Item.Product_ID = dbo.Product.Product_ID INNER JOIN
                                             dbo.Stock_Item_Count ON dbo.Stock_Item.Stock_Item_ID = dbo.Stock_Item_Count.Stock_Item_ID INNER JOIN
                                             dbo.vwProductCategory ON dbo.Product.Product_Category_ID = dbo.vwProductCategory.Product_Category_ID INNER JOIN
                                             dbo.Product_Brand AS pb ON dbo.Product.Product_Brand_ID = pb.Product_Brand_ID LEFT OUTER JOIN
                                             dbo.Buyshop_Trans_Line AS ss ON ss.Buyshop_Trans_Line_ID = dbo.Stock_Item.Buyshop_Trans_Line_ID LEFT OUTER JOIN
                                             dbo.Item_Condition AS ic ON ic.Item_Condition_ID = ss.Item_Condition_ID LEFT OUTER JOIN
                                             dbo.Lay_Bye ON dbo.Lay_Bye.Stock_Item_ID = dbo.Stock_Item.Stock_Item_ID AND dbo.Lay_Bye.Lay_Bye_Status_ID = 1 AND dbo.Lay_Bye.laybystockstatusid = 3 LEFT OUTER JOIN
                                             (SELECT DISTINCT Stock_Item_ID, 1 AS ItemOnHold
                                              FROM (SELECT Stock_Item_ID,
                                                   (SELECT TOP (1) Created
                                                    FROM dbo.StockOnHold AS h
                                                    WHERE (Stock_Item_ID = s.Stock_Item_ID)
                                                    ORDER BY StockOnHoldID DESC) AS created,
                                                   (SELECT TOP (1) StockOnHoldStatusID
                                                    FROM dbo.StockOnHold AS h
                                                    WHERE (Stock_Item_ID = s.Stock_Item_ID)
                                                    ORDER BY StockOnHoldID DESC) AS stockonholdstatusid
                 FROM dbo.StockOnHold AS s) AS a
                 WHERE (stockonholdstatusid = 1)) AS oh ON dbo.Stock_Item.Stock_Item_ID = oh.Stock_Item_ID
WHERE (dbo.Stock_Item_Count.Stock_Count > 0)) AS intbl
GO


ALTER PROCEDURE [dbo].[spStockItemSearch] @SearchValue nvarchar(30) AS 

SELECT DISTINCT TOP(50) 
    SIC.Stock_Item_ID, 
    SI.Stock_Code, 
    SIC.Stock_Count, 
    PC3.Description + '-' + PC2.Description + (CASE WHEN PC1.Description = '' THEN '' ELSE '-' + PC1.Description END) AS Composite,     
    prg.RRP,    
    pb.description + ' ' + p.product_code AS Description,   
    ist.description AS Stock_Type,
    (SELECT TOP(1) IsExtendedLayby 
     FROM dbo.HQ_Product_Stock 
     WHERE Product_ID = SI.Product_ID) IsExtendedLayby
FROM STOCK_ITEM_COUNT SIC   
JOIN STOCK_ITEM SI ON SIC.STOCK_ITEM_ID = SI.STOCK_ITEM_ID      
JOIN PRODUCT P ON SI.Product_ID = P.PRODUCT_ID      
JOIN Product_Brand PB ON P.Product_Brand_ID = PB.Product_Brand_ID       
JOIN ITEM_STOCK_TYPE IST ON SI.ITEM_STOCK_TYPE_ID = IST.ITEM_STOCK_TYPE_ID      
LEFT OUTER JOIN PRICING PRG ON SI.Stock_Item_ID = PRG.Stock_Item_ID AND PRG.FROM_DATE <= SYSDATETIME() AND PRG.TO_DATE > SYSDATETIME()          
LEFT OUTER JOIN product_category AS PC1 on PC1.Product_Category_ID= P.Product_Category_ID       
INNER JOIN product_category AS PC2 ON PC1.Parent_ID = PC2.Product_Category_ID   
INNER JOIN product_category AS PC3 ON PC2.Parent_ID = PC3.Product_Category_ID   
where 
    (si.stock_code LIKE '%' + @SearchValue + '%' OR 
     PB.DESCRIPTION LIKE  '%' + @SearchValue + '%'  OR 
     PC3.Description + '-' + PC2.Description + (CASE WHEN PC1.Description = '' THEN '' ELSE '-' + PC1.Description END) LIKE '%' + @SearchValue + '%' OR P.Product_Code LIKE '%' + @SearchValue + '%') AND 
     sic.Stock_Holding_Group_ID = 2 AND 
     sic.Stock_Count >= 1 AND  
     prg.RRP > 0 
Order By Description ASC
GO

if((select count(0) from System_Default where Default_Name = 'Region_code' and Default_Value = 'SADC') =0)
begin
update SystemSetting set SettingValueBit = 1 where SettingKey ='CcwLayby';
End



/*
=========================================
EXECUTING SCRIPT GOES ABOVE THIS LINE
=========================================
*/

insert into Script_History(RunDate, Script_Name)
values (getdate(), '2025JulyWeek3') -- script name has max of 50 characters
go