USP_REGISTRANTREGISTRATION_CHANGEGOAL

CREATE procedure dbo.USP_REGISTRANTREGISTRATION_CHANGEGOAL
(
  @ID uniqueidentifier, --RegistrantID

  @GROUPID uniqueidentifier,
  @ROLECODE tinyint,
  @REGISTRATIONOPTIONID uniqueidentifier,
  @FUNDRAISINGGOAL money ,
    @TARGETFUNDRAISINGGOAL money ,
    @PARTICIPANTRECRUITGOAL int,
    @DONORRETENTIONGOAL decimal(7,2) ,
    @COMMUNICATIONGOAL int ,
    @GROUPFUNDRAISINGGOAL money , 
    @GROUPPARTICIPANTRECRUITMENTGOAL int, 
    @GROUPDONORRETENTIONGOAL decimal(7,2),
  @GROUPTEAMMEMBERSGOAL int,
    @GROUPTEAMMEMBERSRETENTIONGOAL decimal(7,2),
    @GROUPTEAMSGOAL int,
    @GROUPTEAMSRETENTIONGOAL decimal(7,2),
    @GROUPCOMMUNICATIONGOAL int,
  @SKIPPERSONALGOALS bit = 0, -- 1 when only change group goals

  @SKIPGROUPGOALS bit = 0
)
as
  declare @R_FUNDRAISINGGOAL money ,
            @R_TARGETFUNDRAISINGGOAL money ,
            @R_PARTICIPANTRECRUITGOAL int,
            @R_DONORRETENTIONGOAL decimal(7,2) ,
            @R_COMMUNICATIONGOAL int ,

            @R_GROUPFUNDRAISINGGOAL money , 
            @R_GROUPPARTICIPANTRECRUITMENTGOAL int, 
            @R_GROUPDONORRETENTIONGOAL decimal(7,2),
            @R_GROUPTEAMMEMBERSGOAL int,
            @R_GROUPTEAMMEMBERSRETENTIONGOAL decimal(7,2),
            @R_GROUPTEAMSGOAL int,
            @R_GROUPTEAMSRETENTIONGOAL decimal(7,2),
            @R_GROUPCOMMUNICATIONGOAL int,
            @REGTYPEGOAL tinyint,
            @ISNOTLOWERFUNDRAISING bit,
            @ISNOTLOWERRECRUITEMENT bit,
            @ISNOTLOWERDONORRETENTION bit,
            @ISNOTLOWERCOMMUNICATION bit,
            @ISNOTLOWERTEAMMEMBERS bit,
            @ISNOTLOWERTEAMMEMBERRETENTION bit,
            @ISNOTLOWERTEAMS bit,
            @ISNOTLOWERTEAMRETENTION bit,
            @ISNOTLOWERTARGETFUNDRAISING bit,
          @CHANGEAGENTID uniqueidentifier,
          @CURRENTDATE datetime


begin try

        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output
    set @CURRENTDATE = getdate()

  if (@ROLECODE in (0,1,2,3,4,5) And @SKIPPERSONALGOALS = 0) 
  begin
    --if @ROLECODE = 3

    --  set @REGTYPEGOAL = 3

    --else

    set @REGTYPEGOAL = 0

    select    @R_FUNDRAISINGGOAL=MINFUNDRAISING, 
                @R_TARGETFUNDRAISINGGOAL=TARGETFUNDRAISING,
                @R_PARTICIPANTRECRUITGOAL=RECRUITEMENT,
                @R_DONORRETENTIONGOAL=DONORRETENTION * 100,
                @R_COMMUNICATIONGOAL=COMMUNICATIONS,
                @ISNOTLOWERFUNDRAISING = ISNOTLOWERFUNDRAISING,
                @ISNOTLOWERTARGETFUNDRAISING = ISNOTLOWERTARGETFUNDRAISING,
                @ISNOTLOWERRECRUITEMENT = ISNOTLOWERRECRUITEMENT,
                @ISNOTLOWERDONORRETENTION = ISNOTLOWERDONORRETENTION,
                @ISNOTLOWERCOMMUNICATION = ISNOTLOWERCOMMUNICATION
    from FAFREgistrationTypeGoal 
    where EVENTPRICEID = @REGISTRATIONOPTIONID AND FAFREGISTRATIONTYPEGOAL.REGTYPEGOALCODE = @REGTYPEGOAL

    if (@FUNDRAISINGGOAL < @R_FUNDRAISINGGOAL and @ISNOTLOWERFUNDRAISING = 1) 
      raiserror('MINFUNDRAISINGVIOLATION', 13, 1)

    if (@TARGETFUNDRAISINGGOAL < @R_FUNDRAISINGGOAL and @ISNOTLOWERFUNDRAISING = 1)
      raiserror('TARGETFUNDRAISINGVIOLATION', 13, 1)

    if (@PARTICIPANTRECRUITGOAL < @R_PARTICIPANTRECRUITGOAL and @ISNOTLOWERRECRUITEMENT = 1) 
      raiserror('RECRUITEMENTVIOLATION', 13, 1)    

   -- if (@DONORRETENTIONGOAL < @R_DONORRETENTIONGOAL and @ISNOTLOWERDONORRETENTION = 1) 

  --    raiserror('DONORRETENTIONVIOLATION', 13, 1)


    if (@COMMUNICATIONGOAL < @R_COMMUNICATIONGOAL and @ISNOTLOWERCOMMUNICATION = 1)
      raiserror('COMMUNICATIONVIOLATION', 13, 1)
  end

  if (@ROLECODE in (0,1,3) and @SKIPGROUPGOALS = 0)
  begin
    if @ROLECODE = 0
      set @REGTYPEGOAL = 2

    if @ROLECODE in (1,3)
      set @REGTYPEGOAL = @ROLECODE

    select    @R_GROUPFUNDRAISINGGOAL = MINFUNDRAISING , 
              @R_GROUPPARTICIPANTRECRUITMENTGOAL = RECRUITEMENT, 
              @R_GROUPDONORRETENTIONGOAL = DONORRETENTION * 100,
              @R_GROUPTEAMMEMBERSGOAL = TEAMMEMBERS,
            @R_GROUPTEAMMEMBERSRETENTIONGOAL = TEAMMEMBERRETENTION * 100,
              @R_GROUPTEAMSGOAL = TEAMS,
              @R_GROUPTEAMSRETENTIONGOAL = TEAMRETENTION * 100,
              @R_GROUPCOMMUNICATIONGOAL = COMMUNICATIONS,
              @ISNOTLOWERFUNDRAISING = ISNOTLOWERFUNDRAISING,
              @ISNOTLOWERRECRUITEMENT = ISNOTLOWERRECRUITEMENT,
              @ISNOTLOWERDONORRETENTION = ISNOTLOWERDONORRETENTION,
              @ISNOTLOWERTEAMMEMBERS = ISNOTLOWERTEAMMEMBERS,
              @ISNOTLOWERTEAMMEMBERRETENTION = ISNOTLOWERTEAMMEMBERRETENTION,
              @ISNOTLOWERTEAMS = ISNOTLOWERTEAMS,
              @ISNOTLOWERTEAMRETENTION = ISNOTLOWERTEAMRETENTION,
                @ISNOTLOWERCOMMUNICATION = ISNOTLOWERCOMMUNICATION
    from FAFREgistrationTypeGoal 
    where EVENTPRICEID = @REGISTRATIONOPTIONID AND FAFREGISTRATIONTYPEGOAL.REGTYPEGOALCODE = @REGTYPEGOAL 

    if (@GROUPFUNDRAISINGGOAL < @R_GROUPFUNDRAISINGGOAL and @ISNOTLOWERFUNDRAISING = 1) 
      raiserror('GROUPFUNDRAISINGVIOLATION', 13, 1)  

    if (@GROUPPARTICIPANTRECRUITMENTGOAL < @R_GROUPPARTICIPANTRECRUITMENTGOAL and @ISNOTLOWERRECRUITEMENT = 1) 
      raiserror('GROUPRECRUITEMENTVIOLATION', 13, 1)    

    if (@GROUPDONORRETENTIONGOAL < @R_GROUPDONORRETENTIONGOAL and @ISNOTLOWERDONORRETENTION = 1) 
      raiserror('GROUPDONORRETENTIONVIOLATION', 13, 1)

    if (@GROUPTEAMMEMBERSGOAL < @R_GROUPTEAMMEMBERSGOAL  and @ISNOTLOWERTEAMMEMBERS = 1)
      raiserror('TEAMMEMBERGOALVIOLATION', 13, 1)

    if (@GROUPTEAMMEMBERSRETENTIONGOAL < @R_GROUPTEAMMEMBERSRETENTIONGOAL and @ISNOTLOWERTEAMMEMBERS = 1)
      raiserror('TEAMMEMBERRETENTIONVIOLATION', 13, 1)

    if (@GROUPTEAMSGOAL < @R_GROUPTEAMSGOAL and @ISNOTLOWERTEAMS = 1)
      raiserror('TEAMSVIOLATION', 13, 1)

    if (@GROUPTEAMSRETENTIONGOAL < @R_GROUPTEAMSRETENTIONGOAL and @ISNOTLOWERTEAMRETENTION = 1)
      raiserror('TEAMSRETENTIONVIOLATION', 13, 1)

    if (@GROUPCOMMUNICATIONGOAL < @R_GROUPCOMMUNICATIONGOAL and @ISNOTLOWERCOMMUNICATION = 1)
      raiserror('GROUPCOMMUNICATIONVIOLATION', 13, 1)

  end                

 --if @ROLECODE = 4

  --begin

  --  set @FUNDRAISINGGOAL = 0

  --  set @PARTICIPANTRECRUITGOAL = 0

  --  set @COMMUNICATIONGOAL = 0

  --  set @TARGETFUNDRAISINGGOAL = 0

  --  set @DONORRETENTIONGOAL = 0

  --end


  if @SKIPPERSONALGOALS = 0
  begin
      update dbo.REGISTRANTEXTENSION
      set FUNDRAISINGGOAL = @FUNDRAISINGGOAL,
          MEMBERECRUITMENTGOAL = @PARTICIPANTRECRUITGOAL,
          COMMUNICATIONGOAL = @COMMUNICATIONGOAL,
          TARGETFUNDRAISINGGOAL = @TARGETFUNDRAISINGGOAL,
          DONORRETENTIONGOAL = @DONORRETENTIONGOAL / 100,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where REGISTRANTID = @ID
  end

  if (@ROLECODE in (0,1,3) AND @SKIPGROUPGOALS = 0)
  begin    
    update dbo.TEAMFUNDRAISINGTEAM
    set GOAL = @GROUPFUNDRAISINGGOAL
    where ID = @GROUPID

    update dbo.TEAMEXTENSION
    set TARGETFUNDRAISINGGOAL = @GROUPFUNDRAISINGGOAL,
        PARTICIPANTGOAL = @GROUPPARTICIPANTRECRUITMENTGOAL,
        TEAMMEMBERGOAL = @GROUPTEAMMEMBERSGOAL,
        PCTTEAMMEMBERRETENSION = @GROUPTEAMMEMBERSRETENTIONGOAL / 100,
        TEAMRECRUITMENTGOAL = @GROUPTEAMSGOAL,
        PCTTEAMSRETENSION = @GROUPTEAMSRETENTIONGOAL / 100,
        COMMUNICATIONGOAL = @GROUPCOMMUNICATIONGOAL,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
    where TEAMFUNDRAISINGTEAMID = @GROUPID    
  end
  --else

  --  if @ROLECODE = 3

  --  begin

  --    update dbo.TEAMFUNDRAISINGTEAM

  --    set GOAL = @FUNDRAISINGGOAL

  --    where ID = @GROUPID


  --    update dbo.TEAMEXTENSION

  --    set TARGETFUNDRAISINGGOAL = @TARGETFUNDRAISINGGOAL,

  --        PARTICIPANTGOAL = @PARTICIPANTRECRUITGOAL,

  --        COMMUNICATIONGOAL = @COMMUNICATIONGOAL,

  --        DONORRETENTIONGOAL = @DONORRETENTIONGOAL / 100

  --    where TEAMFUNDRAISINGTEAMID = @GROUPID 


  --    if @SKIPPERSONALGOALS = 0

  --    begin

  --      update dbo.REGISTRANTEXTENSION

  --      set FUNDRAISINGGOAL = @TARGETFUNDRAISINGGOAL,

  --          MEMBERECRUITMENTGOAL = @PARTICIPANTRECRUITGOAL,

  --          COMMUNICATIONGOAL = @COMMUNICATIONGOAL,

  --          TARGETFUNDRAISINGGOAL = @TARGETFUNDRAISINGGOAL,

  --          DONORRETENTIONGOAL = @DONORRETENTIONGOAL / 100

  --      where REGISTRANTID = @ID

  --    end


  --  end


end try
begin catch
  exec dbo.USP_RAISE_ERROR;
    return 1;
end catch
return 0;