USP_CLONESITE_USERLOGIN

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

    -- copy the new user acknowledgement and forgotten password email templates for this part

    declare @IDs xml
    set @IDs = 
    (
        select ID from
        (
            select ET.ID
            from EmailTemplate ET
            inner join SignInProperties SIP on SIP.NewUserTemplateID = ET.ID
            inner join dbo.SiteContent SC on SC.CopySourceID = SIP.ContentID 
            where ET.ClientSitesID = @SourceClientSitesID and ET.Deleted = 0 and SC.CopyBatchGUID = @CopyBatchGUID
            union all
            select ET.ID
            from EmailTemplate ET
            inner join SignInProperties SIP on SIP.ForgottenPWDTemplateID = ET.ID
            inner join dbo.SiteContent SC on SC.CopySourceID = SIP.ContentID
            where ET.ClientSitesID = @SourceClientSitesID and ET.Deleted = 0 and SC.CopyBatchGUID = @CopyBatchGUID
        ) E
        FOR XML RAW, ELEMENTS
    )        
    exec spCloneSite_EmailTemplate @TargetClientSitesID,@CurrentUserID,@CopyBatchGUID,@IDs

    INSERT INTO SignInProperties
    (HomePageID, NewUserStartPageID, NewUserTemplateID, ForgottenPWDTemplateID, ContentID)
    SELECT
    hp.ID,
    nu.ID,
    nuet.ID,
    fpet.ID,
    SiteContent.ID
    FROM SignInProperties
    INNER JOIN SiteContent ON SiteContent.CopySourceID = SignInProperties.ContentID AND SiteContent.CopyBatchGuid = @CopyBatchGuid
    LEFT JOIN SitePages hp ON hp.CopySourceID = SignInProperties.HomePageID AND hp.CopyBatchGuid = @CopyBatchGuid
    LEFT JOIN SitePages nu ON nu.CopySourceID = SignInProperties.NewUserStartPageID AND nu.CopyBatchGuid = @CopyBatchGuid
    LEFT JOIN EmailTemplate nuet ON nuet.CopySourceID = SignInProperties.NewUserTemplateID AND nuet.CopyBatchGuid = @CopyBatchGuid
    LEFT JOIN EmailTemplate fpet ON fpet.CopySourceID = SignInProperties.ForgottenPWDTemplateID AND fpet.CopyBatchGuid = @CopyBatchGuid


    INSERT INTO SignOnControls
    (Required, ContentID, DisplayOrder, AttributeTypeID, FieldID, ParentFieldID, Display, Text)
    SELECT
    [Required],
    SiteContent.ID,
    DisplayOrder,
    AttributeTypeID,
    FieldID,
    ParentFieldID,
    Display,
    [Text]
    FROM SignOnControls
    INNER JOIN SiteContent ON SiteContent.CopySourceID = SignOnControls.ContentID AND SiteContent.CopyBatchGuid = @CopyBatchGuid

    --Update XML Data

    declare @Data xml
    set @Data = 
    (
        select P.SiteContentID, cast(SP.ID as nvarchar) + ';' + SUBSTRING(P.Value, CHARINDEX(';',P.Value)+1, LEN(P.Value)) as Value
        from (select P.SiteContentID, case when (CHARINDEX(';',P.Value) > 0) then P.Value else P.Value + ';0' end as Value from dbo.UFN_GETXMLPROPERTYFORSITECOPY(@CopyBatchGuid,33,'HomePage') P) P
        inner join dbo.SitePages SP on SP.CopySourceID = SUBSTRING(P.Value, 0, CHARINDEX(';',P.Value))
        where SP.CopyBatchGUID = @CopyBatchGUID
        FOR XML RAW, ELEMENTS
    )    
    exec USP_UPDATEXMLPROPERTYFORSITECOPY 'HomePage', @Data    

    set @Data = 
    (
        select P.SiteContentID, cast(SP.ID as nvarchar) + ';' + SUBSTRING(P.Value, CHARINDEX(';',P.Value)+1, LEN(P.Value)) as Value
        from (select P.SiteContentID, case when (CHARINDEX(';',P.Value) > 0) then P.Value else P.Value + ';0' end as Value from dbo.UFN_GETXMLPROPERTYFORSITECOPY(@CopyBatchGuid,33,'NewUserStartPage') P) P
        inner join dbo.SitePages SP on SP.CopySourceID = SUBSTRING(P.Value, 0, CHARINDEX(';',P.Value))
        where SP.CopyBatchGUID = @CopyBatchGUID
        FOR XML RAW, ELEMENTS
    )    
    exec USP_UPDATEXMLPROPERTYFORSITECOPY 'NewUserStartPage', @Data    

    set @Data = 
    (
        select P.SiteContentID, cast(SP.ID as nvarchar) + ';' + SUBSTRING(P.Value, CHARINDEX(';',P.Value)+1, LEN(P.Value)) as Value
        from (select P.SiteContentID, case when (CHARINDEX(';',P.Value) > 0) then P.Value else P.Value + ';0' end as Value from dbo.UFN_GETXMLPROPERTYFORSITECOPY(@CopyBatchGuid,33,'LogoutPage') P) P
        inner join dbo.SitePages SP on SP.CopySourceID = SUBSTRING(P.Value, 0, CHARINDEX(';',P.Value))
        where SP.CopyBatchGUID = @CopyBatchGUID
        FOR XML RAW, ELEMENTS
    )    
    exec USP_UPDATEXMLPROPERTYFORSITECOPY 'LogoutPage', @Data