SqlReports

diff --git a/GetShopsListByCompanies.sql b/GetShopsListByCompanies.sql
new file mode 100644
index 0000000..9e99b68
--- /dev/null
+++ b/GetShopsListByCompanies.sql
@@ -0,0 +1,56 @@
+
+set noexec off
+go
+if object_id('GetShopsListByCompanies') is not null
+begin
+	set noexec on
+end	
+go
+-- ������� ������ ������
+-- ���������� ������� �������� ���������
+create procedure dbo.GetShopsListByCompanies as
+raiserror ('������ ������. ������ ��� ��������� �������', -- Message text.
+				16, -- Severity.
+				1 -- State.
+			);
+go
+set noexec off
+go
+alter procedure dbo.GetShopsListByCompanies
+(
+	@CompaniesList varchar(max)
+)
+as 
+begin
+
+--declare @CompaniesList as varchar(max)
+
+--set @CompaniesList = '1, 2'	
+	
+	declare @CompaniesTable table (CompanyID int index IX_CompaniesTable_CompanyID nonclustered (CompanyID))
+	
+	if isnull(@CompaniesList,'') <> ''
+	begin
+		insert into @CompaniesTable
+			select
+				Convert(int,Value)
+			from STRING_SPLIT(@CompaniesList, ',')
+	end
+
+	select 
+		s.Id,
+		
+		case 
+			when isnull(s.Name,'') = '' 
+				 then '(ShopId = ' + convert(nvarchar(16), s.Id) + ')' 
+			 else s.Name 
+		end as Name
+
+	from dbo.Shops s with (nolock)
+		inner join @CompaniesTable ct on ct.CompanyID = s.CompanyId
+		inner join dbo.Terminals t with (nolock) on 
+				t.ShopId = s.Id
+			and isnull(t.HostName, '') <> ''
+
+	
+end
\ No newline at end of file

diff --git a/GetWeeksSales.sql b/GetWeeksSales.sql
new file mode 100644
index 0000000..74aeb1c
--- /dev/null
+++ b/GetWeeksSales.sql
@@ -0,0 +1,126 @@
+set noexec off
+go
+if object_id('GetWeeksSales') is not null
+begin
+	set noexec on
+end	
+go
+-- ������� ������ ������
+-- ���������� ������� �������� ���������
+create procedure dbo.GetWeeksSales as
+raiserror ('������ ������. ������ ��� ��������� �������', -- Message text.
+				16, -- Severity.
+				1 -- State.
+			);
+go
+set noexec off
+go
+alter procedure dbo.GetWeeksSales
+(
+	@Date as DateTime,
+	@CompaniesList varchar(max),
+	@ExcludedShopsList varchar(max)
+)
+as 
+
+begin
+	
+--declare @Date as DateTime
+--declare @CompaniesList as varchar(max)
+--declare @ExcludedShopsList as varchar(max)
+
+--set @Date = convert(DateTime,'07.09.2020',104)
+--set @CompaniesList = '1, 2'
+--set @ExcludedShopsList = ''
+
+	set DATEFIRST 1
+	declare @DateFrom as DateTime
+	declare @DateTo as DateTime
+	declare @cDate as DateTime
+	declare @cDateTo as DateTime
+	
+	declare @CompaniesTable table (CompanyID int index IX_CompaniesTable_CompanyID nonclustered (CompanyID))
+	
+	insert into @CompaniesTable
+		select
+			Convert(int,Value)
+		from STRING_SPLIT(@CompaniesList, ',')	
+	
+	declare @ExcludedShopsTable table (ShopID int index IX_ExcludedShopsTable_ShopID nonclustered (ShopID))
+	
+	if isnull(@ExcludedShopsList,'') <> ''
+	begin
+		insert into @ExcludedShopsTable
+			select
+				Convert(int,Value)
+			from STRING_SPLIT(@ExcludedShopsList, ',')
+	end
+	
+
+	set @DateTo = dateadd(ms, -2, dateadd(day, 8 - datepart(dw, @Date),@Date))
+	set @DateFrom = dateadd(ms, 2, dateadd(week, -13, @DateTo))
+	
+	declare @DateMap table (ID int  identity(1,1), Description nvarchar(32), DateFrom DateTime, DateTo DateTime index IX_DateMap_ID nonclustered (ID))
+	
+	set @cDate = @DateFrom
+
+	while @cDate < @DateTo
+	begin
+		set @cDateTo = dateadd(ms, -2, dateadd(week, 1, @cDate))
+		
+		insert into @DateMap
+			(Description, DateFrom, DateTo)
+				values(
+						convert (nvarchar(16), @cDate, 104) + ' - ' + convert (nvarchar(16), @cDateTo, 104),
+						@cDate,
+						@cDateTo
+					)
+		
+		set @cDate = dateadd(week, 1, @cDate)
+	end
+
+	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
+			inner join @CompaniesTable ct on ct.CompanyID = s.CompanyId
+			left join @ExcludedShopsTable exst on exst.ShopID = s.Id
+		
+		where
+			exst.ShopID is null
+
+		
+		select
+			dm.Description,	
+			t.SalesSumm
+
+		from (
+				select
+					dm.ID,
+				Sum(Cast(sl.Value/100 as decimal(15, 2))) as SalesSumm
+			
+				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 @DateMap dm on ss.CloseTime between dm.DateFrom and dm.DateTo
+
+				inner join dbo.Sales sl with (nolock) on
+						sl.TerminalId = tl.TerminalID
+					and sl.SessionId = ss.SessionId
+
+				group by
+					dm.ID
+			) as t
+				inner join @DateMap dm on dm.ID = t.ID
+		
+		order by 
+			t.id
+end
\ No newline at end of file