跑登录日志、操作日志

操作日志：

CREATE GenerateActivityLogs(
IN start_id INT,
IN num_rows INT,
IN start_date DATE,
IN end_date DATE
)
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE current_id INT;
DECLARE random_log_type INT;
DECLARE random_subject_id INT;
DECLARE random_causer_id INT;
DECLARE random_date DATE;
DECLARE random_time TIME;
DECLARE random_datetime DATETIME;
DECLARE update_datetime DATETIME;
DECLARE product_count INT;
DECLARE customer_count INT;
DECLARE sales_order_count INT;
DECLARE production_task_count INT;
DECLARE user_count INT;
DECLARE days_range INT;
DECLARE log_name_val VARCHAR(255) CHARSET utf8mb4;
DECLARE description_val VARCHAR(255) CHARSET utf8mb4;
DECLARE subject_type_val VARCHAR(255) CHARSET utf8mb4;
DECLARE subject_code_val VARCHAR(255) CHARSET utf8mb4;
DECLARE properties_json JSON;

-- 设置会话字符集
SET NAMES utf8mb4;
SET CHARACTER SET utf8mb4;

-- 开始事务
START TRANSACTION;

SET current_id = start_id;
SET days_range = DATEDIFF(end_date, start_date);

-- 获取各表数量
SELECT COUNT(*) INTO product_count FROM products;
SELECT COUNT(*) INTO customer_count FROM customers;
SELECT COUNT(*) INTO sales_order_count FROM sales_orders;
SELECT COUNT(*) INTO production_task_count FROM production_tasks;
SELECT COUNT(*) INTO user_count FROM users;

WHILE i < num_rows DO
    -- 随机选择日志类型：1=产品, 2=客户, 3=销售订单, 4=生产任务
    SET random_log_type = FLOOR(1 + RAND() * 4);

    -- 随机选择操作用户
    SELECT id INTO random_causer_id FROM users ORDER BY RAND() LIMIT 1;

    -- 生成随机创建日期
    SET random_date = DATE_ADD(start_date, INTERVAL FLOOR(RAND() * (days_range + 1)) DAY);
    SET random_time = SEC_TO_TIME(FLOOR(RAND() * 35400) + 28800);
    SET random_datetime = TIMESTAMP(random_date, random_time);

    -- 生成随机更新日期（50%概率与创建时间不同）
    IF RAND() > 0.5 THEN
        SET update_datetime = DATE_ADD(random_datetime, INTERVAL FLOOR(1 + RAND() * 1440) MINUTE);
    ELSE
        SET update_datetime = random_datetime;
    END IF;

    CASE random_log_type
        WHEN 1 THEN -- 产品
            IF product_count > 0 THEN
                SELECT id, CONVERT(code USING utf8mb4) INTO random_subject_id, subject_code_val 
                FROM products ORDER BY RAND() LIMIT 1;

                SET log_name_val = 'products';
                SET subject_type_val = 'App\\Models\\Product';

                -- 生成创建产品记录
                SET description_val = CONCAT('创建产品{', subject_code_val, '}');
                SET properties_json = JSON_OBJECT(
                    'attributes', JSON_OBJECT(
                        'id', random_subject_id,
                        'code', subject_code_val,
                        'created_at', DATE_FORMAT(random_datetime, '%Y-%m-%d %H:%i:%s'),
                        'updated_at', DATE_FORMAT(random_datetime, '%Y-%m-%d %H:%i:%s')
                    )
                );

                INSERT INTO `activity_log` (
                    `id`, `log_name`, `description`, `subject_id`, `subject_type`, 
                    `causer_id`, `causer_type`, `properties`, `location`, 
                    `created_at`, `updated_at`
                ) VALUES (
                    current_id, log_name_val, description_val, random_subject_id, subject_type_val,
                    random_causer_id, 'App\\Models\\User', properties_json, '江西宜春',
                    random_datetime, random_datetime
                );

                SET current_id = current_id + 1;

                -- 如果创建时间和更新时间不同，生成更新产品记录
                IF update_datetime != random_datetime THEN
                    SET description_val = CONCAT('更新产品{', subject_code_val, '}');
                    SET properties_json = JSON_OBJECT(
                        'old', JSON_OBJECT(
                            'updated_at', DATE_FORMAT(random_datetime, '%Y-%m-%d %H:%i:%s')
                        ),
                        'attributes', JSON_OBJECT(
                            'updated_at', DATE_FORMAT(update_datetime, '%Y-%m-%d %H:%i:%s')
                        )
                    );

                    INSERT INTO `activity_log` (
                        `id`, `log_name`, `description`, `subject_id`, `subject_type`, 
                        `causer_id`, `causer_type`, `properties`, `location`, 
                        `created_at`, `updated_at`
                    ) VALUES (
                        current_id, log_name_val, description_val, random_subject_id, subject_type_val,
                        random_causer_id, 'App\\Models\\User', properties_json, '江西宜春',
                        update_datetime, update_datetime
                    );

                    SET current_id = current_id + 1;
                END IF;
            END IF;

        WHEN 2 THEN -- 客户
            IF customer_count > 0 THEN
                SELECT id, CONVERT(name USING utf8mb4) INTO random_subject_id, subject_code_val 
                FROM customers ORDER BY RAND() LIMIT 1;

                SET log_name_val = 'customers';
                SET subject_type_val = 'App\\Models\\Customer';

                -- 生成创建客户记录
                SET description_val = CONCAT('创建客户{', subject_code_val, '}');
                SET properties_json = JSON_OBJECT(
                    'attributes', JSON_OBJECT(
                        'id', random_subject_id,
                        'name', subject_code_val,
                        'created_at', DATE_FORMAT(random_datetime, '%Y-%m-%d %H:%i:%s'),
                        'updated_at', DATE_FORMAT(random_datetime, '%Y-%m-%d %H:%i:%s')
                    )
                );

                INSERT INTO `activity_log` (
                    `id`, `log_name`, `description`, `subject_id`, `subject_type`, 
                    `causer_id`, `causer_type`, `properties`, `ip`, `location`, 
                    `created_at`, `updated_at`
                ) VALUES (
                    current_id, log_name_val, description_val, random_subject_id, subject_type_val,
                    random_causer_id, 'App\\Models\\User', properties_json, '115.153.102.58', '江西宜春',
                    random_datetime, random_datetime
                );

                SET current_id = current_id + 1;
            END IF;

        WHEN 3 THEN -- 销售订单
            IF sales_order_count > 0 THEN
                SELECT id, CONVERT(serial_code USING utf8mb4) INTO random_subject_id, subject_code_val 
                FROM sales_orders ORDER BY RAND() LIMIT 1;

                SET log_name_val = 'sales_orders';
                SET subject_type_val = 'App\\Models\\SalesOrder';

                -- 生成创建订单记录
                SET description_val = CONCAT('创建订单{', subject_code_val, '}');
                SET properties_json = JSON_OBJECT(
                    'attributes', JSON_OBJECT(
                        'id', random_subject_id,
                        'serial_code', subject_code_val,
                        'created_at', DATE_FORMAT(random_datetime, '%Y-%m-%d %H:%i:%s'),
                        'updated_at', DATE_FORMAT(random_datetime, '%Y-%m-%d %H:%i:%s')
                    )
                );

                INSERT INTO `activity_log` (
                    `id`, `log_name`, `description`, `subject_id`, `subject_type`, 
                    `causer_id`, `causer_type`, `properties`, `ip`, `location`, 
                    `created_at`, `updated_at`
                ) VALUES (
                    current_id, log_name_val, description_val, random_subject_id, subject_type_val,
                    random_causer_id, 'App\\Models\\User', properties_json, '115.153.102.58', '江西宜春',
                    random_datetime, random_datetime
                );

                SET current_id = current_id + 1;

                -- 如果创建时间和更新时间不同，生成更新订单记录
                IF update_datetime != random_datetime THEN
                    SET description_val = CONCAT('更新订单{', subject_code_val, '}');
                    SET properties_json = JSON_OBJECT(
                        'old', JSON_OBJECT(
                            'updated_at', DATE_FORMAT(random_datetime, '%Y-%m-%d %H:%i:%s')
                        ),
                        'attributes', JSON_OBJECT(
                            'updated_at', DATE_FORMAT(update_datetime, '%Y-%m-%d %H:%i:%s')
                        )
                    );

                    INSERT INTO `activity_log` (
                        `id`, `log_name`, `description`, `subject_id`, `subject_type`, 
                        `causer_id`, `causer_type`, `properties`, `ip`, `location`, 
                        `created_at`, `updated_at`
                    ) VALUES (
                        current_id, log_name_val, description_val, random_subject_id, subject_type_val,
                        random_causer_id, 'App\\Models\\User', properties_json, '115.153.102.58', '江西宜春',
                        update_datetime, update_datetime
                    );

                    SET current_id = current_id + 1;

                    -- 随机生成打开订单详情记录（30%概率）
                    IF RAND() < 0.3 THEN
                        SET description_val = CONCAT('打开订单详情{', subject_code_val, '}');
                        SET properties_json = JSON_OBJECT();

                        INSERT INTO `activity_log` (
                            `id`, `log_name`, `description`, `subject_id`, `subject_type`, 
                            `causer_id`, `causer_type`, `properties`, `ip`, `location`, 
                            `created_at`, `updated_at`
                        ) VALUES (
                            current_id, log_name_val, description_val, random_subject_id, subject_type_val,
                            random_causer_id, 'App\\Models\\User', properties_json, '115.153.102.58', '江西宜春',
                            update_datetime, update_datetime
                        );

                        SET current_id = current_id + 1;
                    END IF;
                END IF;
            END IF;

        WHEN 4 THEN -- 生产任务
            IF production_task_count > 0 THEN
                SELECT id, CONVERT(serial_code USING utf8mb4) INTO random_subject_id, subject_code_val 
                FROM production_tasks ORDER BY RAND() LIMIT 1;

                SET log_name_val = 'production_tasks';
                SET subject_type_val = 'App\\Models\\ProductionTask';

                -- 生成创建生产任务记录
                SET description_val = CONCAT('创建生产任务{', subject_code_val, '}');
                SET properties_json = JSON_OBJECT(
                    'attributes', JSON_OBJECT(
                        'id', random_subject_id,
                        'task_code', subject_code_val,
                        'created_at', DATE_FORMAT(random_datetime, '%Y-%m-%d %H:%i:%s'),
                        'updated_at', DATE_FORMAT(random_datetime, '%Y-%m-%d %H:%i:%s')
                    )
                );

                INSERT INTO `activity_log` (
                    `id`, `log_name`, `description`, `subject_id`, `subject_type`, 
                    `causer_id`, `causer_type`, `properties`, `location`, 
                    `created_at`, `updated_at`
                ) VALUES (
                    current_id, log_name_val, description_val, random_subject_id, subject_type_val,
                    random_causer_id, 'App\\Models\\User', properties_json, '江西宜春',
                    random_datetime, random_datetime
                );

                SET current_id = current_id + 1;

                -- 如果创建时间和更新时间不同，生成更新生产任务记录
                IF update_datetime != random_datetime THEN
                    SET description_val = CONCAT('更新生产任务{', subject_code_val, '}');
                    SET properties_json = JSON_OBJECT(
                        'old', JSON_OBJECT(
                            'updated_at', DATE_FORMAT(random_datetime, '%Y-%m-%d %H:%i:%s')
                        ),
                        'attributes', JSON_OBJECT(
                            'updated_at', DATE_FORMAT(update_datetime, '%Y-%m-%d %H:%i:%s')
                        )
                    );

                    INSERT INTO `activity_log` (
                        `id`, `log_name`, `description`, `subject_id`, `subject_type`, 
                        `causer_id`, `causer_type`, `properties`, `location`, 
                        `created_at`, `updated_at`
                    ) VALUES (
                        current_id, log_name_val, description_val, random_subject_id, subject_type_val,
                        random_causer_id, 'App\\Models\\User', properties_json, '江西宜春',
                        update_datetime, update_datetime
                    );

                    SET current_id = current_id + 1;
                END IF;
            END IF;
    END CASE;

    SET i = i + 1;
END WHILE;

-- 提交事务
COMMIT;

-- 返回成功信息
SELECT CONCAT('成功生成 ', i, ' 条活动日志记录，ID范围: ', start_id, ' - ', current_id - 1) AS result;

END

跑登录日志，根据时间间隔更改，如下：时间间隔225=2025/09/26-2025/03/17

CREATE PROCEDURE GenerateLoginLogData(IN num_rows INT)
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE current_id INT DEFAULT 0;
DECLARE random_username VARCHAR(255);
DECLARE random_name VARCHAR(255);
DECLARE random_user_info VARCHAR(255);
DECLARE random_date DATE;
DECLARE random_time TIME;
DECLARE random_datetime DATETIME;
DECLARE user_count INT;

-- 获取用户表中的用户数量
SELECT COUNT(*) INTO user_count FROM users;
  SELECT MAX(id)+1 into current_id FROM login_log ;

        -- 生成10条活动日志，

— CALL GenerateLoginLogData(10′);

-- 如果用户表为空，则使用默认值
IF user_count = 0 THEN
    SET random_user_info = 'admin-系统管理员';
ELSE
    WHILE i < num_rows DO
        -- 随机选择用户名和姓名
        SELECT username, name INTO random_username, random_name 
        FROM users 
        ORDER BY RAND() 
        LIMIT 1;

        -- 拼接用户信息
        SET random_user_info = CONCAT(random_username, '-', random_name);

        -- 生成随机日期（2025-03-17 到 2025-09-26）
        SET random_date = DATE_ADD('2025-03-15', INTERVAL FLOOR(RAND() * 225) DAY);

        -- 生成随机时间（08:00:00 到 17:50:00）
        SET random_time = SEC_TO_TIME(FLOOR(RAND() * 35400) + 28800); -- 28800秒=8小时, 35400秒=9小时50分钟

        -- 组合日期时间
        SET random_datetime = CONCAT(random_date, ' ', random_time);

        -- 插入登录日志
        INSERT INTO `login_log` 
        (`id`, `user_info`, `ip`, `location`, `method`, `user_agent`, `remark`, `created_at`, `updated_at`) 
        VALUES 
        (current_id, random_user_info, '118.212.198.82', '江西宜春', 'POST', 
         'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWeb', '登录成功', 
         random_datetime, random_datetime);

        SET i = i + 1;
        SET current_id = current_id + 1;
    END WHILE;
END IF;

END