USP_ADDPROSPECTPLANSPROCESS

CREATE procedure dbo.USP_ADDPROSPECTPLANSPROCESS (
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier,
    @NUMBERPROCESSED int output,
    @NUMBEREXCEPTIONS int output,
    @CONSTITUENTTABLENAME nvarchar(128),
    @CURRENTAPPUSERID uniqueidentifier = null
)
as
begin

    set @NUMBERPROCESSED = 0;
    set @NUMBEREXCEPTIONS = 0;

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

    declare @IDSETREGISTERID uniqueidentifier;
    declare @PROSPECTMANAGERFUNDRAISERID uniqueidentifier;
    declare @PROSPECTPLANNAME nvarchar(100);
    declare @PROSPECTPLANTYPECODEID uniqueidentifier;
    declare @NARRATIVE nvarchar(1000);
    declare @PLANOUTLINEID uniqueidentifier;
    declare @STARTDATE date;
    declare @PRIMARYMANAGERFUNDRAISERID uniqueidentifier;
    declare @SECONDARYMANAGERFUNDRAISERID uniqueidentifier;
    declare @SECONDARYFUNDRAISERS table (FUNDRAISERID uniqueidentifier,
                                         SOLICITORROLECODEID uniqueidentifier,
                                         SEQUENCE int);
    declare @SITES table (SITEID uniqueidentifier);
    declare @CREATEOPPORTUNITIES bit;
    declare @OPPORTUNITYTYPECODEID uniqueidentifier;
    declare @OPPORTUNITYSTATUSCODE tinyint;
    declare @EXPECTEDASKAMOUNT money;
    declare @ASKAMOUNT money;
    declare @OPPORTUNITYDESIGNATIONS table (DESIGNATIONID uniqueidentifier,
                                            AMOUNT money,
                                            SEQUENCE int,
                                            FUNDINGMETHODCODEID uniqueidentifier,
                                            CATEGORYCODEID uniqueidentifier,
                                            TYPECODEID uniqueidentifier,
                                            USECODEID uniqueidentifier);
    declare @PROSPECTPLANCURRENCYSETID uniqueidentifier;
    declare @OPPORTUNITYTRANSACTIONCURRENCYID uniqueidentifier;
    declare @PROSPECTPLANBASECURRENCYID uniqueidentifier;
  declare @PLANSTEPS xml;
  declare @NEWPLANSTEPS xml;
    declare @SQL nvarchar(250);
    set @SQL = N'insert into dbo.' + @CONSTITUENTTABLENAME + ' (CONSTITUENTID, NEWPROSPECT, RESULTCODE) values (@CID, @NP, @RC)';
    declare @PARAMDEF nvarchar(50);
    set @PARAMDEF = N'@CID uniqueidentifier, @NP bit, @RC tinyint';

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

    -- get parameters

    select
        @IDSETREGISTERID = IDSETREGISTERID,
        @PROSPECTPLANNAME = PROSPECTPLANNAME,
        @PROSPECTPLANTYPECODEID = PROSPECTPLANTYPECODEID,
        @NARRATIVE = NARRATIVE,
        @PLANOUTLINEID = PLANOUTLINEID,
        @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,
        @PRIMARYMANAGERFUNDRAISERID = PRIMARYMANAGERFUNDRAISERID,
        @SECONDARYMANAGERFUNDRAISERID = SECONDARYMANAGERFUNDRAISERID,
        @PROSPECTMANAGERFUNDRAISERID = PROSPECTMANAGERFUNDRAISERID,
        @CREATEOPPORTUNITIES = CREATEOPPORTUNITIES,
        @OPPORTUNITYTYPECODEID = OPPORTUNITYTYPECODEID,
        @OPPORTUNITYSTATUSCODE = OPPORTUNITYSTATUSCODE,
        @EXPECTEDASKAMOUNT = EXPECTEDASKAMOUNT,
        @ASKAMOUNT = case OPPORTUNITYSTATUSCODE when 0 then 0 else EXPECTEDASKAMOUNT end,
        @PROSPECTPLANCURRENCYSETID = PROSPECTPLANCURRENCYSETID,
        @OPPORTUNITYTRANSACTIONCURRENCYID = OPPORTUNITYTRANSACTIONCURRENCYID,
    @PLANSTEPS = (select PLANSTEPS.query('/PLANSTEPS/node()') for xml raw('STEPS'),type)    
    from dbo.ADDPROSPECTPLANSPROCESS
    where ID = @ID;    

    declare @ISADMIN bit = 0;
    declare @ADDAMAJORGIVINGPROSPECTTASKID uniqueidentifier = 'F3D5EC46-C463-4873-923A-B392F1F3C472';
    declare @PROSPECTMANAGEREDITDATAFORMINSTANCEID uniqueidentifier = 'C69768CC-D4DF-42A7-A1E0-185419D14854'
    declare @ERROR_DUPLICATEPLAN nvarchar(max) = 'UIX_PROSPECTPLAN_PROSPECTID_PROSPECTPLANTYPECODEID_NAME';
    declare @ERROR_INSUFFICIENTRIGHTS_ADDMAJORGIVINGPROSPECTTASK nvarchar(max) = 'Insufficient rights: Add a major giving prospect task';
    declare @ERROR_INSUFFICIENTRIGHTS_PROSPECTMANAGEREDITFORM nvarchar(max) = 'Insufficient rights: Prospect manager edit form';
    declare @ISADDAMAJORGIVINGPROSPECTTASKALLOWED bit = 0;
    declare @ISPROSPECTMANAGEREDITDATAFORMINSTANCEALLOWED bit = 0;

    set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);
    set @ISADDAMAJORGIVINGPROSPECTTASKALLOWED = dbo.UFN_SECURITY_APPUSER_GRANTED_TASK_IN_SYSTEMROLE(@CURRENTAPPUSERID,@ADDAMAJORGIVINGPROSPECTTASKID);
    set @ISPROSPECTMANAGEREDITDATAFORMINSTANCEALLOWED = dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE(@CURRENTAPPUSERID,@PROSPECTMANAGEREDITDATAFORMINSTANCEID);


    select @PROSPECTPLANBASECURRENCYID = BASECURRENCYID 
    from dbo.CURRENCYSET 
    where ID = @PROSPECTPLANCURRENCYSETID

    insert into @SECONDARYFUNDRAISERS (FUNDRAISERID, SOLICITORROLECODEID, SEQUENCE)
    select FUNDRAISERID, SOLICITORROLECODEID, SEQUENCE
    from dbo.UFN_ADDPROSPECTPLANSPROCESS_SECONDARYFUNDRAISERS(@ID);

    insert into @SITES (SITEID)
    select SITEID
    from dbo.UFN_ADDPROSPECTPLANSPROCESS_SITES(@ID);

    if @CREATEOPPORTUNITIES = 1
        insert into @OPPORTUNITYDESIGNATIONS (DESIGNATIONID, AMOUNT, SEQUENCE, FUNDINGMETHODCODEID, CATEGORYCODEID, TYPECODEID, USECODEID)
        select DESIGNATIONID, AMOUNT, SEQUENCE, FUNDINGMETHODCODEID, CATEGORYCODEID, TYPECODEID, USECODEID
        from dbo.UFN_ADDPROSPECTPLANSPROCESS_OPPORTUNITYDESIGNATIONS(@ID);

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

    -- get constituents

    declare @CONSTITUENTID uniqueidentifier;

    if @ISADMIN = 1    
        declare CONSTITUENTCURSOR cursor local fast_forward for        
        select ID from dbo.UFN_IDSETREADER_GETRESULTS(@IDSETREGISTERID)
    else
        begin
          declare CONSTITUENTCURSOR cursor local fast_forward for

          with [CONSTITUENT_RACS] as
            (select ID as ConstituentID from dbo.[CONSTITUENT]
            where (
            ((
            (select count(*) from dbo.UFN_SITEID_MAPFROM_CONSTITUENTID(CONSTITUENT.ID) as SITES where 
            dbo.UFN_SECURITY_APPUSER_GRANTED_BUSINESSPROCESS_FORSITE(
                /*userid*/ @CURRENTAPPUSERID,
                /*businessprocesscatalogid*/ '59ba0c29-3410-4c65-adf8-25242a005ebc',
                /*siteid*/ SITEID
                ) = 1
            ) > 0)
            )
            ))

          select ID from dbo.UFN_IDSETREADER_GETRESULTS(@IDSETREGISTERID)
            inner join [CONSTITUENT_RACS] on [CONSTITUENT_RACS].ConstituentID = ID
        end 

    open CONSTITUENTCURSOR;
    begin try
        begin transaction;

        fetch next from CONSTITUENTCURSOR into @CONSTITUENTID;

        while (@@FETCH_STATUS = 0)
        begin
            begin try
                save transaction PROSPECTTX;

                declare @NEWPROSPECTMANAGERFUNDRAISERID uniqueidentifier = @PROSPECTMANAGERFUNDRAISERID;

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

                -- track if this is a new prospect


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

                    if not(
                            @ISADMIN = 1 or (
                                @ISADDAMAJORGIVINGPROSPECTTASKALLOWED = 1
                                and dbo.UFN_SECURITY_APPUSER_GRANTED_TASK_FORCONSTIT(@CURRENTAPPUSERID,@ADDAMAJORGIVINGPROSPECTTASKID,@CONSTITUENTID) = 1
                            )
                        )
                        raiserror(@ERROR_INSUFFICIENTRIGHTS_ADDMAJORGIVINGPROSPECTTASK,13,1);

                    if @NEWPROSPECTMANAGERFUNDRAISERID is not null
                        if not(
                                @ISADMIN = 1 or (
                                    @ISPROSPECTMANAGEREDITDATAFORMINSTANCEALLOWED = 1
                                    and dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_FORCONSTIT(@CURRENTAPPUSERID,@PROSPECTMANAGEREDITDATAFORMINSTANCEID,@CONSTITUENTID) = 1
                                )
                            )
                            set @NEWPROSPECTMANAGERFUNDRAISERID = null;
                end

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

                -- create prospect plans, and prospects as needed


                declare @PROSPECTMANAGERDATEFROM date;
                declare @PRIMARYMANAGERDATEFROM date;
                declare @SECONDARYMANAGERDATEFROM date;
                declare @SECONDARYFUNDRAISERSXML xml;
                declare @SITESXML xml;

                if @NEWPROSPECTMANAGERFUNDRAISERID is not null
                    set @PROSPECTMANAGERDATEFROM = @STARTDATE;
                if @PRIMARYMANAGERFUNDRAISERID is not null
                    set @PRIMARYMANAGERDATEFROM = @STARTDATE;
                if @SECONDARYMANAGERFUNDRAISERID is not null
                    set @SECONDARYMANAGERDATEFROM = @STARTDATE;

                -- reassign IDs for secondary fundraisers

                set @SECONDARYFUNDRAISERSXML = (select newid(), FUNDRAISERID, SOLICITORROLECODEID, SEQUENCE
                                                from @SECONDARYFUNDRAISERS
                                                for xml raw('ITEM'),type,elements,root('SECONDARYFUNDRAISERS'),BINARY BASE64);

                -- reassign IDs for sites

                set @SITESXML = (select newid(), SITEID
                                 from @SITES
                                 for xml raw('ITEM'),type,elements,root('SITES'),BINARY BASE64);

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

                exec dbo.USP_PROSPECT_ADD
                    null,
                    @CHANGEAGENTID,
                    @CONSTITUENTID,
                    @NEWPROSPECTMANAGERFUNDRAISERID,
                    @PROSPECTPLANTYPECODEID,
          --the PLANOUTLINEID steps are no longer created automatically here; this is now done on the plan steps tab

                    null,
                    @PRIMARYMANAGERFUNDRAISERID,
                    @SECONDARYMANAGERFUNDRAISERID,
                    @SECONDARYFUNDRAISERSXML,
                    @PROSPECTPLANNAME,
                    null,
                    @SITESXML,
                    null,
                    @PRIMARYMANAGERDATEFROM,
                    @SECONDARYMANAGERDATEFROM,
                    @PROSPECTMANAGERDATEFROM,
                    @NARRATIVE,
                    @STARTDATE,
                    0,  --don't override existing prospect manager

                    @PROSPECTPLANID,
                    @PROSPECTPLANBASECURRENCYID,
                    @STARTDATE
                ---------------------------------------------------------------------

                -- create opportunities


                if @CREATEOPPORTUNITIES = 1
                begin
                    declare @OPPORTUNITYDESIGNATIONSXML xml;

                    -- reassign IDs for designations

                    set @OPPORTUNITYDESIGNATIONSXML =
                     (select newid(), DESIGNATIONID, AMOUNT, SEQUENCE, FUNDINGMETHODCODEID, CATEGORYCODEID, TYPECODEID, USECODEID
                      from @OPPORTUNITYDESIGNATIONS
                      for xml raw('ITEM'),type,elements,root('DESIGNATION'),BINARY BASE64);

                    exec dbo.USP_DATAFORMTEMPLATE_OPPORTUNITY_ADD
                        @CHANGEAGENTID = @CHANGEAGENTID,
                        @PROSPECTPLANID = @PROSPECTPLANID,
                        @EXPECTEDASKAMOUNT = @EXPECTEDASKAMOUNT,
                        @ASKAMOUNT = @ASKAMOUNT,
                        @DESIGNATION = @OPPORTUNITYDESIGNATIONSXML,
                        @OPPORTUNITYTYPECODEID = @OPPORTUNITYTYPECODEID,
                        @STATUSCODE = @OPPORTUNITYSTATUSCODE,
                        @TRANSACTIONCURRENCYID = @OPPORTUNITYTRANSACTIONCURRENCYID
                end
        ---------------------------------------------------------------------

        -- plan steps

        -- calculate expected date

        set @NEWPLANSTEPS = (select * from
                                      dbo.UFN_ADDPROSPECTPLANPROCESS_STEPS_FROMITEMLISTXML(@PLANSTEPS, @STARTDATE)
                                    for xml raw('ITEM'), type, elements, root('STEPS'), binary base64
                                  )

                exec dbo.USP_PROSPECTPLAN_STEPSWITHCHILDREN_UPDATEFROMXML @PROSPECTPLANID, @NEWPLANSTEPS, @CHANGEAGENTID;
                ---------------------------------------------------------------------

                -- reporting


                if @PROSPECTMANAGERFUNDRAISERID is not null and @NEWPROSPECTMANAGERFUNDRAISERID is null
                begin
                    exec sp_executesql @SQL, @PARAMDEF, @CONSTITUENTID, @NEWPROSPECT, 3
                    set @NUMBEREXCEPTIONS = @NUMBEREXCEPTIONS + 1;
                end
                else
                begin
                    exec sp_executesql @SQL, @PARAMDEF, @CONSTITUENTID, @NEWPROSPECT, 0
                    set @NUMBERPROCESSED = @NUMBERPROCESSED + 1;
                end
            end try
            begin catch
                rollback transaction PROSPECTTX;

                declare @RESULTCODE tinyint = 255;
                /*
                    @RESULTCODE
                    255 - Unknown
                    0 - Success
                    1 - Failed - duplicate plan
                    2 - Failed - Insufficient rights: Add a major giving prospect task
                    3 - Partial Success - Insufficient rights: Prospect manager edit dataform
                */

                if charindex(@ERROR_DUPLICATEPLAN, error_message()) > 0
                    set @RESULTCODE = 1
                else if error_message() = @ERROR_INSUFFICIENTRIGHTS_ADDMAJORGIVINGPROSPECTTASK
                    set @RESULTCODE = 2

                if @RESULTCODE <> 255
                begin
                    exec sp_executesql @SQL, @PARAMDEF, @CONSTITUENTID, @NEWPROSPECT, @RESULTCODE
                    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