ALTER TABLE `security_certificate`
ADD COLUMN `revokeDate`  varchar(20) NULL COMMENT '吊销日期' AFTER `rkkflag`;

ALTER TABLE `security_certificate`
ADD COLUMN `revokeOrg`  varchar(12) NULL COMMENT '吊销决定机构代码' AFTER `rkkflag`;

ALTER TABLE `security_certificate`
ADD COLUMN `revokeOrgName`  varchar(150) NULL COMMENT '吊销决定机构' AFTER `rkkflag`;

ALTER TABLE `security_certificate`
ADD COLUMN `revokeReason`  varchar(1000) NULL COMMENT '吊销原因' AFTER `rkkflag`;

ALTER TABLE `security_certificate`
ADD COLUMN `cancelOrg`  varchar(12) NULL COMMENT '撤销决定机构代码' AFTER `rkkflag`;

ALTER TABLE `security_certificate`
ADD COLUMN `cancelOrgName`  varchar(150) NULL COMMENT '撤销决定机构' AFTER `rkkflag`;

ALTER TABLE `security_certificate`
ADD COLUMN `cancelReason`  varchar(1000) NULL COMMENT '撤销原因' AFTER `rkkflag`;

ALTER TABLE `security_certificate`
ADD COLUMN `cancelDate`  varchar(20) NULL COMMENT '撤销日期' AFTER `rkkflag`;

ALTER TABLE `security_certificate`
ADD COLUMN `score`  varchar(5) NULL COMMENT '考试成绩' AFTER `rkkflag`;

ALTER TABLE `security_certificate`
ADD COLUMN `examDate`  varchar(20) NULL COMMENT '考试时间' AFTER `rkkflag`;

ALTER TABLE `security_certificate`
ADD COLUMN `state`  varchar(5) NULL COMMENT '证书状态 1正常2撤销3吊销' AFTER `rkkflag`;


ALTER TABLE `problemperson`
ADD COLUMN `comHandleDate`  datetime NULL COMMENT '企业处理时间' AFTER `handlerOrg`,
ADD COLUMN `handleDate`  datetime NULL COMMENT '公安机关处理时间' AFTER `comHandleDate`;

ALTER TABLE `security_certificate`
ADD COLUMN `electronCerUrl`  varchar(500) NULL COMMENT '证书地址';

delete  from `sys_dict` where codeType = 'state';
INSERT INTO `sys_dict` (`sysDictId`, `isActive`, `codeType`, `tableName`, `description`, `codeValue`, `label`, `seq`) VALUES (null, '1', 'state', NULL, '保安证状态', '1', '正常', '1');
INSERT INTO `sys_dict` (`sysDictId`, `isActive`, `codeType`, `tableName`, `description`, `codeValue`, `label`, `seq`) VALUES (null, '1', 'state', NULL, '保安证状态', '2', '撤消', '2');
INSERT INTO `sys_dict` (`sysDictId`, `isActive`, `codeType`, `tableName`, `description`, `codeValue`, `label`, `seq`) VALUES (null, '1', 'state', '', '保安证状态', '3', '吊销', '3');


update security_person s1,security_certificate s2
set s1.zgcredential=s2.zsbh,
s1.zgcertificatejgno=s2.fzjgbh,
s1.zgcertificatejgname=s2.fzjgmc,
s1.zgcertificatejgtime=DATE_FORMAT(s2.fzrq,'%Y-%m-%d') 
where 
s1.incumbencyStatus=TRUE
AND s1.zgcredential is null
and s2.isOldData='1'
AND s1.id_number=s2.idnum
and s2.active=true;



update  problemperson   set problemtype = REPLACE(problemtype,',前科','');
update  problemperson   set problemtype = REPLACE(problemtype,'前科','');
delete from sys_dict where codeType ='inform' and codeValue ='4'