set noexec off
go
if object_id('RevenueComparationByCategories') is not null
begin
set noexec on
end
go
-- создаем пустой объект
-- необходимо указать название процедуры
create procedure dbo.RevenueComparationByCategories as
raiserror ('Объект пустой. Ошибка при создаении объекта', -- Message text.
16, -- Severity.
1 -- State.
);
go
set noexec off
go
alter procedure dbo.RevenueComparationByCategories
(
@CompanyID int,
@PeriodTypeID int,
@PeriodID int,
@ShopID int = null
)
as
begin
--declare @CompanyID int
--declare @PeriodTypeID int
--declare @PeriodID int
--declare @ShopID int
--set @CompanyID = 1
--set @PeriodTypeID = 1
--set @PeriodID = 2
declare @ActualPeriodDateFrom DateTime
declare @ActualPeriodDateTo DateTime
declare @LastPeriodDateFrom DateTime
declare @LastPeriodDateTo DateTime
declare @PeriodTable table (
ID Int,
ActualPeriodDateFrom DateTime,
ActualPeriodDateTo DateTime,
LastPeriodDateFrom DateTime,
LastPeriodDateTo DateTime,
Period2DateFrom DateTime,
Period2DateTo DateTime,
Period1DateFrom DateTime,
Period1DateTo DateTime,
PeiodDescription nvarchar(256)
)
insert into @PeriodTable
exec dbo.GetPeriodList_For_RevenueComparation @PeriodTypeID
select top 1
@ActualPeriodDateFrom = pt.ActualPeriodDateFrom,
@ActualPeriodDateTo = pt.ActualPeriodDateTo,
@LastPeriodDateFrom = pt.LastPeriodDateFrom,
@LastPeriodDateTo = pt.LastPeriodDateTo
from @PeriodTable pt
where pt.ID = @PeriodID
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
where
isnull(t.HostName,'') <> ''
and (
isnull(@ShopID, 0) = 0
or t.ShopId = @ShopID
)
declare @SalesTotals table (ShopID int, CategoryID int, LastPeriodSumm decimal(15, 2), ActualPeriodSumm decimal(15, 2), SalesLastPeriodCount int, SalesActualPeriodCount int)
insert into @SalesTotals
select
tl.ShopID,
p.CategoryId,
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
inner join dbo.Products p with (nolock) on
p.Id = sl.ProductId
group by tl.ShopID, p.CategoryId
declare @WithdrawsLoadingsTotals table (ShopID int, CategoryID int, WithdrawsLastPeriodCount int, WithdrawsActualPeriodCount int, LoadingsLastPeriodCount int, LoadingsActualPeriodCount int)
insert into @WithdrawsLoadingsTotals
select
tl.ShopID,
p.CategoryId,
Sum
(
case
when prc.Type = 0 and pr.CreatedOn between @LastPeriodDateFrom and @LastPeriodDateTo
then prc.Value
else 0
end
) as WithdrawsLastPeriodCount,
Sum
(
case
when prc.Type = 0 and pr.CreatedOn between @ActualPeriodDateFrom and @ActualPeriodDateTo
then prc.Value
else 0
end
) as WithdrawsActualPeriodCount,
Sum
(
case
when prc.Type = 1 and pr.CreatedOn between @LastPeriodDateFrom and @LastPeriodDateTo
then prc.Value
else 0
end
) as LoadingsLastPeriodCount,
Sum
(
case
when prc.Type = 1 and pr.CreatedOn between @ActualPeriodDateFrom and @ActualPeriodDateTo
then prc.Value
else 0
end
) as LoadingsActualPeriodCount
from @TerminalsList tl
inner join dbo.ProductReports pr with (nolock) on
pr.TerminalId = tl.TerminalID
and pr.ShopId = tl.ShopID
and (
pr.CreatedOn between @LastPeriodDateFrom and @LastPeriodDateTo
or pr.CreatedOn between @ActualPeriodDateFrom and @ActualPeriodDateTo
)
inner join dbo.ProductReportCounters prc with (nolock) on
prc.ReportId = pr.Id
and prc.Type in (0, 1)
inner join dbo.Products p with (nolock) on
p.Id = prc.ProductId
group by tl.ShopID, p.CategoryId
select
gtl.ShopID,
s.Name as ShopName,
isnull(pc.Name, '') as CategoryName,
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,
isnull(wlt.LoadingsLastPeriodCount, 0) as LoadingsLastPeriodCount,
isnull(wlt.LoadingsActualPeriodCount, 0) as LoadingsActualPeriodCount,
isnull(st.SalesLastPeriodCount, 0) as SalesLastPeriodCount,
isnull(st.SalesActualPeriodCount, 0) as SalesActualPeriodCount,
isnull(wlt.WithdrawsLastPeriodCount, 0) as WithdrawsLastPeriodCount,
isnull(wlt.WithdrawsActualPeriodCount, 0) as WithdrawsActualPeriodCount
from (
select
tl.ShopID
from @TerminalsList tl
group by tl.ShopID
) as gtl
inner join dbo.ProductCategories pc with (nolock) on 1 = 1
inner join dbo.Shops s with (nolock) on s.Id = gtl.ShopID
left join @SalesTotals st on
st.ShopID = gtl.ShopID
and st.CategoryID = pc.Id
left join @WithdrawsLoadingsTotals wlt on
wlt.ShopID = gtl.ShopID
and wlt.CategoryID = pc.ID
order by gtl.ShopID, pc.Id
end