SqlReports

diff --git a/GetPeriods_For_RevenueComparationDaily.sql b/GetPeriods_For_RevenueComparationDaily.sql
new file mode 100644
index 0000000..f1ef79e
--- /dev/null
+++ b/GetPeriods_For_RevenueComparationDaily.sql
@@ -0,0 +1,76 @@
+
+set noexec off
+go
+if object_id('GetPeriods_For_RevenueComparationDaily') is not null
+begin
+	set noexec on
+end	
+go
+-- ������� ������ ������
+-- ���������� ������� �������� ���������
+create procedure dbo.GetPeriods_For_RevenueComparationDaily as
+raiserror ('������ ������. ������ ��� ��������� �������', -- Message text.
+				16, -- Severity.
+				1 -- State.
+			);
+go
+set noexec off
+go
+alter procedure dbo.GetPeriods_For_RevenueComparationDaily
+(
+	@Date1 DateTime,
+	@Date2 DateTime
+) 
+as 
+
+begin
+
+
+--declare @Date1 DateTime
+--declare @Date2 DateTime
+
+--set @Date1 = '20190527'
+--set @Date2 = '20190526'
+
+	declare @ActualPeriodDateFrom DateTime
+	declare @ActualPeriodDateTo DateTime
+	declare @LastPeriodDateFrom DateTime
+	declare @LastPeriodDateTo DateTime
+	declare @CurrentDate DateTime
+	declare @CurrentDateTime DateTime
+	declare @msOffset int
+
+	if (@Date1 > @Date2)
+	begin
+		set @ActualPeriodDateFrom = cast(@Date1 as Date)
+		set @LastPeriodDateFrom = cast(@Date2 as Date)
+	end
+	else
+	begin
+		set @ActualPeriodDateFrom = cast(@Date2 as Date)
+		set @LastPeriodDateFrom = cast(@Date1 as Date)
+	end
+	
+	set @CurrentDateTime = GetDate()
+	set @CurrentDate = cast(@CurrentDateTime as Date)
+
+	if (@ActualPeriodDateFrom = @CurrentDate)
+	begin
+		set @msOffset = datediff(ms, @CurrentDate, @CurrentDateTime)
+		
+		set @ActualPeriodDateTo = dateadd(ms, @msOffset, @ActualPeriodDateFrom)
+		set @LastPeriodDateTo = dateadd(ms, @msOffset, @LastPeriodDateFrom)
+	end
+	else
+	begin
+		set @ActualPeriodDateTo = dateadd(ms, -2, dateadd(day, 1, @ActualPeriodDateFrom))
+		set @LastPeriodDateTo = dateadd(ms, -2, dateadd(day, 1, @LastPeriodDateFrom))
+	end
+
+	select
+		@ActualPeriodDateFrom as ActualPeriodDateFrom,
+		@ActualPeriodDateTo as ActualPeriodDateTo,
+		@LastPeriodDateFrom as LastPeriodDateFrom,
+		@LastPeriodDateTo as LastPeriodDateTo
+
+end
\ No newline at end of file

diff --git a/RevenueComparationDaily.sql b/RevenueComparationDaily.sql
new file mode 100644
index 0000000..1b670fc
--- /dev/null
+++ b/RevenueComparationDaily.sql
@@ -0,0 +1,163 @@
+
+set noexec off
+go
+if object_id('RevenueComparationDaily') is not null
+begin
+	set noexec on
+end	
+go
+-- ������� ������ ������
+-- ���������� ������� �������� ���������
+create procedure dbo.RevenueComparationDaily as
+raiserror ('������ ������. ������ ��� ��������� �������', -- Message text.
+				16, -- Severity.
+				1 -- State.
+			);
+go
+set noexec off
+go
+alter procedure dbo.RevenueComparationDaily
+(
+	@CompanyID int,
+	@Date1 DateTime,
+	@Date2 DateTime
+) 
+as 
+
+begin
+
+--declare @CompanyID int
+--declare @Date1 DateTime
+--declare @Date2 DateTime
+
+--set @CompanyID = 1
+--set @Date1 = '20190523'
+--set @Date2 = '20190524'
+		
+	declare @ActualPeriodDateFrom DateTime
+	declare @ActualPeriodDateTo DateTime
+	declare @LastPeriodDateFrom DateTime
+	declare @LastPeriodDateTo DateTime
+	
+	declare @PeriodTable table (
+			ActualPeriodDateFrom DateTime,
+			ActualPeriodDateTo DateTime,
+			LastPeriodDateFrom DateTime, 
+			LastPeriodDateTo DateTime
+		)
+	
+	insert into @PeriodTable
+		exec dbo.GetPeriods_For_RevenueComparationDaily @Date1, @Date2 
+
+	select top 1
+		@ActualPeriodDateFrom = pt.ActualPeriodDateFrom,
+		@ActualPeriodDateTo = pt.ActualPeriodDateTo,
+		@LastPeriodDateFrom = pt.LastPeriodDateFrom,
+		@LastPeriodDateTo = pt.LastPeriodDateTo
+	
+	from @PeriodTable pt
+	
+
+	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 
+ 
+ 	
+	declare @SalesTotals table (ShopID int, LastPeriodSumm decimal(15, 2), ActualPeriodSumm decimal(15, 2), SalesLastPeriodCount int, SalesActualPeriodCount int)
+
+	insert into @SalesTotals
+		select
+			tl.ShopID,
+			
+			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
+
+		group by tl.ShopID
+
+	select
+		gtl.ShopID, 
+		isnull(s.Description, s.Name) as ShopName,
+		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
+	
+	from (
+			select 
+				tl.ShopID
+			from @TerminalsList tl
+
+			group by tl.ShopID
+		) as gtl
+		
+			inner join dbo.Shops s with (nolock) on s.Id = gtl.ShopID
+			left join @SalesTotals st on st.ShopID = gtl.ShopID
+
+end
\ No newline at end of file

diff --git a/RevenueComparationInsideTheDay.sql b/RevenueComparationInsideTheDay.sql
new file mode 100644
index 0000000..6a6e1a3
--- /dev/null
+++ b/RevenueComparationInsideTheDay.sql
@@ -0,0 +1,189 @@
+
+set noexec off
+go
+if object_id('RevenueComparationInsideTheDay') is not null
+begin
+	set noexec on
+end	
+go
+-- ������� ������ ������
+-- ���������� ������� �������� ���������
+create procedure dbo.RevenueComparationInsideTheDay as
+raiserror ('������ ������. ������ ��� ��������� �������', -- Message text.
+				16, -- Severity.
+				1 -- State.
+			);
+go
+set noexec off
+go
+alter procedure dbo.RevenueComparationInsideTheDay
+(
+	@CompanyID int,
+	@ShopID int,
+	@Date1 DateTime,
+	@Date2 DateTime
+) 
+as 
+	
+begin
+
+--declare @CompanyID int
+--declare @ShopID int
+--declare @Date1 DateTime
+--declare @Date2 DateTime
+
+--set @CompanyID = 1
+--set @ShopID = 6
+--set @Date1 = '20190527'
+--set @Date2 = '20190526'
+		
+	declare @ActualPeriodDateFrom DateTime
+	declare @ActualPeriodDateTo DateTime
+	declare @LastPeriodDateFrom DateTime
+	declare @LastPeriodDateTo DateTime
+	
+	declare @PeriodTable table (
+			ActualPeriodDateFrom DateTime,
+			ActualPeriodDateTo DateTime,
+			LastPeriodDateFrom DateTime, 
+			LastPeriodDateTo DateTime
+		)
+	
+	insert into @PeriodTable
+		exec dbo.GetPeriods_For_RevenueComparationDaily @Date1, @Date2 
+	
+	select top 1
+		@ActualPeriodDateFrom = pt.ActualPeriodDateFrom,
+		@ActualPeriodDateTo = pt.ActualPeriodDateTo,
+		@LastPeriodDateFrom = pt.LastPeriodDateFrom,
+		@LastPeriodDateTo = pt.LastPeriodDateTo
+
+	from @PeriodTable pt
+
+
+	declare @CurDate1 datetime
+	declare @CurDate2 datetime
+	declare @CurDate1To datetime
+	declare @CurDate2To datetime
+	declare @PeriodDescr varchar(32)
+	
+	set @CurDate1 = @ActualPeriodDateFrom
+	set @CurDate2 = @LastPeriodDateFrom
+
+	declare @DateMatrix table (ID int identity(1,1), PeriodDescr varchar(32), ActualPeriodDateFrom DateTime, ActualPeriodDateTo DateTime, LastPeriodDateFrom DateTime, LastPeriodDateTo DateTime)
+
+	while (@CurDate1 < @ActualPeriodDateTo) 
+	begin
+		
+		set @CurDate1To = dateadd(ms, -2, dateadd(hour, 1, @CurDate1))
+		set @CurDate2To = dateadd(ms, -2, dateadd(hour, 1, @CurDate2))
+		
+		if (@CurDate1To > @ActualPeriodDateTo) set @CurDate1To = @ActualPeriodDateTo
+		if (@CurDate2To > @LastPeriodDateTo) set @CurDate2To = @LastPeriodDateTo
+
+		set @PeriodDescr = substring(convert(varchar(32),@CurDate1, 120), 12, 5) + ' - ' + substring(convert(varchar(32),@CurDate1To, 120), 12, 8)
+
+		insert into @DateMatrix
+			(PeriodDescr, ActualPeriodDateFrom, ActualPeriodDateTo, LastPeriodDateFrom, LastPeriodDateTo)
+				values(@PeriodDescr, @CurDate1, @CurDate1To, @CurDate2, @CurDate2To)
+		
+		set @CurDate1 = dateadd(hour, 1, @CurDate1)
+		set @CurDate2 = dateadd(hour, 1, @CurDate2)
+	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)
+		where
+				t.ShopID = @ShopID
+
+	declare @SalesTotals table (PeriodID int, LastPeriodSumm decimal(15, 2), ActualPeriodSumm decimal(15, 2), SalesLastPeriodCount int, SalesActualPeriodCount int)
+
+	insert into @SalesTotals
+		select
+			dm.ID as PeriodID,
+			
+			Sum
+			(
+				case
+					when ss.CloseTime between dm.LastPeriodDateFrom and dm.LastPeriodDateTo
+						then Cast(sl.Value/100 as decimal(15, 2))
+
+					else 0
+				end
+			) as LastPeriodSumm,
+			
+			Sum
+			(
+				case
+					when ss.CloseTime between dm.ActualPeriodDateFrom and dm.ActualPeriodDateTo
+						then Cast(sl.Value/100 as decimal(15, 2))
+					
+					else 0
+				end
+			) as ActualPeriodSumm,
+
+
+			Sum
+			(
+				case
+					when ss.CloseTime between dm.LastPeriodDateFrom and dm.LastPeriodDateTo
+						then sl.Count
+
+					else 0
+				end
+			) as SalesLastPeriodCount,
+			
+			Sum
+			(
+				case
+					when ss.CloseTime between dm.ActualPeriodDateFrom and dm.ActualPeriodDateTo
+						then sl.Count
+					
+					else 0
+				end
+			) as SalesActualPeriodCount
+
+		from @TerminalsList tl
+			inner join @DateMatrix dm on 1 = 1
+			inner join dbo.Sessions ss with (nolock) on
+					ss.TerminalId = tl.TerminalID
+				and	(
+							ss.CloseTime between dm.LastPeriodDateFrom and dm.LastPeriodDateTo 
+						or	ss.CloseTime between dm.ActualPeriodDateFrom and dm.ActualPeriodDateTo
+					)
+				and (ss.Flags & 2) = 0 -- �������� ��������� ������
+			
+			inner join dbo.Sales sl with (nolock) on
+					sl.TerminalId = tl.TerminalID
+				and sl.SessionId = ss.SessionId
+
+		group by dm.ID
+	
+	select
+		dm.PeriodDescr as PeriodDescr,
+		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
+	
+	from @SalesTotals st
+		inner join @DateMatrix dm on dm.ID = st.PeriodID 
+	
+	order by dm.ID		
+			
+			
+end
\ No newline at end of file