Application of purchasing monthly report statistics function developed by PHP in enterprise resource planning (ERP) system

Application of monthly purchasing report statistics function developed by PHP in enterprise resource planning (ERP) system

In modern enterprises, purchasing management is a very important link. In order to facilitate enterprises to carry out statistics and analysis of procurement data, it is essential to develop a monthly procurement report statistical function. This article will introduce how to develop this function using PHP and integrate it into an enterprise resource planning (ERP) system.

1. Functional requirements

The main functional requirements of the purchasing monthly report statistics function are as follows:

1. Statistics on the total amount of monthly purchases.
2. Statistics on the total quantity purchased each month.
3. Count the number and amount of items purchased in different categories each month.
4. Statistics on the amount and quantity of monthly purchases from different suppliers.
5. Generate chart display of monthly report statistical results.

2. Database design

In order to realize the statistical function of monthly procurement report, we need to design a database to store procurement-related data. The following is an example of the structure of a simple purchase table:

CREATE TABLE `purchase` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `item` varchar(100) NOT NULL,
  `category` varchar(50) NOT NULL,
  `quantity` int(11) NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `supplier` varchar(100) NOT NULL,
  `purchase_date` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

3. Procurement statistics logic and code examples

1. Statistics on the total amount and total quantity of monthly purchases

In order to count the total amount and total quantity of monthly purchases, we can use the database's aggregate functions SUM() and COUNT(). The following is a sample function:

function getMonthlyPurchaseStats($year, $month){
    $conn = new mysqli('localhost', 'username', 'password', 'database');
    
    $sql = "SELECT SUM(amount) AS total_amount, COUNT(id) AS total_quantity FROM purchase WHERE YEAR(purchase_date) = $year AND MONTH(purchase_date) = $month";
    $result = $conn->query($sql);
    
    $stats = $result->fetch_assoc();
    
    $conn->close();
    
    return $stats;
}

2. To count the number and amount of items purchased in different categories every month

In order to count the number and amount of items purchased in different categories every month , we can use the GROUP BY clause and SUM() function of the database. The following is a sample function:

function getCategoryPurchaseStats($year, $month){
    $conn = new mysqli('localhost', 'username', 'password', 'database');
    
    $sql = "SELECT category, SUM(amount) AS total_amount, COUNT(id) AS total_quantity FROM purchase WHERE YEAR(purchase_date) = $year AND MONTH(purchase_date) = $month GROUP BY category";
    $result = $conn->query($sql);
    
    $stats = array();
    while($row = $result->fetch_assoc()){
        $stats[] = $row;
    }
    
    $conn->close();
    
    return $stats;
}

3. To count the amount and quantity of different suppliers purchased every month

In order to count the amount and quantity of different suppliers purchased every month , we can use the GROUP BY clause and SUM() function of the database. The following is a sample function:

function getSupplierPurchaseStats($year, $month){
    $conn = new mysqli('localhost', 'username', 'password', 'database');
    
    $sql = "SELECT supplier, SUM(amount) AS total_amount, COUNT(id) AS total_quantity FROM purchase WHERE YEAR(purchase_date) = $year AND MONTH(purchase_date) = $month GROUP BY supplier";
    $result = $conn->query($sql);
    
    $stats = array();
    while($row = $result->fetch_assoc()){
        $stats[] = $row;
    }
    
    $conn->close();
    
    return $stats;
}

4. Result display

In order to display the monthly report statistical results in a chart, we can use a third-party chart library, such as Google Charts or Chart.js. The following is a sample code that uses Chart.js to generate a histogram:

<!DOCTYPE html>
<html>
<head>
    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
</head>
<body>
    <canvas id="purchaseChart"></canvas>
    
    <?php
        // 获取统计结果数据
        $stats = getCategoryPurchaseStats(2021, 1);
        
        // 根据统计结果生成图表数据
        $labels = array();
        $data = array();
        foreach($stats as $row){
            $labels[] = $row['category'];
            $data[] = $row['total_amount'];
        }
    ?>
    
    <script>
        var ctx = document.getElementById('purchaseChart').getContext('2d');
        var myChart = new Chart(ctx, {
            type: 'bar',
            data: {
                labels: <?php echo json_encode($labels); ?>,
                datasets: [{
                    label: '采购金额',
                    data: <?php echo json_encode($data); ?>,
                    backgroundColor: 'rgba(75, 192, 192, 0.2)',
                    borderColor: 'rgba(75, 192, 192, 1)',
                    borderWidth: 1
                }]
            },
            options: {
                scales: {
                    y: {
                        beginAtZero: true
                    }
                }
            }
        });
    </script>
</body>
</html>

Through the above code example, we can see how to use PHP to develop monthly purchasing report statistical functions and integrate it into Enterprise Resource Planning (ERP) ) system. This feature can help companies better understand their procurement situations and make targeted decisions. Of course, in actual applications, we can also add more functions according to actual needs, such as exporting reports, setting filter conditions, etc.

Summary

This article introduces how to use PHP to develop the monthly purchasing report statistical function and integrate it into the enterprise resource planning (ERP) system. Through database queries and aggregation functions, we can achieve various statistical needs. At the same time, by using a third-party chart library, we can display statistical results in an intuitive way to help companies better understand the procurement situation. I hope this article will be helpful for you to learn the statistical function of developing monthly purchasing report in PHP.

The above is the detailed content of Application of purchasing monthly report statistics function developed by PHP in enterprise resource planning (ERP) system. For more information, please follow other related articles on the PHP Chinese website!