-- Sales Summary
Declare @ForecastDate datetime = '2018-12-12'
Declare @EntityId bigint = 1287


-- Check if the store is closed for the forecast date.
IF Exists(Select * from tbEntityTime Where EntityID = @EntityId And DayOfWeek = DATEPART(dw, 

@ForecastDate) and IsClosed = 1)
	OR exists(Select * from tbEntityTimeException Where EntityID = @EntityId And CalendarDay 

= @ForecastDate and IsClosed = 1)
begin
	PRINT 'The store is closed on ' + Convert(varchar(100), @ForecastDate)
	return
end




declare @Weeks int
declare @LastYearWeighting float
declare @RemoveHiLo bit
Select @Weeks = Weeks, @LastYearWeighting = LastYearWeighting, @RemoveHiLo = RemoveHighLow from 

tbForecastingRule r JOIN
tbForecastingRuleEntity e on r.ForecastingRuleID = e.ForecastingRuleID
where e.EntityID = @EntityId

PRINT Convert(varchar(10), @Weeks) + ' weeks, ' + Convert(Varchar(10), @LastYearWeighting) + ' 

last year weighting, ' + Convert(Varchar(10), @RemoveHiLo) + ' remove high/low'


declare @dayTable Table ( businessDate datetime, totalSales money)
declare @curDate datetime = @ForecastDate
declare @skippedDays int = 0
declare @idx int = 0

Declare @CurrentBusinessDay datetime
Declare @CurrentDayOpen DateTime


Declare @CurrentStoreTime datetime = dbo.fn_localTime(@EntityId, GETUTCDate(), GetDAte())

select @CurrentBusinessDay = CalendarDay, @CurrentDayOpen = DayOpen From tbMXDay where EntityID = 

@EntityId and DayOpen < @CurrentStoreTime and DayClose > @CurrentStoreTime
Print 'Current Business day is ' + Convert(varchar(100), @CurrentBusinessDay)

if @CurrentBusinessDay IS NULL
begin
	PRINT 'Trading day for today does not exist'
	return 
end

while @idx < @Weeks
begin
	set @curDate = DateAdd(d, -7, @curDate)
	print 'CurrentDate: ' +  Cast(@curDate as varchar(12))
	print 'Skipped Days: ' + Cast(@skippedDays as varchar(5))
	
	

	--if @skippedDays >= 2 OR 
	
	if (not Exists(Select * from tbEntityTime Where EntityID = @EntityId And DayOfWeek = 

DATEPART(dw, @curDate) and IsClosed = 1)
	and not exists(Select * from tbEntityTimeException Where EntityID = @EntityId And 

CalendarDay = @curDate and IsClosed = 1))
	begin
		IF not (@curDate = @CurrentBusinessDay and DATEDIFF(hh, @CurrentDayOpen, 

@CurrentStoreTime) < 24)
		BEGIN
			Declare @TotalSalesForDay as money
			select @TotalSalesForDay = SUM(Sales_Excl_GST_Excl) from 

tbTransactionSummary 
			where EntityID = @EntityId 
			and BusinessDay = @curDate
			AND TransactionDate Between DateAdd(HOUR, DATEPART(Hour, 

@CurrentDayOpen), BusinessDay) AND DateAdd(HOUR, DATEPART(Hour, @CurrentDayOpen) + 24, 

BusinessDay)
			print 'Total Sales For Day = ' + Cast(@TotalSalesForDay as varchar(15))
			IF(ISNULL(@TotalSalesForDay, 0) > 0)
			BEGIN
				insert into @dayTable (businessDate, totalSales) Values 

(@curDate, @TotalSalesForDay)
			END
			ELSE
			BEGIN
				PRINT 'No Sales Found for ' + Convert(varchar(100), @CurDate)
			END
		END
		ELSE
		BEGIN
			PRINT Convert(varchar(100), @CurDate) + ' is the current business day and 

is excluded from calculation.'
		END
		set @idx = @idx + 1

	end
	else
	begin
		IF @curDate > @CurrentBusinessDay
		BEGIN
			Print Convert(varchar(100), @CurDate) + ' is a future day and is excluded from calculation.'
			set @idx = @idx + 1
		END
		ELSE
		BEGIN
			Print Convert(varchar(100), @CurDate) + ' is a closed day and is being skipped.'
			set @skippedDays = @skippedDays + 1
			if @skippedDays > 2
				set @idx = @idx + 1
		END
	end
	
end

select * from @dayTable order by totalSales

-- Find last years weeks
DEclare @periodNum int
declare @fiscalYear varchar(4)
Declare @DayName varchar(50)
select @periodNum=iDetail_num, @DayName = DayName, @fiscalYear = Convert(int, sFiscalYear)
from tbPeriodDay Where DayDate = @ForecastDate
declare @sameDayLastYear datetime 
declare @salesSameDayLastYear money

if @periodNum = 53
begin
	Select @sameDayLastYear = DayDate from tbPeriodDay where sFiscalYear = (@fiscalYear) and 

iDetail_num = 1 and DayName = @DayName
end
else 
begin

	Declare @periodsInPreviousYear int
	Select @periodsInPreviousYear = MAX(iDetail_num) From tbPeriodDetail Where sFiscalYear = 

(@fiscalYear - 1)
	if @periodsInPreviousYear = 53
	begin
		Select @sameDayLastYear = DayDate from tbPeriodDay where sFiscalYear = 

(@fiscalYear -1) and iDetail_num = @periodNum + 1 and DayName = @DayName
	end
	else
	begin
		Select @sameDayLastYear = DayDate from tbPeriodDay where sFiscalYear = 

(@fiscalYear -1) and iDetail_num = @periodNum and DayName = @DayName
	end
end

select @salesSameDayLastYear = SUM(Sales_Excl_GST_Excl) from tbTransactionSummary 
	where EntityID = @EntityId 
	and BusinessDay = @sameDayLastYear


declare @lastYearDaysTable Table ( businessDate datetime, totalSales money)
set @curDate = @sameDayLastYear
Set @skippedDays = 0
set @idx = 0

while @idx < @Weeks
begin
	set @curDate = DateAdd(d, -7, @curDate)
	if @skippedDays >= 2 OR (not Exists(Select * from tbEntityTime Where EntityID = @EntityId 

And DayOfWeek = DATEPART(dw, @curDate) and IsClosed = 1)
	and not exists(Select * from tbEntityTimeException Where EntityID = @EntityId And 

CalendarDay = @curDate and IsClosed = 1))
	begin
		insert into @lastYearDaysTable (businessDate, totalSales) 
		select @curDate, SUM(Sales_Excl_GST_Excl) from tbTransactionSummary 
		where EntityID = @EntityId 
		and BusinessDay = @curDate
		having SUM(sales_Excl_GST_Excl) > 0
		set @idx = @idx + 1
	end
	else
	begin
		set @skippedDays = @skippedDays + 1
	end
	
end

Select 'Last Years Average Week ', businessDate, totalsales from @lastYearDaysTable

if @RemoveHiLo <> 0 
begin
	declare @high datetime
	declare @low datetime
	;WITH hilosRemoved AS (select businessDate, totalSales, ROW_NUMBER() OVER (Order by 

TotalSales desc) AS RowNumber from @dayTable Where totalSales is not null)
	Select top 1 @high = businessDate from hilosRemoved order by RowNumber

	;WITH hilosRemoved AS (select businessDate, totalSales, ROW_NUMBER() OVER (Order by 

TotalSales desc) AS RowNumber from @dayTable Where totalSales is not null)
	Select top 1 @low = businessDate from hilosRemoved order by RowNumber desc
	delete from @dayTable where businessDate in (@high, @low)
end
select 'days used in forecast' , * from @dayTable order by totalSales

declare @lastYearPrevWeeksAverage money
select @lastYearPrevWeeksAverage = Avg(totalSales) from @lastYearDaysTable

select 'Last Year Prev Weeks Average ' , @LastYearPrevWEeksAverage
union
select 'Same Day Last Year  ', @SalesSameDayLastYear
--union 
select 'same day last year ' , @SameDayLastYear
declare @seasonality float = ((@salesSameDayLastYear - @lastYearPrevWeeksAverage) / 

@lastYearPrevWeeksAverage) * @lastYearWeighting
declare @actualWeeks int 
select @actualWeeks = COUNT(*) From @dayTable
print 'Seasonality ' + convert(varchar(100), @seasonality)

SELECT 'Actual Inside Sales', BusinessDay, SUM(RawSales) OriginalRaw, 
	SUM (RawSales 
		+ (RawSales * -(ISNULL(ma.Adjustment,1)-1) / ISNULL(ma.Adjustment,1))
		+ (RawSales * -(ISNULL(aa.SalesAdjustment,1)-1) / ISNULL(aa.SalesAdjustment,1))) AdjustedRawMinusEvents
FROM (
	Select BusinessDay, 
		convert(smalldatetime,Floor(cast(TransactionDate as float) * 96)/96) AS IntervalStart, 
		SUM(ts.Sales_EXCL_GST_EXCL) AS RawSales, 
		SUM(ts.TransactionCount) AS RawTransactionCount,
		ts.InOutDel AS ServiceType
	FROM tbTransactionSummary ts WITH(nolock)
	WHERE EntityID = @EntityId AND BusinessDay IN (SELECT BusinessDate FROM @dayTable)
		--AND TransactionDate Between DateAdd(HOUR, DATEPART(Hour, @CurrentDayOpen), BusinessDay)
	Group By BusinessDay, convert(smalldatetime,Floor(cast(TransactionDate as float) * 96)/96), ts.InOutDel
) r
LEFT JOIN Forecasting.EventProfileTags e ON e.EntityId = @EntityId AND e.Date = r.BusinessDay
LEFT JOIN (Forecasting.EventAdjustments ma 
	JOIN Forecasting.EventProfile p ON p.EventProfileId = ma.EventProfileId AND p.Source = 2
) ON FORMAT(r.IntervalStart, 'HH:mm') = FORMAT(ma.IntervalStart, 'HH:mm') AND e.EventProfileId = ma.EventProfileId
LEFT JOIN (
	SELECT t.Date, FORMAT(IntervalStart, 'HH:mm') Interval, AVG(SalesAdjustment) SalesAdjustment 
	FROM Forecasting.EventProfileTags t
	JOIN Forecasting.EventProfileHistory h ON h.EventProfileId = t.EventProfileId
	JOIN Forecasting.EventAutoAdjustment a ON a.EventProfileHistoryId = h.EventProfileHistoryId
	JOIN Forecasting.EventProfile e ON e.EventProfileId = t.EventProfileId AND e.Source = 1
	WHERE t.EntityID = @EntityId AND t.Date <> h.Date
	GROUP BY t.Date, FORMAT(IntervalStart, 'HH:mm')
) aa ON aa.Date = r.BusinessDay AND aa.Interval = FORMAT(r.IntervalStart, 'HH:mm')
WHERE ServiceType NOT IN (1,2)
GROUP BY BusinessDay


declare @rawData Table (businessDay datetime, IntervalStart smalldatetime, RawSales money, 

RawTransactionCount float, ServiceType int)

INSERT INTO @rawData
    Select BusinessDay, 
        convert(smalldatetime,Floor(cast(TransactionDate as float) * 96)/96) AS IntervalStart, 
        SUM(ts.Sales_EXCL_GST_EXCL) AS RawSales, 
        SUM(ts.TransactionCount) AS RawTransactionCount,
        ts.InOutDel AS ServiceType
    FROM tbTransactionSummary ts (nolock)
    JOIN @dayTable d
    ON d.businessDate = ts.BusinessDay
    WHERE EntityID = @EntityId
	AND TransactionDate Between DateAdd(HOUR, DATEPART(Hour, @CurrentDayOpen), BusinessDay) 

AND DateAdd(HOUR, DATEPART(Hour, @CurrentDayOpen) + 24, BusinessDay)
    Group By BusinessDay, convert(smalldatetime,Floor(cast(TransactionDate as float) * 96)/96), 

ts.InOutDel
--select 'Raw data ', * from @rawData
select 'Historical Data ',  businessDay AS HistoricalBusinessDay, IntervalStart AS Interval, SUM

(RawSales) AS Sales, SUM(RawTransactionCount) AS TransactionCount, ServiceType from @rawData
Group By businessDay, IntervalStart, ServiceType order by businessDay
declare @CalculatedForecast Table (IntervalStart datetime, RawSales money, RawTransactionCount 

float, ServiceType int)

insert into @CalculatedForecast
select IntervalStart - businessDay + @ForecastDate as ForecastDateTime, (SUM(RawSales) / 

@actualWeeks) + ((SUM(RawSales) / @actualWeeks) * @seasonality) AS ForecastedSales, SUM

(RawTransactionCount) / @actualWeeks AS ForecastedTransactionCount, ServiceType from @rawData
Group By IntervalStart - businessDay + @ForecastDate, ServiceType
Order By IntervalStart - businessDay + @ForecastDate


select 'Caluclated Forecast ', * from @CalculatedForecast

If Exists (select * from forecasting.Forecast where EntityId = @EntityId and BusinessDay = 

@ForecastDate And Status = 0)
BEGIN
	DECLARE @ForecastId bigint
	Select top 1 @ForecastId = ForecastId From Forecasting.Forecast where EntityId = 

@EntityId and BusinessDay = @ForecastDate And Status = 0 order by GenerationDate desc
	Select 'MxConnect forecast ', ForecastId, IntervalStart, SUM(RawSales) as RawSales, SUM

(RawTransactionCount) as RawTransactionCount, SUM(SystemSales) as SystemSales, SUM

(SystemTransactionCount) as SystemTransactionCount, SUM(ManagerSales) as ManagerSales, SUM

(ManagerTransactionCount) as ManagerTransactionCount, SUM(LastYearSales) as LastYearSales, SUM

(LastYearTransactionCount) as LastYearTransactionCount, ServiceType from forecasting.MetricDetail 

where ForecastId = @ForecastId group by ForecastId, IntervalStart, ServiceType order by 

IntervalStart
	Select 'Difference', * from 
	(select sum(rawsales) as RawSales, IntervalStart, ServiceType from 

forecasting.MetricDetail where ForecastId = @ForecastId group by intervalStart, ServiceType) m 

JOIN @CalculatedForecast c
	ON c.IntervalStart = m.IntervalStart and c.ServiceType = m.ServiceType
	Where abs(m.RawSales -  c.RawSales) > .01

	Union Select 'Difference',RawSales, IntervalStart, ServiceType, null, null, null, null 

from forecasting.MetricDetail m 
	Where ForecastId = @ForecastId
	and not exists (
		select * from @CalculatedForecast where IntervalStart = m.IntervalStart and 

ServiceType = m.servicetype
	)
	Union Select 'Difference', null, null, null , * from @CalculatedForecast c 
	Where not exists (
		select * from forecasting.MetricDetail where forecastId = @forecastid  and 

IntervalStart = c.IntervalStart and ServiceType = c.servicetype
	)

END

