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

use trs_data_migration;

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 isover INT DEFAULT 0;	
	
  DECLARE f_DOCID int(11) DEFAULT 0;
  DECLARE f_DOCCHANNEL int(11) DEFAULT 0;
  DECLARE f_DOCVERSION int(11) DEFAULT 0;
  DECLARE f_DOCTYPE int(11) DEFAULT 0;
  DECLARE f_DOCTITLE varchar(1000) DEFAULT 0;
  DECLARE f_ShortTitle varchar(400) DEFAULT NULL;
  DECLARE f_DOCSOURCE int(11) DEFAULT 0;
  DECLARE f_DOCSECURITY int(11) DEFAULT 0;
  DECLARE f_DOCSTATUS int(11) DEFAULT 0;
  DECLARE f_DOCKIND int(11) DEFAULT NULL;
  DECLARE f_DOCCONTENT longtext;
  DECLARE f_DOCHTMLCON longtext;
  DECLARE f_DOCABSTRACT varchar(2000) DEFAULT NULL;
  DECLARE f_DOCKEYWORDS varchar(200) DEFAULT NULL;
  DECLARE f_DOCRELWORDS varchar(200) DEFAULT NULL;
  DECLARE f_DOCPEOPLE varchar(200) DEFAULT NULL;
  DECLARE f_DOCPLACE varchar(200) DEFAULT NULL;
  DECLARE f_DOCAUTHOR varchar(200) DEFAULT NULL;
  DECLARE f_DOCEDITOR varchar(50) DEFAULT NULL;
  DECLARE f_DOCAUDITOR varchar(50) DEFAULT NULL;
  DECLARE f_DOCOUTUPID int(11) DEFAULT NULL;
  DECLARE f_DOCVALID datetime DEFAULT NULL;
  DECLARE f_DOCPUBURL varchar(400) DEFAULT NULL;
  DECLARE f_DOCPUBTIME datetime DEFAULT NULL;
  DECLARE f_DOCRELTIME datetime DEFAULT NULL;
  DECLARE f_CRUSER varchar(50) DEFAULT NULL;
  DECLARE f_CRTIME datetime DEFAULT NULL;
  DECLARE f_DOCWORDSCOUNT int(11) DEFAULT 0;
  DECLARE f_DOCPRO int(11) DEFAULT 1;
  DECLARE f_RIGHTDEFINED smallint(6) DEFAULT 0;
  DECLARE f_TITLECOLOR varchar(20) DEFAULT NULL;
  DECLARE f_TEMPLATEID int(11) DEFAULT 0;
  DECLARE f_SCHEDULE int(11) DEFAULT 0;
  DECLARE f_DOCNO varchar(20) DEFAULT NULL;
  DECLARE f_DOCFLAG int(11) DEFAULT 0;
  DECLARE f_EDITOR varchar(100) DEFAULT NULL;
  DECLARE f_ATTRIBUTE varchar(500) DEFAULT NULL;
  DECLARE f_HITSCOUNT int(11) DEFAULT 0;
  DECLARE f_DOCPUBHTMLCON longtext ;
  DECLARE f_SUBDOCTITLE varchar(500) DEFAULT NULL;
  DECLARE f_ATTACHPIC smallint(6) DEFAULT NULL;
  DECLARE f_DOCLINK varchar(500) DEFAULT NULL;
  DECLARE f_DOCFILENAME varchar(50) DEFAULT NULL;
  DECLARE f_DOCFROMVERSION int(11) DEFAULT NULL;
  DECLARE f_OPERTIME datetime DEFAULT NULL;
  DECLARE f_OPERUSER varchar(50) DEFAULT NULL;
  DECLARE f_FLOWOPERATIONMARK varchar(20) DEFAULT NULL;
  DECLARE f_FLOWPREOPERATIONMARK varchar(20) DEFAULT NULL;
  DECLARE f_FLOWOPERATIONMASKENUM varchar(20) DEFAULT NULL;
  DECLARE f_DOCSOURCENAME varchar(100) DEFAULT NULL;
  DECLARE f_DOCLINKTO varchar(300) DEFAULT NULL;
  DECLARE f_DOCMIRRORTO varchar(300) DEFAULT NULL;
  DECLARE f_RANDOMSERIAL varchar(100) DEFAULT NULL;
  DECLARE f_POSTUSER varchar(50) DEFAULT NULL;
  DECLARE f_ISPAGEIMG tinyint(4) DEFAULT NULL;
  DECLARE f_PUBLISHDATE varchar(50) DEFAULT NULL;
  DECLARE f_PAGENUM varchar(20) DEFAULT NULL;
  DECLARE f_PAGENAME varchar(150) DEFAULT NULL;
  DECLARE f_PDFFILENAME varchar(150) DEFAULT NULL;
  DECLARE f_PAGEIMAGEFILENAME varchar(50) DEFAULT NULL;
  DECLARE f_MAP varchar(500) DEFAULT NULL;
  DECLARE f_YINTI varchar(200) DEFAULT NULL;
  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_ORDERID int(11) DEFAULT 0;
  DECLARE f_CRDEPT varchar(200) DEFAULT NULL;
  DECLARE f_DOCFORM int(11) DEFAULT 0;
  DECLARE f_DOCLEVEL int(11) DEFAULT NULL;
  DECLARE f_olddocpuburl varchar(400) DEFAULT NULL;
  DECLARE f_AttachVideo smallint(6) DEFAULT 0;
  DECLARE f_AttachAudio smallint(6) DEFAULT 0;
  DECLARE f_ThumbFiles varchar(2000) DEFAULT NULL;
  DECLARE f_commentFlag tinyint(4) DEFAULT 0;
  DECLARE f_readingMoodFlag tinyint(4) DEFAULT 0;
  DECLARE f_FromType int(11) DEFAULT 0 ;
  DECLARE f_FromId int(11) DEFAULT 0 ;
  DECLARE f_OriginDocId int(11) DEFAULT 0 ;


  DECLARE cur_doc CURSOR FOR
		SELECT
      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
		FROM trs_data_migration.wcmdocument
		ORDER BY docid asc;

	DECLARE CONTINUE HANDLER FOR NOT FOUND SET isover = 1;
	
	OPEN cur_doc;
	
	FETCH cur_doc INTO
    f_DOCID,
    f_DOCCHANNEL,
    f_DOCVERSION,
    f_DOCTYPE,
    f_DOCTITLE,
    f_ShortTitle,
    f_DOCSOURCE,
    f_DOCSECURITY,
    f_DOCSTATUS,
    f_DOCKIND,
    f_DOCCONTENT,
    f_DOCHTMLCON,
    f_DOCABSTRACT,
    f_DOCKEYWORDS,
    f_DOCRELWORDS,
    f_DOCPEOPLE,
    f_DOCPLACE,
    f_DOCAUTHOR,
    f_DOCEDITOR,
    f_DOCAUDITOR,
    f_DOCOUTUPID,
    f_DOCVALID,
    f_DOCPUBURL,
    f_DOCPUBTIME,
    f_DOCRELTIME,
    f_CRUSER,
    f_CRTIME,
    f_DOCWORDSCOUNT,
    f_DOCPRO,
    f_RIGHTDEFINED,
    f_TITLECOLOR,
    f_TEMPLATEID,
    f_SCHEDULE,
    f_DOCNO,
    f_DOCFLAG,
    f_EDITOR,
    f_ATTRIBUTE,
    f_HITSCOUNT,
    f_DOCPUBHTMLCON,
    f_SUBDOCTITLE,
    f_ATTACHPIC,
    f_DOCLINK,
    f_DOCFILENAME,
    f_DOCFROMVERSION,
    f_OPERTIME,
    f_OPERUSER,
    f_FLOWOPERATIONMARK,
    f_FLOWPREOPERATIONMARK,
    f_FLOWOPERATIONMASKENUM,
    f_DOCSOURCENAME,
    f_DOCLINKTO,
    f_DOCMIRRORTO,
    f_RANDOMSERIAL,
    f_POSTUSER,
    f_ISPAGEIMG,
    f_PUBLISHDATE,
    f_PAGENUM,
    f_PAGENAME,
    f_PDFFILENAME,
    f_PAGEIMAGEFILENAME,
    f_MAP,
    f_YINTI,
    f_SITEID,
    f_SRCSITEID,
    f_DOCFIRSTPUBTIME,
    f_NODEID,
    f_ORDERID,
    f_CRDEPT,
    f_DOCFORM,
    f_DOCLEVEL,
    f_olddocpuburl,
    f_AttachVideo,
    f_AttachAudio,
    f_ThumbFiles,
    f_commentFlag,
    f_readingMoodFlag,
    f_FromType,
    f_FromId,
    f_OriginDocId
    ;



    WHILE isover= 0 DO

      # 断点续传
      IF (f_DOCID > prevDocId)

        THEN
          # 迁移后的数据，ID增加某一特定的值
          # 文档ID
          SET @DOCID = f_DOCID;
          SET f_DOCID = f_DOCID + docIdOffset;
          # 栏目ID
          SET f_DOCCHANNEL = f_DOCCHANNEL + channelIdOffset;
          # 视图ID
          IF (f_DOCKIND > 0)
          THEN SET f_DOCKIND = f_SITEID + viewIdOffset;
          END IF ;
          # 站点ID
          SET f_SITEID = f_SITEID + siteIdOffset;

          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
            )
            VALUES
              (
                f_DOCID,
                f_DOCCHANNEL,
                f_DOCVERSION,
                f_DOCTYPE,
                f_DOCTITLE,
                f_ShortTitle,
                f_DOCSOURCE,
                f_DOCSECURITY,
                f_DOCSTATUS,
                f_DOCKIND,
                f_DOCCONTENT,
                f_DOCHTMLCON,
                f_DOCABSTRACT,
                f_DOCKEYWORDS,
                f_DOCRELWORDS,
                f_DOCPEOPLE,
                f_DOCPLACE,
                f_DOCAUTHOR,
                f_DOCEDITOR,
                f_DOCAUDITOR,
                f_DOCOUTUPID,
                f_DOCVALID,
                f_DOCPUBURL,
                f_DOCPUBTIME,
                f_DOCRELTIME,
                f_CRUSER,
                f_CRTIME,
                f_DOCWORDSCOUNT,
                f_DOCPRO,
                f_RIGHTDEFINED,
                f_TITLECOLOR,
                f_TEMPLATEID,
                f_SCHEDULE,
                f_DOCNO,
                f_DOCFLAG,
                f_EDITOR,
                f_ATTRIBUTE,
                f_HITSCOUNT,
                f_DOCPUBHTMLCON,
                f_SUBDOCTITLE,
                f_ATTACHPIC,
                f_DOCLINK,
                f_DOCFILENAME,
                f_DOCFROMVERSION,
                f_OPERTIME,
                f_OPERUSER,
                f_FLOWOPERATIONMARK,
                f_FLOWPREOPERATIONMARK,
                f_FLOWOPERATIONMASKENUM,
                f_DOCSOURCENAME,
                f_DOCLINKTO,
                f_DOCMIRRORTO,
                f_RANDOMSERIAL,
                f_POSTUSER,
                f_ISPAGEIMG,
                f_PUBLISHDATE,
                f_PAGENUM,
                f_PAGENAME,
                f_PDFFILENAME,
                f_PAGEIMAGEFILENAME,
                f_MAP,
                f_YINTI,
                f_SITEID,
                f_SRCSITEID,
                f_DOCFIRSTPUBTIME,
                f_NODEID,
                f_ORDERID,
                f_CRDEPT,
                f_DOCFORM,
                f_DOCLEVEL,
                f_olddocpuburl,
                f_AttachVideo,
                f_AttachAudio,
                f_ThumbFiles,
                f_commentFlag,
                f_readingMoodFlag,
                f_FromType,
                f_FromId,
                f_OriginDocId
                );

          SET @sql = trs_data_migration.build_migration_log_sql('wcmdocument', @DOCID);

          prepare stmt from @sql;
          EXECUTE stmt;
          deallocate prepare stmt;
    END IF ;

		FETCH cur_doc INTO
      f_DOCID,
      f_DOCCHANNEL,
      f_DOCVERSION,
      f_DOCTYPE,
      f_DOCTITLE,
      f_ShortTitle,
      f_DOCSOURCE,
      f_DOCSECURITY,
      f_DOCSTATUS,
      f_DOCKIND,
      f_DOCCONTENT,
      f_DOCHTMLCON,
      f_DOCABSTRACT,
      f_DOCKEYWORDS,
      f_DOCRELWORDS,
      f_DOCPEOPLE,
      f_DOCPLACE,
      f_DOCAUTHOR,
      f_DOCEDITOR,
      f_DOCAUDITOR,
      f_DOCOUTUPID,
      f_DOCVALID,
      f_DOCPUBURL,
      f_DOCPUBTIME,
      f_DOCRELTIME,
      f_CRUSER,
      f_CRTIME,
      f_DOCWORDSCOUNT,
      f_DOCPRO,
      f_RIGHTDEFINED,
      f_TITLECOLOR,
      f_TEMPLATEID,
      f_SCHEDULE,
      f_DOCNO,
      f_DOCFLAG,
      f_EDITOR,
      f_ATTRIBUTE,
      f_HITSCOUNT,
      f_DOCPUBHTMLCON,
      f_SUBDOCTITLE,
      f_ATTACHPIC,
      f_DOCLINK,
      f_DOCFILENAME,
      f_DOCFROMVERSION,
      f_OPERTIME,
      f_OPERUSER,
      f_FLOWOPERATIONMARK,
      f_FLOWPREOPERATIONMARK,
      f_FLOWOPERATIONMASKENUM,
      f_DOCSOURCENAME,
      f_DOCLINKTO,
      f_DOCMIRRORTO,
      f_RANDOMSERIAL,
      f_POSTUSER,
      f_ISPAGEIMG,
      f_PUBLISHDATE,
      f_PAGENUM,
      f_PAGENAME,
      f_PDFFILENAME,
      f_PAGEIMAGEFILENAME,
      f_MAP,
      f_YINTI,
      f_SITEID,
      f_SRCSITEID,
      f_DOCFIRSTPUBTIME,
      f_NODEID,
      f_ORDERID,
      f_CRDEPT,
      f_DOCFORM,
      f_DOCLEVEL,
      f_olddocpuburl,
      f_AttachVideo,
      f_AttachAudio,
      f_ThumbFiles,
      f_commentFlag,
      f_readingMoodFlag,
      f_FromType,
      f_FromId,
      f_OriginDocId
      ;
		
	END WHILE;
	
	CLOSE cur_doc;

    SET @sql = trs_data_migration.build_migration_log_sql('wcmdocument', -1);

    prepare stmt from @sql;
    EXECUTE stmt;
    deallocate prepare stmt;
		
	END $$
DELIMITER ;