USP_SPONSORSHIP_BULKTRANSFER

CREATE procedure dbo.USP_SPONSORSHIP_BULKTRANSFER (
    @SUCCESSTABLE nvarchar(128),
    @EXCEPTIONTABLE nvarchar(128),
    @TRANSFERTYPE tinyint = 0,        -- 0=Full, 1=Pending

    @MATCHRULE tinyint = 0,            -- 0=Standard, 1=Nearest location

    @SPONSORSHIPREASONID uniqueidentifier = null,
    @COMPLETEPENDINGTRANSFERS bit = 0,
    @OVERRIDEPENDINGTRANSFERLOCATION uniqueidentifier = null,
    @UPDATEOPPORTUNITYAVAILABILITY bit = 1,
    @CHANGEAGENTID uniqueidentifier = null,
    @SUCCESSCOUNT int = 0 output,
    @EXCEPTIONCOUNT int = 0 output,
    @SPONSORPREFERENCEOPTION tinyint = 1,  -- 0=Clear conflicts, 1=Overwrite specified, 2=Overwrite all

  @MAINTAINLOCKONTARGETOPPORTUNITY bit = 0
)
as
begin
    set nocount on;

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate()

    if @CHANGEAGENTID is null  
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

    set @SUCCESSCOUNT = 0
    set @EXCEPTIONCOUNT = 0

    declare @OVERRIDEHIERARCHYPATH hierarchyid
    if @COMPLETEPENDINGTRANSFERS = 1 and
       @OVERRIDEPENDINGTRANSFERLOCATION is not null

        select @OVERRIDEHIERARCHYPATH = HIERARCHYPATH
        from dbo.SPONSORSHIPLOCATION
        where ID = @OVERRIDEPENDINGTRANSFERLOCATION

    begin try
        declare @FROMSPONSORSHIPID uniqueidentifier;
        declare @SPONSORSHIPOPPORTUNITYID uniqueidentifier;
        declare @SPONSORSHIPPROGRAMID uniqueidentifier;
        declare @SPONSORSHIPLOCATIONID uniqueidentifier;
        declare @FIXSPONSORSHIPLOCATION bit;
        declare @FIXSPONSORSHIPLOCATIONID uniqueidentifier;
        declare @GENDERCODE tinyint;
        declare @FIXGENDERCODE bit;
        declare @SPONSORSHIPOPPORTUNITYAGERANGEID uniqueidentifier;
        declare @FIXSPONSORSHIPOPPORTUNITYAGERANGEID bit;
        declare @ISHIVPOSITIVECODE tinyint;
        declare @FIXISHIVPOSITIVECODE bit;
        declare @HASCONDITIONCODE tinyint;
        declare @FIXHASCONDITIONCODE bit;
        declare @ISORPHANEDCODE tinyint;
        declare @FIXISORPHANEDCODE bit;
        declare @SPROPPPROJECTCATEGORYCODEID uniqueidentifier;
        declare @FIXSPROPPPROJECTCATEGORYCODEID bit;
        declare @ISSOLESPONSORSHIP bit;
        declare @STATUSCODE tinyint;
        declare @PENDINGSPONSORSHIPID uniqueidentifier;
        declare @OVERRIDE bit;

        declare @TOSPONSORSHIPID uniqueidentifier;

        declare @LOGSUCCESSSQL nvarchar(200);
        declare @LOGSUCCESSPARAMS nvarchar(75);
        declare @LOGEXCEPTIONSQL nvarchar(200);
        declare @LOGEXCEPTIONPARAMS nvarchar(75);
        declare @PLANNEDENDDATE datetime;
    declare @EXPIRATIONREASONID uniqueidentifier;
        set @LOGSUCCESSSQL = N'insert into dbo.' + @SUCCESSTABLE + N' (FROMSPONSORSHIPID, TOSPONSORSHIPID) ' +
                             N'values (@FROMSPONSORSHIPID,@TOSPONSORSHIPID)';
        set @LOGSUCCESSPARAMS = N'@FROMSPONSORSHIPID uniqueidentifier,' +
                                N'@TOSPONSORSHIPID uniqueidentifier';

        set @LOGEXCEPTIONSQL = N'insert into dbo.' + @EXCEPTIONTABLE + N' (FROMSPONSORSHIPID, ERRORMESSAGE) ' +
                               N'values (@FROMSPONSORSHIPID,@ERRORMESSAGE)';
        set @LOGEXCEPTIONPARAMS = N'@FROMSPONSORSHIPID uniqueidentifier,' +
                                  N'@ERRORMESSAGE nvarchar(255)';

        declare @PROGRAMOPPORTUNITYCONSISTENT bit;
        declare @SOLESPONSORSHIPEXCEPTION bit;

    declare @UNLOCKTARGETOPPORTUNITY bit;
    set @UNLOCKTARGETOPPORTUNITY = 1 - @MAINTAINLOCKONTARGETOPPORTUNITY;

        declare SPONSORSHIP_CURSOR cursor local fast_forward for
            select
                TRANSFERS.FROMSPONSORSHIPID,
                TRANSFERS.NEWSPONSORSHIPOPPORTUNITYID,
                isnull(TRANSFERS.NEWSPONSORSHIPPROGRAMID,SPONSORSHIP.SPONSORSHIPPROGRAMID),
                -------------------------

                -- Location

                isnull(TRANSFERS.NEWSPONSORSHIPLOCATIONID,
                       case @SPONSORPREFERENCEOPTION when 2 then null else SPONSORSHIP.SPONSORSHIPLOCATIONID end),
                -- fix location preference?

                case @SPONSORPREFERENCEOPTION
                  when 0 then case
                                -- when original preference is blank and transfer setting is not, retain no preference

                                when SPONSORSHIP.SPONSORSHIPLOCATIONID is null and TRANSFERS.NEWSPONSORSHIPLOCATIONID is not null then 1
                                -- when transferring to a more specific location, retain the less specific location

                                -- when transferring to a conflicting location, set to no preference

                                when SPONSORSHIP.SPONSORSHIPLOCATIONID <> TRANSFERS.NEWSPONSORSHIPLOCATIONID and
                                     (TRANSFERLOCATION.HIERARCHYPATH.IsDescendantOf(OLDPREFERREDLOCATION.HIERARCHYPATH) = 1 or
                                      OLDPREFERREDLOCATION.HIERARCHYPATH.IsDescendantOf(TRANSFERLOCATION.HIERARCHYPATH) = 0) then 1
                                else 0
                              end
                  else 0
                end,
                -- value to set the location preference to, if fixing (see above)

                case
                  when @SPONSORPREFERENCEOPTION = 0 and
                       SPONSORSHIP.SPONSORSHIPLOCATIONID <> TRANSFERS.NEWSPONSORSHIPLOCATIONID and
                       TRANSFERLOCATION.HIERARCHYPATH.IsDescendantOf(OLDPREFERREDLOCATION.HIERARCHYPATH) = 1 then OLDPREFERREDLOCATION.ID
                end,
                -------------------------

                -- Gender

                isnull(TRANSFERS.NEWCHILDGENDERCODE,
                       case @SPONSORPREFERENCEOPTION when 2 then 0 else SPONSORSHIP.CHILDGENDERCODE end),
                -- when no original preference or preferences conflict, set to no preference

                case
                  when @SPONSORPREFERENCEOPTION = 0 and
                       ((SPONSORSHIP.CHILDGENDERCODE = 0 and TRANSFERS.NEWCHILDGENDERCODE is not null) or
                        (SPONSORSHIP.CHILDGENDERCODE > 0 and SPONSORSHIP.CHILDGENDERCODE <> TRANSFERS.NEWCHILDGENDERCODE)) then 1
                  else 0
                end,
                -------------------------

                -- Age range

                isnull(TRANSFERS.NEWSPONSORSHIPOPPORTUNITYAGERANGEID,
                       case @SPONSORPREFERENCEOPTION when 2 then null else SPONSORSHIP.SPONSORSHIPOPPORTUNITYAGERANGEID end),
                case
                  when @SPONSORPREFERENCEOPTION = 0 and
                       isnull(SPONSORSHIP.SPONSORSHIPOPPORTUNITYAGERANGEID,'00000000-0000-0000-0000-000000000000') <> TRANSFERS.NEWSPONSORSHIPOPPORTUNITYAGERANGEID then 1
                  else 0
                end,
                -------------------------

                -- HIV+

                isnull(TRANSFERS.NEWISHIVPOSITIVECODE,
                       case @SPONSORPREFERENCEOPTION when 2 then 0 else SPONSORSHIP.ISHIVPOSITIVECODE end),
                case
                  when @SPONSORPREFERENCEOPTION = 0 and
                       ((SPONSORSHIP.ISHIVPOSITIVECODE = 0 and TRANSFERS.NEWISHIVPOSITIVECODE is not null) or
                        (SPONSORSHIP.ISHIVPOSITIVECODE > 0 and SPONSORSHIP.ISHIVPOSITIVECODE <> TRANSFERS.NEWISHIVPOSITIVECODE)) then 1
                  else 0
                end,
                -------------------------

                -- Condition

                isnull(TRANSFERS.NEWHASCONDITIONCODE,
                       case @SPONSORPREFERENCEOPTION when 2 then 0 else SPONSORSHIP.HASCONDITIONCODE end),
                case
                  when @SPONSORPREFERENCEOPTION = 0 and
                       ((SPONSORSHIP.HASCONDITIONCODE = 0 and TRANSFERS.NEWHASCONDITIONCODE is not null) or
                        (SPONSORSHIP.HASCONDITIONCODE > 0 and SPONSORSHIP.HASCONDITIONCODE <> TRANSFERS.NEWHASCONDITIONCODE)) then 1
                  else 0
                end,
                -------------------------

                -- Orphaned

                isnull(TRANSFERS.NEWISORPHANEDCODE,
                       case @SPONSORPREFERENCEOPTION when 2 then 0 else SPONSORSHIP.ISORPHANEDCODE end),
                case
                  when @SPONSORPREFERENCEOPTION = 0 and
                       ((SPONSORSHIP.ISORPHANEDCODE = 0 and TRANSFERS.NEWISORPHANEDCODE is not null) or
                        (SPONSORSHIP.ISORPHANEDCODE > 0 and SPONSORSHIP.ISORPHANEDCODE <> TRANSFERS.NEWISORPHANEDCODE)) then 1
                  else 0
                end,
                -------------------------

                -- Project category

                isnull(TRANSFERS.NEWSPROPPPROJECTCATEGORYCODEID,
                       case @SPONSORPREFERENCEOPTION when 2 then null else SPONSORSHIP.SPROPPPROJECTCATEGORYCODEID end),
                case
                  when @SPONSORPREFERENCEOPTION = 0 and
                       isnull(SPONSORSHIP.SPROPPPROJECTCATEGORYCODEID,'00000000-0000-0000-0000-000000000000') <> TRANSFERS.NEWSPROPPPROJECTCATEGORYCODEID then 1
                  else 0
                end,
                -------------------------

                SPONSORSHIP.ISSOLESPONSORSHIP,
                SPONSORSHIP.STATUSCODE,
                SPONSORSHIP.PLANNEDENDDATE,
                PENDINGSPONSORSHIP.ID,
                PENDINGLOCATION.HIERARCHYPATH.IsDescendantOf(@OVERRIDEHIERARCHYPATH),
        SPONSORSHIP.EXPIRATIONREASONID
            from #TRANSFERS TRANSFERS
            inner join dbo.SPONSORSHIP on SPONSORSHIP.ID = TRANSFERS.FROMSPONSORSHIPID
            inner join dbo.SPONSORSHIPOPPORTUNITY on SPONSORSHIPOPPORTUNITY.ID = SPONSORSHIP.SPONSORSHIPOPPORTUNITYID
            left outer join dbo.SPONSORSHIPTRANSACTION INITIATETRANSFERTRANSACTION on INITIATETRANSFERTRANSACTION.CONTEXTSPONSORSHIPID = SPONSORSHIP.ID and INITIATETRANSFERTRANSACTION.ACTIONCODE = 6
            left outer join dbo.SPONSORSHIP PENDINGSPONSORSHIP on PENDINGSPONSORSHIP.ID = INITIATETRANSFERTRANSACTION.TARGETSPONSORSHIPID and PENDINGSPONSORSHIP.STATUSCODE = 0
            left outer join dbo.SPONSORSHIPOPPORTUNITY PENDINGOPPORTUNITY on PENDINGOPPORTUNITY.ID = PENDINGSPONSORSHIP.SPONSORSHIPOPPORTUNITYID
            left outer join dbo.SPONSORSHIPLOCATION PENDINGLOCATION on PENDINGLOCATION.ID = PENDINGOPPORTUNITY.SPONSORSHIPLOCATIONID
            left outer join dbo.SPONSORSHIPLOCATION OLDPREFERREDLOCATION on OLDPREFERREDLOCATION.ID = SPONSORSHIP.SPONSORSHIPLOCATIONID
            left outer join dbo.SPONSORSHIPLOCATION TRANSFERLOCATION on TRANSFERLOCATION.ID = TRANSFERS.NEWSPONSORSHIPLOCATIONID
            order by
                -- settings that will be least likely to find matches should be first

                -- (best guess as to which settings those are)

                -- highest number of requests for HIV+, w/ Condition, or Orphaned child first

                case when SPONSORSHIP.ISHIVPOSITIVECODE = 1 then 1 else 2 end +
                 case when SPONSORSHIP.HASCONDITIONCODE = 1 then 1 else 2 end +
                 case when SPONSORSHIP.ISORPHANEDCODE = 1 then 1 else 2 end,
                -- specific location first

                case when isnull(TRANSFERS.NEWSPONSORSHIPLOCATIONID,SPONSORSHIP.SPONSORSHIPLOCATIONID) is not null then 1 else 2 end,
                -- age range specified first

                case when SPONSORSHIP.SPONSORSHIPOPPORTUNITYAGERANGEID is not null then 1 else 2 end,
                -- project category specified first

                case when SPONSORSHIP.SPROPPPROJECTCATEGORYCODEID is not null then 1 else 2 end,
                -- sole sponsorships first

                SPONSORSHIP.ISSOLESPONSORSHIP desc

        open SPONSORSHIP_CURSOR;
        fetch next from SPONSORSHIP_CURSOR into
            @FROMSPONSORSHIPID,
            @SPONSORSHIPOPPORTUNITYID,
            @SPONSORSHIPPROGRAMID,
            @SPONSORSHIPLOCATIONID,
            @FIXSPONSORSHIPLOCATION,
            @FIXSPONSORSHIPLOCATIONID,
            @GENDERCODE,
            @FIXGENDERCODE,
            @SPONSORSHIPOPPORTUNITYAGERANGEID,
            @FIXSPONSORSHIPOPPORTUNITYAGERANGEID,
            @ISHIVPOSITIVECODE,
            @FIXISHIVPOSITIVECODE,
            @HASCONDITIONCODE,
            @FIXHASCONDITIONCODE,
            @ISORPHANEDCODE,
            @FIXISORPHANEDCODE,
            @SPROPPPROJECTCATEGORYCODEID,
            @FIXSPROPPPROJECTCATEGORYCODEID,
            @ISSOLESPONSORSHIP,
            @STATUSCODE,
            @PLANNEDENDDATE,
            @PENDINGSPONSORSHIPID,
            @OVERRIDE,
      @EXPIRATIONREASONID;

        while (@@FETCH_STATUS = 0)
        begin

            set @TOSPONSORSHIPID = null

            set @PROGRAMOPPORTUNITYCONSISTENT = case when @SPONSORSHIPOPPORTUNITYID is null then 1 else dbo.UFN_SPONSORSHIP_PROGRAMANDOPPORTUNITYCONSISTENT(@SPONSORSHIPPROGRAMID,@SPONSORSHIPOPPORTUNITYID) end;

            begin try

                save transaction TRANSFER;

                if @PROGRAMOPPORTUNITYCONSISTENT = 1
                begin   
                    if @STATUSCODE = 1
                    begin       
                          if @PENDINGSPONSORSHIPID is null
                          begin
                              if @TRANSFERTYPE = 0
                                begin
                                  exec dbo.USP_SPONSORSHIP_FULLTRANSFER
                                      @TOSPONSORSHIPID output,
                                      @FROMSPONSORSHIPID,
                                      @SPONSORSHIPREASONID,
                                      @MATCHRULE,
                                      @SPONSORSHIPPROGRAMID,
                                      @SPONSORSHIPLOCATIONID,
                                      @GENDERCODE,
                                      @SPONSORSHIPOPPORTUNITYAGERANGEID,
                                      @ISHIVPOSITIVECODE,
                                      @HASCONDITIONCODE,
                                      @ISORPHANEDCODE,
                                      @SPROPPPROJECTCATEGORYCODEID,
                                      @SPONSORSHIPOPPORTUNITYID,
                                      @ISSOLESPONSORSHIP,
                                      @UPDATEOPPORTUNITYAVAILABILITY,
                                      @CHANGEAGENTID,
                                      @PLANNEDENDDATE,
                    @EXPIRATIONREASONID,
                    @UNLOCKTARGETOPPORTUNITY
                                    end
                              else
                                  begin
                                    exec dbo.USP_SPONSORSHIP_CREATETRANSFER
                                        @TOSPONSORSHIPID output,
                                        @FROMSPONSORSHIPID,
                                        6,
                                        @SPONSORSHIPREASONID,
                                        null,
                                        @MATCHRULE,
                                        @SPONSORSHIPOPPORTUNITYID,
                                        @SPONSORSHIPPROGRAMID,
                                        @SPONSORSHIPLOCATIONID,
                                        @GENDERCODE,
                                        @SPONSORSHIPOPPORTUNITYAGERANGEID,
                                        @ISHIVPOSITIVECODE,
                                        @HASCONDITIONCODE,
                                        @ISORPHANEDCODE,
                                        @SPROPPPROJECTCATEGORYCODEID,
                                        @ISSOLESPONSORSHIP,
                                        null,
                                        @CHANGEAGENTID,
                                        null,
                                        @PLANNEDENDDATE,
                      @EXPIRATIONREASONID,
                      @UNLOCKTARGETOPPORTUNITY
                                    end
                          end
                          else
                          begin
                              -- pending transfer

                              if @COMPLETEPENDINGTRANSFERS = 1
                              begin
                                  if @OVERRIDE = 1
                                      exec dbo.USP_SPONSORSHIP_OVERRIDETRANSFER
                                          @TOSPONSORSHIPID output,
                                          @FROMSPONSORSHIPID,
                                          @PENDINGSPONSORSHIPID,
                                          @SPONSORSHIPREASONID,
                                          @MATCHRULE,
                                          @SPONSORSHIPPROGRAMID,
                                          @SPONSORSHIPLOCATIONID,
                                          @GENDERCODE,
                                          @SPONSORSHIPOPPORTUNITYAGERANGEID,
                                          @ISHIVPOSITIVECODE,
                                          @HASCONDITIONCODE,
                                          @ISORPHANEDCODE,
                                          @SPROPPPROJECTCATEGORYCODEID,
                                          @SPONSORSHIPOPPORTUNITYID,
                                          @ISSOLESPONSORSHIP,
                                          @UPDATEOPPORTUNITYAVAILABILITY,
                                          @CHANGEAGENTID,
                                          @PLANNEDENDDATE,
                                            @EXPIRATIONREASONID,
                      @UNLOCKTARGETOPPORTUNITY
                                  else
                                  begin
                                      exec dbo.USP_SPONSORSHIP_COMPLETETRANSFER
                                          @PENDINGSPONSORSHIPID,
                                          @FROMSPONSORSHIPID,
                                          @UPDATEOPPORTUNITYAVAILABILITY,
                                          @CHANGEAGENTID

                                      set @TOSPONSORSHIPID = @PENDINGSPONSORSHIPID
                                  end
                              end
                              else
                                begin
                                  -- pending transfer found, but not completing pending transfers

                                  raiserror('BBERR_NOTCOMPLETINGPENDINGTRANSFERS',13,1)
                                    end
                        end
                    end             
                    else
                    begin
                        if @STATUSCODE = 0
                            raiserror('BBERR_PENDINGTRANSFERFOUND',13,1)
                        else
                            raiserror('BBERR_INACTIVESPONSORSHIPFOUND',13,1)
                    end
                end
                else 
                   raiserror('BBERR_PROGRAMOPPORTUNITYNOTCONSISTENT',13,1)

                -- fix preferences if necessary

                if @SPONSORPREFERENCEOPTION = 0 and
                   (@FIXSPONSORSHIPLOCATION = 1 or
                    @FIXGENDERCODE = 1 or
                    @FIXSPONSORSHIPOPPORTUNITYAGERANGEID = 1 or
                    @FIXISHIVPOSITIVECODE = 1 or
                    @FIXHASCONDITIONCODE = 1 or
                    @FIXISORPHANEDCODE = 1 or
                    @FIXSPROPPPROJECTCATEGORYCODEID = 1)
                begin
                    update dbo.SPONSORSHIP
                    set SPONSORSHIPLOCATIONID = case @FIXSPONSORSHIPLOCATION when 1 then @FIXSPONSORSHIPLOCATIONID else SPONSORSHIPLOCATIONID end,
                        CHILDGENDERCODE = case @FIXGENDERCODE when 1 then 0 else CHILDGENDERCODE end,
                        SPONSORSHIPOPPORTUNITYAGERANGEID = case @FIXSPONSORSHIPOPPORTUNITYAGERANGEID when 1 then null else SPONSORSHIPOPPORTUNITYAGERANGEID end,
                        ISHIVPOSITIVECODE = case @FIXISHIVPOSITIVECODE when 1 then 0 else ISHIVPOSITIVECODE end,
                        HASCONDITIONCODE = case @FIXHASCONDITIONCODE when 1 then 0 else HASCONDITIONCODE end,
                        ISORPHANEDCODE = case @FIXISORPHANEDCODE when 1 then 0 else ISORPHANEDCODE end,
                        SPROPPPROJECTCATEGORYCODEID = case @FIXSPROPPPROJECTCATEGORYCODEID when 1 then null else SPROPPPROJECTCATEGORYCODEID end,
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CURRENTDATE
                    where ID = @TOSPONSORSHIPID;
                end

                -- log the successful transfer

                exec sp_executesql @LOGSUCCESSSQL,
                                   @LOGSUCCESSPARAMS,
                                   @FROMSPONSORSHIPID,
                                   @TOSPONSORSHIPID

                set @SUCCESSCOUNT = @SUCCESSCOUNT + 1
            end try
            begin catch
                rollback transaction TRANSFER

                declare @MSG nvarchar(255)
                set @MSG = ERROR_MESSAGE()

                exec sp_executesql @LOGEXCEPTIONSQL,
                                   @LOGEXCEPTIONPARAMS,
                                   @FROMSPONSORSHIPID,
                                   @MSG

                set @EXCEPTIONCOUNT = @EXCEPTIONCOUNT + 1
            end catch

            fetch next from SPONSORSHIP_CURSOR into
                @FROMSPONSORSHIPID,
                @SPONSORSHIPOPPORTUNITYID,
                @SPONSORSHIPPROGRAMID,
                @SPONSORSHIPLOCATIONID,
                @FIXSPONSORSHIPLOCATION,
                @FIXSPONSORSHIPLOCATIONID,
                @GENDERCODE,
                @FIXGENDERCODE,
                @SPONSORSHIPOPPORTUNITYAGERANGEID,
                @FIXSPONSORSHIPOPPORTUNITYAGERANGEID,
                @ISHIVPOSITIVECODE,
                @FIXISHIVPOSITIVECODE,
                @HASCONDITIONCODE,
                @FIXHASCONDITIONCODE,
                @ISORPHANEDCODE,
                @FIXISORPHANEDCODE,
                @SPROPPPROJECTCATEGORYCODEID,
                @FIXSPROPPPROJECTCATEGORYCODEID,
                @ISSOLESPONSORSHIP,
                @STATUSCODE,
                @PLANNEDENDDATE,
                @PENDINGSPONSORSHIPID,
                @OVERRIDE,
        @EXPIRATIONREASONID;
        end
        close SPONSORSHIP_CURSOR;
        deallocate SPONSORSHIP_CURSOR;
    end try
    begin catch
      exec dbo.USP_RAISE_ERROR;
      return 1;
    end catch

    return 0;
end