EnterpriseSalesManagementSystem/sql/storeprocess.sql

-- 该存储过程应接受客户ID、销售人员ID、产品列表和数量作为参数，并自动计算总金额。
DELIMITER //
CREATE PROCEDURE InsertNewOrder (
    IN p_customer_id INT,
    IN p_salesperson_id INT,
    IN p_product_list JSON,  
    OUT p_order_id INT
)
BEGIN
    DECLARE v_total_amount DECIMAL(10, 2) DEFAULT 0;
    DECLARE v_product_id INT;
    DECLARE v_quantity INT;
    DECLARE v_unit_price DECIMAL(10, 2);
    DECLARE v_done BOOLEAN DEFAULT FALSE;
    DECLARE cur_product CURSOR FOR SELECT product_id, quantity FROM JSON_TABLE(p_product_list, '$[*]' COLUMNS (product_id INT PATH '$.product_id', quantity INT PATH '$.quantity')) AS products;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;
    -- 开始事务
    START TRANSACTION;
    -- 插入销售订单
    INSERT INTO sales_orders (order_date, customer_id, salesperson_id, total_amount)
    VALUES (CURDATE(), p_customer_id, p_salesperson_id, 0);
    -- 获取新插入的订单ID
    SET p_order_id = LAST_INSERT_ID();
    -- 遍历产品列表并插入订单详情
    OPEN cur_product;
    read_loop: LOOP
        FETCH cur_product INTO v_product_id, v_quantity;
        IF v_done THEN
            LEAVE read_loop;
        END IF;
        -- 获取产品单价
        SELECT price INTO v_unit_price FROM products WHERE product_id = v_product_id;
        -- 插入订单详情
        INSERT INTO order_details (order_id, product_id, quantity, unit_price)
        VALUES (p_order_id, v_product_id, v_quantity, v_unit_price);
        -- 累加总金额
        SET v_total_amount = v_total_amount + (v_unit_price * v_quantity);
    END LOOP;
    CLOSE cur_product;
    -- 更新销售订单的总金额
    UPDATE sales_orders SET total_amount = v_total_amount WHERE order_id = p_order_id;
    -- 提交事务
    COMMIT;
END //
DELIMITER ;
CALL InsertNewOrder(1, 2, '[{"product_id": 1, "quantity": 2}, {"product_id": 3, "quantity": 1}]', @p_order_id);
-- 查询新插入的订单ID
SELECT @p_order_id;

-- 该存储过程应接受产品ID和调整的数量作为参数，并根据正负值增加或减少库存。
DELIMITER //
CREATE PROCEDURE UpdateInventory (
    IN p_product_id INT,
    IN p_quantity_change INT
)
BEGIN
    -- 开始事务
    START TRANSACTION;
    -- 更新库存数量
    UPDATE products 
    SET stock_quantity = stock_quantity + p_quantity_change 
    WHERE product_id = p_product_id;
    -- 如果库存数量变为负数，则回滚事务
    IF (SELECT stock_quantity FROM products WHERE product_id = p_product_id) < 0 THEN
        ROLLBACK;
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '库存不足，无法完成操作';
    ELSE
        -- 提交事务
        COMMIT;
    END IF;
END //
DELIMITER ;

-- 该存储过程应返回每个销售人员的总销售额、订单数量和平均订单金额。
DELIMITER //
CREATE PROCEDURE GenerateSalesReport (
    IN p_start_date DATE,
    IN p_end_date DATE
)
BEGIN
    SELECT 
        sp.saleperson_name,
        COUNT(so.order_id) AS total_orders,
        SUM(so.total_amount) AS total_sales,
        AVG(so.total_amount) AS avg_order_amount
    FROM sales_people sp
    JOIN sales_orders so ON sp.salesperson_id = so.salesperson_id
    WHERE so.order_date BETWEEN p_start_date AND p_end_date
    GROUP BY sp.saleperson_name;
END //
DELIMITER ;

-- 该存储过程应接受订单ID、退货原因和退货日期作为参数，并更新相关表
DELIMITER //
CREATE PROCEDURE ProcessReturn (
    IN p_order_id INT,
    IN p_return_reason VARCHAR(30),
    IN p_return_date DATE
)
BEGIN
    DECLARE v_product_id INT;
    DECLARE v_quantity INT;
    DECLARE v_done BOOLEAN DEFAULT FALSE;
    DECLARE cur_order_detail CURSOR FOR SELECT product_id, quantity FROM order_details WHERE order_id = p_order_id;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;
    -- 开始事务
    START TRANSACTION;
    -- 插入退货记录
    INSERT INTO returns (return_date, order_id, reason)
    VALUES (p_return_date, p_order_id, p_return_reason);
    -- 遍历订单详情并恢复库存
    OPEN cur_order_detail;
    read_loop: LOOP
        FETCH cur_order_detail INTO v_product_id, v_quantity;
        IF v_done THEN
            LEAVE read_loop;
        END IF;
        -- 更新产品库存
        UPDATE products 
        SET stock_quantity = stock_quantity + v_quantity 
        WHERE product_id = v_product_id;
    END LOOP;
    CLOSE cur_order_detail;
    -- 提交事务
    COMMIT;
END //
DELIMITER ;

-- 该存储过程应接受一个包含销售人员ID和新KPI等级的JSON数组作为参数，并批量更新kpi表。
DELIMITER //
CREATE PROCEDURE BatchUpdateKPI (
    IN p_kpi_updates JSON  -- JSON格式的KPI更新列表，例如: [{"salesperson_id": 1, "grade": 90}, {"salesperson_id": 2, "grade": 85}]
)
BEGIN
    DECLARE v_salesperson_id INT;
    DECLARE v_grade INT;
    DECLARE v_done BOOLEAN DEFAULT FALSE;
    DECLARE cur_kpi_update CURSOR FOR SELECT salesperson_id, grade FROM JSON_TABLE(p_kpi_updates, '$[*]' COLUMNS (salesperson_id INT PATH '$.salesperson_id', grade INT PATH '$.grade')) AS updates;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;
    -- 开始事务
    START TRANSACTION;
    -- 遍历KPI更新列表并更新kpi表
    OPEN cur_kpi_update;
    read_loop: LOOP
        FETCH cur_kpi_update INTO v_salesperson_id, v_grade;
        IF v_done THEN
            LEAVE read_loop;
        END IF;
        -- 更新KPI等级
        UPDATE kpi 
        SET grade = v_grade 
        WHERE sid = v_salesperson_id;
    END LOOP;
    CLOSE cur_kpi_update;
    -- 提交事务
    COMMIT;
END //
DELIMITER ;