ALTER TABLE `sb_person_grade`
ADD COLUMN `makeUp`  varchar(5) NULL COMMENT '是否补考 1是 0否',
ADD COLUMN `firstTrainId`  bigint(20) NULL COMMENT '首次培训机构id',
ADD COLUMN `firstTrainName`  varchar(200) NULL COMMENT '首次培训机构',
ADD COLUMN `firstTrainPhone`  varchar(50) NULL COMMENT '首次培训机构联系方式';

ALTER TABLE `grade_subsidy_roster`
ADD COLUMN `batchId`  bigint(20) NULL COMMENT '批次id',
MODIFY COLUMN `used`  varchar(5) NULL COMMENT '是否补贴申请';


ALTER TABLE `security_certificate`
ADD COLUMN `userid`  bigint(20) NULL COMMENT '用户id',
ADD COLUMN `areaCode`  varchar(6) NULL COMMENT '数据区域',
ADD COLUMN `nation`  varchar(20) NULL COMMENT '民族',
ADD COLUMN `personId`  bigint(20) NULL COMMENT '保安员id',
ADD COLUMN `orgId`  bigint(20) NULL COMMENT '办事机构id',
ADD COLUMN `sex`  varchar(5) NULL COMMENT '性别',
ADD COLUMN `phone`  varchar(20) NULL COMMENT '手机号',
ADD COLUMN `trainId`  bigint(20) NULL COMMENT '培训单位id',
ADD COLUMN `trainName`  varchar(200) NULL COMMENT '培训单位',
ADD COLUMN `companyId`  bigint(20) NULL COMMENT '报名所在单位id',
ADD COLUMN `populationAddress`  varchar(500) NULL COMMENT '人口库地址';



#等级证报名导出视图
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,
	ifnull(t1.compareStatus,'01') as compareStatus,
	ifnull(t1.makeUp,'0') as makeUp,
	t1.compareReason,
	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.newest = true;