-- 申诉表结构
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');

##其它犯罪信息检查
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='其它犯罪信息检查';

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';

