Cascading Rankings from Households to Individuals

USE [impresario]
GO
/****** Object:  StoredProcedure [dbo].[LP_PRESALE_RANK]    Script Date: 1/11/2018 12:11:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[LP_PRESALE_RANK]

AS
Set NoCount On

/***************************************************************************************************
This procedure sets a ranking for web access to SFUSA presale. It looks at Membership, SCENE
membership and web contribution CSI's to assign the correct ranking for web access to ticketing.
During presale period, this should be scheduled to run every 15 min or 30 min to capture new 
online giving and get them access if they have given the proper amount to qualify for an active
tier of the presale.
***************************************************************************************************/

IF OBJECT_ID('tempDB..##presale_base','U')IS NOT NULL
	DROP TABLE ##presale_base;

IF OBJECT_ID('tempDB..##ranking_base','U')IS NOT NULL
	DROP TABLE ##ranking_base;
	
IF OBJECT_ID('tempDB..##ranking_1','U')IS NOT NULL
	DROP TABLE ##ranking_1;
	
IF OBJECT_ID('tempDB..##ranking_2','U')IS NOT NULL
	DROP TABLE ##ranking_2;
	
IF OBJECT_ID('tempDB..##ranking_3','U')IS NOT NULL
	DROP TABLE ##ranking_3;
	
IF OBJECT_ID('tempDB..##ranking_4','U')IS NOT NULL
	DROP TABLE ##ranking_4;
	
IF OBJECT_ID('tempDB..##ranking_5','U')IS NOT NULL
	DROP TABLE ##ranking_5;
	
IF OBJECT_ID('tempDB..##final_ranking','U')IS NOT NULL
	DROP TABLE ##final_ranking;
	

--Select the full group of constituents: membership, SCENE, web no membership yet.

;WITH	SELECTION (customer_no) AS
		(
			SELECT	customer_no
				FROM TX_CUST_MEMBERSHIP
				WHERE memb_org_no IN(1,2)
				AND current_status IN (2,3,9)	
				AND init_dt BETWEEN '2017-02-01 00:00:00.000' AND '2018-01-31 23:59:59.997'
				--By business rule per JF 10/5/2015
				--late January gifts get pre-sale even if they received the presale benefit last year.
				--Is date range because of pending memberships beyond the presale year range.
				--This also looks for active SCENE memberships made for the current FY.
				AND memb_level NOT IN ('NoM','SNM')
			UNION
			SELECT customer_no
				FROM TX_CUST_MEMBERSHIP
				WHERE memb_org_no = 2
				AND init_dt BETWEEN '2016-10-01 00:00:00.000' AND '2018-01-31 23:59:59.997' --last year's SCENE init_dt. We want to include them
				--even though they are not active.
				AND memb_level NOT IN ('SNM')
			UNION
			SELECT	customer_no
				FROM T_CUST_ACTIVITY
				WHERE activity_type IN (8,9,10,11,12,13,14,15,17,18,19,20,25,41,86)
				AND issue_dt >= '2017-09-01 00:00:00.000'
			UNION
			SELECT customer_no
				FROM T_CUST_RANK
				WHERE rank_type = 3
				AND rank = 6 --manual added 'grant access' ranks that we do not want to delete or re-insert.
			UNION
			SELECT customer_no
				FROM T_CUST_RANK
				WHERE rank_type = 3
				AND rank = 10 --manual added to void presale. (e.g. creditor on gifted membership.)
			UNION
			SELECT customer_no
				FROM T_CUST_RANK
				WHERE rank_type = 3
				AND rank = 8 --manual added to grant presale to Gaillard donors (seats/boxes/loges)
			UNION
			SELECT	customer_no
				FROM T_PAYMENT
				WHERE pmt_method = 12
				AND pmt_dt >= '2018-01-01 00:00:00.000'
				AND pmt_amt <= -100

		),
	
--Add membership information for evaluation

		MEMBERSHIP (	
					cust_memb_no,
					customer_no,
					memb_org_no,
					campaign,
					memb_level,
					init_dt,
					expr_dt,
					current_status,
					m_create_dt,
					m_last_update_dt
					) AS
		(					
			SELECT	a.cust_memb_no,
					a.customer_no,
					a.memb_org_no,
					c.description as campaign,
					a.memb_level,
					a.init_dt,
					a.expr_dt,
					a.current_status,
					a.create_dt as m_create_dt,
					a.last_update_dt as m_last_update_dt
				FROM TX_CUST_MEMBERSHIP a
			LEFT JOIN T_CAMPAIGN c on a.campaign_no = c.campaign_no
				WHERE a.memb_org_no = 1
				AND a.current_status IN (2,3,9)
				AND init_dt BETWEEN '2017-02-01 00:00:00.000' AND '2018-01-31 23:59:59.997'
				AND a.memb_level NOT IN ('NoM', 'SNM')

		),
		
--Add SCENE membership information for evaluation
	
		SCENE		(	
					customer_no,
					scene_eligible
					) AS
		(					
			SELECT	customer_no,
					'Y' as scene_eligible
				FROM TX_CUST_MEMBERSHIP 
				WHERE memb_org_no = 2
				AND init_dt >= '2016-10-01 00:00:00.000'
				AND memb_level NOT IN ('SNM')
		),
	
	
--Add recent web giving for evaluation if no membership exists yet (instant access after giving).
	
		CSI_CONTRIB (customer_no, activity_type, issue_dt) AS
		(
			SELECT	customer_no,
					activity_type,
					issue_dt
				FROM T_CUST_ACTIVITY
				WHERE activity_type IN (8,9,10,11,12,13,14,15,16,17,18,19,20,25,41,86)
				AND issue_dt >= '2017-09-01 00:00:00.000' --Whatever date you need automation to start.
		),
		
--Existing grant access ranks (rank IN 6,8,10) that we do not want to delete or re-insert

		GRANT_ACCESS_RANKS (customer_no, rank) AS
		(
			SELECT	customer_no,
					rank
				FROM T_CUST_RANK
				WHERE rank_type = 3
				AND rank IN (6,8,10)
		),
	
--Add recent BO On Account contributions for temporary evaluation.
--This will need to look at only negative amounts in the final evaluation below.

		BO_ONACCOUNT (customer_no, bo_amt) AS
		(
			SELECT	customer_no,
					pmt_amt as bo_amt
				FROM T_PAYMENT
				WHERE pmt_method = 12
				AND pmt_amt <= -100
				AND pmt_dt >= '2017-09-01 00:00:00.000'
		)

	
		
SELECT	a.customer_no,
		b.memb_level as m_level,
		b.current_status as m_status,
		b.init_dt as m_init_dt,
		b.m_create_dt,
		b.m_last_update_dt,
		c.scene_eligible,
		d.activity_type,
		d.issue_dt,
		e.rank,
		f.bo_amt
INTO ##presale_base
	FROM SELECTION a
LEFT JOIN MEMBERSHIP b on a.customer_no = b.customer_no
LEFT JOIN SCENE c on a.customer_no = c.customer_no
LEFT JOIN CSI_CONTRIB d on a.customer_no = d.customer_no
LEFT JOIN GRANT_ACCESS_RANKS e on a.customer_no = e.customer_no
LEFT JOIN BO_ONACCOUNT f on a.customer_no = f.customer_no

--Find regular memberships that are pending during the valid presale year dates and select
--the later pending init_dt instead of a current active membership. The membership level might be
--different in the pending mb. This is a handful of mostly board members with multi-year pledges
--They would be missed in the initital dataset if we did not include pending.

;WITH	GET_PENDING_MB (seq, customer_no, m_level, m_status, m_init_dt, m_create_dt, m_last_update_dt,
								scene_eligible,activity_type, issue_dt, rank, bo_amt) AS				
		(
			SELECT ROW_NUMBER() OVER(PARTITION BY [customer_no] ORDER BY m_init_dt DESC) AS 
					seq,
					customer_no,
					m_level,
					m_status,
					m_init_dt,
					m_create_dt,
					m_last_update_dt,
					scene_eligible,
					activity_type,
					issue_dt,
					rank,
					bo_amt
				FROM ##presale_base
		)
			
SELECT *
INTO ##ranking_base
	FROM GET_PENDING_MB
	WHERE seq = 1;
	
--SELECT * FROM ##ranking_base


--First set of rankings (Permanent access manual (6,8,10))--
SELECT	a.customer_no,
		(CASE	WHEN a.rank = 6 THEN 6 
				WHEN a.rank = 8 THEN 8
			 	WHEN a.rank = 10 THEN 10	
					ELSE NULL END)as set_rank
INTO ##ranking_1
	FROM ##ranking_base a;


--Second level of rankings (upgrades to existing (5))
SELECT	a.customer_no,
		(
			CASE
				WHEN(
						b.m_level IN ('FRD','SUP','ASC','PAT','PRD','SUS','BNF','GTR','AMB','LDR','FND')
						AND b.activity_type IN (8,9,10,11,12,13,14,15,17,18,19,20,25,41,86)
						AND b.issue_dt > b.m_last_update_dt
					)	THEN 5 --instant access rank upgrades
				ELSE NULL END
		)as set_rank
INTO ##ranking_2
	FROM ##ranking_1 a
JOIN ##ranking_base b on a.customer_no = b.customer_no
	WHERE a.set_rank IS NULL


--Third level of ranking (memberships(4,3,2,1)--	
SELECT	a.customer_no,
		(
			CASE 
				WHEN b.m_level IN ('PAT','PRD','SUS','BNF','GTR','AMB','LDR','FND') THEN 4
				WHEN b.m_level IN ('ASC') THEN 3
				WHEN b.m_level IN ('SUP') THEN 2
				WHEN b.m_level IN ('FRD') THEN 1
				WHEN b.scene_eligible = 'Y' AND b.m_level IS NULL THEN 1
			ELSE NULL END
		) as set_rank
INTO ##ranking_3
	FROM ##ranking_2 a
JOIN ##ranking_base b on a.customer_no = b.customer_no
	WHERE a.set_rank IS NULL;

--Forth level of ranking (OL new giving(4,3,2,1))
SELECT	a.customer_no,
		(
			CASE
				WHEN b.activity_type IN (11,12,13,14,15) AND b.m_level IS NULL THEN 4
				WHEN b.activity_type IN (10) THEN 3
				WHEN b.activity_type IN (9) AND b.m_level IS NULL THEN 2
				WHEN b.activity_type IN (8) AND b.m_level IS NULL THEN 1
				WHEN b.activity_type IN (17,18) AND b.scene_eligible IS NULL THEN 1
			ELSE NULL END
		)as set_rank
INTO ##ranking_4
	FROM ##ranking_3 a
JOIN ##ranking_base b on a.customer_no = b.customer_no
	WHERE a.set_rank IS NULL;
	


--Fifth level of rankings for BO On Account payment types that have no other rank 
--until the data entry can be completed and these constituents sorted into proper ranking.
--and in case the box office does not apply a 5 ranking to these records when taking contribution.
	
SELECT	a.customer_no,
		(
			CASE	
				WHEN b.bo_amt IS NOT NULL THEN 5
			ELSE NULL END
		) as set_rank
INTO ##ranking_5
	FROM ##ranking_4 a
JOIN ##ranking_base b on a.customer_no = b.customer_no
	WHERE a.set_rank IS NULL;


SELECT *
INTO ##final_ranking
	FROM ##ranking_1
	WHERE set_rank IS NOT NULL
UNION
SELECT *
	FROM ##ranking_2
	WHERE set_rank IS NOT NULL
UNION
SELECT *
	FROM ##ranking_3
	WHERE set_rank IS NOT NULL
UNION
SELECT *
	FROM ##ranking_4
	WHERE set_rank IS NOT NULL
UNION
SELECT *
	FROM ##ranking_5
	WHERE set_rank IS NOT NULL;

--SELECT * FROM ##final_ranking

DELETE T_CUST_RANK
	WHERE rank_type = 3
--The accounts below are TEST accounts for FY2016 presale. Don't want these to change for the moment.
	AND customer_no NOT IN (145683,145678,145684,145680,145686,145681,145682,145725,145728,145729,145679,145685,
	122588,130841,145601,40273,52510,148272)
	
INSERT	T_CUST_RANK (customer_no,rank_type, rank)
SELECT customer_no, 3, set_rank
		FROM ##final_ranking
--The accounts below are TEST accounts for FY2016 presale. Don't want these to change for the moment.
		WHERE customer_no NOT IN (145683,145678,145684,145680,145686,145681,145682,145725,145728,145729,145679,145685,
		122588,130841,145601,40273,52510,148272);
		
		
		
--Delete the entries created in the audit trail when a ranking changes
--If not we'll end up with millions of usless rows as this procedure
--keeps presale information updated
--At some point this could be modified to keep only the last update in audit
--history, there is no time for that now.

DELETE FROM TA_AUDIT_TRAIL
	WHERE table_name = 'T_CUST_RANK'
	AND cg_key = 3;