SqlReports

diff --git a/GetAverageCheck.sql b/GetAverageCheck.sql
new file mode 100644
index 0000000..b7161ad
--- /dev/null
+++ b/GetAverageCheck.sql
@@ -0,0 +1,200 @@
+
+set noexec off
+go
+if object_id('GetAverageCheck') is not null
+begin
+	set noexec on
+end	
+go
+-- ������� ������ ������
+-- ���������� ������� �������� ���������
+create procedure dbo.GetAverageCheck as
+raiserror ('������ ������. ������ ��� ��������� �������', -- Message text.
+				16, -- Severity.
+				1 -- State.
+			);
+go
+set noexec off
+go
+alter procedure dbo.GetAverageCheck
+(
+	@CompanyID int,
+	@DateFrom DateTime,
+	@DateTo DateTime,
+	@ShopID int = null
+) 
+as
+
+begin
+
+--declare @CompanyID int
+--declare @DateFrom DateTime
+--declare @DateTo DateTime
+--declare @ShopID int
+
+--set @CompanyID = 1
+--set @DateFrom = convert(DateTime, '15.04.2019', 104)	
+--set @DateTo = convert(DateTime, '21.04.2019', 104)	
+	
+	declare @t1_RowCount int
+	declare @DateTo_Date DateTime
+	set @DateTo_Date = cast(@DateTo as Date)
+	
+	set @DateTo = dateadd(ms, -2, dateadd(day, 1, @DateTo_Date))
+	 
+	declare @t1 table (ShopID int index IX_t1_ShopID, Total decimal(15,2), ItemsCount int, CashCredit int, BankcardCredit int, MobileAppCredit int, CardNumber nvarchar(max))
+	
+	insert into @t1
+		select 
+			t.ShopId,
+			cast(sum(sl.Value)/100 as decimal(15,2)) as Total,
+			sum(sl.Count) as ItemsCount,
+			ss.CashCredit,
+			ss.BankcardCredit,
+			ss.MobileAppCredit,
+			c.CardNumber
+		
+		from dbo.Shops sh with (nolock)
+			inner join dbo.Terminals t with (nolock) on sh.Id = t.ShopId
+		
+			inner join dbo.Sessions ss with (nolock) on 
+					ss.TerminalId = t.Id
+				and ss.OpenTime between @DateFrom and @DateTo
+				and (ss.Flags & 2) = 0
+			
+			inner join dbo.Sales sl with (nolock) on	
+					sl.TerminalId = t.Id
+				and sl.SessionId = ss.SessionId
+			
+			left join dbo.Cards c on c.Id = ss.CardId
+			
+			
+		where
+				sh.CompanyId = @CompanyID 
+			and ( isnull(@ShopID,0) = 0 or sh.Id = @ShopID )
+
+
+		group by 
+			sl.SessionId,
+			t.ShopId, 
+			ss.CashCredit, 
+			ss.BankcardCredit, 
+			ss.MobileAppCredit,
+			c.CardNumber
+
+	set @t1_RowCount  = (select count(*) from @t1)
+
+	select
+		0 as Totals,
+		isnull(s.Description, s.Name) as ShopName,
+		t.TotalSumm,
+		t.AverageCheckSumm,
+		t.Purchases,
+		t.Cards,
+		t.Guests,
+		t.ByCashCount,
+		t.ByBankcardCount,
+		t.ByMobileAppCount
+
+	from (
+			select
+				t1.ShopID,
+				Sum(t1.Total) as TotalSumm,
+				Cast(Round(Sum(t1.Total) / Sum(1), 2) as decimal (15,2) ) as AverageCheckSumm,
+				Sum(t1.ItemsCount) as Purchases,
+				
+				Sum	(
+						case
+							when t1.CardNumber is null then 0
+							else 1
+						end
+					
+					) as Cards,
+
+				Sum	(
+
+						case
+							when t1.CardNumber is null then 1
+							else 0
+						end 
+					) as Guests,
+
+				Sum (
+						case 
+							when t1.CashCredit > 0 then 1
+							else 0
+						end 
+					) as ByCashCount,
+
+				Sum (
+						case 
+							when t1.BankcardCredit > 0 then 1
+							else 0
+						end 
+					) as ByBankcardCount,
+
+				Sum (
+						case 
+							when t1.MobileAppCredit > 0 then 1
+							else 0
+						end 
+					) as ByMobileAppCount
+
+
+			from @t1 t1
+
+			group by t1.ShopID
+		) as t
+	
+		inner join dbo.Shops s on
+			s.Id = t.ShopID
+
+	union all
+	
+	select
+		1 as Totals,
+		null as ShopName,
+		Sum(t1.Total) as TotalSumm,
+		Cast(Round(Sum(t1.Total) / @t1_RowCount, 2) as decimal (15,2) ) as AverageCheckSumm,
+		Sum(t1.ItemsCount) as Purchases,
+		
+		Sum	(
+				case
+					when t1.CardNumber is null then 0
+					else 1
+				end
+			
+			)  as Cards,
+
+		Sum	(
+
+				case
+					when t1.CardNumber is null then 1
+					else 0
+				end 
+			) as Guests,
+
+		Sum (
+				case 
+					when t1.CashCredit > 0 then 1
+					else 0
+				end 
+			) as ByCashCount,
+
+		Sum (
+				case 
+					when t1.BankcardCredit > 0 then 1
+					else 0
+				end 
+			) as ByBankcardCount,
+
+		Sum (
+				case 
+					when t1.MobileAppCredit > 0 then 1
+					else 0
+				end 
+			) as ByMobileAppCount
+
+
+		from @t1 t1
+end
\ No newline at end of file