RevenueComparationCCM.sql

set noexec off
go
if object_id('RevenueComparationCCM') is not null
begin
	set noexec on
end	
go
-- создаем пустой объект
-- необходимо указать название процедуры
create procedure dbo.RevenueComparationCCM as
raiserror ('Объект пустой. Ошибка при создаении объекта', -- Message text.
				16, -- Severity.
				1 -- State.
			);
go
set noexec off
go
alter procedure dbo.RevenueComparationCCM
(
	 @CompaniesList varchar(max),
	 @ActualPeriodDateFrom DateTime = null,
	 @ActualPeriodDateTo DateTime = null,
	 @LastPeriodDateFrom DateTime = null,
	 @LastPeriodDateTo DateTime = null
) 
as 

begin

--declare @CompaniesList varchar(max)
--declare @ActualPeriodDateFrom DateTime
--declare @ActualPeriodDateTo DateTime
--declare @LastPeriodDateFrom DateTime
--declare @LastPeriodDateTo DateTime

--Set @CompaniesList = '1'
--Set @ActualPeriodDateFrom = convert(DateTime, '21.01.2020', 104)
--Set @ActualPeriodDateTo = convert(DateTime, '21.01.2020', 104)
--Set @LastPeriodDateFrom = convert(DateTime, '14.01.2020', 104)
--Set @LastPeriodDateTo = convert(DateTime, '14.01.2020', 104)

	
	declare @ActualPeriodDateFromInt DateTime
	declare @ActualPeriodDateToInt DateTime
	declare @LastPeriodDateFromInt DateTime
	declare @LastPeriodDateToInt DateTime

	Set @ActualPeriodDateFromInt = cast(@ActualPeriodDateFrom as Date)
	Set @LastPeriodDateFromInt = cast(@LastPeriodDateFrom as Date)
	Set @ActualPeriodDateToInt = cast(@ActualPeriodDateTo as Date)
	Set @LastPeriodDateToInt = cast(@LastPeriodDateTo as Date)

	Set @ActualPeriodDateToInt = dateadd(ms, -2, dateadd(day, 1, @ActualPeriodDateToInt)) 
	Set @LastPeriodDateToInt = dateadd(ms, -2, dateadd(day, 1, @LastPeriodDateToInt))

	
	declare @TerminalsList table (TerminalID nvarchar(256), ShopID int)

	insert into @TerminalsList
		select
			t.Id as TerminalID,
			t.ShopID as ShopID

		from dbo.Shops s with (nolock)
			inner join dbo.Terminals t with (nolock) on
				t.ShopId = s.Id
		where
			s.CompanyId in
				(
					select
						Convert(int, Value)
					from STRING_SPLIT(@CompaniesList, ',')
				)
		and t.HostName <> ''
		
	declare @SessionList table (
			ShopId int,
			TerminalId nvarchar(256),
			SessionId bigint,
			CashCredit decimal (15,2),
			BankcardCredit decimal (15,2),
			MobileAppCredit decimal (15,2),
			LastPeriod tinyint

			INDEX IX_SessionList_ShopId NONCLUSTERED (ShopId),
			INDEX IX_SessionList_LastPeriod NONCLUSTERED (LastPeriod)
		)

	insert into @SessionList
		select
			tl.ShopID,
			ss.TerminalId,
			ss.SessionId,
			cast(ss.CashCredit as decimal (15,2)) / 100 as CashCredit,
			cast(ss.BankcardCredit as decimal (15,2)) / 100 as BankcardCredit,
			cast(ss.MobileAppCredit as decimal (15,2)) / 100 as MobileAppCredit,
			
			case 
				when ss.CloseTime between @LastPeriodDateFromInt and @LastPeriodDateToInt then 1
				else 0
			end as LastPeriod 
			
		from @TerminalsList tl
			inner join dbo.Sessions ss with (nolock) on
					(
							ss.CloseTime between @ActualPeriodDateFromInt and @ActualPeriodDateToInt
						or	ss.CloseTime between @LastPeriodDateFromInt and @LastPeriodDateToInt
					)
				and ss.TerminalId = tl.TerminalID
				and (ss.Flags & 2) = 0 -- Отсекаем отменённые сессии

	
		
	declare @SessionTotals table (
			ShopId int,
			CashCreditLastPeriod decimal (15,2),
			BankcardCreditLastPeriod decimal (15,2),
			MobileAppCreditLastPeriod decimal (15,2),
			CashCredit decimal (15,2),
			BankcardCredit decimal (15,2),
			MobileAppCredit decimal (15,2)

			INDEX IX_SessionTotals_ShopId NONCLUSTERED (ShopId)
		)

	insert into @SessionTotals	
		select
			t.ShopId,
			sum(t.CashCreditLastPeriod),
			sum(t.BankcardCreditLastPeriod),
			sum(t.MobileAppCreditLastPeriod),
			sum(t.CashCredit),
			sum(t.BankcardCredit),
			sum(t.MobileAppCredit)

		from(
			select
				sl.ShopId,
				0 as CashCreditLastPeriod,
				0 as BankcardCreditLastPeriod,
				0 as MobileAppCreditLastPeriod,
				sum(sl.CashCredit) as CashCredit,
				sum(sl.BankcardCredit) as BankcardCredit,
				sum(sl.MobileAppCredit) as MobileAppCredit
		
			from @SessionList sl
			where
				sl.LastPeriod = 0
			
			group by sl.ShopId

			union all
			
			select
				sl.ShopId,
				sum(sl.CashCredit) as CashCreditLastPeriod,
				sum(sl.BankcardCredit) as BankcardCreditLastPeriod,
				sum(sl.MobileAppCredit) as MobileAppCreditLastPeriod,
				0 as CashCredit,
				0 as BankcardCredit,
				0 as MobileAppCredit
		
			from @SessionList sl
			where
				sl.LastPeriod = 1
			
			group by sl.ShopId
			
			) as t
		group by t.ShopId
		

	select 
		tl.ShopID as ShopID,
		s.Name,
		isnull(SessionTotals.CashCreditLastPeriod, 0) as CashCreditLastPeriod,
		isnull(SessionTotals.BankcardCreditLastPeriod, 0) as BankcardCreditLastPeriod,
		isnull(SessionTotals.MobileAppCreditLastPeriod, 0) as MobileAppCreditLastPeriod,				
		isnull(SessionTotals.CashCredit, 0) as CashCredit,
		isnull(SessionTotals.BankcardCredit, 0) as BankcardCredit,
		isnull(SessionTotals.MobileAppCredit, 0) as MobileAppCredit,
		
		isnull(SessionTotals.CashCredit, 0) + isnull(SessionTotals.BankcardCredit, 0) + isnull(SessionTotals.MobileAppCredit, 0)
			- isnull(SessionTotals.CashCreditLastPeriod, 0) - isnull(SessionTotals.BankcardCreditLastPeriod, 0) - isnull(SessionTotals.MobileAppCreditLastPeriod, 0) as Delta

	from @TerminalsList tl
		inner join dbo.Shops s on s.Id = tl.ShopID
		left join @SessionTotals SessionTotals on SessionTotals.ShopID = tl.ShopID
end

go