<?php

namespace App\Exports\Sheets;

use App\Models\Server\MerchantAccount;
use Illuminate\Support\Facades\Log;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\ShouldAutoSize;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Concerns\WithStrictNullComparison;
use Maatwebsite\Excel\Concerns\WithTitle;
use Maatwebsite\Excel\Events\AfterSheet;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use Maatwebsite\Excel\Concerns\WithColumnFormatting;

class SaasLoveIncome implements FromCollection, WithTitle, WithEvents, WithStrictNullComparison, ShouldAutoSize,WithColumnFormatting
{
    protected $start_date, $end_date;
    public function __construct($start_date, $end_date)
    {
        $this->start_date = $start_date;
        $this->end_date = $end_date;
    }

    public function columnFormats(): array
    {
        return [
            'D' => NumberFormat::FORMAT_NUMBER_00, //金额保留两位小数
        ];
    }
    /**
     * @return \Illuminate\Support\Collection
     */
    public function collection()
    {
        $start_date = $this->start_date;
        $end_date = $this->end_date;
        Log::info($start_date.'---'.$end_date);
        $merchants = MerchantAccount::with(['earnings'=> function($sql) use($start_date, $end_date){
            $sql->where('m_user_id', 0)->whereBetween('created_at', [$start_date, $end_date]);
        }, 'anchorV2'])->whereIn('id', [491, 71])->get();
        $rows = [
            ["深圳福恋智能信息科技有限公司
            
            SAAS系统 直属福恋收入明细表", '', '', '', '','','',''],
            ["序号", '日期', '收款名称', '金额', '收款手续费','实际到账额','收入到账银行','备注']
        ];
        $date = date('Y-m', strtotime($start_date));
        $index = 1;
        $sum = 0;
        foreach ($merchants as $merchant) {
           $amount = $merchant->earnings->sum('value');
            $sum += $amount;
            $amount = number_format($merchant->earnings->sum('value'), 2, '.', '') ;
            $rows[] = [$index, $date, $merchant->anchorV2->name, $amount, '', '', '微信', ''];
            $index++;
        }
        $rows[] = ['', '合计','', $sum, '', '', '', ''];
        return collect($rows);
    }

    public function registerEvents(): array
    {
        return [
            AfterSheet::class  => function(AfterSheet $event) {
                //设置列宽
                $arr  = ['A','B','C','D','E','F',"G","H"];
                foreach ($arr as $value) {
                    $event->sheet->getDelegate()->getColumnDimension($value)->setWidth(20);
                }
                //自动换行
                $event->sheet->getStyle("A1:F1")->getAlignment()->setWrapText(TRUE);
                $event->sheet->getDelegate()->getRowDimension(1)->setRowHeight(50);
//                //设置行高，$i为数据行数
                for ($i = 2; $i<=1265; $i++) {
                    $event->sheet->getDelegate()->getRowDimension($i)->setRowHeight(30);
                }
//                //设置区域单元格垂直居中
                $event->sheet->getDelegate()->getStyle('A1:K1265')->getAlignment()->setVertical('center');
//                //设置区域单元格水平居中
                $event->sheet->getDelegate()->getStyle('A1:K1265')->getAlignment()->setHorizontal('center');
//                $event->sheet->getDelegate()->getStyle('A11:F18')->getAlignment()->setHorizontal('center');
//
//                //设置区域单元格字体、颜色、背景等，其他设置请查看 applyFromArray 方法，提供了注释
//                $event->sheet->getDelegate()->getStyle('A1:F9')->applyFromArray([
//                    'borders'=> [
//                        'allBorders' => [
//                            'borderStyle' =>'thin',
//                        ]
//                    ]
//                ]);
//                $event->sheet->getDelegate()->getStyle('A11:F19')->applyFromArray([
//                    'borders'=> [
//                        'allBorders' => [
//                            'borderStyle' =>'thin',
//                        ]
//                    ]
//                ]);
//                $event->sheet->getDelegate()->getStyle('1')->applyFromArray([
//                    'font' => [
//                        'name' => 'Arial',
//                        'bold' => true,
//                        'italic' => false,
//                        'strikethrough' => false,
//                        'size' => 20,
//                        'color' => [
//                            'rgb' => '000000'
//                        ],
//                    ],
//                    // 'fill' => [
//                    //     'fillType' => 'linear', //线性填充，类似渐变
//                    //     'rotation' => 45, //渐变角度
//                    //     'startColor' => [
//                    //         'rgb' => '000000' //初始颜色
//                    //     ],
//                    //     //结束颜色，如果需要单一背景色，请和初始颜色保持一致
//                    //     'endColor' => [
//                    //         'argb' => 'FFFFFF'
//                    //     ]
//                    // ],
//
//                ]);
//                $event->sheet->getDelegate()->getStyle('2:9')->applyFromArray([
//                    'font' => [
//                        'name' => 'Arial',
//                        'bold' => false,
//                        'italic' => false,
//                        'strikethrough' => false,
//                        'size' => 12,
//                        'color' => [
//                            'rgb' => '000000'
//                        ],
//
//                    ],
//                ]);
//                $event->sheet->getDelegate()->getStyle('11')->applyFromArray([
//                    'font' => [
//                        'name' => 'Arial',
//                        'bold' => true,
//                        'italic' => false,
//                        'strikethrough' => false,
//                        'size' => 20,
//                        'color' => [
//                            'rgb' => '000000'
//                        ]
//                    ],
//
//                ]);
//                $event->sheet->getDelegate()->getStyle('12:19')->applyFromArray([
//                    'font' => [
//                        'name' => 'Arial',
//                        'bold' => false,
//                        'italic' => false,
//                        'strikethrough' => false,
//                        'size' => 12,
//                        'color' => [
//                            'rgb' => '000000'
//                        ],
//
//                    ],
//                ]);
                //合并单元格
                $event->sheet->getDelegate()->mergeCells('A1:H1');
                $event->sheet->getDelegate()->mergeCells('B5:C5');
//                $event->sheet->getDelegate()->mergeCells('B4:F4');
//                $event->sheet->getDelegate()->mergeCells('B5:F5');
//                $event->sheet->getDelegate()->mergeCells('B6:F6');
//                $event->sheet->getDelegate()->mergeCells('D7:F7');
//                $event->sheet->getDelegate()->mergeCells('C8:F8');
//                $event->sheet->getDelegate()->mergeCells('A9:F9');
//                $event->sheet->getDelegate()->mergeCells('A11:F11');
//                $event->sheet->getDelegate()->mergeCells('B13:F13');
//                $event->sheet->getDelegate()->mergeCells('B14:F14');
//                $event->sheet->getDelegate()->mergeCells('B15:F15');
//                $event->sheet->getDelegate()->mergeCells('B16:F16');
//                $event->sheet->getDelegate()->mergeCells('D17:F17');
//                $event->sheet->getDelegate()->mergeCells('C18:F18');
//                $event->sheet->getDelegate()->mergeCells('A19:F19');

            }
        ];
    }


    /**
     * @return string
     */
    public function title(): string
    {
        return '福恋收入明细';
    }
}