<?php

namespace App\Exports;

use App\Models\OBF;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithStyles;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use Maatwebsite\Excel\Concerns\ShouldAutoSize;
use DB;

class ExportYetToDeliverReport implements FromCollection, WithHeadings,WithStyles ,ShouldAutoSize
{
    /**
    * @return \Illuminate\Support\Collection
    */
    function __construct($slug) {
        $this->slug = $slug;
    }
    public function collection()
    {
             $data = OBF::select(
            'obf.id',
            'obf.customer_name',
            'branches.name as branch',
            DB::raw("CONCAT(users.first_name,' ',users.last_name) AS sales_person_name"),
            'car_model.name AS model',
            'car_varient.name AS car_varient_name',
            'orders.vin_number',
            'orders.engine_number',
            'obf.on_road_price',
            DB::raw("(
                IFNULL(obf_finance.finance_discount, 0) +
                IFNULL(obf.way_off, 0) +
                IFNULL(obf.foc_accessories, 0) +
                IFNULL(obf.offer_ib_amount, 0) +
                IFNULL(obf.offer_kin_amount, 0)
            ) AS total_discount"),
            DB::raw('ROUND(SUM(cash_receipt.amount)) as total_received_amount'),
            DB::raw("(obf.on_road_price 
                - IFNULL((SELECT SUM(amount) FROM cash_receipt WHERE cash_receipt.obf_id = obf.id), 0)
                - (
                    IFNULL(obf_finance.finance_discount, 0) +
                    IFNULL(obf.way_off, 0) +
                    IFNULL(obf.foc_accessories, 0) +
                    IFNULL(obf.offer_ib_amount, 0) +
                    IFNULL(obf.offer_kin_amount, 0)
                )
            ) as due_amount"),
            'orders.invoice_no',
            DB::raw("CONCAT(LPAD(DAY(orders.invoice_date), 2, '0'), '-', ELT(MONTH(orders.invoice_date),'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'), '-', YEAR(orders.invoice_date)) as invoice_date"),
            DB::raw("CONCAT(UCASE(LEFT(REPLACE(orders.status, '_', ' '), 1)), 
            LOWER(SUBSTRING(REPLACE(orders.status, '_', ' '), 2))) AS invoice_status"),
            DB::raw("CONCAT(UCASE(LEFT(REPLACE(obf.insurance_status, '_', ' '), 1)),
            LOWER(SUBSTRING(REPLACE(obf.insurance_status, '_', ' '), 2))) AS insurance_status"),
            DB::raw("CONCAT(UCASE(LEFT(REPLACE(obf.rto_status, '_', ' '), 1)),
            LOWER(SUBSTRING(REPLACE(obf.rto_status, '_', ' '), 2))) AS rto_status"),
            DB::raw("CONCAT(UCASE(LEFT(REPLACE(obf.municipal_tax_status, '_', ' '), 1)),LOWER(SUBSTRING(REPLACE(obf.municipal_tax_status, '_', ' '), 2))) AS municipal_status")
        )
        ->leftJoin('branches','branches.id','=','obf.branch_id')
        ->leftjoin('users','users.id','=','obf.sales_person_id')
        ->leftJoin('cash_receipt','cash_receipt.obf_id','=','obf.id')
        ->leftJoin('orders','orders.obf_id','=','obf.id')
        ->leftJoin('exchange_purchase', 'exchange_purchase.obf_id', '=', 'obf.id')
        ->leftJoin('inventory','inventory.id','=','orders.inventory_id')
        ->leftjoin('products', 'inventory.name', 'products.id')
        ->leftjoin('car_varient', 'car_varient.id', 'products.veriant')
        ->leftjoin('car_model', 'car_model.id', 'products.car_model')
        ->leftJoin('obf_finance','obf_finance.obf_id','=','obf.id')
        ->where('orders.status', '=', 'invoice_create')
        ->groupBy(
            'obf.id',
            'obf.customer_name',
            'branches.name',
            'users.first_name',
            'users.last_name',
            'car_model.name',
            'car_varient.name',
            'orders.vin_number',
            'orders.engine_number',
            'obf.on_road_price',
            'obf_finance.finance_discount',
            'obf.way_off',
            'obf.foc_accessories',
            'obf.offer_ib_amount',
            'obf.offer_kin_amount',
            'orders.invoice_no',
            'orders.invoice_date',
            'orders.status',
            'obf.insurance_status',
            'obf.rto_status',
            'obf.municipal_tax_status'
        )
        ->get();
    
        if ($data->isEmpty()) {
            throw new \ErrorException('No Data Found!');
        }
        
        return $data;
    }

    public function headings() :array{
        return ["No" , "Customer Name" ,"Branch" ,"KEC", "Model", "Varient", "VIN Number","Engine Number" ,"On Road Price" ,"Less: Discount", "Received Amount" ,"Due Amount","Invoice No" ,"Invoice Date" ,"Invoice Status", "Insurance Status", "RTO Status", "Municipal Status"];
    }

    public function styles(Worksheet $sheet)
    {
        return [
            // Style the first row as bold text.
            1    => ['font' => ['bold' => true]],
        ];
    }
}