ALTER TABLE `sb_personcertificate_temp`
ADD COLUMN `cityOrgId`  int(11) NULL COMMENT '市机构id' AFTER `orgid`,
ADD COLUMN `districtOrgId`  int(11) NULL COMMENT '区县机构id' AFTER `cityOrgId`;

ALTER TABLE `sb_personcertificate`
ADD COLUMN `cityOrgId`  int(11) NULL COMMENT '市机构id' AFTER `orgid`,
ADD COLUMN `districtOrgId`  int(11) NULL COMMENT '区县机构id' AFTER `cityOrgId`;

ALTER TABLE `sb_armtrainorg`
ADD COLUMN `cityOrgId`  int(11) NULL COMMENT '市机构id' AFTER `orgid`,
ADD COLUMN `districtOrgId`  int(11) NULL COMMENT '区县机构id' AFTER `cityOrgId`;

ALTER TABLE `sb_subsidiary`
ADD COLUMN `cityOrgId`  int(11) NULL COMMENT '市机构id' AFTER `orgid`,
ADD COLUMN `districtOrgId`  int(11) NULL COMMENT '区县机构id' AFTER `cityOrgId`;

ALTER TABLE `sb_sponsorlicense`
ADD COLUMN `cityOrgId`  int(11) NULL COMMENT '市机构id' AFTER `orgid`,
ADD COLUMN `districtOrgId`  int(11) NULL COMMENT '区县机构id' AFTER `cityOrgId`;

ALTER TABLE `sb_confesscompany`
ADD COLUMN `cityOrgId`  int(11) NULL COMMENT '市机构id' AFTER `orgid`,
ADD COLUMN `districtOrgId`  int(11) NULL COMMENT '区县机构id' AFTER `cityOrgId`;

ALTER TABLE `sb_crosscompany`
ADD COLUMN `cityOrgId`  int(11) NULL COMMENT '市机构id' AFTER `orgid`,
ADD COLUMN `districtOrgId`  int(11) NULL COMMENT '区县机构id' AFTER `cityOrgId`;

ALTER TABLE `sb_confesscompanyundo`
ADD COLUMN `cityOrgId`  int(11) NULL COMMENT '市机构id' AFTER `orgid`,
ADD COLUMN `districtOrgId`  int(11) NULL COMMENT '区县机构id' AFTER `cityOrgId`;

ALTER TABLE `sb_legalchange`
ADD COLUMN `cityOrgId`  int(11) NULL COMMENT '市机构id' AFTER `orgid`,
ADD COLUMN `districtOrgId`  int(11) NULL COMMENT '区县机构id' AFTER `cityOrgId`;

ALTER TABLE `yearreportdata`
ADD COLUMN `cityOrgId`  int(11) NULL COMMENT '市机构id' AFTER `orgid`,
ADD COLUMN `districtOrgId`  int(11) NULL COMMENT '区县机构id' AFTER `cityOrgId`;

ALTER TABLE `sb_traincompanyapply`
ADD COLUMN `cityOrgId`  int(11) NULL COMMENT '市机构id' AFTER `orgid`,
ADD COLUMN `districtOrgId`  int(11) NULL COMMENT '区县机构id' AFTER `cityOrgId`;

ALTER TABLE `security_certificate`
ADD COLUMN `receiveState`  varchar(3) NULL COMMENT '领取状态 0-未领取1-已领取' AFTER `updateflag`;

ALTER TABLE `sb_traincompanyapply`
ADD COLUMN `permitVideoPath` varchar(255) NULL COMMENT '签字承诺书视频路径' AFTER `permitPath`;



/*
Navicat MySQL Data Transfer

Source Server         : 百保盾内网库
Source Server Version : 50720
Source Host           : 183.250.166.207:3345
Source Database       : gxbbd

Target Server Type    : MYSQL
Target Server Version : 50720
File Encoding         : 65001

Date: 2019-03-27 14:17:28
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- 保安师库
-- Table structure for security_guard
-- ----------------------------
DROP TABLE IF EXISTS `security_guard`;
CREATE TABLE `security_guard` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `active` bit(1) NOT NULL COMMENT '是否有效',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `update_by` varchar(50) DEFAULT NULL COMMENT '更新人',
  `id_number` varchar(50) DEFAULT NULL COMMENT '身份证号',
  `name` varchar(50) DEFAULT NULL COMMENT '姓名',
  `sex` varchar(5) DEFAULT NULL COMMENT '性别',
  `profession` varchar(20) DEFAULT NULL COMMENT '职业',
  `profession_level` int(50) DEFAULT NULL COMMENT '职业水平',
  `edu` int(10) DEFAULT '4' COMMENT '学历',
  `phone` varchar(20) DEFAULT NULL COMMENT '联系电话',
  `serviceStartTime` varchar(16) DEFAULT NULL COMMENT '服务开始时间',
  `credential` varchar(50) DEFAULT NULL COMMENT '证书编号',
  `certificatejgtime` varchar(16) DEFAULT NULL COMMENT '颁证日期',
  `security_company` varchar(50) DEFAULT NULL COMMENT '服务保安公司',
  `workPlace` varchar(50) DEFAULT NULL COMMENT '工作地点',
  `ticketNum` varchar(50) DEFAULT NULL COMMENT '准考证号',
  `written_score` varchar(6) DEFAULT NULL COMMENT '笔试成绩',
  `operate_score` varchar(6) DEFAULT NULL COMMENT '实操成绩',
  `sum_score` varchar(6) DEFAULT NULL COMMENT '综合成绩',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_id` (`id`) USING BTREE,
  KEY `security_person_id_number` (`id_number`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=556220753536614402 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;


/*
Navicat MySQL Data Transfer

Source Server         : 百保盾内网库
Source Server Version : 50720
Source Host           : 183.250.166.207:3345
Source Database       : gxbbd

Target Server Type    : MYSQL
Target Server Version : 50720
File Encoding         : 65001

Date: 2019-03-27 14:17:58
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- 批量导入保安师库临时表
-- Table structure for temp_batch_security_guard_import
-- ----------------------------
DROP TABLE IF EXISTS `temp_batch_security_guard_import`;
CREATE TABLE `temp_batch_security_guard_import` (
  `tempBatchGuardImportId` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '离职批次ID',
  `createTime` datetime NOT NULL COMMENT '创建时间',
  `id_number` varchar(50) DEFAULT NULL COMMENT '身份证号',
  `name` varchar(100) DEFAULT NULL COMMENT '姓名',
  `sex` varchar(5) DEFAULT NULL COMMENT '性别',
  `profession` varchar(20) DEFAULT NULL COMMENT '职业',
  `profession_level` varchar(10) DEFAULT NULL COMMENT '职业水平',
  `edu` varchar(10) DEFAULT '4' COMMENT '学历',
  `phone` varchar(20) DEFAULT NULL COMMENT '联系电话',
  `credential` varchar(50) DEFAULT NULL COMMENT '证书编号',
  `certificatejgtime` varchar(16) DEFAULT NULL COMMENT '颁证日期',
  `security_company` varchar(50) DEFAULT NULL COMMENT '服务保安公司',
  `isSuccess` varchar(1) DEFAULT '1' COMMENT '成功标志(0:失败, 1:成功)',
  `importBatchId` bigint(20) NOT NULL COMMENT '导入批次ID',
  `errorMsg` varchar(1000) DEFAULT NULL COMMENT '错误信息',
  PRIMARY KEY (`tempBatchGuardImportId`),
  KEY `index_temp_batch_per_import_importBatchId` (`importBatchId`)
) ENGINE=InnoDB AUTO_INCREMENT=1030 DEFAULT CHARSET=utf8 COMMENT='批量人员导入临时表';

INSERT INTO `file_temp` (`id`, `createTime`, `createBy`, `updateTime`, `updateBy`, `fileTempName`, `fileTempType`, `title`, `path`, `description`) VALUES ('3', '2019-03-25 17:52:39', '', '2019-03-25 17:52:44', '', 'securityGuardTemp.xls', 'xls', '历史保安师导入模板', 'C:\\data\\ars\\temp\\securityGuardTemp.xls', '历史保安师导入模板');
INSERT INTO `file_temp` (`id`, `createTime`, `createBy`, `updateTime`, `updateBy`, `fileTempName`, `fileTempType`, `title`, `path`, `description`) VALUES ('4', '2019-03-25 17:52:39', '', '2019-03-25 17:52:44', '', 'securityGuardFailTemp.xls', 'xls', '历史保安师失败导出模板', 'C:\\data\\ars\\temp\\securityGuardFailTemp.xls', '历史保安师失败导出模板');

ALTER TABLE `security_guard`
ADD COLUMN `updateflag`  bit NULL DEFAULT b'0'  COMMENT '新增，修改标志' AFTER `sum_score`;


ALTER TABLE `security_guard`
MODIFY COLUMN `written_score`  varchar(6) NULL DEFAULT NULL COMMENT '笔试成绩' AFTER `ticketNum`,
MODIFY COLUMN `operate_score`  varchar(6) NULL DEFAULT NULL COMMENT '实操成绩' AFTER `written_score`,
MODIFY COLUMN `sum_score`  varchar(6) NULL DEFAULT NULL COMMENT '综合成绩' AFTER `operate_score`;

ALTER TABLE `security_guard`
MODIFY COLUMN `profession_level`  varchar(10) NULL DEFAULT NULL COMMENT '职业水平' AFTER `profession`,
MODIFY COLUMN `edu`  varchar(10) NULL DEFAULT '4' COMMENT '学历' AFTER `profession_level`;

ALTER TABLE `operation`
MODIFY COLUMN `code`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL AFTER `native_code`;


delete from sys_dict where codeType='profession';
delete from sys_dict where codeType='professionLevel';
INSERT INTO `sys_dict` (`isActive`, `codeType`, `tableName`, `description`, `codeValue`, `label`, `seq`)
VALUES ('1', 'professionLevel', NULL, '保安师级别', '1', '一级', '1'),
('1', 'professionLevel', NULL, '保安师级别', '2', '二级', '2'),
('1', 'profession', NULL, '保安师职业', '1', '保安员', '1'),
('1', 'profession', NULL, '保安师职业', '2', '保安师', '2');



#民族
update nation set `name`=REPLACE(`name`,'\r\n','');
delete from sys_dict where codeType='nation';
insert into sys_dict(isActive,codeType,description,seq,codeValue,label)
SELECT true,'nation','民族',@rownum := @rownum+1 AS rownum,nation.`code`,nation.`name`
FROM (SELECT @rownum:=0) r,nation;

update sys_dict set label=REPLACE(label,'\r\n','') where codeType='nation';


update sb_personcertificate set Nation=CONCAT('0',Nation) where active=true and Nation is not null
and LENGTH(Nation)=1;



delete from `config` where `key`='ZAURL_BASE_BJ';
INSERT INTO `config` (`key`, `value`, `name`, `active`, `create_time`, `update_time`, `update_by`)
VALUES ('ZAURL_BASE_BJ', 'http://10.8.1.28/hlpda/pda/reponse/CheckPersonBasicInfoAction.do', '北京人口库地址', true, now(), now(), 'system');

delete from `config` where `key`='ZAURL_BJ';
INSERT INTO `config` (`key`, `value`, `name`, `active`, `create_time`, `update_time`, `update_by`)
VALUES ('ZAURL_BJ', 'http://14.28.2.32:8080/verify', '北京核查库地址', true, now(), now(), 'system');


DELETE from `sys_dict` where `codeType` = 'politics';
INSERT INTO `sys_dict` (`isActive`, `codeType`, `tableName`, `description`, `codeValue`, `label`, `seq`)
VALUES ('1', 'politics', NULL, '政治面貌', '01', '中共党员', '1'),
('1', 'politics', NULL, '政治面貌', '02', '中共预备党员', '2'),
('1', 'politics', NULL, '政治面貌', '03', '共青团员', '3'),
('1', 'politics', NULL, '政治面貌', '04', '民革会员', '4'),
('1', 'politics', NULL, '政治面貌', '05', '民盟盟员', '5'),
('1', 'politics', NULL, '政治面貌', '06', '民建会员', '6'),
('1', 'politics', NULL, '政治面貌', '07', '民进会员', '7'),
('1', 'politics', NULL, '政治面貌', '08', '农工党党员', '8'),
('1', 'politics', NULL, '政治面貌', '09', '致公党党员', '9'),
('1', 'politics', NULL, '政治面貌', '10', '九三学社社员', '10'),
('1', 'politics', NULL, '政治面貌', '11', '台盟盟员', '11'),
('1', 'politics', NULL, '政治面貌', '12', '无党派民主人士', '12'),
('1', 'politics', NULL, '政治面貌', '13', '群众', '13');
-- 1	党员
-- 2	团员
-- 3	群众
update security_person set politics='01' where politics='1';
update security_person set politics='03' where politics='2';
update security_person set politics='13' where politics='3';

update sb_personcertificate set politics='01' where politics='1';
update sb_personcertificate set politics='03' where politics='2';
update sb_personcertificate set politics='13' where politics='3';


-- 系统接口日志
DROP TABLE IF EXISTS `sys_interface_log`;
CREATE TABLE `sys_interface_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `active` bit(1) DEFAULT NULL COMMENT '是否可用',
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  `update_by` varchar(50) DEFAULT NULL,
  `parameters` longtext COMMENT '接口请求数据',
  `result` longtext COMMENT '接口返回结果',
  `url` varchar(500) DEFAULT NULL COMMENT '方法url',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=390 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='系统接口日志';



ALTER TABLE `invoice`
MODIFY COLUMN `id`  bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键' FIRST ;

ALTER TABLE `person_experience`
MODIFY COLUMN `company_id`  bigint(20) NULL DEFAULT NULL COMMENT '所属公司id' AFTER `idNum`;



#旧数据处理
update sb_personcertificate sp,police p1
set sp.cityOrgId= p1.id
where sp.active=true
and sp.city= p1.areacode
and p1.orgtype=2;

update sb_armtrainorg sp,police p1
set sp.cityOrgId= p1.id
where sp.active=true
and sp.city= p1.areacode
and p1.orgtype=2;

update sb_subsidiary sp,police p1
set sp.cityOrgId= p1.id
where sp.active=true
and sp.city= p1.areacode
and p1.orgtype=2;

update sb_sponsorlicense sp,police p1
set sp.cityOrgId= p1.id
where sp.active=true
and sp.city= p1.areacode
and p1.orgtype=2;

update sb_confesscompany sp,police p1
set sp.cityOrgId= p1.id
where sp.active=true
and sp.city= p1.areacode
and p1.orgtype=2;

update sb_crosscompany sp,police p1
set sp.cityOrgId= p1.id
where sp.active=true
and sp.city= p1.areacode
and p1.orgtype=2;

update sb_confesscompanyundo sp,police p1
set sp.cityOrgId= p1.id
where sp.active=true
and sp.city= p1.areacode
and p1.orgtype=2;

update sb_legalchange sp,police p1
set sp.cityOrgId= p1.id
where sp.active=true
and sp.city= p1.areacode
and p1.orgtype=2;

update yearreportdata sp,police p1
set sp.cityOrgId= p1.id
where sp.active=true
and sp.city= p1.areacode
and p1.orgtype=2;

update sb_traincompanyapply sp,police p1
set sp.cityOrgId= p1.id
where sp.active=true
and sp.city= p1.areacode
and p1.orgtype=2;

update sb_personcertificate sp,police p2
set sp.districtOrgId=p2.id
where sp.active=true
and sp.district= p2.areacode
and p2.orgtype=3;

update sb_armtrainorg sp,police p2
set sp.districtOrgId=p2.id
where sp.active=true
and sp.district= p2.areacode
and p2.orgtype=3;

update sb_subsidiary sp,police p2
set sp.districtOrgId=p2.id
where sp.active=true
and sp.district= p2.areacode
and p2.orgtype=3;

update sb_sponsorlicense sp,police p2
set sp.districtOrgId=p2.id
where sp.active=true
and sp.district= p2.areacode
and p2.orgtype=3;

update sb_confesscompany sp,police p2
set sp.districtOrgId=p2.id
where sp.active=true
and sp.district= p2.areacode
and p2.orgtype=3;

update sb_crosscompany sp,police p2
set sp.districtOrgId=p2.id
where sp.active=true
and sp.district= p2.areacode
and p2.orgtype=3;

update sb_confesscompanyundo sp,police p2
set sp.districtOrgId=p2.id
where sp.active=true
and sp.district= p2.areacode
and p2.orgtype=3;

update sb_legalchange sp,police p2
set sp.districtOrgId=p2.id
where sp.active=true
and sp.district= p2.areacode
and p2.orgtype=3;

update yearreportdata sp,police p2
set sp.districtOrgId=p2.id
where sp.active=true
and sp.district= p2.areacode
and p2.orgtype=3;

update sb_traincompanyapply sp,police p2
set sp.districtOrgId=p2.id
where sp.active=true
and sp.district= p2.areacode
and p2.orgtype=3;

#血型分类
delete from sys_dict where codeType='bloodType';
INSERT INTO `sys_dict` ( `isActive`, `codeType`, `tableName`, `description`, `codeValue`, `label`, `seq`)
VALUES ('1', 'bloodType', NULL, '血型分类', 'A', 'A', '1'),
('1', 'bloodType', NULL, '血型分类', 'B', 'B', '2'),
('1', 'bloodType', NULL, '血型分类', 'AB', 'AB', '3'),
('1', 'bloodType', NULL, '血型分类', 'O', 'O', '4'),
('1', 'bloodType', NULL, '血型分类', 'Rh阳', 'Rh阳', '5'),
('1', 'bloodType', NULL, '血型分类', 'Rh阴', 'Rh阴', '6');