SqlReports

diff --git a/TotalRevenueByCategories.sql b/TotalRevenueByCategories.sql
new file mode 100644
index 0000000..7e8194f
--- /dev/null
+++ b/TotalRevenueByCategories.sql
@@ -0,0 +1,298 @@
+
+set noexec off
+go
+if object_id('TotalRevenueByCategories') is not null
+begin
+	set noexec on
+end	
+go
+-- ������� ������ ������
+-- ���������� ������� �������� ���������
+create procedure dbo.TotalRevenueByCategories as
+raiserror ('������ ������. ������ ��� ��������� �������', -- Message text.
+				16, -- Severity.
+				1 -- State.
+			);
+go
+set noexec off
+go
+alter procedure dbo.TotalRevenueByCategories
+(
+	@PeriodTypeID int,
+	@PeriodID int	
+)
+as 
+
+begin
+
+--declare @PeriodTypeID int	
+--declare @PeriodID int
+
+--set @PeriodTypeID = 1
+--set @PeriodID = 2
+	
+	
+	declare @DateFrom datetime
+	declare @DateTo 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
+		@DateFrom = pt.ActualPeriodDateFrom,
+		@DateTo = pt.ActualPeriodDateTo
+
+	from @PeriodTable pt
+	where
+		pt.ID = @PeriodID
+
+	declare @TerminalsList table (TerminalID nvarchar(256), ShopID int, CompanyID int)
+
+	insert into @TerminalsList
+		select
+			t.Id as TerminalID,
+			t.ShopID as ShopID,
+			s.CompanyID as CompanyID
+
+		from dbo.Terminals t with (nolock)
+			inner join dbo.Shops s with (nolock) on 
+					s.Id = t.ShopId
+				and isnull(s.CompanyId, 0) <> 0
+
+	
+	declare @Sales table (
+			CompanyID int,
+			ShopID int,
+			CategoryID int,
+			SalesSumm decimal(15, 2),
+			SalesCount int
+			
+			INDEX IX_Sales_CompanyID NONCLUSTERED (CompanyID)
+		)
+
+	
+	insert into @Sales		
+		select
+			tl.CompanyID,
+			tl.ShopID,
+			p.CategoryId,
+			Sum(Cast(sl.Value/100 as decimal(15, 2))) as SalesSumm,
+			Sum(sl.Count) as  SalesCount
+
+		from @TerminalsList tl 
+		inner join dbo.Sessions ss with (nolock) on
+				ss.TerminalId = tl.TerminalID
+			and	ss.CloseTime between @DateFrom and @DateTo 
+			and (ss.Flags & 2) = 0 -- �������� ��������� ������
+		
+		inner join dbo.Sales sl with (nolock) on
+				sl.TerminalId = tl.TerminalID
+			and sl.SessionId = ss.SessionId
+
+		left join dbo.Products p with (nolock) on p.Id = sl.ProductId
+			
+		group by
+			tl.CompanyID,
+			tl.ShopID,
+			p.CategoryID
+	
+	declare @ShopsCount table (
+			CompanyID int, 
+			ShopsCount int 
+			
+			INDEX IX_ShopsCount_CompanyID NONCLUSTERED (CompanyID)
+		)
+
+	insert into @ShopsCount
+		select
+			t.CompanyID,
+			Sum(1) as ShopsCount 		
+
+		from (
+				select
+					sl.CompanyID,
+					sl.ShopID
+
+				from @Sales sl
+				group by
+					sl.CompanyID,
+					sl.ShopID
+
+			) as t
+		
+		group by
+			t.CompanyID		 
+
+
+	declare @SalesTotals table (
+			CompanyID int, 
+			CategoryID int,
+			SalesSumm decimal(15, 2), 
+			SalesCount int
+			
+			INDEX IX_SalesTotals_CompanyID NONCLUSTERED (CompanyID),
+			INDEX IX_SalesTotals_CategoryID NONCLUSTERED (CategoryID)
+		)
+	
+	insert into @SalesTotals
+		select
+			sl.CompanyID,
+			isnull(sl.CategoryID, 0) as CategoryID,
+			Sum(sl.SalesSumm) as SalesSumm,
+			Sum(sl.SalesCount) as SalesCount
+
+		from @Sales sl
+		group by 
+			sl.CompanyID,
+			sl.CategoryID
+
+	declare @SalesSummByCategories table (
+			CompanyID int,
+			CategorySumm01 decimal (15,2),
+			CategorySumm02 decimal (15,2),
+			CategorySumm03 decimal (15,2),
+			CategorySumm04 decimal (15,2),
+			CategorySumm05 decimal (15,2),
+			CategorySumm06 decimal (15,2),
+			CategorySumm07 decimal (15,2),
+			CategorySumm08 decimal (15,2),
+			CategorySumm09 decimal (15,2),
+			SummWithoutCategory decimal (15,2)
+			
+			INDEX IX_SalesSummByCategories_CompanyID NONCLUSTERED (CompanyID)
+		)
+	
+	insert into @SalesSummByCategories
+		select 
+			CompanyID,
+			isnull([1], 0) as CategorySumm01, 
+			isnull([2], 0) as CategorySumm02, 
+			isnull([3], 0) as CategorySumm03, 
+			isnull([4], 0) as CategorySumm04, 
+			isnull([5], 0) as CategorySumm05, 
+			isnull([6], 0) as CategorySumm06, 
+			isnull([7], 0) as CategorySumm07, 
+			isnull([8], 0) as CategorySumm08, 
+			isnull([9], 0) as CategorySumm09,
+			isnull([0], 0) as SummWithoutCategory
+
+		from (
+				select
+					CompanyID,
+					CategoryID,
+					SalesSumm
+				from @SalesTotals
+			) as SorceTable
+			pivot
+			(
+				Sum(SalesSumm)
+				for CategoryID IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [0]) 
+			) as PivotTable
+
+	declare @SalesCountByCategories table (
+			CompanyID int,
+			CategoryCount01 int,
+			CategoryCount02 int,
+			CategoryCount03 int,
+			CategoryCount04 int,
+			CategoryCount05 int,
+			CategoryCount06 int,
+			CategoryCount07 int,
+			CategoryCount08 int,
+			CategoryCount09 int,
+			CountWithoutCategory int
+			
+			INDEX IX_SalesSummByCategories_CompanyID NONCLUSTERED (CompanyID)
+		)
+	
+	insert into @SalesCountByCategories
+		select 
+			CompanyID,
+			isnull([1], 0) as CategoryCount01, 
+			isnull([2], 0) as CategoryCount02, 
+			isnull([3], 0) as CategoryCount03, 
+			isnull([4], 0) as CategoryCount04, 
+			isnull([5], 0) as CategoryCount05, 
+			isnull([6], 0) as CategoryCount06, 
+			isnull([7], 0) as CategoryCount07, 
+			isnull([8], 0) as CategoryCount08, 
+			isnull([9], 0) as CategoryCount09,
+			isnull([0], 0) as SummWithoutCategory
+
+		from (
+				select
+					CompanyID,
+					CategoryID,
+					SalesCount
+				from @SalesTotals
+			) as SorceTable
+			pivot
+			(
+				Sum(SalesCount)
+				for CategoryID IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [0]) 
+			) as PivotTable
+	
+
+	select
+		datepart(year, @DateFrom) as Year,
+		datepart(week, @DateFrom) as Week,
+		@DateFrom as DateFrom,
+		@DateTo as DateTo,
+		c.Name,
+		isnull(sc.ShopsCount, 0 ) as ShopsCount,
+		isnull(totals.SalesCount, 0) as SalesCount,
+		isnull(scbc.CategoryCount01, 0) as CategoryCount01,
+		isnull(scbc.CategoryCount02, 0) as CategoryCount02,
+		isnull(scbc.CategoryCount03, 0) as CategoryCount03,
+		isnull(scbc.CategoryCount04, 0) as CategoryCount04,
+		isnull(scbc.CategoryCount05, 0) as CategoryCount05,
+		isnull(scbc.CategoryCount06, 0) as CategoryCount06,
+		isnull(scbc.CategoryCount07, 0) as CategoryCount07,
+		isnull(scbc.CategoryCount08, 0) as CategoryCount08,
+		isnull(scbc.CategoryCount09, 0) as CategoryCount09,
+		isnull(scbc.CountWithoutCategory, 0) as CountWithoutCategory,
+		isnull(totals.SalesSumm, 0) as SalesSumm,
+		isnull(ssbc.CategorySumm01, 0) as CategorySumm01,
+		isnull(ssbc.CategorySumm02, 0) as CategorySumm02,
+		isnull(ssbc.CategorySumm03, 0) as CategorySumm03,
+		isnull(ssbc.CategorySumm04, 0) as CategorySumm04,
+		isnull(ssbc.CategorySumm05, 0) as CategorySumm05,
+		isnull(ssbc.CategorySumm06, 0) as CategorySumm06,
+		isnull(ssbc.CategorySumm07, 0) as CategorySumm07,
+		isnull(ssbc.CategorySumm08, 0) as CategorySumm08,
+		isnull(ssbc.CategorySumm09, 0) as CategorySumm09,
+		isnull(ssbc.SummWithoutCategory, 0) as SummWithoutCategory
+
+	from dbo.Companies c with (nolock)
+		left join @ShopsCount as sc on sc.CompanyID = c.Id
+		left join @SalesCountByCategories scbc on scbc.CompanyID = c.Id
+		left join @SalesSummByCategories ssbc on ssbc.CompanyID = c.Id
+
+		left join (
+					select
+						st.CompanyID,
+						Sum(st.SalesSumm) as SalesSumm,
+						Sum(st.SalesCount) as SalesCount					
+					from @SalesTotals as st
+					
+					group by
+						st.CompanyID
+
+			) as totals on totals.CompanyID = c.Id
+
+end
\ No newline at end of file