SET Ansi_Nulls On
Go

IF EXISTS (Select name From sysobjects Where name = 'back_stats_payment_report' And type = 'P')
	DROP PROCEDURE [dbo].[back_stats_payment_report]
Go

CREATE PROCEDURE back_stats_payment_report
	@site As Char(4),
	@partner As VarChar(25), 
	@zone As  Char(10), 
	@country As Char(2),
	@minDate As DateTime = Null,
	@maxDate As DateTime = Null,
	@payType  As Char(3), 
	@wireTransferMethod As VarChar(80), 
	@cardType As Char(12), 
	@currency As Char(3), 
	@detail As VarChar(20),
	@chargebackDateMatchWith As Char(1)
AS
BEGIN

/* 
	v5.400 - 2779 - SA  - Creation of the payment type "cb4" (Ticketsurf) - 27/05/2009
	v5.400 - 3010 - SA  - Modification - Added calculation for chargebackDateMatchWith.
	v5.000 - 1967 - PBE - Modification - Tuning + corrections
	v5.000 -      - JL  - Creation     - Récupère les statistiques pour la page PaymentReport.
*/

	/* FOR TESTING PURPOSE ONLY -- BEGIN */

	/*	
	DECLARE 
		@site As Char(4),
		@partner As VarChar(25), 
		@zone As  Char(10), 
		@country As Char(2),
		@minDate As DateTime ,
		@maxDate As DateTime ,

		@payType  As Char(3), 
		@wireTransferMethod As VarChar(80), 
		@cardType As Char(12), 
		@currency As Char(3), 
		@detail As VarChar(20),
		@chargebackDateMatchWith AS Char(1)

	DBCC FREEPROCCACHE
	DBCC DROPCLEANBUFFERS

	SELECT 
		  @minDate  = '20090401 00:00:00' 
		, @maxDate  = '20090401 23:59:59' 
		, @cardType =''
		, @currency ='eur'
		, @country  ='FR'
		, @site     ='frfr'
		, @chargebackDateMatchWith = '1'

		exec back_stats_payment_report @site=@site,@partner=@partner,@zone=@zone,@country=@country,@minDate=@minDate,@maxDate=@maxDate,@payType=@payType,@wireTransferMethod=@wireTransferMethod,@cardType=@cardType,@currency=@currency,@detail=@detail,@chargebackDateMatchWith=@chargebackDateMatchWith

	*/
	/* FOR TESTING PURPOSE ONLY -- END */


	DECLARE 
		  @mainFilters       NVARCHAR(2000)
		, @innerJoins        NVARCHAR(2000)
		, @innerJoinUsers    NVARCHAR(255)
		, @query             NVARCHAR(4000)
		, @RC                CHAR(1)
		, @montantField      VARCHAR(14)
		, @fraisField        VARCHAR(20)  
		, @detailColumn      NVARCHAR(100)
		, @withDetailCols    NVARCHAR(100)
		, @queryOrderByExpr  NVARCHAR(100)
		, @queryGroupByExpr  NVARCHAR(100)
		, @detailResultCols  NVARCHAR(100)

	SELECT 
		  @site                = NULLIF(@site,'')
		, @partner             = NULLIF(@partner,'')
		, @zone                = NULLIF(@zone,'')
		, @country             = NULLIF(@country,'')
		, @payType             = NULLIF(@payType,'')
		, @wireTransferMethod  = NULLIF(@wireTransferMethod,'')
		, @cardType            = NULLIF(@cardType,'')
		, @currency            = NULLIF(@currency,'')

	SELECT 
		  @RC               = CHAR(10)
		, @montantField     = CASE WHEN NULLIF(@currency,'eur') IS NOT NULL THEN 'A.montant' ELSE 'A.montant_eur' END
		, @fraisField       = CASE WHEN NULLIF(@currency,'eur') IS NOT NULL THEN 'A.frais'   ELSE 'A.frais_eur' END
		, @mainFilters      = ''
		, @detailResultCols = ''
		, @withDetailCols   = ''
		, @queryGroupByExpr = ''
		, @queryOrderByExpr = ''
		, @innerJoins       = ''
		, @innerJoinUsers   = ''


	/*
	** Filtres sur la table stats_mvt
	*/
	IF (@site               IS NOT NULL) SET @mainFilters = @mainFilters + @RC + '	AND A.site_code = @site '
	IF (@country            IS NOT NULL) SET @mainFilters = @mainFilters + @RC + '	AND A.c_pays_code = @country '
	IF (@payType            IS NOT NULL) SET @mainFilters = @mainFilters + @RC + '	AND A.ref_typpay_code = @payType '
	IF (@wireTransferMethod IS NOT NULL) SET @mainFilters = @mainFilters + @RC + '	AND A.complement LIKE @wireTransferMethod + ''%'' '

	IF COALESCE(@minDate,@maxDate) IS NOT NULL
	BEGIN
		IF @minDate IS NOT NULL AND @maxDate IS NOT NULL
			SET @mainFilters = @mainFilters + @RC +'	AND A.datmvt BETWEEN @minDate AND @maxDate '
		ELSE
			IF @minDate IS NOT NULL
				SET @mainFilters = @mainFilters + @RC +'	AND A.datmvt >= @minDate '
			ELSE
				SET @mainFilters = @mainFilters + @RC +'	AND A.datmvt <= @maxDate ' 
		END

	/*
	** Jointure necessaire sur la table users si :
	** - Un ou plusieurs filtres sont definis sur currency, partner et/ou zone 
	** - Le regroupement est Partner ou Zone
	*/
	IF (	(COALESCE(@currency, @partner, @zone) IS NOT NULL )
		OR	( @detail IN ( 'Partner','Zone' ) )
		)
	BEGIN
		SET @innerJoinUsers  = @RC + '		INNER JOIN users u (NOLOCK) on u.id = A.user_id '	
		
		IF (@currency  IS NOT NULL) SET @mainFilters = @mainFilters + @RC + '	AND u.currency_code = @currency '
		IF (@partner   IS NOT NULL) SET @mainFilters = @mainFilters + @RC + '	AND u.partner_code = @partner '
		IF (@zone      IS NOT NULL) SET @mainFilters = @mainFilters + @RC + '	AND u.zone_code = @zone '

	END

	/*
	** Definition des colonnes de regroupement au niveau de la clause WITH
	*/
	SET @withDetailCols = CASE @detail
									WHEN 'Site'     THEN 'A.Site_code'
									WHEN 'Country'  THEN 'A.c_pays_code'
									WHEN 'Month'    THEN 'Convert(Char(7), A.datmvt, 111)'			
									WHEN 'Week'     THEN 'dbo.GetISOWeekAndYearNumber(A.datmvt)'
									WHEN 'Day'      THEN 'Convert(Char(10), A.datmvt, 111)'
									WHEN 'Partner'  THEN 'u.Partner_code'
									WHEN 'Zone'     THEN 'u.Partner_code, u.Zone_code'
									WHEN 'Cardtype' THEN 'PC.type'
									WHEN 'Paytype'  THEN 'PT.[Payment Type]'
													ELSE ''
								END

	/*
	** Definition des colonnes de regroupement au niveau de la requete principale
	*/
	SET @detailResultCols = CASE @detail
									WHEN 'Site'     THEN @detail 
									WHEN 'Country'  THEN @detail 
									WHEN 'Month'    THEN @detail 
									WHEN 'Week'     THEN @detail 
									WHEN 'Day'      THEN @detail 
									WHEN 'Partner'  THEN @detail 
									WHEN 'Zone'     THEN 'Partner, Zone'
									WHEN 'Cardtype' THEN '[Card Type]'
									WHEN 'Paytype'  THEN '[Payment Type]'
													ELSE ''
								END
								
	/*
	** Si un regroupement est defini, generation des clauses GROUP BY et ORDER BY pour la requete principale
	*/
	IF @detailResultCols <> ''
	BEGIN
		SET @queryGroupByExpr = @RC + 'GROUP BY '+ @detailResultCols
		SET @queryOrderByExpr = @RC + 'ORDER BY '+ @detailResultCols

		SET @detailResultCols = @detailResultCols + ','
		SET @withDetailCols   = @withDetailCols   + ','
	END

	/*
	** Si un regroupement par Month, Week ou Day est defini, le tri se fait dans l'ordre inverse
	*/
	IF  @detail = 'Month' Or @detail = 'Week' Or @detail = 'Day' 
		SET @queryOrderByExpr = @queryOrderByExpr + ' DESC'

	/*
	** Une requete specifique est generee si :
	** - Un filtre est defini sur cardtype
	** - Le regroupement est Cardtype ou Paytype
	**
	** EXPLICATION : 
	** Les champs ref_typpay_code (regroupement ou filtre sur Paytype) et cardtype (regroupement ou filtre sur Cardtype ) 
	** ne sont remplis que pour des mouvements de type 'Deposit', 'Chargeback','Withdrawal')
	** 
	*/
	IF ( (COALESCE(@cardType,@payType) IS NOT NULL) OR (@detail IN ( 'Cardtype','Paytype' )) ) 
	BEGIN

		IF ( @detail = 'Paytype' ) 
			SELECT 
				  @innerJoins  = @innerJoins + @RC + '		INNER JOIN (
			SELECT ref_typpay_code
				, CASE ref_typpay_code 
						WHEN ''cb''  THEN ''Credit card by Metacharge''
						WHEN ''cb2'' THEN ''Credit card by Datacash'' 
						WHEN ''cb3'' THEN ''Credit card by EiOle''
						WHEN ''cb4'' THEN ''Credit card by Ticketsurf''
							   ELSE libelle 
					END AS [Payment Type]
			FROM ref_typpay_trad (NOLOCK) 
			WHERE langue_code = ''en''
		) PT ON ( A.ref_typpay_code = PT.ref_typpay_code )' 
				, @mainFilters = @mainFilters + @RC + '	AND ( A.ref_typpay_code NOT IN (''gra'', ''NULL'') )'

		IF ( (@cardType <> '') OR (@detail = 'Cardtype' ) )
			SET @innerJoins  = @innerJoins + @RC + '		INNER JOIN payment_cards PC (NOLOCK) On PC.id = A.card_id'

		IF (@cardType <> '')	
			SET @mainFilters = @mainFilters + @RC +'	  AND PC.type = @cardType '

		SET @query = '
;WITH tmp ('+ @detailResultCols +'ref_mvt_code, nb,montant,  frais)
AS
(
	SELECT '+ @withDetailCols +'ref_mvt_code, count(*) as nb, SUM(' + @montantField + ') as montant, SUM(' + @fraisField + ') as frais
	FROM stats_mvt A (NOLOCK)'
		+ @innerJoins + @innerJoinUsers + '
	WHERE ref_mvt_code IN (''Deposit'', ''Withdrawal'') '
		+ @mainFilters + '
	GROUP BY '+ @withDetailCols + 'ref_mvt_code
	UNION'

	
	/* 
	**  Ajout d un parametre 0/1 definissant si le colonne chargeback est calculee en fonction des dates de depot
	**	ou de chargeback.

	**	I have added a new parameter @chargebackDateMatchWith that can be either true or false.
	**  This parameter determines whether the Chargeback column is calculated from the date of user deposit
	**  or the date of Chargeback transaction.
	**  The REPLACE() functionality is used as I need to determine whether there is a date or date range
	**  selected in the query when there is a chargeback related to the user deposit.
	**  As I have modified the query, the date range builder (in @mainFilters) will be using the wrong alias,
	**  and therefore the REPLACE is used to swap the alias to the correct table being joined (i.e. Deposit and
	**  not Chargeback).
	*/

	IF (@chargebackDateMatchWith = '1')
			
		SET @query = @query + @RC + 
		'SELECT '+ @withDetailCols +'ref_mvt_code, count(*) as nb, SUM(' + @montantField + ') as montant, SUM(' + @fraisField + ') as frais
		FROM stats_mvt A (NOLOCK)'
			+ @innerJoins + @innerJoinUsers + '
		WHERE ref_mvt_code IN (''Chargeback'') '
			+ @mainFilters + '
		GROUP BY '+ @withDetailCols + 'ref_mvt_code
		UNION'

	ELSE

		SET @query = @query + @RC + 
		'SELECT '+ @withDetailCols +'A.ref_mvt_code, count(*) as nb, SUM(' + @montantField + ') as montant, SUM(' + @fraisField + ') as frais
		FROM stats_mvt A (NOLOCK)'
			+ @innerJoins + @innerJoinUsers + '
		INNER JOIN stats_mvt B (NOLOCK) ON A.related_mvt_id = B.mvt_id
		WHERE A.ref_mvt_code IN (''Chargeback'') AND B.ref_mvt_code IN (''Deposit'') '
			+ REPLACE(REPLACE(REPLACE(@mainFilters, 'A.datmvt BETWEEN', 'B.datmvt BETWEEN'),'A.datmvt >=','B.datmvt >='),'A.datmvt <=','B.datmvt <=') + '
		GROUP BY '+ @withDetailCols + 'A.ref_mvt_code
		UNION'

	SET @query = @query + @RC + '
	SELECT '+ @withDetailCols +'''Newdeposit'', count(*), SUM(' + @montantField + ') , 0 
	FROM stats_mvt A (NOLOCK)
		INNER JOIN users u (NOLOCK) on u.id = A.user_id'
		+ @innerJoins + '
	WHERE ref_mvt_code = ''Deposit'' 
	  AND datmvt between DateAdd(second, -1, u.dat1erdepot) And DateAdd(second, 1, u.dat1erdepot) ' 
		+ @mainFilters + '
	GROUP BY '+ @withDetailCols + 'ref_mvt_code
)
SELECT ' + @detailResultCols + '
	  SUM(CASE ref_mvt_code WHEN ''Newdeposit'' THEN montant ELSE 0 END) as "New deposit sum"
	, SUM(CASE ref_mvt_code WHEN ''Newdeposit'' THEN nb      ELSE 0 END) as "New deposit count"
	, SUM(CASE ref_mvt_code WHEN ''Deposit''    THEN montant ELSE 0 END) as "Deposit sum"
	, SUM(CASE ref_mvt_code WHEN ''Deposit''    THEN nb      ELSE 0 END) as "Deposit count"
	, SUM(CASE ref_mvt_code WHEN ''Withdrawal'' THEN montant ELSE 0 END) as "Withdrawal sum"
	, SUM(CASE ref_mvt_code WHEN ''Withdrawal'' THEN nb      ELSE 0 END) as "Withdrawal count"
	, SUM(CASE ref_mvt_code WHEN ''Chargeback'' THEN montant ELSE 0 END) as "Chargeback sum"
	, SUM(CASE ref_mvt_code WHEN ''Chargeback'' THEN nb      ELSE 0 END) as "Chargeback count"
	, SUM(CASE ref_mvt_code WHEN ''Deposit''    THEN frais   ELSE 0 END) AS "Deposit expenses sum"
	, SUM(CASE ref_mvt_code WHEN ''Withdrawal'' THEN frais   ELSE 0 END) AS "Withdrawal expenses sum"
FROM tmp'
+ @queryGroupByExpr
+ @queryOrderByExpr
	END
	ELSE
	BEGIN
		SET @query = '
;WITH tmp ('+ @detailResultCols +'ref_mvt_code, nb,montant, frais)
AS
(
	SELECT '+ @withDetailCols +'ref_mvt_code, count(*) as nb, SUM(' + @montantField + ') as montant, SUM(' + @fraisField + ') as frais
	FROM stats_mvt A (NOLOCK)'
		+  @innerJoinUsers + '
	WHERE ref_mvt_code IN (''Deposit'', ''Withdrawal'') ' 
		+ @mainFilters + '
	GROUP BY '+ @withDetailCols + 'ref_mvt_code
	UNION'

	/*
	**  Ajout d un parametre 0/1 definissant si le colonne chargeback est calculee en fonction des dates de depot
	**	ou de chargeback.

	**	I have added a new parameter @chargebackDateMatchWith that can be either true or false.
	**  This parameter determines whether the Chargeback column is calculated from the date of user deposit
	**  or the date of Chargeback transaction.
	**  The REPLACE() functionality is used as I need to determine whether there is a date or date range
	**  selected in the query when there is a chargeback related to the user deposit.
	**  As I have modified the query, the date range builder (in @mainFilters) will be using the wrong alias,
	**  and therefore the REPLACE is used to swap the alias to the correct table being joined (i.e. Deposit and
	**  not Chargeback).
	*/

	IF (@chargebackDateMatchWith = '1')
			
		SET @query = @query + @RC + 
		'SELECT '+ @withDetailCols +'ref_mvt_code, count(*) as nb, SUM(' + @montantField + ') as montant, SUM(' + @fraisField + ') as frais
		FROM stats_mvt A (NOLOCK)'
			+ @innerJoins + @innerJoinUsers + '
		WHERE ref_mvt_code IN (''Chargeback'') '
			+ @mainFilters + '
		GROUP BY '+ @withDetailCols + 'ref_mvt_code
		UNION'

	ELSE

		SET @query = @query + @RC + 
		'SELECT '+ @withDetailCols +'A.ref_mvt_code, count(*) as nb, SUM(' + @montantField + ') as montant, SUM(' + @fraisField + ') as frais
		FROM stats_mvt A (NOLOCK)'
			+ @innerJoins + @innerJoinUsers + '
		INNER JOIN stats_mvt B (NOLOCK) ON A.related_mvt_id = B.mvt_id
		WHERE A.ref_mvt_code IN (''Chargeback'') AND B.ref_mvt_code IN (''Deposit'') '
			+ REPLACE(REPLACE(REPLACE(@mainFilters, 'A.datmvt BETWEEN', 'B.datmvt BETWEEN'),'A.datmvt >=','B.datmvt >='),'A.datmvt <=','B.datmvt <=') + '
		GROUP BY '+ @withDetailCols + 'A.ref_mvt_code
		UNION'

	SET @query = @query + @RC + '
	SELECT '+ @withDetailCols +'ref_mvt_code, count(*), SUM(' + @montantField + '),0
	FROM stats_mvt A (NOLOCK)'
		+  @innerJoinUsers + '
	WHERE ref_mvt_code IN ( ''Bonus'', ''Offer'',''Filleul'', ''Parrain'',''Cashback'', ''Freebet'',''Bet'',''Livebet'',''Cancel'',''Win'',''Backbet'',''Backlivbet'') '
		+ @mainFilters + '
	GROUP BY '+ @withDetailCols + 'ref_mvt_code
	UNION
	SELECT '+ @withDetailCols +'''Newdeposit'', count(*), SUM(' + @montantField + '),0  
	FROM stats_mvt A (NOLOCK)
		INNER JOIN users u (NOLOCK) on u.id = A.user_id
	WHERE ref_mvt_code = ''Deposit'' 
	  AND datmvt between DateAdd(second, -1, u.dat1erdepot) And DateAdd(second, 1, u.dat1erdepot) ' 
		+ @mainFilters + '
	GROUP BY '+ @withDetailCols + 'ref_mvt_code
)
SELECT ' + @detailResultCols + '
	  SUM(CASE ref_mvt_code WHEN ''Newdeposit'' THEN montant ELSE 0 END) as "New deposit sum"
	, SUM(CASE ref_mvt_code WHEN ''Newdeposit'' THEN nb ELSE 0 END) as "New deposit count"
	, SUM(CASE ref_mvt_code WHEN ''Deposit'' THEN montant ELSE 0 END) as "Deposit sum"
	, SUM(CASE ref_mvt_code WHEN ''Deposit'' THEN nb ELSE 0 END) as "Deposit count"
	, SUM(CASE WHEN ref_mvt_code IN (''Bonus'', ''Offer'',''Filleul'', ''Parrain'',''Cashback'', ''Freebet'') THEN montant ELSE 0 END) as "Bonus sum"
	, SUM(CASE WHEN ref_mvt_code IN (''Bonus'', ''Offer'',''Filleul'', ''Parrain'',''Cashback'', ''Freebet'') THEN nb ELSE 0 END) as "Bonus count"
	, SUM(CASE ref_mvt_code WHEN ''Withdrawal'' THEN montant ELSE 0 END) as "Withdrawal sum"
	, SUM(CASE ref_mvt_code WHEN ''Withdrawal'' THEN nb ELSE 0 END) as "Withdrawal count"
	, SUM(CASE ref_mvt_code WHEN ''Chargeback'' THEN montant ELSE 0 END) as "Chargeback sum"
	, SUM(CASE ref_mvt_code WHEN ''Chargeback'' THEN nb ELSE 0 END) as "Chargeback count"
	, SUM(CASE ref_mvt_code WHEN ''Bet''     THEN montant
							WHEN ''Livebet'' THEN montant 
							WHEN ''Cancel''  THEN -montant 
							               ELSE 0 
			END) as "Total bets sum"
	, SUM(CASE ref_mvt_code WHEN ''Win''        THEN montant
							WHEN ''Backbet''    THEN -montant 
							WHEN ''Backlivbet'' THEN -montant 
							                  ELSE 0 
			END) as "Total win sum"
	, SUM(CASE ref_mvt_code WHEN ''Bet''     THEN montant
							WHEN ''Livebet'' THEN montant 
							WHEN ''Cancel''  THEN -montant 
							               ELSE 0 
			END)
		- SUM(CASE ref_mvt_code WHEN ''Win''        THEN montant
							WHEN ''Backbet''    THEN -montant 
							WHEN ''Backlivbet'' THEN -montant 
							                  ELSE 0 
			END) as "Gross profit"
	, SUM(CASE ref_mvt_code WHEN ''Deposit'' THEN frais ELSE 0 END) AS "Deposit expenses sum"
	, SUM(CASE ref_mvt_code WHEN ''Withdrawal'' THEN frais ELSE 0 END) AS "Withdrawal expenses sum"
FROM tmp'
+ @queryGroupByExpr
+ @queryOrderByExpr
	END


	--PRINT @query

	Exec sp_executesql @query, 
		N'
		@site As Char(4),
		@partner As VarChar(25), 
		@zone As  Char(10), 
		@country As Char(2),
		@minDate As DateTime = Null,
		@maxDate As DateTime = Null,
		@payType  As Char(3), 
		@wireTransferMethod As VarChar(80), 
		@cardType As Char(12), 
		@currency As Char(3)',
		@site = @site,
		@zone = @zone,
		@partner = @partner,
		@country = @country,
		@minDate = @minDate, 
		@maxDate = @maxDate,
		@payType = @payType,
		@wireTransferMethod = @wireTransferMethod,
		@cardType = @cardType, 
		@currency = @currency

END
GO

GRANT Execute On back_stats_payment_report To BackOffice
GO