delete from approve_temp where tempCode='activitiGradeSubsidyapprovalProcess-110100';
INSERT INTO `approve_temp` (`active`, `create_time`, `update_time`, `update_by`, `tempName`, `tempCode`, `iconUrl`, `approveType`, `effectTime`, `failureTime`, `approveSetStatus`, `noticeTemp`, `repulseExplain`, `seq`, `approveGroupId`, `policeId`, `parentPoliceId`, `parentTempId`)
VALUES (true,now(),now(), 'system', '保安员等级证补贴管理', 'activitiGradeSubsidyapprovalProcess-110100', '', '', now(), now(), '0', NULL, NULL, '15', '100002', '1', NULL, '1');


INSERT INTO approve_temp_link_rela (  update_time, update_by, create_time, active, updateflag, approveLinkId, approveTempId, seq, createType )
select now(),'system',now(), true, true, 1, id, 1, 0
from approve_temp where tempCode ='activitiGradeSubsidyapprovalProcess-110100';

INSERT INTO `approve_link_level` ( `active`, `create_time`, `update_time`, `update_by`, `approveLevel`, `parentId`, `tempLinkRelaId`, `updateflag`)
select true,now(),now(), NULL, '2', NULL, id, '\0'
from approve_temp_link_rela where approveTempId in (
select id from approve_temp where tempCode  ='activitiGradeSubsidyapprovalProcess-110100'
);

INSERT INTO approve_area_process (
	active,
	create_time,
	update_time,
	approveSetStatus,
	regionLevel,
	regionId,
	approveLinkLevelId,
	policeCode,
	policeId,
	updateflag,
	path
)
SELECT
	FALSE,
	SYSDATE(),
	SYSDATE(),
	0,
	t1.orgtype,
	t1.areacode,
	t2.id,
	t1.areacode,
	t1.id,
	TRUE,
	t1.path
FROM
	police t1,
	approve_link_level t2
WHERE
	tempLinkRelaId IN (
		SELECT
			id
		FROM
			approve_temp_link_rela
		WHERE
			approveTempId IN (
				SELECT
					id
				FROM
					approve_temp
				WHERE
					tempCode  ='activitiGradeSubsidyapprovalProcess-110100'
			)
	)
AND t1.areacode='110100'
AND t1.orgtype IN ('2')
AND t1.ACTIVE = TRUE
AND t2.ACTIVE = TRUE
AND t1.id NOT IN (
	SELECT
		policeId
	FROM
		approve_area_process
	WHERE
		regionLevel IN ('2')
	AND approveLinkLevelId = t2.id
	AND regionId='110100'
);


delete from sys_dict where codeType ='businessType' and codeValue='011';
INSERT INTO `sys_dict` ( `isActive`, `updateTime`, `updateflag`, `codeType`, `tableName`, `description`, `codeValue`, `label`, `seq`)
VALUES ( '1',NOW(),true, 'businessType', NULL, '业务类型', '011', '等级证补贴审批', '11');


delete from sys_dict where codeType ='businessType' and codeValue='012';
INSERT INTO `sys_dict` ( `isActive`, `updateTime`, `updateflag`, `codeType`, `tableName`, `description`, `codeValue`, `label`, `seq`)
VALUES ( '1',NOW(),true, 'businessType', NULL, '业务类型', '012', '资格证补贴审批', '12');

delete from sys_dict where codeType ='subsidyCerType' ;
INSERT INTO `sys_dict` ( `isActive`, `updateTime`, `updateflag`, `codeType`, `tableName`, `description`, `codeValue`, `label`, `seq`) VALUES ('1', '2020-06-01 11:20:12', true, 'subsidyCerType', NULL, '补贴证书类型', '01', '等级证补贴', '1');
INSERT INTO `sys_dict` ( `isActive`, `updateTime`, `updateflag`, `codeType`, `tableName`, `description`, `codeValue`, `label`, `seq`) VALUES ('1', '2020-06-01 11:20:37', true, 'subsidyCerType', NULL, '补贴证书类型', '02', '资格证补贴', '2');


delete from sys_dict where codeType ='compareStatus' ;
delete from sys_dict where codeType ='cerCompareStatus' ;
delete from sys_dict where codeType ='exportType' ;
delete from sys_dict where codeType ='subsidyBusinessType' ;
delete from sys_dict where codeType ='comparisonBusinessType' ;
INSERT INTO `sys_dict` ( `isActive`, `updateTime`, `updateflag`, `codeType`, `tableName`, `description`, `codeValue`, `label`, `seq`)
values
('1', '2020-05-19 10:39:54', '1', 'compareStatus', NULL, '补贴比对状态', '04', '可修改', '4'),
('1', '2020-05-18 09:50:50', '1', 'compareStatus', NULL, '补贴比对状态', '03', '不可申请', '3'),
('1', '2020-05-18 09:50:17', '1', 'compareStatus', NULL, '补贴比对状态', '02', '可申请', '2'),
('1', '2020-05-18 09:49:24', '1', 'compareStatus', NULL, '补贴比对状态', '01', '待比对', '1'),
('1', '2020-05-18 09:50:50', '1', 'cerCompareStatus', NULL, '补贴比对状态', '03', '不可申请', '3'),
('1', '2020-05-18 09:50:17', '1', 'cerCompareStatus', NULL, '补贴比对状态', '02', '可申请', '2'),
('1', '2020-05-18 09:49:24', '1', 'cerCompareStatus', NULL, '补贴比对状态', '01', '待比对', '1'),
('1', '2020-05-14 17:19:34', '1', 'subsidyBusinessType', NULL, '补贴业务类型', '005', '鉴定补贴', '4'),
('1', '2020-05-14 17:19:34', '1', 'subsidyBusinessType', NULL, '补贴业务类型', '004', '资格证补贴', '4'),
('1', '2020-05-14 17:19:03', '1', 'subsidyBusinessType', NULL, '补贴业务类型', '003', '鉴定补考补贴', '3'),
('1', '2020-05-14 17:18:38', '1', 'subsidyBusinessType', NULL, '补贴业务类型', '001', '结业证补贴', '1'),
('1', '2020-05-14 17:18:23', '1', 'subsidyBusinessType', NULL, '补贴业务类型', '002', '等级证补贴', '2'),
('1', '2020-05-18 16:27:20', '1', 'comparisonBusinessType', NULL, '比对业务类型', '03', '资格证比对', '3'),
('1', '2020-05-18 16:26:49', '1', 'comparisonBusinessType', NULL, '比对业务类型', '02', '等级证补贴比对', '2'),
('1', '2020-05-18 16:25:56', '1', 'comparisonBusinessType', NULL, '比对业务类型', '01', '等级证报名比对', '1'),
('1', '2020-06-05 16:27:20', '1', 'exportType', NULL, '通用导出类型', '03', '报名比对导出', '3'),
('1', '2020-06-05 16:26:49', '1', 'exportType', NULL, '通用导出类型', '02', '补贴比对导出', '2'),
('1', '2020-06-05 16:25:56', '1', 'exportType', NULL, '通用导出类型', '01', '花名册导出', '1');


INSERT INTO `file_temp` (`createTime`, `createBy`, `updateTime`, `updateBy`, `fileTempName`, `fileTempType`, `title`, `path`, `description`)
VALUES ('2020-05-20 15:38:00', NULL, '2020-05-20 15:38:06', NULL, 'importRosterFailTemp.xls', 'xlx', '比对结果导入失败', 'C:\\data\\ars\\temp\\importRosterFailTemp.xls', '比对结果导入失败');


###北京环境执行
INSERT INTO `train_subsidy_config` (`id`, `active`, `updateflag`, `create_time`, `update_time`, `update_by`, `userid`, `trainId`, `trainName`, `shortName`, `batchNo`)
VALUES ('592668573445390337', true, true, now(), now(), 'bj_super', NULL, '592668573445390337', '北京政博安保安职业技能培训学校', '政博安学校', '0'),
('591283927860117505', true, true, now(), now(), 'bj_super', NULL, '591283927860117505', '北京市保安职业技能培训学校', '保总学校', '0'),
('591322729324478465', true, true, now(), now(), 'bj_super', NULL, '591322729324478465', '北京振远护卫职业技能培训学校', '振远护卫学校', '0'),
('592668404494630913', true, true, now(), now(), 'bj_super', NULL, '592668404494630913', '北京保全世纪保安职业技能培训学校', '保全世纪学校', '0'),
('591297265516150785', true, true, now(), now(), 'bj_super', NULL, '591297265516150785', '北京华安保安职业技能培训学校', '华安学校', '0'),
('592738493080272897', true, true, now(), now(), 'bj_super', NULL, '592738493080272897', '北京市昌平区特卫安防职业技能培训学校', '特卫安防学校', '0'),
('592668177863802881', true, true, now(), now(), 'bj_super', NULL, '592668177863802881', '北京市房山区现代管理职业技能培训学校', '现代管理学校', '0'),
('592718733567328257', true, true, now(), now(), 'bj_super', NULL, '592718733567328257', '北京市丰台区睿翔世纪职业技能培训学校', '睿翔世纪学校', '0'),
('592660737587937281', true, true, now(), now(), 'bj_super', NULL, '592660737587937281', '北京市丰台区华信中安保安职业技能培训学校', '华信中安学校', '0'),
('592743279196372993', true, true, now(), now(), 'bj_super', NULL, '592743279196372993', '北京市朝阳区安泰保安职业技能培训学校', '安泰学校', '0'),
('591569745669521409', true, true, now(), now(), 'bj_super', NULL, '591569745669521409', '北京市顺义区精彩人生职业技能培训学校', '精彩人生学校', '0');



delete from approve_temp where tempCode='activitiQualificationSubsidyapprovalProcess-110100';
INSERT INTO `approve_temp` (`active`, `create_time`, `update_time`, `update_by`, `tempName`, `tempCode`, `iconUrl`, `approveType`, `effectTime`, `failureTime`, `approveSetStatus`, `noticeTemp`, `repulseExplain`, `seq`, `approveGroupId`, `policeId`, `parentPoliceId`, `parentTempId`)
VALUES (true,now(),now(), 'system', '保安员资格证补贴管理', 'activitiQualificationSubsidyapprovalProcess-110100', '', '', now(), now(), '0', NULL, NULL, '16', '100002', '1', NULL, '1');


INSERT INTO approve_temp_link_rela (  update_time, update_by, create_time, active, updateflag, approveLinkId, approveTempId, seq, createType )
select now(),'system',now(), true, true, 1, id, 1, 0
from approve_temp where tempCode ='activitiQualificationSubsidyapprovalProcess-110100';

INSERT INTO `approve_link_level` ( `active`, `create_time`, `update_time`, `update_by`, `approveLevel`, `parentId`, `tempLinkRelaId`, `updateflag`)
select true,now(),now(), NULL, '2', NULL, id, '\0'
from approve_temp_link_rela where approveTempId in (
select id from approve_temp where tempCode  ='activitiQualificationSubsidyapprovalProcess-110100'
);

INSERT INTO approve_area_process (
	active,
	create_time,
	update_time,
	approveSetStatus,
	regionLevel,
	regionId,
	approveLinkLevelId,
	policeCode,
	policeId,
	updateflag,
	path
)
SELECT
	FALSE,
	SYSDATE(),
	SYSDATE(),
	0,
	t1.orgtype,
	t1.areacode,
	t2.id,
	t1.areacode,
	t1.id,
	TRUE,
	t1.path
FROM
	police t1,
	approve_link_level t2
WHERE
	tempLinkRelaId IN (
		SELECT
			id
		FROM
			approve_temp_link_rela
		WHERE
			approveTempId IN (
				SELECT
					id
				FROM
					approve_temp
				WHERE
					tempCode  ='activitiQualificationSubsidyapprovalProcess-110100'
			)
	)
AND t1.areacode='110100'
AND t1.orgtype IN ('2')
AND t1.ACTIVE = TRUE
AND t2.ACTIVE = TRUE
AND t1.id NOT IN (
	SELECT
		policeId
	FROM
		approve_area_process
	WHERE
		regionLevel IN ('2')
	AND approveLinkLevelId = t2.id
	AND regionId='110100'
);


delete from `config` where `key`='ZGZBT_SWITCH';
INSERT INTO `config` (`key`, `value`, `name`, `active`, `create_time`, `update_time`, `update_by`)
VALUES ('ZGZBT_SWITCH', '1', '资格证补贴开关', true, now(), now(), 'system');





###权限 yyx 20200618
delete from `module` where code in
('subsidyApplyList','fiveGradeSubsidy','certificateSubsidyList','cerSubsidyList','cerSubsidyBatch','trainCerSubsidyBatch','trainCerSubsidyList',
'gradeSubsidyList','gradeSubsidyBatch','trainGradeSubsidyBatch','trainGradeSubsidyList');
### 公司权限
INSERT INTO `module` (`native_code`, `code`, `name`, `url`, `seq`, `index`, `shortcut`, `class`, `action`, `active`, `create_time`, `update_time`, `update_by`, `moduletype`, `p_id`)
VALUES
( '11', 'gradeSubsidyList', '等级证补贴人员名单', '', '1', b'0', b'0', '', '', b'1', now(), now(), 'system', '2', ''),
( '11', 'gradeSubsidyBatch', '等级证补贴批次审批管理', '', '1', b'0', b'0', '', '', b'1', now(), now(), 'system', '2', ''),
( '11', 'cerSubsidyList', '资格证比对人员名单', '', '1', b'0', b'0', '', '', b'1', now(), now(), 'system', '2', ''),
( '11', 'cerSubsidyBatch', '资格证补贴批次审批管理', '', '1', b'0', b'0', '', '', b'1', now(), now(), 'system', '2', ''),
( '11', 'trainCerSubsidyBatch', '培训机构资格证补贴批次审批管理', '', '1', b'0', b'0', '', '', b'1', now(), now(), 'system', '2', ''),
( '11', 'trainCerSubsidyList', '培训机构资格证补贴人员名单', '', '1', b'0', b'0', '', '', b'1', now(), now(), 'system', '2', ''),
( '11', 'trainGradeSubsidyBatch', '培训机构等级证补贴批次审批管理', '', '1', b'0', b'0', '', '', b'1', now(), now(), 'system', '2', ''),
( '11', 'trainGradeSubsidyList', '培训机构等级证补贴人员名单', '', '1', b'0', b'0', '', '', b'1', now(), now(), 'system', '2', '');
### 一级菜单
INSERT INTO `module` (`native_code`, `code`, `name`, `url`, `seq`, `index`, `shortcut`, `class`, `action`, `active`, `create_time`, `update_time`, `update_by`, `moduletype`, `p_id`)
select '11', 'subsidyApplyList', '补贴申请管理', '', '6', b'0', b'0', '', '', b'1', now(), now(), 'system', '1', id from module where code = 'check_all' ;

###二级菜单
INSERT INTO `module` (`native_code`, `code`, `name`, `url`, `seq`, `index`, `shortcut`, `class`, `action`, `active`, `create_time`, `update_time`, `update_by`, `moduletype`, `p_id`)
select '11', 'fiveGradeSubsidy', '五级等级证补贴申请管理', null, '1', b'0', b'0', null, null, b'1', now(), now(), 'system', '1', id from module where code = 'subsidyApplyList' union
select '11', 'certificateSubsidyList', '资格证补贴申请管理', null, '2', b'0', b'0', null, null, b'1', now(), now(), 'system', '1', id from module where code = 'subsidyApplyList';

###三级菜单
INSERT INTO `module` (`native_code`, `code`, `name`, `url`, `seq`, `index`, `shortcut`, `class`, `action`, `active`, `create_time`, `update_time`, `update_by`, `moduletype`, `p_id`)
select '11', 'gradeSubsidyList', '五级等级证补贴人员名单', null, '1', b'0', b'0', null, null, b'1', now(), now(), 'system', '1', id from module where code = 'fiveGradeSubsidy' union
select '11', 'gradeSubsidyBatch', '五级等级证补贴批次审批管理', null, '1', b'0', b'0', null, null, b'1', now(), now(), 'system', '1',id from module where code = 'fiveGradeSubsidy' union
select '11', 'cerSubsidyList', '资格证比对人员名单', null, '1', b'0', b'0', null, null, b'1', now(), now(), 'system', '1', id from module where code = 'certificateSubsidyList' union
select '11', 'cerSubsidyBatch', '资格证补贴批次审批管理', null, '1', b'0', b'0', null, null, b'1', now(), now(), 'system', '1', id from module where code = 'certificateSubsidyList';

delete from  operation where module_id in(select id from module where code in ('subsidyApplyList','fiveGradeSubsidy','certificateSubsidyList','cerSubsidyList',
'cerSubsidyBatch','trainCerSubsidyBatch','trainCerSubsidyList','gradeSubsidyList','gradeSubsidyBatch','trainGradeSubsidyBatch','trainGradeSubsidyList'));
INSERT INTO `operation` (`native_code`, `code`, `name`, `module_id`, `active`, `create_time`, `update_time`, `update_by`)
select null,'query','查询',id,true,now(),now(),'system' from `module` where `code`='cerSubsidyList' union
select null,'view','查看',id,true,now(),now(),'system' from `module` where `code`='cerSubsidyList' union
select null,'export','导出',id,true,now(),now(),'system' from `module` where `code`='cerSubsidyList' union
select null,'exportRoster','导出比对人员',id,true,now(),now(),'system' from `module` where `code`='cerSubsidyList' union
select null,'import','导入比对结果',id,true,now(),now(),'system' from `module` where `code`='cerSubsidyList' union
select null,'query','查询',id,true,now(),now(),'system' from `module` where `code`='gradeSubsidyList' union
select null,'view','查看',id,true,now(),now(),'system' from `module` where `code`='gradeSubsidyList' union
select null,'export','导出',id,true,now(),now(),'system' from `module` where `code`='gradeSubsidyList' union
select null,'exportRoster','导出比对人员',id,true,now(),now(),'system' from `module` where `code`='gradeSubsidyList' union
select null,'import','导入比对结果',id,true,now(),now(),'system' from `module` where `code`='gradeSubsidyList' union
select null,'query','查询',id,true,now(),now(),'system' from `module` where `code`='cerSubsidyBatch' union
select null,'view','查看',id,true,now(),now(),'system' from `module` where `code`='cerSubsidyBatch' union
select null,'exportRoster','导出花名册',id,true,now(),now(),'system' from `module` where `code`='cerSubsidyBatch' union
select null,'query','查询',id,true,now(),now(),'system' from `module` where `code`='gradeSubsidyBatch' union
select null,'view','查看',id,true,now(),now(),'system' from `module` where `code`='gradeSubsidyBatch' union
select null,'exportRoster','导出花名册',id,true,now(),now(),'system' from `module` where `code`='gradeSubsidyBatch' union
select null,'query','查询',id,true,now(),now(),'system' from `module` where `code`='trainCerSubsidyBatch' union
select null,'view','查看',id,true,now(),now(),'system' from `module` where `code`='trainCerSubsidyBatch' union
select null,'add','新增',id,true,now(),now(),'system' from `module` where `code`='trainCerSubsidyBatch' union
select null,'query','查询',id,true,now(),now(),'system' from `module` where `code`='trainCerSubsidyList' union
select null,'view','查看',id,true,now(),now(),'system' from `module` where `code`='trainCerSubsidyList' union
select null,'export','导出',id,true,now(),now(),'system' from `module` where `code`='trainCerSubsidyList' union
select null,'query','查询',id,true,now(),now(),'system' from `module` where `code`='trainGradeSubsidyBatch' union
select null,'view','查看',id,true,now(),now(),'system' from `module` where `code`='trainGradeSubsidyBatch' union
select null,'add','新增',id,true,now(),now(),'system' from `module` where `code`='trainGradeSubsidyBatch' union
select null,'query','查询',id,true,now(),now(),'system' from `module` where `code`='trainGradeSubsidyList' union
select null,'view','查看',id,true,now(),now(),'system' from `module` where `code`='trainGradeSubsidyList' union
select null,'export','导出',id,true,now(),now(),'system' from `module` where `code`='trainGradeSubsidyList' ;

delete from  `permission` where module_id in(select id from module where code in ('gradeSubsidyList','gradeSubsidyBatch','cerSubsidyBatch','cerSubsidyList'));
INSERT INTO`permission` (`role_id`, `module_id`, `operation_id`, `roletype`)
select pr.id,o.module_id,o.id,'1'
from police_role pr,police p,operation o,module m
where pr.active=true
and p.active=TRUE
and o.active=TRUE
and pr.orgid=p.id
and o.module_id=m.id
and m.code in ('gradeSubsidyList','gradeSubsidyBatch','cerSubsidyBatch','cerSubsidyList')
and m.moduletype='1';

## 五级等级证报名信息
delete from  operation where module_id = (select id from module where code ='personGradeManager') and code in ('exportRoster','import');
INSERT INTO `operation` (`native_code`, `code`, `name`, `module_id`, `active`, `create_time`, `update_time`, `update_by`)
select null,'import','导入比对结果',id,true,now(),now(),'system' from `module` where `code`='personGradeManager' union
select null,'exportRoster','导出比对人员',id,true,now(),now(),'system' from `module` where `code`='personGradeManager' ;

delete from  `permission` where module_id = (select id from module where code ='personGradeManager') and
operation_id in (select id from operation where code in ('import','exportRoster'));

INSERT INTO`permission` (`role_id`, `module_id`, `operation_id`, `roletype`)
select pr.id,o.module_id,o.id,'1'
from police_role pr,police p,operation o,module m
where pr.active=true
and p.active=TRUE
and o.active=TRUE
and pr.orgid=p.id
and o.module_id=m.id
and m.code = 'personGradeManager'
and o.code in ('import','exportRoster')
and m.moduletype='1';



#yyx 0615
delete from sys_dict where codeType ='cerCensorStatus' ;
delete from sys_dict where codeType ='subsidyCerType' ;
delete from sys_dict where codeType ='exportFormulaType' ;
INSERT INTO `sys_dict` (`isActive`, `updateTime`, `updateflag`, `codeType`, `tableName`, `description`, `codeValue`, `label`, `seq`) VALUES
( '1', now(), true, 'cerCensorStatus', NULL, '资格证背景筛查状态', '001', '审查中', '1'),
( '1', now(), true, 'cerCensorStatus', NULL, '资格证背景筛查状态', '003', '正常', '2'),
( '1', now(), true, 'cerCensorStatus', NULL, '资格证背景筛查状态', '004', '关注', '3'),
( '1', now(), true, 'subsidyCerType', NULL, '补贴证书类型', '02', '资格证补贴', '2'),
( '1', now(), true, 'subsidyCerType', NULL, '补贴证书类型', '01', '等级证补贴', '1'),
( '1', now(), true, 'exportFormulaType', NULL, '导出类型公式', '004', '保留两位小数', '4'),
( '1', now(), true, 'exportFormulaType', NULL, '导出类型公式', '003', '时间格式化', '3'),
( '1', now(), true, 'exportFormulaType', NULL, '导出类型公式', '002', '码表转换', '2'),
( '1', now(), true, 'exportFormulaType', NULL, '导出类型公式', '001', '获取字段名', '1');


INSERT INTO `sys_export_column` (`active`, `create_time`, `update_time`, `update_by`, `columnName`, `columnWith`, `occupy`,
`columnValueSource`, `formula`, `context`, `seq`, `required`, `configId`, `position`, `beginCol`, `formatDate`, `sysCodeType`) VALUES
( true, now(), NULL, NULL, '身份证号', '10000', NULL, 'idNum', NULL, NULL, '2', '0', '00000000003', 'center', NULL, NULL, NULL),
( true, now(), NULL, NULL, '姓名', '5000', NULL, 'name', NULL, NULL, '1', '0', '00000000003', 'center', NULL, NULL, NULL),
( true, now(), NULL, NULL, '电话', '5000', NULL, 'phone', NULL, NULL, '3', '1', '00000000003', 'center', NULL, NULL, NULL),
( true, now(), NULL, NULL, '单位名称：', '80', '3', 'trainName', '001', '11', '1', '1', '00000000001', 'top', '1', NULL, NULL),
( true, now(), NULL, NULL, '单位：元', '5000', '3', 'subsidyMoney', NULL, NULL, '2', NULL, '00000000001', 'top', '23', NULL, NULL),
( true, now(), NULL, NULL, '填报人：', NULL, '3', NULL, NULL, NULL, '1', NULL, '00000000001', 'bottom', '1', NULL, NULL),
( true, now(), NULL, NULL, '联系电话：', NULL, '3', NULL, NULL, NULL, '2', NULL, '00000000001', 'bottom', '6', NULL, NULL),
( true, now(), NULL, NULL, '日期：', NULL, '3', NULL, NULL, NULL, '3', NULL, '00000000001', 'bottom', '11', NULL, NULL),
( true, now(), NULL, NULL,'提示：', NULL, '10', NULL, NULL, '注：1.带“*”的代表必填项；\n2.补贴政策类型：(1)市科委：高精尖产业培训补贴（企业）、高精尖产业培训补贴（个人）' ||
 '（2）市经济信息化局：高精尖产业培训补贴（企业）、高精尖产业培训补贴（个人）（3）市金融局：金融科技产业培训补贴（企业）、金融科技产业培训补贴（个人）（4）市商务局：' ||
  '家政服务员岗前培训补贴、家政服务员回炉（非员工制）培训补贴、家政服务员回炉（员工制）培训补贴（5）市民政局：养老护理员培训补贴（6）市卫生健康委：护理员培训补贴（7）' ||
   '市公安局：保安员培训补贴（8）市邮政管理局：快递员培训补贴（9）市教委：保育员培训补贴（10）市国资委：市管国有企业职工培训补贴（11）市园林绿化局：园林绿化企业职工培训补贴' ||
    '（12）市应急管理局：特种作业操作证补贴（13）市市场监督管理局：特种设备作业人员证补贴（14）市文化和旅游局：文创产业人才培训；\n3.证书类型：职业资格证、技能等级证、' ||
     '特种作业操作证、特种设备作业人员证、合格证、结业证、其他；\n4.课时：每课时45分钟，数据保留到个位；\n5.身份证号、手机号、证书编号使用文本格式填写；\n6.日期：' ||
      '所有日期格式按照yymmdd填写；\n7.补贴金额：数据保留到个位。', '4', NULL, '00000000001', 'bottom', '1', NULL, NULL),
( true, now(), NULL, NULL, '姓名', '3000', NULL, 'name', NULL, NULL, '1', '1', '00000000001', 'center', NULL, NULL, NULL),
( true, now(), NULL, NULL, '性别', '2000', NULL, 'sex', '002', NULL, '2', '1', '00000000001', 'center', NULL, NULL, 'sex'),
( true, now(), NULL, NULL, '身份证号', '6000', NULL, 'idNum', NULL, NULL, '3', '1', '00000000001', 'center', NULL, NULL, NULL),
( true, now(), NULL, NULL, '手机号', '3700', NULL, 'phone', NULL, NULL, '4', '1', '00000000001', 'center', NULL, NULL, NULL),
( true, now(), NULL, NULL, '户籍性质（城镇、农村）', '3000', NULL, 'residentNature', '002', NULL, '5', '1', '00000000001', 'center', NULL, NULL, 'residentType'),
( true, now(), NULL, NULL, '户籍所在地（本市、外埠）', '3000', NULL, 'residentPlace', '002', NULL, '6', '1', '00000000001', 'center', NULL, NULL, 'residentPlaceType'),
( true, now(), NULL, NULL, '劳动合同起始时间', '4000', NULL, 'contractStartTime', '003', NULL, '7', '1', '00000000001', 'center', NULL, 'yyyy-MM-dd', NULL),
( true, now(), NULL, NULL, '劳动合同结束时间', '4000', NULL, 'contractEndTime', '003', NULL, '8', '0', '00000000001', 'center', NULL, 'yyyy-MM-dd', NULL),
( true, now(), NULL, NULL, '补贴政策类型', '5000', NULL, 'subsidyType', NULL, NULL, '9', '1', '00000000001', 'center', NULL, NULL, NULL),
( true, now(), NULL, NULL, '补贴对象类型（企业、培训机构）', '5000', NULL, 'subsidyPeopleType', NULL, NULL, '10', '1', '00000000001', 'center', NULL, NULL, NULL),
( true, now(), NULL, NULL, '补贴对象名称', '5000', NULL, 'trainName', NULL, NULL, '11', '1', '00000000001', 'center', NULL, NULL, NULL),
( true, now(), NULL, NULL, '统一社会信用代码', '5500', NULL, 'socialCode', NULL, NULL, '12', '1', '00000000001', 'center', NULL, NULL, NULL),
( true, now(), NULL, NULL, '培训职业（工种）或项目', '4500', NULL, 'trainProfession', NULL, NULL, '13', '1', '00000000001', 'center', NULL, NULL, NULL),
( true, now(), NULL, NULL, '培训等级(一级、二级、三级、四级、五级、非等级)', '4000', NULL, 'trainGrade', NULL, NULL, '14', '1', '00000000001', 'center', NULL, NULL, NULL),
( true, now(), NULL, NULL, '培训课时', '3000', NULL, 'classHour', '004', NULL, '15', '0', '00000000001', 'center', NULL, NULL, NULL),
( true, now(), NULL, NULL, '培训开始日期', '4000', NULL, 'trainBeginDate', '003', NULL, '16', '1', '00000000001', 'center', NULL, 'yyyy-MM-dd', NULL),
( true, now(), NULL, NULL, '培训结束日期', '4000', NULL, 'trainEndDate', '003', NULL, '17', '1', '00000000001', 'center', NULL, 'yyyy-MM-dd', NULL),
( true, now(), NULL, NULL, '证书类型', '4500', NULL, 'certificateType', NULL, NULL, '18', '1', '00000000001', 'center', NULL, NULL, NULL),
( true, now(), NULL, NULL, '发证时间', '4000', NULL, 'certificateDate', '003', NULL, '19', '0', '00000000001', 'center', NULL, 'yyyy-MM-dd', NULL),
( true, now(), NULL, NULL, '证书编号', '5000', NULL, 'certificateNumber', NULL, NULL, '20', '0', '00000000001', 'center', NULL, NULL, NULL),
( true, now(), NULL, NULL, '证书职业工种', '3000', NULL, 'certificateProfession', NULL, NULL, '21', '0', '00000000001', 'center', NULL, NULL, NULL),
( true, now(), NULL, NULL, '证书等级（一级、二级、三级、四级、五级、非等级）', '4000', NULL, 'certificateGrade', NULL, NULL, '22', '0', '00000000001', 'center', NULL, NULL, NULL),
( true, now(), NULL, NULL, '补贴金额', '3000', NULL, 'subsidyMoney', '004', NULL, '23', '1', '00000000001', 'center', NULL, NULL, NULL),
( true, now(), NULL, NULL, '备注', '5000', NULL, 'remark', NULL, NULL, '24', '0', '00000000001', 'center', NULL, NULL, NULL),
( true, now(), NULL, NULL, '姓名', '3000', NULL, 'name', NULL, NULL, '1', '1', '00000000002', 'center', NULL, NULL, NULL),
( true, now(), NULL, NULL, '性别', '2000', NULL, 'sex', '002', NULL, '2', '1', '00000000002', 'center', NULL, NULL, 'sex'),
( true, now(), NULL, NULL, '身份证号', '6000', NULL, 'idNum', NULL, NULL, '3', '1', '00000000002', 'center', NULL, NULL, NULL),
( true, now(), NULL, NULL, '手机号', '3700', NULL, 'phone', NULL, NULL, '4', '1', '00000000002', 'center', NULL, NULL, NULL),
( true, now(), NULL, NULL, '户籍性质（城镇、农村）', '3000', NULL, 'residentNature', '002', NULL, '5', '1', '00000000002', 'center', NULL, NULL, 'residentType'),
( true, now(), NULL, NULL, '户籍所在地（本市、外埠）', '3000', NULL, 'residentPlace', '002', NULL, '6', '1', '00000000002', 'center', NULL, NULL, 'residentPlaceType'),
( true, now(), NULL, NULL, '劳动合同起始时间', '4000', NULL, 'contractStartTime', '003', NULL, '7', '1', '00000000002', 'center', NULL, 'yyyy-MM-dd', NULL),
( true, now(), NULL, NULL, '劳动合同结束时间', '4000', NULL, 'contractEndTime', '003', NULL, '8', '0', '00000000002', 'center', NULL, 'yyyy-MM-dd', NULL),
( true, now(), NULL, NULL, '补贴政策类型', '5000', NULL, 'subsidyType', NULL, NULL, '9', '1', '00000000002', 'center', NULL, NULL, NULL),
( true, now(), NULL, NULL, '补贴对象类型（企业、培训机构）', '5000', NULL, 'subsidyPeopleType', NULL, NULL, '10', '1', '00000000002', 'center', NULL, NULL, NULL),
( true, now(), NULL, NULL, '补贴对象名称', '5000', NULL, 'trainName', NULL, NULL, '11', '1', '00000000002', 'center', NULL, NULL, NULL),
( true, now(), NULL, NULL, '统一社会信用代码', '5500', NULL, 'socialCode', NULL, NULL, '12', '1', '00000000002', 'center', NULL, NULL, NULL),
( true, now(), NULL, NULL, '培训职业（工种）或项目', '4500', NULL, 'trainProfession', NULL, NULL, '13', '1', '00000000002', 'center', NULL, NULL, NULL),
( true, now(), NULL, NULL, '培训等级(一级、二级、三级、四级、五级、非等级)', '4000', NULL, 'trainGrade', NULL, NULL, '14', '1', '00000000002', 'center', NULL, NULL, NULL),
( true, now(), NULL, NULL, '培训课时', '3000', NULL, 'classHour', '004', NULL, '15', '0', '00000000002', 'center', NULL, NULL, NULL),
( true, now(), NULL, NULL, '培训开始日期', '4000', NULL, 'trainBeginDate', '003', NULL, '16', '1', '00000000002', 'center', NULL, 'yyyy-MM-dd', NULL),
( true, now(), NULL, NULL, '培训结束日期', '4000', NULL, 'trainEndDate', '003', NULL, '17', '1', '00000000002', 'center', NULL, 'yyyy-MM-dd', NULL),
( true, now(), NULL, NULL, '证书类型', '4500', NULL, 'certificateType', NULL, NULL, '18', '1', '00000000002', 'center', NULL, NULL, NULL),
( true, now(), NULL, NULL, '发证时间', '4000', NULL, 'certificateDate', '003', NULL, '19', '0', '00000000002', 'center', NULL, 'yyyy-MM-dd', NULL),
( true, now(), NULL, NULL, '证书编号', '5000', NULL, 'certificateNumber', NULL, NULL, '20', '0', '00000000002', 'center', NULL, NULL, NULL),
( true, now(), NULL, NULL, '证书职业工种', '3000', NULL, 'certificateProfession', NULL, NULL, '21', '0', '00000000002', 'center', NULL, NULL, NULL),
( true, now(), NULL, NULL, '证书等级（一级、二级、三级、四级、五级、非等级）', '4000', NULL, 'certificateGrade', NULL, NULL, '22', '0', '00000000002', 'center', NULL, NULL, NULL),
( true, now(), NULL, NULL, '补贴金额', '3000', NULL, 'subsidyMoney', '004', NULL, '23', '1', '00000000002', 'center', NULL, NULL, NULL),
( true, now(), NULL, NULL, '备注', '5000', NULL, 'remark', NULL, NULL, '24', '0', '00000000002', 'center', NULL, NULL, NULL);


INSERT INTO `sys_export_config` ( `active`, `create_time`, `update_time`, `update_by`, `title`, `exportType`, `columns`, `columnNo`, `tableName`, `isJar`, `subsidyCerType`, `trainNameKey`) VALUES
( true, now(), NULL, NULL, '北京市职业技能提升行动专项资金培训补贴人员花名册', '03', '25', '1', 'v_grade_subsidy_batch', '1', '01', 'trainName'),
( true, now(), NULL, NULL, '补贴比对', '01', '25', '1', 'grade_subsidy_roster', '0', '01', NULL),
( true, now(), NULL, NULL, '报名比对', '02', '3', '0', 'v_person_grade', '0', NULL, NULL);

INSERT INTO `sys_import_config` (`active`, `create_time`, `update_time`, `update_by`, `readRow`, `keyColumn`, `resultColumn`, `reasonColumn`, `businessType`,
`tableName`, `filedName`, `reasonFiledName`, `resultFiledName`, `subsidyCerType`) VALUES
( true, now(), NULL, 'bj_super', '4', '4', '26', '27', '02', 'grade_subsidy_roster', 'idNum', 'reason', 'subsidyStatus', '01'),
( true, now(), NULL, 'bj_super', '4', '4', '26', '27', '01', 'sb_person_grade', 'idNum', 'compareReason', 'compareStatus', '01'),
( true, now(), NULL, 'bj_super', '4', '4', '26', '27', '03', 'grade_subsidy_roster', 'idNum', 'reason', 'subsidyStatus', '02');

INSERT INTO `subsidyconfig` ( `active`, `create_time`, `update_time`, `update_by`, `businessType`, `businessName`, `amount`, `percent`, `EffectiveTime`) VALUES
( true, now(), NULL, 'bj_super', '001', '结业证书', '1140', '60%', NULL),
( true, now(), NULL, 'bj_super', '002', '结业鉴定证书', '1900', '100%', NULL),
( true, now(), NULL, 'bj_super', '003', '鉴定证书', '760', '40%', NULL),
( true, now(), NULL, 'bj_super', '004', '鉴定证书', '600', NULL, NULL),
( true, now(), NULL, 'bj_super', '005', '鉴定补贴', '170', NULL, NULL);


INSERT INTO `subsidycomparison` ( `active`, `create_time`, `update_time`, `update_by`, `code`, `codeValue`, `flowTo`, `businessType`) VALUES
 ( true, now(), NULL, 'bj_super', '01', '数据项格式不正确', '04', '01'),
 ( true, now(), NULL, 'bj_super', '02', '数据项不能为空', '04', '01'),
 ( true, now(), NULL, 'bj_super', '03', '数据项长度超长', '04', '01'),
 ( true, now(), NULL, 'bj_super', '04', '数据项填写编码不存在', '04', '01'),
 ( true, now(), NULL, 'bj_super', '05', '培训开始日期必须在2019.01.01至2021.12.31之间', '03', '01'),
 ( true, now(), NULL, 'bj_super', '06', '培训结束日期必须在2019.01.01至2021.12.31之间', '03', '01'),
 ( true, now(), NULL, 'bj_super', '07', '培训开始日期不能大于培训结束日期', '03', '01'),
 ( true, now(), NULL, 'bj_super', '08', '发证时间不能在2019.01.01之间', '03', '01'),
 ( true, now(), NULL, 'bj_super', '09', '培训开始日期不能大于当前日期', '02', '01'),
 ( true, now(), NULL, 'bj_super', '10', '培训结束日期不能大于当前日期', '02', '01'),
 ( true, now(), NULL, 'bj_super', '11', '发证时间不能大于当前日期', '02', '01'),
 ( true, now(), NULL, 'bj_super', '12', '此证书编号已存在', '02', '01'),
 ( true, now(), NULL, 'bj_super', '13', '存在身份证号码、培训开始日期、培训结束日期、社会统一信用代码完全一致的人员', '01', '01'),
 ( true, now(), NULL, 'bj_super', '14', '14XX补贴政策类型补贴金额不能超过XX', '01', '01'),
 ( true, now(), NULL, 'bj_super', '15', '15同一自然年度内申请享受补贴不超过3次', '01', '01'),
 ( true, now(), NULL, 'bj_super', '16', '16提交的文件中存在重复人员', '01', '01'),
 ( true, now(), NULL, 'bj_super', '17', '17在培训时间内参加过企业新型学徒制的人员不能再参加其他培训', '01', '01'),
 ( true, now(), NULL, 'bj_super', '18', '18正在XX委办局XX政策申请享受补贴', '01', '01'),
 ( true, now(), NULL, 'bj_super', '19', '01数据项格式不正确', '04', '02'),
 ( true, now(), NULL, 'bj_super', '20', '02数据项不能为空', '03', '02'),
 ( true, now(), NULL, 'bj_super', '21', '03数据长度超长', '02', '02'),
 ( true, now(), NULL, 'bj_super', '22', '04数据项填写编码不存在', '01', '02'),
 ( true, now(), NULL, 'bj_super', '23', '可申请', '02', '03'),
 ( true, now(), NULL, 'bj_super', '24', '不可申请', '03', '03');


###070 2020-06-17 资格证申诉
delete from `sys_dict` where `codeType`='cerCensorStatus' and codeValue='005';
delete from `sys_dict` where `codeType`='cerCensorStatus' and codeValue='006';
delete from `sys_dict` where `codeType`='cerCensorStatus' and codeValue='007';
INSERT INTO `sys_dict` (`isActive`, `updateTime`, `updateflag`, `codeType`, `tableName`, `description`, `codeValue`, `label`, `seq`)
select '1', now(), true , 'cerCensorStatus', NULL, '审查状态', '005', '申诉中', '5' from config where `key`='current_native' and `value`='11' union
select '1', now(), true, 'cerCensorStatus', NULL, '审查状态', '006', '申诉通过', '6' from config where `key`='current_native' and `value`='11' union
select '1', now(), true, 'cerCensorStatus', NULL, '审查状态', '007', '申诉不通过', '7' from config where `key`='current_native' and `value`='11';



#yyx 20200622
INSERT INTO `sys_dict` ( `isActive`, `updateTime`, `updateflag`, `codeType`, `tableName`, `description`, `codeValue`, `label`, `seq`)
values
('1', now(), '1', 'exportType', NULL, '通用导出类型', '04', '疫情防控导出', '4');



##yyx 20200623 疫情防控脚本
INSERT INTO `sys_export_config` (`active`, `create_time`, `update_time`, `update_by`, `title`, `exportType`, `columns`, `columnNo`, `tableName`, `isJar`, `subsidyCerType`, `trainNameKey`) VALUES
( true , now(),  now(), 'bj_super', '疫情中高风险地区保安公司驻勤点及保安员统计表疫情中高风险地区保安公司驻勤点及保安员统计表', '04', '14', '1', 'epidemic', '1', NULL, 'companyName');

INSERT INTO `sys_export_column` ( `active`, `create_time`, `update_time`, `update_by`, `columnName`, `columnWith`, `occupy`, `beginCol`, `columnValueSource`, `formula`, `formatDate`, `sysCodeType`, `context`, `seq`, `required`, `configId`, `position`, `isNeedSelect`)
select true, now(), now(), 'bj_super', '保安公司名称：', NULL, '2', '1', 'companyName', '001', NULL, NULL, NULL, '1', NULL, id, 'top', '1' from sys_export_config where exportType = '04' union
select true, now(), now(), 'bj_super', '填报人：', NULL, '2', '4', 'filledBy', '001', NULL, NULL, NULL, '2', NULL, id, 'top', '1' from sys_export_config where exportType = '04' union
select true, now(), now(), 'bj_super', '填报人电话：', NULL, '3', '7', 'filledByPhone', '001', NULL, NULL, '1', '3', NULL, id, 'top', '1' from sys_export_config where exportType = '04' union
select true, now(), now(), 'bj_super', '填报时间：', NULL, '3', '11', 'filledByTime', '001', 'yyyy-MM-dd', NULL, NULL, '4', NULL, id, 'top', '1' from sys_export_config where exportType = '04' union
select true, now(), now(), 'bj_super', '驻勤点名称', '4000', NULL, NULL, 'taskName', NULL, NULL, NULL, NULL, '1', NULL, id, 'center', '1' from sys_export_config where exportType = '04' union
select true, now(), now(), 'bj_super', '驻勤点详细地址', '6000', NULL, NULL, 'taskAddress', NULL, NULL, NULL, NULL, '2', NULL, id, 'center', '1' from sys_export_config where exportType = '04' union
select true, now(), now(), 'bj_super', '保安公司监管分局', '6000', NULL, NULL, 'orgName', NULL, NULL, NULL, NULL, '3', NULL, id, 'center', '1' from sys_export_config where exportType = '04' union
select true, now(), now(), 'bj_super', '保安员总人数', '3000', NULL, NULL, 'perNum', NULL, NULL, NULL, NULL, '4', NULL, id, 'center', '1' from sys_export_config where exportType = '04' union
select true, now(), now(), 'bj_super', '已做核酸检测人数', '3000', NULL, NULL, 'nucleicCheckNum', NULL, NULL, NULL, NULL, '5', NULL, id, 'center', '1' from sys_export_config where exportType = '04' union
select true, now(), now(), 'bj_super', '已做核酸未出结果人数', '3000', NULL, NULL, 'nucleicNum', NULL, NULL, NULL, NULL, '6', NULL, id, 'center', '1' from sys_export_config where exportType = '04' union
select true, now(), now(), 'bj_super', '未做核酸检测人数', '3000', NULL, NULL, 'unNucleicNum', NULL, NULL, NULL, NULL, '7', NULL, id, 'center', '1' from sys_export_config where exportType = '04' union
select true, now(), now(), 'bj_super', '核酸检测呈阳性人数', '3000', NULL, NULL, 'nucleicPositiveNum', NULL, NULL, NULL, NULL, '8', NULL, id, 'center', '1' from sys_export_config where exportType = '04' union
select true, now(), now(), 'bj_super', '确诊人数', '3000', NULL, NULL, 'diagnosisNum', NULL, NULL, NULL, NULL, '9', NULL, id, 'center', '1' from sys_export_config where exportType = '04' union
select true, now(), now(), 'bj_super', '政府集中隔离人数', '3000', NULL, NULL, 'quarantineGovNum', NULL, NULL, NULL, NULL, '10', NULL, id, 'center', '1' from sys_export_config where exportType = '04' union
select true, now(), now(), 'bj_super', '公司集中隔离人数', '3000', NULL, NULL, 'quarantineComNum', NULL, NULL, NULL, NULL, '11', NULL, id, 'center', '1' from sys_export_config where exportType = '04' union
select true, now(), now(), 'bj_super', '驻勤点原地隔离人数', '3000', NULL, NULL, 'quarantineTaskNum', NULL, NULL, NULL, NULL, '12', NULL, id, 'center', '1' from sys_export_config where exportType = '04' union
select true, now(), now(), 'bj_super', '隔离总人数', '3000', NULL, NULL, 'quarantineNum', NULL, NULL, NULL, NULL, '13', NULL, id, 'center', '1' from sys_export_config where exportType = '04' ;


delete from `sys_dict` where `codeType`in ('areaNameBJ','riskLevel');
INSERT INTO `sys_dict` (`isActive`, `updateTime`, `updateflag`, `codeType`, `tableName`, `description`, `codeValue`, `label`, `seq`) VALUES
( '1', now(), true , 'areaNameBJ', NULL, '北京市区域', '110221', '昌平区', '1'),
( '1', now(), true , 'areaNameBJ', NULL, '北京市区域', '110105', '朝阳区', '2'),
( '1', now(), true , 'areaNameBJ', NULL, '北京市区域', '110224', '大兴区', '3'),
( '1', now(), true , 'areaNameBJ', NULL, '北京市区域', '110101', '东城区', '4'),
( '1', now(), true , 'areaNameBJ', NULL, '北京市区域', '110111', '房山区', '5'),
( '1', now(), true , 'areaNameBJ', NULL, '北京市区域', '110106', '丰台区', '6'),
( '1', now(), true , 'areaNameBJ', NULL, '北京市区域', '110108', '海淀区', '7'),
( '1', now(), true , 'areaNameBJ', NULL, '北京市区域', '110227', '怀柔区', '8'),
( '1', now(), true , 'areaNameBJ', NULL, '北京市区域', '110109', '门头沟区', '9'),
( '1', now(), true , 'areaNameBJ', NULL, '北京市区域', '110228', '密云区', '10'),
( '1', now(), true , 'areaNameBJ', NULL, '北京市区域', '110226', '平谷区', '11'),
( '1', now(), true , 'areaNameBJ', NULL, '北京市区域', '110107', '石景山区', '12'),
( '1', now(), true , 'areaNameBJ', NULL, '北京市区域', '110222', '顺义区', '13'),
( '1', now(), true , 'areaNameBJ', NULL, '北京市区域', '110091', '天安门区', '14'),
( '1', now(), true , 'areaNameBJ', NULL, '北京市区域', '110223', '通州区', '15'),
( '1', now(), true , 'areaNameBJ', NULL, '北京市区域', '110102', '西城区', '16'),
( '1', now(), true , 'areaNameBJ', NULL, '北京市区域', '110229', '延庆区', '17'),
( '1', now(), true , 'areaNameBJ', NULL, '北京市区域', '110092', '西站区', '18'),
( '1', now(), true , 'areaNameBJ', NULL, '北京市区域', '110231', '机场区', '19'),
( '1', now(), true, 'riskLevel', NULL, '风险等级', '2', '中风险', '3'),
( '1', now(), true, 'riskLevel', NULL, '风险等级', '3', '低风险', '2'),
( '1', now(), true, 'riskLevel', NULL, '风险等级', '1', '高风险', '1');


#20200713 问题人员公安处理意见辞退标准用词3.18
delete from `config` where `key`='dismiss_suggestion';
INSERT INTO `config` (`key`, `value`, `name`, `active`, `create_time`, `update_time`, `update_by`)
select 'dismiss_suggestion', '该人不符合《保安服务管理条例》招录保安员标准，辞退时请注意方式方法，注意保密', '辞退意见', true, now(), now(), 'system'
FROM config
where `key`='current_native'
and `value`='11';