INSERT INTO `iotdb`.`sys_dict_type`(`dict_id`, `dict_name`, `dict_type`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (null, '物联卡排序值', 'yz_card_sortValue', '0', 'admin', '2022-10-24 11:11:30', '', NULL, NULL);
INSERT INTO `iotdb`.`sys_dict_data`(`dict_code`, `dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (null0, '激活时间', '0', 'yz_card_sortValue', NULL, NULL, 'N', '0', 'admin', '2022-10-24 11:11:49', '', NULL, NULL);
INSERT INTO `iotdb`.`sys_dict_data`(`dict_code`, `dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (null, 1, '到期时间', '1', 'yz_card_sortValue', NULL, NULL, 'N', '0', 'admin', '2022-10-24 11:12:01', '', NULL, NULL);
INSERT INTO `iotdb`.`sys_dict_data`(`dict_code`, `dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (null, 2, '用量', '2', 'yz_card_sortValue', NULL, NULL, 'N', '0', 'admin', '2022-10-24 11:12:10', '', NULL, NULL);
INSERT INTO `iotdb`.`sys_dict_data`(`dict_code`, `dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (null, 3, 'MSISDN', '3', 'yz_card_sortValue', NULL, NULL, 'N', '0', 'admin', '2022-10-24 16:04:25', '', NULL, NULL);
INSERT INTO `iotdb`.`sys_dict_data`(`dict_code`, `dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (null, 4, 'ICCID', '4', 'yz_card_sortValue', NULL, NULL, 'N', '0', 'admin', '2022-10-24 16:04:38', '', NULL, NULL);
INSERT INTO `iotdb`.`sys_dict_data`(`dict_code`, `dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (null, 5, 'IMSI', '5', 'yz_card_sortValue', NULL, NULL, 'N', '0', 'admin', '2022-10-24 16:04:50', '', NULL, NULL);
INSERT INTO `iotdb`.`sys_dict_data`(`dict_code`, `dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (null, 6, '虚拟编号', '6', 'yz_card_sortValue', NULL, NULL, 'N', '0', 'admin', '2022-10-24 16:05:14', '', NULL, NULL);
INSERT INTO `iotdb`.`sys_dict_data`(`dict_code`, `dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (null, 7, '同步时间', '7', 'yz_card_sortValue', NULL, NULL, 'N', '0', 'admin', '2022-10-24 16:05:34', '', NULL, NULL);

INSERT INTO `iotdb`.`sys_dict_data`(`dict_code`, `dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (null, 1, '到期时间', '1', 'yz_card_sortValue', NULL, NULL, 'N', '0', 'admin', '2022-10-24 11:12:01', '', NULL, NULL);
INSERT INTO `iotdb`.`sys_dict_data`(`dict_code`, `dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (null, 2, '用量', '2', 'yz_card_sortValue', NULL, NULL, 'N', '0', 'admin', '2022-10-24 11:12:10', '', NULL, NULL);
INSERT INTO `iotdb`.`sys_dict_data`(`dict_code`, `dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (null, 3, 'MSISDN', '3', 'yz_card_sortValue', NULL, NULL, 'N', '0', 'admin', '2022-10-24 16:04:25', '', NULL, NULL);
INSERT INTO `iotdb`.`sys_dict_data`(`dict_code`, `dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (null, 4, 'ICCID', '4', 'yz_card_sortValue', NULL, NULL, 'N', '0', 'admin', '2022-10-24 16:04:38', '', NULL, NULL);
INSERT INTO `iotdb`.`sys_dict_data`(`dict_code`, `dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (null, 5, 'IMSI', '5', 'yz_card_sortValue', NULL, NULL, 'N', '0', 'admin', '2022-10-24 16:04:50', '', NULL, NULL);
INSERT INTO `iotdb`.`sys_dict_data`(`dict_code`, `dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (null, 6, '虚拟编号', '6', 'yz_card_sortValue', NULL, NULL, 'N', '0', 'admin', '2022-10-24 16:05:14', '', NULL, NULL);
INSERT INTO `iotdb`.`sys_dict_data`(`dict_code`, `dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (null, 7, '同步时间', '7', 'yz_card_sortValue', NULL, NULL, 'N', '0', 'admin', '2022-10-24 16:05:34', '', NULL, NULL);
INSERT INTO `iotdb`.`sys_dict_data`(`dict_code`, `dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (null, 0, '升序', '0', 'yz_card_sort', NULL, NULL, 'N', '0', 'admin', '2022-10-24 10:10:26', '', NULL, NULL);
INSERT INTO `iotdb`.`sys_dict_data`(`dict_code`, `dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (null, 1, '降序', '1', 'yz_card_sort', NULL, NULL, 'N', '0', 'admin', '2022-10-24 10:10:35', '', NULL, NULL);
INSERT INTO `iotdb`.`sys_dict_type`(`dict_id`, `dict_name`, `dict_type`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (null, '物联卡查询排序', 'yz_card_sort', '0', 'admin', '2022-10-24 10:10:10', '', NULL, NULL);

ALTER TABLE yz_card_route ADD sync_field varchar(300) DEFAULT NULL COMMENT '自动同步的字段 如 activate_date,open_date';
INSERT INTO `iotdb`.`sys_menu`(`menu_id`, `menu_name`, `parent_id`, `order_num`, `path`, `component`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (null, '上游集团群组', 2012, 3, 'upSteam', 'yunze/API/upStream/index', 1, 0, 'C', '0', '0', 'yunze:upsteam:list', 'peoples', 'admin', '2022-09-28 13:40:05', 'admin', '2022-09-28 13:46:03', '');
DROP TABLE IF EXISTS `yz_syn_card_info`;
CREATE TABLE `yz_syn_card_info`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `msisdn` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '接入号',
  `iccid` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `imsi` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `activate_date` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '激活时间',
  `open_date` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '开卡时间',
  `create_date` datetime(0) NOT NULL COMMENT '创建时间',
  `update_date` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',
  `syn_Time` datetime(0) NULL DEFAULT NULL COMMENT '同步时间',
  `channel_id` int(0) NOT NULL COMMENT '通道编号',
  `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '备注',
  `inconsistent_iccid` smallint(0) NOT NULL DEFAULT 0 COMMENT '是否 与 card_info 表 iccid不一致',
  `is_new` smallint(0) NOT NULL DEFAULT 0 COMMENT '是否 需要新增到 card_info 表',
  `groupId` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '上游成员组id',
  `custId` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '客户id-[DX_CMP]',
  `status_id` smallint(0) NULL DEFAULT NULL COMMENT '卡状态描述 -【DX-CMP】',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `msisdn`(`msisdn`) USING BTREE,
  INDEX `channel_id`(`channel_id`) USING BTREE,
  INDEX `inconsistent_iccid`(`inconsistent_iccid`, `is_new`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 235456 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '上游返回-群组所属成员信息' ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;
DROP TABLE IF EXISTS `yz_upstream_group`;
CREATE TABLE `yz_upstream_group`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `groupId` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '群组ID',
  `groupName` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '群组名称',
  `offeringId` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '群组主资费id',
  `offeringName` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '群组主资费名称',
  `channel_id` int(0) NOT NULL COMMENT '通道编号',
  `create_date` datetime(0) NOT NULL COMMENT '创建时间',
  `upd_date` datetime(0) NOT NULL COMMENT '最近一次修改时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 18 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '上游集团群组' ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;
INSERT INTO `iotdb`.`sys_job`(`job_id`, `job_name`, `job_group`, `invoke_target`, `cron_expression`, `misfire_policy`, `concurrent`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (NULL, '通道组同步成员', 'POLLING', 'synChannelCardInfoTask.SynCardInfo', '45 0 1 * * ? *', '1', '1', '0', 'admin', '2022-09-14 17:32:53', 'admin', '2022-09-27 10:55:43', '');
INSERT INTO `iotdb`.`sys_job`(`job_id`, `job_name`, `job_group`, `invoke_target`, `cron_expression`, `misfire_policy`, `concurrent`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (NULL, '接入号同步iccid数据等', 'POLLING', 'synCardInfoTask.SynCardInfo', '30 0 0/4 * * ?', '1', '1', '0', 'admin', '2022-09-15 16:39:43', 'admin', '2022-09-21 15:08:22', '');
INSERT INTO `iotdb`.`sys_job`(`job_id`, `job_name`, `job_group`, `invoke_target`, `cron_expression`, `misfire_policy`, `concurrent`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (NULL, '对比主表数据进行标记', 'POLLING', 'synCardDataComparisonTask.DataComparison', '20 0 0/4 * * ?', '1', '1', '0', 'admin', '2022-09-21 09:19:58', 'admin', '2022-09-27 11:26:24', '');
INSERT INTO `iotdb`.`sys_job`(`job_id`, `job_name`, `job_group`, `invoke_target`, `cron_expression`, `misfire_policy`, `concurrent`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (NULL, '上游标记数据执行新增', 'DEFAULT', 'cardSynInfoTask.addDetect', '0 0 9 ? * MON-FRI', '1', '1', '0', 'admin', '2022-09-23 10:33:58', 'admin', '2022-09-26 09:03:03', '');
INSERT INTO `iotdb`.`sys_menu`(`menu_id`, `menu_name`, `parent_id`, `order_num`, `path`, `component`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (null, '短信日志', 108, 6, 'smslog', 'yunze/flowcard/smslog/index', 1, 0, 'C', '0', '0', 'yunze:sysLog:list', 'button', 'admin', '2022-09-01 14:38:51', 'admin', '2022-09-01 14:55:21', '');
INSERT INTO `iotdb`.`sys_menu`(`menu_id`, `menu_name`, `parent_id`, `order_num`, `path`, `component`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (null, '短信业务受理', 108, 5, 'smsbatchBusiness', 'yunze/flowcard/smsbatchBusiness/index', 1, 0, 'C', '0', '0', 'yunze:smsbatchBusiness:list', 'message', 'admin', '2022-08-16 12:21:05', '', NULL, '');
DROP TABLE IF EXISTS `yz_busi_card_sms`;
CREATE TABLE `yz_busi_card_sms`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `msgid` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '短息id',
  `msisdn` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '短信服务号',
  `content` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '下发内容',
  `state_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '状态',
  `type` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '操作方式',
  `create_date` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  `del_flag` smallint(5) NOT NULL COMMENT '是否删除 0 否 1 是',
  `sms_src_TerminalId` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '短信服务号',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 16 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '短信收发记录' ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;
INSERT INTO `iotdb`.`sys_job`(`job_id`, `job_name`, `job_group`, `invoke_target`, `cron_expression`, `misfire_policy`, `concurrent`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (null, '同步通道成员组', 'POLLING', 'synUpstreamMemberTask.SynOfferinginfo', '10 0 2 * * ? *', '1', '1', '0', 'admin', '2022-09-14 14:50:03', 'admin', '2022-09-21 10:37:13', '');