AZA_Export_Order_2

gistfile1.txt

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use App\Model\Customer;
use App\Model\Orderstate;

$customerIds = array_column(DB::select('select id, (select count(*) from `order` o where o.customer_id = c.id and orderstate_id in(9,10,12,29,67)) as total_order, (select max(created_at) from `order` o where o.customer_id = c.id) as latest_order from `customer` c  having total_order > 2 order by total_order desc, latest_order desc'), 'id');

$spreadsheet = new Spreadsheet();
$activeWorksheet = $spreadsheet->getActiveSheet();
$index = 1;
$orderstates = Orderstate::get();
Customer::whereIntegerInRaw('id', $customerIds)
    ->orderByRaw('field(id,' . implode(',', $customerIds) . ')')
    ->with('orders')
    ->chunkById(10000, function ($customers) use ($orderstates, &$index, $activeWorksheet) {
        foreach ($customers as $customer) {
            $index++;
            $activeWorksheet->setCellValue("A$index", $index - 1);
            $activeWorksheet->setCellValueExplicit("B$index", $customer->phone, \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING2);
            $activeWorksheet->setCellValueExplicit("C$index", $customer->id, \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING2);
            foreach ($customer->orders->whereIn('orderstate_id', [9, 10, 12, 29, 67])->values() as $key => $order) {
                $activeWorksheet->getCell([$key + 4, $index])->setValue($orderstates->where('id', $order->orderstate_id)->first()->name);
            }
            $latestOrder = $customer->orders->last();
            if ($latestOrder && !in_array($latestOrder->orderstate_id, [9, 10, 12, 29, 67])) {
                $activeWorksheet->getCell([$key + 5, $index])->setValue($orderstates->where('id', $latestOrder->orderstate_id)->first()->name);
            }
        }
    });

$writer = new Xlsx($spreadsheet);
$writer->save('report.xlsx');