<?php

namespace App\Http\Controllers\Action;
use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use Input;
use Validator;
use DB;
use File;
use Session;
use App\Http\Controllers\Auth\User;
use App\Http\Controllers\Auth\authentication;

class StplCommonController extends Controller
{
    /**
     * Show the profile for the given user.
     *
     * @param  int  $id
     * @return Response
     */
    public function getDetaTypeName($attr, $siteName = 'SM',$selected_market){
    /* $data = DB::table('tbl_stats_datatype_header_list_bm')->where('requiredFor',$attr)->lists('datatypeName');*/
	//DB::enableQueryLog();
// 	if( $siteName == 'CM' ){
// 		$data = DB::table('tbl_stats_datatype_header_list_bm')
// 			->where('requiredFor',$attr)
// 			->where('datatypeName','LIKE', "%_not_reqd%")
// 			->distinct()
// 			->orderBy('f_orderBy')
// 			->get();
// 	}else{
// 		$data = DB::table('tbl_stats_datatype_header_list_bm')
// 			->where('requiredFor',$attr)
// 			->where('datatypeName','NOT LIKE', "%_not_reqd%")
// 			->where('datatypeName','NOT LIKE', "%dispatch%")
// 			->distinct()
// 			->orderBy('f_orderBy')
// 			->get();
// 	}



        $data = DB::table('tbl_stats_datatype_header_list_bm')
		->where('requiredFor',$attr);
		
		    //$data = $data ->whereRaw("FIND_IN_SET(?, f_market) > 0", [$selected_market])
        if($selected_market != '')
		    $data = $data->whereRaw("FIND_IN_SET('$selected_market', f_market)");
		//->where('datatypeName','NOT LIKE', "%_not_reqd%")
		//->where('datatypeName','NOT LIKE', "%dispatch%")
		 $data = $data->distinct()
		->orderBy('f_orderBy')
		->get();
    
    //  dd(DB::getQueryLog());
	 $data = json_decode(json_encode($data), true);
     if(count($data) > 0 ){
        $finalArray = array();
        foreach ($data as $key => $value) {
           $finalArray[$value['tableName']] = str_replace('_not_reqd','', $value['datatypeName']);
         }
        return $finalArray;  
     }else{
      return null;
     }
    }
	
	public function getStatsHeaderList($attr, $siteName = 'SM'){
	   // echo "<pre>";
	   // print_r($attr);
		$resourceType = $attr['resourceType'];
		$dataType = isset($attr['dataType'])?$attr['dataType']:'';
		$region = $attr['region'];
		$commodity = $attr['commodity'];	
		$commodityCol = isset($attr['commodityCol'])?$attr['commodityCol']:'commodity';	
		$selected_market = $attr['selected_market'];
		
		//DB::enableQueryLog();
		if( $siteName == 'CM' ){
			
			if(isset($attr['filterType']) &&  $attr['filterType']=='regionWise'){			
				$regionArr=[];
				if(strtolower($attr['region'])=='india' || strtolower($attr['region'])=='china'){
					$regionArr[]=$attr['region'];
				}else{
					$dataR = DB::table('tbl_region_commodity_master')
						->select('ID')
						->where('itemType','=', 'region')
						->where('itemName','=', $attr['region'])
						->first();
					$regionID = isset($dataR)?$dataR['ID']:$attr['region'];	
					
					if($regionID){
						$dataR = DB::table('tbl_region_commodity_master')
						->select(DB::raw('group_concat(distinct itemName) as regionName'))
						->where('itemType','=', 'region')
						->where('parentID','=', $regionID)
						->first();
						
						$regionArr= explode(',',$dataR['regionName']);					
					}	
				}
				$dataParent = DB::table('tbl_stats_left_toolbox_landing_bm')
				 ->select('tbl_stats_left_toolbox_landing_bm.commodity as mainCommodity','tbl_stats_left_toolbox_landing_bm.datatypeName as tradeType','tbl_stats_left_toolbox_landing_bm.commodity as commodityName','tbl_stats_left_toolbox_landing_bm.region',DB::raw('DATE_FORMAT(`startDate`, \'%b-%Y\') as dataAvailableFrom'), DB::raw('DATE_FORMAT(`toDate`, \'%b-%Y\') as dataAvailableTo'), DB::raw('DATE_FORMAT(`lastUpdated`, \'%d-%b-%Y\') as lastUpdated'),'commo_region_ordering.id AS fOrder','country_code','col_name_commodity AS columnNameOfCommodity','col_name_region as columnNameofRegion','nextParentsList','periodFormat','tbl_region_commodity_master.ID as commodityID')
				->leftJoin('commo_region_ordering','commo_region_ordering.country','=','tbl_stats_left_toolbox_landing_bm.region')
				->leftJoin('tbl_region_commodity_master','tbl_region_commodity_master.itemName','=','tbl_stats_left_toolbox_landing_bm.commodity')
				->leftJoin('tbl_stats_datatype_header_list_bm','tbl_stats_datatype_header_list_bm.datatypeName','=','tbl_stats_left_toolbox_landing_bm.datatypeName')
				->leftJoin('tbl_country','tbl_country.country','=','tbl_stats_left_toolbox_landing_bm.region')
				->whereIn('tbl_stats_left_toolbox_landing_bm.region',$regionArr)
				->where('tbl_stats_left_toolbox_landing_bm.commodity','=', $commodity);
				if($dataType)
					$dataParent = $dataParent->where('tbl_stats_left_toolbox_landing_bm.datatypeName','=', $dataType);
				else{
					$dataParent = $dataParent->where('tbl_stats_left_toolbox_landing_bm.datatypeName','LIKE', "%_not_reqd%");
					$dataParent = $dataParent->where('tbl_stats_left_toolbox_landing_bm.datatypeName','NOT LIKE', "%indian_coal_stock%");
				}
				
				$dataParent = $dataParent->groupBy('tbl_stats_left_toolbox_landing_bm.datatypeName','tbl_stats_left_toolbox_landing_bm.region')
				->get(); 
				
				///print_r($dataParent);
				$data = DB::table('tbl_stats_left_toolbox_landing_bm')
				 ->select('tbl_stats_left_toolbox_landing_bm.commodity as mainCommodity','tbl_stats_left_toolbox_landing_bm.datatypeName as tradeType','tbl_stats_left_toolbox_landing_bm.subCommodity as commodityName','tbl_stats_left_toolbox_landing_bm.region',DB::raw('DATE_FORMAT(`startDate`, \'%b-%Y\') as dataAvailableFrom'), DB::raw('DATE_FORMAT(`toDate`, \'%b-%Y\') as dataAvailableTo'), DB::raw('DATE_FORMAT(`lastUpdated`, \'%d-%b-%Y\') as lastUpdated'),'commo_region_ordering.id AS fOrder','country_code','col_name_subcommodity AS columnNameOfCommodity', 'col_name_region as columnNameofRegion','nextParentsList','periodFormat','tbl_region_commodity_master.ID as commodityID')
				->leftJoin('commo_region_ordering','commo_region_ordering.country','=','tbl_stats_left_toolbox_landing_bm.region')
				->leftJoin('tbl_region_commodity_master','tbl_region_commodity_master.itemName','=','tbl_stats_left_toolbox_landing_bm.subCommodity')
				->leftJoin('tbl_stats_datatype_header_list_bm','tbl_stats_datatype_header_list_bm.datatypeName','=','tbl_stats_left_toolbox_landing_bm.datatypeName')
				->leftJoin('tbl_country','tbl_country.country','=','tbl_stats_left_toolbox_landing_bm.region')
				->whereIn('tbl_stats_left_toolbox_landing_bm.region',$regionArr)
				->where('tbl_stats_left_toolbox_landing_bm.commodity','=', $commodity)
				->where('tbl_stats_left_toolbox_landing_bm.subCommodity','<>', "");
				
				if($dataType)
					$data = $data->where('tbl_stats_left_toolbox_landing_bm.datatypeName','=', $dataType);
				else{
					$data = $data->where('tbl_stats_left_toolbox_landing_bm.datatypeName','LIKE', "%_not_reqd%");
					$data = $data->where('tbl_stats_left_toolbox_landing_bm.datatypeName','NOT LIKE', "%indian_coal_stock%");
				}
				
				$data = $data->groupBy('tbl_stats_left_toolbox_landing_bm.datatypeName','tbl_stats_left_toolbox_landing_bm.region','tbl_stats_left_toolbox_landing_bm.subCommodity'); 
				
			}else{
				$dataParent = DB::table('tbl_stats_left_toolbox_landing_bm')
				 ->select('tbl_stats_left_toolbox_landing_bm.commodity as mainCommodity', 'tbl_stats_left_toolbox_landing_bm.datatypeName as tradeType','tbl_stats_left_toolbox_landing_bm.commodity as commodityName','tbl_stats_left_toolbox_landing_bm.region',DB::raw('DATE_FORMAT(`startDate`, \'%b-%Y\') as dataAvailableFrom'), DB::raw('DATE_FORMAT(`toDate`, \'%b-%Y\') as dataAvailableTo'), DB::raw('DATE_FORMAT(`lastUpdated`, \'%d-%b-%Y\') as lastUpdated'),'periodFormat','commo_region_ordering.id AS fOrder','country_code','col_name_commodity AS columnNameOfCommodity','col_name_region as columnNameofRegion','nextParentsList','tbl_region_commodity_master.ID as commodityID')
				->leftJoin('commo_region_ordering','commo_region_ordering.country','=','tbl_stats_left_toolbox_landing_bm.region')
				->leftJoin('tbl_region_commodity_master','tbl_region_commodity_master.itemName','=','tbl_stats_left_toolbox_landing_bm.commodity')
				->leftJoin('tbl_stats_datatype_header_list_bm','tbl_stats_datatype_header_list_bm.datatypeName','=','tbl_stats_left_toolbox_landing_bm.datatypeName')
				->leftJoin('tbl_country','tbl_country.country','=','tbl_stats_left_toolbox_landing_bm.region')
				->where('tbl_stats_left_toolbox_landing_bm.region','=', $region)
				->where('tbl_stats_left_toolbox_landing_bm.commodity','=', $commodity);
				if($dataType)
					$dataParent = $dataParent->where('tbl_stats_left_toolbox_landing_bm.datatypeName','=', $dataType);
				else{
					$dataParent = $dataParent->where('tbl_stats_left_toolbox_landing_bm.datatypeName','LIKE', "%_not_reqd%");
					$dataParent = $dataParent->where('tbl_stats_left_toolbox_landing_bm.datatypeName','NOT LIKE', "%indian_coal_stock%");
				}
				
				$dataParent = $dataParent->groupBy('tbl_stats_left_toolbox_landing_bm.datatypeName','tbl_stats_left_toolbox_landing_bm.region')
				->get(); 
				
				$data = DB::table('tbl_stats_left_toolbox_landing_bm')
				 ->select('tbl_stats_left_toolbox_landing_bm.commodity as mainCommodity', 'tbl_stats_left_toolbox_landing_bm.datatypeName as tradeType','tbl_stats_left_toolbox_landing_bm.subCommodity as commodityName','tbl_stats_left_toolbox_landing_bm.region',DB::raw('DATE_FORMAT(`startDate`, \'%b-%Y\') as dataAvailableFrom'), DB::raw('DATE_FORMAT(`toDate`, \'%b-%Y\') as dataAvailableTo'), DB::raw('DATE_FORMAT(`lastUpdated`, \'%d-%b-%Y\') as lastUpdated'),'periodFormat','commo_region_ordering.id AS fOrder','country_code','col_name_subcommodity AS columnNameOfCommodity', 'col_name_region as columnNameofRegion','nextParentsList','tbl_region_commodity_master.ID as commodityID')
				->leftJoin('commo_region_ordering','commo_region_ordering.country','=','tbl_stats_left_toolbox_landing_bm.region')
				->leftJoin('tbl_region_commodity_master','tbl_region_commodity_master.itemName','=','tbl_stats_left_toolbox_landing_bm.subCommodity')
				->leftJoin('tbl_stats_datatype_header_list_bm','tbl_stats_datatype_header_list_bm.datatypeName','=','tbl_stats_left_toolbox_landing_bm.datatypeName')
				->leftJoin('tbl_country','tbl_country.country','=','tbl_stats_left_toolbox_landing_bm.region')
				->where('tbl_stats_left_toolbox_landing_bm.region','=', $region)
				->where('tbl_stats_left_toolbox_landing_bm.commodity','=', $commodity)
				->where('tbl_stats_left_toolbox_landing_bm.subCommodity','<>', "");
				
				if($dataType)
					$data = $data->where('tbl_stats_left_toolbox_landing_bm.datatypeName','=', $dataType);
				else{
					$data = $data->where('tbl_stats_left_toolbox_landing_bm.datatypeName','LIKE', "%_not_reqd%");
					$data = $data->where('tbl_stats_left_toolbox_landing_bm.datatypeName','NOT LIKE', "%indian_coal_stock%");
				}
				
				$data = $data->groupBy('tbl_stats_left_toolbox_landing_bm.datatypeName','tbl_stats_left_toolbox_landing_bm.subCommodity'); 
				
			}
		}else{
			if(isset($attr['filterType']) &&  $attr['filterType']=='regionWise'){			
				$regionArr=[];
				if(strtolower($attr['region'])=='india' || strtolower($attr['region'])=='china'){
					$regionArr[]=$attr['region'];
				}else{
					$dataR = DB::table('tbl_region_commodity_master')
						->select('ID')
						->where('itemType','=', 'region')
						->where('itemName','=', $attr['region'])
						->first();
					$regionID = isset($dataR)?$dataR['ID']:$attr['region'];	
					
					if($regionID){
						$dataR = DB::table('tbl_region_commodity_master')
						->select(DB::raw('group_concat(distinct itemName) as regionName'))
						->where('itemType','=', 'region')
						->where('parentID','=', $regionID)
						->first();
						
						$regionArr= explode(',',$dataR['regionName']);					
					}	
				}
				$dataParent = DB::table('tbl_stats_left_toolbox_landing_bm')
				 ->select('tbl_stats_left_toolbox_landing_bm.commodity as mainCommodity','tbl_stats_left_toolbox_landing_bm.datatypeName as tradeType','tbl_stats_left_toolbox_landing_bm.commodity as commodityName','tbl_stats_left_toolbox_landing_bm.region',DB::raw('DATE_FORMAT(`startDate`, \'%b-%Y\') as dataAvailableFrom'), DB::raw('DATE_FORMAT(`toDate`, \'%b-%Y\') as dataAvailableTo'), DB::raw('DATE_FORMAT(`lastUpdated`, \'%d-%b-%Y\') as lastUpdated'),'commo_region_ordering.id AS fOrder','country_code','col_name_commodity AS columnNameOfCommodity','col_name_region as columnNameofRegion','nextParentsList','periodFormat','tbl_region_commodity_master.ID as commodityID')
				->leftJoin('commo_region_ordering','commo_region_ordering.country','=','tbl_stats_left_toolbox_landing_bm.region')
				->leftJoin('tbl_region_commodity_master','tbl_region_commodity_master.itemName','=','tbl_stats_left_toolbox_landing_bm.commodity')
				->leftJoin('tbl_stats_datatype_header_list_bm','tbl_stats_datatype_header_list_bm.datatypeName','=','tbl_stats_left_toolbox_landing_bm.datatypeName')
				->leftJoin('tbl_country','tbl_country.country','=','tbl_stats_left_toolbox_landing_bm.region')
				->whereIn('tbl_stats_left_toolbox_landing_bm.region',$regionArr)
				->where('tbl_stats_left_toolbox_landing_bm.commodity','=', $commodity);
				if($dataType)
					$dataParent = $dataParent->where('tbl_stats_left_toolbox_landing_bm.datatypeName','=', $dataType);
				else
					$dataParent = $dataParent->where('tbl_stats_left_toolbox_landing_bm.datatypeName','NOT LIKE', "%_not_reqd%");
                
                
                if($selected_market!=''){
                    $dataParent = $dataParent->whereRaw("FIND_IN_SET('$selected_market', tbl_stats_left_toolbox_landing_bm.f_market)");
                }
				
				$dataParent = $dataParent->groupBy('tbl_stats_left_toolbox_landing_bm.datatypeName','tbl_stats_left_toolbox_landing_bm.region')
				->get(); 
				
				///print_r($dataParent);
				$data = DB::table('tbl_stats_left_toolbox_landing_bm')
				 ->select('tbl_stats_left_toolbox_landing_bm.commodity as mainCommodity','tbl_stats_left_toolbox_landing_bm.datatypeName as tradeType','tbl_stats_left_toolbox_landing_bm.subCommodity as commodityName','tbl_stats_left_toolbox_landing_bm.region',DB::raw('DATE_FORMAT(`startDate`, \'%b-%Y\') as dataAvailableFrom'), DB::raw('DATE_FORMAT(`toDate`, \'%b-%Y\') as dataAvailableTo'), DB::raw('DATE_FORMAT(`lastUpdated`, \'%d-%b-%Y\') as lastUpdated'),'commo_region_ordering.id AS fOrder','country_code','col_name_subcommodity AS columnNameOfCommodity', 'col_name_region as columnNameofRegion','nextParentsList','periodFormat','tbl_region_commodity_master.ID as commodityID')
				->leftJoin('commo_region_ordering','commo_region_ordering.country','=','tbl_stats_left_toolbox_landing_bm.region')
				->leftJoin('tbl_region_commodity_master','tbl_region_commodity_master.itemName','=','tbl_stats_left_toolbox_landing_bm.subCommodity')
				->leftJoin('tbl_stats_datatype_header_list_bm','tbl_stats_datatype_header_list_bm.datatypeName','=','tbl_stats_left_toolbox_landing_bm.datatypeName')
				->leftJoin('tbl_country','tbl_country.country','=','tbl_stats_left_toolbox_landing_bm.region')
				->whereIn('tbl_stats_left_toolbox_landing_bm.region',$regionArr)
				->where('tbl_stats_left_toolbox_landing_bm.commodity','=', $commodity)
				->where('tbl_stats_left_toolbox_landing_bm.subCommodity','<>', "");
				
				if($dataType)
					$data = $data->where('tbl_stats_left_toolbox_landing_bm.datatypeName','=', $dataType);
				else
					$data = $data->where('tbl_stats_left_toolbox_landing_bm.datatypeName','NOT LIKE', "%_not_reqd%");
				
				if($selected_market!=''){
                    $data = $data->whereRaw("FIND_IN_SET('$selected_market', tbl_stats_left_toolbox_landing_bm.f_market)");
                }
				
				$data = $data->groupBy('tbl_stats_left_toolbox_landing_bm.datatypeName','tbl_stats_left_toolbox_landing_bm.region','tbl_stats_left_toolbox_landing_bm.subCommodity'); 
				
			}else{
				$dataParent = DB::table('tbl_stats_left_toolbox_landing_bm')
				 ->select('tbl_stats_left_toolbox_landing_bm.commodity as mainCommodity', 'tbl_stats_left_toolbox_landing_bm.datatypeName as tradeType','tbl_stats_left_toolbox_landing_bm.commodity as commodityName','tbl_stats_left_toolbox_landing_bm.region',DB::raw('DATE_FORMAT(`startDate`, \'%b-%Y\') as dataAvailableFrom'), DB::raw('DATE_FORMAT(`toDate`, \'%b-%Y\') as dataAvailableTo'), DB::raw('DATE_FORMAT(`lastUpdated`, \'%d-%b-%Y\') as lastUpdated'),'periodFormat','commo_region_ordering.id AS fOrder','country_code','col_name_commodity AS columnNameOfCommodity','col_name_region as columnNameofRegion','nextParentsList','tbl_region_commodity_master.ID as commodityID')
				->leftJoin('commo_region_ordering','commo_region_ordering.country','=','tbl_stats_left_toolbox_landing_bm.region')
				->leftJoin('tbl_region_commodity_master','tbl_region_commodity_master.itemName','=','tbl_stats_left_toolbox_landing_bm.commodity')
				->leftJoin('tbl_stats_datatype_header_list_bm','tbl_stats_datatype_header_list_bm.datatypeName','=','tbl_stats_left_toolbox_landing_bm.datatypeName')
				->leftJoin('tbl_country','tbl_country.country','=','tbl_stats_left_toolbox_landing_bm.region')
				->where('tbl_stats_left_toolbox_landing_bm.region','=', $region)
				->where('tbl_stats_left_toolbox_landing_bm.commodity','=', $commodity);
				if($dataType)
					$dataParent = $dataParent->where('tbl_stats_left_toolbox_landing_bm.datatypeName','=', $dataType);
				else
					$dataParent = $dataParent->where('tbl_stats_left_toolbox_landing_bm.datatypeName','NOT LIKE', "%_not_reqd%");
				
				if($selected_market!=''){
                    $dataParent = $dataParent->whereRaw("FIND_IN_SET('$selected_market', tbl_stats_left_toolbox_landing_bm.f_market)");
                }
                
				$dataParent = $dataParent->groupBy('tbl_stats_left_toolbox_landing_bm.datatypeName','tbl_stats_left_toolbox_landing_bm.region')
				->get(); 
				
				$data = DB::table('tbl_stats_left_toolbox_landing_bm')
				 ->select('tbl_stats_left_toolbox_landing_bm.commodity as mainCommodity', 'tbl_stats_left_toolbox_landing_bm.datatypeName as tradeType','tbl_stats_left_toolbox_landing_bm.subCommodity as commodityName','tbl_stats_left_toolbox_landing_bm.region',DB::raw('DATE_FORMAT(`startDate`, \'%b-%Y\') as dataAvailableFrom'), DB::raw('DATE_FORMAT(`toDate`, \'%b-%Y\') as dataAvailableTo'), DB::raw('DATE_FORMAT(`lastUpdated`, \'%d-%b-%Y\') as lastUpdated'),'periodFormat','commo_region_ordering.id AS fOrder','country_code','col_name_subcommodity AS columnNameOfCommodity', 'col_name_region as columnNameofRegion','nextParentsList','tbl_region_commodity_master.ID as commodityID')
				->leftJoin('commo_region_ordering','commo_region_ordering.country','=','tbl_stats_left_toolbox_landing_bm.region')
				->leftJoin('tbl_region_commodity_master','tbl_region_commodity_master.itemName','=','tbl_stats_left_toolbox_landing_bm.subCommodity')
				->leftJoin('tbl_stats_datatype_header_list_bm','tbl_stats_datatype_header_list_bm.datatypeName','=','tbl_stats_left_toolbox_landing_bm.datatypeName')
				->leftJoin('tbl_country','tbl_country.country','=','tbl_stats_left_toolbox_landing_bm.region')
				->where('tbl_stats_left_toolbox_landing_bm.region','=', $region)
				->where('tbl_stats_left_toolbox_landing_bm.commodity','=', $commodity)
				->where('tbl_stats_left_toolbox_landing_bm.subCommodity','<>', "");
				
				if($dataType)
					$data = $data->where('tbl_stats_left_toolbox_landing_bm.datatypeName','=', $dataType);
				else
					$data = $data->where('tbl_stats_left_toolbox_landing_bm.datatypeName','NOT LIKE', "%_not_reqd%");
				
				if($selected_market!=''){
                    $data = $data->whereRaw("FIND_IN_SET('$selected_market', tbl_stats_left_toolbox_landing_bm.f_market)");
                }
                
				$data = $data->groupBy('tbl_stats_left_toolbox_landing_bm.datatypeName','tbl_stats_left_toolbox_landing_bm.subCommodity'); 
				
			}
		}
		$data = $data -> orderByRaw("
						case tbl_stats_left_toolbox_landing_bm.subCommodity
							when 'Fines/Lumps' then 1
							when 'Pellet/Concentrate' then 2
							when 'Non Coking Coal' then 3
							when 'Coking Coal' then 4
							
							when 'Sponge Iron' then 6
							when 'Pig Iron' then 7
							when 'Ferrous Scrap' then 8
							when 'Crude Steel' then 9
							when 'Semi Finish' then 10
							when 'Finish Long' then 11
							when 'Finish Flat' then 12
							when 'Manganese Ore' then 13
							when 'Chrome Ore' then 14
							when 'Ferro Silico Manganese' then 15
							when 'Ferro Manganese' then 16
							when 'Ferro Chrome' then 17
							when 'Finish Long Stainless Steel' then 18
							when 'Finish Flat Stainless Steel' then 19
							when 'PCI Coal' then 51
							when 'Met Coke' then 52
							when 'Pet Coke' then 53
							when 'Anthracite' then 54
							when 'Bituminous Coal' then 55
							when 'Bituminous' then 55
							when 'Lignite' then 56
							when 'Ship Breaking' then 21
							else 99
						end,tbl_stats_left_toolbox_landing_bm.region, commo_region_ordering.id		
					")
					->get();

		//dd(DB::getQueryLog());
		///$extra = json_decode(file_get_contents("https://www.steelmint.com/nw/public/datatypes.json"), true);
		////$extra = json_decode(file_get_contents("https://www.steelmint.com/nw/public/statsHeaderType.json"), true);
		
		$finalArray = array();
		$dataParent = json_decode(json_encode($dataParent), true);
		if(count($dataParent) > 0 ){
			foreach ($dataParent as $key => $value) {
				////$subCommodity =  explode(',',trim('All,'.trim($value['subCommodityAll'],','),','));
				$dataParent[$key]['flag'] = $value['country_code']!=''?"https://www.steelmint.com/steeladmin/flags/".strtolower($value['country_code']).".png":''; 
				$dataParent[$key]['tradeType'] =str_replace("_not_reqd","",$dataParent[$key]['tradeType']); 
				$dataParent[$key]['country_code'] = !empty($value['country_code'])?$value['country_code']:'NA'; 
								
				$nextParent = $this->nextParentDetailHandlerApp($value['nextParentsList'],$commOrSubComm='commodity',$commodity,$value['region'],$selected_market);
				$nextParentArr=[];
				foreach($nextParent as $name=>$valArr){
					///$nextParentArr[]=array('name'=>$name,'data'=>$valArr);
					$valArr['name'] = str_replace("_not_reqd","",$valArr['name']); 
					$nextParentArr[]=$valArr;
				}
				$dataParent[$key]['nextParentsListDetail'] = $nextParentArr;
				//$dataParent[$key]['headerColumn'] = isset($extra[strtolower($value['tradeType'])][$value['commodityName']][$value['region']])?explode(',',$extra[strtolower($value['tradeType'])][$value['commodityName']][$value['region']]):'';
				$attr['dataType']=$value['tradeType'];
				$attr['selected_market']=$selected_market;
				
				$dataParent[$key]['headerColumn'] = $this->getHeaderNameAndColumns($attr);
				$finalArray['tabs']['All']['name'] = 'All';
				$finalArray['tabs']['All']['data'][] = $dataParent[$key];
				
				///$dataParent[$key]['childList'] = $extra[strtolower($value['tradeType'])]['data']['commodity'][$value['commodityName']]['subCommodity']['All']['region']
				
			}
		}
		
		$data = json_decode(json_encode($data), true); 
		if(count($data) > 0 ){
			foreach ($data as $key => $value) {
				////$subCommodity =  explode(',',trim('All,'.trim($value['subCommodityAll'],','),','));
				$data[$key]['flag'] = $value['country_code']!=''?"https://www.steelmint.com/steeladmin/flags/".strtolower($value['country_code']).".png":'';
			   
			  
			   $data[$key]['tradeType']= str_replace('_not_reqd',"",$data[$key]['tradeType']);
			  
				///$data[$key]['tradeType'] = ucwords(strtolower($value['tradeType'])); 
				$data[$key]['country_code'] = !empty($value['country_code'])?$value['country_code']:'NA'; 
				$data[$key]['commodityID'] = !empty($value['commodityID'])?$value['commodityID']:($value['commodityName']=='Semi Finish'?448:($value['commodityName']=='Pellet/Concentrate'?434:0)); 
				/* ////$finalArray['All'] = $data;	
				////unset($data[$key]['subCommodityAll']);
				$subCommodity = explode(',',trim('All,'.trim($value['commodityName'],','),','));
				foreach($subCommodity as $_subCommodity){ 
					$finalArray[$_subCommodity][] = $data[$key];	
				} */
				$nextParent = $this->nextParentDetailHandlerApp($value['nextParentsList'],$commOrSubComm='subCommodity',$value['commodityName'],$region,$selected_market);
				$nextParentArr=[];
				foreach($nextParent as $name=>$valArr){
					//$nextParentArr[]=array('name'=>$name,'data'=>$valArr);
					$valArr['name'] = str_replace("_not_reqd","",$valArr['name']); 
					$nextParentArr[]=$valArr;
				}
				$data[$key]['nextParentsListDetail'] = $nextParentArr;
				///$dataParent[$key]['headerColumn'] = isset($extra[strtolower($value['tradeType'])][$value['commodityName']][$value['region']])?explode(',',$extra[strtolower($value['tradeType'])][$value['commodityName']][$value['region']]):'';
				$attr['dataType']=$value['tradeType'];
				$attr['selected_market']=$selected_market;
				$data[$key]['headerColumn'] = $this->getHeaderNameAndColumns($attr);
				$finalArray['tabs'][$value['commodityName']]['name'] = $value['commodityName'];	
				$finalArray['tabs'][$value['commodityName']]['data'][] = $data[$key];
				//print_r($finalArray);				
			}
		}
		
		if(count($finalArray['tabs'])==2)
			unset($finalArray['tabs']['All']);
		return array_values($finalArray['tabs']);
	}	
	
	public function getCommRegMenuByCount($attr, $siteName = 'SM',$selected_market=''){ 
		//DB::enableQueryLog();
		
		$dataM = DB::table('tbl_region_commodity_master')
			->select('tbl_region_commodity_master.ID','itemType','itemName', 'tbl_region_commodity_master.parentID','grandParentID','back_ground_image','country_code')
			->leftJoin('tbl_country','tbl_country.id','=','tbl_region_commodity_master.ID')
			->whereIn('itemType', ["region", "commodity"])
			->whereNOTIn('itemName', ["All", "Others"]);
        
        if($selected_market!=''){
          //$dataM = $dataM->whereRaw("FIND_IN_SET('$selected_market', f_market)");
		}
        $dataM = $dataM->groupBy('itemName')
			->orderBy('f_order_by')->get();
		///dd(DB::getQueryLog());
		$dataM = json_decode(json_encode($dataM), true);	
		$masterArray = array();
		$parentArray = array();
		$regionArray = array();
		$res = array();
		if(count($dataM) > 0 ){		
			foreach ($dataM as $key => $value) {
				if($value['grandParentID']==0 && $value['parentID']==0)
					$parentArray[$value['ID']] = $value['itemName'];
				elseif($value['parentID']==0)
					$parentArray[$value['ID']] = $value['itemName'];
			}
			foreach ($dataM as $key => $value) {
				/* if($value['grandParentID'])
					$parentArray[$value['ID']] = $value['itemName'];
				elseif($value['parentID'])
					$parentArray[$value['ID']] = $value['itemName'];
				else */
				
				$parentName='';
				if($value['grandParentID'])
					$parentName=$parentArray[$value['grandParentID']];
				elseif($value['parentID'])
					$parentName=$parentArray[$value['parentID']];
				elseif($value['itemName']=='India')	
					$parentName=$value['itemName'];
				elseif($value['itemName']=='China')	
					$parentName=$value['itemName'];
					
				$masterArray[$value['itemName']]['parent'] = $parentName?$parentName:'';
				$masterArray[$value['itemName']]['back_ground_image'] = $value['itemType']=='region'?"https://www.steelmint.com/steeladmin/flags/".strtolower($value['country_code']).".png":$value['back_ground_image'];
				
				if($value['parentID']==0 && $value['itemType']=='region' ){
					if( $siteName == 'CM' )
						$regionArray[$value['itemName']] = array('name'=>$value['itemName'],'img'=>str_replace('/events_steelmintgroup/images/','/events_steelmintgroup/images/coalmint/',$value['back_ground_image']));
					else
						$regionArray[$value['itemName']] = array('name'=>$value['itemName'],'img'=>$value['back_ground_image']);
					}
					//$regionArray[$value['itemName']] = array('name'=>$value['itemName'],'img'=>$value['back_ground_image']);
			}
		}
		
		///echo '<pre>',print_r($parentArray),print_r($masterArray),'</pre>';
		//DB::enableQueryLog();
		if( $siteName == 'CM' ){
			$data = DB::table('tbl_stats_left_toolbox_landing_bm')
			->select('commodity','region', DB::raw('count(distinct region) as total'), DB::raw('group_concat(distinct subCommodity) as subCommodityAll'))
			->where('datatypeName','LIKE', "%_not_reqd%")
			->groupBy('commodity','region');
		}else{
			$data = DB::table('tbl_stats_left_toolbox_landing_bm')
                 ->select('commodity','region', DB::raw('count(distinct region) as total'), DB::raw('group_concat(distinct subCommodity) as subCommodityAll'))
				 ->where('datatypeName','NOT LIKE', "%_not_reqd%");
                 
                 
           if($selected_market!=''){
                $data = $data->whereRaw("FIND_IN_SET('$selected_market', f_market)");
            }
            $data  = $data->groupBy('commodity','region');
		}
		$data = $data -> orderByRaw("
						 case commodity
							when 'Iron Ore' then 1
							when 'Coal' then 2
							when 'Scrap & Metallics' then 3
							when 'Steel' then 4
							when 'Ferro Alloy' then 5		
							when 'Crude Steel' then 6
							when 'Ship Breaking' then 7
							when 'Graphite Electrode' then 8
							when 'Finish Long Stainless Steel' then 9
							when 'Finish Flat Stainless Steel' then 10					
							
							else 99
						end, region				
					")
					->get();

		 // dd(DB::getQueryLog());
		$data = json_decode(json_encode($data), true);
		$finalArray = array();
		$regionArray1 = array();
		if(count($data) > 0 ){
		
			foreach ($data as $key => $value) {
				/* if(isset($finalArray[$value['commodity']]))
					$finalArray[$value['commodity']]['cnt'] += 1;
				else */{
				$finalArray[$value['commodity']]['cnt'] += 1;
					$finalArray[$value['commodity']]['name'] = $value['commodity'];
					///$finalArray[$value['commodity']]['img'] = "https://www.steelmint.com/events_steelmintgroup/images/".strtolower(str_replace(' ','',$value['commodity'])).".jpg";
					$finalArray[$value['commodity']]['img'] = "https://www.steelmint.com/api_bm/public/assets/app/stats/Icons/".strtolower(str_replace(' ','',$value['commodity'])).".png";
				}
				$img = isset($masterArray[$value['region']]['back_ground_image'])?$masterArray[$value['region']]['back_ground_image']:'';
				
				//"https://www.steelmint.com/events_steelmintgroup/images/".strtolower(str_replace(' ','',$value['region'])).".jpg"
				$value['subCommodityAll'] = explode(',',trim('All,'.trim($value['subCommodityAll'],','),','));
				$finalArray[$value['commodity']]['region'][] = array('name'=>$value['region'],'img'=>$img, 'subCommodity'=>$value['subCommodityAll']);
				
				$region = isset($masterArray[$value['region']])?$masterArray[$value['region']]['parent']:'';
				if($region){
					if(in_array($value['commodity'], $regionArray1[$region])!==true){
						$regionArray1[$region][$value['commodity']]=$value['commodity'];	
						$regionArray[$region]['commodities'][]=array('name'=>$value['commodity'],'img'=>"https://www.steelmint.com/api_bm/public/assets/app/stats/Icons/".strtolower(str_replace(' ','',$value['commodity'])).".png", 'subCommodity'=>$value['subCommodityAll']);	
					}						
				}
			}			
		}
		
		$res['commodities'] = array_values($finalArray); 		
		$res['regions'] = array_values($regionArray); 		
		///$res['masterArray'] = $masterArray; 		
		/* if( $siteName == 'CM' ){
			$data = DB::table('tbl_stats_left_toolbox_landing_bm')
			->select('commodity','region', DB::raw('count(distinct region) as total'))
			->where('datatypeName','LIKE', "%_not_reqd%")
			->groupBy('commodity','region');
		}else{
			DB::table('tbl_stats_left_toolbox_landing_bm')
				->select('commodity','region', DB::raw('count(distinct region) as total'),'itemName','back_ground_image')
				->leftJoin('tbl_region_commodity_master','tbl_stats_left_toolbox_landing_bm.region','=','tbl_region_commodity_master.itemName')
				->where('itemType','=','region')
				->where('datatypeName','LIKE', "%_not_reqd%")
				->groupBy('region', 'commodity');
			
		}
		$data = $data -> orderByRaw("
						 case commodity
							when 'Iron Ore' then 1
							when 'Coal' then 2
							when 'Scrap & Metallics' then 3
							when 'Steel' then 4
							when 'Ferro Alloy' then 5		
							when 'Crude Steel' then 6
							when 'Ship Breaking' then 7
							when 'Graphite Electrode' then 8
							when 'Finish Long Stainless Steel' then 9
							when 'Finish Flat Stainless Steel' then 10					
							
							else 99
						end, region				
					")
					->get();

		 /// dd(DB::getQueryLog());
		$data = json_decode(json_encode($data), true);
		$finalArray = array();
		if(count($data) > 0 ){
		
			foreach ($data as $key => $value) {
				if(isset($finalArray[$value['commodity']]))
					$finalArray[$value['commodity']]['cnt'] += 1;
				else{
					$finalArray[$value['commodity']]['name'] = $value['commodity'];
					$finalArray[$value['commodity']]['img'] = "https://www.steelmint.com/events_steelmintgroup/images/".strtolower(str_replace(' ','',$value['commodity'])).".jpg";
				}
				$finalArray[$value['commodity']]['region'][] = array('name'=>$value['region'],'img'=>"https://www.steelmint.com/events_steelmintgroup/images/".strtolower(str_replace(' ','',$value['region'])).".jpg");
			}			
		}
		
		$res['commodities'] = array_values($finalArray);  */
		
		return $res;
    }

    public function checkDatatypeExist($attr,$memid,$appType='SM'){
      $check = 'true';
	  
// 	  if($appType=='CM'){
// 		  $attr = $attr.'_not_reqd';
// 	  }
	  
	  if(
	      DB::table('tbl_stats_datatype_header_list_bm')
	        ->where('datatypeName', 'like', $attr . '%')
	        ->count() < 1 || 
	      DB::table('tbl_stats_left_toolbox_landing_bm')
	        ->where('datatypeName', 'like', $attr . '%')
	        ->count() < 1
	   ){
            $check="false";
      }
      return $check;
    }

public function getCommAndRegion($attr, $memid=0, $siteName = 'SM',$selected_market=''){
     if($siteName=='CM'){
		  $attr = $attr.'_not_reqd';
	  }
	// DB::enableQueryLog();  
	
	 $stats =  DB::table('tbl_stats_datatype_header_list_bm')
	                ->where('datatypeName', 'like', $attr . '%')
                    ->whereRaw("FIND_IN_SET('$selected_market', f_market)")
	                ->take(1)
	                ->get();
	  //dd(DB::getQueryLog());              
      $colomnNameofRegion = '';
       $stats = json_decode(json_encode($stats), true);
	   
			///DB::enableQueryLog(); 
			$data = DB::table('tbl_stats_left_toolbox_landing_bm')
                        ->whereRaw("FIND_IN_SET('$selected_market', f_market)")
						->where('datatypeName', 'like', $attr . '%')
						;
				// $memid == 116707 || 	
// 			if($memid == 120030 || $memid == 120954  ){
// 				$data = $data -> orWhere('datatypeName',$attr . "_not_reqd");
// 			}
			$data = $data 	
				->select('region','startDate','toDate','nextParentsList','commodity','col_name_commodity','subCommodity','col_name_subcommodity','col_name_region', 'lastUpdated')
				-> orderByRaw("
					 case subCommodity
						when 'Fines/Lumps' then 1
						when 'Pellet/Concentrate' then 2
						when 'Non Coking Coal' then 3
						when 'Coking Coal' then 4
						
						when 'Sponge Iron' then 6
						when 'Pig Iron' then 7
						when 'Ferrous Scrap' then 8
						when 'Crude Steel' then 9
						when 'Semi Finish' then 10
						when 'Finish Long' then 11
						when 'Finish Flat' then 12
						when 'Manganese Ore' then 13
						when 'Chrome Ore' then 14
						when 'Ferro Silico Manganese' then 15
						when 'Ferro Manganese' then 16
						when 'Ferro Chrome' then 17
						when 'Finish Long Stainless Steel' then 18
						when 'Finish Flat Stainless Steel' then 19
						when 'PCI Coal' then 51
						when 'Met Coke' then 52
						when 'Pet Coke' then 53
						when 'Anthracite' then 54
						when 'Bituminous Coal' then 55
						when 'Bituminous' then 55
						when 'Lignite' then 56
						when 'Ship Breaking' then 21
						else 99
					end, region					
				")
				->get();
			if( $memid == 56350 ){
			///dd(DB::getQueryLog());
			}	
					//DB::enableQueryLog(); dd(DB::getQueryLog());
            $data = json_decode(json_encode($data), true);
            if(count($data) > 0){
                //$user = new User($memid=1318, $appType='SM');
        		//echo 'test'.$isAccessible =  
        		$user = new User($memid, $appType='SM');
                $user->isAccessible($resourceType='Web', $section='Statistics_Graph','','','',$selected_market);
        		
        		if(!empty(Session::get($selected_market)['sectionFromDate'])){			
        			$sectionFromDate = Session::get($selected_market)['sectionFromDate'];
        			$sectionTodate = Session::get($selected_market)['sectionTodate'];	
        			$sectionTodate = strtotime($sectionTodate) <= strtotime($GLOBALS['currDate']) ?$sectionTodate:$GLOBALS['currDate'];
        			//$value['startDate'] = $sectionFromDate;
        			//$value['endDate'] = $sectionTodate;
        			
        		}else{
        		    $sectionFromDate = $GLOBALS['currDate'];
        			$sectionTodate = $GLOBALS['currDate'];
        		}
        	
                $communityArray = array();
                //$globalRegionArray = array();
                foreach ($data as $key => $value) {
				  $communityArray[$value['commodity']]['subCommodity']['All']['columnNameOfCommodity'] = 'commodity_new';
					
                  if($colomnNameofRegion==''){
                    $colomnNameofRegion= $value['col_name_region'];
                  }
				  /*
					//no limit for below ids
					manish.agrawal@steelmint.com
					damini@steelmintgroup.com
					shubham.gupta@steelmintgroup.com
					rubal.saini@steelmintgroup.com
					jagrati.goswami@steelmint.com
					ankit.kumar@steelmint.com*/
				  if( $memid == 27956 || $memid == 95898 || $memid == 120954 || $memid == 127182 || $memid == 113678 || $memid == 161902 || $memid == 163411){
					$sFromDate = $value['startDate'];
				  }else{
					$sFromDate = strtotime($value['startDate']) <= strtotime($sectionFromDate) ?$sectionFromDate:$value['startDate'];  
				  }
				  $sTodate = strtotime($value['toDate']) <= strtotime($sectionTodate) ?$value['toDate']:$sectionTodate; 
				   
                   $communityArray[$value['commodity']]['columnNameOfCommodity'] = $value['col_name_commodity'];
                   //$communityArray[$value['commodity']]['subCommodity'][$value['subCommodity']] = $value['subCommodity'];
                   if(!empty($value['subCommodity'])){
					   $communityArray[$value['commodity']]['subCommodity'][$value['subCommodity']]['columnNameOfCommodity'] = $value['col_name_subcommodity'];
					   $communityArray[$value['commodity']]['subCommodity'][$value['subCommodity']]['region'][] = array(
							   'region' => $value['region'],
							   'startDate' => $sFromDate,
							   'endDate' => $sTodate,
							   'lastUpdated' => $value['lastUpdated'],
							   'nextParentsList' => $value['nextParentsList'],
							   'nextParentsListDetail' => $this->nextParentDetailHandler($value['nextParentsList'])
						 );
					
				    }
				   
				   $a = $communityArray[$value['commodity']]['subCommodity']['All']['region'];
				   if( count($a) == 0 ){
					   $communityArray[$value['commodity']]['subCommodity']['All']['region'][] = array(
							   'region' => $value['region'],
							   'startDate' => $sFromDate,
							   'endDate' => $sTodate,
							   'lastUpdated' => $value['lastUpdated'],
							   'nextParentsList' => $value['nextParentsList'],
							   'nextParentsListDetail' => $this->nextParentDetailHandler($value['nextParentsList'])
						 );
				   } else {
						$flag = 0;	
					   foreach($a as $key => $val){
						   if( $val['region'] == $value['region'] ){
								$flag = 1;
								
								if(strtotime($val['endDate']) < strtotime($value['toDate'])){
									$communityArray[$value['commodity']]['subCommodity']['All']['region'][$key]['endDate'] = $sTodate;
									
								}
								if( $memid == 116707 && 'Steel'==$value['commodity']){
									//echo '<pre>',$key, $value['region'],'---',$value['commodity'],'---' , $value['subCommodity'],$val['endDate'],'---' ,$value['toDate'], '--',$aa , '</pre>';
								} 
								
								if(strtotime($val['startDate']) > strtotime($sFromDate)){
									$communityArray[$value['commodity']]['subCommodity']['All']['region'][$key]['startDate'] = $sFromDate;
								}
								
						   }
					   }
					   if( $flag == 0 ){
						   $communityArray[$value['commodity']]['subCommodity']['All']['region'][] = array(
								   'region' => $value['region'],
								   'startDate' => $sFromDate,
								   'endDate' => $sTodate,
								   'lastUpdated' => $value['lastUpdated'],
								   'nextParentsList' => $value['nextParentsList'],
								   'nextParentsListDetail' => $this->nextParentDetailHandler($value['nextParentsList'])
							 );
					   }
				   }
                }
            }
			///shif ALL from first to last index
			foreach($communityArray as $commodityName=>$subCommodityArray){
				$a=$subCommodityArray['subCommodity']['All'];
				/* if( $memid == 56350){
					echo "<pre>";
					print_r($a);
				} */
				unset($communityArray[$commodityName]['subCommodity']['All']);
				$communityArray[$commodityName]['subCommodity']['All']= $subCommodityArray['subCommodity']['All'];
			}
			
			if( $memid == 56350){
			//echo "<pre>";
			//print_r($stats);
			}
         //$regionColname = $stats[0]['col_name_region_stats_toolbox'];
         $regionColname = $colomnNameofRegion;
         $periodFormat = $stats[0]['periodFormat'];
         $dataArray = array(
                    'periodFormat' => $periodFormat,
                    'regionColname' => $regionColname,
                    'commodity' => $communityArray      
           
           );
         return $dataArray;
   }
public function getCommAndRegion_19_5_2020($attr, $memid=0){
     $stats =  DB::table('tbl_stats_datatype_header_list_bm')->where('datatypeName',$attr)->take(1)->get();
      $colomnNameofRegion = '';
       $stats = json_decode(json_encode($stats), true);
            $data = DB::table('tbl_stats_left_toolbox_landing_bm')
					->where('datatypeName',$attr)
					->select('region','startDate','nextParentsList','commodity','col_name_commodity','col_name_region')
					-> orderByRaw("
						 case commodity
							when 'Iron Ore' then 1
							when 'Coal' then 2
							when 'Scrap & Metallics' then 3
							when 'Ferrous Scrap' then 4
							when 'Crude Steel' then 5
							when 'Semi Finish' then 6
							when 'Finish Long' then 7
							when 'Finish Flat' then 8
							when 'Ferro Alloy' then 9
							when 'Finish Flat Stainless Steel' then 10
							when 'Finish Long Stainless Steel' then 11
							when 'Graphite Electrode' then 12
							when 'Ship Breaking' then 13
							else 99
						end					
					")
					->get();
            $data = json_decode(json_encode($data), true);
            if(count($data) > 0){
                //$user = new User($memid=1318, $appType='SM');
        		//echo 'test'.$isAccessible =  $user->isAccessible($resourceType='Web', $section='Statistics_Graph');
        		
        		if(!empty(Session::get('sectionFromDate'))){			
        			$sectionFromDate = Session::get('sectionFromDate');
        			$sectionTodate = Session::get('sectionTodate');	
        			$sectionTodate = strtotime($sectionTodate) <= strtotime($GLOBALS['currDate']) ?$sectionTodate:$GLOBALS['currDate'];
        			$value['startDate'] = $sectionFromDate;
        			$value['endDate'] = $sectionTodate;
        			
        		}else{
        		    $sectionFromDate = $GLOBALS['currDate'];
        			$sectionTodate = $GLOBALS['currDate'];
        		}
        	
                $communityArray = array();
                 foreach ($data as $key => $value) {
                  if($colomnNameofRegion==''){
                    $colomnNameofRegion= $value['col_name_region'];
                  }
                   $communityArray[$value['commodity']]['columnNameOfCommodity'] = $value['col_name_commodity'];
                   $communityArray[$value['commodity']]['region'][] = array(
                           'region' => $value['region'],
                           'startDate' => $sectionFromDate,
                           'endDate' => $sectionTodate,
                           'nextParentsList' => $value['nextParentsList'],
                           'nextParentsListDetail' => $this->nextParentDetailHandler($value['nextParentsList'])

                     );
                }
            }
         //$regionColname = $stats[0]['col_name_region_stats_toolbox'];
         $regionColname = $colomnNameofRegion;
         $periodFormat = $stats[0]['periodFormat'];
         $dataArray = array(
                    'periodFormat' => $periodFormat,
                    'regionColname' => $regionColname,
                    'commodity' => $communityArray
           );
         return $dataArray;
   }
    
    public function getGraphData($attr, &$unitTextToShow, &$mntArr){
      
		$siteName = 'SM';
		if(isset($attr['filters']['app_type'])){
			$siteName = $attr['filters']['app_type'];
		}
		
		if($siteName=='CM'){
		  $attr['dataType'] = $attr['dataType'].'_not_reqd';
		}
		$resourceType = 'Web';
		if(isset($attr['resourceType'])){
			$resourceType =$attr['resourceType'];
		}
		
		$unit = isset($attr['unit'])?$attr['unit']:"MnT";
          $getTableByDataType =  DB::table('tbl_stats_datatype_header_list_bm')->where('datatypeName',$attr['dataType'])->value('tableName');
		  
          if($getTableByDataType){
               $periodFormat = trim($attr['periodFormat']);
               $commodityColumn = trim($attr['columnNameOfCommodity']);
               $commodity = trim($attr['commodity']);
               $columnNameOfRegion = trim($attr['columnNameOfRegion']);
               $region = trim(ucfirst($attr['region']));

               $monthColumName = '';
               $yearColumnName = '';
               $quantityColumnName = ''; 
               if($getTableByDataType=='tbl_capacity'){
                 $yearColumnName='capacity_year';
                 $quantityColumnName='capacity';
               }else if($getTableByDataType=='tbl_production' ||$getTableByDataType=='tbl_dispatch' || $getTableByDataType=='tbl_consumption' || $getTableByDataType=='tbl_indian_coal_stock'){
                $monthColumName= 'month';
                $yearColumnName='year';
                $quantityColumnName = 'quantity';
               }else{
                 $monthColumName= 'month_of_loading';
                 $yearColumnName='year_of_loading';
                 $quantityColumnName = 'quantity';
               }
               
                
                $memid = 0;
                $user_id = 0;
                //update memId if getting in POST request
            	if(isset($attr['memid']) && $attr['memid']>0){
                   $memid = trim($attr['memid']);
                }
				if ($resourceType=='app' && isset($attr['accessToken']) && $attr['accessToken']!=''){
					//$uemail = $request->input('email');//oldPassword
					$token = $attr['accessToken'];
					//$user = new User();
					$auth = new authentication();
					$user_id = $auth->authToken($token,$siteName);
					if(!$user_id)
						return 'Unauthorized';
				}elseif ($resourceType=='web' && isset($attr['memid']) && $attr['memid']!=''){
										
					$auth = new authentication(); 
					$user_id = $auth->authUserId($memid);//for web user
					if(!$user_id)
						return 'Unauthorized';
				}
		
                $user = new User($user_id, $appType=$siteName);
				if( isset($attr['requiredFor']) && $attr['requiredFor']=='comparison') {
					$isAccessible =  $user->isAccessible($resourceType, $section='comparison','','','',$attr['selected_market']);
				}else{
					$isAccessible =  $user->isAccessible($resourceType, $section='Statistics_Graph','','','',$attr['selected_market']);
				}
  if($attr['debugMode'] == true){
	  echo "<pre> isAccessible:" . $isAccessible;	 
echo "<br /> user_id:" . $user_id;
			  echo "<br /> memid:" . $memid;
			  echo "<br /> resourceType:" . $resourceType;	
			  print_r($attr);
  }	  
				
                $valDivideBy = 1; 
               /// if($isAccessible>=0 || $attr['source']=='home' || $attr['source']=='home_new'){//visitor or upgrade
                if($isAccessible>=0 && ($attr['source']=='home' || $attr['source']=='home_new')){//visitor or upgrade
                    /* $periodFormat = 'yearly';
                    $attr['fromDate'] = '2017';
                    $attr['toDate'] = date('Y'); */
					$periodFormat = 'monthly';
                    $attr['fromDate'] = 'jul 2019';
                    $attr['toDate'] = "dec 2019";//date('Y');
                    $valDivideBy = 1;
                }elseif(strtolower($resourceType)=='app' && !isset($attr['periodFormat']) && !isset($attr['fromDate'])){//no from and todate then  default value for case of resourceType is app 
                    /* $periodFormat = 'yearly';
                    $attr['fromDate'] = '2017';
                    $attr['toDate'] = date('Y'); */
					$periodFormat = 'yearly';
                    $attr['fromDate'] = date('Y')-4;
                    $attr['toDate'] = date('Y');
                    ///$valDivideBy = 1;
                }elseif($isAccessible>=0){
					return 'Unauthorized';
				}else{
					if($attr['source']=='home_new'){
						$periodFormat = 'monthly';
						$dataQry = DB::table('tbl_stats_left_toolbox_landing_bm')
						->select('toDate')
						->where('datatypeName','=', 'production')
						->where('region','=', 'India')
						->where('commodity','=', 'Iron Ore')
						->first();
						$attr['fromDate'] = date('M Y', strtotime($dataQry['toDate']." -6 Months"));
						$attr['toDate'] = date('M Y', strtotime($dataQry['toDate']));//date('Y');
					}elseif($attr['source']=='tabular'){
						if(strtolower($periodFormat)=='monthly'){
							$sectionFromDate = date('M Y',strtotime(Session::get($attr['selected_market'])['sectionFromDate']));
							$sectionTodate = date('M Y',strtotime(Session::get($attr['selected_market'])['sectionTodate']));	
						} elseif(strtolower($periodFormat)=='yearly'){
							$sectionFromDate = date('Y',strtotime(Session::get($attr['selected_market'])['sectionFromDate']));
							$sectionTodate = date('Y',strtotime(Session::get($attr['selected_market'])['sectionTodate']));	
						}else{
							$sectionFromDate = date('m',strtotime(Session::get($attr['selected_market'])['sectionFromDate']));
							$sectionTodate = date('m',strtotime(Session::get($attr['selected_market'])['sectionTodate']));	
							
							$yearQuarterFrom = ceil($sectionFromDate / 3);
							$sectionFromDate = "q".$yearQuarterFrom. ' '.date('Y',strtotime(Session::get($attr['selected_market'])['sectionFromDate']));
							
							$yearQuarterto = ceil($sectionTodate / 3);
							$sectionTodate = "q".$yearQuarterto. ' '.date('Y',strtotime(Session::get($attr['selected_market'])['sectionTodate']));
						}
						
						$attr['fromDate'] = $sectionFromDate;
						$attr['toDate'] = $sectionTodate;
					}
				}
			  $extraCol = "qtrYear";
              if(strtolower($periodFormat)=='monthly'){
                 $explodeFrom = explode(' ', $attr['fromDate']);
                 $fromMonth = date('m',strtotime($explodeFrom[0]));
                 $fromYear = $explodeFrom[1];
        
                 $explodeTo = explode(' ', $attr['toDate']);
                 $toMonth = date('m',strtotime($explodeTo[0]));
                 $toYear = $explodeTo[1];

                $fromDate = $fromYear.'-'.$fromMonth.'-01';
                $toDate = $toYear.'-'.$toMonth.'-01';

				$time1  = strtotime($fromDate);
				$time2  = strtotime($toDate);
				$fromDate = date('Y-m-d', $time1); 
				$toDate = date('Y-m-d', $time2); 
				
				$grpBy = "formattedDate";
				
			  } elseif(strtolower($periodFormat)=='yearly'){
				$fromDate =trim($attr['fromDate']).'-01'.'-01';
				$toDate = trim($attr['toDate']).'-12'.'-31';
				
				$grpBy = $yearColumnName;
				
			  } elseif(strtolower($periodFormat)=='quarterly'){
				$explodeFrom = explode(' ', $attr['fromDate']);
				$explodeTo = explode(' ', $attr['toDate']);

				$fromMonth = $this->getQuater(strtoupper($explodeFrom[0]));
				$fromYear = $explodeFrom[1];
				$toMonth = $this->getQuaterAfter(strtoupper($explodeTo[0]));
				$toYear = $explodeTo[1];  
				$fromDate = $fromYear.'-'.$fromMonth.'-01';
				$toDate = $toYear.'-'.$toMonth;

				$time1  = strtotime($fromDate);
				$time2  = strtotime($toDate);
				$fromDate = date('Y-m-d', $time1); 
				$toDate = date('Y-m-31', $time2); 
				$grpBy = "qtrYear";
				
			  } elseif(strtolower($periodFormat)=='weekly'){
				$explodeFrom = explode(' to ', $attr['fromDate']);
				$explodeTo = explode(' to ', $attr['toDate']);
/* 
				$fromMonth = $this->getQuater(strtoupper($explodeFrom[0]));
				$fromYear = $explodeFrom[1];
				$toMonth = $this->getQuaterAfter(strtoupper($explodeTo[0]));
				$toYear = $explodeTo[1]; */  
				$fromDate = $explodeFrom[0];
				$toDate = $explodeTo[1];

				$time1  = strtotime($fromDate);
				$time2  = strtotime($toDate);
				$fromDate = date('Y-m-d', $time1); 
				$toDate = date('Y-m-d', $time2); 
				$grpBy = "formattedDate";
				$extraCol = "week";
			  }  
//echo $fromDate . " -- " . $toDate; 
//query formation - START                 
				$data = DB::table($getTableByDataType)
						->select($extraCol, 'formattedDate',$yearColumnName,   DB::raw('SUM(' . $quantityColumnName . ') as total_quantity'));
						
				if($attr['source']=='tabular'){
					if(!empty($attr['commodity']) && !empty($attr['columnNameOfCommodity']) ){
                        if(isset($attr['mainCommodity']) && $attr['mainCommodity']!='')
                            $data = $data->where('commodity_new',$attr['mainCommodity']);
						$data = $data->where($commodityColumn,$commodity);
					} elseif(empty($attr['columnNameOfCommodity'])) {
						if(!empty($attr['commodity'])){
							$commColName = DB::table("tbl_stats_selection_compare_bm")
									->	select("commodity", 'subCommodity', 'subSubCommodity')
									->	where('commodity', '=', $attr['commodity'])
									->	orWhere('subCommodity', '=', $attr['commodity'])
									->	orWhere('subSubCommodity', '=', $attr['commodity'])
									->	get();
							if( count($commColName) > 0 ){
								if( $commColName[0]['commodity'] ==  $attr['commodity'])
									$commodityColumn = 'commodity';
								elseif( $commColName[0]['subCommodity'] ==  $attr['commodity'])
									$commodityColumn = 'subCommodity';
								elseif( $commColName[0]['subSubCommodity'] ==  $attr['commodity'])
									$commodityColumn = 'subSubCommodity';
							}
							$data = $data->where($commodityColumn,$commodity);	
							if(isset($attr['mainCommodity']) && $attr['mainCommodity']!='')
                             $data = $data->where('commodity_new',$attr['mainCommodity']);
						}
					}
					if(!empty($attr['region']) && !empty($attr['columnNameOfRegion']) ){
						$data = $data->where($columnNameOfRegion,$region);
					}
				}else{
					$data = $data	->where($commodityColumn,$commodity)
									->where($columnNameOfRegion,$region);
                    if(isset($attr['mainCommodity']) && $attr['mainCommodity']!='')
                            $data = $data->where('commodity_new',$attr['mainCommodity']);
				}
				
				if(!empty($attr['filters']['app_type'])){
					if( $attr['filters']['app_type'] == 'CM' )
						$siteFilter = "coalmint";
					else 
						$siteFilter = "steelmint";
					//$data	=  $data	->	where('website_access', 'like', '%' . $siteFilter . '%');
				}else{
					$siteFilter = "steelmint";
					//$data	=  $data	->	where('website_access', 'like', '%' . $siteFilter . '%');
				}
				$tillDate='';
				if( ($memid == 116707 && isset($attr['assoName']) && $attr['assoName'] == 'goAdvisor')){
					///offset(1) is used instead of offset(0) as we want to show second last date's data to the goadvisor association. we dont want to show latest data to the goadvisor association
					
					$dataMax = DB::table($getTableByDataType)
						->select('formattedDate');
					$dataMax = $dataMax	->where($commodityColumn,$commodity)
									->where($columnNameOfRegion,$region);
					//$dataMax	=  $dataMax	->	where('website_access', 'like', '%' . $siteFilter . '%');
					$dataMax=  $dataMax->groupBy('formattedDate')
										->orderBy('formattedDate','DESC')
										->offset(1)
										->limit(1)
										->first();
					if($dataMax){
						//print_r($dataMax);
						$toDate = date("Y-m-t", strtotime($dataMax['formattedDate']));
						$tillDate = date("M Y", strtotime($dataMax['formattedDate']));
					}
				}else{
					$dataMax = DB::table($getTableByDataType)
						->select('formattedDate');
					$dataMax = $dataMax	->where($commodityColumn,$commodity)
									->where($columnNameOfRegion,$region);
					//$dataMax	=  $dataMax	->	where('website_access', 'like', '%' . $siteFilter . '%');
					$dataMax=  $dataMax->groupBy('formattedDate')
										->orderBy('formattedDate','DESC')
										->offset(0)
										->limit(1)
										->first();
					
					if($dataMax && strtolower($resourceType)=='app' ){
						//print_r($dataMax);
						$tillDate = date("M Y", strtotime($dataMax['formattedDate']));
					}
					
				}
				$data	=  $data	->	where('formattedDate', '>=', $fromDate)
									->	where('formattedDate', '<=', $toDate)
									->	groupBy($grpBy);
									
				if($attr['debugMode'] == true){
					//$data=  $data->orderBy('formattedDate','ASC')->toSql();
					echo "<pre>";
					echo "<pre>" . $commodityColumn . " -- " . $commodity . " -- " . $columnNameOfRegion . " -- " . $region;
					//print_r($data);
					//dd();
				}
									
				$data=  $data->orderBy('formattedDate','ASC')->get();
					if($attr['debugMode'] == true){
					//$data=  $data->orderBy('formattedDate','ASC')->toSql();
					echo "<pre>";
					echo "<pre>" . $commodityColumn . " -- " . $commodity . " -- " . $columnNameOfRegion . " -- " . $region;
					print_r($data);
					//dd();
				}
				/* echo "<pre>" . $commodityColumn . " -- " . $commodity . " -- " . $columnNameOfRegion . " -- " . $region;
				print_r($data);
				dd(); */
				/* print_r($data);
				dd(); */
				
				$mntArr=[];
				$data = json_decode(json_encode($data), true);
				if(count($data) > 0){
					///code for missing month with '-'
					$minDateArr = explode('-',$fromDate);
					$maxDateArr = explode('-',$toDate);
					$minYear1 = $minDateArr[0];
					$maxYear1 = $maxDateArr[0];
					if($maxYear1>$minYear1 && strtolower($periodFormat)=='monthly'){
						$start = strtotime($minYear1.'-01-01');
						$end = strtotime($fromDate);
						$yrArr = array();
						while($start < $end)
						{
							$newDate = date("F Y", $start);
							$start = strtotime("+1 month", $start);	
							
							$responceArrayStart[] = array(
									'x' => trim($newDate),
									'y' => '-'
								);
						}						
						//echo "<pre>", $minYear1 , '--', $maxYear1, '--',print_r($new),'</pre>';								
					}
					
					/* $max=0;
					for($i = 0; $i < count($data); $i++){
						if(($data[$i]["total_quantity"]/1000000)>$max)
							$max = $data[$i]["total_quantity"]/1000000;
					} */
					for($i = 0; $i < count($data); $i++){
						 $yValue = '';
						 if(strtolower($periodFormat)=='monthly'){
							$time=strtotime($data[$i]["formattedDate"]);
							$month= strtolower($resourceType)=='app' ? date("M",$time):date("F",$time);
							$year=date("Y",$time);	
							$yValue = $month . ' ' . $year;	
							///$mntArr['month'][ltrim(date("m",$time),'0')] = $month;
							$mntArr['month'][date("n",$time)] = $month;
							if(!array_key_exists($year, $mntArr['year']))
								$mntArr['year'][$year]=$year;
						 }	 
						 if(strtolower($periodFormat)=='yearly'){
							$time=strtotime($data[$i]["formattedDate"]);
							$year=date("Y",$time);	
							$yValue = $year;							
						 }	 
						 if(strtolower($periodFormat)=='quarterly'){
							/* $time=strtotime($data[$i]["formattedDate"]);
							
							//Get the month number of the date
							//in question.
							$month = date("n", strtotime($time));

							//Divide that month number by 3 and round up
							//using ceil.
							$yearQuarter = ceil($month / 3);

							//Print it out
							$yValue = $yearQuarter . " " . date("Y", strtotime($dateStr)); */
							$yValue = $data[$i]["qtrYear"];
							$yValueArr0 = explode(' ',$yValue); 
							$mntArr['month'][ltrim($yValueArr0[0],'Q')] = $yValueArr0[0];
							if(!array_key_exists($yValueArr0[1], $mntArr['year']))
								$mntArr['year'][$yValueArr0[1]]=$yValueArr0[1];
						 }
						if(strtolower($periodFormat)=='weekly'){
							//$yValue = $data[$i]["week"]. ', '.date('Y', strtotime($data[$i]["formattedDate"]));
							$yValue = $data[$i]["week"]. ', '.$data[$i][$yearColumnName];
						}
						$unitTextToShow = 'Quantity in '.$unit;
						/* if($data[$i]["total_quantity"]>0){ *///comment ason 22/12/2021 as chnages given by yashwant to show 0 qty 
							if($yValue==date('Y')){
								$yValue .= ' till';
								
								if( ($memid == 116707 && isset($attr['assoName']) && $attr['assoName'] == 'goAdvisor') || strtolower($resourceType)=='app'){
									//$yValue .= ' '.date("M Y", strtotime($toDate));
									$yValue .= ' ('.$tillDate.')';
								}
							}elseif(strpos($yValue, "Q")!==false){
								$monthToday = date('n',strtotime($dataMax['formattedDate']));
								$quarterToday = ceil($monthToday / 3);
								//$yValueArr = explode(" ",$yValue);
								//if($yValueArr[1]==date('Y')){
									//if($memid==56350 )
									//	echo '<br>::'.$dataMax['formattedDate'].'::Q'.$quarterToday;
								///if('Q'.$yearQuarter.' '.date('Y')==$yValue){
								if('Q'.$quarterToday.' '.date('Y')==$yValue){
									if($monthToday % 3!=0)
										$yValue .= ' till';
								
									if(( $memid == 116707 && isset($attr['assoName']) && $attr['assoName'] == 'goAdvisor')  || strtolower($resourceType)=='app'){
										//$yValue .= ' '.date("M Y", strtotime($toDate));
										$yValue .= ' ('.$tillDate.')';
									}
								}
							}
							
							//if(strtolower($attr['commodity'])=='graphite electrode' && strtolower($attr['dataType'])=='export'){
							/*if($attr['source']!='tabular' && round($data[$i]["total_quantity"]/1000000, 2)<1){
								$responceArray[] = array(
									'x' => trim($yValue),
									'y' => number_format(round($data[$i]["total_quantity"], 2),2)
								);
								$unitTextToShow = 'Quantity in MT';
							}else*/
							/* if($attr['source']!='tabular' && round($max)<10){
								$responceArray[] = array(
									'x' => trim($yValue),
									'y' => number_format(round($data[$i]["total_quantity"], 2),2)
								);
								$unitTextToShow = 'Quantity in MT';
							}else */{
							$mnt_mt_division_inn = 1000000;
								$_round_2 = 2;
								if($unit=='MT' || $unit=='LDT'){
									$mnt_mt_division_inn = 1;
									$_round_2 = 0;
									$y = number_format((int) round($data[$i]["total_quantity"]/$mnt_mt_division_inn));
									
								}else{
									if($data[$i]["total_quantity"]/$mnt_mt_division_inn < 0.1)
										$y = number_format((float) $data[$i]["total_quantity"]/$mnt_mt_division_inn, 4);
									else
										$y = number_format((float) $data[$i]["total_quantity"]/$mnt_mt_division_inn, 2);
								}
								
								$responceArray[] = array(
									'x' => trim($yValue),
									'y' => $data[$i]["total_quantity"]!=0?$y:"0"
									////'y' => $data[$i]["total_quantity"]!=0?number_format(round($data[$i]["total_quantity"]/$mnt_mt_division_inn, 2),2):0
								);
							}
						/* } */
					}
					
			
					return $responceArray;
				} else {
					return 'false';
				}				
				
				
//query formation - END
				//$data = $data ->toSql();					
 				/* echo "<pre>";
				print_r($data);
				dd(); */

                 /* $data = json_decode(json_encode($data), true);
                 if(count($data) > 0){
                  $arragedArray = array();
                  foreach ($data as $key => $value) {
                    $arragedArray[$value[$yearColumnName]][$value[$monthColumName]][]=$value[$quantityColumnName];  
                  }
                  
                 foreach ($arragedArray as $keys => $monthsArray) {
                   foreach ($monthsArray as $key => $val) {
                     $responceArray[] = array(
                         'x' => $key.' '.$keys,
                         'y' => number_format(array_sum($val),2)
                      );
                   }
                 }
                 return $responceArray;
               }else{
                  return 'false';
               } */
             // }

              /* if(strtolower($periodFormat)=='yearly'){

               $fromYear =trim($attr['fromDate']);
               $toYear = trim($attr['toDate']);
               $getYearRange = range($fromYear,$toYear);
                $data = DB::table($getTableByDataType)->whereIn($yearColumnName,$getYearRange);
                if($attr['source']=='tabular'){
                    if(!empty($attr['commodity']) && !empty($attr['columnNameOfCommodity']) ){
                      $data = $data->where($commodityColumn,$commodity);
                    }
                    if(!empty($attr['region']) && !empty($attr['columnNameOfRegion']) ){
                      $data = $data->where($columnNameOfRegion,$region);
                    }
                  }else{
                    $data = $data->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region);
                  }
                  $data=  $data->get();
                  $data = json_decode(json_encode($data), true);
				 
                  if(count($data) > 0){
                    $arragedArray = array();
                    foreach ($data as $key => $value) {
                      $arragedArray[$value[$yearColumnName]][] =$value[$quantityColumnName];  
                    }

                    foreach ($arragedArray as $keys => $val) {
                        $responceArray[] = array(
                           'x' => $keys,
                           'y' => $val>1 ? number_format(array_sum($val)/$valDivideBy,2):number_format(array_sum($val),2)
                        );
                   }
                   return $responceArray;
                  }else{
                      return 'false';
                  }
            }

            if(strtolower($periodFormat)=='quarterly'){
               $explodeFrom = explode(' ', $attr['fromDate']);
               $explodeTo = explode(' ', $attr['toDate']);

               $fromMonth = $this->getQuater(strtoupper($explodeFrom[0]));
               $fromYear = $explodeFrom[1];


               $toMonth = $this->getQuaterAfter(strtoupper($explodeTo[0]));
               $toYear = $explodeTo[1];  
                

               $fromDate = $fromYear.'-'.$fromMonth.'-01';
               $toDate = $toYear.'-'.$toMonth.'-01';
               $monthList = $this->getMonthBetweenTwoDates($fromDate,$toDate);
				
				
                $data = DB::table($getTableByDataType)->select('*', DB::raw("STR_TO_DATE(".$monthColumName.", '%M') as mm1"))->whereIn(DB::raw("CONCAT(`".$monthColumName."`, ' ', `".$yearColumnName."`)"),$monthList);
                if($attr['source']=='tabular'){
                    if(!empty($attr['commodity']) && !empty($attr['columnNameOfCommodity']) ){
                      $data = $data->where($commodityColumn,$commodity);
                    }
                    if(!empty($attr['region']) && !empty($attr['columnNameOfRegion']) ){
                      $data = $data->where($columnNameOfRegion,$region);
                    }
                  }else{
                    $data = $data->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region);
                  }
                $data=  $data->orderBy($yearColumnName,'ASC')->orderBy("mm1",'ASC')->get();
				
               $data = json_decode(json_encode($data), true);
               if(count($data) > 0){
					  $arragedArray = array();
					 foreach ($data as $key => $value) {
						 $qter = ceil(date("n", strtotime($value[$monthColumName]))/3);
						 $arragedArray[$value[$yearColumnName]]['Q'.$qter][] = $value[$quantityColumnName];
					 }

					foreach ($arragedArray as $keys => $yearArray) {
					   foreach ($yearArray as $key => $val) {
						 $responceArray[] = array(
							 'x' => $key.' '.$keys,
							 'y' => number_format(array_sum($val),2)
						  );
					   }
					} 
					return $responceArray;
				}else{
				 return 'false';
				}
			} */
        }else{
          return 'false';
        }
    }

    public function getGraphData_16_4_2020($attr){
       
          $getTableByDataType =  DB::table('tbl_stats_datatype_header_list_bm')->where('datatypeName',$attr['dataType'])->value('tableName');
		  
          if($getTableByDataType){
               $periodFormat = trim($attr['periodFormat']);
               $commodityColumn = trim($attr['columnNameOfCommodity']);
               $commodity = trim($attr['commodity']);
               $columnNameOfRegion = trim($attr['columnNameOfRegion']);
               $region = trim(ucfirst($attr['region']));

               $monthColumName = '';
               $yearColumnName = '';
               $quantityColumnName = ''; 
               if($getTableByDataType=='tbl_capacity'){
                 $yearColumnName='capacity_year';
                 $quantityColumnName='capacity';
               }else if($getTableByDataType=='tbl_production' || $getTableByDataType=='tbl_consumption'){
                $monthColumName= 'month';
                $yearColumnName='year';
                $quantityColumnName = 'quantity';
               }else{
                 $monthColumName= 'month_of_loading';
                 $yearColumnName='year_of_loading';
                 $quantityColumnName = 'quantity';
               }
               
                
                $memid = 0;
                //update memId if getting in POST request
            	if(isset($attr['memid']) && $attr['memid']>0){
                   $memid = trim($attr['memid']);
                }
                
                /*if($memid<=0){
                    $status = array(
                          'status' => 2001,
                          'message' => 'Unauthorized action'
                        );
                       return response()->json($status);
                }*/
                
                $user = new User($memid, $appType='SM');
				if( isset($attr['requiredFor']) && $attr['requiredFor']=='comparison') {
					$isAccessible =  $user->isAccessible($resourceType='Web', $section='comparison');
				}else{
					$isAccessible =  $user->isAccessible($resourceType='Web', $section='Statistics_Graph');
				}
				
                $valDivideBy = 1; 
                if($isAccessible>=0){//visitor or upgrade
                    $periodFormat = 'yearly';
                    $attr['fromDate'] = '2017';
                    $attr['toDate'] = date('Y');
                    $valDivideBy = 3.7;
                    
                    
                }else{
					//isset($attr['fromDate'])?$attr['fromDate']:;
					//isset($attr['toDate'])?$attr['fromDate']:;
					if($attr['source']=='tabular'){
						if(strtolower($periodFormat)=='monthly'){
							$sectionFromDate = date('M Y',strtotime(Session::get('sectionFromDate')));
							$sectionTodate = date('M Y',strtotime(Session::get('sectionTodate')));	
						} elseif(strtolower($periodFormat)=='yearly'){
							$sectionFromDate = date('Y',strtotime(Session::get('sectionFromDate')));
							$sectionTodate = date('Y',strtotime(Session::get('sectionTodate')));	
						}else{
							$sectionFromDate = date('m',strtotime(Session::get('sectionFromDate')));
							$sectionTodate = date('m',strtotime(Session::get('sectionTodate')));	
							
							$yearQuarterFrom = ceil($sectionFromDate / 3);
							$sectionFromDate = "q".$yearQuarterFrom. ' '.date('Y',strtotime(Session::get('sectionFromDate')));
							
							$yearQuarterto = ceil($sectionTodate / 3);
							$sectionTodate = "q".$yearQuarterto. ' '.date('Y',strtotime(Session::get('sectionTodate')));
						}
						
						$attr['fromDate'] = $sectionFromDate;
						$attr['toDate'] = $sectionTodate;
						//if(!isset($attr['periodFormat']))
						//	$periodFormat = 'monthly';
					}
					//$attr['fromDate'] = strtotime($fromDate) < strtotime($sectionFromDate) ?$sectionFromDate:$fromDate;
					//$attr['toDate'] = strtotime($toDate) > strtotime($sectionTodate) ?$sectionTodate:$toDate;
				}

              if(strtolower($periodFormat)=='monthly'){
                 
                 $explodeFrom = explode(' ', $attr['fromDate']);
				 //print_r($explodeFrom);
                 $fromMonth = date('m',strtotime($explodeFrom[0]));
                 $fromYear = $explodeFrom[1];
        
                 $explodeTo = explode(' ', $attr['toDate']);
                 $toMonth = date('m',strtotime($explodeTo[0]));
                 $toYear = $explodeTo[1];

                $fromDate = $fromYear.'-'.$fromMonth.'-01';
                $toDate = $toYear.'-'.$toMonth.'-01';
                 $monthList = $this->getMonthBetweenTwoDates($fromDate,$toDate);
                 $data = DB::table($getTableByDataType)->select('*', DB::raw("STR_TO_DATE(".$monthColumName.", '%M') as mm1"))->whereIn(DB::raw("CONCAT(`".$monthColumName."`, ' ', `".$yearColumnName."`)"),$monthList);
                  if($attr['source']=='tabular'){
                    if(!empty($attr['commodity']) && !empty($attr['columnNameOfCommodity']) ){
                      $data = $data->where($commodityColumn,$commodity);
                    }
                    if(!empty($attr['region']) && !empty($attr['columnNameOfRegion']) ){
                      $data = $data->where($columnNameOfRegion,$region);
                    }
                  }else{
                    $data = $data->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region);
                  }
                 $data=  $data->orderBy($yearColumnName,'ASC')->orderBy("mm1",'ASC')->get();

                 $data = json_decode(json_encode($data), true);
                 if(count($data) > 0){
                  $arragedArray = array();
                  foreach ($data as $key => $value) {
                    $arragedArray[$value[$yearColumnName]][$value[$monthColumName]][]=$value[$quantityColumnName];  
                  }
                  
                 foreach ($arragedArray as $keys => $monthsArray) {
                   foreach ($monthsArray as $key => $val) {
                     $responceArray[] = array(
                         'x' => $key.' '.$keys,
                         'y' => number_format(array_sum($val),2)
                      );
                   }
                 }
                 return $responceArray;
               }else{
                  return 'false';
               }
              }
              //return $responceArray;

              if(strtolower($periodFormat)=='yearly'){

               $fromYear =trim($attr['fromDate']);
               $toYear = trim($attr['toDate']);
               $getYearRange = range($fromYear,$toYear);
			   //DB::enableQueryLog(); 
                $data = DB::table($getTableByDataType)->whereIn($yearColumnName,$getYearRange);
				//dd(DB::getQueryLog());	
                if($attr['source']=='tabular'){
                    if(!empty($attr['commodity']) && !empty($attr['columnNameOfCommodity']) ){
                      $data = $data->where($commodityColumn,$commodity);
                    }
                    if(!empty($attr['region']) && !empty($attr['columnNameOfRegion']) ){
                      $data = $data->where($columnNameOfRegion,$region);
                    }
                  }else{
                    $data = $data->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region);
                  }
                  $data=  $data->get();
                  $data = json_decode(json_encode($data), true);
				 
                  if(count($data) > 0){
                    $arragedArray = array();
                    foreach ($data as $key => $value) {
                      $arragedArray[$value[$yearColumnName]][] =$value[$quantityColumnName];  
                    }

                    foreach ($arragedArray as $keys => $val) {
                        $responceArray[] = array(
                           'x' => $keys,
                           'y' => $val>1 ? number_format(array_sum($val)/$valDivideBy,2):number_format(array_sum($val),2)
                        );
                   }
                   return $responceArray;
                  }else{
                      return 'false';
                  }
            }

            if(strtolower($periodFormat)=='quarterly'){
               $explodeFrom = explode(' ', $attr['fromDate']);
               $explodeTo = explode(' ', $attr['toDate']);

               $fromMonth = $this->getQuater(strtoupper($explodeFrom[0]));
               $fromYear = $explodeFrom[1];


               $toMonth = $this->getQuaterAfter(strtoupper($explodeTo[0]));
               $toYear = $explodeTo[1];  
                

               $fromDate = $fromYear.'-'.$fromMonth.'-01';
               $toDate = $toYear.'-'.$toMonth.'-01';
               $monthList = $this->getMonthBetweenTwoDates($fromDate,$toDate);
				
				
                $data = DB::table($getTableByDataType)->select('*', DB::raw("STR_TO_DATE(".$monthColumName.", '%M') as mm1"))->whereIn(DB::raw("CONCAT(`".$monthColumName."`, ' ', `".$yearColumnName."`)"),$monthList);
                if($attr['source']=='tabular'){
                    if(!empty($attr['commodity']) && !empty($attr['columnNameOfCommodity']) ){
                      $data = $data->where($commodityColumn,$commodity);
                    }
                    if(!empty($attr['region']) && !empty($attr['columnNameOfRegion']) ){
                      $data = $data->where($columnNameOfRegion,$region);
                    }
                  }else{
                    $data = $data->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region);
                  }
                $data=  $data->orderBy($yearColumnName,'ASC')->orderBy("mm1",'ASC')->get();
                // $data=  $data->orderBy('YYMM','ASC')->get();
				
               $data = json_decode(json_encode($data), true);
               if(count($data) > 0){
					  $arragedArray = array();
					 foreach ($data as $key => $value) {
						 $qter = ceil(date("n", strtotime($value[$monthColumName]))/3);
						 $arragedArray[$value[$yearColumnName]]['Q'.$qter][] = $value[$quantityColumnName];
					 }

					foreach ($arragedArray as $keys => $yearArray) {
					   foreach ($yearArray as $key => $val) {
						 $responceArray[] = array(
							 'x' => $key.' '.$keys,
							 'y' => number_format(array_sum($val),2)
						  );
					   }
					} 
					return $responceArray;
				}else{
				 return 'false';
				}
			}
        }else{
          return 'false';
        }
    }

    public function getHeaderNameAndColumns($attr){
		$siteName = 'SM';
		if(isset($attr['filters']['app_type'])){
			$siteName = $attr['filters']['app_type'];
		}
		
		if($siteName=='CM'){
		  $attr['dataType'] = $attr['dataType'].'_not_reqd';
		}
		
	  $getDataTypeId = DB::table('tbl_stats_datatype_header_list_bm')->where('datatypeName',$attr['dataType']);
	 
	  if($attr['selected_market']!=''){
         $selected_market = $attr['selected_market'];
         $getDataTypeId = $getDataTypeId->whereRaw("FIND_IN_SET('$selected_market', f_market)");
      }

	  
	  $getDataTypeId = $getDataTypeId->value('id');
      if($getDataTypeId){
         $getHeaderFromTable = DB::table('tbl_stats_datatype_header_dtls')->where('datatypeID',$getDataTypeId)->get();
         $getHeaderFromTable = json_decode(json_encode($getHeaderFromTable), true);
         if(count($getHeaderFromTable) > 0){
           $headerArray = array();
           foreach ($getHeaderFromTable as $key => $value) {
              $headerArray[] = array(
                  'headerName' => $value['headerName_display'],
                  'headerColumn' => $value['tbl_column_name']
                );
           }
           return $headerArray;
         }else{
          return 'false';
         }

      }else{
        return 'false';
      }
    }

	public function getChildrenGraphPoint($attr, $siteName = 'SM', $title){
		
		if( $siteName == 'CM' ){
			$getTableByDataType =  DB::table('tbl_stats_datatype_header_list_bm')
									->where('datatypeName',$attr['dataType'] . "_not_reqd")
									->value('tableName');
		} else {
			$getTableByDataType =  DB::table('tbl_stats_datatype_header_list_bm')
									->where('datatypeName',$attr['dataType'])
									->value('tableName');
		}
		
		$resourceType = 'Web';
		if(isset($attr['resourceType'])){
			$resourceType =$attr['resourceType'];
		}
		
		if($getTableByDataType){
			$periodFormat = trim($attr['periodFormat']);
			$commodityColumn = trim($attr['columnNameOfCommodity']);
			$commodity = trim($attr['commodity']);
			$headerColumn = trim($attr['headerColumn']);

			if($headerColumn == 'subCommodity'){
			if( $commodityColumn == 'commodity_new' )
				$headerColumn = 'subCommodity';
			else
				$headerColumn = 'subSubCommodity';
			} 
			
			

			$columnNameOfRegion = trim($attr['columnNameOfRegion']);
			$region = trim(ucfirst($attr['region']));

			$monthColumName = '';
			$yearColumnName = '';
			$quantityColumnName = ''; 
			if($getTableByDataType=='tbl_capacity'){
				$yearColumnName='capacity_year';
				$quantityColumnName='capacity';
			}else if($getTableByDataType=='tbl_production' || $getTableByDataType=='tbl_dispatch' || $getTableByDataType=='tbl_consumption' || $getTableByDataType=='tbl_indian_coal_stock'){
				$monthColumName= 'month';
				$yearColumnName='year';
				$quantityColumnName = 'quantity';
			}else{
				$monthColumName= 'month_of_loading';
				$yearColumnName='year_of_loading';
				$quantityColumnName = 'quantity';
			}
			$user_id = 0;
			//update memId if getting in POST request
			if(isset($attr['memid']) && $attr['memid']>0){
			   $memid = trim($attr['memid']);
			}
			if ($resourceType=='app' && isset($attr['accessToken']) && $attr['accessToken']!=''){
				//$uemail = $request->input('email');//oldPassword
				$token = $attr['accessToken'];
				//$user = new User();
				$auth = new authentication();
				$user_id = $auth->authToken($token,$siteName);
				if(empty($user_id))
					return 'Unauthorized';
			}elseif ($resourceType=='web' && isset($attr['memid']) && $attr['memid']!=''){
									
				$auth = new authentication(); 
				$user_id = $auth->authUserId($memid);//for web user
				if(!$user_id)
					return 'Unauthorized';
			}
			$user = new User($user_id, $appType=$siteName);
			$isAccessible =  $user->isAccessible($resourceType, $section='Statistics_Graph','','','',$attr['selected_market']);
			$valDivideBy = 1;
			///if($isAccessible>=0){//visitor or upgrade
			if($isAccessible>=0 && ($attr['source']=='home' || $attr['source']=='home_new')){//visitor or upgrade
				$periodFormat = 'monthly';
				$attr['fromDate'] = 'jul 2019';
				$attr['toDate'] = "dec 2019";//date('Y');
				$valDivideBy = 1;
			}elseif($attr['source']=='home_new' && $isAccessible==-1){
				$periodFormat = 'monthly';
				$dataQry = DB::table('tbl_stats_left_toolbox_landing_bm')
				->select('toDate')
				->where('datatypeName','=', 'production')
				->where('region','=', 'India')
				->where('commodity','=', 'Iron Ore')
				->first();
				$attr['fromDate'] = date('M Y', strtotime($dataQry['toDate']." -6 Months"));
				$attr['toDate'] = date('M Y', strtotime($dataQry['toDate']));//date('Y');
			}elseif(strtolower($resourceType)=='app' && !isset($attr['periodFormat']) && !isset($attr['fromDate'])){//no from and todate then  default value for case of resourceType is app 
                    /* $periodFormat = 'yearly';
                    $attr['fromDate'] = '2017';
                    $attr['toDate'] = date('Y'); */
					$periodFormat = 'yearly';
                    $attr['fromDate'] = date('Y')-4;
                    $attr['toDate'] = date('Y');
                    ///$valDivideBy = 1;
            }elseif($isAccessible>=0){
				return 'Unauthorized';
			}
			
			$extraCol = "qtrYear";
			if(strtolower($periodFormat)=='monthly'){
				$explodeFrom = explode(' ', $attr['fromDate']);
				$fromMonth = date('m',strtotime($explodeFrom[0]));
				$fromYear = $explodeFrom[1];

				$explodeTo = explode(' ', $attr['toDate']);
				$toMonth = date('m',strtotime($explodeTo[0]));
				$toYear = $explodeTo[1];

				$fromDate = $fromYear.'-'.$fromMonth.'-01';
				$toDate = $toYear.'-'.$toMonth.'-01';

				$time1  = strtotime($fromDate);
				$time2  = strtotime($toDate);
				$fromDate = date('Y-m-d', $time1); 
				$toDate = date('Y-m-d', $time2); 

				$grpBy = "formattedDate";
				$fromDateStart = $fromDate;
			} elseif(strtolower($periodFormat)=='yearly'){
				$fromDate =trim($attr['fromDate']).'-01'.'-01';
				$toDate = trim($attr['toDate']).'-12'.'-31';

				$grpBy = $yearColumnName;
				$fromDateStart = $fromDate;
			} elseif(strtolower($periodFormat)=='quarterly'){
				$explodeFrom = explode(' ', $attr['fromDate']);
				$explodeTo = explode(' ', $attr['toDate']);

				$fromMonth = $this->getQuater(strtoupper($explodeFrom[0]));
				$fromYear = $explodeFrom[1];
				$toMonth = $this->getQuaterAfter(strtoupper($explodeTo[0]));
				$toYear = $explodeTo[1];  
				$fromDate = $fromYear.'-'.$fromMonth.'-01';
				$toDate = $toYear.'-'.$toMonth;

				$time1  = strtotime($fromDate);
				$time2  = strtotime($toDate);
				$fromDate = date('Y-m-d', $time1); 
				$toDate = date('Y-m-31', $time2); 
				$grpBy = "qtrYear";
				$fromDateStart = $fromDate;
			}  elseif(strtolower($periodFormat)=='weekly'){
				$explodeFrom = explode(' to ', $attr['fromDate']);
				$explodeTo = explode(' to ', $attr['toDate']);
/* 
				$fromMonth = $this->getQuater(strtoupper($explodeFrom[0]));
				$fromYear = $explodeFrom[1];
				$toMonth = $this->getQuaterAfter(strtoupper($explodeTo[0]));
				$toYear = $explodeTo[1]; */  
				$fromDateStart = $explodeFrom[0];
				$fromDate = $explodeFrom[1];
				$fromDate =$fromDateStart;
				$toDate = $explodeTo[1];

				$time1  = strtotime($fromDate);
				$time2  = strtotime($toDate);
				$fromDate = date('Y-m-d', $time1); 
				$toDate = date('Y-m-d', $time2); 
				$grpBy = "formattedDate";
				//$grpBy = "'year','week'";
				$extraCol = "week";
			  } 		
			
			if(!empty($attr['filters']['app_type'])){
				if( $attr['filters']['app_type'] == 'CM' )
					$siteFilter = "coalmint";
				else 
					$siteFilter = "steelmint";
				
			}else{
				$siteFilter = "steelmint";
				
			}
			
			if( $attr['filters']['app_type'] == 'CM' )
					$logoPath='https://www.coalmint.com/images/logo.png';
				else
					$logoPath='https://www.steelmint.com/images/logo.png';
				
			if($memid==5635000)
				DB::enableQueryLog();	
			if(strtolower($periodFormat)=='weekly'){
				//$formattedDate = DB::raw('DATE(formattedDate + INTERVAL (6 - WEEKDAY(formattedDate)) DAY) as formattedDate');	
				//$formattedDate = DB::raw('CONCAT(week," ",year) as formattedDate');
				$formattedDate = 'formattedDate';
			}
			elseif(strtolower($periodFormat)=='yearly'){
				$formattedDate = DB::raw('MAX(formattedDate) as formattedDate');								
			}			
			else{
				$formattedDate = 'formattedDate';
			}
			
			$unit = isset($attr['unit'])?$attr['unit']:"MnT";
			
			if($unit=='MT' || $unit=='LDT'){
				$mnt_mt_division_inn = 1;
				$_round_2 = 0;
				//$y = number_format((int) round($data[$i]["total_quantity"]/$mnt_mt_division_inn));
				
			}else{
				$mnt_mt_division_inn = 1000000;
				$_round_2 = 2;
				///$y = number_format((float) $data[$i]["total_quantity"]/$mnt_mt_division_inn, 2);
			}
			
			
			
			$data = DB::table($getTableByDataType)
				->	select($headerColumn, $extraCol, $formattedDate,  DB::raw('SUM(' . $quantityColumnName . ')/'.$mnt_mt_division_inn.' as total_quantity'),$yearColumnName)
				->	whereNotIn($headerColumn,['','-'])
				->	where($commodityColumn,$commodity)
				->	where($columnNameOfRegion,$region)
				->	where('formattedDate', '>=', $fromDateStart)
				->	where('formattedDate', '<=', $toDate)
				//->	where('website_access', 'like', '%' . $siteFilter . '%')
				;
			
			if($commodityColumn != 'commodity_new'){
                if(isset($attr['mainCommodity']) && !empty(trim($attr['mainCommodity']))){
                  $data = $data->where('commodity_new',$attr['mainCommodity']);
                }
			}	
            if(strtolower($periodFormat)=='quarterly'){
				$data = $data -> groupBy($grpBy, $headerColumn)
				->	orderBy('formattedDate', 'ASC')
				->	orderBy($grpBy, 'ASC')
				-> orderByRaw("case when $headerColumn='Others' then 99 else 1 end")
				->	orderBy('total_quantity','DESC')
				->	get();
			}
			else{
				$data = $data -> groupBy($grpBy, $headerColumn)
				->	orderBy($grpBy, 'ASC')
				-> orderByRaw("case when $headerColumn='Others' then 99 else 1 end")
				->	orderBy('total_quantity','DESC')
				->	get();
			}
			if($attr['memid']==115566000000000)
				print_r($data);
			if($memid==563500000)
				dd(DB::getQueryLog());	
			
			
			
			//Yashwant Vaid 15 Oct 2022. Put a query to fetch the maximum date for the selected data set
			$data1 = DB::table($getTableByDataType)
				->	select('formattedDate')
				->	whereNotIn($headerColumn,['','-'])
				->	where($commodityColumn,$commodity)
				->	where($columnNameOfRegion,$region)
				->	where('formattedDate', '>=', $fromDateStart)
				->	where('formattedDate', '<=', $toDate)
				//->	where('website_access', 'like', '%' . $siteFilter . '%')
				;
			
			if($commodityColumn != 'commodity_new'){
                if(isset($attr['mainCommodity']) && !empty(trim($attr['mainCommodity']))){
                  $data1 = $data1->where('commodity_new',$attr['mainCommodity']);
                }
			}	
            
			$max_date_formatted = $data1 -> orderBy('formattedDate', 'DESC') -> limit(1) ->	get();
			$min_date_formatted = $data1 -> orderBy('formattedDate', 'ASC') -> limit(1) ->	get();
			if($attr['memid']==115566000000)
				print_r($max_date_formatted[0]['formattedDate']);
			/* echo "<pre>" . $commodityColumn . " -- " . $commodity . " -- " . $columnNameOfRegion . " -- " . $region . " -- " . $headerColumn . " -- " . $grpBy . " -- " . $extraCol . " -- " . $quantityColumnName . " -- " . $fromDate . " -- " . $toDate;
			print_r($data);
			dd(); */
/*
 if ($attr['memid'] == 56350 ){
	echo "<pre>";
	print($fromDate);
echo ":<br>".$this->weekOfYear($fromDate);
echo ":<br>";	
	print($toDate);
echo ":<br>".$this->weekOfYear($toDate);
	
	echo "</pre>";
	die;
} */
			
			$data = json_decode(json_encode($data), true);

			$toDate12=$max_date_formatted[0]['formattedDate'];
			if(isset($data[0]['formattedDate']) && strtotime($data[0]['formattedDate'])>strtotime($fromDate)){
				$fromDate = $data[0]['formattedDate'];				
			}
			$toDate_ass = $toDate;	
			//if(isset($max_date_formatted[0]['formattedDate']) && strtotime($max_date_formatted[0]['formattedDate'])<strtotime($toDate)){
			if(isset($data[count($data)-1]['formattedDate']) && strtotime($data[count($data)-1]['formattedDate'])<strtotime($toDate)){
				$toDate = $data[count($data)-1]['formattedDate'];				
				//$toDate12 = $data[count($data)-1]['formattedDate'];				
				$toDate = date("Y-m-t", strtotime($toDate));
				$toDate_ass = $max_date_formatted[0]['formattedDate'];
			}
			
			$minDateArr = explode('-',$fromDate);
			$maxDateArr = explode('-',$toDate);
 			$atcdds = array();
			$properOrderArr = array();
			$assessmentArr = array();
			$icnt = 0; 
			if(count($data) > 0){
				if(strtolower($periodFormat)=='weekly'){
					$start = $month = strtotime($fromDate);
					$end = strtotime($toDate);
					$yrArr = array();
					while($month <= $end)
					{
						if(in_array(date("Y",$month), $yrArr)===false)
							$yrArr[] = date('Y', $month);
						$month = strtotime("+1 month", $month);
					}
						
					/*foreach($yrArr as $ii=>$_yr){
						if($_yr==date('Y')){//till current week
							///for($i=1; $i<= $this->weekOfYear($toDate); $i++){ 
							$frmWeek = $this->weekOfYear($fromDate);
							$toWeek = $this->weekOfYear($toDate);
							if($frmWeek>$toWeek)
							{
								$frmWeek = 1;
							}
							for($i=$frmWeek; $i<= $toWeek; $i++){
								$assessmentArr[] = 'W '.$i.' '.$_yr;
							}
							
						}elseif($ii==0){//start current week
							///echo $ii.'--'.$fromDate.'--'.$this->weekOfYear($fromDate).'--';die;
							for($i=$this->weekOfYear($fromDate); $i<= 52; $i++){
								$assessmentArr[] = 'W '.$i.' '.$_yr;
							}
						}else{
							for($i=1; $i<= 52; $i++){
								$assessmentArr[] = 'W '.$i.' '.$_yr;
							}
						}
					}*/
					//getting week list for start and end date     
					$getMon = getMondayOfWeek($fromDateStart);
                    $data_yw = DB::table('tbl_year_week')
                        ->where('monday', '>=', $getMon )
                        ->where('sunday', '<=', $toDate_ass )
                        ->orderBy('year')
                        ->orderBy('week')
                        ->get();
                    foreach($data_yw as $d1):
                        $assessmentArr[] = 'W '.$d1['week'].' '.$d1['year'];
                    endforeach;
				}else{
					$start = $month = strtotime($fromDate);
					$end = strtotime($toDate);
					while($month <= $end)
					{
						 if(strtolower($periodFormat)=='quarterly'){
							$monthn = date("n", $month);
							$yearQuarter = ceil($monthn / 3);
							////echo "Q$yearQuarter " . date("Y",$month), PHP_EOL;
							if(in_array("Q$yearQuarter " . date("Y",$month), $assessmentArr)===false)
								$assessmentArr[] = "Q$yearQuarter " . date("Y",$month);
						}elseif(strtolower($periodFormat)=='monthly'){
							$assessmentArr[] = strtolower($resourceType)=='app'?date('M Y', $month):date('F Y', $month);
							// date('F Y', $month), PHP_EOL;
						}elseif(strtolower($periodFormat)=='weekly'){
							//$weeknno = weekOfYear($month);
							///$assessmentArr[] = $weeknno. ' '.date('Y', strtotime($data[$i]["month"]));
						}else{
							if(in_array(date("Y",$month), $assessmentArr)===false)
								$assessmentArr[] = date('Y', $month);
						}
						 $month = strtotime("+1 month", $month);					 
					}
				}
				
				if($user_id==11668200){
                    echo "<pre>";
                    print_r($assessmentArr);
                    echo $toDate."==>".$getMon;
                    print_r($data);
				}
				$asArrYr2Othr=[];
				for($i = 0; $i < count($data); $i++){
					 $yValue = '';
					 $year=date("Y",$time);	
					 $newHeaderColumn = $data[$i][$headerColumn];
					if(strtolower($periodFormat)=='monthly'){
						$time=strtotime($data[$i]["formattedDate"]);
						$month=strtolower($resourceType)=='app'?date('M', $time):date("F",$time);
						$year=date("Y",$time);	
						$yValue = $month . ' ' . $year;							
					 }	 
					 if(strtolower($periodFormat)=='yearly'){
						$time=strtotime($data[$i]["formattedDate"]);
						$year=date("Y",$time);	
						$yValue = $year;				
						if(strtolower($newHeaderColumn)!='others')
							$asArrYr2[$newHeaderColumn] += $data[$i]["total_quantity"];
						else
							$asArrYr2Othr['Others'] += $data[$i]["total_quantity"];
					 }	 
					 if(strtolower($periodFormat)=='quarterly'){
						$yValue = $data[$i]["qtrYear"];
						$year = end(explode(' ',$yValue));
					 }
					 
					 if(strtolower($periodFormat)=='weekly'){
						/*$year = date('Y', strtotime($data[$i]["formattedDate"]));
						if(trim($data[$i]["week"], 'W ')>=52 && date('d', strtotime($data[$i]["formattedDate"]))<=2)
							$year=$year - 1;
						$yValue = $data[$i]["week"]. ' '.$year;*/						
						$year = $data[$i][$yearColumnName];						
						$yValue = $data[$i]["week"]. ' '.$year;
						/*$getyr=explode(" ",$data[$i]["formattedDate"]);
						$year=$getyr[2];
						$yValue =$data[$i]["formattedDate"];*/
					}
					
					$asArr[$newHeaderColumn][$yValue] = $data[$i]["total_quantity"];
					$asArr1[$yValue][$newHeaderColumn] = $data[$i]["total_quantity"];
					$asArrYr[$year][$newHeaderColumn] += $data[$i]["total_quantity"];
					/* if ($attr['memid'] == 56350 ){
					print '<pre>'. trim($data[$i]["week"], 'W ') .'---'. $yValue.'</pre>';
					} */
				}
				
				if(strtolower($periodFormat)=='yearly'){
					arsort($asArrYr2);
					$asArrYr2 = array_merge($asArrYr2, $asArrYr2Othr);
				}
				
				$asArrYrFin=array();
				$asArrYrTbl=array();
				
				
				ksort($asArrYr);
				if(isset($asArrYr2Othr['Others'])){
					foreach($asArrYr as $yr=>$colArr){
						arsort($colArr);
						$others = $colArr['Others'];
						unset($colArr['Others']);
						$colArr['Others'] = $others;
						$asArrYr[$yr] = $colArr;					
						/* $asArrYr[$yr] = array_keys(array_slice($colArr,0,5));
						$asArrYrTbl[$yr] = array_keys(array_slice($colArr,0,10)); */
						//$asArrYrFin = array_merge($asArrYrFin,array_slice($colArr,0,5));					
					}
				}
				if ($attr['memid'] == 56350 ){
					//print_r($assessmentArr);
					//echo '<pre>', print_r($asArrYr), print_r($asArrYr2),'</pre>';
					//echo '<pre>', print_r($asArr),'</pre>';
				}
				$asArr2 = array();
				
				foreach($asArrYr as $yr=>$colNameArr):
					$o=0;
					foreach($colNameArr as $colName=>$colVal):
						$colValArr = $asArr[$colName];						
					//foreach($asArr as $colName=>$colValArr):
						$o++;
						$_colName = $o>5?'Others':$colName;
						if ($attr['memid'] == 56350 ){
							//print_r($assessmentArr);
							//echo '<pre>',$colName,print_r($colValArr), $_colName,'<br></pre>';die;
						}
						foreach($colValArr as $mYKey=>$mYValue):
							$yrMY = end(explode(' ',$mYKey));
							if($yrMY==$yr){
								//$_colName = in_array($colName, $asArrYr[$yrMY])!==false?$colName:'Others';
								if(isset($asArr2[$_colName][$mYKey]))
									if($_colName=='Others')
										$asArr2[$_colName][$mYKey] += $mYValue; 
									else
										$asArr2[$_colName][$mYKey] = $mYValue; 
								else
									$asArr2[$_colName][$mYKey] = $mYValue; 
							}
						endforeach;
					endforeach;
				endforeach;
				$tillTxt='';
				if (1){
					//print_r($assessmentArr);
// 					if ($attr['memid'] == 116682 ){
// 					echo '<pre>', print_r($asArr2),'</pre>';die;
// 					}
					foreach($asArr2 AS $colName=>$colValArr):
						foreach($assessmentArr as $mntYr):
							if($mntYr==date('Y')){
								$tillTxt=' till';
							}elseif(strpos($mntYr, "Q")!==false){
								$tillTxt='';
								$monthToday = date('n',strtotime($toDate12));
								if ($attr['memid'] == 1155660000000 ){
									echo "<br/>mnthtoday==>";print_r($monthToday);
								}
								$quarterToday = ceil($monthToday / 3);
								if ($attr['memid'] == 11556600000000 ){
									echo "<br/>quarterToday:==>";
									print_r($quarterToday);
									echo "<br/>mnt==>";
									echo $mntYr;
								}
								if('Q'.$quarterToday.' '.date('Y')==$mntYr){
									
									if($monthToday % 3!=0){
										$tillTxt=' till';
									}
								}
							}else{
								$tillTxt='';
							} 
							
							if($tillTxt!='' && strtolower($resourceType)=='app' ){
								$tillTxt = $tillTxt.' ('.date("M Y", strtotime($toDate12)).')'; 
							}
							
							if(isset($colValArr[$mntYr])){
								///echo '<pre>', $mntYr, '--', date('Y'),'</pre>';
								$atcdds[$colName]['data'][] = array(
										'key' => trim($mntYr).$tillTxt,
										'value' => ($_round_2==0)?number_format((int) round($colValArr[$mntYr])):number_format((float) $colValArr[$mntYr], 2)
								);
							}else{								
								$atcdds[$colName]['data'][] = array(
										'key' => trim($mntYr).$tillTxt,
										'value' => ''
									);
							}					
						endforeach;
					endforeach;
				}ELSE{
								
					foreach($asArr2 AS $colName=>$colValArr):
						foreach($assessmentArr as $mntYr):
							if(isset($colValArr[$mntYr])){
								$atcdds[$colName]['data'][] = array(
									'key' => trim($mntYr),
									'value' => $colValArr[$mntYr]
								);
							}else{
								$atcdds[$colName]['data'][] = array(
									'key' => trim($mntYr),
									'value' => ''
								);
							}					
						endforeach;
					endforeach;
				}
				
				$asArr2Tbl=array();
				if ($attr['memid'] == 5635000 ){
					//print_r($assessmentArr);
					///echo '<pre>', print_r($asArrYr), print_r($asArrYr2),'</pre>';
					//echo '<pre>', print_r($asArr),'</pre>';
				}
				foreach($asArrYr as $yr=>$colNameArr):
					$o=0;
					foreach($colNameArr as $colName=>$colVal):
						$colValArr = $asArr[$colName];						
					//foreach($asArr as $colName=>$colValArr):
						$o++;
						$_colName = $o>10?'Others':$colName;
						foreach($colValArr as $mYKey=>$mYValue):
							$yrMY = end(explode(' ',$mYKey));
							if($yrMY==$yr){
								if(isset($asArr2Tbl[$yr][$_colName][$mYKey]))
									if($_colName=='Others')
										$asArr2Tbl[$yr][$_colName][$mYKey] += $mYValue; 
									else
										$asArr2Tbl[$yr][$_colName][$mYKey] = $mYValue; 
								else
									$asArr2Tbl[$yr][$_colName][$mYKey] = $mYValue; 
							}
						endforeach;
						if(strtolower($periodFormat)!='yearly'){
							$asArr2Tbl[$yr][$_colName]['Total'] += $colVal;
						}
					endforeach;
				endforeach;
				
				
				if ($attr['memid'] == 56350 ){
					///print_r($assessmentArr);
					///echo '<pre>', $title, print_r($asArr2Tbl),'</pre>';
				}
				
				if(strtolower($resourceType)!='app'){
				///if(strtolower($periodFormat)=='yearly'){
					if(strtolower($periodFormat)=='yearly'){
						$colspan=count($asArrYr)+1;
						$monthsArray=array();
						$childGraph='<table id="childrenee'.$headerColumn.'" class="table table-bordered"><tbody><tr style="visibility:hidden"><td colspan="'.$colspan.'"></td></tr><tr style="visibility:hidden"><td colspan="'.$colspan.'"></td></tr>';
						$minYear1 = $minDateArr[0];
						$maxYear1 = $maxDateArr[0];
						
						$tillTxt='';
						if($maxYear1==date('Y')){
							$tillTxt=' till'.' ('.date('M-Y',strtotime($toDate12)).')';
						}
						
						$childGraph.='<tr><th colspan="'. ($colspan-2) .'">'.$title.' - '.$minYear1.' - '.$maxYear1.$tillTxt.' (Quantity in '.$unit.')</th><th colspan="2"><div class="primaryHeaderLogo" style="margin-right: 25px;display:none;"><img src="'.$logoPath.'" width="143" height="27"></div></th></tr><tr><th style="display:none;" colspan="'.$colspan.'">&nbsp;</th></tr>';
						$childGraph.='<tr><th>'.ucfirst(strtolower(str_replace('_', ' ',$headerColumn))).'</th>';
						foreach($asArrYr as $yr=>$val):
							if($tillTxt!='' && $yr==date('Y',strtotime($toDate12)))
								$yr .= ' '.$tillTxt;
							$childGraph.='<th>'.$yr.'</th>';
						endforeach;
						$childGraph.='</tr>';
						$o=0;
						$othersTotal=array();
						foreach($asArrYr2 AS $colName=>$totalVal):
							$o++;
							if($colName){
								if($o<=10)
									$childGraph.='<tr><th>'.$colName.'</th>';
								
								foreach($asArrYr as $yr=>$val):
									$yrVal = $val[$colName];
									if($o>10){
										if($yrVal)
											$othersTotal[$yr]+=$yrVal;
										else
											$othersTotal[$yr]+=0;
											
										$mntYrTotal[$yr]+=$yrVal;
									}else{									
										if($yrVal){
											//$childGraph.='<td>'.number_format($yrVal).'</td>';
											$mntYrTotal[$yr]+=$yrVal;
											$yrVal = ($_round_2==0)?number_format((int) round($yrVal)):number_format((float) $yrVal, 2);
											$childGraph.='<td>'.$yrVal.'</td>';
											
										}else
											$childGraph.='<td></td>';
									}
								endforeach;
								$childGraph.='</tr>';
							}
						endforeach;	
						if(!empty($othersTotal) && count($othersTotal)>0){
							$childGraph.='<tr><th>Others</th>';
							foreach($asArrYr as $yr=>$val):
								$yrVal = $othersTotal[$yr];
								if($yrVal){
									$yrVal = ($unit!='MnT')?number_format((int) round($yrVal)):number_format((float) $yrVal, 2);
									$childGraph.='<td>'.$yrVal.'</td>';					
								}else
									$childGraph.='<td></td>';
							endforeach;
							$childGraph.='</tr>';
						}
							$GrandTotal=0;
							$childGraph.='<tr>';
							$childGraph.='<th>Grand Total</th>';
							foreach($asArrYr as $yr=>$val):
								if($mntYrTotal[$yr]){
									$GrandTotal+=$mntYrTotal[$yr];
									$mntYrTotal[$yr] = ($_round_2==0)?number_format((int) round($mntYrTotal[$yr])):number_format((float) $mntYrTotal[$yr], 2);
									////$childGraph.='<th>'.number_format($mntYrTotal[$yr]).'</th>';
									$childGraph.='<th>'.$mntYrTotal[$yr].'</th>';
									
								}else
									$childGraph.='<th></th>';
							endforeach;
							///$childGraph.='<th>'.number_format($GrandTotal).'</th>';
							$childGraph.='</tr>';
							
						$childGraph.='<tr  style="visibility:hidden"><td colspan="'.$colspan.'">All Quantity in '.$unit.'</td></tr></tbody></table>';
						
					}else{
						if(strtolower($periodFormat)=='monthly'){
							$colspan=14;
							$monthsArray = array(
								'Jan' => 'January',
								'Feb' => 'February',
								'Mar' => 'March',
								'Apr' => 'April',
								'May' => 'May',
								'Jun' => 'June',
								'Jul' => 'July',
								'Aug' => 'August',
								'Sep' => 'September',
								'Oct' => 'October',
								'Nov' => 'November',
								'Dec' => 'December'
							);
						}elseif(strtolower($periodFormat)=='quarterly'){
							$colspan=6;
							$monthsArray = array(
								'Q1' => 'Q1',
								'Q2' => 'Q2',
								'Q3' => 'Q3',
								'Q4' => 'Q4'
							);
						}elseif(strtolower($periodFormat)=='weekly'){
							$colspan=12;
							$monthsArray = array();
							for($i=1; $i<= 52; $i++){
								$monthsArray[] = 'W '.$i;
							}
						}
							
						$childGraph='<table id="childrenee'.$headerColumn.'" class="table table-bordered"><tbody><tr style="visibility:hidden"><td colspan="'.$colspan.'"></td></tr><tr style="visibility:hidden"><td colspan="'.$colspan.'"></td></tr>';
						if(strtolower($periodFormat)=='weekly'){
							
							foreach($asArr2Tbl AS $yr=>$colValArr1):
								$childGraph.='<tr><th colspan="'. ($colspan-2) .'">'.$title.' - '.$yr.' (Quantity in '.$unit.')</th><th colspan="2"><div class="primaryHeaderLogo" style="margin-right: 25px;display:none;"><img src="'.$logoPath.'" width="143" height="27"></div></th></tr><tr><th style="display:none;" colspan="'.$colspan.'">&nbsp;</th></tr>';
								$childGraph.='<tr><th></th>';
								foreach($colValArr1 as $colName=>$colValArr):
									$childGraph.='<th>'.$colName.'</th>';
								endforeach;
								$childGraph.='<th>Total</th>';
								$childGraph.='</tr>';
								$mntYrTotal=array();
								foreach($monthsArray AS $mnt):
								if(in_array($mnt.' '.$yr, $assessmentArr)!==false):
									$childGraph.='<tr>';
									$childGraph.='<th>'.$mnt.'</th>';
									$_colTotal=0;
									foreach($colValArr1 as $colName=>$colValArr):
									$_colName= $colName;
										$mntYr = $mnt.' '.$yr;
										$val1= isset($colValArr[$mntYr])?number_format($colValArr[$mntYr],2,'.',''):'';
										if(isset($colValArr[$mntYr]))
											$_colTotal+=$colValArr[$mntYr];
										//$val1= isset($colValArr[$mntYr])?number_format($colValArr[$mntYr],2,'.',''):'-';
										$childGraph.='<td>'.$val1.'</td>';
										if(isset($colValArr[$mntYr])){
											$atcddsTbl[$yr][$colName]['data'][] = array(
												'key' => trim($mntYr),
												'value' => $colValArr[$mntYr]
											);
										}else{
											$atcddsTbl[$yr][$colName]['data'][] = array(
												'key' => trim($mntYr),
												'value' => ''
											);
										}				
									endforeach;
									////$total1 = $asArr2Tbl[$yr][$colName]['Total'];
									
									if($_colTotal){
										$_colTotal = ($_round_2==0)?number_format((int) round($_colTotal)):number_format((float) $_colTotal, 2);
										//$childGraph.='<th>'.number_format($_colTotal).'</th>';
										$childGraph.='<th>'.$_colTotal.'</th>';
									}else
										$childGraph.='<th>-</th>';
									$childGraph.='</tr>';
									
									//$atcddsTbl[$yr][$colName]['data'][]= array(
									//			'key' => 'Total',
									//			'value' => $colValArr['Total']
									//		);
									endif;
								endforeach;
								$GrandTotal=0;
								$childGraph.='<tr>';
								$childGraph.='<th>Grand Total</th>';
								foreach($colValArr1 as $colName=>$colValArr):
									if($colValArr['Total']){
										$GrandTotal+=$colValArr['Total'];
										$colValArr['Total'] = ($colValArr['Total']==0)?number_format((int) round($colValArr['Total'])):number_format((float) $colValArr['Total'], 2);
									///	$childGraph.='<th>'.number_format($colValArr['Total']).'</th>';
										$childGraph.='<th>'.$colValArr['Total'].'</th>';
										
									}else{
										$childGraph.='<th></th>';
									}
								endforeach;
								$childGraph.='<th>'.number_format($GrandTotal).'</th>';
								$childGraph.='</tr>';
								
								$childGraph.='<tr style="visibility:hidden"><td colspan="'.$colspan.'"></td></tr><tr style="visibility:hidden"><td colspan="'.$colspan.'"></td></tr>';
							endforeach;
						
						}else{
							foreach($asArr2Tbl AS $yr=>$colValArr1):
								if( $attr['filters']['app_type'] == 'CM' )
									$logoPath='https://www.coalmint.com/images/logo.png';
								else
									$logoPath='https://www.steelmint.com/images/logo.png';
								$tillTxt='';
								if(strtolower($periodFormat)=='quarterly' && date('Y')==$yr){
									
									$monthToday = date('n',strtotime($toDate12));
									$quarterToday = ceil($monthToday / 3);
									
									if($monthToday % 3!=0){
										$tillTxt=' till'.' ('.date('M-Y',strtotime($toDate12)).')'; 
									}
								}
								$childGraph.='<tr><th colspan="'. ($colspan-2) .'">'.$title.' - '.$yr.$tillTxt.' (Quantity in '.$unit.')</th><th colspan="2"><div class="primaryHeaderLogo" style="margin-right: 25px;display:none;"><img src="'.$logoPath.'" width="143" height="27"></div></th></tr><tr><th style="display:none;" colspan="'.$colspan.'">&nbsp;</th></tr>';
								$childGraph.='<tr><th></th>';
								foreach($monthsArray as $mnt):
									$childGraph.='<th>'.$mnt.'</th>';
								endforeach;
								$childGraph.='<th>Total</th>';
								$childGraph.='</tr>';
								$mntYrTotal=array();
								foreach($colValArr1 AS $colName=>$colValArr):
									$childGraph.='<tr>';
									$childGraph.='<th>'.$colName.'</th>';
									foreach($monthsArray as $mnt):
										$mntYr = $mnt.' '.$yr;
										///$val1= isset($colValArr[$mntYr])?number_format($colValArr[$mntYr]):'';
										$val1= isset($colValArr[$mntYr])?($unit!='MnT')?number_format((int) round($colValArr[$mntYr])):(number_format((float) $colValArr[$mntYr], 2)):'';
										if($colValArr[$mntYr])
											$mntYrTotal[$mnt]+=$colValArr[$mntYr];
										//$val1= isset($colValArr[$mntYr])?number_format($colValArr[$mntYr],2,'.',''):'-';
										
										$childGraph.='<td>'.$val1.'</td>';
										if(isset($colValArr[$mntYr])){
											$atcddsTbl[$yr][$colName]['data'][] = array(
												'key' => trim($mntYr),
												'value' => $colValArr[$mntYr]
											);
										}else{
											$atcddsTbl[$yr][$colName]['data'][] = array(
												'key' => trim($mntYr),
												'value' => ''
											);
										}				
									endforeach;
									$total1 = $asArr2Tbl[$yr][$colName]['Total'];
									if ($unit=='MnT')
										$childGraph.='<th>'.number_format((float)$total1,2).'</th>';
									else
										$childGraph.='<th>'.number_format($total1).'</th>';
									$childGraph.='</tr>';
									
									//$atcddsTbl[$yr][$colName]['data'][]= array(
									//			'key' => 'Total',
									//			'value' => $colValArr['Total']
									//		);
								endforeach;
								$GrandTotal=0;
								$childGraph.='<tr>';
								$childGraph.='<th>Grand Total</th>';
								foreach($monthsArray as $mnt):
									if($mntYrTotal[$mnt]){
										if ($unit=='MnT')
											$childGraph.='<th>'.number_format((float)$mntYrTotal[$mnt],2).'</th>';
										else
											$childGraph.='<th>'.number_format($mntYrTotal[$mnt]).'</th>';
										$GrandTotal+=$mntYrTotal[$mnt];
									}else{
										$childGraph.='<th></th>';
									}
								endforeach;
								if ($unit=='MnT')
									$childGraph.='<th>'.number_format((float)$GrandTotal,2).'</th>';
								else
									$childGraph.='<th>'.number_format($GrandTotal).'</th>';
								
								$childGraph.='</tr>';
								
								$childGraph.='<tr style="visibility:hidden"><td colspan="'.$colspan.'"></td></tr><tr style="visibility:hidden"><td colspan="'.$colspan.'"></td></tr>';
							endforeach;
						}$childGraph.='<tr  style="visibility:hidden"><td colspan="'.$colspan.'">All Quantity in '.$unit.'</td></tr></tbody></table>';
					}
				}
				if ($attr['memid'] == 56350 ){
					//print_r($assessmentArr);
					//echo '<pre>', print_r($asArr2), print_r($asArr),'</pre>';
				}
				$responceArray['headerColumn'] = $headerColumn;
				$responceArray['value'] = $atcdds;
				//$responceArray['_round_2'] = $_round_2;
				if(strtolower($resourceType)!='app')
					$responceArray['tblvalue'] = ($attr['memid'] == 5635000)?$atcddsTbl:$childGraph;
				return $responceArray;
			} else {
				return 'false';
			}
			
			
			$firstColHeaderColumnName='';
			if(count($data) > 0){
				for($i = 0; $i < count($data); $i++){
					 $yValue = '';
					 if(strtolower($periodFormat)=='monthly'){
						$time=strtotime($data[$i]["formattedDate"]);
						$month=date("F",$time);
						$year=date("Y",$time);	
						$yValue = $month . ' ' . $year;							
					 }	 
					 if(strtolower($periodFormat)=='yearly'){
						$time=strtotime($data[$i]["formattedDate"]);
						$year=date("Y",$time);	
						$yValue = $year;							
					 }	 
					 if(strtolower($periodFormat)=='quarterly'){
						$yValue = $data[$i]["qtrYear"];
					 }	 
					
					if( empty($properOrderArr[$yValue]) ){
						$properOrderArr[$yValue] = 0;	
						$icnt += 1;
					}	
					if($data[$i]["total_quantity"]>0){
						$properOrderArr[$yValue] += 1;
						if( $properOrderArr[$yValue] == 6 ){
							$newHeaderColumn = "Others";
							$flag = 0;
							foreach($atcdds[$newHeaderColumn]['data'] as $key => $value){
								if( $value['key'] == $yValue ){
									$flag = 1;
									$atcdds[$newHeaderColumn]['data'][$key]['value'] += $data[$i]["total_quantity"];
								}	
							}
							if( $flag == 0 ){
								$atcdds[$newHeaderColumn]['data'][] = array(
									'key' => trim($yValue),
									'value' => $data[$i]["total_quantity"]
								);
							} 
							
						} elseif( $properOrderArr[$yValue] > 6 ){
							$newHeaderColumn = "Others";
							foreach($atcdds[$newHeaderColumn]['data'] as $key => $value){
							    if( $value['key'] == $yValue )
							       $atcdds[$newHeaderColumn]['data'][$key]['value'] += $data[$i]["total_quantity"];
							}	
						} else {	
							$newHeaderColumn = $data[$i][$headerColumn];
							if($firstColHeaderColumnName==''){
								$firstColHeaderColumnName = $newHeaderColumn;
							}
							if( $newHeaderColumn == 'Others' ){
								$newHeaderColumn = "Others";
								$atcdds[$newHeaderColumn]['data'][] = array(
									'key' => trim($yValue),
									'value' => $data[$i]["total_quantity"]
								);
								
								$properOrderArr[$yValue] -= 1;
							} else {
								$newqty = $data[$i]["total_quantity"];
								$atcdds[$newHeaderColumn]['data'][] = array(
									'key' => trim($yValue),
									'value' => number_format($newqty,2)
								);
							}
						}
					}
				}
				
				
			 	if ($attr['memid'] == 56350 ){
	//echo "<pre>";
/* 	print_r($properOrderArr);
foreach($properOrderArr as $k1=>$v1){
	$atcdds[$newHeaderColumn]['data'] = 
} */	
} 
				$responceArray['headerColumn'] = $headerColumn;
				$responceArray['value'] = $atcdds;
				return $responceArray;
			} else {
				return 'false';
			}	
		}			
/* 			if(strtolower($periodFormat)=='monthly'){
			 $explodeFrom = explode(' ', $attr['fromDate']);
			 $fromMonth = date('m',strtotime($explodeFrom[0]));
			 $fromYear = $explodeFrom[1];
	
			 $explodeTo = explode(' ', $attr['toDate']);
			 $toMonth = date('m',strtotime($explodeTo[0]));
			 $toYear = $explodeTo[1];

			 $fromDate = $fromYear.'-'.$fromMonth.'-01';
			 $toDate = $toYear.'-'.$toMonth.'-01';
			 $monthList = $this->getMonthBetweenTwoDates($fromDate,$toDate);
			
			if($getTableByDataType=='tbl_capacity'){
				 $data = DB::table($getTableByDataType)
							->whereNotIn($headerColumn,['','-'])
							->where($yearColumnName, '>=', $fromYear)
							->where($yearColumnName, '<=', $toYear)
							->where($commodityColumn,$commodity)
							->where($columnNameOfRegion,$region)
							->groupBy($yearColumnName,$headerColumn)
							->selectRaw(''.$yearColumnName.','.$headerColumn.',sum('.$quantityColumnName.') as '.$quantityColumnName.'')
							->orderBy($quantityColumnName,'DESC')
							->get();
			} else {
				 $data = DB::table($getTableByDataType)
							->whereNotIn($headerColumn,['','-'])
							->whereIn(DB::raw("CONCAT(`".$monthColumName."`, ' ', `".$yearColumnName."`)"),$monthList)
							->where($commodityColumn,$commodity)
							->where($columnNameOfRegion,$region)
							->groupBy($yearColumnName,$monthColumName,$headerColumn)
							->selectRaw(''.$monthColumName.','.$yearColumnName.','.$headerColumn.',sum('.$quantityColumnName.') as '.$quantityColumnName.'')
							->orderBy($quantityColumnName,'DESC')
							->get();
			}
			
			if($attr['debugMode'] == true){
				$data = DB::table($getTableByDataType)
							->whereNotIn($headerColumn,['','-'])
							->where($yearColumnName, '>=', $fromYear)
							->where($yearColumnName, '<=', $toYear)
							->where($commodityColumn,$commodity)
							->where($columnNameOfRegion,$region)
							->groupBy($yearColumnName,$headerColumn)
							->selectRaw(''.$yearColumnName.','.$headerColumn.',sum('.$quantityColumnName.') as '.$quantityColumnName.'')
							->orderBy($quantityColumnName,'DESC');
							
				echo "<pre>";
				print_r($data->getBindings() );
				die;
			}			
			
			  $variable = json_decode(json_encode($data), true);
			  $responceArray = array();
			  $categoryArray = array();
			  $qtySumArray = array();
			  foreach ($variable as $key => $value) {				   
				$qtySumArray[$value[$headerColumn]] += $value[$quantityColumnName];			  
			  }			  
			  arsort($qtySumArray);			  
			  $qtySumArrayKeys = array_chunk(array_keys($qtySumArray),4);
			  foreach ($variable as $key => $value) {				 
				$newkey = $value[$headerColumn];
				if(in_array($newkey, $qtySumArrayKeys[0])===false){					
					$newkey = 'Others';				 
				}								
				 if(!isset($categoryArray[$newkey]['data'][$value[$monthColumName].' '.$value[$yearColumnName]])){
				  $categoryArray[$newkey]['data'][$value[$monthColumName].' '.$value[$yearColumnName]] = $value[$quantityColumnName];
				}else{
					$categoryArray[$newkey]['data'][$value[$monthColumName].' '.$value[$yearColumnName]] += $value[$quantityColumnName];
				  }
			  }	
			  $categoryArrayOther=[];
			  if(isset($categoryArray['Others'])){				  
				$categoryArrayOther = $categoryArray['Others'];				  
				unset($categoryArray['Others']);				 
				$categoryArray['Others'] = $categoryArrayOther;			  
			 }
			  $atcdds = array();
			  foreach ($categoryArray as $key => $value) {
				foreach ($monthList as $ky => $val) {
				  if(array_key_exists($val, $value['data'])){
					$value['data'][$val] = $value['data'][$val];
					$atcdds[$key]['data'][] = array(
							'key' => $val,
							'value' => $value['data'][$val]>0 ? number_format($value['data'][$val]/$valDivideBy,2):number_format($value['data'][$val],2)
						);
				  }
				}
			  }
			  $responceArray['headerColumn'] = $headerColumn;
			  $responceArray['value'] = $atcdds;
			  return $responceArray;
			 }
		  //return $responceArray;

		  if(strtolower($periodFormat)=='yearly'){
		   $fromYear =trim($attr['fromDate']);
		   $toYear = trim($attr['toDate']);
		   $getYearRange = range($fromYear,$toYear);
			if($getTableByDataType=='tbl_capacity'){
			$data = DB::table($getTableByDataType)->whereNotIn($headerColumn,['','-'])->whereIn($yearColumnName,$getYearRange)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region)->groupBy($yearColumnName,$headerColumn)->selectRaw(''.$yearColumnName.','.$headerColumn.',sum('.$quantityColumnName.') as '.$quantityColumnName.'')->orderBy($quantityColumnName,'DESC')->get();
			}else{
			  $data = DB::table($getTableByDataType)->whereNotIn($headerColumn,['','-'])->whereIn($yearColumnName,$getYearRange)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region)->groupBy($yearColumnName,$headerColumn)->selectRaw(''.$monthColumName.','.$yearColumnName.','.$headerColumn.',sum('.$quantityColumnName.') as '.$quantityColumnName.'')->orderBy($quantityColumnName,'DESC')->get();
			}

			  $variable = json_decode(json_encode($data), true);
			  $responceArray = array();
			  $categoryArray = array();

			  foreach ($variable as $key => $value) {
				 if(!isset($categoryArray[$value[$headerColumn]]['data'][$value[$yearColumnName]])){
				  $categoryArray[$value[$headerColumn]]['data'][$value[$yearColumnName]] = $value[$quantityColumnName];
				}else{
					$categoryArray[$value[$headerColumn]]['data'][$value[$yearColumnName]] += $value[$quantityColumnName];
				  }
			  }

			  $atcdds = array();
			  foreach ($categoryArray as $key => $value) {
				foreach ($getYearRange as $ky => $val) {
				  if(array_key_exists($val, $value['data'])){
					 //$value['data'][$val] = $value['data'][$val]/1000000;
					 $value['data'][$val] = $value['data'][$val];
					$atcdds[$key]['data'][] = array(
							'key' => $val,
							'value' => $value['data'][$val]>0 ? number_format($value['data'][$val]/$valDivideBy,2):number_format($value['data'][$val],2)
						);
				  }
				}
			  }

			  $responceArray['headerColumn'] = $headerColumn;
			  $responceArray['value'] = $atcdds;
			  return $responceArray;
		}

		if(strtolower($periodFormat)=='quarterly'){
		   $explodeFrom = explode(' ', $attr['fromDate']);
		   $explodeTo = explode(' ', $attr['toDate']);

		   $fromMonth = $this->getQuater(strtoupper($explodeFrom[0]));
		   $fromYear = $explodeFrom[1];


		   $toMonth = $this->getQuaterAfter(strtoupper($explodeTo[0]));
		   $toYear = $explodeTo[1];  
			

		   $fromDate = $fromYear.'-'.$fromMonth.'-01';
		   $toDate = $toYear.'-'.$toMonth.'-01';
		   $monthList = $this->getMonthBetweenTwoDates($fromDate,$toDate);

		   $data = DB::table($getTableByDataType)->whereNotIn($headerColumn,['','-'])->whereIn(DB::raw("CONCAT(`".$monthColumName."`, ' ', `".$yearColumnName."`)"),$monthList)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region)->groupBy($yearColumnName,$monthColumName,$headerColumn)->selectRaw(''.$monthColumName.','.$yearColumnName.','.$headerColumn.',sum('.$quantityColumnName.') as '.$quantityColumnName.'')->orderBy($quantityColumnName,'DESC')->get();


			   $variable = json_decode(json_encode($data), true);
			  $responceArray = array();
			  $categoryArray = array();
			 

			  foreach ($variable as $key => $value) {
				 $qter = ceil(date("n", strtotime($value[$monthColumName]))/3);

				if(!isset($categoryArray[$value[$headerColumn]]['data']['Q'.$qter.' '.$value[$yearColumnName]])){
				  $categoryArray[$value[$headerColumn]]['data']['Q'.$qter.' '.$value[$yearColumnName]] = $value[$quantityColumnName];
				}else{
					$categoryArray[$value[$headerColumn]]['data']['Q'.$qter.' '.$value[$yearColumnName]] += $value[$quantityColumnName];
				  }
				}


			  $quarterArray = array(); 
			  foreach ($monthList as $kesdsdy => $sdsddsds) {
				 $explode = explode(' ', $sdsddsds);
				 $qter = ceil(date("n", strtotime($explode[0]))/3);
				 $vaknhm = 'Q'.$qter.' '.$explode[1];
				 if(!in_array($vaknhm,$quarterArray)){
				  $quarterArray[] = $vaknhm;
				 }
			  }  
			  

			  $atcdds = array();
			  foreach ($categoryArray as $key => $value) {
				foreach ($quarterArray as $ky => $val) {
				  if(array_key_exists($val, $value['data'])){
					  //$value['data'][$val] = $value['data'][$val]/1000000;
					  $value['data'][$val] = $value['data'][$val];
					$atcdds[$key]['data'][] = array(
							'key' => $val,
							'value' => $value['data'][$val]>0 ? number_format($value['data'][$val]/$valDivideBy,2):number_format($value['data'][$val],2)
						);
				  }
				}
			  }
			  $responceArray['headerColumn'] = $headerColumn;
			  $responceArray['value'] = $atcdds;
			  return $responceArray;
			}
		  }else{
			return 'false';
		  } */

	}
	public function getChildrenGraphPoint_20_5_2020($attr){
		$getTableByDataType =  DB::table('tbl_stats_datatype_header_list_bm')->where('datatypeName',$attr['dataType'])->value('tableName');
		if($getTableByDataType){
		   $periodFormat = trim($attr['periodFormat']);
		   $commodityColumn = trim($attr['columnNameOfCommodity']);
		   $commodity = trim($attr['commodity']);
			$headerColumn = trim($attr['headerColumn']);
		   
		   if($headerColumn == 'subCommodity'){
			if( $commodityColumn == 'commodity_new' )
				$headerColumn = 'subCommodity';
			else
				$headerColumn = 'subSubCommodity';
		   } 
		   
		   $columnNameOfRegion = trim($attr['columnNameOfRegion']);
		   $region = trim(ucfirst($attr['region']));
			


			  $monthColumName = '';
			  $yearColumnName = '';
			  $quantityColumnName = ''; 
				if($getTableByDataType=='tbl_capacity'){
					$yearColumnName='capacity_year';
					$quantityColumnName='capacity';
				}else if($getTableByDataType=='tbl_production' || $getTableByDataType=='tbl_consumption'){
					$monthColumName= 'month';
					$yearColumnName='year';
					$quantityColumnName = 'quantity';
				}else{
					$monthColumName= 'month_of_loading';
					$yearColumnName='year_of_loading';
					$quantityColumnName = 'quantity';
				}
			$memid = 0;
			//update memId if getting in POST request
			if(isset($attr['memid']) && $attr['memid']>0){
			   $memid = trim($attr['memid']);
			}
			$user = new User($memid, $appType='SM');
			$isAccessible =  $user->isAccessible($resourceType='Web', $section='Statistics_Graph');
			$valDivideBy = 1;
			if($isAccessible>=0){//visitor or upgrade
				/* $periodFormat = 'yearly';
				$attr['fromDate'] = '2017';
				$attr['toDate'] = date('Y');
				$valDivideBy = 3.7; */
				$periodFormat = 'monthly';
				$attr['fromDate'] = 'jul 2019';
				$attr['toDate'] = "dec 2019";//date('Y');
				$valDivideBy = 1;
			}
			
			if(strtolower($periodFormat)=='monthly'){
			 $explodeFrom = explode(' ', $attr['fromDate']);
			 $fromMonth = date('m',strtotime($explodeFrom[0]));
			 $fromYear = $explodeFrom[1];
	
			 $explodeTo = explode(' ', $attr['toDate']);
			 $toMonth = date('m',strtotime($explodeTo[0]));
			 $toYear = $explodeTo[1];

			 $fromDate = $fromYear.'-'.$fromMonth.'-01';
			 $toDate = $toYear.'-'.$toMonth.'-01';
			 $monthList = $this->getMonthBetweenTwoDates($fromDate,$toDate);
			
			if($getTableByDataType=='tbl_capacity'){
				 $data = DB::table($getTableByDataType)
							->whereNotIn($headerColumn,['','-'])
							->where($yearColumnName, '>=', $fromYear)
							->where($yearColumnName, '<=', $toYear)
							->where($commodityColumn,$commodity)
							->where($columnNameOfRegion,$region)
							->groupBy($yearColumnName,$headerColumn)
							->selectRaw(''.$yearColumnName.','.$headerColumn.',sum('.$quantityColumnName.') as '.$quantityColumnName.'')
							->orderBy($quantityColumnName,'DESC')
							->get();
			} else {
				 $data = DB::table($getTableByDataType)
							->whereNotIn($headerColumn,['','-'])
							->whereIn(DB::raw("CONCAT(`".$monthColumName."`, ' ', `".$yearColumnName."`)"),$monthList)
							->where($commodityColumn,$commodity)
							->where($columnNameOfRegion,$region)
							->groupBy($yearColumnName,$monthColumName,$headerColumn)
							->selectRaw(''.$monthColumName.','.$yearColumnName.','.$headerColumn.',sum('.$quantityColumnName.') as '.$quantityColumnName.'')
							->orderBy($quantityColumnName,'DESC')
							->get();
			}
			
			if($attr['debugMode'] == true){
				$data = DB::table($getTableByDataType)
							->whereNotIn($headerColumn,['','-'])
							->where($yearColumnName, '>=', $fromYear)
							->where($yearColumnName, '<=', $toYear)
							->where($commodityColumn,$commodity)
							->where($columnNameOfRegion,$region)
							->groupBy($yearColumnName,$headerColumn)
							->selectRaw(''.$yearColumnName.','.$headerColumn.',sum('.$quantityColumnName.') as '.$quantityColumnName.'')
							->orderBy($quantityColumnName,'DESC');
							
				echo "<pre>";
				print_r($data->getBindings() );
				die;
			}			
			
			  $variable = json_decode(json_encode($data), true);
			  $responceArray = array();
			  $categoryArray = array();
			  $qtySumArray = array();
			  foreach ($variable as $key => $value) {				   
				$qtySumArray[$value[$headerColumn]] += $value[$quantityColumnName];			  
			  }			  
			  arsort($qtySumArray);			  
			  $qtySumArrayKeys = array_chunk(array_keys($qtySumArray),4);
			  foreach ($variable as $key => $value) {				 
				$newkey = $value[$headerColumn];
				if(in_array($newkey, $qtySumArrayKeys[0])===false){					
					$newkey = 'Others';				 
				}								
				 if(!isset($categoryArray[$newkey]['data'][$value[$monthColumName].' '.$value[$yearColumnName]])){
				  $categoryArray[$newkey]['data'][$value[$monthColumName].' '.$value[$yearColumnName]] = $value[$quantityColumnName];
				}else{
					$categoryArray[$newkey]['data'][$value[$monthColumName].' '.$value[$yearColumnName]] += $value[$quantityColumnName];
				  }
				  //$qtySumArray[$value[$headerColumn]] += $value[$quantityColumnName];
			  }	
			  $categoryArrayOther=[];
			  if(isset($categoryArray['Others'])){				  
				$categoryArrayOther = $categoryArray['Others'];				  
				unset($categoryArray['Others']);				 
				$categoryArray['Others'] = $categoryArrayOther;			  
			 }
			 /*  echo "<pre>";
			  print_r($categoryArray); 			  print_r($qtySumArrayKeys); 
			  */
				
			  $atcdds = array();
			  foreach ($categoryArray as $key => $value) {
				foreach ($monthList as $ky => $val) {
				  if(array_key_exists($val, $value['data'])){
					//$value['data'][$val] = $value['data'][$val]/1000000;
					$value['data'][$val] = $value['data'][$val];
					$atcdds[$key]['data'][] = array(
							'key' => $val,
							'value' => $value['data'][$val]>0 ? number_format($value['data'][$val]/$valDivideBy,2):number_format($value['data'][$val],2)
						);
				  }
				}
			  }
			  $responceArray['headerColumn'] = $headerColumn;
			  $responceArray['value'] = $atcdds;
			  return $responceArray;
			 }
		  //return $responceArray;

		  if(strtolower($periodFormat)=='yearly'){
		   $fromYear =trim($attr['fromDate']);
		   $toYear = trim($attr['toDate']);
		   $getYearRange = range($fromYear,$toYear);
			if($getTableByDataType=='tbl_capacity'){
			$data = DB::table($getTableByDataType)->whereNotIn($headerColumn,['','-'])->whereIn($yearColumnName,$getYearRange)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region)->groupBy($yearColumnName,$headerColumn)->selectRaw(''.$yearColumnName.','.$headerColumn.',sum('.$quantityColumnName.') as '.$quantityColumnName.'')->orderBy($quantityColumnName,'DESC')->get();
			}else{
			  $data = DB::table($getTableByDataType)->whereNotIn($headerColumn,['','-'])->whereIn($yearColumnName,$getYearRange)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region)->groupBy($yearColumnName,$headerColumn)->selectRaw(''.$monthColumName.','.$yearColumnName.','.$headerColumn.',sum('.$quantityColumnName.') as '.$quantityColumnName.'')->orderBy($quantityColumnName,'DESC')->get();
			}

			  $variable = json_decode(json_encode($data), true);
			  $responceArray = array();
			  $categoryArray = array();

			 /* echo "<pre>";
			  print_r($variable);
			  die();*/
			  foreach ($variable as $key => $value) {
				 if(!isset($categoryArray[$value[$headerColumn]]['data'][$value[$yearColumnName]])){
				  $categoryArray[$value[$headerColumn]]['data'][$value[$yearColumnName]] = $value[$quantityColumnName];
				}else{
					$categoryArray[$value[$headerColumn]]['data'][$value[$yearColumnName]] += $value[$quantityColumnName];
				  }
			  }

			  $atcdds = array();
			  foreach ($categoryArray as $key => $value) {
				foreach ($getYearRange as $ky => $val) {
				  if(array_key_exists($val, $value['data'])){
					 //$value['data'][$val] = $value['data'][$val]/1000000;
					 $value['data'][$val] = $value['data'][$val];
					$atcdds[$key]['data'][] = array(
							'key' => $val,
							'value' => $value['data'][$val]>0 ? number_format($value['data'][$val]/$valDivideBy,2):number_format($value['data'][$val],2)
						);
				  }/* else{
					$atcdds[$key]['data'][] = array(
							'key' => $val,
							'value' => 0 
						);
				  } */
				}
			  }

			  /*$atcdds = array();
			  foreach ($categoryArray as $key => $value) {
				foreach ($value as $ky => $val) {
				  foreach ($val as $k => $vae) {
					foreach ($getYearRange as $keysds => $sdsd) {
					if(array_key_exists($sdsd, $val)){
					  $atcdds[$key][$ky][] = array(
						'key' => $k,
						'value' => number_format($vae,2) 
					  );
					 }else{
						$atcdds[$key][$ky][] = array(
						'key' => $sdsd,
						'value' => 0 
					  );
					 }
					}
					
				  }
				}
			  }*/

			  $responceArray['headerColumn'] = $headerColumn;
			  $responceArray['value'] = $atcdds;
			  return $responceArray;

			  /*foreach ($variable as $key => $value) {
				$categoryArray[$value[$yearColumnName]][][$value[$headerColumn]] = number_format($value[$quantityColumnName],2);
			  }
			  return $categoryArray;*/
			
		}

		if(strtolower($periodFormat)=='quarterly'){
		   $explodeFrom = explode(' ', $attr['fromDate']);
		   $explodeTo = explode(' ', $attr['toDate']);

		   $fromMonth = $this->getQuater(strtoupper($explodeFrom[0]));
		   $fromYear = $explodeFrom[1];


		   $toMonth = $this->getQuaterAfter(strtoupper($explodeTo[0]));
		   $toYear = $explodeTo[1];  
			

		   $fromDate = $fromYear.'-'.$fromMonth.'-01';
		   $toDate = $toYear.'-'.$toMonth.'-01';
		   $monthList = $this->getMonthBetweenTwoDates($fromDate,$toDate);

		   $data = DB::table($getTableByDataType)->whereNotIn($headerColumn,['','-'])->whereIn(DB::raw("CONCAT(`".$monthColumName."`, ' ', `".$yearColumnName."`)"),$monthList)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region)->groupBy($yearColumnName,$monthColumName,$headerColumn)->selectRaw(''.$monthColumName.','.$yearColumnName.','.$headerColumn.',sum('.$quantityColumnName.') as '.$quantityColumnName.'')->orderBy($quantityColumnName,'DESC')->get();

		 /* 
		  if($getTableByDataType=='tbl_production' || $getTableByDataType=='tbl_capacity'){

			$data = DB::table($getTableByDataType)->whereIn($yearColumnName,$monthList)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region)->groupBy($yearColumnName,$headerColumn)->selectRaw(''.$yearColumnName.','.$headerColumn.',sum('.$quantityColumnName.') as '.$quantityColumnName.'')->orderBy($quantityColumnName,'DESC')->get();
		   }else{
			  
		   }*/
			   $variable = json_decode(json_encode($data), true);
			  $responceArray = array();
			  $categoryArray = array();
			 

			  foreach ($variable as $key => $value) {
				 $qter = ceil(date("n", strtotime($value[$monthColumName]))/3);

				if(!isset($categoryArray[$value[$headerColumn]]['data']['Q'.$qter.' '.$value[$yearColumnName]])){
				  $categoryArray[$value[$headerColumn]]['data']['Q'.$qter.' '.$value[$yearColumnName]] = $value[$quantityColumnName];
				}else{
					$categoryArray[$value[$headerColumn]]['data']['Q'.$qter.' '.$value[$yearColumnName]] += $value[$quantityColumnName];
				  }
				}


			  $quarterArray = array(); 
			  foreach ($monthList as $kesdsdy => $sdsddsds) {
				 $explode = explode(' ', $sdsddsds);
				 $qter = ceil(date("n", strtotime($explode[0]))/3);
				 $vaknhm = 'Q'.$qter.' '.$explode[1];
				 if(!in_array($vaknhm,$quarterArray)){
				  $quarterArray[] = $vaknhm;
				 }
			  }  
			  
			 /* echo "<pre>";
			  print_r($quarterArray);
			  die();*/
			  
			  $atcdds = array();
			  foreach ($categoryArray as $key => $value) {
				foreach ($quarterArray as $ky => $val) {
				  if(array_key_exists($val, $value['data'])){
					  //$value['data'][$val] = $value['data'][$val]/1000000;
					  $value['data'][$val] = $value['data'][$val];
					$atcdds[$key]['data'][] = array(
							'key' => $val,
							'value' => $value['data'][$val]>0 ? number_format($value['data'][$val]/$valDivideBy,2):number_format($value['data'][$val],2)
						);
				  }/* else{
					$atcdds[$key]['data'][] = array(
							'key' => $val,
							'value' => 0 
						);
				  } */
				}
			  }
			  $responceArray['headerColumn'] = $headerColumn;
			  $responceArray['value'] = $atcdds;
			  return $responceArray;
			}
		  }else{
			return 'false';
		  }
	}
//chnages on 18Dec19
	public function getChildrenGraphPointBak($attr){
        $getTableByDataType =  DB::table('tbl_stats_datatype_header_list_bm')->where('datatypeName',$attr['dataType'])->value('tableName');
           if($getTableByDataType){
               $periodFormat = trim($attr['periodFormat']);
               $commodityColumn = trim($attr['columnNameOfCommodity']);
               $commodity = trim($attr['commodity']);
               $headerColumn = trim($attr['headerColumn']);
               $columnNameOfRegion = trim($attr['columnNameOfRegion']);
               $region = trim(ucfirst($attr['region']));
                  $monthColumName = '';
                  $yearColumnName = '';
                  $quantityColumnName = ''; 
                  if($getTableByDataType=='tbl_capacity'){
                   $yearColumnName='capacity_year';
                   $quantityColumnName='capacity';
                  }else if($getTableByDataType=='tbl_production' || $getTableByDataType=='tbl_consumption'){
                  $monthColumName= 'month';
                  $yearColumnName='year';
                  $quantityColumnName = 'quantity';
                  }else{
                   $monthColumName= 'month_of_loading';
                   $yearColumnName='year_of_loading';
                   $quantityColumnName = 'quantity';
                  }
              if(strtolower($periodFormat)=='monthly'){
                 $explodeFrom = explode(' ', $attr['fromDate']);
                 $fromMonth = date('m',strtotime($explodeFrom[0]));
                 $fromYear = $explodeFrom[1];
        
                 $explodeTo = explode(' ', $attr['toDate']);
                 $toMonth = date('m',strtotime($explodeTo[0]));
                 $toYear = $explodeTo[1];
                 $fromDate = $fromYear.'-'.$fromMonth.'-01';
                 $toDate = $toYear.'-'.$toMonth.'-01';
                 $monthList = $this->getMonthBetweenTwoDates($fromDate,$toDate);
                 /*echo "<pre>";
                 print_r($monthList);
                 die();*/
                 $data = DB::table($getTableByDataType)->whereIn(DB::raw("CONCAT(`".$monthColumName."`, ' ', `".$yearColumnName."`)"),$monthList)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region)->groupBy($yearColumnName,$monthColumName,$headerColumn)->selectRaw(''.$monthColumName.','.$yearColumnName.','.$headerColumn.',sum('.$quantityColumnName.') as '.$quantityColumnName.'')->orderBy($quantityColumnName,'DESC')->get();
                  $variable = json_decode(json_encode($data), true);
                  $responceArray = array();
                  $categoryArray = array();
                  foreach ($variable as $key => $value) {
                     /*$categoryArray[$value[$headerColumn]]['data'][$value[$monthColumName].' '.$value[$yearColumnName]] = number_format($value[$quantityColumnName],2);*/
                     if(!isset($categoryArray[$value[$headerColumn]]['data'][$value[$monthColumName].' '.$value[$yearColumnName]])){
                      $categoryArray[$value[$headerColumn]]['data'][$value[$monthColumName].' '.$value[$yearColumnName]] = $value[$quantityColumnName];
                    }else{
                        $categoryArray[$value[$headerColumn]]['data'][$value[$monthColumName].' '.$value[$yearColumnName]] += $value[$quantityColumnName];
                      }
                  }
                  
                  //echo "<pre>";
                  //print_r($categoryArray);
                  //die();
                  $atcdds = array();
                  foreach ($categoryArray as $key => $value) {
                    foreach ($monthList as $ky => $val) {
                      if(array_key_exists($val, $value['data'])){
                        $atcdds[$key]['data'][] = array(
                                'key' => $val,
                                'value' => number_format($value['data'][$val],2) 
                            );
                      }else{
                        $atcdds[$key]['data'][] = array(
                                'key' => $val,
                                'value' => 0 
                            );
                      }
                    }
                  }
                  //die();
                 /* foreach ($categoryArray as $key => $value) {
                    foreach ($value as $ky => $val) {
                      foreach ($monthList as $ksds => $sdsddsd) {
                      foreach ($val as $k => $vae) {
                        $checkExist = array_search($k,$monthList);
                        if($checkExist){
                         $atcdds[$key][$ky][] = array(
                                'key' => $k,
                                'value' => number_format($vae,2) 
                            );
                          
                         }else{
                           $atcdds[$key][$ky][] = array(
                                'key' => $monthList[$checkExist],
                                'value' => 0 
                            );
                         }
                        }
                      }
                      }
                    }*/
                  $responceArray['headerColumn'] = $headerColumn;
                  $responceArray['value'] = $atcdds;
                  return $responceArray;
                 }
              //return $responceArray;
              if(strtolower($periodFormat)=='yearly'){
               $fromYear =trim($attr['fromDate']);
               $toYear = trim($attr['toDate']);
               $getYearRange = range($fromYear,$toYear);
			   //DB::enableQueryLog();
                $data = DB::table($getTableByDataType)->whereIn($yearColumnName,$getYearRange)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region)->groupBy($yearColumnName,$headerColumn)->selectRaw(''.$monthColumName.','.$yearColumnName.','.$headerColumn.',sum('.$quantityColumnName.') as '.$quantityColumnName.'')->orderBy($quantityColumnName,'DESC')->get();
				//dd(DB::getQueryLog()); // Show results of log
                  $variable = json_decode(json_encode($data), true);
                  $responceArray = array();
                  $categoryArray = array();
                  foreach ($variable as $key => $value) {
                     /*$categoryArray[$value[$headerColumn]]['data'][$value[$yearColumnName]] =number_format($value[$quantityColumnName],2);*/
                     if(!isset($categoryArray[$value[$headerColumn]]['data'][$value[$yearColumnName]])){
                      $categoryArray[$value[$headerColumn]]['data'][$value[$yearColumnName]] = $value[$quantityColumnName];
                    }else{
                        $categoryArray[$value[$headerColumn]]['data'][$value[$yearColumnName]] += $value[$quantityColumnName];
                      }
                  }
                 /* echo "<pre>";
                  print_r($categoryArray);
                  die();*/
                  $atcdds = array();
                  foreach ($categoryArray as $key => $value) {
                    foreach ($getYearRange as $ky => $val) {
                      if(array_key_exists($val, $value['data'])){
                        $atcdds[$key]['data'][] = array(
                                'key' => $val,
                                'value' => number_format($value['data'][$val],2) 
                            );
                      }else{
                        $atcdds[$key]['data'][] = array(
                                'key' => $val,
                                'value' => 0 
                            );
                      }
                    }
                  }
                  /*$atcdds = array();
                  foreach ($categoryArray as $key => $value) {
                    foreach ($value as $ky => $val) {
                      foreach ($val as $k => $vae) {
                        foreach ($getYearRange as $keysds => $sdsd) {
                        if(array_key_exists($sdsd, $val)){
                          $atcdds[$key][$ky][] = array(
                            'key' => $k,
                            'value' => number_format($vae,2) 
                          );
                         }else{
                            $atcdds[$key][$ky][] = array(
                            'key' => $sdsd,
                            'value' => 0 
                          );
                         }
                        }
                        
                      }
                    }
                  }*/
                  $responceArray['headerColumn'] = $headerColumn;
                  $responceArray['value'] = $atcdds;
                  return $responceArray;
                  /*foreach ($variable as $key => $value) {
                    $categoryArray[$value[$yearColumnName]][][$value[$headerColumn]] = number_format($value[$quantityColumnName],2);
                  }
                  return $categoryArray;*/
                
            }
            if(strtolower($periodFormat)=='quarterly'){
               $explodeFrom = explode(' ', $attr['fromDate']);
               $explodeTo = explode(' ', $attr['toDate']);
               $fromMonth = $this->getQuater(strtoupper($explodeFrom[0]));
               $fromYear = $explodeFrom[1];
               $toMonth = $this->getQuaterAfter(strtoupper($explodeTo[0]));
               $toYear = $explodeTo[1];  
                
               $fromDate = $fromYear.'-'.$fromMonth.'-01';
               $toDate = $toYear.'-'.$toMonth.'-01';
               $monthList = $this->getMonthBetweenTwoDates($fromDate,$toDate);
                $data = DB::table($getTableByDataType)->whereIn(DB::raw("CONCAT(`".$monthColumName."`, ' ', `".$yearColumnName."`)"),$monthList)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region)->groupBy($yearColumnName,$monthColumName,$headerColumn)->selectRaw(''.$monthColumName.','.$yearColumnName.','.$headerColumn.',sum('.$quantityColumnName.') as '.$quantityColumnName.'')->orderBy($quantityColumnName,'DESC')->get();
                   $variable = json_decode(json_encode($data), true);
                  $responceArray = array();
                  $categoryArray = array();
                 
                  foreach ($variable as $key => $value) {
                     $qter = ceil(date("n", strtotime($value[$monthColumName]))/3);
                    if(!isset($categoryArray[$value[$headerColumn]]['data']['Q'.$qter.' '.$value[$yearColumnName]])){
                      $categoryArray[$value[$headerColumn]]['data']['Q'.$qter.' '.$value[$yearColumnName]] = $value[$quantityColumnName];
                    }else{
                        $categoryArray[$value[$headerColumn]]['data']['Q'.$qter.' '.$value[$yearColumnName]] += $value[$quantityColumnName];
                      }
                    }
 
 
                  $quarterArray = array(); 
                  foreach ($monthList as $kesdsdy => $sdsddsds) {
                     $explode = explode(' ', $sdsddsds);
                     $qter = ceil(date("n", strtotime($explode[0]))/3);
                     $vaknhm = 'Q'.$qter.' '.$explode[1];
                     if(!in_array($vaknhm,$quarterArray)){
                      $quarterArray[] = $vaknhm;
                     }
                  }  
                  
                 /* echo "<pre>";
                  print_r($quarterArray);
                  die();*/
                  
                  $atcdds = array();
                  foreach ($categoryArray as $key => $value) {
                    foreach ($quarterArray as $ky => $val) {
                      if(array_key_exists($val, $value['data'])){
                        $atcdds[$key]['data'][] = array(
                                'key' => $val,
                                'value' => number_format($value['data'][$val],2) 
                            );
                      }else{
                        $atcdds[$key]['data'][] = array(
                                'key' => $val,
                                'value' => 0 
                            );
                      }
                    }
                  }
                  $responceArray['headerColumn'] = $headerColumn;
                  $responceArray['value'] = $atcdds;
                  return $responceArray;
                }
              }else{
                return 'false';
              }
        }


    public function getQuater($quater){
        $fromMonth = ''; 
        if($quater=='Q1'){
            $fromMonth = 'January';
         }if($quater=='Q2'){
           $fromMonth = 'April';
         }if($quater=='Q3'){
           $fromMonth = 'July';
         }if($quater=='Q4'){
           $fromMonth = 'October';
         }
         return $fromMonth;
    }

    public function getQuaterAfter($quater){
        $fromMonth = ''; 
        if($quater=='Q1'){
            $fromMonth = 'March';
         }if($quater=='Q2'){
           $fromMonth = 'June';
         }if($quater=='Q3'){
           $fromMonth = 'September';
         }if($quater=='Q4'){
           $fromMonth = 'December';
         }
         return $fromMonth;
    }

    public function getMonthBetweenTwoDates($date1,$date2){
       $time1  = strtotime($date1);
       $time2  = strtotime($date2);
       $my     = date('mY', $time2);
       $months = array(date('F Y', $time1));
       $f      = '';
       while($time1 < $time2) {
          $time1 = strtotime((date('Y-m-d', $time1).' +15days'));
          if(date('F', $time1) != $f) {
             $f = date('F', $time1);
             if(date('mY', $time1) != $my && ($time1 < $time2))
                $months[] = date('F Y', $time1);
          }
       }

       $months[] = date('F Y', $time2);
       array_shift($months);
       return $months;
    }

    public function addtoPortfolio($request){
       if($request['graphType']=='parent' || $request['graphType']=='child'){
		   if(empty($request['childGraphType']))
			   $cgt = '';
		   else
			   $cgt = $request['childGraphType'];
        $id = DB::table('portfolio')->insertGetId([
           'memid' => $request['memid'],
           'dataType' => trim($request['dataType']),
           'commodity' => $request['commodity'],
           'region' => $request['region'],
           'fromDate' => $request['fromDate'],
           'toDate' => $request['toDate'],
           'periodFormat' => $request['periodFormat'],
           'columnNameOfRegion' => $request['columnNameOfRegion'],
           'columnNameOfCommodity' => $request['columnNameOfCommodity'],
           'type' => $request['graphType'],
           'childGraphType' => $cgt,
           'excel_card' => (isset($request['excel_card']) && $request['excel_card'] != null)?$request['excel_card']:"n"
        ]);
        return $id;
       }else{
        return 'false';
       }
    }

    public function removeFromPortfolio($request){
      if(DB::table('portfolio')->where('id',$request['portFolioId'])->where('memid',$request['memid'])->count() > 0){
         DB::table('portfolio')->where('id',$request['portFolioId'])->delete();
         return $request['portFolioId'];
      }else{
        return 'false';
      }
    }
    public function getRecentView($request){
      $type = trim($request['type']);
      $memid = trim($request['memid']);
       if(DB::table('recent_activities')->where('type',$type)->where('memid',$memid)->count() > 0){
		   $data = DB::table('recent_activities')
					->where('type',$type)
					->where('memid',$memid);
					
			if( isset($request['filters']['app_type']) && $request['filters']['app_type'] == 'CM' )					
				$data = $data ->where('commodity', "coal")		;
		
			$data = $data 	->orderBy('id','DESC')
						->limit(5)
						->get();
		   return $data = json_decode(json_encode($data), true);
      }else{
        return 'false';
      }

    }

   

	public function checkPortfolio($request,$type){
		/* if($request['debugMode']=='true')
			DB::enableQueryLog(); */
		
		if($request['source']=='home'){
			$request['periodFormat'] = 'monthly';
			$request['fromDate'] = 'jul 2019';
			$request['toDate'] = "dec 2019";
		}			
		
		$check_portfolio = DB::table('portfolio')
			->where('memid',$request['memid'])
			->where('dataType',$request['dataType'])
			->where('commodity',$request['commodity'])
			->where('region',trim($request['region']))
			->where('fromDate',$request['fromDate'])
			->where('toDate',$request['toDate'])
			->where('periodFormat',$request['periodFormat'])
			->where('columnNameOfRegion',$request['columnNameOfRegion'])
			->where('columnNameOfCommodity',$request['columnNameOfCommodity'])
			->where('type',$type);
		
		if($type == "child"){
			if( $request['headerColumn'] == 'subCommodity' ){
				$check_portfolio = $check_portfolio	->where('childGraphType',$request['headerColumn']);
				$check_portfolio = $check_portfolio	->orWhere('childGraphType','subSubCommodity');
			} else
				$check_portfolio = $check_portfolio	->where('childGraphType',$request['headerColumn']);
		}	
		
		$check_portfolio = $check_portfolio ->orderBy("id",'DESC');
		$check_portfolio = $check_portfolio->first();
		
		//if($request['debugMode']=='true')
			//dd(DB::getQueryLog()); 
    
	 if($request['debugMode']=='true'){
		 echo "<pre>check_portfolio:";
		 print_r($check_portfolio);
		//echo "check_portfolio:" . $check_portfolio['id'];
	 }	
 /* echo "<pre>";	 
print_r($check_portfolio);  */
		if($type == "parent"){
			$post = DB::table('recent_activities')->insert([
				'memid' => $request['memid'],
				'dataType' => $request['dataType'],
				'commodity' => $request['commodity'],
				'region' => $request['region'],
				'fromDate' => $request['fromDate'],
				'toDate' => $request['toDate'],
				'periodFormat' => $request['periodFormat'],
				'columnNameOfRegion' => $request['columnNameOfRegion'],
				'columnNameOfCommodity' => $request['columnNameOfCommodity'],
				'f_datetime' => date("Y-m-d H:i:s")
			]);
		}

		if(count($check_portfolio) > 0){
			return $check_portfolio['id'];
		} else {
			return "false";
		}
	}

   
   public function getUserStatsPortfolioV2019($user_id, $appType='SM', $renderForDataType, $offset, $isNewDashboardGraph)
	{
		$res = array(); 
		//DB::enableQueryLog();
		$temp = DB::table('portfolio')
				->select('*')
				->where('memid', '=' , $user_id);
		
		//if called from new dashboard then send data for specific datatype
		//$renderForDataType contains datatype for whse data is required on frontend
		if( $renderForDataType != '' )	{	
		    $temp = $temp   ->  where('dataType', '=' , $renderForDataType);
		    
		    //get total pages for this query
		    $temp1 = $temp;
		    $temp1 = $temp1  ->  count();
		    $totalrecs = ceil($temp1/10);
	
	        //on the new dashboard page, if user clicks on the "graph" button under statistics section then we will throw list of all stats added in his portfolio without any limit	    
		    //limit will be applied only for the list view of stats data
		    //for "graph" button, list will still be sent datatype wise
		    if( $isNewDashboardGraph == 'n' ){
                $temp = $temp   ->  offset($offset)
                                ->  limit(10);
		    }
		    
		}else{
			$temp = $temp   ->  offset($offset)
                                ->  limit(8);
		}			
		
        $temp = $temp -> orderBy("id",'DESC')
				->get();
		///dd(DB::getQueryLog()); 
        //$cntRecs = 0;
		$siteFilter='steelmint';
         foreach($temp as $k=>$v){
			 ///if($user_id==56350){
			 if($v['columnNameOfCommodity'] == 'commodity_new')
				 $columnNameOfCommodity='commodity';
			 else
				 $columnNameOfCommodity=$v['columnNameOfCommodity'];
			 
			 $dataM = DB::table('tbl_historical_landing_bm')
				  ->where('section','statistics')
				  ->where('tradeType',$v['dataType'])
				  //->where('website_access', 'like', '%' . $siteFilter . '%')
				   ->where('dataAvailableFrom','>','1990-01-01')
				   ->where($columnNameOfCommodity,'=',$v['commodity'])
				   ->where('country','=',$v['region'])
				  // ->whereNOTIn('subCommodity', array('Non Coking Coal', 'Pet Coke', 'Anthracite', 'Others', 'Bituminous Coal', 'Lignite'))
				   ->whereNotIn('commodity',['','-'])
				  ->whereNotIn('subCommodity',['','-'])
				  ->whereNotIn('subSubCommodity',['-'])
				  ->orderBy('commodity_orderby_1st_level')
				  ->orderBy('commodity_orderby_2nd_level')
				  ->orderBy('region_orderby_1st_level')
				  ->orderBy('region_orderby_2nd_level')
				  ->orderBy('region_orderby_3rd_level')
				  ->first();
				
				/*  if( $siteFilter == "coalmint" )
					$data = $data->where('commodity',"coal");
				else 
					$data = $data->whereNOTIn('subCommodity', array('Non Coking Coal', 'Pet Coke', 'Anthracite', 'Others', 'Bituminous Coal', 'Lignite'));
					
				$data =	  $data->where('website_access', 'like', '%' . $siteFilter . '%')
				   ->where('dataAvailableFrom','>','1990-01-01')
				   ->where('commodity','=','1990-01-01')
				   ->where('subCommodity','=','1990-01-01')
				   ->whereNotIn('commodity',['','-'])
				  ->whereNotIn('subCommodity',['','-'])
				  ->whereNotIn('subSubCommodity',['-']);*/
				  
				  
					$v['dataAvailableTo'] = date('M Y', strtotime($dataM['dataAvailableTo']));
					$v['dataAvailableFrom'] = date('M Y', strtotime($dataM['dataAvailableFrom']));
					$v['lastUpdated'] = date('M Y', strtotime($dataM['lastUpdated']));
			 ///}		
             $res[] = $v;
             //$cntRecs += 1;
         }
	    if( $renderForDataType != '' ){
            $finres = array();
            $finres["dataValue"] = $res;
            $finres["cntValue"] = $totalrecs;
            
            return $finres;
	    }
    	 return $res;
    }


/* Historical data Api */

   public function historicalGetData($attr, $memid, $appType='SM'){
    $type = strtolower($attr);
	/// echo $appType.'appType';die;
	/*
    if($appType=='SM'){
		/*$dataCom = DB::table('tbl_historical_landing_bm')
					->where('section',$type)
					->whereNOTIn('subCommodity', array('Non Coking', 'Non Coking Coal', 'Pet Coke', 'Anthracite', 'Others', 'Bituminous Coal', 'Lignite'))
					->get();
		$dataCom = DB::table('tbl_historical_landing_bm')
					->where('section',$type)
					->whereNOTIn('subCommodity', array('Non Coking Coal', 'Pet Coke', 'Anthracite', 'Others', 'Bituminous Coal', 'Lignite'))
					->where('website_access','LIKE', "%steelmint%")
					->get();
	} else {
		$dataCom = DB::table('tbl_historical_landing_bm')
					->where('section',$type)
					->where('commodity', "coal")
					->get();
	 }	*/
	 $dataCom = DB::table('tbl_historical_landing_bm')
					->where('section',$type)					
					->get();
      $dataCom = json_decode(json_encode($dataCom), true);

//for commodity
	  array_multisort(array_column($dataCom, 'commodity_orderby_1st_level'), SORT_ASC,
			//array_column($dataCom, 'commodity_orderby_2nd_level'),      SORT_ASC,
			array_column($dataCom, 'country'),      SORT_ASC,
			$dataCom);
      if(count($dataCom) > 0){
        $responceArray = array();
        $responceArrays = array();
        $sortCommodity = array();
        foreach ($dataCom as $key => $value) {
			if(strtotime($value['dataAvailableFrom']) >= strtotime('1990-01-01')){
				if(!empty($value['commodity'])){
				  $responceArray[$value['commodity']][] = array(
					'id' => $value['id'],
					'subCommodity' => $value['subCommodity'],
					'subSubCommodity' => $value['subSubCommodity'],
					'country' => $value['country'],
					'tradeType' => $value['tradeType'],
					'dataAvailableFrom' => $value['dataAvailableFrom']
				   ); 
				}
				/* if(!empty($value['country'])){
				  $responceArrays[$value['country']][] = array(
					'id' => $value['id'],
					'commodity' => $value['commodity'],
					'subCommodity' => $value['subCommodity'],
					'subSubCommodity' => $value['subSubCommodity'],
					'tradeType' => $value['tradeType'],
					'dataAvailableFrom' => $value['dataAvailableFrom']
				   );
				} */
			}
        }
	 }	
//for commodity
	  array_multisort(array_column($dataCom, 'region_orderby_1st_level'), SORT_ASC,
			array_column($dataCom, 'region_orderby_2nd_level'),      SORT_ASC,
			array_column($dataCom, 'commodity_orderby_1st_level'),      SORT_ASC,
			array_column($dataCom, 'commodity_orderby_2nd_level'),      SORT_ASC,
			$dataCom);
      if(count($dataCom) > 0){
        //$responceArray = array();
        $responceArrays = array();
        
        foreach ($dataCom as $key => $value) {
			if(strtotime($value['dataAvailableFrom']) >= strtotime('1990-01-01')){
				/* if(!empty($value['commodity'])){
				  $responceArray[$value['commodity']][] = array(
					'id' => $value['id'],
					'subCommodity' => $value['subCommodity'],
					'subSubCommodity' => $value['subSubCommodity'],
					'country' => $value['country'],
					'tradeType' => $value['tradeType'],
					'dataAvailableFrom' => $value['dataAvailableFrom']
				   ); 
				} */
				if(!empty($value['country'])){
				  $responceArrays[$value['country']][] = array(
					'id' => $value['id'],
					'commodity' => $value['commodity'],
					'subCommodity' => $value['subCommodity'],
					'subSubCommodity' => $value['subSubCommodity'],
					'tradeType' => $value['tradeType'],
					'dataAvailableFrom' => $value['dataAvailableFrom']
				   );
				}
			}
        }
	  }
        $responce['commodityWise'] = $responceArray;
        $responce['regionWise'] = $responceArrays;
        
        if(!empty(Session::get('sectionFromDate'))){			
			$sectionFromDate = Session::get('sectionFromDate');
			$sectionTodate = Session::get('sectionTodate');	
			$sectionTodate = strtotime($sectionTodate) <= strtotime($GLOBALS['currDate']) ?$sectionTodate:$GLOBALS['currDate'];
			$responce['startDate'] = $sectionFromDate;
			$responce['endDate'] = $sectionTodate;
		
		}else{
		   	$responce['startDate'] = '1990-01-01';//$GLOBALS['currDate'];
			$responce['endDate'] = '1990-01-01';//$GLOBALS['currDate'];
		}
		
        return $responce;
      /* }else{
        return 'false';
      } */

   }

   public function checkListNameExist($attr){
     $listName = trim($attr['listName']);
     $memid = trim($attr['memid']);
     $check = DB::table('tb_historical_data_saved_list')->where('memid',$memid)->where('listName',$listName)->count();
     if($check < 1){
       return 'true';
     }else{
      return 'false';
     }
   }

	public function saveHistoricalDataList($attr){
		//print_r($attr);
		$listName = trim($attr['listName']);
		$memid = trim($attr['memid']);
		$type = trim($attr['type']);
		$requiredFor = trim($attr['requiredFor']);
		$fromDates = trim($attr['fromDate']); 
		$date = str_replace('/', '-', $fromDates);
		$fromDate = date("Y-m-d", strtotime($date));

		$toDated = trim($attr['toDate']);
		$date = str_replace('/', '-', $toDated);
		$toDate = date("Y-m-d", strtotime($date));

		//IDs checked by the user to download historical data
		$checkedlistid = rtrim($attr['checkedIdList'],',');
		$dt = date("Y-m-d H:i:s");
		$selected_market = trim($attr['selected_market']);	
		$results = DB::select( DB::raw(" 
				insert into tbl_historical_landing_selections_during_save (section,tradeType,commodity,subCommodity,subSubCommodity,country,dataAvailableFrom,rid,commodity_orderby_1st_level,commodity_orderby_2nd_level,region_orderby_1st_level,region_orderby_2nd_level,region_name,region_orderby_3rd_level,memid,f_date)
				SELECT section,tradeType,commodity,subCommodity,subSubCommodity,country,dataAvailableFrom,rid,commodity_orderby_1st_level,commodity_orderby_2nd_level,region_orderby_1st_level,region_orderby_2nd_level,region_name,region_orderby_3rd_level,$memid, '$dt'
				FROM tbl_historical_landing_bm 
				WHERE id in ($checkedlistid)
			"
		));			
		
		$insertedIDs = \DB::table("tbl_historical_landing_selections_during_save")
			   ->select("id")
			   ->where('memid', '=', $memid)
               ->where('f_date', '=', "$dt")
			   ->get();
			   
        //$insertedIDs = json_decode(json_encode($insertedIDs), true);
        
        $str = '';
        foreach ($insertedIDs as $key => $value) {
            $str .= $value['id'] . ',';
        }
      
		if(strlen($checkedlistid) > 0){
			DB::table('tb_historical_data_saved_list')->insert([
			'listName' => $listName,
			'type' => $type,
			'requiredFor' => $requiredFor,
			'parent_id' => rtrim($str, ','),
			'fromDate' => $fromDate,
			'toDate' => $toDate,
			'memid'=> $memid,
			'f_market'=> $selected_market
			]);
			return 'Record has been Added!';
		}else{
			return 'false';
		}
	}

   public function saveHistoricalDataList_5_mar_2021($attr){
       //print_r($attr);
       $listName = trim($attr['listName']);
       $memid = trim($attr['memid']);
       $type = trim($attr['type']);
       $requiredFor = trim($attr['requiredFor']);
       $fromDates = trim($attr['fromDate']); 
       $date = str_replace('/', '-', $fromDates);
       $fromDate = date("Y-m-d", strtotime($date));
    
       $toDated = trim($attr['toDate']);
       $date = str_replace('/', '-', $toDated);
       $toDate = date("Y-m-d", strtotime($date));

       $getIds = DB::table('tbl_historical_landing_bm')->lists('id');

       $checkedlistid = rtrim($attr['checkedIdList'],',');
       $explode = explode(',', $checkedlistid);
       if(count($explode) > 0){
        $str = '';
        $check = 'true';
         foreach ($explode as $key => $value) {
           if(in_array($value,$getIds)){
             $str.=$value.',';
           }else{
             $check = 'false';
           } 
         }
       if($check=='true'){ 
       if(strlen($checkedlistid) > 0){
         DB::table('tb_historical_data_saved_list')->insert([
             'listName' => $listName,
             'type' => $type,
             'requiredFor' => $requiredFor,
             'parent_id' => rtrim($str,','),
             'fromDate' => $fromDate,
             'toDate' => $toDate,
             'memid'=> $memid
           ]);
         return 'Record has been Added!';
       }else{
          return 'false';
       }
    }else{
      return 'false';
    }
   }else{
     return 'false';
   }
 }

   public function getSavedHistoricaldataList($attr, &$moreParam){
       $memid = trim($attr['memid']);
       $selected_market = trim($attr['selected_market']);
       if( $memid == 56350 ){
           ///DB::enableQueryLog();
       }
       if(empty($memid))
            return false;
       
        $user = new User($memid);
		$isAccessible =  $user->isAccessible($resourceType='Web', 'Prices_Historical');
        	
        $marketsIncludes='';
        if(!empty(Session::get('marketsIncludes'))){
            $marketsIncludes = Session::get('marketsIncludes');
        }
       
       //$type = trim($attr['type']);
       $getParentData = DB::table('tb_historical_data_saved_list')->where('memid',$memid)       
       ->whereRaw("FIND_IN_SET(f_market,'$marketsIncludes')");       
       if($selected_market!='')
            $getParentData = $getParentData->where('f_market',$selected_market);
      
       $getParentData = $getParentData->orderBy('id','DESC');
	   $cntValue = $getParentData->count();
	
	   if(isset($attr['filters']["renderAt"]) && $attr['filters']["renderAt"]=="new_dashboard" ){
			$pageValueOffset = 0;
			if(isset($attr['pageValue']))
				$pageValueOffset = ($attr['pageValue'] -1) * 6; 
			
			$getParentData = $getParentData   ->  offset($pageValueOffset)
							->  limit(6);
						
		}
		$getParentData = $getParentData ->get();
       if( $memid == 56350 ){
          // dd(DB::getQueryLog());
       }
       
       $getParentData = json_decode(json_encode($getParentData), true);
       if(count($getParentData) > 0){
        $responceArray = array();
        foreach ($getParentData as $key => $value) {
          $explode = explode(',', $value['parent_id']);
            $dataFromParent = DB::table('tbl_historical_landing_selections_during_save')->whereIn('id',$explode)->get();
          $dataFromParent = json_decode(json_encode($dataFromParent), true);
          $responceArray[] = array(
               'id' => $value['id'],
               'name' => $value['listName'],
               'type' => $value['type'],
               'fromDate' => $value['fromDate'],
               'toDate' => $value['toDate'],
			   'section' => $value['requiredFor'],	
			   'selected_market' => $value['f_market'],	
               'point' => $dataFromParent
            );
        }
		
		if(isset($attr['filters']["renderAt"]) && $attr['filters']["renderAt"]=="new_dashboard"  && count($responceArray)>0)
			$moreParam['cntValue']=ceil($cntValue/6);
      /*if( $memid == 116707 ){
          echo "<pre>";
           print_r($responceArray);
       }*/
 
        return $responceArray;
       }else{
        return 'false';
       }

   }
   public function downloadHistoricaldataList($attr){
      $requiredFor = ucfirst(trim($attr['requiredFor']));
      $type = trim($attr['type']);
      $explode = explode(',', trim($attr['checkedIdList']));
        
	   $fromDates = trim($attr['fromDate']); 
       $date = str_replace('/', '-', $fromDates);
       $fromDate = date("Y-m-d", strtotime($date));
   
       $toDated = trim($attr['toDate']);
       $date = str_replace('/', '-', $toDated);
       $toDate = date("Y-m-d", strtotime($date));
		
		$section = 'Prices_Historical';
		if(strtolower($requiredFor)=='price' || strtolower($requiredFor)=='prices')
			$section = 'Prices_Historical';
		elseif(strtolower($requiredFor)=='statistic' || strtolower($requiredFor)=='statistics')
			$section = 'Statistics_Historical';
			
		$memid = 0;
		//update memId if getting in POST request
		if(isset($attr['memid']) && $attr['memid']>0){
		   $memid = trim($attr['memid']);
		}
		
        $user = new User($memid, $appType='SM');
		$isAccessible =  $user->isAccessible($resourceType='Web', $section);
		if(!empty(Session::get('sectionFromDate'))){			
			$sectionFromDate = Session::get('sectionFromDate');
			$sectionTodate = Session::get('sectionTodate');	
			
			$fromDate = strtotime($fromDate) < strtotime($sectionFromDate) ?$sectionFromDate:$fromDate;
			$toDate = strtotime($toDate) > strtotime($sectionTodate) ?$sectionTodate:$toDate;
			
		}else{
			$fromDate = '1990-01-01';//$GLOBALS['currDate'];
			$toDate = '1990-01-01';//$GLOBALS['currDate'];
		}
		
       $dataFromParent = DB::table('tbl_historical_landing_bm')->where('section',$requiredFor)->whereIn('id',$explode)->get();
      
      $dataFromParent = json_decode(json_encode($dataFromParent), true);
      $responceArray = array();
      foreach ($dataFromParent as $key => $value) {
        /* $responceArray[] = array(
           'id' => $value['id'],
           'tradeType' => $value['tradeType'],
           'commodity' => $value['commodity'],
           'subCommodity' => $value['subCommodity'],
           'country' => $value['country'],
           'dataAvailableFrom' => $value['dataAvailableFrom'],
           'fromDate' => $fromDate,
           'toDate' => $toDate
          ); */
		$responceArray['filePath'] = BASEURL . "excel_download/_intraday_20181026_091049.zip";
      }
      return $responceArray;
   }

   public function removeItemFromList($attr){
      if(DB::table('tb_historical_data_saved_list')->where('id',$attr['itemId'])->where('memid',$attr['memid'])->count() > 0){
       DB::table('tb_historical_data_saved_list')->where('id',$attr['itemId'])->where('memid',$attr['memid'])->delete();
       return 'true';
     }else{
      return 'false';
     }
   }
   public function getItemFromList($attr){
      if(DB::table('tb_historical_data_saved_list')->where('id',$attr['itemId'])->count() > 0){
       $data = DB::table('tb_historical_data_saved_list')->where('id',$attr['itemId'])->where('memid',$attr['memid'])->first();
         $data = json_decode(json_encode($data), true);
         if(count($data) > 0){
            $explode = explode(',', $data['parent_id']);
            $dataFromParent = DB::table('tbl_historical_landing_bm')->whereIn('id',$explode)->get();
            $dataFromParent = json_decode(json_encode($dataFromParent), true);
            $responceArray = array();
            $dataArray['itemId'] = $data['id'];
            $dataArray['parent_id'] = $data['parent_id'];
            foreach ($dataFromParent as $key => $value) {
              $responceArray[] = array(
                 'id' => $value['id'],
                 'tradeType' => $value['tradeType'],
                 'commodity' => $value['commodity'],
                 'subCommodity' => $value['subCommodity'],
                 'country' => $value['country'],
                 'dataAvailableFrom' => $value['dataAvailableFrom'],
                 'fromDate' => $data['fromDate'],
                 'toDate' => $data['toDate'],
                 'f_market'=>$data['f_market']
                );
            }
            $dataArray['selectedItem'] = $responceArray;
            return $dataArray;
         }else{
          return 'false';
         }
      }else{
        return 'false';
      }
   }

	public function editSaveList($attr){
		$fromDates = trim($attr['fromDate']); 
		$date = str_replace('/', '-', $fromDates);
		$fromDate = date("Y-m-d", strtotime($date));

		$toDated = trim($attr['toDate']);
		$date = str_replace('/', '-', $toDated);
		$toDate = date("Y-m-d", strtotime($date));
		$checkedlistid = rtrim($attr['parent_id'],',');
		
		$dt = date("Y-m-d H:i:s");
			
		$results = DB::select( DB::raw(" 
				insert into tbl_historical_landing_selections_during_save (section,tradeType,commodity,subCommodity,subSubCommodity,country,dataAvailableFrom,rid,commodity_orderby_1st_level,commodity_orderby_2nd_level,region_orderby_1st_level,region_orderby_2nd_level,region_name,region_orderby_3rd_level,pid,f_date)
				SELECT section,tradeType,commodity,subCommodity,subSubCommodity,country,dataAvailableFrom,rid,commodity_orderby_1st_level,commodity_orderby_2nd_level,region_orderby_1st_level,region_orderby_2nd_level,region_name,region_orderby_3rd_level," . $attr['itemId'] .  ", '$dt'
				FROM tbl_historical_landing_bm 
				WHERE id in ($checkedlistid)
			"
		));			
		
		$insertedIDs = \DB::table("tbl_historical_landing_selections_during_save")
			   ->select("id")
			   ->where('pid', '=', $attr['itemId'])
			   ->get();
			   
        //$insertedIDs = json_decode(json_encode($insertedIDs), true);
        
        $str = '';
        foreach ($insertedIDs as $key => $value) {
            $str .= $value['id'] . ',';
        }

		$updated = DB::table('tb_historical_data_saved_list')
					->where('id',$attr['itemId'])
					->update([	'parent_id' => rtrim($str,','),
								'fromDate' => $fromDate,
								'toDate' => $toDate
							]);
		if($updated){
			return 'true';
		}else{ 
			return 'false';
		}
	}

   public function editSaveList_5_mar_2021($attr){
       $fromDates = trim($attr['fromDate']); 
       $date = str_replace('/', '-', $fromDates);
       $fromDate = date("Y-m-d", strtotime($date));

       $toDated = trim($attr['toDate']);
       $date = str_replace('/', '-', $toDated);
       $toDate = date("Y-m-d", strtotime($date));
       $parent = rtrim($attr['parent_id'],',');
       $explode = explode(',', $parent);
       $getIds = DB::table('tbl_historical_landing_bm')->lists('id');

       if(count($explode) > 0){
        $str = '';
        $check = 'true';
         foreach ($explode as $key => $value) {
           if(in_array($value,$getIds)){
             $str.=$value.',';
           }else{
             $check = 'false';
           } 
         }
         if($check=='true'){
           $updated = DB::table('tb_historical_data_saved_list')->where('id',$attr['itemId'])->update(['parent_id' => rtrim($str,','),'fromDate' => $fromDate,'toDate' => $toDate]);
           if($updated){
           return 'true';
           }else{ 
           return 'false';
           }
         }else{
          return 'false';
         }
       }else{
        return 'false';
       }
       

        
   }



   /*  Compare Controller Functions*/
  public function comGetCommodityRegion($attr){
       $requiredFor = trim($attr['requiredFor']);
       $dataType = trim($attr['dataType']);
       /* if(DB::table('tbl_stats_datatype_header_list_bm')->where('requiredFor',$requiredFor)->where('datatypeName',$dataType)->count() < 1){
         return 'false';
       } */
       //$stats =  DB::table('tbl_stats_datatype_header_list_bm')->where('datatypeName',$dataType)->take(1)->get();
	  
		if( isset($attr['filters']['app_type']) && $attr['filters']['app_type'] == 'CM' ){
			//DB::enableQueryLog();
		   $stats =  DB::table('tbl_stats_datatype_header_list_bm')
					->where('datatypeName',$dataType."_not_reqd")
					->where('requiredFor',$requiredFor)
					->take(1)
					->get();		
					//dd(DB::getQueryLog());
		}else{
		   $stats =  DB::table('tbl_stats_datatype_header_list_bm')
					->where('datatypeName',$dataType)
					->where('requiredFor',$requiredFor)
					->take(1)
					->get();		
		}	   
       $stats = json_decode(json_encode($stats), true);
       $data = DB::table('tbl_stats_selection_compare_bm')
              ->where('datatypeName',$dataType)
              ->where('commodity','!=','-')
              ->where('subCommodity','!=','-');
              ///->where('subSubCommodity','!=','-');
       
	   if( isset($attr['filters']['app_type']) && $attr['filters']['app_type'] == 'CM' )
			$data = $data->	where('commodity',"coal");
		
	   $data = $data  ->get();
	   
           $data = json_decode(json_encode($data), true);
	
		if(count($data) > 0){
			$communityArray = array();
			$tempArray = array();
			$tempSubArray = array();
			$regionArray = array();
			$tempsubsubArray = array();
			$col_name_region = '';
			foreach ($data as $key => $value) {
				if($col_name_region==''){
					$col_name_region = $value['col_name_region'];
				}
				$temps = $value['commodity'].'-'.$value['subCommodity'].'-'.$value['subSubCommodity'];
				if(!in_array($temps,$tempsubsubArray)){
					if($value['subSubCommodity']!=''){
						$communityArray[$value['commodity']][$value['subCommodity']][] = $value['subSubCommodity'];
					}else{
						$communityArray[$value['commodity']][] = $value['subCommodity'];
					}
					$tempsubsubArray[] = $temps;
				}
				/* $communityArray[$value['commodity']][$value['subCommodity']] = $value['subSubCommodity']; */
				$cont = $value['commodity'].'-'.$value['region'];
				$conts = $value['subCommodity'].'-'.$value['region'];
				$contss = $value['subSubCommodity'].'-'.$value['region'];
				if(!in_array($cont, $tempArray)){
					$regionArray[$value['commodity']][] = $value['region'];
					$tempArray[] = trim($cont);
				}
				if(!in_array($conts,$tempSubArray)){
					if($value['commodity']!=$value['subCommodity']){
						$regionArray[$value['subCommodity']][] = $value['region'];
						$tempSubArray[] = $conts;
					}
					/* if(!in_array($contss, $tempArray)){
						$regionArray[$value['subSubCommodity']][] = $value['region'];
						$tempSubSubArray[] = trim($contss);
					} */
				}
				if(!in_array(trim($contss), $tempSubSubArray)){
					$regionArray[$value['subSubCommodity']][] = $value['region'];
					$tempSubSubArray[] = trim($contss);
				}
			}
		}
		
		$regionColname = $col_name_region;
		$periodFormat = $stats[0]['periodFormat'];
		$dataArray = array(
			 'columnNameOfRegion' => $regionColname,
			 'columnNameOfCommodity' => $stats[0]['col_name_commodity_compare_toolbox'],
			 'commodity' => $communityArray,
			 'region' => $regionArray
		  );
		return $dataArray;
  }
    public function formatCompareHeaderData($attr){
      $array = array();
       foreach ($attr as $key => $variable) {
         foreach ($variable as $ke => $val) {
          $array[] = $val;
         }
       }
       return $array;
    }

   public function getHeaderList($attr){
    $datatype = trim($attr['dataType']);
    $columnNameOfCommodity = trim($attr['columnNameOfCommodity']);
    $columnNameOfRegion = trim($attr['columnNameOfRegion']);
    $commodity = trim($attr['commodity']);
    $region = trim(ucfirst($attr['region']));
     $getTableByDataType =  DB::table('tbl_stats_datatype_header_list_bm')->where('datatypeName',$datatype)->take(1)->get();
     $getTableByDataType = json_decode(json_encode($getTableByDataType), true);

     if(count($getTableByDataType) > 0){
      $getHeaderscolumns = DB::table('tbl_stats_datatype_header_dtls')->where('datatypeID',$getTableByDataType[0]['id'])->get();
       $getHeaderscolumns = json_decode(json_encode($getHeaderscolumns), true);
       if(count($getHeaderscolumns) > 0){
          $responceArray = array();
          foreach ($getHeaderscolumns as $key => $value) { 
            $responceArray['headers'][$value['headerName_display']]['headerColumn'] = $value['tbl_column_name'];

            $getvalues = DB::table($getTableByDataType[0]['tableName'])->select($value['tbl_column_name'])
            ->where($columnNameOfCommodity,$commodity)
            ->where($columnNameOfRegion,$region)
            ->distinct()->get();
            $heaedtr = $this->formatCompareHeaderData($getvalues);

            $responceArray['headers'][$value['headerName_display']]['values'] = $heaedtr;  
          }
          return $responceArray;
       }else{
        return 'false';
       }

     }else{
      return 'false';
     }

   }

  /****************************/
  public function getDataForCompare($attr){
    $datatype = trim($attr['dataType']); 
     $getTableByDataType =  DB::table('tbl_stats_datatype_header_list_bm')->where('datatypeName',$datatype)->value('tableName');
           if($getTableByDataType){
               $periodFormat = trim($attr['periodFormat']);
               $commodityColumn = trim($attr['columnNameOfCommodity']);
               $commodity = trim($attr['commodity']);

               $columnNameOfRegion = trim($attr['columnNameOfRegion']);
               $region = trim(ucfirst($attr['region']));
              
               $monthColumName = '';
               $yearColumnName = '';
               $quantityColumnName = ''; 
               if($getTableByDataType=='tbl_capacity'){
                 $yearColumnName='capacity_year';
                 $quantityColumnName='capacity';
               }else if($getTableByDataType=='tbl_production' ||$getTableByDataType=='tbl_dispatch' || $getTableByDataType=='tbl_consumption'){
                $monthColumName= 'month';
                $yearColumnName='year';
                $quantityColumnName = 'quantity';
               }else{
                 $monthColumName= 'month_of_loading';
                 $yearColumnName='year_of_loading';
                 $quantityColumnName = 'quantity';
               }



              if(strtolower($periodFormat)=='monthly'){
                 $explodeFrom = explode(' ', $attr['fromDate']);
                 $fromMonth = date('m',strtotime($explodeFrom[0]));
                 $fromYear = $explodeFrom[1];
        
                 $explodeTo = explode(' ', $attr['toDate']);
                 $toMonth = date('m',strtotime($explodeTo[0]));
                 $toYear = $explodeTo[1];

                 $fromDate = $fromYear.'-'.$fromMonth.'-01';
                 $toDate = $toYear.'-'.$toMonth.'-01';
                 $monthList = $this->getMonthBetweenTwoDates($fromDate,$toDate);
                /* $data = DB::table($getTableByDataType)
                     ->whereIn(DB::raw("CONCAT(`".$yearColumnName."`, ' ', `".$monthColumName."`)"),$monthList)
                     ->where($commodityColumn,$commodity)
                     ->where($headerColumn,$headerValue)
                      ->where($columnNameOfRegion,$region)
                      ->groupBy($yearColumnName,$monthColumName,$headerColumn)
                      ->selectRaw(''.$monthColumName.','.$yearColumnName.','.$headerColumn.',sum('.$quantityColumnName.') as '.$quantityColumnName.'')
                      ->orderBy($quantityColumnName,'DESC')
                      ->get();*/
                $data = DB::table($getTableByDataType)->whereIn(DB::raw("CONCAT(`".$monthColumName."`, ' ', `".$yearColumnName."`)"),$monthList)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region);
                    if(isset($attr['headerColumn']) && isset($attr['headerValue'])){

                      $headerColumn = trim($attr['headerColumn']);
                      $headerValue = trim($attr['headerValue']);
                      $data->where($headerColumn,$headerValue);
                    }
					 $data = $data->orderBy('formattedDate','asc'); 
                    $data = $data->get(); 
                  $variable = json_decode(json_encode($data), true);
                  if(count($variable) > 0){
                      $arragedArray = array();
                      $arragedArrayTemp = array();
                      foreach ($variable as $key => $value) {
                        $arragedArray[$value[$yearColumnName]][$value[$monthColumName]][]=$value[$quantityColumnName];  
						
						$arragedArrayTemp[$value[$monthColumName].' '.$value[$yearColumnName]]+= $value[$quantityColumnName];
                      }
                        
						 
                    /*  foreach ($arragedArray as $keys => $monthsArray) {
                       foreach ($monthsArray as $key => $val) {
                         $responceArray[] = array(
                             'x' => $key.' '.$keys,
                             'y' => number_format(array_sum($val)/1000000,2)
                          );
                       }
                     } */
					
					$offset = array_search(reset(array_keys($arragedArrayTemp)), $monthList);				  
				    $getYearRange1 = array_slice($monthList, ($offset));
				   
				    $offsetend = array_search(end(array_keys($arragedArrayTemp)), $getYearRange1);
				    $getYearRangeNew = array_slice($getYearRange1, 0, ($offsetend)+1);
					
					foreach ($getYearRangeNew as $keyIndex => $keys) {
						
                        $responceArray[] = array(
                           'x' => $keys,
                           'y' => isset($arragedArrayTemp[$keys])?round(($arragedArrayTemp[$keys])/1000000,2):'-'
                        );
                    } 
                     return $responceArray;
                   }else{
                      return 'false';
                   }
                 }
              //return $responceArray;

              if(strtolower($periodFormat)=='yearly'){

               $fromYear =trim($attr['fromDate']);
               $toYear = trim($attr['toDate']);
               $getYearRange = range($fromYear,$toYear);
               /* $data = DB::table($getTableByDataType)
                      ->whereIn($yearColumnName,$getYearRange)
                      ->where($commodityColumn,$commodity)
                      ->where($headerColumn,$headerValue)
                      ->where($columnNameOfRegion,$region)
                      ->groupBy($yearColumnName,$headerColumn)
                      ->selectRaw(''.$monthColumName.','.$yearColumnName.','.$headerColumn.',sum('.$quantityColumnName.') as '.$quantityColumnName.'')
                      ->orderBy($quantityColumnName,'DESC')
                      ->get();*/
                    $data = DB::table($getTableByDataType)->whereIn($yearColumnName,$getYearRange)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region);
                    if(isset($attr['headerColumn']) && isset($attr['headerValue'])){

                      $headerColumn = trim($attr['headerColumn']);
                      $headerValue = trim($attr['headerValue']);
                      $data->where($headerColumn,$headerValue);
                    }
                    $data = $data->orderBy('formattedDate','asc'); 
                    $data = $data->get(); 

                  

                  $variable = json_decode(json_encode($data), true);
                   if(count($variable) > 0){
                    $arragedArray = array();
                    foreach ($variable as $key => $value) {
                      $arragedArray[$value[$yearColumnName]][] =$value[$quantityColumnName];  
                    }
					
                   /*  foreach ($arragedArray as $keys => $val) {
						
                        $responceArray[] = array(
                           'x' => $keys,
                           'y' => number_format(array_sum($val)/1000000,2)
                        );
                   } */
				    $offset = array_search(reset(array_keys($arragedArray)), $getYearRange);				  
				    $getYearRange1 = array_slice($getYearRange, ($offset));
				   
				    $offsetend = array_search(end(array_keys($arragedArray)), $getYearRange1);
				    $getYearRangeNew = array_slice($getYearRange1, 0, ($offsetend)+1);
					
				   foreach ($getYearRangeNew as $keyIndex => $keys) {
						
                        $responceArray[] = array(
                           'x' => $keys,
                           'y' => isset($arragedArray[$keys])?round(array_sum($arragedArray[$keys])/1000000,2):'-'
                        );
                   } 
                   return $responceArray;
                  }else{
                      return 'false';
                  }
                
            }

            if(strtolower($periodFormat)=='quarterly'){
                  $explodeFrom = explode(' ', $attr['fromDate']);
                  $explodeTo = explode(' ', $attr['toDate']);

                  $fromMonth = $this->getQuater(strtoupper($explodeFrom[0]));
                  $fromYear = $explodeFrom[1];


                  $toMonth = $this->getQuaterAfter(strtoupper($explodeTo[0]));
                  $toYear = $explodeTo[1];  


                  $fromDate = $fromYear.'-'.$fromMonth.'-01';
                  $toDate = $toYear.'-'.$toMonth.'-01';
                  $monthList = $this->getMonthBetweenTwoDates($fromDate,$toDate);

                  $data = DB::table($getTableByDataType)->whereIn(DB::raw("CONCAT(`".$monthColumName."`, ' ', `".$yearColumnName."`)"),$monthList)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region);
                  if(isset($attr['headerColumn']) && isset($attr['headerValue'])){

                      $headerColumn = trim($attr['headerColumn']);
                      $headerValue = trim($attr['headerValue']);
                      $data->where($headerColumn,$headerValue);
                    }
					 $data = $data->orderBy('formattedDate','asc'); 
                   $data = $data->get(); 
                     $variable = json_decode(json_encode($data), true);
                     $arragedArray = array();
                     $arragedArrayTemp = array();
                   foreach ($variable as $key => $value) {
                       $qter = ceil(date("n", strtotime($value[$monthColumName]))/3);
                       $arragedArray[$value[$yearColumnName]]['Q'.$qter][] = $value[$quantityColumnName];
					   
					   $arragedArrayTemp['Q'.$qter.' '.$value[$yearColumnName]]+= $value[$quantityColumnName];
                   }

                /*  foreach ($arragedArray as $keys => $yearArray) {
                   foreach ($yearArray as $key => $val) {
                     $responceArray[] = array(
                         'x' => $key.' '.$keys,
                         'y' => number_format(array_sum($val)/1000000,2)
                      );
                   }
                }  */
					$monthListQtr= [];
					foreach ($monthList as $key => $value) {
					   $qtrArr = explode(' ', $value);
                       $qter = ceil(date("n", strtotime($qtrArr[0]))/3);
                       
					   if(!in_array('Q'.$qter.' '.$qtrArr[1], $monthListQtr))
						   $monthListQtr[] = 'Q'.$qter.' '.$qtrArr[1];
					}
				   
				  /*  */
					$offset = array_search(reset(array_keys($arragedArrayTemp)), $monthListQtr);				  
				    $getYearRange1 = array_slice($monthListQtr, ($offset));
				   
				    $offsetend = array_search(end(array_keys($arragedArrayTemp)), $getYearRange1);
				    $getYearRangeNew = array_slice($getYearRange1, 0, ($offsetend)+1);
					 
					foreach ($getYearRangeNew as $keyIndex => $keys) {
						
                        $responceArray[] = array(
                           'x' => $keys,
                           'y' => isset($arragedArrayTemp[$keys])?round(($arragedArrayTemp[$keys])/1000000,2):'-'
                        );
                    }
                return $responceArray;
            }
              }else{
                return 'false';
              }

	}

  public function getDataForCompareOLD05082021($attr){
    $datatype = trim($attr['dataType']); 
     $getTableByDataType =  DB::table('tbl_stats_datatype_header_list_bm')->where('datatypeName',$datatype)->value('tableName');
           if($getTableByDataType){
               $periodFormat = trim($attr['periodFormat']);
               $commodityColumn = trim($attr['columnNameOfCommodity']);
               $commodity = trim($attr['commodity']);

               $columnNameOfRegion = trim($attr['columnNameOfRegion']);
               $region = trim(ucfirst($attr['region']));
              
               $monthColumName = '';
               $yearColumnName = '';
               $quantityColumnName = ''; 
               if($getTableByDataType=='tbl_capacity'){
                 $yearColumnName='capacity_year';
                 $quantityColumnName='capacity';
               }else if($getTableByDataType=='tbl_production' || $getTableByDataType=='tbl_consumption'){
                $monthColumName= 'month';
                $yearColumnName='year';
                $quantityColumnName = 'quantity';
               }else{
                 $monthColumName= 'month_of_loading';
                 $yearColumnName='year_of_loading';
                 $quantityColumnName = 'quantity';
               }



              if(strtolower($periodFormat)=='monthly'){
                 $explodeFrom = explode(' ', $attr['fromDate']);
                 $fromMonth = date('m',strtotime($explodeFrom[0]));
                 $fromYear = $explodeFrom[1];
        
                 $explodeTo = explode(' ', $attr['toDate']);
                 $toMonth = date('m',strtotime($explodeTo[0]));
                 $toYear = $explodeTo[1];

                 $fromDate = $fromYear.'-'.$fromMonth.'-01';
                 $toDate = $toYear.'-'.$toMonth.'-01';
                 $monthList = $this->getMonthBetweenTwoDates($fromDate,$toDate);
                /* $data = DB::table($getTableByDataType)
                     ->whereIn(DB::raw("CONCAT(`".$yearColumnName."`, ' ', `".$monthColumName."`)"),$monthList)
                     ->where($commodityColumn,$commodity)
                     ->where($headerColumn,$headerValue)
                      ->where($columnNameOfRegion,$region)
                      ->groupBy($yearColumnName,$monthColumName,$headerColumn)
                      ->selectRaw(''.$monthColumName.','.$yearColumnName.','.$headerColumn.',sum('.$quantityColumnName.') as '.$quantityColumnName.'')
                      ->orderBy($quantityColumnName,'DESC')
                      ->get();*/
                $data = DB::table($getTableByDataType)->whereIn(DB::raw("CONCAT(`".$monthColumName."`, ' ', `".$yearColumnName."`)"),$monthList)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region);
                    if(isset($attr['headerColumn']) && isset($attr['headerValue'])){

                      $headerColumn = trim($attr['headerColumn']);
                      $headerValue = trim($attr['headerValue']);
                      $data->where($headerColumn,$headerValue);
                    }
					 $data = $data->orderBy('formattedDate','asc'); 
                    $data = $data->get(); 
                  $variable = json_decode(json_encode($data), true);
                  if(count($variable) > 0){
                      $arragedArray = array();
                      foreach ($variable as $key => $value) {
                        $arragedArray[$value[$yearColumnName]][$value[$monthColumName]][]=$value[$quantityColumnName];  
                      }
                      
                     foreach ($arragedArray as $keys => $monthsArray) {
                       foreach ($monthsArray as $key => $val) {
                         $responceArray[] = array(
                             'x' => $key.' '.$keys,
                             'y' => number_format(array_sum($val)/1000000,2)
                          );
                       }
                     }
                     return $responceArray;
                   }else{
                      return 'false';
                   }
                 }
              //return $responceArray;

              if(strtolower($periodFormat)=='yearly'){

               $fromYear =trim($attr['fromDate']);
               $toYear = trim($attr['toDate']);
               $getYearRange = range($fromYear,$toYear);
               /* $data = DB::table($getTableByDataType)
                      ->whereIn($yearColumnName,$getYearRange)
                      ->where($commodityColumn,$commodity)
                      ->where($headerColumn,$headerValue)
                      ->where($columnNameOfRegion,$region)
                      ->groupBy($yearColumnName,$headerColumn)
                      ->selectRaw(''.$monthColumName.','.$yearColumnName.','.$headerColumn.',sum('.$quantityColumnName.') as '.$quantityColumnName.'')
                      ->orderBy($quantityColumnName,'DESC')
                      ->get();*/
                    $data = DB::table($getTableByDataType)->whereIn($yearColumnName,$getYearRange)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region);
                    if(isset($attr['headerColumn']) && isset($attr['headerValue'])){

                      $headerColumn = trim($attr['headerColumn']);
                      $headerValue = trim($attr['headerValue']);
                      $data->where($headerColumn,$headerValue);
                    }
                    $data = $data->orderBy('formattedDate','asc'); 
                    $data = $data->get(); 

                  

                  $variable = json_decode(json_encode($data), true);
                   if(count($variable) > 0){
                    $arragedArray = array();
                    foreach ($variable as $key => $value) {
                      $arragedArray[$value[$yearColumnName]][] =$value[$quantityColumnName];  
                    }

                    foreach ($arragedArray as $keys => $val) {
                        $responceArray[] = array(
                           'x' => $keys,
                           'y' => number_format(array_sum($val)/1000000,2)
                        );
                   }
                   return $responceArray;
                  }else{
                      return 'false';
                  }
                
            }

            if(strtolower($periodFormat)=='quarterly'){
                  $explodeFrom = explode(' ', $attr['fromDate']);
                  $explodeTo = explode(' ', $attr['toDate']);

                  $fromMonth = $this->getQuater(strtoupper($explodeFrom[0]));
                  $fromYear = $explodeFrom[1];


                  $toMonth = $this->getQuaterAfter(strtoupper($explodeTo[0]));
                  $toYear = $explodeTo[1];  


                  $fromDate = $fromYear.'-'.$fromMonth.'-01';
                  $toDate = $toYear.'-'.$toMonth.'-01';
                  $monthList = $this->getMonthBetweenTwoDates($fromDate,$toDate);

                  $data = DB::table($getTableByDataType)->whereIn(DB::raw("CONCAT(`".$monthColumName."`, ' ', `".$yearColumnName."`)"),$monthList)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region);
                  if(isset($attr['headerColumn']) && isset($attr['headerValue'])){

                      $headerColumn = trim($attr['headerColumn']);
                      $headerValue = trim($attr['headerValue']);
                      $data->where($headerColumn,$headerValue);
                    }
					 $data = $data->orderBy('formattedDate','asc'); 
                   $data = $data->get(); 
                     $variable = json_decode(json_encode($data), true);
                     $arragedArray = array();
                   foreach ($variable as $key => $value) {
                       $qter = ceil(date("n", strtotime($value[$monthColumName]))/3);
                       $arragedArray[$value[$yearColumnName]]['Q'.$qter][] = $value[$quantityColumnName];
                   }

                 foreach ($arragedArray as $keys => $yearArray) {
                   foreach ($yearArray as $key => $val) {
                     $responceArray[] = array(
                         'x' => $key.' '.$keys,
                         'y' => number_format(array_sum($val)/1000000,2)
                      );
                   }
               } 
                return $responceArray;
            }
              }else{
                return 'false';
              }

}
  
  /***************************/
  
 
   

public function getTabularData($requiredFor,$datatype,$commodity,$commodityColumn,$region,$columnNameOfRegion,$memid,$appType,$mainCommodityName=''){
            /*===if user select all fild it show only one recourd ===*/
            $post = DB::table('recent_activities')->insert([
               'memid' => $memid,
               'type' => "tabular",
               'dataType' => $datatype,
               'commodity' => $commodity,
               'region' => $region,
               'columnNameOfRegion' => $columnNameOfRegion,
               'columnNameOfCommodity' => $commodityColumn ,
               'requiredFor' => $requiredFor,
			   'f_datetime' => date("Y-m-d H:i:s")
            ]);
 if($memid==56350){
	// echo $commodity .'--'.$region;
 }
          if($commodity != 'ALL' && $region != 'ALL'){
	        $tmpCommodityColumn = $commodityColumn;
	        if( $commodityColumn == "commodity_new" ){
		        $tmpCommodityColumn = 'commodity';
		        $mainCommodityName='';
            }
           
                
 
             $frequency = DB::table("tbl_stats_datatype_freq")->where($tmpCommodityColumn,$commodity);
              if($mainCommodityName!='')
                $frequency = $frequency->where('commodity',$mainCommodityName);
                
            $frequency = $frequency->pluck('frequency');
           //     $frequency = DB::table("tbl_stats_datatype_freq")->where($commodityColumn,$commodity)->where('region',$region)->pluck('frequency');
                
              if(!$frequency){
                $frequency = 'monthly';
              }

            $data = $this->getTabularDataCase1($requiredFor,$datatype,$commodity,$commodityColumn,$region,$columnNameOfRegion,$frequency,$memid,$appType,$mainCommodityName);
            } 
            /*if user select all fild expect region then show all recourd accourdingly different region*/ 
            else if($commodity != 'ALL' && $region == 'ALL') {
                
              $data = $this->getTabularDataCase2($requiredFor,$datatype,$commodity,$commodityColumn,$region,$columnNameOfRegion,$mainCommodityName);
            }
           else if($commodity == 'ALL' && $region != 'ALL') {
           
              $data = $this->getTabularDataCase3($requiredFor,$datatype,$commodity,$commodityColumn,$region,$columnNameOfRegion, $memid);
            }
            return $data;
    }

	
/*Case 1: when user select all requried field, like-comdity-sub comodity,region*/
    public function getTabularDataCase1($requiredFor,$datatype,$commodity,$commodityColumn,$region,$columnNameOfRegion,$frequency,$memid,$appType,$mainCommodityName=''){     

      $stats = DB::table('tbl_stats_datatype_header_list_bm')
              ->where('datatypeName',$datatype)
              ->where('requiredFor',$requiredFor)
              ->take(1)
              ->get();

      $stats = json_decode(json_encode($stats), true);
      $stat_table_name = $stats[0]['tableName'];

       /*It used to manage column name of year and column name of month*/
       $monthColumName = 'month_of_loading';
       $yearColumnName = 'year_of_loading';
       $quantityColumnName = 'quantity';
       $unitColumnName = 'quantity_unit';

       if($stat_table_name == 'tbl_capacity'){
         $yearColumnName='capacity_year';
         $quantityColumnName='capacity';
         $unitColumnName = 'qty_unit';
         $frequency = 'yearly';
       } else if($stat_table_name=='tbl_production' || $stat_table_name=='tbl_dispatch' || $stat_table_name=='tbl_consumption' || $stat_table_name=='tbl_indian_coal_stock'){
          $monthColumName= 'month';
          $yearColumnName='year';
          $quantityColumnName = 'quantity';
          $unitColumnName = 'unit';
       }

        $get_year = DB::table($stat_table_name)
                  ->where($commodityColumn,$commodity)
                  ->where($columnNameOfRegion,$region);
        if($mainCommodityName!='' && $commodityColumn!='commodity' )
          $get_year= $get_year->where('commodity_new',$mainCommodityName);  
        $get_year =$get_year->orderBy($yearColumnName,'desc')
                  ->take(2)
                  ->groupBy($yearColumnName)
                  ->lists($yearColumnName);
      
      $get_data = array();

if( $memid == 116707 ){
   // echo $frequency . ' -- ' . count($get_year);    
}

             
      if($get_year){
        if($frequency == 'yearly'){
              if(count($get_year) >= 2){
                $tbl_data = DB::table($stat_table_name)
                    //->where($commodityColumn,$commodity)
                    ->where($columnNameOfRegion,$region)
                    ->where($yearColumnName,$get_year[0])
                    ->select('id',$yearColumnName,DB::raw("SUM($quantityColumnName)/1000000 as latest_quantity"),$unitColumnName)
                   ;
                 if($mainCommodityName!='' && $commodityColumn!='commodity' )
                    $tbl_data= $tbl_data->where('commodity_new',$mainCommodityName); 
            if( 'CM' != $appType && strtolower($commodity) == 'coal'  )                            
                    $tbl_data = $tbl_data -> whereIn('subCommodity', ["Coking Coal", "Met Coke"]);        
            else
                    $tbl_data = $tbl_data ->where($commodityColumn,$commodity);
            $tbl_data = $tbl_data   ->take(1)
                                    ->get();

                $tbl_data = json_decode(json_encode($tbl_data), true);
                $tbl_data = $tbl_data[0];
                                  
                $get_old_quantity = DB::table($stat_table_name)
                   // ->where($commodityColumn,$commodity)
                    ->where($columnNameOfRegion,$region)
                    ->where($yearColumnName,$get_year[1])
                    ;

            if( 'CM' != $appType && strtolower($commodity) == 'coal'  )                            
                    $get_old_quantity = $get_old_quantity -> whereIn('subCommodity', ["Coking Coal", "Met Coke"]);        
            else
                    $get_old_quantity = $get_old_quantity ->where($commodityColumn,$commodity);
                $get_old_quantity = $get_old_quantity ->pluck(DB::raw("SUM($quantityColumnName)/1000000"));

                  if($tbl_data){
                    $get_data['id'] = $tbl_data['id'];
                    $get_data['latest_quantity'] = number_format((float)$tbl_data['latest_quantity'], 2, '.', '');
                    if($get_old_quantity == 0){
                      $get_data['previous_quantity'] = $get_old_quantity;
                    } else {
                      $get_data['previous_quantity'] = number_format((float)$get_old_quantity, 2, '.', '');
                    }
                    $get_data['region'] = $region;
                    $get_data['commodity'] = $commodity;
                    //$get_data['unit'] = $tbl_data[$unitColumnName];
					$get_data['unit'] ="MnT";
                    $get_data['frequency'] = $frequency;
                    $get_data['reference'] = $get_year[0];
                    $get_data['mom'] = $this->getMom($tbl_data['latest_quantity'],$get_old_quantity);
                  }
                

              } else {

                $tbl_data = DB::table($stat_table_name)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region);
                 if($mainCommodityName!='' && $commodityColumn!='commodity' )
                    $tbl_data= $tbl_data->where('commodity_new',$mainCommodityName); 
                $tbl_data= $tbl_data->where($yearColumnName,$get_year[0])->select('id',$yearColumnName,DB::raw("SUM($quantityColumnName)/1000000 as latest_quantity"),$unitColumnName)->take(1)->get();

                $tbl_data = json_decode(json_encode($tbl_data), true);
                $tbl_data = $tbl_data[0];

                if($tbl_data){
                  $get_old_quantity = 0;
                  $get_data['id'] = $tbl_data['id'];
                   $get_data['latest_quantity'] = number_format((float)$tbl_data['latest_quantity'], 2, '.', '');
                  $get_data['previous_quantity'] = $get_old_quantity;
                  $get_data['region'] = $region;
                  $get_data['commodity'] = $commodity;
                  //$get_data['unit'] = $tbl_data[$unitColumnName];
				  $get_data['unit'] ="MnT";
                  $get_data['frequency'] = $frequency;
                  $get_data['reference'] = $get_year[0];
                  $get_data['mom'] = $this->getMom($tbl_data['latest_quantity'],$get_old_quantity);
                }
              }
        } else {
            $get_months = DB::table($stat_table_name)
                            ->where($commodityColumn,$commodity)
                            ->where($columnNameOfRegion,$region);
            if($mainCommodityName!='' && $commodityColumn!='commodity' )
                $get_months= $get_months->where('commodity_new',$mainCommodityName); 
                        
            $get_months= $get_months->where($yearColumnName,$get_year[0])
                ->orderByRaw("FIELD($monthColumName, 'December', 'November', 'October','September','August','July','June','May','April','March','February','January') ASC")
                ->take(2)
                ->groupBy($monthColumName)
                ->lists($monthColumName);
//if( $memid== 116707 )
//    DB::enableQueryLog();

            $tbl_data = DB::table($stat_table_name)
                            //->where($commodityColumn,$commodity)
                            ->where($columnNameOfRegion,$region)
                            ->where($yearColumnName,$get_year[0])
                            ->where($monthColumName,$get_months[0])
                            ->select('id',$monthColumName,$yearColumnName,DB::raw("SUM($quantityColumnName)/1000000 as latest_quantity"),$unitColumnName)
                            ;
       
       //if( $memid== 116707 ){
        //  echo $appType . strtolower($commodity);
       //}
       
       if($mainCommodityName!='' && $commodityColumn!='commodity' )
            $tbl_data= $tbl_data->where('commodity_new',$mainCommodityName); 
                            
        if( 'CM' != $appType && strtolower($commodity) == 'coal'  )                            
                $tbl_data = $tbl_data -> whereIn('subCommodity', ["Coking Coal", "Met Coke"]);        
        else
                $tbl_data = $tbl_data ->where($commodityColumn,$commodity);
                
                $tbl_data = $tbl_data->take(1)
                            ->get();
                
                
         // if( $memid== 116707 )
          //  dd(DB::getQueryLog());                            
            $tbl_data = json_decode(json_encode($tbl_data), true);
            $tbl_data = $tbl_data[0];
               
            if(count($get_months) >= 2){
                
                $get_old_quantity = DB::table($stat_table_name)
                                    //->where($commodityColumn,$commodity)
                                    ->where($columnNameOfRegion,$region)
                                    ->where($yearColumnName,$get_year[0])
                                    ->where($monthColumName,$get_months[1])
                                    ;
                  if($mainCommodityName!='' && $commodityColumn!='commodity' )
                    $get_old_quantity= $get_old_quantity->where('commodity_new',$mainCommodityName); 
            
                if( 'CM' != $appType && strtolower($commodity) == 'coal'  )                            
                        $get_old_quantity = $get_old_quantity -> whereIn('subCommodity', ["Coking Coal", "Met Coke"]);        
                else
                        $get_old_quantity = $get_old_quantity ->where($commodityColumn,$commodity);
                
                
                $get_old_quantity = $get_old_quantity->pluck(DB::raw("SUM($quantityColumnName)/1000000"));
                
                $get_data['id'] = $tbl_data['id'];
                $get_data['latest_quantity'] = number_format((float)$tbl_data['latest_quantity'], 2, '.', '');
                if($get_old_quantity == 0){
                      $get_data['previous_quantity'] = $get_old_quantity;
                    } else {
                      $get_data['previous_quantity'] = number_format((float)$get_old_quantity, 2, '.', '');
                    }
                $get_data['region'] = $region;
                $get_data['commodity'] = $commodity;
                //$get_data['unit'] = $tbl_data[$unitColumnName];
            	$get_data['unit'] ="MnT";
                $get_data['frequency'] = $frequency;
                $get_data['reference'] = date("M",strtotime($tbl_data[$monthColumName])).'-'.substr($tbl_data[$yearColumnName], -2);
                $get_data['mom'] = $this->getMom($tbl_data['latest_quantity'],$get_old_quantity);
        
                // DB::raw("SUM($quantityColumnName) - ".$get_old_quantity." as previous_quantity")
            } else if(count($get_months) == 1 && count($get_year) >= 2) {
        
                $get_last_months = DB::table($stat_table_name)
                    //->where($commodityColumn,$commodity)
                    ->where($columnNameOfRegion,$region)
                    ->where($yearColumnName,$get_year[1]);
                    
                if($mainCommodityName!='' && $commodityColumn!='commodity' )
                  $get_last_months= $get_last_months->where('commodity_new',$mainCommodityName); 
                        
                $get_last_months= $get_last_months->orderByRaw("FIELD($monthColumName, 'December', 'November', 'October','September','August','July','June','May','April','March','February','January') ASC")
                    ->groupBy($monthColumName);
                    
                if( 'CM' != $appType && strtolower($commodity) == 'coal'  )                            
                        $get_last_months = $get_last_months -> whereIn('subCommodity', ["Coking Coal", "Met Coke"]);        
                else
                        $get_last_months = $get_last_months ->where($commodityColumn,$commodity);
                $get_last_months = $get_last_months -> pluck($monthColumName);
        
                $get_old_quantity = DB::table($stat_table_name)
                    //->where($commodityColumn,$commodity)
                    ->where($columnNameOfRegion,$region)
                    ->where($yearColumnName,$get_year[1])
                    ->where($monthColumName,$get_last_months);
                
                 if($mainCommodityName!='' && $commodityColumn!='commodity' )
                    $get_old_quantity= $get_old_quantity->where('commodity_new',$mainCommodityName); 
                        
                if( 'CM' != $appType && strtolower($commodity) == 'coal'  )                            
                        $get_old_quantity = $get_old_quantity -> whereIn('subCommodity', ["Coking Coal", "Met Coke"]);        
                else
                        $get_old_quantity = $get_old_quantity ->where($commodityColumn,$commodity);
                $get_old_quantity = $get_old_quantity ->pluck(DB::raw("SUM($quantityColumnName)/1000000"));
        
                $get_data['id'] = $tbl_data['id'];
                $get_data['latest_quantity'] = number_format((float)$tbl_data['latest_quantity'], 2, '.', '');
        
               
                if($get_old_quantity == 0){
                  $get_data['previous_quantity'] = $get_old_quantity;
                } else {
                  $get_data['previous_quantity'] = number_format((float)$get_old_quantity, 2, '.', '');
                }
                
                $get_data['region'] = $region;
                $get_data['commodity'] = $commodity;
                //$get_data['unit'] = $tbl_data[$unitColumnName];
        		$get_data['unit'] ="MnT";
                $get_data['frequency'] = $frequency;
                $get_data['reference'] = date("M",strtotime($tbl_data[$monthColumName])).'-'.substr($tbl_data[$yearColumnName], -2);
                $get_data['mom'] = $this->getMom($tbl_data['latest_quantity'],$get_old_quantity);
        
            } else {
                $get_old_quantity = 0;
                $get_data['id'] = $tbl_data['id'];
                $get_data['latest_quantity'] = number_format((float)$tbl_data['latest_quantity'], 2, '.', '');
                $get_data['previous_quantity'] = $get_old_quantity;
                $get_data['region'] = $region;
                $get_data['commodity'] = $commodity;
                //$get_data['unit'] = $tbl_data[$unitColumnName];
            	$get_data['unit'] ="MnT";
                $get_data['frequency'] = $frequency;
                $get_data['reference'] = date("M",strtotime($tbl_data[$monthColumName])).'-'.substr($tbl_data[$yearColumnName], -2);
                $get_data['mom'] = $this->getMom($tbl_data['latest_quantity'],$get_old_quantity);
            }
  }
  $get_data['unit'] ="MnT";
  $get_data['mainCommodity'] =$mainCommodityName;
  //$get_data['unitText'] = 'Unit showing in MT';
  return $get_data;
    } else {
      return $data = array();
    }

    }




  /*Case 1: when user select all requried field, like-comdity-sub comodity,region*/
    public function getTabularDataCase2($requiredFor,$datatype,$commodity,$commodityColumn,$region,$columnNameOfRegion,$mainCommodityName=''){

      $stats = DB::table('tbl_stats_datatype_header_list_bm')->where('datatypeName',$datatype)->where('requiredFor',$requiredFor)->take(1)->get();
    
      $stats = json_decode(json_encode($stats), true);
      $stat_table_name = $stats[0]['tableName'];


       $monthColumName = 'month_of_loading';
       $yearColumnName = 'year_of_loading';
       $quantityColumnName = 'quantity';
       $unitColumnName = 'quantity_unit';

       if($stat_table_name == 'tbl_capacity'){
         $yearColumnName='capacity_year';
         $quantityColumnName='capacity';
         $unitColumnName = 'qty_unit';
         $frequency = 'yearly';
       }
       else if($stat_table_name=='tbl_production' ||$stat_table_name=='tbl_dispatch' || $stat_table_name == 'tbl_consumption'){
          $monthColumName= 'month';
          $yearColumnName='year';
          $quantityColumnName = 'quantity';
          $unitColumnName = 'unit';
      }else if($stat_table_name=='tbl_indian_coal_stock'){
          $monthColumName= 'month';
          $yearColumnName='year';
          $quantityColumnName = 'quantity';
          $unitColumnName = 'unit';
         }
		 
	$tmpCommodityColumn = $commodityColumn;
	if( $commodityColumn == "commodity_new" )
		$tmpCommodityColumn = 'commodity';
      $all_regions = DB::table("tbl_stats_selection_compare_bm")
						->where('datatypeName',$datatype)
						->where($tmpCommodityColumn,$commodity);
						
     if($mainCommodityName!='' && $commodityColumn != "commodity_new")
        $all_regions = $all_regions->where('commodity',$mainCommodityName);
        $all_regions = $all_regions->groupBy('region')
        ->select('region','col_name_region')
        ->get();

///DB::enableQueryLog();dd(DB::getQueryLog());
      $all_regions = json_decode(json_encode($all_regions), true);

      $dd = array();

      foreach ($all_regions as $key => $r) {
        $region = $r['region'];
        $columnNameOfRegion = $r['col_name_region'];

        if($stat_table_name == 'tbl_capacity'){
           $frequency = 'yearly';
        }
        else
        {
            //$frequency = DB::table("tbl_stats_datatype_freq")->where($commodityColumn,$commodity)->where('region',$region)->pluck('frequency');
            $frequency = DB::table("tbl_stats_datatype_freq")->where($tmpCommodityColumn,$commodity);
            if($mainCommodityName!='')
                $frequency = $frequency->where('commodity',$mainCommodityName);
            
            $frequency = $frequency->pluck('frequency');
  
            if(!$frequency){
                $frequency = 'monthly';
              }
        }      

        $get_year = DB::table($stat_table_name)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region);
         if($mainCommodityName!='')
           $get_year = $get_year->where('commodity_new',$mainCommodityName);
        $get_year=$get_year->orderBy($yearColumnName,'desc')->take(2)->groupBy($yearColumnName)->lists($yearColumnName);
        
      if($get_year){
        if($frequency == 'yearly'){
              if(count($get_year) >= 2){
                $tbl_data = DB::table($stat_table_name)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region);
                if($mainCommodityName!='')
                    $tbl_data = $tbl_data->where('commodity_new',$mainCommodityName);
                $tbl_data=$tbl_data->where($yearColumnName,$get_year[0])->select('id',$yearColumnName,DB::raw("SUM($quantityColumnName)/1000000 as latest_quantity"),$unitColumnName)->take(1)->get();

                $tbl_data = json_decode(json_encode($tbl_data), true);
                $tbl_data = $tbl_data[0];
                                  
                $get_old_quantity = DB::table($stat_table_name)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region);
                
                if($mainCommodityName!='')
                    $get_old_quantity = $get_old_quantity->where('commodity_new',$mainCommodityName);
                $get_old_quantity = $get_old_quantity->where($yearColumnName,$get_year[1])->pluck(DB::raw("SUM($quantityColumnName)/1000000"));
                   

                  if($tbl_data){
                    $get_data['id'] = $tbl_data['id'];
                     $get_data['latest_quantity'] = number_format((float)$tbl_data['latest_quantity'], 2, '.', '');

                    if($get_old_quantity == 0){
                      $get_data['previous_quantity'] = $get_old_quantity;
                    } else {
                      $get_data['previous_quantity'] = number_format((float)$get_old_quantity, 2, '.', '');
                    }

                    $get_data['region'] = $region;
                    $get_data['commodity'] = $commodity;
                    $get_data['mainCommodity'] = $mainCommodityName;
                    //$get_data['unit'] = $tbl_data[$unitColumnName];
					$get_data['unit'] = "MnT";
                    $get_data['frequency'] = $frequency;
                    $get_data['reference'] = $get_year[0];
                    $get_data['mom'] = $this->getMom($tbl_data['latest_quantity'],$get_old_quantity);
                    $dd[] = $get_data;
                  }

                         

              } else {

                $tbl_data = DB::table($stat_table_name)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region);
                if($mainCommodityName!='')
                    $tbl_data = $tbl_data->where('commodity_new',$mainCommodityName);
               $tbl_data = $tbl_data->where($yearColumnName,$get_year[0])->select('id',$yearColumnName,DB::raw("SUM($quantityColumnName)/1000000 as latest_quantity"),$unitColumnName)->take(1)->get();

                $tbl_data = json_decode(json_encode($tbl_data), true);
                $tbl_data = $tbl_data[0];

                if($tbl_data){
                  $get_old_quantity = 0;
                  $get_data['id'] = $tbl_data['id'];
                  $get_data['latest_quantity'] = number_format((float)$tbl_data['latest_quantity'], 2, '.', '');
                  $get_data['previous_quantity'] = $get_old_quantity;
                  $get_data['region'] = $region;
                  $get_data['commodity'] = $commodity;
                  $get_data['mainCommodity'] = $mainCommodityName;
                  //$get_data['unit'] = $tbl_data[$unitColumnName];
				  $get_data['unit'] = "MnT";
                  $get_data['frequency'] = $frequency;
                  $get_data['reference'] = $get_year[0];
                  $get_data['mom'] = $this->getMom($tbl_data['latest_quantity'],$get_old_quantity);
      
                  $dd[] = $get_data;
                }

              }

        } else {
            $get_months = DB::table($stat_table_name)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region);
            
            if($mainCommodityName!='')
                $get_months = $get_months->where('commodity_new',$mainCommodityName);
            $get_months = $get_months->where($yearColumnName,$get_year[0])->orderByRaw("FIELD($monthColumName, 'December', 'November', 'October','September','August','July','June','May','April','March','February','January') ASC")->take(2)->groupBy($monthColumName)->lists($monthColumName);


        $tbl_data = DB::table($stat_table_name)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region);
        if($mainCommodityName!='')
            $tbl_data = $tbl_data->where('commodity_new',$mainCommodityName);
        
        $tbl_data = $tbl_data->where($yearColumnName,$get_year[0])->where($monthColumName,$get_months[0])->select('id',$monthColumName,$yearColumnName,DB::raw("SUM($quantityColumnName)/1000000 as latest_quantity"),$unitColumnName)->take(1)->get();

        $tbl_data = json_decode(json_encode($tbl_data), true);
        $tbl_data = $tbl_data[0];
               
    

    if(count($get_months) >= 2){
        
    $get_old_quantity = DB::table($stat_table_name)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region);
    if($mainCommodityName!='')
        $get_old_quantity = $get_old_quantity->where('commodity_new',$mainCommodityName);
    
     $get_old_quantity = $get_old_quantity->where($yearColumnName,$get_year[0])->where($monthColumName,$get_months[1])->pluck(DB::raw("SUM($quantityColumnName)/1000000"));
    
    $get_data['id'] = $tbl_data['id'];
    $get_data['latest_quantity'] = number_format((float)$tbl_data['latest_quantity'], 2, '.', '');

    if($get_old_quantity == 0){
          $get_data['previous_quantity'] = $get_old_quantity;
        } else {
          $get_data['previous_quantity'] = number_format((float)$get_old_quantity, 2, '.', '');
        }

    $get_data['region'] = $region;
    $get_data['commodity'] = $commodity;
    $get_data['mainCommodity'] = $mainCommodityName;
    //$get_data['unit'] = $tbl_data[$unitColumnName];
	$get_data['unit'] = "MnT";
    $get_data['frequency'] = $frequency;
    $get_data['reference'] = date("M",strtotime($tbl_data[$monthColumName])).'-'.substr($tbl_data[$yearColumnName], -2);
    $get_data['mom'] = $this->getMom($tbl_data['latest_quantity'],$get_old_quantity);
    $dd[] = $get_data;

    // DB::raw("SUM($quantityColumnName) - ".$get_old_quantity." as previous_quantity")
    } else if(count($get_months) == 1 && count($get_year) >= 2) {

        $get_last_months = DB::table($stat_table_name)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region);
        
        if($mainCommodityName!='')
          $get_last_months = $get_last_months->where('commodity_new',$mainCommodityName);
       $get_last_months=$get_last_months->where($yearColumnName,$get_year[1])->orderByRaw("FIELD($monthColumName, 'December', 'November', 'October','September','August','July','June','May','April','March','February','January') ASC")->groupBy($monthColumName)->pluck($monthColumName);

        $get_old_quantity = DB::table($stat_table_name)->where($commodityColumn,$commodity)->where($columnNameOfRegion,$region);
        If($mainCommodityName!='')
            $get_old_quantity = $get_old_quantity->where('commodity_new',$mainCommodityName);
            
        $get_old_quantity =$get_old_quantity->where($yearColumnName,$get_year[1])->where($monthColumName,$get_last_months)->pluck(DB::raw("SUM($quantityColumnName)/1000000"));

        $get_data['id'] = $tbl_data['id'];
      $get_data['latest_quantity'] = number_format((float)$tbl_data['latest_quantity'], 2, '.', '');

        if($get_old_quantity == 0){
          $get_data['previous_quantity'] = $get_old_quantity;
        } else {
          $get_data['previous_quantity'] = number_format((float)$get_old_quantity, 2, '.', '');
        }

        $get_data['region'] = $region;
        $get_data['commodity'] = $commodity;
        $get_data['mainCommodity'] = $mainCommodityName;
        //$get_data['unit'] = $tbl_data[$unitColumnName];
		$get_data['unit'] = "MnT";
        $get_data['frequency'] = $frequency;
        $get_data['reference'] = date("M",strtotime($tbl_data[$monthColumName])).'-'.substr($tbl_data[$yearColumnName], -2);
        $get_data['mom'] = $this->getMom($tbl_data['latest_quantity'],$get_old_quantity);
        $dd[] = $get_data;

    } else {

    $get_old_quantity = 0;
    $get_data['id'] = $tbl_data['id'];
    $get_data['latest_quantity'] = number_format((float)$tbl_data['latest_quantity'], 2, '.', '');
    $get_data['previous_quantity'] = $get_old_quantity;
    $get_data['region'] = $region;
    $get_data['commodity'] = $commodity;
    $get_data['mainCommodity'] = $mainCommodityName;
    //$get_data['unit'] = $tbl_data[$unitColumnName];
	$get_data['unit'] = "MnT";
    $get_data['frequency'] = $frequency;
    $get_data['reference'] = date("M",strtotime($tbl_data[$monthColumName])).'-'.substr($tbl_data[$yearColumnName], -2);
    $get_data['mom'] = $this->getMom($tbl_data['latest_quantity'],$get_old_quantity);
    $dd[] = $get_data;

    }

  }

    } 

  }

  return $dd;

  }


  /*Case 3: when user select all requried field, like-comdity-sub comodity,region*/
public function getTabularDataCase3($requiredFor,$datatype,$commodity,$commodityColumn,$region,$columnNameOfRegion, $memid=0){

    $stats = DB::table('tbl_stats_datatype_header_list_bm')->where('datatypeName',$datatype)->where('requiredFor',$requiredFor)->take(1)->get();
    
    $stats = json_decode(json_encode($stats), true);
    $stat_table_name = $stats[0]['tableName'];
    
    $monthColumName = 'month_of_loading';
    $yearColumnName = 'year_of_loading';
    $quantityColumnName = 'quantity';
    $unitColumnName = 'quantity_unit';
    
    if($stat_table_name == 'tbl_capacity'){
        $yearColumnName='capacity_year';
        $quantityColumnName='capacity';
        $unitColumnName = 'qty_unit';
        $frequency = 'yearly';
    }else if($stat_table_name=='tbl_production' ||$stat_table_name=='tbl_dispatch' || $stat_table_name == 'tbl_consumption' || $stat_table_name=='tbl_indian_coal_stock'){
        $monthColumName= 'month';
        $yearColumnName='year';
        $quantityColumnName = 'quantity';
        $unitColumnName = 'unit';
    }
    
    $sub_sub_commodity = DB::table("tbl_stats_selection_compare_bm")
                        ->where('datatypeName',$datatype)
                        ->where("region",$region)
                        ->where("subSubCommodity",'!=','')
                        ->groupBy('subSubCommodity', 'commodity')
                        ->select('subSubCommodity', 'commodity');
                        
    $sub_sub_commodity_ids = DB::table("tbl_stats_selection_compare_bm")
                            ->where('datatypeName',$datatype)
                            ->where("region",$region)
                            ->where("subSubCommodity",'!=','')
                            ->lists('id');
    
  // if( $memid== 116707 )
  //     DB::enableQueryLog();
    $sub_commodity = DB::table("tbl_stats_selection_compare_bm")
                        ->where('datatypeName',$datatype)
                        ->where("region",$region)
                        //->whereNotIn('id',$sub_sub_commodity_ids)
                        ->groupBy('subCommodity', 'commodity')
                        ->select('subCommodity', 'commodity')
                        ->unionAll($sub_sub_commodity)
    -> orderByRaw("
        case 
        when commodity='Iron Ore' then 1
        when commodity='Coal' then 2
        when commodity='Scrap & Metallics' then 3
        when commodity='Steel' then 4
        when commodity='Ferro Alloy' then 5
        when commodity='Ship Breaking' then 6
        when commodity='Graphite Electrode' then 7
        else 99
        end,
        case subCommodity
        when 'Fines/Lumps' then 1
        when 'Pellet/Concentrate' then 2
        when 'Non Coking Coal' then 3
        when 'Coking Coal' then 4
        when 'Met Coke' then 5
        when 'Sponge Iron' then 6
        when 'Pig Iron' then 7
        when 'Ferrous Scrap' then 8
        when 'Crude Steel' then 9
        when 'Semi Finish' then 10
        when 'Finish Long' then 11
        when 'Finish Flat' then 12
        when 'Manganese Ore' then 13
        when 'Chrome Ore' then 14
        when 'Ferro Silico Manganese' then 15
        when 'Ferro Manganese' then 16
        when 'Ferro Chrome' then 17
        when 'Finish Long Stainless Steel' then 18
        when 'Finish Flat Stainless Steel' then 19
        when 'Graphite Electrode' then 20
        when 'Ship Breaking' then 21
        else 99
        end					
    ")
    ->get();
    
  //  if( $memid== 116707 )
   //     dd(DB::getQueryLog());
        
    $commodity = $sub_commodity;
    $commodities = json_decode(json_encode($commodity), true);
    $dd = array();
    foreach ($commodities as $key => $r) {
        $mainCommodity='';
        if(isset($r['subSubCommodity'])){
            $commodityColumn = 'subSubCommodity';
            $commodity = $r['subSubCommodity'];
            $mainCommodity = $r['commodity'];
        } else {
            $commodityColumn = 'subCommodity';
            $commodity = $r['subCommodity'];
            $mainCommodity = $r['commodity'];
        }
        
        if($stat_table_name == 'tbl_capacity'){
            $frequency = 'yearly';
        }else {
            $tmpCommodityColumn = $commodityColumn;
            if( $commodityColumn == "commodity_new" )
                $tmpCommodityColumn = 'commodity';
          
            //$frequency = DB::table("tbl_stats_datatype_freq")->where($commodityColumn,$commodity)->where('region',$region)->pluck('frequency');
            $frequency = DB::table("tbl_stats_datatype_freq")->where($tmpCommodityColumn,$commodity);
            if($tmpCommodityColumn != 'commodity' && $mainCommodity!='')
                $frequency=$frequency->where('commodity',$mainCommodity);           
            
            $frequency=$frequency->pluck('frequency');
            if(!$frequency){
                $frequency = 'monthly';
            }
        }   
        
        $get_year = DB::table($stat_table_name)
                    ->where($commodityColumn,$commodity)
                    ->where($columnNameOfRegion,$region);
                    
        if($mainCommodity!='')
           $get_year=$get_year->where('commodity_new',$mainCommodity);  
                
        $get_year=$get_year->orderBy($yearColumnName,'desc')
        ->take(2)
        ->groupBy($yearColumnName)
        ->lists($yearColumnName);
        
        
        if($get_year){
            if($frequency == 'yearly'){
                if(count($get_year) >= 2){
                    $tbl_data = DB::table($stat_table_name)->where($commodityColumn,$commodity);
                    if($mainCommodity!='')
                        $tbl_data=$tbl_data->where('commodity_new',$mainCommodity);  
                    
                    $tbl_data=$tbl_data->where($columnNameOfRegion,$region)->where($yearColumnName,$get_year[0])->select('id',$yearColumnName,DB::raw("SUM($quantityColumnName)/1000000 as latest_quantity"),$unitColumnName)->take(1)->get();
                    
                    $tbl_data = json_decode(json_encode($tbl_data), true);
                    $tbl_data = $tbl_data[0];
                    
                    $get_old_quantity = DB::table($stat_table_name)->where($commodityColumn,$commodity);
                    if($mainCommodity!='')
                        $get_old_quantity=$get_old_quantity->where('commodity_new',$mainCommodity);  
                    
                    $get_old_quantity=$get_old_quantity->where($columnNameOfRegion,$region)->where($yearColumnName,$get_year[1])->pluck(DB::raw("SUM($quantityColumnName)/1000000"));
                    
                    
                    if($tbl_data){
                        $get_data['id'] = $tbl_data['id'];
                        $get_data['latest_quantity'] = number_format((float)$tbl_data['latest_quantity'], 2, '.', '');
                    
                        if($get_old_quantity == 0){
                            $get_data['previous_quantity'] = $get_old_quantity;
                        } else {
                            $get_data['previous_quantity'] = number_format((float)$get_old_quantity, 2, '.', '');
                        }
            
                        $get_data['region'] = $region;
                        $get_data['commodity'] = $commodity;
                        $get_data['mainCommodity'] = $mainCommodity;
                        //$get_data['unit'] = $tbl_data[$unitColumnName];
                        $get_data['unit'] = "MnT";
                        $get_data['frequency'] = $frequency;
                        $get_data['reference'] = $get_year[0];
                        $get_data['mom'] = $this->getMom($tbl_data['latest_quantity'],$get_old_quantity);
                        $dd[] = $get_data;
                    }
                } else {
                
                    $tbl_data = DB::table($stat_table_name)->where($commodityColumn,$commodity);
                     if($mainCommodity!='')
                        $tbl_data=$tbl_data->where('commodity_new',$mainCommodity);  
                    
                    $tbl_data=$tbl_data->where($columnNameOfRegion,$region)->where($yearColumnName,$get_year[0])->select('id',$yearColumnName,DB::raw("SUM($quantityColumnName)/1000000 as latest_quantity"),$unitColumnName)->take(1)->get();
                    
                    $tbl_data = json_decode(json_encode($tbl_data), true);
                    $tbl_data = $tbl_data[0];
                    
                    if($tbl_data){
                        $get_old_quantity = 0;
                        $get_data['id'] = $tbl_data['id'];
                        $get_data['latest_quantity'] = number_format((float)$tbl_data['latest_quantity'], 2, '.', '');
                        
                        $get_data['previous_quantity'] = $get_old_quantity;
                        $get_data['region'] = $region;
                        $get_data['commodity'] = $commodity;
                        $get_data['mainCommodity'] = $mainCommodity;
                        //$get_data['unit'] = $tbl_data[$unitColumnName];
                        $get_data['unit'] = "MnT";
                        $get_data['frequency'] = $frequency;
                        $get_data['reference'] = $get_year[0];
                        $get_data['mom'] = $this->getMom($tbl_data['latest_quantity'],$get_old_quantity);
                        
                        $dd[] = $get_data;
                    }
                
                }
            
            } else {
                $get_months = DB::table($stat_table_name)->where($commodityColumn,$commodity);
                if($mainCommodity!='')
                    $get_months=$get_months->where('commodity_new',$mainCommodity);  
                
                $get_months = $get_months->where($columnNameOfRegion,$region)->where($yearColumnName,$get_year[0])->orderByRaw("FIELD($monthColumName, 'December', 'November', 'October','September','August','July','June','May','April','March','February','January') ASC")->take(2)->groupBy($monthColumName)->lists($monthColumName);
               
               $tbl_data = DB::table($stat_table_name)->where($commodityColumn,$commodity);
               
                if($mainCommodity!='')
                  $tbl_data=$tbl_data->where('commodity_new',$mainCommodity);
               
               $tbl_data=$tbl_data->where($columnNameOfRegion,$region)->where($yearColumnName,$get_year[0])->where($monthColumName,$get_months[0])->select('id',$monthColumName,$yearColumnName,DB::raw("SUM($quantityColumnName)/1000000 as latest_quantity"),$unitColumnName)->take(1)->get();
                
                $tbl_data = json_decode(json_encode($tbl_data), true);
                $tbl_data = $tbl_data[0];
            
                if(count($get_months) >= 2){
                    $get_old_quantity = DB::table($stat_table_name)->where($commodityColumn,$commodity);
                    
                    if($mainCommodity!='')
                        $get_old_quantity=$get_old_quantity->where('commodity_new',$mainCommodity);  
                    
                    $get_old_quantity=$get_old_quantity->where($columnNameOfRegion,$region)->where($yearColumnName,$get_year[0])->where($monthColumName,$get_months[1])->pluck(DB::raw("SUM($quantityColumnName)/1000000"));
                    $get_data['id'] = $tbl_data['id'];
                    $get_data['latest_quantity'] = number_format((float)$tbl_data['latest_quantity'], 2, '.', '');
                    if($get_old_quantity == 0){
                        $get_data['previous_quantity'] = $get_old_quantity;
                    } else {
                        $get_data['previous_quantity'] = number_format((float)$get_old_quantity, 2, '.', '');
                    }
                    $get_data['region'] = $region;
                    $get_data['commodity'] = $commodity;
                    $get_data['mainCommodity'] = $mainCommodity;
                    //$get_data['unit'] = $tbl_data[$unitColumnName];
                    $get_data['unit'] = "MnT";
                    $get_data['frequency'] = $frequency;
                    $get_data['reference'] = date("M",strtotime($tbl_data[$monthColumName])).'-'.substr($tbl_data[$yearColumnName], -2);
                    $get_data['mom'] = $this->getMom($tbl_data['latest_quantity'],$get_old_quantity);
                    $dd[] = $get_data;
                    
                    // DB::raw("SUM($quantityColumnName) - ".$get_old_quantity." as previous_quantity")
                } else if(count($get_months) == 1 && count($get_year) >= 2) {
                
                    $get_last_months = DB::table($stat_table_name)->where($commodityColumn,$commodity);
                    if($mainCommodity!='')
                        $get_last_months=$get_last_months->where('commodity_new',$mainCommodity);  
                
                    
                    $get_last_months=$get_last_months->where($columnNameOfRegion,$region)->where($yearColumnName,$get_year[1])->orderByRaw("FIELD($monthColumName, 'December', 'November', 'October','September','August','July','June','May','April','March','February','January') ASC")->groupBy($monthColumName)->pluck($monthColumName);
                    
                    $get_old_quantity = DB::table($stat_table_name)->where($commodityColumn,$commodity);
                    
                    if($mainCommodity!='')
                        $get_old_quantity=$get_old_quantity->where('commodity_new',$mainCommodity);  
                        
                    $get_old_quantity=$get_old_quantity->where($columnNameOfRegion,$region)->where($yearColumnName,$get_year[1])->where($monthColumName,$get_last_months)->pluck(DB::raw("SUM($quantityColumnName)/1000000"));
                    
                    $get_data['id'] = $tbl_data['id'];
                    $get_data['latest_quantity'] = number_format((float)$tbl_data['latest_quantity'], 2, '.', '');
                    
                    if($get_old_quantity == 0){
                        $get_data['previous_quantity'] = $get_old_quantity;
                    } else {
                        $get_data['previous_quantity'] = number_format((float)$get_old_quantity, 2, '.', '');
                    }
                    
                    $get_data['region'] = $region;
                    $get_data['commodity'] = $commodity;
                    $get_data['mainCommodity'] = $mainCommodity;
                    //$get_data['unit'] = $tbl_data[$unitColumnName];
                    $get_data['unit'] = "MnT";
                    $get_data['frequency'] = $frequency;
                    $get_data['reference'] = date("M",strtotime($tbl_data[$monthColumName])).'-'.substr($tbl_data[$yearColumnName], -2);
                    $get_data['mom'] = $this->getMom($tbl_data['latest_quantity'],$get_old_quantity);
                    $dd[] = $get_data;
                } else {
                    $get_old_quantity = 0;
                    $get_data['id'] = $tbl_data['id'];
                    $get_data['latest_quantity'] = number_format((float)$tbl_data['latest_quantity'], 2, '.', '');
                    $get_data['previous_quantity'] = $get_old_quantity;
                    $get_data['region'] = $region;
                    $get_data['commodity'] = $commodity;
                    $get_data['mainCommodity'] = $mainCommodity;
                    //$get_data['unit'] = $tbl_data[$unitColumnName];
                    $get_data['unit'] = "MnT";
                    $get_data['frequency'] = $frequency;
                    $get_data['reference'] = date("M",strtotime($tbl_data[$monthColumName])).'-'.substr($tbl_data[$yearColumnName], -2);
                    $get_data['mom'] = $this->getMom($tbl_data['latest_quantity'],$get_old_quantity);
                    $dd[] = $get_data;
                }
            
            }
        } 
    }
    return $dd;
}
  
	public function getTabularFilter($datatype, $appType,$memid=0, $market = ''){
		$_not_reqd="";
        //DB::enableQueryLog();
		$data = DB::table('tbl_stats_selection_compare_bm')
		        ->where('datatypeName',$datatype.$_not_reqd)
		        ->whereRaw("FIND_IN_SET('$market', f_market)")
		        ;
		        
		$data = $data -> orderByRaw("
						 case subCommodity
							when 'Fines/Lumps' then 1
							when 'Pellet/Concentrate' then 2
							
							when 'Non Coking Coal' then 3
							when 'Coking Coal' then 4
							when 'PCI Coal' then 5
							when 'Anthracite' then 6
							when 'Lignite' then 7
							when 'Met Coke' then 8
							when 'Pet Coke' then 9
							when 'Bituminous Coal' then 10
							when 'Bituminous' then 10
							when 'Others' then 99
							
							when 'Ferrous Scrap' then 10
							when 'Sponge Iron' then 11
							when 'Pig Iron' then 12
							when 'Hot Metal' then 13							
							
							when 'Semi Finish' then 14
							when 'Finish Long' then 15
							when 'Finish Flat' then 17
							
							when 'Manganese Ore' then 18
							when 'Chrome Ore' then 19
							when 'Ferro Manganese' then 20													
							when 'Ferro Silicon' then 21
							when 'Ferro Chrome' then 22
							when 'Ferro Silico Manganese' then 23
							when 'Silico Manganese' then 23
							
							when 'Crude Steel' then 60	
							when 'Graphite Electrode' then 70
							when 'Finish Long Stainless Steel' then 80
							when 'Finish Flat Stainless Steel' then 90							
							
							else 99
						end, region				
					")
					->get();
//dd(DB::getQueryLog());		 
		/* if($datatype=='production'){
			print_r($stats);
			print_r($data);
		}  */
		$data = json_decode(json_encode($data), true);
		if(count($data) > 0){
			$communityArray = array();
			$tempArray = array();
			$tempSubArray = array();
			$tempSubSubArray = array();
			$regionArray = array();
			$tempsubsubArray = array();
			$regions = array();
			$columnNameOfRegion = '';  
			$l = 0;          
			
			$regionArray_new = array();
			$tempArray_new = array();
			$tempSubArray_new = array();
			
/* echo "<pre>";
print_r($data); */
			foreach ($data as $key => $value) {
				if($columnNameOfRegion==''){
					$columnNameOfRegion = $value['col_name_region'];
				}

				$temps = $value['commodity'].'-'.$value['subCommodity'].'-'.$value['subSubCommodity'];

				if(!in_array($temps,$tempsubsubArray)){
					if($value['subSubCommodity']!=''){
						$communityArray[$value['commodity']][$value['subCommodity']][] = $value['subSubCommodity'];
					}else{
						$communityArray[$value['commodity']][] = $value['subCommodity'];
					}
					$tempsubsubArray[] = $temps;
				}
				$cont = $value['commodity'].'-'.$value['region'];
				$conts = $value['subCommodity'].'-'.$value['region'];
				$contss = $value['subSubCommodity'].'-'.$value['region'];

				if(!in_array($cont, $tempArray)){
					if(!in_array($value['region'],$regionArray[$value['commodity']])){
						$regionArray[$value['commodity']][] = $value['region'];
						$regionArray_new[$value['commodity']][] = $value['region'];
                    }
					$tempArray[] = $cont;
				}

				if(!in_array($contss,$tempSubSubArray) && $value['subSubCommodity'] != ''){
					if(!in_array($value['region'],$regionArray[$value['subSubCommodity']]))
						$regionArray[$value['subSubCommodity']][] = $value['region'];
					$tempSubSubArray[] = $contss;
				} 
				if(!in_array($conts,$tempSubArray)){
					if(!in_array($value['region'],$regionArray[$value['subCommodity']]))
						$regionArray[$value['subCommodity']][] = $value['region'];
					$tempSubArray[] = $conts;
				}
				
				
				$cont_new = $value['commodity'].'-'.$value['subCommodity'].'-'.$value['region'];
				if(!in_array($cont_new,$tempArray_new) && $value['commodity'] != '' && $value['subCommodity'] != ''){
					if(!in_array($value['region'],$regionArray_new[$value['commodity']."-".$value['subCommodity']]))
						$regionArray_new[$value['commodity']."-".$value['subCommodity']][] = $value['region'];
					$tempArray_new[] = $cont_new;
				}
				
				$conts_new = $value['commodity'].'-'.$value['subSubCommodity'].'-'.$value['region'];
				if(!in_array($conts_new,$tempArray_new) && $value['commodity'] != '' && $value['subSubCommodity'] != ''){
					if(!in_array($value['region'],$regionArray_new[$value['commodity']."-".$value['subSubCommodity']]))
						$regionArray_new[$value['commodity']."-".$value['subSubCommodity']][] = $value['region'];
					$tempArray_new[] = $conts_new;
				}
				
				
				if($l == 0 && $value['region'] != ''){
					$regions[] = $value['region'];
					$l = 1;
				}

				if(isset($regions) && !in_array($value['region'],$regions)){
					$regions[] = $value['region'];
				}
			}
		}
		foreach($regionArray as $_comm=>$_arr):
			sort($_arr);
			$regionArray[$_comm] = $_arr;
		endforeach;
		
		sort($regions);
		sort($regions);
		$regionColname = $columnNameOfRegion;
		$dataArray = array(
				   'commodity' => $communityArray,
				   'regionColname' => $regionColname,
				   'commodityRegion' => $regionArray,
				   'commoditySubRegion' => $regionArray_new,
				   'allRegions' => $regions
		  );
		  
		if(!empty(Session::get($filters['selected_market'])['sectionFromDate'])){			
			$sectionFromDate = Session::get($filters['selected_market'])['sectionFromDate'];
			$sectionTodate = Session::get($filters['selected_market'])['sectionTodate'];	
			$sectionTodate = strtotime($sectionTodate) <= strtotime($GLOBALS['currDate']) ?$sectionTodate:$GLOBALS['currDate'];
			$dataArray['startDate'] = $sectionFromDate;
			$dataArray['endDate'] = $sectionTodate;
		
		}else{
			$dataArray['startDate'] = $GLOBALS['currDate'];
			$dataArray['endDate'] = $GLOBALS['currDate'];
		}  

		return $dataArray;

   }


   
     public function getMom($latest_quantity,$old_quantity){
      $mom = $latest_quantity - $old_quantity;
      $mom = number_format((float)$mom, 3, '.', '');
      if($old_quantity == 0){
        $mom_per = "100";
      } else {
        $mom_per = (1 - $old_quantity / $latest_quantity) * 100;
      }
      return $mom.'('.number_format((float)$mom_per, 2, '.', '').'%)';
   }
   
   
   public function checkDatatypeExistTabular($attr,$requiredFor){
     $check = 'true';
     if(DB::table('tbl_stats_datatype_header_list_bm')->where('datatypeName',$attr)->where('requiredFor',$requiredFor)->count() < 1 || DB::table('tbl_stats_selection_compare_bm')->where('datatypeName',$attr)->count() < 1){
           $check="false";
     }
     return $check;
   }

/* Map APi */


public function getComandRegionList($attr){
  $datatype = strtolower(trim($attr['dataType']));
  if($datatype){
	  
	$isCM = '';
	if('' != $attr['filters']['app_type']){
		$isCM = $attr['filters']['app_type'];
	}
	  
	 // DB::enableQueryLog();
     $data = DB::table('tbl_map_left_toolbox')
			->where('datatypeName',$datatype);
	 
	 if( "CM" == $isCM )
			$data -> where('commodity', 'Coal');
		
	 $data =	$data->get();
	
	//dd(DB::getQueryLog());
     //$data = json_decode(json_encode($data), true);
	 if( "CM" == $isCM ){
		 /* echo "<pre>";
		 print_r($data);die; */
	 }
     if(count($data) > 0){
      $responceArray = array();
      $checked = array();
       foreach ($data as $key => $value) {
         $responceArray[$value['commodity']][] = array(
             'col_name_commodity' => $value['col_name_commodity'],
             'region' => $value['region'],
             'commodityID' => $value['commodityID'],
             'regionID' => $value['regionID']
          ); 
         if(!in_array($value['region'], $checked)){
            if( $value['region'] != '' )
                $checked[$value['regionID']] = $value['region']; 
         }
       }
        $responceArray['allRegion'] = $checked;
       return $responceArray;
     }else{
      return 'false';
     }
  }else{
    return 'false';
  }
}

public function checkSelectionNameexist($attr){
  $memid = trim($attr['memid']);
  $name = trim($attr['name']);
  if(DB::table('tbl_map_selection_saved_list')->where('memid',$memid)->where('name',$name)->count() > 0){
    return 'false';
  }else{
    return 'true';
  }
}
public function uploadMapFromBase64($attr){
      $image_parts = explode(";base64,", $attr);
      $image_type_aux = explode("image/", $image_parts[0]);
      $image_type = $image_type_aux[1];
      $folderUrl = public_path().'/uploads/map/';//image
      $file = base64_decode($image_parts[1]);
      $safeName = rand(1000000,9999999).'.'.'png';
      if (!File::exists($folderUrl)) {
      File::makeDirectory($folderUrl, $mode = 0777, true, true);
      }
        if(file_put_contents($folderUrl.$safeName, $file)){
          return $safeName;
        }else{
         return 'false';
      }
}
public function saveMapSelectionList($attr){
   $memid = trim($attr['memid']);
   $name = trim($attr['name']);
   $dataType = trim($attr['dataType']);
   $commodity = trim($attr['commodity']);
   $columnNameOfCommodity = trim($attr['columnNameOfCommodity']);
   if(isset($attr['image'])){
    $imageBase64 = $attr['image'];
    $imageName = $this->uploadMapFromBase64($imageBase64);
   }else{
     $imageName=NULL;
   }

   $lastId = DB::table('tbl_map_selection_saved_list')->insertGetId([
     'memid' => $memid,
     'name' => $name,
     'datatype' => $dataType,
     'commodity' => $commodity,
     'image' => $imageName,
     'col_name_commodity' => $columnNameOfCommodity,
     'region' => isset($attr['region']) ? trim($attr['region']) :NULL,
     'added_on'=> date('Y-m-d')
   ]);
 $responceArray = array(
   'lastInsertedId' => $lastId,
   'message' => 'Record Has been Saved!'
 );  
return $lastId;

}
public function getSelectionById($attr){
  $memid = trim($attr['memid']);
  $data = DB::table('tbl_map_selection_saved_list')->where('memid',$memid)->orderBy('id','DESC')->get();
  $data = json_decode(json_encode($data), true);
  $responceArray = array();
  foreach ($data as $key => $value) {
    if($value['image']!=NULL){
       $responceArray[] = array(
         'id' => $value['id'],
         'memid' => $value['memid'],
         'name' => $value['name'],
         'dataType' => $value['datatype'],
         'commodity' => $value['commodity'],
         'columnNameOfCommodity' => $value['col_name_commodity'],
         'image' => url('/uploads/map',$value['image']),
         'region' => $value['region'],
         'added_on' => $value['added_on']  
      ); 
     }else{
       $responceArray[] = array(
         'id' => $value['id'],
         'memid' => $value['memid'],
         'name' => $value['name'],
         'dataType' => $value['datatype'],
         'commodity' => $value['commodity'],
         'columnNameOfCommodity' => $value['col_name_commodity'],
         'image' => $value['image'],
         'region' => $value['region'],
         'added_on' => $value['added_on']  
      ); 
     }
  }
  return $responceArray;
  if(count($data) > 0){
   return $data;
  }else{
    return 'false';
  }
}
public function removeSelectionById($attr){
  $memid = trim($attr['memid']);
  $id = trim($attr['selectionId']);
  if(DB::table('tbl_map_selection_saved_list')->where('memid',$memid)->where('id',$id)->delete()){
    $responceArray = array(
         'removedId' => $id,
         'message' => 'Selection Has been Removed!' 
      );
    return $id;
  }else{
    return 'false';
  }
}

	public function downloadHistoricaldataListTest($attr){
		
		$requiredFor = ucfirst(trim($attr['requiredFor']));
		$type = trim($attr['type']);
		$ex = explode(',', trim($attr['checkedIdList']));

		$fromDates = trim($attr['fromDate']); 
		$date = str_replace('/', '-', $fromDates);
		$fromDate = date("Y-m-d", strtotime($date));

		$toDated = trim($attr['toDate']);
		$date = str_replace('/', '-', $toDated);
		$toDate = date("Y-m-d", strtotime($date));

		$appType = 'SM';
		if(isset($attr['filters']['app_type'])){
			$appType = $attr['filters']['app_type'];
		}

        $selected_market =  $attr['selected_market'];

		$section = 'Prices_Historical';
		if(strtolower($requiredFor)=='price' || strtolower($requiredFor)=='prices')
			$section = 'Prices_Historical';
		elseif(strtolower($requiredFor)=='statistic' || strtolower($requiredFor)=='statistics')
			$section = 'Statistics_Historical';
			
		$memid = 0;
		//update memId if getting in POST request
		if(isset($attr['memid']) && $attr['memid']>0){
		   $memid = trim($attr['memid']);
		}

		$user = new User($memid, $appType);
		$isAccessible =  $user->isAccessible($resourceType='Web', $section,'','','',$selected_market);
		if(!empty(Session::get($selected_market)['sectionFromDate'])){			
			$sectionFromDate = Session::get($selected_market)['sectionFromDate'];
			$sectionTodate = Session::get($selected_market)['sectionTodate'];	
			
			$fromDate = strtotime($fromDate) < strtotime($sectionFromDate) ?$sectionFromDate:$fromDate;
			$toDate = strtotime($toDate) > strtotime($sectionTodate) ?$sectionTodate:$toDate;
			
		}else{
			$fromDate =$attr['fromDate'];  // '2019-07-01'; //$GLOBALS['currDate'];
			$toDate = $attr['toDate']; //'2019-12-31';  //$GLOBALS['currDate'];
		}
		//if( $appType == 'CM' )	
		//	DB::enableQueryLog();
		
		if( $attr['downloadFrom'] == 'listData')
		    $tblName = 'tbl_historical_landing_bm';
		else    
		    $tblName = 'tbl_historical_landing_selections_during_save';
		
			/* if($memid=='116682')		{
				echo "<pre>".$tblName. ' -- ' . $requiredFor; 
				print_r($attr);
			} */
		
		$dataFromParent = DB::table($tblName)->where('section',$requiredFor)->whereIn('id',$ex)->get();
		//if( $appType == 'CM' )	
		//	dd(DB::getQueryLog());	
		
		if(count($dataFromParent)>0){
			
			$dataFromParent = json_decode(json_encode($dataFromParent), true);
			$responceArray = array();
			
			/* if($memid=='116682')		{
				echo "<pre>"; 
				print_r($dataFromParent);
			} */
			
			
			if($section == 'Prices_Historical')
			{
				foreach ($dataFromParent as $key => $value) {
					if($value['tradeType']=='import'){
						$section_val = 'global';
						$type_cnf_fob = 'cnf';
					}
					elseif($value['tradeType']=='export'){
						$section_val = 'global';
						$type_cnf_fob = 'fob';
					}
					else{
						//$section_val = 'domestic';
						$section_val = $value['tradeType'];
						$type_cnf_fob = '';
					}
					$item='';
					if(strtolower($value['commodity']) == 'iron ore'){			
						if($value['subCommodity']=='Pellets')
							$category=$value['subCommodity'];
						else
							$category = $value['commodity'];
					}	
					elseif(strtolower($value['commodity']) == 'coal' && $value['subCommodity']!=''){
							$item=$value['subCommodity'];
							$category = $value['commodity'];
					}		
					elseif(strtolower($value['commodity']) == 'steel' )
						$category = $value['subSubCommodity'];
					else
						$category = $value['subCommodity'];
					
					if($value['country']!='' && $category!=''){
						/*if($category=='Billet/Bloom/Ingot')
						{
							$responceArray[$section_val]['Billet'][] = array('type'=> $type_cnf_fob, 'item'=> $item ,'country'=> $value['country']);
							$responceArray[$section_val]['Ingot'][] = array('type'=> $type_cnf_fob, 'item'=> $item ,'country'=> $value['country']);
						}
						else*/
							$responceArray[$section_val][$category][] = array('type'=> $type_cnf_fob, 'item'=> $item ,'country'=> $value['country']);
					}
				}
				
				$responceArray['from_date']=date('Y-m-d', strtotime($fromDate));
				$responceArray['to_date']=date('Y-m-d', strtotime($toDate));
			}
			else{
				
				//if($memid=='116707' || $memid=='116682')
				{
                    foreach ($dataFromParent as $key => $value) {
                        $colCondition='';$colConditionArr=array();
                        if(trim($value['subCommodity']=='') && ($value['commodity']=='Graphite Electrode' || $value['commodity']=='Crude Steel')){
                            $category = $value['commodity'];
                            $colType='commodity';
                            $colConditionArr[] = " $colType='$category' ";
                        }else{
                            if(trim($value['subSubCommodity']) != '' ){                            
                                if($value['tradeType']=='consumption' && strpos($value['subSubCommodity'], "Finish")!==false){
                                   //in case of consumption "-" stored in database  in subSubCommodity
                                   //no need to match subSubCommodity in this case
                                }else{
                                    $category = $value['subSubCommodity'];
                                    $colType='subSubCommodity';
                                    $colConditionArr[] = " $colType='$category' ";
                                }
                            }
                            if(trim($value['subCommodity']) != '' ){
                                $category = $value['subCommodity']; 
                                $colType='subCommodity';
                                 $colConditionArr[] = " $colType='$category' ";
                            }                            
                            if(trim($value['commodity']) != '' ){
                                $category = $value['commodity']; 
                                $colType='commodity_new';
                                 $colConditionArr[] = " $colType='$category' ";
                            }
                        }
                        if(!empty($colConditionArr))
                           $colCondition  = "(".implode(" and ",$colConditionArr ).")"; 
                        $responceArray[ucfirst($value['tradeType'])][$value['country']][] = array('colCondition'=>$colCondition);
                        //$responceArray[ucfirst($value['tradeType'])][$value['country']][] = array('colType'=>$colType, 'colValue'=>$category);			  ///$responceArray[ucfirst($value['tradeType'])][$value['country']][] = $value['subCommodity'];
                
                    }
                    
                }/*else{
                    foreach ($dataFromParent as $key => $value) {
                        if(trim($value['subCommodity']=='') && ($value['commodity']=='Graphite Electrode' || $value['commodity']=='Crude Steel')){
                            $category = $value['commodity'];
                            $colType='commodity';
                        }elseif(trim($value['subSubCommodity']) != '' ){
                            $category = $value['subSubCommodity'];
                            $colType='subSubCommodity';
                        }else{
                            $category = $value['subCommodity']; 
                            $colType='subCommodity';
                        }
                        $responceArray[ucfirst($value['tradeType'])][$value['country']][] = array('colType'=>$colType, 'colValue'=>$category);			  ///$responceArray[ucfirst($value['tradeType'])][$value['country']][] = $value['subCommodity'];
                
                    }
				}*/
			}
			
			$responceArray['statistics_from_year'] = date('Y', strtotime($fromDate));
			$responceArray['statistics_from_month'] = date('m', strtotime($fromDate));
			$responceArray['statistics_to_year'] = date('Y', strtotime($toDate));
			$responceArray['statistics_to_month'] = date('m', strtotime($toDate));
			
			
            if($section == 'Prices_Historical'){
                if( $appType != 'CM' ){
                    $responceArray['websiteSrc'] = 'steelmint';
                    $responceArray['source'] = 'STEELMINT';
                }
                else{
                    $responceArray['websiteSrc'] = 'coalmint';
                    $responceArray['source'] = 'COALMINT';
                }
			}
			$responceArray['sourceFrom'] = 'newWebsite';
			$responceArray['uid'] = $memid;
			$responceArray['selected_market'] = $selected_market;
			 
			$_REQUEST = $responceArray;
			//print_r($responceArray);
			//echo $section;
			
			/* if($memid=='56350')		{
				echo "<pre>";
				print_r($responceArray);
			}	 */
			
			if($memid>0 && $memid!=102020  && $memid!=160147){
				if($section == 'Prices_Historical'){	
					/*if($memid=='116707')
						include("/home/steelmin/public_html/excel_download/xls_products_new_f_date.php");
					else*/
						include("/home/steelmin/public_html/excel_download_bm/xls_products_new.php");
					
					//$file_path= BASEURL . "excel_download/_20200323_050315.zip";
				}
				else{                    
					include("/home/steelmin/public_html/excel_download_bm/xls_statistics_csv_v2019.php");
				}
				$resValue['filePath'] = $file_path;
			}else{
				return 'Invalid User Session!';
			}
			//print_r($_REQUEST);
			return $resValue;
			//return $responceArray['filePath'] = $file_path;
			//return $responceArray['filePath'] = BASEURL . "excel_download/_intraday_20181026_091049.zip";
		}else{
			return 'No Data Found';
		}
	}
	
	public function nextParentDetailHandler($attr){
	  $nextParentDataType = explode(',', $attr);
	  $nextParentsListDetail = array();
		 if(count($nextParentDataType) > 0){
			foreach ($nextParentDataType as $key => $value) {
			$data = DB::table('tbl_stats_datatype_header_list_bm')->where('datatypeName',$value)->select('col_name_region_stats_toolbox','periodFormat')->get();
			$data = json_decode(json_encode($data), true);
			 $nextParentsListDetail[$value] = array(
			   'colomnNameofRegion' => $data[0]['col_name_region_stats_toolbox'],
			   'periodFormat' => $data[0]['periodFormat']
			  );
		  }
		}
	  return $nextParentsListDetail; 
	}
	
	public function nextParentDetailHandlerApp($attr,$commOrSubComm='commodity',$commodity='',$region='',$selected_market=''){
	  $nextParentDataType = explode(',', $attr);
	  $nextParentsListDetail = array();
		 if(count($nextParentDataType) > 0){
			foreach ($nextParentDataType as $key => $value) {
			if($value!='indian_coal_stock_not_reqd'){
				$data = DB::table('tbl_stats_datatype_header_list_bm')->where('datatypeName',$value)->select('col_name_region_stats_toolbox','periodFormat');
				
				if($selected_market!=''){
                    $data = $data->whereRaw("FIND_IN_SET('$selected_market', f_market)");
                }
				
				$data = $data->get();
				$data = json_decode(json_encode($data), true);
				////if('subCommodity'==$commOrSubComm)DB::enableQueryLog();
				$dataR = DB::table('tbl_stats_left_toolbox_landing_bm')
						->select('startDate as dataAvailableFrom','toDate as dataAvailableTo','lastUpdated')
						->where('tbl_stats_left_toolbox_landing_bm.region','=', $region)
						->where('tbl_stats_left_toolbox_landing_bm.'.$commOrSubComm,'=', $commodity)
						->where('datatypeName','=', $value);
                
                if($selected_market!=''){
                    $dataR = $dataR->whereRaw("FIND_IN_SET('$selected_market', f_market)");
                }
				
				
                $dataR = $dataR->first();
				////if('subCommodity'==$commOrSubComm)dd(DB::getQueryLog());
				if($dataR['dataAvailableFrom']){
				 $nextParentsListDetail[$value] = array(
				   'name' => $value,
				   'columnNameofRegion' => $data[0]['col_name_region_stats_toolbox'],
				   'periodFormat' => $data[0]['periodFormat'],
				   'dataAvailableFrom' => !empty($dataR['dataAvailableFrom'])?date('M-Y', strtotime($dataR['dataAvailableFrom'])):'',
				   'dataAvailableTo' => !empty($dataR['dataAvailableTo'])?date('M-Y', strtotime($dataR['dataAvailableTo'])):'',
				   'lastUpdated' => !empty($dataR['lastUpdated'])?date('d-M-Y', strtotime($dataR['lastUpdated'])):''
				  );
				}
			}
		  }
		}
	  return $nextParentsListDetail; 
	}
	
	function weekOfYear($date) {
		$weekOfYear = intval(date("W", strtotime($date)));
		if (date('n', strtotime($date)) == "1" && $weekOfYear > 52) {
			// It's the last week of the previos year.
			$weekOfYear = 1;    
		}
		return $weekOfYear;
	}
	
    function getMondayOfWeek($date)
    {
        $day = date("D", strtotime($date));
        switch($day)
        {
            case "Mon":
            break;
            case "Tue":
                $date = date("Y-m-d",strtotime($date . '-1 days'));
            break;
            case "Wed":
                $date = date("Y-m-d",strtotime($date . '-2 days'));
            break;
            case "Thu":
                $date = date("Y-m-d",strtotime($date . '-3 days'));
            break;
            case "Fri":
                $date = date("Y-m-d",strtotime($date . '-4 days'));
            break;
            case "Sat":
                $date = date("Y-m-d",strtotime($date . '-5 days'));
            break;
            case "Sun":
                $date = date("Y-m-d",strtotime($date . '-6 days'));
            break;
        }
        return $date;
    }
	
	public function getHistoricalCommRegion($attr){
		if(isset($attr['section'])){
		    
		    //echo "<pre>";
		    //print_r($attr);
		    
			$dataType = $attr['section'];
			$communityArray=Array();
			///DB::enableQueryLog(); 
// 			if(!empty($attr['filters']['app_type'])){
// 				if( $attr['filters']['app_type'] == 'CM' )
// 					$siteFilter = "coalmint";
// 				else 
// 					$siteFilter = "steelmint";
				
// 			}else{
// 				$siteFilter = "steelmint";
// 			}
			$selected_market=$attr['selected_market'];
			$memid = 0;$portFolioIdArray=array();
			if(isset($attr['memid']) && $attr['memid']>0){
			   $memid = trim($attr['memid']);
			   $data = DB::table('tbl_user_portfolio')
					->where('type', '=', 'historical' )
					->where('user_id', '=', $memid )
				    ->get();
				foreach($data as $d1):
					$portFolioIdArray[$d1['row_id']] = $d1['prices_col'];
				endforeach;
				
			}
		//DB::enableQueryLog(); 
            if('price'==$dataType)
            {
                $data = DB::table('tbl_historical_landing_bm')
                ->where('section','price');
            }else{
				$data = DB::table('tbl_historical_landing_bm')
				  ->where('section','statistics')
				  ->where('tradeType',$dataType);
			}
    //         if( isset($attr['allowedHierarchyData']) ){
    //             $colName_commID = json_decode($attr['allowedHierarchyData'], true);
				// $data = $data->whereIn($colName_commID['colName'], $colName_commID['commodityNames']);
    //         }			
            if( isset($attr['selected_market']) ){
                //$selected_market = json_decode($attr['selected_market'], true);
				//$data = $data->where("f_market", $attr['selected_market']);
				//$data = $data->whereRaw("FIND_IN_SET(?, f_market) > 0", [$attr['selected_market']]);
				$data = $data->whereRaw("FIND_IN_SET('$selected_market', f_market)");
            }			
		    $data =	  $data
			   ->where('dataAvailableFrom','>','1990-01-01')
			   ->whereNotIn('commodity',['','-'])
			  //->whereNotIn('subCommodity',['','-'])
			  ->whereNotIn('subCommodity',['-'])
			  ->whereNotIn('subSubCommodity',['-'])
			  ->orderBy('commodity_orderby_1st_level')
			  ->orderBy('commodity_orderby_2nd_level')
			  ->orderBy('region_orderby_1st_level')
			  ->orderBy('region_orderby_2nd_level')
			  ->orderBy('region_orderby_3rd_level');
		   
		   //if( isset($attr['filters']['app_type']) && $attr['filters']['app_type'] == 'CM' )
		//		$data = $data->	where('commodity',"coal");
		
		    $data = $data  ->get();
		    	//dd(DB::getQueryLog());
			$data = json_decode(json_encode($data), true);
		
		  
			if(count($data) > 0){
							
				foreach ($data as $key => $value) {
					if($col_name_region==''){
						$col_name_region = $value['col_name_region'];
					}
					$isAddedONPF=0;
					if(in_array($value['id'], array_keys($portFolioIdArray))){
						$isAddedONPF=1;
					}
					
					$value['dataAvailableTo'] = date('M Y', strtotime($value['dataAvailableTo']));
					$value['dataAvailableFrom'] = date('M Y', strtotime($value['dataAvailableFrom']));
					$value['lastUpdated'] = date('M Y', strtotime($value['lastUpdated']));
					if($value['subSubCommodity']=='' || ($value['tradeType']=='consumption' && strpos($value['subSubCommodity'], "Finish")!==false)){
						if($value['subCommodity']=='' && ($value['commodity']=='Graphite Electrode' || $value['commodity']=='Crude Steel' || $value['commodity']=='Finish Flat Stainless Steel' || $value['commodity']=='Finish Long Stainless Steel')){
							$value['subCommodity'] = $value['commodity']; 
						}elseif($value['subCommodity']==''){
							continue;
						}
						$communityArray['filter'][$value['commodity']][$value['subCommodity']][$value['country']]['commodityWise'][$value['subCommodity']][$value['id']] = array('assessment'=>ucfirst($value['tradeType']).' | '.$value['subCommodity'], 'dataAvailableFrom'=>$value['dataAvailableFrom'], 'dataAvailableTo'=>$value['dataAvailableTo'], 'lastUpdated'=>$value['lastUpdated'], 'isAddedONPF'=>$isAddedONPF);
						$communityArray['filter'][$value['commodity']][$value['subCommodity']][$value['country']]['regionWise'][$value['country']][$value['id']] = array('assessment'=>ucfirst($value['tradeType']).' | '.$value['country'], 'dataAvailableFrom'=>$value['dataAvailableFrom'], 'dataAvailableTo'=>$value['dataAvailableTo'], 'lastUpdated'=>$value['lastUpdated'], 'isAddedONPF'=>$isAddedONPF);
						$communityArray['commodityWise'][$value['subCommodity']][$value['country']][$value['id']] = array('assessment'=>ucfirst($value['tradeType']).' | '.$value['country'], 'dataAvailableFrom'=>$value['dataAvailableFrom'], 'dataAvailableTo'=>$value['dataAvailableTo'], 'lastUpdated'=>$value['lastUpdated'], 'isAddedONPF'=>$isAddedONPF);
						$communityArray['regionWise'][$value['subCommodity']][$value['region_name']][$value['country']][$value['id']] = array('assessment'=>ucfirst($value['tradeType']).' | '.$value['country'], 'dataAvailableFrom'=>$value['dataAvailableFrom'], 'dataAvailableTo'=>$value['dataAvailableTo'], 'lastUpdated'=>$value['lastUpdated'], 'isAddedONPF'=>$isAddedONPF);
						$communityArray['countryWise'][$value['country']][$value['subCommodity']][$value['id']] = array('assessment'=>ucfirst($value['tradeType']).' | '.$value['subCommodity'], 'dataAvailableFrom'=>$value['dataAvailableFrom'], 'dataAvailableTo'=>$value['dataAvailableTo'], 'lastUpdated'=>$value['lastUpdated'], 'isAddedONPF'=>$isAddedONPF);
						$communityArray['regionWise_unique'][$value['commodity']."-".$value['subCommodity']][$value['region_name']][$value['country']][$value['id']] = array('assessment'=>ucfirst($value['tradeType']).' | '.$value['country'], 'dataAvailableFrom'=>$value['dataAvailableFrom'], 'dataAvailableTo'=>$value['dataAvailableTo'], 'lastUpdated'=>$value['lastUpdated'], 'isAddedONPF'=>$isAddedONPF);
						$communityArray['countryWise_new'][$value['country']][$value['commodity']."-".$value['subCommodity']][$value['id']] = array('assessment'=>ucfirst($value['tradeType']).' | '.$value['subCommodity'], 'dataAvailableFrom'=>$value['dataAvailableFrom'], 'dataAvailableTo'=>$value['dataAvailableTo'], 'lastUpdated'=>$value['lastUpdated'], 'isAddedONPF'=>$isAddedONPF);
						$communityArray['commodityWise_new'][$value['commodity']."-".$value['subCommodity']][$value['country']][$value['id']] = array('assessment'=>ucfirst($value['tradeType']).' | '.$value['country'], 'dataAvailableFrom'=>$value['dataAvailableFrom'], 'dataAvailableTo'=>$value['dataAvailableTo'], 'lastUpdated'=>$value['lastUpdated'], 'isAddedONPF'=>$isAddedONPF);
					}else{
						$communityArray['filter'][$value['commodity']][$value['subCommodity']][$value['subSubCommodity']][$value['country']]['commodityWise'][$value['subSubCommodity']][$value['id']] = array('assessment'=>ucfirst($value['tradeType']).' | '.$value['country'], 'dataAvailableFrom'=>$value['dataAvailableFrom'], 'dataAvailableTo'=>$value['dataAvailableTo'], 'lastUpdated'=>$value['lastUpdated'], 'isAddedONPF'=>$isAddedONPF);
						$communityArray['filter'][$value['commodity']][$value['subCommodity']][$value['subSubCommodity']][$value['country']]['regionWise'][$value['country']][$value['id']] = array('assessment'=>ucfirst($value['tradeType']).' | '.$value['country'], 'dataAvailableFrom'=>$value['dataAvailableFrom'], 'dataAvailableTo'=>$value['dataAvailableTo'], 'lastUpdated'=>$value['lastUpdated'], 'isAddedONPF'=>$isAddedONPF);
						$communityArray['commodityWise'][$value['subSubCommodity']][$value['country']][$value['id']] = array('assessment'=>ucfirst($value['tradeType']).' | '.$value['country'], 'dataAvailableFrom'=>$value['dataAvailableFrom'], 'dataAvailableTo'=>$value['dataAvailableTo'], 'lastUpdated'=>$value['lastUpdated'], 'isAddedONPF'=>$isAddedONPF);
						$communityArray['regionWise'][$value['subSubCommodity']][$value['region_name']][$value['country']][$value['id']] = array('assessment'=>ucfirst($value['tradeType']).' | '.$value['country'], 'dataAvailableFrom'=>$value['dataAvailableFrom'], 'dataAvailableTo'=>$value['dataAvailableTo'], 'lastUpdated'=>$value['lastUpdated'], 'isAddedONPF'=>$isAddedONPF);			
						$communityArray['countryWise'][$value['country']][$value['subSubCommodity']][$value['id']] = array('assessment'=>ucfirst($value['tradeType']).' | '.$value['subSubCommodity'], 'dataAvailableFrom'=>$value['dataAvailableFrom'], 'dataAvailableTo'=>$value['dataAvailableTo'], 'lastUpdated'=>$value['lastUpdated'], 'isAddedONPF'=>$isAddedONPF);
						$communityArray['regionWise_unique'][$value['commodity']."-".$value['subCommodity']."-".$value['subSubCommodity']][$value['region_name']][$value['country']][$value['id']] = array('assessment'=>ucfirst($value['tradeType']).' | '.$value['country'], 'dataAvailableFrom'=>$value['dataAvailableFrom'], 'dataAvailableTo'=>$value['dataAvailableTo'], 'lastUpdated'=>$value['lastUpdated'], 'isAddedONPF'=>$isAddedONPF);
						$communityArray['countryWise_new'][$value['country']][$value['commodity']."-".$value['subCommodity']."-".$value['subSubCommodity']][$value['id']] = array('assessment'=>ucfirst($value['tradeType']).' | '.$value['subSubCommodity'], 'dataAvailableFrom'=>$value['dataAvailableFrom'], 'dataAvailableTo'=>$value['dataAvailableTo'], 'lastUpdated'=>$value['lastUpdated'], 'isAddedONPF'=>$isAddedONPF);
						$communityArray['commodityWise_new'][$value['commodity']."-".$value['subCommodity']."-".$value['subSubCommodity']][$value['country']][$value['id']] = array('assessment'=>ucfirst($value['tradeType']).' | '.$value['country'], 'dataAvailableFrom'=>$value['dataAvailableFrom'], 'dataAvailableTo'=>$value['dataAvailableTo'], 'lastUpdated'=>$value['lastUpdated'], 'isAddedONPF'=>$isAddedONPF);
					}
					$regionArr[]=$value['region_name'];
				}
			}
			//DB::enableQueryLog(); 
			//->whereRaw("FIND_IN_SET('$selected_market', f_market)")
			$data = DB::table('tbl_region_commodity_master')
						 ->select('ID','itemName')
						  ->where('itemType','region')						  
						  ->whereIn('ID',array_unique($regionArr))
						  ->get();
			//dd(DB::getQueryLog());
			foreach($data as $d1):
				$communityArray['regionName'][$d1['ID']] = $d1['itemName'];
			endforeach;
			return $communityArray;
		}else{
			return 'false';
		}
	}
	
	//download price data 
	//18jan2022
	public function downloadPricedata($attr){		
		$resourceType = isset($attr['resourceType'])?$attr['resourceType']:'web';
		$assList = explode(',', trim($attr['checkedIdList']));

		$fromDates = trim($attr['fromDate']); 
		$date = str_replace('/', '-', $fromDates);
		$fromDate = date("Y-m-d", strtotime($date));

		$toDated = trim($attr['toDate']);
		$date = str_replace('/', '-', $toDated);
		$toDate = date("Y-m-d", strtotime($date));

		$appType = 'SM';
		if(isset($attr['filters']['app_type'])){
			$appType = $attr['filters']['app_type'];
		}
		$selected_market='';
		if(isset($attr['selected_market'])){
            $selected_market =  $attr['selected_market'];
        }
		$user_id = 0;
		$memid = 0;
		//update memId if getting in POST request
		if(isset($attr['memid']) && $attr['memid']>0){
		   $memid = trim($attr['memid']);
		}
		if ($resourceType=='app' && isset($attr['accessToken']) && $attr['accessToken']!=''){
			//$uemail = $request->input('email');//oldPassword
			$token = $attr['accessToken'];
			//$user = new User();
			$auth = new authentication();
			$user_id = $auth->authToken($token,$siteName);
			if(!$user_id)
				return 'Unauthorized';
		}elseif ($resourceType=='web' && isset($attr['memid']) && $attr['memid']!=''){
								
			$auth = new authentication(); 
			$user_id = $auth->authUserId($memid);//for web user
			if(!$user_id)
				return 'Unauthorized';
		}
		
		$user = new User($user_id, $appType);
		
		$isAccessible =  $user->isAccessible($resourceType, $section = 'Prices_Historical','','','',$selected_market);
		 
		if(!empty(Session::get($selected_market)['sectionFromDate'])){			
			$sectionFromDate = Session::get($selected_market)['sectionFromDate'];
			$sectionTodate = Session::get($selected_market)['sectionTodate'];	
			
			$fromDate = strtotime($fromDate) < strtotime($sectionFromDate) ?$sectionFromDate:$fromDate;
			$toDate = strtotime($toDate) > strtotime($sectionTodate) ?$sectionTodate:$toDate;
			
		}else{
			$fromDate =$GLOBALS['currDate'];  
			$toDate = $GLOBALS['currDate']; 
		}
		
		if(count($assList)>0){
			$responceArray = array();
		
			if( $appType != 'CM' ){
				$responceArray['websiteSrc'] = 'steelmint';
				$responceArray['source'] = 'STEELMINT';
			}
			else{
				$responceArray['websiteSrc'] = 'coalmint';
				$responceArray['source'] = 'COALMINT';
			}
			
			$responceArray['sourceFrom'] = 'newWebsite';
			$responceArray['uid'] = $memid;
			
			$responceArray['from_date'] =date('Y-m-d', strtotime($fromDate));
			$responceArray['to_date'] = date('Y-m-d', strtotime($toDate)); 
			$responceArray['assessment'] = $attr['assessment'];
			
			$responceArray['relation_id'] = $assList;
			$responceArray['selected_market'] = $selected_market;
			$_REQUEST = $responceArray;
			 
			if($memid>0){
					include("/home/steelmin/public_html/excel_download_bm/price_download_new.php");				
					$resValue['filePath'] = $file_path;
			}else{
				return 'Invalid User Session!';
			}			
			return $resValue;			
		}else{
			return 'No Data Found';
		}
	}
	
	//function to get dashboard selection list
    public function getHistoricalDashboard($attr){
		if(isset($attr['section'])){
			$dataType = $attr['section'];
			$communityArray=Array();

            ///DB::enableQueryLog(); 
            if(!empty($attr['filters']['app_type'])){
                if( $attr['filters']['app_type'] == 'CM' )
                    $siteFilter = "coalmint";
                else 
                    $siteFilter = "steelmint";
                
            }else{
                $siteFilter = "steelmint";
            }					
            $memid = 0;$portFolioIdArray=array();
            if(isset($attr['memid']) && $attr['memid']>0){
                $memid = trim($attr['memid']);
            }
            
            $data = DB::table('tbl_dashboard');                                
           // $data =	  $data->where('website_access', 'like', '%' . $siteFilter . '%');
            $data =	  $data->orderBy('id');
            $data = $data  ->get();
            ///dd(DB::getQueryLog());	
            $data = json_decode(json_encode($data), true);
             $isAddedONPF=0;
            if(count($data) > 0){                            
                foreach ($data as $key => $value) { 
                   /* $dataAvailableTo = '2022-11-01';
                    $dataAvailableFrom = '2020-11-01';
                    $lastUpdated = '2022-11-05';
                   
                    $value['dataAvailableTo'] = date('M Y', strtotime($dataAvailableTo));
                    $value['dataAvailableFrom'] = date('M Y', strtotime($dataAvailableFrom));*/
                    $value['dataAvailableTo'] ='';
                    $value['dataAvailableFrom'] ='';
                    $value['data_type'] = strtolower(trim($value['data_type']));
                    $value['lastUpdated'] = date('M Y', strtotime($value['created_on']));                    
                    {                        
                            $communityArray['filter'][$value['data_type']][$value['commodity']][$value['country']]['commodityWise'][$value['commodity']][$value['id']] = array('assessment'=>' Dashboard | '.$value['commodity'], 'dataAvailableFrom'=>$value['dataAvailableFrom'], 'dataAvailableTo'=>$value['dataAvailableTo'], 'lastUpdated'=>$value['lastUpdated'], 'isAddedONPF'=>$isAddedONPF);
                            
                            $communityArray['filter'][$value['data_type']][$value['commodity']][$value['country']]['regionWise'][$value['country']][$value['id']] = array('assessment'=>'Dashboard | '.$value['country'], 'dataAvailableFrom'=>$value['dataAvailableFrom'], 'dataAvailableTo'=>$value['dataAvailableTo'], 'lastUpdated'=>$value['lastUpdated'], 'isAddedONPF'=>$isAddedONPF);
                            
                          /*   $communityArray['commodityWise'][$value['commodity']][$value['country']][$value['id']] = array('assessment'=>' Dashboard | '.$value['country'], 'dataAvailableFrom'=>$value['dataAvailableFrom'], 'dataAvailableTo'=>$value['dataAvailableTo'], 'lastUpdated'=>$value['lastUpdated'], 'isAddedONPF'=>$isAddedONPF);
                            
                            $communityArray['regionWise'][$value['commodity']]["All"][$value['country']][$value['id']] = array('assessment'=>'Dashboard | '.$value['country'], 'dataAvailableFrom'=>$value['dataAvailableFrom'], 'dataAvailableTo'=>$value['dataAvailableTo'], 'lastUpdated'=>$value['lastUpdated'], 'isAddedONPF'=>$isAddedONPF);			
                            
                            $communityArray['countryWise'][$value['country']][$value['commodity']][$value['id']] = array('assessment'=>'Dashboard | '.$value['commodity'], 'dataAvailableFrom'=>$value['dataAvailableFrom'], 'dataAvailableTo'=>$value['dataAvailableTo'], 'lastUpdated'=>$value['lastUpdated'], 'isAddedONPF'=>$isAddedONPF); 
                            
                 
                       */   
                            $month='';
                            if($value['month']!=''){
                                $month = "(".$value['month'].")";
                            }
                            $communityArray['regionWise_unique'][$value['data_type']."-".$value['commodity']]["All"][$value['country']][$value['id']] = array('assessment'=>'Dashboard | '.ucfirst($value['country'])." ".$month, 'dataAvailableFrom'=>$value['dataAvailableFrom'], 'dataAvailableTo'=>$value['dataAvailableTo'], 'lastUpdated'=>$value['lastUpdated'], 'isAddedONPF'=>$isAddedONPF);
                            
     
                            
                            $communityArray['countryWise_new'][$value['country']][$value['data_type']."-".$value['commodity']][$value['id']] = array('assessment'=>ucfirst($value['data_type']).' | '.ucfirst($value['commodity']).' | '.ucfirst($value['country'])." ".$month, 'dataAvailableFrom'=>$value['dataAvailableFrom'], 'dataAvailableTo'=>$value['dataAvailableTo'], 'lastUpdated'=>$value['lastUpdated'], 'isAddedONPF'=>$isAddedONPF);
									
                            $communityArray['commodityWise_new'][$value['data_type']."-".$value['commodity']][$value['country']][$value['id']] = array('assessment'=>ucfirst($value['data_type']).' | '.ucfirst($value['commodity']).' | '.ucfirst($value['country'])." ".$month, 'dataAvailableFrom'=>$value['dataAvailableFrom'], 'dataAvailableTo'=>$value['dataAvailableTo'], 'lastUpdated'=>$value['lastUpdated'], 'isAddedONPF'=>$isAddedONPF);
                    }
                   // $regionArr[]=$value['region_name'];
                    ////$communityArray['region_name']
                }
            }
			//DB::enableQueryLog(); 
			/*$data = DB::table('tbl_region_commodity_master')
						 ->select('ID','itemName')
						  ->where('itemType','region')
						  ->whereIn('ID',array_unique($regionArr))
						  ->get();
			//dd(DB::getQueryLog());
			foreach($data as $d1):
				$communityArray['regionName'][$d1['ID']] = $d1['itemName'];
			endforeach;*/
			$communityArray['regionName']["All"] = "Regions";
			return $communityArray;
		}else{
			return 'false';
		}
	}
	
	
	//function to get dashboard data urls
    public function getDashboardUrlPath_func($attr){
		$requiredFor = ucfirst(trim($attr['requiredFor']));
		$ex = explode(',', trim($attr['checkedIdList']));

	
        if(!empty($attr['filters']['app_type'])){
            if( $attr['filters']['app_type'] == 'CM' )
                $siteFilter = "coalmint";
            else 
                $siteFilter = "steelmint";
            
        }else{
            $siteFilter = "steelmint";
        }

        $section='Dashboard_url';
        $memid = 0;
		//update memId if getting in POST request
		if(isset($attr['memid']) && $attr['memid']>0){
		   $memid = trim($attr['memid']);
		}

		$user = new User($memid, $appType);
		$isAccessible =  $user->isAccessible($resourceType='Web');
		$activePackageName =  session('userService');
		
		if( stripos($activePackageName, 'Platinum+')!==false){
            $field = "file_path_platinum_plus";
		}elseif( stripos($activePackageName, 'Platinum')!==false){
             $field = "file_path_platinum";
		}else{
             $field = "file_path_sample";
		}
        $tblName ='tbl_dashboard';
		$dataFromParent = DB::table($tblName)->select($field)->whereIn('id',$ex)->get();
		//$dataFromParent = DB::table($tblName)->where('website_access', 'like', '%' . $siteFilter . '%')->whereIn('id',$ex)->get();
		//if( $appType == 'CM' )	
			//dd(DB::getQueryLog());	
			//print_r($dataFromParent);
		
		if(count($dataFromParent)>0){			
			$dataFromParent = json_decode(json_encode($dataFromParent), true);
			$responceArray = array();
			$file_path = $dataFromParent[0][$field];
		/*	echo "<pre>"; 
			print_r($dataFromParent);die;	*/		 
			if($memid>0){
				$resValue['filePath'] = $file_path;
			}else{
				return 'Invalid User Session!';
			}			
			return $resValue;		
		}else{
			return 'No Data Found.';
		}
    
	}
}
