<?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 getUserPortfolioV2019($user_id, $type, $appType='SM')
	{
		$temp = DB::table('tbl_user_portfolio')
           // ->select('type','category', 'country', 'lead_type','row_id','prices_col')
            ->select('*')
			->where('user_id', '=' , $user_id)
			->where('type', '=' , $type)
			///->where('appType', '=' , $appType)
            ->get();
            //->toSql();//	dd($temp);
			//DB::enableQueryLog(); 
			//dd(DB::getQueryLog());
		
		/* return $temp ;  */
		
		$res=array();
		if($type=='statistics'){	
			foreach( $temp as $tm)
			{
				$a['commodityId'] = trim($tm['commodityId'],',');				
				$a['regionId'] = trim($tm['regionId'],',');
				$a['dataType'] = trim($tm['dataType'],',');
				$a['headerType'] = trim($tm['headerType'],',');
				$res[$tm['id']] = $a;
			}
		}elseif($type=='intel_insight'){
			foreach( $temp as $tm)
			{
				$a['commodityId'] = trim($tm['commodityId'],',');				
				$a['regionId'] = trim($tm['regionId'],',');
				$res[$tm['id']] = $a;
			}
		}elseif($type!='prices' && $type!='price'){	
			/*foreach( $temp as $tm)
			{
				$res['category'] = trim($tm['category'],',');
				$res['country'] = str_replace(',',';',trim($tm['country'],','));
				$res['lead_type'] = trim($tm['lead_type'],',');
			}*/
			foreach( $temp as $tm)
			{
				$a['commodityId'] = trim($tm['commodityId'],',');				
				$a['regionId'] = trim($tm['regionId'],',');
				$res[$tm['id']] = $a;
			}
		} 		
		
		if($type=='prices' || $type=='price'){	
			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']; 
					///for all childs
					/* $CparenIDSQry = qryToCommaSepString("select relation_id from tbl_price_static where parentID = ".$tm['row_id']."");
					if($CparenIDSQry){
						$_cIDS = explode(',', $CparenIDSQry);
						foreach($_cIDS as $_cID){
							$res[strtolower($tm['prices_col'])][] = $_cID;
							///for all sub childs
							$CCparenIDSQry = qryToCommaSepString("select relation_id from tbl_price_static where parentID = ".$_cID."");
							if($CCparenIDSQry){
								$_ccIDS = explode(',', $CCparenIDSQry);
								foreach($_ccIDS as $_csID){
									$res[strtolower($tm['prices_col'])][] = $_csID;
								}
							}
						}
					} */
				}
			}
		}
		return $res ;
	}
	
    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 getUserBookmarksV2019($resourceType='app', $user_id, $section, $status='', $event_id=0, $offset=0, $filter=array(), $appType='SM'){
		// DB::enableQueryLog(); 
			//
			if(strtolower($section) == 'insights')
				$section = 'insight';
		 $bookmarkItemIds = DB::table('tbl_bookmarks')
			->select('item_id')
            ->where('section', 'like', $section . '%')
            ->where('memid', $user_id)
            ->where('event_id', $event_id)
            //->where('resourceType', $resourceType)
            ->where('status', 'active');
        
        //we want to show only 2 reports on new dashboard of steelmint.
        //so, we are selecting only 2 records
        if(strtolower($section) == 'report' && isset( $filter['renderAt'] ) && $filter['renderAt'] == 'new_dashboard'){ 
            $bookmarkItemIds =  $bookmarkItemIds ->orderBy('id', 'desc')
                                                ->offset($offset)
                                                 ->limit(2);            
        }else{
			$bookmarkItemIds =  $bookmarkItemIds ->orderBy('id', 'desc')
                                                ->offset($offset)
                                                 ->limit(15);  
		}
		
		
        
        $bookmarkItemIds =  $bookmarkItemIds -> get();
            
            
		//	dd(DB::getQueryLog());
        $val = array();    
		
        foreach($bookmarkItemIds as $v ){
           $val[] = $v['item_id'];
        } 
		
		return $val ;
	}
	
	public function getUserAlertsV2019($resourceType='app', $user_id, $section, $status='active', $event_id=0)
	{
		if($event_id){
			$temp = DB::table('tbl_alerts')
				->select('*')
				->where('memid', '=' , $user_id)
				->where('section', '=' , $section)
				->where('event_id', $event_id)
				//->where('resourceType', '=' ,$resourceType)
				//->where('status', $status)
				->get();
		}else{
			$temp = DB::table('tbl_alerts')
           // ->select('type','category', 'country', 'lead_type','row_id','prices_col')
            ->select('*')
			->where('memid', '=' , $user_id)
			->where('section', '=' , $section)
			//->where('resourceType', '=' , $resourceType)
			//->where('status', $status)
			->get();
		}
		/* if($event_id)
			$temp->where('event_id', $event_id);
		if($status!='')
			$temp->where('status', $status); */
           
		   //$temp->get();
		// print_r($temp);
            //->toSql();//	dd($temp);
			//DB::enableQueryLog(); 
			//dd(DB::getQueryLog());
		
		/* return $temp ;  */
		
		$res=array();
		if($section=='tender'){	
			foreach( $temp as $tm)
			{
				$res[$tm['item_id']] = $tm['x_days_before']; 
			}
		}
		
		if($section=='price'){	
			foreach( $temp as $tm)
			{
				if(!in_array($tm['item_id'], $res[strtolower($tm['x_days_before'])]) && $tm['item_id']!='')
					$res[strtolower($tm['x_days_before'])][] = $tm['item_id']; 
			}
		}
		return $res ;
	}
	
	///slider master
	public function getSliderDetails($slider_id=0)
	{
		$tempSlider = DB::table('tbl_slider_master')
            ->select('commodityID','regionID', 'assessmentID')
			->where('sliderID', '=' , $slider_id)
            ->first();
		/* return $temp ;  */
		
		return $tempSlider ;
	}
	
    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'
			);
		}
	}
	
	public function getPackagesDetail($packageID=0, $memid=0, $status='Active')
	{
		$result=array();
		if(!empty($packageID)){
			$isEnabled = 'yes';
			$isEnabledArr=array();
			if($memid){
			//both memid, packageID are present
			
			
				$r = DB::table('tbl_reg_logs')
					->select('selected_products')
					->where('memid' , '=' , $memid)
					->where('status', '=', $status )
					->whereIn('selected_products', $packageID )
					->orderBy('id', 'desc')
					->get();
				
				/* if( count($r) > 0  )
					$isEnabled = 'yes';
				else
					$isEnabled = 'no'; */
				foreach($r as $log){
					$isEnabledArr[] = $log['selected_products'];
				}
			}
			
			//print_r($isEnabledArr);
			
			$rowSet = DB::table('tbl_packages')
					->whereIn('id', $packageID )
					->get();
			
			foreach($rowSet as $row){
				$result[] = array(
					"packageID"=>$row['id'],
					"IsDefault"=>$row['IsDefault'],
					"code"=>$row['currency_code'] ,
					'billedText' => $row['billed_text'],
					'amountPerMonth' => $row['amountPerMonth'],
					'totalAmountWoGST' => $row['totalAmount_Wo_GST'],
					'totalAmountWithGST' => $row['totalAmount_With_GST'],
					'currencySymbol' => $row['currency_symbol'],
					'scratchValue' => $row['scratch_value'],
					'actualPrice' => $row['actual_price'],
					'discountPercentage' => $row['discount_percentage'],
					'discountValue' => $row['discount_value'],
					'gstPercentage' => $row['gst_percentage'],
					'gstValue' => $row['gst_value'],	
					'isEnabled' => in_array($row['id'], $isEnabledArr)?'y':"n"
				);
			}
					
		}
		
		return $result;
	}
	
	public function getCountriesByRegionID($IDs=array())
	{
		$regionArr = array();
		if(!empty($IDs)){
			$parentID_cond = DB::select("SELECT parentID,grandParentID FROM `tbl_region_commodity_master` as m where itemType='region' AND m.ID in (".implode(',', array_unique($IDs)).") ORDER BY `itemName` ASC ");	
				
			foreach($parentID_cond as $d1){				
				if($d1['parentID']>0)
					$regionArr[] = $d1['parentID'];
				if($d1['grandParentID']>0)
					$regionArr[] = $d1['grandParentID'];
			}
		}
		
		return $regionArr;
	}
	
	public function getVisitorCountPerWeek($section='insight', $resourceType='app', $appType='SM', $memid=0, $visitor_history_id=0, &$visitedItem)
	{
	   // DB::enableQueryLog();
/* 		$result1 = DB::table('tbl_year_week')
				->select(DB::raw("concat(week, '-',year) as `weekNo`"))
				->where('sunday', '>=', date("Y-m-d", strtotime($GLOBALS['currDate'])))
				->where('monday', '<=', date("Y-m-d", strtotime($GLOBALS['currDate'])))
				//->orderBy('monday' , 'desc')
				->first(); */
	//	dd(DB::getQueryLog()); 
/* 		if(count($result1)>0)
			$weekNo = $result1['weekNo']; */
        
        $sqlTC = "select distinct item_id from tbl_recently_viewd 
			where created_on >= '".$GLOBALS['currDate']." 00:00:00'
            and created_on <= '".$GLOBALS['currDate']." 23:59:59'
			and resource_type='$resourceType' 
			and section='$section' 
			
			"; ///and appType='".$appType."'
				
	/*	$sqlTC = "select distinct item_id from tbl_recently_viewd 
			where created_on like '" . $GLOBALS['currDate'] . "%' 
			and resource_type='$resourceType' 
			and section='$section' 
			and appType='".$appType."'
			"; */
			if($memid)
				$sqlTC .= " AND memid=".$memid." ";
			elseif($visitor_history_id)
				$sqlTC .= " AND visitor_history_id=".$visitor_history_id." ";
		//if($memid==78616)echo $sqlTC;		
		$dbRSet = DB::select($sqlTC);	
		
		
		//$visitItem =array();
		/* if(count($dbRSet)>0){
			foreach($dbRSet as $row){
				$visitedItem[]= $row['item_id'];
			}
		} */
		
		return count($dbRSet);
	}
	
	public function getSearchTermFilterCond($searchTerm, $STSection='price'){
		
		$c_phrase_server = "http://0.0.0.0:8947";
		$utterance = trim($searchTerm);
			
		$resultST = json_decode(file_get_contents($c_phrase_server."/api/slot-filler?session=0&question=".urlencode($utterance)), true );
			
		if($STSection=='price'){	
			$isDescription=false;
			foreach($resultST['interpretations'] as $key=>$res){
				if($focusTab=='')
					$focusTab = $res['focus'];
				foreach($res['slots'] as $slot){
					if(isset($sectionST[$res['focus']][$key][$slot['slot']]) && !in_array($slot['value'],$sectionST[$res['focus']][$key][$slot['slot']]) )
						$sectionST[$res['focus']][$key][$slot['slot']][] = $slot['value'];
					elseif(!isset($sectionST[$res['focus']][$slot['slot']]))
						$sectionST[$res['focus']][$key][$slot['slot']][] = $slot['value'];
					
					if(strpos($slot['slot'], "description") !== false){
						$isDescription=true;
					}
				}
			}
			
			$innerFilterCondFinal['price'] = array();
			if(isset($sectionST['price']) && $isDescription==false){					
				
				include_once "/home/steelmin/public_html/api/storage/v2019/Json/Web/searchTermsTableColumns.php";
				//echo "<pre>" , print_r($sectionST), print_r($tres),"</pre>" ;
				
				foreach($sectionST as $sectionName=>$STT){
				if($sectionName=='price'){
					//$innerFilterCond[$sectionName] = "";
					$innerFilterCond[$sectionName]=array();
					 foreach($STT as $kPos=>$ST){
						$tableCount=array();
						foreach($ST as $keyType=>$ext){
							if(isset($tres[$keyType])){
								$tableNames = $tres[$keyType]['tableNames'];
								foreach($tableNames as $tableName){
									if(!in_array($tableName, $tableCount))
										$tableCount[$tableName][] = $tableName;
								}
							}
						}
						
						foreach($ST as $keyType=>$ext){
							if(isset($tres[$keyType])){
								$tableNames = $tres[$keyType]['tableNames'];
								$columnName = $tres[$keyType]['columnName'];
								foreach($tableNames as $tableName){
									//match all cases in any table will be consider 
									if(count($tableCount[$tableName]) == count($ST)){
										if($keyType=='trade_type'){
											if(implode(',',$ext)=='local' || implode(',',$ext)=='domestic')
												$param['filters']['tradeType'] = 'domestic';
											else
												$param['filters']['tradeType'] = implode(',',$ext);//import/export						
										}else{
											if(isset($innerFilterCond[$sectionName][$kPos][$tableName]))
												$innerFilterCond[$sectionName][$kPos][$tableName] .= " AND ";
											else
												$innerFilterCond[$sectionName][$kPos][$tableName]='';
											$val = "'". implode("','",$ext)."'";
											$val = strtr($val, array("['"=>'',"']"=>''));
											$innerFilterCond[$sectionName][$kPos][$tableName] .= " m.$columnName in ($val) ";
										}
									}
								}
							}
						}
					 }
					if(isset($param['filters']['tradeType'])){
						if($param['filters']['tradeType']=='domestic')
							unset($innerFilterCond['price'][0]['tbl_exim_master']);
						else{
							unset($innerFilterCond['price'][0]['tbl_global_dom_master']);
							unset($innerFilterCond['price'][0]['tbl_dom_master']);
						}
					}
					///$innerFilterCond['price'][0]['tbl_exim_master'] = "m.commodty_name in ('Iron Ore')  AND  m.load_country in ('China') ";
					
					///when multiple results of a section
					foreach($innerFilterCond['price'] as $pos=>$tblCond){
						foreach($tblCond as $tbl=>$tblCond){
							if(isset($innerFilterCondFinal['price'][$tbl]))
								$innerFilterCondFinal['price'][$tbl] .= " OR ";
							else
								$innerFilterCondFinal['price'][$tbl] ="";
							$innerFilterCondFinal['price'][$tbl] .= " (". $tblCond.") ";
						}
					}
				}
				}			 
			}
		
			return $innerFilterCondFinal;
		}else{//insight or tender			
			$itemCommodity=array();
			$itemRegion=array();
			foreach($resultST['interpretations'] as $key=>$res){
				if($focusTab=='')
					$focusTab = $res['focus'];
				foreach($res['slots'] as $slot){
					if(isset($sectionST[$res['focus']][$key][$slot['slot']]) && !in_array($slot['value'],$sectionST[$res['focus']][$key][$slot['slot']]) )
						$sectionST[$res['focus']][$key][$slot['slot']][] = $slot['value'];
					elseif(!isset($sectionST[$res['focus']][$slot['slot']]))
						$sectionST[$res['focus']][$key][$slot['slot']][] = $slot['value'];
					
					if(strpos($slot['slot'], "description") !== false){
						$isDescription=true;
					}
					
					if(strpos($slot['slot'], "commodity") !== false){
						if(strpos($slot['value'], "[") !== false)
							$itemCommodity = explode(',',strtr($slot['value'], array('['=>'',']'=>'',"'"=>'')));
						else
							$itemCommodity[] = strtr($slot['value'], array('['=>'',']'=>''));
						//$itemCommodity[] = $slot['value'];
					}
					if(strpos($slot['slot'], "country") !== false || strpos($slot['slot'], "world_region") !== false){
						//$itemRegion[] = $slot['value'];
						if(strpos($slot['value'], "[") !== false)
							$itemRegion = explode(',',strtr($slot['value'], array('['=>'',']'=>'',"'"=>'')));
						else
							$itemRegion[] = strtr($slot['value'], array('['=>'',']'=>''));
					}
					
				}
			}
			
			if(isset($sectionST['insight']) || isset($sectionST['tender'])){
				if($isDescription){
					return '';
				}
				
				if(!empty($itemCommodity)){
					$rcmID = DB::table('tbl_region_commodity_master')
						->select(DB::raw("GROUP_CONCAT(distinct ID) as `ids`"))	
						->where('itemType', '=', 'commodity')
						->whereIN('itemName', $itemCommodity)
						->first();
					//print_r($rcmID);
					$param['commodity'] = $rcmID['ids'];
					$param['selectComm'] = implode(',',array_unique($itemCommodity));
					
					$param['searchTerm']='';
				}

				if(!empty($itemRegion)){
					$rcmID = DB::table('tbl_region_commodity_master')
						->select(DB::raw("GROUP_CONCAT(distinct ID) as `ids`"))	
						->where('itemType', '=', 'region')
						->whereIN('itemName', $itemRegion)
						->first();
					//print_r($rcmID);
					$param['location'] = $rcmID['ids'];
					$param['selectRegion'] = implode(',',array_unique($itemRegion));
					
					$param['searchTerm']='';
				}
				
				///echo "<pre>",print_r($param),"<pre>";
			}
			
			if(isset($sectionST['tender']) && $STSection=='tender'){
					
				foreach($sectionST['tender'] as $kPos=>$ST){
					if($innerFilterCondFinal['tender'])
						$innerFilterCondFinal['tender'].=" OR ";
					///$innerFilterCondFinal['tender'].=" ( ";
					$tenderFiltr='';
					foreach($ST as $keyType=>$ext){
						//echo '<br>',$kPos,'-->',$keyType,'-->', print_r($ext),'</pre>';
						$val = "'". implode("','",$ext)."'";
						if($tenderFiltr)
							$tenderFiltr.=" AND ";
						if($keyType=='commodity')
							$tenderFiltr.=" n.commodityID "." in (".$param['commodity'].") ";
						elseif($keyType=='sub_commodity')
							$tenderFiltr.=" n.subCommodityID "." in (".$param['commodity'].") ";
						elseif($keyType=='sub_sub_commodity')
							$tenderFiltr.=" n.subSubCommodityID "." in (".$param['commodity'].") ";
						elseif($keyType=='country')
							$tenderFiltr.=" sc.`country_id` "." in (".$param['location'].") ";
						else
							$tenderFiltr.=" n.$keyType in ($val) ";
					}
					$innerFilterCondFinal['tender'].=" (".$tenderFiltr.") ";
				}
				return $innerFilterCondFinal['tender'];
			}else{
				if( isset($sectionST['insight']) ){
					return $param;
				} else {
					$fields = array();				
					return $fields;
				}
			}	
				
		}			
	}
	
	public function getBookmarked($memid,$p_array, $withCommodity=false){
        $result = DB::select("select section, group_concat(item_id) as `idm` from tbl_bookmarks where section IN ('insight','tender','insights') and memid =".$memid."  group by section");
        $result_price = DB::select("select group_concat(concat(`row_id`,'-',`prices_col`)) as `idm` from tbl_user_portfolio where user_id = ".$memid." and type='prices' and row_id <> 0 ");
        $result_tender_alert = DB::select("select group_concat(item_id) as `idm` from tbl_alerts where section IN ('tender') and memid ='".$memid."' and status='active' ");
		for( $i = 0; $i < count($result); $i++ ){
			if( $result[$i]['section'] == 'insight' )
				$insight_items = explode(",", $result[$i]["idm"]);
			if( $result[$i]['section'] == 'insights' )
				$insights_items = explode(",", $result[$i]["idm"]);
			if( $result[$i]['section'] == 'tender' )
				$tender_items = explode(",", $result[$i]["idm"]);
		}			
		
        /* $insight_items = explode(",", $result['insight']["idm"]);
        $insights_items = explode(",", $result['insights']["idm"]);
        $tender_items = explode(",", $result['tender']["idm"]); */
		
		/* echo "1:<pre>";
		print_r($insights_items);
		echo "2:<pre>";
		print_r($insight_items);
		echo "3:<pre>";
		print_r($p_array["insight"]["data"]); */
		
		
		$price_items = explode(",", $result_price[0]["idm"]);
		$alert_items = explode(",", $result_tender_alert[0]["idm"]);
        /* echo "1:<pre>";
		print_r($price_items); */
		
        //foreach($p_array["insight"]["data"] as $v ){
		for( $i = 0; $i < count($p_array["insight"]["data"]); $i++ ){	
			
            if(
				in_array($p_array["insight"]["data"][$i]["itemID"],$insight_items)  	|| 
				in_array($p_array["insight"]["data"][$i]["itemID"],$insights_items) 
			){
              $p_array["insight"]["data"][$i]["isBookmarked"] = 'y';
            } else 
				$p_array["insight"]["data"][$i]["isBookmarked"] = 'n';
		
        }
		
        //foreach($p_array["tenders"]["data"] as $w ){
		for( $i = 0; $i < count($p_array["tenders"]["data"]); $i++ ){		
            if(in_array($p_array["tenders"]["data"][$i]["itemID"],$tender_items)){
				
				$p_array["tenders"]["data"][$i]["isBookmarked"] = 'y';  
			} else 
				$p_array["tenders"]["data"][$i]["isBookmarked"] = 'n';  
			if(in_array($p_array["tenders"]["data"][$i]["itemID"],$alert_items)){
				$p_array["tenders"]["data"][$i]["isSetAlert"] = 'y';  
			} else 
				$p_array["tenders"]["data"][$i]["isSetAlert"] = 'n';				
		}
		
		if($withCommodity){
			foreach($p_array["data"] as $cID=>$itemArr){	
				foreach( $itemArr as $j=>$items){	
					//echo $p_array["price"]["data"][$i]["itemID"] . "<br />";
					if(in_array($items["itemID"]."-f",$price_items) || in_array($items["itemID"]."-cnf",$price_items) || in_array($items["itemID"]."-fob",$price_items)){
						$p_array["data"][$cID][$j]["isAddedToPortfolio"] = 'y';  
					} else{
						$p_array["data"][$cID][$j]["isAddedToPortfolio"] = 'n';  
					}	
				}
			}
		}else{
			
			//foreach($p_array["price"]["data"] as $g ){
			for( $i = 0; $i < count($p_array["price"]["data"]); $i++ ){	
				//echo $p_array["price"]["data"][$i]["itemID"] . "<br />";
				if(in_array($p_array["price"]["data"][$i]["itemID"]."-f",$price_items) || in_array($p_array["price"]["data"][$i]["itemID"]."-cnf",$price_items) || in_array($p_array["price"]["data"][$i]["itemID"]."-fob",$price_items)){
					$p_array["price"]["data"][$i]["isAddedToPortfolio"] = 'y';  
				} else{
					$p_array["price"]["data"][$i]["isAddedToPortfolio"] = 'n';  
				}	
				  //$g["isAddedToPortfolio"] = "y";
			}
        }
		
        return $p_array;
	}
	
	
	
 function getMarketByID($itemID=0,$section,$commodityID=array()){
        $result=array();
		if((empty($itemID) || empty($section)) &&  empty($commodityID) ){
			 return array("code" => 2001, "message" => "item id/section  blank","allowed_market"=>$result);
		}
		if(strtolower($section)=='prices'){
               $sqlQry = "
                    SELECT f_market                  
                    FROM `tbl_op_commodity_master` as r where              
                    id=".$itemID;		
                $resultData = DB::select($sqlQry);
                $result = explode(",",$resultData[0]['f_market']);
		}else{
            if(!empty($itemID)){
                if(strtolower($section)=='insights'){
                    $tbl='tbl_news_post';
                    $where_condition = " post_id = $itemID  ";
                }
                if(strtolower($section)=='intel'){
                    $tbl='intel';
                    $where_condition = " itemId = $itemID  ";
                }
                if(strtolower($section)=='prices'){
                    $tbl='tbl_price_static';
                    $where_condition = " relation_id = $itemID  ";
                }
                if(strtolower($section)=='tenders'){
                    $tbl='tbl_tenders';
                    $where_condition = " id = $itemID  ";
                }
                if(strtolower($section)=='infographics'){
                    $tbl='tbl_stats_infographics';
                    $where_condition = " post_id = $itemID  ";
                }
                if($tbl=='')
                    return array("code" => 2001, "message" => "Table name blank.","allowed_market"=>$result);
                
                $sqlQry = "
                    SELECT case when subSubCommodityID<>0 then subSubCommodityID
                                when subCommodityID<>0 then subCommodityID
                                else commodityID
                        end as commodity                           
                    FROM `".$tbl."` as r where              
                    $where_condition";		
                $resultData = DB::select($sqlQry);
                
                foreach($resultData as $data):
                        $result['commodityID'] = $data['commodity'];                        			
                endforeach;	
            }elseif(!empty($commodityID)){
                $result = $commodityID;
            }
            
              //getting data from the region commdity table 
            $comm_arr = implode(",", $result);
            $qry =  "select 
                GROUP_CONCAT(f_market) as market
             from tbl_region_commodity_master where id in ($comm_arr)  and itemtype='commodity' ";        
            $resultData = DB::select($qry);            
            $marketOfPage= $resultData[0]['market'];
            
            $result = array_unique(explode(",",$marketOfPage));
            
            
        }
		return array("code" => 1001, "message" => "success", "allowed_market"=> $result);
	}
 
	
}
