PHP社が開発した売上動向分析レポート機能をERP（基幹業務システム）で活用

SELECT DATE_FORMAT(sale_date, '%Y-%m') AS month, SUM(sale_amount) AS amount

FROM sales_table

WHERE sale_date BETWEEN '2020-01-01' AND '2020-12-31'

GROUP BY DATE_FORMAT(sale_date, '%Y-%m')

<?php

// 获取销售数据

$pdo = new PDO('mysql:host=localhost;dbname=sales_db', 'username', 'password');

$sql = "SELECT DATE_FORMAT(sale_date, '%Y-%m') AS month, SUM(sale_amount) AS amount

FROM sales_table

WHERE sale_date BETWEEN '2020-01-01' AND '2020-12-31'

GROUP BY DATE_FORMAT(sale_date, '%Y-%m')";

$stmt = $pdo->prepare($sql);

$stmt->execute();

$data = $stmt->fetchAll(PDO::FETCH_ASSOC);

// 构建图表数据

$labels = [];

$amounts = [];

foreach ($data as $item) {

    $labels[] = $item['month'];

    $amounts[] = $item['amount'];

}

// 生成图表

echo "<canvas id='sales-chart'></canvas>";

?>

<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>

<script>

var ctx = document.getElementById('sales-chart').getContext('2d');

var salesChart = new Chart(ctx, {

    type: 'line',

    data: {

        labels: <?php echo json_encode($labels); ?>,

        datasets: [{

            label: '销售趋势',

            data: <?php echo json_encode($amounts); ?>,

            backgroundColor: 'rgba(0, 123, 255, 0.5)',

            borderColor: 'rgba(0, 123, 255, 1)',

            borderWidth: 1

        }]

    },

    options: {

        responsive: true,

        scales: {

            yAxes: [{

                ticks: {

                    beginAtZero: true

                }

            }]

        }

    }

});

</script>