--===================================================================
--Author:wuyang
--CreateDate:2017-1-13
--Desc:CMS转移
--eg:把人南路社区（011002002002048004）的信息数据迁移至青石桥社区（011002002002048002），然后删除人南路社区。
--===================================================================

CREATE PROCEDURE [dbo].[CMSGroupChange]
(
@oldid      NVARCHAR(100),--转移id
@newid  NVARCHAR(100),--新id
@result         INT OUT       --1：成功 0：失败   
)
AS
BEGIN
    SET XACT_ABORT ON
    BEGIN TRANSACTION
        DECLARE @oldgroupid NVARCHAR(100);
        DECLARE @newgroupid NVARCHAR(100);
        DECLARE @oldusername NVARCHAR(100);
        DECLARE @newusername NVARCHAR(100);
        IF EXISTS(SELECT * FROM gpsp_sitemapping WHERE old_id=@oldid) and  EXISTS(SELECT * FROM gpsp_sitemapping WHERE old_id=@newid)
        BEGIN
            --1.根据id查询站点名称和站点id
            SELECT @oldgroupid=new_id,@oldusername=old_name FROM gpsp_sitemapping WHERE old_id=@oldid;
            SELECT @newgroupid=new_id,@newusername=old_name FROM gpsp_sitemapping WHERE old_id=@newid;
            print @oldusername+@oldgroupid+'    中的数据即将转移到     '+@newusername+@newgroupid+'中';
            --2.开始转移 update CMSPublishedArticle（更新站点id和plid）
            update CMSPublishedArticle set groupid = @newgroupid,plid = 
            (select l.plid ) from CMSPublishedArticle c,JournalArticle j ,Layout l
            where
            c.groupId = @oldgroupid and 
            c.resourcePrimKey = j.resourcePrimKey and 
            l.uuid_ = j.layoutUuid and
            l.groupId = @newgroupid;
            --3.update JournalArticle(更新站点id)
            update JournalArticle set groupId = @newgroupid where groupId = @oldgroupid;
            --4.update AssetEntry(更新站点di)
            update AssetEntry set groupId = @newgroupid where groupId = @oldgroupid and classNameId = '10109';
            --5.delete gpsp_site(删除旧站点)
            delete from gpsp_site where siteid = @oldgroupid;
            --6.delete gpsp_sitemapping(删除旧站点映射关系)
            delete from gpsp_sitemapping where new_id = @oldgroupid
            --7.跟新CMS显示来源名称
            update CMSPublishedArticle set userName = @newusername where groupId = @newgroupid
        END
        ELSE
        BEGIN
            SET @result=0;
            print '错误，不存在站点';
        END

    IF @@ERROR<>0
    BEGIN
        SET @result=0;
        ROLLBACK TRANSACTION
        RETURN ;
    END
    ELSE
    BEGIN
        SET @result=1;
        COMMIT TRANSACTION
    END


END


GO