<?php

namespace App\Http\Controllers\Administrator;
use Illuminate\Http\Request;
use App\Http\Controllers\Controller;

use Auth;
use DB;
use App\JournalModel;
use App\AccountChartModel;
use App\ProjectModel;
use App\SearchModel;
use App\SupplierModel;
use DataTables;
use App\BankModel;

class ExpenseController extends Controller
{
    
    function __construct()
    {
        # code...
    }

    public function index()
    {
        return view('administrator.accounting.expense.index');
    }

    public function get_expense_list(Request $request)
    {
        $branch_id=\Branch::branch_id();
        $search = $request->input('search.value');
        $count_total = DB::table('nso007_journal')
            ->where('nso007_journal.branch_id' , '=' , \Branch::branch_id())
            ->where('nso007_journal.journal_acccode' , 'LIKE' , '5%')
            ->whereNotIn('nso007_journal.journal_type', [13])
            ->count();

        $count_filter = DB::table('nso007_journal')
            ->leftjoin('users', 'users.id', '=', 'nso007_journal.journal_by')
            ->leftjoin('nso007_account_chart', 'nso007_journal.journal_acccode', '=', 'nso007_account_chart.id')
            ->leftjoin('nso007_account_payable', 'nso007_journal.journal_referenceid', '=', 'nso007_account_payable.id')
            ->leftjoin('nso007_supplier', 'nso007_supplier.id', '=', 'nso007_account_payable.supplier_id')
            ->leftjoin('nso007_project_stock', 'nso007_journal.pay_for', '=', 'nso007_project_stock.id')

            ->where('nso007_journal.branch_id' , '=' , \Branch::branch_id())
            ->where('nso007_journal.journal_acccode' , 'LIKE' , '5%')
            ->whereNotIn('nso007_journal.journal_type', [13])
            ->where(function($query) use ($search){
                $query->orWhere('nso007_project_stock.project_name' , 'LIKE' , '%'.$search.'%');
                $query->orWhere('nso007_journal.journal_referenceid' , 'LIKE' , '%'.$search.'%');
                $query->orWhere('nso007_journal.pay_to' , 'LIKE' , '%'.$search.'%');
                $query->orWhere('nso007_journal.journal_invoice' , 'LIKE' , '%'.$search.'%');
                $query->orWhere('nso007_journal.journal_des' , 'LIKE' , '%'.$search.'%');
            })

            ->count();

        $expense = DB::table('nso007_journal')
            ->leftjoin('users', 'users.id', '=', 'nso007_journal.journal_by')
            ->leftjoin('nso007_account_chart', 'nso007_journal.journal_acccode', '=', 'nso007_account_chart.id')
            ->leftjoin('nso007_account_payable', 'nso007_journal.journal_referenceid', '=', 'nso007_account_payable.id')
            ->leftjoin('nso007_supplier', 'nso007_supplier.id', '=', 'nso007_account_payable.supplier_id')
            ->leftjoin('nso007_project_stock', 'nso007_journal.pay_for', '=', 'nso007_project_stock.id')
            ->select(
                'nso007_journal.id',
                'nso007_project_stock.project_name AS expense_for',
                'nso007_journal.journal_paydate AS do_date',
                'nso007_journal.journal_referenceid AS expense_to',
                'nso007_journal.pay_to AS pay_to_bank',
                'nso007_journal.journal_invoice AS expense_invoice',
                'nso007_journal.journal_des AS expense_des',
                'nso007_supplier.supplier_name',
                'nso007_journal.journal_debit AS expense_amount',
                DB::raw("CONCAT(users.user_firstname,' ', users.user_lastname) as user_by"),
                'nso007_account_chart.acc_name AS expense_title',
                'nso007_account_chart.id AS expense_code'
            )
            ->where('nso007_journal.branch_id' , '=' , \Branch::branch_id())
            ->where('nso007_journal.journal_acccode' , 'LIKE' , '5%')
            ->whereNotIn('nso007_journal.journal_type', [13])
            ->orderBy('id','asc')
            ->take(20);

        return  Datatables::of($expense)
        ->addIndexColumn()
        ->with([
            "recordsTotal" => $count_total,
            "recordsFiltered" => $count_filter,
        ])
        ->addColumn('action', function($expense) {
           return '<a href="'.route("accouting-expense.edit",$expense->id).'" title="Edit"><span class="button_success fa fa-edit"></span></a>';
        })
        ->addColumn('expense_amount',function($expense){
            return '$'.number_format($expense->expense_amount,2);
        })
        ->addColumn('do_date',function($expense){
            return date('Y-m-d',strtotime($expense->do_date));
        })
        ->make(true);
    }

    public function create()
    {
        $cash_flow = AccountChartModel::select('id', 'acc_name')
        ->whereIn('id', [10000, 10001])
        ->pluck('acc_name', 'id')
        ->prepend('Please Select', '');

        $bank_data = BankModel::select('id', 'name', 'cash_flow_type')
        ->where('cash_flow_type', 10001)->where('status', 1)
        ->orderBy('id', 'DESC')
        ->pluck('name', 'id')
        ->prepend('Please Select', '');
        $account_chats = AccountChartModel::select(
                        DB::raw('CONCAT(id," ",acc_name) AS account_mixed'),'id')
                        ->where('id','LIKE','5%')
                        ->pluck('account_mixed','id')
                        ->prepend('Please Select', '');
        $projects = ProjectModel::when(empty(\Branch::branch_key()), function($query){
            $query->where('branch_id',\Branch::branch_id());
        })->pluck('project_name','id')->prepend('Please Select Option', '')->toArray();

        return view('administrator.accounting.expense.create',compact('account_chats','projects', 'cash_flow', 'bank_data'));
    }

    public function edit($id)
    {
        $cash_flow = AccountChartModel::select('id', 'acc_name')
        ->whereIn('id', [10000, 10001])
        ->pluck('acc_name', 'id')
        ->prepend('Please Select', '');

        $bank_data = BankModel::select('id', 'name', 'cash_flow_type')
        ->where('cash_flow_type', 10001)->where('status', 1)
        ->orderBy('id', 'DESC')
        ->pluck('name', 'id')
        ->prepend('Please Select', '');

        $expense = JournalModel::find($id);
        $account_chats = AccountChartModel::select(
                        DB::raw('CONCAT(id," ",acc_name) AS account_mixed'),'id')
                        ->where('id','LIKE','5%')
                        ->pluck('account_mixed','id')
                        ->prepend('Please Select', '');
        $projects = ProjectModel::select(
                    DB::raw('CONCAT(project_code," / ",project_name) AS project_mixed'),'id')
                    ->pluck('project_mixed','id')
                    ->prepend('Please Select', '');

        return view('administrator.accounting.expense.edit', compact('expense','account_chats','projects', 'cash_flow', 'bank_data'));
    }

    public function store(Request $request)
    {
        $this->validate($request, [
            'cash_flow_bank'    => 'required',
            'journal_acccode'   => 'required',
            'amount'            => 'required|numeric',
            'journal_paydate'   => 'required',
        ]);
        if ($request->cash_flow_bank == 10001) { 
            $this->validate($request, [
                'bank_id'       => 'required',
            ]);
        }

        $user_by = auth::user()->id;

        $journal = new JournalModel;
        $journal1 = new JournalModel;

    
        $journal->journal_parentid   = 0;
        $journal->journal_acccode    = $request->journal_acccode;
        $journal->journal_type       = 5;
        $journal->journal_debit      = $request->amount;
        $journal->pay_for            = $request->pay_for;
        $journal->journal_referenceid= $request->pay_to;
        $journal->pay_to             = $request->bank_id;
        $journal->journal_invoice    = $request->journal_invoice;

        $journal->journal_transactiondate    = date('Y-m-d H:i:s A');
        $journal->journal_paydate    = date('Y-m-d',strtotime($request->journal_paydate));

        $journal->journal_des        = $request->journal_des;
        $journal->journal_by         = $user_by;
        $journal->cash_flow_bank     = $request->cash_flow_bank;
        $journal->journal_currency   = 1;
        $journal->branch_id          = \Branch::branch_id();
        $journal->save();

        $max_journal_id = DB::table('nso007_journal')->max('id');

        $journal1->journal_parentid   = $max_journal_id;
        $journal1->journal_acccode    = $request->cash_flow_bank;
        $journal1->journal_type       = 5;
        $journal1->journal_credit     = $request->amount;
        $journal1->pay_for            = $request->pay_for;
        $journal1->journal_referenceid= $request->pay_to;
        $journal1->pay_to             = $request->bank_id;
        $journal1->journal_invoice    = $request->journal_invoice;

        $journal1->journal_transactiondate    = date('Y-m-d H:i:s A');
        $journal1->journal_paydate    = date('Y-m-d',strtotime($request->journal_paydate));
        $journal1->journal_des        = $request->journal_des;
        $journal1->journal_by         = $user_by;
        $journal->cash_flow_bank     = $request->cash_flow_bank;
        $journal1->journal_currency   = 1;
        $journal1->branch_id          = \Branch::branch_id();
        $journal1->save();

        \LogActivity::addToLog('Income createted successfully!');
        return redirect()->route('accouting-expense.index');
    }

    public function update(Request $request, $id)
    { 
        $this->validate($request, [
            'journal_acccode'       => 'required',
            'cash_flow_bank'        => 'required',
            'journal_debit'         => 'required|numeric',
            'journal_paydate'       => 'required',
        ]);
        if ($request->cash_flow_bank == 10001) { 
            $this->validate($request, [
                'pay_to'       => 'required',
            ]);
        }
        $user_by = Auth::user()->id;
        $journal = JournalModel::find($id);

        $journal->journal_acccode    = $request->journal_acccode;
        $journal->journal_debit      = $request->journal_debit;
        $journal->journal_invoice    = $request->journal_invoice;

        $journal->journal_transactiondate    = date('Y-m-d H:i:s A');
        $journal->journal_paydate    = $request->journal_paydate;
        $journal->journal_des        = $request->journal_des;
        $journal->pay_for            = $request->pay_for;
        $journal->pay_to             = $request->pay_to;
        $journal->journal_by         = $user_by;
        $journal->cash_flow_bank     = $request->cash_flow_bank;
        $journal->journal_referenceid= $request->journal_referenceid;
        $journal->update();


        JournalModel::where('journal_parentid', $id)
        ->update([
            'journal_acccode'           => $request->cash_flow_bank,
            'journal_credit'            => $request->journal_debit,
            'journal_invoice'           => $request->journal_invoice,
            'journal_transactiondate'   => date('Y-m-d H:i:s A'),
            'journal_paydate'           => date('Y-m-d', strtotime($request->journal_paydate)),
            'journal_des'               => $request->journal_des,
            'pay_for'                   => $request->pay_for,
            'pay_to'                    => $request->pay_to,
            'cash_flow_bank'            => $request->cash_flow_bank,
            'journal_referenceid'       => $request->journal_referenceid,
        ]);

        DB::table('nso007_journal')
            ->where('journal_parentid',$id)
            ->update([
                'journal_debit' => $request->journal_credit,
                'journal_invoice' => $request->journal_invoice,
                'journal_transactiondate' => date('Y-m-d H:i:s A'),
                'journal_paydate' => $request->journal_paydate,
                'journal_des' => $request->journal_des,
            ]);
            
        \LogActivity::addToLog('Capital Updated successfully!');
        return redirect()->route('accouting-expense.index');
    }




    public function report()
    {
        $account_chats = AccountChartModel::select(
                        DB::raw('CONCAT(id," ",acc_name) AS account_mixed'),'id')
                        ->where('id','LIKE','5%')
                        ->pluck('account_mixed','id')
                        ->prepend('Please Select', '');
        $projects = ProjectModel::select(
                    DB::raw('CONCAT(project_code," / ",project_name) AS project_mixed'),'id')
                    ->pluck('project_mixed','id')
                    ->prepend('Please Select', '');
        $supplier = SupplierModel::select(
            DB::raw('CONCAT(id, " ", supplier_code, " ", supplier_name) AS supplier_mixed'),'id')
            ->pluck('supplier_mixed','id')
            ->prepend('Please Select', '');

        return view('administrator.accounting.expense.report',compact('account_chats','projects','supplier'));
    }

    public function get_report_expense_list(Request $request)
    {

        $search = $request->input('search.value');
        $count_total = DB::table('nso007_journal')
                    ->where('nso007_journal.branch_id',\Branch::branch_id())
                    ->where('nso007_journal.journal_acccode','LIKE','5%')
                    ->whereNotIn('nso007_journal.journal_type',[13])
                    ->count();
        $count_filter = DB::table('nso007_journal')
                    ->join('users','users.id','nso007_journal.journal_by')
                    ->join('nso007_account_chart','nso007_account_chart.id','nso007_journal.journal_acccode')
                    ->join('nso007_project_stock','nso007_project_stock.id','nso007_journal.pay_for')
                    ->leftjoin('nso007_account_payable', 'nso007_journal.journal_referenceid', '=', 'nso007_account_payable.id')
                    ->leftjoin('nso007_supplier', 'nso007_supplier.id', '=', 'nso007_account_payable.supplier_id')
                    ->where('nso007_journal.branch_id',\Branch::branch_id())
                    ->where('nso007_journal.journal_acccode','LIKE','5%')
                    ->whereNotIn('nso007_journal.journal_type',[13])
                    ->where(function($query) use ($search){
                        $query->orWhere('nso007_project_stock.project_name','LIKE','%'.$search.'%');
                        $query->orWhere('nso007_journal.journal_paydate','LIKE','%'.$search.'%');
                        $query->orWhere('nso007_journal.pay_to','LIKE','%'.$search.'%');
                        $query->orWhere('nso007_journal.journal_referenceid','LIKE','%'.$search.'%');
                        $query->orWhere('nso007_journal.journal_invoice','LIKE','%'.$search.'%');
                        $query->orWhere('nso007_journal.journal_debit','LIKE','%'.$search.'%');
                        $query->orWhere('nso007_account_chart.acc_name','LIKE','%'.$search.'%');
                        $query->orWhere('nso007_account_chart.id','LIKE','%'.$search.'%');
                        $query->orWhere('nso007_journal.journal_des','LIKE','%'.$search.'%');
                    })
                    ->count();

        $expense = DB::table('nso007_journal')
                    ->join('users','users.id','nso007_journal.journal_by')
                    ->join('nso007_account_chart','nso007_account_chart.id','nso007_journal.journal_acccode')
                    ->join('nso007_project_stock','nso007_project_stock.id','nso007_journal.pay_for')
                    ->leftjoin('nso007_account_payable', 'nso007_journal.journal_referenceid', '=', 'nso007_account_payable.id')
                    ->leftjoin('nso007_supplier', 'nso007_supplier.id', '=', 'nso007_account_payable.supplier_id')
                    ->select(
                        'nso007_journal.id',
                        'nso007_project_stock.project_name AS expense_for',
                        'nso007_journal.journal_paydate AS do_date',
                        'nso007_journal.journal_referenceid AS reference_by',
                        'nso007_journal.journal_invoice AS expense_invoice',
                        'nso007_journal.journal_debit AS expense_amount',
                        'nso007_account_chart.acc_name AS expense_title',
                        'nso007_supplier.supplier_name',
                        'nso007_account_chart.id AS expense_code',
                        DB::raw("IFNULL(nso007_journal.journal_des,'N/A') as expense_des"),
                        DB::raw("CONCAT(users.user_firstname,' ',users.user_lastname) AS user_by")
                    )
                    ->where('nso007_journal.branch_id',\Branch::branch_id())
                    ->where('nso007_journal.journal_acccode','LIKE','5%')
                    ->whereNotIn('nso007_journal.journal_type',[13])
                    ->take(20);

        if($request->isMethod('GET'))
        {
            return  Datatables::of($expense)
                ->addIndexColumn()
                ->with([
                    "recordsTotal" => $count_total,
                     "recordsFiltered" => $count_filter,
                ])
                ->addColumn('expense_amount',function($expense){
                    return '$'.number_format($expense->expense_amount,2);
                })
                ->addColumn('do_date',function($expense){
                    return date('Y-m-d',strtotime($expense->do_date));
                })
                ->make(true);
        }else{
            $start_date =  (empty($request->start_date))? '': date('Y-m-d',strtotime($request->start_date));
            $end_date   =  (empty($request->end_date))? '': date('Y-m-d',strtotime($request->end_date));

            $expenses = DB::table('nso007_journal')
                    ->join('users','users.id','nso007_journal.journal_by')
                    ->join('nso007_account_chart','nso007_account_chart.id','nso007_journal.journal_acccode')
                    ->join('nso007_project_stock','nso007_project_stock.id','nso007_journal.pay_for')
                    ->leftjoin('nso007_account_payable', 'nso007_journal.journal_referenceid', '=', 'nso007_account_payable.id')
                    ->leftjoin('nso007_supplier', 'nso007_supplier.id', '=', 'nso007_account_payable.supplier_id')
                    ->select(
                        'nso007_journal.id',
                        'nso007_project_stock.project_name AS expense_for',
                        'nso007_journal.journal_paydate AS do_date',
                        'nso007_journal.journal_referenceid AS reference_by',
                        'nso007_journal.journal_invoice AS expense_invoice',
                        'nso007_journal.journal_debit AS expense_amount',
                        'nso007_account_chart.acc_name AS expense_title',
                        'nso007_supplier.supplier_name',
                        'nso007_account_chart.id AS expense_code',
                        DB::raw("IFNULL(nso007_journal.journal_des,'N/A') as expense_des"),
                        DB::raw("CONCAT(users.user_firstname,' ',users.user_lastname) AS user_by")
                    )
                    ->where('nso007_journal.branch_id',\Branch::branch_id())
                    ->where('nso007_journal.journal_acccode','LIKE','5%')
                    ->whereNotIn('nso007_journal.journal_type',[13])
                    ->where('nso007_journal.journal_acccode','LIKE',isset($request->journal_acccode)? $request->journal_acccode:NULL)
                    ->where('nso007_journal.pay_for','LIKE',isset($request->pay_for)? $request->pay_for:NULL)
                    ->where('nso007_supplier.id','LIKE',isset($request->supp_id)? $request->supp_id:NULL)
                    ->when(request('start_date', ''), function ($query) use ($start_date, $end_date){
                        $query->whereBetween('nso007_journal.journal_paydate', [$start_date, $end_date]);
                    })
                    ->get();
            echo json_encode($expenses);
        }
    }

    public function report_print(Request $request)
    {
        $start_date =  (empty($request->start_date))? '': date('Y-m-d',strtotime($request->start_date));
        $end_date   =  (empty($request->end_date))? '': date('Y-m-d',strtotime($request->end_date));

        $mdoel_searching = new SearchModel();
        $journal_date =  (empty($start_date))? '':' nso007_journal.journal_paydate BETWEEN "'.$start_date.'" AND "'.$end_date.'" ';
        $journal_acccode =  (empty($request->journal_acccode))? '':' nso007_journal.journal_acccode = "'.$request->journal_acccode.'" ';
        $pay_for =  (empty($request->pay_for))? '':' nso007_journal.pay_for = '.$request->pay_for;
        $branch_id ='nso007_journal.branch_id = '.\Branch::branch_id();

        $data_search = array(
            'journal_date' =>$journal_date,
            'journal_acccode' =>$journal_acccode,
            'pay_for' =>$pay_for,
            'branch_id' =>$branch_id,
        );
        $search= $mdoel_searching->search($data_search);
        $search = (empty($search))? " WHERE journal_acccode like '5%' AND journal_type NOT IN (13)":" " .$search. "  AND journal_acccode like '5%' AND journal_type NOT IN (13)";
        $expense = DB::select("
            SELECT nso007_journal.id,
            nso007_project_stock.project_name AS expense_for,
            nso007_journal.journal_paydate AS do_date,
            nso007_journal.pay_to AS expense_to,
            nso007_journal.journal_referenceid AS reference_by,
            nso007_journal.journal_invoice AS expense_invoice,
            IFNULL(nso007_journal.journal_des,'N/A') as expense_des,
            nso007_journal.journal_debit AS expense_amount,
            CONCAT(users.user_firstname,' ',users.user_lastname) AS user_by,
            nso007_account_chart.acc_name AS expense_title,
            nso007_supplier.supplier_name,
            nso007_account_chart.id AS expense_code
            FROM nso007_journal 
            INNER JOIN users ON nso007_journal.journal_by = users.id
            INNER JOIN nso007_account_chart ON nso007_journal.journal_acccode = nso007_account_chart.id
            INNER JOIN nso007_project_stock ON nso007_journal.pay_for = nso007_project_stock.id
            INNER JOIN nso007_account_payable ON nso007_account_payable.id = nso007_journal.journal_referenceid
            INNER JOIN nso007_supplier ON nso007_supplier.id = nso007_account_payable.supplier_id
            $search
            ORDER BY nso007_journal.id DESC
        ");

        $total = 0;
        foreach ($expense as $key => $value) {
            $total =+ $value->expense_amount;
        }
        $branch = DB::table('nso007_branches')->where('id',\Branch::branch_id())->first();
        return view('administrator.accounting.expense.report-view-print',compact('expense','total','branch'));
    }
}