<?php
namespace App\Http\Controllers\Admin\accounting;

use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use App\Helpers\LogActivity;
use App\Helpers\ReportHelper;
use App\Models\{
    User,
    Role,
    School,
    Plan
};
use App\Models\accounting\{
    AccountLedger,
    AccountLedgerGroup,
    AccountBank,
    AccountPaymentReceive,
    AccountCustomer,
    AccountAdvancePayment,
    AccountInvoice,
    AccountPurchaseBill,
    AccountExpense,
    AccountExpenseDetail,
    AccountExpenseDetailItem
};
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Hash;
use Illuminate\Support\Str;
use App\Exports\ProfitLossExport;
use App\Exports\BalanceSheetExport;
use App\Exports\DayBookExport;
use Maatwebsite\Excel\Facades\Excel;
use Validator;

class ReportController extends Controller
{
    protected $admin_id;
    protected $school_id, $res,$allData,$data,$validate;
    public function __construct()
    {
        $this->middleware(function ($request, $next) {
            // fetch session and use it in entire class with constructor
            if (session()->get('loggedIn')['user_type'] == 'admin') {
                $this->admin_id = session()->get('loggedIn')['id'];
                $this->school_id = School::where("admin_id",$this->admin_id)->pluck("id")->first();
            } else {
                $this->admin_id = session()->get('loggedIn')['admin_id'];
                $this->school_id = School::where("admin_id",$this->admin_id)->pluck("id")->first();
            }
         
            return $next($request);
        });
    }

    public function export_pl()
    {        
        $filename = "profit_and_loss.xlsx";
        return Excel::download(new ProfitLossExport($this->admin_id),$filename);
        
    }

    public function exportBalanceSheet()
    {
        
        $filename = "balance_sheet.xlsx";
        return Excel::download(new BalanceSheetExport($this->admin_id),$filename);
    }

    public function exportDayBook()
    {
        
        $filename = "day_book.xlsx";
        return Excel::download(new DayBookExport($this->admin_id),$filename);
    }

    

     // Display all employees
     public function trial_balance()
     {
        $data['pagetitle'] = "account";
        $data['mainSuperAdminMenu'] = 'reports';
        $data['superadminSubMenu'] = 'trial-balance';

       
       $currentMonthStart = date('Y-m-01');
        $currentMonthEnd = date('Y-m-t');

        $start_date = request('start_date', $currentMonthStart);
        $end_date = request('end_date', $currentMonthEnd);

        
        $received_cash = AccountPaymentReceive::where('admin_id',$this->admin_id)
        ->where("payment_method","=","cash")
        ->whereDate('created_at','>=',$start_date)
        ->whereDate('created_at','<=',$end_date)
        ->where("type","received")->sum('amount');

        $payment_cash = AccountPaymentReceive::where('admin_id',$this->admin_id)
        ->where("payment_method","=","cash")
        ->whereDate('created_at','>=',$start_date)
        ->whereDate('created_at','<=',$end_date)
        ->where("type","payment")->sum('amount');
        
       

        $receive_ad = AccountAdvancePayment::where("admin_id",$this->admin_id)
        ->where("payment_method","cash")
        ->whereDate('created_at','>=',$start_date)
        ->whereDate('created_at','<=',$end_date)
        ->where('type','received')
        ->sum('amount');

        $pay_ad = AccountAdvancePayment::where("admin_id",$this->admin_id)
        ->where("payment_method","cash")
        ->whereDate('created_at','>=',$start_date)
        ->whereDate('created_at','<=',$end_date)
        ->where('type','payment')
        ->sum('amount');

        $receive = $received_cash + $receive_ad;
        $payment =  $payment_cash + $pay_ad;
    

        if($receive>=$payment)
        {
            $data["cash"] = $receive - $payment;
            $data["cash_type"] = "dr";
        }
        else
        {
            $data["cash"] = $payment - $receive;
            $data["cash_type"] = "cr";
        }

        $data["purchase"] = AccountPurchaseBill::where("admin_id",$this->admin_id)
        ->whereDate('created_at','>=',$start_date)
        ->whereDate('created_at','<=',$end_date)
        ->sum('grand_total');

        $data["sales"] = AccountInvoice::where("admin_id",$this->admin_id)        
        ->whereDate('created_at','>=',$start_date)
        ->whereDate('created_at','<=',$end_date)
        ->sum('grand_total');

        $data["expenses"] = AccountExpenseDetailItem::where("admin_id",$this->admin_id)
        ->whereDate('created_at','>=',$start_date)
        ->whereDate('created_at','<=',$end_date)->sum('item_total');
        

        return view('accounting.reports.trial_balance',$data);
     }

     public function profit_and_loss()
    {

        $data['pagetitle'] = "account";
        $data['mainSuperAdminMenu'] = 'reports';
        $data['superadminSubMenu'] = 'profit-and-loss';

       
       $currentMonthStart = date('Y-m-01');
        $currentMonthEnd = date('Y-m-t');

        $start_date = request('start_date', $currentMonthStart);
        $end_date = request('end_date', $currentMonthEnd);

        $opening_stock = ReportHelper::openingStock($start_date,$end_date,$this->admin_id);
        $data["purchase"] = ReportHelper::purchase($start_date,$end_date,$this->admin_id);
        $data["expenses"] = ReportHelper::expenses($start_date,$end_date,$this->admin_id);
        $data["sales"] = ReportHelper::sales($start_date,$end_date,$this->admin_id);
        $data["credit_note"] = ReportHelper::salesReturn($start_date,$end_date,$this->admin_id);
        $data["debit_note"] = ReportHelper::purchaseReturn($start_date,$end_date,$this->admin_id);

             

        return view('accounting.reports.profit_and_loss',$data);
    }

     public function balanceSheet()
    {

        $data['pagetitle'] = "account";
        $data['mainSuperAdminMenu'] = 'reports';
        $data['superadminSubMenu'] = 'balance-sheet';

       
       $currentMonthStart = date('Y-m-01');
        $currentMonthEnd = date('Y-m-t');

        $start_date = request('start_date', $currentMonthStart);
        $end_date = request('end_date', $currentMonthEnd);

        $purchase = ReportHelper::purchase($start_date,$end_date,$this->admin_id);
        $expenses = ReportHelper::expenses($start_date,$end_date,$this->admin_id);
        $sales = ReportHelper::sales($start_date,$end_date,$this->admin_id);
        $credit_note = ReportHelper::salesReturn($start_date,$end_date,$this->admin_id);
        $debit_note = ReportHelper::purchaseReturn($start_date,$end_date,$this->admin_id);

        $data["net_profit"] =  ReportHelper::net_profit($start_date,$end_date,$this->admin_id);;

        $data["cash"] = ReportHelper::cash($start_date,$end_date,$this->admin_id);
        $data["bank"] = ReportHelper::bank($start_date,$end_date,$this->admin_id);
        $data["debtor"] = ReportHelper::client($start_date,$end_date,$this->admin_id);
        $data["creditor"] = ReportHelper::vendor($start_date,$end_date,$this->admin_id);
        $data["stocks"] = ReportHelper::openingStock($start_date,$end_date,$this->admin_id);
            

        return view('accounting.reports.balance_sheet',$data);
    }
 
    public function dayBook()
    {
        $data['pagetitle'] = "account";
        $data['mainSuperAdminMenu'] = 'reports';
        $data['superadminSubMenu'] = 'day-book';

       
       $currentDate = date('Y-m-d');
       
        

        $start_date = request('start_date', $currentDate);

        $query = AccountPaymentReceive::where('account_payment_receives.admin_id',$this->admin_id)
        ->leftJoin("account_customers","account_customers.id","=","account_payment_receives.client_id");        
        
        $query->whereDate('account_payment_receives.created_at','=',$start_date);
               
        
       
        $data['payment'] = $query->orderBy('account_payment_receives.created_at','desc')
        ->select(
            "account_payment_receives.*",
            "account_customers.name as customer_name"
        )
        ->get();        

        $data["advance_payment"] = AccountAdvancePayment::where("account_advance_payments.admin_id",$this->admin_id)
        ->whereDate('account_advance_payments.created_at','=',$start_date)
        ->leftJoin('account_customers','account_customers.id','=','account_advance_payments.client_vendor_id')
        ->select(
            'account_advance_payments.*',
            'account_customers.name as customer_name'
        )->get();        

        return view('accounting.reports.day_book',$data);
    }

    public function allTransaction()
    {
        
        $data['pagetitle'] = "account";
        $data['mainSuperAdminMenu'] = 'reports';
        $data['superadminSubMenu'] = 'transaction';

       
       $currentDate = date('Y-m-d');       
        

        $start_date = request('date', $currentDate);

        $payments = AccountPaymentReceive::where('account_payment_receives.admin_id',$this->admin_id)
        ->leftJoin("account_customers","account_customers.id","=","account_payment_receives.client_id")       
        ->whereDate('account_payment_receives.created_at','=',$start_date)
        ->select(
            "account_payment_receives.*",
            "account_customers.name as customer_name"
        )
        ->get()->map(function ($item) {
            return [
                'id' => $item->id,
                'transaction_type' => 'payment/received',
                's_number'=>$item->payment_number,
                'date' => $item->date,
                'amount' => $item->amount,
                'customer_name' => $item->customer_name,
                'reference' => $item->reference_no,
                'payment_method' => $item->payment_method
            ];
        });        

        $advancePayments = AccountAdvancePayment::where("account_advance_payments.admin_id",$this->admin_id)
        ->whereDate('account_advance_payments.created_at','=',$start_date)
        ->leftJoin('account_customers','account_customers.id','=','account_advance_payments.client_vendor_id')
        ->select(
            'account_advance_payments.*',
            'account_customers.name as customer_name'
        )->get()->map(function ($item) {
            return [
                'id' => $item->id,
                'transaction_type' => 'advance_payment',
                's_number'=>$item->payment_number,
                'date' => $item->date,
                'amount' => $item->amount,
                'customer_name' => $item->customer_name,
                'reference' => $item->reference_no,
                'payment_method' => $item->payment_method
            ];
        });        

        $invoices = AccountInvoice::where("account_invoices.admin_id",$this->admin_id)
        ->whereDate('account_invoices.created_at','=',$start_date)
        ->leftJoin('account_customers','account_customers.id','=','account_invoices.customer_id')
        ->select(
            'account_invoices.*',
            'account_customers.name as customer_name'
        )->get()->map(function ($item) {
            return [
                'id' => $item->id,
                'transaction_type' => 'invoice',
                's_number'=>$item->invoice_number,
                'date' => $item->invoice_date,
                'amount' => $item->grand_total,
                'customer_name' => $item->customer_name,
                'reference' => $item->reference_no,
                'payment_method' => $item->payment_method
            ];
        });

         $purchaseBill = AccountPurchaseBill::where("account_purchase_bills.admin_id",$this->admin_id)
        ->whereDate('account_purchase_bills.created_at','=',$start_date)
        ->leftJoin('account_customers','account_customers.id','=','account_purchase_bills.vendor_id')
        ->select(
            'account_purchase_bills.*',
            'account_customers.name as customer_name'
        )->get()->map(function ($item) {
            return [
                'id' => $item->id,
                'transaction_type' => 'Purchase Bill',
                's_number'=>$item->bill_date,
                'date' => $item->bill_date,
                'amount' => $item->grand_total,
                'customer_name' => $item->customer_name,
                'reference' => $item->reference_no,
                'payment_method' => $item->payment_method
            ];
        });


        $data['transactions'] = $payments
        ->concat($advancePayments)
        ->concat($invoices)
        ->concat($purchaseBill)
        ->sortByDesc('date')
        ->values()
        ->all();

        //return $data["transactions"];

        return view('accounting.reports.transaction',$data);
    }
   
    
}
