<?php

namespace App\Http\Controllers\Agencies;
use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use Auth;
use DB;
use DataTables;

class ReportCommissionController extends Controller
{
    public function index()
    {
        return view('agencies.report-commission.index');
    }
    public function datatable_get_report_commission(){
        $log_id = Auth::user()->id;
        $commission = DB::table('nso007_agency_commission_list')
                        ->leftJoin('nso007_agency_commission', 'nso007_agency_commission.id', '=', 'nso007_agency_commission_list.commission_id')
                        ->leftJoin('nso007_loan', 'nso007_agency_commission.sale_id', '=', 'nso007_loan.loan_saleid')
                        ->leftJoin('nso007_sale_stock', 'nso007_agency_commission.sale_id', '=', 'nso007_sale_stock.id')
                        ->leftJoin('nso007_client', 'nso007_client.id', '=', 'nso007_sale_stock.sale_clientid')
                        ->leftJoin('users', 'nso007_agency_commission_list.agency_id', '=', 'users.id')
                        ->leftJoin('nso007_item_stock', 'nso007_item_stock.id', '=', 'nso007_agency_commission.item_id')
                        ->leftJoin('nso007_project_stock', 'nso007_project_stock.id', '=', 'nso007_item_stock.item_projectid')
                        ->leftJoin('nso007_category_stock', 'nso007_category_stock.id', '=', 'nso007_item_stock.item_categoryid')
                        ->where('nso007_agency_commission_list.agency_id',$log_id)
                        ->where('nso007_agency_commission_list.status', 1)
                        ->select(
                            'nso007_category_stock.category_name',
                            'nso007_project_stock.project_name',
                            'nso007_item_stock.item_name',
                            'nso007_item_stock.item_house',
                            'nso007_item_stock.item_street',
                            'nso007_item_stock.item_price',
                            'nso007_client.client_fullname',
                            'nso007_loan.loan_amount',
                            'nso007_loan.loan_discount',
                            'nso007_loan.loan_amountos',
                            'nso007_sale_stock.sale_purchase',
                            'nso007_sale_stock.sale_datecontract',
                            'nso007_sale_stock.sale_amount',
                            'nso007_sale_stock.sale_receive',
                            'nso007_sale_stock.id as sale_id',
                            'nso007_agency_commission_list.commission',
                            'nso007_agency_commission_list.amount',
                            'nso007_agency_commission_list.receive',
                            'nso007_agency_commission_list.id',
                            'users.user_code',
                            DB::raw('CONCAT(users.user_firstname," ",users.user_lastname) as full_name')
                        )
                        ->get();
        $agency_comm = array();
        foreach ($commission as $key => $value) {
            $array = array();
            $max_date=DB::select("SELECT date_contract,status_num FROM nso007_deposit WHERE nso007_deposit.deposit_saleid = $value->sale_id AND id = (select max(id) from nso007_deposit WHERE nso007_deposit.deposit_saleid = $value->sale_id)");
            $mdate='';
            if($max_date[0]->status_num==1){
                $mdate = $value->sale_datecontract;
            }else{
                $mdate = $max_date[0]->date_contract;
            }

            $balan=0;
            $balance = $value->amount - $value->receive;
            if($balance <= 0){
                $balan=0;
            }else{
                $balan=$balance;
            }

            $balans=0;
            $balances = $value->amount - $value->receive;
            if($balances <= 0){
                $balans=0;
            }else{
                $balans =($balances*100/$value->amount);
            }

            $receive_per = ($value->receive*100/$value->amount);

            if($value->sale_purchase==0){
                $amount_paid=$value->sale_receive;
            }else{
                $amount_paid = ($value->loan_amount-$value->loan_discount-$value->loan_amountos);
            }

            if($value->sale_purchase==0){
                $amount_per=($value->sale_receive*100/$value->sale_amount);
            }else{
                $amount_per = (($value->loan_amount-$value->loan_discount-$value->loan_amountos)*100/$value->loan_amount);
            }
            $array = array(
                'id' =>  $value->id,
                'project_name' =>  $value->project_name,
                'category_name' =>  $value->category_name,
                'item_name' =>  $value->item_name,
                'item_house' =>  $value->item_house,
                'item_street' =>  $value->item_street,
                'client_fullname' =>  $value->client_fullname,
                'balance' =>  $balan,
                'balan_per' =>  $balans,
                'receive_per' =>  $receive_per,
                'amount_paid' =>  $amount_paid,
                'amount_per' =>  $amount_per,
                'item_price' =>  $value->item_price,
                'amount' =>  $value->amount,
                'receive' =>  $value->receive,
                'commission' =>  $value->commission,
                'agency_name' =>  $value->full_name." (".$value->user_code.")",
                'sale_datecontract' =>  $mdate,
            );
            array_push($agency_comm, $array);
        }
		return Datatables::of($agency_comm)
            ->addColumn('contract_date', function ($agency_comm) {
                return date('d-m-Y', strtotime($agency_comm["sale_datecontract"]));
            })
            ->addColumn('balance', function($agency_comm) {

                  return '$'.number_format($agency_comm["balance"],2);

            })
            ->editColumn('full_name', function($agency_comm) {
                  return $agency_comm["agency_name"];
            })
            ->editColumn('item_price', function($agency_comm) {
                  return '$'.number_format($agency_comm["item_price"],2);
            })
            ->addColumn('amount', function($agency_comm) {
                  return '$'.number_format($agency_comm["amount"],2);
            })
            ->addColumn('receive', function($agency_comm) {
                  return '$'.number_format($agency_comm["receive"],2);
            })
            ->addColumn('commission', function($agency_comm) {
                  return $agency_comm["commission"].'%';
            })
            ->addColumn('balance_per', function ($agency_comm) {
                  return number_format($agency_comm["balan_per"],2). " %";
            })
            ->addColumn('receive_per', function ($agency_comm) {
                return number_format($agency_comm["receive_per"], 2). " %";
            })
            ->addColumn('amount_paid', function ($agency_comm) {
                return '$ '.number_format($agency_comm["amount_paid"], 2);
            })
            ->addColumn('amount_per', function ($agency_comm) {
                return number_format($agency_comm["amount_per"], 2). " %";
            })
			->addIndexColumn()
            ->make(true);
    }
}
