SqlReports

diff --git a/RevenueComparationByCategories.sql b/RevenueComparationByCategories.sql
new file mode 100644
index 0000000..102cd1f
--- /dev/null
+++ b/RevenueComparationByCategories.sql
@@ -0,0 +1,267 @@
+
+set noexec off
+go
+if object_id('RevenueComparationByCategories') is not null
+begin
+	set noexec on
+end	
+go
+-- ������� ������ ������
+-- ���������� ������� �������� ���������
+create procedure dbo.RevenueComparationByCategories as
+raiserror ('������ ������. ������ ��� ��������� �������', -- Message text.
+				16, -- Severity.
+				1 -- State.
+			);
+go
+set noexec off
+go
+alter procedure dbo.RevenueComparationByCategories
+(
+	@CompanyID int,
+	@PeriodTypeID int,
+	@PeriodID int,
+	@ShopID int = null
+) 
+as
+
+begin
+
+--declare @CompanyID int
+--declare @PeriodTypeID int
+--declare @PeriodID int
+--declare @ShopID 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,
+			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
+		
+		where 
+				isnull(@ShopID, 0) = 0
+			or	t.ShopId = @ShopID
+
+
+
+	declare @SalesTotals table (ShopID int, CategoryID int, LastPeriodSumm decimal(15, 2), ActualPeriodSumm decimal(15, 2), SalesLastPeriodCount int, SalesActualPeriodCount int)
+
+	insert into @SalesTotals
+		select
+			tl.ShopID,
+			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 tl.ShopID, p.CategoryId
+
+
+	declare @WithdrawsLoadingsTotals table (ShopID int, CategoryID int, WithdrawsLastPeriodCount int, WithdrawsActualPeriodCount int, LoadingsLastPeriodCount int, LoadingsActualPeriodCount int)
+
+	insert into @WithdrawsLoadingsTotals
+		select
+			tl.ShopID,
+			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 tl.ShopID, p.CategoryId
+
+	
+	select
+		gtl.ShopID,
+		isnull(s.Description, s.Name) as ShopName,
+		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 (
+			select 
+				tl.ShopID
+			from @TerminalsList tl
+
+			group by tl.ShopID
+		) as gtl
+		
+			inner join dbo.ProductCategories pc with (nolock) on 1 = 1
+			inner join dbo.Shops s with (nolock) on s.Id = gtl.ShopID
+			
+			left join @SalesTotals st on 
+					st.ShopID = gtl.ShopID
+				and	st.CategoryID = pc.Id  
+			
+			left join @WithdrawsLoadingsTotals wlt on 
+					wlt.ShopID = gtl.ShopID
+				and wlt.CategoryID = pc.ID
+
+	order by gtl.ShopID, pc.Id
+			
+
+end
\ No newline at end of file