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

DROP PROCEDURE IF EXISTS sp_data_migration_wcmwebsite;
DELIMITER $$
CREATE PROCEDURE sp_data_migration_wcmwebsite(IN citySuffix VARCHAR(255), IN prevSiteId BIGINT(20), IN siteIdOffset BIGINT(20))
	BEGIN 
	
	  DECLARE isover INT DEFAULT 0;

	  DECLARE v_classificationId INT DEFAULT 0;

    DECLARE migr_table_name varchar(255) DEFAULT 'wcmwebsite';
	
    DECLARE f_SITEID int(11) DEFAULT 0;
    DECLARE f_SITENAME varchar(200) DEFAULT 0;
    DECLARE f_SITEDESC varchar(200) DEFAULT NULL;
    DECLARE f_FTPSERVER varchar(200) DEFAULT NULL;
    DECLARE f_FTPUSER varchar(50) DEFAULT NULL;
    DECLARE f_FTPPASSWORD varchar(50) DEFAULT NULL;
    DECLARE f_FTPFOLDER varchar(200) DEFAULT NULL;
    DECLARE f_DATAPATH varchar(200) DEFAULT NULL;
    DECLARE f_WEBHTTP varchar(200) DEFAULT NULL;
    DECLARE f_SITEPROP int(11) DEFAULT NULL;
    DECLARE f_PARENTID int(11) DEFAULT NULL;
    DECLARE f_SITEORDER int(11) DEFAULT 0;
    DECLARE f_ATTRIBUTE varchar(500) DEFAULT NULL;
    DECLARE f_HOMETEMPLATEID int(11) DEFAULT NULL;
    DECLARE f_SITEORDERBY varchar(200) DEFAULT NULL;
    DECLARE f_CRUSER varchar(50) DEFAULT NULL;
    DECLARE f_CRTIME datetime DEFAULT NULL;
    DECLARE f_PUBSTATUSES varchar(200) DEFAULT NULL;
    DECLARE f_SCHEDULE int(11) DEFAULT 0;
    DECLARE f_STATUS int(11) DEFAULT 0;
    DECLARE f_PUBLISHPRO smallint(6) DEFAULT 1;
    DECLARE f_OPERUSER varchar(50) DEFAULT NULL;
    DECLARE f_OPERTIME datetime DEFAULT NULL;
    DECLARE f_SITETYPE int(11) DEFAULT 0;
    DECLARE f_lastmodifytime datetime DEFAULT NULL;
    DECLARE f_ISMOBILE int(11) DEFAULT 0;
    DECLARE f_BASEPROPS varchar(300) DEFAULT NULL;
    DECLARE f_OTHERPROPS varchar(300) DEFAULT NULL;
    DECLARE f_ADVANCEPROPS varchar(300) DEFAULT NULL;
    DECLARE f_VIEWPROPS varchar(300) DEFAULT NULL;
    DECLARE f_NEEDEDPROPS varchar(300) DEFAULT NULL;
    DECLARE f_MediaType int(11) DEFAULT 1;
    DECLARE f_IsSubscribe smallint(6) DEFAULT 0;
    DECLARE f_ISPUSHABLE tinyint(4) DEFAULT 0 ;
    DECLARE f_ISDISTRIBUTABLE tinyint(4) DEFAULT 0 ;
    DECLARE f_OUTLINETEMPLATE int(11) DEFAULT 0;
    DECLARE f_CHNLOUTLINETEMP int(11) DEFAULT 0;
    DECLARE f_DETAILTEMPLATE int(11) DEFAULT 0;
    DECLARE f_SITEDESCPINYIN varchar(254) DEFAULT NULL;
    DECLARE f_IDXPREFIX varchar(30) DEFAULT NULL ;
    DECLARE f_AUTOCHECK tinyint(4) DEFAULT 0 ;
  





    DECLARE cur CURSOR FOR
		SELECT
      SITEID,
      SITENAME,
      SITEDESC,
      FTPSERVER,
      FTPUSER,
      FTPPASSWORD,
      FTPFOLDER,
      DATAPATH,
      WEBHTTP,
      SITEPROP,
      PARENTID,
      SITEORDER,
      ATTRIBUTE,
      HOMETEMPLATEID,
      SITEORDERBY,
      CRUSER,
      CRTIME,
      PUBSTATUSES,
      SCHEDULE,
      STATUS,
      PUBLISHPRO,
      OPERUSER,
      OPERTIME,
      SITETYPE,
      lastmodifytime,
      ISMOBILE,
      BASEPROPS,
      OTHERPROPS,
      ADVANCEPROPS,
      VIEWPROPS,
      NEEDEDPROPS,
      MediaType,
      0,
      0,
      0,
      0,
      0,
      0,
      null,
      null,
      0
		FROM wcmwebsite
		ORDER BY siteid asc;

	DECLARE CONTINUE HANDLER FOR NOT FOUND SET isover = 1;
	
	OPEN cur;
	
	FETCH cur INTO
    f_SITEID,
    f_SITENAME,
    f_SITEDESC,
    f_FTPSERVER,
    f_FTPUSER,
    f_FTPPASSWORD,
    f_FTPFOLDER,
    f_DATAPATH,
    f_WEBHTTP,
    f_SITEPROP,
    f_PARENTID,
    f_SITEORDER,
    f_ATTRIBUTE,
    f_HOMETEMPLATEID,
    f_SITEORDERBY,
    f_CRUSER,
    f_CRTIME,
    f_PUBSTATUSES,
    f_SCHEDULE,
    f_STATUS,
    f_PUBLISHPRO,
    f_OPERUSER,
    f_OPERTIME,
    f_SITETYPE,
    f_lastmodifytime,
    f_ISMOBILE,
    f_BASEPROPS,
    f_OTHERPROPS,
    f_ADVANCEPROPS,
    f_VIEWPROPS,
    f_NEEDEDPROPS,
    f_MediaType,
    f_IsSubscribe,
    f_ISPUSHABLE,
    f_ISDISTRIBUTABLE,
    f_OUTLINETEMPLATE,
    f_CHNLOUTLINETEMP,
    f_DETAILTEMPLATE,
    f_SITEDESCPINYIN,
    f_IDXPREFIX,
    f_AUTOCHECK
    ;


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


    # 新建站点分类
    SET @metaTableExists = IFNULL((SELECT count(1) from trs_hycloud_iip.wcmclassification where CNAME = concat('贵州迁移站点_', citySuffix)), 0);

    if @metaTableExists = 0
      then
        SELECT ifnull(max(classificationId), 0) into v_classificationId from trs_hycloud_iip.wcmclassification;
        #select v_classificationId;
        SET @saveClassification_sql =  concat('insert into ',
                                                 'trs_hycloud_iip.wcmclassification (',
                                                     'CLASSIFICATIONID,',
                                                     'CNAME,',
                                                     'CSHORTNAME,',
                                                     'CTYPE',
                                                 ') ',
                                                 'values(',
                                                    (v_classificationId + 1), ',',
                                                    '\'', '贵州迁移站点_', citySuffix, '\',',
                                                    '\'', right(citySuffix, 5), '\',',
                                                    '11',
                                                 ') ');
        prepare stmt from @saveClassification_sql;
        EXECUTE stmt;
        deallocate prepare stmt;
    else
      SELECT ifnull(max(classificationId), 0) into v_classificationId from trs_hycloud_iip.wcmclassification where CNAME = concat('贵州迁移站点_', citySuffix);
    end if;



    WHILE isover= 0 DO

      # 支持断点续传
      IF (f_SITEID > prevSiteId)

        THEN

            SET @SITEID = f_SITEID;
            SET f_SITEID = f_SITEID + siteIdOffset;

            insert into trs_hycloud_iip.wcmwebsite
              (
                SITEID,
                SITENAME,
                SITEDESC,
                FTPSERVER,
                FTPUSER,
                FTPPASSWORD,
                FTPFOLDER,
                DATAPATH,
                WEBHTTP,
                SITEPROP,
                PARENTID,
                SITEORDER,
                ATTRIBUTE,
                HOMETEMPLATEID,
                SITEORDERBY,
                CRUSER,
                CRTIME,
                PUBSTATUSES,
                SCHEDULE,
                STATUS,
                PUBLISHPRO,
                OPERUSER,
                OPERTIME,
                SITETYPE,
                lastmodifytime,
                ISMOBILE,
                BASEPROPS,
                OTHERPROPS,
                ADVANCEPROPS,
                VIEWPROPS,
                NEEDEDPROPS,
                MediaType,
                IsSubscribe,
                ISPUSHABLE,
                ISDISTRIBUTABLE,
                OUTLINETEMPLATE,
                CHNLOUTLINETEMP,
                DETAILTEMPLATE,
                SITEDESCPINYIN,
                IDXPREFIX,
                AUTOCHECK
              )
              VALUES
                (
                  f_SITEID,
                  f_SITENAME,
                  f_SITEDESC,
                  f_FTPSERVER,
                  f_FTPUSER,
                  f_FTPPASSWORD,
                  f_FTPFOLDER,
                  f_DATAPATH,
                  f_WEBHTTP,
                  f_SITEPROP,
                  f_PARENTID,
                  f_SITEORDER,
                  f_ATTRIBUTE,
                  f_HOMETEMPLATEID,
                  f_SITEORDERBY,
                  f_CRUSER,
                  f_CRTIME,
                  f_PUBSTATUSES,
                  f_SCHEDULE,
                  f_STATUS,
                  f_PUBLISHPRO,
                  f_OPERUSER,
                  f_OPERTIME,
                  f_SITETYPE,
                  f_lastmodifytime,
                  f_ISMOBILE,
                  f_BASEPROPS,
                  f_OTHERPROPS,
                  f_ADVANCEPROPS,
                  f_VIEWPROPS,
                  f_NEEDEDPROPS,
                  f_MediaType,
                  f_IsSubscribe,
                  f_ISPUSHABLE,
                  f_ISDISTRIBUTABLE,
                  f_OUTLINETEMPLATE,
                  f_CHNLOUTLINETEMP,
                  f_DETAILTEMPLATE,
                  f_SITEDESCPINYIN,
                  f_IDXPREFIX,
                  f_AUTOCHECK
                );

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

          prepare stmt from @log_sql;
          EXECUTE stmt;
          deallocate prepare stmt;

          call setOffset(migr_table_name, siteIdOffset, prevSiteId, @SITEID);

          # 插入 站点分类-站点 关系数据
          set @classificationObjId = (SELECT ifnull(max(classificationObjId), 0) from trs_hycloud_iip.wcmclassificationobj);

          # select @classificationObjId;

          SET @saveClassificationObj_sql =  concat('insert into ',
                             'trs_hycloud_iip.wcmclassificationobj (',
                               'classificationObjId,',
                               'classificationId,',
                               'objType,',
                               'objId',
                             ') ',
                             'values(',
                                 (@classificationObjId + 1), ',',
                                 v_classificationId + 1, ',',
                                 '103,',
                                 f_SITEID,
                             ') ');
          prepare stmt from @saveClassificationObj_sql;
          EXECUTE stmt;
          deallocate prepare stmt;

          set @pubstatusconfigId = (SELECT ifnull(max(WCMPUBSTATUSCONFIGID), 0) from trs_hycloud_iip.wcmpubstatusconfig);

          SET @savepubstatusconfig_sql =  concat('insert into ',
                             'trs_hycloud_iip.wcmpubstatusconfig (',
                               'WCMPUBSTATUSCONFIGID,',
                               'FOLDERTYPE,',
                               'FOLDERID,',
                               'STATUSESCANDOPUB,',
                               'STATUSIDAFTERMODIFY,',
                               'CRUSER,',
                               'CRTIME',
                             ') ',
                             'values(',
                                 (@pubstatusconfigId + 1), ',',
                                 '103,',
                                 f_SITEID, ',',
                                 '\'12,10,8,1027\', 1,\'admin\', \'2018-09-20 14:50:31\') ');
          prepare stmt from @savepubstatusconfig_sql;
          EXECUTE stmt;
          deallocate prepare stmt;

    END IF ;

		FETCH cur INTO
      f_SITEID,
      f_SITENAME,
      f_SITEDESC,
      f_FTPSERVER,
      f_FTPUSER,
      f_FTPPASSWORD,
      f_FTPFOLDER,
      f_DATAPATH,
      f_WEBHTTP,
      f_SITEPROP,
      f_PARENTID,
      f_SITEORDER,
      f_ATTRIBUTE,
      f_HOMETEMPLATEID,
      f_SITEORDERBY,
      f_CRUSER,
      f_CRTIME,
      f_PUBSTATUSES,
      f_SCHEDULE,
      f_STATUS,
      f_PUBLISHPRO,
      f_OPERUSER,
      f_OPERTIME,
      f_SITETYPE,
      f_lastmodifytime,
      f_ISMOBILE,
      f_BASEPROPS,
      f_OTHERPROPS,
      f_ADVANCEPROPS,
      f_VIEWPROPS,
      f_NEEDEDPROPS,
      f_MediaType,
      f_IsSubscribe,
      f_ISPUSHABLE,
      f_ISDISTRIBUTABLE,
      f_OUTLINETEMPLATE,
      f_CHNLOUTLINETEMP,
      f_DETAILTEMPLATE,
      f_SITEDESCPINYIN,
      f_IDXPREFIX,
      f_AUTOCHECK
    ;
		
	END WHILE;
	
	CLOSE cur;

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




  END $$
DELIMITER ;