TRS.WCM.Developer/贵州项目/全量迁移/3_sp_data_migration_wcmdocument.sql

DROP PROCEDURE
IF
	EXISTS sp_data_migration_wcmdocument;

DELIMITER $$
CREATE PROCEDURE sp_data_migration_wcmdocument (
	IN prevDocId BIGINT ( 20 ),
	IN docIdOffset BIGINT ( 20 ),
	IN viewIdOffset BIGINT ( 20 ),
	IN siteIdOffset BIGINT ( 20 ),
	IN channelIdOffset BIGINT ( 20 )
	) BEGIN

	DECLARE
		migr_table_name VARCHAR ( 255 ) DEFAULT 'wcmdocument';

	SELECT
    concat('开始迁移 ', migr_table_name) info;

	INSERT INTO trs_hycloud_iip.wcmdocument (
		DOCID,
		DOCCHANNEL,
		DOCVERSION,
		DOCTYPE,
		DOCTITLE,
		ShortTitle,
		DOCSOURCE,
		DOCSECURITY,
		DOCSTATUS,
		DOCKIND,
		DOCCONTENT,
		DOCHTMLCON,
		DOCABSTRACT,
		DOCKEYWORDS,
		DOCRELWORDS,
		DOCPEOPLE,
		DOCPLACE,
		DOCAUTHOR,
		DOCEDITOR,
		DOCAUDITOR,
		DOCOUTUPID,
		DOCVALID,
		DOCPUBURL,
		DOCPUBTIME,
		DOCRELTIME,
		CRUSER,
		CRTIME,
		DOCWORDSCOUNT,
		DOCPRO,
		RIGHTDEFINED,
		TITLECOLOR,
		TEMPLATEID,
		SCHEDULE,
		DOCNO,
		DOCFLAG,
		EDITOR,
		ATTRIBUTE,
		HITSCOUNT,
		DOCPUBHTMLCON,
		SUBDOCTITLE,
		ATTACHPIC,
		DOCLINK,
		DOCFILENAME,
		DOCFROMVERSION,
		OPERTIME,
		OPERUSER,
		FLOWOPERATIONMARK,
		FLOWPREOPERATIONMARK,
		FLOWOPERATIONMASKENUM,
		DOCSOURCENAME,
		DOCLINKTO,
		DOCMIRRORTO,
		RANDOMSERIAL,
		POSTUSER,
		ISPAGEIMG,
		PUBLISHDATE,
		PAGENUM,
		PAGENAME,
		PDFFILENAME,
		PAGEIMAGEFILENAME,
		MAP,
		YINTI,
		SITEID,
		SRCSITEID,
		DOCFIRSTPUBTIME,
		NODEID,
		ORDERID,
		CRDEPT,
		DOCFORM,
		DOCLEVEL,
		olddocpuburl,
		AttachVideo,
		AttachAudio,
		ThumbFiles,
		commentFlag,
		readingMoodFlag,
		FromType,
		FromId,
		OriginDocId
	) SELECT
	DOCID + docIdOffset,
	DOCCHANNEL + channelIdOffset,
	DOCVERSION,
	DOCTYPE,
	DOCTITLE,
	NULL,
	DOCSOURCE,
	DOCSECURITY,
	DOCSTATUS,
	DOCKIND + viewIdOffset,
	DOCCONTENT,
	DOCHTMLCON,
	DOCABSTRACT,
	DOCKEYWORDS,
	DOCRELWORDS,
	DOCPEOPLE,
	DOCPLACE,
	DOCAUTHOR,
	DOCEDITOR,
	DOCAUDITOR,
	DOCOUTUPID,
	DOCVALID,
	DOCPUBURL,
	DOCPUBTIME,
	DOCRELTIME,
	CRUSER,
	CRTIME,
	DOCWORDSCOUNT,
	DOCPRO,
	RIGHTDEFINED,
	TITLECOLOR,
	TEMPLATEID,
	SCHEDULE,
	DOCNO,
	DOCFLAG,
	EDITOR,
	ATTRIBUTE,
	HITSCOUNT,
	DOCPUBHTMLCON,
	SUBDOCTITLE,
	ATTACHPIC,
	DOCLINK,
	DOCFILENAME,
	DOCFROMVERSION,
	OPERTIME,
	OPERUSER,
	FLOWOPERATIONMARK,
	FLOWPREOPERATIONMARK,
	FLOWOPERATIONMASKENUM,
	DOCSOURCENAME,
	DOCLINKTO,
	DOCMIRRORTO,
	RANDOMSERIAL,
	POSTUSER,
	ISPAGEIMG,
	PUBLISHDATE,
	PAGENUM,
	PAGENAME,
	PDFFILENAME,
	PAGEIMAGEFILENAME,
	MAP,
	YINTI,
	SITEID + siteIdOffset,
	SRCSITEID,
	DOCFIRSTPUBTIME,
	NODEID,
	ORDERID,
	CRDEPT,
	DOCFORM,
	DOCLEVEL,
	olddocpuburl,
	0,
	0,
	NULL,
	NULL,
	NULL,
	0,
	0,
	DOCID + docIdOffset
	FROM
		wcmdocument
	where DOCID > prevDocId
	ORDER BY
		docid ASC;

	SET @maxDOCID = IFNULL( ( SELECT MAX( DOCID ) FROM wcmdocument ), 0 );

	SET @log_sql = build_migration_log_sql ( migr_table_name, @maxDOCID, now( ) );
	PREPARE stmt FROM @log_sql;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;

	CALL setOffset ( migr_table_name, docIdOffset, prevDocId, @maxDOCID );
	SELECT
		concat( '完成迁移 ', migr_table_name ) info;

END $$
DELIMITER;