<?php
/**
 * Created by PhpStorm.
 * User: varun_2
 * Date: 30-12-2015
 * Time: 17:03
 */

namespace App\Http\Controllers\Action;
use DB;

class Common
{
	private $category_abbreviations = [
	    "CL"    =>  "COAL",
	    "FA"    =>  "FERRO ALLOY",
	    "FF"    =>  "FINISH FLAT",
	    "FL"    =>  "FINISH LONG",
	    "FS"    =>  "FERROUS SCRAP",
	    "FS2"   =>  "FINISH STEEL",
	    "IN"    =>  "INGOT",
	    "IO"    =>  "IRON ORE",
	    "L"     =>  "LOGISTICS",
	    "RM"    =>  "RAW MATERIAL",
	    "SB"    =>  "SHIP BREAKING",
	    "SF"    =>  "SEMI FINISH"
	];

	public function getCategories()
	{
		$results = array();
		$categories = array();
		$category_id = 1;

		$sql = "SELECT DISTINCT(`category`) as name, `item` as sub_category, 1 as `prices`, 0 as `news`, 0 as `tenders` FROM `tbl_prices_index_master` WHERE `category` <> '' AND `category` <> 'index' AND `tabs` <> '' AND `page` = 'mobileapp' AND view_all_spinner_ordering <> 0";
		$sql .= " UNION";
		$sql .= " SELECT DISTINCT(`category`) as name, `sub_category`, 0 as `prices`, 1 as `news`, 0 as `tenders` FROM `tbl_news_post` WHERE `category` <> '' AND `category` <> 'index'";
		$sql .= " UNION";
		$sql .= " SELECT DISTINCT(`category`) as name, `sub_category`, 0 as `prices`, 0 as `news`, 1 as `tenders` FROM `tbl_tenders` WHERE `category` <> '' AND `category` <> 'index'";

		$sql .= " ORDER BY CASE name";
		$sql .= " WHEN 'Raw Material' THEN 1";
		$sql .= " WHEN 'Iron Ore' THEN 2";
		$sql .= " WHEN 'Coal' THEN 3";
		$sql .= " WHEN 'Ferrous Scrap' THEN 4";
		$sql .= " WHEN 'Scrap' THEN 5";
		$sql .= " WHEN 'Ship Breaking' THEN 6";
		$sql .= " WHEN 'Semi Finish' THEN 7";
		$sql .= " WHEN 'Semi Finished' THEN 8";
		$sql .= " WHEN 'Finish Long' THEN 9";
		$sql .= " WHEN 'Finish Flat' THEN 10";
		$sql .= " WHEN 'Ferro Alloy' THEN 11";
		$sql .= " WHEN 'Logistics' THEN 12";
		$sql .= " ELSE 99";
		$sql .= " END";

		$rows = DB::select($sql);

		if($rows) {
			foreach($rows as $row) {
				$category = $row['name'];
		        $category_name = isset($this->category_abbreviations[strtoupper($category)]) ? ucwords(strtolower($this->category_abbreviations[strtoupper($category)])) : ucwords(strtolower($category));
		        $category_key = strtolower(preg_replace('/[^a-zA-Z]+/', '-', $category_name));
		        if(!isset($categories[$category_key])) {
		            $categories[$category_key]['cat_id'] = $category_id;
		            $categories[$category_key]['cat_name'] = $category_name;
		            $categories[$category_key]['sub_cat'] = [];
		            $category_id++;
		        }

		        if(!isset($categories[$category_key]['category_inclusion'])) {
		            $categories[$category_key]['category_inclusion'] = array();
		        }

		        if($row['prices']) {
		        	/*$trade_type = $row['trade_type'];

		        	if($row['sub_category'] == 'HRC' || $row['sub_category'] == 'CRC' || $row['sub_category'] == 'GP') {
						$sub_category = $row['sub_category'];
		        	} else if($row['sub_category'] == 'HR PLATE') {
						$sub_category = "HR Plate";
					} else if($row['sub_category'] == 'TMT') {
						$sub_category = "Rebar";
					} else {
						$sub_category = ucwords(strtolower($row['sub_category']));
					}

					if(!isset($tempCategories[$category]['sub_cat'][$sub_category])) {
	                    $categories[$category_key]['sub_cat'][$sub_category] = array();
	                }

		            $tabs = $row['tabs'];
	                if(strlen(preg_replace('/\s+/', '', $tabs)) > 0) {
	                    $is_numeric = is_numeric(preg_replace('/[^a-zA-Z0-9\']/', '', $tabs));
	                    
	                    if(!$is_numeric) {
	                        $tabs = explode(",", trim($tabs));
	                        $tabs = array_unique($tabs);
	                        if(strtoupper($sub_category) == 'LOGISTICS') {
	                            rsort($tabs);
	                        } else {
	                            sort($tabs);
	                        }
	                    }

	                    $categories[$category_key]['sub_cat'][$sub_category][$trade_type]['tabs'] = $tabs;
	                }*/

	                $categories[$category_key]['category_inclusion']['prices'] = 1;
		        }
		        if($row['news']) {
		            $categories[$category_key]['category_inclusion']['news'] = 1;
		        }
		        if($row['tenders']) {
		            $categories[$category_key]['category_inclusion']['tenders'] = 1;
		        }
			}
		}

		$results['categories'] = $categories;

		return $results;
	}

    public function getUserPortfolio($user_id, $type, $appType='SM')
	{
		$temp = DB::table('tbl_user_portfolio')
            ->select('type','category', 'country', 'lead_type','row_id','prices_col')
			->where('user_id', '=' , $user_id)
			->where('type', '=' , $type)
			->where('appType', '=' , $appType)
            ->get();
		/* return $temp ;  */
		$res=array();
		if($type!='prices'){	
			foreach( $temp as $tm)
			{
				$res['category'] = trim($tm['category'],',');
				$res['country'] = str_replace(',',';',trim($tm['country'],','));
				$res['lead_type'] = trim($tm['lead_type'],',');
			}
		}		
		
		if($type=='prices'){	
			foreach( $temp as $tm)
			{
				if(!in_array($tm['row_id'], $res[strtolower($tm['prices_col'])]) && $tm['row_id']!='')
					$res[strtolower($tm['prices_col'])][] = $tm['row_id']; 
			}
		}
		return $res ;
	}
	
    public function getCountryState($country_id)
    {
    	if($country_id){
       		$country_list = DB::connection('mysql')
            ->select(DB::raw('SELECT  `city_id`, `city_name`, state_id, state_name, country_id,  country FROM tbl_state_city state INNER JOIN tbl_country country ON state.`country_id` = country.id Where country_id ="'.$country_id.'"')) ;
		}else{
			  $country_list = DB::connection('mysql')
            ->select(DB::raw('SELECT  `city_id`, `city_name`, state_id, state_name, country_id,  country FROM tbl_state_city state INNER JOIN tbl_country country ON state.`country_id` = country.id ORDER BY `state`.`country_id` ASC')) ;
		
		}	
        $list = array();
        foreach($country_list as $country )
        {
            if(empty( $list[$country['country_id']] ))
                $list[$country['country_id']] = array("name" => $country["country"] , "id" => strval($country['country_id']) ) ;
            if(empty($list[$country['country_id']]['state'][$country["state_id"]]))
                $list[$country['country_id']]['state'][$country["state_id"]] = array( 'name' => $country["state_name"], 'id' => strval($country["state_id"])) ;

            $list[$country['country_id']]['state'][$country["state_id"]]['city'][] = array( 'name' => $country["city_name"], 'id' => strval( $country["city_id"]) ) ;

        }

//        $list = array_values ($list) ;
//        for($i = 0 ; $i< count($list) ; $i++ )
//            $list[$i]["state"] = array_values ($list[$i]["state"]) ;



        return $list ;
    }
	
	public function getNewCategories($filter_type='')
    {
		if($filter_type=='news_arr'){
			return $finalCategoryArr = array( 
										"Iron Ore"=> "Raw Material",
										"Coal"=>"Raw Material",										 
										"Ferrous Scrap"=>"Raw Material",										 
										"Ship Breaking"=>"Raw Material",										 
										"Semi Finished"=>"Semi Finished",									 
										"Finish Long"=>"Finish Long",
										"Finish Flat"=>"Finish Flat",
										"Ferro Alloy"=>"Ferro Alloy"
										/* "Iron Ore"=> "13,14,15",
										"Coal"=>"16",										 
										"Ferrous Scrap"=>"17",										 
										"Ship Breaking"=>"114",										 
										"Semi Finish"=>"18,19,20,21",									 
										"Finish Long"=>"4,25,26,2427",
										"Finish Flat"=>"24",
										"Ferro Alloy"=>"22,23,3659,3660,3661,3662" */
									);
		}elseif($filter_type=='tenders'){
		
			$finalCategoryArr = array( 
										array("Iron Ore"=> "Raw Material"),
										array("Coal"=>"Raw Material"),										 
										array("Ferrous Scrap"=>"Raw Material"),										 
										array("Ship Breaking"=>"Raw Material"),										 
										array("Semi Finished"=>"Semi Finished"),									 
										array("Finish Long"=>"Finish Long"),
										array("Finish Flat"=>"Finish Flat"),
										array("Ferro Alloy"=>"Ferro Alloy")
									);
			
			
			return array('filter' => "category", "sub_filter" => $finalCategoryArr);
			//return $finalCategoryArr;
		}elseif($filter_type=='news'){
		
			$finalCategoryArr = array( 
										array("Iron Ore"=> "13,14,15"),
										array("Coal"=>"16"),										 
										array("Ferrous Scrap"=>"17"),										 
										array("Ship Breaking"=>"114"),										 
										array("Semi Finish"=>"18,19,20,21"),									 
										array("Finish Long"=>"4,25,26,2427"),
										array("Finish Flat"=>"24"),
										array("Ferro Alloy"=>"22,23,3659,3660,3661,3662")
									);
			
			
			//return array('filter' => "category", "sub_filter" => $finalCategoryArr);
			return $finalCategoryArr;
		}else{
		
			return array(
				"categories" => array(
					array(
						"cat_id"	=>	"13,14,15",
						"cat_name"	=>	"Iron Ore",
						"sub_cat"	=>	array(
							array(
								"cat_id"	=>	"13,14",
								"cat_name"	=>	"Fines/Lumps"
							),
							array(
								"cat_id"	=>	"15",
								"cat_name"	=>	"Pellets"
							)
						)
					),
					array(
						"cat_id"	=>	"16",
						"cat_name"	=>	"Coal",
						"sub_cat"	=>	array(
							array(
								"cat_id"	=>	"16",
								"cat_name"	=>	"Non Coking"
							),
							array(
								"cat_id"	=>	"16",
								"cat_name"	=>	"Coking"
							),
							array(
								"cat_id"	=>	"16",
								"cat_name"	=>	"Met Coke"
							),
							array(
								"cat_id"	=>	"16",
								"cat_name"	=>	"Anthracite"
							)
						)
					),
					array(
						"cat_id"	=>	"17",
						"cat_name"	=>	"Ferrous Scrap",
						"sub_cat"	=>	array(
							array(
								"cat_id"	=>	"17",
								"cat_name"	=>	"Melting Scrap"
							),
							array(
								"cat_id"	=>	"17",
								"cat_name"	=>	"Mill Scale"
							)
						)
					),
					array(
						"cat_id"	=>	"114",
						"cat_name"	=>	"Ship Breaking",
						"sub_cat"	=>	array()
					),
					array(
						"cat_id"	=>	"18,19,20,21",
						"cat_name"	=>	"Semi Finish",
						"sub_cat"	=>	array(
							array(
								"cat_id"	=>	"21",
								"cat_name"	=>	"Sponge Iron"
							),
							array(
								"cat_id"	=>	"20",
								"cat_name"	=>	"Pig Iron"
							),
							array(
								"cat_id"	=>	"18,19",
								"cat_name"	=>	"Billet/Ingot"
							)
						)
					),
					array(
						"cat_id"	=>	"4,25,26,2427",
						"cat_name"	=>	"Finish Long",
						"sub_cat"	=>	array(
							array(
								"cat_id"	=>	"25",
								"cat_name"	=>	"Re bar"
							),
							array(
								"cat_id"	=>	"26",
								"cat_name"	=>	"Wire rod"
							),
							array(
								"cat_id"	=>	"4",
								"cat_name"	=>	"Structures"
							),
							array(
								"cat_id"	=>	"2427",
								"cat_name"	=>	"Pipes"
							)
						)
					),
					array(
						"cat_id"	=>	"793,794,3663",
						"cat_name"	=>	"Finish Flat",
						"sub_cat"	=>	array(
							array(
								"cat_id"	=>	"793,794",
								"cat_name"	=>	"HRC/CRC"
							),
							array(
								"cat_id"	=>	"3663",
								"cat_name"	=>	"Plates"
							),
							array(
								"cat_id"	=>	"3663",
								"cat_name"	=>	"GP"
							)
						)
					),
					array(
						"cat_id"	=>	"22,23,3659,3660,3661,3662",
						"cat_name"	=>	"Ferro Alloy",
						"sub_cat"	=>	array(
							array(
								"cat_id"	=>	"3659",
								"cat_name"	=>	"Silico Manganese"
							),
							array(
								"cat_id"	=>	"3660",
								"cat_name"	=>	"Ferro Manganese"
							),
							array(
								"cat_id"	=>	"3662",
								"cat_name"	=>	"Ferro Chrome"
							),
							array(
								"cat_id"	=>	"3661",
								"cat_name"	=>	"Ferro Silicon"
							),
							array(
								"cat_id"	=>	"23",
								"cat_name"	=>	"Manganese Ore"
							),
							array(
								"cat_id"	=>	"22",
								"cat_name"	=>	"Chrome Ore"
							)
						)
					)
				)
			);
		}
    	
    }
	
	public function getRegions($filter_type='')
	{
		
		if($filter_type=='news'){
			
			$finalCountryArr = array( 
										
										array("Asia & Asia Pacific"=> "India,China,Japan,Korea,Pakistan, Bangladesh, Nepal,Australia",
											"sub_filter" =>array(
													'India'=>'India',
													'China'=>'China',
													'Japan,Korea'=>'Japan,Korea',
													'Pakistan, Bangladesh, Nepal'=>'Pakistan, Bangladesh, Nepal',
													'Australia'=>'Australia',
												)
											),
										array("South East Asia"=>"South East Asia"),										 
										array("Middle East Africa"=>"Middle East Africa"),										 
										array("CIS Nations"=>"CIS Nations"),										 
										array("Europe"=>"Europe"),									 
										array("America"=> "US/Canada,Brazil",
											"sub_filter" =>array(
													'US/Canada'=>'US/Canada',
													'Brazil'=>'Brazil'
												)
											),
										array("Global"=>"Global")
									);
			return array('filter' => "country", "sub_filter" => $finalCountryArr);
		}elseif($filter_type=='tenders'){
			
			$finalCountryArr = array( 
										
										array("filter"=> "Asia & Asia Pacific",
											"sub_filter" =>array(
													array('filter'=>'India'),
													array('filter'=>'China'),
													array('filter'=>'Japan,Korea'),
													array('filter'=>'Pakistan, Bangladesh, Nepal'),
													array('filter'=>'Australia'),
												)
											),
										array("filter"=>"South East Asia"),										 
										array("filter"=>"Middle East Africa"),										 
										array("filter"=>"CIS Nations"),										 
										array("filter"=>"Europe"),									 
										array("filter"=> "US/Canada,Brazil",
											"sub_filter" =>array(
													array('filter'=>'US/Canada'),
													array('filter'=>'Brazil')
												)
											),
										array("filter"=>"Global")
									);
			return array('filter' => "regions", "sub_filter" => $finalCountryArr);
		}else{
			return $countryArr = array(
				'Asia & Asia Pacific'	=>	array(
					'India'=>'India',
					'China'=>'China',
					'Japan, Korea'=>'Japan, Korea',
					'Pakistan, Bangladesh, Nepal'=>'Pakistan, Bangladesh, Nepal',
					'Australia'=>'Australia'
				),
				'South East Asia'=>'South East Asia',
				'Middle East Africa'=>'Middle East Africa',
				'CIS Nations'=>'CIS Nations',
				'Europe'=>'Europe',
				'America'	=>	array(
					'US/Canada'=>'US/Canada',
					'Brazil'=>'Brazil'
				),
				'Global'=>'Global'
			);
		}
	}
}