<?php 

require_once('../lib/PHPExcel.php'); // 引入PHPExcel库

$objPHPExcel = new PHPExcel(); // 创建PHPExcel对象，即创建一个Excel文件

$objPHPExcel->setActiveSheetIndex(0); // 设置表格缺省编码为utf8

$objPHPExcel->getActiveSheet()->setTitle('订单列表'); // 设置工作表标题

// 设置表头
$objPHPExcel->getActiveSheet()->setCellValue('A1', '订单ID');
$objPHPExcel->getActiveSheet()->setCellValue('B1', '订单号');
// $objPHPExcel->getActiveSheet()->setCellValue('C1', '订单来源');
$objPHPExcel->getActiveSheet()->setCellValue('D1', '商品名称');
$objPHPExcel->getActiveSheet()->setCellValue('E1', '姓名');
$objPHPExcel->getActiveSheet()->setCellValue('F1', '身份证号');
$objPHPExcel->getActiveSheet()->setCellValue('G1', '手机号码');
$objPHPExcel->getActiveSheet()->setCellValue('H1', '省份');
$objPHPExcel->getActiveSheet()->setCellValue('I1', '城市');
$objPHPExcel->getActiveSheet()->setCellValue('J1', '县区');
$objPHPExcel->getActiveSheet()->setCellValue('K1', '详细地址');
$objPHPExcel->getActiveSheet()->setCellValue('L1', '处理状态');
$objPHPExcel->getActiveSheet()->setCellValue('M1', '号码状态');
$objPHPExcel->getActiveSheet()->setCellValue('N1', '备注');
$objPHPExcel->getActiveSheet()->setCellValue('O1', '下单时间');

// 填充数据
include_once("../untils/conn.php");
mysqli_query($con, "set names utf8");

if ($con) {
    if ($db) {
        $sql = "SELECT oder.id,oder.oderid, oder.channel, oder.goods, oder.name, oder.uid, oder.phone, oder.sf, oder.city, oder.xian, oder.dizhi, oder.status, oder.beizhu, oder.time, oder.card_sta, channel.channel_name 
                FROM oder 
                LEFT JOIN channel 
                ON oder.channel = channel.channel_id
                ORDER BY oder.id DESC";
        $data = mysqli_query($con, $sql);

        $num = 2; // 从第二行开始输出数据

        while ($row = mysqli_fetch_array($data)) {
            $status = '';
            switch ($row['status']) {
                case '0':
                    $status = '未处理';
                    break;
                case '1':
                    $status = '已下单';
                    break;
                case '2':
                    $status = '已发货';
                    break;
                case '3':
                    $status = '开卡失败';
                    break;
            }
            
            $card_sta = '';
            switch ($row['card_sta']) {
                case '0':
                    $card_sta = '未激活';
                    break;
                case '1':
                    $card_sta = '已激活';
                    break;
            }
            
            // 将内容强制设置为文本格式
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('A'.$num, $row['id'], PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$num, $row['oderid'], PHPExcel_Cell_DataType::TYPE_STRING);
            // $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$num, $row['channel'], PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$num, $row['goods'], PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$num, $row['name'], PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$num, $row['uid'], PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$num, $row['phone'], PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('H'.$num, $row['sf'], PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('I'.$num, $row['city'], PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('J'.$num, $row['xian'], PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('K'.$num, $row['dizhi'], PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('L'.$num, $status, PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('M'.$num, $card_sta, PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('N'.$num, $row['beizhu'], PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValue('O'.$num, date('Y-m-d H:i:s', $row['time']));
        
            $num++;
        }

    }
}

// 设置文件名并为Excel文件格式添加扩展名
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$filename = 'order'.date('YmdHis').'.xlsx'; // 定义Excel名称，如：order20211019141825.xlsx

// 下载Excel文件
header('Content-Disposition: attachment;filename="'.$filename.'"');
header('Content-Type: application/octet-stream');
header('Content-Transfer-Encoding: binary');
header('Cache-Control: must-revalidate');
header('Pragma: public');
header('Expires: 0');

$objWriter->save('php://output'); // 将Excel文件输出给浏览器


?>