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

 #2020-09-14 security_certificate   修改isDraw 类型   0 未推送 1推送 2 未授权
ALTER TABLE security_certificate MODIFY isDraw VARCHAR(5)  NULL DEFAULT '2' COMMENT '是否抽取';

#2020-09-14 等级证调整
ALTER TABLE `grade_certificate`
MODIFY COLUMN `update_by`  varchar(50)  NULL COMMENT '更新人' AFTER `update_time`;

ALTER TABLE `grade_certificate`
ADD COLUMN `personGradeId`  bigint(20) NULL COMMENT '等级证报名id' AFTER `pushDate`,
ADD COLUMN `trainId`  bigint(20) NULL COMMENT '培训机构id' AFTER `personGradeId`,
ADD COLUMN `profession`  varchar(200) NULL COMMENT '职业' AFTER `trainId`,
ADD COLUMN `gender`  varchar(5) NULL COMMENT '性别' AFTER `profession`,
ADD COLUMN `trainStartTime`  varchar(50) NULL COMMENT '培训开始时间' AFTER `gender`,
ADD COLUMN `trainEndTime`  varchar(50) NULL COMMENT '培训结束时间' AFTER `trainStartTime`;

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

#2020-09-22 记录登录 ip
ALTER TABLE `loginlog`
ADD COLUMN `ipAddress`  varchar(50) NULL AFTER `username`;

#2020-09-24 cer_learn_record  增加 trainId培训学校idtrainName培训学校名称
ALTER TABLE cer_learn_record  ADD  trainId bigint(20)   NULL COMMENT '培训学校id';
ALTER TABLE cer_learn_record  ADD  trainName varchar(200)   NULL  COMMENT '培训学校名称';

#2020-09-21 北京三证数据汇聚 对接网安
DROP PROCEDURE IF EXISTS `prc_createBAFWGS`;

CREATE PROCEDURE `prc_createBAFWGS`()
BEGIN
	-- 吉天祥 每月的25号咱们这边把信息打包发过来。这回的话，上次报应该是6月15日的事了，所以您这边麻烦把之前的都一起打包过来
	-- 数据分三项：保安员培训许可证，中华人民共和国保安员证，保安服务许可证
	-- 截止到8月25日的就可以，8月25日之后的，我们9月25日再报。上报方式的话，咱们这边数据量比较大，所以您用ftp给我发过来
	-- 地址：14.21.3.23
	-- 用户名：wzs
	-- 密码：123
-- 14.21.3.23下的 保安系统电子照 里面
set @today = NOW();

update company_certificate t1,
	security_company t2
set t2.pzwh = t1.pzwh
WHERE
	t1.pzwh IS NOT NULL
AND t1.gsmc = t2.`name`
AND t1.active = TRUE
AND t2.active = TRUE
AND t2.pzwh IS NULL;

-- update gtx_bayzgz set push=1,update_time=() where push=0;
-- update gtx_bafwgs set push=1,update_time=() where push=0;
-- update gtx_bapxdw set push=1,update_time=() where push=0;

insert into gtx_bafwgs
SELECT '保安服务许可证', CONCAT('京公保服',REPLACE(REPLACE(public_security_license_number,'京公保服',''),'号','')), `name`, '70', social_code, '北京市公安局', '1111000000002888XF', '110100', cast(give_license_date as date), '', REPLACE(REPLACE(public_security_license_number,'京公保服',''),'号',''), address, legal_person, securityScopeType, registered_money, pzwh, '0',@today,@today
from security_company where active=true and institutionType='0102' and LENGTH(REPLACE(REPLACE(public_security_license_number,'京公保服',''),'号',''))=8
and not EXISTS(
select 1 from gtx_bafwgs where CYRMC=`name`
);


update gtx_bafwgs t1,sys_dict t2
set `FWFW`=REPLACE(`FWFW`,'1',t2.label)
where push=0 and t2.codeType='securityScopeType' and t2.codeValue='1';


update gtx_bafwgs t1,sys_dict t2
set `FWFW`=REPLACE(`FWFW`,'2',t2.label)
where push=0 and t2.codeType='securityScopeType' and t2.codeValue='2';


update gtx_bafwgs t1,sys_dict t2
set `FWFW`=REPLACE(`FWFW`,'3',t2.label)
where push=0 and t2.codeType='securityScopeType' and t2.codeValue='3';

update gtx_bafwgs t1,sys_dict t2
set `FWFW`=REPLACE(`FWFW`,'4',t2.label)
where push=0 and t2.codeType='securityScopeType' and t2.codeValue='4';

update gtx_bafwgs t1,sys_dict t2
set `FWFW`=REPLACE(`FWFW`,'5',t2.label)
where push=0 and t2.codeType='securityScopeType' and t2.codeValue='5';

update gtx_bafwgs t1,sys_dict t2
set `FWFW`=REPLACE(`FWFW`,'6',t2.label)
where push=0 and t2.codeType='securityScopeType' and t2.codeValue='6';

update gtx_bafwgs t1,sys_dict t2
set `FWFW`=REPLACE(`FWFW`,'7',t2.label)
where push=0 and t2.codeType='securityScopeType' and t2.codeValue='7';

update gtx_bafwgs t1,sys_dict t2
set `FWFW`=REPLACE(`FWFW`,'8',t2.label)
where push=0 and t2.codeType='securityScopeType' and t2.codeValue='8';

update gtx_bafwgs t1,sys_dict t2
set `FWFW`=REPLACE(`FWFW`,'9',t2.label)
where push=0 and t2.codeType='securityScopeType' and t2.codeValue='9';


SELECT * from gtx_bafwgs where push=0;

END;


DROP PROCEDURE IF EXISTS `prc_createBAPXDW`;

CREATE PROCEDURE `prc_createBAPXDW`()
BEGIN
	-- 吉天祥 每月的25号咱们这边把信息打包发过来。这回的话，上次报应该是6月15日的事了，所以您这边麻烦把之前的都一起打包过来
	-- 数据分三项：保安员培训许可证，中华人民共和国保安员证，保安服务许可证
	-- 截止到8月25日的就可以，8月25日之后的，我们9月25日再报。上报方式的话，咱们这边数据量比较大，所以您用ftp给我发过来
	-- 地址：14.21.3.23
	-- 用户名：wzs
	-- 密码：123
-- 14.21.3.23下的 保安系统电子照 里面
set @today = NOW();


update train_certificate t1,
	security_company t2
set t2.pzwh = t1.pzwh
WHERE
	t1.pzwh IS NOT NULL
AND t1.gsmc = t2.`name`
AND t1.active = TRUE
AND t2.active = TRUE
AND t2.pzwh IS NULL;

insert into gtx_bapxdw
SELECT '保安培训许可证', CONCAT('京公保培',REPLACE(REPLACE(public_security_license_number,'京公保培',''),'号','')), `name`, '70', social_code, '北京市公安局', '1111000000002888XF', '110100', cast(give_license_date as date), '', REPLACE(REPLACE(public_security_license_number,'京公保培',''),'号',''), legal_person, trainContent, pzwh, address, '0',@today,@today
from security_company where active=true and institutionType='0103' and LENGTH(REPLACE(REPLACE(public_security_license_number,'京公保培',''),'号',''))=8
and not EXISTS(
select 1 from gtx_bapxdw where CYRMC=`name`
);

SELECT * from gtx_bapxdw where push=0;

END;

DROP PROCEDURE IF EXISTS `prc_createBAYZGZ`;

CREATE PROCEDURE `prc_createBAYZGZ`()
BEGIN
	-- 吉天祥 每月的25号咱们这边把信息打包发过来。这回的话，上次报应该是6月15日的事了，所以您这边麻烦把之前的都一起打包过来
	-- 数据分三项：保安员培训许可证，中华人民共和国保安员证，保安服务许可证
	-- 截止到8月25日的就可以，8月25日之后的，我们9月25日再报。上报方式的话，咱们这边数据量比较大，所以您用ftp给我发过来
	-- 地址：14.21.3.23
	-- 用户名：wzs
	-- 密码：123
-- 14.21.3.23下的 保安系统电子照 里面
set @today = NOW();

	INSERT into gtx_bayzgz (`ZZMC`, `ZZHM`, `CYRMC`, `CYRSFZJLX`, `CYRSFZJHM`, `FZJGMC`, `FZJGZZJGDM`, `FZJGSSXZQHDM`, `FZRQ`, `YXQJSRQ`, `MZ`, `CSRQ`, `ZZ`,push, `create_time`, `update_time`)
	SELECT DISTINCT '保安员资格证', t1.zsbh, t1.xm, '10', t1.idnum, '北京市公安局', '1111000000002888XF', '110100', cast(t1.fzrq as date), '', ifnull(t1.nation,'01'), cast(substring(t1.idnum,7,8) as date), t1.address,0,@today,@today
	from security_certificate t1
	where
	ifnull(t1.isOldData,0)=0
	and t1.active=true and ifnull(t1.state,1)=1
	and not EXISTS(
	select 1 from gtx_bayzgz t2 where t2.CYRSFZJHM = t1.idnum
	) limit 200;

	update gtx_bayzgz t1,sys_dict t2
	set t1.MZ = t2.label
	where t1.MZ = t2.codeValue
	and t1.push='0' and t2.codeType='nation';

  select * from gtx_bayzgz where push = '0' limit 200;
END;

#2020-09-21 end


drop view if EXISTS v_grade_Certificate;
create view v_grade_Certificate as
SELECT
	t1.id,
	t1.active,
	t1.create_time,
	t1.update_time,
	t1.update_by,
	t1.personGradeId,
	t1.trainId,
	t1.profession,
	t1.gender,
	t1.`name`,
	t1.idnum,
	t1.trainTime,
	t1.appraisalTime,
	t1.appraisalGrade,
	t1.certificateNo,
	t1.companyName,
	t1.companyId,
	t1.personId,
	t1.companyCode,
	t1.trainOrgName,
	t1.securityCertificateNo,
	t1.securityCertificateId,
	t1.uploadCompanyName,
	t1.trainType,
	t1.userId,
	t1.push,
	t1.pushDate,
	ifnull(t2.incumbencyStatus,0) as jobStatus
FROM
	Grade_Certificate t1
LEFT JOIN security_person t2 on t1.idnum=t2.id_number and t2.active=true
LEFT JOIN security_company_person t3 on t2.id = t3.person_id and t3.active=TRUE;


 #2020-09-16 新增是否自然人
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');