How to Generate Inventory Reports in a Stock Management System Using PHP

How to Generate Inventory Reports in a Stock Management System Using PHP

Inventory management is critical for any business. Inventory reports are essential tools for businesses to track stock levels, sales, and make informed decisions. Through these reports, businesses can monitor product quantities, sales trends, and adjust accordingly. This article explains how to use PHP to create the inventory report generation functionality within a stock management system, with code examples provided.

Database Design

Before implementing the inventory report functionality, we first need to design an appropriate database structure to store the related inventory data. Below is a simple example of the database design:

products Table

This table stores product information, including product ID, name, purchase price, and sale price.

CREATE TABLE products (
  id INT(11) AUTO_INCREMENT PRIMARY KEY,
  product_code VARCHAR(50) NOT NULL,
  product_name VARCHAR(255) NOT NULL,
  purchase_price DECIMAL(10,2) NOT NULL,
  selling_price DECIMAL(10,2) NOT NULL
);

stock_records Table

This table stores inventory change records, including product ID, change type (purchase or sale), change quantity, and change date.

CREATE TABLE stock_records (
  id INT(11) AUTO_INCREMENT PRIMARY KEY,
  product_id INT(11) NOT NULL,
  change_type ENUM('purchase', 'sale') NOT NULL,
  change_quantity INT(11) NOT NULL,
  change_date DATE NOT NULL,
  FOREIGN KEY (product_id) REFERENCES products(id)
);

sales_records Table

This table stores sales data, including product ID, sales quantity, and sale date.

CREATE TABLE sales_records (
  id INT(11) AUTO_INCREMENT PRIMARY KEY,
  product_id INT(11) NOT NULL,
  sale_quantity INT(11) NOT NULL,
  sale_date DATE NOT NULL,
  FOREIGN KEY (product_id) REFERENCES products(id)
);

PHP Code Example for Generating Inventory Reports

Next, we will write PHP code to generate the inventory reports. The main logic is to query the relevant data from the database and generate the report as required. Below is a simple code example:

<?php
// Connect to the database
$conn = mysqli_connect('localhost', 'username', 'password', 'database');

// Query product information and stock change records
$query = 'SELECT p.product_code AS code, p.product_name AS name, p.purchase_price AS purchase, s.change_type AS type, s.change_quantity AS quantity, s.change_date AS date
          FROM products p
          JOIN stock_records s ON p.id = s.product_id
          ORDER BY p.product_code ASC, s.change_date DESC';

$result = mysqli_query($conn, $query);

// Initialize report data
$report = array();

// Generate report data
while ($row = mysqli_fetch_assoc($result)) {
    $code = $row['code'];
    $name = $row['name'];
    $purchase = $row['purchase'];
    $type = $row['type'];
    $quantity = $row['quantity'];
    $date = $row['date'];

    if (!isset($report[$code])) {
        $report[$code] = array(
            'name' => $name,
            'purchase' => $purchase,
            'stock' => 0,
            'sales' => 0
        );
    }

    if ($type == 'purchase') {
        $report[$code]['stock'] += $quantity;
    } elseif ($type == 'sale') {
        $report[$code]['stock'] -= $quantity;
        $report[$code]['sales'] += $quantity;
    }

    $report[$code]['date'] = $date;
}

// Output the report
foreach ($report as $code => $data) {
    echo 'Product Code: ' . $code . '<br>';
    echo 'Product Name: ' . $data['name'] . '<br>';
    echo 'Purchase Price: ' . $data['purchase'] . '<br>';
    echo 'Stock Quantity: ' . $data['stock'] . '<br>';
    echo 'Sales Quantity: ' . $data['sales'] . '<br>';
    echo 'Last Change Date: ' . $data['date'] . '<br><br>';
}

// Close the database connection
mysqli_close($conn);
?>

Conclusion

Through the example code above, we've shown how to use PHP to generate inventory reports for a stock management system. This is a basic example, and in real-world applications, you might need to extend or optimize the code based on specific needs. We hope this article helps you understand how to implement inventory report functionality in PHP and can be applied effectively in actual development scenarios.