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 DateTime
--declare @ActualPeriodDateTo DateTime
--declare @LastPeriodDateFrom DateTime
--declare @LastPeriodDateTo DateTime
--set @CompanyID = 1
--set @PeriodTypeID = 1
--set @PeriodID = 2
declare @Period1DateFrom DateTime
declare @Period1DateTo DateTime
declare @Period2DateFrom DateTime
declare @Period2DateTo DateTime
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,
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,
@Period2DateFrom = pt.Period2DateFrom,
@Period2DateTo = pt.Period2DateTo,
@Period1DateFrom = pt.Period1DateFrom,
@Period1DateTo = pt.Period1DateTo
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, Period1Summ decimal(15, 2), Period2Summ decimal(15, 2), LastPeriodSumm decimal(15, 2), ActualPeriodSumm decimal(15, 2), SalesLastPeriodCount int, SalesActualPeriodCount int)
insert into @SalesTotals
select
tl.ShopID,
Sum
(
case
when ss.CloseTime between @Period1DateFrom and @Period1DateTo
then Cast(sl.Value/100 as decimal(15, 2))
else 0
end
) as Period1Summ,
Sum
(
case
when ss.CloseTime between @Period2DateFrom and @Period2DateTo
then Cast(sl.Value/100 as decimal(15, 2))
else 0
end
) as Period2Summ,
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
or (
@PeriodTypeID = 2
and (
ss.CloseTime between @Period1DateFrom and @Period1DateTo
or ss.CloseTime between @Period2DateFrom and @Period2DateTo
)
)
)
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.Period1Summ, 0) as Period1Summ,
isnull(st.Period2Summ, 0) as Period2Summ,
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