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,
@CategoryID int
)
as
begin
--declare @CompanyID int
--declare @PeriodTypeID int
--declare @PeriodID int
--declare @CategoryID int
--set @CompanyID = 0
--set @PeriodTypeID = 1
--set @PeriodID = 2
--set @CategoryID = 1
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 or @CompanyID = 0)
declare @SalesTotals table (ProductId nvarchar(28), CategoryID int, LastPeriodSumm decimal(15, 2), ActualPeriodSumm decimal(15, 2), SalesLastPeriodCount int, SalesActualPeriodCount int)
insert into @SalesTotals
select
sl.ProductId,
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
and (p.CategoryId = @CategoryID or @CategoryID = 0)
group by
sl.ProductId,
p.CategoryId
declare @WithdrawsLoadingsTotals table (ProductId nvarchar(28), CategoryID int, WithdrawsLastPeriodCount int, WithdrawsActualPeriodCount int, LoadingsLastPeriodCount int, LoadingsActualPeriodCount int)
insert into @WithdrawsLoadingsTotals
select
prc.ProductId,
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
prc.ProductId,
p.CategoryId
select
isnull(p.Name, '') as ProductName,
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 @SalesTotals st
inner join dbo.Products p with (nolock) on p.Id = st.ProductId
inner join dbo.ProductCategories pc with (nolock) on pc.Id = st.CategoryID
left join @WithdrawsLoadingsTotals wlt on wlt.ProductId = st.ProductId
order by pc.Id, p.Name
end