4dkankan-center-manage/doc/update-eur-2.8.0.sql

/*
 Navicat Premium Data Transfer

 Source Server         : 4dkk120.24.144.164
 Source Server Type    : MySQL
 Source Server Version : 80025
 Source Host           : 120.24.144.164:3306
 Source Schema         : 4dkankan_v4

 Target Server Type    : MySQL
 Target Server Version : 80025
 File Encoding         : 65001

 Date: 24/01/2025 09:37:16
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for t_rtk_account
-- ----------------------------
DROP TABLE IF EXISTS `t_rtk_account`;
CREATE TABLE `t_rtk_account`  (
                                  `id` int NOT NULL AUTO_INCREMENT,
                                  `user_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
                                  `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
                                  `ip_addr` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
                                  `mount_point` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
                                  `port` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
                                  `operator` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '运营商',
                                  `status` int NULL DEFAULT 0 COMMENT '使用状态0初始状态未激活，1未使用，2正在使用，3已停用',
                                  `failure_time` timestamp NULL DEFAULT NULL COMMENT '失效时间',
                                  `use_status` int NULL DEFAULT 0 COMMENT '0正常，1禁用',
                                  `rec_status` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT 'A',
                                  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
                                  `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                                  `create_user_id` int NULL DEFAULT NULL,
                                  `update_user_id` int NULL DEFAULT NULL,
                                  `del_user_id` int NULL DEFAULT NULL,
                                  PRIMARY KEY (`id`) USING BTREE,
                                  UNIQUE INDEX `uni_user_name_rec_status`(`user_name`, `rec_status`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 299 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_rtk_device
-- ----------------------------
DROP TABLE IF EXISTS `t_rtk_device`;
CREATE TABLE `t_rtk_device`  (
                                 `id` int NOT NULL AUTO_INCREMENT,
                                 `rtk_sn_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
                                 `camera_sn` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
                                 `sg_rtk_sn` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
                                 `rtk_type` int NULL DEFAULT 0,
                                 `use_status` int NULL DEFAULT 0 COMMENT '0正常，1禁用',
                                 `rec_status` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT 'A',
                                 `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
                                 `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                                 `create_user_id` int NULL DEFAULT NULL,
                                 `update_user_id` int NULL DEFAULT NULL,
                                 `del_user_id` int NULL DEFAULT NULL,
                                 `failure_time` timestamp NULL DEFAULT NULL COMMENT '失效时间',
                                 `account_type` int NULL DEFAULT 0 COMMENT '0账号池，1专用账号',
                                 `camera_type` int NULL DEFAULT NULL COMMENT '0看看，1看见，2深时，3深光',
                                 `user_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
                                 `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
                                 `ip_addr` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
                                 `mount_point` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
                                 `port` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
                                 `operator` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '运营商',
                                 PRIMARY KEY (`id`) USING BTREE,
                                 UNIQUE INDEX `uni_rtk_sn_code_rec_status`(`rtk_sn_code`, `rec_status`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 616 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;




INSERT INTO t_rtk_device(rtk_sn_code,camera_sn,sg_rtk_sn,rtk_type,create_user_id,update_user_id,user_name,`password`,ip_addr,mount_point,`port`,operator,account_type,failure_time)
SELECT rtk_sn_code,camera_sn_code,sg_rtk_sn,rtk_type,create_user_id,update_user_id,user_name,`password`,ip_addr,mount_point,`port`,operator,1 as account_type,failure_time FROM t_rtk_info WHERE rec_status = 'A' and rtk_sn_code is not null and rtk_sn_code != ''

CREATE TEMPORARY TABLE temp_table AS
SELECT rd.id,camera_sn ,camera_type,d.type from t_rtk_device rd
                                                    LEFT JOIN t_camera a on rd.camera_sn = a.sn_code
                                                    LEFT JOIN t_camera_detail d on a.id = d.camera_id
WHERE rd.rec_status ='A' and camera_sn is not null and camera_type  is null;


UPDATE t_rtk_device set camera_type = 0 WHERE id in (SELECT id from temp_table WHERE type = 1);

UPDATE t_rtk_device set camera_type = 1 WHERE id in (SELECT id from temp_table WHERE type = 9);

UPDATE t_rtk_device set camera_type = 2 WHERE id in (SELECT id from temp_table WHERE type = 10);

UPDATE t_rtk_device set camera_type = 3 WHERE id in (SELECT id from temp_table WHERE type = 11);

DROP TABLE IF EXISTS temp_table;