USP_CLONESITE_FORM

CREATE procedure dbo.USP_CLONESITE_FORM
(
@SourceClientSitesID int,
@TargetClientSitesID int,
@CurrentUserID int,
@CopyBatchGuid uniqueidentifier
)
as
begin

  -- duplicate rows in APEXFORM related to @sourceClientSiteID 

  declare @CHANGEAGENTID uniqueidentifier 
  exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

  insert into dbo.APEXFORM
  (ID, NAME, LAYOUT, ISDEFAULT, FORMTYPEID, OWNERID, SITEID,COPYSOURCEID, COPYBATCHGUID, ADDEDBYID, CHANGEDBYID)
  select NEWID()
  , NAME
  , LAYOUT
  , ISDEFAULT
  , FORMTYPEID
  , OWNERID
  , @TargetClientSitesID
  , ID
  , @CopyBatchGuid
  , @CHANGEAGENTID
  , @CHANGEAGENTID
  from dbo.APEXFORM AF
  WHERE AF.SITEID = @SourceClientSitesID;


  --need to deal with attributes inside APEXFORM.LAYOUT xml

  --PageLinkID for login element

/*  declare @ElementUIDescriptor as table(ID uniqueidentifier, displayConfig varchar(max), pageID int NULL, newPageID int NULL)

  insert into @ElementUIDescriptor
  select AF.ID
  ,cast(layout.query('
    for $ElementUIDescriptor in /FormLayout/FormStepList/FormStep/SectionList/FormSection/ElementUIDescriptorList/ElementUIDescriptor
    where $ElementUIDescriptor/@DisplayControlPath="ApexUserLogin.ascx"
    return $ElementUIDescriptor/DisplayConfig/text()'
    ) as nvarchar(max))
  ,NULL
  ,NULL
  from dbo.APEXFORM AF
  where AF.SITEID = @TargetClientSitesID
  and AF.COPYBATCHGUID = @CopyBatchGuid
  and convert(varchar(max), layout) like '%ApexUserLogin.ascx%';

  --then retrieve the value of PageLinkId and switch to new site
  update @ElementUIDescriptor
  set pageID = cast(dbo.UFN_UTIL_GETVALUEBYNAMEINJSON(displayConfig, 'PageLinkId') as int);

  update E
  set newPageID = SP.ID
  from @ElementUIDescriptor E
  inner join dbo.SitePages SP
    on E.pageID = SP.CopySourceID
  where SP.ClientSitesID = @TargetClientSitesID
    and SP.CopyBatchGUID = @CopyBatchGuid;

  --ready to swap it back: instead of looping xml.modify(), just use good old replace()
  update AF
  set LAYOUT = cast(
    replace(
    cast(layout as nvarchar(max))
    ,E.displayConfig 
    ,replace(E.displayConfig, '"PageLinkId":"'+CAST(E.pageID as varchar(50)), '"PageLinkId":"'+CAST(E.newPageID as varchar(50)) )
    )
    as xml) 
  from dbo.APEXFORM AF
  inner join @ElementUIDescriptor E
    on AF.ID = E.ID
    and AF.COPYBATCHGUID = @CopyBatchGuid;
*/
  -- copy part config data in APEXFORMCONTENT

  declare @CURRENTDATE datetime
  set @CURRENTDATE = getdate()
  insert into dbo.APEXFORMCONTENT
      (ID, FORMID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
  select SC2.Guid, F.ID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE  from APEXFORMCONTENT FC 
      inner join SiteContent SC 
          on FC.ID = SC.Guid and SC.ClientSitesID = @SourceClientSitesID
      inner join SiteContent SC2 
          on SC2.CopyBatchGUID = @CopyBatchGuid
          and SC.ID = SC2.CopySourceID  and SC2.ClientSitesID = @TargetClientSitesID
      inner join APEXFORM F
          on F.COPYSOURCEID = FC.FORMID and F.COPYBATCHGUID = @CopyBatchGuid and F.SITEID = @TargetClientSitesID

end