Untitled
unknown
plain_text
a year ago
40 kB
4
Indexable
USE [MNTRANS]
GO
/****** Object: StoredProcedure [dbo].[SP_CR_GetCustomDataForSummaryReport] Script Date: 02/07/2017 11:28:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------
--Version Author Modification Date Comments
--5.0 Infosys 07 Jun 2007 1. Modified the stored proc to display the Funds Available.
--5.0.1 Infosys 06 Aug 2007 1. Included the condition for meter id in the subquery to return
-- single record for each meter.
-- 2. Inserted the GMT offset field to show correct date time
-- in case of different geographical location.
--5.0.1 Infosys 09/22 Aug 2007 1. Restructured the stored proc for the calculation of Start DR,
-- End DR and FundsAvailable field between date range.
-- 2. Also contain the fix for the FundsAvailable numeric filter
-- condition.
--5.5 Infosys 17 Sept 2007 1. Changed the calculation of StartDR. FundsAvailable calculation
-- is also changed as it depends on StartDR.
--5.5 Infosys 02 Nov 2007 1. Restructured the stored procedure and done the calculation of
-- the six field in separate table and merged then in a single table.
-- 2. Changed the calculation of Refill Amount from curRefillAmount
-- to (curEndMeterDR - curStartMeterDR) from table tblmeterrefill.
--5.5 Infosys 13 Feb 2008 1. Provided the support to display the start and end date in
-- correct format for different locale.
--5.5.1 a299ghw 07 Feb 2017 1. changed to exclude temporary tables when fields requested will not call
-- for the use of the slow temp tables
-- using delcared table of @vwTransactiondataForCustomReports where able
---------------------------------------------------------------------------------------------------------------------
--select * from fn_CR_GetSPParamValues(1)
--SP_CR_GetCustomDataForSummaryReport 838, NULL ,'US'
--SP_GetTransactionsbyAccount 1,5
ALTER PROC [dbo].[SP_CR_GetCustomDataForSummaryReport]
(@ReportParameterID INT,
@EntityType INT,
@strLanguageCode VARCHAR(3))
AS
DECLARE @FieldDispalyList NVARCHAR(max)
DECLARE @IsFundAvailable INT
DECLARE @GroupbyFieldList NVARCHAR(max)
DECLARE @OrderbyFieldList NVARCHAR(max)
DECLARE @WhereClauseFieldList NVARCHAR(max)
DECLARE @SQL NVARCHAR(max)
DECLARE @WhereClause NVARCHAR(max)
DECLARE @JOBLIST CHAR(3)
DECLARE @OPERATORLIST CHAR(3)
DECLARE @WORKSTATIONLIST CHAR(3)
DECLARE @METERLIST CHAR(3)
DECLARE @ACCOUNTLIST CHAR(3)
DECLARE @CARRIERLIST CHAR(3)
DECLARE @ACCOUNTINGPERIODLIST CHAR(3)
DECLARE @AccountListSelected INT
DECLARE @JobListSelected INT
DECLARE @OperatorListSelected INT
DECLARE @MeterListSelected INT
DECLARE @CarrierListSelected INT
DECLARE @WorkstationListSelected INT
DECLARE @AccountingPeriodListSelected INT
DECLARE @ReportNumber INT
DECLARE @StartMajorAccountingPeriod DATETIME
DECLARE @currentdate DATETIME
DECLARE @intIDAccountingMajorPeriod INT
DECLARE @intIDLanguage INT
DECLARE @ShowNormalTransactions INT
DECLARE @ShowEditedTransactions INT
DECLARE @ShowDiscrepantTransactions INT
DECLARE @ShowDisabledAccounts INT
DECLARE @StartDate DATETIME
DECLARE @ENDDate DATETIME
DECLARE @SecondStartDate DATETIME
DECLARE @SecondENDDate DATETIME
DECLARE @EntityCount INT
DECLARE @localtime DATETIME
DECLARE @timediff INT
DECLARE @ShowAccountNameOption INT
DECLARE @ShowVoidTransactions INT
DECLARE @Newtimediff INT
DECLARE @ShowFeeDetails INT
DECLARE @ShowOriginalTransactions INT
DECLARE @ShowSystemAccounts INT
DECLARE @ShowAdjustmentTransactions INT
DECLARE @intCount INT
DECLARE @intViewFeesCount INT
DECLARE @intViewMailPieceCount INT
DECLARE @intWhereClauseCount INT
DECLARE @intCounter INT
DECLARE @UnionOperator NVARCHAR(100)
DECLARE @DataType VARCHAR(50)
DECLARE @Operand VARCHAR(50)
DECLARE @ShowRelativeDates INT
DECLARE @RelativeDate INT
DECLARE @ShowSecondRelativeDates INT
DECLARE @SecondRelativeDate INT
DECLARE @ShowTwoDateBlocks INT
DECLARE @UseCustomReportTables INT
DECLARE @EntitySelectionTableName VARCHAR(50)
DECLARE @ShowDisabledOperators INT
DECLARE @ShowDisabledWorkStations INT
DECLARE @ShowDisabledCarriers INT
DECLARE @intJobFieldCount INT
DECLARE @strEndDate VARCHAR(50)
SET NOCOUNT ON
SET @JOBLIST='1'
SET @OPERATORLIST='2'
SET @WORKSTATIONLIST='3'
SET @METERLIST='4'
SET @ACCOUNTLIST='5'
SET @CARRIERLIST='6'
SET @ACCOUNTINGPERIODLIST='7'
SELECT @ReportNumber = ISNULL(intReportNumber,0)
FROM tblCustomReportParameters
WHERE intReportParameterID = @ReportParameterID
IF @ReportNumber IS NULL
BEGIN
SET @UseCustomReportTables=0
SELECT @ReportNumber = intReportNumber
FROM tblReportParameters
WHERE intReportParameterID = @ReportParameterID
SELECT @AccountListSelected = AccountListSelected,
@JobListSelected = JobListSelected,
@OperatorListSelected = OperatorListSelected,
@MeterListSelected = MeterListSelected,
@CarrierListSelected = CarrierListSelected,
@WorkstationListSelected = WorkstationListSelected ,
@AccountingPeriodListSelected = AccountingPeriodListSelected
FROM GetSelectedEntityTypes(@ReportParameterID)
SELECT @ShowEditedTransactions = ISNULL(ShowEditedTransactions,0),
@ShowDiscrepantTransactions = ISNULL(ShowDiscrepantTransactions,0),
@ShowNormalTransactions =ISNULL(ShowNormalTransactions,0),
@ShowDisabledAccounts = ISNULL(ShowDisabledAccounts,1),
@ShowFeeDetails = ISNULL(ShowFeeDetails,0),
@ShowSystemAccounts = ISNULL(ShowSystemAccounts,0),
@StartDate = StartDate,
@ENDDate = ENDDate,
@SecondStartDate = SecondStartDate,
@SecondENDDate = SecondENDDate,
@ShowAccountNameOption = ISNULL(ShowAccountNameOption,0),
@ShowRelativeDates = ShowRelativeDates,
@RelativeDate = RelativeDate,
@ShowDisabledOperators = ISNULL(ShowDisabledOperators ,1),
@ShowDisabledWorkStations = ISNULL(ShowDisabledWorkStations,1),
@ShowDisabledCarriers = ISNULL(ShowDisabledCarriers,1),
@ShowSecondRelativeDates = ShowSecondRelativeDates,
@SecondRelativeDate = SecondRelativeDate
FROM GetSPParamValues(@ReportParameterID)
END
ELSE
BEGIN
SET @UseCustomReportTables=1
SELECT @AccountListSelected =AccountListSelected,
@JobListSelected=JobListSelected ,
@OperatorListSelected =OperatorListSelected,
@MeterListSelected =MeterListSelected,
@CarrierListSelected =CarrierListSelected,
@WorkstationListSelected=WorkstationListSelected ,
@AccountingPeriodListSelected =AccountingPeriodListSelected
FROM fn_CR_GetSelectedEntityTypes(@ReportParameterID)
SELECT @ShowEditedTransactions = ISNULL(ShowEditedTransactions,0),
@ShowDiscrepantTransactions = ISNULL(ShowDiscrepantTransactions,0),
@ShowNormalTransactions = ISNULL(ShowNormalTransactions,0),
@ShowDisabledAccounts = ISNULL(ShowDisabledAccounts,1),
@ShowFeeDetails = ISNULL(ShowFeeDetails,0),
@ShowSystemAccounts = ISNULL(ShowSystemAccounts,0),
@StartDate = StartDate,
@ENDDate = ENDDate,
@SecondStartDate = SecondStartDate,
@SecondENDDate = SecondENDDate,
@ShowAccountNameOption = ISNULL(ShowAccountNameOption,0),
@ShowRelativeDates = ShowRelativeDates,
@RelativeDate = RelativeDate,
@ShowSecondRelativeDates = ShowSecondRelativeDates,
@ShowDisabledOperators = ISNULL(ShowDisabledOperators ,1),
@ShowDisabledWorkStations = ISNULL(ShowDisabledWorkStations,1),
@ShowDisabledCarriers = ISNULL(ShowDisabledCarriers,1),
@SecondRelativeDate = SecondRelativeDate
FROM fn_CR_GetSPParamValues(@ReportParameterID)
END
-- 5.5.1 added
declare @vwTransactiondataForCustomReports table (MeterID int,TransactionNumber int,TrxEndTime datetime
,AscendingRegisterStart money,AscendingRegisterEnd money
,DescendingRegisterStart money,DescendingRegisterEnd money
,BeginningPieceCount int,EndingPieceCount int)
insert into @vwTransactiondataForCustomReports
select tblBatchTransaction.intIDMeter AS MeterID,intNumTrans AS TransactionNumber,CASE WHEN tblBatch.intDecalageGMT IS NULL THEN DATEADD(hh, DATEDIFF(hh,
GETUTCDATE(), GETDATE()), tblBatchTransaction.dateGMTFin) ELSE DATEADD(n, - tblBatch.intDecalageGMT, tblBatchTransaction.dateGMTFin)
END AS TrxEndTime,
tblBatchTransaction.curARInit AS AscendingRegisterStart, dbo.tblBatchTransaction.curARFin AS AscendingRegisterEnd,
dbo.tblBatchTransaction.curDRInit AS DescendingRegisterStart, dbo.tblBatchTransaction.curDRFin AS DescendingRegisterEnd,
dbo.tblBatchTransaction.intPCInit AS BeginningPieceCount, dbo.tblBatchTransaction.intPCFin AS EndingPieceCount
from tblBatch inner join tblBatchTransaction on tblBatch.intIDMeter = tblBatchTransaction.intIDMeter
and tblBatch.intNumBatch = tblBatchTransaction.intNumBatch
SET @currentdate = GETUTCDATE()
SET @localtime = GETDATE()
SET @timediff = DATEDIFF(hh, @currentdate, @localtime)
SET @Newtimediff = DATEDIFF(hh, @localtime, @currentdate)
if @ShowRelativeDates = 1
BEGIN
--MAS
DECLARE @strlocalDate VARCHAR(50)
DECLARE @dtLocalDate DATETIME
--create date as just date, no time
SET @strlocalDate = CONVERT(VARCHAR, @localtime, 101)
SET @dtLocalDate = CONVERT(DATETIME, @strlocalDate, 101)
SELECT @StartDate = StartDate, @EndDate = EndDate+ '23:59:59'
FROM fn_CR_GetDates (@RelativeDate, @dtLocalDate)
--MAS
END
IF @ShowSecondRelativeDates = 1
BEGIN
SELECT @SecondStartDate = StartDate, @SecondENDDate = ENDDate
FROM fn_CR_GetDates (@SecondRelativeDate, @localtime)
END
--Added by Ankur Goel for adding time range in date filter
SET @strEndDate = CONVERT(VARCHAR, @EndDate, 101) + ' ' + CONVERT(VARCHAR, @EndDate, 108)
--set @strEndDate= convert(varchar,@EndDate,101) + '' 23:59:59''
IF @SecondStartDate IS NULL AND @SecondENDDate IS NULL
SET @ShowTwoDateBlocks = 0
ELSE
BEGIN
SET @ShowTwoDateBlocks = 1
SET @SecondENDDate = @SecondENDDate + '23:59:59'
END
SET @ShowVoidTransactions = 0
SET @ShowOriginalTransactions = 0
IF @ShowDisabledAccounts = 1
SET @ShowDisabledAccounts = -1
IF @ShowSystemAccounts =1
BEGIN
SET @ShowSystemAccounts = 0
SET @ShowAdjustmentTransactions = 32
IF @AccountListSelected > 0
BEGIN
IF @UseCustomReportTables = 0
BEGIN
INSERT INTO tblReportEntitySelectionList(intReportParameterID, intEntityID, intEntityType)
SELECT @ReportParameterID, tblAccountHierarchy.intID, CONVERT(INT, @ACCOUNTLIST)
FROM tblAccountHierarchy
WHERE intIDClient = 1
END
ELSE
BEGIN
INSERT INTO tblCustomReportEntitySelectionList(intReportParameterID, intEntityID,
intEntityType)
SELECT @ReportParameterID, tblAccountHierarchy.intID, CONVERT(INT, @ACCOUNTLIST)
FROM tblAccountHierarchy
WHERE intIDClient = 1
END
END
END
ELSE
BEGIN
SET @ShowSystemAccounts=1
SET @ShowAdjustmentTransactions=0
END
IF @ShowTwoDateBlocks = 0
BEGIN
IF @UseCustomReportTables=0
BEGIN
/**********************************************************************************************/
-- This block add the group by if fields are selected from that entity but not grouped by on
-- that entity in report saved mode.
SELECT @GroupbyFieldList =
CASE WHEN dbo.fn_EntityGroupByFieldToBeAdded(0,@ReportNumber,5) <> '' THEN
COALESCE(@GroupbyFieldList + ', ', '') +
dbo.fn_EntityGroupByFieldToBeAdded(0, @ReportNumber, 5)
ELSE @GroupbyFieldList
END
SELECT @GroupbyFieldList =
CASE WHEN dbo.fn_EntityGroupByFieldToBeAdded(0,@ReportNumber,4) <> '' THEN
COALESCE(@GroupbyFieldList + ', ', '') +
dbo.fn_EntityGroupByFieldToBeAdded(0, @ReportNumber, 4)
ELSE @GroupbyFieldList
END
/**********************************************************************************************/
SELECT @GroupbyFieldList = COALESCE(@GroupbyFieldList + ', ', '') + b.strViewFieldName
FROM tblReportsFieldSelectionList a, tblCustomReportsFieldList b
WHERE a.intReportNumber = @ReportNumber
AND a.intFieldID = b.intFieldID
AND a.booGroupbyField = -1
ORDER BY intGroupbySequence
SELECT @FieldDispalyList = COALESCE(@FieldDispalyList + ', ', '') + b.strViewGroupbyFieldName
+ ' AS ' + CONVERT(VARCHAR, b.strViewFieldName)
FROM tblReportsFieldSelectionList a, tblCustomReportsFieldList b
WHERE a.intReportNumber = @ReportNumber
AND a.intFieldID = b.intFieldID
AND b.strViewFieldName NOT IN ('StartDate','EndDate','PageNo','PageTotal','SystemDate')
SELECT @OrderbyFieldList = COALESCE(@OrderbyFieldList + ', ', '') + b.strViewFieldName + ' '
+ a.strSortOrder
FROM tblReportsFieldSelectionList a, tblCustomReportsFieldList b
WHERE a.intReportNumber = @ReportNumber
AND a.intFieldID = b.intFieldID
AND a.booSortbyField = -1
ORDER BY intOrderbySequence
--jay
SELECT @intViewMailPieceCount = COUNT(1)
FROM tblReportsFieldSelectionList a, tblCustomReportsFieldList b
WHERE a.intReportNumber = @ReportNumber
AND a.intFieldID = b.intFieldID
AND b.booJoinMailPieceView = -1
END
ELSE
BEGIN
/**********************************************************************************************/
-- This block add the group by if fields are selected from that entity but not grouped by on
-- that entity is in wizard mode.
SELECT @GroupbyFieldList =
CASE WHEN dbo.fn_EntityGroupByFieldToBeAdded(1,@ReportNumber,5) <> '' THEN
COALESCE(@GroupbyFieldList + ', ', '') +
dbo.fn_EntityGroupByFieldToBeAdded(1, @ReportNumber, 5)
ELSE @GroupbyFieldList
END
SELECT @GroupbyFieldList =
CASE WHEN dbo.fn_EntityGroupByFieldToBeAdded(1,@ReportNumber,4) <> '' THEN
COALESCE(@GroupbyFieldList + ', ', '') +
dbo.fn_EntityGroupByFieldToBeAdded(1, @ReportNumber, 4)
ELSE @GroupbyFieldList
END
/**********************************************************************************************/
SELECT @GroupbyFieldList = COALESCE(@GroupbyFieldList + ', ', '') + b.strViewFieldName
FROM tblCustomReportsFieldSelectionList a, tblCustomReportsFieldList b
WHERE a.intReportNumber = @ReportNumber
AND a.intFieldID = b.intFieldID
AND a.booGroupbyField = -1
ORDER BY intGroupbySequence
SELECT @FieldDispalyList = COALESCE(@FieldDispalyList + ', ', '') + b.strViewGroupbyFieldName
+ ' AS ' + b.strViewFieldName
FROM tblCustomReportsFieldSelectionList a ,tblCustomReportsFieldList b
WHERE a.intReportNumber=@ReportNumber
AND a.intFieldID = b.intFieldID
AND b.strViewFieldName NOT IN ('StartDate','EndDate','PageNo','PageTotal','SystemDate')
SELECT @OrderbyFieldList = COALESCE(@OrderbyFieldList + ', ', '') + b.strViewFieldName + ' '
+ a.strSortOrder
FROM tblCustomReportsFieldSelectionList a, tblCustomReportsFieldList b
WHERE a.intReportNumber = @ReportNumber
AND a.intFieldID = b.intFieldID
AND a.booSortbyField = -1
ORDER BY intOrderbySequence
SELECT @intViewMailPieceCount = COUNT(*)
FROM tblCustomReportsFieldSelectionList a, tblCustomReportsFieldList b
WHERE a.intReportNumber = @ReportNumber
AND a.intFieldID = b.intFieldID
AND b.booJoinMailPieceView = -1
END
IF EXISTS(SELECT strValeur FROM tblparametrage where strCle = 'intIDCountry' and strValeur = '69')
BEGIN
SET @FieldDispalyList = @FieldDispalyList + ' ' + ',' + ' 0 AS PageNo, 0 AS PageTotal,
CAST(GetDate() as varchar(50)) AS SystemDate'
SET @FieldDispalyList = @FieldDispalyList + ' ' + ',''' + CONVERT(VARCHAR, @StartDate, 101)
+ '''' + ' AS StartDate'
SET @FieldDispalyList = @FieldDispalyList + ' ' + ',''' + CONVERT(VARCHAR, @ENDDate, 101)
+ '''' + ' AS EndDate'
END
ELSE
BEGIN
SET @FieldDispalyList = @FieldDispalyList + ' ' + ',' + ' 0 AS PageNo, 0 AS PageTotal,
CONVERT(VARCHAR(17), GetDate(), 113) AS SystemDate'
SET @FieldDispalyList = @FieldDispalyList + ' ' + ',''' + CONVERT(VARCHAR, @StartDate, 103)
+ '''' + ' AS StartDate'
SET @FieldDispalyList = @FieldDispalyList + ' ' + ',''' + CONVERT(VARCHAR, @ENDDate, 103)
+ '''' + ' AS EndDate'
END
--skip if columns are not DescendingRegisterStart or DescendingRegisterEnd or FundsAvailable or PostageUsed or RefillAmount or AverageRefill
if (@FieldDispalyList like '%DescendingRegisterStart%' OR
@FieldDispalyList like '%DescendingRegisterEnd%' OR
@FieldDispalyList like '%FundsAvailable%' OR
@FieldDispalyList like '%PostageUsed%' OR
@FieldDispalyList like '%RefillAmount%' OR
@FieldDispalyList like '%AverageRefill%')
begin
/**********************************************************************************************/
-- Calculation to retrive the meter related information DescendingRegisterStart,
-- DescendingRegisterEnd, PostageUsed, RefillAmount, AverageRefill and FundsAvailable
-- for a meter between the date range.
-- Transaction summary information to get the minimum Transaction Number and Date & maximum
-- Transaction Number and Date for all meter within a user defined date range.
CREATE TABLE #TempTransSummary(MeterID INT, MinTransactionNumber INT, MaxTransactionNumber INT,
MinTrxEndTime DATETIME, MaxTrxEndTime DATETIME)
INSERT INTO #TempTransSummary(MeterID, MinTransactionNumber, MaxTransactionNumber,
MinTrxEndTime, MaxTrxEndTime)
SELECT E.MeterID, ( SELECT MIN(MINTN.TransactionNumber)
FROM @vwTransactiondataForCustomReports MINTN
WHERE MINTN.MeterID = E.METERID
AND MINTN.TrxEndTime = MIN(E.TrxEndTime)
GROUP BY MINTN.MeterID
) MinTransactionNumber,
( SELECT MAX(MAXTN.TransactionNumber)
FROM @vwTransactiondataForCustomReports MAXTN
WHERE MAXTN.MeterID = E.METERID
AND MAXTN.TrxEndTime = MAX(E.TrxEndTime)
GROUP BY MAXTN.MeterID
) MaxTransactionNumber,
MIN(E.TrxEndTime) MinTrxEndTime,
MAX(E.TrxEndTime) MaxTrxEndTime
FROM @vwTransactiondataForCustomReports E
WHERE E.TrxEndTime BETWEEN @StartDate AND @strEndDate
GROUP BY E.MeterID
-- Transaction summary information to get Refill Amount and Average Refill for all meter
-- within a user defined date range.
CREATE TABLE #TEMPRefill(MeterID INT, RefillAmount MONEY, AverageRefill MONEY)
INSERT INTO #TEMPRefill(MeterID, RefillAmount, AverageRefill)
SELECT DISTINCT a.MeterID, ISNULL(z.SUMcurRefillAmount, 0) AS RefillAmount,
ISNULL(z.AVGcurRefillAmount, 0) AS AverageRefill
FROM @vwTransactiondataForCustomReports a
LEFT OUTER JOIN (SELECT w.intIDMeter intIDMeter,
SUM(w.curEndMeterDR - w.curStartMeterDR) SUMcurRefillAmount,
AVG(w.curEndMeterDR - w.curStartMeterDR) AVGcurRefillAmount
FROM tblMeterRefill w
WHERE datRefill BETWEEN CAST(@StartDate AS VARCHAR(50)) AND @strEndDate
AND w.bytStatus = '2'
GROUP BY w.intIDMeter
) z
ON a.MeterID = z.intIDMeter
ORDER BY a.MeterID
-- Transaction summary information to get Ending Descending Register for all meter
-- within a user defined date range.
-- Added by jay on 22 May for the defect No GMSE00161234
--******************************************
Declare @datrefill table (Daterefill Datetime)
Declare @TrxEndTime table (TrxEndTime Datetime)
Insert into @datrefill
SELECT MAX(x.datRefill)
FROM tblMeterRefill x with (nolock) inner join @vwTransactiondataForCustomReports a on a.MeterID = x.intIDMeter
WHERE x.datRefill BETWEEN @StartDate AND @strEndDate
AND x.bytStatus = 2
GROUP BY x.intIDMeter
Insert into @TrxEndTime
SELECT MAX(y.TrxEndTime)
FROM @vwTransactiondataForCustomReports y -- inner join vwTransactiondataForCustomReports a with (nolock) on a.MeterID = y.MeterID
WHERE y.TrxEndTime BETWEEN @StartDate AND @strEndDate
-- GROUP BY y.MeterID
CREATE TABLE #TEMPDRE(MeterID INT, DescendingRegisterEnd MONEY)
INSERT INTO #TEMPDRE (MeterID, DescendingRegisterEnd)
SELECT a.MeterID
, ISNULL(
CASE WHEN a.TrxEndTime <= tblMeterRefill.datRefill
THEN tblMeterRefill.curEndMeterDR
ELSE a.DescendingRegisterEnd
END
, 0) AS DescendingRegisterEnd
FROM @vwTransactiondataForCustomReports a
LEFT OUTER JOIN #TempTransSummary with (nolock) ON #TempTransSummary.MeterID = a.MeterID
LEFT OUTER JOIN tblMeterRefill with (nolock) ON a.MeterID = tblMeterRefill.intIDMeter
AND tblMeterRefill.datRefill BETWEEN @StartDate AND @strEndDate
AND tblMeterRefill.datRefill in(Select * from @datrefill)
WHERE a.TrxEndTime BETWEEN @StartDate AND @strEndDate
AND a.TransactionNumber = #TempTransSummary.MaxTransactionNumber
AND a.TrxEndTime
in(Select * from @TrxEndTime)
Order by a.MeterID
--******************************************
--Commented by jay on 22 may for the defect no GMSE00161234
-- These line blocks the table due to inner loop so that these lines takes time to excute.
--******************************************
------------
------------ CREATE TABLE #TEMPDRE(MeterID INT, DescendingRegisterEnd MONEY)
------------ INSERT INTO #TEMPDRE (MeterID, DescendingRegisterEnd)
------------ SELECT a.MeterID
------------ , ISNULL(
------------ CASE WHEN a.TrxEndTime <= tblMeterRefill.datRefill
------------ THEN tblMeterRefill.curEndMeterDR
------------ ELSE a.DescendingRegisterEnd
------------ END
------------ , 0) AS DescendingRegisterEnd
------------ FROM vwTransactiondataForCustomReports a
------------ LEFT OUTER JOIN #TempTransSummary ON #TempTransSummary.MeterID = a.MeterID
------------ LEFT OUTER JOIN tblMeterRefill ON a.MeterID = tblMeterRefill.intIDMeter
------------ AND tblMeterRefill.datRefill BETWEEN CAST(@StartDate AS VARCHAR(50)) AND @strEndDate
------------ AND tblMeterRefill.datRefill IN (
------------ SELECT MAX(x.datRefill)
------------ FROM tblMeterRefill x
------------ WHERE x.datRefill BETWEEN CAST(@StartDate AS VARCHAR(50)) AND @strEndDate
------------ AND a.MeterID = x.intIDMeter
------------ AND x.bytStatus = 2
------------ GROUP BY x.intIDMeter
------------ )
------------ WHERE a.TrxEndTime BETWEEN @StartDate AND @strEndDate
------------ AND a.TransactionNumber = #TempTransSummary.MaxTransactionNumber
------------ AND a.TrxEndTime
------------ = (
------------ SELECT MAX(y.TrxEndTime)
------------ FROM vwTransactiondataForCustomReports y
------------ WHERE y.TrxEndTime BETWEEN CAST(@StartDate AS VARCHAR(50)) AND @strEndDate
------------ AND a.MeterID = y.MeterID
------------ GROUP BY y.MeterID)
------------ Order by a.MeterID
--******************************************
-- Transaction summary information to get Postage Used for all meter
-- within a user defined date range.
CREATE TABLE #TEMPPU(MeterID INT, PostageUsed MONEY)
INSERT INTO #TEMPPU(MeterID, PostageUsed)
SELECT a.MeterID, SUM(ISNULL((a.AscendingRegisterEnd - a.AscendingRegisterStart),0)) AS PostageUsed
FROM @vwTransactiondataForCustomReports a
WHERE a.TrxEndTime BETWEEN @StartDate AND @strEndDate
GROUP BY a.MeterID
ORDER BY a.MeterID
-- Transaction summary information to get Start Descending Register for all meter
-- within a user defined date range.
CREATE TABLE #TEMPDRS(MeterID INT, DescendingRegisterStart MONEY)
INSERT INTO #TEMPDRS (MeterID, DescendingRegisterStart)
SELECT DISTINCT a.MeterID,
ISNULL(
CASE WHEN a.TrxEndTime <= ISNULL(tblMeterRefill.datRefill,@strEndDate)
THEN a.DescendingRegisterStart
ELSE tblMeterRefill.curStartMeterDR
END
, 0) AS DescendingRegisterStart
FROM @vwTransactiondataForCustomReports a
LEFT OUTER JOIN #TempTransSummary ON #TempTransSummary.MeterID = a.MeterID
LEFT OUTER JOIN tblMeterRefill ON a.MeterID = tblMeterRefill.intIDMeter
AND tblMeterRefill.datRefill BETWEEN CAST(@StartDate AS VARCHAR(50)) AND @strEndDate
AND tblMeterRefill.datRefill in (Select * from @datrefill)
WHERE a.TrxEndTime BETWEEN @StartDate AND @strEndDate
AND a.TransactionNumber = #TempTransSummary.MinTransactionNumber
AND a.TrxEndTime
in(Select * from @TrxEndTime)
ORDER BY a.MeterID
-- Transaction summary information to get FundsAvailable for all meter
-- within a user defined date range.
CREATE TABLE #TEMPFA(MeterID INT, FundsAvailable MONEY)
INSERT INTO #TEMPFA (MeterID, FundsAvailable)
SELECT DISTINCT a.MeterID, #TEMPDRS.DescendingRegisterStart + ISNULL(#TEMPRefill.RefillAmount, 0) -
ISNULL(#TEMPPU.PostageUsed, 0) AS FundsAvailable
FROM @vwTransactiondataForCustomReports a
LEFT OUTER JOIN #TEMPDRS with (nolock) ON #TEMPDRS.MeterID = a.MeterID
LEFT OUTER JOIN #TEMPRefill ON #TEMPRefill.MeterID = a.MeterID
LEFT OUTER JOIN #TEMPPU ON #TEMPPU.MeterID = a.MeterID
ORDER BY a.MeterID
-- Consolidated all the above fields
CREATE TABLE #TEMP(MeterID INT, DescendingRegisterStart MONEY, DescendingRegisterEnd MONEY,
FundsAvailable MONEY, PostageUsed MONEY, RefillAmount MONEY, AverageRefill MONEY)
INSERT INTO #TEMP (MeterID, DescendingRegisterStart, DescendingRegisterEnd, FundsAvailable, PostageUsed,
RefillAmount, AverageRefill)
SELECT #TEMPPU.MeterID, DescendingRegisterStart, DescendingRegisterEnd, FundsAvailable, PostageUsed,
RefillAmount, AverageRefill
FROM #TEMPPU
LEFT OUTER JOIN #TEMPRefill ON #TEMPPU.MeterID = #TEMPRefill.MeterID
LEFT OUTER JOIN #TEMPDRS ON #TEMPPU.MeterID = #TEMPDRS.MeterID
LEFT OUTER JOIN #TEMPFA ON #TEMPPU.MeterID = #TEMPFA.MeterID
LEFT OUTER JOIN #TEMPDRE ON #TEMPPU.MeterID = #TEMPDRE.MeterID
ORDER BY #TEMPPU.MeterID
/**********************************************************************************************/
End --skip if columns are not DescendingRegisterStart or DescendingRegisterEnd or FundsAvailable or PostageUsed or RefillAmount or AverageRefill
SELECT @SQL = 'SELECT '
SELECT @SQL = @SQL + @FieldDispalyList
SELECT @SQL = @SQL + ' FROM vwTransactiondataForCustomReports'
if (@FieldDispalyList like '%DescendingRegisterStart%' OR
@FieldDispalyList like '%DescendingRegisterEnd%' OR
@FieldDispalyList like '%FundsAvailable%' OR
@FieldDispalyList like '%PostageUsed%' OR
@FieldDispalyList like '%RefillAmount%' OR
@FieldDispalyList like '%AverageRefill%')
begin
SELECT @SQL = @SQL + ' LEFT OUTER JOIN #TEMP tblARDR ON tblARDR.MeterID = vwTransactiondataForCustomReports.MeterID '
end
IF @UseCustomReportTables = 0
SET @EntitySelectionTableName = 'tblReportEntitySelectionList'
ELSE
SET @EntitySelectionTableName = 'tblCustomReportEntitySelectionList'
IF @intViewMailPieceCount > 0
BEGIN
SELECT @SQL = @SQL + ' JOIN vwTransactionMailPiecedataForSummaryCustomReports
ON vwTransactiondataForCustomReports.TransactionNumber =
vwTransactionMailPiecedataForSummaryCustomReports.TransactionNumber
AND vwTransactiondataForCustomReports.MeterID =
vwTransactionMailPiecedataForSummaryCustomReports.MeterID '
END
IF @AccountListSelected > 0
BEGIN
SELECT @SQL = @SQL + ' JOIN ' + @EntitySelectionTableName + ' a
ON vwTransactiondataForCustomReports.intID = a.intEntityID
AND a.intReportParameterID = (' + CONVERT(VARCHAR, @ReportParameterID) +')'
SELECT @SQL = @SQL + ' AND a.intEntityType = (' + @ACCOUNTLIST + ')'
END
IF @OperatorListSelected > 0
BEGIN
SELECT @SQL = @SQL + ' JOIN ' + @EntitySelectionTableName + ' b
ON vwTransactiondataForCustomReports.OperatorID = b.intEntityID
AND b.intReportParameterID= (' + CONVERT(VARCHAR, @ReportParameterID) +')'
SELECT @SQL = @SQL + ' and b.intEntityType = (' + @OPERATORLIST + ')'
END
IF @JobListSelected > 0
BEGIN
SELECT @SQL = @SQL + ' JOIN ' + @EntitySelectionTableName + ' c
ON vwTransactiondataForCustomReports.JobID = c.intEntityID
AND c.intReportParameterID = (' + CONVERT(VARCHAR, @ReportParameterID) +')'
SELECT @SQL = @SQL + ' and c.intEntityType = (' + @JOBLIST + ')'
END
IF @MeterListSelected > 0
BEGIN
SELECT @SQL = @SQL + ' JOIN ' + @EntitySelectionTableName + ' d
ON vwTransactiondataForCustomReports.MeterID = d.intEntityID
AND d.intReportParameterID = (' + CONVERT(VARCHAR, @ReportParameterID) +')'
SELECT @SQL = @SQL + ' and d.intEntityType = (' + @METERLIST + ')'
END
IF @CarrierListSelected > 0
BEGIN
DECLARE @CarrierCodeList TABLE ( [PrimaryCarrierID] [int] IDENTITY (1, 1) NOT NULL ,
intMDSCarrierToken INT NULL, intIDCarrier INT NULL,
strCarrierCode VARCHAR(100) NULL, intIDFleesDatabase INT NULL,
booCarrierStatus SMALLINT NULL )
CREATE TABLE #SelectedCarrierCodeList ( intIDCarrier INT NULL,
intIDFleesDatabase INT NULL, strCarrierCode VARCHAR(100) NULL,
booCarrierStatus SMALLINT NULL )
INSERT INTO @CarrierCodeList ( intMDSCarrierToken, intIDCarrier, strCarrierCode,
intIDFleesDatabase, booCarrierStatus )
SELECT MIN( tblFleesCarrier.intMDSCarrierToken ), MIN( tblFleesCarrier.intIDCarrier ),
MIN( tblFleesCarrier.strCarrierCode ), MIN( tblFleesCarrier.intIDFleesDatabase ),
MIN( tblFleesCarrier.booCarrierStatus )
FROM tblFleesCarrier
GROUP BY tblFleesCarrier.strcarriercode
IF @UseCustomReportTables=0
BEGIN
INSERT INTO #SelectedCarrierCodeList ( intIDCarrier, intIDFleesDatabase, strCarrierCode,
booCarrierStatus )
SELECT a.intIDCarrier, a.intIDFleesDatabase, a.strCarrierCode, a.booCarrierStatus
FROM tblFleesCarrier a, @CarrierCodeList c, tblReportEntitySelectionList b
WHERE a.strCarrierCode = c.strCarrierCode
AND c.PrimaryCarrierID = b.intEntityID
AND b.intReportParameterID = @ReportParameterID
AND b.intEntityType = @CARRIERLIST
END
ELSE
BEGIN
INSERT INTO #SelectedCarrierCodeList(intIDCarrier, intIDFleesDatabase, strCarrierCode,
booCarrierStatus)
SELECT a.intIDCarrier, a.intIDFleesDatabase, a.strCarrierCode, a.booCarrierStatus
FROM tblFleesCarrier a, @CarrierCodeList c, tblCustomReportEntitySelectionList b
WHERE a.strCarrierCode = c.strCarrierCode
AND c.PrimaryCarrierID = b.intEntityID
AND b.intReportParameterID = @ReportParameterID
AND b.intEntityType = @CARRIERLIST
END
SELECT @SQL = @SQL + ' JOIN #SelectedCarrierCodeList e
ON vwTransactiondataForCustomReports.CarrierID = e.intIDCarrier
AND vwTransactiondataForCustomReports.FleesDatabaseID = e.intIDFleesDatabase '
END
IF @WorkstationListSelected > 0
BEGIN
SELECT @SQL = @SQL + ' JOIN ' + @EntitySelectionTableName + ' f
ON vwTransactiondataForCustomReports.WorkstationID = f.intEntityID
AND f.intReportParameterID = (' + CONVERT(VARCHAR, @ReportParameterID) + ')'
SELECT @SQL = @SQL + ' AND f.intEntityType = (' + @WORKSTATIONLIST + ')'
END
IF @AccountingPeriodListSelected > 0
BEGIN
SELECT @SQL = @SQL + ' JOIN ' + @EntitySelectionTableName + ' g
ON vwTransactiondataForCustomReports.intID = g.intEntityID
AND g.intReportParameterID = (' + CONVERT(VARCHAR, @ReportParameterID) +')'
SELECT @SQL = @SQL + ' AND g.intEntityType = (' + @ACCOUNTINGPERIODLIST + ')'
END
IF @GroupbyFieldList IS NOT NULL
BEGIN
IF @UseCustomReportTables=1
BEGIN
SELECT @intJobFieldCount = COUNT(*)
FROM tblCustomReportsFieldSelectionList a, tblCustomReportsFieldList b
WHERE a.intReportNumber = @ReportNumber
AND a.intFieldID = b.intFieldID
AND a.booGroupbyField = -1
AND b.strViewFieldName IN ('JobName', 'JobCode')
END
ELSE
BEGIN
SELECT @intJobFieldCount = COUNT(*)
FROM tblReportsFieldSelectionList a, tblCustomReportsFieldList b
WHERE a.intReportNumber = @ReportNumber
AND a.intFieldID = b.intFieldID
AND a.booGroupbyField = -1
AND b.strViewFieldName IN ('JobName', 'JobCode')
END
IF @intJobFieldCount > 0
BEGIN
SELECT @SQL = @SQL + ' JOIN tblJob
ON tblJob.intIDJob = vwTransactiondataForCustomReports.JobID'
END
END
SELECT @SQL = @SQL + ' WHERE TrxStartTime BETWEEN ''' + CONVERT(VARCHAR, @StartDate) +
'''' + ' AND ''' + @strEndDate + ''''
SELECT @SQL = @SQL + ' AND ClientID > ' + CONVERT(VARCHAR, @ShowSystemAccounts)
IF @UseCustomReportTables = 1
BEGIN
SELECT @intWhereClauseCount = COUNT(*)
FROM tblCustomReportsFieldSelectionList a, tblCustomReportsFieldList b
WHERE a.intReportNumber = @ReportNumber
AND a.intFieldID = b.intFieldID
AND a.booSearchField = -1
SET @intCounter = 1
IF @intWhereClauseCount > 0
WHILE @intWhereClauseCount > 0
BEGIN
SELECT @WhereClauseFieldList = b.strViewFieldName,
@WhereClause = a.strWhereClause , @UnionOperator = a.strUnionOperator,
@DataType = b.strDataType, @Operand = a.strOperand
FROM tblCustomReportsFieldSelectionList a, tblCustomReportsFieldList b
WHERE a.intReportNumber = @ReportNumber
AND a.intFieldID = b.intFieldID
AND a.booSearchField = -1
AND a.intSearchSequence = @intCounter
IF @DataType = 'datetime'
SELECT @SQL = @SQL + @UnionOperator + ' ' + @WhereClauseFieldList
+ ' ' + @Operand + ' ' + ' ''' + @WhereClause + ''''
ELSE
SELECT @SQL = @SQL + @UnionOperator + ' ' +
REPLACE(REPLACE(@WhereClauseFieldList, 'DescendingRegisterStart',
'tblARDR.DescendingRegisterStart'), 'DescendingRegisterEnd',
'tblARDR.DescendingRegisterEnd') + ' ' + @Operand + ' ' +
@WhereClause
SET @WhereClauseFieldList = ''
SET @WhereClause = ''
SET @intWhereClauseCount = @intWhereClauseCount - 1
set @intCounter = @intCounter + 1
SET @UnionOperator = ''
END
END
ELSE
BEGIN
SELECT @intWhereClauseCount = count(*)
FROM tblReportsFieldSelectionList a, tblCustomReportsFieldList b
WHERE a.intReportNumber = @ReportNumber
AND a.intFieldID = b.intFieldID
AND a.booSearchField = -1
SET @intCounter = 1
IF @intWhereClauseCount > 0
WHILE @intWhereClauseCount > 0
BEGIN
SELECT @WhereClauseFieldList = b.strViewFieldName,
@WhereClause = a.strWhereClause, @UnionOperator = a.strUnionOperator,
@DataType = b.strDataType, @Operand = a.strOperand
FROM tblReportsFieldSelectionList a, tblCustomReportsFieldList b
WHERE a.intReportNumber = @ReportNumber
AND a.intFieldID = b.intFieldID
AND a.booSearchField = -1
AND a.intSearchSequence = @intCounter
IF @DataType = 'datetime'
SELECT @SQL = @SQL + @UnionOperator + ' ' + @WhereClauseFieldList
+ ' ' + @Operand + ' ' + ' ''' + @WhereClause + ''''
ELSE
SELECT @SQL = @SQL + @UnionOperator + ' ' +
REPLACE(REPLACE(@WhereClauseFieldList, 'DescendingRegisterStart',
'tblARDR.DescendingRegisterStart'), 'DescendingRegisterEnd',
'tblARDR.DescendingRegisterEnd') + ' ' + @Operand + ' '
+ @WhereClause
SET @WhereClauseFieldList=''
SET @WhereClause=''
SET @intWhereClauseCount=@intWhereClauseCount -1
set @intCounter = @intCounter + 1
SET @UnionOperator = ''
END
END
IF @ShowDisabledAccounts=0
SELECT @SQL = @SQL + ' AND AccountStatus = 0 and SubAccountStatus=0 and SubSubAccountStatus=0 '
IF @ShowDisabledOperators =0
SET @ShowDisabledOperators =1
ELSE
SET @ShowDisabledOperators =0
SELECT @SQL = @SQL + ' AND IsUserActive >= (' + CONVERT(VARCHAR, @ShowDisabledOperators) +')'
IF @ShowDisabledWorkStations= 0
BEGIN
SET @ShowDisabledWorkStations= -1
END
SELECT @SQL = @SQL + ' AND IsWorkstationActive <= (' + CONVERT(VARCHAR, @ShowDisabledWorkStations) + ')'
SELECT @SQL = @SQL + ' AND IsCarrierActive < (' + CONVERT(VARCHAR, @ShowDisabledCarriers) + ')'
IF ISNULL(@GroupbyFieldList,'') <> '' --IS NOT NULL
BEGIN
SELECT @SQL = @SQL + ' GROUP BY ' + REPLACE(REPLACE( @GroupbyFieldList,
'DescendingRegisterStart', 'tblARDR.DescendingRegisterStart'),
'DescendingRegisterEnd', 'tblARDR.DescendingRegisterEnd' )
END
-- ELSE
-- SELECT @SQL = @SQL + ' GROUP BY ' + ' AccountName' + ' , ' + ' SubAccountName' + ' , '
-- + 'SubSubAccountName'
IF @OrderbyFieldList IS NOT NULL
SELECT @SQL = @SQL + ' ORDER BY ' + REPLACE(REPLACE( @OrderbyFieldList,
'DescendingRegisterStart', 'tblARDR.DescendingRegisterStart'),
'DescendingRegisterEnd', 'tblARDR.DescendingRegisterEnd' )
-- Replacing the field DescendingRegisterStart and DescendingRegisterEnd reference to be
-- calculated from the table #TEMP instead of taken from the vwTransactiondataForCustomReports
-- to show the correct the value within the user defined date range
SELECT @SQL = REPLACE(REPLACE( @SQL, 'MIN(DescendingRegisterStart)',
'MIN(tblARDR.DescendingRegisterStart)'), 'Min(DescendingRegisterEnd)',
'MIN(tblARDR.DescendingRegisterEnd)')
PRINT @SQL
EXEC sp_executesql @SQL
IF @ShowSystemAccounts = 0
BEGIN
IF @AccountListSelected > 0
BEGIN
IF @UseCustomReportTables = 0
BEGIN
DELETE FROM tblReportEntitySelectionList
WHERE intReportParameterID = @ReportParameterID
AND intEntityType = CONVERT(INT, @ACCOUNTLIST)
AND intEntityID IN ( SELECT tblAccountHierarchy.intID
FROM tblAccountHierarchy
WHERE intIDClient = 1 )
END
ELSE
BEGIN
DELETE FROM tblCustomReportEntitySelectionList
WHERE intReportParameterID = @ReportParameterID
AND intEntityType = CONVERT(INT, @ACCOUNTLIST)
AND intEntityID IN (SELECT tblAccountHierarchy.intID
FROM tblAccountHierarchy
WHERE intIDClient = 1 )
END
END
END
END
ELSE
BEGIN
PRINT 'sql ; ' + @SQL
END
IF object_id('tempdb..#TempTransSummary') is not null
drop table #TempTransSummary
IF object_id('tempdb..#TEMPRefill') is not null
drop table #TEMPRefill
IF object_id('tempdb..#TEMPDRE') is not null
drop table #TEMPDRE
IF object_id('tempdb..#TEMPPU') is not null
drop table #TEMPPU
IF object_id('tempdb..#TEMPDRS') is not null
drop table #TEMPDRS
IF object_id('tempdb..#TEMPFA') is not null
drop table #TEMPFA
IF object_id('tempdb..#TEMP') is not null
drop table #TEMP
IF object_id('tempdb..#SelectedCarrierCodeList') is not null
drop table #SelectedCarrierCodeList
Editor is loading...
Leave a Comment