<?php

namespace App\Http\Controllers\Administrator;

use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use App\ProjectModel;
use App\SupplierModel;
use App\AccountPayableModel;
use App\JournalModel;
use App\AccountChartModel;
use App\BankModel;
use App\SearchModel;
use DB;
use Auth;
use DataTables;
use Illuminate\Support\Facades\Crypt;
use App\PayableExpenseModel;
use Illuminate\Support\Facades\Input;
class AccountPayableController extends Controller
{
    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function index()
    {
        $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', '');
        return view('administrator.accounting.payable.index', compact('cash_flow', 'bank_data'));
    }

    public function datatable_get_payable(Request $request)
    {
        $search = $request->input('search.value');
        $count_total = DB::table('nso007_account_payable')
        ->where('nso007_account_payable.branch_id' , \Branch::branch_id())
        ->count();
        
        $count_filter = DB::table('nso007_account_payable')
        ->leftjoin('nso007_project_stock', 'nso007_account_payable.payable_for', '=', 'nso007_project_stock.id')
        ->leftjoin('nso007_supplier', 'nso007_supplier.id', '=', 'nso007_account_payable.supplier_id')
        ->leftjoin('nso007_account_chart', 'nso007_account_payable.payable_from', '=', 'nso007_account_chart.id')  

        ->where('nso007_account_payable.branch_id', \Branch::branch_id())
        ->where(function($query) use ($search){
            $query->orWhere('nso007_account_payable.payable_des' , 'LIKE' , '%'.$search.'%');
            $query->orWhere('nso007_project_stock.project_name' , 'LIKE' , '%'.$search.'%');
        })
        ->count();

        $payable = DB::table('nso007_account_payable')
        ->leftjoin('nso007_project_stock', 'nso007_account_payable.payable_for', '=', 'nso007_project_stock.id')
        ->leftjoin('nso007_supplier', 'nso007_supplier.id', '=', 'nso007_account_payable.supplier_id')
        ->leftjoin('nso007_account_chart', 'nso007_account_payable.payable_from', '=', 'nso007_account_chart.id')
        ->select(
            'nso007_account_payable.id',
            'nso007_account_payable.payable_from',
            'nso007_account_payable.payable_for',
            'nso007_account_payable.payable_date', 
            'nso007_account_payable.payable_des', 
            'nso007_account_payable.payable_invoice', 
            'nso007_project_stock.project_name', 
            'nso007_supplier.supplier_name',
            'nso007_account_payable.payable_amount',
            'nso007_account_payable.payable_refund',
            'nso007_account_payable.status',
            'nso007_account_chart.acc_name'
        )
        ->where('nso007_account_payable.branch_id' , '=' , \Branch::branch_id())
        ->orderBy('id','asc')
        ->take(20);


        return  Datatables::of($payable)

        ->addIndexColumn()
        ->with([
                "recordsTotal" => $count_total,
                 "recordsFiltered" => $count_filter,
              ])
        ->setRowId('id')
        ->editColumn('payable_date',function($payable){
                return date('Y-m-d', strtotime($payable->payable_date));
        })
        ->editColumn('payable_owe',function($payable){
                return '$ '.number_format(($payable->payable_amount - $payable->payable_refund), 2);
        })
        ->editColumn('payable_refund',function($payable){
                return '$ '.number_format($payable->payable_refund, 2);
        })
        ->editColumn('payable_amount',function($payable){
                return '$ '.number_format($payable->payable_amount, 2);
        })
        ->editColumn('action', function ($payable) {
            if (!empty($payable->status)) {
                return '
                <a style="color:white;margin-left:0;" role="button" data-project="'.$payable->payable_for.'" data-chartid="'.$payable->payable_from.'" data-toggle="modal" onclick="payable_refund(this)" href="#payable_refund" class="button_primary btn-circle btn-sm" title="Payable Expense" id="payable-expense"><i class="glyphicon glyphicon-usd"></i></a>
                ';
            } else {
                return '
                <a style="color:white;margin-left:0;" class="button_success btn-circle btn-sm" title="Completed"><i class="glyphicon glyphicon-ok"></i></a>
                
                ';
            }
            
        })
        ->make(true);
    }

    /**
     * Show the form for creating a new resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function create()
    {
        $account_chart = AccountChartModel::select(
                        DB::raw('CONCAT(id," ",acc_name) AS account_chart_mixed'),'id')
                        ->where('id','LIKE','5%')
                        ->pluck('account_chart_mixed','id')
                        ->prepend('Please Select', '');
        $project = ProjectModel::when(empty(\Branch::branch_key()), function($query){
            $query->where('branch_id',\Branch::branch_id());
        })->pluck('project_name','id')->prepend('Please Select Option', '')->toArray();
         $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.payable.create',compact('project', 'supplier', 'account_chart'));
    }

    /**
     * Store a newly created resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @return \Illuminate\Http\Response
     */
    public function store(Request $request)
    {
        // dd($request->all());

        $payable = New AccountPayableModel;
        $this->validate($request, [
            'project_id'        => 'required',
            'payable_from'      => 'required',
            'supplier_name'      => 'required',
            'payable_amount'    => 'required|numeric',
            'pay_date'          => 'required',
        ]);

        $payable->payable_des       = $request->payable_des;
        $payable->payable_amount    = $request->payable_amount;
        $payable->payable_date      = date('Y-m-d',strtotime($request->pay_date));
        $payable->payable_by        = Auth::user()->id;
        $payable->payable_invoice   = $request->payable_invoice;
        $payable->payable_for       = $request->project_id;
        $payable->payable_from      = $request->payable_from;
        $payable->supplier_id      = $request->supplier_name;
        $payable->branch_id      = \Branch::branch_id();
        $payable->save();

        \LogActivity::addToLog('Account payable created successfully!');
        return redirect()->route('accounting-payable.index')
                        ->with('success','Account payable created successfully!');
    }

    
    public function payable($id)
    {
        $payable = New AccountPayableModel;
        $payable->journal_parentid  = 0;
        $payable->journal_type      = 20;
        $payable->journal_acccode   = 20002;
        $payable->journal_des       = $request->payable_des;
        $payable->journal_debit     = $request->payable_amount;
        $payable->journal_paydate   = date('Y-m-d',strtotime($request->pay_date));
        $payable->journal_by        = Auth::user()->id;
        $payable->journal_invoice   = $request->payable_invoice;
        $payable->pay_for           = $request->project_id;
        $payable->pay_to            = $request->payable_from;
        $payable->save();

        $payable_id = $payable->id;
        $data = array(
            'journal_parentid' => $payable_id,
            'journal_type'     => 20,
            'journal_acccode'  => 0,
            'journal_des'      => $request->payable_des,
            'journal_credit'   => $request->payable_amount,
            'journal_invoice'  => $request->payable_invoice,
            'journal_paydate'  => date('Y-m-d',strtotime($request->pay_date)),
            'pay_for'          => $request->project_id,
            'pay_to'           => $request->payable_from,
            'journal_by'       => Auth::user()->id,
        );
        JournalModel::insert($data);

        \LogActivity::addToLog('Account payable created successfully!');
        return redirect()->route('accounting-payable.index')
                        ->with('success','Account payable created successfully!');
    }

    /**
     *  Ajax get payable record
     */
    public function ajax_get_payable($id)
    {
        $data = AccountPayableModel::find($id);
        echo json_encode($data);
    }
    /**
     *  Save account payable
     */ 
    public function refund(Request $request)
    {
        $id  = $request->pid;
        $owe = $request->owe_pay;
        $pay = $request->owe_amount;
        $remark = $request->remark;
        $project_id = $request->project_id;
        $account_chart = $request->account_chart;
        $cash_flow     = $request->cash_flow;
        $bank_list     = $request->bank_list;
        $do_date = date('Y-m-d');

        $data = AccountPayableModel::find($id);

        if ($pay == $owe) {
            $count_payable_expense_step = PayableExpenseModel::where('payable_id', $id)->count();
            AccountPayableModel::where('id', $id)->increment('payable_refund', $pay);
            $payable = New JournalModel;
            $payable->journal_parentid  = 0;
            $payable->journal_type      = 5;
            $payable->journal_acccode   = $account_chart;
            $payable->journal_des       = $remark;
            $payable->journal_debit     = $pay;
            $payable->journal_referenceid = $id;
            $payable->journal_paydate   = $do_date;
            $payable->journal_by        = Auth::user()->id;
            $payable->journal_invoice   = $data->payable_invoice;
            $payable->pay_for           = $data->payable_for;
            $payable->cash_flow_bank    = $cash_flow;
            $payable->pay_to            = $bank_list;
            $payable->branch_id         = \Branch::branch_id();
            $payable->save();

            $payable_id = $payable->id;
            $branch_id  = \Branch::branch_id();
            $data = array(
                'journal_parentid' => $payable_id,
                'journal_type'     => 5,
                'journal_acccode'  => $cash_flow,
                'journal_des'      => $remark,
                'journal_credit'   => $pay,
                'journal_invoice'  => $data->payable_invoice,
                'journal_referenceid'  => $id,
                'journal_paydate'  => $do_date,
                'pay_for'          => $data->payable_for,
                'pay_to'           => $data->payable_from,
                'journal_by'       => Auth::user()->id,
                'branch_id'       => $branch_id,
            );
            $last_id = JournalModel::insertGetId($data);
            $payable_expense = array(
                'payable_id'    =>  $id,
                'journal_id'    =>  $last_id,
                'payable_des'   =>  $request->remark,
                'payable_step'  =>  $count_payable_expense_step + 1,
            );
            PayableExpenseModel::insert($payable_expense);

            AccountPayableModel::where('id', $id)->update([
                'status' => 0
            ]);

            $data['message'] = "success";
            echo json_encode($data);
        } elseif ($pay < $owe) {
            $count_payable_expense_step = PayableExpenseModel::where('payable_id', $id)->count();
            AccountPayableModel::where('id', $id)->increment('payable_refund', $pay);
            $payable = New JournalModel;
            $payable->journal_parentid  = 0;
            $payable->journal_type      = 5;
            $payable->journal_acccode   = $account_chart;
            $payable->journal_des       = $remark;
            $payable->journal_debit     = $pay;
            $payable->journal_referenceid = $id;
            $payable->journal_paydate   = $do_date;
            $payable->journal_by        = Auth::user()->id;
            $payable->journal_invoice   = $data->payable_invoice;
            $payable->pay_for           = $data->payable_for;
            $payable->cash_flow_bank    = $cash_flow;
            $payable->pay_to            = $bank_list;
            $payable->branch_id         = \Branch::branch_id();
            $payable->save();

            $payable_id = $payable->id;
            $data = array(
                'journal_parentid' => $payable_id,
                'journal_type'     => 5,
                'journal_acccode'  => $cash_flow,
                'journal_des'      => $remark,
                'journal_credit'   => $pay,
                'journal_invoice'  => $data->payable_invoice,
                'journal_paydate'  => $do_date,
                'journal_referenceid'  => $id,
                'pay_for'          => $data->payable_for,
                'pay_to'           => $data->payable_from,
                'journal_by'       => Auth::user()->id,
                'branch_id'       => \Branch::branch_id(),
            );
            $last_id = JournalModel::insertGetId($data);
            $payable_expense = array(
                'payable_id'    =>  $id,
                'journal_id'    =>  $last_id,
                'payable_des'   =>  $request->remark,
                'payable_step'  =>  $count_payable_expense_step + 1,
            );
            PayableExpenseModel::insert($payable_expense);

            AccountPayableModel::where('id', $id)->update([
                'status' => 2
            ]);
            $data['message'] = "success";
            echo json_encode($data);
        }
    }
    // report account payable
    public function report()
    {
        $account_chart = AccountChartModel::select(
                        DB::raw('CONCAT(id," ",acc_name) AS account_chart_mixed'),'id')
                        ->where('id','LIKE','5%')
                        ->pluck('account_chart_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.payable.report',compact('account_chart','supplier'));
    }
    // get report account payable
    public function get_report(Request $request)
    {
        $search_datatable = $request->input('search.value');
        $count_total = AccountPayableModel::where('branch_id',\Branch::branch_id())->count();

        $count_filter = DB::table('nso007_account_payable')
                    ->leftJoin('nso007_project_stock', 'nso007_account_payable.payable_for', '=', 'nso007_project_stock.id')
                    ->leftjoin('nso007_supplier', 'nso007_supplier.id', '=', 'nso007_account_payable.supplier_id')
                    ->leftJoin('nso007_account_chart', 'nso007_account_payable.payable_from', '=', 'nso007_account_chart.id')
                    ->where('nso007_account_payable.branch_id',\Branch::branch_id())
                    ->where('nso007_account_payable.payable_invoice' , 'LIKE' , '%'.$search_datatable.'%')
                    ->orWhere('nso007_account_payable.payable_date' , 'LIKE' , '%'.$search_datatable.'%')
                    ->orWhere('nso007_project_stock.project_name' , 'LIKE' , '%'.$search_datatable.'%')
                    ->orWhere('nso007_supplier.supplier_name' , 'LIKE' , '%'.$search_datatable.'%')
                    ->orWhere('nso007_account_chart.acc_name' , 'LIKE' , '%'.$search_datatable.'%')
                    ->orWhere('nso007_account_payable.payable_des' , 'LIKE' , '%'.$search_datatable.'%')
                    ->count();
        
        $payables =  DB::table('nso007_account_payable')
                    ->leftJoin('nso007_project_stock', 'nso007_account_payable.payable_for', '=', 'nso007_project_stock.id')
                    ->leftjoin('nso007_supplier', 'nso007_supplier.id', '=', 'nso007_account_payable.supplier_id')
                    ->leftJoin('nso007_account_chart', 'nso007_account_payable.payable_from', '=', 'nso007_account_chart.id')
                    ->where('nso007_account_payable.branch_id',\Branch::branch_id())
                    ->select(
                        'nso007_account_payable.payable_date',
                        'nso007_account_payable.payable_des',
                        'nso007_account_payable.payable_invoice',
                        'nso007_project_stock.project_name',
                        'nso007_supplier.supplier_name',
                        'nso007_account_chart.acc_name',
                        'nso007_account_payable.payable_amount',
                        'nso007_account_payable.payable_refund'
                    )
                    ->take(20);
        
        if($request->isMethod('get')){
            return  Datatables::of($payables)
            ->addIndexColumn()
            ->with([
                "recordsTotal" => $count_total,
                 "recordsFiltered" => $count_filter,
            ])
            ->editColumn('payable_date',function($payables){
                    return date('Y-m-d', strtotime($payables->payable_date));
            })
            ->editColumn('payable_owe',function($payables){
                    return '$ '.number_format(($payables->payable_amount - $payables->payable_refund), 2);
            })
            ->editColumn('payable_refund',function($payables){
                    return '$ '.number_format($payables->payable_refund, 2);
            })
            ->editColumn('payable_amount',function($payables){
                    return '$ '.number_format($payables->payable_amount, 2);
            })
            ->make(true);
        }else{

            $from =  (empty($request->start_date))? '': date('Y-m-d',strtotime($request->start_date));
            $to =  (empty($request->end_date))? '': date('Y-m-d',strtotime($request->end_date));
            $payable =  DB::table('nso007_account_payable')
                ->leftJoin('nso007_project_stock', 'nso007_account_payable.payable_for', '=', 'nso007_project_stock.id')
                ->leftjoin('nso007_supplier', 'nso007_supplier.id', '=', 'nso007_account_payable.supplier_id')
                ->leftJoin('nso007_account_chart', 'nso007_account_payable.payable_from', '=', 'nso007_account_chart.id')
                ->select(
                    'nso007_account_payable.payable_date',
                    'nso007_account_payable.payable_des',
                    'nso007_account_payable.payable_invoice',
                    'nso007_project_stock.project_name',
                    'nso007_supplier.supplier_name',
                    'nso007_account_chart.acc_name',
                    'nso007_account_payable.payable_amount',
                    'nso007_account_payable.payable_refund'
                )
                ->where('nso007_account_payable.branch_id',\Branch::branch_id())
                ->where('nso007_supplier.id','LIKE',isset($request->supplier_name)? $request->supplier_name:NULL)
                ->where('nso007_account_chart.id','LIKE',isset($request->account_code)? $request->account_code:NULL)
                ->when(request('start_date', ''), function ($query) use ($from, $to){
                    $query->whereBetween('nso007_account_payable.payable_date', [$from, $to]);
                })
                ->get();  
            
            echo json_encode($payable);
        }
    }

    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_account_payable.payable_date BETWEEN "'.$start_date.'" AND "'.$end_date.'" ';
        $account_code =  (empty($request->account_code))? '':' nso007_account_chart.id = "'.$request->account_code.'" ';
        $supplier_name =  (empty($request->supplier_name))? '':' nso007_supplier.id = "'.$request->supplier_name.'" ';

        $data_search = array(
            'journal_date' =>$journal_date,
            'account_code' =>$account_code,
            'supplier_name' =>$supplier_name,
        );

        $search= $mdoel_searching->search($data_search);
        $search = (empty($search))? " WHERE nso007_account_payable.branch_id = ".\Branch::branch_id() : $search ." AND nso007_account_payable.branch_id = ".\Branch::branch_id();
        $payable = DB::select("
                SELECT 
                    nso007_account_payable.id,
                    nso007_account_payable.payable_date,
                    IFNULL(nso007_account_payable.payable_des, 'N/A') AS payable_des,
                    nso007_account_payable.payable_invoice,
                    nso007_account_payable.payable_amount,
                    nso007_account_payable.payable_refund,
                    nso007_account_payable.status,
                    nso007_account_chart.acc_name,
                    nso007_project_stock.project_name
                    FROM nso007_account_payable 
                    LEFT JOIN nso007_project_stock ON nso007_account_payable.payable_for = nso007_project_stock.id
                    LEFT JOIN nso007_supplier ON nso007_supplier.id = nso007_account_payable.supplier_id
                    LEFT JOIN nso007_account_chart ON nso007_account_payable.payable_from = nso007_account_chart.id
                    $search
            ");

        $payable_amount = 0;
        $payable_refund = 0;

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