#2020-09-16 sb_personcertificate  资格证表 新增是否自然人
ALTER TABLE sb_personcertificate  ADD  naturalPer varchar(5)   NULL DEFAULT '0' COMMENT '是否自然人';


 #2020-09-14 资格证结业电子证书头像
ALTER TABLE sb_grade_complete_cer  ADD  headImg varchar(500) NOT NULL DEFAULT '' COMMENT '头像';

#2020-07-06 保安师加培训机构
ALTER TABLE `temp_batch_security_guard_import`
ADD COLUMN `trainName`  varchar(200) NULL COMMENT '培训机构';

ALTER TABLE `security_guard`
ADD COLUMN `trainId`  BIGINT(20) NULL COMMENT '培训机构id',
ADD COLUMN `trainName`  varchar(200) NULL COMMENT '培训机构';

#2020-07-06 end

ALTER TABLE `sb_person_grade`
ADD COLUMN `inTime` datetime DEFAULT NULL COMMENT '内网更新时间',
ADD COLUMN `compareStatus`  varchar(5) NULL COMMENT '比对状态',
ADD COLUMN `compareReason`  varchar(500) NULL COMMENT '比对不通过原因';


#批次人员导出视图
drop view if EXISTS v_grade_subsidy_roster;
CREATE
VIEW `v_grade_subsidy_roster` AS
SELECT
	t1.*, t2.batchId
FROM grade_subsidy_roster t1
INNER JOIN sb_grade_subsidy_batch_roster t2 ON t1.id = t2.rosterId AND t2.active = TRUE
WHERE
	t1.active = TRUE;

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

ALTER TABLE `sb_personcertificate`
ADD COLUMN `checkState` VARCHAR(5) DEFAULT NULL COMMENT '材料核对状态',
ADD COLUMN `checkDate` datetime DEFAULT NULL COMMENT '材料核对时间',
ADD COLUMN `trainStartDate` datetime DEFAULT NULL COMMENT '培训开始时间',
ADD COLUMN `trainEndDate` datetime DEFAULT NULL COMMENT '培训结束时间';

ALTER TABLE `sys_data_change`
ADD COLUMN `tableName`  varchar(100) NULL COMMENT '表名' AFTER `entityClazz`;


ALTER TABLE `sb_grade_complete_cer`
ADD COLUMN `personId`  bigint(20) NULL COMMENT '人员id' AFTER `personGradeId`;


ALTER TABLE `sb_grade_appraisal_cer`
ADD COLUMN `personId`  bigint(20) NULL COMMENT '人员id' AFTER `personGradeId`;

ALTER TABLE `sb_personcertificate`
ADD COLUMN `bonusApply` VARCHAR(5) NULL COMMENT '是否要申请补贴' AFTER `checkDate`;

#yyx 20200617
ALTER TABLE `security_certificate`
ADD COLUMN `isDraw`  bit NULL COMMENT '是否抽取' AFTER `snapshotId`;


drop view if EXISTS v_person_certificate;
CREATE
VIEW `v_person_certificate` AS
SELECT
t.id,t.sex,t.brith,t.nation,t.politics,t.culture,t.zipcode,t.approvaldate,t.address,t.beforeName,
t.guaranteecar,t.familyPhone,t.militaryService,t.fingerprint,t.religion,t.nationality,t.birthPlace,
t.hight,t.booldType,t.healthStatus,t.nowAddress,t.baseRemack,t.operate_score,t.sum_score,t.fingercard_state,
t.szss,t.nocrime_photo,t.exam_address,t.exam_start,t.exam_end,t.stamina_address,t.stamina_startDate,
t.stamina_endDate,t.signConfirm,t.payConfirm,t.isMakeUp,t.proState,t.makeUpstate,t.approvalperson,
t.approvalreason,t.applyforstate,
t.approvalstate as approvalState,   -- 报名审批状态
t.companyname as scName, -- 报名公司名称
t.name,  -- 姓名
t.orgid, -- 机构id
IFNULL(t.censor_status,'001') as censorStatus, -- 背景筛查
t.trainOrgName,   -- 所属培训机构
date_format(t.create_time,'%Y-%m-%d')as createTime,    -- 报名日期
IFNULL(t.fee_state,'0') as feeState,      -- 缴费情况
IFNULL(t.test_state,'2') as testState,    -- 考试结果
t.Cardnumber as cardNumber,         -- 身份证号
t.phone ,        -- 手机号
IFNULL(t.checkState,'0') as checkState,    -- 材料核对状态
t.exam_state as examState,    -- 考试安排
t.written_score as writtenScore,   -- 笔试成绩
t.active,
t.payComName,
sa.areaCode,
sa.id as approvalId,
sa.processInstanceId
FROM
sb_personcertificate t
INNER JOIN sys_approval sa ON t.id = sa.businessid and sa.businesstype='004' and sa.active =true
WHERE t.active = true
AND NOT EXISTS (SELECT 1   /*<!-- 排除历史导入的保安员 by llc 2018-08-21 -->*/
FROM security_certificate
WHERE active = 1
AND securitypersonid = t.id
AND isOldData = '1');


-- #dt20200827

drop view if EXISTS v_person_certificate;
CREATE
VIEW `v_person_certificate` AS
SELECT
t.id,t.sex,t.brith,t.nation,t.politics,t.culture,t.zipcode,t.approvaldate,t.address,t.beforeName,
t.guaranteecar,t.familyPhone,t.militaryService,t.fingerprint,t.religion,t.nationality,t.birthPlace,
t.hight,t.booldType,t.healthStatus,t.nowAddress,t.baseRemack,t.operate_score,t.sum_score,t.fingercard_state,
t.szss,t.nocrime_photo,t.exam_address,
t.exam_start,
t.exam_start as examTime,
t.exam_end,
t.stamina_address,t.stamina_startDate,
t.stamina_endDate,t.signConfirm,t.payConfirm,t.isMakeUp,t.proState,t.makeUpstate,t.approvalperson,
t.approvalreason,t.applyforstate,
t.approvalstate as approvalState,   -- 报名审批状态
t.companyname as scName, -- 报名公司名称
t.name,  -- 姓名
t.orgid, -- 机构id
IFNULL(t.censor_status,'001') as censorStatus, -- 背景筛查
t.trainOrgName,   -- 所属培训机构
date_format(t.create_time,'%Y-%m-%d')as createTime,    -- 报名日期
IFNULL(t.fee_state,'0') as feeState,      -- 缴费情况
IFNULL(t.test_state,'2') as testState,    -- 考试结果
t.Cardnumber as cardNumber,         -- 身份证号
t.phone ,        -- 手机号
IFNULL(t.checkState,'0') as checkState,    -- 材料核对状态
t.exam_state as examState,    -- 考试安排
t.written_score as writtenScore,   -- 笔试成绩
t.active,
t.payComName,
sa.areaCode,
sa.id as approvalId,
sa.processInstanceId
FROM
sb_personcertificate t
INNER JOIN sys_approval sa ON t.id = sa.businessid and sa.businesstype='004' and sa.active =true
WHERE t.active = true
AND NOT EXISTS (SELECT 1   /*<!-- 排除历史导入的保安员 by llc 2018-08-21 -->*/
FROM security_certificate
WHERE active = 1
AND securitypersonid = t.id
AND isOldData = '1');








-- dt 20200918新增是否自然人
drop view if EXISTS v_person_certificate;
CREATE
VIEW `v_person_certificate` AS
SELECT
t.id,t.sex,t.brith,t.nation,t.politics,t.culture,t.zipcode,t.approvaldate,t.address,t.beforeName,
t.guaranteecar,t.familyPhone,t.militaryService,t.fingerprint,t.religion,t.nationality,t.birthPlace,
t.hight,t.booldType,t.healthStatus,t.nowAddress,t.baseRemack,t.operate_score,t.sum_score,t.fingercard_state,
t.szss,t.nocrime_photo,t.exam_address,
t.exam_start,
t.exam_start as examTime,
t.exam_end,
t.stamina_address,t.stamina_startDate,
t.stamina_endDate,t.signConfirm,t.payConfirm,t.isMakeUp,t.proState,t.makeUpstate,t.approvalperson,
t.approvalreason,t.applyforstate,
t.approvalstate as approvalState,   -- 报名审批状态
t.companyname as scName, -- 报名公司名称
t.name,  -- 姓名
t.naturalPer, -- 是否自然人
t.orgid, -- 机构id
IFNULL(t.censor_status,'001') as censorStatus, -- 背景筛查
t.trainOrgName,   -- 所属培训机构
date_format(t.create_time,'%Y-%m-%d')as createTime,    -- 报名日期
IFNULL(t.fee_state,'0') as feeState,      -- 缴费情况
IFNULL(t.test_state,'2') as testState,    -- 考试结果
t.Cardnumber as cardNumber,         -- 身份证号
t.phone ,        -- 手机号
IFNULL(t.checkState,'0') as checkState,    -- 材料核对状态
t.exam_state as examState,    -- 考试安排
t.written_score as writtenScore,   -- 笔试成绩
t.active,
t.payComName,
sa.areaCode,
sa.id as approvalId,
sa.processInstanceId
FROM
sb_personcertificate t
INNER JOIN sys_approval sa ON t.id = sa.businessid and sa.businesstype='004' and sa.active =true
WHERE t.active = true
AND NOT EXISTS (SELECT 1   /*<!-- 排除历史导入的保安员 by llc 2018-08-21 -->*/
FROM security_certificate
WHERE active = 1
AND securitypersonid = t.id
AND isOldData = '1');



#yyx 20200622
ALTER TABLE `sys_export_column`
ADD COLUMN `isNeedSelect`  varchar(10) NULL COMMENT '是否需要查询' ;


#yyx 20200618
ALTER TABLE `company_award`
ADD COLUMN `orgAreaCode`  varchar(10) NULL COMMENT '公安单位areaCode';
ALTER TABLE `company_complaint`
ADD COLUMN `orgAreaCode`  varchar(10) NULL COMMENT '公安单位areaCode';
ALTER TABLE `company_inspection`
ADD COLUMN `orgAreaCode`  varchar(10) NULL COMMENT '公安单位areaCode';
ALTER TABLE `company_punish`
ADD COLUMN `orgAreaCode`  varchar(10) NULL COMMENT '公安单位areaCode';
ALTER TABLE `person_award`
ADD COLUMN `orgAreaCode`  varchar(10) NULL COMMENT '公安单位areaCode';
ALTER TABLE `person_complaint`
ADD COLUMN `orgAreaCode`  varchar(10) NULL COMMENT '公安单位areaCode';
ALTER TABLE `person_punish`
ADD COLUMN `orgAreaCode`  varchar(10) NULL COMMENT '公安单位areaCode';