DROP TABLE IF EXISTS `approve_area_process_temp`;
CREATE TABLE `approve_area_process_temp` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '区域审批流程ID',
  `active` bit(1) DEFAULT NULL COMMENT '是否可用',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '修改时间',
  `update_by` varchar(50) DEFAULT NULL COMMENT '修改者',
  `effectTime` datetime DEFAULT NULL COMMENT '生效时间',
  `failureTime` datetime DEFAULT NULL COMMENT '失效时间',
  `approveSetStatus` varchar(6) DEFAULT NULL COMMENT '审批配置状态',
  `regionLevel` varchar(6) DEFAULT NULL COMMENT '区域级别',
  `regionId` int(11) NOT NULL COMMENT '单位所在区域ID',
  `approveLinkLevelId` int(11) NOT NULL COMMENT '审批环节层级ID',
  `policeCode` varchar(50) NOT NULL COMMENT '单位代码',
  `policeId` int(11) NOT NULL COMMENT '单位代码',
  `path` varchar(50) DEFAULT NULL COMMENT '树',
  `processInfo` longtext COMMENT '流程配置信息',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=203040 DEFAULT CHARSET=utf8 COMMENT='区域审批流程';

DROP TABLE IF EXISTS `approve_link_level_temp`;
CREATE TABLE `approve_link_level_temp` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '审批环节层级ID',
  `active` bit(1) DEFAULT NULL COMMENT '是否可用',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '修改时间',
  `update_by` varchar(50) DEFAULT NULL COMMENT '修改者',
  `approveLevel` varchar(6) DEFAULT NULL COMMENT '审批层级',
  `parentId` int(11) DEFAULT NULL COMMENT '上级审批环节层级ID',
  `tempLinkRelaId` int(11) NOT NULL COMMENT '审批模板环节关联ID',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100406 DEFAULT CHARSET=utf8 COMMENT='审批环节层级';

DROP TABLE IF EXISTS `approve_temp_link_rela_temp`;
CREATE TABLE `approve_temp_link_rela_temp` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '审批模板环节关联ID',
  `active` bit(1) DEFAULT NULL COMMENT '是否可用',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '修改时间',
  `update_by` varchar(50) DEFAULT NULL COMMENT '修改者',
  `approveTempId` int(11) NOT NULL COMMENT '审批模板ID',
  `approveLinkId` int(11) NOT NULL COMMENT '审批环节ID',
  `seq` int(4) DEFAULT NULL COMMENT '排序',
  `linkSetStatus` varchar(6) DEFAULT '0' COMMENT '环节配置状态（1为配置完成 ，0为为配置）',
  `createType` varchar(3) DEFAULT '' COMMENT '创建机构类型(1省级机构，其他市级机构)',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10053 DEFAULT CHARSET=utf8 COMMENT='审批模板环节关联';


DROP PROCEDURE IF EXISTS `approveDataSyn`;

CREATE PROCEDURE `approveDataSyn`()
BEGIN
	update approve_area_process sp1,approve_area_process_temp sp2
  set
		sp1.active = sp2.active,
    sp1.create_time = sp2.create_time,
    sp1.update_time = sp2.update_time,
    sp1.update_by = sp2.update_by,
    sp1.effectTime = sp2.effectTime,
    sp1.failureTime = sp2.failureTime,
    sp1.approveSetStatus = sp2.approveSetStatus,
    sp1.regionLevel = sp2.regionLevel,
    sp1.regionId = sp2.regionId,
    sp1.approveLinkLevelId = sp2.approveLinkLevelId,
    sp1.policeCode = sp2.policeCode,
    sp1.policeId = sp2.policeId,
		sp1.path = sp2.path,
		sp1.processInfo = sp2.processInfo
	WHERE
		sp1.id = sp2.id
	AND
		sp2.update_time > sp1.update_time;


	INSERT INTO approve_area_process (id, active, create_time,
	      update_time, update_by, effectTime,
	      failureTime, approveSetStatus, regionLevel,
	      regionId, approveLinkLevelId, policeCode,
	      policeId, path, processInfo
	) SELECT
		id, active, create_time,
	      update_time, update_by, effectTime,
	      failureTime, approveSetStatus, regionLevel,
	      regionId, approveLinkLevelId, policeCode,
	      policeId, path, processInfo
	FROM
		approve_area_process_temp sp2
	WHERE
		NOT EXISTS (
			SELECT
				*
			FROM
				approve_area_process sp1
			WHERE
				sp1.id = sp2.id
		);

	update approve_temp_link_rela sp1,approve_temp_link_rela_temp sp2
  set
		sp1.active = sp2.active,
    sp1.create_time = sp2.create_time,
    sp1.update_time = sp2.update_time,
    sp1.update_by = sp2.update_by,
    sp1.approveTempId = sp2.approveTempId,
    sp1.approveLinkId = sp2.approveLinkId,
    sp1.seq = sp2.seq,
    sp1.createType = sp2.createType,
    sp1.linkSetStatus = sp2.linkSetStatus
	WHERE
		sp1.id = sp2.id
	AND
		sp2.update_time > sp1.update_time;


	INSERT INTO approve_temp_link_rela (id, active, create_time,
      update_time, update_by, approveTempId,
      approveLinkId, seq, linkSetStatus, createType
	) SELECT
		id, active, create_time,
      update_time, update_by, approveTempId,
      approveLinkId, seq, linkSetStatus, createType
	FROM
		approve_temp_link_rela_temp sp2
	WHERE
		NOT EXISTS (
			SELECT
				*
			FROM
				approve_temp_link_rela sp1
			WHERE
				sp1.id = sp2.id
		);

update approve_link_level sp1,approve_link_level_temp sp2
  set 
		sp1.active = sp2.active,
    sp1.create_time = sp2.create_time,
    sp1.update_time = sp2.update_time,
    sp1.update_by = sp2.update_by,
    sp1.approveLevel = sp2.approveLevel,
    sp1.parentId = sp2.parentId,
    sp1.tempLinkRelaId = sp2.tempLinkRelaId
	WHERE
		sp1.id = sp2.id
	AND 
		sp2.update_time > sp1.update_time;


	INSERT INTO approve_link_level (id, active, create_time, 
      update_time, update_by, approveLevel, 
      parentId, tempLinkRelaId
	) SELECT
		id, active, create_time, 
      update_time, update_by, approveLevel, 
      parentId, tempLinkRelaId
	FROM
		approve_link_level_temp sp2
	WHERE
		NOT EXISTS (
			SELECT
				*
			FROM
				approve_link_level sp1
			WHERE
				sp1.id = sp2.id
		);
END;