USE [Impresario]
GO
/****** Object:  StoredProcedure [dbo].[so_job_insert_new_program_names]    Script Date: 11/29/2022 12:52:44 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create procedure [dbo].[so_job_insert_new_program_names]
as

/*
Created 3/1/2013 Stuart for correcting Default Salutations
Modified 4/16/13 Stuart - exempt salutation update queries if person has a salutation type of "Devo Contact" 
		and then update everyone who has a Devo Contact type, setting their Default salutation to be the same as their Devo Contact salutation
Modified 5/2/13 Stuart - added Pref Seating campaign clause 
Modified 7/1/13 Stuart  - changed Program type from 1 Annual Individual to 21 Annual Fund in Program Name update
						- and changed Memb Org from 1 Annual Individual to 18 Annual Fund in Program Name update
Modified 7/30/13 Stuart - changed program name inserts from joins to exists
Modified 8/28/13 Stuart - changed program name to allow inserting new program name if they don't already have an Annual Fund program name
11/29/22 Stuart removed Preferred Seating lines and added notes for v16 upgrade

select * from T_MEMB_ORG

*/

/*************************************************************************************************************************************************
	Update Program Names for recent gifts 
************************************************************************************************************************************************/

-- insert new program names for Individuals
insert TX_CUST_PROGRAM (customer_no, program_no, cust_pname, sort_name, created_by, create_dt, last_updated_by, last_update_dt)
select customer_no = c.customer_no,
	program_no = 21,						-- Annual Fund		
	cust_pname = cast(c.fname + ' ' + c.lname as varchar(70)),
	sort_name = cast(c.sort_name as varchar(30)),
	created_by = 'SQLJob', 
	create_dt = GETDATE(), 
	last_updated_by = 'SQLJob', 
	last_update_dt = GETDATE() 
from T_CUSTOMER c
	join TX_CUST_MEMBERSHIP m on c.customer_no = m.customer_no			-- substitute for table T_Membership
			and m.cur_record = 'Y' and m.current_status = 2	-- Active	-- substitute for view VS_Membership_Current lookup
			and m.memb_org_no in (18)						-- Annual Fund		
	left outer join TX_CUST_PROGRAM cp on cp.customer_no = c.customer_no and cp.program_no = 21	-- Annual Fund	
where cp.customer_no is null
	and c.cust_type in (1)		-- Individuals
	and exists (select g.customer_no, MAX(g.cont_dt) as maxcont
				from T_CONTRIBUTION g
					join T_CAMPAIGN cm on g.campaign_no = cm.campaign_no
				where g.cont_amt > 0 
						and g.cont_dt > GETDATE() - 1
				group by g.customer_no)
group by c.customer_no, cast(c.fname + ' ' + c.lname as varchar(70)), cast(c.sort_name as varchar(30))


-- insert new program names for Households
insert TX_CUST_PROGRAM (customer_no, program_no, cust_pname, sort_name, created_by, create_dt, last_updated_by, last_update_dt)
select customer_no = c.customer_no,
	program_no = 21,								-- Annual Fund	
	cust_pname = REPLACE(c.lname,' Household',''),
	sort_name = c.sort_name,
	created_by = 'SQLJob', 
	create_dt = GETDATE(), 
	last_updated_by = 'SQLJob', 
	last_update_dt = GETDATE() 
from T_CUSTOMER c
	join TX_CUST_MEMBERSHIP m on c.customer_no = m.customer_no			-- substitute for table T_Membership
			and m.cur_record = 'Y' and m.current_status = 2	-- Active	-- substitute for view VS_Membership_Current lookup
			and m.memb_org_no in (18)						-- Annual Fund
	left outer join TX_CUST_PROGRAM cp on cp.customer_no = c.customer_no and cp.program_no = 21	-- Annual Fund
where cp.customer_no is null	
	and c.cust_type in (13)		-- Households
	and exists (select g.customer_no, MAX(g.cont_dt) as maxcont
				from T_CONTRIBUTION g
					join T_CAMPAIGN cm on g.campaign_no = cm.campaign_no
				where g.cont_amt > 0 
						and g.cont_dt > GETDATE() - 1
				group by g.customer_no)
group by c.customer_no, c.lname, c.sort_name