aes_cursor

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[aes_sessionschedule]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[aes_sessionschedule]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE procedure dbo.aes_sessionschedule
as
--
   declare @suser_sname nvarchar(50)
   declare @getdate nvarchar(50)
--
   declare @ID nvarchar(50)
   declare @ApplicationID nvarchar(50)
   declare @Program nvarchar(50)
   declare @ResourceID nvarchar(10)
   declare @ResourceGroupID nvarchar(10)
   declare @FamilyID nvarchar(10)
   declare @HHID nvarchar(10)
   declare @SecurityID nvarchar(10)
   declare @SecurityGroupID nvarchar(10)
   declare @SecurityOrgID nvarchar(10)
   declare @SecurityRegionID nvarchar(10)
   declare @SecurityMode nvarchar(50)
   declare @ValidFrom nvarchar(50)
   declare @ValidTo nvarchar(50)
   declare @Mon bit
   declare @Tue  bit
   declare @Wed bit
   declare @Thu bit
   declare @Fri bit
   declare @Sat bit
   declare @Sun bit
   declare @Day int
   declare @RightDay int
   declare @ActivityKey nvarchar(10)
--
   declare @KeyValue int
   declare @ActivityNumber nvarchar(10)
   declare @ActivityDetailsNumber nvarchar(10)
   declare @SourceID nvarchar(050)
   declare @Counts int
--
   declare @ExitKey nvarchar(10)
   declare @CountActivityDetails int
   declare @CountActivity int
   declare @error int
   declare @rowcount int
   declare @Activity nvarchar(50)
   declare @MilestoneStatusKey  nvarchar(50)
--
begin
--
   set @suser_sname = suser_sname()
-- ********************************************************************************
-- testing
--   set @getdate = convert(nvarchar,dateadd(day,1,getdate()),101)
-- ********************************************************************************
-- set @getdate = convert(nvarchar,dateadd(day,0,getdate()),101)
-- ********************************************************************************
   set @getdate=cast(datepart(mm,getdate())  as nvarchar)+'/'+cast(datepart(dd,getdate()) as nvarchar)+'/'+cast(datepart(yyyy,getdate()) as nvarchar)

declare
   C_sessionschedule
      cursor
   for
      select
         [Application].[ID],
         [Application].[ApplicationID],
         [Application].[Program],
         [Application].[ResourceID],
         [Application].[ResourceGroupID],
         [Application].[FamilyID],
         [Application].[HouseHoldID],
         [Application].[SecurityID],
         [Application].[SecurityGroupID],
         [Application].[SecurityOrgID],
         [Application].[SecurityRegionID],
         [Application].[SecurityMode],
         [AutoSchedule].[ValidFrom],
         [AutoSchedule].[ValidTo],
         isnull([AutoSchedule].[Mon],0) as Mon,
         isnull([AutoSchedule].[Tue],0) as Tue,
         isnull([AutoSchedule].[Wed],0) as Wed,
         isnull([AutoSchedule].[Thu],0) as Thu,
         isnull([AutoSchedule].[Fri],0) as Fri,
         isnull([AutoSchedule].[Sat],0) as Sat,
         isnull([AutoSchedule].[Sun],0) as Sun,
         datepart(dw,getdate())as [Day],
         [AutoSchedule].[ActivityKey],
         isnull([AutoSchedule].[MilestoneStatusKey],'') as MilestoneStatusKey,
         [ActivityTemplate].[Service] as Activity        
      from
         [Application],
         [AutoSchedule],
         [ActivityTemplate]
      where
         [Application].[ApplicationID] = [AutoSchedule].[ApplicationID] and
         [ActivityTemplate].EligibilityDescriptionKey=[AutoSchedule].[ActivityKey] and
         [Application].[Active] = 1 and
         [AutoSchedule].[Daily_Session]='Session' and
         ([AutoSchedule].[ValidFrom] is not null or [AutoSchedule].[ValidFrom] <>'')  and
         ([AutoSchedule].[ValidTo] is not null or  [AutoSchedule].[ValidTo]<>'') and
         datediff(day, isnull([AutoSchedule].[ValidFrom],'1/1/1900'), @getdate) >= 0 and
         datediff(day, @getdate, isnull([AutoSchedule].[ValidTo],'1/1/1900')) >= 0
-- ********************************************************************************
   open C_sessionschedule
      fetch
         next
      from
         C_sessionschedule
      into
         @ID,
         @ApplicationID,
         @Program,
         @ResourceID,
         @ResourceGroupID,
         @FamilyID,
         @HHID,
         @SecurityID,
         @SecurityGroupID,
         @SecurityOrgID,
         @SecurityRegionID,
         @SecurityMode,
         @ValidFrom,
         @ValidTo,
         @Mon,
         @Tue,
         @Wed,
         @Thu,
         @Fri,
         @Sat,
         @Sun,
         @Day,
         @ActivityKey,
         @MilestoneStatusKey,
         @Activity
--
      set @rowcount=0
      while
         (@@fetch_status = 0)
      begin
-- ********************************************************************************
-- check for [ActivityDetails] row
-- ********************************************************************************
         select
            @CountActivityDetails = count(*)
         from
            [ActivityDetails]
         where
            [ApplicationID] = @ApplicationID and
            [ID] = @ID and
            [Activity] = @Activity and
            [ActivityType] = 'Session' and
            [ActivityKey]=@ActivityKey and
            [StartDate] = @getdate
         if
            @CountActivityDetails = 0
         begin

--Check schedule
             set @RightDay=0
 if @Day=1 and @Sun=1
    set @RightDay=1
 else if @Day=2 and @Mon=1
    set @RightDay=1
 else if @Day=3 and @Tue=1
    set @RightDay=1
 else if @Day=4 and @Wed=1
    set @RightDay=1
 else if @Day=5 and @Thu=1
    set @RightDay=1
 else if @Day=6 and @Fri=1
    set @RightDay=1
 else if @Day=7 and @Sat=1
    set @RightDay=1   
        
 if @RightDay=1
 begin
-- *****************************************************************************************
-- get next keys for all of the tables involved
-- *****************************************************************************************
--
-- get next [ActivityNumber] from [SystemKey]
--
Print @RightDay
Print @GetDate

              exec aes_systemkey 'ActivityNumber', @ActivityNumber output

              if
                   @ActivityNumber = 'TimeOut'
               begin
                   return
             end
              exec aes_systemkey 'ActivityDetailsNumber', @ActivityDetailsNumber output
              if
                @ActivityDetailsNumber = 'TimeOut'
              begin
                return
              end
Print @ActivityDetailsNumber
--
             insert into
                [Activity]
                (
                   [ActivityNumber],
                   [ID],
                   [ApplicationID],
                   [SecurityID],
                   [SecurityGroupID],
                   [SecurityOrgID],
                   [SecurityRegionID],
                   [SecurityMode]
                )
             values
                (
                   @ActivityNumber,
                   @ID,
                   @ApplicationID,
                   @SecurityID,
                   @SecurityGroupID,
                   @SecurityOrgID,
                   @SecurityRegionID,
                   @SecurityMode
                )
 --
             insert into
                [ActivityDetails]
                (
                   [ActivityDetailsNumber],
                   [ActivityNumber],
                   [Activity],
                   [ActivityType],
                   [ID],
                   [ApplicationID],
                   [FamilyID],
                   [HouseholdID],
                   [resourceID],
                   [resourcegroupID],
                   [StartDate],
                   [ActivityKey],
                               [MilestoneStatusKey],
                   [AppointmentCheckIn],  
                   [AppointmentKept],
                   [AppointmentEnded],
                   [DateCheckIn],  
                   [DateKept],
                   [DateEnded],
                               [Attendance],
                   [SecurityID],
                   [SecurityGroupID],
                   [SecurityOrgID],
                   [SecurityRegionID],
                   [SecurityMode]
                )
             values
                (
                   @ActivityDetailsNumber,
                   @ActivityNumber,
                   @Activity,
                   'Session',
                   @ID,
                   @ApplicationID,
                   @FamilyID,
                   @HHID,
                   @ResourceID,
                   @ResourceGroupID,
                   @getdate,
                   @ActivityKey,
                               @MilestoneStatusKey,
       1,
       1,
       1,
                   @getdate,
                   @getdate,
                   @getdate,
                   1,
                   @SecurityID,
                   @SecurityGroupID,
                   @SecurityOrgID,
                   @SecurityRegionID,
                   @SecurityMode
                )
 end
         end
exit_trigger:
-- ********************************************************************************
         fetch
            next
         from
            C_sessionschedule
         into
            @ID,
            @ApplicationID,
            @Program,
            @ResourceID,
            @ResourceGroupID,
            @FamilyID,
            @HHID,
            @SecurityID,
            @SecurityGroupID,
            @SecurityOrgID,
            @SecurityRegionID,
            @SecurityMode,
            @ValidFrom,
            @ValidTo,
            @Mon,
            @Tue,
            @Wed,
            @Thu,
            @Fri,
            @Sat,
            @Sun,
            @Day,
            @ActivityKey,
            @MilestoneStatusKey,
            @Activity
--         goto C_BedSchedule_end
      end
C_sessionschedule_end:
--
close C_sessionschedule

deallocate C_sessionschedule
--
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO