/****** Object:  Stored Procedure dbo.Assign_Meeting_Date    Script Date: 11/15/2004 6:54:20 AM ******/

/****** Object:  Stored Procedure dbo.sp_Assign_Meeting_Date    Script Date: 10/1/2004 12:50:40 PM ******/
/****** Object:  Stored Procedure dbo.Assign_Meeting_Date    Script Date: 11/26/2003 1:01:57 PM ******/

CREATE procedure Assign_Meeting_Date 
	@c_SID nvarchar(10)
AS
declare 	@start_of_nextweek datetime,
	@priority_sought int,
	@slot datetime
	Declare 
		@p_stat nvarchar(5),
		@c_stat nvarchar(5),
		@comment nvarchar(255),
		@now datetime,
		@start_of_nextweek_plus_6 datetime,
		@start_of_nextweek_plus_12 datetime,

		@maxPriority int,
		@filld int,
		@rcount int
		
	

	--set @start_of_nextweek= CONVERT(CHAR(10),GETDATE(),110) 
	  set @start_of_nextweek= getdate()
	
	select @filld = (select filled from buckets where bucket_time = '3/2/2007 12:30:00 PM')


	-- If the disk is uploaded on any day after Thursday then, 
	-- move to the coming Monday and then start finding a slot 
	-- as before.

	while ( datename(weekday,@start_of_nextweek) > 'Friday' and datename(weekday,@start_of_nextweek) < 'Monday')	
	begin
		set @start_of_nextweek=dateadd(day,1,@start_of_nextweek)
			
		if( datename (weekday, @start_of_nextweek) = 'Monday' ) break
	end

	-- Catering for the fact that Disk could be uploaded on latest by Wednesday
	-- and still being able to assign a meeting date of Tuesday next week at the
	-- earliest

	set @priority_sought=1

	if( datename (weekday, @start_of_nextweek) = 'Tuesday' )
		set @start_of_nextweek = @start_of_nextweek -1
	else if ( datename (weekday, @start_of_nextweek) ='Wednesday' )
		set @start_of_nextweek = @start_of_nextweek - 2

	else if ( datename (weekday, @start_of_nextweek) ='Thursday' )
		set @start_of_nextweek = @start_of_nextweek - 3

	-- If the disk is uploaded uptil Thursday you can invite people for the coming Tuesday

	else if ( datename (weekday, @start_of_nextweek) ='Friday' )
	set @priority_sought = 2



	while (1=1)
	begin		
		set @start_of_nextweek_plus_6 = @start_of_nextweek + 6
		set @start_of_nextweek_plus_12 = @start_of_nextweek + 12		

		while (1=1)
		begin
			
			select @slot=null
			set @slot=( select b.bucket_time from (SELECT     Meeting_Day, Capacity, Filled, Priority, CONVERT(datetime, CONVERT(char, Bucket_Time, 1), 1) AS Bucket_Time, Attended
                      							   FROM          Buckets) b
				where 	(b.capacity>b.filled) and 
					(b.priority=@priority_sought) and
					Bucket_Time between @start_of_nextweek_plus_6 AND @start_of_nextweek_plus_12
					
					
				)
			if (@slot is not null)
			begin
				break
			end
			
			select @maxPriority = (select max(priority) from buckets)
			
			if @priority_sought=@maxPriority break
			set @priority_sought=@priority_sought+1

			
		end
		
		if @slot is not null break

		-- if you were not able to find a time slot in a period of between 6 to 12 days from the
		-- current day ( which could be Monday if you uploaded the disk on Monday uptill Thursday, 
		-- or Friday if you were uploading the disk on a Friday)

		while ( @priority_sought=@maxPriority and (@slot is null) ) 
		begin
			if(datename(weekday,@start_of_nextweek)<>'Monday')
			begin		
				set @start_of_nextweek=dateadd(day,1,@start_of_nextweek)
			end
			
			if( datename (weekday, @start_of_nextweek) = 'Monday' )
			begin
				set @priority_sought = 1
				 break
			end
		end -- end of while( @priority_sought=@maxPriority and (@slot is null) )  loop

		if @start_of_nextweek >(select max(Bucket_time) from buckets) break
		
	end -- end of while(1) loop


	select @slot = (select bucket_time from Buckets where priority = @priority_sought and DATEPART(DAY,bucket_time) = DATEPART(DAY,@slot) and DATEPART(MONTH,bucket_time) = DATEPART(MONTH,@slot) and DATEPART(YEAR,bucket_time) = DATEPART(YEAR,@slot) ) 

	update dockets 
		set Meeting_date=@slot
	from dockets
	where dockets.student_ID = @c_SID

	set @rcount = @@rowcount

	if (@rcount = 1)
	begin
		set  @filld = (select filled from buckets where bucket_time = @slot)
	
		update buckets set filled = filled + 1 where bucket_time=@slot

		set @filld = 0
		set  @filld = (select filled from buckets where bucket_time = @slot)

		
		set @filld = 0
	end
GO