ekshopApi/app/shop/service/order/ExportService.php

<?php

namespace app\shop\service\order;


use app\shop\model\order\OrderRefund;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

/**
 * 订单导出服务类
 */
class ExportService
{
    /**
     * 订单导出
     */
    public function orderList($list)
    {
        set_time_limit(0);
        //设置程序运行内存
        ini_set('memory_limit', '20480M');
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        //列宽
        $sheet->getColumnDimension('B')->setWidth(35);
        $sheet->getColumnDimension('P')->setWidth(35);
        //设置工作表标题名称
        $sheet->setTitle('订单明细');
        $sheet->setCellValue('A1', '会员编号');
        $sheet->setCellValue('B1', '报单中心');
        $sheet->setCellValue('C1', '订单号');
        $sheet->setCellValue('D1', '订单状态');
        $sheet->setCellValue('E1', '存货编码'); // 商品编码
        $sheet->setCellValue('F1', '存货名称'); // 商品名称
        $sheet->setCellValue('G1', '规格编码');
        $sheet->setCellValue('H1', '数量'); // 商品数量
        $sheet->setCellValue('I1', '收货人');
        $sheet->setCellValue('J1', '联系方式1');
        $sheet->setCellValue('K1', '联系方式2');  // TODO: 新增
        $sheet->setCellValue('L1', '省');
        $sheet->setCellValue('M1', '市');
        $sheet->setCellValue('N1', '区');
        $sheet->setCellValue('O1', '详细地址');
        $sheet->setCellValue('P1', '期数');
        $sheet->setCellValue('Q1', '订单类型');
        $sheet->setCellValue('R1', '发货仓');
        $sheet->setCellValue('S1', '创建时间');
        $sheet->setCellValue('T1', '支付时间');
        $sheet->setCellValue('U1', '发货时间');
        $sheet->setCellValue('V1', '商品单价');
        $sheet->setCellValue('W1', '商品金额'); // 商品总额
        $sheet->setCellValue('X1', '商品PV');   // 商品pv
        $sheet->setCellValue('Y1', 'PV合计'); // 合计pv
        $sheet->setCellValue('Z1', '运费');
        $sheet->setCellValue('AA1', '快递公司');
        $sheet->setCellValue('AB1', '快递单号');
        $sheet->setCellValue('AC1', '发货方式');
        $sheet->setCellValue('AD1','会员备注');
        $sheet->setCellValue('AE1','发货状态');
        $sheet->setCellValue('AF1','售后状态');
        //填充数据
        $index = 0;

        //获取售后记录
        $orderProductIds = array_column($list, 'order_product_id');
        $refunds         = OrderRefund::whereIn('order_product_id', $orderProductIds)
            ->field('order_refund_id,order_product_id,type,is_agree')
            ->select();
        $refundMap       = [];
        if (!$refunds->isEmpty()) {
            $refundMap = array_column($refunds->toArray(), null, 'order_product_id');
        }

        foreach ($list as $order) {
            //售后状态
            $refundStatus = '无';

            if (isset($refundMap[$order['order_product_id']])) {
                $info         = $refundMap[$order['order_product_id']];
                $refundStatus = $info['type']['text'] . '-' . $info['is_agree']['text'];
            }

            $state_text = $this->getStateTextAttr($order);

            $delivery = $order['delivery_status'] == 10?'未发货':'已发货';
            if ($order['delivery_type'] == 10) {
                $type = '快递配送';
            }elseif ($order['delivery_type'] == 20) {
                $type = '上门自提';
            }else{
                $type = '无需物流';
            }
            $price = empty($order['grade_product_price'])?$order['product_price']:$order['grade_product_price'];
            $richText = new \PhpOffice\PhpSpreadsheet\RichText\RichText();
            $richText->createText((string)$order['user_no']);
            $sheet->setCellValue('A' . ($index + 2), $richText);
            $sheet->setCellValue('B'.($index + 2),"");
            $richText = new \PhpOffice\PhpSpreadsheet\RichText\RichText();
            $richText->createText((string)$order['order_no']);
            $sheet->setCellValue('C'.($index + 2), $richText);
            $sheet->setCellValue('D'.($index + 2), $state_text);
            $sheet->setCellValue('E'.($index + 2), trim($order['product_no']));
            $sheet->setCellValue('F'.($index + 2), $order['product_name']);
            $sheet->setCellValue('G'.($index + 2), trim($order['sku_product_no'] ?: $order['product_no']));
            $sheet->setCellValue('H'.($index + 2), $order['total_num']);
            $sheet->setCellValue('I'.($index + 2), $order['name']);
            $sheet->setCellValue('J'.($index + 2), $order['phone']);
            $sheet->setCellValue('K'.($index + 2), '');
            $sheet->setCellValue('L'.($index + 2), $order['province']);
            $sheet->setCellValue('M'.($index + 2), $order['city']);
            $sheet->setCellValue('N'.($index + 2), $order['region']);
            $sheet->setCellValue('O'.($index + 2), $order['detail']);
            $sheet->setCellValue('P'.($index + 2), "");
            $sheet->setCellValue('Q'.($index + 2), '重消');
            $sheet->setCellValue('R'.($index + 2), "\t"."01"."\t");
            $sheet->setCellValue('S'.($index + 2), $order['create_time']);
            $sheet->setCellValue('T'.($index + 2), $this->filterTime($order['pay_time']));
            $sheet->setCellValue('U'.($index + 2), $this->filterTime($order['delivery_time']));
            $sheet->setCellValue('V'.($index + 2), $price);
            $sheet->setCellValue('W'.($index + 2), $order['total_price']);
            $sheet->setCellValue('X'.($index + 2), $order['pv']);
            $sheet->setCellValue('Y'.($index + 2), $order['pv'] * $order['total_num']);
            $sheet->setCellValue('Z'.($index + 2), $order['express_price']);
            $sheet->setCellValue('AA'.($index + 2), $order['express_name']);
            $sheet->setCellValue('AB'.($index + 2), $order['express_no']);
            $sheet->setCellValue('AC'.($index + 2), $type);
            $sheet->setCellValue('AD'.($index + 2), $order['buyer_remark']);
            $sheet->setCellValue('AE'.($index + 2), $delivery);
            $sheet->setCellValue('AF'.($index + 2), $refundStatus);
            $index ++;
        }

        //保存文件
        $filename = iconv("UTF-8","GB2312//IGNORE", '订单'). '-' . date('YmdHis') . '.xlsx';
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="'.$filename.'"');
        header('Cache-Control: max-age=0');
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
    }
    public function getStateTextAttr($order)
    {
        // 订单状态
        if ($order['order_status']==20) {
            return '已取消';
        }
        if ($order['order_status']==30) {
            return '已完成';
        }
        if ($order['order_status'] == 21) {
            return '取消申请中';
        }
        // 付款状态
        if ($order['pay_status'] == 10) {
           return '待付款';
        }
        // 发货状态
        if ($order['delivery_status'] == 10) {
            return '已付款，待发货';
        }
        if ($order['receipt_status'] == 10 && $order['delivery_status'] == 20) {
           return '已发货，待收货';
        }
        return '进行中';
    }
    /**
     * 订单导出
     */
    // public function orderList($list)
    // {
    //     $strTable  ='<table width="4000" border="1">';
    //     $strTable .= '<tr style="height:25px">';
    //     $strTable .= '<td style="text-align:center;font-size:12px;" width:100;">会员编号</td>';
    //     $strTable .= '<td style="text-align:center;font-size:12px;" width:80;">报单中心</td>';
    //     $strTable .= '<td style="text-align:center;font-size:12px;" width:150;">订单号</td>';
    //     $strTable .= '<td style="text-align:center;font-size:12px;" width:120;">订单状态</td>';
    //     $strTable .= '<td style="text-align:center;font-size:12px;" width="80">商品编码</td>';
    //     $strTable .= '<td style="text-align:center;font-size:12px;" width="500">商品名称</td>';
    //     $strTable .= '<td style="text-align:center;font-size:12px;" width="80">商品数量</td>';
    //     $strTable .= '<td style="text-align:center;font-size:12px;" width="80">商品单价</td>';
    //     $strTable .= '<td style="text-align:center;font-size:12px;" width="80">商品总额</td>';
    //     $strTable .= '<td style="text-align:center;font-size:12px;" width="60">商品pv</td>';
    //     $strTable .= '<td style="text-align:center;font-size:12px;" width="60">合计pv</td>';
    //     $strTable .= '<td style="text-align:center;font-size:12px;" width="80">收货人</td>';
    //     $strTable .= '<td style="text-align:center;font-size:12px;" width="100">联系方式</td>';
    //     $strTable .= '<td style="text-align:center;font-size:12px;" width="100">省</td>';
    //     $strTable .= '<td style="text-align:center;font-size:12px;" width="100">市</td>';
    //     $strTable .= '<td style="text-align:center;font-size:12px;" width="100">区</td>';
    //     $strTable .= '<td style="text-align:center;font-size:12px;" width="120">详细地址</td>';
    //     $strTable .= '<td style="text-align:center;font-size:12px;" width="80">期数</td>';
    //     $strTable .= '<td style="text-align:center;font-size:12px;" width="80">订单类型</td>';
    //     $strTable .= '<td style="text-align:center;font-size:12px;" width="80">发货仓</td>';
    //     $strTable .= '<td style="text-align:center;font-size:12px;" width="120">创建时间</td>';
    //     $strTable .= '<td style="text-align:center;font-size:12px;" width="120">支付时间</td>';
    //     $strTable .= '<td style="text-align:center;font-size:12px;" width="120">运费</td>';
    //     $strTable .= '<td style="text-align:center;font-size:12px;" width="120">快递公司</td>';
    //     $strTable .= '<td style="text-align:center;font-size:12px;" width="120">快递单号</td>';
    //     $strTable .= '<td style="text-align:center;font-size:12px;" width="120">发货方式</td>';
    //     $strTable .= '<td style="text-align:center;font-size:12px;" width="180">会员备注</td>';
    //     $strTable .= '<td style="text-align:center;font-size:12px;" width="120">发货状态</td>';
    //     $strTable .= '<td style="text-align:center;font-size:12px;" width="120">发货时间</td>';
    //     $strTable .= '</tr>';
    //     foreach ($list as $order) {
    //         foreach ($order['product'] as $key => $product) {
    //             $price = empty($product['grade_product_price'])?$product['product_price']:$product['grade_product_price'];
    //             $strTable .= '<tr style="height:20px">';
    //             $strTable .= '<td style="text-align:center;font-size:12px;" rowspan="">&nbsp;'.$order['user']['user_no'].'</td>';
    //             $strTable .= '<td style="text-align:center;font-size:12px;" rowspan=""></td>';
    //             $strTable .= '<td style="text-align:center;font-size:12px;" rowspan="">&nbsp;'.$order['order_no'].'</td>';
    //             $strTable .= '<td style="text-align:center;font-size:12px;" rowspan="">'.$order['state_text'].'</td>';
    //             $strTable .= '<td style="text-align:center;font-size:12px;" rowspan="">'.$product['product']['product_no'].'</td>';
    //             $strTable .= '<td style="text-align:center;font-size:12px;" rowspan="">'.$product['product_name'].'</td>';
    //             $strTable .= '<td style="text-align:center;font-size:12px;" rowspan="">'.$product['total_num'].'</td>';
    //             $strTable .= '<td style="text-align:center;font-size:12px;" rowspan="">'.$price.'</td>';
    //             $strTable .= '<td style="text-align:center;font-size:12px;" rowspan="">'.$product['total_price'].'</td>';
    //             $strTable .= '<td style="text-align:center;font-size:12px;" rowspan=""></td>';
    //             $strTable .= '<td style="text-align:center;font-size:12px;" rowspan=""></td>';
    //             $strTable .= '<td style="text-align:center;font-size:12px;" rowspan="">'.$order['address']['name'].'</td>';
    //             $strTable .= '<td style="text-align:center;font-size:12px;" rowspan="">'.$order['address']['phone'].'</td>';
    //             $strTable .= '<td style="text-align:center;font-size:12px;" rowspan="">'.$order['address']['region']['province'].'</td>';
    //             $strTable .= '<td style="text-align:center;font-size:12px;" rowspan="">'.$order['address']['region']['city'].'</td>';
    //             $strTable .= '<td style="text-align:center;font-size:12px;" rowspan="">'.$order['address']['region']['region'].'</td>';
    //             $strTable .= '<td style="text-align:center;font-size:12px;" rowspan="">'.$order['address']['detail'].'</td>';
    //             $strTable .= '<td style="text-align:center;font-size:12px;" rowspan=""></td>';
    //             $strTable .= '<td style="text-align:center;font-size:12px;" rowspan="">重消</td>';
    //             $strTable .= '<td style="text-align:center;font-size:12px;" rowspan="">&nbsp;01</td>';
    //             $strTable .= '<td style="text-align:center;font-size:12px;" rowspan="">'.$order['create_time'].'</td>';
    //             $pay_time  = empty($order['pay_time'])?'':date('Y-m-d H:i',$order['pay_time']);
    //             $strTable .= '<td style="text-align:center;font-size:12px;" rowspan="">'.$pay_time.'</td>';
    //             $strTable .= '<td style="text-align:center;font-size:12px;" rowspan="">'.$order['express_price'].'</td>';
    //             $strTable .= '<td style="text-align:center;font-size:12px;" rowspan="">'.$order['express']['express_name'].'</td>';
    //             $strTable .= '<td style="text-align:center;font-size:12px;" rowspan="">'.$order['express_no'].'</td>';
    //             $strTable .= '<td style="text-align:center;font-size:12px;" rowspan="">'.$order['delivery_type']['text'].'</td>';
    //             $strTable .= '<td style="text-align:center;font-size:12px;" rowspan="">'.$order['buyer_remark'].'</td>';
    //             $strTable .= '<td style="text-align:center;font-size:12px;" rowspan="">'.$order['delivery_status']['text'].'</td>';
    //             $delivery_time = empty($order['delivery_time'])?'':date('Y-m-d H:i',$order['delivery_time']);
    //             $strTable .= '<td style="text-align:center;font-size:12px;" rowspan="">'.$delivery_time.'</td>';
    //         }
    //     }
    //     $strTable .='</table>';
    //     $this->downloadExcel($strTable,'订单');
    //     exit();
    // }
    /**
     * 分销订单导出
     */
    public function agentOrderList($list)
    {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        //列宽
        $sheet->getColumnDimension('B')->setWidth(30);

        //设置工作表标题名称
        $sheet->setTitle('分销订单明细');

        $sheet->setCellValue('A1', '订单号');
        $sheet->setCellValue('B1', '商品信息');
        $sheet->setCellValue('C1', '订单总额');
        $sheet->setCellValue('D1', '实付款金额');
        $sheet->setCellValue('E1', '支付方式');
        $sheet->setCellValue('F1', '下单时间');
        $sheet->setCellValue('G1', '一级分销商');
        $sheet->setCellValue('H1', '一级分销佣金');
        $sheet->setCellValue('I1', '二级分销商');
        $sheet->setCellValue('J1', '二级分销佣金');
        $sheet->setCellValue('K1', '三级分销商');
        $sheet->setCellValue('L1', '三级分销佣金');
        $sheet->setCellValue('M1', '买家');
        $sheet->setCellValue('N1', '付款状态');
        $sheet->setCellValue('O1', '付款时间');
        $sheet->setCellValue('P1', '发货状态');
        $sheet->setCellValue('Q1', '发货时间');
        $sheet->setCellValue('R1', '收货状态');
        $sheet->setCellValue('S1', '收货时间');
        $sheet->setCellValue('T1', '订单状态');
        $sheet->setCellValue('U1', '佣金结算');
        $sheet->setCellValue('V1', '结算时间');
        //填充数据
        $index = 0;
        foreach ($list as $agent) {
            $order = $agent['order_master'];
            $richText = new \PhpOffice\PhpSpreadsheet\RichText\RichText();
            $richText->createText((string)$order['order_no']);
            $sheet->setCellValue('A' . ($index + 2), $richText);

            $sheet->setCellValue('B' . ($index + 2), $this->filterProductInfo($order));
            $sheet->setCellValue('C' . ($index + 2), $order['total_price']);
            $sheet->setCellValue('D' . ($index + 2), $order['pay_price']);
            $sheet->setCellValue('E' . ($index + 2), $order['pay_type']['text']);
            $sheet->setCellValue('F' . ($index + 2), $order['create_time']);
            $sheet->setCellValue('G' . ($index + 2), $agent['agent_first']['nickName']);
            $sheet->setCellValue('H' . ($index + 2), $agent['first_money']);
            $sheet->setCellValue('I' . ($index + 2), $agent['agent_second']['nickName']);
            $sheet->setCellValue('J' . ($index + 2), $agent['second_money']);
            $sheet->setCellValue('K' . ($index + 2), $agent['agent_third']['nickName']);
            $sheet->setCellValue('L' . ($index + 2), $agent['third_money']);
            $sheet->setCellValue('M' . ($index + 2), $order['user']['nickName']);
            $sheet->setCellValue('N' . ($index + 2), $order['pay_status']['text']);
            $sheet->setCellValue('O' . ($index + 2), $this->filterTime($order['pay_time']));
            $sheet->setCellValue('P' . ($index + 2), $order['delivery_status']['text']);
            $sheet->setCellValue('Q' . ($index + 2), $this->filterTime($order['delivery_time']));
            $sheet->setCellValue('R' . ($index + 2), $order['receipt_status']['text']);
            $sheet->setCellValue('S' . ($index + 2), $this->filterTime($order['receipt_time']));
            $sheet->setCellValue('T' . ($index + 2), $order['order_status']['text']);
            $sheet->setCellValue('U' . ($index + 2), $agent['is_settled'] == 1 ? '已结算' : '未结算');
            $sheet->setCellValue('V' . ($index + 2), $this->filterTime($agent['settle_time']));
            $index++;
        }

        //保存文件
        $filename = iconv("UTF-8", "GB2312//IGNORE", '分销订单') . '-' . date('YmdHis') . '.xlsx';
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $filename . '"');
        header('Cache-Control: max-age=0');
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
    }

    /**
     * 提现订单导出
     */
    public function cashList($list)
    {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        //列宽
        $sheet->getColumnDimension('H')->setWidth(50);

        //设置工作表标题名称
        $sheet->setTitle('提现明细');

        $sheet->setCellValue('A1', 'ID');
        $sheet->setCellValue('B1', '分销商id');
        $sheet->setCellValue('C1', '分销商姓名');
        $sheet->setCellValue('D1', '微信昵称');
        $sheet->setCellValue('E1', '手机号');
        $sheet->setCellValue('F1', '提现金额');
        $sheet->setCellValue('G1', '提现方式');
        $sheet->setCellValue('H1', '提现信息');
        $sheet->setCellValue('I1', '审核状态');
        $sheet->setCellValue('J1', '申请时间');
        $sheet->setCellValue('K1', '审核时间');
        //填充数据
        $index = 0;
        foreach ($list as $cash) {
            $sheet->setCellValue('A' . ($index + 2), $cash['id']);
            $sheet->setCellValue('B' . ($index + 2), $cash['user_id']);
            $sheet->setCellValue('C' . ($index + 2), $cash['real_name']);
            $sheet->setCellValue('D' . ($index + 2), $cash['nickName']);
            $sheet->setCellValue('E' . ($index + 2), "\t" . $cash['mobile'] . "\t");
            $sheet->setCellValue('F' . ($index + 2), $cash['money']);
            $sheet->setCellValue('G' . ($index + 2), $cash['pay_type']['text']);
            $sheet->setCellValue('H' . ($index + 2), $this->cashInfo($cash));
            $sheet->setCellValue('I' . ($index + 2), $cash['apply_status']['text']);
            $sheet->setCellValue('J' . ($index + 2), $cash['create_time']);
            $sheet->setCellValue('K' . ($index + 2), $cash['audit_time']);
            $index++;
        }
        //保存文件
        $filename = iconv("UTF-8", "GB2312//IGNORE", '提现明细') . '-' . date('YmdHis') . '.xlsx';
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $filename . '"');
        header('Cache-Control: max-age=0');
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
    }

    /**
     * 格式化提现信息
     */
    private function cashInfo($cash)
    {
        $content = '';
        if ($cash['pay_type']['value'] == 20) {
            $content .= "支付宝姓名：{$cash['alipay_name']}\n";
            $content .= "  支付宝账号：{$cash['alipay_account']}\n";
        } elseif ($cash['pay_type']['value'] == 30) {
            $content .= "开户行：{$cash['bank_name']}\n";
            $content .= "  姓名：{$cash['bank_account']}\n";
            $content .= "  卡号：{$cash['bank_account']}\n";
        }
        return $content;
    }

    /**
     * 格式化商品信息
     */
    private function filterProductInfo($order)
    {
        $content = '';
        foreach ($order['product'] as $key => $product) {
            $content .= ($key + 1) . ".商品名称：{$product['product_name']}\n";
            !empty($product['product_attr']) && $content .= "　商品规格：{$product['product_attr']}\n";
            $content .= "　购买数量：{$product['total_num']}\n";
            $content .= "　商品总价：{$product['total_price']}元\n\n";
        }
        return $content;
    }

    /**
     * 日期值过滤
     */
    private function filterTime($value)
    {
        if (!$value) return '';
        return date('Y-m-d H:i:s', $value);
    }
    /**
     * 导出excel
     * @param $strTable 表格内容
     * @param $filename 文件名
    */
    private function downloadExcel($strTable,$filename)
    {
        ob_end_clean();
        header('Cache-Control: max-age=0');
        header("Content-type: application/vnd.ms-excel");
        header("Content-Type: application/force-download");
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header("Content-Disposition: attachment; filename=".$filename.'-'.date('Ymd').".xlsx");
        header('Expires:0');
        header('Pragma:public');
        echo '<html><meta http-equiv="Content-Type" content="text/html; charset=utf-8" />'.$strTable.'</html>';
        exit();
    }

}