USP_DATAFORMTEMPLATE_VIEW_FAFEVENT_HOME

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_FAFEVENT_HOME
(
    @ID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier=null,
    @DATALOADED bit = 0 output,
  @EVENTID uniqueidentifier = null output,
  @GOALISSET bit = null output,
  @SOCIALNETWORKISSET bit = null output,
  @LABELISSET bit = null output,
  @MERCHANTACCOUNTISSET bit = null output,
  @TEAMHIERARCHYISSET bit = null output,
  @SPONSORSHIPOPTIONISSET bit = null output,
  @WAIVERISSET bit = null output,
  @REGISTRATIONOPTIONISSET bit = null output,
  @INCENTIVEOPTIONISSET bit = null output,
  @INCENTIVELEVELISSET bit = null output,
  @DONATIONOPTIONSISSET bit = null output,
  @REVENUERULESISSET bit = null output,  
  @FUNDRAISINGCOACHISSET bit = null output,
  @REGCFMISSET bit = null output,
  @SPOCFMISSET bit = null output,
  @DONCFMISSET bit = null output,
  @VOLCFMISSET bit = null output,
  @PARTICIPANTNOTIFICATIONISSET bit = null output,
  @PARTICIPANTCOMMUNICATIONISSET bit = null output,
  @MANAGEWEBSITEISSET bit = null output,
  @CONTACTFMISSET bit = null output,
  @FORMISSET bit = null output,
  @SCHEDULEMESSAGEISSET bit = null output,
  @ISMAPBBNCUSER bit = null output,
  @CLIENTSITESID int = null output,
  @INTEGRATIONURL nvarchar(1024) = null output,
  @AID int = null output, -- client user id (abstract term since we want to use it in web UI)-- 
  @REGFORMID uniqueidentifier = null output,
  @DONFORMID uniqueidentifier = null output,
  @SPONFORMID uniqueidentifier = null output,
  @CONTACTFORMID uniqueidentifier = null output,
  @PROFILEFORMID uniqueidentifier = null output,
  @ISMANAGETHEMEDISABLED bit = null output,
  @PREFERENCESFORMID uniqueidentifier = null output,
  @ISPUBLISHED bit = null output,
  @ISLOADED bit = null output,
  @ISIMPORTSUCCESS bit = null output,
  @ISIMPORTING bit = null output,
  @CONFIRMATIONISSET bit = null output,
  @ACKNOWLEDGEMENTISSET bit = null output,
  @SOCIALNETWORKPUBLISHISSET bit = null output
)
as
    set nocount on;

  declare @FAFEventAdminRoleId int,
          @IsFAFEventAdmin bit,
          @ClientUserId int,
          @importuser uniqueidentifier,
          @lasterror nvarchar(500)

    -- be sure to set this, in case the select returns no rows
    set @DATALOADED = 0;

  if ((select value from dbo.CMSSITESETTING where CLIENTSITESID in (select CLIENTSITESID from dbo.EVENTEXTENSION where EVENTID = @ID) and ENUMID = 35) = 'True')
    set @ISMANAGETHEMEDISABLED=1

  -- be sure to select default apexform template
  if OBJECT_ID('tempdb.dbo.#ApexTmp') is not null
  begin 
      drop table #ApexTmp
  end

  select AF.ID, AF.NAME, AF.DATEADDED , AF.FORMTYPEID, AF.SITEID  into #ApexTmp from APEXFORM AF 
  inner join EVENTEXTENSION  EX on AF.SITEID = EX.CLIENTSITESID  
  where EX.EVENTID = @ID and AF.NAME not like '%Mobile%'
  and NOT EXISTS (select * from APEXFORM AT INNER JOIN EVENTEXTENSION  EX on AT.SITEID = EX.CLIENTSITESID  
                          where EX.EVENTID = @ID and AT.FORMTYPEID = AF.FORMTYPEID and AT.DATEADDED < AF.DATEADDED) 

  select  @DATALOADED = 1,
          @EVENTID = EX.EVENTID,
          @INTEGRATIONURL = dbo.UFN_BBNC_URL(),
          @ISLOADED = dbo.UFN_FAFWEBSITE_ISIMPORTED(@ID),
          @ISIMPORTSUCCESS = dbo.UFN_FAFWEBSITE_ISIMPORTSUCCESS(@ID), /* always return 1 unless import actually failed */
          @ISIMPORTING = case when isnull(EI.ISIMPORTINGBYUSERID,@CURRENTAPPUSERID) = @CURRENTAPPUSERID then 0 else 1 end,
          @importuser = EI.ISIMPORTINGBYUSERID,
          @CLIENTSITESID=CLIENTSITESID,
          @ISPUBLISHED = dbo.UFN_CLIENTSITES_GETEVENTWEBSITESTATUS(@ID),    
          @FORMISSET = case when REG.ID is not null or SPON.ID is not null or DON.ID is not null or CONTACT.ID is not null or PROF.ID is not null or PREF.ID is not null then 1 else 0 end,
          @REGFORMID = case when REGNEW.ID is not null then REGNEW.ID else REG.ID end, 
          @SPONFORMID = SPON.ID, 
          @DONFORMID = DON.ID, 
          @CONTACTFORMID = CONTACT.ID, 
          @PROFILEFORMID = PROF.ID,
          @PREFERENCESFORMID = PREF.ID
  from dbo.EVENTEXTENSION EX  
  left join dbo.EVENTIMPORT EI on EX.EVENTID = EI.ID
  left join #ApexTmp REG ON EX.CLIENTSITESID = REG.SITEID AND REG.FORMTYPEID = '34C019D8-9CD0-4321-9778-09BD145A3E57'
  left join #ApexTmp REGNEW ON EX.CLIENTSITESID = REGNEW.SITEID AND REGNEW.FORMTYPEID = '0B29CBAA-0B53-47CC-B6B9-B96311299584'
  left join #ApexTmp SPON ON EX.CLIENTSITESID = SPON.SITEID and SPON.FORMTYPEID = '59F07A4F-FE97-47D9-A197-B430E8BCBC1C'
  left join #ApexTmp DON ON EX.CLIENTSITESID = DON.SITEID and DON.FORMTYPEID = '33BE06AD-F3EF-46C9-A002-3DD13790737E'
  left join #ApexTmp CONTACT ON EX.CLIENTSITESID = CONTACT.SITEID and CONTACT.FORMTYPEID = '644EBCF1-15B1-4B24-AD45-AF1A63A9CBEE'
  left join #ApexTmp PROF ON EX.CLIENTSITESID = PROF.SITEID and PROF.FORMTYPEID = '714FB1BE-4A9D-4CA1-B819-8B5B562037A1'
  left join #ApexTmp PREF ON EX.CLIENTSITESID = PREF.SITEID and PREF.FORMTYPEID = '89D3C4CC-4D01-4E8B-B82E-04AD966EEE09'
  where EX.EVENTID = @ID

  if @ISLOADED = 0 --and @ISIMPORTSUCCESS = 0
  begin    
    declare @CHANGEAGENTID uniqueidentifier,
            @CURRENTDATE datetime

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

    merge into dbo.EVENTIMPORT as Target
    using
    (
      select E.ID, ISNULL(IMPORTATTEMPT, 1) as IMPORTATTEMPT from dbo.EVENT E
      left join dbo.EVENTIMPORT EI on E.ID = EI.ID
      where E.ID = @ID
    ) as source on Target.ID = source.ID
    when matched then
      update set [IMPORTATTEMPT] = source.[IMPORTATTEMPT] + 1, ISIMPORTINGBYUSERID = @CURRENTAPPUSERID
    when not matched then 
      insert (ID,  ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, ISIMPORTINGBYUSERID)
      values (@ID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, @CURRENTAPPUSERID);

    select @lasterror=LASTIMPORTERROR from dbo.PAGEMODEL where CLIENTSITESID = @CLIENTSITESID
    if @lasterror <> '' and @lasterror like '%Error%'
    begin
      if (select [IMPORTATTEMPT] from dbo.EVENTIMPORT where ID = @ID) > 2        
      begin
          set @ISIMPORTSUCCESS = 0      
        update dbo.EVENTIMPORT set [IMPORTATTEMPT] = 1 where ID = @ID
      end
      else
        set @ISIMPORTSUCCESS = 1
    end

  end   

  if @ISLOADED = 1 and @importuser is not null
    update dbo.EVENTIMPORT set ISIMPORTINGBYUSERID = null where ID = @ID

  select @ClientUserId=cu.ID, @AID=cu.ID from dbo.BBNCUSERMAP map
    join dbo.ClientUsers cu on cu.UserName=map.BBNCUSERNAME
    where map.ID=@CURRENTAPPUSERID
  if @ClientUserId is null
      set @ISMAPBBNCUSER = 0
  else
      begin
          select @FAFEventAdminRoleId=cr.ID from dbo.ClientRoles cr where cr.Guid='4625EB3C-1673-4D8C-95D8-BD0B1AD201A6';

      if @FAFEventAdminRoleId is null
              set @ISMAPBBNCUSER = 1
          else
              begin
                  select @IsFAFEventAdmin = case when count(0)=0 then 0 else 1 end
                  from UserRoles ur
                  where ur.ClientUsersID=@ClientUserId and ur.ClientRolesID=@FAFEventAdminRoleId

                  if @IsFAFEventAdmin = 0
                      insert into UserRoles (ClientUsersID, ClientRolesID, ManuallyAdded) values (@ClientUserId, @FAFEventAdminRoleId, 1)

                  set @ISMAPBBNCUSER = 1
              end
      end

    drop table #ApexTmp 

    return 0;