Untitled

mail@pastecode.io avatar
unknown
plain_text
a month ago
40 kB
2
Indexable
Never
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








Leave a Comment