
ALTER TABLE `sb_person_grade`
ADD COLUMN `checkDate`  datetime DEFAULT NULL COMMENT '材料核对时间' AFTER `checkState`;


#等级证报名导出视图
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.checkDate,
	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
  )