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

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

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[LaybyTerm](
[LaybyTermId] [int] IDENTITY(1,1) NOT NULL,
[Description] varchar(30) NOT NULL,
[TotalMonths] [int] NOT NULL,
[IsActive] [bit] NOT NULL,
CONSTRAINT [PK_LaybyTerm] PRIMARY KEY CLUSTERED
(
[LaybyTermId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO


SET IDENTITY_INSERT [dbo].[LaybyTerm] ON

INSERT [dbo].[LaybyTerm] ([LaybyTermId], [Description], [TotalMonths], [IsActive]) VALUES (1, '1 Month', 1, 1)
GO
INSERT [dbo].[LaybyTerm] ([LaybyTermId], [Description], [TotalMonths], [IsActive]) VALUES (2, '2 Months', 2, 1)
GO
INSERT [dbo].[LaybyTerm] ([LaybyTermId], [Description], [TotalMonths], [IsActive]) VALUES (3, '3 Months', 3, 1)
GO
INSERT [dbo].[LaybyTerm] ([LaybyTermId], [Description], [TotalMonths], [IsActive]) VALUES (4, '4 Months', 4, 1)
GO
INSERT [dbo].[LaybyTerm] ([LaybyTermId], [Description], [TotalMonths], [IsActive]) VALUES (5, '5 Months', 5, 1)
GO
INSERT [dbo].[LaybyTerm] ([LaybyTermId], [Description], [TotalMonths], [IsActive]) VALUES (6, '6 Months', 6, 1)
GO
INSERT [dbo].[LaybyTerm] ([LaybyTermId], [Description], [TotalMonths], [IsActive]) VALUES (7, '7 Months', 7, 1)
GO
INSERT [dbo].[LaybyTerm] ([LaybyTermId], [Description], [TotalMonths], [IsActive]) VALUES (8, '8 Months', 8, 1)
GO
INSERT [dbo].[LaybyTerm] ([LaybyTermId], [Description], [TotalMonths], [IsActive]) VALUES (9, '9 Months', 9, 1)
GO
INSERT [dbo].[LaybyTerm] ([LaybyTermId], [Description], [TotalMonths], [IsActive]) VALUES (10, '10 Months', 10, 1)
GO
INSERT [dbo].[LaybyTerm] ([LaybyTermId], [Description], [TotalMonths], [IsActive]) VALUES (11, '11 Months', 11, 1)
GO
INSERT [dbo].[LaybyTerm] ([LaybyTermId], [Description], [TotalMonths], [IsActive]) VALUES (12, '12 Months', 12, 1)
GO
SET IDENTITY_INSERT [dbo].[LaybyTerm] OFF
Go



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[StoreLaybySetting](
    [StoreLaybyeSettingId] [int] NOT NULL,
    [NewGoodsMinLaybyTermId] [int] NOT NULL,
    [NewGoodsMaxLaybyTermId] [int] NOT NULL,
    [NewGoodsStandardLaybyTermId] [int] NOT NULL,
    [NewGoodsMinDepositAmount] [decimal](18, 2) NOT NULL,
    [AllowCashierToChangeTermNewGoods] [bit] NOT NULL,
    [SecHandMinLaybyTermId] [int] NOT NULL,
    [SecHandMaxLaybyTermId] [int] NOT NULL,
    [SecHandStandardLaybyTermId] [int] NOT NULL,
    [SecHandMinDepositAmount] [decimal](18, 2) NOT NULL,
    [AllowCashierToChangeTermSecHand] [bit] NOT NULL,
    [SecHandLaybyEnabled] [bit] NOT NULL,
 CONSTRAINT [PK_StoreLaybySetting] PRIMARY KEY CLUSTERED 
(
    [StoreLaybyeSettingId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[StoreLaybySetting] ([StoreLaybyeSettingId], [NewGoodsMinLaybyTermId], [NewGoodsMaxLaybyTermId], [NewGoodsStandardLaybyTermId], [NewGoodsMinDepositAmount], [AllowCashierToChangeTermNewGoods], [SecHandMinLaybyTermId], [SecHandMaxLaybyTermId], [SecHandStandardLaybyTermId], [SecHandMinDepositAmount], [AllowCashierToChangeTermSecHand], [SecHandLaybyEnabled]) VALUES (1, 1, 6, 6, CAST(100.00 AS Decimal(18, 2)), 1, 1, 6, 2, CAST(100.00 AS Decimal(18, 2)), 1, 1)
GO
ALTER TABLE [dbo].[StoreLaybySetting]  WITH CHECK ADD  CONSTRAINT [FK_StoreLaybySetting_LaybyTerm] FOREIGN KEY([NewGoodsMinLaybyTermId])
REFERENCES [dbo].[LaybyTerm] ([LaybyTermId])
GO
ALTER TABLE [dbo].[StoreLaybySetting] CHECK CONSTRAINT [FK_StoreLaybySetting_LaybyTerm]
GO
ALTER TABLE [dbo].[StoreLaybySetting]  WITH CHECK ADD  CONSTRAINT [FK_StoreLaybySetting_LaybyTerm1] FOREIGN KEY([NewGoodsMaxLaybyTermId])
REFERENCES [dbo].[LaybyTerm] ([LaybyTermId])
GO
ALTER TABLE [dbo].[StoreLaybySetting] CHECK CONSTRAINT [FK_StoreLaybySetting_LaybyTerm1]
GO
ALTER TABLE [dbo].[StoreLaybySetting]  WITH CHECK ADD  CONSTRAINT [FK_StoreLaybySetting_LaybyTerm2] FOREIGN KEY([NewGoodsStandardLaybyTermId])
REFERENCES [dbo].[LaybyTerm] ([LaybyTermId])
GO
ALTER TABLE [dbo].[StoreLaybySetting] CHECK CONSTRAINT [FK_StoreLaybySetting_LaybyTerm2]
GO
ALTER TABLE [dbo].[StoreLaybySetting]  WITH CHECK ADD  CONSTRAINT [FK_StoreLaybySetting_LaybyTerm3] FOREIGN KEY([SecHandMinLaybyTermId])
REFERENCES [dbo].[LaybyTerm] ([LaybyTermId])
GO
ALTER TABLE [dbo].[StoreLaybySetting] CHECK CONSTRAINT [FK_StoreLaybySetting_LaybyTerm3]
GO
ALTER TABLE [dbo].[StoreLaybySetting]  WITH CHECK ADD  CONSTRAINT [FK_StoreLaybySetting_LaybyTerm4] FOREIGN KEY([SecHandMaxLaybyTermId])
REFERENCES [dbo].[LaybyTerm] ([LaybyTermId])
GO
ALTER TABLE [dbo].[StoreLaybySetting] CHECK CONSTRAINT [FK_StoreLaybySetting_LaybyTerm4]
GO
ALTER TABLE [dbo].[StoreLaybySetting]  WITH CHECK ADD  CONSTRAINT [FK_StoreLaybySetting_LaybyTerm5] FOREIGN KEY([SecHandStandardLaybyTermId])
REFERENCES [dbo].[LaybyTerm] ([LaybyTermId])
GO
ALTER TABLE [dbo].[StoreLaybySetting] CHECK CONSTRAINT [FK_StoreLaybySetting_LaybyTerm5]
GO



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[StoreLaybySettingHistory](
[StoreLaybySettingHistoryId] [bigint] IDENTITY(1,1) NOT NULL,
[NewGoodsMinLaybyTermId] [int] NOT NULL,
[NewGoodsMaxLaybyTermId] [int] NOT NULL,
[NewGoodsStandardLaybyTermId] [int] NOT NULL,
[NewGoodsMinDepositAmount] [decimal](18, 2) NOT NULL,
[AllowCashierToChangeTermNewGoods] [bit] NOT NULL,
[SecHandMinLaybyTermId] [int] NOT NULL,
[SecHandMaxLaybyTermId] [int] NOT NULL,
[SecHandStandardLaybyTermId] [int] NOT NULL,
[SecHandMinDepositAmount] [decimal](18, 2) NOT NULL,
[AllowCashierToChangeTermSecHand] [bit] NOT NULL,
[SecHandLaybyEnabled] [bit] NOT NULL,
[SystemUserId] [bigint] NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
CONSTRAINT [PK_StoreLaybySettingHistory] PRIMARY KEY CLUSTERED
(
[StoreLaybySettingHistoryId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO


IF COL_LENGTH('Lay_Bye', 'TermId') IS NULL
BEGIN
ALTER TABLE Lay_Bye
ADD TermId int
END
GO

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

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[GetActiveLaybyContracts]

AS
BEGIN
SET NOCOUNT ON;

SELECT
Inception_Date AS StartDate,
[Expiry_Date] AS ExpiryDate,
ISNULL(lbt.TotalMonths, 0) AS ContractTerm,
(SELECT TOP(1) Date_Paid FROM Lay_Bye_Payment lbp WHERE lbp.Lay_Bye_ID = lb.Lay_Bye_ID ORDER BY lbp.Date_Paid DESC) AS LastPaymentDate,
CONCAT(s.First_Name, SPACE(1), s.Surname, SPACE(1), s.ID_Number) AS Customer,
lb.Contract_Number AS ContractNumber,
si.Item_Description AS StockItemDetail,
lb.Purchase_Amount AS SaleValue,
ISNULL((SELECT SUM(Amount_Paid) FROM Lay_Bye_Payment lbp WHERE lbp.Lay_Bye_ID = lb.Lay_Bye_ID), 0) AS TotalPaid,
ISNULL((SELECT lb.Purchase_Amount - SUM(Amount_Paid) FROM Lay_Bye_Payment lbp WHERE lbp.Lay_Bye_ID = lb.Lay_Bye_ID), 0) AS TotalOwing,
ISNULL(DATEDIFF(DAY, (SELECT TOP(1) Date_Paid FROM Lay_Bye_Payment lbp WHERE lbp.Lay_Bye_ID = lb.Lay_Bye_ID ORDER BY lbp.Date_Paid DESC), GETDATE()), 0) AS DaysSinceLastPayment,
ISNULL(lbt.TotalMonths, 0) AS TotalPaymentsExpected,
(SELECT COUNT(0) FROM Lay_Bye_Payment lbp WHERE lbp.Lay_Bye_ID = lb.Lay_Bye_ID) AS TotalPaymentsReceived
FROM Lay_Bye lb
INNER JOIN Seller s ON s.Seller_ID = lb.Customer_ID
INNER JOIN Stock_Item si ON si.Stock_Item_ID = lb.Stock_Item_ID
LEFT JOIN LaybyTerm lbt ON lbt.LaybyTermId = lb.TermId
WHERE lb.Lay_Bye_Status_ID in (1) -- "CURRENT" status
ORDER BY lb.Expiry_Date

END
GO

IF (SELECT COUNT(0) FROM SystemSetting WHERE SettingValueString= 'LaybyMinMaxNewAndSec') = 0
insert into SystemSetting (SettingKey,SettingCategoryId,SettingValueString,TypeFlag)
values ('LaybyMinMaxNewAndSec',1,'1,6,1,6',4)
GO

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

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[spGetQuarantineReleaseLines] 

@Date datetime,
@ItemStockTypeId bigint,
@QuarantineReleaseId bigint,
@Accepted tinyint

AS
BEGIN
SET NOCOUNT ON;

/*
======================================
    TEMP TABLE CREATION (in memory)
======================================
*/
DECLARE @StockTypes table(Id int);

/*
=====================================
    POPULATE TABLE WITH TYPES
=====================================
*/

INSERT INTO @StockTypes(Id) VALUES(@ItemStockTypeId); -- this type will be either 1 or 3

IF (@ItemStockTypeId = 1)
BEGIN
    INSERT INTO @StockTypes(Id) VALUES(2); -- we also insert type 2 when passed type is 1
END

SELECT        
lines.Quarintine_Rel_Line_ID AS QuarantineReleaseLineId, 
s.Stock_Code AS StockCode, 
p.Product_Category_ID AS ProductCategoryId, 
pb.Description + ' ' + p.Product_Code AS Product, 
s.Unit_Cost AS UnitCost, 
lines.MarkupPerc, 
lines.GP AS Gp, 
lines.GPPerc AS GpPerc, 
lines.RRP AS Rrp, 
s.Source_Contract AS SourceContract, 
i.Description AS ItemStockType, 
lines.Release, 
lines.Accepted, 
s.Target_Release_Date AS TargetReleaseDate, 
s.Stock_Item_ID AS StockItemId, 
lines.Quarintine_Release_ID AS QuarantineReleaseId, 
s.Original_Purchase_Date AS OriginalPurchaseDate, 
s.Vat, 
sc.Stock_Count AS StockCount, 
s.Buyshop_Trans_Line_ID AS BuyshopTransLineId, 
p.Description, 
p.ForHQReview, 
p.HQ_Processed AS HqProcessed, 
vw.Composite AS Category, 
btl.Notes, 
ic.Description AS ItemCondition,
ISNULL(btlc.CCWebsitePrice1, 0) + ISNULL(btlc.CCWebsitePrice2, 0) + ISNULL(btlc.CCWebsitePrice3, 0) AS AvgWebsitePrice,
ISNULL(btlc.FacebookMarketplacePrice1, 0) + ISNULL(btlc.FacebookMarketplacePrice2, 0) + ISNULL(btlc.FacebookMarketplacePrice3, 0) AS AvgFacebookPrice,
ISNULL(btlc.OtherPrice1, 0) + ISNULL(btlc.OtherPrice2, 0) + ISNULL(btlc.OtherPrice3, 0) AS AvgOtherPrice,
btlc.CMSRegionalPriceAverage AS AvgRegionalPrice,
btlc.CMSRegionalDaysToSell AS AvgRegionalDaysToSell,
btlc.CMSNationalPriceAverage AS AvgNationalPrice,
btlc.CMSNationalDaysToSell AS AvgNationalDaysToSell,
/*
===============
    DEPOSIT
===============
*/
(CASE WHEN 
    (SELECT TOP 1 ReceivedPaymentId FROM ReceivedPayments rp 
        INNER JOIN Sales_Transaction_Line stl ON stl.Sales_Transaction_ID = rp.SalesTransactionId 
        INNER JOIN Sales_Transaction st ON st.Sales_Transaction_ID = rp.SalesTransactionId 
     WHERE rp.PaymentCategoryId = 2 AND rp.SalesTransactionId = stl.Sales_Transaction_ID AND st.Sales_Status_ID = 7 AND stl.Stock_Item_ID = s.Stock_Item_ID AND rp.Refunded = 0) IS NULL 
 THEN CAST(0 AS int)  
 ELSE CAST(1 AS int) 
 END) AS Deposit,
/*
=============================
    RECEIVED PAYMENT ID
=============================
*/
(SELECT TOP (1) rp.ReceivedPaymentId FROM ReceivedPayments AS rp  
    INNER JOIN Sales_Transaction_Line AS stl ON stl.Sales_Transaction_ID = rp.SalesTransactionId AND rp.SalesTransactionId = stl.Sales_Transaction_ID  
    INNER JOIN Sales_Transaction AS st ON st.Sales_Transaction_ID = rp.SalesTransactionId 
 WHERE (rp.PaymentCategoryId = 2) AND (st.Sales_Status_ID = 7) AND (stl.Stock_Item_ID = s.Stock_Item_ID) AND (rp.Refunded = 0)) AS ReceivedPaymentId, 
/*
=============================
    LAST STOCK PRICE
=============================
*/
ISNULL(
(SELECT TOP (1) pri.RRP FROM Pricing AS pri 
    LEFT OUTER JOIN Product AS pro ON pro.Product_ID = pri.Product_ID
WHERE (pro.ItemMonsterId = p.ItemMonsterId)
ORDER BY pri.Pricing_ID DESC), 0) AS LastStockPrice, 

/*
=============================
    LAST SALE PRICE
=============================
*/
ISNULL(
(SELECT TOP (1) stl.RPP FROM Sales_Transaction_Line AS stl 
    LEFT OUTER JOIN Product AS pro ON pro.Product_ID = stl.Product_ID
 WHERE (pro.ItemMonsterId = p.ItemMonsterId) 
 ORDER BY stl.Sales_Transaction_ID DESC), 0) AS LastSalePrice, 
/*
=============================
    REGIONAL UNIT COST
=============================
*/
ISNULL(btl.RegionalUnitCost, 0) AS RegionalUnitCost, 
/*
=============================
    REGIONAL RRP
=============================
*/
ISNULL(btl.RegionalRRP, 0) AS RegionalRRP, 
/*
=============================
    DAYS TO SELL
=============================
*/
ISNULL(
(SELECT TOP (1) DATEDIFF(day, si.Original_Purchase_Date, st.Created) AS Expr1 FROM Sales_Transaction_Line AS stl 
    LEFT OUTER JOIN Sales_Transaction AS st ON st.Sales_Transaction_ID = stl.Sales_Transaction_ID 
    LEFT OUTER JOIN Stock_Item AS si ON si.Stock_Item_ID = stl.Stock_Item_ID 
    LEFT OUTER JOIN Product AS pro ON pro.Product_ID = stl.Product_ID
 WHERE (pro.ItemMonsterId = p.ItemMonsterId)
 ORDER BY stl.Sales_Transaction_ID DESC), 0) AS DaysToSell, 
/*
=============================
    DAYS IN STOCK
=============================
*/
ISNULL(
(SELECT TOP (1) DATEDIFF(day, si.Original_Purchase_Date, GETDATE()) AS DaysInStock FROM Stock_Item_Count AS sic 
    LEFT OUTER JOIN Stock_Item AS si ON si.Stock_Item_ID = sic.Stock_Item_ID 
    LEFT OUTER JOIN Product AS pr ON pr.Product_ID = si.Product_ID
 WHERE (sic.Stock_Holding_Group_ID = 2) AND (sic.Stock_Count > 0) AND (pr.ItemMonsterId = p.ItemMonsterId)
 ORDER BY si.Original_Purchase_Date), 0) AS DaysInStock

FROM Quarintine_Rel_Line AS lines 
    RIGHT OUTER JOIN Quarantine_Release AS qr ON lines.Quarintine_Release_ID = qr.Quarantine_Release_ID 
    RIGHT OUTER JOIN Stock_Item_Count AS sc ON lines.Stock_Item_ID = sc.Stock_Item_ID 
    RIGHT OUTER JOIN Stock_Item AS s ON s.Stock_Item_ID = sc.Stock_Item_ID 
    RIGHT OUTER JOIN Product AS p ON s.Product_ID = p.Product_ID 
    RIGHT OUTER JOIN Product_Brand AS pb ON p.Product_Brand_ID = pb.Product_Brand_ID 
    RIGHT OUTER JOIN Item_Stock_Type AS i ON s.Item_Stock_Type_ID = i.Item_Stock_Type_ID 
    RIGHT OUTER JOIN vwProductCategory AS vw ON p.Product_Category_ID = vw.Product_Category_ID 
    LEFT OUTER JOIN Buyshop_Trans_Line AS btl ON s.Buyshop_Trans_Line_ID = btl.Buyshop_Trans_Line_ID 
    LEFT OUTER JOIN Buyshop_Trans_Line_Calc AS btlc ON btlc.Buyshop_Trans_Line_ID = btl.Buyshop_Trans_Line_ID
    LEFT OUTER JOIN Item_Condition AS ic ON ic.Item_Condition_ID = btl.Item_Condition_ID 
WHERE (sc.Stock_Count > 0) AND (sc.Stock_Holding_Group_ID = 1) AND (s.Item_Stock_Type_ID = ANY(SELECT Id FROM @StockTypes)) 

AND 

/*
=========================================
    FILTERS ALL NON ACCEPTED LINES - based on setting in cashpos
=========================================
*/
((@Accepted = 0 AND 
    ((YEAR(s.Target_Release_Date) = YEAR(CAST(@Date AS datetime)) AND  
      MONTH(s.Target_Release_Date) = MONTH(CAST(@Date AS datetime)) AND  
      DAY(s.Target_Release_Date) = DAY(CAST(@Date AS datetime))) OR  s.Target_Release_Date <= @Date AND (lines.Quarintine_Rel_Line_ID IS NULL OR lines.Quarintine_Release_ID = @QuarantineReleaseId))) OR 
/*
=========================================
    FILTERS ALL ACCEPTED LINES - based on setting in cashpos
=========================================
*/
 (@Accepted = 1 AND lines.Release = 1 AND (lines.Accepted = 0 OR lines.Accepted IS NULL)))


END
GO

IF (OBJECT_ID('spGetPendingBuys', 'P')) IS NOT NULL
BEGIN
DROP PROCEDURE spGetPendingBuys
END
GO
CREATE PROCEDURE spGetPendingBuys
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    SELECT 
    Created,
    Transaction_Number as TransactionNumber,
    CONCAT(sel.Surname, ', ', sel.First_Name, ', ', sel.ID_Number) as Seller,
    bt.is_House_Buy as IsHouseBuy,
    bt.Notes, 
    btt.Description as 'Type',
    bt.CCF_Funded as CcfFunded,
    bt.InitiationFeePaid,
    bt.Projected_into_Stock as ProjectedIntoStock,
    bts.Description as 'Status',
    bt.Comments,
    bt.Buyshop_Transaction_ID as BuyshopTransactionId
    FROM Buyshop_Transaction bt 
    INNER JOIN Seller sel on bt.Seller_ID = sel.Seller_ID
    INNER JOIN [System_User] su on bt.System_User_ID = su.System_User_ID
    INNER JOIN Buyshop_Trans_Type btt on bt.Buyshop_Trans_Type_ID = btt.Buyshop_Trans_Type_ID
    INNER JOIN Buyshop_Trans_Status bts on bt.Buyshop_Trans_Status_ID = bts.Buyshop_Trans_Status_ID
    WHERE bt.Buyshop_Trans_Status_ID <= 4
END
GO

IF (OBJECT_ID('spAddPendingBuyItem', 'P')) IS NOT NULL
BEGIN
DROP PROCEDURE spAddPendingBuyItem
END
GO
CREATE PROCEDURE spAddPendingBuyItem 
    -- Add the parameters for the stored procedure here
    @Buyshop_Transaction_Id bigint, 
    @CategoryId bigint,
    @AssociationId bigint,
    @ProductId bigint,
    @ItemConditionId bigint,
    @FinalBuyingPrice decimal,
    @RRP decimal,
    @Qty int,
    @CategoryText varchar(200),
    @MakeText varchar(200),
    @ModelText varchar(200)
AS
BEGIN
    insert into Buyshop_Pending_Items 
    (Buyshop_Transaction_Id, CategoryId, AssociationId, ProductId, ItemConditionId, FinalBuyingPrice, RRP, Qty, CategoryText, MakeText, ModelText) 
    values 
    (@Buyshop_Transaction_Id, @CategoryId, @AssociationId, @ProductId, @ItemConditionId, @FinalBuyingPrice, @RRP, @Qty, @CategoryText, @MakeText, @ModelText)
END
GO

IF (OBJECT_ID('spGetPendingBuyshopItems', 'P')) IS NOT NULL
BEGIN
DROP PROCEDURE spGetPendingBuyshopItems
END
GO
CREATE PROCEDURE spGetPendingBuyshopItems 
    -- Add the parameters for the stored procedure here
    @Buyshop_Transaction_Id bigint
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT
       Buyshop_Pending_Items_Id as BuyshopPendingItemsId
      ,Buyshop_Transaction_Id as BuyshopTransactionId
      ,CategoryId
      ,AssociationId
      ,ProductId
      ,ItemConditionId
      ,FinalBuyingPrice
      ,RRP as Rrp
      ,Qty
      ,CategoryText
      ,MakeText
      ,ModelText
    FROM 
        Buyshop_Pending_Items 
    WHERE
        Buyshop_Transaction_Id = @Buyshop_Transaction_Id
END
GO

IF (OBJECT_ID('spDeletePendingBuyshopItem', 'P')) IS NOT NULL
BEGIN
DROP PROCEDURE spDeletePendingBuyshopItem
END
GO
CREATE PROCEDURE spDeletePendingBuyshopItem 
    -- Add the parameters for the stored procedure here
    @Buyshop_Pending_Items_Id bigint
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    DELETE FROM 
        Buyshop_Pending_Items 
    WHERE
        Buyshop_Pending_Items_Id = @Buyshop_Pending_Items_Id
END
GO

IF (OBJECT_ID('spDeletePendingBuyshopItems', 'P')) IS NOT NULL
BEGIN
DROP PROCEDURE spDeletePendingBuyshopItems
END
GO
CREATE PROCEDURE spDeletePendingBuyshopItems 
    -- Add the parameters for the stored procedure here
    @Buyshop_Transaction_Id bigint
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    DELETE FROM 
        Buyshop_Pending_Items 
    WHERE
        Buyshop_Transaction_Id = @Buyshop_Transaction_Id
END
GO

/*Create new pending buy items*/
CREATE TABLE [dbo].[Buyshop_Pending_Items](
    [Buyshop_Pending_Items_Id] [bigint] IDENTITY(1,1) NOT NULL,
    [Buyshop_Transaction_Id] [bigint] NOT NULL,
    [CategoryId] [bigint] NOT NULL,
    [AssociationId] [bigint] NULL,
    [ProductId] [bigint] NULL,
    [ItemConditionId] [bigint] NOT NULL,
    [FinalBuyingPrice] [decimal](10, 2) NOT NULL,
    [RRP] [decimal](10, 2) NOT NULL,
    [Qty] [int] NOT NULL,
    [CategoryText] [varchar] (200) NOT NULL,
    [MakeText] [varchar] (200) NOT NULL,
    [ModelText] [varchar] (200) NOT NULL
 CONSTRAINT [PK_Buyshop_Pending_Items] PRIMARY KEY CLUSTERED 
(
    [Buyshop_Pending_Items_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[LaybyParameter](
    [LaybyParameterId] [int] IDENTITY(1,1) NOT NULL,
    [FromValue] [decimal](18, 2) NOT NULL,
    [ToValue] [decimal](18, 2) NOT NULL,
    [MaxTermId] [int] NOT NULL,
    [MinDepositPercFactor] [decimal](5, 2) NOT NULL,
 CONSTRAINT [PK_LaybyParameter] PRIMARY KEY CLUSTERED 
(
    [LaybyParameterId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[LaybyParameter] ON 
GO
INSERT [dbo].[LaybyParameter] ([LaybyParameterId], [FromValue], [ToValue], [MaxTermId], [MinDepositPercFactor]) VALUES (1, CAST(500.00 AS Decimal(18, 2)), CAST(999.99 AS Decimal(18, 2)), 3, CAST(0.25 AS Decimal(5, 2)))
GO
INSERT [dbo].[LaybyParameter] ([LaybyParameterId], [FromValue], [ToValue], [MaxTermId], [MinDepositPercFactor]) VALUES (2, CAST(1000.00 AS Decimal(18, 2)), CAST(1999.99 AS Decimal(18, 2)), 3, CAST(0.25 AS Decimal(5, 2)))
GO
INSERT [dbo].[LaybyParameter] ([LaybyParameterId], [FromValue], [ToValue], [MaxTermId], [MinDepositPercFactor]) VALUES (3, CAST(2000.00 AS Decimal(18, 2)), CAST(2999.99 AS Decimal(18, 2)), 6, CAST(0.10 AS Decimal(5, 2)))
GO
INSERT [dbo].[LaybyParameter] ([LaybyParameterId], [FromValue], [ToValue], [MaxTermId], [MinDepositPercFactor]) VALUES (4, CAST(3000.00 AS Decimal(18, 2)), CAST(3999.99 AS Decimal(18, 2)), 6, CAST(0.10 AS Decimal(5, 2)))
GO
INSERT [dbo].[LaybyParameter] ([LaybyParameterId], [FromValue], [ToValue], [MaxTermId], [MinDepositPercFactor]) VALUES (5, CAST(4000.00 AS Decimal(18, 2)), CAST(4999.99 AS Decimal(18, 2)), 6, CAST(0.10 AS Decimal(5, 2)))
GO
INSERT [dbo].[LaybyParameter] ([LaybyParameterId], [FromValue], [ToValue], [MaxTermId], [MinDepositPercFactor]) VALUES (6, CAST(5000.00 AS Decimal(18, 2)), CAST(5999.99 AS Decimal(18, 2)), 6, CAST(0.10 AS Decimal(5, 2)))
GO
INSERT [dbo].[LaybyParameter] ([LaybyParameterId], [FromValue], [ToValue], [MaxTermId], [MinDepositPercFactor]) VALUES (7, CAST(6000.00 AS Decimal(18, 2)), CAST(6999.99 AS Decimal(18, 2)), 6, CAST(0.10 AS Decimal(5, 2)))
GO
INSERT [dbo].[LaybyParameter] ([LaybyParameterId], [FromValue], [ToValue], [MaxTermId], [MinDepositPercFactor]) VALUES (8, CAST(7000.00 AS Decimal(18, 2)), CAST(7999.99 AS Decimal(18, 2)), 6, CAST(0.10 AS Decimal(5, 2)))
GO
INSERT [dbo].[LaybyParameter] ([LaybyParameterId], [FromValue], [ToValue], [MaxTermId], [MinDepositPercFactor]) VALUES (10, CAST(8000.00 AS Decimal(18, 2)), CAST(8999.99 AS Decimal(18, 2)), 6, CAST(0.10 AS Decimal(5, 2)))
GO
INSERT [dbo].[LaybyParameter] ([LaybyParameterId], [FromValue], [ToValue], [MaxTermId], [MinDepositPercFactor]) VALUES (11, CAST(9000.00 AS Decimal(18, 2)), CAST(9999.99 AS Decimal(18, 2)), 6, CAST(0.10 AS Decimal(5, 2)))
GO
INSERT [dbo].[LaybyParameter] ([LaybyParameterId], [FromValue], [ToValue], [MaxTermId], [MinDepositPercFactor]) VALUES (12, CAST(10000.00 AS Decimal(18, 2)), CAST(999999999999.00 AS Decimal(18, 2)), 6, CAST(0.10 AS Decimal(5, 2)))
GO
SET IDENTITY_INSERT [dbo].[LaybyParameter] OFF
GO
ALTER TABLE [dbo].[LaybyParameter]  WITH CHECK ADD  CONSTRAINT [FK_LaybyParameter_LaybyTerm] FOREIGN KEY([MaxTermId])
REFERENCES [dbo].[LaybyTerm] ([LaybyTermId])
GO
ALTER TABLE [dbo].[LaybyParameter] CHECK CONSTRAINT [FK_LaybyParameter_LaybyTerm]
GO



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

CREATE PROCEDURE [dbo].[GetLaybyParameters] 

AS
BEGIN

    SET NOCOUNT ON;

    SELECT 
        LaybyParameterId, 
        FromValue, 
        ToValue, 
        MaxTermId, 
        MinDepositPercFactor
    FROM LaybyParameter
    ORDER BY FromValue

END
GO

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

/****** Object:  StoredProcedure [dbo].[GetCBucksSalesItems]    Script Date: 01/02/2024 10:24:44 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
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,
ISNULL(srl.RRP, 0) AS CcwRrp
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

    /* SWIFT-2484 (Return RRP value from stock receipt line) */
    INNER JOIN Stock_Receipt_Line srl ON srl.Stock_Item_ID = si.Stock_Item_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

update System_Default set Default_Value = '2.0.3.4' where Default_Name = 'CurrentVersionNumber' and Default_Value = '2.0.3.3'
GO


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

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

GO