<?php

namespace App\Exports;

use Maatwebsite\Excel\Concerns\FromCollection;

use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\FromQuery;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithColumnFormatting;
use Maatwebsite\Excel\Concerns\ShouldAutoSize;

use App\Models\LoanBorrowList;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use Illuminate\Support\Facades\DB;
use Carbon\Carbon;


class InvoicesExport implements FromQuery, WithHeadings, ShouldAutoSize, WithColumnFormatting
{
    use Exportable;
    protected $company_id;
    protected $filters;

    function __construct($id,$filters) {
        $this->company_id = $id;
        $this->filters = $filters;
    }

    public function query()
    {     
        $company = $this->company_id;       
        $filters = $this->filters;
        if($filters['export_type'] == "Loan"){
            return LoanBorrowList::query()
                        ->select(
                            'loan_borrow_lists.invoice_no',
                            'loan_borrow_lists.original_rate',
                            'loan_borrow_lists.status',
                            'workers.first_name',
                            'workers.last_name',
                            'IBR.initial_date',
                            'IBR.cutoff_date', 
                            DB::raw("IFNULL(IBR.other_expense, 0.0)"),
                            DB::raw("IFNULL(IBR.outstanding_balance, 0.0)"),
                            DB::raw("IFNULL(IBR.total_billing, 0.0)"),
                            'OBR.loaner_status',
                            'IBR.days', 
                        )
                        ->join('inbound_billing_requests AS IBR', 'IBR.loan_borrow_lists_id', '=', 'loan_borrow_lists.id')
                        ->join('workers', 'workers.id', '=', 'loan_borrow_lists.worker_id')
                        ->join('outbound_billing_requests as OBR', 'OBR.loan_borrow_lists_id', '=', 'loan_borrow_lists.id')
                        ->when($filters['inbound_status'] != 'all' , function($query) use ( $filters) { 
                            return $query->whereRaw('loan_borrow_lists.status = "'.$filters['inbound_status'].'"');
                        })
                        ->when($filters['outbound_status'] != 'all' , function($query) use ( $filters) { 
                            return $query->whereRaw('OBR.loaner_status = "'.$filters['outbound_status'].'"');
                        })
                        ->when($filters['dates'] == 'single_date', function($query) use ( $filters) {
                            return $query->whereRaw('loan_borrow_lists.date_to <= date("'.Carbon::parse($filters['single_date'])->toDateString().'")');
                         })
                        ->when($filters['dates'] == 'date_range', function($query) use ( $filters) {
                             return $query->Where(function($query) use( $filters) {
                                        $query->whereRaw('loan_borrow_lists.date_from >= date("'.Carbon::parse($filters['date_hired_from'])->toDateString().'")')
                                              ->orWhereRaw('loan_borrow_lists.date_from <= date("'.Carbon::parse($filters['date_hired_to'])->toDateString().'")');
                                  });
                        })
                        ->where('loan_borrow_lists.loaner_company_id', $company);
                      
        }elseif($filters['export_type'] == "Borrow"){
            return LoanBorrowList::query()
            ->select(
                'loan_borrow_lists.invoice_no',
                'loan_borrow_lists.rate',
                'loan_borrow_lists.status',
                'workers.first_name',
                'workers.last_name',
                'IBR.initial_date',
                'IBR.cutoff_date', 
                DB::raw("IFNULL(IBR.other_expense, 0.0)"),
                DB::raw("IFNULL(IBR.outstanding_balance, 0.0)"),
                DB::raw("IFNULL(IBR.total_billing, 0.0)"),
                'OBR.loaner_status',
                'IBR.days', 
            )
            ->join('inbound_billing_requests AS IBR', 'IBR.loan_borrow_lists_id', '=', 'loan_borrow_lists.id')
            ->join('workers', 'workers.id', '=', 'loan_borrow_lists.worker_id')
            ->join('outbound_billing_requests as OBR', 'OBR.loan_borrow_lists_id', '=', 'loan_borrow_lists.id')
            ->when($filters['inbound_status'] != 'all' , function($query) use ( $filters) { 
                return $query->whereRaw('loan_borrow_lists.status = "'.$filters['inbound_status'].'"');
            })
            ->when($filters['outbound_status'] != 'all' , function($query) use ( $filters) { 
                return $query->whereRaw('OBR.loaner_status = "'.$filters['outbound_status'].'"');
            })
            ->when($filters['dates'] == 'single_date', function($query) use ( $filters) {
                return $query->whereRaw('loan_borrow_lists.date_to <= date("'.Carbon::parse($filters['single_date'])->toDateString().'")');
             })
            ->when($filters['dates'] == 'date_range', function($query) use ( $filters) {
                 return $query->Where(function($query) use( $filters) {
                            $query->whereRaw('loan_borrow_lists.date_from >= date("'.Carbon::parse($filters['date_hired_from'])->toDateString().'")')
                                  ->orWhereRaw('loan_borrow_lists.date_from <= date("'.Carbon::parse($filters['date_hired_to'])->toDateString().'")');
                      });
            })
         /*    ->where('loan_borrow_lists.status', '!=', 'Approved')
            ->where('loan_borrow_lists.status', '!=', 'Pending') */
            ->where('loan_borrow_lists.borrower_company_id', $company);
        }
    }
    public function headings(): array
    {
        return [
                 "Invoice Number", 
                 "Borrowed Worker Rate (per hour)", 
                 "Invoice Status",
                 "First name", 
                 "Last name", 
                 "Invoice Initial Date", 
                 "Invoice Cutoff Date", 
                 "Other Expenses", 
                 "Outstanding Balance",
                 "Total Billing",
                 "Bill Payment Status", 
                 "Details", 
                ];
    }
    public function columnFormats(): array
    {
        return [
            'A' => NumberFormat::FORMAT_TEXT,
            'B' => NumberFormat::FORMAT_TEXT,
            'B' => NumberFormat::FORMAT_TEXT,
            'C' => NumberFormat::FORMAT_TEXT,
            'D' => NumberFormat::FORMAT_TEXT,
            'E' => NumberFormat::FORMAT_DATE_DDMMYYYY,            
            'F' => NumberFormat::FORMAT_DATE_DDMMYYYY,
            'G' => NumberFormat::FORMAT_TEXT,
            'H' => NumberFormat::FORMAT_TEXT,
            'I' => NumberFormat::FORMAT_TEXT,
            'J' => NumberFormat::FORMAT_TEXT,
        ];
    }
    
}