', print_r($data),  print_r($response), '

'; print_r($response);die;
                foreach($getData as $key => $value){
                    foreach ($response as $rkkey=> $rkvalue) {
                        if($value->f_date == $rkkey){
                            foreach ($rkvalue['data'] as $kskey => $ksvalue) {
                                if($ksvalue['name']==$value->division){
                                    $response[$rkkey]['data'][$kskey]['value']++;
                                }
                            }
                        }
                    }
                }

            }
        }elseif($data['type_wise']=="load-station"){
            // set response arr of api
            foreach($response as $response_k =>$v){
                foreach($getData as $kd => $val){
                    if($kd > 0){
                        $count=0;
                        foreach ($response[$response_k]['data'] as $unikey => $univalue) {
                            if ($val->load_stn==$univalue['name']){
                                $count++;
                            }
                        }
                        if($count==0){
                            $response[$response_k]['data'][$kd]['name']=$val->load_stn;
                            $response[$response_k]['data'][$kd]['value']=0;
                        }
                    }else{
                        $response[$response_k]['data'][$kd]['name']=$val->load_stn;
                        $response[$response_k]['data'][$kd]['value']=0;
                    }
                    
                }
            }
            // print_r($response);die;
            // set response of api
            if($data['date_formate']=="yearly"){
                foreach($getData as $key => $value){
                    foreach ($response as $rkkey=> $rkvalue) {
                        if($value->f_year == $rkkey){
                            foreach ($rkvalue['data'] as $kskey => $ksvalue) {
                                if($ksvalue['name']==$value->load_stn){
                                    $response[$rkkey]['data'][$kskey]['value']++;
                                }
                            }
                        }
                    }
                }
            }elseif($data['date_formate']=="quaterly"){
                foreach($getData as $key => $value){
                    foreach ($response as $rkkey=> $rkvalue) {
                        if($value->qtrYear == $rkkey){
                            foreach ($rkvalue['data'] as $kskey => $ksvalue) {
                                if($ksvalue['name']==$value->load_stn){
                                    $response[$rkkey]['data'][$kskey]['value']++;
                                }
                            }
                        }
                    }
                }
            }elseif($data['date_formate']=="monthly"){
                foreach($getData as $key => $value){
                    $m = date("M",strtotime($value->f_month)).'-'.$value->f_year;
                    foreach ($response as $rkkey=> $rkvalue) {
                        if($m == $rkkey){
                            foreach ($rkvalue['data'] as $kskey => $ksvalue) {
                                if($ksvalue['name']==$value->load_stn){
                                    $response[$rkkey]['data'][$kskey]['value']++;
                                }
                            }
                        }
                    }
                }
            }elseif($data['date_formate']=="date"){
                foreach($getData as $key => $value){
                    foreach ($response as $rkkey=> $rkvalue) {
                        if($value->f_date == $rkkey){
                            foreach ($rkvalue['data'] as $kskey => $ksvalue) {
                                if($ksvalue['name']==$value->load_stn){
                                    $response[$rkkey]['data'][$kskey]['value']++;
                                }
                            }
                        }
                    }
                }
            }
        }elseif($data['type_wise']=="unload-station"){
            // set response arr of api
            foreach($response as $response_k =>$v){
                foreach($getData as $kd => $val){
                    if($kd > 0){
                        $count=0;
                        foreach ($response[$response_k]['data'] as $unikey => $univalue) {
                            if ($val->unload_stn==$univalue['name']){
                                $count++;
                            }
                        }
                        if($count==0){
                            $response[$response_k]['data'][$kd]['name']=$val->unload_stn;
                            $response[$response_k]['data'][$kd]['value']=0;
                        }
                    }else{
                        $response[$response_k]['data'][$kd]['name']=$val->unload_stn;
                        $response[$response_k]['data'][$kd]['value']=0;
                    }
                    
                }
            }
            // set response of api
            if($data['date_formate']=="yearly"){
                foreach($getData as $key => $value){
                    foreach ($response as $rkkey=> $rkvalue) {
                        if($value->f_year == $rkkey){
                            foreach ($rkvalue['data'] as $kskey => $ksvalue) {
                                if($ksvalue['name']==$value->unload_stn){
                                    $response[$rkkey]['data'][$kskey]['value']++;
                                }
                            }
                        }
                    }
                }
            }elseif($data['date_formate']=="quaterly"){
                foreach($getData as $key => $value){
                    foreach ($response as $rkkey=> $rkvalue) {
                        if($value->qtrYear == $rkkey){
                            foreach ($rkvalue['data'] as $kskey => $ksvalue) {
                                if($ksvalue['name']==$value->unload_stn){
                                    $response[$rkkey]['data'][$kskey]['value']++;
                                }
                            }
                        }
                    }
                }
            }elseif($data['date_formate']=="monthly"){
                foreach($getData as $key => $value){
                    foreach ($response as $rkkey=> $rkvalue) {
                        $m = date("M",strtotime($value->f_month)).'-'.$value->f_year;
                        if($m == $rkkey){
                            foreach ($rkvalue['data'] as $kskey => $ksvalue) {
                                if($ksvalue['name']==$value->unload_stn){
                                    $response[$rkkey]['data'][$kskey]['value']++;
                                }
                            }
                        }
                    }
                }
            }elseif($data['date_formate']=="date"){
                foreach($getData as $key => $value){
                    foreach ($response as $rkkey=> $rkvalue) {
                        if($value->f_date == $rkkey){
                            foreach ($rkvalue['data'] as $kskey => $ksvalue) {
                                if($ksvalue['name']==$value->unload_stn){
                                    $response[$rkkey]['data'][$kskey]['value']++;
                                }
                            }
                        }
                    }
                }
            }
        }elseif($data['type_wise']=="consignor"){
            // set response arr of api
            foreach($response as $response_k =>$v){
                foreach($getData as $kd => $val){
                    if($kd > 0){
                        $count=0;
                        foreach ($response[$response_k]['data'] as $unikey => $univalue) {
                            if ($val->consignor==$univalue['name']){
                                $count++;
                            }
                        }
                        if($count==0){
                            $response[$response_k]['data'][$kd]['name']=$val->consignor;
                            $response[$response_k]['data'][$kd]['value']=0;
                        }
                    }else{
                        $response[$response_k]['data'][$kd]['name']=$val->consignor;
                        $response[$response_k]['data'][$kd]['value']=0;
                    }
                    
                }
            }
            // set response of api
            if($data['date_formate']=="yearly"){
                foreach($getData as $key => $value){
                    foreach ($response as $rkkey=> $rkvalue) {
                        if($value->f_year == $rkkey){
                            foreach ($rkvalue['data'] as $kskey => $ksvalue) {
                                if($ksvalue['name']==$value->consignor){
                                    $response[$rkkey]['data'][$kskey]['value']++;
                                }
                            }
                        }
                    }
                }
            }elseif($data['date_formate']=="quaterly"){
                foreach($getData as $key => $value){
                    foreach ($response as $rkkey=> $rkvalue) {
                        if($value->qtrYear == $rkkey){
                            foreach ($rkvalue['data'] as $kskey => $ksvalue) {
                                if($ksvalue['name']==$value->consignor){
                                    $response[$rkkey]['data'][$kskey]['value']++;
                                }
                            }
                        }
                    }
                }
            }elseif($data['date_formate']=="monthly"){
                foreach($getData as $key => $value){
                    foreach ($response as $rkkey=> $rkvalue) {
                        $m = date("M",strtotime($value->f_month)).'-'.$value->f_year;
                        if($m == $rkkey){
                            foreach ($rkvalue['data'] as $kskey => $ksvalue) {
                                if($ksvalue['name']==$value->consignor){
                                    $response[$rkkey]['data'][$kskey]['value']++;
                                }
                            }
                        }
                    }
                }
            }elseif($data['date_formate']=="date"){
                foreach($getData as $key => $value){
                    foreach ($response as $rkkey=> $rkvalue) {
                        if($value->f_date == $rkkey){
                            foreach ($rkvalue['data'] as $kskey => $ksvalue) {
                                if($ksvalue['name']==$value->consignor){
                                    $response[$rkkey]['data'][$kskey]['value']++;
                                }
                            }
                        }
                    }
                }
            }
        }elseif($data['type_wise']=="consignee"){
            // set response arr of api
            foreach($response as $response_k =>$v){
                foreach($getData as $kd => $val){
                    if($kd > 0){
                        $count=0;
                        foreach ($response[$response_k]['data'] as $unikey => $univalue) {
                            if ($val->consignee==$univalue['name']){
                                $count++;
                            }
                        }
                        if($count==0){
                            $response[$response_k]['data'][$kd]['name']=$val->consignee;
                            $response[$response_k]['data'][$kd]['value']=0;
                        }
                    }else{
                        $response[$response_k]['data'][$kd]['name']=$val->consignee;
                        $response[$response_k]['data'][$kd]['value']=0;
                    }
                    
                }
            }
            // set response of api
            if($data['date_formate']=="yearly"){
                foreach($getData as $key => $value){
                    foreach ($response as $rkkey=> $rkvalue) {
                        if($value->f_year == $rkkey){
                            foreach ($rkvalue['data'] as $kskey => $ksvalue) {
                                if($ksvalue['name']==$value->consignee){
                                    $response[$rkkey]['data'][$kskey]['value']++;
                                }
                            }
                        }
                    }
                }
            }elseif($data['date_formate']=="quaterly"){
                foreach($getData as $key => $value){
                    foreach ($response as $rkkey=> $rkvalue) {
                        if($value->qtrYear == $rkkey){
                            foreach ($rkvalue['data'] as $kskey => $ksvalue) {
                                if($ksvalue['name']==$value->consignee){
                                    $response[$rkkey]['data'][$kskey]['value']++;
                                }
                            }
                        }
                    }
                }
            }elseif($data['date_formate']=="monthly"){
                foreach($getData as $key => $value){
                    foreach ($response as $rkkey=> $rkvalue) {
                        $m = date("M",strtotime($value->f_month)).'-'.$value->f_year;
                        if($m == $rkkey){
                            foreach ($rkvalue['data'] as $kskey => $ksvalue) {
                                if($ksvalue['name']==$value->consignee){
                                    $response[$rkkey]['data'][$kskey]['value']++;
                                }
                            }
                        }
                    }
                }
            }elseif($data['date_formate']=="date"){
                foreach($getData as $key => $value){
                    foreach ($response as $rkkey=> $rkvalue) {
                        if($value->f_date == $rkkey){
                            foreach ($rkvalue['data'] as $kskey => $ksvalue) {
                                if($ksvalue['name']==$value->consignee){
                                    $response[$rkkey]['data'][$kskey]['value']++;
                                }
                            }
                        }
                    }
                }
            }
        }else{
            $response = $this->sailed_graph_response_parent_set($data);
            // set response of api
            if($data['date_formate']=="yearly"){
                foreach($getData as $value){
                    $response[$value->f_year]++;
                }
            }elseif($data['date_formate']=="quaterly"){
                foreach($getData as $value){
                    $response[$value->qtrYear]++;
                }
            }elseif($data['date_formate']=="monthly"){
                foreach($getData as $value){
                    $m = date("M",strtotime($value->f_month));
                    $response[$m.'-'.$value->f_year]++;
                }
            }elseif($data['date_formate']=="date"){
                foreach($getData as $value){
                    $response[$value->f_date]++;
                }
            }
        }
        return $response;
    }
    
    //new code for railway prog stack graphs
   /* public function railway_movement_graph_13042022_new(Request $request)
    {
        // get requested data from post
        $data = $request->only('date_formate','from_date','to_date','type_wise','commodity', 'country','division','consignor','consignee','load_siding','unload_siding');
        $validator = Validator::make($data, [
            'date_formate' => 'required',
            'from_date' => 'required',
            'to_date' => 'required',
            'commodity' => 'required',
            'country' => 'required'
        ]);
        
        //Send failed response if request is not valid
        if ($validator->fails()) {
            return response()->json(['status' => 401,'error' => $validator->messages()]);
        }

        if ($data['commodity']=='Iron Ore') {
            $data['commodity']='ORES';
        }
       
        $query='';
        $q1_s="";
        $q1_l="";
        $q2_s="";
        $q2_l="";
        $subquery="";
        $wherecount=0;
        
        $qtr_arr['from'] = array("Q1"=>1,"Q2"=>4,"Q3"=>7,"Q4"=>10);
        $qtr_arr['to'] = array("Q1"=>3,"Q2"=>6,"Q3"=>9,"Q4"=>12);
        if($data['type_wise'] !=""){
             $sel_type = array();
             $f_type  ='';
             $time_col = '';
             $group_by_col = '';
                if($data['type_wise'] =="division"){
                    $sel_type = "division";
                    $f_type  ='dvsn';
                }
                elseif($data['type_wise'] =="load-station"){
                    $sel_type ='load_stn';
                    $f_type  ='sttn_from_destn';
                }
                elseif($data['type_wise'] =="unload-station"){
                     $sel_type ='unload_stn';
                     $f_type  ='sttn_from_destn';
                }
                elseif($data['type_wise'] =="consignor"){
                    $sel_type="consignor";
                    $f_type  ='CNSR_CNSG';
                }
                elseif($data['type_wise'] =="consignee"){
                    $sel_type ="consignee";
                    $f_type  ='CNSR_CNSG';
                }
                
                $from_date='';
                $to_date ='';
                 if($data['date_formate']=="yearly"){
                    $from_date = $data['from_date']."-01-01";
                    $to_date = $data['to_date']."-12-31";
                    $time_col = 'f_year  as graph_x ,f_year as order_col';
                    $group_by_col = 'f_year';
                 }
                 elseif($data['date_formate']=="quaterly"){
                    $from_tmp = explode(" ",$data['from_date']);
                    $to_tmp = explode(" ",$data['to_date']);
                    $from_date = $from_tmp[1]."-".$qtr_arr['from'][$from_tmp[0]]."-01";
                    $to_date = date("Y-m-t",strtotime($to_tmp[1]."-".$qtr_arr['to'][$to_tmp[0]]."-01"));
                    $time_col='qtrYear as graph_x,qtrYear as order_col';
                    $group_by_col = 'qtrYear';
                 }
                 elseif($data['date_formate']=="monthly"){
                     $from_date = date("Y-m-01",strtotime($data['from_date']));
                     $to_date = date("Y-m-t",strtotime($data['to_date']));
                     $time_col = "concat(f_year,'-',f_month) as graph_x,concat(f_year,'-',f_month) as order_col";
                     $group_by_col = 'f_year,f_month';
                     
                 }
                 elseif($data['date_formate']=="date"){
                    $from_date = date("Y-m-d",strtotime($data['from_date']));
                    $to_date = date("Y-m-d",strtotime($data['to_date']));
                    $time_col = "f_date  as graph_x,f_date as order_col ";
                    $group_by_col = 'f_date';
                 }
            
            $q1_l=" group by $group_by_col, $sel_type )";
            $q2_l=" group by $group_by_col) order by 2,5,4 desc";
            $date_cond = "  `tbl_railway_programme`.`f_date` >= '$from_date' and `tbl_railway_programme`.`f_date` <= '$to_date'";
            
            $subquery .= "SELECT $sel_type, count(*) as cnt FROM `tbl_railway_programme` WHERE  $date_cond ";           
            $subquery.=" group by $sel_type order by cnt desc limit 0,11";
             
             
              $q1_s=" (SELECT $time_col,
                    (SELECT `f_full_form`
                    FROM `tbl_abbreviation`
                    WHERE `f_abbr` = tbl_railway_programme.$sel_type
                        AND `f_type` = '$f_type') as graph_y,
                        count(*) AS cnt,
                        '1' AS flag
                    FROM `tbl_railway_programme`
                    WHERE ". $date_cond ;
                $q2_s="
                        UNION
                        
                        (SELECT $time_col,
                            'others' AS graph_y,
                            count(*),
                            '2' AS flag
                        FROM `tbl_railway_programme`
                        WHERE ". $date_cond ;
                
                //getting in values
                
                $subquery= DB::select($subquery);
              //  print_r($subquery);
                $subwhere='"';
                for( $i = 0; $i < count($subquery); $i++ ){
                    if ($i == count($subquery)-1) {
                        $subwhere.=$subquery[$i][$sel_type].'"';
                    }else{
                        $subwhere.=$subquery[$i][$sel_type].'","';
                    }
                }
                //echo $subwhere;
                $q1_s.=" and $sel_type in (".$subwhere.") ";
                $q2_s.=" and $sel_type not in  (".$subwhere.") ";
        }  
        else{
            $query="SELECT  qtrYear,f_year,f_month,f_date FROM `tbl_railway_programme` WHERE";
        }

        if($data['division']!=""){
            $division = "'".str_replace(",","','",$data['division'])."'";
            if ($data['type_wise'] !="division") {
                $q1_s.=" and division in (".$division.")";
                $q2_s.=" and division in  (".$division.")";
            }
        }
        if($data['consignor']!=""){
            $consignor = "'".str_replace(",","','",$data['consignor'])."'";
            if ($data['type_wise'] !="consignor") {
                $q1_s.=" and consignor in (".$consignor.")";
                $q2_s.=" and consignor in  (".$consignor.")";
            }
        }
        if($data['consignee']!=""){
            $consignee = "'".str_replace(",","','",$data['consignee'])."'";
            if ($data['type_wise'] !="consignee") {
                $q1_s.=" and consignee in (".$consignee.")";
                $q2_s.=" and consignee in  (".$consignee.")";
            }
        }
        if($data['load_siding']!=""){
            $load_siding = "'".str_replace(",","','",$data['load_siding'])."'";
            if ($data['type_wise'] !="load-station") {
                $q1_s.=" and load_stn in (".$load_siding.")";
                $q2_s.=" and load_stn in  (".$load_siding.")";
            }
        }
        if($data['unload_siding']!=""){
            $unload_siding = "'".str_replace(",","','",$data['unload_siding'])."'";
            if ($data['type_wise'] !="unload-station") {
                $q1_s.=" and unload_stn in (".$unload_siding.")";
                $q2_s.=" and unload_stn in  (".$unload_siding.")";
            }
        }
        

        if($data['commodity']!="" && $data['commodity']!="All"){
            $q1_s.=" and commodity = '".$data['commodity']."'";
            $q2_s.=" and commodity = '".$data['commodity']."'";
        }
        if($data['country']!="" && $data['country']!="All"){
            $q1_s.=" and selected_country = '".$data['country']."'";
            $q2_s.=" and selected_country = '".$data['country']."'";
        }
        
        //echo $q1_s.$q1_l.$q2_s.$q2_l;
        
        $getData =  DB::select($q1_s.$q1_l.$q2_s.$q2_l);
        $response = $this->set_railway_graph_data_13042022_new($data,$getData);

        if($data['type_wise']!=""){
            //$newdata=[];
            $newdata = array();
            $c=0;
            foreach ($response as $key => $value) {
                if (isset($value) && !empty($value)) {
                    $newdata[$c]['name']=$key;
                    $newdata[$c]['data']=array_values($value);
                    $c++;
                }
            }
            $response=$newdata;
        }
        
        if(!empty($response)){
            $datas['graph_type']=  empty($data['type_wise']) ? 'Parent graph' : $data['type_wise'].' wise';
            $datas['graph_points']= $response;
            return response()->json(['status'=>1001,'message'=>'Data Found successfully','data'=>$datas],$this->successStatus);
        }else{
            return response()->json(['status'=>401,'message'=>'No Data Found'], 401);
        }
    }
    
    function set_railway_graph_data_13042022_new($data,$getData){
        for( $i = 0; $i < count($getData); $i++ ){
            if($data['date_formate']=="monthly")
                $m = date("M-Y",strtotime($getData[$i]['graph_x']));
            else
                $m = $getData[$i]['graph_x'];
            
            $response[$m][$i]['name']= $getData[$i]['graph_y'];
            $response[$m][$i]['value']= $getData[$i]['cnt'];
        }
        return $response;
    } 
    */
      public function railway_movement_graph_13042022_new_tarun(Request $request)
    {      
        // get requested data from post
        $data = $request->only('date_formate','from_date','to_date','type_wise','commodity', 'country','division','consignor','consignee','load_siding','unload_siding');
        $validator = Validator::make($data, [
            'date_formate' => 'required',
            'from_date' => 'required',
            'to_date' => 'required',
            'commodity' => 'required',
            'country' => 'required',
            //'type_wise' => 'required',

        ]);
        //Send failed response if request is not valid
        if ($validator->fails()) {
            return response()->json(['status' => 401,'error' => $validator->messages()]);
        }

//         if ($data['commodity']=='Iron Ore') {
//             $data['commodity']='ORES';
//         }
       
        $query='';
        $q1_s="";
        $q1_l="";
        $q2_s="";
        $q2_l="";
        $subquery="";
        $wherecount=0;
        
        $qtr_arr['from'] = array("Q1"=>1,"Q2"=>4,"Q3"=>7,"Q4"=>10);
        $qtr_arr['to'] = array("Q1"=>3,"Q2"=>6,"Q3"=>9,"Q4"=>12);
        
        $from_date='';
        $to_date ='';
        $time_col = '';
        $group_by_col = '';
        
        //making date condition based on passed input
        if($data['date_formate']=="yearly"){
            $from_date = $data['from_date']."-01-01";
            $to_date = $data['to_date']."-12-31";
            $time_col = 'f_year  as graph_x ,f_year as order_col';
            $group_by_col = 'f_year';
        }
        elseif($data['date_formate']=="quaterly"){
            $from_tmp = explode(" ",$data['from_date']);
            $to_tmp = explode(" ",$data['to_date']);
            $from_date = $from_tmp[1]."-".$qtr_arr['from'][$from_tmp[0]]."-01";
            $to_date = date("Y-m-t",strtotime($to_tmp[1]."-".$qtr_arr['to'][$to_tmp[0]]."-01"));
            $time_col='qtrYear as graph_x,qtrYear as order_col';
            $group_by_col = 'qtrYear';
        }
        elseif($data['date_formate']=="monthly"){
            $from_date = date("Y-m-01",strtotime($data['from_date']));
            $to_date = date("Y-m-t",strtotime($data['to_date']));
            $time_col = "concat(f_year,'-',f_month) as graph_x,concat(f_year,'-',f_month) as order_col";
            $group_by_col = 'f_year,f_month';
            
        }
        elseif($data['date_formate']=="date"){
            $from_date = date("Y-m-d",strtotime($data['from_date']));
            $to_date = date("Y-m-d",strtotime($data['to_date']));
            $time_col = "f_date  as graph_x,f_date as order_col ";
            $group_by_col = 'f_date';
        }
        $date_cond = " and `tbl_railway_programme`.`f_date` >= '$from_date' and `tbl_railway_programme`.`f_date` <= '$to_date'";  
        
        // making where condition on the base of value passed 
        $extra_filter ='';
        $extra_filter_subqry ='';        
        if($data['division']!=""){
            // $division= explode(',',$data['division']);
            $division = "'".str_replace(",","','",$data['division'])."'";
            if ($data['type_wise'] !="division") {
                $extra_filter.=" and division in (".$division.")";                
            }
            $extra_filter_subqry.=" and division in (".$division.")";     
        }
        if($data['consignor']!=""){
            // $consignor= explode(',',$data['consignor']);
            $consignor = "'".str_replace(",","','",$data['consignor'])."'";
            if ($data['type_wise'] !="consignor") {
               $extra_filter .=" and consignor in (".$consignor.")";                
            }
            $extra_filter_subqry .=" and consignor in (".$consignor.")";                
        }
        if($data['consignee']!=""){
            // $consignee= explode(',',$data['consignee']);
            $consignee = "'".str_replace(",","','",$data['consignee'])."'";
            if ($data['type_wise'] !="consignee") {
                $extra_filter .=" and consignee in (".$consignee.")";                
            }
            $extra_filter_subqry .=" and consignee in (".$consignee.")";                
        }
        if($data['load_siding']!=""){
            // $load_siding= explode(',',$data['load_siding']);
            $load_siding = "'".str_replace(",","','",$data['load_siding'])."'";
            if ($data['type_wise'] !="load-station") {
                $extra_filter.=" and load_stn in (".$load_siding.")";
            }
            $extra_filter_subqry.=" and load_stn in (".$load_siding.")";
        }
        if($data['unload_siding']!=""){
            // $unload_siding= explode(',',$data['unload_siding']);
            $unload_siding = "'".str_replace(",","','",$data['unload_siding'])."'";
            if ($data['type_wise'] !="unload-station") {
                $extra_filter.=" and unload_stn in (".$unload_siding.")";                
            }
            $extra_filter_subqry.=" and unload_stn in (".$unload_siding.")";                
        }
            
        if($data['type_wise'] !=""){
             $sel_type = array();
             $f_type  ='';
             
                if($data['type_wise'] =="division"){
                    $sel_type = "division";
                    $f_type  ='dvsn';
                }
                elseif($data['type_wise'] =="load-station"){
                    $sel_type ='load_stn';
                    $f_type  ='sttn_from_destn';
                }
                elseif($data['type_wise'] =="unload-station"){
                     $sel_type ='unload_stn';
                     $f_type  ='sttn_from_destn';
                }
                elseif($data['type_wise'] =="consignor"){
                    $sel_type="consignor";
                    $f_type  ='CNSR_CNSG';
                }
                elseif($data['type_wise'] =="consignee"){
                    $sel_type ="consignee";
                    $f_type  ='CNSR_CNSG';
                }
                
                
            $q1_l=" group by $group_by_col, graph_y )";
            $q2_l=" group by $group_by_col) order by 2,5,4 desc";
            
            
            $subquery = "
                        SELECT $sel_type,  
                                (
                                    SELECT `f_full_form`
                                    FROM `tbl_abbreviation`
                                    WHERE `f_abbr` = tbl_railway_programme.$sel_type
                                    AND     `f_type` = '$f_type'
                                ) as fullForm,  
                                count(*) as cnt
                        FROM `tbl_railway_programme` 
                        WHERE  1 
                                $date_cond $extra_filter_subqry
                    ";           
            $subquery.=" group by $sel_type,fullForm order by cnt desc limit 0,11";
            
             
              $q1_s=" (SELECT $time_col,
                    (SELECT `f_full_form`
                    FROM `tbl_abbreviation`
                    WHERE `f_abbr` = tbl_railway_programme.$sel_type
                        AND `f_type` = '$f_type') as graph_y,
                        count(*) AS cnt,
                        '1' AS flag,
                        $sel_type as abbrLegends
                    FROM `tbl_railway_programme`
                    WHERE 1 ". $date_cond ;
                $q2_s="
                        UNION
                        
                        (SELECT $time_col,
                            'Others' AS graph_y,
                            count(*),
                            '2' AS flag,
                            'others' as abbrLegends
                        FROM `tbl_railway_programme`
                        WHERE 1 ". $date_cond ;
                
                //getting in values
                
                $subquery= DB::select($subquery);
                $subwhere="'"; $abbrFullForm = array();
                for( $i = 0; $i < count($subquery); $i++ ){
                    if ($i == count($subquery)-1) {
                        $subwhere.=$subquery[$i][$sel_type]."'";
                    }else{
                        $subwhere.=$subquery[$i][$sel_type]."','";
                    }
                    $abbrFullForm[] = $subquery[$i]['fullForm'];
                }
                
                $q1_s.=" and $sel_type in (".$subwhere.") ";
                $q2_s.=" and $sel_type not in  (".$subwhere.") ";
               // $response = $this->sailed_graph_response_child_set($data);
                
                // where condition on the base of value passed
                if($data['commodity']!="" && $data['commodity']!="All"){
                    if($data['commodity']=='Iron Ore'){
                        $q1_s.=" and commodity in ('RMSP','ORES') ";
                        $q2_s.="  and commodity in ('RMSP','ORES') ";
                    }
                    else
                    {
                        $q1_s.=" and commodity = '".$data['commodity']."'";
                        $q2_s.=" and commodity = '".$data['commodity']."'";
                    }
                }
                if($data['country']!="" && $data['country']!="All"){
                    $q1_s.=" and selected_country = '".$data['country']."'";
                    $q2_s.=" and selected_country = '".$data['country']."'";
                }
                $q1_s.= $extra_filter;
                $q2_s.= $extra_filter;
                
                //echo $q1_s.$q1_l.$q2_s.$q2_l;
                //die;
                $getData =  DB::select($q1_s.$q1_l.$q2_s.$q2_l);
                // dd(DB::getQueryLog());
                
                $response = $this->set_railway_graph_data_13042022_new_tarun($data,$getData, $abbrFullForm);
                  //$newdata=[];
                //print_r($response['data']);
                $newdata = array();
                $c=0;
                /*foreach ($response['data'] as $key => $value) {
                    if (isset($value) && !empty($value)) {
                        $newdata[$c]['name']=$key;

                        $newdata[$c]['data']=array_values($value);
                        $c++;
                    }
                    
                } */ 
                
                
                //$response=$newdata;
        }  
        else{
               $q1_s=" SELECT $time_col,
                        count(*) AS cnt                      
                    FROM `tbl_railway_programme`
                    WHERE 1 ". $date_cond  ;                
              // where condition on the base of value passed
              $q1_s.= $extra_filter;
               if($data['commodity']!="" && $data['commodity']!="All"){
                    if($data['commodity']=='Iron Ore'){
                        $q1_s.=" and commodity in ('RMSP','ORES') ";                        
                    }
                    else 
                        $q1_s.=" and commodity = '".$data['commodity']."'";
                }
                if($data['country']!="" && $data['country']!="All"){
                    $q1_s.=" and selected_country = '".$data['country']."'";
                }
               $q1_s .= " group by " .$group_by_col.  " order by f_date ";
               $getData =  DB::select($q1_s);
              // print_r($getData);
               foreach($getData as $key => $value){
                if($data['date_formate']=="monthly")
                    $m = date("M-Y",strtotime($value[graph_x]));
                else
                    $m = $value[graph_x];
                $response[$m]=$value[cnt];
              }       
        
        }       
        if(!empty($response)){
            $datas['graph_type']=  empty($data['type_wise']) ? 'Parent graph' : $data['type_wise'].' wise';
            $datas['graph_points']= $response;
            //print_r($datas);
            return response()->json(['status'=>1001,'message'=>'Data Found successfully','data'=>$datas],$this->successStatus);
        }else{
            return response()->json(['status'=>401,'message'=>'No Data Found'], 401);
        }
    }
 
 
  
    function set_railway_graph_data_13042022_new_tarun($data,$getData, $abbrLegends){
        $dateKey = '';
        //$abbrLegends = explode(",", str_replace("'", "", $abbrLegends));
        $tempArrLegends = $abbrLegends;
        $finres = array();
        for( $i = 0; $i < count($getData); $i++ ){
            if($data['date_formate']=="monthly")
                $m = date("M-Y",strtotime($getData[$i]['graph_x']));
            else
                $m = $getData[$i]['graph_x'];
                
             $response[$m][]= array("name"=>$getData[$i]['graph_y'],"value"=>$getData[$i]['cnt']);
            if( $dateKey == $m ){
                
                $tempArrLegends = array_diff($tempArrLegends, array($getData[$i]['graph_y']));
                //$tempArrLegends
            } else {
                if( $i > 0 ){
                    $tempArrLegends = array_values($tempArrLegends);
                    
                    //date key has chnaged
                    if( count($tempArrLegends) > 0 ){
                        for( $i1 = 0; $i1 < count($tempArrLegends); $i1++ ){
                            $response[$dateKey][]= array("name"=>$tempArrLegends[$i1],"value"=>0);
                        }
                    }
                    $finres[] = array("name"=>$dateKey,"data"=>array_values($response[$dateKey]));
                    $response = array();
                }
                $dateKey = $m;
                $tempArrLegends = $abbrLegends;
            }
        }
        return $finres;
    }
    
    public function railway_movement_graph_13042022_new(Request $request)
    {      
        // get requested data from post
        $data = $request->only('date_formate','from_date','to_date','type_wise','commodity', 'country','division','consignor','consignee','load_siding','unload_siding');
        $validator = Validator::make($data, [
            'date_formate' => 'required',
            'from_date' => 'required',
            'to_date' => 'required',
            'commodity' => 'required',
            'country' => 'required',
            //'type_wise' => 'required',

        ]);
        //Send failed response if request is not valid
        if ($validator->fails()) {
            return response()->json(['status' => 401,'error' => $validator->messages()]);
        }

//         if ($data['commodity']=='Iron Ore') {
//             $data['commodity']='ORES';
//         }
       
        $query='';
        $q1_s="";
        $q1_l="";
        $q2_s="";
        $q2_l="";
        $subquery="";
        $wherecount=0;
        
        $qtr_arr['from'] = array("Q1"=>1,"Q2"=>4,"Q3"=>7,"Q4"=>10);
        $qtr_arr['to'] = array("Q1"=>3,"Q2"=>6,"Q3"=>9,"Q4"=>12);
        
        $from_date='';
        $to_date ='';
        $time_col = '';
        $group_by_col = '';
        
        //making date condition based on passed input
        if($data['date_formate']=="yearly"){
            $from_date = $data['from_date']."-01-01";
            $to_date = $data['to_date']."-12-31";
            $time_col = 'f_year  as graph_x ,f_year as order_col';
            $group_by_col = 'f_year';
        }
        elseif($data['date_formate']=="quaterly"){
            $from_tmp = explode(" ",$data['from_date']);
            $to_tmp = explode(" ",$data['to_date']);
            $from_date = $from_tmp[1]."-".$qtr_arr['from'][$from_tmp[0]]."-01";
            $to_date = date("Y-m-t",strtotime($to_tmp[1]."-".$qtr_arr['to'][$to_tmp[0]]."-01"));
            $time_col='qtrYear as graph_x,qtrYear as order_col';
            $group_by_col = 'qtrYear';
        }
        elseif($data['date_formate']=="monthly"){
            $from_date = date("Y-m-01",strtotime($data['from_date']));
            $to_date = date("Y-m-t",strtotime($data['to_date']));
            $time_col = "concat(f_year,'-',f_month) as graph_x,concat(f_year,'-',f_month) as order_col";
            $group_by_col = 'f_year,f_month';
            
        }
        elseif($data['date_formate']=="date"){
            $from_date = date("Y-m-d",strtotime($data['from_date']));
            $to_date = date("Y-m-d",strtotime($data['to_date']));
            $time_col = "f_date  as graph_x,f_date as order_col ";
            $group_by_col = 'f_date';
        }
        $date_cond = " and `tbl_railway_programme`.`f_date` >= '$from_date' and `tbl_railway_programme`.`f_date` <= '$to_date'";  
        
        // making where condition on the base of value passed 
        $extra_filter ='';
        $extra_filter_subqry ='';        
        if($data['division']!=""){
            // $division= explode(',',$data['division']);
            $division = "'".str_replace(",","','",$data['division'])."'";
            if ($data['type_wise'] !="division") {
                $extra_filter.=" and division in (".$division.")";                
            }
            $extra_filter_subqry.=" and division in (".$division.")";     
        }
        if($data['consignor']!=""){
            // $consignor= explode(',',$data['consignor']);
            $consignor = "'".str_replace(",","','",$data['consignor'])."'";
            if ($data['type_wise'] !="consignor") {
               $extra_filter .=" and consignor in (".$consignor.")";                
            }
            $extra_filter_subqry .=" and consignor in (".$consignor.")";                
        }
        if($data['consignee']!=""){
            // $consignee= explode(',',$data['consignee']);
            $consignee = "'".str_replace(",","','",$data['consignee'])."'";
            if ($data['type_wise'] !="consignee") {
                $extra_filter .=" and consignee in (".$consignee.")";                
            }
            $extra_filter_subqry .=" and consignee in (".$consignee.")";                
        }
        if($data['load_siding']!=""){
            // $load_siding= explode(',',$data['load_siding']);
            $load_siding = "'".str_replace(",","','",$data['load_siding'])."'";
            if ($data['type_wise'] !="load-station") {
                $extra_filter.=" and load_stn in (".$load_siding.")";
            }
            $extra_filter_subqry.=" and load_stn in (".$load_siding.")";
        }
        if($data['unload_siding']!=""){
            // $unload_siding= explode(',',$data['unload_siding']);
            $unload_siding = "'".str_replace(",","','",$data['unload_siding'])."'";
            if ($data['type_wise'] !="unload-station") {
                $extra_filter.=" and unload_stn in (".$unload_siding.")";                
            }
            $extra_filter_subqry.=" and unload_stn in (".$unload_siding.")";                
        }
            
        if($data['type_wise'] !=""){
             $sel_type = array();
             $f_type  ='';
             
                if($data['type_wise'] =="division"){
                    $sel_type = "division";
                    $f_type  ='dvsn';
                }
                elseif($data['type_wise'] =="load-station"){
                    $sel_type ='load_stn';
                    $f_type  ='sttn_from_destn';
                }
                elseif($data['type_wise'] =="unload-station"){
                     $sel_type ='unload_stn';
                     $f_type  ='sttn_from_destn';
                }
                elseif($data['type_wise'] =="consignor"){
                    $sel_type="consignor";
                    $f_type  ='CNSR_CNSG';
                }
                elseif($data['type_wise'] =="consignee"){
                    $sel_type ="consignee";
                    $f_type  ='CNSR_CNSG';
                }
                
                
            $q1_l=" group by $group_by_col, graph_y )";
            $q2_l=" group by $group_by_col) order by 2,5,4 desc";
            
            
            $subquery .= "SELECT $sel_type, count(*) as cnt FROM `tbl_railway_programme` WHERE  1 $date_cond $extra_filter_subqry";           
            $subquery.=" group by $sel_type order by cnt desc limit 0,11";
            
             
              $q1_s=" (SELECT $time_col,
                    (SELECT `f_full_form`
                    FROM `tbl_abbreviation`
                    WHERE `f_abbr` = tbl_railway_programme.$sel_type
                        AND `f_type` = '$f_type') as graph_y,
                        count(*) AS cnt,
                        '1' AS flag
                    FROM `tbl_railway_programme`
                    WHERE 1 ". $date_cond ;
                $q2_s="
                        UNION
                        
                        (SELECT $time_col,
                            'Others' AS graph_y,
                            count(*),
                            '2' AS flag
                        FROM `tbl_railway_programme`
                        WHERE 1 ". $date_cond ;
                
                //getting in values
                
                $subquery= DB::select($subquery);
                $subwhere="'";
                for( $i = 0; $i < count($subquery); $i++ ){
                    if ($i == count($subquery)-1) {
                        $subwhere.=$subquery[$i][$sel_type]."'";
                    }else{
                        $subwhere.=$subquery[$i][$sel_type]."','";
                    }
                }
                //print_r($subquery);
                if($subwhere=="'"){
                    $q1_s.=" and $sel_type in (NULL) ";
                    $q2_s.=" and $sel_type in  (NULL) ";
                }
                else{
                    $q1_s.=" and $sel_type in (".$subwhere.") ";
                    $q2_s.=" and $sel_type not in  (".$subwhere.") ";
                }
//                 $q1_s.=" and $sel_type in (".$subwhere.") ";
//                 $q2_s.=" and $sel_type not in  (".$subwhere.") ";
               // $response = $this->sailed_graph_response_child_set($data);
                
                // where condition on the base of value passed
                if($data['commodity']!="" && $data['commodity']!="All"){
                    if($data['commodity']=='Iron Ore'){
                        $q1_s.=" and commodity in ('RMSP','ORES') ";
                        $q2_s.="  and commodity in ('RMSP','ORES') ";
                    }
                    else
                    {
                        $q1_s.=" and commodity = '".$data['commodity']."'";
                        $q2_s.=" and commodity = '".$data['commodity']."'";
                    }
                }
                if($data['country']!="" && $data['country']!="All"){
                    $q1_s.=" and selected_country = '".$data['country']."'";
                    $q2_s.=" and selected_country = '".$data['country']."'";
                }
                $q1_s.= $extra_filter;
                $q2_s.= $extra_filter;
                
                //echo $q1_s.$q1_l.$q2_s.$q2_l;
                //die;
                $getData =  DB::select($q1_s.$q1_l.$q2_s.$q2_l);
                // dd(DB::getQueryLog());
                
                $response = $this->set_railway_graph_data_13042022_new($data,$getData);
                  //$newdata=[];
                //print_r($response['data']);
                $newdata = array();
                $c=0;
                if(isset($response['data']) && !empty($response['data'])){
                    foreach ($response['data'] as $key => $value) {
                        if (isset($value) && !empty($value)) {
                            $newdata[$c]['name']=$key;
                            //if($c==0)
                            {   
                                $value_new=array();
                                $array_new=array();
                                $array_new = array_column($value, 'name');                         
                                $diff = array_diff($response['unique_col'],$array_new);
                                foreach($diff as $diff_val){
                                    $value_new[] = array("name"=>$diff_val,"value"=>0);
                                }
                                if(!empty($value_new))
                                    $value=array_merge($value,$value_new);
                                $newdata[$c]['data']=array_values($value);
                            }
                            //else
                            // $newdata[$c]['data']=array_values($value);                       
                        
                            $c++;
                        }
                        
                    } 
                }
              //  print_r($newdata);
                $response=$newdata;
        }  
        else{
               $q1_s=" SELECT $time_col,
                        count(*) AS cnt                      
                    FROM `tbl_railway_programme`
                    WHERE 1 ". $date_cond  ;                
              // where condition on the base of value passed
              $q1_s.= $extra_filter;
               if($data['commodity']!="" && $data['commodity']!="All"){
                    if($data['commodity']=='Iron Ore'){
                        $q1_s.=" and commodity in ('RMSP','ORES') ";                        
                    }
                    else 
                        $q1_s.=" and commodity = '".$data['commodity']."'";
                }
                if($data['country']!="" && $data['country']!="All"){
                    $q1_s.=" and selected_country = '".$data['country']."'";
                }
               $q1_s .= " group by " .$group_by_col.  " order by f_date ";
               $getData =  DB::select($q1_s);
              // print_r($getData);
               foreach($getData as $key => $value){
                if($data['date_formate']=="monthly")
                    $m = date("M-Y",strtotime($value[graph_x]));
                else
                    $m = $value[graph_x];
                $response[$m]=$value[cnt];
              }       
        
        }       
        if(!empty($response)){
            $datas['graph_type']=  empty($data['type_wise']) ? 'Parent graph' : $data['type_wise'].' wise';
            $datas['graph_points']= $response;
            return response()->json(['status'=>1001,'message'=>'Data Found successfully','data'=>$datas],$this->successStatus);
        }else{
            return response()->json(['status'=>401,'message'=>'No Data Found'], 401);
        }
    }
    
    function set_railway_graph_data_13042022_new($data,$getData){
        /*$count=0;
       
        foreach($getData as $key => $value){
            if($data['date_formate']=="monthly")
                $m = date("M-Y",strtotime($value->graph_x));
            else
                $m = $value->graph_x;
            $response[$m][$count]['name']=$value->graph_y;
            $response[$m][$count]['value']=$value->cnt;
            $count++;
        }
        
        return $response;*/
        $final_data =array();
        if(!empty($getData)){
            $unique_col = array();
            for( $i = 0; $i < count($getData); $i++ ){
                if($data['date_formate']=="monthly")
                    $m = date("M-Y",strtotime($getData[$i]['graph_x']));
                else
                    $m = $getData[$i]['graph_x'];
                
                $response[$m][$i]['name']= $getData[$i]['graph_y'];
                $response[$m][$i]['value']= $getData[$i]['cnt'];
                
                $unique_col[$getData[$i]['graph_y']] = $getData[$i]['graph_y'];
            }
            $final_data['unique_col'] = array_unique($unique_col);
            $final_data['data'] = $response;
        }
        return $final_data;
    }
    
    
}