<?php

namespace App\Http\Controllers\Administrator;

use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use DB;
use DataTables;
use App\ProjectModel;
use App\BranchModel;

class ReceivableNoteController extends Controller
{
    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function index()
    {
        $project = ProjectModel::select(DB::raw('CONCAT(id," ",project_name) AS project_name'),'id')
            ->pluck('project_name','id')
            ->prepend('Please Select', '');
        $key_branch=\Branch::branch_key();
        return view('administrator.accounting.receivable-note.index',compact('project','key_branch'));
    }
    public function payable_note_branch()
    {
        $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();
        $key_branch=\Branch::branch_key();
        return view('administrator.accounting.receivable-note.index_branch',compact('project','key_branch'));
    }

    // Get Report

    public function get_report(Request $request)
    {
        $data = DB::select("
            SELECT 
            project.id AS id,
            project.project_code,
            project.project_name,
            SUM(commission.commission_amount) AS commission_amount,
            SUM(commission.commission_received) AS commission_available,
            (SELECT SUM(amount_received) FROM nso007_received_commission WHERE nso007_received_commission.project_id = project.id) AS commission_received
            FROM nso007_project_stock AS project
            LEFT JOIN nso007_item_stock AS item ON project.id = item.item_projectid
            LEFT JOIN nso007_sale_detail_stock AS detail ON item.id = detail.saledetail_itemid
            LEFT JOIN nso007_sale_stock AS stock ON detail.saledetail_saleid = stock.id
            LEFT JOIN nso007_admin_commission AS commission ON stock.id = commission.sale_id
            GROUP BY project.id
        ");
        if($request->isMethod('GET')) {
            return Datatables::of($data)
                ->addIndexColumn()
                ->setRowId('id')
                ->editColumn('commission_amount', function ($data) {
                    return '$ ' . number_format($data->commission_amount, 2);
                })
                ->editColumn('commission_available', function ($data) {
                    return '$ ' . number_format($data->commission_available, 2);
                })
                ->editColumn('commission_received', function ($data) {
                    return '$ ' . number_format($data->commission_received, 2);
                })
                ->editColumn('commission_owe', function ($data) {
                    return '$ ' . number_format(($data->commission_available - $data->commission_received), 2);
                })
                ->make(true);
        }else{
            $project_name = (empty($request->project_name)) ? '' : $request->project_name;
            $data = DB::select("
                SELECT 
                project.id AS id,
                project.project_code,
                project.project_name,
                SUM(commission.commission_amount) AS commission_amount,
                SUM(commission.commission_received) AS commission_available,
                IFNULL((SELECT SUM(amount_received) FROM nso007_received_commission WHERE nso007_received_commission.project_id = project.id),0) AS commission_received
                FROM nso007_project_stock AS project
                LEFT JOIN nso007_item_stock AS item ON project.id = item.item_projectid
                LEFT JOIN nso007_sale_detail_stock AS detail ON item.id = detail.saledetail_itemid
                LEFT JOIN nso007_sale_stock AS stock ON detail.saledetail_saleid = stock.id
                LEFT JOIN nso007_admin_commission AS commission ON stock.id = commission.sale_id
                where project.id=$project_name
                GROUP BY project.id
            ");
            echo json_encode($data);
        }
    }

    public function get_report_branch(Request $request)
    {
        $data = DB::select("
            SELECT 
            project.id AS id,
            'ML Company' as project_code,
            project.project_name,
            SUM(commission.commission_amount) AS commission_amount,
            SUM(commission.commission_received) AS commission_available,
            (SELECT SUM(amount_received) FROM nso007_received_commission WHERE nso007_received_commission.project_id = project.id) AS commission_received
            FROM nso007_project_stock AS project
            LEFT JOIN nso007_item_stock AS item ON project.id = item.item_projectid
            LEFT JOIN nso007_sale_detail_stock AS detail ON item.id = detail.saledetail_itemid
            LEFT JOIN nso007_sale_stock AS stock ON detail.saledetail_saleid = stock.id
            LEFT JOIN nso007_admin_commission AS commission ON stock.id = commission.sale_id
            WHERE branch_id= '".\Branch::branch_id()."'
            GROUP BY project.id
        ");
        if($request->isMethod('GET')) {
            return Datatables::of($data)
                ->addIndexColumn()
                ->setRowId('id')
                ->editColumn('commission_amount', function ($data) {
                    return '$ ' . number_format($data->commission_amount, 2);
                })
                ->editColumn('commission_available', function ($data) {
                    return '$ ' . number_format($data->commission_available, 2);
                })
                ->editColumn('commission_received', function ($data) {
                    return '$ ' . number_format($data->commission_received, 2);
                })
                ->editColumn('commission_owe', function ($data) {
                    return '$ ' . number_format(($data->commission_available - $data->commission_received), 2);
                })
                ->make(true);
        }else{
            $project_name = (empty($request->project_name)) ? '' : $request->project_name;
            $data = DB::select("
                SELECT 
                project.id AS id,
               'ML Company' as project_code,
                project.project_name,
                SUM(commission.commission_amount) AS commission_amount,
                SUM(commission.commission_received) AS commission_available,
                IFNULL((SELECT SUM(amount_received) FROM nso007_received_commission WHERE nso007_received_commission.project_id = project.id),0) AS commission_received
                FROM nso007_project_stock AS project
                LEFT JOIN nso007_item_stock AS item ON project.id = item.item_projectid
                LEFT JOIN nso007_sale_detail_stock AS detail ON item.id = detail.saledetail_itemid
                LEFT JOIN nso007_sale_stock AS stock ON detail.saledetail_saleid = stock.id
                LEFT JOIN nso007_admin_commission AS commission ON stock.id = commission.sale_id
                where project.id=$project_name AND branch_id= '".Branch::branch_id()."'
                GROUP BY project.id
            ");
            echo json_encode($data);
        }
    }

    // Get Available Commission Received
    public function get_available_commission_by_project($project_id)
    {
        $data = DB::select("
            SELECT 
            project.id AS id,
            project.project_code,
            project.project_name,
            SUM(commission.commission_amount) AS commission_amount,
            SUM(commission.commission_received) AS commission_available,
            (SELECT SUM(amount_received) FROM nso007_received_commission WHERE nso007_received_commission.project_id = project.id) AS commission_received
            FROM nso007_project_stock AS project
            LEFT JOIN nso007_item_stock AS item ON project.id = item.item_projectid
            LEFT JOIN nso007_sale_detail_stock AS detail ON item.id = detail.saledetail_itemid
            LEFT JOIN nso007_sale_stock AS stock ON detail.saledetail_saleid = stock.id
            LEFT JOIN nso007_admin_commission AS commission ON stock.id = commission.sale_id
            WHERE project.id = $project_id
            GROUP BY project.id
        ");
        echo json_encode($data);
    }

    public function report_receivable_note_print(Request $request)
    {
        $pro_id = empty($request->project_name)?'WHERE branch_id= "'.\Branch::branch_id().'"':'WHERE branch_id= "'.\Branch::branch_id().'" AND project.id="'.$request->project_name.'"';
        $data = DB::select("
            SELECT 
            project.id AS id,
            project.project_code,
            project.project_name,
            SUM(commission.commission_amount) AS commission_amount,
            SUM(commission.commission_received) AS commission_available,
            (SELECT SUM(amount_received) FROM nso007_received_commission WHERE nso007_received_commission.project_id = project.id) AS commission_received
            FROM nso007_project_stock AS project
            LEFT JOIN nso007_item_stock AS item ON project.id = item.item_projectid
            LEFT JOIN nso007_sale_detail_stock AS detail ON item.id = detail.saledetail_itemid
            LEFT JOIN nso007_sale_stock AS stock ON detail.saledetail_saleid = stock.id
            LEFT JOIN nso007_admin_commission AS commission ON stock.id = commission.sale_id
             $pro_id
            GROUP BY project.id
        ");

        $commission_amount = 0;
        $commission_available = 0;
        $commission_received = 0;
        $commission_owe = 0;
        foreach ($data as $key => $value) {
            $commission_amount += $value->commission_amount;
            $commission_available += $value->commission_available;
            $commission_received += $value->commission_received;
            $commission_owe += ($value->commission_available-$value->commission_received);
        }
        $branch = DB::table('nso007_branches')->where('id',\Branch::branch_id())->first();
        return view('administrator.accounting.receivable-note.report-view-print-rn',compact('data','branch','commission_owe','commission_received','commission_available','commission_amount'));

    }
    public function report_payable_note_branch_print(Request $request)
    {
        $pro_id = empty($request->project_name)?'WHERE branch_id= "'.\Branch::branch_id().'"':'WHERE branch_id= "'.\Branch::branch_id().'" AND project.id="'.$request->project_name.'"';
        $data = DB::select("
            SELECT 
            project.id AS id,
            'ML Company' as project_code,
            project.project_name,
            SUM(commission.commission_amount) AS commission_amount,
            SUM(commission.commission_received) AS commission_available,
            (SELECT SUM(amount_received) FROM nso007_received_commission WHERE nso007_received_commission.project_id = project.id) AS commission_received
            FROM nso007_project_stock AS project
            LEFT JOIN nso007_item_stock AS item ON project.id = item.item_projectid
            LEFT JOIN nso007_sale_detail_stock AS detail ON item.id = detail.saledetail_itemid
            LEFT JOIN nso007_sale_stock AS stock ON detail.saledetail_saleid = stock.id
            LEFT JOIN nso007_admin_commission AS commission ON stock.id = commission.sale_id
             $pro_id
            GROUP BY project.id
        ");

        $commission_amount = 0;
        $commission_available = 0;
        $commission_received = 0;
        $commission_owe = 0;
        foreach ($data as $key => $value) {
            $commission_amount += $value->commission_amount;
            $commission_available += $value->commission_available;
            $commission_received += $value->commission_received;
            $commission_owe += ($value->commission_available-$value->commission_received);
        }
        $branch = DB::table('nso007_branches')->where('id',\Branch::branch_id())->first();
        return view('administrator.accounting.receivable-note.report-view-print-pn-branch',compact('data','branch','commission_owe','commission_received','commission_available','commission_amount'));

    }
}
