delete from `sys_dict` where codeType = 'riskAssessmentLevel';

INSERT INTO `sys_dict` (`sysDictId`, `isActive`, `codeType`, `tableName`, `description`, `codeValue`, `label`, `seq`) VALUES (null, '1', 'riskAssessmentLevel', NULL, '风险评估等级', '0', '否', '1');
INSERT INTO `sys_dict` (`sysDictId`, `isActive`, `codeType`, `tableName`, `description`, `codeValue`, `label`, `seq`) VALUES ('235', '1', 'riskAssessmentLevel', NULL, '风险评估等级', '1', '是', '2');

update security_company set risk_assessment_level='1' where ifnull(risk_assessment_level,'')!=''

UPDATE `sys_dict` SET `label`='未核对' WHERE codeType='checkState' and codeValue='0';
UPDATE `sys_dict` SET `label`='核对通过' WHERE codeType='checkState' and codeValue='1';
UPDATE `sys_dict` SET `label`='核对不通过' WHERE codeType='checkState' and codeValue='2';
UPDATE `sys_dict` SET `label`='已完成' WHERE codeType='periodStatus' and codeValue='1';
UPDATE `sys_dict` SET `label`='已完成' WHERE codeType='practiceStatus' and codeValue='1';


#等级证申报状态
delete from sys_dict where codeType='applyStatus';
INSERT INTO `sys_dict` (`isActive`, `codeType`, `tableName`, `description`, `codeValue`, `label`, `seq`)
VALUES ('1', 'applyStatus', NULL, '申报状态', '0', '申报中', '1'),
('1', 'applyStatus', NULL, '申报状态', '1', '申报通过', '2'),
('1', 'applyStatus', NULL, '申报状态', '2', '申报不通过', '3'),
('1', 'applyStatus', NULL, '申报状态', '3', '申报撤销', '4');


#等级证背景筛查状态
delete from sys_dict where codeType='gradeCensorStatus';
INSERT INTO `sys_dict` (`isActive`, `codeType`, `tableName`, `description`, `codeValue`, `label`, `seq`)
VALUES ('1', 'gradeCensorStatus', NULL, '等级证背景筛查状态', '0', '审查中', '1'),
('1', 'gradeCensorStatus', NULL, '等级证背景筛查状态', '1', '正常', '2'),
('1', 'gradeCensorStatus', NULL, '等级证背景筛查状态', '2', '关注', '3'),
('1', 'gradeCensorStatus', NULL, '等级证背景筛查状态', '3', '申诉通过', '4'),
('1', 'gradeCensorStatus', NULL, '等级证背景筛查状态', '4', '申诉不通过', '5'),
('1', 'gradeCensorStatus', NULL, '等级证背景筛查状态', '5', '申诉中', '6');


update sb_person_grade set approvalState='0' where approvalState='002';
update sb_person_grade set approvalState='1' where approvalState='003';
update sb_person_grade set approvalState='2' where approvalState='004';
update sb_person_grade set approvalState='0' where approvalState='005';
update sb_person_grade set approvalState='3' where approvalState='009';

update sb_person_grade set censorStatus='0' where censorStatus='001';
update sb_person_grade set censorStatus='1' where censorStatus='003';
update sb_person_grade set censorStatus='2' where censorStatus='004';

#等级证报名导出视图
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',
	IF(t2.practiceStartDate is null,'',CONCAT(DATE_FORMAT(t2.practiceStartDate,'%Y-%m-%d'),' ~ ',DATE_FORMAT(t2.practiceEndDate,'%Y-%m-%d'))) as 'trainTime'
	FROM sb_person_grade t1
	LEFT JOIN sb_grade_class t2 ON t1.classId = t2.id and t2.active = true
	where
	  t1.active =true
	AND t1.id IN (
    SELECT
      SUBSTRING_INDEX(GROUP_CONCAT(tt.id ORDER BY tt.create_time DESC),',',1)
    FROM
      sb_person_grade tt
    WHERE
      tt.active = TRUE
    GROUP BY
      tt.personId
  )