SqlReports

diff --git a/GetSales24U.sql b/GetSales24U.sql
new file mode 100644
index 0000000..f5aa91d
--- /dev/null
+++ b/GetSales24U.sql
@@ -0,0 +1,130 @@
+set noexec off
+go
+if object_id('GetSales24U') is not null
+begin
+	set noexec on
+end	
+go
+-- ������� ������ ������
+-- ���������� ������� �������� ���������
+create procedure dbo.GetSales24U as
+raiserror ('������ ������. ������ ��� ��������� �������', -- Message text.
+				16, -- Severity.
+				1 -- State.
+			);
+go
+set noexec off
+go
+alter procedure dbo.GetSales24U
+(
+	@DateFrom as DateTime,
+	@DateTo as DateTime,
+	@ShopsList varchar(max)
+)
+as 
+begin
+
+--declare	@DateFrom as DateTime
+--declare	@DateTo as DateTime
+--declare @ShopsList varchar(max)
+
+--set @DateFrom = Convert(DateTime,'05.09.2020', 104)
+--set @DateTo= Convert(DateTime,'14.09.2020', 104)
+--set @ShopsList='3,571'
+
+	declare @DateFromInt as DateTime
+	declare @DateToInt as DateTime
+	
+	set @DateFromInt = cast(@DateFrom as Date)
+	set @DateToInt = cast(@DateTo as Date)
+	set @DateToInt = dateadd(ms, -2, dateadd(day, 1, @DateToInt))
+	
+	declare @tShopsList table (ID int)
+
+	insert into @tShopsList
+		select
+			Convert(int, Value)
+		from STRING_SPLIT(@ShopsList, ',')
+		
+		select
+			t2.ShopId,
+			s.Name as ShopName,		
+			t2.ProductId,
+			p.Name as ProductName,
+			t2.Count,
+			t2.Summ,
+			t2.Count24u,
+			t2.Discount,
+			t2.Summ24u
+
+		from (
+				select
+					t.ShopId,
+					t.ProductId,
+					cast(Sum(t.Count) as decimal(15,2)) as Count,
+					cast(Sum(t.Summ) as decimal(15,2)) as Summ,
+					cast(Sum(t.Count24u) as decimal(15,2)) as Count24u,
+					cast(Sum(t.Discount) as decimal(15,2)) as Discount,
+					cast(Sum(t.Summ24u) as decimal(15,2)) as Summ24u
+
+				from (
+						select
+							t.ShopId,
+							sl.ProductId,
+							Sum(cast(sl.Value as decimal(15,2)) / 100) as Summ,
+							Sum(sl.Count) as Count,
+							0 as Count24u,
+							0 as Discount,
+							0 as Summ24u
+
+						from @tShopsList list
+							inner join dbo.Terminals t with (nolock) on t.ShopId = list.ID
+
+							inner join dbo.Sessions ss with (nolock) on
+									ss.CloseTime between @DateFromInt and @DateToInt
+								and ss.TerminalId = t.Id
+								and (ss.Flags & 2) = 0 -- �������� ��������� ������
+							
+							inner join dbo.Sales sl with (nolock) on 
+									sl.SessionId = ss.SessionId
+								and	sl.TerminalId = ss.TerminalId
+
+							group by
+								t.ShopId,
+								sl.ProductId
+						
+						union all
+						
+						select
+							c.ShopId,
+							ci.ProductId,
+							0 as Summ,
+							0 as Count,
+							Sum(ci.Count) as Count24u,
+							Sum(cast(ci.Discount as decimal(15,2))/100) as Discount,
+							Sum(cast((ci.Price - ci.Discount) as decimal(15,2))/ 100) as Summ24u
+
+						from @tShopsList list
+							inner join dbo.Carts c with (nolock) on
+									c.OpenTime between @DateFromInt and @DateToInt
+								and	c.ShopId = list.ID
+								and (c.Flags & 0x2) <> 0 -- ���� ������ ������ � ������ Processed
+
+							inner join dbo.CartItems ci with (nolock) on
+									ci.CartId = c.Id
+
+						group by
+							c.ShopId,
+							ci.ProductId
+				) as t
+			
+			group by			
+				t.ShopId,
+				t.ProductId
+			
+		) as t2
+		
+		left join dbo.Products p with (nolock) on p.Id = t2.ProductId
+		left join dbo.Shops s with (nolock) on s.Id = t2.ShopId
+
+end
\ No newline at end of file

diff --git a/GetSalesByOZON.sql b/GetSalesByOZON.sql
index 777ff85..e33b462 100644
--- a/GetSalesByOZON.sql
+++ b/GetSalesByOZON.sql
@@ -18,16 +18,28 @@ go
 alter procedure dbo.GetSalesByOZON
 (
 	@DateFrom as DateTime,
-	@DateTo as DateTime
+	@DateTo as DateTime,
+	@ShopsList as varchar(max) = null
 )
 as 
 begin
 
 --declare @DateFrom as DateTime
 --declare @DateTo as DateTime
+--declare @ShopsList as varchar(max)
 --set @DateFrom = Convert(DateTime,'10.09.2020', 104)
 --set @DateTo= Convert(DateTime,'10.09.2020', 104)
+--set @ShopsList = '1,4'
+	
+	
+	declare @ShopsTable table (ID int)
 
+	insert into @ShopsTable				
+		select
+			Convert(int, Value)
+		from STRING_SPLIT(@ShopsList, ',')
+	
+		
 	declare @DateFromInt as DateTime
 	declare @DateToInt as DateTime
 	
@@ -49,7 +61,8 @@ begin
 				Sum(cast(cast(s.Value as decimal(15,2))/100 as decimal(15,2))) as Summ
 				
 					
-			from dbo.Terminals t with (nolock)
+			from @ShopsTable st
+				inner join dbo.Terminals t with (nolock)on st.ID = t.ShopId
 				inner join dbo.Sessions ss with (nolock) on 
 						ss.CloseTime between @DateFromInt and @DateToInt
 					and	ss.TerminalId = t.Id
@@ -64,9 +77,6 @@ begin
 					and s.TerminalId = t.Id
 				
 			
-			where 
-					t.HostName like '%OZON%'
-			
 			group by
 				t.HostName,
 				s.ProductId