USE [TmsEPrd]	
GO

-- ****************************************************************************
-- Drop existing stored procedure if present
-- ****************************************************************************
IF EXISTS (SELECT name FROM sysobjects
            WHERE name = 'UIUSPSA007' AND type = 'P')
    DROP PROCEDURE UIUSPSA007
GO

/****** Object:  StoredProcedure [dbo].[UIUSPSA007]    Script Date: 3/20/2015 9:39:20 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/**********************************************************************************************
Here's what this Stored Procedure does:
1- Checks (Series of checks to make sure the data is ready to be processed)
1a- Make sure there's an endline to @tags (so we don't miss the last ID number)
1b- Make sure meeting is tagged as "met" in system (Meetings can only have attendance if they have "Met")
1c- Inserts everybody that already exists in ACT_PART to MTG_ATTEND_HST for this particular meeting as absent

2- Loop through each submission (a big while loop that runs until @tags is empty)
2a- find the 1st ID number in TAGs, saves it to @left, and deletes it from the @tags variable.
2b- insert ID_num into ACT_PART if not already in there (IN order for you to be in a meeting, you have to be an already existing participant)
2c- Insert the attendance to MTG_ATTEND_HST(or updates in case of repeated submissions or user was already in ACT_PART in step 1C)

3- Recalcs (EX does lots of recalcs/summary counts after recording attendance, we are just mimicking what they do)
3a- recalc mtg_hist
3b- recalc sess_act_mast
3c- recalc Stud_Attend_Sum
3d- recalc Sess_act_Att_sum

Hope it makes sense,

Henrique Donati

***********************************************************************************************/
CREATE PROCEDURE [dbo].[UIUSPSA007]


@tags varchar(max), -- We get a list of ID_numbers with a new line in between them (New Line = Char(13))
@Session_Code varchar(16), -- question from form flow/ parameter for insert
@Activity_Code varchar(16), -- question from form flow/ parameter for insert
@Meeting_Time datetime, -- question from form flow/ parameter for insert
@left varchar(max),  -- Variable used to temporarily store each ID_num from @Tags
@nextSpace int -- used to know where to cut the string since form flows changes char(13)s into spaces since version 9.1.1 of JICS
AS
SET NOCOUNT ON
Begin
	-- ****************************************************************************
	--		Changes necessary to the code work without breaking
	-- ****************************************************************************
	/*if the string doesn't end in a new line, the program will run forever, 
	so this adds a new line char at the end of the string if there isn't already one*/
	if (substring(@tags, len(@tags), 1) != char(13))
	begin
		set @tags += char(13);
	end

	/* If the meeting hasn't happened yet, make it as it happened! (We can add a If statement here if deemed necessary) */
	if (not exists (select MTG_STS from mtg_hist 
			where 
				SESS_CDE = @Session_Code 
				and ACT_CDE = @Activity_Code
				and MTG_START_DTE = @Meeting_Time
				and MTG_STS = 'M'))
	begin
			update mtg_hist 
			set mtg_hist.MTG_STS = 'M' -- Met
				,USER_NAME = 'TE_ADMIN'
				,JOB_NAME = 'UIUSPSA007'
				,JOB_TIME = GETDATE()
			where mtg_hist.SESS_CDE = @Session_Code 
				and mtg_hist.ACT_CDE = @Activity_Code
				and mtg_hist.MTG_START_DTE = @Meeting_Time
	end
	/* Copy Everybody from the ACT PART that is not already in MTG_ATTEND_HST as Absent for the recalc to work  */
	if (exists(select act.act_cde
					from ACT_PART as act
						left join MTG_ATTEND_HST as mtg on act.SESS_CDE = mtg.SESS_CDE
							and mtg.ID_NUM = act.ID_NUM
							and mtg.MTG_START_DTE = @Meeting_Time
					where 
						act.ACT_CDE = @Activity_Code 
						and act.SESS_CDE = @Session_Code
						and mtg.ID_NUM is null))
	begin
		insert into MTG_ATTEND_HST(
						sess_cde,
						act_cde,
						MTG_START_DTE,
						id_num,
						PRES_ABS_FLAG,
						USER_NAME,
						JOB_NAME,
						JOB_TIME)
					select 
							act.SESS_CDE 
							, act.ACT_CDE 
							, @Meeting_Time
							, act.ID_NUM 
							, 'A' -- absent so the update at the end of the scrip can make into a P for present
							, 'TE_ADMIN'
							, 'UIUSPSA007' 
							, GETDATE() 
						from ACT_PART as act
							left join MTG_ATTEND_HST as mtg on act.SESS_CDE = mtg.SESS_CDE
								and mtg.ID_NUM = act.ID_NUM
								and mtg.MTG_START_DTE = @Meeting_Time
						where 
							act.ACT_CDE = @Activity_Code 
							and act.SESS_CDE = @Session_Code
							and mtg.ID_NUM is null -- Not already in the meeting
					
	end


	-- ****************************************************************************
	--	Loop that processes each ID Number
	-- ****************************************************************************
	/* Loop until there is nothing on the main string */
	while (LEN(@tags) > 1)
	begin
		/* set the left variable as the first item in the list */
			set @nextSpace = PATINDEX('%[^0-9]%',@tags); -- finds the next non numeric value on the string
              set @left = LEFT(@tags, @nextSpace); - sets left from the beggining of the string until the 
              set @left =  Replace(@left,CHAR(13),' '); -- make all CR into spaces
              set @left =  Replace(@left,CHAR(10),' '); -- need this so l and r trim work
              set @left = rtrim(ltrim(@left)); -- does the trim
              set @left = cast(@left as int); -- does the cast and it's good to be inserted
			  
		/* Set the main string to everything but the item we alredy processed
		FYI:2147483647 is the biggest int possible, MAX didn't work... */
		set @tags = substring(@tags, @nextSpace + 1, 2147483647);

		if (@left = 0) continue -- breaks the instance of the loop because the ID number is 0
		
		/* If the ID number is not in the ACT_PART, insert it in that first */
		IF (@left not in 
			(select id_num 
				from ACT_PART 
				where SESS_CDE = @Session_Code 
				and ACT_CDE = @Activity_Code ))
			begin
				insert into ACT_PART (
					SESS_CDE,
					ACT_CDE,
					ID_NUM,
					PART_CDE,
					MEMBERSHIP_STS,
					TRACK_MTG_ATTEND,
					BEGIN_DTE,
					END_DTE,
					COMMENT_TXT,
					INCL_PROFILE_RPT,
					USER_NAME,
					JOB_NAME,
					JOB_TIME)
				VALUES(
					@Session_Code 
					,@Activity_Code 
					,@left
					,'PART'  -- Participant
					,'N'  -- Not a member, but a participant
					,'Y'  -- Yes, we want to track attendance
					,@Meeting_Time
					,@Meeting_Time + 1 
					,'Autofilled from myUIU'
					,'Y' 
					,'TE_ADMIN' 
					,'UIUSPSA007' 
					, GETDATE() )
			end
			
		/* insert attendance on MTG_ATTEND_HST or update in case of repeats!  */
		IF (@left not in (select id_num 
				from  MTG_ATTEND_HST 
				where SESS_CDE = @Session_Code 
				and ACT_CDE = @Activity_Code 
				and MTG_START_DTE = @Meeting_Time))
			begin
				insert into MTG_ATTEND_HST(
					sess_cde,
					act_cde,
					MTG_START_DTE,
					id_num,
					PRES_ABS_FLAG,
					USER_NAME,
					JOB_NAME,
					JOB_TIME
				) values (
					@Session_Code,
					@Activity_Code,
					@Meeting_Time,
					@left,
					'P',
					'TE_ADMIN',
					'UIUSPSA007',
					GETDATE()
				)
			end
		else 
			begin
				update MTG_ATTEND_HST
					set PRES_ABS_FLAG = 'P'
						, USER_NAME = 'TE_ADMIN'
						,JOB_NAME = 'UIUSPSA007'
						, JOB_TIME = GETDATE()
					where SESS_CDE = @Session_Code 
						and ACT_CDE = @Activity_Code 
						and MTG_START_DTE = @Meeting_Time
						and ID_NUM = @left
			end
	end

	-- ****************************************************************************
	--	Recalcs Jenzabar would do to these tables
	-- ****************************************************************************
 
	/* recalc for mtg_hist 
		Total number of members from act_part goes in NUM_MEMBERS
		Every member from MTG_ATTEND_HST with a P goes in Members_present and total_attend
		Every member from MTG_ATTEND_HST with an A goes in Members_absent
		We don't have guests, but that could be a future implementation.
	*/
	Update mtg_hist
	set NUM_MEMBERS = (select 
							count(id_num) 
						from 
							act_part 
						where 
							sess_cde = @Session_Code  
							and act_cde = @Activity_Code )
		, Members_present =(select 
								count(id_num) 
							from 
								MTG_ATTEND_HST 
							where SESS_CDE = @Session_Code 
									and ACT_CDE = @Activity_Code 
									and MTG_START_DTE = @Meeting_Time
									and MTG_ATTEND_HST.PRES_ABS_FLAG = 'P' )
		, TOTAL_ATTEND =(select 
								count(id_num) 
							from 
								MTG_ATTEND_HST 
							where SESS_CDE = @Session_Code 
									and ACT_CDE = @Activity_Code 
									and MTG_START_DTE = @Meeting_Time
									and MTG_ATTEND_HST.PRES_ABS_FLAG = 'P' )
		, MEMBERS_ABSENT =(select 
								count(id_num) 
							from 
								MTG_ATTEND_HST 
							where SESS_CDE = @Session_Code 
									and ACT_CDE = @Activity_Code 
									and MTG_START_DTE = @Meeting_Time
									and MTG_ATTEND_HST.PRES_ABS_FLAG = 'A' )
		, USER_NAME = 'TE_ADMIN'
		,JOB_NAME = 'UIUSPSA007'
		, JOB_TIME = GETDATE()

	where SESS_CDE = @Session_Code 
			and ACT_CDE = @Activity_Code 
			and MTG_START_DTE = @Meeting_Time

	/* Recalc for sess_act_mast 
		Num_MTGS_HELD = sum of all from mtg_hist that have MTG_STS = 'M' for the session
		Current_members = Total number of members from act_part
	*/

	Update SESS_ACT_MASTER
	set Num_MTGS_HELD =(select top 1
							count(mtg_sts)
						from 
							MTG_HIST
						where
							SESS_CDE = @Session_Code 
							and ACT_CDE = @Activity_Code 
							and mtg_sts = 'M'
						)
		, Current_members = (select top 1
								count(id_num) 
							from 
								act_part 
							where 
								sess_cde = @Session_Code  
								and act_cde = @Activity_Code
							)
		, USER_NAME = 'TE_ADMIN'
		,JOB_NAME = 'UIUSPSA007'
		, JOB_TIME = GETDATE()
	where sess_cde = @Session_Code  
			and act_cde = @Activity_Code


	/* Recalc for Stud_Attend_Sum
		Num_Times_present = the sum of attendance for all meetings of the same activity in the same session
		Num_Times_absent = the sum of absense for all meetings of the same activity in the same session
	*/

	if (exists (select 
						*
					from MTG_ATTEND_HST as mtg
						left join Stud_Attend_Sum as stu 
							on stu.ID_NUM = mtg.ID_NUM
								and stu.SESS_CDE = mtg.sess_cde
								and stu.ACT_CDE = mtg.ACT_CDE
					where mtg.sess_cde = @Session_Code  
							and mtg.act_cde = @Activity_Code
							and stu.ID_NUM is null -- students that don't already exist in the table
					group by mtg.SESS_CDE
						, mtg.ACT_CDE
						, mtg.ID_NUM ))
	begin
		insert into Stud_Attend_Sum(
						sess_cde,
						act_cde,
						id_num,
						Num_times_present,
						num_times_absent,
						USER_NAME,
						JOB_NAME,
						JOB_TIME
					) select 
							mtg.SESS_CDE
							, mtg.ACT_CDE
							, mtg.ID_NUM 
							, sum(case when mtg.PRES_ABS_FLAG = 'P' then 1 else 0 end)
							, sum(case when mtg.PRES_ABS_FLAG = 'A' then 1 else 0 end)
							, 'TE_ADMIN'
							, 'UIUSPSA007'
							, GETDATE()
						from MTG_ATTEND_HST as mtg
							left join Stud_Attend_Sum as stu 
								on stu.ID_NUM = mtg.ID_NUM
									and stu.SESS_CDE = mtg.sess_cde
									and stu.ACT_CDE = mtg.ACT_CDE
						where mtg.sess_cde = @Session_Code  
								and mtg.act_cde = @Activity_Code
								and stu.ID_NUM is null -- students that don't already exist in the table
						group by mtg.SESS_CDE
							, mtg.ACT_CDE
							, mtg.ID_NUM 
	end
							

	update Stud_Attend_Sum 
	set Stud_Attend_Sum.Num_times_present = b.pres
		, Stud_Attend_Sum.NUM_TIMES_ABSENT = b.abs
		, Stud_Attend_Sum.USER_NAME = b.usr
		, Stud_Attend_Sum.JOB_TIME = b.today
	from (select 
						mtg.SESS_CDE
						, mtg.ACT_CDE
						, mtg.ID_NUM 
						, sum(case when mtg.PRES_ABS_FLAG = 'P' then 1 else 0 end) as pres
						, sum(case when mtg.PRES_ABS_FLAG = 'A' then 1 else 0 end) as abs
						, 'TE_ADMIN' as usr
						, 'UIUSPSA007' as job
						, GETDATE() as today
					from MTG_ATTEND_HST as mtg
						left join Stud_Attend_Sum as stu 
							on stu.ID_NUM = mtg.ID_NUM
								and stu.SESS_CDE = mtg.sess_cde
								and stu.ACT_CDE = mtg.ACT_CDE
					where mtg.sess_cde = @Session_Code  
							and mtg.act_cde = @Activity_Code
							and stu.ID_NUM is not null -- Students that already exist in the table
					group by mtg.SESS_CDE
						, mtg.ACT_CDE
						, mtg.ID_NUM 
					) as B
	where Stud_Attend_Sum.ID_NUM = b.ID_NUM
		and Stud_Attend_Sum.ACT_CDE = b.ACT_CDE
		and Stud_Attend_Sum.SESS_CDE = b.SESS_CDE
		
	/* Recalc for Sess_act_Att_sum  
		NUM_Members = total number from act_part
		members_present = Value of unique members present
		members_absent = value of unique members that were absent
		total_attend = Value of unique members present as we don;t currently use guests.
	*/
	update Sess_act_Att_sum
	set NUM_MEMBERS = (select 
							count(id_num) 
						from 
							act_part 
						where 
							sess_cde = @Session_Code  
							and act_cde = @Activity_Code
						)
	, MEMBERS_ABSENT = (select
							count(ID_NUM)
						from Stud_Attend_Sum as stu
						where act_cde = @Session_Code
							and sess_cde =  @Activity_Code
							and stu.NUM_TIMES_ABSENT != 0)
	, members_present = ((select 
							count(id_num) 
						from 
							act_part 
						where 
							sess_cde = @Session_Code  
							and act_cde = @Activity_Code
						) 
						- -- minus
						 (select
							count(ID_NUM)
						from Stud_Attend_Sum as stu
						where act_cde = @Session_Code
							and sess_cde =  @Activity_Code
							and stu.NUM_TIMES_ABSENT != 0))
		, USER_NAME = 'TE_ADMIN'
		,JOB_NAME = 'UIUSPSA007'
		, JOB_TIME = GETDATE()
	where sess_cde = @Session_Code  
			and act_cde = @Activity_Code
end

GO