USP_FAFEVENTDEFAULTDESIGNATION_ADD_EDIT

CREATE procedure dbo.USP_FAFEVENTDEFAULTDESIGNATION_ADD_EDIT
(
  @DESIGNATIONLEVELID uniqueidentifier = null output,
  @EVENTID uniqueidentifier,
  @APPEALID uniqueidentifier,
  @NAME nvarchar(100),
  @DESCRIPTION nvarchar(255),
  @LOOKUPID nvarchar(200) = null,  -- USERID

  @CURRENTAPPUSERID uniqueidentifier,
  @SITEID uniqueidentifier = null,
  @CHANGEAGENTID uniqueidentifier
)
as
    declare @DESIGNATIONLEVELTYPEID uniqueidentifier
  declare @DESIGNATIONHIERARCHYID uniqueidentifier
  declare @APPEALDESIGNATIONS nvarchar(250)
  declare @CURRENTDATE datetime
  declare @DESIGNATIONLEVELNAME nvarchar(100)

  set @CURRENTDATE = getdate()

begin try

if @DESIGNATIONLEVELID is null   
  begin

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

    if not exists(SELECT 1 FROM dbo.DESIGNATIONLEVELTYPE where [DESCRIPTION] = 'FAF event fundraising')
    begin
      set @DESIGNATIONLEVELTYPEID = newid();
      insert into dbo.DESIGNATIONLEVELTYPE
                (ID, DESCRIPTION, HASADMINISTRATOR, HASRECIPIENTS, HASDONORINFORMATION, HASFINANCIALINFORMATION, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            values
                (@DESIGNATIONLEVELTYPEID, 'FAF event fundraising', 1, 1, 1, 1, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);
    end
    else
        SELECT @DESIGNATIONLEVELTYPEID = ID FROM dbo.DESIGNATIONLEVELTYPE where [DESCRIPTION] = 'FAF event fundraising'

    if @SITEID is null
        begin 
        if dbo.UFN_SITEREQUIREDFORUSER(@CURRENTAPPUSERID) = 1 
            begin
            raiserror('Site is required.',13,1)
            return
            end
        end

    select @DESIGNATIONLEVELNAME = NAME, @DESIGNATIONLEVELID = ID from dbo.DESIGNATIONLEVEL where NAME = @NAME    
    if @DESIGNATIONLEVELNAME is null
    begin
      set @DESIGNATIONLEVELID = newid();    

          insert into dbo.DESIGNATIONLEVEL
              (ID,NAME,DESCRIPTION,DESIGNATIONLEVELCATEGORYCODEID,DESIGNATIONLEVELTYPEID,STEWARDSHIPPACKAGEPROCESSID,ADMINISTRATORID,USERID,DESIGNATIONREPORT1CODEID,DESIGNATIONREPORT2CODEID,SITEID,VANITYNAME,TIMEPERIODCODE,ISENDOWED,ISFULLYFUNDED,ISINCOMETOPRINCIPAL,INCOMETOPRINCIPALPERCENT,STATEMENTWORDING,ENDOWMENTMINAMOUNT,ENDOWMENTTARGETDATE,ENDOWMENTMINAMOUNTDATEMET,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
          VALUES
              (@DESIGNATIONLEVELID,@NAME,@DESCRIPTION,null,@DESIGNATIONLEVELTYPEID,null,null,@LOOKUPID,null,null,@SITEID,'', 0, 0, 0, 0, 0.00, '', 0, null, null, @CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE)
    end  

    select @DESIGNATIONHIERARCHYID = ID from dbo.DESIGNATION where DESIGNATIONLEVEL1ID = @DESIGNATIONLEVELID and DESIGNATIONLEVEL2ID is null and DESIGNATIONLEVEL3ID is null and DESIGNATIONLEVEL4ID is null and DESIGNATIONLEVEL5ID is null
    if @DESIGNATIONHIERARCHYID is null
    begin
      set @DESIGNATIONHIERARCHYID = newid();

      insert into dbo.DESIGNATION
            (ID,DESIGNATIONLEVEL1ID,USERID,DESIGNATIONREPORT1CODEID,DESIGNATIONREPORT2CODEID,VANITYNAME,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
        VALUES
            (@DESIGNATIONHIERARCHYID,@DESIGNATIONLEVELID,@LOOKUPID,null,null,@NAME,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE);        
    end

    set @APPEALDESIGNATIONS = N'<?xml version="1.0" encoding="utf-16"?><APPEALDESIGNATIONS><ITEM><DESIGNATIONID>' + cast(@DESIGNATIONHIERARCHYID as varchar(40)) + '</DESIGNATIONID><ISDEFAULT>True</ISDEFAULT></ITEM></APPEALDESIGNATIONS>'

    if(select count(DESIGNATIONID) from dbo.UFN_APPEAL_GETDESIGNATIONS_FROMITEMLISTXML(@APPEALDESIGNATIONS) where ISDEFAULT = 1) > 1
          raiserror('Only one designation can be set as the default for an appeal.', 13, 2);

        exec dbo.USP_APPEAL_GETDESIGNATIONS_UPDATEFROMXML @APPEALID, @APPEALDESIGNATIONS, @CHANGEAGENTID            

  end
  else
    begin
      update dbo.APPEAL
      set 
        NAME = @NAME,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where ID = @APPEALID

      update dbo.DESIGNATIONLEVEL
      set 
        NAME = @NAME,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where ID = @DESIGNATIONLEVELID;

      /*
      update dbo.DESIGNATION
      set 
        USERID = @LOOKUPID,
        VANITYNAME = @LOOKUPID,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where DESIGNATIONLEVEL1ID = @DESIGNATIONLEVELID; 
      */  
    end
end try

begin catch
    exec dbo.USP_RAISE_ERROR
    return 1
end catch

return 0