USP_EVENTSPONSOR_EDITSAVE2

CREATE procedure dbo.USP_EVENTSPONSOR_EDITSAVE2
(
    @ID uniqueidentifier,
    @CURRENTAPPUSERID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @NEWSPONSORCONSTITUENTID uniqueidentifier = null output,
    @CONSTITUENTID uniqueidentifier,
    @EVENTSPONSORSHIPLOGO varbinary(max) = null,
    @PICTURECHANGED bit = 0,
    @EVENTSPONSORSHIPURL nvarchar(4000) = null,
    @SPONSORSHIPMESSAGE nvarchar(2000) = null,
    @ORGANIZATIONCONSTITUENTID uniqueidentifier = null,
    @ORGANIZATIONWEBADDRESS nvarchar(4000) = null,
    @EVENTSPONSORSHIPTYPEID uniqueidentifier,
    @FEESWAIVED bit = 0,
    @BENEFITSWAIVED bit = 0,
    @PASSWORD nvarchar(255),
  @ALLOWADMINACCESSHQ bit = 0
)
as
begin
    -- do work


  set nocount on;

    declare @CURRENTDATE datetime;
    set @CURRENTDATE = getdate();

  declare @CONTINUE bit;
  set @CONTINUE = 1;

  begin try
    if exists (select 1 from dbo.EVENTSPONSOR(nolock) where ID <> @ID and CONSTITUENTID = @CONSTITUENTID)
    begin
      set @CONTINUE = 0;
      raiserror('Cannot save. Selected individual can only sponse once in the event.', 13, 1);
    end

    if exists (select 1 from dbo.EVENTSPONSOR(nolock) where ID <> @ID and ORGANIZATIONCONSTITUENTID = @ORGANIZATIONCONSTITUENTID)
    begin
      set @CONTINUE = 0;
      raiserror('Cannot save. Selected organization can only sponse once in the event.', 13, 1);
    end

    if @CONTINUE = 1
    begin

      declare @OLDCONSTITUENTID uniqueidentifier;
      declare @OLDORGANIZATIONCONSTITUENTID uniqueidentifier;
      declare @BENEFITSWASWAIVED bit;
      declare @SPONSORSHIPLOGOID int;
      declare @OLDSPONSORSHIPTYPEID uniqueidentifier;
      declare @FEESAMOUNT money;
      declare @CLIENTSITESID int;
      declare @SPONSORNAME nvarchar(100);
      declare @OLDALLOWADMINACCESSHQ bit;

      select @OLDCONSTITUENTID = ES.CONSTITUENTID,
              @OLDORGANIZATIONCONSTITUENTID = ES.ORGANIZATIONCONSTITUENTID,
              @BENEFITSWASWAIVED = ES.BENEFITSWAIVED,
              @SPONSORSHIPLOGOID = ES.EVENTSPONSORSHIPLOGOID,
              @OLDSPONSORSHIPTYPEID = ES.EVENTSPONSORSHIPTYPEID,
          @FEESAMOUNT = ES.FEESAMOUNT,
              @CLIENTSITESID = EX.CLIENTSITESID,
              @SPONSORNAME = isnull(isnull(OC.NAME, IC.NAME), ''),
          @OLDALLOWADMINACCESSHQ = ES.ALLOWADMINACCESSHQ
      from dbo.EVENTSPONSOR ES(nolock)
      left outer join EVENTEXTENSION EX on EX.EVENTID = ES.EVENTID
      left outer join dbo.CONSTITUENT IC(nolock) on IC.ID = ES.CONSTITUENTID
      left outer join dbo.CONSTITUENT OC(nolock) on OC.ID = ES.ORGANIZATIONCONSTITUENTID
      where ES.ID = @ID;    

      if @ORGANIZATIONCONSTITUENTID is null
      begin
          if @OLDCONSTITUENTID != @CONSTITUENTID
            set @NEWSPONSORCONSTITUENTID = @CONSTITUENTID;
      end
      else
      begin
          if @OLDORGANIZATIONCONSTITUENTID != @ORGANIZATIONCONSTITUENTID
            set @NEWSPONSORCONSTITUENTID = @ORGANIZATIONCONSTITUENTID;
      end

      if @BENEFITSWAIVED = 1    and @BENEFITSWASWAIVED = 0
      begin
          delete from dbo.SPONSORBENEFITEXTENSION where SPONSORID = @ID;
      end;

      if @BENEFITSWAIVED = 0 and
        (@BENEFITSWASWAIVED = 1 or @OLDSPONSORSHIPTYPEID != @EVENTSPONSORSHIPTYPEID)
      begin

          delete from dbo.SPONSORBENEFITEXTENSION where SPONSORID = @ID;

          insert into dbo.SPONSORBENEFITEXTENSION(
              [ID]
              ,[SPONSORID]
              ,[BENEFITID]
              ,[EVENTSPONSORSHIPTYPEID]
              ,[QUANTITY]
              ,[UNITVALUE]
              ,[TOTALVALUE]
              ,[BASECURRENCYID]
              ,[ADDEDBYID]
              ,[CHANGEDBYID]
              ,[DATEADDED]
              ,[DATECHANGED]
          )
          select
               newid()
              ,@ID
              ,ESTB.[BENEFITID]
              ,ESTB.[EVENTSPONSORSHIPTYPEID]
              ,ESTB.[QUANTITY]
              ,B.VALUE
              ,B.VALUE * ESTB.QUANTITY
              ,B.[BASECURRENCYID]
              ,@CHANGEAGENTID
              ,@CHANGEAGENTID
              ,@CURRENTDATE
              ,@CURRENTDATE
          from dbo.EVENTSPONSORSHIPTYPEBENEFIT ESTB(nolock)
          inner join dbo.BENEFIT B(nolock) on B.ID = ESTB.BENEFITID
          where EVENTSPONSORSHIPTYPEID = @EVENTSPONSORSHIPTYPEID;

      end;

      if @ORGANIZATIONCONSTITUENTID is not null
        update dbo.CONSTITUENT
        set WEBADDRESS = @ORGANIZATIONWEBADDRESS
        where ID = @ORGANIZATIONCONSTITUENTID;

        declare @BBNCUSERID int;
      select @BBNCUSERID = CU.ID from BBNCUSERMAP UM(nolock)
      inner join ClientUsers CU(nolock) on CU.UserName = UM.BBNCUSERNAME
      where UM.ID = @CURRENTAPPUSERID;

      if @SPONSORSHIPLOGOID is null 
        set @SPONSORSHIPLOGOID = 0;

      if @PICTURECHANGED = 1
      begin
        if @SPONSORSHIPLOGOID = 0 or not exists(select 1 from dbo.SiteImages(nolock) where ID = @SPONSORSHIPLOGOID)
        begin
          if @CLIENTSITESID is not null
          begin
            declare @SITEIMAGESGUID uniqueidentifier;
            set @SITEIMAGESGUID = newid();

            insert into dbo.SiteImages(
              ClientSitesID,
              Name,
              [FileName],
              ImageContent, 
              [Guid],
              OwnerID,
              Caption,
              Photographer,
              PhotoDateTaken)
            values (
              @CLIENTSITESID,
              @SPONSORNAME + ' sponsorship logo',
              '',
              @EVENTSPONSORSHIPLOGO, 
              @SITEIMAGESGUID,
              @BBNCUSERID,
              '',
              '',
              '');

            select @SPONSORSHIPLOGOID = ID
            from dbo.SiteImages(nolock)
            where [Guid] = @SITEIMAGESGUID;
          end
        end
        else
        begin
          update dbo.SiteImages
          set ImageContent = @EVENTSPONSORSHIPLOGO
          where ID = @SPONSORSHIPLOGOID;
        end
      end

      if @OLDSPONSORSHIPTYPEID != @EVENTSPONSORSHIPTYPEID
        set @FEESAMOUNT = dbo.UFN_SPONSORSHIPTYPE_GETFEEAMOUNTBYID(@EVENTSPONSORSHIPTYPEID);

      update
          dbo.EVENTSPONSOR
      set
          CONSTITUENTID = @CONSTITUENTID,
          EVENTSPONSORSHIPLOGOID = @SPONSORSHIPLOGOID,
          EVENTSPONSORSHIPURL = @EVENTSPONSORSHIPURL,
          SPONSORSHIPMESSAGE = @SPONSORSHIPMESSAGE,
          ORGANIZATIONCONSTITUENTID = @ORGANIZATIONCONSTITUENTID,
          EVENTSPONSORSHIPTYPEID = @EVENTSPONSORSHIPTYPEID,
          FEESAMOUNT = @FEESAMOUNT,
          FEESWAIVED = @FEESWAIVED,
          BENEFITSWAIVED = @BENEFITSWAIVED,
          CHANGEDBYID = @CHANGEAGENTID,
          DATECHANGED = @CURRENTDATE,
        ALLOWADMINACCESSHQ = @ALLOWADMINACCESSHQ
      where
          ID = @ID;

      declare @CLIENTUSERSID int;

      select @CLIENTUSERSID = dbo.fnGetUserIDFromLinkedRecordID(C.SEQUENCEID, 0)
      from dbo.CONSTITUENT C
      where C.ID = @CONSTITUENTID;

      if @CLIENTUSERSID is null
        exec dbo.USP_FAFEVENT_ADDCLIENTUSERFORINDIVIDUAL
            @CONSTITUENTID = @CONSTITUENTID,
            @Password = @PASSWORD;

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

  return 0;
end