set noexec off
go
if object_id('GetSales24U_WithoutDetails') is not null
begin
set noexec on
end
go
-- создаем пустой объект
-- необходимо указать название процедуры
create procedure dbo.GetSales24U_WithoutDetails as
raiserror ('Объект пустой. Ошибка при создаении объекта', -- Message text.
16, -- Severity.
1 -- State.
);
go
set noexec off
go
alter procedure dbo.GetSales24U_WithoutDetails
(
@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.Count,
t2.Summ,
t2.Count24u,
t2.Discount,
t2.Summ24u
from (
select
t.ShopId,
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,
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
union all
select
c.ShopId,
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
) as t
group by
t.ShopId
) as t2
left join dbo.Shops s with (nolock) on s.Id = t2.ShopId
end