TRS.WCM.Developer/贵州项目6个WCM节点迁移到海云/如增量迁移需运行SQL/3_sp_data_migration_wcmchnldoc.sql

DROP PROCEDURE IF EXISTS sp_data_migration_increment_wcmchnldoc;
DELIMITER $$
CREATE PROCEDURE sp_data_migration_increment_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 isover INT DEFAULT 0;

    DECLARE migr_table_name varchar(255) DEFAULT 'wcmchnldoc';
    DECLARE doc_migr_table_name varchar(255) DEFAULT 'wcmdocument';
    DECLARE view_migr_table_name varchar(255) DEFAULT 'xwcmviewinfo';
    DECLARE site_migr_table_name varchar(255) DEFAULT 'wcmwebsite';
    DECLARE channel_migr_table_name varchar(255) DEFAULT 'wcmchannel';

    DECLARE  f_CHNLID int(11)  DEFAULT '0';
    DECLARE  f_DOCID int(11)  DEFAULT '0';
    DECLARE  f_DOCORDER int(11)  DEFAULT '0';
    DECLARE  f_DOCSTATUS int(11)  DEFAULT '0';
    DECLARE  f_CRUSER varchar(100)  DEFAULT 'admin';
    DECLARE  f_CRTIME datetime DEFAULT NULL;
    DECLARE  f_DOCPUBTIME datetime DEFAULT NULL;
    DECLARE  f_DOCPUBURL varchar(300) DEFAULT NULL;
    DECLARE  f_RECID int(11)  DEFAULT '0';
    DECLARE  f_DOCORDERPRI int(11)  DEFAULT '0';
    DECLARE  f_INVALIDTIME datetime DEFAULT NULL;
    DECLARE  f_OPERUSER varchar(50) DEFAULT NULL;
    DECLARE  f_OPERTIME datetime DEFAULT NULL;
    DECLARE  f_MODAL int(11) DEFAULT '1';
    DECLARE  f_DOCRELTIME datetime DEFAULT NULL;
    DECLARE  f_DOCCHANNEL int(11) DEFAULT NULL;
    DECLARE  f_DOCFLAG int(11) DEFAULT NULL;
    DECLARE  f_DOCKIND int(11) DEFAULT '0';
    DECLARE  f_SITEID int(11)  DEFAULT '0';
    DECLARE  f_SRCSITEID int(11)  DEFAULT '0';
    DECLARE  f_DOCFIRSTPUBTIME datetime DEFAULT NULL;
    DECLARE  f_NODEID int(11) DEFAULT '0';
    DECLARE  f_CRDEPT varchar(200) DEFAULT NULL;
    DECLARE  f_DOCOUTUPID int(11) DEFAULT '0';
    DECLARE  f_DOCFORM int(11) DEFAULT '0';
    DECLARE  f_DOCLEVEL int(11) DEFAULT NULL;
    DECLARE  f_attachpic smallint(6) DEFAULT NULL;
    DECLARE  f_POSCHNLID int(11) DEFAULT '0';
    DECLARE  f_DocType smallint(6) DEFAULT '1';
    DECLARE  f_ISTIMINGPUBLISH tinyint(2) DEFAULT '0';
    DECLARE  f_ACTIONTYPE tinyint(4) DEFAULT '0';
    DECLARE  f_PUBSTATUS tinyint(4) DEFAULT '0' ;
    DECLARE  f_GDORDER tinyint(4) DEFAULT '0';
    DECLARE  f_DOCAUTHOR varchar(200) DEFAULT NULL ;
    DECLARE  f_ORIGINRECID int(11) DEFAULT '0';
    DECLARE  f_DOCSOURCENAME varchar(200) DEFAULT NULL ;
    DECLARE  f_TIMEDSTATUS tinyint(4) DEFAULT '0' ;
    DECLARE  f_CANCELPUBTIME datetime DEFAULT NULL ;
    -- 迭代十五才有的字段 DECLARE  f_ACTIONUSER varchar(100) DEFAULT NULL ;





    DECLARE cur CURSOR FOR
		SELECT
      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,
      20,
      0,
      0,
      0,
      0,
      null,
      0,
      null,
      0,
      null
    -- 迭代十五才有的字段 ACTIONUSER
    FROM wcmchnldoc
    where CHNLID > 0 and SITEID > 0 AND RECID > prevRecId
    ORDER BY RECID ASC ;

	DECLARE CONTINUE HANDLER FOR NOT FOUND SET isover = 1;
	
	OPEN cur;
	
	FETCH cur INTO
    f_CHNLID,
    f_DOCID,
    f_DOCORDER,
    f_DOCSTATUS,
    f_CRUSER,
    f_CRTIME,
    f_DOCPUBTIME,
    f_DOCPUBURL,
    f_RECID,
    f_DOCORDERPRI,
    f_INVALIDTIME,
    f_OPERUSER,
    f_OPERTIME,
    f_MODAL,
    f_DOCRELTIME,
    f_DOCCHANNEL,
    f_DOCFLAG,
    f_DOCKIND,
    f_SITEID,
    f_SRCSITEID,
    f_DOCFIRSTPUBTIME,
    f_NODEID,
    f_CRDEPT,
    f_DOCOUTUPID,
    f_DOCFORM,
    f_DOCLEVEL,
    f_attachpic,
    f_POSCHNLID,
    f_DocType,
    f_ISTIMINGPUBLISH,
    f_ACTIONTYPE,
    f_PUBSTATUS,
    f_GDORDER,
    f_DOCAUTHOR,
    f_ORIGINRECID,
    f_DOCSOURCENAME,
    f_TIMEDSTATUS,
    f_CANCELPUBTIME
    -- 迭代十五才有的字段 f_ACTIONUSER
    ;


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

    WHILE isover= 0 DO

      # 断点续传
      IF (f_RECID > prevRecId)

        THEN
          SET @RECID = f_RECID;
          SET f_RECID = f_RECID + recIdOffset;
          set f_ORIGINRECID = f_RECID;
          SET @doc_offset_num = ifnull((SELECT offset_num FROM data_migration_offset WHERE table_name = doc_migr_table_name AND start_id < f_DOCID and  end_id >= f_DOCID), docIdOffset);
          SET f_DOCID = f_DOCID + @doc_offset_num;
          SET @channel_offset_num = ifnull((SELECT offset_num FROM data_migration_offset WHERE table_name = channel_migr_table_name AND start_id < f_CHNLID and  end_id >= f_CHNLID), channelIdOffset);
          SET f_CHNLID = f_CHNLID + @channel_offset_num;
          SET @site_offset_num = ifnull((SELECT offset_num FROM data_migration_offset WHERE table_name = site_migr_table_name AND start_id < f_SITEID and  end_id >= f_SITEID), siteIdOffset);
          SET f_SITEID = f_SITEID + @site_offset_num;
          SET @view_offset_num = ifnull((SELECT offset_num FROM data_migration_offset WHERE table_name = view_migr_table_name AND start_id < f_DOCKIND and  end_id >= f_DOCKIND), docKindOffset);
          SET f_DOCKIND = f_DOCKIND + @view_offset_num;
          SET @docchannel_offset_num = ifnull((SELECT offset_num FROM data_migration_offset WHERE table_name = channel_migr_table_name AND start_id < f_DOCCHANNEL and  end_id >= f_DOCCHANNEL), channelIdOffset);
          SET f_DOCCHANNEL = f_DOCCHANNEL + @docchannel_offset_num;
          IF (f_DocType = 1)
            THEN
              SET f_DocType = 20;
           END IF;


        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
            -- 迭代十五才有的字段 ACTIONUSER
          )
          VALUES
            (
              f_CHNLID,
              f_DOCID,
              f_DOCORDER,
              f_DOCSTATUS,
              f_CRUSER,
              f_CRTIME,
              f_DOCPUBTIME,
              f_DOCPUBURL,
              f_RECID,
              f_DOCORDERPRI,
              f_INVALIDTIME,
              f_OPERUSER,
              f_OPERTIME,
              f_MODAL,
              f_DOCRELTIME,
              f_DOCCHANNEL,
              f_DOCFLAG,
              f_DOCKIND,
              f_SITEID,
              f_SRCSITEID,
              f_DOCFIRSTPUBTIME,
              f_NODEID,
              f_CRDEPT,
              f_DOCOUTUPID,
              f_DOCFORM,
              f_DOCLEVEL,
              f_attachpic,
              f_POSCHNLID,
              f_DocType,
              f_ISTIMINGPUBLISH,
              f_ACTIONTYPE,
              f_PUBSTATUS,
              f_GDORDER,
              f_DOCAUTHOR,
              f_ORIGINRECID,
              f_DOCSOURCENAME,
              f_TIMEDSTATUS,
              f_CANCELPUBTIME
              -- 迭代十五才有的字段 f_ACTIONUSER
            );

          SET @log_sql = build_migration_log_sql(migr_table_name, @RECID, now());

          prepare stmt from @log_sql;
          EXECUTE stmt;
          deallocate prepare stmt;
          call setOffset(migr_table_name, recIdOffset, prevRecId, @RECID);
    END IF ;

		FETCH cur INTO
      f_CHNLID,
      f_DOCID,
      f_DOCORDER,
      f_DOCSTATUS,
      f_CRUSER,
      f_CRTIME,
      f_DOCPUBTIME,
      f_DOCPUBURL,
      f_RECID,
      f_DOCORDERPRI,
      f_INVALIDTIME,
      f_OPERUSER,
      f_OPERTIME,
      f_MODAL,
      f_DOCRELTIME,
      f_DOCCHANNEL,
      f_DOCFLAG,
      f_DOCKIND,
      f_SITEID,
      f_SRCSITEID,
      f_DOCFIRSTPUBTIME,
      f_NODEID,
      f_CRDEPT,
      f_DOCOUTUPID,
      f_DOCFORM,
      f_DOCLEVEL,
      f_attachpic,
      f_POSCHNLID,
      f_DocType,
      f_ISTIMINGPUBLISH,
      f_ACTIONTYPE,
      f_PUBSTATUS,
      f_GDORDER,
      f_DOCAUTHOR,
      f_ORIGINRECID,
      f_DOCSOURCENAME,
      f_TIMEDSTATUS,
      f_CANCELPUBTIME
      -- 迭代十五才有的字段 f_ACTIONUSER
    ;
		
	END WHILE;
	
	CLOSE cur;

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

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


  END $$
DELIMITER ;