<?php
    
namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Models\ObfInsurance;
use App\Models\Accessory;
use App\Models\Allocation; 
use App\Models\Approval;
use App\Models\Branch;
use App\Models\CarExchange;
use App\Models\ExtandWarranty;
use App\Models\Fasttag;
use App\Models\Finance;
use App\Models\Insurance;
use App\Models\Lead;
use App\Models\OBF;
use App\Models\User;
use App\Models\Product;
use App\Models\Carmodel;
use App\Models\Interiorcolor;
use App\Models\Exteriorcolor;
use App\Models\Carvarient;
use App\Models\pricelist;
use App\Models\Mcp;
use App\Models\Tax;
use App\Models\OtherOne;
use App\Models\OtherTwo;
use App\Models\Inventory;
use App\Models\CashReceipt;
use Spatie\Permission\Models\Role;
use Spatie\Permission\Models\Permission;
use Maatwebsite\Excel\Facades\Excel; 
use App\Imports\ImportObf;
use App\Exports\ExportObf;
use Auth, DB, Mail, Validator, File, DataTables;
use Session; 
use Carbon\Carbon;



class LastmonthInsuranceController extends Controller{
    /** construct */
        public function __construct(){
            $this->middleware('permission:last_month_insurance-create', ['only' => ['create']]);
            $this->middleware('permission:last_month_insurance-edit', ['only' => ['edit']]);
            $this->middleware('permission:last_month_insurance-view', ['only' => ['view']]);
            $this->middleware('permission:last_month_insurance-delete', ['only' => ['delete']]);
        }
    /** construct */

    /** index */
        public function index(Request $request){
            if($request->ajax()){

                $date = \Carbon\Carbon::today()->subDays(30);

                /*->whereRaw('DATEDIFF(last_time_of_insurance,current_date) < 30')
                ->whereRaw('DATEDIFF(last_time_of_insurance,current_date) > 0')*/
                $data = ObfInsurance::select('obf_insurance.*','obf.customer_name as o_customer_name','obf.product_id as o_product_id','car_model.name as car_name','car_varient.name as v_name','orders.order_id')
                ->leftJoin('obf','obf.id','=','obf_insurance.obf_id')
                ->leftJoin('products','products.id','=','obf.product_id')
                ->leftJoin('car_model','car_model.id','=','products.car_model')
                ->leftJoin('car_varient','car_varient.id','=','products.veriant')
                ->leftJoin('orders','orders.obf_id','=','obf.id')
                // ->where('obf_insurance.created_at','>=',$date)
                ->where('obf_insurance.date_of_insurance','>=',$date)
                ->where('obf_insurance.is_delete', 0)
                ->where('obf_insurance.status', '1')
                ->orderBy('id' , 'desc')->get();


                return Datatables::of($data)
                        ->addIndexColumn()
                        ->addColumn('o_customer_name', function($data) {
                            $return = '';
                                $return .= $data->o_customer_name."<br>".$data->order_id;
                            return $return;
                        })
                        
                        ->addColumn('policy_number', function($data) {
                            $return = '';
                                $return .= $data->policy_number."<br>".$data->car_name. ' - '.$data->v_name;
                            return $return;
                        })

                        ->addColumn('action', function($data){
                            $return = '<div class="btn-group">';

                            if(auth()->user()->can('last_month_insurance-view')){
                                $return .= '<a href="'.route('last_month_insurance.view', ['id' => base64_encode($data->id)]).'" class="btn btn-default btn-xs">
                                                <i class="fa fa-eye"></i>
                                            </a> &nbsp;';
                            }                               
                            $return .= '</div>';

                            return $return;
                        })

                     
                        ->editColumn('date_of_insurance', function ($data) {
                            if($data->date_of_insurance != null)
                                return date("d/m/Y",strtotime($data->date_of_insurance));
                            else
                                return '-';
                        })

                        ->editColumn('last_time_of_insurance', function ($data) {
                            if($data->last_time_of_insurance != null)
                                return date("d/m/Y",strtotime($data->last_time_of_insurance));
                            else
                                return '-';
                        })

                        ->rawColumns(['o_customer_name','policy_number', 'action' ,'date_of_insurance','last_time_of_insurance'])
                        ->make(true);
            }
            return view('last_month_insurance.index');
        }
    /** index */
     /** view */
        public function view(Request $request){
            if(auth()->user()->can('obf-view')){
                $id = base64_decode($request->id);               
                $path = URL('/uploads/kyc').'/';

                $sales = User::role('sales')->select('id' , DB::raw("CONCAT(first_name,' ',last_name) AS sales_person_name"))->where(['status' => 'active'])->get();
                $product = Product::select('id' , 'name' ,'veriant')->where(['status' => 'active'])->get();
                $tax_1 = Tax::select('id' , 'name' ,'percentage')->where(['status' => 'active' ,'name' => 'registration_tax'])->first();
                $tax_2 = Tax::select('id' , 'name' ,'percentage')->where(['status' => 'active' ,'name' => 'municipal_tax'])->first();
                $tax_3 = Tax::select('id' , 'name' ,'percentage')->where(['status' => 'active' ,'name' => 'tcs_tax'])->first();
                $accessory = Accessory::select('id' , 'name' ,'price')->where(['status' => 'active'])->get();
                $extanded_warranty = ExtandWarranty::select('id' , 'years' ,'amount')->where(['status' => 'active'])->get();
                $fasttag = Fasttag::select('id', 'brand_name' ,'amount')->where(['status' => 'active'])->get();

                $branch = Branch::select('id' , 'name' ,'city')->where(['status' => 'active'])->get();
                $insurance = Insurance::select('id' , 'name')->where(['status' => 'active'])->get();
                $finance = Finance::select('finance.id' ,'finance.name' ,'branches.name AS branch_name' ,'branches.id AS branch_id')->leftjoin('branches' ,'finance.branch_id' ,'branches.id')->where(['finance.status' => 'active'])->get();
                $lead = Lead::select('id' ,'name')->where(['status' => 'active'])->get();
                $car_exchange = CarExchange::select('car_exchange.id' ,'car_exchange.price','cep.name AS product_name')->leftjoin('car_exchange_product AS cep' ,'car_exchange.product_id' ,'cep.id')->get();
                
                
                        
                $data = OBF::select('obf.id','obf.sales_person_id',
                                DB::raw("CONCAT(users.first_name,' ',users.last_name) AS sales_person_name"),'obf.temporary_id' ,'obf.booking_date' ,'obf.customer_name' ,'obf.customer_type' ,'obf.convinience_program' ,'branches.name AS branch_name' ,'branches.id AS branch_id' ,'obf.company_name','obf.fasttag_brand_name AS fasttag_brand_name' ,'obf.einvoicing_applicable', 'obf.gst' ,'obf.address' ,'obf.registration' ,'obf.email' ,'obf.pan_number','obf.adhar_number' ,'obf.licance_number','obf.contact_number' ,'obf.dob' ,'obf.nominee_name','obf.nominee_reletion' ,'obf.nominee_age' ,'obf.occupation','products.name AS product_name','obf.product_id' ,'products.veriant','products.is_applicable_for_mcp' ,'obf.ex_showroom_price' ,'registration_tax.percentage AS registration_tax' ,'insurance.name AS insurance' ,'obf.insurance_id' ,'municipal_tax.percentage AS municipal_tax' ,'tcs_tax.percentage AS tcs_tax' ,'accessories.name AS accessory_name' ,'obf.accessory_id','obf.extanded_warranty_id' ,'extand_warranties.years AS extand_warranties_years' ,'extand_warranties.amount AS extand_warranties_amount' ,'fasttags.id AS fasttag_id' ,'fasttags.amount AS fasttag_amount' ,'fasttags.brand_name AS brand_name','obf.trade_car_name', 'obf.trade_car_value', 'obf.on_road_price' ,'obf.on_road_price_word' ,'obf.on_road_price_word','obf.finance_id' ,'finance.name AS finance_name' ,'finance_branch.name AS finance_branch_name' ,'lead.name AS lead_name','obf.lead_id' ,'obf.booking_amount' ,'obf.mode_of_payment' ,'obf.status' ,'obf.created_by' ,'obf.updated_by','obf.created_at' ,'obf.updated_at',
                                'obf_insurance.ncb','obf_insurance.documents','obf_insurance.date_of_insurance','obf_insurance.last_time_of_insurance','obf_insurance.time_of_insurance','obf_insurance.company_name','obf_insurance.ncb_amount','obf_insurance.policy_number','obf_insurance.status as insurance_status',
                'obf_rto.rto_type','obf_rto.registration_date','obf_rto.application_number','obf_rto.temporary_number','obf_rto.final_amount','obf_rto.vehicle_number','obf_rto.status as rto_status',

                'obf_fasttag.fasttag_number','obf_fasttag.fasttag_brand','obf_fasttag.date_of_issue','obf_fasttag.fasttag_amount','obf_fasttag.opening_stock','obf_fasttag.status as fasttag_status','obf.other_documents','obf_insurance.documents','obf.pan_image','obf.adhar_image','obf.licance_image','obf.other_details_1','obf.other_details_2','obf.priclist_detail','obf.inventory_id',

                'obf_finance.finance_name','obf_finance.repo_rate','obf_finance.net_disbusment','obf_finance.loan_amount','obf_finance.processing_fee','obf_finance.finance_discount','obf_finance.payout','obf_finance.pay_booster','obf_finance.finance_required','obf_insurance.insurance_exist'

                               /* DB::Raw("CASE
                                            WHEN ".'obf_insurance.documents'." != '' THEN CONCAT("."'".$path."'".", ".'obf_insurance.documents'.")
                                            ELSE CONCAT("."'".$path."'".", 'default.jpg')
                                        END as documents"),
                                DB::Raw("CASE
                                            WHEN ".'pan_image'." != '' THEN CONCAT("."'".$path."'".", ".'pan_image'.")
                                            ELSE CONCAT("."'".$path."'".", 'default.jpg')
                                        END as pan_image"),
                                DB::Raw("CASE
                                            WHEN ".'adhar_image'." != '' THEN CONCAT("."'".$path."'".", ".'adhar_image'.")
                                            ELSE CONCAT("."'".$path."'".", 'default.jpg')
                                        END as adhar_image"),
                                DB::Raw("CASE
                                            WHEN ".'licance_image'." != '' THEN CONCAT("."'".$path."'".", ".'licance_image'.")
                                            ELSE CONCAT("."'".$path."'".", 'default.jpg')
                                        END as licance_image"),*/
                                )
                            ->leftjoin('users' ,'obf.sales_person_id' ,'users.id')
                            ->leftjoin('branches' ,'obf.branch_id' ,'branches.id')
                            ->leftjoin('products' ,'obf.product_id' ,'products.id')
                            ->leftjoin('taxes AS registration_tax' ,'obf.registration_tax_id' ,'registration_tax.id')
                            ->leftjoin('taxes AS municipal_tax' ,'obf.municipal_tax_id' ,'municipal_tax.id')
                            ->leftjoin('taxes AS tcs_tax' ,'obf.tcs_tax_id' ,'tcs_tax.id')
                            ->leftjoin('insurance' ,'obf.insurance_id' ,'insurance.id')
                            ->leftjoin('extand_warranties' ,'obf.extanded_warranty_id' ,'extand_warranties.id')
                            ->leftjoin('fasttags' ,'obf.fasttag_brand_name' ,'fasttags.id')
                            ->leftjoin('finance' ,'obf.finance_id' ,'finance.id')
                            ->leftjoin('branches AS finance_branch' ,'obf.finance_branch_id' ,'finance_branch.id')
                            ->leftjoin('lead' ,'obf.lead_id' ,'lead.id')
                            ->leftjoin('obf_insurance' ,'obf.id' ,'obf_insurance.obf_id')
                            ->leftjoin('obf_rto' ,'obf.id' ,'obf_rto.obf_id')
                            ->leftjoin('obf_fasttag' ,'obf.id' ,'obf_fasttag.obf_id')
                            ->leftjoin('obf_finance' ,'obf.id' ,'obf_finance.obf_id')
                            ->leftjoin('accessories' ,'obf.accessory_id' ,'accessories.id')
                            ->leftjoin('other_one' ,'obf.other_details_1' ,'other_one.id')
                            ->leftjoin('other_two' ,'obf.other_details_2' ,'other_two.id')
                            ->leftjoin('mcp' ,'mcp.id' ,'obf.convinience_program')
                            ->where(['obf_insurance.id' => $id])
                            ->first();      
            }



             $carmodel = Product::select('car_model.name as car_name','products.id as car_id','car_varient.name as varient_name','exteriorcolor.name as exterior_color', 'interiorcolor.name as interior_color')
                    ->leftjoin('car_model', 'car_model.id', 'products.car_model')
                    ->leftjoin('car_varient', 'car_varient.id', 'products.veriant')
                    ->leftjoin('interiorcolor', 'interiorcolor.id', 'products.interior_color')
                    ->leftjoin('exteriorcolor', 'exteriorcolor.id', 'products.exterior_color')
                    ->where('products.status', '=', 'active')
                    ->get();

                    // dd($carmodel);


        $Cardata = Obf::select('obf.id as o_id','obf.product_id as o_product','car_model.name as car_name','car_model.id as car_id','car_varient.name as varient_name','exteriorcolor.name as exterior_color', 'interiorcolor.name as interior_color')
                            ->leftJoin('car_model','car_model.id','=','obf.product_id')
                            ->leftJoin('car_varient','car_varient.car_model','=','car_model.id')
                            ->leftJoin('exteriorcolor','exteriorcolor.id','=','car_varient.exterior_color')
                            ->leftJoin('interiorcolor','interiorcolor.id','=','car_varient.interior_color')
                            ->where('obf.id', '=', $id)  
                            ->first();
         $mcp = Mcp::select('id' ,'name')->where(['status' => 'active'])->get();

         $OtherOne = OtherOne::where(['status' => 'active'])->get();  
            
         $OtherTwo = OtherTwo::where(['status' => 'active'])->get(); 

         $Inventory_data = [];
         if($data->inventory_id != NULL){
             $Inventory_data = Inventory::where('id', '=', $data->inventory_id)->first();    
         }


            return view('last_month_insurance.view')->with(['data' =>$data,'sales' => $sales ,'product' => $product ,'taxOne' => $tax_1,'taxTwo' => $tax_2 ,'taxThree' => $tax_3,'accessory' => $accessory ,'extanded_warranty' => $extanded_warranty ,'fasttag' => $fasttag ,'branch' => $branch ,'insurance' => $insurance ,'car_exchange' => $car_exchange ,'carmodel' => $carmodel ,'Cardata' => $Cardata, 'finance' => $finance ,'lead' => $lead, 'OtherOne' => $OtherOne, 'mcp' => $mcp, 'OtherTwo' => $OtherTwo,'Inventory_data' => $Inventory_data]);
        }
    /** view */
   /** Edit */
        /*public function edit(Request $request){
            $id = base64_decode($request->id);
            $data = TemplateMaster::where(['id' => $id])->first();
            return view('last_month_insurance.edit')->with(['data' => $data]);
        }*/
    /** Edit */

    /** update */
        /*public function update(Request $request)
        {   
            if($request->ajax()){ 
                return true ;
            }

            $crud = [
                'template_name' => ucfirst($request->temp_text),
                'template_values' => ucfirst($request->temp_desc),
                'updated_at' => date('Y-m-d H:i:s'),
                'updated_by' => auth()->user()->id
            ];
            
            
            DB::beginTransaction();
            try {
                DB::enableQueryLog();
                $update = TemplateMaster::where(['id' => $request->id])->update($crud);
                if ($update) {
                    
                    DB::commit();
                    return redirect()->route('template_master_category')->with('success', 'Record updated successfully');
                } else {
                    DB::rollback();
                    return redirect()->back()->with('error', 'Failed to update record')->withInput();
                }
            } catch (\Throwable $th) {
                DB::rollback();
                return redirect()->back()->with('error', 'Something went wrong, please try again later')->withInput();
            }
        }*/
    /** update */

    /** change-status */
        public function change_status(Request $request){
            if (!$request->ajax()) { exit('No direct script access allowed'); }

            $id = base64_decode($request->id);
            $data = TemplateMaster::where(['id' => $id])->first();

            if(!empty($data)){
                $update = TemplateMaster::where(['id' => $id])->update(['status' => $request->status, 'updated_at' => date('Y-m-d H:i:s'), 'updated_by' => auth('sanctum')->user()->id]);
                if($update){
                    return response()->json(['code' => 200]);
                }else{
                    return response()->json(['code' => 201]);
                }
            }else{
                return response()->json(['code' => 201]);
            }
        }
    /** change-status */
}
