USP_ADDSTEWARDSHIPPLANSTEPSPROCESS

CREATE procedure dbo.USP_ADDSTEWARDSHIPPLANSTEPSPROCESS
(
  @ID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier,
  @NUMBERPROCESSED int output,
  @NUMBEREXCEPTIONS int output,
  @CONSTITUENTTABLENAME nvarchar(128),
  @CURRENTAPPUSERID uniqueidentifier
)
as
begin
  set @NUMBERPROCESSED = 0;
  set @NUMBEREXCEPTIONS = 0;

  declare @PROCESSDATE date;
  set @PROCESSDATE = getdate();

  declare @IDSETREGISTERID uniqueidentifier;
  declare @PROSPECTPLANNAME nvarchar(100);
  declare @PLANSTEPS xml;

  declare @SQL nvarchar(250);
  set @SQL = N'insert into dbo.' + @CONSTITUENTTABLENAME + ' (PLANID, RESULTCODE) values (@PLANID, @RC)';
  declare @PARAMDEF nvarchar(82);
  set @PARAMDEF = N'@PLANID uniqueidentifier, @RC tinyint, @STEPXML xml';

  select
    @IDSETREGISTERID = IDSETREGISTERID,
    @PLANSTEPS = (select PLANSTEPS.query('/STEPS/node()') for xml raw('STEPS'),type)
  from dbo.ADDSTEWARDSHIPPLANSTEPSPROCESS
  where ID = @ID;    

  --get plans

  declare @PLANID uniqueidentifier;

  declare PLANCURSOR cursor local fast_forward for
  select ID
  from dbo.UFN_IDSETREADER_GETRESULTS(@IDSETREGISTERID);

  open PLANCURSOR;
  begin try
    begin transaction;

    fetch next from PLANCURSOR into @PLANID;
    while (@@FETCH_STATUS = 0)
    begin

      begin try
        save transaction STEWARDSHIPPLANSTEPSTX;
        -- create stewardship plans, and prospects as needed

        declare @SITESXML xml;
        declare @STEWARDSXML xml;

        declare @PLANSTEPS_EXISTING xml;
        declare @PLANSTEPS_NEW xml;
        declare @PLANSTEPS_ALL xml;

        declare @STARTDATE date;
        select @STARTDATE = STARTDATE from dbo.STEWARDSHIPPLAN where ID = @PLANID
        ---------------------------------------------------------------------

        -- plan steps

        -- calculate target date

        set @PLANSTEPS_NEW = 
          (select
            newid() as 'ID',
            T.c.value('(CATEGORYCODEID)[1]','uniqueidentifier') as 'CATEGORYCODEID',
              T.c.value('(OBJECTIVE)[1]','nvarchar(100)') as'OBJECTIVE',
            coalesce(T.c.value('(TARGETDATE)[1]','date'), dateadd(day, T.c.value('(DATEOFFSET)[1]','int'), dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE))) as 'TARGETDATE',
              T.c.value('(ACTUALDATE)[1]','date') as 'ACTUALDATE',
              T.c.value('(STARTDATE)[1]','date') as 'STARTDATE',
              T.c.value('(ENDDATE)[1]','date') as 'ENDDATE',
              T.c.value('(DATELOCKED)[1]','bit') as'DATELOCKED',
              T.c.value('(CONTACTPERSONID)[1]','uniqueidentifier') as 'CONTACTPERSONID',    
              T.c.value('(CONTACTMETHODCODEID)[1]','uniqueidentifier') as 'CONTACTMETHODCODEID',
              T.c.value('(CONSTITUENTID)[1]','uniqueidentifier') as 'CONSTITUENTID',
              T.c.value('(TEMPLATE)[1]','nvarchar(100)') as'TEMPLATE',
              T.c.value('(RECURSCODE)[1]','smallint') as 'RECURSCODE',
              T.c.value('(STATUSCODE)[1]','smallint') as 'STATUSCODE',
              T.c.value('(NEXTTARGETDATE)[1]','date') as 'NEXTTARGETDATE',
              T.c.value('(TARGETSTARTTIME)[1]','dbo.UDT_HOURMINUTE') as 'TARGETSTARTTIME',
              T.c.value('(TARGETENDTIME)[1]','dbo.UDT_HOURMINUTE') as 'TARGETENDTIME',
              T.c.value('(TIMEZONEENTRYID)[1]','uniqueidentifier') as 'TIMEZONEENTRYID',
              case when T.c.value('(TARGETSTARTTIME)[1]','dbo.UDT_HOURMINUTE') = '' then 1 else 0 end as 'ISALLDAYEVENT',
              T.c.value('(ACTUALSTARTTIME)[1]','dbo.UDT_HOURMINUTE') as 'ACTUALSTARTTIME',
              T.c.value('(ACTUALENDTIME)[1]','dbo.UDT_HOURMINUTE') as 'ACTUALENDTIME',
              T.c.value('(EVENTID)[1]','uniqueidentifier') as 'EVENTID',
              T.c.value('(MAILINGID)[1]','uniqueidentifier') as 'MAILINGID',
              T.c.value('(BENEFITID)[1]','uniqueidentifier') as 'BENEFITID',
              case when T.c.exist('./STEPPARTICIPANTS/ITEM') = 1 then T.c.query('(STEPPARTICIPANTS/ITEM)') else null end AS 'STEPPARTICIPANTS',
              T.c.value('(LINKTYPECODE)[1]','smallint') as LINKTYPECODE
          from @PLANSTEPS.nodes('/STEPS/ITEM') T(c)
          for xml raw('ITEM'), type, elements, root('STEPS'), binary base64
          )

         set @PLANSTEPS_ALL = @PLANSTEPS_NEW;

         set @PLANSTEPS_EXISTING = 
          (select 
            STEP.ID,
            STEP.PLANID,
            STEP.CATEGORYCODEID,
            STEP.OBJECTIVE,
            STEP.TARGETDATE,
            STEP.DATELOCKED,
            STEP.ACTUALDATE,
            STEP.STARTDATE,
            STEP.ENDDATE,
            STEP.CONTACTPERSONID,
            STEP.CONTACTMETHODCODEID,
            STEP.CONSTITUENTID,            
            dbo.UFN_STEWARDSHIPPLANSTEP_ASSOCIATEDPLANS_TOITEMLISTXML(STEP.ID, @CURRENTAPPUSERID),
            STEP.TEMPLATE,                        
            STEP.RECURSCODE,
            STEP.STATUSCODE,
            STEP.EVENTID,
            STEP.MAILINGID,
            STEP.BENEFITID,
            STEP.TARGETSTARTTIME,
            STEP.TARGETENDTIME,
            STEP.TIMEZONEENTRYID,
            STEP.ISALLDAYEVENT,
            STEP.ACTUALSTARTTIME,
            STEP.ACTUALENDTIME,
            dbo.UFN_STEWARDSHIPPLANSTEP_PARTICIPANTS_TOITEMLISTXML(STEP.ID)
          from
            dbo.STEWARDSHIPPLANSTEP STEP
          where
            PLANID = @PLANID and COMPLETED = 0 --or CONTACTPERSONID = @CONSTITUENTID

          for xml raw('ITEM'),type,elements,BINARY BASE64);

        SET @PLANSTEPS_ALL.modify('insert sql:variable("@PLANSTEPS_EXISTING") into (/STEPS[1])');

        exec dbo.USP_STEWARDSHIPPLAN_STEPSWITHCHILDREN_UPDATEFROMXML @PLANID, 0, @PLANSTEPS_ALL, null, @CHANGEAGENTID; -- 0->pending

        ---------------------------------------------------------------------

        -- reporting

        declare @STEPSQL nvarchar(350);
        set @STEPSQL = N'insert into dbo.' + @CONSTITUENTTABLENAME + ' (PLANID, STEPID, RESULTCODE) select @PLANID, item.value(''ID[1]'',''uniqueidentifier''), 0 from @STEPXML.nodes(''STEPS/ITEM'') Steps(item)';

        exec sp_executesql @STEPSQL, @PARAMDEF, @PLANID, 0, @PLANSTEPS_NEW
        set @NUMBERPROCESSED = @NUMBERPROCESSED + 1;
      end try
      begin catch
        rollback transaction STEWARDSHIPPLANSTEPSTX;
        if error_message() like '%TARGETDATE%'
          begin
            -- ignore error related to plan with missing date and log exception...bad data

            exec sp_executesql @SQL, @PARAMDEF, @PLANID, 1, null
            set @NUMBEREXCEPTIONS = @NUMBEREXCEPTIONS + 1;
          end
        else
          exec dbo.USP_RAISE_ERROR;
      end catch

      fetch next from PLANCURSOR into @PLANID;
    end
    close PLANCURSOR;
    deallocate PLANCURSOR;
    commit transaction;
  end try
  begin catch
    close PLANCURSOR;
    deallocate PLANCURSOR;
    rollback transaction;
    exec dbo.USP_RAISE_ERROR;
    return 1;
  end catch
end