set noexec off
go
if object_id('RevenueComparation') is not null
begin
set noexec on
end
go
-- создаем пустой объект
-- необходимо указать название процедуры
create procedure dbo.RevenueComparation as
raiserror ('Объект пустой. Ошибка при создаении объекта', -- Message text.
16, -- Severity.
1 -- State.
);
go
set noexec off
go
alter procedure dbo.RevenueComparation
(
@CompanyID int,
@PeriodTypeID int,
@PeriodID int,
@ActualPeriodDateFrom DateTime = null,
@ActualPeriodDateTo DateTime = null,
@LastPeriodDateFrom DateTime = null,
@LastPeriodDateTo DateTime = null
)
as
begin
--declare @CompanyID int
--declare @PeriodTypeID int
--declare @PeriodID int
--declare @ActualPeriodDateFrom as DateTime
--declare @ActualPeriodDateTo as DateTime
--declare @LastPeriodDateFrom as DateTime
--declare @LastPeriodDateTo as DateTime
--set @CompanyID = 1
--set @PeriodTypeID = 1
--set @PeriodID = 2
if (
@ActualPeriodDateFrom is null
and @ActualPeriodDateTo is null
and @LastPeriodDateFrom is null
and @LastPeriodDateTo is null
)
begin
declare @PeriodTable table (
ID Int,
ActualPeriodDateFrom DateTime,
ActualPeriodDateTo DateTime,
LastPeriodDateFrom DateTime,
LastPeriodDateTo 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
end
else
begin
declare @CurrentDate DateTime
declare @CurrentDateTime DateTime
declare @ActualPeriodDateTo_Date DateTime
declare @LastPeriodDateTo_Date DateTime
declare @msOffset int
set @CurrentDateTime = GetDate()
set @CurrentDate = cast(@CurrentDateTime as Date)
set @ActualPeriodDateTo_Date = cast(@ActualPeriodDateTo as Date)
set @LastPeriodDateTo_Date = cast(@LastPeriodDateTo as Date)
if (@ActualPeriodDateTo_Date = @CurrentDate)
begin
set @msOffset = datediff(ms, @CurrentDate, @CurrentDateTime)
set @ActualPeriodDateTo = dateadd(ms, @msOffset, @ActualPeriodDateTo_Date)
set @LastPeriodDateTo = dateadd(ms, @msOffset, @LastPeriodDateTo_Date)
end
else
begin
set @ActualPeriodDateTo = dateadd(ms, -2, dateadd(day, 1, @ActualPeriodDateTo_Date))
set @LastPeriodDateTo = dateadd(ms, -2, dateadd(day, 1, @LastPeriodDateTo_Date))
end
end
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 (ShopID int, LastPeriodSumm decimal(15, 2), ActualPeriodSumm decimal(15, 2), SalesLastPeriodCount int, SalesActualPeriodCount int)
insert into @SalesTotals
select
tl.ShopID,
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
group by tl.ShopID
declare @WithdrawsLoadingsTotals table (ShopID int, WithdrawsLastPeriodCount int, WithdrawsActualPeriodCount int, LoadingsLastPeriodCount int, LoadingsActualPeriodCount int)
insert into @WithdrawsLoadingsTotals
select
tl.ShopID,
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)
group by tl.ShopID
select
gtl.ShopID,
isnull(s.Description, s.Name) as ShopName,
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.Shops s with (nolock) on s.Id = gtl.ShopID
left join @SalesTotals st on st.ShopID = gtl.ShopID
left join @WithdrawsLoadingsTotals wlt on wlt.ShopID = gtl.ShopID
end
go