set noexec off
go
if object_id('ShopSales') is not null
begin
set noexec on
end
go
-- создаем пустой объект
-- необходимо указать название процедуры
create procedure dbo.ShopSales as
raiserror ('Объект пустой. Ошибка при создаении объекта', -- Message text.
16, -- Severity.
1 -- State.
);
go
set noexec off
go
alter procedure dbo.ShopSales
(
@CompanyID Int
)
as
begin
--declare @CompanyID Int
--set @CompanyID = 1
declare @LastEncashmentsList table (TerminalID nvarchar(256), ShopID Int, ToSessionId Int, LastEncashmentDate DateTime)
insert into @LastEncashmentsList
select
t.Id as TerminalID,
t.ShopId as ShopID,
e.ToSessionId as ToSessionId,
e.Timestamp as LastEncashmentDate
from dbo.Terminals t with (nolock)
inner join dbo.Shops s with (nolock) on s.Id = t.ShopId
inner join dbo.Encashments e with (nolock) on e.TerminalId = t.Id and e.EncashmentId = t.LastEncashmentId
where
s.CompanyId = @CompanyID
declare @Sales table (ShopID Int, ProductID nvarchar(256), Count Int, Summ Decimal(15,2))
insert into @Sales
select
l.ShopID,
s.ProductId,
Sum(s.Count) as Count,
Sum(convert(decimal(15,2), s.Value/100)) as Summ
from @LastEncashmentsList l
inner join dbo.Sales s with (nolock) on
s.TerminalId = l.TerminalID
and s.SessionId > l.ToSessionId
inner join dbo.Sessions ss with (nolock) on
ss.TerminalId = s.TerminalID
and ss.SessionId = s.SessionId
--and ss.CloseTime > l.LastEncashmentDate
and (ss.Flags & 2) = 0 -- Отсекаем отменённые сессии
group by l.ShopID, s.ProductId
declare @ShopList table (ShopID Int, LastEncashmentDate DateTime, index IX_ShopList_ShopID nonclustered (ShopID))
insert into @ShopList
select
l.ShopID,
MIN(l.LastEncashmentDate) as LastEncashmentDate
from @LastEncashmentsList l
group by l.ShopID
declare @SalesTotals table (ShopID int, CoffeeCount Int, SnackCount int, CoffeeSumm Decimal(15,2), SnackSumm Decimal(15,2), TotalSumm Decimal(15,2))
insert into @SalesTotals
select
s.ShopID,
Sum (
case
when p.CategoryId = 1 then s.Count
else 0
end
) as CoffeeCount,
Sum (
case
when p.CategoryId <> 1 then s.Count
else 0
end
) as SnackCount,
Sum (
case
when p.CategoryId = 1 then s.Summ
else 0
end
) as CoffeeSumm,
Sum (
case
when p.CategoryId <> 1 then s.Summ
else 0
end
) as SnackSumm,
Sum (s.Summ) as TotalSumm
from @Sales s
inner join dbo.Products p with (nolock) on p.Id = s.ProductID
group by s.ShopID
select
isnull(shops.Description, shops.Name) as ShopName,
sl.LastEncashmentDate,
isnull(st.CoffeeCount, 0) as CoffeeCount,
isnull(st.CoffeeSumm, 0) as CoffeeSumm,
isnull(st.SnackCount, 0) as SnackCount,
isnull(st.SnackSumm, 0) as SnackSumm,
isnull(st.TotalSumm, 0) as TotalSumm
from @ShopList sl
left join @SalesTotals st on st.ShopID = sl.ShopID
left join dbo.Shops shops with (nolock) on shops.Id = sl.ShopID
end
go