<?php

namespace App\Models;

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

class Carvarient extends Model{
    use HasFactory;
    protected $table = 'car_varient';
    
    protected $fillable = [
        'name',
        'car_model',
        'color_tone ',
        'exterior_color',
        'Interior_color',
        'fule_type',
        'transmission',
        'hsn_code',
        'tex_master_id',
        'status',
        'created_by',
        'created_at',
        'updated_by',
        'updated_at'
    ];

    public function export($slug){
        //$collection = DB::table('taxes')
                        // ->select('name', 'status');

        $collection = DB::table('car_varient as i')
        ->select('i.name','cm.name as car_model','ft.title as fule_type','tra.name as transmission_value','hsn_code','i.exterior_color','i.Interior_color','i.color_tone','i.status','tex.gst','tex.compensation_cess')
        ->leftjoin('car_model as cm', 'cm.id', 'i.car_model')
        ->leftjoin('fule_type as ft', 'ft.id', 'i.fule_type')
        ->leftjoin('transmission as tra', 'tra.id', 'i.transmission')
        ->leftjoin('tex_master as tex', 'tex.id', 'i.tex_master_id')
        ->orderBy('i.id', 'desc');

        //SELECT GROUP_CONCAT(name) FROM `exteriorcolor` WHERE id IN(5,6,8,10,12,16,17)

        if($slug != 'all')
            $collection->where(['i.status' => $slug]);
        
        $data = $collection->get();

        foreach ($data as $value) {
            $value->exterior_color = explode(',', $value->exterior_color);
            $exteriorcolor_data = DB::table('exteriorcolor')->select(DB::raw('group_concat(name) as names'))->whereIn('id',$value->exterior_color)->get();
            $exteriorcolor_get = '';
            if(!empty($exteriorcolor_data)){
                $exteriorcolor_get = $exteriorcolor_data[0]->names;
            }
            $value->exterior_color_name = $exteriorcolor_get;
            unset($value->exterior_color);

            $value->Interior_color = explode(',', $value->Interior_color);
            $Interior_color_data = DB::table('interiorcolor')->select(DB::raw('group_concat(name) as names'))->whereIn('id',$value->Interior_color)->get();
            $Interior_color_get = '';
            if(!empty($Interior_color_data)){
               $Interior_color_get = $Interior_color_data[0]->names;
            }
            $value->Interior_color_get = $Interior_color_get;
            unset($value->Interior_color);

            $value->color_tone = explode(',', $value->color_tone);
            $color_tone_data = DB::table('colortone')->select(DB::raw('group_concat(name) as names'))->whereIn('id',$value->color_tone)->get(); 
            $color_tone_get = '';
            if(!empty($color_tone_data)){
               $color_tone_get = $color_tone_data[0]->names;
            }
            $value->color_tone_get = $color_tone_get;
            unset($value->color_tone);   

            
            if($value->gst != ''){
                $tax = 'GST - '.$value->gst.' | Compensation Cess - '.$value->compensation_cess;
                $value->tax = $tax;    
            }else{
                $value->tax = '-';    
            }
            
            

            unset($value->compensation_cess); 
            unset($value->gst); 

            
            $status = $value->status;
            unset($value->status); 
            
            $value->status = $status;



        }

        /*echo "<pre>";
        print_r($data);
        die('s');*/
        
        if($data->isNotEmpty()){
            return $data;
        }else{
            return null;
        }
    }
}
