set noexec off
go
if object_id('RevenueComparationByCategoriesTotalByCompany') is not null
begin
set noexec on
end
go
-- создаем пустой объект
-- необходимо указать название процедуры
create procedure dbo.RevenueComparationByCategoriesTotalByCompany as
raiserror ('Объект пустой. Ошибка при создаении объекта', -- Message text.
16, -- Severity.
1 -- State.
);
go
set noexec off
go
alter procedure dbo.RevenueComparationByCategoriesTotalByCompany
(
@CompanyID int,
@PeriodTypeID int,
@PeriodID int
)
as
begin
--declare @CompanyID int
--declare @PeriodTypeID int
--declare @PeriodID 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
declare @SalesTotals table (CategoryID int, LastPeriodSumm decimal(15, 2), ActualPeriodSumm decimal(15, 2), SalesLastPeriodCount int, SalesActualPeriodCount int)
insert into @SalesTotals
select
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 p.CategoryId
declare @WithdrawsLoadingsTotals table (CategoryID int, WithdrawsLastPeriodCount int, WithdrawsActualPeriodCount int, LoadingsLastPeriodCount int, LoadingsActualPeriodCount int)
insert into @WithdrawsLoadingsTotals
select
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 p.CategoryId
select
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 dbo.ProductCategories pc with (nolock)
left join @SalesTotals st on st.CategoryID = pc.Id
left join @WithdrawsLoadingsTotals wlt on wlt.CategoryID = pc.ID
order by pc.Id
end