set noexec off
go
if object_id('GetEncashmentsStats') is not null
begin
set noexec on
end
go
-- создаем пустой объект
-- необходимо указать название процедуры
create procedure dbo.GetEncashmentsStats as
raiserror ('Объект пустой. Ошибка при создаении объекта', -- Message text.
16, -- Severity.
1 -- State.
);
go
set noexec off
go
alter procedure dbo.GetEncashmentsStats
(
@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 @DateFromInt DateTime
declare @DateToInt DateTime
set @DateFromInt = cast(@DateFrom as Date)
set @DateToInt = cast(@DateTo as Date)
set @DateToInt = dateadd(ms, -2, dateadd(day, 1, @DateToInt))
declare @EncashmentsList table (ID int identity(1,1), ShopID int, TerminalID nvarchar(256), EncashmentDate DateTime, FromSessionID int, ToSessionID int)
insert into @EncashmentsList
(ShopID, TerminalID, EncashmentDate, FromSessionID, ToSessionID)
select
t.ShopId,
e.TerminalId,
e.Timestamp as EncashmentDate,
e.FromSessionId,
e.ToSessionId
from dbo.Encashments e with (nolock)
inner join dbo.Terminals t with (nolock) on
t.Id = e.TerminalId
inner join dbo.Shops s with (nolock) on
s.Id = t.ShopId
and s.CompanyId = @CompanyID
where
e.Timestamp between @DateFromInt and @DateToInt
and (isnull(@ShopID, 0) = 0 or t.ShopId = @ShopID)
declare @t1 table (ID int index IX1, EncashmentCount int, EncashmentSumm decimal(15, 2))
insert into @t1
select
el.ID,
sl.Count as EncashmentCount,
Cast(sl.Value/100 as decimal(15, 2)) as EncashmentSumm
from @EncashmentsList el
inner join dbo.Sessions ss with (nolock) on
ss.TerminalId = el.TerminalID
and ss.SessionId between el.FromSessionID and el.ToSessionID
and (ss.Flags & 2) = 0 -- Отсекаем отменённые сессии
inner join dbo.Sales sl with (nolock) on
sl.TerminalId = el.TerminalID
and sl.SessionId = ss.SessionId
select
el.ShopID,
isnull(s.Description, s.Name) as ShopName,
el.EncashmentDate,
t.EncashmentCount,
t.EncashmentSumm
from (
select
t1.ID,
Sum(t1.EncashmentCount) as EncashmentCount,
Sum(t1.EncashmentSumm) as EncashmentSumm
from @t1 t1
group by t1.ID
) as t
inner join @EncashmentsList el on el.ID = t.ID
inner join dbo.Shops s with (nolock) on s.Id = el.ShopID
end