delete from sys_dict where codeType='appealType';
INSERT INTO `sys_dict` (`isActive`, `codeType`, `tableName`, `description`, `codeValue`, `label`, `seq`)
VALUES ('1', 'appealType', NULL, '申诉类型', '001', '身份认证', '1'),
('1', 'appealType', NULL, '申诉类型', '002', '资格证', '2'),
('1', 'appealType', NULL, '申诉类型', '003', '等级证', '3');


delete from sys_dict where codeType='practiceStatus';
INSERT INTO `sys_dict` (`isActive`, `codeType`, `tableName`, `description`, `codeValue`, `label`, `seq`)
VALUES ('1', 'practiceStatus', NULL, '实习状态', '1', '完成', '1'),
('1', 'practiceStatus', NULL, '实习状态', '0', '未完成', '2');


ALTER TABLE `sb_person_grade`
ADD COLUMN `practiceStatus`  varchar(5) NULL COMMENT '实习状态' AFTER `periodStatus`;


#等级证报名导出视图
drop view if EXISTS v_person_grade;
CREATE
VIEW `v_person_grade`AS
SELECT
	t1.id,
	t1.active,
	t1.create_time,
	t1.update_time,
	t1.update_by,
	t1.updateflag,
	t1.userid,
	t1.areaCode,
	t1.orgId,
	t1.personId,
	t1.name,
	t1.phone,
	t1.idNum,
	t1.applyTime,
	t1.trainId,
	t1.trainName,
	t1.testTime,
	t1.idFrontImg,
	t1.idBackImg,
	t1.headImg,
	t1.comId,
	t1.comName,
	t1.gradeLevel,
	t1.period,
	t1.periodStatus,
	t1.practiceStatus,
	t1.practiceDay,
	t1.practiceHour,
	t1.censorStatus,
	t1.address,
	t1.liveAddress,
	t1.residentType,
	t1.residentPlaceType,
	t1.contractId,
	t1.classId,
	t1.testStateComplete,
	t1.writtenScoreComplete,
	t1.operateScoreComplete,
	t1.sumScoreComplete,
	t1.zgCerNo,
	t1.zgCerUrl,
	t1.zgOrg,
	t1.zgTrainName,
	t1.zgIssueDate,
	t1.payTime,
	t1.payState,
	t1.checkState,
	t1.classState,
	t1.writtenScore,
	t1.operateScore,
	t1.sumScore,
	t1.testState,
	t1.approvalReason,
	t1.approvalState,
	t1.gradePersonType,
	t1.mainHomeUrl,
	t1.selfHomeUrl,
	t1.regCerUrl,
	t1.gradCerUrl,
	t1.empCerUrl,
	t1.laborTranCerUrl,
	t1.dischargeCerUrl,
	t1.selfEmpCerUrl,
	t1.demobCerUrl,
	t1.conversionCerUrl,
	t1.confirmCerUrl,
	t2.`name` as 'className',
	IF(t2.startTime is null,'',CONCAT(DATE_FORMAT(t2.startTime,'%Y-%m-%d'),' ~ ',DATE_FORMAT(t2.endTime,'%Y-%m-%d'))) as 'classStartTime'
	FROM sb_person_grade t1
	LEFT JOIN sb_grade_class t2 ON t1.classId = t2.id and t2.active = true
	where
	t1.active =true;


DROP TABLE IF EXISTS v_sb_grade_class;
create table v_sb_grade_class as select * from (
	select * from sb_grade_class
) as sql_table;

DROP TABLE if EXISTS sb_grade_class;
CREATE TABLE `sb_grade_class` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `active` bit(1) DEFAULT b'1' COMMENT '是否可用',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '修改时间',
  `userid` bigint(20) DEFAULT NULL COMMENT '用户id',
  `name` varchar(50) DEFAULT NULL COMMENT '班级名称',
  `trainId` bigint(20) DEFAULT NULL COMMENT '培训机构id',
  `trainName` varchar(500) DEFAULT NULL COMMENT '培训机构',
  `perNum` int(10) DEFAULT NULL COMMENT '班级人数',
  `startTime` varchar(50) DEFAULT NULL COMMENT '考试开始时间',
  `endTime` varchar(50) DEFAULT NULL COMMENT '考试结束时间',
  `approvalState` varchar(5) DEFAULT NULL COMMENT '审批状态',
  `address` varchar(500) DEFAULT NULL COMMENT '考试地点',
  `approvalReson` varchar(500) DEFAULT NULL COMMENT '审批原因',
  `updateflag` bit(1) DEFAULT NULL COMMENT '更新标识',
  `approvalResonIn` varchar(500) DEFAULT NULL COMMENT '对内意思',
  `update_by` varchar(50) DEFAULT NULL COMMENT '更新人',
  `gradeLevel` varchar(10) DEFAULT NULL COMMENT '等级',
  `contactPerson` varchar(50) DEFAULT NULL COMMENT '考场联系人',
  `contactPersonTel` varchar(20) DEFAULT NULL COMMENT '考场联系人电话',
  `otherTel` varchar(20) DEFAULT NULL COMMENT '其它联系方式',
  `contactPersonEmail` varchar(20) DEFAULT NULL COMMENT '考场联系人邮箱',
  `graduationStartTime` datetime DEFAULT NULL COMMENT '结业理论考试开始时间',
  `graduationEndTime` datetime DEFAULT NULL COMMENT '结业理论考试结束时间',
  `graduationStartAddress` varchar(200) DEFAULT NULL COMMENT '结业理论考试地点',
  `graduationStartExamType` varchar(10) DEFAULT NULL COMMENT '考试试卷',
  `graduationOperationStartTime` datetime DEFAULT NULL COMMENT '实操考试开始时间',
  `graduationOperationEndTime` datetime DEFAULT NULL COMMENT '实操考试结束时间',
  `graduationOperationAddress` varchar(200) DEFAULT NULL COMMENT '实操考试地点',
  `appraisalStartTime` datetime DEFAULT NULL COMMENT '鉴定理论考试时间',
  `appraisalEndTime` datetime DEFAULT NULL COMMENT '鉴定理论考试开始时间',
  `appraisalAddress` varchar(200) DEFAULT NULL COMMENT '鉴定理论考试地址',
  `appraisalOperationStartTime` datetime DEFAULT NULL COMMENT '鉴定实操考试开始时间',
  `appraisalOperationEndTime` datetime DEFAULT NULL COMMENT '鉴定实操考试结束时间',
  `appraisalOperationAddress` varchar(200) DEFAULT NULL COMMENT '鉴定实操考试地点',
  `graduationQualified` varchar(10) DEFAULT NULL COMMENT '结业理论成绩合格',
  `graduationOperationQualified` varchar(10) DEFAULT NULL COMMENT '结业实操成绩合格',
  `appraisalQualified` varchar(10) DEFAULT NULL COMMENT '鉴定实操成绩合格',
  `appraisalOperationQualified` varchar(10) DEFAULT NULL COMMENT '鉴定实操成绩合格',
  `graduationStartExamPaper` varchar(255) DEFAULT NULL COMMENT '结业考试试卷',
  `operationAddress` varchar(300) DEFAULT NULL COMMENT '实操培训地点',
  `carefulMatter` varchar(500) DEFAULT NULL COMMENT '注意事项',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=679628575199985665 DEFAULT CHARSET=utf8;

INSERT into sb_grade_class
select * from v_sb_grade_class;

DROP TABLE IF EXISTS v_sb_grade_class;