USP_ADDSTEWARDSHIPPLANSPROCESS

CREATE procedure dbo.USP_ADDSTEWARDSHIPPLANSPROCESS
(
  @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 @PLANTYPECODEID uniqueidentifier;
  declare @PLANSUBTYPECODEID uniqueidentifier;
  declare @MANAGERID uniqueidentifier;
  declare @MANAGERSTARTDATE date;
  declare @STARTDATETYPECODE tinyint;
  declare @STARTDATE date;
  declare @DAYSBEFOREORAFTER int;
  declare @SITES xml;
  declare @STEWARDS xml;
  declare @PLANSTEPS xml;

  declare @SQL nvarchar(250);
  set @SQL = N'insert into dbo.' + @CONSTITUENTTABLENAME + ' (CONSTITUENTID, NEWPROSPECT, RESULTCODE, PLANID) values (@CID, @NP, @RC, @PLANID)';
  declare @PARAMDEF nvarchar(82);
  set @PARAMDEF = N'@CID uniqueidentifier, @NP bit, @RC tinyint, @PLANID uniqueidentifier';

  select
    @IDSETREGISTERID = IDSETREGISTERID,
    @PROSPECTPLANNAME = PROSPECTPLANNAME,
    @PLANTYPECODEID = PLANTYPECODEID,
    @PLANSUBTYPECODEID = PLANSUBTYPECODEID,
    @MANAGERID = MANAGERID,
    @MANAGERSTARTDATE = MANAGERSTARTDATE,
    @STARTDATE = 
      case STARTDATETYPECODE
         when 0 then @PROCESSDATE
         when 1 then STARTDATE
         when 2 then dateadd(d,DAYSBEFOREORAFTER,@PROCESSDATE)
         when 3 then dateadd(d,-DAYSBEFOREORAFTER,@PROCESSDATE)
       end,
    @PLANSTEPS = (select PLANSTEPS.query('/STEPS/node()') for xml raw('STEPS'),type),
    @SITES = (select SITES.query('/SITES/node()') for xml raw('SITES'),type),
    @STEWARDS = (select STEWARDS.query('/STEWARDS/node()') for xml raw('STEWARDS'),type)
  from dbo.ADDSTEWARDSHIPPLANSPROCESS
  where ID = @ID;   

  --get constituents

  declare @CONSTITUENTID uniqueidentifier;

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

  open CONSTITUENTCURSOR;
  begin try
    begin transaction;

    fetch next from CONSTITUENTCURSOR into @CONSTITUENTID;
    while (@@FETCH_STATUS = 0)
    begin

      begin try
        save transaction STEWARDSHIPPLANTX;
        ---------------------------------------------------------------------

        -- track if this is a new prospect


        declare @NEWPROSPECT bit;
        if (exists(select 1 from dbo.PROSPECT where ID=@CONSTITUENTID))
          set @NEWPROSPECT = 0;
        else
          set @NEWPROSPECT = 1;
        ---------------------------------------------------------------------

        -- create stewardship plans, and prospects as needed

        declare @SITESXML xml;
        declare @STEWARDSXML xml;
        declare @PLANSTEPSXML xml;

        -- reassign IDs for sites

        set @SITESXML = (select newid(), SITEID
                 from 
                 (select
                          T.c.value('(ID)[1]','uniqueidentifier') as ID,
                          T.c.value('(SITEID)[1]','uniqueidentifier') as SITEID
                          from @SITES.nodes('/SITES/ITEM') as T(c)) SITES 
                 for xml raw('ITEM'),type,elements,root('SITES'),BINARY BASE64);

        -- reassign IDs for stewards

        set @STEWARDSXML = (select newid(), CONSTITUENTID, ROLECODEID, STARTDATE
                 from 
                 (select
                          T.c.value('(ID)[1]','uniqueidentifier') as ID,
                          T.c.value('(CONSTITUENTID)[1]','uniqueidentifier') as CONSTITUENTID,
                          T.c.value('(ROLECODEID)[1]','uniqueidentifier') as ROLECODEID,
                          T.c.value('(STARTDATE)[1]','date') as STARTDATE
                          from @STEWARDS.nodes('/STEWARDS/ITEM') as T(c)) STEWARDS 
                 for xml raw('ITEM'),type,elements,root('STEWARDS'),BINARY BASE64);

        declare @STEWARDSHIPPLANID uniqueidentifier;
        set @STEWARDSHIPPLANID = newid();

        exec dbo.USP_DATAFORMTEMPLATE_ADD_STEWARDSHIPPLAN
          @STEWARDSHIPPLANID,
          @CHANGEAGENTID,   
          @CONSTITUENTID,
          @MANAGERID,
          @STEWARDSXML,
          @PROSPECTPLANNAME,
          @SITESXML,
          @CURRENTAPPUSERID,
          @PLANTYPECODEID,
          @PLANSUBTYPECODEID,
          @STARTDATE,
          null,
          @MANAGERSTARTDATE 

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

        -- plan steps

        -- calculate target date

        set @PLANSTEPSXML = 
          (select
            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
          )

        exec dbo.USP_STEWARDSHIPPLAN_STEPSWITHCHILDREN_UPDATEFROMXML @STEWARDSHIPPLANID, 0, @PLANSTEPSXML, null, @CHANGEAGENTID; -- 0->pending

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

        -- reporting


        exec sp_executesql @SQL, @PARAMDEF, @CONSTITUENTID, @NEWPROSPECT, 0, @STEWARDSHIPPLANID 
        set @NUMBERPROCESSED = @NUMBERPROCESSED + 1;
      end try
      begin catch
        rollback transaction STEWARDSHIPPLANTX;
        if error_message() like '%UIX_STEWARDSHIPPLAN_CONSTITUENTID_NAME%'
        begin
          -- ignore duplicate plan errors

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

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