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

if @@ROWCOUNT!=0
	BEGIN
		RAISERROR('THIS SCRIPT HAS ALREADY BEEN RUN ON THIS SERVER, CANNOT CONTINUE, PLEASE CONTACT DEV',11,1);
		RETURN;
	END
GO
-- add column to buyerstat table
IF COL_LENGTH('buyerstat', 'RepairStockValue') IS NULL
BEGIN
	alter table buyerstat
	add RepairStockValue decimal(18,2) default 0;
END
GO

IF OBJECT_ID('PopulateBuyerStats', 'P') IS NOT NULL
BEGIN
	DROP PROCEDURE PopulateBuyerStats
END
GO

CREATE PROCEDURE [dbo].[PopulateBuyerStats] @TradingDayId bigint
AS

delete from BuyerStat where TradingDayId=@TradingDayId;
declare @TradingDate datetime;
set @TradingDate=(select trading_Date from Trading_Day where Trading_Day_ID=@TradingDayId);

IF OBJECT_ID('tempdb..#SSBBook') IS NOT NULL DROP TABLE #SSBBook;
IF OBJECT_ID('tempdb..#SSBIncome') IS NOT NULL DROP TABLE #SSBIncome;
IF OBJECT_ID('tempdb..#SecondHandRetailSOH') IS NOT NULL DROP TABLE #SecondHandRetailSOH;
IF OBJECT_ID('tempdb..#SecGPMTD') IS NOT NULL DROP TABLE #SecGPMTD;
IF OBJECT_ID('tempdb..#OutRightCSBMix') IS NOT NULL DROP TABLE #OutRightCSBMix;
IF OBJECT_ID('tempdb..#Last30GP') IS NOT NULL DROP TABLE #Last30GP;
IF OBJECT_ID('tempdb..#SalesForLast30') IS NOT NULL DROP TABLE #SalesForLast30;
IF OBJECT_ID('tempdb..#StockAge') IS NOT NULL DROP TABLE #StockAge;
IF OBJECT_ID('tempdb..#RepairCost') IS NOT NULL DROP TABLE #RepairCost;


select 
  System_User_ID, 
  a.SSbStockHolding into #SSBBook from (
select 
  su.System_User_ID, 
  sum(si.Unit_Cost * sic.Stock_Count) as SSbStockHolding 
from 
  Stock_Item_Count sic 
  left join Stock_Item si on si.Stock_Item_ID = sic.Stock_Item_ID 
  left join Buyshop_Trans_Line btl on btl.Buyshop_Trans_Line_ID = si.Buyshop_Trans_Line_ID 
  left join Buyshop_Transaction bt on bt.Buyshop_Transaction_ID = btl.Buyshop_Transaction_ID 
  left join [System_User] su on su.System_User_ID = bt.System_User_ID 
where 
  si.Item_Stock_Type_ID = 3 
  and sic.Stock_Holding_Group_ID = 1 
  and sic.Stock_Count > 0 
group by 
  su.System_User_ID
) a;


select 
  System_User_ID, 
  sum(PaymentsReceived) as PaymentsReceived into #SSBIncome from (
select 
  (
    select 
      top 1 su.System_User_ID 
    from 
      SSB_Item si 
      left join Stock_Item ssi on ssi.Stock_Item_ID = si.Stock_Item_ID 
      left join Buyshop_Trans_Line btl on btl.Buyshop_Trans_Line_ID = ssi.Buyshop_Trans_Line_ID 
      left join Buyshop_Transaction bt on bt.Buyshop_Transaction_ID = btl.Buyshop_Transaction_ID 
      left join [System_User] su on su.System_User_ID = bt.System_User_ID 
    where 
      si.SSB_ID = sp.SSB_ID
  ) as System_User_ID, 
  case when SSB_Payment_Type_ID = 4 then (Amount_Paid + sp.vat) else Amount_Paid end as PaymentsReceived 
from 
  SSB_Payment sp 
  left join ssb s on s.SSB_ID = sp.SSB_ID 
where 
  sp.Cashier_Session_ID in (
    select 
      Cashier_Session_ID 
    from 
      Cashier_Session c 
      left join Trading_Day t on t.Trading_Day_ID = c.Trading_Day_ID 
    where 
      Trading_Date >= @TradingDate - DAY(@TradingDate)+ 1 
      and trading_date <= @TradingDate
  ) 
  and SSB_Payment_Type_ID != 1
) a 
group by 
  a.System_User_ID;



select 
  su.System_User_ID, 
  sum(v.Unit_Cost * v.quantity) as TotalSOH into #SecondHandRetailSOH from vw_Stock_Age v
	  left join Stock_Item si on si.Stock_Item_ID = v.Stock_Item_ID 
	  left join Buyshop_Trans_Line btl on btl.Buyshop_Trans_Line_ID = si.Buyshop_Trans_Line_ID 
	  left join Buyshop_Transaction bt on bt.Buyshop_Transaction_ID = btl.Buyshop_Transaction_ID 
	  left join [System_User] su on su.System_User_ID = bt.System_User_ID 
group by 
  su.System_User_ID, 
  (su.First_Name + ' ' + su.Surname);



select 
  su.System_User_ID, 
  SUM(
	(
      CASE WHEN ((v.ExclTotal -(v.Quantity * v.unit_Cost)) < 0 AND SaleType != 'Refund') 
	  THEN 0 
	  ELSE (v.ExclTotal -(v.Quantity * v.unit_Cost)) END
    )
  ) AS SecGP into #SecGPMTD from vwSales_Report v
  left join Stock_Item si on si.Stock_Item_ID = v.stock_item_id 
  left join Buyshop_Trans_Line btl on btl.Buyshop_Trans_Line_ID = si.Buyshop_Trans_Line_ID 
  left join Buyshop_Transaction bt on bt.Buyshop_Transaction_ID = btl.Buyshop_Transaction_ID 
  left join [System_User] su on su.System_User_ID = bt.System_User_ID 
where 
  v.Item_Stock_Type_ID in (1, 2, 3, 5, 7) 
  and Trading_Date >= @TradingDate - DAY(@TradingDate)+ 1 
  and trading_date <= @TradingDate 
group by 
  su.System_User_ID;



select 
  a.system_user_id, 
  (OutRightSales / (case when TotalSales = 0 then 1 else TotalSales end)) * 100 as OutRightMixLast30Days, 
  (CSBSales / (case when TotalSales = 0 then 1 else TotalSales end)) * 100 as CSBMixLast30Days into #OutRightCSBMix 
  from (
select 
  su.System_User_ID, 
  sum(exclTotal) as TotalSales, 
  sum(case when v.item_stock_type_id in (1, 3, 2, 5) then (ExclTotal) else 0 end) as OutRightSales, 
  sum(case when v.item_stock_type_id in (7) then (ExclTotal) else 0 end) as CSBSales 
from 
  vwSales_Report v 
  left join Stock_Item si on si.Stock_Item_ID = v.stock_item_id 
  left join Buyshop_Trans_Line btl on btl.Buyshop_Trans_Line_ID = si.Buyshop_Trans_Line_ID 
  left join Buyshop_Transaction bt on bt.Buyshop_Transaction_ID = btl.Buyshop_Transaction_ID 
  left join [System_User] su on su.System_User_ID = bt.System_User_ID 
where 
  Trading_Date between dateadd(day,-30, @TradingDate) 
  and @TradingDate 
  and v.Item_Stock_Type_ID in (1, 2, 3, 7, 5) 
group by 
  su.System_User_ID
) a;



select 
  a.system_user_id, 
  cast(((SaleValue - CostValue) / (case when SaleValue = 0 then 1 else SaleValue end)) * 100 as decimal(10, 2)) as Last30GpPerc into #Last30GP 
  from (
SELECT 
  su.System_User_ID, 
  isnull(SUM(v.Quantity * v.unit_Cost), 0) AS CostValue, 
  SUM(isnull(v.ExclTotal, 0)) SaleValue 
FROM 
  vwSales_Report v 
  LEFT JOIN Stock_Item si ON si.Stock_Item_ID = v.stock_item_id 
  LEFT JOIN Buyshop_Trans_Line btl ON btl.Buyshop_Trans_Line_ID = si.Buyshop_Trans_Line_ID 
  LEFT JOIN Buyshop_Transaction bt ON bt.Buyshop_Transaction_ID = btl.Buyshop_Transaction_ID 
  LEFT JOIN [SYSTEM_USER] su ON su.System_User_ID = bt.System_User_ID 
WHERE 
  v.Item_Stock_Type_ID IN (1, 2, 3, 5, 7) 
  AND Trading_Date between dateadd(day,-30, @TradingDate) 
  and @TradingDate 
GROUP BY 
  su.System_User_ID
) a;



SELECT 
  su.System_User_ID, 
  isnull(SUM(v.Quantity * v.unit_Cost), 0) AS SalesCost into #SalesForLast30 
  FROM vwSales_Report v
	  LEFT JOIN Stock_Item si ON si.Stock_Item_ID = v.stock_item_id 
	  LEFT JOIN Buyshop_Trans_Line btl ON btl.Buyshop_Trans_Line_ID = si.Buyshop_Trans_Line_ID 
	  LEFT JOIN Buyshop_Transaction bt ON bt.Buyshop_Transaction_ID = btl.Buyshop_Transaction_ID 
	  LEFT JOIN [SYSTEM_USER] su ON su.System_User_ID = bt.System_User_ID 
WHERE 
  v.Item_Stock_Type_ID IN (1, 2, 3, 5, 7) 
  AND Trading_Date between dateadd(day,-30, @TradingDate) 
  and @TradingDate 
GROUP BY 
  su.System_User_ID;


/*
==============================
	Buyer Repair Cost
==============================
*/
select
bt.System_User_ID,
sum(si.Unit_Cost) AS RepairCost into #RepairCost
from 
  Stock_Item si 
	  inner join Stock_Item_Count sic on si.Stock_Item_ID = sic.Stock_Item_ID 
	  inner join Buyshop_Trans_Line btl on btl.Buyshop_Trans_Line_ID = si.Buyshop_Trans_Line_ID 
	  inner join Buyshop_Transaction bt on bt.Buyshop_Transaction_ID = btl.Buyshop_Transaction_ID
where 
  sic.Stock_Holding_Group_ID = 3 
  and sic.Stock_Count > 0
group by
  bt.System_User_ID;


declare @Category Table (Category varchar(50) primary key) 
declare @IsFurniture varchar(10);
set @IsFurniture = (select ISNULL(Default_Value, '0') from System_Default where Default_Name = 'Furniture_Component');

if @IsFurniture = '0' 
begin 
	insert into @Category(Category) values ('Gold') 
end 
else 
begin 
	insert into @Category(Category) values ('Gold'), ('Furniture') 
end


select 
  su.System_User_ID, 
  sum(a.StockValue) as StockAgeValue into #StockAge  from  (
select 
  Stock_Item_ID, 
  v.Unit_Cost * v.quantity as StockValue, 
  (case when Last_Stock_Age_Change is null then Original_Purchase_Date else Last_Stock_Age_Change end) as StockAgeDate 
from 
  vw_Stock_Age v 
where 
  Division not in (select Category from @Category) and Category != 'Silver Jewellery'
) a 
left join Stock_Item si on si.Stock_Item_ID = a.Stock_Item_ID 
left join Buyshop_Trans_Line btl on btl.Buyshop_Trans_Line_ID = si.Buyshop_Trans_Line_ID 
left join Buyshop_Transaction bt on bt.Buyshop_Transaction_ID = btl.Buyshop_Transaction_ID 
left join [System_User] su on su.System_User_ID = bt.System_User_ID 
where 
  a.StockAgeDate <= DATEADD(DAY,-90, @TradingDate) 
group by 
  su.System_User_ID, 
  (su.First_Name + ' ' + su.Surname);


insert into buyerstat(
  SystemUserId, 
  TradingDayId, 
  SSBBookValue, 
  SSBIncomeMTD, 
  SecondHandRetailSOH, 
  SecGPMTD, 
  OutrightMixPercLast30, 
  CSBMixPercLast30, 
  Last30GpPerc, 
  SalesCostLast30, 
  StockAgeValue, 
  StockAgePerc, 
  RepairStockValue
) 
select 
  System_User_ID, 
  TradingDayId, 
  SSBBookValue, 
  SSBIncomeMTD, 
  SecondHandRetailSOH, 
  SecGPMTD, 
  OutrightMixPercLast30, 
  CSBMixPercLast30, 
  Last30GpPerc, 
  SalesCostLast30, 
  StockAgeValue, 
  StockAgePerc,
  RepairStockValue

from 
  (select distinct System_User_ID, @TradingDayId as TradingDayId, 
  -- SSB Book Value
  isnull((select SSbStockHolding from #SSBBook where #SSBBook.System_User_ID=cs.System_User_ID),0) as SSBBookValue,
  -- SSB Income MTD
  isnull((select PaymentsReceived from #SSBIncome where #SSBIncome.System_User_ID=cs.System_User_ID),0) as SSBIncomeMTD,
  -- Second Hand Retail SOH
  isnull((select TotalSOH from #SecondHandRetailSOH where #SecondHandRetailSOH.System_User_ID=cs.System_User_ID),0) as SecondHandRetailSOH,
  -- Second GP MTD
  isnull((select SecGP from #SecGPMTD where #SecGPMTD.System_User_ID=cs.System_User_ID),0) as SecGPMTD,
  -- Outright Mix Percentage Last 30 Days
  cast(isnull((select OutRightMixLast30Days from #OutRightCSBMix where #OutRightCSBMix.System_User_ID=cs.System_User_ID),0) as decimal(10,2)) as OutrightMixPercLast30,
  -- CSB Mix Percentage Last 30 Days
  cast (isnull((select CSBMixLast30Days from #OutRightCSBMix where #OutRightCSBMix.System_User_ID=cs.System_User_ID),0) as decimal(10,2)) as CSBMixPercLast30,
  -- Last 30 Days GP Percentage
  isnull((select Last30GpPerc from #Last30GP where #Last30GP.System_User_ID=cs.System_User_ID),0) as Last30GpPerc,
  -- Sales Cost Last 30 Days
  isnull((select SalesCost from #SalesForLast30 where #SalesForLast30.System_User_ID=cs.System_User_ID),0) as SalesCostLast30, 
  -- Stock Age Value
  isnull((select StockAgeValue from #StockAge where #StockAge.System_User_ID=cs.System_User_ID),0) as StockAgeValue,
  -- Stock Age Percentage
  cast((isnull((select StockAgeValue from #StockAge where #StockAge.System_User_ID=cs.System_User_ID),0) / 
		isnull((select TotalSOH from #SecondHandRetailSOH where #SecondHandRetailSOH.System_User_ID=cs.System_User_ID),1)) * 100 as decimal(10,2)) as StockAgePerc,
  -- Repair Stock Cost
  isnull((select RepairCost from #RepairCost where #RepairCost.System_User_ID = cs.System_User_ID), 0) AS RepairStockValue

from Cashier_Session cs where Session_Type_ID = 3 and trading_Day_id in 
	(select Trading_Day_Id from Trading_Day where Trading_Date > DATEADD(YEAR, -5, (select trading_date from trading_day where trading_day_id = @TradingDayId)))
) a 

where 
a.SSBBookValue + 
a.SSBIncomeMTD + 
SecondHandRetailSOH + 
SecGPMTD + 
OutrightMixPercLast30 + 
CSBMixPercLast30 + 
Last30GpPerc + 
SalesCostLast30 + 
stockagevalue + 
RepairStockValue > 0;
GO
DROP VIEW [dbo].[vwGetConsumables]
GO
CREATE VIEW [dbo].[vwGetConsumables]
AS
SELECT 
p.Product_ID, 
v.Division, 
v.Category, 
p.Product_Code, 
p.Description, 
h.Current_Cost, 
h.Current_Stock, 
0 as OrderQuantity
FROM dbo.Product AS p 
LEFT OUTER JOIN dbo.vwProductCategory AS v ON p.Product_Category_ID = v.Product_Category_ID 
LEFT OUTER JOIN dbo.HQ_Product_Stock AS h ON h.Product_ID = p.Product_ID
WHERE v.Division = 'Expenses' and h.HQ_Branch_ID in (select top(1) CAST(Default_Value AS int) from System_Default where Default_Name = 'HQ_Store_Branch')
GO
Print 'Insert into script history if everything else is cool';

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