DROP TABLE IF EXISTS `person_black_list`;

CREATE TABLE `person_black_list` (
  `id` bigint(20) NOT NULL,
  `active` bit(1) NOT NULL COMMENT '是否有效',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` datetime NOT NULL COMMENT '修改时间',
  `update_by` varchar(50) NOT NULL COMMENT '修改者',
  `realName` varchar(50) DEFAULT NULL COMMENT '姓名',
  `phone` varchar(20) DEFAULT NULL COMMENT '手机',
  `idNum` varchar(50) DEFAULT NULL COMMENT '身份证号',
  `address` varchar(100) DEFAULT NULL COMMENT '现住址',
  `reason` varchar(300) DEFAULT NULL COMMENT '入单原因',
  `sex` varchar(5) DEFAULT NULL COMMENT '性别',
  `orgName` varchar(100) DEFAULT NULL COMMENT '登记机构名',
  `registrant` varchar(100) DEFAULT NULL COMMENT '登记人',
  `orgId` bigint(20) DEFAULT NULL COMMENT '登记机构id',
  `registerDate` varchar(20) DEFAULT NULL COMMENT '登记时间',
  `updateflag` bit(1) DEFAULT NULL COMMENT '更新标志位',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='保安员行业黑名单表';


ALTER TABLE `security_company`
ADD COLUMN `orgName`  varchar(100) NULL COMMENT '监管机构名' AFTER `operatorname`;
ALTER TABLE `security_company`
ADD COLUMN `orgId`  varchar(20) NULL COMMENT '监管机构id' AFTER `operatorname`;

ALTER TABLE `security_company`
ADD COLUMN `securityScopeType`  varchar(100) NULL COMMENT '服务范围' AFTER `operatorname`;

update security_company sc,
(select scs.company_id,GROUP_CONCAT(DISTINCT scs.scope_id) as scope from security_company_scope scs GROUP BY scs.company_id
)scs set sc.securityScopeType=scope
where sc.active=true and sc.id=scs.company_id ;


delete from `sys_dict` where `codeType` = 'sponsorlicenseType';
INSERT INTO `sys_dict` (`isActive`, `codeType`, `tableName`, `description`, `codeValue`, `label`, `seq`)
VALUES ('1', 'sponsorlicenseType', NULL, '保安公司类型', '01', '普通保安服务公司', '1'),
('1', 'sponsorlicenseType', NULL, '保安公司类型', '02', '武装押运保安服务公司', '2'),
('1', 'sponsorlicenseType', NULL, '保安公司类型', '03', '中外合资保安服务公司', '3');

/*对比/预警、问题人员、保安行业黑名单 权限调整*/
UPDATE `module` SET `p_id`='175' WHERE `code`='problemPerson' and moduletype='1';
UPDATE `module` SET `p_id`='175' WHERE `code`='personBlackListManager' and moduletype='1';

/*对比/预警、问题人员、保安行业黑名单 权限调整*/
INSERT INTO `module` (`id`, `native_code`, `code`, `name`, `url`, `seq`, `index`, `shortcut`, `class`, `action`, `active`, `create_time`, `update_time`, `update_by`, `moduletype`, `p_id`)
VALUES ('175', '3502', 'ContrastWarning', '对比/预警', '', '1', '\0', '\0', '', '', '
', now(), now(), 'system', '1', '122');

INSERT INTO`module` (`id`, `native_code`, `code`, `name`, `url`, `seq`, `index`, `shortcut`, `class`, `action`, `active`, `create_time`, `update_time`, `update_by`, `moduletype`, `p_id`)
VALUES ('176', '3502', 'personBlackListManager', '保安行业黑名单', '', '2', '\0', '\0', '', '', '
', now(), now(), 'system', '1', '175');


INSERT INTO`operation` (`native_code`, `code`, `name`, `module_id`, `active`, `create_time`, `update_time`, `update_by`)
VALUES ('3502', 'query', '查询', '176', '
', now(), now(), 'system'),
('3502', 'add', '新增', '176', '
', now(), now(), 'system');


INSERT INTO`permission` (`native_code`, `role_id`, `module_id`, `operation_id`, `roletype`)
select '45',pr.id,o.module_id,o.id,'2' from police_role pr,police p,operation o
where pr.active=true
and p.active=TRUE
and o.active=TRUE
and pr.orgid=p.id
and o.module_id=176;


##外网权限删除
DELETE from permission where id in (
select t.id from (select p.id from module m, config c,permission p where (m.p_id = 175 or m.id=175)
AND c.active = TRUE
AND c.`key` = 'police'
AND c.`value` = '0'
and p.module_id=m.id)t
);

##外网权限删除
DELETE from operation where id in (
select t.id from (
select p.id from module m, config c,operation p where (m.p_id = 175 or m.id=175)
AND c.active = TRUE
AND c.`key` = 'police'
AND c.`value` = '0'
and p.module_id=m.id)t
);

##外网权限删除
delete FROM module where id in (
select t.id from (
select m.id from module m, config c WHERE (m.p_id = 175 or m.id=175)
AND c.active = TRUE
AND c.`key` = 'police'
AND c.`value` = '0')t
);

/*对比/预警、问题人员、保安行业黑名单 权限调整 end*/


#保安员资格证管理
INSERT INTO`module` (`id`, `native_code`, `code`, `name`, `url`, `seq`, `index`, `shortcut`, `class`, `action`, `active`, `create_time`, `update_time`, `update_by`, `moduletype`, `p_id`)
VALUES ('177', '3502', 'SecurityPersonCertificatManagement', '保安员资格证管理', '', '2', '\0', '\0', '', '', '
', now(), now(), 'system', '1', '122');

update module set p_id=177 where `code`='personcertificate' and moduletype=1;
update module set p_id=177 where `code`='examinationarrange' and moduletype=1;
update module set p_id=177 where `code`='personmakeup' and moduletype=1;
update module set p_id=177 where `code`='personcertificatePrint' and moduletype=1;

INSERT INTO`permission` (`native_code`, `role_id`, `module_id`, `operation_id`, `roletype`)
select '45',pr.id,o.module_id,o.id,'2' from police_role pr,police p,operation o
where pr.active=true
and p.active=TRUE
and o.active=TRUE
and pr.orgid=p.id
and o.module_id=177;


update permission p1,operation o1
set p1.module_id=177,o1.module_id=177
where p1.operation_id=o1.id
and o1.active=true and `code`='personCertificate';


update permission p1,operation o1
set p1.module_id=177,o1.module_id=177
where p1.operation_id=o1.id
and o1.active=true and `code`='personCertificateStatistics';

#保安员资格证管理 end

##修正在职人数
update security_company sc,
(
SELECT
	sc.id,
	count(sp.id) as num
FROM
	security_company sc,
	security_person sp,
	security_company_person scp
WHERE
	sc.active = TRUE
AND scp.active = TRUE
AND sp.id = scp.person_id
AND scp.company_id = sc.id
GROUP BY
	sc.id
)t
set sc.security_amount=t.num
where t.id=sc.id;