<?php
require 'admincp/assets/phpspreadsheet/vendor/autoload.php';
require 'config/Database_mysqli_old.php';
require 'config/reuse_function.php';

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// Create new Spreadsheet object
$spreadsheet = new Spreadsheet();

// Set document properties
$spreadsheet->getProperties()->setCreator('Diendengiadung')
    ->setLastModifiedBy('Diendengiadung')
    ->setTitle('Office 2007 XLSX Test Document')
    ->setSubject('Office 2007 XLSX Test Document')
    ->setDescription('Products data.')
    ->setKeywords('office 2007 openxml php')
    ->setCategory('Test result file');

// Add some data
$spreadsheet->setActiveSheetIndex(0)
    ->setCellValue('A1', 'STT')
    ->setCellValue('B1', 'id_product')    
    ->setCellValue('C1', 'small')
    ->setCellValue('D1', 'title')
    ->setCellValue('E1', 'id_category')
    ->setCellValue('F1', 'product_code')
    ->setCellValue('G1', 'Ảnh chi tiết 1')
    ->setCellValue('H1', 'Ảnh chi tiết 2')
    ->setCellValue('I1', 'Ảnh chi tiết 3')
    ->setCellValue('J1', 'Mô tả chi tiết')
    ->setCellValue('K1', 'is_hot')
    ->setCellValue('L1', 'is_new')
    ->setCellValue('M1', 'is_sell_off')
    ->setCellValue('N1', 'is_favorite');


// Miscellaneous glyphs, UTF-8

$sql = "SELECT * FROM tbl_product ";
$result = mysqli_query($conn,$sql);
$stt = 2;
while( $row = mysqli_fetch_array($result) ){

    // get detail picture
        $sql_tmp = 'SELECT * FROM tbl_product_detail WHERE product_id = '.$row['id'].' ';
        $result_tmp = mysqli_query($conn,$sql_tmp);

        $image_detail[0]='';
        $image_detail[1]='';
        $image_detail[2]='';

        $i_tmp=0;

        while( $row_tmp = mysqli_fetch_array($result_tmp) ){
            $image_detail[$i_tmp]=$row_tmp['photo'];    
            $i_tmp++;
        }
       
    //end get detail picture

    $spreadsheet->setActiveSheetIndex(0)
    ->setCellValue('A'.$stt, ($stt-1) )
    ->setCellValue('B'.$stt, $row['id'])
    ->setCellValue('C'.$stt, $row['small_photo'])
    ->setCellValue('D'.$stt, $row['title'])
    ->setCellValue('E'.$stt, $row['category_id'])
    ->setCellValue('F'.$stt, $row['prod_code'])
    ->setCellValue('G'.$stt, $image_detail[0])
    ->setCellValue('H'.$stt, $image_detail[1])
    ->setCellValue('I'.$stt, $image_detail[2])
    ->setCellValue('J'.$stt, $row['specification'] )
    ->setCellValue('K'.$stt, $row['prod_hot'] )
    ->setCellValue('L'.$stt, $row['prod_new'] )
    ->setCellValue('M'.$stt, $row['prod_discount'] )
    ->setCellValue('N'.$stt, $row['prod_favour'] );
    $stt++;
}

// Rename worksheet
$spreadsheet->getActiveSheet()->setTitle('products');

// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$spreadsheet->setActiveSheetIndex(0);

// Redirect output to a client’s web browser (Xlsx)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="products.xlsx"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');

// If you're serving to IE over SSL, then the following may be needed
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header('Pragma: public'); // HTTP/1.0

$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save('php://output');
exit;