如何在IMS系统中实现输入数量大于可用库存时触发告警

Hey there! Let's tackle that inventory going negative problem head-on. The core issue with your current code is that you're deducting stock before checking if the requested quantity is actually available—so we need to add validation guardrails, plus use database transactions to make sure your data stays consistent if something goes wrong.

Key Fixes to Implement: #

• Add Stock Validation: Check each product's available inventory before processing the order item
• Use Database Transactions: If any product fails validation, roll back all changes (including the order entry) to avoid partial data saves
• Return Clear Error Messages: Pass back a user-friendly alert when stock is insufficient

Modified Code with Explanations: #

public function create() {
    // Start a database transaction to ensure all changes are atomic
    $this->db->trans_start();

    $user_id = $this->session->userdata('id');
    $query = $this->db->query("SELECT bill_no FROM orders ORDER BY id DESC LIMIT 1");
    $result = $query->row()->bill_no;
    $result++;
    $invoice_no = $result;

    $data = array(
        'po_no' => $this->input->post('po_no'),
        'po_date' => $this->input->post('po_date'),
        'challan_no' => $this->input->post('challan_no'),
        'challan_date' => $this->input->post('challan_date'),
        'bill_no' => $invoice_no,
        'bill_date' => $this->input->post('bill_date'),
        'terms' => $this->input->post('terms'),
        'dispatch' => $this->input->post('dispatch'),
        'party_id' => $this->input->post('id'),
        'name' => $this->input->post('name_value'),
        'address' => $this->input->post('address_value'),
        'gstin' => $this->input->post('gstin_value'),
        'mobile' => $this->input->post('mobile_value'),
        'date_time' => strtotime(date('Y-m-d h:i:s a')),
        'qty' => $this->input->post('qty_value'),
        'gross_amount' => $this->input->post('gross_amount_value'),
        'central_amount' => $this->input->post('central_amount_value'),
        'net_amount' => $this->input->post('net_amount_value'),
        'round_amount' =>$this->input->post('round_amount_value'),
        'round_amount_words' => $this->input->post('round_amount_words'),
        'paid_status' => 2,
        'user_id' => $user_id
    );
    $insert = $this->db->insert('orders', $data);
    $order_id = $this->db->insert_id();

    $this->load->model('model_products');
    $count_product = count($this->input->post('product'));
    $has_error = false;
    $error_msg = "输入数量大于可用库存，无法生成订单";

    for($x = 0; $x < $count_product; $x++) {
        $product_id = $this->input->post('product')[$x];
        $requested_qty = (int) $this->input->post('qty')[$x];
        
        // Fetch current available stock for the product
        $product_data = $this->model_products->getProductData($product_id);
        $available_qty = (int) $product_data['qty'];

        // Critical validation: Check if requested quantity exceeds available stock
        if($requested_qty > $available_qty) {
            $has_error = true;
            break; // Stop processing further items immediately
        }

        // If validation passes, add the order item
        $items = array(
            'order_id' => $order_id,
            'product_id' => $product_id,
            'hsn' => $this->input->post('hsn_value')[$x],
            'rate' => $this->input->post('rate')[$x],
            'qty' => $requested_qty,
            'unit' => $this->input->post('unit_value')[$x],
            'amount' => $this->input->post('amount_value')[$x],
            'gst' => $this->input->post('gst_value')[$x],
            'gst_amount' => $this->input->post('gst_amount_value')[$x],
            'last_amount' => $this->input->post('last_amount_value')[$x],
        );
        $this->db->insert('orders_item', $items);

        // Deduct the stock from the product
        $new_qty = $available_qty - $requested_qty;
        $update_product = array('qty' => $new_qty);
        $this->model_products->update($update_product, $product_id);
    }

    // Handle success/error scenarios
    if($has_error) {
        // Roll back all database changes since validation failed
        $this->db->trans_rollback();
        // Return error to frontend for alert display
        return ['success' => false, 'message' => $error_msg];
    } else {
        // Commit all valid changes to the database
        $this->db->trans_complete();
        return ['success' => true, 'order_id' => $order_id];
    }
}

Quick Frontend Tip: #

When calling this create() function from your frontend, check the returned response. If success is false, display the message in a popup alert to notify the user.

Extra Considerations: #

• Add validation for invalid product IDs (in case getProductData() returns empty)
• For high-traffic systems, use database locks or optimistic concurrency control to prevent race conditions (two orders trying to deduct the same stock at the same time)

内容的提问来源于stack exchange，提问作者karuna