File manager - Edit - /var/www/order.cmtc.ac.th/admin/orders_product_summary_export.php
Back
<?php session_start(); if(!isset($_SESSION['admin'])) { header('Location: index.php'); exit; } include('../config/db.php'); date_default_timezone_set('Asia/Bangkok'); $date_from = $_GET['date_from'] ?? date('Y-m-d', strtotime('-6 days')); $date_to = $_GET['date_to'] ?? date('Y-m-d'); // ตั้งชื่อไฟล์ Excel $filename = "report_product_summary_{$date_from}_to_{$date_to}.xls"; // Header สำหรับดาวน์โหลดเป็น Excel header("Content-Type: application/vnd.ms-excel; charset=UTF-8"); header("Content-Disposition: attachment; filename=\"$filename\""); header("Pragma: no-cache"); header("Expires: 0"); // ดึงข้อมูลทั้งหมดตามช่วงวันที่ $sql = " SELECT o.order_type, p.name AS product_name, SUM(i.qty) AS total_qty, SUM(i.qty * i.price) AS total_amount, SUM(CASE WHEN i.receive_method='รับเหรียญด้วยตนเอง' THEN i.qty ELSE 0 END) AS qty_self, SUM(CASE WHEN i.receive_method='จัดส่งทางไปรษณีย์' THEN i.qty ELSE 0 END) AS qty_post FROM order_items i JOIN orders o ON i.order_id = o.id JOIN products p ON i.product_id = p.id WHERE DATE(o.created_at) BETWEEN ? AND ? AND i.confirm_status = 'ยืนยัน' AND (o.order_type = 'ปกติ' OR o.order_type = 'Pre Order') GROUP BY p.id, o.order_type ORDER BY o.order_type ASC, p.name ASC "; $stmt = $conn->prepare($sql); $stmt->bind_param('ss', $date_from, $date_to); $stmt->execute(); $res = $stmt->get_result(); $data_normal = []; $data_free = []; while($r = $res->fetch_assoc()) { if($r['order_type'] == 'Pre Order') $data_free[] = $r; else $data_normal[] = $r; } // รวมยอด function render_section($title, $data) { static $total_qty_all = 0, $total_amt_all = 0, $total_self_all = 0, $total_post_all = 0; $sum_qty = $sum_amt = $sum_self = $sum_post = 0; $i = 1; echo "<tr><th colspan='6' style='background:#ddd;text-align:left;'>$title</th></tr>"; echo "<tr> <th>ลำดับ</th> <th>ชื่อเหรียญ</th> <th>จำนวนทั้งหมด</th> <th>รับด้วยตนเอง</th> <th>จัดส่งทางไปรษณีย์</th> <th>ยอดรวม (บาท)</th> </tr>"; if(count($data) > 0){ foreach($data as $r){ echo "<tr> <td>{$i}</td> <td>".htmlspecialchars($r['product_name'])."</td> <td>".number_format($r['total_qty'])."</td> <td>".number_format($r['qty_self'])."</td> <td>".number_format($r['qty_post'])."</td> <td>".number_format($r['total_amount'],2)."</td> </tr>"; $sum_qty += $r['total_qty']; $sum_amt += $r['total_amount']; $sum_self += $r['qty_self']; $sum_post += $r['qty_post']; $i++; } } else { echo "<tr><td colspan='6' align='center'>ไม่มีข้อมูล</td></tr>"; } echo "<tr style='font-weight:bold;background:#f2f2f2;'> <td colspan='2'>รวมทั้งหมด ($title)</td> <td>".number_format($sum_qty)."</td> <td>".number_format($sum_self)."</td> <td>".number_format($sum_post)."</td> <td>".number_format($sum_amt,2)."</td> </tr>"; // บวกไปยอดรวมทั้งหมด $total_qty_all += $sum_qty; $total_self_all += $sum_self; $total_post_all += $sum_post; $total_amt_all += $sum_amt; return [$total_qty_all, $total_self_all, $total_post_all, $total_amt_all]; } // เริ่มตาราง Excel echo "<meta charset='UTF-8'>"; echo "<table border='1' cellspacing='0' cellpadding='5'>"; echo "<tr><th colspan='6' style='font-size:18px;text-align:center;'>รายงานสรุปยอดรวมตามสินค้า (เหรียญแต่ละรุ่น)</th></tr>"; echo "<tr><th colspan='6' style='text-align:center;'>ช่วงวันที่ ".htmlspecialchars($date_from)." ถึง ".htmlspecialchars($date_to)."</th></tr>"; echo "<tr><td colspan='6'> </td></tr>"; list($tq1, $ts1, $tp1, $ta1) = render_section('🟩 ปกติ', $data_normal); list($tq2, $ts2, $tp2, $ta2) = render_section('🟨 Pre Order', $data_free); // รวมทั้งหมด $total_qty = $tq1 + $tq2; $total_self = $ts1 + $ts2; $total_post = $tp1 + $tp2; $total_amt = $ta1 + $ta2; echo "<tr><td colspan='6'> </td></tr>"; echo "<tr style='font-weight:bold;background:#dff0d8;'> <td colspan='2'>📊 รวมทั้งหมด (ปกติ + Pre Order)</td> <td>".number_format($total_qty)."</td> <td>".number_format($total_self)."</td> <td>".number_format($total_post)."</td> <td>".number_format($total_amt,2)."</td> </tr>"; echo "</table>"; exit; ?>
| ver. 1.4 |
Github
|
.
| PHP 7.4.33 | Generation time: 0.42 |
proxy
|
phpinfo
|
Settings