weixin/app/Exports/Human/StaffExport.php

<?php

namespace App\Exports\Human;

use App\Repositories\Enums\Human\InterviewInductionStatusEnum;
use App\Repositories\Enums\Human\InterviewStatusEnum;
use App\Repositories\Enums\Human\StaffEntryStatusEnum;
use App\Repositories\Enums\Human\StaffStepEnum;
use App\Repositories\Enums\ModelStatusEnum;
use App\Repositories\Models\Base\Dict;
use App\Repositories\Models\Human\Dispatch;
use App\Repositories\Models\Human\Interview;
use App\Repositories\Models\Human\Staff;
use App\Repositories\Models\Recruit\Position;
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 StaffExport implements FromQuery, WithMapping, WithHeadings, ShouldAutoSize, WithColumnWidths, WithStyles
{
    use Exportable;

    const BaseFields = [
        [
            'name' => '序号',
            'width' => 8,
        ],
        [
            'name' => '姓名',
            'width' => 15,
        ],
        [
            'name' => '性别',
            'width' => 8,
        ],
        [
            'name' => '身份证号',
            'width' => 30,
        ],
        [
            'name' => '手机号',
            'width' => 20,
        ],
        [
            'name' => '年龄',
            'width' => 8,
        ],
        [
            'name' => '供应商',
            'width' => 30,
        ],
        [
            'name' => '派遣单位',
            'width' => 30,
        ],
        [
            'name' => '派遣工种',
            'width' => 15,
        ],
        [
            'name' => '入职日期',
            'width' => 15,
        ],

        [
            'name' => '是否完善注册信息',
            'width' => 20,
        ],

        [
            'name' => '银行卡号',
            'width' => 30,
        ],

        [
            'name' => '银行名称',
            'width' => 20,
        ],
    ];
    protected $rowNums = 0;
    protected $factory_id = 0;
    protected $supplier_id = 0;
    protected $where = false;
    protected $day = 0;
    protected $lastC = 'A';
    protected $fields = [];

    public function __construct($day, $factory_id, $supplier_id, $field_ids = [], $where = false)
    {
        $this->day = $day;
        $this->factory_id = $factory_id;
        $this->supplier_id = $supplier_id;
        $this->where = $where;
        $fields = [];
        if (is_string($field_ids)) {
            $fields = self::BaseFields;
        } else {
            foreach ($field_ids as $field_id) {
                $fields[] = self::BaseFields[$field_id];
            }
        }
        $this->fields = $fields;
    }

    public function query()
    {
        $day = $this->day;
        $factory_id = $this->factory_id;
        $supplier_id = $this->supplier_id;
        $where = $this->where;
        return Staff::query()
            ->with(['human' => function ($q) {
                return $q->select('*');
            }, 'factory', 'supplier'])
            ->where('status', ModelStatusEnum::OK)
            ->where('step', StaffStepEnum::WORK)
            ->whereIn('entry_status', [StaffEntryStatusEnum::WAIT_SIGN, StaffEntryStatusEnum::WORKING])
            ->when($this->factory_id, function ($query) use ($factory_id) {
                return $query->where('factory_id', $factory_id);
            })
            ->when($day, function ($query) use ($day) {
                return $query->whereDate('induction_date', $day);
            })->when($supplier_id, function ($query) use ($supplier_id) {
                return $query->where('supplier_id', $supplier_id);
            })
            ->when($where, function ($query) use ($where) {
                return $query->where($where);
            })
            ->orderBy('id');
    }

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

    public function headings(): array
    {

        $room_name = '在职员工名单';
        $data[] = [$room_name];

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

    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);
    }

    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['human'] ? $row['human']['name'] : '';
            }
            if ($field == '身份证号') {
                $data[$k] = $row['human'] ? $row['human']['card_id'] . ' ' : '';
            }
            if ($field == '性别') {
                if ($row['human']) {
                    if ($row['human']['sex'] == 1) $data[$k] = '男';
                    if ($row['human']['sex'] == 2) $data[$k] = '女';
                } else {
                    $data[$k] = '未知';
                }
            }

            if ($field == '年龄') {
                $data[$k] = $row['human'] ? $row['human']['age'] : '';
            }

            if ($field == '手机号') {
                $data[$k] = $row['human'] ? $row['human']['mobile'] : '';
            }

            if ($field == '供应商') {
                $data[$k] = $row['supplier'] ? $row['supplier']['name'] : '';
            }
            if ($field == '派遣单位') {
                $data[$k] = $row['factory'] ? $row['factory']['name'] : '';
            }
            if ($field == '派遣工种') {
                $data[$k] = Dict::byCodeAndIdGetDict(Position::JOB_CODE, $row['job_position']);
            }

            if ($field == '入职日期') $data[$k] = $row['induction_date'];

            if ($field == '是否完善注册信息') {
                if ($row['human']) {
                    $data[$k] = $row['human']['is_register_info'] ? '已完善' : '未完善';
                } else {
                    $data[$k] = $row['human'] ? $row['human']['is_register_info'] : '未完善';
                }
            }
            if ($field == '银行卡号') {
                $data[$k] = $row['human'] ? $row['human']['bank_no'] . ' ' : '';
            }
            if ($field == '银行名称') {
                $data[$k] = $row['human'] ? $row['human']['bank_name'] : '';
            }

        }

        return $data;
    }


}