erp_yzx/app/admin/controller/OrderReport.php

<?php


namespace app\admin\controller;


use app\common\model\AdminModel;
use app\common\model\CompanyModel;
use app\common\model\OrderProductModel;
use app\common\model\PaymentChannelModel;
use app\Request;
use think\App;
use think\facade\View;

class OrderReport extends \app\BaseController
{

    private $orderProductModel;
    private $companyModel;
    private $channelModel;
    private $adminModel;

    public function __construct(App $app)
    {
        $this->orderProductModel = new OrderProductModel();
        $this->companyModel = new CompanyModel();
        $this->channelModel = new PaymentChannelModel();
        $this->adminModel = new AdminModel();
        parent::__construct($app);
    }


    public function index(Request $request)
    {
        $params = $request->param();
        $fmt_params = [
            'order_no' => format_string($params['order_no'] ?? null),
            'bar_code' => format_string($params['bar_code'] ?? null),
            'product_name' => format_string($params['product_name'] ?? null),
            "company_id" => format_string($params['company_id'] ?? null),
            "adviser_id" => format_string($params['adviser_id'] ?? null),
            "teacher_id" => format_string($params['teacher_id'] ?? null),
            "date_range" => format_string($params['date_range'] ?? null),
            "channel_id" => format_string($params['channel_id'] ?? null),
        ];
        $list = $this->orderProductModel->findByPaginate($fmt_params);
        foreach ($list as &$item) {
            $payments = $item->getRelation('payments');
            $payment_names = join(' ', array_unique(array_map(function ($data) {
                return "[{$data['channel_name']}]";
            }, $payments->toArray())));
            $item['payment_names'] = $payment_names;
        }
        View::assign([
            'params' => $fmt_params,
            'list' => $list,
            'companys' => $this->companyModel->findAll(),
            'admins' => $this->adminModel->findAll(),
            'channels' => $this->channelModel->findAll(),
        ]);
        return view();
    }

    public function report(Request $request)
    {
        $params = $request->param();
        $list = $this->orderProductModel->report([
            'order_no' => format_string($params['order_no'] ?? null),
            'bar_code' => format_string($params['bar_code'] ?? null),
            'product_name' => format_string($params['product_name'] ?? null),
            "company_id" => format_string($params['company_id'] ?? null),
            "adviser_id" => format_string($params['adviser_id'] ?? null),
            "teacher_id" => format_string($params['teacher_id'] ?? null),
            "date_range" => format_string($params['date_range'] ?? null),
            "channel_id" => format_string($params['channel_id'] ?? null),
        ])->toArray();
        $objPHPExcel = new \PHPExcel();
        try {
            $objSheet = $objPHPExcel->getActiveSheet();
            //工作表标题
            $objSheet->setTitle("main");
            //第一行内容，放列标题
            $objSheet->setCellValue("A1", lang('Invoice No'));
            $objSheet->setCellValue("B1", lang('Store') . lang('Abbr'));
            $objSheet->setCellValue("C1", lang("Date"));
            $objSheet->setCellValue("D1", lang("Bar code"));
            $objSheet->setCellValue("E1", lang('Product') . lang('Name'));
            $objSheet->setCellValue("F1", lang("Belong Company"));
            $objSheet->setCellValue("G1", lang("Sale price"));
            $objSheet->setCellValue("H1", lang("Consumption tax"));
            $objSheet->setCellValue("I1", lang("Quantity"));
            $objSheet->setCellValue("J1", lang("Real Price"));
            $objSheet->setCellValue("K1", lang("Adviser") . '1');
            $objSheet->setCellValue("L1", lang("Adviser") . '2');
            $objSheet->setCellValue("M1", lang("Teacher") . '1');
            $objSheet->setCellValue("N1", lang("Teacher") . '2');
            //设置第一行的背景颜色
            $objPHPExcel->getActiveSheet()
                ->getStyle('A1:N1')
                ->getFill()
                ->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)
                ->getStartColor()
                ->setRGB('FFFF00');
            //后面行循环放内容
            for ($i = 0; $i < count($list); $i++) {
                $item = $list[$i];
                $objSheet->setCellValue("A" . ($i + 2), fmtN_A($item['order_no']));
                $objSheet->setCellValue("B" . ($i + 2), fmtN_A(preg_replace("/\\d+/", '', $item['order_no'])));
                $objSheet->setCellValue("C" . ($i + 2), fmtN_A($item['create_time']));
                $objSheet->setCellValue("D" . ($i + 2), fmtN_A($item['product']['bar_code']));
                $objSheet->setCellValue("E" . ($i + 2), fmtN_A($item['product_name']));
                $objSheet->setCellValue("F" . ($i + 2), fmtN_A($item['product']['company']['name']));
                $objSheet->setCellValue("G" . ($i + 2), fmtN_A($item['product']['real_price']));
                $objSheet->setCellValue("H" . ($i + 2), fmtN_A($item['sales_tax']));
                $objSheet->setCellValue("I" . ($i + 2), 1);
                $objSheet->setCellValue("J" . ($i + 2), fmtN_A($item['transaction_price']));
                $objSheet->setCellValue("K" . ($i + 2), fmtN_A($item['adviser_1_name']));
                $objSheet->setCellValue("L" . ($i + 2), fmtN_A($item['adviser_2_name']));
                $objSheet->setCellValue("M" . ($i + 2), fmtN_A($item["teacher_1_name"]));
                $objSheet->setCellValue("N" . ($i + 2), fmtN_A($item["teacher_2_name"]));
            }
            $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
            $fileName = time();
            header('Content-Disposition: attachment;filename="' . $fileName . '.xlsx"'); //设置excel文件名称
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            return json($objWriter->save("php://output"));
        } catch (\PHPExcel_Exception $e) {
        }
    }
}