<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use DB;

class Pricelist extends Model{
    use HasFactory;
    protected $table = 'pricelist_master';
    
    protected $fillable = [
        'rto_type',
        'car_model', 
        'car_variant',
        'fule_type',
        'colour_type',
        'branch_id',
        'transmission',
        'ex_showroom_price',
        'tcs',
        'statutory_charges',
        'registration_individual_charges',
        'registration_corporate_charges',
        'insurance',
        'fasttag',
        'on_road_individual_charges',
        'on_road_corporate_charges',
        'extended_warranty',
        'convinience_program',
        'other_details_1',
        'other_details_2',
        'rsa_master',
        'accessories_kit',
        'total_road_individual_charges',
        'total_road_corporate_charges',
        'created_at',
        'detail_value',
        'price_list_name',
        'basic',
        'gst_percentage',
        'gst_amount',
        'cess_percentage',
        'cess_amount',
        'on_road_price_individual',
        'on_road_price_corporate',
        'status',
        'is_csd',
        'rto_custome_handling_charges',
        'csd_selling_price',
        'ls_order_amount'
    ];

    // public function export($slug){
    //     $collection = DB::table('pricelist_master as pp')

    //                     ->select('price_list_name', 'cm.name as cm_name', 'car_varient.name as car_varient_name', 'fule_type.title as fule_type_name', 'colortone.name as color_tone_name', 'branches.name as branches_name', 'transmission.name as transmission_name', 'basic', 'pp.gst_percentage', 'gst_amount', 'cess_percentage', 'cess_amount', 'ex_showroom_price', 'tcs', 'statutory_charges', 'registration_individual_charges', 'registration_corporate_charges', 'insurance', DB::raw('CONCAT(fasttags.brand_name, \' - \', fasttags.amount) as fasttags_name'), 'on_road_individual_charges', 'on_road_corporate_charges', DB::raw('CONCAT(rsa.amount, \' - \', rsa.name) as rsa_name'), DB::raw('CONCAT(extand_warranties.years, \' - \', extand_warranties.amount) as ex_amount'), DB::raw('CONCAT(mcp.name, \' - \', mcp.amount) as convinience_program_name'), 'on_road_price_individual','on_road_price_corporate', 'other_details_1', 'other_details_2', 'detail_value')

    //                     //'car_model', 'car_variant', 'colour_type', ,'product_id',
    //                     ->leftjoin('car_model as cm', 'cm.id', 'pp.car_model')
    //                     ->leftjoin('car_varient as car_varient', 'car_varient.id', 'pp.car_variant')
    //                     ->leftjoin('fule_type' ,'fule_type.id' ,'car_varient.fule_type')
    //                     ->leftjoin('colortone' ,'pp.colour_type' ,'colortone.id')
    //                     ->leftjoin('branches' ,'pp.branch_id' ,'branches.id')
    //                     ->leftjoin('transmission' ,'pp.transmission' ,'transmission.id')
    //                     ->leftjoin('fasttags' ,'pp.fasttag' ,'fasttags.id')
    //                     ->leftjoin('rsa' ,'pp.rsa_master' ,'rsa.id')
    //                     ->leftjoin('extand_warranties' ,'pp.extended_warranty' ,'extand_warranties.id')
    //                     ->leftjoin('mcp' ,'pp.convinience_program' ,'mcp.id');
    //                     //other_one_name as 
        
        
    //     $data = $collection->get();

    //     foreach ($data as $value) {        

    //         $value->other_details_1 = explode(',', $value->other_details_1);
    //         $data_new = DB::table('other_one')->select(DB::raw('group_concat(name) as names'))->whereIn('id',$value->other_details_1)->get();
    //         $other_details_1_set = '';
    //         if(!empty($data_new)){
    //             $other_details_1_set = $data_new[0]->names;
    //         }
    //         $value->other_details_1_name = $other_details_1_set;
    //         unset($value->other_details_1);  


    //         $value->other_details_2 = explode(',', $value->other_details_2);
    //         $data_new = DB::table('other_two')->select(DB::raw('group_concat(name) as names'))->whereIn('id',$value->other_details_2)->get();
    //         $other_details_2_set = '';
    //         if(!empty($data_new)){
    //             $other_details_2_set = $data_new[0]->names;
    //         }
    //         $value->other_details_2_name = $other_details_2_set;
    //         unset($value->other_details_2);

    //         $value->detail_value = json_decode($value->detail_value);

    //         $detail_value_new = '';
    //         if(!empty($value->detail_value)){
    //             foreach ($value->detail_value as $value_new) {
    //                 $detail_value_new .= 'Detail-'.$value_new->detail_field.' and Value-'.$value_new->value_field.', ';
    //             }
    //         }

    //         $value->detail_value_new = $detail_value_new;
    //         unset($value->detail_value);

    //       /* echo "<pre>";
    //         print_r($value->detail_value);
    //         var_dump($detail_value_new);*/
    //         // die('stop');

    //     }

    //     /*echo "<pre>";
    //     print_r($data);
    //     die('stop');*/
        
    //     if($data->isNotEmpty()){
    //         return $data;
    //     }else{
    //         return null;
    //     }
    // }
    
    public function export($slug){
        $collection = DB::table('pricelist_master as pp')

                        ->select('price_list_name',
                                 'is_csd',
                                 'rto_type',
                                 'cm.name as cm_name',
                                 'fule_type.title as fule_type_name', 
                                 'car_varient.name as car_varient_name',
                                 'rto_custome_handling_charges', 
                                 'colortone.name as color_tone_name', 
                                  DB::raw("(SELECT GROUP_CONCAT(name SEPARATOR ', ') FROM branches WHERE FIND_IN_SET(branches.id, pp.branch_id)) as branches_name"),
                                //  'branches.name as branches_name', 
                                 'transmission.name as transmission_name', 
                                 'basic', 
                                 'csd_selling_price',
                                 'ls_order_amount',
                                 'pp.gst_percentage', 
                                 'gst_amount', 
                                 'cess_percentage', 
                                 'cess_amount', 
                                 'ex_showroom_price', 
                                 'tcs', 
                                 'registration_individual_charges', 
                                 'registration_corporate_charges', 
                                 'insurance', 
                                 DB::raw('CONCAT(fasttags.brand_name, \' - \', fasttags.amount) as fasttags_name'), 
                                 'on_road_individual_charges', 
                                 'on_road_corporate_charges', 
                                 DB::raw('CONCAT(rsa.amount, \' - \', rsa.name) as rsa_name'), 
                                 DB::raw('CONCAT(extand_warranties.years, \' - \', extand_warranties.amount) as ex_amount'), 
                                 DB::raw('CONCAT(mcp.name, \' - \', mcp.amount) as convinience_program_name'), 
                                 'on_road_price_individual',
                                 'on_road_price_corporate', 
                                 'other_details_1', 
                                 'other_details_2', 
                                 'detail_value')
                        ->leftjoin('car_model as cm', 'cm.id', 'pp.car_model')
                        ->leftjoin('car_varient as car_varient', 'car_varient.id', 'pp.car_variant')
                        ->leftjoin('fule_type' ,'fule_type.id' ,'car_varient.fule_type')
                        ->leftjoin('colortone' ,'pp.colour_type' ,'colortone.id')
                        // ->leftjoin('branches' ,'pp.branch_id' ,'branches.id')
                        ->leftjoin('transmission' ,'pp.transmission' ,'transmission.id')
                        ->leftjoin('fasttags' ,'pp.fasttag' ,'fasttags.id')
                        ->leftjoin('rsa' ,'pp.rsa_master' ,'rsa.id')
                        ->leftjoin('extand_warranties' ,'pp.extended_warranty' ,'extand_warranties.id')
                        ->leftjoin('mcp' ,'pp.convinience_program' ,'mcp.id');
                         
        
        
        $data = $collection->get();

        foreach ($data as $value) {        

            $value->other_details_1 = explode(',', $value->other_details_1);
            $data_new = DB::table('other_one')->select(DB::raw('group_concat(name) as names'))->whereIn('id',$value->other_details_1)->get();
            $other_details_1_set = '';
            if(!empty($data_new)){
                $other_details_1_set = $data_new[0]->names;
            }
            $value->other_details_1_name = $other_details_1_set;
            unset($value->other_details_1);  

            $value->other_details_2 = explode(',', $value->other_details_2);
            $data_new = DB::table('other_two')->select(DB::raw('group_concat(name) as names'))->whereIn('id',$value->other_details_2)->get();
            $other_details_2_set = '';
            if(!empty($data_new)){
                $other_details_2_set = $data_new[0]->names;
            }
            $value->other_details_2_name = $other_details_2_set;
            unset($value->other_details_2);

            $value->detail_value = json_decode($value->detail_value);

            $detail_value_new = '';
            if(!empty($value->detail_value)){
                foreach ($value->detail_value as $value_new) {
                    $detail_value_new .= 'Detail-'.$value_new->detail_field.' and Value-'.$value_new->value_field.', ';
                }
            }

            $value->detail_value_new = $detail_value_new;
            unset($value->detail_value);
        }
        
        if($data->isNotEmpty()){
            return $data;
        }else{
            return null;
        }
    }
}
