<?php

namespace App\Http\Controllers;

use App\Models\CommReleaseDtl;
use App\Models\GrossRelease;
use App\Models\SalesComm;
use Illuminate\Http\Request;
use Spatie\Permission\Models\Role;
use App\Models\Developer;
use App\Models\Team;
use App\Models\Project;
use App\Models\CommRelease;
use App\Models\Agent;
use App\User;

use App\Helpers\DBFunctions;

use DB;
use Auth;
use JavaScript;

class CommReleaseController extends Controller
{
    public function index()
    {
        //$releases   = DB::select('select * from vw_release_list where ' .' month = '.$mm.' AND ' .' year = '.$yy. ' order by is_released asc');

        $month = date('m');
        //$month = '08';
        $yy = date('Y');

        $role_      = Auth::user()->role_id;
        $user_id    = Auth::user()->id;

        $cmonth     = date('m');
        $months     = DB::select("select * from vw_months");
        $years      = DB::select("select * from vw_year");

        if ($role_ == 4) {

            $user_agent = DB::select('select * from vw_user_agent' . ' where user_id = ' . $user_id);
            $uagent = $user_agent[0]->agent_id;

            $releases = DB::select('select * from vw_release_list where where deleted_at IS NULL AND agent_id ='.$uagent);
            //dd($releases);
            //$comms = CommRelease::with(['agent', 'manager', 'user'])->whereNull('deleted_at')->orderBy('id', 'DESC')->get();
            $agents = Agent::orderBy('fullname', 'ASC')->get();
            $ums = Agent::where('position', '=', 'Unit Manager')->get();
            $share = DB::select('SELECT * FROM sharing_table');
            //dd($teams);
            return view('admin.releases.index', compact('releases', 'agents', 'ums', 'share', 'months', 'years', 'cmonth', 'yy'));
        } else {
            //$releases = CommRelease::with(['release_dtl', 'agent', 'manager'])->orderBy('id', 'DESC')->get();
            $releases = DB::select('select * from vw_release_list');
            //$releases =  DB::select('call sp_release_list(?,?)',array($month, $yy));
            //dd($releases);
            //$comms = CommRelease::with(['agent', 'manager', 'user'])->whereNull('deleted_at')->orderBy('id', 'DESC')->get();
            $agents = Agent::orderBy('fullname', 'ASC')->get();
            $ums = Agent::where('position', '=', 'Unit Manager')->get();
            $share = DB::select('SELECT * FROM sharing_table');
            //dd($share);
            return view('admin.releases.index', compact('releases', 'agents', 'ums', 'share', 'months', 'years', 'cmonth', 'yy'));

        }
    }

    public function month($month, $year)
    {
        //$yy = date('Y');
        $role_      = Auth::user()->role_id;
        $user_id    = Auth::user()->id;

        $cmonth = date('m');
        $yy     = date('Y');
        $months = DB::select("select * from vw_months");
        $years  = DB::select("select * from vw_year");

        if ($role_ == 4) {

            $user_agent = DB::select('select * from vw_user_agent' . ' where user_id = ' . $user_id);
            $uagent = $user_agent[0]->agent_id;

            //$releases = DB::select('select * from vw_release_list where agent_id ='.$uagent);
            $releases = DB::select('select * from vw_release_list where agent_id ="'.$uagent.'" and mmonth="'.$month.'" and yyear="'.$year.'"');
            //$comms = CommRelease::with(['agent', 'manager', 'user'])->whereNull('deleted_at')->orderBy('id', 'DESC')->get();
            $agents = Agent::orderBy('fullname', 'ASC')->get();
            $ums = Agent::where('position', '=', 'Unit Manager')->get();
            $share = DB::select('SELECT * FROM sharing_table');
            //dd($teams);
            return view('admin.releases.index', compact('releases', 'agents', 'ums', 'share', 'months', 'years', 'cmonth', 'yy'));
        } else {
            //$releases = CommRelease::with(['release_dtl', 'agent', 'manager'])->orderBy('id', 'DESC')->get();
            //$releases = DB::select('select * from vw_release_list where mmonth="'.$month.'" and yyear="'.$year.'"');
            $releases =  DB::select('call sp_release_list(?,?)',array($month, $year));
            //dd($releases);
            //$comms = CommRelease::with(['agent', 'manager', 'user'])->whereNull('deleted_at')->orderBy('id', 'DESC')->get();
            $agents = Agent::orderBy('fullname', 'ASC')->get();
            $ums = Agent::where('position', '=', 'Unit Manager')->get();
            $share = DB::select('SELECT * FROM sharing_table');
            //dd($share);

            return view('admin.releases.index', compact('releases', 'agents', 'ums', 'share', 'months', 'years', 'cmonth', 'yy'));

        }
    }

    public function getNewReleases($id, $rcvd_id) {

        //$mm = date("m");
        //$yy = date("Y");
        /*$releases   = DB::select('select * from vw_release_list where deleted_at IS NULL'); //' where ' .' month = '.$mm.' AND ' .' year = '.$yy. ' order by is_released asc');
        $agents     = Agent::orderBy('fullname','ASC')->get();
        $ums        = Agent::where('position', '=', 'Unit Manager')->get();
        $share      = DB::select('SELECT * FROM sharing_table');

        return view('admin.releases.new', compact('releases', 'agents', 'ums', 'share'));*/
        $share      = DB::select('SELECT * FROM sharing_table');
        $agents     = Agent::orderBy('fullname','ASC')->get();
        $ums        = Agent::where('position', '=', 'Unit Manager')->get();
        $header     = DB::select('select * from vw_release_list where gr_id = '.$id);
        //dd($header[0]->unit_manager_id);
        //$release    = CommReleaseDtl::where('gl_id', $id)->get();
        //dd($release);
        $ca_bal     = DB::select('call sp_agent_cash_adv_balance(?)',array($header[0]->agent_id));
        $ca_bal2    = DB::select('call sp_manager_cash_adv_balance(?)',array($header[0]->unit_manager_id));
        //$rcvidd     = $release[0]->rcvd_id;
        $rcvd       = DB::select('select * from vw_recv_list where rcvd_id = '.$rcvd_id);
        $ttl_rls    = DB::select('call sp_get_release_amount2(?)',array($rcvd_id));
        $ttl_rcvd   = DB::select('call sp_get_receive_amount2(?)',array($rcvd_id));
        $max_rls    = ($rcvd[0]->contract_amount * $rcvd[0]->def_comm_rate) / 100;

        if ($ttl_rls == null || !isset($ttl_rls)) {
            $rls_release = 0;
            $ttl_release_mgr = 0;
        } else {
            $rls_release =  $ttl_rls[0]->ttl_release;
            $ttl_release_mgr = $ttl_rls[0]->ttl_release_mgr;
        }

        if ($ttl_rcvd == null || !isset($ttl_rcvd)) {
            $ttl_rcvd = 0;
            $ttl_receive = 0;
            $contract_amount = 0;
            //$prg_ = 0;
        } else {
            $ttl_receive = $ttl_rcvd[0]->ttl_receive;
            $contract_amount =  $rcvd[0]->contract_amount;
            //$prg_ = ($ttl_receive / $contract_amount) * 100;
        }

        //$rl_amt     = $release[0]->comm_amount;
        //dd($ttl_rcvd[0]->ttl_receive);
        $bal        = DB::select('select * from vw_agent_ca_balances where agent_id = '.$header[0]->agent_id);

        if ($bal == null || $bal == '') {
            $ca_bal     = 0;
        } else {
            $ca_bal     = $bal[0]->ca_bal;
        }

        $bal2       = DB::select('select * from vw_manager_ca_balances where manager_id = '.$header[0]->unit_manager_id);

        if ($bal2 == null || $bal2 == '') {
            $um_bal     = 0;
        } else {
            $um_bal     = $bal2[0]->ca_bal;
        }

        $sale_comm  = SalesComm::where('sales_id', $rcvd[0]->id)->first();
        //dd($sale_comm);

        $sell_price = $rcvd[0]->net_selling_price;

        $rem_rcvd1  = ($sell_price * $sale_comm->agent_pcg_rate) / 100;
        $a_rem_rcvd = $rem_rcvd1 - $rls_release; //$ttl_rls[0]->ttl_release;

        $rem_rcvd2  = ($sell_price * $sale_comm->um_pcg_rate) / 100;
        $u_rem_rcvd = $rem_rcvd2 - $ttl_release_mgr; //$ttl_rls[0]->ttl_release_mgr;

        return view('admin.releases.new', compact(
                'id',
                'rcvd_id',
                'header',
                'agents',
                'ums',
                'ca_bal',
                'um_bal',
                'max_rls',
                'ttl_rls',
                'ttl_rcvd',
                'a_rem_rcvd',
                'u_rem_rcvd',
                'sale_comm',
                'share',
                'rls_release',
                'ttl_receive',
                'ttl_release_mgr')
        );

    }

    public function editt($id) {

        $agents     = Agent::orderBy('fullname','ASC')->get();
        $ums        = Agent::where('position', '=', 'Unit Manager')->get();
        $header     = DB::select('select * from vw_release_list where gr_id = '.$id);
        //dd($header[0]->unit_manager_id);
        $release    = CommReleaseDtl::where('gl_id', $id)->get();
        //$ca_bal     = DB::select('call sp_agent_cash_adv_balance(?)',array($header[0]->agent_id));
        //$ca_bal2    = DB::select('call sp_manager_cash_adv_balance(?)',array($header[0]->unit_manager_id));
        $rcvidd     = $release[0]->rcvd_id;
        $rcvd       = DB::select('select * from vw_recv_list where rcvd_id = '.$rcvidd);
        $ttl_rls    = DB::select('call sp_get_release_amount2(?)',array($rcvidd));
        $ttl_rcvd   = DB::select('call sp_get_receive_amount2(?)',array($rcvidd));
        $max_rls    = ($rcvd[0]->contract_amount * $rcvd[0]->def_comm_rate) / 100;
        //$rl_amt     = $release[0]->comm_amount;
        //dd($ttl_rcvd[0]->ttl_receive);
        $bal        = DB::select('select * from vw_agent_ca_balances where agent_id = '.$header[0]->agent_id);

        if ($bal == null || $bal == '') {
            $ca_bal     = 0;
        } else {
            $ca_bal     = $bal[0]->ca_bal;
        }

        $bal2       = DB::select('select * from vw_manager_ca_balances where manager_id = '.$header[0]->unit_manager_id);

        if ($bal2 == null || $bal2 == '') {
            $um_bal     = 0;
        } else {
            $um_bal     = $bal2[0]->ca_bal;
        }

        $sale_comm  = SalesComm::where('sales_id', $rcvd[0]->id)->first();
        //dd($sale_comm);

        $sell_price = $rcvd[0]->net_selling_price;

        $rem_rcvd1  = ($sell_price * $sale_comm->agent_pcg_rate) / 100;
        $a_rem_rcvd = $rem_rcvd1 - $ttl_rls[0]->ttl_release;

        $rem_rcvd2  = ($sell_price * $sale_comm->um_pcg_rate) / 100;
        $u_rem_rcvd = $rem_rcvd2 - $ttl_rls[0]->ttl_release_mgr;

        return view('admin.releases.edit', compact('release', 'header', 'agents', 'ums', 'ca_bal', 'um_bal', 'max_rls', 'ttl_rls', 'ttl_rcvd', 'a_rem_rcvd', 'u_rem_rcvd', 'sale_comm', 'rcvidd'));
    }

    public function postNewReleases(Request $request) {

        //dd($request->all());

        try {

            DB::beginTransaction();

            /*$validate = [
                'agent_id'      => 'required',
                'umanager_id'   => 'required',
                'comm_amount'  => 'required'
            ];

            $validator = \Validator::make($request->all(), $validate);

            // dd($validator);
            if ( $validator->fails() ) {
                //dd( $validator );
                return back()
                    ->withErrors($validator)
                    ->withInput();
            }*/

            $userid = Auth::user()->id;
            $rcvdid = $request['rcvid'];

            $rcvdr = new CommRelease();

            $rdata = [
                'gr_id'         => $request['gl_id'],
                'agent_id'      => $request['agent_id'],
                'umanager_id'   => $request['umanager_id'],
                'referrer_id'   => $request['referrer_id'],
                'remarks'       => $request['remarks'],
                'user_id'       => $userid
            ];
            //dd($rdata);

            $rcvdr->fill($rdata);
            $rcvdr->save();

            $id_ = $rcvdr->id;

            if ($rcvdr) {

                $gr = new CommReleaseDtl();

                $comm_amt       = $this->floatvalue($request['ttl_receive']);
                $agt_prate      = $this->floatvalue($request['agent_pcg_rate']);
                $agt_com_rate   = $this->getcomm_rate($comm_amt,$agt_prate);
                $um_prate       = $this->floatvalue($request['um_pcg_rate']);
                $um_com_rate    = $this->getcomm_rate($comm_amt,$um_prate);
                $brk_prate      = $this->floatvalue($request['broker_pcg_rate']);
                $brk_com_rate   = $this->getcomm_rate($comm_amt,$brk_prate);
                $ref_prate      = $this->floatvalue($request['referrer_pcg_rate']);
                $ref_com_rate   = $this->getcomm_rate($comm_amt,$ref_prate);

                $grdata = [
                    'comm_id'           => $id_,
                    'rcvd_id'           => $request['rcvid'],
                    'gl_id'             => $request['gl_id'],
                    'comm_amount'       => $comm_amt,
                    'agent_pcg_rate'    => $agt_prate,
                    'agent_com_rate'    => $agt_com_rate,
                    'um_pcg_rate'       => $um_prate,
                    'um_com_rate'       => $um_com_rate,
                    'broker_pcg_rate'   => $brk_prate,
                    'broker_com_rate'   => $brk_com_rate,
                    'ref_pcg_rate'      => $ref_prate,
                    'ref_com_rate'      => $ref_com_rate,
                    'payment_adv'       => $request['ca_amount'],
                    'um_payment_adv'    => $request['um_ca_amount'],
                    'tax_rate'          => 13, //$request['tax']
                    'remarks'           => $request['remarks']
                ];
                //dd($grdata);

                $gr->fill($grdata);
                $gr->save();

                if ($gr) {
                    $this->post_release($request['gl_id']);
                    }
                }

            DB::commit();

            //return redirect()->route('receivables')->with('info', 'New commission release was created.');
            return redirect()->route('receivables.edit', $rcvdid)->with('info', 'New commission release was created.');

        } catch (\Exception $e) {
            DB::rollBack();
            //dd($e->getMessage());
            //return redirect()->route('receivables.new')->with('info', 'Oppsss something went wrong..');
            return redirect()->route('receivables.new')->with('info', $e->getMessage());
        }

    }

    public function updateRelease(Request $request) {

        //dd($request->all());

        try {

            DB::beginTransaction();

            /*$validate = [
                'agent_id'      => 'required',
                'umanager_id'   => 'required',
                'comm_amount'   => 'required'
            ];

            $validator = \Validator::make($request->all(), $validate);

            // dd($validator);
            if ( $validator->fails() ) {
                //dd( $validator );
                return back()
                    ->withErrors($validator)
                    ->withInput();
            }*/

            //$userid = Auth::user()->id;

            $id = $request['gl_id'];
            //dd($id);

            //$rcvdr = CommRelease::where('gr_id', $id)->first();
            //dd($rcvdr);

            $gr = CommReleaseDtl::where('gl_id', $id)->first();
            //dd($gr);

            $comm_amt       = $request['ttl_receive']; //$this->floatvalue($request['release_amount']);
            $agt_prate      = $request['agent_pcg_rate'];
            $agt_com_rate   = $this->getcomm_rate($comm_amt,$agt_prate);
            $um_prate       = $request['um_pcg_rate'];
            $um_com_rate    = $this->getcomm_rate($comm_amt,$um_prate);
            $brk_prate      = $request['broker_pcg_rate'];
            $brk_com_rate   = $this->getcomm_rate($comm_amt,$brk_prate);
            $ref_prate      = $request['ref_pcg_rate'];
            $ref_com_rate   = $this->getcomm_rate($comm_amt,$ref_prate);

            $grdata = [
                //'comm_id'           => $id,
                //'rcvd_id'           => $request['rcvd_id'],
                //'gl_id'             => $request['gl_id'],
                'comm_amount'       => $this->floatvalue($comm_amt),
                'agent_pcg_rate'    => $this->floatvalue($agt_prate),
                'agent_com_rate'    => $this->floatvalue($agt_com_rate),
                'um_pcg_rate'       => $this->floatvalue($um_prate),
                'um_com_rate'       => $this->floatvalue($um_com_rate),
                'broker_pcg_rate'   => $this->floatvalue($brk_prate),
                'broker_com_rate'   => $this->floatvalue($brk_com_rate),
                'ref_pcg_rate'      => $this->floatvalue($ref_prate),
                'ref_com_rate'      => $this->floatvalue($ref_com_rate),
                'payment_adv'       => $this->floatvalue($request['ca_amount']),
                'um_payment_adv'    => $this->floatvalue($request['um_ca_amount'])
                //'remarks'           => $request['remarks']
                //'tax_rate'          => $request['tax']
            ];
            //dd($grdata);

            $gr->fill($grdata);
            $gr->update();

            if ($gr) {
                $this->post_release($request['gl_id']);
            }

            DB::commit();

            return redirect()->route('receivables.edit', [$request['rcvd_id']])->with('info', 'The release commission have been updated.');
            //return redirect()->route('releases.editt', [$request['gl_id']])->with('info', 'The release commission have been updated.');
            //redirect()->back()->with('info', 'The release commission have been updated.');

        } catch (\Exception $e) {
            DB::rollBack();
            //return redirect()->route('receivables.new')->with('info', 'Oppsss something went wrong..');
            //return redirect()->route('receivables')->with('info', $e->getMessage());
            redirect()->back()->with('info', $e->getMessage());
        }

    }

    public function editPostDetails($id) {
        dd($id);
    }

    public function postDetails(Request $request) {
        dd($request->all());

        $id = $request['id'];
        //dd($id);

        $gr = CommReleaseDtl::where('id', $id)->first();

        //$comm_amt       = $request['ttl_receive'];
        $agt_prate      = $request['agent_pcg_rate'];
        $agt_com_rate   = $this->getcomm_rate($comm_amt,$agt_prate);
        $um_prate       = $request['um_pcg_rate'];
        $um_com_rate    = $this->getcomm_rate($comm_amt,$um_prate);
        $brk_prate      = $request['broker_pcg_rate'];
        $brk_com_rate   = $this->getcomm_rate($comm_amt,$brk_prate);
        $ref_prate      = $request['ref_pcg_rate'];
        $ref_com_rate   = $this->getcomm_rate($comm_amt,$ref_prate);

        $grdata = [
            //'comm_id'           => $id,
            //'rcvd_id'           => $request['rcvd_id'],
            //'gl_id'             => $request['gl_id'],
            //'comm_amount'       => $this->floatvalue($comm_amt),
            'agent_pcg_rate'    => $this->floatvalue($agt_prate),
            'agent_com_rate'    => $this->floatvalue($agt_com_rate),
            'um_pcg_rate'       => $this->floatvalue($um_prate),
            'um_com_rate'       => $this->floatvalue($um_com_rate),
            'broker_pcg_rate'   => $this->floatvalue($brk_prate),
            'broker_com_rate'   => $this->floatvalue($brk_com_rate),
            'ref_pcg_rate'      => $this->floatvalue($ref_prate),
            'ref_com_rate'      => $this->floatvalue($ref_com_rate),
            'payment_adv'       => $this->floatvalue($request['ca_amount']),
            'um_payment_adv'    => $this->floatvalue($request['um_ca_amount'])
            //'tax_rate'          => $request['tax']
        ];
        //dd($grdata);

        $gr->fill($grdata);
        $gr->update();
    }

    function getcomm_rate($comm_amt, $percent) {
        $comm_rate = ($this->floatvalue($comm_amt) * floatval($percent)) / 100;
        //$comm_rate = (floatval($comm_amt) * floatval($percent)) / 100;
        return $comm_rate;
    }

    function floatvalue($val){
        $val = str_replace(",",".",$val);
        $val = preg_replace('/\.(?=.*\.)/', '', $val);
        return floatval($val);
    }

    public function post_release($id) {
        //GrossRelease::where('id', $id)->update(['is_released', 1]);
        DB::table('rcvd_gross_release')
            ->where("id", '=', $id)
            ->update(['is_released'=> 1]);
        //return redirect()->back()->with('status', trans('User has been updated successfully.'));
    }

    public function chk_CA_Bal(Request $request) {

        $agent_id   = $request->agent_id;
        $bal        = DB::select('call sp_agent_cash_adv_balance(?)',array($agent_id));

        //return response()->json(["data"=>$agent]);
        if (isset($bal)) {
            return response()->json(["data"=>$bal]);
        } else {
            return response('No data');
        }
    }

}
