<?php

namespace App\Http\Controllers;
use App\Models\BillsLo;
use App\Models\BillsPo;
use App\Models\Expense;
use App\Models\Rab;
use App\Models\Project;
use App\Models\Invoice;
use App\Models\CashRequest;
use App\Models\CashReport;
use App\Models\CashReportDetail;
use App\Models\PaymentMade;
use Illuminate\Http\Request;
use App\Http\Controllers\ProgressControlller;
use App\Http\Controllers\TimeLineController;
use App\Http\Controllers\RapController;
use App\Http\Controllers\CurvasController;

class CostControlController extends Controller
{
    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function index()
    {
        $rabs = Rab::where([['status', 'approve'], ['id_project', '<>', null]])->get();
        return view('staff.cost-control', compact('rabs'));
    }

    /**
     * Show the form for creating a new resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function create()
    {
        //
    }

    /**
     * Store a newly created resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @return \Illuminate\Http\Response
     */
    public function store(Request $request)
    {
        if ($request->act == 'rab-edit') {
            $rab                     = Rab::findOrFail($request->id);
            $rab->total_real_cashout = str_replace('.', '', $request->total_real_cashout);
            $rab->progress_lapangan  = str_replace('.', '', $request->progress_lapangan);
            $rab->save();

            return redirect()->route('cost_control.index')->with('msg', 'data berhasil dirubah');
        } else {
            abort('404');
        }
    }

    /**
     * Display the specified resource.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function show(Request $request, $id)
    {
        if (isset($_GET['act']) and \Request::ajax()) {
            if ($_GET['act'] == 'rabData') {
                $rab = Rab::findOrFail($id);
                return response()->json([
                    'rab' => $rab,
                ]);
            }elseif ($_GET['act'] == 'get_notif') {
                set_time_limit(0);
                $projects  = Project::all();
                $over_budgets = [];
                $deviasi_minus = [];
                foreach ($projects as $row_p) {
                    foreach ($row_p->progress as $row) {
                        $bobot_kerja_c = [];
                        $bobot_kerja_p = [];
                        $progress_lapangan = 0;
                        foreach ($row->progress_details as $row2) {
                            if ($row2->rab_detail->ahs) {
                                $progress_lapangan += round(TimeLineController::getProgressDetail($row->id,$row2->rab_detail->id,'bobot_kerja'),2);
                            }else{
                                $progress_lapangan += round((TimeLineController::getProgressDetail($row->id,$row2->rab_detail->id,'volume_progress') / $row2->rab_detail->volume * 100),2);
                            }
                        }
                        $pdp = CostControlController::pdp($row_p->rab->id);
                        $progress_lapangan = ($progress_lapangan/100) * $row_p->rab->rap?$row_p->rab->rap:RapController::cekTotalRap($row_p->rab->id);
                        if ($pdp > $progress_lapangan) {
                            $id_rab = array_column($over_budgets, 'id_rab');
                            $found_key = array_search($row_p->rab->id, $id_rab);
                            if ($found_key !== false) {

                            }else{
                                array_push($over_budgets, ['id_rab' => $row_p->rab->id,'nama_project' => $row_p->name,'progress_lapangan' => $progress_lapangan,'pdp' => $pdp]);
                            }
                        }
                    }
                }
                return response()->json(['over_budgets' => $over_budgets]);
            }
        } else {
            if ($request->act) {
                if ($request->act == 'material_by_po_detail') {
                    $payments_po = PaymentMade::whereHas('purchase_order.purchaseRequest.project.rab',function ($q) use ($id) {
                        $q->where('rab.id', $id);
                    })->get();

                    $bills = BillsPo::whereHas('purchaseOrder.purchaseRequest.project.rab', function ($q) use ($id) {
                        $q->where('rab.id', $id);
                    })->where('status', 'paid')
                    ->orWhereHas('project.rab', function ($q) use ($id) {
                        $q->where('rab.id', $id);
                    })->where('status', 'paid')->get();
                    
                    $cash_reports = CashReportDetail::whereHas('cash_report.cash_request.project.rab', function ($q) use ($id) {
                        $q->where('rab.id', $id);
                    })->where('account', 'PO')->get();

                    return view('staff.cost-control-detail_material_by_po', compact('bills','cash_reports','payments_po'));
                } elseif ($request->act == 'labour_by_lo_detail') {
                    $payments_lo = PaymentMade::whereHas('labour_order.project.rab',function ($q) use ($id) {
                        $q->where('rab.id', $id);
                    })->get();

                    $bills = BillsLo::whereHas('labourOrder.project.rab', function ($q) use ($id) {
                        $q->where('rab.id', $id);
                    })->where('status', 'paid')->get();

                    $cash_reports = CashReportDetail::whereHas('cash_report.cash_request.project.rab', function ($q) use ($id) {
                        $q->where('rab.id', $id);
                    })->where('account', 'LO')->get();

                    return view('staff.cost-control-detail_labour_by_lo_detail', compact('bills','cash_reports','payments_lo'));
                } elseif ($request->act == 'site_management_detail') {
                    $expenses = Expense::whereHas('project.rab', function ($q) use ($id) {
                        $q->where('rab.id', $id);
                    })->where('expense_account', 'Site Management')->get();

                    $cash_reports = CashReportDetail::whereHas('cash_report.cash_request.project.rab', function ($q) use ($id) {
                        $q->where('rab.id', $id);
                    })->where('account', 'Site Management')->get();

                    return view('staff.cost-control-detail_site_management', compact('expenses','cash_reports'));
                }elseif ($request->act == 'other_cost_detail') {
                    $expenses = Expense::whereHas('project.rab', function ($q) use ($id) {
                        $q->where('rab.id', $id);
                    })->where('expense_account', 'Other Cost')->get();

                    $cash_reports = CashReportDetail::whereHas('cash_report.cash_request.project.rab', function ($q) use ($id) {
                        $q->where('rab.id', $id);
                    })->where('account', 'Other Cost')->get();


                    return view('staff.cost-control-detail_other_cost', compact('expenses','cash_reports'));
                } elseif ($request->act == 'debt_detail') {
                    $bill_pos = BillsPo::whereHas('purchaseOrder.purchaseRequest.project.rab', function ($q) use ($id) {
                        $q->where('rab.id', $id);
                    })->where('status', 'open')
                    ->orWhereHas('project.rab', function ($q) use ($id) {
                        $q->where('rab.id', $id);
                    })
                    ->where('status', 'open')->get();

                    $bill_los = BillsLo::whereHas('labourOrder.project.rab', function ($q) use ($id) {
                        $q->where('rab.id', $id);
                    })->where('status', 'open')->get();
                    return view('staff.cost-control-detail-debt', compact('bill_pos', 'bill_los'));
                }
            } else {
                $rab = Rab::findOrFail($id);
                return view('staff.cost-control-rabdata', compact('rab'));
            }
        }
    }

    /**
     * Show the form for editing the specified resource.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function edit($id)
    {
        //
    }

    /**
     * Update the specified resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function update(Request $request, $id)
    {
        if($request->act == 'submit'){
	  $rab = Rab::findOrFail($id);
          $rab->status_cost_control = "submit";
          $rab->save();
          
          return redirect()->back()->with('msg', 'data berhasil disubmit');        
        }else{
	  $rab = Rab::findOrFail($id);
          $rab->rap = str_replace('.', '', $request->rap);
          $rab->save();
          
          return redirect()->back()->with('msg', 'data berhasil diperbarui');
        }
    }

    /**
     * Remove the specified resource from storage.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function destroy($id)
    {
        //
    }
    public static function material_bypo($id)
    {
        $total = BillsPo::selectRaw('sum(amount) as total')
            ->join('purchase_orders', 'bills_po.id_purchase_order', 'purchase_orders.id')
            ->join('purchase_request', 'purchase_orders.id_purchase_request', 'purchase_request.id')
            ->join('projects', 'purchase_request.id_project', 'projects.id')
            ->join('rab', 'projects.id', 'rab.id_project')
            ->where([
                ['rab.id', $id],
                ['bills_po.status', 'paid'],
            ])->first()->total;

        $total += BillsPo::selectRaw('sum(amount) as total')
            ->join('projects', 'bills_po.id_project', 'projects.id')
            ->join('rab', 'projects.id', 'rab.id_project')
            ->where([
                ['rab.id', $id],
                ['bills_po.status', 'paid'],
            ])->first()->total;
    
    $total += PaymentMade::selectRaw('sum(amount) as total')
            ->join('purchase_orders', 'payment_made.id_purchase_order', 'purchase_orders.id')
            ->join('purchase_request', 'purchase_orders.id_purchase_request', 'purchase_request.id')
            ->join('projects', 'purchase_request.id_project', 'projects.id')
            ->join('rab', 'projects.id', 'rab.id_project')
            ->where('rab.id', $id)->first()->total;

        $total += CashReportDetail::selectRaw('sum(kredit) as total')
        ->whereHas('cash_report.cash_request.project.rab',function($q) use ($id){
            $q->where('rab.id',$id);
        })->where('account','PO')->first()->total;

        return $total;
    }
    public static function labour_bylo($id)
    {
        $total = BillsLo::selectRaw('sum(amount) as total')
            ->join('labour_order', 'bills_lo.id_labour_order', 'labour_order.id')
            ->join('projects', 'labour_order.id_project', 'projects.id')
            ->join('rab', 'projects.id', 'rab.id_project')
            ->where([
                ['rab.id', $id],
                ['bills_lo.status', 'paid'],
            ])->first()->total;

        $total += CashReportDetail::selectRaw('sum(kredit) as total')
        ->whereHas('cash_report.cash_request.project.rab',function($q) use ($id){
            $q->where('rab.id',$id);
        })->where('account','LO')->first()->total;

         $total += PaymentMade::selectRaw('sum(amount) as total')
            ->join('labour_order', 'payment_made.id_labour_order', 'labour_order.id')
            ->join('projects', 'labour_order.id_project', 'projects.id')
            ->join('rab', 'projects.id', 'rab.id_project')
            ->where('rab.id', $id)->first()->total;


        return $total;
    }
    public static function site_management($id)
    {
        $total = Expense::selectRaw('sum(amount) as total')
            ->join('projects', 'expenses.id_project', 'projects.id')
            ->join('rab', 'projects.id', 'rab.id_project')
            ->where([
                ['rab.id', $id],
                ['expenses.expense_account', 'Site Management'],
            ])->first()->total;
        
        $total += CashReportDetail::selectRaw('sum(kredit) as total')
        ->whereHas('cash_report.cash_request.project.rab',function($q) use ($id){
            $q->where('rab.id',$id);
        })->where('account','Site Management')->first()->total;

        return $total;
    }
    public static function other_cost($id)
    {
        $total = Expense::selectRaw('sum(amount) as total')
            ->join('projects', 'expenses.id_project', 'projects.id')
            ->join('rab', 'projects.id', 'rab.id_project')
            ->where([
                ['rab.id', $id],
                ['expenses.expense_account', 'Other Cost'],
            ])->first()->total;

        $total += PaymentMade::selectRaw('sum(bank_changes) as total')
            ->join('bills_po', 'payment_made.id_bill_po', 'bills_po.id')
            ->join('purchase_orders', 'bills_po.id_purchase_order', 'purchase_orders.id')
            ->join('purchase_request', 'purchase_orders.id_purchase_request', 'purchase_request.id')
            ->join('projects', 'purchase_request.id_project', 'projects.id')
            ->join('rab', 'projects.id', 'rab.id_project')
            ->where('rab.id', $id)->first()->total;

        $total += PaymentMade::selectRaw('sum(bank_changes) as total')
            ->join('bills_lo', 'payment_made.id_bill_lo', 'bills_lo.id')
            ->join('labour_order', 'bills_lo.id_labour_order', 'labour_order.id')
            ->join('projects', 'labour_order.id_project', 'projects.id')
            ->join('rab', 'projects.id', 'rab.id_project')
            ->where('rab.id', $id)->first()->total;

        $total += CashReportDetail::selectRaw('sum(kredit) as total')
        ->whereHas('cash_report.cash_request.project.rab',function($q) use ($id){
            $q->where('rab.id',$id);
        })->where('account','Other Cost')->first()->total;

        return $total;
    }
    public static function kas_project($id)
    {
        $total = CashRequest::selectRaw('sum(nominal) as total')
        ->whereHas('project.rab',function($q) use ($id){
            $q->where('rab.id',$id);
        })->where('status','approve')->first()->total;

        $total -= CashReportDetail::selectRaw('sum(kredit) as total')
        ->whereHas('cash_report.cash_request.project.rab',function($q) use ($id){
            $q->where('rab.id',$id);
        })->first()->total;

        return $total;

    }
    public static function debt($id)
    {
        $total = BillsPo::selectRaw('sum(amount) as total')
            ->join('purchase_orders', 'bills_po.id_purchase_order', 'purchase_orders.id')
            ->join('purchase_request', 'purchase_orders.id_purchase_request', 'purchase_request.id')
            ->join('projects', 'purchase_request.id_project', 'projects.id')
            ->join('rab', 'projects.id', 'rab.id_project')
            ->where([
                ['rab.id', $id],
                ['bills_po.status', 'open'],
            ])->first()->total;

        $total += BillsPo::selectRaw('sum(amount) as total')
            ->join('projects', 'bills_po.id_project', 'projects.id')
            ->join('rab', 'projects.id', 'rab.id_project')
            ->where([
                ['rab.id', $id],
                ['bills_po.status', 'open'],
            ])->first()->total;

        $total += BillsLo::selectRaw('sum(amount) as total')
            ->join('labour_order', 'bills_lo.id_labour_order', 'labour_order.id')
            ->join('projects', 'labour_order.id_project', 'projects.id')
            ->join('rab', 'projects.id', 'rab.id_project')
            ->where([
                ['rab.id', $id],
                ['bills_lo.status', 'open'],
            ])->first()->total;

        return $total;
    }
    public static function total_real_cashout($id)
    {
        return CostControlController::material_bypo($id) + CostControlController::labour_bylo($id) + CostControlController::site_management($id) + CostControlController::other_cost($id) + CostControlController::kas_project($id) + CostControlController::debt($id);
    }
    public static function pdp($id)
    {
        return CostControlController::material_bypo($id) + CostControlController::labour_bylo($id) + CostControlController::site_management($id) + CostControlController::other_cost($id) + CostControlController::kas_project($id);
    }
    
    public static function totalCashIn($id) {
        $id_project = Rab::findOrFail($id)->id_project;
        $invoice_ppn = Invoice::where([['id_projects', $id_project], ['ppn', '10%'], ['status', 'PAID']])->get();
        $invoice_non_ppn = Invoice::where([['id_projects', $id_project], ['ppn', 'Non PPN'], ['status', 'PAID']])->get();

        $total_ppn = 0;
        if(!empty($invoice_ppn)) {
            foreach($invoice_ppn as $i) {
                foreach($i->detailInvoice as $d) {
                    $total_ppn += ($d->qty * $d->rate) + ($d->qty * $d->rate * 0.1);
                }
            }
        }

        $total_non_ppn = 0;
        if(!empty($invoice_non_ppn)) {
            foreach($invoice_non_ppn as $i) {
                foreach($i->detailInvoice as $d) {
                    $total_non_ppn += ($d->qty * $d->rate);
                }
            }
        }

        $total = $total_ppn + $total_non_ppn;
        if($total == 0) {
            return 0;
        } else {
            return $total;
        }
    }
}