SqlReports
Changes
GetPeriodList_For_RevenueComparation.sql 140(+140 -0)
RevenueComparation.sql 234(+234 -0)
ShopSales.sql 138(+138 -0)
Details
GetPeriodList_For_RevenueComparation.sql 140(+140 -0)
diff --git a/GetPeriodList_For_RevenueComparation.sql b/GetPeriodList_For_RevenueComparation.sql
new file mode 100644
index 0000000..2c3645b
--- /dev/null
+++ b/GetPeriodList_For_RevenueComparation.sql
@@ -0,0 +1,140 @@
+-- GetPeriodList_For_RevenueComparation
+
+set noexec off
+go
+if object_id('GetPeriodList_For_RevenueComparation') is not null
+begin
+ set noexec on
+end
+go
+-- ������� ������ ������
+-- ���������� ������� �������� ���������
+create procedure dbo.GetPeriodList_For_RevenueComparation as
+raiserror ('������ ������. ������ ��� ��������� �������', -- Message text.
+ 16, -- Severity.
+ 1 -- State.
+ );
+go
+set noexec off
+go
+alter procedure dbo.GetPeriodList_For_RevenueComparation
+(
+ @PeriodTypeID Int
+)
+as
+
+begin
+
+
+--declare @PeriodTypeID Int
+--set @PeriodTypeID = 2
+
+ declare @PeriodCount Int
+
+ set datefirst 1;
+
+ declare @PeriodTable table (
+ ID int identity (1, 1),
+ ActualPeriodDateFrom DateTime,
+ ActualPeriodDateTo DateTime,
+ LastPeriodDateFrom DateTime,
+ LastPeriodDateTo DateTime
+ )
+
+
+ declare @ActualPeriodDateFrom as DateTime
+ declare @ActualPeriodDateTo as DateTime
+ declare @LastPeriodDateFrom as DateTime
+ declare @LastPeriodDateTo as DateTime
+
+ set @ActualPeriodDateTo = GetDate()
+
+ if (@PeriodTypeID = 1)
+ begin
+
+ set @PeriodCount = 52
+
+ set @ActualPeriodDateFrom = Cast(
+ DateAdd(day, 1 - DatePart(dw, @ActualPeriodDateTo), @ActualPeriodDateTo) as Date
+ )
+
+ while @PeriodCount > 0
+ begin
+ set @LastPeriodDateTo = DateAdd(day, -7, @ActualPeriodDateTo)
+ set @LastPeriodDateFrom = DateAdd(day, -7, @ActualPeriodDateFrom)
+
+ insert into @PeriodTable
+ (ActualPeriodDateFrom, ActualPeriodDateTo, LastPeriodDateFrom, LastPeriodDateTo)
+ values(@ActualPeriodDateFrom, @ActualPeriodDateTo, @LastPeriodDateFrom, @LastPeriodDateTo)
+
+ set @ActualPeriodDateTo = DateAdd(ms, -2 , @ActualPeriodDateFrom)
+ set @ActualPeriodDateFrom = DateAdd(day, -7 , @ActualPeriodDateFrom)
+
+ set @PeriodCount = @PeriodCount - 1
+ end;
+
+ select
+ pt.ID,
+ pt.ActualPeriodDateFrom,
+ pt.ActualPeriodDateTo,
+ pt.LastPeriodDateFrom,
+ pt.LastPeriodDateTo,
+
+ case
+ when ID = 1 then N'������� ������'
+ when ID = 2 then N'������� ������'
+ else Convert(nvarchar(16), pt.ActualPeriodDateFrom, 104) + ' - ' + Convert(nvarchar(16), pt.ActualPeriodDateTo, 104)
+ end as PeiodDescription
+
+ from @PeriodTable pt
+
+ order by pt.ID
+
+
+ end
+ else if (@PeriodTypeID = 2)
+ begin
+
+ set @PeriodCount = 12
+
+ set @ActualPeriodDateFrom = Cast(
+ DateAdd(day, 1 - DatePart(day, @ActualPeriodDateTo), @ActualPeriodDateTo) as Date
+ )
+
+ while @PeriodCount > 0
+ begin
+ set @LastPeriodDateTo = DateAdd(month, -1, @ActualPeriodDateTo)
+ set @LastPeriodDateFrom = DateAdd(month, -1, @ActualPeriodDateFrom)
+
+ insert into @PeriodTable
+ (ActualPeriodDateFrom, ActualPeriodDateTo, LastPeriodDateFrom, LastPeriodDateTo)
+ values(@ActualPeriodDateFrom, @ActualPeriodDateTo, @LastPeriodDateFrom, @LastPeriodDateTo)
+
+ set @ActualPeriodDateTo = DateAdd(ms, -2 , @ActualPeriodDateFrom)
+ set @ActualPeriodDateFrom = DateAdd(month, -1 , @ActualPeriodDateFrom)
+
+ set @PeriodCount = @PeriodCount - 1
+ end;
+
+ select
+ pt.ID,
+ pt.ActualPeriodDateFrom,
+ pt.ActualPeriodDateTo,
+ pt.LastPeriodDateFrom,
+ pt.LastPeriodDateTo,
+
+ case
+ when ID = 1 then N'������� �����'
+ when ID = 2 then N'������� �����'
+ else Convert(nvarchar(16), pt.ActualPeriodDateFrom, 104) + ' - ' + Convert(nvarchar(16), pt.ActualPeriodDateTo, 104)
+ end as PeiodDescription
+
+ from @PeriodTable pt
+
+ order by pt.ID
+
+ end
+
+end
+
+go
\ No newline at end of file
RevenueComparation.sql 234(+234 -0)
diff --git a/RevenueComparation.sql b/RevenueComparation.sql
new file mode 100644
index 0000000..5bbed27
--- /dev/null
+++ b/RevenueComparation.sql
@@ -0,0 +1,234 @@
+-- RevenueComparation
+
+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))
+
+ 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
+
+
+ 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 @WithdrawsTotals table (ShopID Int, LastPeriodSumm Int, ActualPeriodSumm Int)
+
+ insert into @WithdrawsTotals
+ select
+ tl.ShopID,
+
+ Sum
+ (
+ case
+ when pr.CreatedOn between @LastPeriodDateFrom and @LastPeriodDateTo
+ then prc.Value
+
+ else 0
+ end
+ ) as LastPeriodSumm,
+
+ Sum
+ (
+ case
+ when pr.CreatedOn between @ActualPeriodDateFrom and @ActualPeriodDateTo
+ then prc.Value
+
+ else 0
+ end
+ ) as ActualPeriodSumm
+
+ 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 = 0
+
+ group by tl.ShopID
+
+ select
+ isnull(s.Description, s.Name) as ShopName,
+ isnull(st.LastPeriodSumm, 0) as LastPeriodSumm,
+ isnull(wt.LastPeriodSumm, 0) as WithdrawsLastPeriodSumm,
+ isnull(st.ActualPeriodSumm, 0) as ActualPeriodSumm,
+ isnull(wt.ActualPeriodSumm, 0) as WithdrawsActualPeriodSumm,
+
+ 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
+
+ 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 @WithdrawsTotals wt on wt.ShopID = gtl.ShopID
+end
+
+go
ShopSales.sql 138(+138 -0)
diff --git a/ShopSales.sql b/ShopSales.sql
new file mode 100644
index 0000000..ee00a73
--- /dev/null
+++ b/ShopSales.sql
@@ -0,0 +1,138 @@
+set noexec off
+go
+if object_id('ShopSales') is not null
+begin
+ set noexec on
+end
+go
+-- ������� ������ ������
+-- ���������� ������� �������� ���������
+create procedure dbo.ShopSales as
+raiserror ('������ ������. ������ ��� ��������� �������', -- Message text.
+ 16, -- Severity.
+ 1 -- State.
+ );
+go
+set noexec off
+go
+alter procedure dbo.ShopSales
+(
+ @CompanyID Int
+)
+as
+
+begin
+
+--declare @CompanyID Int
+--set @CompanyID = 1
+
+
+ declare @LastEncashmentsList table (TerminalID nvarchar(256), ShopID Int, ToSessionId Int, LastEncashmentDate DateTime)
+
+ insert into @LastEncashmentsList
+ select
+ t.Id as TerminalID,
+ t.ShopId as ShopID,
+ e.ToSessionId as ToSessionId,
+ e.Timestamp as LastEncashmentDate
+
+ from dbo.Terminals t with (nolock)
+ inner join dbo.Shops s with (nolock) on s.Id = t.ShopId
+ inner join dbo.Encashments e with (nolock) on e.TerminalId = t.Id and e.EncashmentId = t.LastEncashmentId
+
+ where
+ s.CompanyId = @CompanyID
+
+
+ declare @Sales table (ShopID Int, ProductID nvarchar(256), Count Int, Summ Decimal(15,2))
+
+ insert into @Sales
+ select
+ l.ShopID,
+ s.ProductId,
+ Sum(s.Count) as Count,
+ Sum(convert(decimal(15,2), s.Value/100)) as Summ
+
+ from @LastEncashmentsList l
+ inner join dbo.Sales s with (nolock) on
+ s.TerminalId = l.TerminalID
+ and s.SessionId > l.ToSessionId
+
+ inner join dbo.Sessions ss with (nolock) on
+ ss.TerminalId = s.TerminalID
+ and ss.SessionId = s.SessionId
+ --and ss.CloseTime > l.LastEncashmentDate
+ and (ss.Flags & 2) = 0 -- �������� ��������� ������
+
+ group by l.ShopID, s.ProductId
+
+
+ declare @ShopList table (ShopID Int, LastEncashmentDate DateTime, index IX_ShopList_ShopID nonclustered (ShopID))
+
+ insert into @ShopList
+ select
+ l.ShopID,
+ MIN(l.LastEncashmentDate) as LastEncashmentDate
+
+ from @LastEncashmentsList l
+
+ group by l.ShopID
+
+
+ declare @SalesTotals table (ShopID int, CoffeeCount Int, SnackCount int, CoffeeSumm Decimal(15,2), SnackSumm Decimal(15,2), TotalSumm Decimal(15,2))
+
+ insert into @SalesTotals
+ select
+ s.ShopID,
+
+ Sum (
+ case
+ when p.CategoryId = 1 then s.Count
+ else 0
+ end
+ ) as CoffeeCount,
+
+ Sum (
+ case
+ when p.CategoryId <> 1 then s.Count
+ else 0
+ end
+ ) as SnackCount,
+
+ Sum (
+ case
+ when p.CategoryId = 1 then s.Summ
+ else 0
+ end
+ ) as CoffeeSumm,
+
+ Sum (
+ case
+ when p.CategoryId <> 1 then s.Summ
+ else 0
+ end
+ ) as SnackSumm,
+
+ Sum (s.Summ) as TotalSumm
+
+ from @Sales s
+ inner join dbo.Products p with (nolock) on p.Id = s.ProductID
+
+ group by s.ShopID
+
+ select
+ isnull(shops.Description, shops.Name) as ShopName,
+ sl.LastEncashmentDate,
+ isnull(st.CoffeeCount, 0) as CoffeeCount,
+ isnull(st.CoffeeSumm, 0) as CoffeeSumm,
+ isnull(st.SnackCount, 0) as SnackCount,
+ isnull(st.SnackSumm, 0) as SnackSumm,
+ isnull(st.TotalSumm, 0) as TotalSumm
+
+ from @ShopList sl
+ left join @SalesTotals st on st.ShopID = sl.ShopID
+ left join dbo.Shops shops with (nolock) on shops.Id = sl.ShopID
+
+end
+
+go