TRS.WCM.Developer/TRS.WCM.Developer/贵州项目/3_sp_data_migration_wcmchnldoc.sql

use trs_data_migration;

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))
	BEGIN
	
    DECLARE isover INT DEFAULT 0;

    DECLARE migr_table_name varchar(255) DEFAULT 'wcmchnldoc';

    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_CRDEPTID int(11) DEFAULT '0' ;
    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,
      DocType,
      ISTIMINGPUBLISH,
      ACTIONTYPE,
      PUBSTATUS,
      GDORDER,
      DOCAUTHOR,
      ORIGINRECID,
      DOCSOURCENAME,
      CRDEPTID,
      TIMEDSTATUS,
      CANCELPUBTIME,
      ACTIONUSER
    FROM trs_data_migration.wcmchnldoc
    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_CRDEPTID,
    f_TIMEDSTATUS,
    f_CANCELPUBTIME,
    f_ACTIONUSER
    ;


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

    SET @log_sql = trs_data_migration.build_migration_log_sql(migr_table_name, 0);
    prepare stmt from @log_sql;
    EXECUTE stmt;
    deallocate prepare stmt;

    WHILE isover= 0 DO

      # 断点续传
      IF (f_RECID > prevRecId)

        THEN
          SET @RECID = f_RECID;
          SET f_RECID = f_RECID + recIdOffset;
          SET f_DOCID = f_DOCID + docIdOffset;
          SET f_CHNLID = f_CHNLID + channelIdOffset;
          SET f_SITEID = f_SITEID + siteIdOffset;



        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,
            CRDEPTID,
            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_CRDEPTID,
              f_TIMEDSTATUS,
              f_CANCELPUBTIME,
              f_ACTIONUSER
            );

          SET @log_sql = trs_data_migration.build_migration_log_sql(migr_table_name, @RECID);

          prepare stmt from @log_sql;
          EXECUTE stmt;
          deallocate prepare stmt;
    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_CRDEPTID,
      f_TIMEDSTATUS,
      f_CANCELPUBTIME,
      f_ACTIONUSER
    ;
		
	END WHILE;
	
	CLOSE cur;

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


  END $$
DELIMITER ;