<?php 
ob_start();
session_start();
date_default_timezone_set('Asia/Ho_Chi_Minh');

require("Connection.php");

// --- BUGTRAP ---
mysqli_report(MYSQLI_REPORT_OFF);

// 1. Lấy dữ liệu Input
$thang = isset($_POST["thang"]) ? (int)$_POST["thang"] : (int)date('m');
$nam = isset($_POST["nam"]) ? (int)$_POST["nam"] : (int)date('Y');

// 2. Reset bảng tạm (Logic cũ)
$sql10 = "DELETE FROM solieugt";
mysqli_query($link, $sql10);

// 3. Phân tích dữ liệu (Logic tách chuỗi)
$sql1 = "SELECT * FROM dexuatgiaotrinh WHERE month(ngaydexuat)=" . $thang . " AND year(ngaydexuat)=" . $nam;
$result1 = mysqli_query($link, $sql1);

if ($result1 && mysqli_num_rows($result1) > 0) {
    while ($row1 = mysqli_fetch_array($result1)) {   
        $matruongmn = $row1["matruongmn"];
        $malopmn = $row1["malopmn"];
        $soluongbe = (int)$row1["soluongbe"];
        $noidungdx = $row1["noidungdx"];
        $ngaydexuat = $row1["ngaydexuat"];
        
        $len = strlen($noidungdx);
        for ($i = 0; $i < $len; $i++) {
            $kytu = substr($noidungdx, $i, 1);
            $kytu_upper = strtoupper($kytu);
            $mabai = "";
            $jump = 0;

            if ($kytu_upper == "K") { $mabai = substr($noidungdx, $i, 6); $jump = 6; }
            elseif ($kytu_upper == "L" || $kytu_upper == "D" || $kytu_upper == "N") { $mabai = substr($noidungdx, $i, 4); $jump = 4; }

            if ($mabai != "") {
                if (strlen($mabai) > 10) $mabai = substr($mabai, 0, 10);
                
                $mabai_safe = mysqli_real_escape_string($link, $mabai);
                $matruongmn_safe = mysqli_real_escape_string($link, $matruongmn);
                $ngaydexuat_safe = mysqli_real_escape_string($link, $ngaydexuat);

                $sql2 = "INSERT INTO solieugt(matruongmn, malopmn, magt, solg, ngaydx) VALUES ('$matruongmn_safe', $malopmn, '$mabai_safe', $soluongbe, '$ngaydexuat_safe')";  
                mysqli_query($link, $sql2);
                $i = $i + $jump - 1; 
            }
        }
    }
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<HTML>
<HEAD>
    <Title>Thống kê theo trường</Title>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
    <style type="text/css">
        .style3 { font-size: 14pt; font-family: Arial, Helvetica, sans-serif; color: #990000; font-weight: bold; }
    </style>
</HEAD>
<body>
    <form name="f1" method="POST" action="thongketruong.php"> <div align="center">
            <p><font color="#FF0000" size="+2"><b><br />THỐNG KÊ SỐ LƯỢNG THEO TRƯỜNG</b></font><br /><br /></p>
        </div>
        
        <div align="center">
            <table width="700" border="1" align="center" cellpadding="2" cellspacing="0">
                <tr>
                    <td width="101" align="right"><strong>Chọn tháng :</strong></td>
                    <td width="93">
                        <select name="thang">
                            <?php for ($m = 1; $m <= 12; $m++) {
                                $selected = ($m == $thang) ? "selected" : "";
                                echo "<option value='$m' $selected>$m</option>";
                            } ?>
                        </select>
                    </td>
                    <td width="141" align="right"><strong>Chọn năm :</strong></td>
                    <td width="136">
                        <select name="nam">
                            <?php 
                            $currentYear = (int)date('Y');
                            for ($y = 2017; $y <= $currentYear + 1; $y++) {
                                $selected = ($y == $nam) ? "selected" : "";
                                echo "<option value='$y' $selected>$y</option>";
                            } ?>
                        </select>
                    </td>
                    <td width="217"><input type="submit" value="thống kê" name="cmd1" /></td>
                </tr>
            </table>

            <p><br /><strong><font color="#000066" size="+1">Tháng : <?php echo $thang; ?> Năm: <?php echo $nam; ?></font></strong></p>

            <table width="60%" cellspacing="0" border="1">
                <tr>
                    <td width="60%" align="center"><p align="center"><font color="#800000" face="Arial" size="2">TÊN TRƯỜNG</font></p></td>
                    <td width="40%" align="center"><p align="center"><font color="#800000" size="2" face="Arial">TỔNG SỐ LƯỢNG</font></p></td>
                </tr>
                <?php
                // Query: Group by TRƯỜNG (matruongmn) thay vì magt
                // Join với bảng truongmn để lấy tên trường hiển thị cho đẹp
                $sql5 = "SELECT a.matruongmn, b.diachi, sum(a.solg) as tongsoluong 
                         FROM solieugt a 
                         LEFT JOIN truongmn b ON a.matruongmn = b.matruongmn
                         WHERE month(a.ngaydx)=" . $thang . " AND year(a.ngaydx)=" . $nam . " 
                         GROUP BY a.matruongmn 
                         ORDER BY tongsoluong DESC";
                
                $result5 = mysqli_query($link, $sql5);
                
                if ($result5 && mysqli_num_rows($result5) > 0) {
                    while ($row5 = mysqli_fetch_array($result5)) {
                        // Nếu không tìm thấy tên trường thì hiện mã trường
                        $tenTruong = ($row5["diachi"]) ? $row5["diachi"] : $row5["matruongmn"];
                ?>
                    <tr>
                        <td align="left" style="padding-left:10px;"><font face="arial" size="2"><?php echo $tenTruong; ?></font></td>
                        <td align="center"><font face="arial" size="2"><?php echo $row5["tongsoluong"]; ?></font></td>
                    </tr>
                <?php
                    }
                }
                ?>
            </table>
            <p><br /></p>
        </div>
    </form>
</body>
</HTML>