<?php
require_once __DIR__ . '/../core/init.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$sql = "SELECT 
            p.prd_name, p.prd_code, p.prd_sls AS prd_inventory, p.prd_origin_price, p.prd_sell_price, 
            p.prd_sell_price2, g.prd_group_name, m.prd_manuf_name, u.prd_unit_name, p.prd_tenquycach, p.prd_quycach
        FROM cms_products p 
        LEFT JOIN cms_products_group g ON p.prd_group_id = g.id 
        LEFT JOIN cms_products_manufacture m ON p.prd_manufacture_id = m.id
        LEFT JOIN cms_products_unit u ON p.prd_unit_id = u.id
        ORDER BY p.id DESC";
$result = $conn->query($sql);

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setTitle('Danh Sach San Pham');

$sheet->setCellValue('A1', 'Tên sản phẩm');
$sheet->setCellValue('B1', 'Mã sản phẩm');
$sheet->setCellValue('C1', 'Tồn kho');
$sheet->setCellValue('D1', 'Giá vốn');
$sheet->setCellValue('E1', 'Giá bán lẻ');
$sheet->setCellValue('F1', 'Giá bán sỉ');
$sheet->setCellValue('G1', 'Danh mục');
$sheet->setCellValue('H1', 'Nhà sản xuất');
$sheet->setCellValue('I1', 'Đơn vị tính');
$sheet->setCellValue('J1', 'Tên quy cách');
$sheet->setCellValue('K1', 'Quy cách');

$rowIndex = 2;
if ($result && $result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        $sheet->setCellValue('A' . $rowIndex, $row['prd_name']);
        $sheet->setCellValue('B' . $rowIndex, $row['prd_code']);
        $sheet->setCellValue('C' . $rowIndex, $row['prd_inventory']);
        $sheet->setCellValue('D' . $rowIndex, $row['prd_origin_price']);
        $sheet->setCellValue('E' . $rowIndex, $row['prd_sell_price']);
        $sheet->setCellValue('F' . $rowIndex, $row['prd_sell_price2']);
        $sheet->setCellValue('G' . $rowIndex, $row['prd_group_name']);
        $sheet->setCellValue('H' . $rowIndex, $row['prd_manuf_name']);
        $sheet->setCellValue('I' . $rowIndex, $row['prd_unit_name']);
        $sheet->setCellValue('J' . $rowIndex, $row['prd_tenquycach']);
        $sheet->setCellValue('K' . $rowIndex, $row['prd_quycach']);
        $rowIndex++;
    }
}

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="danh_sach_san_pham.xlsx"');
header('Cache-Control: max-age=0');

$writer = new Xlsx($spreadsheet);
$writer->save('php://output');
exit;
?>