weixin/app/Exports/Car/BillExport.php

<?php

namespace App\Exports\Car;

use App\Repositories\Enums\Car\BillTypeEnum;
use App\Repositories\Enums\Car\OrderDrivingStatusEnum;
use App\Repositories\Enums\Check\StatusEnum;
use App\Repositories\Models\Base\Dict;
use App\Repositories\Models\Car\Bill;
use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\FromQuery;
use Maatwebsite\Excel\Concerns\ShouldAutoSize;
use Maatwebsite\Excel\Concerns\WithColumnWidths;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithMapping;
use Maatwebsite\Excel\Concerns\WithStyles;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;


class BillExport implements FromQuery, WithMapping, WithHeadings, ShouldAutoSize, WithColumnWidths, WithStyles
{
    use Exportable;

    const BaseFields = [
        [
            'name' => '序号',
            'width' => 8,
        ],
//        [
//            'name' => '报名日期',
//            'width' => 15,
//        ],
        [
            'name' => '学员姓名',
            'width' => 15,
        ],
//        [
//            'name' => '手机号',
//            'width' => 15,
//        ],
//        [
//            'name' => '身份证号',
//            'width' => 30,
//        ],
        [
            'name' => '班型',
            'width' => 15,
        ],
        [
            'name' => '费用类型',
            'width' => 15,
            'type' => 0,
        ],
        [
            'name' => '总学费',
            'width' => 10,
        ],
//        [
//            'name' => '优惠金额',
//            'width' => 10,
//        ],
//        [
//            'name' => '已交学费',
//            'width' => 10,
//        ],
//        [
//            'name' => '学车进度',
//            'width' => 15,
//        ],
        [
            'name' => '门店名称',
            'width' => 20,
        ],
        [
            'name' => '准驾类型',
            'width' => 10,
        ],
        [
            'name' => '招生老师',
            'width' => 15,
        ],
        [
            'name' => '缴费金额',
            'width' => 10,
            'type' => 1
        ],
        [
            'name' => '奖励金额',
            'width' => 10,
            'type' => 0
        ],
        [
            'name' => '录入人员',
            'width' => 15,
            'type' => 1,
        ],
        [
            'name' => '审核状态',
            'width' => 15,
            'type' => 0
        ],
        [
            'name' => '确收状态',
            'width' => 15,
            'type' => 1
        ],
        [
            'name' => '审核人',
            'width' => 15,
            'type' => 0
        ],
        [
            'name' => '确收人',
            'width' => 15,
            'type' => 1
        ],
        [
            'name' => '收款人',
            'width' => 15,
            'type' => 0,
        ],
        [
            'name' => '订单号',
            'width' => 25,
        ],
        [
            'name' => '是否发放',
            'width' => 10,
            'type' => 0
        ],
        [
            'name' => '备注',
            'width' => 20
        ],
        [
            'name' => '时间',
            'width' => 20
        ],
    ];
    protected $rowNums = 0;
    protected $where = [];
    protected $lastC = 'A';
    protected $fields = [];

    public function __construct($where, $field_ids = [])
    {
        $this->where = $where;
        $fields = [];
        if (!count($field_ids)) {
            $fields = self::BaseFields;
        } else {
            foreach ($field_ids as $field_id) {
                $fields[] = self::BaseFields[$field_id];
            }
        }
        $this->fields = $fields;
    }

    public function map($row): array
    {
        $row = $row->toArray();
        $this->rowNums++;
        $data = [];
        foreach (array_column($this->fields, 'name') as $k => $field) {
            if ($field == '序号') $data[$k] = $this->rowNums;
//            if ($field == '报名日期') $data[$k] = $row['sign_up_day'];
            if ($field == '学员姓名') $data[$k] = $row['user'] ? $row['user']['name'] : '';

//            if ($field == '手机号') $data[$k] = $row['user_mobile'];
//            if ($field == '身份证号') $data[$k] = $row['user_id_card'];
            if ($field == '总学费') $data[$k] = $row['order']['total_money'];
            if ($field == '缴费金额') $data[$k] = $row['money'];
            if ($field == '奖励金额') $data[$k] = $row['money'];
            if ($field == '备注') $data[$k] = $row['remark'];
            if ($field == '是否发放') $data[$k] = $row['is_give'];
            if ($field == '时间') $data[$k] = $row['created_at'];


            if ($field == '班型') $data[$k] = $row['order'] ? $row['order']['grade']['name'] : '';
            if ($field == '门店名称') $data[$k] = $row['shop'] ? $row['shop']['name'] : '';
            if ($field == '招生老师') $data[$k] = $row['order']['salesman_admin'] ? $row['order']['salesman_admin']['name'] : '';
            if ($field == '录入人员') $data[$k] = $row['admin'] ? $row['admin']['name'] : '';
            if ($field == '录入人员') $data[$k] = $row['admin'] ? $row['admin']['name'] : '';
            if ($field == '收款人') $data[$k] = $row['admin'] ? $row['admin']['name'] : '';
            if ($field == '审核人') $data[$k] = $row['check_admin'] ? $row['check_admin']['name'] : '';
            if ($field == '确收人') $data[$k] = $row['check_admin'] ? $row['check_admin']['name'] : '';
            if ($field == '订单号') $data[$k] = $row['order'] ? $row['order']['no'] : '';

            if ($field == '费用类型') $data[$k] = BillTypeEnum::getDescription($row['type_id']);
            if ($field == '审核状态') $data[$k] = StatusEnum::getDescription($row['check_status']);
            if ($field == '确收状态') $data[$k] = StatusEnum::getDescription($row['check_status']);
            if ($field == '学车进度') $data[$k] = OrderDrivingStatusEnum::getDescription($row['order']['driving_status']);
            if ($field == '准驾类型') $data[$k] = Dict::byCodeAndIdGetDict('CAR_TYPE', $row['order']['drive_type']);
        }

        return $data;
    }

    public function query()
    {
        $where = $this->where;
        return Bill::query()
            ->with(['user', 'shop', 'order', 'order.grade', 'admin', 'check_admin'])
            ->when(array_key_exists('ids', $where) && count($where['ids']), function ($query) use ($where) {
                return $query->whereIn('id', $where['ids']);
            })
            ->when(array_key_exists('disburse', $where), function ($query) use ($where) {
                return $query->where('disburse', $where['disburse']);
            })
            ->when(array_key_exists('check_status', $where), function ($query) use ($where) {
                return $query->where('check_status', $where['check_status']);
            })
            ->when(array_key_exists('type_id', $where), function ($query) use ($where) {
                return $query->where('type_id', $where['type_id']);
            })
            ->when(array_key_exists('shop_id', $where), function ($query) use ($where) {
                return $query->where('shop_id', $where['shop_id']);
            })
            ->when(array_key_exists('is_give', $where), function ($query) use ($where) {
                return $query->where('is_give', $where['is_give']);
            })
            ->when(array_key_exists('start_check_day', $where), function ($query) use ($where) {
                return $query->whereDate('check_time', '>=', $where['start_check_day']);
            })
            ->when(array_key_exists('end_check_time', $where), function ($query) use ($where) {
                return $query->whereDate('check_time', '<=', $where['start_check_day']);
            })
            ->when(array_key_exists('admin_id', $where), function ($query) use ($where) {
                return $query->where('admin_id', $where['admin_id']);
            })
            ->where('is_lock', 0)
            ->orderByDesc('id');
    }

    public function headings(): array
    {
        $room_name = '财务流水列表';
        $data[] = [$room_name];

        $data[] = array_column($this->fields, 'name');
        return $data;
    }

    public function headingRow(): int
    {
        return 2;
    }

    public function columnWidths(): array
    {
        $i = 65;
        $data = [];
        foreach ($this->fields as $k => $field) {
            $s = intval($k / 26);

            if (!$s) $key = chr($i + $k);
            if ($s > 0) {
                $y = intval($k % 26);
                $key = chr($s + $i - 1);
                $key .= chr($y + $i);
            }
            $data["{$key}"] = $field['width'];
            $this->lastC = $key;
        }
        return $data;
    }

    public function styles(Worksheet $sheet)
    {
        $last = $this->lastC;
        $cellRange = "A1:{$last}1";
        $sheet->getStyle($cellRange)->getFont()->setSize(12);

        $sheet->getDefaultRowDimension()->setRowHeight(40);//设置行高

        // 文字居中
        $lastrow = $sheet->getHighestRow();
        $sheet->getStyle("A1:{$last}" . $lastrow)->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);//垂直居中
        $sheet->getStyle("A1:{$last}" . $lastrow)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle("A1:{$last}" . $lastrow)->getAlignment()->setWrapText(true);

        $sheet->mergeCells("A1:{$last}1"); //合并
        $sheet->getStyle("A1:{$last}1")->getFont()->setSize(16);
    }


}