-- 申诉表结构 DROP TABLE IF EXISTS `appeal`; CREATE TABLE `appeal` ( `id` bigint(32) NOT NULL AUTO_INCREMENT COMMENT '主键id', `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 '更新人', `name` varchar(20) DEFAULT NULL COMMENT '姓名', `idnum` varchar(20) DEFAULT NULL COMMENT '公民身份证号', `companyId` bigint(20) DEFAULT NULL COMMENT '公司ID', `companyName` varchar(255) DEFAULT NULL COMMENT '公司名称', `appealState` varchar(10) DEFAULT NULL COMMENT '审批状态', `updateflag` bit(1) DEFAULT NULL COMMENT '更新标记', `appealContext` varchar(1000) DEFAULT NULL COMMENT '申诉内容', `orgId` bigint(20) DEFAULT NULL COMMENT '申诉机构ID', `orgName` varchar(100) DEFAULT NULL COMMENT '机构名称', `appealResonIn` varchar(1000) DEFAULT NULL COMMENT '对内申诉审批原因', `appealReson` varchar(1000) DEFAULT NULL COMMENT '对外申诉审批原因', `cardImgPositive` varchar(255) DEFAULT NULL COMMENT '身份证正面', `cardImgBack` varchar(255) DEFAULT NULL COMMENT '身份证反面', `cardHold` varchar(255) DEFAULT NULL COMMENT '手持身份证号', `businesstype` varchar(3) DEFAULT NULL COMMENT '业务类型(见码表)', `businessid` bigint(20) DEFAULT NULL COMMENT '业务ID', `areaCode` varchar(255) DEFAULT NULL COMMENT '区域编号', `authImg` varchar(255) DEFAULT NULL COMMENT '认证照片', `dataImg` varchar(255) DEFAULT NULL COMMENT '人口库照片', `attFile` varchar(255) DEFAULT NULL COMMENT '附件', PRIMARY KEY (`id`), KEY `appeal_key` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8; -- 权限 delete from `module` where id in(314,315,316,317); 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 ('314', '11', 'qzmanager', '枪支管理', '', '2', b'0', b'0', '', '', b'1', '2017-06-30 18:00:19', '2017-06-30 18:00:21', 'system', '1', '142'); 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 ('315', '11', 'clmanager', '车辆管理', '', '2', b'0', b'0', '', '', b'1', '2017-06-30 18:00:19', '2017-06-30 18:00:21', 'system', '1', '142'); 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 ('316', '11', 'jfmanager', '技防设备管理', '', '2', b'0', b'0', '', '', b'1', '2017-06-30 18:00:19', '2017-06-30 18:00:21', 'system', '1', '142'); 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 ('317', '11', 'appealManager', '申诉管理', '', '2', b'0', b'0', '', '', b'1', '2017-06-30 18:00:19', '2017-06-30 18:00:21', 'system', '1', '142'); delete from operation where module_id in(314,315,316,317); INSERT INTO `operation` (`id`, `native_code`, `code`, `name`, `module_id`, `active`, `create_time`, `update_time`, `update_by`) VALUES (null, '11', 'toQueryPage', '查询页面', '314', b'1', '2019-10-15 13:19:09', '2019-10-15 13:19:09', 'system'); INSERT INTO `operation` (`id`, `native_code`, `code`, `name`, `module_id`, `active`, `create_time`, `update_time`, `update_by`) VALUES (null, '11', 'toQueryPage', '查询页面', '315', b'1', '2019-10-15 13:19:09', '2019-10-15 13:19:09', 'system'); INSERT INTO `operation` (`id`, `native_code`, `code`, `name`, `module_id`, `active`, `create_time`, `update_time`, `update_by`) VALUES (null, '11', 'toQueryPage', '查询页面', '316', b'1', '2019-10-15 13:19:09', '2019-10-15 13:19:09', 'system'); INSERT INTO `operation` (`id`, `native_code`, `code`, `name`, `module_id`, `active`, `create_time`, `update_time`, `update_by`) VALUES (null, '11', 'query', '查询', '317', b'1', '2019-10-15 13:19:09', '2019-10-15 13:19:09', 'system'); -- 申诉码表 delete from `sys_dict` where `codeType` = 'appealState'; INSERT INTO `sys_dict` (`sysDictId`, `isActive`, `codeType`, `tableName`, `description`, `codeValue`, `label`, `seq`) VALUES (null, '1', 'appealState', NULL, '申诉状态', '002', '申诉中', '1'); INSERT INTO `sys_dict` (`sysDictId`, `isActive`, `codeType`, `tableName`, `description`, `codeValue`, `label`, `seq`) VALUES (null, '1', 'appealState', '', '申诉状态', '003', '申诉通过', '2'); INSERT INTO `sys_dict` (`sysDictId`, `isActive`, `codeType`, `tableName`, `description`, `codeValue`, `label`, `seq`) VALUES (null, '1', 'appealState', '', '申诉状态', '004', '申诉不通过', '3'); delete from sys_dict where codeType='punishType'; INSERT INTO `sys_dict` (`isActive`, `codeType`, `tableName`, `description`, `codeValue`, `label`, `seq`) VALUES ('1', 'punishType', NULL, '惩罚对象类型', '1', '个人', '1'), ('1', 'punishType', NULL, '惩罚对象类型', '2', '组织', '2'); ##其它犯罪信息检查 DROP TABLE IF EXISTS `other_punish`; CREATE TABLE `other_punish` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id', `areaCode` varchar(10) DEFAULT NULL COMMENT '数据区域', `active` bit(1) NOT NULL COMMENT '是否有效', `create_time` datetime DEFAULT NULL COMMENT '创建时间', `update_time` datetime DEFAULT NULL COMMENT '更新时间', `update_by` varchar(50) DEFAULT NULL COMMENT '更新者', `updateflag` bit(1) DEFAULT NULL COMMENT '更新标志位', `punish_type` varchar(5) DEFAULT NULL COMMENT '惩罚对象类型', `company_name` varchar(200) DEFAULT NULL COMMENT '公司名称', `address` varchar(500) DEFAULT NULL COMMENT '公司名称', `name` varchar(50) DEFAULT NULL COMMENT '姓名', `idnum` varchar(20) DEFAULT NULL COMMENT '身份证号', `phone` varchar(20) DEFAULT NULL COMMENT '手机号码', `case_no` varchar(50) DEFAULT NULL COMMENT '案件编号', `reason` longtext COMMENT '处罚原因', `content` longtext COMMENT '处罚内容', `punish_date` varchar(20) DEFAULT NULL COMMENT '处罚时间', `org_id` bigint(20) DEFAULT NULL COMMENT '作出处罚单位id', `org_name` varchar(200) DEFAULT NULL COMMENT '作出处罚单位', `other_org` varchar(500) DEFAULT NULL COMMENT '作出处罚决定的其他单位', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT='其它犯罪信息检查'; DROP TABLE IF EXISTS `other_punish_involver`; CREATE TABLE `other_punish_involver` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id', `areaCode` varchar(10) DEFAULT NULL COMMENT '数据区域', `active` bit(1) NOT NULL COMMENT '是否有效', `create_time` datetime DEFAULT NULL COMMENT '创建时间', `update_time` datetime DEFAULT NULL COMMENT '更新时间', `update_by` varchar(50) DEFAULT NULL COMMENT '更新者', `updateflag` bit(1) DEFAULT NULL COMMENT '更新标志位', `punishId` bigint(20) DEFAULT NULL COMMENT '处罚id', `name` varchar(50) DEFAULT NULL COMMENT '参与者姓名', `idnum` varchar(20) DEFAULT NULL COMMENT '参与者身份证号', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT='其它犯罪信息检查参与者'; ###权限 delete from `module` where id in(400); 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 ('400', '11', 'otherPunishList', '其他违法违规处罚', '', '3', b'0', b'0', '', '', b'1', '2020-01-10 16:31:39', '2020-01-10 16:31:39', 'system', '1', '190'); delete from operation where module_id in(400); INSERT INTO `operation` (`id`, `native_code`, `code`, `name`, `module_id`, `active`, `create_time`, `update_time`, `update_by`) VALUES (null, NULL, 'query', '查询', '400', b'1', '2020-01-10 16:31:39', '2020-01-10 16:31:39', 'system'); INSERT INTO `operation` (`id`, `native_code`, `code`, `name`, `module_id`, `active`, `create_time`, `update_time`, `update_by`) VALUES (null, NULL, 'add', '新增', '400', b'1', '2020-01-10 16:31:39', '2020-01-10 16:31:39', 'system'); INSERT INTO `operation` (`id`, `native_code`, `code`, `name`, `module_id`, `active`, `create_time`, `update_time`, `update_by`) VALUES (null, NULL, 'edit', '修改', '400', b'1', '2020-01-10 16:31:39', '2020-01-10 16:31:39', 'system'); INSERT INTO`permission` (`role_id`, `module_id`, `operation_id`, `roletype`) select pr.id,o.module_id,o.id,'2' 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.id=400; ##外网权限删除 DELETE from permission where id in ( select t.id from (select p.id from module m, config c,permission p where (m.id=400) 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.id=400) 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.id=400) AND c.active = TRUE AND c.`key` = 'police' AND c.`value` = '0')t ); update sys_dict set label ='武装押运',isActive =true where codeType ='securityScopeType' and codeValue ='6'; drop view if EXISTS v_com_contract; CREATE VIEW `v_com_contract`AS SELECT DISTINCT t.comContractId, contractType, contractStatus, contractName, comFileNo, comParty, firstPartyName, firstPartyTel, secondPartyName, secondPartyTel, effectStartDate, effectEndDate, contractPeriod, agreementSignDate, perId, t.comId, t.remark, ( select GROUP_CONCAT(ct.taskName) FROM com_task_contract ctc INNER JOIN com_task ct on ct.comTaskId=ctc.comTaskId WHERE t.comContractId = ctc.comContractId ) as taskName FROM com_contract t WHERE t.isDelete = '0';