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

use trs_data_migration;

DROP PROCEDURE IF EXISTS p_data_migration_wcmtemplate;
DELIMITER $$
CREATE PROCEDURE p_data_migration_wcmtemplate(IN prevTemplateId BIGINT(20),
                                              IN templateIdOffset BIGINT(20),
                                              IN siteIdOffset BIGINT(20),
                                              IN channelIdOffset BIGINT(20))
	BEGIN 
	
    DECLARE isover INT DEFAULT 0;

    DECLARE  f_TEMPID int(11) DEFAULT '0';
    DECLARE  f_TEMPNAME varchar(150) DEFAULT '0';
    DECLARE  f_TEMPDESC varchar(200) DEFAULT NULL;
    DECLARE  f_TEMPEXT varchar(50) DEFAULT 'htm';
    DECLARE  f_TEMPTEXT longtext;
    DECLARE  f_ATTRIBUTE varchar(500) DEFAULT NULL;
    DECLARE  f_CRUSER varchar(50) DEFAULT NULL;
    DECLARE  f_CRTIME datetime DEFAULT NULL;
    DECLARE  f_APDMODIFIED smallint(6) DEFAULT '0';
    DECLARE  f_TEMPTYPE smallint(6) DEFAULT '0';
    DECLARE  f_TEMPFORMID int(11) DEFAULT '0';
    DECLARE  f_ISPARSED smallint(6) DEFAULT '0';
    DECLARE  f_FOLDERTYPE int(11) DEFAULT '103';
    DECLARE  f_ROOTID int(11) DEFAULT '0';
    DECLARE  f_FOLDERID int(11) DEFAULT '0';
    DECLARE  f_ROOTTYPE int(11) DEFAULT '0';
    DECLARE  f_OUTPUTFILENAME varchar(50) DEFAULT NULL;
    DECLARE  f_LASTMODIFIEDUSER varchar(60) DEFAULT NULL;
    DECLARE  f_LASTMODIFIEDTIME datetime DEFAULT NULL;
    DECLARE  f_VISUAL int(11) DEFAULT NULL;


    DECLARE cur CURSOR FOR
		SELECT
      TEMPID,
      TEMPNAME,
      TEMPDESC,
      TEMPEXT,
      TEMPTEXT,
      ATTRIBUTE,
      CRUSER,
      CRTIME,
      APDMODIFIED,
      TEMPTYPE,
      TEMPFORMID,
      ISPARSED,
      FOLDERTYPE,
      ROOTID,
      FOLDERID,
      ROOTTYPE,
      OUTPUTFILENAME,
      LASTMODIFIEDUSER,
      LASTMODIFIEDTIME,
      VISUAL
    FROM trs_data_migration.wcmtemplate
		ORDER BY FOLDERPUBLISHCONFIGID asc;

	DECLARE CONTINUE HANDLER FOR NOT FOUND SET isover = 1;
	
	OPEN cur;
	
	FETCH cur INTO
    f_TEMPID,
    f_TEMPNAME,
    f_TEMPDESC,
    f_TEMPEXT,
    f_TEMPTEXT,
    f_ATTRIBUTE,
    f_CRUSER,
    f_CRTIME,
    f_APDMODIFIED,
    f_TEMPTYPE,
    f_TEMPFORMID,
    f_ISPARSED,
    f_FOLDERTYPE,
    f_ROOTID,
    f_FOLDERID,
    f_ROOTTYPE,
    f_OUTPUTFILENAME,
    f_LASTMODIFIEDUSER,
    f_LASTMODIFIEDTIME,
    f_VISUAL
    ;



    WHILE isover= 0 DO

      # 断点续传
      IF (f_TEMPID > prevTemplateId)

        THEN

            SET @TEMPID = f_TEMPID;
            SET f_TEMPID = f_TEMPID + templateIdOffset;

            IF (f_FOLDERTYPE = 103)
              THEN SET f_FOLDERID = f_FOLDERID + siteIdOffset;
            END IF ;

            IF (f_FOLDERTYPE = 101)
              THEN SET f_FOLDERID = f_FOLDERID + channelIdOffset;
            END IF ;

            IF (f_ROOTTYPE = 103)
              THEN SET f_ROOTID = f_ROOTID + siteIdOffset;
            END IF ;

            IF (f_ROOTTYPE = 101)
              THEN SET f_ROOTID = f_ROOTID + channelIdOffset;
            END IF ;


            insert into trs_hycloud_iip.wcmtemplate
              (
                TEMPID,
                TEMPNAME,
                TEMPDESC,
                TEMPEXT,
                TEMPTEXT,
                ATTRIBUTE,
                CRUSER,
                CRTIME,
                APDMODIFIED,
                TEMPTYPE,
                TEMPFORMID,
                ISPARSED,
                FOLDERTYPE,
                ROOTID,
                FOLDERID,
                ROOTTYPE,
                OUTPUTFILENAME,
                LASTMODIFIEDUSER,
                LASTMODIFIEDTIME,
                VISUAL
              )
              VALUES
                (
                  f_TEMPID,
                  f_TEMPNAME,
                  f_TEMPDESC,
                  f_TEMPEXT,
                  f_TEMPTEXT,
                  f_ATTRIBUTE,
                  f_CRUSER,
                  f_CRTIME,
                  f_APDMODIFIED,
                  f_TEMPTYPE,
                  f_TEMPFORMID,
                  f_ISPARSED,
                  f_FOLDERTYPE,
                  f_ROOTID,
                  f_FOLDERID,
                  f_ROOTTYPE,
                  f_OUTPUTFILENAME,
                  f_LASTMODIFIEDUSER,
                  f_LASTMODIFIEDTIME,
                  f_VISUAL
                );

          SET @sql = trs_data_migration.build_migration_log_sql('wcmtemplate', @TEMPID);

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

		FETCH cur INTO
      f_TEMPID,
      f_TEMPNAME,
      f_TEMPDESC,
      f_TEMPEXT,
      f_TEMPTEXT,
      f_ATTRIBUTE,
      f_CRUSER,
      f_CRTIME,
      f_APDMODIFIED,
      f_TEMPTYPE,
      f_TEMPFORMID,
      f_ISPARSED,
      f_FOLDERTYPE,
      f_ROOTID,
      f_FOLDERID,
      f_ROOTTYPE,
      f_OUTPUTFILENAME,
      f_LASTMODIFIEDUSER,
      f_LASTMODIFIEDTIME,
      f_VISUAL
    ;
		
	END WHILE;
	
	CLOSE cur;

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

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