/* 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;