TRS.WCM.Developer/贵州项目6个WCM节点迁移到海云/3_sp_data_migration_wcmchnldoc.sql

DROP PROCEDURE
IF
	EXISTS sp_data_migration_wcmchnldoc;

DELIMITER $$
CREATE PROCEDURE sp_data_migration_wcmchnldoc (
	IN prevRecId BIGINT ( 20 ),
	IN recIdOffset BIGINT ( 20 ),
	IN docIdOffset BIGINT ( 20 ),
	IN siteIdOffset BIGINT ( 20 ),
	IN channelIdOffset BIGINT ( 20 ),
	IN docKindOffset BIGINT ( 20 )
	) BEGIN

	DECLARE
		migr_table_name VARCHAR ( 255 ) DEFAULT 'wcmchnldoc';
  SELECT
    concat('开始迁移 ', migr_table_name) info;

	INSERT INTO trs_hycloud_iip.wcmchnldoc (
		CHNLID,
		DOCID,
		DOCORDER,
		DOCSTATUS,
		CRUSER,
		CRTIME,
		DOCPUBTIME,
		DOCPUBURL,
		RECID,
		DOCORDERPRI,
		INVALIDTIME,
		OPERUSER,
		OPERTIME,
		MODAL,
		DOCRELTIME,
		DOCCHANNEL,
		DOCFLAG,
		DOCKIND,
		SITEID,
		SRCSITEID,
		DOCFIRSTPUBTIME,
		NODEID,
		CRDEPT,
		DOCOUTUPID,
		DOCFORM,
		DOCLEVEL,
		attachpic,
		POSCHNLID,
		DocType,
		ISTIMINGPUBLISH,
		ACTIONTYPE,
		PUBSTATUS,
		GDORDER,
		DOCAUTHOR,
		ORIGINRECID,
		DOCSOURCENAME,
		TIMEDSTATUS,
		CANCELPUBTIME

	) SELECT
	CHNLID + channelIdOffset,
	DOCID + docIdOffset,
	DOCORDER,
	DOCSTATUS,
	CRUSER,
	CRTIME,
	DOCPUBTIME,
	DOCPUBURL,
	RECID + recIdOffset,
	DOCORDERPRI,
	INVALIDTIME,
	OPERUSER,
	OPERTIME,
	MODAL,
	DOCRELTIME,
	DOCCHANNEL + channelIdOffset,
	DOCFLAG,
	DOCKIND + docKindOffset,
	SITEID + siteIdOffset,
	SRCSITEID,
	DOCFIRSTPUBTIME,
	NODEID,
	CRDEPT,
	DOCOUTUPID,
	DOCFORM,
	DOCLEVEL,
	attachpic,
	POSCHNLID,
	20,
	0,
	0,
	0,
	0,
	NULL,
	0,
	NULL,
	0,
	NULL
	FROM
		wcmchnldoc
	WHERE
		CHNLID > 0
		AND SITEID > 0
		AND RECID > prevRecId
	ORDER BY
		RECID ASC;

	update trs_hycloud_iip.wcmchnldoc set PUBSTATUS = 1 where RECID > prevRecId and DOCSTATUS = 10;

	SET @maxRECID = IFNULL((SELECT MAX(recId) from wcmchnldoc), 0 );

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

	CALL setOffset ( migr_table_name, recIdOffset, prevRecId, @maxRECID );

	SELECT
		concat( '完成迁移 ', migr_table_name ) info;

END $$
DELIMITER;