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

ALTER TABLE `4dkankan_v4`.`t_rtk_device`
    ADD COLUMN `account_type` int NULL DEFAULT 0 COMMENT '0账号池，1专用账号' AFTER `failure_time`;
ALTER TABLE `4dkankan_v4`.`t_rtk_device`
    ADD COLUMN `camera_type` int NULL COMMENT '0看看，1看见，2深时，3深光' AFTER `account_type`;

ALTER TABLE `4dkankan_v4`.`t_rtk_device`
    ADD COLUMN `user_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL AFTER `camera_type`,
ADD COLUMN `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL AFTER `user_name`,
ADD COLUMN `ip_addr` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL AFTER `password`,
ADD COLUMN `mount_point` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL AFTER `ip_addr`,
ADD COLUMN `port` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL AFTER `mount_point`,
ADD COLUMN `operator` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL  COMMENT '运营商' AFTER `port`;



ALTER TABLE `4dkankan_v4`.`t_rtk_account`
DROP COLUMN `type`;


CREATE TABLE `t_manage_agent_log` (
                                      `id` int NOT NULL AUTO_INCREMENT,
                                      `agent_id` int DEFAULT NULL,
                                      `down_add_num` int DEFAULT NULL,
                                      `major_add_num` int DEFAULT NULL,
                                      `high_add_num` int DEFAULT NULL,
                                      `sys_user_id` int DEFAULT NULL,
                                      `rec_status` varchar(255) DEFAULT 'A',
                                      `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
                                      `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                                      PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


ALTER TABLE `4dkankan_v4`.`t_manage_agent_log`
    ADD COLUMN `down_add_num_total` varchar(255) NULL AFTER `high_add_num`,
ADD COLUMN `major_add_num_total` varchar(255) NULL AFTER `down_add_num_total`,
ADD COLUMN `high_add_num_total` varchar(255) NULL AFTER `major_add_num_total`;



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;