SqlReports

diff --git a/GetPeriodList_For_RevenueComparation8W.sql b/GetPeriodList_For_RevenueComparation8W.sql
new file mode 100644
index 0000000..bd46c51
--- /dev/null
+++ b/GetPeriodList_For_RevenueComparation8W.sql
@@ -0,0 +1,73 @@
+set noexec off
+go
+if object_id('GetPeriodList_For_RevenueComparation8W') is not null
+begin
+	set noexec on
+end	
+go
+--������� ������ ������
+--���������� ������� �������� ���������
+create procedure dbo.GetPeriodList_For_RevenueComparation8W as
+raiserror ('������ ������. ������ ��� ��������� �������', -- Message text.
+				16, -- Severity.
+				1 -- State.
+			);
+go
+set noexec off
+go
+alter procedure dbo.GetPeriodList_For_RevenueComparation8W
+as 
+
+begin
+	
+	set datefirst 1;
+
+	declare @PeriodTable table 
+	(
+		ID int identity (1, 1), 
+		ActualPeriodDateFrom DateTime,
+		ActualPeriodDateTo DateTime
+	)
+	
+	declare @PeriodCount as Int
+	declare @ActualPeriodDateFrom as DateTime
+	declare @ActualPeriodDateTo as DateTime
+	
+	set @ActualPeriodDateTo = GetDate()
+	set @PeriodCount = 52
+
+	set @ActualPeriodDateFrom = Cast(
+			DateAdd(day, 1 - DatePart(dw, @ActualPeriodDateTo), @ActualPeriodDateTo) as Date
+		)
+	
+	set @ActualPeriodDateTo = DateAdd(ms, -2, DateAdd(day, 7, @ActualPeriodDateFrom))
+	
+	while @PeriodCount > 0
+	begin
+	
+		insert into @PeriodTable
+			(ActualPeriodDateFrom, ActualPeriodDateTo)
+				values(@ActualPeriodDateFrom, @ActualPeriodDateTo)		
+		
+		set @ActualPeriodDateTo = DateAdd(ms, -2 , @ActualPeriodDateFrom)
+		set @ActualPeriodDateFrom = DateAdd(day, -7 , @ActualPeriodDateFrom)
+		
+		set @PeriodCount = @PeriodCount - 1
+	end
+
+	select 
+		pt.ID,
+		pt.ActualPeriodDateFrom,
+		pt.ActualPeriodDateTo,
+
+		case
+			when ID = 1 then N'������� ������'
+			when ID = 2 then N'������� ������'
+			else Convert(nvarchar(16), pt.ActualPeriodDateFrom, 104) + ' - ' + Convert(nvarchar(16), pt.ActualPeriodDateTo, 104)
+		end as PeiodDescription
+
+	from @PeriodTable pt
+	
+	order by pt.ID 
+
+end
\ No newline at end of file

diff --git a/RevenueComparation8Weeks.sql b/RevenueComparation8Weeks.sql
new file mode 100644
index 0000000..ef643a7
--- /dev/null
+++ b/RevenueComparation8Weeks.sql
@@ -0,0 +1,192 @@
+
+set noexec off
+go
+if object_id('RevenueComparation8Weeks') is not null
+begin
+	set noexec on
+end	
+go
+-- ������� ������ ������
+-- ���������� ������� �������� ���������
+create procedure dbo.RevenueComparation8Weeks as
+raiserror ('������ ������. ������ ��� ��������� �������', -- Message text.
+				16, -- Severity.
+				1 -- State.
+			);
+go
+set noexec off
+go
+alter procedure dbo.RevenueComparation8Weeks
+(
+	@ShopID int,
+	@PeriodID int
+) 
+as 
+	
+begin
+
+--declare @ShopID int
+--declare @PeriodID int	
+
+--set @ShopID = 6
+--set @PeriodID = 1
+	
+	declare @DateFrom DateTime
+	declare @ActualPeriodDateFrom DateTime
+	declare @ActualPeriodDateTo DateTime
+	declare @Counter int
+	declare @DayOfWeek nvarchar(16)
+	declare @DateCounter DateTime
+
+	declare @PeriodTable table 
+	(
+		ID int, 
+		ActualPeriodDateFrom DateTime,
+		ActualPeriodDateTo DateTime,
+		PeriodDescription nvarchar(32)
+	)
+	
+	insert into @PeriodTable
+			exec dbo.GetPeriodList_For_RevenueComparation8W
+
+	select top 1
+		@ActualPeriodDateFrom = pt.ActualPeriodDateFrom,
+		@ActualPeriodDateTo = pt.ActualPeriodDateTo
+	
+	from @PeriodTable pt
+	where pt.ID = @PeriodID
+
+	declare @Matrix table (ID int, DayOfWeek nvarchar(16), week1 DateTime, week2 DateTime, week3 DateTime, week4 DateTime, week5 DateTime, week6 DateTime, week7 DateTime, week8 DateTime)
+	
+	
+	set @Counter = 1
+	set @DateCounter = @ActualPeriodDateFrom
+
+	while @Counter <= 7
+	begin
+		
+		while 1 = 1
+		begin
+			if @Counter = 1
+			begin
+				set @DayOfWeek = N'�����������'	
+				break
+			end
+			
+			if @Counter = 2
+			begin
+				set @DayOfWeek = N'�������'	
+				break
+			end
+			
+			if @Counter = 3
+			begin
+				set @DayOfWeek = N'�����'	
+				break
+			end
+			
+			if @Counter = 4
+			begin
+				set @DayOfWeek = N'�������'	
+				break
+			end
+			if @Counter = 5
+			begin
+				set @DayOfWeek = N'�������'	
+				break
+			end
+			
+			if @Counter = 6
+			begin
+				set @DayOfWeek = N'�������'	
+				break
+			end
+			
+			if @Counter = 7
+			begin
+				set @DayOfWeek = N'�����������'	
+				break
+			end
+			
+			break
+		end
+		
+		insert into @Matrix
+			(ID, DayOfWeek, week1, week2, week3, week4, week5, week6, week7, week8)
+				values
+				(
+					@Counter, 
+					@DayOfWeek,
+					DateAdd(week, -7,@DateCounter),
+					DateAdd(week, -6,@DateCounter),
+					DateAdd(week, -5,@DateCounter),
+					DateAdd(week, -4,@DateCounter),
+					DateAdd(week, -3,@DateCounter),
+					DateAdd(week, -2,@DateCounter),
+					DateAdd(week, -1,@DateCounter),
+					@DateCounter
+				)
+				 
+		set @Counter = @Counter + 1
+		set @DateCounter = DateAdd(day, 1, @DateCounter)
+	end
+	
+	select top 1
+		
+		@DateFrom = mx.week1
+	
+	from @Matrix mx	
+	where mx.ID = 1
+
+	declare @TerminalsList table (TerminalID nvarchar(256))
+
+	insert into @TerminalsList
+		select
+			t.Id as TerminalID
+	
+		from dbo.Terminals t with (nolock)
+		where t.ShopId = @ShopID
+	
+	
+	declare @DaysSales table (WeekDayDate DateTime, Summ decimal(15, 2))
+
+	insert into @DaysSales		
+		select
+			Cast(ss.CloseTime as Date) as WeekDayDate,
+			Sum(Cast(sl.Value/100 as decimal(15, 2))) as Summ
+
+		from @TerminalsList tl 
+			inner join dbo.Sessions ss with (nolock) on
+					ss.TerminalId = tl.TerminalID
+				and ss.CloseTime between @DateFrom and @ActualPeriodDateTo 
+				and (ss.Flags & 2) = 0 -- �������� ��������� ������
+			
+			inner join dbo.Sales sl with (nolock) on
+					sl.TerminalId = tl.TerminalID
+				and sl.SessionId = ss.SessionId
+		
+		group by
+			Cast(ss.CloseTime as Date)
+
+	select
+		mx.DayOfWeek,
+		isnull(w1_sales.Summ, 0) as w1_Summ,
+		isnull(w2_sales.Summ, 0) as w2_Summ,
+		isnull(w3_sales.Summ, 0) as w3_Summ,
+		isnull(w4_sales.Summ, 0) as w4_Summ,
+		isnull(w5_sales.Summ, 0) as w5_Summ,
+		isnull(w6_sales.Summ, 0) as w6_Summ,
+		isnull(w7_sales.Summ, 0) as w7_Summ,
+		isnull(w8_sales.Summ, 0) as w8_Summ
+
+	from @Matrix mx
+		left join @DaysSales as w1_sales on w1_sales.WeekDayDate = mx.week1
+		left join @DaysSales as w2_sales on w2_sales.WeekDayDate = mx.week2
+		left join @DaysSales as w3_sales on w3_sales.WeekDayDate = mx.week3
+		left join @DaysSales as w4_sales on w4_sales.WeekDayDate = mx.week4
+		left join @DaysSales as w5_sales on w5_sales.WeekDayDate = mx.week5
+		left join @DaysSales as w6_sales on w6_sales.WeekDayDate = mx.week6
+		left join @DaysSales as w7_sales on w7_sales.WeekDayDate = mx.week7
+		left join @DaysSales as w8_sales on w8_sales.WeekDayDate = mx.week8
+			
+end
\ No newline at end of file

diff --git a/RevenueComparationByCategoriesTotalByCompany.sql b/RevenueComparationByCategoriesTotalByCompany.sql
new file mode 100644
index 0000000..6bdd287
--- /dev/null
+++ b/RevenueComparationByCategoriesTotalByCompany.sql
@@ -0,0 +1,243 @@
+set noexec off
+go
+if object_id('RevenueComparationByCategoriesTotalByCompany') is not null
+begin
+	set noexec on
+end	
+go
+-- ������� ������ ������
+-- ���������� ������� �������� ���������
+create procedure dbo.RevenueComparationByCategoriesTotalByCompany as
+raiserror ('������ ������. ������ ��� ��������� �������', -- Message text.
+				16, -- Severity.
+				1 -- State.
+			);
+go
+set noexec off
+go
+alter procedure dbo.RevenueComparationByCategoriesTotalByCompany
+(
+	@CompanyID int,
+	@PeriodTypeID int,
+	@PeriodID int
+) 
+as
+
+begin
+	
+--declare @CompanyID int
+--declare @PeriodTypeID int
+--declare @PeriodID int
+
+
+--set @CompanyID = 1
+--set @PeriodTypeID  = 1
+--set @PeriodID = 2
+	
+	declare @ActualPeriodDateFrom DateTime
+	declare @ActualPeriodDateTo DateTime
+	declare @LastPeriodDateFrom DateTime
+	declare @LastPeriodDateTo DateTime	
+
+	declare @PeriodTable table (
+			ID Int, 
+			ActualPeriodDateFrom DateTime,
+			ActualPeriodDateTo DateTime,
+			LastPeriodDateFrom DateTime, 
+			LastPeriodDateTo DateTime,
+			Period2DateFrom DateTime, 
+			Period2DateTo DateTime,
+			Period1DateFrom DateTime, 
+			Period1DateTo DateTime,
+			PeiodDescription nvarchar(256)
+		)
+		
+	insert into @PeriodTable
+		exec dbo.GetPeriodList_For_RevenueComparation @PeriodTypeID
+
+	select top 1
+		@ActualPeriodDateFrom = pt.ActualPeriodDateFrom,
+		@ActualPeriodDateTo = pt.ActualPeriodDateTo,
+		@LastPeriodDateFrom = pt.LastPeriodDateFrom,
+		@LastPeriodDateTo = pt.LastPeriodDateTo
+
+	from @PeriodTable pt
+	where pt.ID = @PeriodID  
+		
+	
+	declare @TerminalsList table (TerminalID nvarchar(256), ShopID int)
+
+	insert into @TerminalsList
+		select
+			t.Id as TerminalID,
+			t.ShopID as ShopID
+
+		from dbo.Terminals t with (nolock)
+			inner join dbo.Shops s with (nolock) on 
+					s.Id = t.ShopId
+				and s.CompanyId = @CompanyID
+
+
+	declare @SalesTotals table (CategoryID int, LastPeriodSumm decimal(15, 2), ActualPeriodSumm decimal(15, 2), SalesLastPeriodCount int, SalesActualPeriodCount int)
+
+	insert into @SalesTotals
+		select
+			p.CategoryId,
+			
+			Sum
+			(
+				case
+					when ss.CloseTime between @LastPeriodDateFrom and @LastPeriodDateTo
+						then Cast(sl.Value/100 as decimal(15, 2))
+
+					else 0
+				end
+			) as LastPeriodSumm,
+			
+			Sum
+			(
+				case
+					when ss.CloseTime between @ActualPeriodDateFrom and @ActualPeriodDateTo
+						then Cast(sl.Value/100 as decimal(15, 2))
+					
+					else 0
+				end
+			) as ActualPeriodSumm,
+
+
+			Sum
+			(
+				case
+					when ss.CloseTime between @LastPeriodDateFrom and @LastPeriodDateTo
+						then sl.Count
+
+					else 0
+				end
+			) as SalesLastPeriodCount,
+			
+			Sum
+			(
+				case
+					when ss.CloseTime between @ActualPeriodDateFrom and @ActualPeriodDateTo
+						then sl.Count
+					
+					else 0
+				end
+			) as SalesActualPeriodCount
+
+		from @TerminalsList tl 
+			inner join dbo.Sessions ss with (nolock) on
+					ss.TerminalId = tl.TerminalID
+				and	(
+							ss.CloseTime between @LastPeriodDateFrom and @LastPeriodDateTo 
+						or	ss.CloseTime between @ActualPeriodDateFrom and @ActualPeriodDateTo
+					)
+				and (ss.Flags & 2) = 0 -- �������� ��������� ������
+
+			inner join dbo.Sales sl with (nolock) on
+					sl.TerminalId = tl.TerminalID
+				and sl.SessionId = ss.SessionId
+
+			inner join dbo.Products p with (nolock) on
+				p.Id = sl.ProductId
+
+		group by p.CategoryId
+
+
+	declare @WithdrawsLoadingsTotals table (CategoryID int, WithdrawsLastPeriodCount int, WithdrawsActualPeriodCount int, LoadingsLastPeriodCount int, LoadingsActualPeriodCount int)
+
+	insert into @WithdrawsLoadingsTotals
+		select
+			p.CategoryId,
+
+			Sum
+			(
+				case
+					when prc.Type = 0 and pr.CreatedOn between @LastPeriodDateFrom and @LastPeriodDateTo
+						then prc.Value
+
+					else 0
+				end
+			) as WithdrawsLastPeriodCount,
+			
+			Sum
+			(
+				case
+					when prc.Type = 0 and pr.CreatedOn between @ActualPeriodDateFrom and @ActualPeriodDateTo
+						then prc.Value
+					
+					else 0
+				end
+			) as WithdrawsActualPeriodCount,
+
+			Sum
+			(
+				case
+					when prc.Type = 1 and pr.CreatedOn between @LastPeriodDateFrom and @LastPeriodDateTo
+						then prc.Value
+
+					else 0
+				end
+			) as LoadingsLastPeriodCount,
+			
+			Sum
+			(
+				case
+					when prc.Type = 1 and pr.CreatedOn between @ActualPeriodDateFrom and @ActualPeriodDateTo
+						then prc.Value
+					
+					else 0
+				end
+			) as LoadingsActualPeriodCount
+		
+		from @TerminalsList tl
+			inner join dbo.ProductReports pr with (nolock) on
+					pr.TerminalId = tl.TerminalID
+				and pr.ShopId = tl.ShopID
+				and (
+							pr.CreatedOn between @LastPeriodDateFrom and @LastPeriodDateTo 
+						or	pr.CreatedOn between @ActualPeriodDateFrom and @ActualPeriodDateTo 
+					)
+
+			inner join dbo.ProductReportCounters prc with (nolock) on
+					prc.ReportId = pr.Id
+				and	prc.Type in (0, 1)
+			
+			inner join dbo.Products p with (nolock) on
+				p.Id = prc.ProductId		
+		
+		group by  p.CategoryId
+
+	
+	select
+		isnull(pc.Name, '') as CategoryName,
+		isnull(st.LastPeriodSumm, 0) as LastPeriodSumm,
+		isnull(st.ActualPeriodSumm, 0) as ActualPeriodSumm,
+
+		isnull(st.ActualPeriodSumm, 0) - isnull(st.LastPeriodSumm, 0) as SummDifference,
+
+		case 
+			when isnull(st.LastPeriodSumm, 0) <> 0 
+				then
+					Cast(isnull(st.ActualPeriodSumm, 0)/isnull(st.LastPeriodSumm, 0) * 100  - 100 as decimal (15, 2))
+			
+			else 0
+		end as SummDifferenceRate,
+		
+		isnull(wlt.LoadingsLastPeriodCount, 0) as LoadingsLastPeriodCount,
+		isnull(wlt.LoadingsActualPeriodCount, 0) as LoadingsActualPeriodCount,
+		
+		isnull(st.SalesLastPeriodCount, 0) as SalesLastPeriodCount,
+		isnull(st.SalesActualPeriodCount, 0) as SalesActualPeriodCount,
+
+		isnull(wlt.WithdrawsLastPeriodCount, 0) as WithdrawsLastPeriodCount,
+		isnull(wlt.WithdrawsActualPeriodCount, 0) as WithdrawsActualPeriodCount
+		
+	
+	from dbo.ProductCategories pc with (nolock) 
+		left join @SalesTotals st on st.CategoryID = pc.Id  
+		left join @WithdrawsLoadingsTotals wlt on wlt.CategoryID = pc.ID
+
+	order by pc.Id
+
+end
\ No newline at end of file