<?php

namespace App\Exports;

use App\Models\Transaction;

use Maatwebsite\Excel\Concerns\FromCollection;
use PhpOffice\PhpSpreadsheet\Shared\Date;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use Maatwebsite\Excel\Concerns\WithColumnFormatting;
use Maatwebsite\Excel\Concerns\WithMapping;
use Illuminate\Contracts\View\View;
use Maatwebsite\Excel\Concerns\FromView;
use DB;
class LedgerExport implements FromView
{
    public function __construct($datefrom, $dateto)
    {
        $this->datefrom = $datefrom;
        $this->dateto = $dateto;
    }

    public function view(): View
    {
        $datefrom=$this->datefrom;
        $dateto=$this->dateto;
        $final_data = DB::select("(SELECT a.transaction_date as transaction_date,a.type,a.payment_mode,a.ledger,a.ledger_value,a.amount,b.company_name,a.description,c.name as cat_name,a.supplier_name FROM transaction a
        LEFT JOIN users b on a.customer_id=b.id
        LEFT JOIN expense_category c on a.expense_category_id=c.id
        WHERE a.transaction_date >= '$datefrom' AND a.transaction_date <='$dateto' and a.type !='purchase')
        UNION ALL
        (SELECT a.payment_date as transaction_date,'payment' as type,a.payment_type as payment_mode,c.ledger,c.ledger_value,a.amount,b.company_name,'' as description,'' as cat_name,'' as supplier_name FROM transaction_payment a
        LEFT JOIN users b on a.payment_for=b.id
        LEFT JOIN transaction c on c.id=a.transaction_id
        WHERE a.payment_date >='$datefrom' AND a.payment_date <='$dateto')ORDER BY   transaction_date asc");
        // print_r($final_data);die;
        return view('excel-ledger', compact('final_data', 'datefrom', 'dateto'));

    }
}
