/*
|
Navicat Premium Data Transfer
|
|
Source Server : 元谋阿里云
|
Source Server Type : MySQL
|
Source Server Version : 80034
|
Source Host : 8.130.130.233:3306
|
Source Schema : pipirr_ym
|
|
Target Server Type : MySQL
|
Target Server Version : 80034
|
File Encoding : 65001
|
|
Date: 10/02/2025 09:44:10
|
*/
|
|
SET NAMES utf8mb4;
|
SET FOREIGN_KEY_CHECKS = 0;
|
|
-- ----------------------------
|
-- Table structure for area_code_2023
|
-- ----------------------------
|
DROP TABLE IF EXISTS `area_code_2023`;
|
CREATE TABLE `area_code_2023` (
|
`code` bigint UNSIGNED NOT NULL COMMENT '区划代码',
|
`name` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '名称',
|
`level` tinyint(1) NOT NULL COMMENT '级别1-5,省市县镇村',
|
`pcode` bigint NULL DEFAULT NULL COMMENT '父级区划代码',
|
`category` int NULL DEFAULT NULL COMMENT '城乡分类',
|
PRIMARY KEY (`code`) USING BTREE,
|
INDEX `name`(`name` ASC) USING BTREE,
|
INDEX `level`(`level` ASC) USING BTREE,
|
INDEX `pcode`(`pcode` ASC) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for ba_block
|
-- ----------------------------
|
DROP TABLE IF EXISTS `ba_block`;
|
CREATE TABLE `ba_block` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`name` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '片区名称',
|
`header` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '负责人',
|
`phone` varchar(11) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '负责人电话',
|
`area` int NULL DEFAULT NULL COMMENT '种植面积',
|
`color` varchar(7) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '电子地图区域着色',
|
`remark` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '备注',
|
`deleted` tinyint NULL DEFAULT 0,
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for ba_captcha
|
-- ----------------------------
|
DROP TABLE IF EXISTS `ba_captcha`;
|
CREATE TABLE `ba_captcha` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`token` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '验证码token',
|
`code` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '验证字符',
|
`expiration` bigint NULL DEFAULT NULL COMMENT '过期时间',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '图形验证记录表' ROW_FORMAT = Dynamic;
|
|
-- ----------------------------
|
-- Table structure for ba_client
|
-- ----------------------------
|
DROP TABLE IF EXISTS `ba_client`;
|
CREATE TABLE `ba_client` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`countyId` bigint NULL DEFAULT NULL COMMENT '县ID',
|
`townId` bigint NULL DEFAULT NULL COMMENT '镇ID',
|
`villageId` bigint NULL DEFAULT NULL COMMENT '村ID',
|
`blockId` bigint NULL DEFAULT NULL COMMENT '外键，指向片区',
|
`divideId` bigint NULL DEFAULT NULL COMMENT '外键，指向分水房',
|
`typeId` bigint NULL DEFAULT NULL COMMENT '外键，指向用水户类型',
|
`name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '姓名',
|
`num` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '编号',
|
`phone` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '手机号',
|
`idCard` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '身份证号',
|
`area` double NULL DEFAULT NULL COMMENT '种植面积',
|
`district` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '县名称+镇名称+村名称',
|
`address` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '街道+门牌号',
|
`remark` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '备注',
|
`disabled` tinyint NULL DEFAULT NULL COMMENT '禁止标志',
|
`deleted` tinyint NULL DEFAULT NULL COMMENT '删除标志',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '用水户' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for ba_client_type
|
-- ----------------------------
|
DROP TABLE IF EXISTS `ba_client_type`;
|
CREATE TABLE `ba_client_type` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '用水户类型名称',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '用水户类型' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for ba_dict
|
-- ----------------------------
|
DROP TABLE IF EXISTS `ba_dict`;
|
CREATE TABLE `ba_dict` (
|
`id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
|
`code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '标识',
|
`title` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '名称',
|
`value_type` tinyint NOT NULL COMMENT '值类型;1-Number，2-String，3-Boolean',
|
`hash_code` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT 'hash值;当字典项被修改时变更',
|
`remarks` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '备注',
|
`deleted` bigint NULL DEFAULT 0 COMMENT '逻辑删除标识;未删除为 0，已删除为删除时间',
|
`create_by` int NULL DEFAULT NULL COMMENT '创建人',
|
`update_by` int NULL DEFAULT NULL COMMENT '修改人',
|
`create_time` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
`update_time` datetime NULL DEFAULT NULL COMMENT '修改时间',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '字典表' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for ba_dict_item
|
-- ----------------------------
|
DROP TABLE IF EXISTS `ba_dict_item`;
|
CREATE TABLE `ba_dict_item` (
|
`id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
|
`dict_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '字典Code',
|
`value` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '数据值',
|
`name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '标签',
|
`status` tinyint NOT NULL DEFAULT 1 COMMENT '状态;1-启用，0-禁用',
|
`attributes` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '附加属性',
|
`sort` int NOT NULL DEFAULT 0 COMMENT '排序（升序）',
|
`remarks` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '备注',
|
`deleted` bigint NULL DEFAULT 0 COMMENT '逻辑删除标识;未删除为 0，已删除为删除时间',
|
`create_by` int NULL DEFAULT NULL COMMENT '创建人',
|
`update_by` int NULL DEFAULT NULL COMMENT '修改人',
|
`create_time` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
`update_time` datetime NULL DEFAULT NULL COMMENT '修改时间',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '字典项' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for ba_district
|
-- ----------------------------
|
DROP TABLE IF EXISTS `ba_district`;
|
CREATE TABLE `ba_district` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`supperId` bigint NULL DEFAULT NULL COMMENT '指向上级行政区',
|
`name` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '行政区名称',
|
`num` varchar(5) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '行政区编号',
|
`level` tinyint NULL DEFAULT NULL COMMENT '行政区级别',
|
`deleted` tinyint NULL DEFAULT 0 COMMENT '删除标志',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for ba_divide
|
-- ----------------------------
|
DROP TABLE IF EXISTS `ba_divide`;
|
CREATE TABLE `ba_divide` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`countyId` bigint NULL DEFAULT NULL COMMENT '外键，指向所在县（归属地）',
|
`townId` bigint NULL DEFAULT NULL COMMENT '外键，指向所在镇（归属地）',
|
`villageId` bigint NULL DEFAULT NULL COMMENT '外键，指向 所在村（归属地）',
|
`blockId` bigint NULL DEFAULT NULL COMMENT '外键，指向片区',
|
`name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '分水口名称或编号',
|
`villages` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '覆盖村',
|
`area` float(10, 2) NULL DEFAULT NULL COMMENT '覆盖面积（平方公里）',
|
`header` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '负责人',
|
`phone` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '联系电话',
|
`lng` double NULL DEFAULT NULL COMMENT '经度',
|
`lat` double NULL DEFAULT NULL COMMENT '纬度',
|
`remark` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '备注',
|
`deleted` tinyint NULL DEFAULT NULL COMMENT '删除标志',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for ba_map_coordinates
|
-- ----------------------------
|
DROP TABLE IF EXISTS `ba_map_coordinates`;
|
CREATE TABLE `ba_map_coordinates` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`graph_id` bigint NOT NULL COMMENT '图形ID',
|
`lat` decimal(18, 15) NULL DEFAULT NULL COMMENT '纬度',
|
`lng` decimal(18, 15) NULL DEFAULT NULL COMMENT '经度',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '地图图形坐标' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for ba_map_graph
|
-- ----------------------------
|
DROP TABLE IF EXISTS `ba_map_graph`;
|
CREATE TABLE `ba_map_graph` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`block_id` bigint NOT NULL COMMENT '片区ID',
|
`graph_type` tinyint NOT NULL DEFAULT 1 COMMENT '图形类型;1-CircleMarker ，2-Polygon ，3-Polyline',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '地图图形表' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for ba_role
|
-- ----------------------------
|
DROP TABLE IF EXISTS `ba_role`;
|
CREATE TABLE `ba_role` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '角色名称',
|
`operator` bigint NULL DEFAULT NULL COMMENT '操作人编号',
|
`operate_dt` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '操作时间',
|
`deleted` tinyint NULL DEFAULT 0 COMMENT '逻辑删除标识;0-未删除，1-删除',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '系统角色表' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for ba_role_permissions
|
-- ----------------------------
|
DROP TABLE IF EXISTS `ba_role_permissions`;
|
CREATE TABLE `ba_role_permissions` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`roleId` bigint NOT NULL COMMENT '角色编号',
|
`permissions` json NOT NULL COMMENT '权限列表',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '角色权限关联表' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for ba_settings
|
-- ----------------------------
|
DROP TABLE IF EXISTS `ba_settings`;
|
CREATE TABLE `ba_settings` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`item_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '配置项',
|
`item_value` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '配置项值',
|
`remarks` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '备注信息',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '系统配置表' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for ba_user
|
-- ----------------------------
|
DROP TABLE IF EXISTS `ba_user`;
|
CREATE TABLE `ba_user` (
|
`id` bigint NOT NULL,
|
`blockId` bigint NULL DEFAULT NULL COMMENT '外键，指向片区实体',
|
`name` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '用户姓名',
|
`phone` varchar(11) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '手机号，用于登录系统',
|
`password` varchar(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '密码，md5加密',
|
`orgTag` varchar(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '用户所有机构标签',
|
`supperAdmin` tinyint NULL DEFAULT NULL COMMENT '是否超级管理员，1是，0否',
|
`disabled` tinyint NULL DEFAULT NULL COMMENT '是否禁用，1是，0否',
|
`deleted` tinyint NULL DEFAULT NULL COMMENT '删除标志，1是，0否',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for ba_user_role
|
-- ----------------------------
|
DROP TABLE IF EXISTS `ba_user_role`;
|
CREATE TABLE `ba_user_role` (
|
`userId` bigint NOT NULL,
|
`roleId` bigint NOT NULL,
|
PRIMARY KEY (`userId`, `roleId`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for ir_crop
|
-- ----------------------------
|
DROP TABLE IF EXISTS `ir_crop`;
|
CREATE TABLE `ir_crop` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`crop_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '作物名称',
|
`remarks` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '备注信息',
|
`deleted` tinyint NULL DEFAULT 0 COMMENT '逻辑删除标识;0-未删除，1-删除',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '作物表' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for ir_group_client
|
-- ----------------------------
|
DROP TABLE IF EXISTS `ir_group_client`;
|
CREATE TABLE `ir_group_client` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`group_id` bigint NOT NULL COMMENT '轮灌组ID',
|
`client_id` bigint NOT NULL COMMENT '农户ID',
|
`operator` bigint NULL DEFAULT NULL COMMENT '创建人',
|
`operate_time` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '轮灌组农户关联表' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for ir_group_unit
|
-- ----------------------------
|
DROP TABLE IF EXISTS `ir_group_unit`;
|
CREATE TABLE `ir_group_unit` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`group_id` bigint NOT NULL COMMENT '轮灌组编号',
|
`unit_id` bigint NOT NULL COMMENT '灌溉单元编号',
|
`operator` bigint NOT NULL COMMENT '操作人',
|
`operate_time` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '操作时间',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '轮灌组灌溉单元关联表' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for ir_irrigate_group
|
-- ----------------------------
|
DROP TABLE IF EXISTS `ir_irrigate_group`;
|
CREATE TABLE `ir_irrigate_group` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`group_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '轮灌组编码',
|
`project_id` bigint NOT NULL COMMENT '项目编号',
|
`remarks` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '备注信息',
|
`operator` bigint NOT NULL COMMENT '操作人',
|
`operate_time` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '操作时间',
|
`deleted` tinyint NULL DEFAULT 0 COMMENT '逻辑删除标识;0-未删除，1-删除',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '轮灌组表' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for ir_irrigate_plan
|
-- ----------------------------
|
DROP TABLE IF EXISTS `ir_irrigate_plan`;
|
CREATE TABLE `ir_irrigate_plan` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`project_id` bigint NULL DEFAULT NULL COMMENT '项目ID',
|
`plan_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '计划名称',
|
`startup_mode` tinyint NULL DEFAULT 1 COMMENT '计划启动模式;1-手动启动，2-自动启动',
|
`plan_start_time` datetime NULL DEFAULT NULL COMMENT '灌溉开始时间;精确到分',
|
`plan_stop_time` datetime NULL DEFAULT NULL COMMENT '灌溉结束时间;精确到分',
|
`plan_state` tinyint NULL DEFAULT 1 COMMENT '计划状态;1-草稿，2-已发布',
|
`executing_state` tinyint NULL DEFAULT 1 COMMENT '执行状态;1-未执行，2-执行中，3-已暂停，4-已终止，5-已完成',
|
`deleted` bigint NULL DEFAULT 0 COMMENT '逻辑删除标识;未删除为0，已删除为删除时间',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '灌溉计划表' ROW_FORMAT = Dynamic;
|
|
-- ----------------------------
|
-- Table structure for ir_irrigate_schedule
|
-- ----------------------------
|
DROP TABLE IF EXISTS `ir_irrigate_schedule`;
|
CREATE TABLE `ir_irrigate_schedule` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`plan_id` bigint NOT NULL COMMENT '灌溉计划ID',
|
`group_id` bigint NOT NULL COMMENT '轮灌组ID',
|
`start_time` datetime NOT NULL COMMENT '灌溉开始时间;精确到分',
|
`stop_time` datetime NOT NULL COMMENT '灌溉结束时间;精确到分',
|
`intake_ids` json NULL COMMENT '取水口ID列表',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '灌溉次序表' ROW_FORMAT = Dynamic;
|
|
-- ----------------------------
|
-- Table structure for ir_irrigate_scheduling
|
-- ----------------------------
|
DROP TABLE IF EXISTS `ir_irrigate_scheduling`;
|
CREATE TABLE `ir_irrigate_scheduling` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`crop_id` bigint NOT NULL COMMENT '作物编号',
|
`growth_period` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '作物生长期',
|
`irrigate_cycle` int NOT NULL COMMENT '灌水周期',
|
`duration` int NOT NULL COMMENT '灌水延续时间',
|
`remarks` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '备注信息',
|
`operator` bigint NOT NULL COMMENT '操作人',
|
`operate_time` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '操作时间',
|
`deleted` tinyint NULL DEFAULT 0 COMMENT '逻辑删除标识;0-未删除，1-删除',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '灌溉制度表' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for ir_irrigate_unit
|
-- ----------------------------
|
DROP TABLE IF EXISTS `ir_irrigate_unit`;
|
CREATE TABLE `ir_irrigate_unit` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`project_id` bigint NOT NULL COMMENT '项目ID',
|
`intake_id` bigint NULL DEFAULT NULL COMMENT '取水口ID',
|
`area` float(10, 2) NULL DEFAULT NULL COMMENT '灌溉面积',
|
`remarks` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '备注信息',
|
`operator` bigint NOT NULL COMMENT '操作人',
|
`operate_time` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '操作时间',
|
`deleted` tinyint NULL DEFAULT 0 COMMENT '逻辑删除标识;0-未删除，1-删除',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '灌溉单元表' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for ir_opening_schedule
|
-- ----------------------------
|
DROP TABLE IF EXISTS `ir_opening_schedule`;
|
CREATE TABLE `ir_opening_schedule` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`schedule_id` bigint NOT NULL COMMENT '灌溉次序ID',
|
`start_time` datetime NOT NULL COMMENT '开阀时间;精确到分',
|
`duration` int NOT NULL COMMENT '灌溉时长;分钟',
|
`intake_nums` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '取水口编号列表',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '开阀计划表' ROW_FORMAT = Dynamic;
|
|
-- ----------------------------
|
-- Table structure for ir_plan_operate
|
-- ----------------------------
|
DROP TABLE IF EXISTS `ir_plan_operate`;
|
CREATE TABLE `ir_plan_operate` (
|
`update_time` datetime NULL DEFAULT NULL COMMENT '更新时间',
|
`id` bigint NOT NULL COMMENT '主键',
|
`plan_id` bigint NOT NULL COMMENT '灌溉计划ID',
|
`operator` bigint NOT NULL COMMENT '操作人编号',
|
`operate_type` tinyint NULL DEFAULT NULL COMMENT '操作类型;1-创建，2-发布，3-执行，4-暂停，5-终止，6-完成',
|
`operate_time` datetime NULL DEFAULT NULL COMMENT '操作时间',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '灌溉计划操作表' ROW_FORMAT = Dynamic;
|
|
-- ----------------------------
|
-- Table structure for ir_plan_pause
|
-- ----------------------------
|
DROP TABLE IF EXISTS `ir_plan_pause`;
|
CREATE TABLE `ir_plan_pause` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`schedule_id` bigint NOT NULL COMMENT '灌溉次序ID',
|
`operator` bigint NOT NULL COMMENT '操作人编号',
|
`pause_time` datetime NOT NULL COMMENT '暂停时间;精确到分',
|
`renew_time` datetime NULL DEFAULT NULL COMMENT '恢复时间;精确到分',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '计划暂停记录表' ROW_FORMAT = Dynamic;
|
|
-- ----------------------------
|
-- Table structure for ir_plan_terminate
|
-- ----------------------------
|
DROP TABLE IF EXISTS `ir_plan_terminate`;
|
CREATE TABLE `ir_plan_terminate` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`schedule_id` bigint NOT NULL COMMENT '灌溉次序ID',
|
`operator` bigint NOT NULL COMMENT '操作人编号',
|
`terminate_time` datetime NOT NULL COMMENT '终止时间;精确到分',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '计划终止记录表' ROW_FORMAT = Dynamic;
|
|
-- ----------------------------
|
-- Table structure for ir_project
|
-- ----------------------------
|
DROP TABLE IF EXISTS `ir_project`;
|
CREATE TABLE `ir_project` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`project_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '项目名称',
|
`province_id` bigint NULL DEFAULT NULL COMMENT '省ID',
|
`city_id` bigint NULL DEFAULT NULL COMMENT '市ID',
|
`county_id` bigint NULL DEFAULT NULL COMMENT '县ID',
|
`town_id` bigint NULL DEFAULT NULL COMMENT '镇ID',
|
`village_id` bigint NULL DEFAULT NULL COMMENT '村ID',
|
`project_state` tinyint NULL DEFAULT 1 COMMENT '项目状态;1-启用，2-废弃',
|
`remarks` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '备注信息',
|
`operator` bigint NOT NULL COMMENT '操作人',
|
`operate_time` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '操作时间',
|
`deleted` tinyint NULL DEFAULT 0 COMMENT '逻辑删除标识;0-未删除，1-删除',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '项目表' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for ir_unit_client
|
-- ----------------------------
|
DROP TABLE IF EXISTS `ir_unit_client`;
|
CREATE TABLE `ir_unit_client` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`unit_id` bigint NOT NULL COMMENT '灌溉单元ID',
|
`client_id` bigint NOT NULL COMMENT '农户ID',
|
`operator` bigint NULL DEFAULT NULL COMMENT '创建人',
|
`operate_time` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '灌溉单元农户关联表' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for ope_approve_result
|
-- ----------------------------
|
DROP TABLE IF EXISTS `ope_approve_result`;
|
CREATE TABLE `ope_approve_result` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`work_order_id` bigint NOT NULL COMMENT '工单ID',
|
`processing_result_id` bigint NOT NULL COMMENT '处理结果ID',
|
`approver_id` bigint NULL DEFAULT NULL COMMENT '审核人ID',
|
`approve_time` datetime NULL DEFAULT NULL COMMENT '审核时间',
|
`approve_result` tinyint NULL DEFAULT NULL COMMENT '审核结果;1-通过，2-驳回',
|
`deleted` bigint NULL DEFAULT 0 COMMENT '逻辑删除标识;未删除为0，已删除为删除时间',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '处理结果审核记录' ROW_FORMAT = Dynamic;
|
|
-- ----------------------------
|
-- Table structure for ope_inspect
|
-- ----------------------------
|
DROP TABLE IF EXISTS `ope_inspect`;
|
CREATE TABLE `ope_inspect` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`inspector_id` bigint NOT NULL COMMENT '巡检员ID',
|
`start_time` datetime NULL DEFAULT NULL COMMENT '开始巡检时间',
|
`stop_time` datetime NULL DEFAULT NULL COMMENT '结束巡检时间',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '巡检表' ROW_FORMAT = Dynamic;
|
|
-- ----------------------------
|
-- Table structure for ope_issue_report
|
-- ----------------------------
|
DROP TABLE IF EXISTS `ope_issue_report`;
|
CREATE TABLE `ope_issue_report` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`content` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '问题内容',
|
`images` json NULL COMMENT '照片列表',
|
`audios` json NULL COMMENT '音频列表',
|
`videos` json NULL COMMENT '视频列表',
|
`lng` decimal(18, 15) NULL DEFAULT NULL COMMENT '经度',
|
`lat` decimal(18, 15) NULL DEFAULT NULL COMMENT '纬度',
|
`inspector_id` bigint NULL DEFAULT NULL COMMENT '巡检员ID',
|
`report_time` datetime NULL DEFAULT NULL COMMENT '上报时间',
|
`phone` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '联系电话',
|
`state` tinyint NULL DEFAULT NULL COMMENT '状态;1-未受理，2-已受理，3已删除',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '问题上报记录表' ROW_FORMAT = Dynamic;
|
|
-- ----------------------------
|
-- Table structure for ope_processing_result
|
-- ----------------------------
|
DROP TABLE IF EXISTS `ope_processing_result`;
|
CREATE TABLE `ope_processing_result` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`work_order_id` bigint NOT NULL COMMENT '工单ID',
|
`inspector_id` bigint NOT NULL COMMENT '巡检员ID',
|
`content` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '结果内容',
|
`images` json NULL COMMENT '照片列表',
|
`audios` json NULL COMMENT '音频列表',
|
`videos` json NULL COMMENT '视频列表',
|
`lng` decimal(18, 15) NULL DEFAULT NULL COMMENT '经度',
|
`lat` decimal(18, 15) NULL DEFAULT NULL COMMENT '纬度',
|
`complete_time` datetime NULL DEFAULT NULL COMMENT '任务完成时间;精确到分',
|
`report_time` datetime NULL DEFAULT NULL COMMENT '上报时间',
|
`state` tinyint NULL DEFAULT NULL COMMENT '状态;1-已上报，2-已通过，3-已驳回',
|
`deleted` bigint NULL DEFAULT 0 COMMENT '逻辑删除标识;未删除为0，已删除为删除时间',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '处理结果记录表' ROW_FORMAT = Dynamic;
|
|
-- ----------------------------
|
-- Table structure for ope_report_reply
|
-- ----------------------------
|
DROP TABLE IF EXISTS `ope_report_reply`;
|
CREATE TABLE `ope_report_reply` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`report_id` bigint NOT NULL COMMENT '问题上报ID',
|
`reply_content` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '回复内容',
|
`reply_time` datetime NULL DEFAULT NULL COMMENT '回复时间',
|
`replier_id` bigint NOT NULL COMMENT '回复人ID',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '上报回复记录表' ROW_FORMAT = Dynamic;
|
|
-- ----------------------------
|
-- Table structure for ope_task_type
|
-- ----------------------------
|
DROP TABLE IF EXISTS `ope_task_type`;
|
CREATE TABLE `ope_task_type` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`task_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '任务类型',
|
`deleted` bigint NULL DEFAULT 0 COMMENT '逻辑删除标识;未删除为0，已删除为删除时间',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '任务类型表' ROW_FORMAT = Dynamic;
|
|
-- ----------------------------
|
-- Table structure for ope_track
|
-- ----------------------------
|
DROP TABLE IF EXISTS `ope_track`;
|
CREATE TABLE `ope_track` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`inspect_id` bigint NULL DEFAULT NULL COMMENT '巡检ID',
|
`lng` decimal(18, 15) NULL DEFAULT NULL COMMENT '经度',
|
`lat` decimal(18, 15) NULL DEFAULT NULL COMMENT '纬度',
|
`locate_time` datetime NULL DEFAULT NULL COMMENT '打点时间',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '巡检轨迹表' ROW_FORMAT = Dynamic;
|
|
-- ----------------------------
|
-- Table structure for ope_work_order
|
-- ----------------------------
|
DROP TABLE IF EXISTS `ope_work_order`;
|
CREATE TABLE `ope_work_order` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`dispatcher_id` bigint NOT NULL COMMENT '派单人ID',
|
`inspector_id` bigint NOT NULL COMMENT '巡检员ID',
|
`task_type_id` bigint NOT NULL DEFAULT 4 COMMENT '任务类型ID',
|
`task_content` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '任务内容',
|
`complete_criteria` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '完成标准',
|
`dead_line` datetime NULL DEFAULT NULL COMMENT '要求完成时间;精确到分',
|
`complete_time` datetime NULL DEFAULT NULL COMMENT '任务完成时间;精确到分',
|
`dispatch_time` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '派单时间',
|
`client_report_id` bigint NULL DEFAULT NULL COMMENT '农户问题上报ID',
|
`inspector_report_id` bigint NULL DEFAULT NULL COMMENT '巡检员问题上报ID',
|
`state` tinyint NULL DEFAULT NULL COMMENT '状态;1-未完成，2-已完成',
|
`reject_times` int NULL DEFAULT 0 COMMENT '驳回次数',
|
`deleted` bigint NOT NULL DEFAULT 0 COMMENT '逻辑删除标识;未删除为0，已删除为删除时间',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '工单表' ROW_FORMAT = Dynamic;
|
|
-- ----------------------------
|
-- Table structure for pr_common_intakes
|
-- ----------------------------
|
DROP TABLE IF EXISTS `pr_common_intakes`;
|
CREATE TABLE `pr_common_intakes` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`operator_id` bigint NOT NULL COMMENT '操作人ID',
|
`intake_id` bigint NOT NULL COMMENT '取水口ID',
|
`last_used_time` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '最后一次使用时间',
|
`usage_count` int NULL DEFAULT 0 COMMENT '使用次数',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '常用取水口' ROW_FORMAT = Dynamic;
|
|
-- ----------------------------
|
-- Table structure for pr_controller
|
-- ----------------------------
|
DROP TABLE IF EXISTS `pr_controller`;
|
CREATE TABLE `pr_controller` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`intakeId` bigint NOT NULL COMMENT '取水口ID',
|
`rtuAddr` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '控制器地址',
|
`protocol` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '通讯协议',
|
`protocol_version` int NULL DEFAULT NULL COMMENT '协议版本号',
|
`findDt` datetime NULL DEFAULT NULL COMMENT '发现日期时间',
|
`orgTag` varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '机构tag',
|
`addWays` tinyint NULL DEFAULT 1 COMMENT '添加方式;1-系统自动，2-手动',
|
`operator` bigint NULL DEFAULT NULL COMMENT '操作人编号',
|
`operateDt` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '操作时间',
|
`deleted` tinyint NULL DEFAULT 0 COMMENT '逻辑删除标识;0-未删除，1-删除',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '控制器表' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for pr_controller_tramp
|
-- ----------------------------
|
DROP TABLE IF EXISTS `pr_controller_tramp`;
|
CREATE TABLE `pr_controller_tramp` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`rtuAddr` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '控制器地址',
|
`protocol` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '通信协议',
|
`protocol_version` int NULL DEFAULT NULL COMMENT '通信协议版本号',
|
`findDt` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '发现日期时间',
|
`orgTag` varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '机构tag',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '流浪控制器' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for pr_divide
|
-- ----------------------------
|
DROP TABLE IF EXISTS `pr_divide`;
|
CREATE TABLE `pr_divide` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`countyId` bigint NULL DEFAULT NULL COMMENT '所在县',
|
`townId` bigint NULL DEFAULT NULL COMMENT '所在镇',
|
`villageId` bigint NULL DEFAULT NULL COMMENT '所在村',
|
`blockId` bigint NOT NULL COMMENT '所属片区',
|
`name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '分水口名称或编号',
|
`villages` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '覆盖村',
|
`area` float(10, 2) NULL DEFAULT NULL COMMENT '覆盖面积（平方公里）',
|
`header` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '负责人',
|
`phone` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '联系电话',
|
`lng` double NOT NULL COMMENT '经度',
|
`lat` double NOT NULL COMMENT '纬度',
|
`remarks` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '备注信息',
|
`operator` bigint NOT NULL COMMENT '操作人编号',
|
`operateDt` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '操作时间',
|
`deleted` tinyint NULL DEFAULT 0 COMMENT '逻辑删除标识;0-未删除，1-删除',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '分水房表' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for pr_flow_monitoring
|
-- ----------------------------
|
DROP TABLE IF EXISTS `pr_flow_monitoring`;
|
CREATE TABLE `pr_flow_monitoring` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`countyId` bigint NULL DEFAULT NULL COMMENT '所在县',
|
`townId` bigint NULL DEFAULT NULL COMMENT '所在镇',
|
`villageId` bigint NULL DEFAULT NULL COMMENT '所在村',
|
`name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '监测站名称',
|
`lng` double NOT NULL COMMENT '经度',
|
`lat` double NOT NULL COMMENT '纬度',
|
`remarks` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '备注信息',
|
`operator` bigint NOT NULL COMMENT '操作人编号',
|
`operateDt` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '操作时间',
|
`deleted` tinyint NULL DEFAULT 0 COMMENT '逻辑删除标识;0-未删除，1-删除',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '流量监测站表' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for pr_flowmeter
|
-- ----------------------------
|
DROP TABLE IF EXISTS `pr_flowmeter`;
|
CREATE TABLE `pr_flowmeter` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`monitoringId` bigint NULL DEFAULT NULL COMMENT '监测站ID',
|
`code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '流量计编号',
|
`protocol` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '通讯协议',
|
`onlineState` tinyint NULL DEFAULT 2 COMMENT '在线状态;1-在线，2-离线',
|
`reportTime` datetime NULL DEFAULT NULL COMMENT '最近上报时间',
|
`addWays` tinyint NULL DEFAULT 1 COMMENT '添加方式;1-系统自动，2-手动',
|
`operator` bigint NULL DEFAULT NULL COMMENT '操作人编号',
|
`operateDt` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '操作时间',
|
`remarks` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '备注信息',
|
`deleted` tinyint NULL DEFAULT 0 COMMENT '逻辑删除标识;0-未删除，1-删除',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '流量计表' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for pr_intake
|
-- ----------------------------
|
DROP TABLE IF EXISTS `pr_intake`;
|
CREATE TABLE `pr_intake` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`countyId` bigint NULL DEFAULT NULL COMMENT '所在县',
|
`townId` bigint NULL DEFAULT NULL COMMENT '所在镇',
|
`villageId` bigint NULL DEFAULT NULL COMMENT '所在村',
|
`divideId` bigint NOT NULL COMMENT '分水口编号',
|
`blockId` bigint NOT NULL COMMENT '片区编号',
|
`name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '取水口名称',
|
`lng` double NULL DEFAULT NULL COMMENT '经度',
|
`lat` double NULL DEFAULT NULL COMMENT '纬度',
|
`remarks` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '备注信息',
|
`operator` bigint NULL DEFAULT NULL COMMENT '操作人编号',
|
`operateDt` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '操作时间',
|
`deleted` tinyint NULL DEFAULT 0 COMMENT '逻辑删除标识;0-未删除，1-删除',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '取水口表' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for pr_intake_controller
|
-- ----------------------------
|
DROP TABLE IF EXISTS `pr_intake_controller`;
|
CREATE TABLE `pr_intake_controller` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`intakeId` bigint NOT NULL COMMENT '取水口编号',
|
`controllerId` bigint NOT NULL COMMENT '控制器编号',
|
`operateType` tinyint NULL DEFAULT 1 COMMENT '操作类型;1-捆绑，2-解绑',
|
`remarks` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '备注信息',
|
`operator` bigint NOT NULL COMMENT '操作人编号',
|
`operateDt` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '操作时间',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '取水口捆绑表' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for pr_intake_vc
|
-- ----------------------------
|
DROP TABLE IF EXISTS `pr_intake_vc`;
|
CREATE TABLE `pr_intake_vc` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`intake_id` bigint NOT NULL COMMENT '取水口ID',
|
`vc_id` bigint NOT NULL COMMENT '虚拟卡ID',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '取水口虚拟卡绑定表' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for pr_monitoring_flowmeter
|
-- ----------------------------
|
DROP TABLE IF EXISTS `pr_monitoring_flowmeter`;
|
CREATE TABLE `pr_monitoring_flowmeter` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`monitoringId` bigint NOT NULL COMMENT '监测站编号',
|
`flowmeterId` bigint NOT NULL COMMENT '流量计编号',
|
`operateType` tinyint NULL DEFAULT 1 COMMENT '操作类型;1-捆绑，2-解绑',
|
`remarks` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '备注信息',
|
`operator` bigint NOT NULL COMMENT '操作人编号',
|
`operateDt` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '操作时间',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '监测站流量计关联表' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for pr_network_coordinates
|
-- ----------------------------
|
DROP TABLE IF EXISTS `pr_network_coordinates`;
|
CREATE TABLE `pr_network_coordinates` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`network_id` bigint NOT NULL COMMENT '管网ID',
|
`lat` decimal(18, 15) NULL DEFAULT NULL COMMENT '纬度',
|
`lng` decimal(18, 15) NULL DEFAULT NULL COMMENT '经度',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '管网坐标表' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for pr_scheduling_params
|
-- ----------------------------
|
DROP TABLE IF EXISTS `pr_scheduling_params`;
|
CREATE TABLE `pr_scheduling_params` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`utilization_coefficient` float(2, 1) NULL DEFAULT NULL COMMENT '灌溉水利用系数',
|
`percentage_soil_wetted` float(5, 4) NULL DEFAULT NULL COMMENT '土壤湿润比',
|
`wetted_depth` int NULL DEFAULT NULL COMMENT '计划湿润层深度（厘米）',
|
`soil_bulk_density` float(5, 4) NULL DEFAULT NULL COMMENT '土壤容重（克/立方厘米）',
|
`field_capacity` float(5, 4) NULL DEFAULT NULL COMMENT '田间持水量',
|
`soil_moisture_upper` float(5, 4) NULL DEFAULT NULL COMMENT '土壤适宜含水率上限',
|
`soil_moisture_lower` float(5, 4) NULL DEFAULT NULL COMMENT '土壤适宜含水率下限',
|
`designed_discharge` float(5, 1) NULL DEFAULT NULL COMMENT '灌水器设计流量（升/小时）',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '灌溉制度设计参数表' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for pr_water_network
|
-- ----------------------------
|
DROP TABLE IF EXISTS `pr_water_network`;
|
CREATE TABLE `pr_water_network` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`network_type` tinyint NOT NULL COMMENT '管网类型;1-输水，2-配水',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '输配水管网表' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for pr_water_price
|
-- ----------------------------
|
DROP TABLE IF EXISTS `pr_water_price`;
|
CREATE TABLE `pr_water_price` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`price` float(5, 2) NOT NULL COMMENT '水价',
|
`project_id` bigint NULL DEFAULT NULL COMMENT '项目编号',
|
`operator` bigint NULL DEFAULT NULL COMMENT '操作人编号',
|
`operate_time` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '操作时间',
|
`deleted` tinyint NULL DEFAULT 0 COMMENT '逻辑删除标识;0-未删除，1-删除',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '水价表' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for rm_alarm_state_history
|
-- ----------------------------
|
DROP TABLE IF EXISTS `rm_alarm_state_history`;
|
CREATE TABLE `rm_alarm_state_history` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`controller_id` bigint NULL DEFAULT NULL COMMENT '控制器实体ID（外键）',
|
`intake_id` bigint NULL DEFAULT NULL COMMENT '取水口ID（外键）',
|
`rtu_addr` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '控制器地址',
|
`dt` datetime NULL DEFAULT NULL COMMENT '上报日期时间',
|
`rtu_dt` datetime NULL DEFAULT NULL COMMENT '控制器时钟',
|
`valve_state` tinyint NULL DEFAULT NULL COMMENT '状态-阀门/泵（0打开、1关闭）',
|
`alarm_remain_water` tinyint NULL DEFAULT NULL COMMENT '剩余水量小于报警上限报警报警（1：报警， 0：正常）',
|
`alarm0_water_remain` tinyint NULL DEFAULT NULL COMMENT '剩余水量为0关泵/阀(1：关泵，0：正常)',
|
`alarm_exceed_year` tinyint NULL DEFAULT NULL COMMENT '年用水量大于限制水量报警(1：报警 0：正常)',
|
`alarm_water_meter_fault` tinyint NULL DEFAULT NULL COMMENT '流量计故障(1：报警，0：正常)',
|
`alarm_loss` tinyint NULL DEFAULT NULL COMMENT '漏损(偷水)报警(1：报警， 0：正常)',
|
`alarm_water_meter_break` tinyint NULL DEFAULT NULL COMMENT '流量计损坏(正常通讯但瞬时为0)报警(1：报警，0：正常)',
|
`alarm_ele_meter_fault` tinyint NULL DEFAULT NULL COMMENT '电表故障报警(1：报警，0：正常)',
|
`alarm0_money_remain` tinyint NULL DEFAULT NULL COMMENT '剩余金额为0(1：报警，0：正常)',
|
`alarm_inner_door` tinyint NULL DEFAULT NULL COMMENT '控制器内门报警(1：报警，0：正常)',
|
`alarm_outer_door` tinyint NULL DEFAULT NULL COMMENT '控制器外门打开报警(1：报警，0：正常)',
|
`alarm_ele_miss` tinyint NULL DEFAULT NULL COMMENT '交流缺项报警(1：报警，0：正常)',
|
`alarm_ele_exceed` tinyint NULL DEFAULT NULL COMMENT '交流过流报警(1：报警，0：正常)',
|
`alarm_ele_low_volt` tinyint NULL DEFAULT NULL COMMENT '三相电欠压报警(1：报警，0：正常)',
|
`state_ic_enable` tinyint NULL DEFAULT NULL COMMENT 'IC卡状态(1、有效，0：无效)',
|
`alarm_battery_volt` tinyint NULL DEFAULT NULL COMMENT '电池电压报警（1：报警，0：正常）',
|
`alarm_valve` tinyint NULL DEFAULT NULL COMMENT '阀门报警（1：报警，0：正常）',
|
`power_type` tinyint NULL DEFAULT NULL COMMENT '供电方式（0: 220V，1：蓄电池）',
|
PRIMARY KEY (`id`) USING BTREE,
|
UNIQUE INDEX `rm_alarm_state_history_id_uindex`(`id` ASC) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '控制器报警与状态（历史）' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for rm_alarm_state_last
|
-- ----------------------------
|
DROP TABLE IF EXISTS `rm_alarm_state_last`;
|
CREATE TABLE `rm_alarm_state_last` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`controller_id` bigint NULL DEFAULT NULL COMMENT '控制器实体ID（外键）',
|
`intake_id` bigint NULL DEFAULT NULL COMMENT '取水口ID（外键）',
|
`rtu_addr` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '控制器地址',
|
`dt` datetime NULL DEFAULT NULL COMMENT '上报日期时间',
|
`rtu_dt` datetime NULL DEFAULT NULL COMMENT '控制器时钟',
|
`valve_state` tinyint NULL DEFAULT NULL COMMENT '状态-阀门/泵（0打开、1关闭）',
|
`alarm_remain_water` tinyint NULL DEFAULT NULL COMMENT '剩余水量小于报警上限报警报警（1：报警， 0：正常）',
|
`alarm0_water_remain` tinyint NULL DEFAULT NULL COMMENT '剩余水量为0关泵/阀(1：关泵，0：正常)',
|
`alarm_exceed_year` tinyint NULL DEFAULT NULL COMMENT '年用水量大于限制水量报警(1：报警 0：正常)',
|
`alarm_water_meter_fault` tinyint NULL DEFAULT NULL COMMENT '流量计故障(1：报警，0：正常)',
|
`alarm_loss` tinyint NULL DEFAULT NULL COMMENT '漏损(偷水)报警(1：报警， 0：正常)',
|
`alarm_water_meter_break` tinyint NULL DEFAULT NULL COMMENT '流量计损坏(正常通讯但瞬时为0)报警(1：报警，0：正常)',
|
`alarm_ele_meter_fault` tinyint NULL DEFAULT NULL COMMENT '电表故障报警(1：报警，0：正常)',
|
`alarm0_money_remain` tinyint NULL DEFAULT NULL COMMENT '剩余金额为0(1：报警，0：正常)',
|
`alarm_inner_door` tinyint NULL DEFAULT NULL COMMENT '控制器内门报警(1：报警，0：正常)',
|
`alarm_outer_door` tinyint NULL DEFAULT NULL COMMENT '控制器外门打开报警(1：报警，0：正常)',
|
`alarm_ele_miss` tinyint NULL DEFAULT NULL COMMENT '交流缺项报警(1：报警，0：正常)',
|
`alarm_ele_exceed` tinyint NULL DEFAULT NULL COMMENT '交流过流报警(1：报警，0：正常)',
|
`alarm_ele_low_volt` tinyint NULL DEFAULT NULL COMMENT '三相电欠压报警(1：报警，0：正常)',
|
`state_ic_enable` tinyint NULL DEFAULT NULL COMMENT 'IC卡状态(1、有效，0：无效)',
|
`alarm_battery_volt` tinyint NULL DEFAULT NULL COMMENT '电池电压报警（1：报警，0：正常）',
|
`alarm_valve` tinyint NULL DEFAULT NULL COMMENT '阀门报警（1：报警，0：正常）',
|
`power_type` tinyint NULL DEFAULT NULL COMMENT '供电方式（0: 220V，1：蓄电池）',
|
`send_ms_time` bigint NULL DEFAULT NULL COMMENT '发送消息时刻（毫秒）',
|
PRIMARY KEY (`id`) USING BTREE,
|
UNIQUE INDEX `rm_alarm_state_last_id_uindex`(`id` ASC) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '控制器报警与状态（最新）' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for rm_client_amount_day
|
-- ----------------------------
|
DROP TABLE IF EXISTS `rm_client_amount_day`;
|
CREATE TABLE `rm_client_amount_day` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`client_id` bigint NULL DEFAULT NULL COMMENT '农户ID',
|
`amount` float(10, 2) NULL DEFAULT NULL COMMENT '日取水量',
|
`money` float(10, 2) NULL DEFAULT NULL COMMENT '日花费金额',
|
`times` int NULL DEFAULT NULL COMMENT '日取水次数',
|
`dt` date NULL DEFAULT NULL COMMENT '统计日期(yyyy-mm-dd)',
|
`open_dt_last` datetime NULL DEFAULT NULL COMMENT '统计日最后一次开阀日期时间（yyyy-mm-dd HH:MM:SS）',
|
`close_dt_last` datetime NULL DEFAULT NULL COMMENT '统计日最后一次关阀日期时间（yyyy-mm-dd HH:MM:SS）',
|
`this_amount_last` float(10, 2) NULL DEFAULT NULL COMMENT '统计日最后一次开阀取水量',
|
`this_money_last` float(10, 2) NULL DEFAULT NULL COMMENT '统计日最后一次开阀花费金额',
|
`this_time_last` int NULL DEFAULT NULL COMMENT '统计日最后一次开阀取水时长（分钟）',
|
`rtu_dt_last` datetime NULL DEFAULT NULL COMMENT '统计日最后一次关阀上报控制器时钟',
|
PRIMARY KEY (`id`) USING BTREE,
|
UNIQUE INDEX `rm_client_amount_day_id_uindex`(`id` ASC) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '农户日用水量统计' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for rm_client_amount_day_last
|
-- ----------------------------
|
DROP TABLE IF EXISTS `rm_client_amount_day_last`;
|
CREATE TABLE `rm_client_amount_day_last` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`last_history_id` bigint NULL DEFAULT NULL COMMENT '指向历史记录中的最新记录',
|
`client_id` bigint NULL DEFAULT NULL COMMENT '农户id',
|
`amount` float(10, 2) NULL DEFAULT NULL COMMENT '日取水量',
|
`money` float(10, 2) NULL DEFAULT NULL COMMENT '日花费金额',
|
`dt` date NULL DEFAULT NULL COMMENT '统计日期(yyyy-mm-dd)',
|
`open_dt_last` datetime NULL DEFAULT NULL COMMENT '统计日最后一次开阀日期时间（yyyy-mm-dd HH:MM:SS）',
|
`close_dt_last` datetime NULL DEFAULT NULL COMMENT '统计日最后一次关阀日期时间（yyyy-mm-dd HH:MM:SS）',
|
`this_amount_last` float(10, 2) NULL DEFAULT NULL COMMENT '统计日最后一次开阀取水量',
|
`this_money_last` float(10, 2) NULL DEFAULT NULL COMMENT '统计日最后一次开阀花费金额',
|
`this_time_last` int NULL DEFAULT NULL COMMENT '统计日最后一次开阀取水时长（分钟）',
|
`rtu_dt_last` datetime NULL DEFAULT NULL COMMENT '统计日最后一次关阀上报控制器时钟',
|
PRIMARY KEY (`id`) USING BTREE,
|
UNIQUE INDEX `rm_client_amount_day_last_id_uindex`(`id` ASC) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '农户日用水量统计' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for rm_command_history
|
-- ----------------------------
|
DROP TABLE IF EXISTS `rm_command_history`;
|
CREATE TABLE `rm_command_history` (
|
`com_id` bigint NOT NULL COMMENT '主键（取名com_id，目的是避免被ID拦截器自动赋值）',
|
`command_code` varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '功能码',
|
`command_name` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '操作内容',
|
`intake_id` bigint NOT NULL COMMENT '取水口ID',
|
`rtu_addr` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '阀控器地址',
|
`protocol` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '通讯协议',
|
`param` json NULL COMMENT '参数数据',
|
`send_time` datetime NOT NULL COMMENT '发送命令时间',
|
`operator` bigint NOT NULL COMMENT '操作人',
|
`result` tinyint NULL DEFAULT NULL COMMENT '命令结果;0-失败，1-成功',
|
`result_time` datetime NULL DEFAULT NULL COMMENT '收到命令结果时间',
|
`result_text` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '命令结果内容',
|
PRIMARY KEY (`com_id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '命令日志表' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for rm_intake_amount_day
|
-- ----------------------------
|
DROP TABLE IF EXISTS `rm_intake_amount_day`;
|
CREATE TABLE `rm_intake_amount_day` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`intake_id` bigint NULL DEFAULT NULL COMMENT '取水口ID（外键）',
|
`dt` date NULL DEFAULT NULL COMMENT '统计日期(yyyy-mm-dd)',
|
`amount` float(10, 2) NULL DEFAULT NULL COMMENT '日取水量（不包括漏损水量）',
|
`money` float(10, 2) NULL DEFAULT NULL COMMENT '日取消费金额',
|
`times` int NULL DEFAULT NULL COMMENT '日取水次数',
|
`dt_last` datetime NULL DEFAULT NULL COMMENT '最后计水量上报数据接收时间(yyyy-mm-dd hh:mm:ss)',
|
`rtu_addr_last` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '最后计水量的控制器地址',
|
`controller_id_last` bigint NULL DEFAULT NULL COMMENT '最后计水量的控制器ID（外键）',
|
`total_amount_last` float(10, 2) NULL DEFAULT NULL COMMENT '最后计水量时控制器累计水量',
|
`rtu_dt_last` datetime NULL DEFAULT NULL COMMENT '最后计水量时控制器时钟（yyyy-mm-dd HH:MM:SS）',
|
PRIMARY KEY (`id`) USING BTREE,
|
UNIQUE INDEX `rm_intake_amount_day_id_uindex`(`id` ASC) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '取水口日取水量漏损量' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for rm_intake_amount_day_last
|
-- ----------------------------
|
DROP TABLE IF EXISTS `rm_intake_amount_day_last`;
|
CREATE TABLE `rm_intake_amount_day_last` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`last_history_id` bigint NULL DEFAULT NULL COMMENT '对应历史记录中的记录',
|
`intake_id` bigint NULL DEFAULT NULL COMMENT '取水口ID（外键）',
|
`dt` date NULL DEFAULT NULL COMMENT '统计日期(yyyy-mm-dd)',
|
`amount` float(10, 2) NULL DEFAULT NULL COMMENT '日取水量（不包括漏损水量）',
|
`money` float(10, 2) NULL DEFAULT NULL COMMENT '日取消费金额',
|
`times` int NULL DEFAULT NULL COMMENT '日取水次数',
|
`dt_last` datetime NULL DEFAULT NULL COMMENT '最后计水量上报数据接收时间(yyyy-mm-dd hh:mm:ss)',
|
`rtu_addr_last` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '最后计水量的控制器地址',
|
`controller_id_last` bigint NULL DEFAULT NULL COMMENT '最后计水量的控制器ID（外键）',
|
`total_amount_last` float(10, 2) NULL DEFAULT NULL COMMENT '最后计水量时控制器累计水量',
|
`rtu_dt_last` datetime NULL DEFAULT NULL COMMENT '最后计水量时控制器时钟（yyyy-mm-dd HH:MM:SS）',
|
PRIMARY KEY (`id`) USING BTREE,
|
UNIQUE INDEX `rm_intake_amount_day_last_id_uindex`(`id` ASC) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '取水口日取水量漏损量最新' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for rm_irrigate_profile
|
-- ----------------------------
|
DROP TABLE IF EXISTS `rm_irrigate_profile`;
|
CREATE TABLE `rm_irrigate_profile` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`default_value` int NOT NULL COMMENT '配置默认值',
|
`unit` tinyint NOT NULL COMMENT '计量单位;1-时（h），2-分（min），3-立方米（cbm）',
|
`sort` int NULL DEFAULT NULL COMMENT '排序',
|
`type` tinyint NULL DEFAULT NULL COMMENT '类型;1-用时，2-用水量',
|
`remarks` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '备注信息',
|
`deleted` bigint NULL DEFAULT 0 COMMENT '逻辑删除标识;未删除为0，已删除为删除时间',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '灌溉配置表' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for rm_loss_day
|
-- ----------------------------
|
DROP TABLE IF EXISTS `rm_loss_day`;
|
CREATE TABLE `rm_loss_day` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`controller_id` bigint NULL DEFAULT NULL COMMENT '控制器实体ID（外键）',
|
`intake_id` bigint NULL DEFAULT NULL COMMENT '取水口实体ID（外键）',
|
`rtu_addr` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '控制器地址',
|
`dt` date NULL DEFAULT NULL COMMENT '统计日期（yyyy-mm-dd）',
|
`dt_last` datetime NULL DEFAULT NULL COMMENT '控制器最后上报数据日期时间',
|
`dt_rtu` datetime NULL DEFAULT NULL COMMENT '控制器时钟',
|
`loss_amount` float(10, 2) NULL DEFAULT NULL COMMENT '日漏损流量',
|
PRIMARY KEY (`id`) USING BTREE,
|
UNIQUE INDEX `rm_loss_history_id_uindex`(`id` ASC) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '漏损历史记录' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for rm_loss_day_last
|
-- ----------------------------
|
DROP TABLE IF EXISTS `rm_loss_day_last`;
|
CREATE TABLE `rm_loss_day_last` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`last_history_id` bigint NULL DEFAULT NULL COMMENT '由最新数据持有历史数据中的最新记录ID',
|
`controller_id` bigint NULL DEFAULT NULL COMMENT '控制器实体ID（外键）',
|
`intake_id` bigint NULL DEFAULT NULL COMMENT '取水口实体ID（外键）',
|
`rtu_addr` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '控制器地址',
|
`dt` date NULL DEFAULT NULL COMMENT '日期（yyyy-mm-dd）',
|
`dt_last` datetime NULL DEFAULT NULL COMMENT '控制器最后上报数据日期时间',
|
`dt_rtu` datetime NULL DEFAULT NULL COMMENT '控制器时钟',
|
`loss_amount` float(10, 2) NULL DEFAULT NULL COMMENT '日漏损流量',
|
PRIMARY KEY (`id`) USING BTREE,
|
UNIQUE INDEX `rm_loss_last_id_uindex`(`id` ASC) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '漏损最新记录' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for rm_on_hour_report_history
|
-- ----------------------------
|
DROP TABLE IF EXISTS `rm_on_hour_report_history`;
|
CREATE TABLE `rm_on_hour_report_history` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`controller_id` bigint NULL DEFAULT NULL COMMENT '控制器实体ID(外键)',
|
`intake_id` bigint NULL DEFAULT NULL COMMENT '取水口实体ID（外键）',
|
`rtu_addr` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '控制器地址\n',
|
`dt` datetime NULL DEFAULT NULL COMMENT '数据接收日期时间',
|
`rtu_dt` datetime NULL DEFAULT NULL COMMENT '控制器时钟',
|
`instant_amount` float(10, 3) NULL DEFAULT NULL COMMENT '瞬时流量,单位为m3/h',
|
`total_amount` float(10, 2) NULL DEFAULT NULL COMMENT '累计流量,单位为m3',
|
`loss_amount` float(10, 2) NULL DEFAULT NULL COMMENT '损失流量(从0时到当前的漏损累计流量，24时一个周期，0时归0)单位为m3。',
|
`water_press` float(10, 2) NULL DEFAULT NULL COMMENT '水压,单位为KPa',
|
`battery_volt` float(10, 2) NULL DEFAULT NULL COMMENT '蓄电池电压，单位为V',
|
`signal_value` int NULL DEFAULT NULL COMMENT '信号强度，取值范围0~99',
|
`water_price` float(10, 2) NULL DEFAULT NULL COMMENT '水价，单位为V。',
|
PRIMARY KEY (`id`) USING BTREE,
|
UNIQUE INDEX `rm_on_hour_report_history_id_uindex`(`id` ASC) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '控制器整点报（只有靳总协议有）' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for rm_on_hour_report_last
|
-- ----------------------------
|
DROP TABLE IF EXISTS `rm_on_hour_report_last`;
|
CREATE TABLE `rm_on_hour_report_last` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`controller_id` bigint NULL DEFAULT NULL COMMENT '控制器实体ID(外键)',
|
`intake_id` bigint NULL DEFAULT NULL COMMENT '取水口实体ID（外键）',
|
`rtu_addr` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '控制器地址\n',
|
`dt` datetime NULL DEFAULT NULL COMMENT '数据接收日期时间',
|
`rtu_dt` datetime NULL DEFAULT NULL COMMENT '控制器时钟',
|
`instant_amount` float(10, 3) NULL DEFAULT NULL COMMENT '瞬时流量,单位为m3/h',
|
`total_amount` float(10, 2) NULL DEFAULT NULL COMMENT '累计流量,单位为m3',
|
`loss_amount` float(10, 2) NULL DEFAULT NULL COMMENT '损失流量(从0时到当前的漏损累计流量，24时一个周期，0时归0)单位为m3。',
|
`water_press` float(10, 2) NULL DEFAULT NULL COMMENT '水压,单位为KPa',
|
`battery_volt` float(10, 2) NULL DEFAULT NULL COMMENT '蓄电池电压，单位为V',
|
`signal_value` int NULL DEFAULT NULL COMMENT '信号强度，取值范围0~99',
|
`water_price` float(10, 2) NULL DEFAULT NULL COMMENT '水价，单位为V。',
|
PRIMARY KEY (`id`) USING BTREE,
|
UNIQUE INDEX `rm_on_hour_report_last_id_uindex`(`id` ASC) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '控制器整点报（只有靳总协议有）' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for rm_open_close_valve_history
|
-- ----------------------------
|
DROP TABLE IF EXISTS `rm_open_close_valve_history`;
|
CREATE TABLE `rm_open_close_valve_history` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`client_id` bigint NULL DEFAULT NULL COMMENT '农户实体外键',
|
`client_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '农户姓名',
|
`controller_id` bigint NULL DEFAULT NULL COMMENT '控制器实体ID（外键）',
|
`intake_id` bigint NULL DEFAULT NULL COMMENT '取水口实体ID（外键）',
|
`rtu_addr` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '控制器地址',
|
`op_dt` datetime NULL DEFAULT NULL COMMENT '开阀数据接收日期时间',
|
`op_type` tinyint NULL DEFAULT NULL COMMENT '开阀类型',
|
`op_total_amount` float(10, 2) NULL DEFAULT NULL COMMENT '开阀时累计流量',
|
`op_ic_card_no` varchar(17) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '开阀IC卡编号',
|
`op_ic_card_addr` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '开阀ic卡地址',
|
`op_remain_money` float(10, 2) NULL DEFAULT NULL COMMENT '开阀时剩余金额',
|
`open_dt` datetime NULL DEFAULT NULL COMMENT '开阀时控制器时钟',
|
`op_order_no` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '开阀订单号（16位数字）',
|
`op_ele_total_amount` float(10, 2) NULL DEFAULT NULL COMMENT '开阀电表累计电量',
|
`op_water_remain_user` float(10, 2) NULL DEFAULT NULL COMMENT '开阀时用水户剩余水量',
|
`cl_dt` datetime NULL DEFAULT NULL COMMENT '关阀数据接收日期时间',
|
`cl_type` tinyint NULL DEFAULT NULL COMMENT '关阀类型',
|
`cl_total_amount` float(10, 2) NULL DEFAULT NULL COMMENT '关阀时累计流量',
|
`cl_ic_card_no` varchar(17) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '关阀时IC卡编号',
|
`cl_ic_card_addr` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '关阀时IC卡地址',
|
`cl_remain_money` float(10, 2) NULL DEFAULT NULL COMMENT '关阀时剩余金额',
|
`cl_this_amount` float(10, 2) NULL DEFAULT NULL COMMENT '关阀报中本次用水量',
|
`cl_this_money` float(10, 2) NULL DEFAULT NULL COMMENT '关阀报中本次消费金额',
|
`cl_this_time` int NULL DEFAULT NULL COMMENT '关阀报中本次用水时长（分钟）',
|
`cl_price` float(10, 2) NULL DEFAULT NULL COMMENT '关阀报中的水价',
|
`cl_open_dt` datetime NULL DEFAULT NULL COMMENT '关阀报中的开阀时控制器时钟',
|
`close_dt` datetime NULL DEFAULT NULL COMMENT '关阀时控制器时钟',
|
`cl_order_no` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '关阀订单号（16位数字）',
|
`cl_ele_total_amount` float(10, 2) NULL DEFAULT NULL COMMENT '关阀时电表累计电量',
|
`cl_water_remain_user` float(10, 2) NULL DEFAULT NULL COMMENT '关阀时间用户剩余水量',
|
`cl_this_ele` float(10, 2) NULL DEFAULT NULL COMMENT '关阀报中本次用电量',
|
PRIMARY KEY (`id`) USING BTREE,
|
UNIQUE INDEX `rm_open_close_valve_history_id_uindex`(`id` ASC) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for rm_open_close_valve_last
|
-- ----------------------------
|
DROP TABLE IF EXISTS `rm_open_close_valve_last`;
|
CREATE TABLE `rm_open_close_valve_last` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`last_history_id` bigint NULL DEFAULT NULL COMMENT '由最新数据持有历史数据中的最新记录ID',
|
`client_id` bigint NULL DEFAULT NULL COMMENT '农户id',
|
`client_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '农户姓名',
|
`controller_id` bigint NULL DEFAULT NULL COMMENT '控制器实体ID（外键）',
|
`intake_id` bigint NULL DEFAULT NULL COMMENT '取水口实体ID（外键）',
|
`rtu_addr` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '控制器地址',
|
`op_dt` datetime NULL DEFAULT NULL COMMENT '开阀数据接收日期时间',
|
`op_type` tinyint NULL DEFAULT NULL COMMENT '开阀类型',
|
`op_total_amount` float(10, 2) NULL DEFAULT NULL COMMENT '开阀时累计流量',
|
`op_ic_card_no` varchar(17) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '开阀IC卡编号',
|
`op_ic_card_addr` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '开阀ic卡地址',
|
`op_remain_money` float(10, 2) NULL DEFAULT NULL COMMENT '开阀时剩余金额',
|
`open_dt` datetime NULL DEFAULT NULL COMMENT '开阀时控制器时钟',
|
`op_order_no` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '开阀订单号（16位数字）',
|
`op_ele_total_amount` float(10, 2) NULL DEFAULT NULL COMMENT '开阀电表累计电量',
|
`op_water_remain_user` float(10, 2) NULL DEFAULT NULL COMMENT '开阀时用水户剩余水量',
|
`cl_dt` datetime NULL DEFAULT NULL COMMENT '关阀数据接收日期时间',
|
`cl_type` tinyint NULL DEFAULT NULL COMMENT '关阀类型',
|
`cl_total_amount` float(10, 2) NULL DEFAULT NULL COMMENT '关阀时累计流量',
|
`cl_ic_card_no` varchar(17) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '关阀时IC卡编号',
|
`cl_ic_card_addr` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '关阀时IC卡地址',
|
`cl_remain_money` float(10, 2) NULL DEFAULT NULL COMMENT '关阀时剩余金额',
|
`cl_this_amount` float(10, 2) NULL DEFAULT NULL COMMENT '关阀报中本次用水量',
|
`cl_this_money` float(10, 2) NULL DEFAULT NULL COMMENT '关阀报中本次消费金额',
|
`cl_this_time` int NULL DEFAULT NULL COMMENT '关阀报中本次用水时长（分钟）',
|
`cl_price` float(10, 2) NULL DEFAULT NULL COMMENT '关阀报中的水价',
|
`cl_open_dt` datetime NULL DEFAULT NULL COMMENT '关阀报中的开阀时控制器时钟',
|
`close_dt` datetime NULL DEFAULT NULL COMMENT '关阀时控制器时钟',
|
`cl_order_no` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '关阀订单号（16位数字）',
|
`cl_ele_total_amount` float(10, 2) NULL DEFAULT NULL COMMENT '关阀时电表累计电量',
|
`cl_water_remain_user` float(10, 2) NULL DEFAULT NULL COMMENT '关阀时间用户剩余水量',
|
`cl_this_ele` float(10, 2) NULL DEFAULT NULL COMMENT '关阀报中本次用电量',
|
PRIMARY KEY (`id`) USING BTREE,
|
UNIQUE INDEX `rm_open_close_valve_last_id_uindex`(`id` ASC) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for rm_timing_report_history
|
-- ----------------------------
|
DROP TABLE IF EXISTS `rm_timing_report_history`;
|
CREATE TABLE `rm_timing_report_history` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`controller_id` bigint NULL DEFAULT NULL COMMENT '控制器实体ID（外键）',
|
`intake_id` bigint NULL DEFAULT NULL COMMENT '取水口实体ID（外键）',
|
`rtu_addr` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '控制器地址',
|
`dt` datetime NULL DEFAULT NULL COMMENT '数据接收日期时间',
|
`rtu_dt` datetime NULL DEFAULT NULL COMMENT '控制器时钟',
|
`data_dt` date NULL DEFAULT NULL COMMENT '数据时间',
|
`water_total_amount_year` float(10, 2) NULL DEFAULT NULL COMMENT '本年累计流量',
|
`water_total_amount_meter` float(10, 2) NULL DEFAULT NULL COMMENT '水表累计流量',
|
`ele_total_amount_year` float(10, 2) NULL DEFAULT NULL COMMENT '本年累计电量',
|
`ele_total_amount_meter` float(10, 2) NULL DEFAULT NULL COMMENT '电表累计电量',
|
`water_total_amount_day` float(10, 2) NULL DEFAULT NULL COMMENT '当日水表净流量',
|
`water_user_total_amount_day` float(10, 2) NULL DEFAULT NULL COMMENT '当日用户用水量',
|
`loss_total_amount_day` float(10, 2) NULL DEFAULT NULL COMMENT '漏损水量',
|
`battery_volt` float(10, 2) NULL DEFAULT NULL COMMENT '后备电池电压',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '定时（0点）报历史数据表（只有江海协议有）' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for rm_timing_report_last
|
-- ----------------------------
|
DROP TABLE IF EXISTS `rm_timing_report_last`;
|
CREATE TABLE `rm_timing_report_last` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`controller_id` bigint NULL DEFAULT NULL COMMENT '控制器实体ID（外键）',
|
`intake_id` bigint NULL DEFAULT NULL COMMENT '取水口实体ID（外键）',
|
`rtu_addr` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '控制器地址',
|
`dt` datetime NULL DEFAULT NULL COMMENT '数据接收日期时间',
|
`rtu_dt` datetime NULL DEFAULT NULL COMMENT '控制器时钟',
|
`data_dt` date NULL DEFAULT NULL COMMENT '数据时间',
|
`water_total_amount_year` float(10, 2) NULL DEFAULT NULL COMMENT '本年累计流量',
|
`water_total_amount_meter` float(10, 2) NULL DEFAULT NULL COMMENT '水表累计流量',
|
`ele_total_amount_year` float(10, 2) NULL DEFAULT NULL COMMENT '本年累计电量',
|
`ele_total_amount_meter` float(10, 2) NULL DEFAULT NULL COMMENT '电表累计电量',
|
`water_total_amount_day` float(10, 2) NULL DEFAULT NULL COMMENT '当日水表净流量',
|
`water_user_total_amount_day` float(10, 2) NULL DEFAULT NULL COMMENT '当日用户用水量',
|
`loss_total_amount_day` float(10, 2) NULL DEFAULT NULL COMMENT '漏损水量',
|
`battery_volt` float(10, 2) NULL DEFAULT NULL COMMENT '后备电池电压',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '定时（0点）报最新数据表（只有江海协议有）' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for rm_work_report_history
|
-- ----------------------------
|
DROP TABLE IF EXISTS `rm_work_report_history`;
|
CREATE TABLE `rm_work_report_history` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`client_id` bigint NULL DEFAULT NULL COMMENT '农户实体外键',
|
`client_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '农户姓名',
|
`controller_id` bigint NULL DEFAULT NULL COMMENT '控制器实体ID（外键）',
|
`intake_id` bigint NULL DEFAULT NULL COMMENT '取水口实体ID（外键）',
|
`rtu_addr` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '控制器地址',
|
`dt` datetime NULL DEFAULT NULL COMMENT '数据接收日期时间',
|
`rtu_dt` datetime NULL DEFAULT NULL COMMENT '开阀时控制器时钟',
|
`order_no` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '订单号（16位数字）',
|
`ic_card_addr` varchar(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT 'IC卡地址(远程开泵/阀时全为0)',
|
`ic_card_no` varchar(17) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT 'IC卡编号',
|
`water_level` float NULL DEFAULT NULL COMMENT '水位（单位0.001米）',
|
`water_press` float NULL DEFAULT NULL COMMENT '水压（单位0.01千帕）',
|
`water_instant` float NULL DEFAULT NULL COMMENT '瞬时流量（单位0.01m3/小时）',
|
`water_total` float NULL DEFAULT NULL COMMENT '水表累计流量(单位0.01m3)',
|
`ele_total` float NULL DEFAULT NULL COMMENT '电表累计电量(单位0.01度)',
|
`money_remain` float NULL DEFAULT NULL COMMENT '用户剩余金额(单位0.01元)',
|
`water_remain` float NULL DEFAULT NULL COMMENT '用户剩余水量(单位0.01m3)',
|
`this_ele` float NULL DEFAULT NULL COMMENT '本次使用电量(单位0.01度)',
|
`this_water` float NULL DEFAULT NULL COMMENT '本次使用水量(单位0.01m3)',
|
`this_money` float NULL DEFAULT NULL COMMENT '本次使用金额(单位0.01元)',
|
`this_duration` int NULL DEFAULT NULL COMMENT '本次使用时长(单位:分钟 最大9999分钟)',
|
`water_price` float NULL DEFAULT NULL COMMENT '当前水量单价(单位0.01元 最大99.99元)',
|
`ele_price` float NULL DEFAULT NULL COMMENT '当前电量单价(单位0.01元 最大99.99元)',
|
`a_volt` float NULL DEFAULT NULL COMMENT '交流A相电压(单位0.1伏特)',
|
`b_volt` float NULL DEFAULT NULL COMMENT '交流B相电压(单位0.1伏特)',
|
`c_volt` float NULL DEFAULT NULL COMMENT '交流C相电压(单位0.1伏特)',
|
`a_current` float NULL DEFAULT NULL COMMENT '交流A相电流(单位0.1安培)',
|
`b_current` float NULL DEFAULT NULL COMMENT '交流B相电流数据(单位0.1安培)',
|
`c_current` float NULL DEFAULT NULL COMMENT '交流C相电流(单位0.1安培)',
|
`battery_volt` float NULL DEFAULT NULL COMMENT '后备电池电压(单位0.01伏特)',
|
`water_meter_trans_volt` float NULL DEFAULT NULL COMMENT '水表远传电压(单位0.01伏特)',
|
`water_meter_work_volt` float NULL DEFAULT NULL COMMENT '水表工作电压(单位0.01伏特)',
|
PRIMARY KEY (`id`) USING BTREE,
|
UNIQUE INDEX `rm_work_report_history_id_uindex`(`id` ASC) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '控制器阀开工作报历史数据' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for rm_work_report_last
|
-- ----------------------------
|
DROP TABLE IF EXISTS `rm_work_report_last`;
|
CREATE TABLE `rm_work_report_last` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`client_id` bigint NULL DEFAULT NULL COMMENT '农户实体外键',
|
`client_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '农户姓名',
|
`controller_id` bigint NULL DEFAULT NULL COMMENT '控制器实体ID（外键）',
|
`intake_id` bigint NULL DEFAULT NULL COMMENT '取水口实体ID（外键）',
|
`rtu_addr` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '控制器地址',
|
`dt` datetime NULL DEFAULT NULL COMMENT '数据接收日期时间',
|
`rtu_dt` datetime NULL DEFAULT NULL COMMENT '开阀时控制器时钟',
|
`order_no` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '订单号（16位数字）',
|
`ic_card_addr` varchar(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT 'IC卡地址(远程开泵/阀时全为0)',
|
`ic_card_no` varchar(17) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT 'IC卡编号',
|
`water_level` float(10, 3) NULL DEFAULT NULL COMMENT '水位（单位0.001米）',
|
`water_press` float(10, 2) NULL DEFAULT NULL COMMENT '水压（单位0.01千帕）',
|
`water_instant` float(10, 2) NULL DEFAULT NULL COMMENT '瞬时流量（单位0.01m3/小时）',
|
`water_total` float(10, 2) NULL DEFAULT NULL COMMENT '水表累计流量(单位0.01m3)',
|
`ele_total` float(10, 2) NULL DEFAULT NULL COMMENT '电表累计电量(单位0.01度)',
|
`money_remain` float(10, 2) NULL DEFAULT NULL COMMENT '用户剩余金额(单位0.01元)',
|
`water_remain` float(10, 2) NULL DEFAULT NULL COMMENT '用户剩余水量(单位0.01m3)',
|
`this_ele` float(10, 2) NULL DEFAULT NULL COMMENT '本次使用电量(单位0.01度)',
|
`this_water` float(10, 2) NULL DEFAULT NULL COMMENT '本次使用水量(单位0.01m3)',
|
`this_money` float(10, 2) NULL DEFAULT NULL COMMENT '本次使用金额(单位0.01元)',
|
`this_duration` int NULL DEFAULT NULL COMMENT '本次使用时长(单位:分钟 最大9999分钟)',
|
`water_price` float(10, 2) NULL DEFAULT NULL COMMENT '当前水量单价(单位0.01元 最大99.99元)',
|
`ele_price` float(10, 2) NULL DEFAULT NULL COMMENT '当前电量单价(单位0.01元 最大99.99元)',
|
`a_volt` float(10, 1) NULL DEFAULT NULL COMMENT '交流A相电压(单位0.1伏特)',
|
`b_volt` float(10, 1) NULL DEFAULT NULL COMMENT '交流B相电压(单位0.1伏特)',
|
`c_volt` float(10, 1) NULL DEFAULT NULL COMMENT '交流C相电压(单位0.1伏特)',
|
`a_current` float(10, 1) NULL DEFAULT NULL COMMENT '交流A相电流(单位0.1安培)',
|
`b_current` float(10, 1) NULL DEFAULT NULL COMMENT '交流B相电流数据(单位0.1安培)',
|
`c_current` float(10, 1) NULL DEFAULT NULL COMMENT '交流C相电流(单位0.1安培)',
|
`battery_volt` float(10, 2) NULL DEFAULT NULL COMMENT '后备电池电压(单位0.01伏特)',
|
`water_meter_trans_volt` float(10, 2) NULL DEFAULT NULL COMMENT '水表远传电压(单位0.01伏特)',
|
`water_meter_work_volt` float(10, 2) NULL DEFAULT NULL COMMENT '水表工作电压(单位0.01伏特)',
|
PRIMARY KEY (`id`) USING BTREE,
|
UNIQUE INDEX `rm_work_report_last_id_uindex`(`id` ASC) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '控制器阀开工作报最新数据' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for se_active_card
|
-- ----------------------------
|
DROP TABLE IF EXISTS `se_active_card`;
|
CREATE TABLE `se_active_card` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`cardId` bigint NOT NULL COMMENT '水卡ID;水卡主键',
|
`clientId` bigint NOT NULL COMMENT '农户ID;农户主键',
|
`cardCost` int NULL DEFAULT 10 COMMENT '卡片费用',
|
`paymentId` bigint NOT NULL COMMENT '付款方式编号',
|
`reissueAmount` double NULL DEFAULT NULL COMMENT '补卡金额',
|
`operateType` tinyint NULL DEFAULT 1 COMMENT '操作类型;1-开卡，2-补卡',
|
`remarks` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '备注信息',
|
`operator` bigint NOT NULL COMMENT '操作人编号',
|
`operateDt` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '操作时间',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '开卡/补卡表' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for se_audits
|
-- ----------------------------
|
DROP TABLE IF EXISTS `se_audits`;
|
CREATE TABLE `se_audits` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`general_id` bigint NULL DEFAULT NULL COMMENT '总账ID',
|
`audit_status` tinyint NULL DEFAULT NULL COMMENT '审核状态;1-未审核，2-同意，3-驳回',
|
`audit_opinion` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '审核意见',
|
`operator` bigint NULL DEFAULT NULL COMMENT '操作人编号',
|
`operate_dt` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '操作时间',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '审核记录表' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for se_cancel
|
-- ----------------------------
|
DROP TABLE IF EXISTS `se_cancel`;
|
CREATE TABLE `se_cancel` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`cardId` bigint NOT NULL COMMENT '水卡编号',
|
`clientId` bigint NOT NULL COMMENT '农户编号',
|
`refund` float(10, 2) NOT NULL COMMENT '退款金额',
|
`refundType` tinyint NULL DEFAULT NULL COMMENT '退款方式;1-现金',
|
`remarks` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '备注信息',
|
`operator` bigint NOT NULL COMMENT '操作人编号',
|
`operateDt` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '操作时间',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '水卡注销表' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for se_card_operate
|
-- ----------------------------
|
DROP TABLE IF EXISTS `se_card_operate`;
|
CREATE TABLE `se_card_operate` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`card_id` bigint NULL DEFAULT NULL COMMENT '水卡ID',
|
`client_id` bigint NULL DEFAULT NULL COMMENT '农户ID',
|
`money` float(10, 2) NULL DEFAULT NULL COMMENT '卡片余额;充值、挂失、冲正、解锁',
|
`system_balance` float(10, 2) NULL DEFAULT NULL COMMENT '系统余额;冲正时使用',
|
`trade_amount` float(10, 2) NULL DEFAULT NULL COMMENT '交易金额;购水金额、退款金额',
|
`price` float(4, 2) NULL DEFAULT NULL COMMENT '水价;充值时使用',
|
`card_cost` int NULL DEFAULT 10 COMMENT '购卡金额',
|
`gift` float(10, 2) NULL DEFAULT NULL COMMENT '赠送金额',
|
`refund_amount` float(10, 2) NULL DEFAULT NULL COMMENT '返还金额;补扣操作时返还的金额',
|
`no_trade_amount` float(10, 2) NULL DEFAULT NULL COMMENT '非交易金额;补卡金额、补扣金额、剩余金额（解锁）',
|
`operate_type` tinyint NULL DEFAULT NULL COMMENT '操作类型;1-开卡，2-充值，3-销卡，4-补卡，5-补扣，6-挂失，7-冲正，8-解锁，9-消费',
|
`payment_id` bigint NULL DEFAULT NULL COMMENT '付款方式编号',
|
`remarks` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '备注信息',
|
`operator` bigint NOT NULL COMMENT '操作人编号',
|
`operate_dt` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '操作时间',
|
`operate_valid` tinyint NULL DEFAULT 1 COMMENT '操作是否有效;1-无效，2-有效',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '水卡操作表' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for se_client
|
-- ----------------------------
|
DROP TABLE IF EXISTS `se_client`;
|
CREATE TABLE `se_client` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`countyId` bigint NULL DEFAULT NULL COMMENT '县ID',
|
`townId` bigint NULL DEFAULT NULL COMMENT '镇ID',
|
`villageId` bigint NOT NULL COMMENT '村ID',
|
`blockId` bigint NULL DEFAULT NULL COMMENT '片区ID',
|
`divideId` bigint NULL DEFAULT NULL COMMENT '分水房ID',
|
`name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '农户姓名',
|
`virtualId` bigint NULL DEFAULT NULL COMMENT '虚拟卡号;10位，从1000000000开始',
|
`clientNum` varchar(12) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '农户编号;8位区划 + 4为顺序号',
|
`districtNum` bigint NOT NULL COMMENT '区划编码串;省区划+市区划+县区划+镇区划+村区划',
|
`districtTitle` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '区划名称串;县名+镇名+村名，用于归属地',
|
`phone` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '手机号',
|
`idCard` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '身份证号',
|
`area` double NULL DEFAULT NULL COMMENT '种植面积',
|
`typeId` bigint NULL DEFAULT NULL COMMENT '用水类型ID',
|
`address` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '街道及门牌号',
|
`remarks` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '备注信息',
|
`operator` bigint NOT NULL COMMENT '操作人编号',
|
`operateDt` datetime NULL DEFAULT NULL COMMENT '操作时间',
|
`disabled` tinyint NULL DEFAULT 0 COMMENT '禁止标志;0-未禁，1-已禁',
|
`deleted` tinyint NULL DEFAULT 0 COMMENT '逻辑删除标识;0-未删除，1-删除',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '农户表' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for se_client_2
|
-- ----------------------------
|
DROP TABLE IF EXISTS `se_client_2`;
|
CREATE TABLE `se_client_2` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`countyId` bigint NOT NULL COMMENT '县ID',
|
`townId` bigint NOT NULL COMMENT '镇ID',
|
`villageId` bigint NOT NULL COMMENT '村ID',
|
`blockId` bigint NOT NULL COMMENT '片区ID',
|
`divideId` bigint NULL DEFAULT NULL COMMENT '分水房ID',
|
`name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '农户姓名',
|
`virtualId` bigint NULL DEFAULT NULL COMMENT '虚拟卡号;10位，从1000000000开始',
|
`clientNum` varchar(12) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '农户编号;8位区划 + 4为顺序号',
|
`districtNum` bigint NOT NULL COMMENT '区划编码串;省区划+市区划+县区划+镇区划+村区划',
|
`districtTitle` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '区划名称串;县名+镇名+村名，用于归属地',
|
`phone` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '手机号',
|
`idCard` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '身份证号',
|
`area` double NULL DEFAULT NULL COMMENT '种植面积',
|
`typeId` bigint NULL DEFAULT NULL COMMENT '用水类型ID',
|
`address` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '街道及门牌号',
|
`remarks` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '备注信息',
|
`operator` bigint NOT NULL COMMENT '操作人编号',
|
`operateDt` datetime NULL DEFAULT NULL COMMENT '操作时间',
|
`disabled` tinyint NULL DEFAULT 0 COMMENT '禁止标志;0-未禁，1-已禁',
|
`deleted` tinyint NULL DEFAULT 0 COMMENT '逻辑删除标识;0-未删除，1-删除',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '农临时户表' ROW_FORMAT = Dynamic;
|
|
-- ----------------------------
|
-- Table structure for se_client_card
|
-- ----------------------------
|
DROP TABLE IF EXISTS `se_client_card`;
|
CREATE TABLE `se_client_card` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`protocol` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '通讯协议',
|
`cardAddr` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '水卡地址;物理卡自带地址',
|
`cardNum` bigint NOT NULL COMMENT '水卡编号;16位水卡编号',
|
`clientId` bigint NULL DEFAULT NULL COMMENT '农户ID;农户主键',
|
`money` float(10, 2) NULL DEFAULT 0.00 COMMENT '余额',
|
`state` tinyint NULL DEFAULT 1 COMMENT '卡片状态;1-正常，2-已注销，3-已挂失',
|
`original_card_id` bigint NULL DEFAULT NULL COMMENT '原水卡ID;补卡时挂失卡的ID',
|
`createDt` datetime NULL DEFAULT NULL COMMENT '开卡时间',
|
`replaceDt` datetime NULL DEFAULT NULL COMMENT '补卡时间',
|
`rechargeDt` datetime NULL DEFAULT NULL COMMENT '充值时间',
|
`lossDtDt` datetime NULL DEFAULT NULL COMMENT '挂失时间',
|
`cancelDt` datetime NULL DEFAULT NULL COMMENT '注销时间',
|
`unlockDt` datetime NULL DEFAULT NULL COMMENT '解锁时间',
|
`reversalDt` datetime NULL DEFAULT NULL COMMENT '冲正时间',
|
`refundDt` datetime NULL DEFAULT NULL COMMENT '补扣时间',
|
`consumeDt` datetime NULL DEFAULT NULL COMMENT '消费时间',
|
`lastOper` tinyint NULL DEFAULT NULL COMMENT '最后操作类型;1-开卡，2-补卡，3-充值，4-挂失，5-注销，6-解锁，7-冲正，8-消费',
|
`remarks` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '备注信息',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '农户水卡表' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for se_code_verify
|
-- ----------------------------
|
DROP TABLE IF EXISTS `se_code_verify`;
|
CREATE TABLE `se_code_verify` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`phone_number` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '手机号',
|
`security_code` varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '验证码',
|
`expires` bigint NOT NULL COMMENT '过期时间',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '验证码记录表' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for se_consume
|
-- ----------------------------
|
DROP TABLE IF EXISTS `se_consume`;
|
CREATE TABLE `se_consume` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`wallet_id` bigint NOT NULL COMMENT '钱包ID',
|
`client_id` bigint NOT NULL COMMENT '农户ID',
|
`money` float(10, 2) NULL DEFAULT NULL COMMENT '钱包余额',
|
`consumption` float(10, 2) NOT NULL COMMENT '消费金额',
|
`after_consume` float(10, 2) NULL DEFAULT NULL COMMENT '消费后余额',
|
`duration` float(4, 2) NULL DEFAULT NULL COMMENT '用水时长',
|
`vot` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '开阀时间',
|
`vct` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '关阀时间',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '电子钱包消费表' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for se_general
|
-- ----------------------------
|
DROP TABLE IF EXISTS `se_general`;
|
CREATE TABLE `se_general` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`cashier_id` bigint NULL DEFAULT NULL COMMENT '收银员ID',
|
`cashier_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '收银员姓名',
|
`trade_amount` float(10, 2) NULL DEFAULT NULL COMMENT '实收金额',
|
`gift` float(10, 2) NULL DEFAULT NULL COMMENT '赠送金额',
|
`refund_amount` float(10, 2) NULL DEFAULT NULL COMMENT '返回金额',
|
`operate_date` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '交易日期',
|
`audit_status` tinyint NULL DEFAULT 1 COMMENT '审核状态;1-未审核，2-同意，3-驳回',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '总账表' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for se_issue_report
|
-- ----------------------------
|
DROP TABLE IF EXISTS `se_issue_report`;
|
CREATE TABLE `se_issue_report` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`content` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '反馈内容',
|
`images` json NULL COMMENT '照片列表',
|
`audios` json NULL COMMENT '音频列表',
|
`videos` json NULL COMMENT '视频列表',
|
`lng` decimal(18, 15) NULL DEFAULT NULL COMMENT '经度',
|
`lat` decimal(18, 15) NULL DEFAULT NULL COMMENT '纬度',
|
`client_id` bigint NULL DEFAULT NULL COMMENT '农户ID',
|
`report_time` datetime NULL DEFAULT NULL COMMENT '上报时间',
|
`phone` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '联系电话',
|
`state` tinyint NULL DEFAULT NULL COMMENT '状态;1-未受理，2-已受理，3已删除',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '问题上报记录表' ROW_FORMAT = Dynamic;
|
|
-- ----------------------------
|
-- Table structure for se_loss
|
-- ----------------------------
|
DROP TABLE IF EXISTS `se_loss`;
|
CREATE TABLE `se_loss` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`cardId` bigint NOT NULL COMMENT '水卡编号',
|
`clientId` bigint NOT NULL COMMENT '农户编号',
|
`money` float(10, 2) NULL DEFAULT 0.00 COMMENT '余额',
|
`refund` float(10, 2) NULL DEFAULT 0.00 COMMENT '退款金额',
|
`remarks` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '备注信息',
|
`operator` bigint NOT NULL COMMENT '操作人编号',
|
`operateDt` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '操作时间',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '水卡挂失表' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for se_manager_card
|
-- ----------------------------
|
DROP TABLE IF EXISTS `se_manager_card`;
|
CREATE TABLE `se_manager_card` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`card_addr` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '卡片地址',
|
`card_num` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '卡片编号',
|
`card_type` tinyint NOT NULL DEFAULT 1 COMMENT '卡片类型;2-管理科，4-开关阀卡，5清空卡',
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '开卡时间',
|
`remarks` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '备注信息',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '管理员水卡表' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for se_open_id
|
-- ----------------------------
|
DROP TABLE IF EXISTS `se_open_id`;
|
CREATE TABLE `se_open_id` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`client_id` bigint NOT NULL COMMENT '农户ID',
|
`open_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '用户唯一标识',
|
`session_key` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '会话密钥',
|
`create_time` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '微信用户账户表' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for se_payment_method
|
-- ----------------------------
|
DROP TABLE IF EXISTS `se_payment_method`;
|
CREATE TABLE `se_payment_method` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '名称',
|
`remarks` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '备注信息',
|
`deleted` tinyint NULL DEFAULT NULL COMMENT '逻辑删除标识;0-未删除，1-删除',
|
PRIMARY KEY (`id`) USING BTREE,
|
UNIQUE INDEX `se_payment_method_id_uindex`(`id` ASC) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for se_recharge
|
-- ----------------------------
|
DROP TABLE IF EXISTS `se_recharge`;
|
CREATE TABLE `se_recharge` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`cardId` bigint NOT NULL COMMENT '水卡ID;水卡主键',
|
`clientId` bigint NOT NULL COMMENT '农户ID;农行主键',
|
`money` float(10, 2) NOT NULL COMMENT '卡片余额',
|
`amount` float(10, 2) NOT NULL COMMENT '充值金额',
|
`gift` float(10, 2) NULL DEFAULT NULL COMMENT '赠送金额',
|
`afterRecharge` float(10, 2) NULL DEFAULT NULL COMMENT '充值后余额',
|
`paymentId` bigint NOT NULL COMMENT '付款方式编号',
|
`price` float(4, 2) NULL DEFAULT NULL COMMENT '水价',
|
`remarks` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '备注信息',
|
`operator` bigint NOT NULL COMMENT '操作人编号',
|
`operateDt` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '操作时间',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '水卡充值表' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for se_recharge_history
|
-- ----------------------------
|
DROP TABLE IF EXISTS `se_recharge_history`;
|
CREATE TABLE `se_recharge_history` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`cardId` bigint NOT NULL COMMENT '水卡编号',
|
`clientId` bigint NOT NULL COMMENT '农户编号',
|
`money` float(10, 2) NOT NULL COMMENT '卡片余额',
|
`amount` float(10, 2) NOT NULL COMMENT '充值金额',
|
`gift` float(10, 2) NULL DEFAULT NULL COMMENT '赠送金额',
|
`afterRecharge` float(10, 2) NULL DEFAULT NULL COMMENT '充值后余额',
|
`paymentId` bigint NOT NULL COMMENT '付款方式编号',
|
`price` float(4, 2) NULL DEFAULT NULL COMMENT '水价',
|
`remarks` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '备注信息',
|
`operator` bigint NOT NULL COMMENT '操作人编号',
|
`operateDt` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '操作时间',
|
`operate_valid` tinyint NULL DEFAULT 1 COMMENT '操作是否有效;1-无效，2-有效',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '充值历史表' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for se_recharge_profile
|
-- ----------------------------
|
DROP TABLE IF EXISTS `se_recharge_profile`;
|
CREATE TABLE `se_recharge_profile` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`recharge_amount` float(10, 2) NOT NULL COMMENT '充值金额',
|
`sort` int NOT NULL COMMENT '排序',
|
`deleted` bigint NULL DEFAULT 0 COMMENT '逻辑删除标识;未删除为0，已删除为删除时间',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '常用充值金额配置表' ROW_FORMAT = Dynamic;
|
|
-- ----------------------------
|
-- Table structure for se_refund
|
-- ----------------------------
|
DROP TABLE IF EXISTS `se_refund`;
|
CREATE TABLE `se_refund` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`vc_id` bigint NULL DEFAULT NULL COMMENT '虚拟卡ID',
|
`client_id` bigint NOT NULL COMMENT '农户ID',
|
`money` float(10, 2) NULL DEFAULT NULL COMMENT '钱包余额',
|
`refund_amount` float(10, 2) NULL DEFAULT NULL COMMENT '退款金额',
|
`after_refund` float(10, 2) NULL DEFAULT NULL COMMENT '退款后余额',
|
`application_time` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '申请时间',
|
`auditor` bigint NULL DEFAULT NULL COMMENT '审核人',
|
`audit_time` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '审核时间',
|
`remarks` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '审核备注',
|
`refund_number` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '退款单号;12位农户号+17位时间戳+2位数量',
|
`refund_time` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '退款完成时间',
|
`refund_status` tinyint NULL DEFAULT 1 COMMENT '退款状态;1-待审核，2-待退款，3-已退款',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '虚拟卡退款表' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for se_report_reply
|
-- ----------------------------
|
DROP TABLE IF EXISTS `se_report_reply`;
|
CREATE TABLE `se_report_reply` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`report_id` bigint NOT NULL COMMENT '问题上报ID',
|
`reply_content` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '回复内容',
|
`reply_time` datetime NULL DEFAULT NULL COMMENT '回复时间',
|
`replier_id` bigint NOT NULL COMMENT '回复人ID',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '上报回复记录表' ROW_FORMAT = Dynamic;
|
|
-- ----------------------------
|
-- Table structure for se_reversal
|
-- ----------------------------
|
DROP TABLE IF EXISTS `se_reversal`;
|
CREATE TABLE `se_reversal` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`cardId` bigint NOT NULL COMMENT '水卡编号',
|
`clientId` bigint NOT NULL COMMENT '农户编号',
|
`cardBalance` float(10, 2) NOT NULL COMMENT '卡片余额',
|
`systemBalance` float(10, 2) NOT NULL COMMENT '系统余额',
|
`remarks` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '备注信息',
|
`operator` bigint NOT NULL COMMENT '操作人编号',
|
`operateDt` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '操作时间',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '冲正表' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for se_unlock
|
-- ----------------------------
|
DROP TABLE IF EXISTS `se_unlock`;
|
CREATE TABLE `se_unlock` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`cardId` bigint NOT NULL COMMENT '水卡编号',
|
`clientId` bigint NOT NULL COMMENT '农户编号',
|
`money` float(10, 2) NOT NULL COMMENT '剩余金额',
|
`remarks` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '备注信息',
|
`operator` bigint NOT NULL COMMENT '操作人编号',
|
`operateDt` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '操作时间',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '水卡解锁表' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for se_vc_operate
|
-- ----------------------------
|
DROP TABLE IF EXISTS `se_vc_operate`;
|
CREATE TABLE `se_vc_operate` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`vc_id` bigint NULL DEFAULT NULL COMMENT '虚拟卡ID',
|
`client_id` bigint NULL DEFAULT NULL COMMENT '农户ID',
|
`operate_type` tinyint NULL DEFAULT NULL COMMENT '操作类型;1-开户，2-充值，3-消费，4-销户，5-申请退款，6-退款审核，7-退款',
|
`recharge_id` bigint NULL DEFAULT NULL COMMENT '充值ID;充值时填写',
|
`operator` bigint NOT NULL COMMENT '操作人',
|
`operate_time` datetime NULL DEFAULT NULL COMMENT '操作时间',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '虚拟卡操作表' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for se_vc_recharge
|
-- ----------------------------
|
DROP TABLE IF EXISTS `se_vc_recharge`;
|
CREATE TABLE `se_vc_recharge` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`vc_id` bigint NOT NULL COMMENT '虚拟卡ID',
|
`client_id` bigint NOT NULL COMMENT '农户ID',
|
`money` float(10, 2) NULL DEFAULT NULL COMMENT '虚拟卡余额',
|
`after_recharge` float(10, 2) NULL DEFAULT NULL COMMENT '充值后余额',
|
`order_number` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '订单号',
|
`recharge_amount` float(10, 2) NOT NULL COMMENT '充值金额',
|
`order_time` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '下单时间',
|
`recharge_time` datetime NULL DEFAULT NULL COMMENT '充值完成时间',
|
`order_state` tinyint NULL DEFAULT 1 COMMENT '订单状态;1-未支付，2-已支付',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '虚拟卡充值表' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for se_vc_refund
|
-- ----------------------------
|
DROP TABLE IF EXISTS `se_vc_refund`;
|
CREATE TABLE `se_vc_refund` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`vc_id` bigint NULL DEFAULT NULL COMMENT '虚拟卡ID',
|
`client_id` bigint NOT NULL COMMENT '农户ID',
|
`money` float(10, 2) NULL DEFAULT NULL COMMENT '虚拟卡余额',
|
`refund_amount` float(10, 2) NULL DEFAULT NULL COMMENT '退款金额',
|
`after_refund` float(10, 2) NULL DEFAULT NULL COMMENT '退款后余额',
|
`application_time` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '申请时间',
|
`auditor` bigint NULL DEFAULT NULL COMMENT '审核人',
|
`audit_time` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '审核时间',
|
`remarks` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '审核备注',
|
`refund_number` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '退款单号;12位农户号+17位时间戳+2位数量',
|
`refund_time` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '退款完成时间',
|
`refund_status` tinyint NULL DEFAULT 1 COMMENT '退款状态;1-待审核，2-待退款，3-已退款',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '虚拟卡退款表' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for se_vc_refund_item
|
-- ----------------------------
|
DROP TABLE IF EXISTS `se_vc_refund_item`;
|
CREATE TABLE `se_vc_refund_item` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`refund_id` bigint NOT NULL COMMENT '退款ID',
|
`order_number` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '订单号;12位农户好+17位时间戳',
|
`refund_number` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '退款单号;12位农户号+17位时间戳+2位数量',
|
`refund_amount` int NULL DEFAULT NULL COMMENT '退款金额;与订单对应的退款金额',
|
`create_time` datetime NOT NULL COMMENT '退款单创建时间',
|
`refund_time` datetime NULL DEFAULT NULL COMMENT '退款完成时间',
|
`refund_status` tinyint NOT NULL COMMENT '退款状态;1-未退款，2-已退款',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '虚拟卡退款分项表' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for se_virtual_card
|
-- ----------------------------
|
DROP TABLE IF EXISTS `se_virtual_card`;
|
CREATE TABLE `se_virtual_card` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`vc_num` bigint NOT NULL COMMENT '虚拟卡编号',
|
`client_id` bigint NOT NULL COMMENT '农户ID',
|
`money` float(10, 2) NULL DEFAULT 0.00 COMMENT '账户余额',
|
`state` tinyint NULL DEFAULT 1 COMMENT '水卡状态;1-正常，2-已注销',
|
`last_operate` tinyint NULL DEFAULT 1 COMMENT '最后操作;1-开户，2-充值，3-消费，4-申请退款，5-退款审核',
|
`last_operate_time` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '最后操作时间',
|
`in_use` tinyint NULL DEFAULT NULL COMMENT '是否使用中;0-未使用，1-使用中',
|
`intake_id` bigint NULL DEFAULT NULL COMMENT '虚拟卡使用时所应用的取水口ID',
|
`open_time` datetime NULL DEFAULT NULL COMMENT '开阀时间',
|
`create_time` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '虚拟卡表' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for se_wallet
|
-- ----------------------------
|
DROP TABLE IF EXISTS `se_wallet`;
|
CREATE TABLE `se_wallet` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`client_id` bigint NOT NULL COMMENT '农户ID',
|
`money` float(10, 2) NULL DEFAULT 0.00 COMMENT '钱包余额',
|
`last_operate` tinyint NULL DEFAULT 1 COMMENT '最后操作;1-开户，2-充值，3-消费，4-申请退款，5-退款审核',
|
`last_operate_time` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '最后操作时间',
|
`create_time` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '电子钱包表' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for se_wallet_recharge
|
-- ----------------------------
|
DROP TABLE IF EXISTS `se_wallet_recharge`;
|
CREATE TABLE `se_wallet_recharge` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`wallet_id` bigint NOT NULL COMMENT '钱包ID',
|
`client_id` bigint NOT NULL COMMENT '农户ID',
|
`money` float(10, 2) NULL DEFAULT NULL COMMENT '钱包余额',
|
`amount` float(10, 2) NOT NULL COMMENT '充值金额',
|
`after_recharge` float(10, 2) NULL DEFAULT NULL COMMENT '充值后余额',
|
`recharge_time` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '充值时间',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '电子钱包充值表' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for se_water_type
|
-- ----------------------------
|
DROP TABLE IF EXISTS `se_water_type`;
|
CREATE TABLE `se_water_type` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`typeName` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '用水类型',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '用水类型表' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for se_wechatpay
|
-- ----------------------------
|
DROP TABLE IF EXISTS `se_wechatpay`;
|
CREATE TABLE `se_wechatpay` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`app_id` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '小程序唯一标识',
|
`app_secret` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '小程序秘钥',
|
`mch_id` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '商户号',
|
`mch_key` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '秘钥key',
|
`serial_no` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT 'API证书序列号',
|
`notify_url` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '支付结果通知url',
|
`remarks` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '备注信息',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '微信支付信息表' ROW_FORMAT = Dynamic;
|
|
-- ----------------------------
|
-- Table structure for st_client_amount_day
|
-- ----------------------------
|
DROP TABLE IF EXISTS `st_client_amount_day`;
|
CREATE TABLE `st_client_amount_day` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`client_id` bigint NULL DEFAULT NULL COMMENT '外键，指向农户',
|
`year` int NULL DEFAULT NULL COMMENT '年度',
|
`month` int NULL DEFAULT NULL COMMENT '月份',
|
`amount1` float(10, 2) NULL DEFAULT NULL COMMENT '1号用水量统计',
|
`money1` float(10, 2) NULL DEFAULT NULL COMMENT '1号花费金额',
|
`times1` int NULL DEFAULT NULL COMMENT '1号用水次数',
|
`amount2` float(10, 2) NULL DEFAULT NULL COMMENT '2号用水量统计',
|
`money2` float(10, 2) NULL DEFAULT NULL COMMENT '2号花费金额',
|
`times2` int NULL DEFAULT NULL COMMENT '2号用水次数',
|
`amount3` float(10, 2) NULL DEFAULT NULL COMMENT '3号用水量统计',
|
`money3` float(10, 2) NULL DEFAULT NULL COMMENT '3号花费金额',
|
`times3` int NULL DEFAULT NULL COMMENT '3号用水次数',
|
`amount4` float(10, 2) NULL DEFAULT NULL COMMENT '4号用水量统计',
|
`money4` float(10, 2) NULL DEFAULT NULL COMMENT '4号花费金额',
|
`times4` int NULL DEFAULT NULL COMMENT '4号用水次数',
|
`amount5` float(10, 2) NULL DEFAULT NULL COMMENT '5号用水量统计',
|
`money5` float(10, 2) NULL DEFAULT NULL COMMENT '5号花费金额',
|
`times5` int NULL DEFAULT NULL COMMENT '5号用水次数',
|
`amount6` float(10, 2) NULL DEFAULT NULL COMMENT '6号用水量统计',
|
`money6` float(10, 2) NULL DEFAULT NULL COMMENT '6号花费金额',
|
`times6` int NULL DEFAULT NULL COMMENT '6号用水次数',
|
`amount7` float(10, 2) NULL DEFAULT NULL COMMENT '7号用水量统计',
|
`money7` float(10, 2) NULL DEFAULT NULL COMMENT '7号花费金额',
|
`times7` int NULL DEFAULT NULL COMMENT '7号用水次数',
|
`amount8` float(10, 2) NULL DEFAULT NULL COMMENT '8号用水量统计',
|
`money8` float(10, 2) NULL DEFAULT NULL COMMENT '8号花费金额',
|
`times8` int NULL DEFAULT NULL COMMENT '8号用水次数',
|
`amount9` float(10, 2) NULL DEFAULT NULL COMMENT '9号用水量统计',
|
`money9` float(10, 2) NULL DEFAULT NULL COMMENT '9号花费金额',
|
`times9` int NULL DEFAULT NULL COMMENT '9号用水次数',
|
`amount10` float(10, 2) NULL DEFAULT NULL COMMENT '10号用水量统计',
|
`money10` float(10, 2) NULL DEFAULT NULL COMMENT '10号花费金额',
|
`times10` int NULL DEFAULT NULL COMMENT '10号用水次数',
|
`amount11` float(10, 2) NULL DEFAULT NULL COMMENT '11号用水量统计',
|
`money11` float(10, 2) NULL DEFAULT NULL COMMENT '11号花费金额',
|
`times11` int NULL DEFAULT NULL COMMENT '11号用水次数',
|
`amount12` float(10, 2) NULL DEFAULT NULL COMMENT '12号用水量统计',
|
`money12` float(10, 2) NULL DEFAULT NULL COMMENT '12号花费金额',
|
`times12` int NULL DEFAULT NULL COMMENT '12号用水次数',
|
`amount13` float(10, 2) NULL DEFAULT NULL COMMENT '13号用水量统计',
|
`money13` float(10, 2) NULL DEFAULT NULL COMMENT '13号花费金额',
|
`times13` int NULL DEFAULT NULL COMMENT '13号用水次数',
|
`amount14` float(10, 2) NULL DEFAULT NULL COMMENT '14号用水量统计',
|
`money14` float(10, 2) NULL DEFAULT NULL COMMENT '14号花费金额',
|
`times14` int NULL DEFAULT NULL COMMENT '14号用水次数',
|
`amount15` float(10, 2) NULL DEFAULT NULL COMMENT '15号用水量统计',
|
`money15` float(10, 2) NULL DEFAULT NULL COMMENT '15号花费金额',
|
`times15` int NULL DEFAULT NULL COMMENT '15号用水次数',
|
`amount16` float(10, 2) NULL DEFAULT NULL COMMENT '16号用水量统计',
|
`money16` float(10, 2) NULL DEFAULT NULL COMMENT '16号花费金额',
|
`times16` int NULL DEFAULT NULL COMMENT '16号用水次数',
|
`amount17` float(10, 2) NULL DEFAULT NULL COMMENT '17号用水量统计',
|
`money17` float(10, 2) NULL DEFAULT NULL COMMENT '17号花费金额',
|
`times17` int NULL DEFAULT NULL COMMENT '17号用水次数',
|
`amount18` float(10, 2) NULL DEFAULT NULL COMMENT '18号用水量统计',
|
`money18` float(10, 2) NULL DEFAULT NULL COMMENT '18号花费金额',
|
`times18` int NULL DEFAULT NULL COMMENT '18号用水次数',
|
`amount19` float(10, 2) NULL DEFAULT NULL COMMENT '19号用水量统计',
|
`money19` float(10, 2) NULL DEFAULT NULL COMMENT '19号花费金额',
|
`times19` int NULL DEFAULT NULL COMMENT '19号用水次数',
|
`amount20` float(10, 2) NULL DEFAULT NULL COMMENT '20号用水量统计',
|
`money20` float(10, 2) NULL DEFAULT NULL COMMENT '20号花费金额',
|
`times20` int NULL DEFAULT NULL COMMENT '20号用水次数',
|
`amount21` float(10, 2) NULL DEFAULT NULL COMMENT '21号用水量统计',
|
`money21` float(10, 2) NULL DEFAULT NULL COMMENT '21号花费金额',
|
`times21` int NULL DEFAULT NULL COMMENT '21号用水次数',
|
`amount22` float(10, 2) NULL DEFAULT NULL COMMENT '22号用水量统计',
|
`money22` float(10, 2) NULL DEFAULT NULL COMMENT '22号花费金额',
|
`times22` int NULL DEFAULT NULL COMMENT '22号用水次数',
|
`amount23` float(10, 2) NULL DEFAULT NULL COMMENT '23号用水量统计',
|
`money23` float(10, 2) NULL DEFAULT NULL COMMENT '23号花费金额',
|
`times23` int NULL DEFAULT NULL COMMENT '23号用水次数',
|
`amount24` float(10, 2) NULL DEFAULT NULL COMMENT '24号用水量统计',
|
`money24` float(10, 2) NULL DEFAULT NULL COMMENT '24号花费金额',
|
`times24` int NULL DEFAULT NULL COMMENT '24号用水次数',
|
`amount25` float(10, 2) NULL DEFAULT NULL COMMENT '25号用水量统计',
|
`money25` float(10, 2) NULL DEFAULT NULL COMMENT '25号花费金额',
|
`times25` int NULL DEFAULT NULL COMMENT '25号用水次数',
|
`amount26` float(10, 2) NULL DEFAULT NULL COMMENT '26号用水量统计',
|
`money26` float(10, 2) NULL DEFAULT NULL COMMENT '26号花费金额',
|
`times26` int NULL DEFAULT NULL COMMENT '26号用水次数',
|
`amount27` float(10, 2) NULL DEFAULT NULL COMMENT '27号用水量统计',
|
`money27` float(10, 2) NULL DEFAULT NULL COMMENT '27号花费金额',
|
`times27` int NULL DEFAULT NULL COMMENT '27号用水次数',
|
`amount28` float(10, 2) NULL DEFAULT NULL COMMENT '28号用水量统计',
|
`money28` float(10, 2) NULL DEFAULT NULL COMMENT '28号花费金额',
|
`times28` int NULL DEFAULT NULL COMMENT '28号用水次数',
|
`amount29` float(10, 2) NULL DEFAULT NULL COMMENT '29号用水量统计',
|
`money29` float(10, 2) NULL DEFAULT NULL COMMENT '29号花费金额',
|
`times29` int NULL DEFAULT NULL COMMENT '29号用水次数',
|
`amount30` float(10, 2) NULL DEFAULT NULL COMMENT '30号用水量统计',
|
`money30` float(10, 2) NULL DEFAULT NULL COMMENT '30号花费金额',
|
`times30` int NULL DEFAULT NULL COMMENT '30号用水次数',
|
`amount31` float(10, 2) NULL DEFAULT NULL COMMENT '31号用水量统计',
|
`money31` float(10, 2) NULL DEFAULT NULL COMMENT '31号花费金额',
|
`times31` int NULL DEFAULT NULL COMMENT '31号用水次数',
|
PRIMARY KEY (`id`) USING BTREE,
|
UNIQUE INDEX `st_client_amount_day_id_uindex`(`id` ASC) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '农户用水量日统计' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for st_client_amount_month
|
-- ----------------------------
|
DROP TABLE IF EXISTS `st_client_amount_month`;
|
CREATE TABLE `st_client_amount_month` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`client_id` bigint NULL DEFAULT NULL COMMENT '外键，指向农户',
|
`year` int NULL DEFAULT NULL COMMENT '年度',
|
`amount1` float(10, 2) NULL DEFAULT NULL COMMENT '1月用水量统计',
|
`money1` float(10, 2) NULL DEFAULT NULL COMMENT '1月花费金额',
|
`times1` int NULL DEFAULT NULL COMMENT '1月用水次数',
|
`amount2` float(10, 2) NULL DEFAULT NULL COMMENT '2月用水量统计',
|
`money2` float(10, 2) NULL DEFAULT NULL COMMENT '2月花费金额',
|
`times2` int NULL DEFAULT NULL COMMENT '2月用水次数',
|
`amount3` float(10, 2) NULL DEFAULT NULL COMMENT '3月用水量统计',
|
`money3` float(10, 2) NULL DEFAULT NULL COMMENT '3月花费金额',
|
`times3` int NULL DEFAULT NULL COMMENT '3月用水次数',
|
`amount4` float(10, 2) NULL DEFAULT NULL COMMENT '4月用水量统计',
|
`money4` float(10, 2) NULL DEFAULT NULL COMMENT '4月花费金额',
|
`times4` int NULL DEFAULT NULL COMMENT '4月用水次数',
|
`amount5` float(10, 2) NULL DEFAULT NULL COMMENT '5月用水量统计',
|
`money5` float(10, 2) NULL DEFAULT NULL COMMENT '5月花费金额',
|
`times5` int NULL DEFAULT NULL COMMENT '5月用水次数',
|
`amount6` float(10, 2) NULL DEFAULT NULL COMMENT '6月用水量统计',
|
`money6` float(10, 2) NULL DEFAULT NULL COMMENT '6月花费金额',
|
`times6` int NULL DEFAULT NULL COMMENT '6月用水次数',
|
`amount7` float(10, 2) NULL DEFAULT NULL COMMENT '7月用水量统计',
|
`money7` float(10, 2) NULL DEFAULT NULL COMMENT '7月花费金额',
|
`times7` int NULL DEFAULT NULL COMMENT '7月用水次数',
|
`amount8` float(10, 2) NULL DEFAULT NULL COMMENT '8月用水量统计',
|
`money8` float(10, 2) NULL DEFAULT NULL COMMENT '8月花费金额',
|
`times8` int NULL DEFAULT NULL COMMENT '8月用水次数',
|
`amount9` float(10, 2) NULL DEFAULT NULL COMMENT '9月用水量统计',
|
`money9` float(10, 2) NULL DEFAULT NULL COMMENT '9月花费金额',
|
`times9` int NULL DEFAULT NULL COMMENT '9月用水次数',
|
`amount10` float(10, 2) NULL DEFAULT NULL COMMENT '10月用水量统计',
|
`money10` float(10, 2) NULL DEFAULT NULL COMMENT '10月花费金额',
|
`times10` int NULL DEFAULT NULL COMMENT '10月用水次数',
|
`amount11` float(10, 2) NULL DEFAULT NULL COMMENT '11月用水量统计',
|
`money11` float(10, 2) NULL DEFAULT NULL COMMENT '11月花费金额',
|
`times11` int NULL DEFAULT NULL COMMENT '11月用水次数',
|
`amount12` float(10, 2) NULL DEFAULT NULL COMMENT '12月用水量统计',
|
`money12` float(10, 2) NULL DEFAULT NULL COMMENT '12月花费金额',
|
`times12` int NULL DEFAULT NULL COMMENT '12月用水次数',
|
PRIMARY KEY (`id`) USING BTREE,
|
UNIQUE INDEX `st_client_amount_month_id_uindex`(`id` ASC) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '农户用水量月统计' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for st_client_amount_year
|
-- ----------------------------
|
DROP TABLE IF EXISTS `st_client_amount_year`;
|
CREATE TABLE `st_client_amount_year` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`client_id` bigint NULL DEFAULT NULL COMMENT '外键，指向农户',
|
`year` int NULL DEFAULT NULL COMMENT '年度',
|
`amount` float(10, 2) NULL DEFAULT NULL COMMENT '年用水量统计',
|
`money` float(10, 2) NULL DEFAULT NULL COMMENT '年花费金额',
|
`times` int NULL DEFAULT NULL COMMENT '年用水次数',
|
PRIMARY KEY (`id`) USING BTREE,
|
UNIQUE INDEX `st_client_amount_year_id_uindex`(`id` ASC) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '农户用水量年统计' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for st_consume_client_day
|
-- ----------------------------
|
DROP TABLE IF EXISTS `st_consume_client_day`;
|
CREATE TABLE `st_consume_client_day` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`client_id` bigint NULL DEFAULT NULL COMMENT '外键，指向农户',
|
`year` int NULL DEFAULT NULL COMMENT '年度',
|
`month` int NULL DEFAULT NULL COMMENT '月份',
|
`amount1` float(10, 2) NULL DEFAULT NULL COMMENT '1号消费统计',
|
`times1` int NULL DEFAULT NULL COMMENT '1号消费次数',
|
`amount2` float(10, 2) NULL DEFAULT NULL COMMENT '2号消费统计',
|
`times2` int NULL DEFAULT NULL COMMENT '2号消费次数',
|
`amount3` float(10, 2) NULL DEFAULT NULL COMMENT '3号消费统计',
|
`times3` int NULL DEFAULT NULL COMMENT '3号消费次数',
|
`amount4` float(10, 2) NULL DEFAULT NULL COMMENT '4号消费统计',
|
`times4` int NULL DEFAULT NULL COMMENT '4号消费次数',
|
`amount5` float(10, 2) NULL DEFAULT NULL COMMENT '5号消费统计',
|
`times5` int NULL DEFAULT NULL COMMENT '5号消费次数',
|
`amount6` float(10, 2) NULL DEFAULT NULL COMMENT '6号消费统计',
|
`times6` int NULL DEFAULT NULL COMMENT '6号消费次数',
|
`amount7` float(10, 2) NULL DEFAULT NULL COMMENT '7号消费统计',
|
`times7` int NULL DEFAULT NULL COMMENT '7号消费次数',
|
`amount8` float(10, 2) NULL DEFAULT NULL COMMENT '8号消费统计',
|
`times8` int NULL DEFAULT NULL COMMENT '8号消费次数',
|
`amount9` float(10, 2) NULL DEFAULT NULL COMMENT '9号消费统计',
|
`times9` int NULL DEFAULT NULL COMMENT '9号消费次数',
|
`amount10` float(10, 2) NULL DEFAULT NULL COMMENT '10号消费统计',
|
`times10` int NULL DEFAULT NULL COMMENT '10号消费次数',
|
`amount11` float(10, 2) NULL DEFAULT NULL COMMENT '11号消费统计',
|
`times11` int NULL DEFAULT NULL COMMENT '11号消费次数',
|
`amount12` float(10, 2) NULL DEFAULT NULL COMMENT '12号消费统计',
|
`times12` int NULL DEFAULT NULL COMMENT '12号消费次数',
|
`amount13` float(10, 2) NULL DEFAULT NULL COMMENT '13号消费统计',
|
`times13` int NULL DEFAULT NULL COMMENT '13号消费次数',
|
`amount14` float(10, 2) NULL DEFAULT NULL COMMENT '14号消费统计',
|
`times14` int NULL DEFAULT NULL COMMENT '14号消费次数',
|
`amount15` float(10, 2) NULL DEFAULT NULL COMMENT '15号消费统计',
|
`times15` int NULL DEFAULT NULL COMMENT '15号消费次数',
|
`amount16` float(10, 2) NULL DEFAULT NULL COMMENT '16号消费统计',
|
`times16` int NULL DEFAULT NULL COMMENT '16号消费次数',
|
`amount17` float(10, 2) NULL DEFAULT NULL COMMENT '17号消费统计',
|
`times17` int NULL DEFAULT NULL COMMENT '17号消费次数',
|
`amount18` float(10, 2) NULL DEFAULT NULL COMMENT '18号消费统计',
|
`times18` int NULL DEFAULT NULL COMMENT '18号消费次数',
|
`amount19` float(10, 2) NULL DEFAULT NULL COMMENT '19号消费统计',
|
`times19` int NULL DEFAULT NULL COMMENT '19号消费次数',
|
`amount20` float(10, 2) NULL DEFAULT NULL COMMENT '20号消费统计',
|
`times20` int NULL DEFAULT NULL COMMENT '20号消费次数',
|
`amount21` float(10, 2) NULL DEFAULT NULL COMMENT '21号消费统计',
|
`times21` int NULL DEFAULT NULL COMMENT '21号消费次数',
|
`amount22` float(10, 2) NULL DEFAULT NULL COMMENT '22号消费统计',
|
`times22` int NULL DEFAULT NULL COMMENT '22号消费次数',
|
`amount23` float(10, 2) NULL DEFAULT NULL COMMENT '23号消费统计',
|
`times23` int NULL DEFAULT NULL COMMENT '23号消费次数',
|
`amount24` float(10, 2) NULL DEFAULT NULL COMMENT '24号消费统计',
|
`times24` int NULL DEFAULT NULL COMMENT '24号消费次数',
|
`amount25` float(10, 2) NULL DEFAULT NULL COMMENT '25号消费统计',
|
`times25` int NULL DEFAULT NULL COMMENT '25号消费次数',
|
`amount26` float(10, 2) NULL DEFAULT NULL COMMENT '26号消费统计',
|
`times26` int NULL DEFAULT NULL COMMENT '26号消费次数',
|
`amount27` float(10, 2) NULL DEFAULT NULL COMMENT '27号消费统计',
|
`times27` int NULL DEFAULT NULL COMMENT '27号消费次数',
|
`amount28` float(10, 2) NULL DEFAULT NULL COMMENT '28号消费统计',
|
`times28` int NULL DEFAULT NULL COMMENT '28号消费次数',
|
`amount29` float(10, 2) NULL DEFAULT NULL COMMENT '29号消费统计',
|
`times29` int NULL DEFAULT NULL COMMENT '29号消费次数',
|
`amount30` float(10, 2) NULL DEFAULT NULL COMMENT '30号消费统计',
|
`times30` int NULL DEFAULT NULL COMMENT '30号消费次数',
|
`amount31` float(10, 2) NULL DEFAULT NULL COMMENT '31号消费统计',
|
`times31` int NULL DEFAULT NULL COMMENT '31号消费次数',
|
PRIMARY KEY (`id`) USING BTREE,
|
UNIQUE INDEX `st_consume_client_day_id_uindex`(`id` ASC) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '农户消费日统计' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for st_consume_client_month
|
-- ----------------------------
|
DROP TABLE IF EXISTS `st_consume_client_month`;
|
CREATE TABLE `st_consume_client_month` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`client_id` bigint NULL DEFAULT NULL COMMENT '外键，指向农户',
|
`year` int NULL DEFAULT NULL COMMENT '年度',
|
`amount1` float(10, 2) NULL DEFAULT NULL COMMENT '1月消费统计',
|
`times1` int NULL DEFAULT NULL COMMENT '1月消费次数',
|
`amount2` float(10, 2) NULL DEFAULT NULL COMMENT '2月消费统计',
|
`times2` int NULL DEFAULT NULL COMMENT '2月消费次数',
|
`amount3` float(10, 2) NULL DEFAULT NULL COMMENT '3月消费统计',
|
`times3` int NULL DEFAULT NULL COMMENT '3月消费次数',
|
`amount4` float(10, 2) NULL DEFAULT NULL COMMENT '4月消费统计',
|
`times4` int NULL DEFAULT NULL COMMENT '4月消费次数',
|
`amount5` float(10, 2) NULL DEFAULT NULL COMMENT '5月消费统计',
|
`times5` int NULL DEFAULT NULL COMMENT '5月消费次数',
|
`amount6` float(10, 2) NULL DEFAULT NULL COMMENT '6月消费统计',
|
`times6` int NULL DEFAULT NULL COMMENT '6月消费次数',
|
`amount7` float(10, 2) NULL DEFAULT NULL COMMENT '7月消费统计',
|
`times7` int NULL DEFAULT NULL COMMENT '7月消费次数',
|
`amount8` float(10, 2) NULL DEFAULT NULL COMMENT '8月消费统计',
|
`times8` int NULL DEFAULT NULL COMMENT '8月消费次数',
|
`amount9` float(10, 2) NULL DEFAULT NULL COMMENT '9月消费统计',
|
`times9` int NULL DEFAULT NULL COMMENT '9月消费次数',
|
`amount10` float(10, 2) NULL DEFAULT NULL COMMENT '10月消费统计',
|
`times10` int NULL DEFAULT NULL COMMENT '10月消费次数',
|
`amount11` float(10, 2) NULL DEFAULT NULL COMMENT '11月消费统计',
|
`times11` int NULL DEFAULT NULL COMMENT '11月消费次数',
|
`amount12` float(10, 2) NULL DEFAULT NULL COMMENT '12月消费统计',
|
`times12` int NULL DEFAULT NULL COMMENT '12月消费次数',
|
PRIMARY KEY (`id`) USING BTREE,
|
UNIQUE INDEX `st_consume_client_month_id_uindex`(`id` ASC) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '农户消费月统计' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for st_consume_client_year
|
-- ----------------------------
|
DROP TABLE IF EXISTS `st_consume_client_year`;
|
CREATE TABLE `st_consume_client_year` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`client_id` bigint NULL DEFAULT NULL COMMENT '外键，指向农户',
|
`year` int NULL DEFAULT NULL COMMENT '年度',
|
`amount` float(10, 2) NULL DEFAULT NULL COMMENT '年消费统计',
|
`times` int NULL DEFAULT NULL COMMENT '年消费次数',
|
PRIMARY KEY (`id`) USING BTREE,
|
UNIQUE INDEX `st_consume_client_year_id_uindex`(`id` ASC) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '农户消费年统计' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for st_consume_ic_day
|
-- ----------------------------
|
DROP TABLE IF EXISTS `st_consume_ic_day`;
|
CREATE TABLE `st_consume_ic_day` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`client_id` bigint NULL DEFAULT NULL COMMENT '外键，指向农户',
|
`card_id` bigint NULL DEFAULT NULL COMMENT '外键，指向农户IC卡',
|
`year` int NULL DEFAULT NULL COMMENT '年度',
|
`month` int NULL DEFAULT NULL COMMENT '月份',
|
`amount1` float(10, 2) NULL DEFAULT NULL COMMENT '1号消费统计',
|
`times1` int NULL DEFAULT NULL COMMENT '1号消费次数',
|
`amount2` float(10, 2) NULL DEFAULT NULL COMMENT '2号消费统计',
|
`times2` int NULL DEFAULT NULL COMMENT '2号消费次数',
|
`amount3` float(10, 2) NULL DEFAULT NULL COMMENT '3号消费统计',
|
`times3` int NULL DEFAULT NULL COMMENT '3号消费次数',
|
`amount4` float(10, 2) NULL DEFAULT NULL COMMENT '4号消费统计',
|
`times4` int NULL DEFAULT NULL COMMENT '4号消费次数',
|
`amount5` float(10, 2) NULL DEFAULT NULL COMMENT '5号消费统计',
|
`times5` int NULL DEFAULT NULL COMMENT '5号消费次数',
|
`amount6` float(10, 2) NULL DEFAULT NULL COMMENT '6号消费统计',
|
`times6` int NULL DEFAULT NULL COMMENT '6号消费次数',
|
`amount7` float(10, 2) NULL DEFAULT NULL COMMENT '7号消费统计',
|
`times7` int NULL DEFAULT NULL COMMENT '7号消费次数',
|
`amount8` float(10, 2) NULL DEFAULT NULL COMMENT '8号消费统计',
|
`times8` int NULL DEFAULT NULL COMMENT '8号消费次数',
|
`amount9` float(10, 2) NULL DEFAULT NULL COMMENT '9号消费统计',
|
`times9` int NULL DEFAULT NULL COMMENT '9号消费次数',
|
`amount10` float(10, 2) NULL DEFAULT NULL COMMENT '10号消费统计',
|
`times10` int NULL DEFAULT NULL COMMENT '10号消费次数',
|
`amount11` float(10, 2) NULL DEFAULT NULL COMMENT '11号消费统计',
|
`times11` int NULL DEFAULT NULL COMMENT '11号消费次数',
|
`amount12` float(10, 2) NULL DEFAULT NULL COMMENT '12号消费统计',
|
`times12` int NULL DEFAULT NULL COMMENT '12号消费次数',
|
`amount13` float(10, 2) NULL DEFAULT NULL COMMENT '13号消费统计',
|
`times13` int NULL DEFAULT NULL COMMENT '13号消费次数',
|
`amount14` float(10, 2) NULL DEFAULT NULL COMMENT '14号消费统计',
|
`times14` int NULL DEFAULT NULL COMMENT '14号消费次数',
|
`amount15` float(10, 2) NULL DEFAULT NULL COMMENT '15号消费统计',
|
`times15` int NULL DEFAULT NULL COMMENT '15号消费次数',
|
`amount16` float(10, 2) NULL DEFAULT NULL COMMENT '16号消费统计',
|
`times16` int NULL DEFAULT NULL COMMENT '16号消费次数',
|
`amount17` float(10, 2) NULL DEFAULT NULL COMMENT '17号消费统计',
|
`times17` int NULL DEFAULT NULL COMMENT '17号消费次数',
|
`amount18` float(10, 2) NULL DEFAULT NULL COMMENT '18号消费统计',
|
`times18` int NULL DEFAULT NULL COMMENT '18号消费次数',
|
`amount19` float(10, 2) NULL DEFAULT NULL COMMENT '19号消费统计',
|
`times19` int NULL DEFAULT NULL COMMENT '19号消费次数',
|
`amount20` float(10, 2) NULL DEFAULT NULL COMMENT '20号消费统计',
|
`times20` int NULL DEFAULT NULL COMMENT '20号消费次数',
|
`amount21` float(10, 2) NULL DEFAULT NULL COMMENT '21号消费统计',
|
`times21` int NULL DEFAULT NULL COMMENT '21号消费次数',
|
`amount22` float(10, 2) NULL DEFAULT NULL COMMENT '22号消费统计',
|
`times22` int NULL DEFAULT NULL COMMENT '22号消费次数',
|
`amount23` float(10, 2) NULL DEFAULT NULL COMMENT '23号消费统计',
|
`times23` int NULL DEFAULT NULL COMMENT '23号消费次数',
|
`amount24` float(10, 2) NULL DEFAULT NULL COMMENT '24号消费统计',
|
`times24` int NULL DEFAULT NULL COMMENT '24号消费次数',
|
`amount25` float(10, 2) NULL DEFAULT NULL COMMENT '25号消费统计',
|
`times25` int NULL DEFAULT NULL COMMENT '25号消费次数',
|
`amount26` float(10, 2) NULL DEFAULT NULL COMMENT '26号消费统计',
|
`times26` int NULL DEFAULT NULL COMMENT '26号消费次数',
|
`amount27` float(10, 2) NULL DEFAULT NULL COMMENT '27号消费统计',
|
`times27` int NULL DEFAULT NULL COMMENT '27号消费次数',
|
`amount28` float(10, 2) NULL DEFAULT NULL COMMENT '28号消费统计',
|
`times28` int NULL DEFAULT NULL COMMENT '28号消费次数',
|
`amount29` float(10, 2) NULL DEFAULT NULL COMMENT '29号消费统计',
|
`times29` int NULL DEFAULT NULL COMMENT '29号消费次数',
|
`amount30` float(10, 2) NULL DEFAULT NULL COMMENT '30号消费统计',
|
`times30` int NULL DEFAULT NULL COMMENT '30号消费次数',
|
`amount31` float(10, 2) NULL DEFAULT NULL COMMENT '31号消费统计',
|
`times31` int NULL DEFAULT NULL COMMENT '31号消费次数',
|
PRIMARY KEY (`id`) USING BTREE,
|
UNIQUE INDEX `st_consume_ic_day_id_uindex`(`id` ASC) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = 'IC卡消费日统计' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for st_consume_ic_month
|
-- ----------------------------
|
DROP TABLE IF EXISTS `st_consume_ic_month`;
|
CREATE TABLE `st_consume_ic_month` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`client_id` bigint NULL DEFAULT NULL COMMENT '外键，指向农户',
|
`card_id` bigint NULL DEFAULT NULL COMMENT '外键，指向农户IC卡',
|
`year` int NULL DEFAULT NULL COMMENT '年度',
|
`amount1` float(10, 2) NULL DEFAULT NULL COMMENT '1月消费统计',
|
`times1` int NULL DEFAULT NULL COMMENT '1月消费次数',
|
`amount2` float(10, 2) NULL DEFAULT NULL COMMENT '2月消费统计',
|
`times2` int NULL DEFAULT NULL COMMENT '2月消费次数',
|
`amount3` float(10, 2) NULL DEFAULT NULL COMMENT '3月消费统计',
|
`times3` int NULL DEFAULT NULL COMMENT '3月消费次数',
|
`amount4` float(10, 2) NULL DEFAULT NULL COMMENT '4月消费统计',
|
`times4` int NULL DEFAULT NULL COMMENT '4月消费次数',
|
`amount5` float(10, 2) NULL DEFAULT NULL COMMENT '5月消费统计',
|
`times5` int NULL DEFAULT NULL COMMENT '5月消费次数',
|
`amount6` float(10, 2) NULL DEFAULT NULL COMMENT '6月消费统计',
|
`times6` int NULL DEFAULT NULL COMMENT '6月消费次数',
|
`amount7` float(10, 2) NULL DEFAULT NULL COMMENT '7月消费统计',
|
`times7` int NULL DEFAULT NULL COMMENT '7月消费次数',
|
`amount8` float(10, 2) NULL DEFAULT NULL COMMENT '8月消费统计',
|
`times8` int NULL DEFAULT NULL COMMENT '8月消费次数',
|
`amount9` float(10, 2) NULL DEFAULT NULL COMMENT '9月消费统计',
|
`times9` int NULL DEFAULT NULL COMMENT '9月消费次数',
|
`amount10` float(10, 2) NULL DEFAULT NULL COMMENT '10月消费统计',
|
`times10` int NULL DEFAULT NULL COMMENT '10月消费次数',
|
`amount11` float(10, 2) NULL DEFAULT NULL COMMENT '11月消费统计',
|
`times11` int NULL DEFAULT NULL COMMENT '11月消费次数',
|
`amount12` float(10, 2) NULL DEFAULT NULL COMMENT '12月消费统计',
|
`times12` int NULL DEFAULT NULL COMMENT '12月消费次数',
|
PRIMARY KEY (`id`) USING BTREE,
|
UNIQUE INDEX `st_consume_ic_month_id_uindex`(`id` ASC) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = 'IC卡消费月统计' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for st_consume_ic_year
|
-- ----------------------------
|
DROP TABLE IF EXISTS `st_consume_ic_year`;
|
CREATE TABLE `st_consume_ic_year` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`client_id` bigint NULL DEFAULT NULL COMMENT '外键，指向农户',
|
`card_id` bigint NULL DEFAULT NULL COMMENT '外键，指向农户IC卡',
|
`year` int NULL DEFAULT NULL COMMENT '年度',
|
`amount` float(10, 2) NULL DEFAULT NULL COMMENT '年消费统计',
|
`times` int NULL DEFAULT NULL COMMENT '年消费次数',
|
PRIMARY KEY (`id`) USING BTREE,
|
UNIQUE INDEX `st_consume_ic_year_id_uindex`(`id` ASC) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = 'IC卡消费年统计' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for st_intake_amount_month
|
-- ----------------------------
|
DROP TABLE IF EXISTS `st_intake_amount_month`;
|
CREATE TABLE `st_intake_amount_month` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`intake_id` bigint NULL DEFAULT NULL COMMENT '取水口ID',
|
`year` int NULL DEFAULT NULL COMMENT '统计年度',
|
`month` int NULL DEFAULT NULL COMMENT '统计月份',
|
`amount` float(10, 2) NULL DEFAULT NULL COMMENT '月取水量',
|
PRIMARY KEY (`id`) USING BTREE,
|
UNIQUE INDEX `st_intake_amount_month_id_uindex`(`id` ASC) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '取水口取水量月统计' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for st_intake_amount_year
|
-- ----------------------------
|
DROP TABLE IF EXISTS `st_intake_amount_year`;
|
CREATE TABLE `st_intake_amount_year` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`intake_id` bigint NULL DEFAULT NULL COMMENT '取水口ID',
|
`year` int NULL DEFAULT NULL COMMENT '统计年度',
|
`amount` float(10, 2) NULL DEFAULT NULL COMMENT '年取水量',
|
PRIMARY KEY (`id`) USING BTREE,
|
UNIQUE INDEX `st_intake_amount_year_id_uindex`(`id` ASC) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '取水口年取水量统计' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for st_loss_month
|
-- ----------------------------
|
DROP TABLE IF EXISTS `st_loss_month`;
|
CREATE TABLE `st_loss_month` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`intake_id` bigint NULL DEFAULT NULL COMMENT '取水口ID',
|
`year` int NULL DEFAULT NULL COMMENT '统计年',
|
`month` int NULL DEFAULT NULL COMMENT '统计月',
|
`amount` float(10, 2) NULL DEFAULT NULL COMMENT '月统计',
|
PRIMARY KEY (`id`) USING BTREE,
|
UNIQUE INDEX `st_loss_month_id_uindex`(`id` ASC) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '取水口漏损月统计' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for st_loss_year
|
-- ----------------------------
|
DROP TABLE IF EXISTS `st_loss_year`;
|
CREATE TABLE `st_loss_year` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`intake_id` bigint NULL DEFAULT NULL COMMENT '取水口ID',
|
`year` int NULL DEFAULT NULL COMMENT '年度',
|
`amount` float(10, 2) NULL DEFAULT NULL COMMENT '年漏损统计',
|
PRIMARY KEY (`id`) USING BTREE,
|
UNIQUE INDEX `st_loss_year_id_uindex`(`id` ASC) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '取水口漏损年统计' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for st_recharge_client_day
|
-- ----------------------------
|
DROP TABLE IF EXISTS `st_recharge_client_day`;
|
CREATE TABLE `st_recharge_client_day` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`client_id` bigint NULL DEFAULT NULL COMMENT '外键，指向农户',
|
`year` int NULL DEFAULT NULL COMMENT '年度',
|
`month` int NULL DEFAULT NULL COMMENT '月份',
|
`amount1` float(10, 2) NULL DEFAULT NULL COMMENT '1号充值统计',
|
`gift1` float(10, 2) NULL DEFAULT NULL COMMENT '1号赠送统计',
|
`times1` int NULL DEFAULT NULL COMMENT '1号充值次数',
|
`amount2` float(10, 2) NULL DEFAULT NULL COMMENT '2号充值统计',
|
`gift2` float(10, 2) NULL DEFAULT NULL COMMENT '2号赠送统计',
|
`times2` int NULL DEFAULT NULL COMMENT '2号充值次数',
|
`amount3` float(10, 2) NULL DEFAULT NULL COMMENT '3号充值统计',
|
`gift3` float(10, 2) NULL DEFAULT NULL COMMENT '3号赠送统计',
|
`times3` int NULL DEFAULT NULL COMMENT '3号充值次数',
|
`amount4` float(10, 2) NULL DEFAULT NULL COMMENT '4号充值统计',
|
`gift4` float(10, 2) NULL DEFAULT NULL COMMENT '4号赠送统计',
|
`times4` int NULL DEFAULT NULL COMMENT '4号充值次数',
|
`amount5` float(10, 2) NULL DEFAULT NULL COMMENT '5号充值统计',
|
`gift5` float(10, 2) NULL DEFAULT NULL COMMENT '5号赠送统计',
|
`times5` int NULL DEFAULT NULL COMMENT '5号充值次数',
|
`amount6` float(10, 2) NULL DEFAULT NULL COMMENT '6号充值统计',
|
`gift6` float(10, 2) NULL DEFAULT NULL COMMENT '6号赠送统计',
|
`times6` int NULL DEFAULT NULL COMMENT '6号充值次数',
|
`amount7` float(10, 2) NULL DEFAULT NULL COMMENT '7号充值统计',
|
`gift7` float(10, 2) NULL DEFAULT NULL COMMENT '7号赠送统计',
|
`times7` int NULL DEFAULT NULL COMMENT '7号充值次数',
|
`amount8` float(10, 2) NULL DEFAULT NULL COMMENT '8号充值统计',
|
`gift8` float(10, 2) NULL DEFAULT NULL COMMENT '8号赠送统计',
|
`times8` int NULL DEFAULT NULL COMMENT '8号充值次数',
|
`amount9` float(10, 2) NULL DEFAULT NULL COMMENT '9号充值统计',
|
`gift9` float(10, 2) NULL DEFAULT NULL COMMENT '9号赠送统计',
|
`times9` int NULL DEFAULT NULL COMMENT '9号充值次数',
|
`amount10` float(10, 2) NULL DEFAULT NULL COMMENT '10号充值统计',
|
`gift10` float(10, 2) NULL DEFAULT NULL COMMENT '10号赠送统计',
|
`times10` int NULL DEFAULT NULL COMMENT '10号充值次数',
|
`amount11` float(10, 2) NULL DEFAULT NULL COMMENT '11号充值统计',
|
`gift11` float(10, 2) NULL DEFAULT NULL COMMENT '11号赠送统计',
|
`times11` int NULL DEFAULT NULL COMMENT '11号充值次数',
|
`amount12` float(10, 2) NULL DEFAULT NULL COMMENT '12号充值统计',
|
`gift12` float(10, 2) NULL DEFAULT NULL COMMENT '12号赠送统计',
|
`times12` int NULL DEFAULT NULL COMMENT '12号充值次数',
|
`amount13` float(10, 2) NULL DEFAULT NULL COMMENT '13号充值统计',
|
`gift13` float(10, 2) NULL DEFAULT NULL COMMENT '13号赠送统计',
|
`times13` int NULL DEFAULT NULL COMMENT '13号充值次数',
|
`amount14` float(10, 2) NULL DEFAULT NULL COMMENT '14号充值统计',
|
`gift14` float(10, 2) NULL DEFAULT NULL COMMENT '14号赠送统计',
|
`times14` int NULL DEFAULT NULL COMMENT '14号充值次数',
|
`amount15` float(10, 2) NULL DEFAULT NULL COMMENT '15号充值统计',
|
`gift15` float(10, 2) NULL DEFAULT NULL COMMENT '15号赠送统计',
|
`times15` int NULL DEFAULT NULL COMMENT '15号充值次数',
|
`amount16` float(10, 2) NULL DEFAULT NULL COMMENT '16号充值统计',
|
`gift16` float(10, 2) NULL DEFAULT NULL COMMENT '16号赠送统计',
|
`times16` int NULL DEFAULT NULL COMMENT '16号充值次数',
|
`amount17` float(10, 2) NULL DEFAULT NULL COMMENT '17号充值统计',
|
`gift17` float(10, 2) NULL DEFAULT NULL COMMENT '17号赠送统计',
|
`times17` int NULL DEFAULT NULL COMMENT '17号充值次数',
|
`amount18` float(10, 2) NULL DEFAULT NULL COMMENT '18号充值统计',
|
`gift18` float(10, 2) NULL DEFAULT NULL COMMENT '18号赠送统计',
|
`times18` int NULL DEFAULT NULL COMMENT '18号充值次数',
|
`amount19` float(10, 2) NULL DEFAULT NULL COMMENT '19号充值统计',
|
`gift19` float(10, 2) NULL DEFAULT NULL COMMENT '19号赠送统计',
|
`times19` int NULL DEFAULT NULL COMMENT '19号充值次数',
|
`amount20` float(10, 2) NULL DEFAULT NULL COMMENT '20号充值统计',
|
`gift20` float(10, 2) NULL DEFAULT NULL COMMENT '20号赠送统计',
|
`times20` int NULL DEFAULT NULL COMMENT '20号充值次数',
|
`amount21` float(10, 2) NULL DEFAULT NULL COMMENT '21号充值统计',
|
`gift21` float(10, 2) NULL DEFAULT NULL COMMENT '21号赠送统计',
|
`times21` int NULL DEFAULT NULL COMMENT '21号充值次数',
|
`amount22` float(10, 2) NULL DEFAULT NULL COMMENT '22号充值统计',
|
`gift22` float(10, 2) NULL DEFAULT NULL COMMENT '22号赠送统计',
|
`times22` int NULL DEFAULT NULL COMMENT '22号充值次数',
|
`amount23` float(10, 2) NULL DEFAULT NULL COMMENT '23号充值统计',
|
`gift23` float(10, 2) NULL DEFAULT NULL COMMENT '23号赠送统计',
|
`times23` int NULL DEFAULT NULL COMMENT '23号充值次数',
|
`amount24` float(10, 2) NULL DEFAULT NULL COMMENT '24号充值统计',
|
`gift24` float(10, 2) NULL DEFAULT NULL COMMENT '24号赠送统计',
|
`times24` int NULL DEFAULT NULL COMMENT '24号充值次数',
|
`amount25` float(10, 2) NULL DEFAULT NULL COMMENT '25号充值统计',
|
`gift25` float(10, 2) NULL DEFAULT NULL COMMENT '25号赠送统计',
|
`times25` int NULL DEFAULT NULL COMMENT '25号充值次数',
|
`amount26` float(10, 2) NULL DEFAULT NULL COMMENT '26号充值统计',
|
`gift26` float(10, 2) NULL DEFAULT NULL COMMENT '26号赠送统计',
|
`times26` int NULL DEFAULT NULL COMMENT '26号充值次数',
|
`amount27` float(10, 2) NULL DEFAULT NULL COMMENT '27号充值统计',
|
`gift27` float(10, 2) NULL DEFAULT NULL COMMENT '27号赠送统计',
|
`times27` int NULL DEFAULT NULL COMMENT '27号充值次数',
|
`amount28` float(10, 2) NULL DEFAULT NULL COMMENT '28号充值统计',
|
`gift28` float(10, 2) NULL DEFAULT NULL COMMENT '28号赠送统计',
|
`times28` int NULL DEFAULT NULL COMMENT '28号充值次数',
|
`amount29` float(10, 2) NULL DEFAULT NULL COMMENT '29号充值统计',
|
`gift29` float(10, 2) NULL DEFAULT NULL COMMENT '29号赠送统计',
|
`times29` int NULL DEFAULT NULL COMMENT '29号充值次数',
|
`amount30` float(10, 2) NULL DEFAULT NULL COMMENT '30号充值统计',
|
`gift30` float(10, 2) NULL DEFAULT NULL COMMENT '30号赠送统计',
|
`times30` int NULL DEFAULT NULL COMMENT '30号充值次数',
|
`amount31` float(10, 2) NULL DEFAULT NULL COMMENT '31号充值统计',
|
`gift31` float(10, 2) NULL DEFAULT NULL COMMENT '31号赠送统计',
|
`times31` int NULL DEFAULT NULL COMMENT '31号充值次数',
|
PRIMARY KEY (`id`) USING BTREE,
|
UNIQUE INDEX `st_recharge_client_day_id_uindex`(`id` ASC) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '农户充值日统计' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for st_recharge_client_month
|
-- ----------------------------
|
DROP TABLE IF EXISTS `st_recharge_client_month`;
|
CREATE TABLE `st_recharge_client_month` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`client_id` bigint NULL DEFAULT NULL COMMENT '外键，指向农户',
|
`year` int NULL DEFAULT NULL COMMENT '年度',
|
`amount1` float(10, 2) NULL DEFAULT NULL COMMENT '1月充值统计',
|
`gift1` float(10, 2) NULL DEFAULT NULL COMMENT '1月赠送统计',
|
`times1` int NULL DEFAULT NULL COMMENT '1月充值次数',
|
`amount2` float(10, 2) NULL DEFAULT NULL COMMENT '2月充值统计',
|
`gift2` float(10, 2) NULL DEFAULT NULL COMMENT '2月赠送统计',
|
`times2` int NULL DEFAULT NULL COMMENT '2月充值次数',
|
`amount3` float(10, 2) NULL DEFAULT NULL COMMENT '3月充值统计',
|
`gift3` float(10, 2) NULL DEFAULT NULL COMMENT '3月赠送统计',
|
`times3` int NULL DEFAULT NULL COMMENT '3月充值次数',
|
`amount4` float(10, 2) NULL DEFAULT NULL COMMENT '4月充值统计',
|
`gift4` float(10, 2) NULL DEFAULT NULL COMMENT '4月赠送统计',
|
`times4` int NULL DEFAULT NULL COMMENT '4月充值次数',
|
`amount5` float(10, 2) NULL DEFAULT NULL COMMENT '5月充值统计',
|
`gift5` float(10, 2) NULL DEFAULT NULL COMMENT '5月赠送统计',
|
`times5` int NULL DEFAULT NULL COMMENT '5月充值次数',
|
`amount6` float(10, 2) NULL DEFAULT NULL COMMENT '6月充值统计',
|
`gift6` float(10, 2) NULL DEFAULT NULL COMMENT '6月赠送统计',
|
`times6` int NULL DEFAULT NULL COMMENT '6月充值次数',
|
`amount7` float(10, 2) NULL DEFAULT NULL COMMENT '7月充值统计',
|
`gift7` float(10, 2) NULL DEFAULT NULL COMMENT '7月赠送统计',
|
`times7` int NULL DEFAULT NULL COMMENT '7月充值次数',
|
`amount8` float(10, 2) NULL DEFAULT NULL COMMENT '8月充值统计',
|
`gift8` float(10, 2) NULL DEFAULT NULL COMMENT '8月赠送统计',
|
`times8` int NULL DEFAULT NULL COMMENT '8月充值次数',
|
`amount9` float(10, 2) NULL DEFAULT NULL COMMENT '9月充值统计',
|
`gift9` float(10, 2) NULL DEFAULT NULL COMMENT '9月赠送统计',
|
`times9` int NULL DEFAULT NULL COMMENT '9月充值次数',
|
`amount10` float(10, 2) NULL DEFAULT NULL COMMENT '10月充值统计',
|
`gift10` float(10, 2) NULL DEFAULT NULL COMMENT '10月赠送统计',
|
`times10` int NULL DEFAULT NULL COMMENT '10月充值次数',
|
`amount11` float(10, 2) NULL DEFAULT NULL COMMENT '11月充值统计',
|
`gift11` float(10, 2) NULL DEFAULT NULL COMMENT '11月赠送统计',
|
`times11` int NULL DEFAULT NULL COMMENT '11月充值次数',
|
`amount12` float(10, 2) NULL DEFAULT NULL COMMENT '12月充值统计',
|
`gift12` float(10, 2) NULL DEFAULT NULL COMMENT '12月赠送统计',
|
`times12` int NULL DEFAULT NULL COMMENT '12月充值次数',
|
PRIMARY KEY (`id`) USING BTREE,
|
UNIQUE INDEX `st_recharge_client_month_id_uindex`(`id` ASC) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '农户充值月统计' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for st_recharge_client_year
|
-- ----------------------------
|
DROP TABLE IF EXISTS `st_recharge_client_year`;
|
CREATE TABLE `st_recharge_client_year` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`client_id` bigint NULL DEFAULT NULL COMMENT '外键，指向农户',
|
`year` int NULL DEFAULT NULL COMMENT '年度',
|
`amount` float(10, 2) NULL DEFAULT NULL COMMENT '年充值统计',
|
`gift` float(10, 2) NULL DEFAULT NULL COMMENT '年赠送统计',
|
`times` int NULL DEFAULT NULL COMMENT '年充值次数',
|
PRIMARY KEY (`id`) USING BTREE,
|
UNIQUE INDEX `st_recharge_client_year_id_uindex`(`id` ASC) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = 'IC卡充值月统计' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for st_recharge_ic_day
|
-- ----------------------------
|
DROP TABLE IF EXISTS `st_recharge_ic_day`;
|
CREATE TABLE `st_recharge_ic_day` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`client_id` bigint NULL DEFAULT NULL COMMENT '外键，指向农户',
|
`card_id` bigint NULL DEFAULT NULL COMMENT '外键，指向农户IC卡',
|
`year` int NULL DEFAULT NULL COMMENT '年度',
|
`month` int NULL DEFAULT NULL COMMENT '月份',
|
`amount1` float(10, 2) NULL DEFAULT NULL COMMENT '1号充值统计',
|
`gift1` float(10, 2) NULL DEFAULT NULL COMMENT '1号赠送统计',
|
`times1` int NULL DEFAULT NULL COMMENT '1号充值次数',
|
`amount2` float(10, 2) NULL DEFAULT NULL COMMENT '2号充值统计',
|
`gift2` float(10, 2) NULL DEFAULT NULL COMMENT '2号赠送统计',
|
`times2` int NULL DEFAULT NULL COMMENT '2号充值次数',
|
`amount3` float(10, 2) NULL DEFAULT NULL COMMENT '3号充值统计',
|
`gift3` float(10, 2) NULL DEFAULT NULL COMMENT '3号赠送统计',
|
`times3` int NULL DEFAULT NULL COMMENT '3号充值次数',
|
`amount4` float(10, 2) NULL DEFAULT NULL COMMENT '4号充值统计',
|
`gift4` float(10, 2) NULL DEFAULT NULL COMMENT '4号赠送统计',
|
`times4` int NULL DEFAULT NULL COMMENT '4号充值次数',
|
`amount5` float(10, 2) NULL DEFAULT NULL COMMENT '5号充值统计',
|
`gift5` float(10, 2) NULL DEFAULT NULL COMMENT '5号赠送统计',
|
`times5` int NULL DEFAULT NULL COMMENT '5号充值次数',
|
`amount6` float(10, 2) NULL DEFAULT NULL COMMENT '6号充值统计',
|
`gift6` float(10, 2) NULL DEFAULT NULL COMMENT '6号赠送统计',
|
`times6` int NULL DEFAULT NULL COMMENT '6号充值次数',
|
`amount7` float(10, 2) NULL DEFAULT NULL COMMENT '7号充值统计',
|
`gift7` float(10, 2) NULL DEFAULT NULL COMMENT '7号赠送统计',
|
`times7` int NULL DEFAULT NULL COMMENT '7号充值次数',
|
`amount8` float(10, 2) NULL DEFAULT NULL COMMENT '8号充值统计',
|
`gift8` float(10, 2) NULL DEFAULT NULL COMMENT '8号赠送统计',
|
`times8` int NULL DEFAULT NULL COMMENT '8号充值次数',
|
`amount9` float(10, 2) NULL DEFAULT NULL COMMENT '9号充值统计',
|
`gift9` float(10, 2) NULL DEFAULT NULL COMMENT '9号赠送统计',
|
`times9` int NULL DEFAULT NULL COMMENT '9号充值次数',
|
`amount10` float(10, 2) NULL DEFAULT NULL COMMENT '10号充值统计',
|
`gift10` float(10, 2) NULL DEFAULT NULL COMMENT '10号赠送统计',
|
`times10` int NULL DEFAULT NULL COMMENT '10号充值次数',
|
`amount11` float(10, 2) NULL DEFAULT NULL COMMENT '11号充值统计',
|
`gift11` float(10, 2) NULL DEFAULT NULL COMMENT '11号赠送统计',
|
`times11` int NULL DEFAULT NULL COMMENT '11号充值次数',
|
`amount12` float(10, 2) NULL DEFAULT NULL COMMENT '12号充值统计',
|
`gift12` float(10, 2) NULL DEFAULT NULL COMMENT '12号赠送统计',
|
`times12` int NULL DEFAULT NULL COMMENT '12号充值次数',
|
`amount13` float(10, 2) NULL DEFAULT NULL COMMENT '13号充值统计',
|
`gift13` float(10, 2) NULL DEFAULT NULL COMMENT '13号赠送统计',
|
`times13` int NULL DEFAULT NULL COMMENT '13号充值次数',
|
`amount14` float(10, 2) NULL DEFAULT NULL COMMENT '14号充值统计',
|
`gift14` float(10, 2) NULL DEFAULT NULL COMMENT '14号赠送统计',
|
`times14` int NULL DEFAULT NULL COMMENT '14号充值次数',
|
`amount15` float(10, 2) NULL DEFAULT NULL COMMENT '15号充值统计',
|
`gift15` float(10, 2) NULL DEFAULT NULL COMMENT '15号赠送统计',
|
`times15` int NULL DEFAULT NULL COMMENT '15号充值次数',
|
`amount16` float(10, 2) NULL DEFAULT NULL COMMENT '16号充值统计',
|
`gift16` float(10, 2) NULL DEFAULT NULL COMMENT '16号赠送统计',
|
`times16` int NULL DEFAULT NULL COMMENT '16号充值次数',
|
`amount17` float(10, 2) NULL DEFAULT NULL COMMENT '17号充值统计',
|
`gift17` float(10, 2) NULL DEFAULT NULL COMMENT '17号赠送统计',
|
`times17` int NULL DEFAULT NULL COMMENT '17号充值次数',
|
`amount18` float(10, 2) NULL DEFAULT NULL COMMENT '18号充值统计',
|
`gift18` float(10, 2) NULL DEFAULT NULL COMMENT '18号赠送统计',
|
`times18` int NULL DEFAULT NULL COMMENT '18号充值次数',
|
`amount19` float(10, 2) NULL DEFAULT NULL COMMENT '19号充值统计',
|
`gift19` float(10, 2) NULL DEFAULT NULL COMMENT '19号赠送统计',
|
`times19` int NULL DEFAULT NULL COMMENT '19号充值次数',
|
`amount20` float(10, 2) NULL DEFAULT NULL COMMENT '20号充值统计',
|
`gift20` float(10, 2) NULL DEFAULT NULL COMMENT '20号赠送统计',
|
`times20` int NULL DEFAULT NULL COMMENT '20号充值次数',
|
`amount21` float(10, 2) NULL DEFAULT NULL COMMENT '21号充值统计',
|
`gift21` float(10, 2) NULL DEFAULT NULL COMMENT '21号赠送统计',
|
`times21` int NULL DEFAULT NULL COMMENT '21号充值次数',
|
`amount22` float(10, 2) NULL DEFAULT NULL COMMENT '22号充值统计',
|
`gift22` float(10, 2) NULL DEFAULT NULL COMMENT '22号赠送统计',
|
`times22` int NULL DEFAULT NULL COMMENT '22号充值次数',
|
`amount23` float(10, 2) NULL DEFAULT NULL COMMENT '23号充值统计',
|
`gift23` float(10, 2) NULL DEFAULT NULL COMMENT '23号赠送统计',
|
`times23` int NULL DEFAULT NULL COMMENT '23号充值次数',
|
`amount24` float(10, 2) NULL DEFAULT NULL COMMENT '24号充值统计',
|
`gift24` float(10, 2) NULL DEFAULT NULL COMMENT '24号赠送统计',
|
`times24` int NULL DEFAULT NULL COMMENT '24号充值次数',
|
`amount25` float(10, 2) NULL DEFAULT NULL COMMENT '25号充值统计',
|
`gift25` float(10, 2) NULL DEFAULT NULL COMMENT '25号赠送统计',
|
`times25` int NULL DEFAULT NULL COMMENT '25号充值次数',
|
`amount26` float(10, 2) NULL DEFAULT NULL COMMENT '26号充值统计',
|
`gift26` float(10, 2) NULL DEFAULT NULL COMMENT '26号赠送统计',
|
`times26` int NULL DEFAULT NULL COMMENT '26号充值次数',
|
`amount27` float(10, 2) NULL DEFAULT NULL COMMENT '27号充值统计',
|
`gift27` float(10, 2) NULL DEFAULT NULL COMMENT '27号赠送统计',
|
`times27` int NULL DEFAULT NULL COMMENT '27号充值次数',
|
`amount28` float(10, 2) NULL DEFAULT NULL COMMENT '28号充值统计',
|
`gift28` float(10, 2) NULL DEFAULT NULL COMMENT '28号赠送统计',
|
`times28` int NULL DEFAULT NULL COMMENT '28号充值次数',
|
`amount29` float(10, 2) NULL DEFAULT NULL COMMENT '29号充值统计',
|
`gift29` float(10, 2) NULL DEFAULT NULL COMMENT '29号赠送统计',
|
`times29` int NULL DEFAULT NULL COMMENT '29号充值次数',
|
`amount30` float(10, 2) NULL DEFAULT NULL COMMENT '30号充值统计',
|
`gift30` float(10, 2) NULL DEFAULT NULL COMMENT '30号赠送统计',
|
`times30` int NULL DEFAULT NULL COMMENT '30号充值次数',
|
`amount31` float(10, 2) NULL DEFAULT NULL COMMENT '31号充值统计',
|
`gift31` float(10, 2) NULL DEFAULT NULL COMMENT '31号赠送统计',
|
`times31` int NULL DEFAULT NULL COMMENT '31号充值次数',
|
PRIMARY KEY (`id`) USING BTREE,
|
UNIQUE INDEX `st_recharge_ic_day_id_uindex`(`id` ASC) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = 'IC卡充值日统计' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for st_recharge_ic_month
|
-- ----------------------------
|
DROP TABLE IF EXISTS `st_recharge_ic_month`;
|
CREATE TABLE `st_recharge_ic_month` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`client_id` bigint NULL DEFAULT NULL COMMENT '外键，指向农户',
|
`card_id` bigint NULL DEFAULT NULL COMMENT '外键，指向农户IC卡',
|
`year` int NULL DEFAULT NULL COMMENT '年度',
|
`amount1` float(10, 2) NULL DEFAULT NULL COMMENT '1月充值统计',
|
`gift1` float(10, 2) NULL DEFAULT NULL COMMENT '1月赠送统计',
|
`times1` int NULL DEFAULT NULL COMMENT '1月充值次数',
|
`amount2` float(10, 2) NULL DEFAULT NULL COMMENT '2月充值统计',
|
`gift2` float(10, 2) NULL DEFAULT NULL COMMENT '2月赠送统计',
|
`times2` int NULL DEFAULT NULL COMMENT '2月充值次数',
|
`amount3` float(10, 2) NULL DEFAULT NULL COMMENT '3月充值统计',
|
`gift3` float(10, 2) NULL DEFAULT NULL COMMENT '3月赠送统计',
|
`times3` int NULL DEFAULT NULL COMMENT '3月充值次数',
|
`amount4` float(10, 2) NULL DEFAULT NULL COMMENT '4月充值统计',
|
`gift4` float(10, 2) NULL DEFAULT NULL COMMENT '4月赠送统计',
|
`times4` int NULL DEFAULT NULL COMMENT '4月充值次数',
|
`amount5` float(10, 2) NULL DEFAULT NULL COMMENT '5月充值统计',
|
`gift5` float(10, 2) NULL DEFAULT NULL COMMENT '5月赠送统计',
|
`times5` int NULL DEFAULT NULL COMMENT '5月充值次数',
|
`amount6` float(10, 2) NULL DEFAULT NULL COMMENT '6月充值统计',
|
`gift6` float(10, 2) NULL DEFAULT NULL COMMENT '6月赠送统计',
|
`times6` int NULL DEFAULT NULL COMMENT '6月充值次数',
|
`amount7` float(10, 2) NULL DEFAULT NULL COMMENT '7月充值统计',
|
`gift7` float(10, 2) NULL DEFAULT NULL COMMENT '7月赠送统计',
|
`times7` int NULL DEFAULT NULL COMMENT '7月充值次数',
|
`amount8` float(10, 2) NULL DEFAULT NULL COMMENT '8月充值统计',
|
`gift8` float(10, 2) NULL DEFAULT NULL COMMENT '8月赠送统计',
|
`times8` int NULL DEFAULT NULL COMMENT '8月充值次数',
|
`amount9` float(10, 2) NULL DEFAULT NULL COMMENT '9月充值统计',
|
`gift9` float(10, 2) NULL DEFAULT NULL COMMENT '9月赠送统计',
|
`times9` int NULL DEFAULT NULL COMMENT '9月充值次数',
|
`amount10` float(10, 2) NULL DEFAULT NULL COMMENT '10月充值统计',
|
`gift10` float(10, 2) NULL DEFAULT NULL COMMENT '10月赠送统计',
|
`times10` int NULL DEFAULT NULL COMMENT '10月充值次数',
|
`amount11` float(10, 2) NULL DEFAULT NULL COMMENT '11月充值统计',
|
`gift11` float(10, 2) NULL DEFAULT NULL COMMENT '11月赠送统计',
|
`times11` int NULL DEFAULT NULL COMMENT '11月充值次数',
|
`amount12` float(10, 2) NULL DEFAULT NULL COMMENT '12月充值统计',
|
`gift12` float(10, 2) NULL DEFAULT NULL COMMENT '12月赠送统计',
|
`times12` int NULL DEFAULT NULL COMMENT '12月充值次数',
|
PRIMARY KEY (`id`) USING BTREE,
|
UNIQUE INDEX `st_recharge_ic_month_id_uindex`(`id` ASC) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = 'IC卡充值月统计' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for st_recharge_ic_year
|
-- ----------------------------
|
DROP TABLE IF EXISTS `st_recharge_ic_year`;
|
CREATE TABLE `st_recharge_ic_year` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`client_id` bigint NULL DEFAULT NULL COMMENT '外键，指向农户',
|
`card_id` bigint NULL DEFAULT NULL COMMENT '外键，指向农户IC卡',
|
`year` int NULL DEFAULT NULL COMMENT '年度',
|
`amount` float(10, 2) NULL DEFAULT NULL COMMENT '年充值统计',
|
`gift` float(10, 2) NULL DEFAULT NULL COMMENT '年赠送统计',
|
`times` int NULL DEFAULT NULL COMMENT '年充值次数',
|
PRIMARY KEY (`id`) USING BTREE,
|
UNIQUE INDEX `st_recharge_ic_year_id_uindex`(`id` ASC) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = 'IC卡充值年统计' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for ug_rtu_controller
|
-- ----------------------------
|
DROP TABLE IF EXISTS `ug_rtu_controller`;
|
CREATE TABLE `ug_rtu_controller` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`task_id` bigint NULL DEFAULT NULL COMMENT '外键，指向升级任务',
|
`controller_id` bigint NULL DEFAULT NULL COMMENT '外键，指向控制器',
|
`rtu_addr` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '控制器地址',
|
`is_over` tinyint NULL DEFAULT NULL COMMENT '是否升级完成(1是0否)',
|
`ug_state` int NULL DEFAULT NULL COMMENT '升级状态',
|
`over_dt` timestamp NULL DEFAULT NULL COMMENT '升级完成时间',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = 'RTU远程升级所关联的测控一体阀' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for ug_rtu_program
|
-- ----------------------------
|
DROP TABLE IF EXISTS `ug_rtu_program`;
|
CREATE TABLE `ug_rtu_program` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`hex_file_name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '文件名',
|
`store_ram_addr` varchar(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '升级程序存放地址(4字节，8位十六进制字符)',
|
`start_ram_addr` varchar(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '程序覆盖超始地址（4字节，8位十六进制字符）',
|
`program_calculate_bytes` int NULL DEFAULT NULL COMMENT '升级程序计算字节数',
|
`program_crc16` int NULL DEFAULT NULL COMMENT '升级程序CRC16校验码',
|
`program_bytes` longblob NULL COMMENT '升级程序内容（解析后程序有效内容字节数组）',
|
`file_bytes` longblob NULL COMMENT '升级程序文件内容（解析前的程序文件内容字节数组）',
|
`remark` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '备注',
|
`dt` timestamp NULL DEFAULT NULL COMMENT '上传时间',
|
`deleted` tinyint NULL DEFAULT NULL COMMENT '删除标识',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = 'RTU远程升级程序' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for ug_rtu_task
|
-- ----------------------------
|
DROP TABLE IF EXISTS `ug_rtu_task`;
|
CREATE TABLE `ug_rtu_task` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`program_id` bigint NULL DEFAULT NULL COMMENT '外键，指向升级程序',
|
`creator` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '创建人',
|
`remark` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '备注',
|
`dt` timestamp NULL DEFAULT NULL COMMENT '创建时间',
|
`is_execute` tinyint NULL DEFAULT NULL COMMENT '是否执行',
|
`is_over` tinyint NULL DEFAULT NULL COMMENT '是否结束（1是，0否）',
|
`deleted` tinyint NULL DEFAULT NULL COMMENT '删除标识',
|
PRIMARY KEY (`id`) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = 'RTU远程升级任务' ROW_FORMAT = DYNAMIC;
|
|
-- ----------------------------
|
-- Table structure for web_file
|
-- ----------------------------
|
DROP TABLE IF EXISTS `web_file`;
|
CREATE TABLE `web_file` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`org_name` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '文件原名称',
|
`ext_name` varchar(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '扩展名',
|
`new_name` varchar(30) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '上传文件后系统自动给文件赋的新名称',
|
`hash` int NULL DEFAULT NULL COMMENT '文件hash值',
|
`file_path` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '文件在服务端存储的相对路径',
|
`dt` timestamp NULL DEFAULT NULL COMMENT '文件上载日期',
|
PRIMARY KEY (`id`) USING BTREE,
|
UNIQUE INDEX `oth_file_id_uindex`(`id` ASC) USING BTREE
|
) ENGINE = InnoDB CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci COMMENT = '上载的文件信息' ROW_FORMAT = Dynamic;
|
|
-- ----------------------------
|
-- View structure for v_operate
|
-- ----------------------------
|
DROP VIEW IF EXISTS `v_operate`;
|
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `v_operate` AS select `act`.`id` AS `orderNumber`,`cli`.`villageId` AS `villageId`,`cli`.`districtTitle` AS `districtTitle`,`cli`.`clientNum` AS `clientNum`,`cli`.`name` AS `name`,`card`.`cardNum` AS `cardNum`,`cli`.`idCard` AS `idCard`,`cli`.`phone` AS `phone`,0 AS `money`,0 AS `gift`,`act`.`cardCost` AS `cardCost`,0 AS `afterRecharge`,`act`.`paymentId` AS `paymentId`,(select `se_payment_method`.`name` from `se_payment_method` where (`se_payment_method`.`id` = `act`.`paymentId`)) AS `paymentMethod`,'开卡' AS `operateType`,`act`.`operateDt` AS `operateDt`,`user`.`name` AS `operatorName` from (((`se_active_card` `act` join `se_client` `cli` on((`act`.`clientId` = `cli`.`id`))) join `se_client_card` `card` on((`act`.`cardId` = `card`.`id`))) join `ba_user` `user` on((`act`.`operator` = `user`.`id`))) union all select `rec`.`id` AS `orderNumber`,`cli`.`villageId` AS `villageId`,`cli`.`districtTitle` AS `districtTitle`,`cli`.`clientNum` AS `clientNum`,`cli`.`name` AS `name`,`card`.`cardNum` AS `cardNum`,`cli`.`idCard` AS `idCard`,`cli`.`phone` AS `phone`,`rec`.`amount` AS `money`,`rec`.`gift` AS `gift`,0 AS `cardCost`,`rec`.`afterRecharge` AS `afterRecharge`,`rec`.`paymentId` AS `paymentId`,(select `se_payment_method`.`name` from `se_payment_method` where (`se_payment_method`.`id` = `rec`.`paymentId`)) AS `paymentMethod`,'充值' AS `operateType`,`rec`.`operateDt` AS `operateDt`,`user`.`name` AS `operatorName` from (((`se_recharge_history` `rec` join `se_client` `cli` on((`rec`.`clientId` = `cli`.`id`))) join `se_client_card` `card` on((`rec`.`cardId` = `card`.`id`))) join `ba_user` `user` on((`rec`.`operator` = `user`.`id`))) union all select `can`.`id` AS `orderNumber`,`cli`.`villageId` AS `villageId`,`cli`.`districtTitle` AS `districtTitle`,`cli`.`clientNum` AS `clientNum`,`cli`.`name` AS `name`,`card`.`cardNum` AS `cardNum`,`cli`.`idCard` AS `idCard`,`cli`.`phone` AS `phone`,-(`can`.`refund`) AS `money`,0 AS `gift`,0 AS `cardCost`,0 AS `afterRecharge`,1 AS `paymentId`,(case when (`can`.`refundType` = 1) then '现金' end) AS `paymentMethod`,'注销' AS `operateType`,`can`.`operateDt` AS `operateDt`,`user`.`name` AS `operatorName` from (((`se_cancel` `can` join `se_client` `cli` on((`can`.`clientId` = `cli`.`id`))) join `se_client_card` `card` on((`can`.`cardId` = `card`.`id`))) join `ba_user` `user` on((`can`.`operator` = `user`.`id`)));
|
|
-- ----------------------------
|
-- View structure for v_trade_details
|
-- ----------------------------
|
DROP VIEW IF EXISTS `v_trade_details`;
|
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `v_trade_details` AS select `ope`.`id` AS `orderNumber`,`cli`.`name` AS `clientName`,`card`.`cardNum` AS `cardNum`,`cli`.`idCard` AS `idCard`,`cli`.`clientNum` AS `clientNum`,`cli`.`phone` AS `phone`,(case `ope`.`operate_type` when 1 then '开卡' when 2 then '充值' when 3 then '销卡' when 5 then '补扣' when 10 then '返还' end) AS `operateType`,(ifnull(`ope`.`trade_amount`,0.00) + ifnull(`ope`.`card_cost`,0.00)) AS `tradeAmount`,ifnull(`ope`.`trade_amount`,0.00) AS `waterCost`,ifnull(`ope`.`card_cost`,0.00) AS `cardCost`,ifnull(`ope`.`gift`,0.00) AS `gift`,ifnull(`ope`.`refund_amount`,0.00) AS `refundAmount`,`card`.`money` AS `money`,`pay`.`name` AS `paymentName`,`ope`.`operate_dt` AS `tradeTime`,`us`.`id` AS `cashierId`,`us`.`name` AS `cashierName`,(case `card`.`state` when 1 then '正常' when 2 then '已注销' when 3 then '已挂失' end) AS `cardState`,`cli`.`districtTitle` AS `districtTitle`,`cli`.`villageId` AS `villageId` from ((((`se_card_operate` `ope` left join `se_client` `cli` on((`ope`.`client_id` = `cli`.`id`))) left join `se_client_card` `card` on((`card`.`id` = `ope`.`card_id`))) left join `se_payment_method` `pay` on((`ope`.`payment_id` = `pay`.`id`))) left join `ba_user` `us` on((`ope`.`operator` = `us`.`id`))) where ((`ope`.`operate_type` in (1,2,3,5,10)) and (`ope`.`operate_valid` = 2));
|
|
-- ----------------------------
|
-- View structure for v_trade_summary
|
-- ----------------------------
|
DROP VIEW IF EXISTS `v_trade_summary`;
|
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `v_trade_summary` AS select (case `ope`.`operate_type` when 1 then '开卡' when 2 then '充值' when 3 then '销卡' when 5 then '补扣' end) AS `operateType`,cast(`ope`.`operate_dt` as date) AS `tradeDate`,count(0) AS `count`,(ifnull(sum(`ope`.`trade_amount`),0) + ifnull(sum(`ope`.`card_cost`),0)) AS `received`,ifnull(sum(`ope`.`gift`),0) AS `gift`,ifnull(sum(`ope`.`refund_amount`),0) AS `refundAmount`,cast(`us`.`id` as char charset utf8mb4) AS `cashierId` from ((`se_card_operate` `ope` join `se_payment_method` `pay` on((`ope`.`payment_id` = `pay`.`id`))) join `ba_user` `us` on((`ope`.`operator` = `us`.`id`))) where (`ope`.`operate_type` in (1,2,3,5)) group by `ope`.`operate_type`,cast(`ope`.`operate_dt` as date),`us`.`id` union all select '充值' AS `operateType`,cast(`se_wallet_recharge`.`recharge_time` as date) AS `tradeDate`,count(0) AS `count`,ifnull(sum(`se_wallet_recharge`.`amount`),0) AS `received`,0 AS `gift`,0 AS `refundAmount`,cast(1000000 as char charset utf8mb4) AS `cashierId` from `se_wallet_recharge` group by cast(`se_wallet_recharge`.`recharge_time` as date) union all select '销卡' AS `operateType`,cast(`se_refund`.`audit_time` as date) AS `tradeDate`,count(0) AS `count`,ifnull(-(sum(`se_refund`.`refund_amount`)),0) AS `received`,0 AS `gift`,0 AS `refundAmount`,cast(1000000 as char charset utf8mb4) AS `cashierId` from `se_refund` where (`se_refund`.`refund_status` = 2) group by cast(`se_refund`.`audit_time` as date);
|
|
-- ----------------------------
|
-- View structure for v_transactionstatistics
|
-- ----------------------------
|
DROP VIEW IF EXISTS `v_transactionstatistics`;
|
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `v_transactionstatistics` AS select (case `ope`.`operate_type` when 1 then '开卡' when 2 then '充值' when 3 then '销卡' when 4 then '补卡' end) AS `operateType`,cast(`ope`.`operate_dt` as date) AS `tradeDate`,count(0) AS `count`,(ifnull(sum(`ope`.`trade_amount`),0) + ifnull(sum(`ope`.`card_cost`),0)) AS `received`,ifnull(sum(`ope`.`gift`),0) AS `gift` from (`se_card_operate` `ope` join `se_payment_method` `pay` on((`ope`.`payment_id` = `pay`.`id`))) where (`ope`.`operate_type` in (1,2,3,4)) group by `ope`.`operate_type`,cast(`ope`.`operate_dt` as date);
|
|
SET FOREIGN_KEY_CHECKS = 1;
|
