<?php
	require_once("/home/steelmin/public_html/inc/config1.php");
	require_once("/home/steelmin/public_html/cf.php");
	require_once("/home/steelmin/public_html/cf_new.php");
	function getSelectableColumns($table = '', $memid = 0, $process = 'other', $_appType='SM')
	{
		$columns = [
			'search'	=>	[
				'tbl_exim_master'	=>	[
					"em.`id`",
					"em.`sub_category`",
					"em.`category`",
					"ep.`f_cnf_fob`",
					"ep.`price`",
					"ep.`change`",
					"ep.`min_price`",
					"ep.`max_price`",
					"CASE 
						WHEN em.`sub_category` = 'LOGISTICS' THEN concat(em.`load_country`, ', ', em.`unload_country` )
						WHEN ep.`f_cnf_fob` = 'cnf' THEN em.`unload_country` 
						ELSE em.`load_country` 
					 END as `country`",
					"CASE
						WHEN em.`sub_category` = 'LOGISTICS' THEN em.`delivery_terms`
						WHEN ep.`f_cnf_fob` = 'fob' THEN CONCAT('FOB ', em.`load_country`)
						WHEN ep.`f_cnf_fob` = 'cnf' THEN CONCAT('CNF ', em.`unload_country`)
						ELSE 'INDIA'
					 END as first_col",
					"CASE
						WHEN em.`sub_category` = 'LOGISTICS' THEN em.`f_grade`
						ELSE ep.`size_grade`
					END as second_col"
				],
				'tbl_dom_master'	=>	[
					"dm.`id`",
					"dm.`sub_category`",
					"dm.`category`",
					"dp.`f_cnf_fob`",
					"dp.`price`",
					"dp.`change`",
					"dp.`min_price`",
					"dp.`max_price`",
					"'INDIA' as `country`",
					"dm.`first_col`",
					"dp.`size_grade` as second_col"
				]
			],
			'other'		=>	[
				'tbl_exim_master'	=>	[
					"em.`id`",
					"em.`sub_category`",
					"em.`assessment`",
					"em.`chemical_composition`",
					"em.`delivery_terms`",
					"em.`payment`",
					"em.`remarks`",
					"em.`quantity`",
					"em.`delivery_period`",
					"em.`taxes`",
					"em.`currency`",
					"em.`sticky_header_value`",
					"em.`category`",
					"ep.`price`",
					"ep.`change`",
					"ep.`min_price`",
					"ep.`max_price`",
					"ep.`size_grade`",
					"ep.`origin`",
					"ep.`particular`",
					"ep.`flag`",
					"ep.`lat_long`",
					"ep.`1W`",
					"ep.`1M`",
					"ep.`3M`",
					"ep.`sentiments`",
					"ep.`date_time`",
					"'E_I' as `trade_type`",
					"ep.`f_cnf_fob` as f_cnf_fob",
					"CASE 
						WHEN em.`sub_category` = 'LOGISTICS' THEN concat(em.`load_country`, ',', em.`unload_country` )
						WHEN ep.`f_cnf_fob` = 'cnf' THEN em.`unload_country` 
						ELSE em.`load_country` 
					 END as `country`",
					"em.`load_port` as load_port",
					"em.`unload_port` as unload_port"
				],
				'tbl_dom_master'	=>	[
					"dm.`id`",
					"dm.`sub_category`",
					"dm.`assessment`",
					"dm.`chemical_composition`",
					"dm.`delivery_terms`",
					"dm.`payment`",
					"dm.`remarks`",
					"dm.`quantity`",
					"dm.`delivery_period`",
					"dm.`taxes`",
					"dm.`currency`",
					"dm.`sticky_header_value`",
					"dm.`category`",
					"dp.`price`",
					"dp.`change`",
					"dp.`min_price`",
					"dp.`max_price`",
					"dp.`size_grade`",
					"dp.`origin`",
					"dp.`particular`",
					"dp.`flag`",
					"dp.`lat_long`",
					"dp.`1W`",
					"dp.`1M`",
					"dp.`3M`",
					"dp.`sentiments`",
					"dp.`date_time`",
					"'DOM' as `trade_type`",
					"dp.`f_cnf_fob` as f_cnf_fob",
					"'INDIA' as country",
					"'NA' as load_port",
					"'NA' as unload_port"
				]
			]
		];

		$portfolio = "0 as portfolio";
		if($memid) {
			$portfolio = "(SELECT COUNT(up.`id`) FROM `tbl_user_portfolio` up WHERE up.`user_id` = '" . $memid . "' AND up.`row_id` = %s.`id` AND up.`prices_col` = %s.`f_cnf_fob` AND up.`type` = 'prices' AND appType='$_appType') as portfolio";
		}

		$columns[$process]['tbl_exim_master'][] = sprintf($portfolio, 'em', 'ep');
		$columns[$process]['tbl_dom_master'][] = sprintf($portfolio, 'dm', 'dp');

		if(isset($columns[$process][$table])) {
			return $columns[$process][$table];
		} else {
			return "*";
		}
	}

	function getSearchableColumns()
	{
		return [
			'common'	=>	[
				'category',
				'sub_category',
				'item',
				'f_grade',
				'f_size',
				'delivery_terms',
				'chemical_composition',
				'payment',
				'quantity',
				'delivery_period',
				'taxes',
				'f_keywords'
			],
			'E_I'		=>	[
				'load_country',
				'load_port',
				'unload_country',
				'unload_port',
				'particular'
			],
			'DOM'		=>	[
				'first_col',
				'region',
				'state',
				'city',
				'supplier'
			]
		];
	}

	function getSearchColumnsPriority($type= 'E_I')
	{
		if($type == 'E_I') {
			return [
				'em.`sub_category`'	=> [
					'full_match'	=>	100,
					'partial_match'	=>	94
				],
				'em.`category`'	=> [
					'full_match'	=>	99,
					'partial_match'	=>	93
				],
				'em.`item`'	=> [
					'full_match'	=>	98,
					'partial_match'	=>	92
				],
				/* 'em.`commodity`'	=> [
					'full_match'	=>	97,
					'partial_match'	=>	91
				], */
				'em.`load_country`'	=> [
					'full_match'	=>	96,
					'partial_match'	=>	90
				],
				'em.`unload_country`'	=> [
					'full_match'	=>	95,
					'partial_match'	=>	89
				]
			];
		} else {
			return [
				'dm.`sub_category`'	=> [
					'full_match'	=>	100,
					'partial_match'	=>	94
				],
				'dm.`category`'	=> [
					'full_match'	=>	99,
					'partial_match'	=>	93
				],
				'dm.`item`'	=> [
					'full_match'	=>	98,
					'partial_match'	=>	92
				],
				/* 'dm.`commodity`'	=> [
					'full_match'	=>	97,
					'partial_match'	=>	91
				], */
				'dm.`first_col`'	=> [
					'full_match'	=>	96,
					'partial_match'	=>	90
				],
				'dm.`region`'	=> [
					'full_match'	=>	95,
					'partial_match'	=>	89
				]
			];
		}
	}

	function getTabsForMobileApp($item = '', $type = 'DOM')
	{
		$tabs = array();
		
		@$content = file_get_contents("/home/steelmin/public_html/api/pre_function/tabs.json");

		if($content != false) {
			$tabs = json_decode($content, true);
		}

		$item = strtoupper($item);
		$type = strtoupper($type);

	    if(isset($tabs[$item][$type])) {

	        return $tabs[$item][$type]['tabs'];

	    } else if($type == 'E_I') {

	    	$sql = "SELECT GROUP_CONCAT(m.`id`) as tabs FROM `tbl_prices_index_master` m WHERE m.`page` = 'mobileapp' AND m.`trade_type` = '" . $type . "' AND m.`item` = '" . $item . "'";

	    	$query = mysql_query($sql) or die("Error: " . mysql_error());
	    	$result = mysql_fetch_assoc($query);
	    	
	    	return $result['tabs'];

		}

		return false;
	}

	function getPricesForApp($memid = 0, $filter = array())
	{
		$appType='SM';
		if(isset($filter['appType']) && strtoupper($filter['appType'])=='CM'){
			$appType='CM';
		}
		$data = array();

		$userType = '';
		$maxCount = 1;

		if($memid) {
			$user = new \App\Http\Controllers\Auth\User($memid,$appType);
			$maxCount =	$user->getTakenServicesByUser();
			
			$userType = isset($user->session['W']['userType']) ? $user->session['W']['userType'] : 1;
			if($appType=='CM'){
				$userType = isset($user->session['C']['userType']) ? $user->session['C']['userType'] : 1;
			}elseif(isset($user->session['MA_PO']['userType'])) {///chk Price Only log 
				$userType = $user->session['MA_PO']['userType'];
				$maxCount =	$user->getTakenServicesByUser('MA_PO');
			}
			elseif($userType != 1) { //WEB log for this client is not active; so, chk if MA log is active or not
				if(isset($user->session['MA']['userType'])) {
					$userType = $user->session['MA']['userType'];
				}
			}
		} else if(isset($_COOKIE['uid']) && !empty($_COOKIE['uid'])) {
			$memid = $_COOKIE['uid'];
			$maxCount =	getTakenServicesByUser();
			$userType = (isset($_SESSION['userType']) ? $_SESSION['userType'] : '');
		}

		$userDetails = [
			'memid'		=>	$memid,
			'userType'	=>	$userType,
			'maxCount'	=>	$maxCount
		];

		if(isset($filter['by_pass'])) {

			// If by_pass filter set call directly filter function
			$data = getPriceRowsByFilter($filter, $userDetails);

		} else if(isset($filter['relatedPrices'])) {

			// Get Prices from `tbl_prices_index_master` on the basis of cond_cols_val_block column
			$data = getRelatedPriceRows($filter, $userDetails);

		} else if(isset($filter['web_regions'])) {

			// Get Prices from `tbl_prices_index_master` By value of Tabs, Trade_Type & Sub_Category
			$filter['page'] = 'home_new';
			$data = getPriceRowsByCategory($filter, $userDetails);

		} else if(isset($filter['tabs'])) {

			// Get Prices from `tbl_prices_index_master` By value of Tabs, Trade_Type & Sub_Category
			$filter['view_all_prices'] = true;
			$data = getPriceRowsByCategory($filter, $userDetails);

		} else if(isset($filter['category'])) {

			// Get Prices from `tbl_prices_index_master` By Category i.e, RM, SF, FL, etc...
			$data = getPriceRowsByCategory($filter, $userDetails);

		} else if(isset($filter['sterm'])) {

			// Get Prices By Search Term
			$data = getPriceRowsBySearchTerm($filter, $userDetails);

		} else if(isset($filter['portfolio'])) {

			// Get Prices By Portfolio
			$data = getPriceRowsByPortfolio($filter, $userDetails);

		} else if(isset($filter['region'])) {

			// Get Prices By Region
			$data = getPriceRowsByRegion($filter, $userDetails);

		} else {

			// Get Prices By Filters
			$data = getPriceRowsByFilter($filter, $userDetails);

		}

		require_once("/home/steelmin/public_html/inc/desconfig.php");

		return $data;
	}

	function getRelatedPriceRows($filter = array(), $userDetails = array())
	{
		$data = array();

		$sql = "SELECT m.`id`, m.`category`, m.`item`, m.`trade_type`, m.`f_price_column`, m.`cond_cols_val_block`, GROUP_CONCAT(m.`cond_cols_val_end`) as relation_ids";

		$sql .= " FROM `tbl_prices_index_master` m";
		$sql .= " WHERE m.`id` > 0";

		if(isset($filter['category']) && !empty($filter['category'])) {
			$category = str_replace(",", "','", $filter['category']);
			$sql .= " AND m.`category` IN ('" . trim(strtoupper($category)) . "')";
		}elseif(isset($filter['appType']) && strtoupper($filter['appType'])=='CM'){
			$sql .= " AND m.`category` = 'CL' ";
			//$domWhere .= " AND m.`category` = 'CL' ";
		}

		if(isset($filter['sub_category']) && !empty($filter['sub_category'])) {
			$item = strtoupper($filter['sub_category']);

			if($item == 'IRONOREFINES') {
	            $item = 'IRON ORE';
			} else if($item == 'FLAT_STEEL') {
	            $item = 'HRC';
			}

			$sql .= " AND m.`item` = '" . mysql_real_escape_string(trim(strtoupper($item))) . "'";
		}

		$pages = [ 
			'home_new', 'prices', 'mobileapp'
		];

		if(isset($filter['page']) && !empty($filter['page'])) {
			if(is_array($filter['page'])) {
				$pages = $filter['page'];
			} else {
				$pages = strtolower($filter['page']);
				$pages = explode(",", $pages);
			}
		}

		$sql .= " AND m.`page` IN ('" . implode("','", $pages) . "')";

		$sql .= " GROUP BY m.`trade_type`, m.`f_price_column`, m.`cond_cols_val_block`";

		$sql .= " ORDER BY";
		$sql .= " m.`trade_type` DESC";
		$sql .= ", CASE";
		$sql .= " WHEN m.`f_price_column` = 'cnf' THEN 1";
		$sql .= " WHEN m.`f_price_column` = 'fob' THEN 2";
		$sql .= " WHEN m.`f_price_column` = 'f' THEN 3";
		$sql .= " ELSE 999";
		$sql .= " END";



		if(isset($filter['sub_category']) && strtoupper($filter['sub_category']) == 'COAL') {
			$sql .= ", m.`cond_cols_val_block` DESC";
		} else {
			$sql .= ", m.`cond_cols_val_block`, m.`id`";
		}

		if(isset($filter['debug']) && $filter['debug'] == true) {
			echo "<pre>", print_r($filter), "</pre>";
			echo $sql . "<br />";
		}

		$results = mysql_query($sql) or die("Error: " . mysql_error());

		if(mysql_num_rows($results) > 0) {
			while($row = mysql_fetch_assoc($results)) {

				$relation_ids = explode(",", $row['relation_ids']);
				$relation_ids = array_unique($relation_ids);
				sort($relation_ids);
				$row['relation_ids'] = implode(",", $relation_ids);

				$data = getPriceRows($row, $data, $filter, $userDetails);

			}
		}

		return $data;
	}

	function getPriceRowsByCategory($filter = array(), $userDetails = array())
	{
		$data = array(); 

		$sql = "SELECT m.`id`, m.`category`, m.`item`, m.`trade_type`, m.`f_price_column`, m.`cond_cols_block`, m.`cond_cols_val_block`";

		if(isset($filter['view_all_prices']) && $filter['view_all_prices'] == true) {
			$sql .= ", GROUP_CONCAT(m.`cond_cols_val_end_prices`) as relation_ids";
		} else if(isset($filter['page']) && strtolower($filter['page']) == 'home_new') {
			$sql .= ", GROUP_CONCAT(m.`cond_cols_val_end`) as relation_ids";
		} else {
			$sql .= ", GROUP_CONCAT(m.`cond_cols_val_end`) as relation_ids";
		}

		$sql .= " FROM `tbl_prices_index_master` m";
		$sql .= " WHERE m.`id` > 0";

		if(isset($filter['category']) && !empty($filter['category'])) {
			$category = str_replace(",", "','", $filter['category']);
			$sql .= " AND m.`category` IN ('" . trim(strtoupper($category)) . "')";
		}elseif(isset($filter['appType']) && strtoupper($filter['appType'])=='CM'){
			$sql .= " AND m.`category` = 'CL' ";
			//$domWhere .= " AND m.`category` = 'CL' ";
		}

		if(isset($filter['sub_category']) && !empty($filter['sub_category'])) {
			$sql .= " AND m.`item` = '" . mysql_real_escape_string(trim(strtoupper($filter['sub_category']))) . "'";
		}

		if(isset($filter['trade_type']) && !empty($filter['trade_type'])) {
			$sql .= " AND m.`trade_type` = '" . mysql_real_escape_string(trim(strtoupper($filter['trade_type']))) . "'";
		}

		if(isset($filter['page']) && !empty($filter['page'])) {
			if(is_array($filter['page'])) {
				$sql .= " AND m.`page` IN ('" . implode("', '", $filter['page']) . "')";
			} else if(strpos($filter['page'], ",") !== false) {
				$pages = str_replace(",", "','", $filter['page']);
				$sql .= " AND m.`page` IN ('" . $pages . "')";
			} else {
				$sql .= " AND m.`page` = '" . mysql_real_escape_string(trim(strtolower($filter['page']))) . "'";
			}
		} else {
			$sql .= " AND m.`page` = 'mobileapp'";
		}

		if(isset($filter['tabs']) && !empty($filter['tabs'])) {
			$tabs = $filter['tabs'];
			$tabs = explode(",", $tabs);
			$tabsWhere = array();
			if($tabs) {
				foreach($tabs as $tab) {
					$tab = str_replace("ship_breaking", "ship breaking", strtolower($tab));
					$tabsWhere[] = "m.`tabs` = '" . mysql_real_escape_string(trim($tab)) . "'";
				}

				$sql .= " AND (" . implode(" OR ", $tabsWhere) . ")";
			}
		}

		if(isset($filter['web_regions']) && !empty($filter['web_regions'])) {
			if(is_array($filter['web_regions'])) {
				$sql .= " AND m.`region` IN ('" . implode("', '", $filter['web_regions']) . "')";
			} else if(strpos($filter['web_regions'], ",") !== false) {
				$regions = str_replace(",", "','", $filter['web_regions']);
				$sql .= " AND m.`region` IN ('" . $regions . "')";
			} else {
				$sql .= " AND m.`region` = '" . mysql_real_escape_string(trim(strtolower($filter['web_regions']))) . "'";
			}
		}

		$sql .= " GROUP BY m.`item`, m.`trade_type`, m.`f_price_column`, m.`cond_cols_val_block`";
		if(isset($filter['category']) && $filter['category'] == 'L') {
			
			$sql .= " ORDER BY";
			$sql .= " CASE";
			$sql .= " WHEN m.`item` = 'iron ore' THEN 1";
			$sql .= " WHEN m.`item` = 'coal' THEN 2";
			$sql .= " WHEN m.`item` = 'met coke' THEN 3";
			$sql .= " END";
			$sql .= ", CASE";
			$sql .= " WHEN m.`f_price_column` = 'cnf' THEN 1";
			$sql .= " WHEN m.`f_price_column` = 'fob' THEN 2";
			$sql .= " WHEN m.`f_price_column` = 'f' THEN 3";
			$sql .= " ELSE 999";
			$sql .= " END";
			
		} else {
			
			$sql .= " ORDER BY";
			$sql .= " CASE";
			$sql .= " WHEN m.`category` LIKE 'RM' THEN 1";
			$sql .= " WHEN m.`category` LIKE 'IO' THEN 1";
			$sql .= " WHEN m.`category` LIKE 'CL' THEN 2";
			$sql .= " WHEN m.`category` LIKE 'FS' THEN 3";
			$sql .= " WHEN m.`category` LIKE 'SB' THEN 4";
			$sql .= " WHEN m.`category` LIKE 'SF' THEN 5";
			$sql .= " WHEN m.`category` LIKE 'FL' THEN 6";
			$sql .= " WHEN m.`category` LIKE 'FF' THEN 7";
			$sql .= " WHEN m.`category` LIKE 'FA' THEN 8";
			$sql .= " ELSE 99";
			$sql .= " END";
			$sql .= ", m.`app_order` ASC";
			$sql .= ", m.`cond_cols_val_block` ASC";
			$sql .= ", CASE";
			$sql .= " WHEN m.`f_price_column` = 'cnf' THEN 1";
			$sql .= " WHEN m.`f_price_column` = 'fob' THEN 2";
			$sql .= " WHEN m.`f_price_column` = 'f' THEN 3";
			$sql .= " ELSE 999";
			$sql .= " END";
			
			
		}
		
		if(isset($filter['debug']) && $filter['debug'] == true) {
			echo "<pre>", print_r($filter), "</pre>";
			echo $sql . "<br />";
			
		}

		$results = mysql_query($sql) or die("Error: " . mysql_error());

		if(mysql_num_rows($results) > 0) {
			while($row = mysql_fetch_assoc($results)) {

				if(isset($filter['debug']) && $filter['debug'] == true) {
					echo "<pre>", print_r($row), "</pre>";
				}

				$data = getPriceRows($row, $data, $filter, $userDetails);

			}
		}

		return $data;
	}

	function getPriceRows($row = array(), $data = array(), $filter = array(), $userDetails = array())
	{
		$table = '';
		$relation_ids = array();
		$cond_cols = '';
		$subCatColValue = $row['item'];

		if(isset($row['trade_type']) && $row['trade_type'] == 'E_I') {
			$table = 'tbl_exim_master';
		} else if(isset($row['trade_type']) && $row['trade_type'] == 'DOM') {
			$table = 'tbl_dom_master';
		}

		if(isset($row['relation_ids']) && !empty($row['relation_ids'])) {
			$relation_ids = str_replace("/*/'12MM'", "", trim($row['relation_ids']));
			$relation_ids = preg_replace('/\s+/', '', $relation_ids);
			$relation_ids = explode(",", $relation_ids);
		}

		if(isset($filter['category']) && $filter['category'] == 'L') {
			$subCatColValue = "LOGISTICS";
			rsort($relation_ids);
		}

		if(isset($filter['sub_category']) && !empty($filter['sub_category'])) {
			if(strtoupper($filter['sub_category']) == 'IRONOREFINES') {
	            $filter['sub_category'] = 'IRON ORE';
			} else if(strtoupper($filter['sub_category']) == 'FLAT_STEEL') {
	            $filter['sub_category'] = 'HRC';
			}
		}


		if($table) {

			$sql = "SELECT m.`id`";
			$sql .= ", m.`assessment`, m.`chemical_composition`, m.`delivery_terms`, m.`payment`, m.`remarks`, m.`quantity`, m.`delivery_period`, m.`taxes`, m.`currency`, m.`category`, p.`price`, p.`change`, p.`min_price`, p.`max_price`, p.`size_grade`, p.`origin`, p.`particular`, p.`flag`, p.`lat_long`, p.`1W`, p.`1M`, p.`3M`, p.`sentiments`, p.`date_time`, p.`f_cnf_fob`, '" . $row['trade_type'] . "' as trade_type, m.`sticky_header_value`, m.`show_in_app_mapview`";
			$sql .= ", CASE";
			$sql .= " WHEN m.`category` = 'LOGISTICS' THEN  m.`item`";
			$sql .= " ELSE m.`sub_category`";
			$sql .= " END as sub_category";

			if($row['f_price_column'] != 'f') {
				$sql .= ", CASE";
				$sql .= " WHEN m.`category` = 'LOGISTICS' THEN  CONCAT(m.`load_country`, ',', m.`unload_country`)";
				$sql .= " WHEN p.`f_cnf_fob` = 'cnf' THEN m.`unload_country`";
				$sql .= " WHEN p.`f_cnf_fob` = 'fob' THEN  m.`load_country`";
				$sql .= " ELSE 'INDIA'";
				$sql .= " END as country";

				$sql .= ", m.`load_port`";
				$sql .= ", m.`unload_port`";
				$sql .= ", '' as city";
			} else {
				$sql .= ", 'INDIA' as country";

				$sql .= ", 'NA' as load_port";
				$sql .= ", 'NA' as unload_port";
				$sql .= ", m.`city` as city";
			}

			if($userDetails['memid']) {
				$sql .= ", (SELECT COUNT(up.`id`) FROM `tbl_user_portfolio` up WHERE up.`user_id` = '" . $userDetails['memid'] . "' AND up.`row_id` = m.`id` AND up.`type` = 'prices') as portfolio";
			} else {
				$sql .= ", 0 as portfolio";
			}
			
			$sql .= " FROM `" . $table . "` m";
			$sql .= " LEFT JOIN `tbl_price_static` p ON(m.`id` = p.`relation_id`)";

			$sql .= " WHERE m.`id` > 0";

			if( (isset($filter['view_all_prices']) && $filter['view_all_prices'] == true) || (isset($filter['category']) && $filter['category'] == 'L')) {

				if(!empty($row['cond_cols_block']) && !empty($row['cond_cols_val_block'])) {
					$sql .= " AND m.`" . $row['cond_cols_block'] . "` IN (" . $row['cond_cols_val_block'] . ")";
				}

			}

			if(isset($relation_ids[0]) && !empty($relation_ids[0])) {
				$sql .= " AND m.`id` IN ('" . implode("', '", $relation_ids) . "')";
			}

			if(isset($filter['sub_category']) && !empty($filter['sub_category'])) {
				$sql .= " AND m.`sub_category` = '" . mysql_real_escape_string($filter['sub_category']) . "'";
			} else {
				$sql .= " AND m.`sub_category` = '" . mysql_real_escape_string($subCatColValue) . "'";
			}


			$sql .= " AND p.`f_cnf_fob` = '" . $row['f_price_column'] . "'";

			if(isset($filter['category']) && $filter['category'] == 'L') {
				$sql .= " ORDER BY m.`sticky_header_value` ASC";
			} else if(isset($filter['view_all_prices']) && $filter['view_all_prices'] == true) {
				if(isset($relation_ids[0]) && !empty($relation_ids[0])) {
					$sql .= " ORDER BY FIELD(m.`id`, " . implode(", ", $relation_ids) . ")";
				}
			} else {
				$sql .= " ORDER BY country ASC";				
			}

			if(isset($filter['debug']) && $filter['debug'] == true) {
				echo "<pre>", print_r($relation_ids), "</pre>";
				echo $sql . "<br />";
			}

			$results = mysql_query($sql) or die("Error: " . mysql_error());

			if(mysql_num_rows($results) > 0) {
				while($result = mysql_fetch_assoc($results)) {
					
					if($row['f_price_column'] == 'f') {
						$row['cond_cols_val_block'] = $result['city'];
					}

					$data[] = formatResultSet($result, $row, $userDetails, $filter);

				}
			}
		}
		
		/* if(count($data)<1 && isset($filter['tabs']) && !empty($filter['tabs'])){
			require_once("/home/steelmin/public_html/mf.php");
			$msgs=='';
			if($filter['sub_category'])
				$msgs=' Please check and update on url "http://www.steelmint.com/steeladmin/allPricesData/"  for item : '. $subCatColValue.' - '. $filter['sub_category'] .' - '. $row['trade_type'];
			if($relation_ids)
				$msgs.=' <br><br>Relation ids are '.implode("', '", $relation_ids);
				$msgs.= '<br>---------index master rows----------';
				$msgs.= json_encode($row);
				$msgs.= '<br>--------filters-----------';
				$msgs.= json_encode($filter);				
				$msgs.= '<br>---------userDetails----------';
				$msgs.= json_encode($userDetails);
				$msgs.= '<br>--------out put data-----------';
				$msgs.= json_encode($data);
				//echo $msgs;

			sendSMMail($msgs, 'mohan@theitbooth.com,tarun@steelmint.com', $name='', $subject="Prices in table tbl_price_static is not Generated for item ". $subCatColValue  .' - '. $row['trade_type']);
		} */
		return $data;
	}

	function getPriceRowsBySearchTerm($filter = array(), $userDetails = array())
	{
		$data = array();
		$upgradeText = 'Upgrade';
		$loginText = 'Login';			
		
		$maxCount = 1;
		if(isset($userDetails['maxCount'])) {
			$maxCount = $userDetails['maxCount'];
		}

		$searchTerm = trim($filter['sterm']);
		$appType='SM';
		if(isset($filter['appType']) && strtoupper($filter['appType'])=='CM'){
			$appType='CM';
		}
		
		if($searchTerm) {
			$E_IWordsWhere = array();
			$DOMWordsWhere = array();

			$searchableColumns = getSearchableColumns();

			$E_ISearchableColumnsPriority = getSearchColumnsPriority('E_I');
			$DOMSearchableColumnsPriority = getSearchColumnsPriority('DOM');

			$E_IColumns = 'em.`';
			$E_IColumns .= implode("`, ' ', em.`" , $searchableColumns['common']);
			$E_IColumns .= "`, ' ', em.`";
			$E_IColumns .= implode("`, ' ', em.`" , $searchableColumns['E_I']);
			$E_IColumns .= '`';

			$DOMColumns = 'dm.`';
			$DOMColumns .= implode("`, ' ', dm.`" , $searchableColumns['common']);
			$DOMColumns .= "`, ' ', dm.`";
			$DOMColumns .= implode("`, ' ', dm.`" , $searchableColumns['DOM']);
			$DOMColumns .= '`';

			$E_IRelevancies = array();
			$DOMRelevancies = array();

			$words = explode(" ", $searchTerm);

			foreach($words as $word) {
				$word = trim($word);

				if(strlen($word) > 0) {
					$E_IWordsWhere[] = "CONCAT(" . $E_IColumns . ") LIKE '%" . mysql_real_escape_string($word) . "%'";
					$DOMWordsWhere[] = "CONCAT(" . $DOMColumns . ") LIKE '%" . mysql_real_escape_string($word) . "%'";

					if($E_ISearchableColumnsPriority) {
						foreach($E_ISearchableColumnsPriority as $column => $weightage) {
							$relevanceQuery = " (CASE WHEN LOCATE('" . mysql_real_escape_string($word) . "', " . $column. ") = 1 THEN " . $weightage['full_match'];
							$relevanceQuery .= " WHEN LOCATE('" . mysql_real_escape_string($word) . "', " . $column. ") > 1 THEN " . $weightage['partial_match'];
							$relevanceQuery .= " ELSE 0 END)";

							$E_IRelevancies[] = $relevanceQuery;
						}
					}

					if($DOMSearchableColumnsPriority) {
						
						foreach($DOMSearchableColumnsPriority as $column => $weightage) {
							$relevanceQuery = " (CASE WHEN LOCATE('" . mysql_real_escape_string($word) . "', " . $column. ") = 1 THEN " . $weightage['full_match'];
							$relevanceQuery .= " WHEN LOCATE('" . mysql_real_escape_string($word) . "', " . $column. ") > 1 THEN " . $weightage['partial_match'];
							$relevanceQuery .= " ELSE 0 END)";								

							$DOMRelevancies[] = $relevanceQuery;
						}
					}
				}
			}
			
			$sql = "(SELECT " . implode(", ", getSelectableColumns('tbl_exim_master', $userDetails['memid'], 'search', $appType));

			if($E_IRelevancies) {
				$sql .= ", (" . implode(" + ", $E_IRelevancies) . ") as weightage";
			} else {
				$sql .= ", 0 as weightage";
			}
			
			$sql_em_cm='';$sql_dm_cm='';
			///for CM
			if($appType=='CM'){
				$sql_em_cm .= " AND em.`category` = 'COAL' ";
				$sql_dm_cm .= " AND dm.`category` = 'COAL' ";
			}
			
			$sql .= " FROM `tbl_exim_master` em";
			$sql .= " LEFT JOIN `tbl_price_static` ep ON(em.`id` = ep.`relation_id`)";
			$sql .= " WHERE em.`id` > 0";
			$sql .= " AND (" . implode(" AND ", $E_IWordsWhere) . ")";
			$sql .= " AND `f_cnf_fob` IS NOT NULL";
			$sql .= " AND em.`category` <> 'Index' ";
			$sql .= $sql_em_cm;
			$sql .= ")";

			$sql .= " UNION ";

			$sql .= "(SELECT " . implode(", ", getSelectableColumns('tbl_dom_master', $userDetails['memid'], 'search', $appType));
			
			if($DOMRelevancies) {
				$sql .= ", (" . implode(" + ", $DOMRelevancies) . ") as weightage";
			} else {
				$sql .= ", 0 as weightage";
			}

			$sql .= " FROM `tbl_dom_master` dm";
			$sql .= " LEFT JOIN `tbl_price_static` dp ON(dm.`id` = dp.`relation_id`)";
			$sql .= " WHERE dm.`id` > 0";
			$sql .= " AND (" . implode(" AND ", $DOMWordsWhere) . ")";
			$sql .= " AND `f_cnf_fob` IS NOT NULL";
			$sql .= " AND dm.`category` <> 'Index' ";
			$sql .= $sql_dm_cm;
			$sql .= " AND dm.`state` <> 'OMC' ";
			$sql .= ")";

			$sql .= " ORDER BY";
			$sql .= " weightage DESC";
			$sql .= ", CASE";
			$sql .= " WHEN `f_cnf_fob` = 'cnf' THEN 1";
			$sql .= " WHEN `f_cnf_fob` = 'fob' THEN 2";
			$sql .= " WHEN `f_cnf_fob` = 'f' THEN 3";
			$sql .= " ELSE 999";
			$sql .= " END";
			$sql .= ", country";

			if(isset($filter['debug']) && $filter['debug'] == true) {
				echo "<pre>", print_r($filter), "</pre>";
				echo "<pre>", print_r($sql), "</pre>";
			}
			/* if($appType=='CM'){
				echo "<pre>", print_r($sql), "</pre>";
			} */

			$results = mysql_query($sql) or die("Error: " . mysql_error());

			if(mysql_num_rows($results) > 0) {

				while($result = mysql_fetch_assoc($results)) {

					$row = array();

					$second_col = $result['second_col'];
					if($result['f_cnf_fob'] == 'cnf' && !empty($result['country']) && $result['sub_category'] != 'LOGISTICS') {
						$second_col .= ", " . $result['country'];
					}

					$flag = 0;
					if($result['portfolio'] > 0) {
						$flag = 1;
					}
					
					$price = '';
					$change = '';

					if($maxCount == -1) {

						if(isset($filter['src']) && $filter['src'] == 'APP') {

							if($result['f_cnf_fob'] == 'f') { // if domestic prepend Rupee Symbol (\u20B9)
								$price = "₹";
							}
							$price .= strip_tags($result['price']);

							if(strpos($price, 'lb') !== false) {
								$numbers = array();
								preg_match_all('/((?:[0-9]+,)*[0-9]+(?:\.[0-9]+)?)/', $price, $numbers);
								$price = '¢' . implode('', $numbers[0]) . '/lb';
							}
							
							$change = str_replace("&nbsp;", "", strip_tags(trim(preg_replace("/\r|\n/", "", $result['change']))));

						} else {
							$price = $result['price'];
							$change = trim(preg_replace("/\r|\n/", "", $result['change']));
						}
					} else if($maxCount == 0) {
						$price = $upgradeText;
						$change = $upgradeText;
					} else {
						$price = $loginText;
						$change = $loginText;			
					}

					$allCaps = array(
						'hrc', 'crc', 'gp'
					);
					
					if(strtolower($result['sub_category']) == "tmt") {
						$sub_category = "Rebar";
					} else if(in_array(strtolower($result['sub_category']), $allCaps)) {
						$sub_category = strtoupper($result['sub_category']);
					} else if(str_replace("_"," ", strtolower( $result['sub_category'])) == 'hr plate') {
						$sub_category = 'HR Plate';
					} else {
						$sub_category = ucwords(str_replace("_"," ", strtolower( $result['sub_category'])));

					}

					$data[] = array(
						'item'			=>	$sub_category,
						'row_id'		=>	$result['id'],
						'prices_col'	=>	$result['f_cnf_fob'],
						'flag'			=>	$flag,
						'first_col'		=>	$result['first_col'],
						'second_col'	=>	$second_col,
						'country'		=>	ucwords(strtolower($result['country'])),
						// 'weightage'		=>	$result['weightage'],
						'price'			=>	$price,						
						'change'		=>	utf8_encode($change)
					);

				}
			}

		}

		return $data;
	}

	function getPriceRowsByPortfolio($filter = array(), $userDetails = array())
	{
		$data = array();

		$portfolio = $filter['portfolio'];
		
		$eximWhere = array();
		$domWhere = array();

		$eximWhere[] = "(ep.`relation_id` = -1 AND ep.`f_cnf_fob` = 'f')";
		$domWhere[] = "(dp.`relation_id` = -1 AND dp.`f_cnf_fob` = 'cnf')";

		if($portfolio) {
			foreach($portfolio as $key => $relation_ids) {
				if(is_array($relation_ids)) {
					foreach($relation_ids as $relation_id) {
						if($key == 'f') {
							$domWhere[] = "(dp.`relation_id` = " . (int)$relation_id . " AND dp.`f_cnf_fob` = '" . $key . "')";
						} else {
							$eximWhere[] = "(ep.`relation_id` = " . (int)$relation_id . " AND ep.`f_cnf_fob` = '" . $key . "')";
						}
					}
				} else {
					if($key == 'f') {
						$domWhere[] = "(dp.`relation_id` = " . (int)$relation_ids . " AND dp.`f_cnf_fob` = '" . $key . "')";
					} else {
						$eximWhere[] = "(ep.`relation_id` = " . (int)$relation_ids . " AND ep.`f_cnf_fob` = '" . $key . "')";
					}
				}
			}
		}

		if(isset($portfolio['cnf']) && !empty($portfolio['cnf']) && isset($portfolio['fob']) && !empty($portfolio['fob'])) {
			$eximRelationIds = array_merge($portfolio['cnf'], $portfolio['fob']);
		} else if(isset($portfolio['cnf']) && !empty($portfolio['cnf'])) {
			$eximRelationIds = $portfolio['cnf'];
		} else if(isset($portfolio['fob']) && !empty($portfolio['fob'])) {
			$eximRelationIds = $portfolio['fob'];
		} else {
			$eximRelationIds = array(0);
		}

		if(isset($portfolio['f']) && !empty($portfolio['f'])) {
			$domRelationIds = $portfolio['f'];
		} else {
			$domRelationIds = array(0);
		}
				
		///for CM
		$sql_em_cm ='';$sql_dm_cm='';
		if(isset($filter['appType']) && strtoupper($filter['appType'])=='CM'){
			$sql_em_cm .= " AND em.`category` = 'COAL' ";
			$sql_dm_cm .= " AND dm.`category` = 'COAL' ";
		}
		
		$eximSelectableColumns = getSelectableColumns('tbl_exim_master', $userDetails['memid']);
		$eximSelectableColumns = array_diff($eximSelectableColumns, array('em.`sticky_header_value`'));
		$eximSelectableColumns[] = "CASE 
									WHEN em.`sub_category` = 'LOGISTICS' THEN 'LOGISTICS' 
									ELSE em.`sticky_header_value` 
									END as `sticky_header_value`";

		$domSelectableColumns = getSelectableColumns('tbl_dom_master', $userDetails['memid']);
		$domSelectableColumns = array_diff($domSelectableColumns, array('dm.`sticky_header_value`'));
		$domSelectableColumns[] = "CASE 
									WHEN dm.`sub_category` = 'LOGISTICS' THEN 'LOGISTICS' 
									ELSE dm.`sticky_header_value` 
									END as `sticky_header_value`";

		$sql = "(SELECT " . implode(", ", $eximSelectableColumns);
		$sql .= " FROM `tbl_exim_master` em";
		$sql .= " LEFT JOIN `tbl_price_static` ep ON(em.`id` = ep.`relation_id`)";
		$sql .= " WHERE em.`id` > 0";

		if($eximWhere) {
			$sql .= " AND (" . implode(" OR ", $eximWhere) . ")";
		}

		$sql .= " AND `f_cnf_fob` IS NOT NULL";
		$sql .= " AND em.`category` <> 'Index'";
		$sql .= $sql_em_cm;
		$sql .= ")";

		$sql .= " UNION";

		$sql .= " (SELECT " . implode(", ", $domSelectableColumns);
		$sql .= " FROM `tbl_dom_master` dm";
		$sql .= " LEFT JOIN `tbl_price_static` dp ON(dm.`id` = dp.`relation_id`)";
		$sql .= " WHERE dm.`id` > 0";
		
		if($domWhere) {
			$sql .= " AND (" . implode(" OR ", $domWhere) . ")";
		}

		$sql .= " AND `f_cnf_fob` IS NOT NULL";
		$sql .= " AND dm.`category` <> 'Index'";
		$sql .= $sql_dm_cm;
		$sql .= " AND dm.`state` <> 'OMC'";		
		$sql .= ")";

		//$sql .= " ORDER BY `trade_type` DESC, `f_cnf_fob` ASC, `country` ASC";
		$sql .= " ORDER BY";
		$sql .= " `sticky_header_value` ASC";
		$sql .= ", CASE";
		$sql .= " WHEN `f_cnf_fob` = 'cnf' THEN 1";
		$sql .= " WHEN `f_cnf_fob` = 'fob' THEN 2";
		$sql .= " WHEN `f_cnf_fob` = 'f' THEN 3";
		$sql .= " ELSE 999";
		$sql .= " END";
		$sql .= ", `country` ASC";

		if(isset($filter['debug']) && $filter['debug'] == true) {
			echo 'getPriceRowsByPortfolio'.$sql . "<br />";
		}

		$results = mysql_query($sql) or die("Error: " . mysql_error());

		if(mysql_num_rows($results) > 0) {
			while($result = mysql_fetch_assoc($results)) {

				$row = array();

				$data[] = formatResultSet($result, $row, $userDetails, $filter);

			}
		}

		return $data;
	}

	function getPriceRowsByRegion($filter = array(), $userDetails = array())
	{
		$data = array();

		$eximWhere = " AND em.`id` = '-1'";
		$domWhere = " AND dm.`id` = '-1'";
			
		
		$postRegion = $filter['region'];
		$regions = explode(",", $postRegion);
		$whereRegion = array();
		if($regions) {
			foreach($regions as $region) {
				$whereRegion[] = '"' . trim($region) . '"';
			}

			$eximWhere = " AND (";
			$eximWhere .= "MATCH(em.`region_of_country`) AGAINST ('" . implode(" ", $whereRegion) . "' IN BOOLEAN MODE)";
			// $eximWhere .= " OR ";
			// $eximWhere .= "MATCH(em.`unload_country`) AGAINST ('" . implode(" ", $whereRegion) . "' IN BOOLEAN MODE)";
			$eximWhere .= ")";
		}

		if(strpos(strtolower($postRegion), "india") !== false) {
			$domWhere = "";
		}

		$eximSelectableColumns = getSelectableColumns('tbl_exim_master', $userDetails['memid']);
		$eximSelectableColumns = array_diff($eximSelectableColumns, array('em.`sticky_header_value`'));
		$eximSelectableColumns[] = "CASE 
									WHEN em.`sub_category` = 'LOGISTICS' THEN 'LOGISTICS' 
									ELSE em.`sticky_header_value` 
									END as `sticky_header_value`";

		$domSelectableColumns = getSelectableColumns('tbl_dom_master', $userDetails['memid']);
		$domSelectableColumns = array_diff($domSelectableColumns, array('dm.`sticky_header_value`'));
		$domSelectableColumns[] = "CASE 
									WHEN dm.`sub_category` = 'LOGISTICS' THEN 'LOGISTICS' 
									ELSE dm.`sticky_header_value` 
									END as `sticky_header_value`";
		
		///for CM
		//$sql_em_cm ='';$sql_dm_cm='';
		if(isset($filter['appType']) && strtoupper($filter['appType'])=='CM'){
			$eximWhere .= " AND em.`category` = 'COAL' ";
			$domWhere .= " AND dm.`category` = 'COAL' ";
		} 
		
		$sql = "(SELECT " . implode(", ", $eximSelectableColumns);
		$sql .= " FROM `tbl_exim_master` em";
		$sql .= " LEFT JOIN `tbl_price_static` ep ON(em.`id` = ep.`relation_id`)";
		$sql .= " WHERE em.`id` > 0";
		$sql .= $eximWhere;
		$sql .= " AND `f_cnf_fob` IS NOT NULL";
		$sql .= ")";

		if(strpos(strtolower($postRegion), "india") !== false) {
			$sql .= " UNION";

			$sql .= " (SELECT " . implode(", ", $domSelectableColumns);
			$sql .= " FROM `tbl_dom_master` dm";
			$sql .= " LEFT JOIN `tbl_price_static` dp ON(dm.`id` = dp.`relation_id`)";
			$sql .= " WHERE dm.`id` > 0";
			$sql .= $domWhere;
			$sql .= " AND `f_cnf_fob` IS NOT NULL";
			$sql .= ")";
		}

		//$sql .= " ORDER BY `trade_type` DESC, `f_cnf_fob` ASC, `country` ASC";
		$sql .= " ORDER BY";
		$sql .= " `sticky_header_value` ASC";
		$sql .= ", CASE";
		$sql .= " WHEN `f_cnf_fob` = 'cnf' THEN 1";
		$sql .= " WHEN `f_cnf_fob` = 'fob' THEN 2";
		$sql .= " WHEN `f_cnf_fob` = 'f' THEN 3";
		$sql .= " ELSE 999";
		$sql .= " END";
		$sql .= ", `country` ASC";

		if(isset($filter['debug']) && $filter['debug'] == true) {
			echo "<pre>", print_r($filter), "</pre>";
			echo $sql . "<br />";
		}

		$results = mysql_query($sql) or die("Error: " . mysql_error());

		if(mysql_num_rows($results) > 0) {
			while($result = mysql_fetch_assoc($results)) {

				$row = array();

				$data[] = formatResultSet($result, $row, $userDetails, $filter);

			}
		}

		return $data;
	}

	function getPriceRowsByFilter($filter = array(), $userDetails = array())
	{
		$data = array();

		$sql = "(SELECT " . implode(", ", getSelectableColumns('tbl_exim_master', $userDetails['memid']));
		$sql .= " FROM `tbl_exim_master` em";
		$sql .= " LEFT JOIN `tbl_price_static` ep ON(em.`id` = ep.`relation_id`)";
		$sql .= " WHERE em.`id` > 0";

		if(isset($filter['category']) && !empty($filter['category'])) {
			$category = trim($filter['category']);
			$sql .= " AND LCASE(em.`category`) = '" . mysql_real_escape_string(strtolower($category)) . "'";
		}
		
		///for CM
		/* $sql_em_cm ='';$sql_dm_cm='';
		if(isset($filter['appType']) && strtoupper($filter['appType'])=='CM'){
			$sql_em_cm .= " AND em.`category` = 'COAL' ";
			$sql_dm_cm .= " AND dm.`category` = 'COAL' ";
		} */
		
		if(isset($filter['sub_category']) && !empty($filter['sub_category'])) {
			$sub_category = trim($filter['sub_category']);

			// if(strtolower($sub_category) == 'flat_steel') {
				
			// 	$category = 'SEMI FINISHED';
			// 	$sql .= " AND LCASE(em.`category`) = '" . mysql_real_escape_string(strtolower($category)) . "'";
				
			// } else {

			// 	if(strtolower($sub_category) == 'ironorefines') {
			// 		$sub_category = 'iron ore';
			// 	} 
			$sql .= " AND LCASE(em.`sub_category`) = '" . mysql_real_escape_string(strtolower($sub_category)) . "'";

			//}
		}

		if(isset($filter['row_id'])) {
			$sql .= " AND em.`id` = '" . (int)$filter['row_id'] . "'";
		}

		if(isset($filter['price_type'])) {
			$sql .= " AND ep.`f_cnf_fob` = '" . mysql_real_escape_string(strtolower($filter['price_type'])) . "'";
		}

		if(isset($filter['row_ids']) && !empty($filter['row_ids'])) {
			$whereRowIds = array();
			
			foreach($filter['row_ids'] as $row_id) {
				$whereRowIds[] = "(em.`id` = " . (int)$row_id['id'] . " AND ep.`f_cnf_fob` = '" . $row_id['trade_type'] . "')";
			}
			$sql .= " AND (";
			$sql .= implode(" OR ", $whereRowIds);
			$sql .= ")";
		}
		
		if(isset($filter['appType']) && strtoupper($filter['appType'])=='CM'){
			$sql .= " AND em.`category` = 'COAL' ";
		}
		
		$sql .= " AND `f_cnf_fob` IS NOT NULL";
		$sql .= ")";

		$sql .= " UNION";

		$sql .= " (SELECT " . implode(", ", getSelectableColumns('tbl_dom_master', $userDetails['memid']));
		$sql .= " FROM `tbl_dom_master` dm";
		$sql .= " LEFT JOIN `tbl_price_static` dp ON(dm.`id` = dp.`relation_id`)";
		$sql .= " WHERE dm.`id` > 0";

		if(isset($filter['category']) && !empty($filter['category'])) {
			$category = trim($filter['category']);
			$sql .= " AND LCASE(dm.`category`) = '" . mysql_real_escape_string(strtolower($category)) . "'";
		}

		if(isset($filter['sub_category']) && !empty($filter['sub_category'])) {
			$sub_category = trim($filter['sub_category']);

			// if(strtolower($sub_category) == 'flat_steel') {
				
			// 	$category = 'SEMI FINISHED';
			// 	$sql .= " AND LCASE(dm.`category`) = '" . mysql_real_escape_string(strtolower($category)) . "'";
				
			// } else {

			// 	if(strtolower($sub_category) == 'ironorefines') {
			// 		$sub_category = 'iron ore';
			// 	} 
			$sql .= " AND LCASE(dm.`sub_category`) = '" . mysql_real_escape_string(strtolower($sub_category)) . "'";

			//}
		}

		if(isset($filter['row_id'])) {
			$sql .= " AND dm.`id` = '" . (int)$filter['row_id'] . "'";
		}

		if(isset($filter['price_type'])) {
			$sql .= " AND dp.`f_cnf_fob` = '" . mysql_real_escape_string(strtolower($filter['price_type'])) . "'";
		}

		if(isset($filter['row_ids']) && !empty($filter['row_ids'])) {
			$whereRowIds = array();
			
			foreach($filter['row_ids'] as $row_id) {
				$whereRowIds[] = "(dm.`id` = " . (int)$row_id['id'] . " AND dp.`f_cnf_fob` = '" . $row_id['trade_type'] . "')";
			}
			$sql .= " AND (";
			$sql .= implode(" OR ", $whereRowIds);
			$sql .= ")";
		}
		
		if(isset($filter['appType']) && strtoupper($filter['appType'])=='CM'){
			$sql .= " AND dm.`category` = 'COAL' ";
		}
		
		$sql .= " AND `f_cnf_fob` IS NOT NULL";
		$sql .= ")";

		//$sql .= " ORDER BY `trade_type` DESC, `f_cnf_fob` ASC, `country` ASC";

		if(isset($filter['row_ids']) && !empty($filter['row_ids'])) {
			$row_ids = array();
			$trade_types = array();

			foreach($filter['row_ids'] as $row_id) {
				$row_ids[] = $row_id['id'];
				$trade_types[] = $row_id['trade_type'];
			}

			$sql .= " ORDER BY";
			$sql .= " FIELD(`id`, " . implode(", ", $row_ids) . ")";
			$sql .= ", FIELD(`f_cnf_fob`, '" . implode("', '", $trade_types) . "')";
		} else {
			$sql .= " ORDER BY";
			$sql .= " `sticky_header_value` ASC";
			$sql .= ", CASE";
			$sql .= " WHEN `f_cnf_fob` = 'cnf' THEN 1";
			$sql .= " WHEN `f_cnf_fob` = 'fob' THEN 2";
			$sql .= " WHEN `f_cnf_fob` = 'f' THEN 3";
			$sql .= " ELSE 999";
			$sql .= " END";
			$sql .= ", `country` ASC";
		}

		if (isset($filter['start']) || isset($filter['limit'])) {
			if ($filter['start'] < 0) {
				$filter['start'] = 0;
			}

			if ($filter['limit'] < 1) {
				$filter['limit'] = 5;
			}

			$sql .= " LIMIT " . (int)$filter['start'] . "," . (int)$filter['limit'];
		}

		if(isset($filter['debug']) && $filter['debug'] == true) {
			echo "<pre>", print_r($filter), "</pre>";
			echo $sql;
		}

		$results = mysql_query($sql) or die("Error: " . mysql_error());

		if(mysql_num_rows($results) > 0) {
			while($result = mysql_fetch_assoc($results)) {

				$row = array();

				$data[] = formatResultSet($result, $row, $userDetails, $filter);

			}
		}

		return $data;
	}

	function formatResultSet($result, $row = array(), $userDetails = array(), $filter = array())
	{
		$data = array();

		$assessments = [
			'D'	=>	'Daily',
			'W'	=>	'Weekly',
			'M'	=>	'Monthly'
		];

		$loginText = 'Login';
		$upgradeText = 'Upgrade';

		$userType = 0;
		if(isset($userDetails['userType'])) {
			$userType = $userDetails['userType'];
		}

		$maxCount = 0;
		if(isset($userDetails['maxCount'])) {
			$maxCount = $userDetails['maxCount'];
		}

		$id = 0;
		$tabs = getTabsForMobileApp($result['sub_category'], $result['trade_type']);
		if($row) {
			$id = $row['id'];			
		}

		$top_flag = 'india';
		if(isset($result['trade_type']) && $result['trade_type'] == 'E_I') {
			$top_flag = 'global';
		}
		
		
		$price = '';
		$_1w = '';
		$_1m = '';
		$_3m = '';
		$change = '';
		$portfolio = $result['portfolio'];

		if($maxCount == -1) {

			if(isset($filter['src']) && $filter['src'] == 'APP') {

				if($result['f_cnf_fob'] == 'f') { // if domestic perpend Rupee Symbol (\u20B9)
					$price = "₹";
					$_1w = "₹";
					$_1m = "₹";
					$_3m = "₹";
				}
				$price .= strip_tags($result['price']);
				$_1w .= strip_tags($result['1W']);
				$_1m .= strip_tags($result['1M']);
				$_3m .= strip_tags($result['3M']);
				$min_price .= strip_tags($result['min_price']);
				$max_price .= strip_tags($result['max_price']);
				
				$change = str_replace("&nbsp;", "", strip_tags(trim(preg_replace("/\r|\n/", "", $result['change']))));

			} else {
				$price = $result['price'];			
				$_1w = $result['1W'];
				$_1m = $result['1M'];
				$_3m = $result['3M'];
				$min_price .= $result['min_price'];
				$max_price .= $result['max_price'];
				$change = trim(preg_replace("/\r|\n/", "", $result['change']));
			}
		} else if($maxCount == 0) {
			$price = $upgradeText;
			$change = $upgradeText;
			$_1w = $upgradeText;
			$_1m = $upgradeText;
			$_3m = $upgradeText;
			$min_price = $upgradeText;
			$max_price = $upgradeText;
		} else {
			$price = $loginText;
			$change = $loginText;
			$_1w = $loginText;
			$_1m = $loginText;
			$_3m = $loginText;
			$min_price = $loginText;
			$max_price = $loginText;
			$portfolio = $loginText;			
		}
		
		if($result['f_cnf_fob'] == 'f' && isset($filter['src']) && $filter['src'] == 'APP' ){
			$p = $price;
			$p1W = $_1w;
			$p1M = $_1m;
			$p3M = $_3m;
			$min_price = $min_price;
			$max_price = $max_price;
		} else {
			$p = utf8_encode($price);
			$p1W = utf8_encode($_1w);
			$p1M = utf8_encode($_1m);
			$p3M = utf8_encode($_3m);
			$min_price = utf8_encode($min_price);
			$max_price = utf8_encode($max_price);
		}
		
		$details = array(
			'One Week Ago'			=>	$p1W,
			'One Month Ago'			=>	$p1M,
			'Three Month Ago'		=>	$p3M,
			'Min Price'				=>	$min_price,
			'Max Price'				=>	$max_price,
			'Sentiments'			=>	$result['sentiments'] ? $result['sentiments'] : 'NA',
			'Assessment'			=>	isset($assessments[$result['assessment']]) ? $assessments[$result['assessment']] : '',
			'Remarks'				=>	$result['remarks'],
			'Currency'				=>	$result['currency'],
			'Disclaimer'			=>	($result['id']==1048)?'Indicative Price, including freight around INR 500/MT':'Indicative Price'
		);

		if("logistics" == strtolower($result['category'])) {
			$details['Load Port'] = $result['load_port'];
			$details['Unload Port'] = $result['unload_port'];
		} else {
			$details['Chemical Composition'] = $result['chemical_composition'];
			$details['Payment'] = $result['payment'];
			$details['Quantity'] = $result['quantity'];
			$details['Taxes'] = $result['taxes'];
			$details['Delivery Terms'] = $result['delivery_terms'];
			$details['Delivery Period'] = $result['delivery_period'];
		}

		$category = $result['category'];

		/*if(strtoupper(str_replace("_", " ", $category)) == 'FINISH FLAT' || strtoupper(str_replace("_", " ", $category)) == 'FINISH LONG') {
			$category = 'FINISH STEEL';
		}*/

		$allCaps = array(
			'hrc', 'crc', 'gp'
		);
		
		if(strtolower($result['sub_category']) == "tmt") {
			$sub_category = "Rebar";
		} else if(in_array(strtolower($result['sub_category']), $allCaps)) {
			$sub_category = strtoupper($result['sub_category']);
		} else if(str_replace("_"," ", strtolower( $result['sub_category'])) == 'hr plate') {
			$sub_category = 'HR Plate';
		} else {
			$sub_category = ucwords(str_replace("_"," ", strtolower( $result['sub_category'])));

		}

		$sticky_header_value = strtolower($result['sticky_header_value']);
		if($sticky_header_value == 'united states') {
			$sticky_header_value = 'USA';
		}
		
		$data = array(
			'id'					=>	$id,
			'sub_id'				=>	$result['id'],
			'category'				=>	$category,
			'sub_category'			=>	$sub_category,
			'tabs'					=>	$tabs,
			'price'					=>	$p,
			'change'				=>	utf8_encode($change),
			'size'					=>	$result['size_grade'],
			'Grade'					=>	$result['origin'],
			'grade'					=>	$result['origin'],
			'delivery'				=>	$result['particular'],
			'top_flag'				=>	$top_flag,
			'graph_id'				=>	$result['id'],
			'country'				=>	$result['country'],
			'coordinates'			=>	$result['lat_long'],
			'flag_code'				=>	strtolower($result['flag']),
			'trade_type'			=>	$result['trade_type'],
			'date'					=>	date('Y-m-d H:i:s', strtotime($result['date_time'])),
			'date'					=>	(date('m')== '01' && date('m', strtotime($result['date_time']))==12)?date('Y')-1 .'-'. date('m-d H:i:s', strtotime($result['date_time'])):date('Y-m-d H:i:s', strtotime($result['date_time'])),
			'portfolio'				=>	$portfolio,
			'f_price_column'		=>	$result['f_cnf_fob'],
			'assesment'				=>	$result['assessment'],
			'details'				=>	$details,
			'graph_view'			=>	$userType == 1 ? true : false, 
			'sticky_header_value'	=>	ucwords($sticky_header_value),
			'cond_cols_val_block'	=>	isset($row['cond_cols_val_block']) ? $row['cond_cols_val_block'] : '',
			'show_in_app_mapview'	=>	(isset($result['show_in_app_mapview']) && !empty($result['lat_long'])) ? $result['show_in_app_mapview'] : 0
		);

		return $data;
	}
	
	function getGraphDataForId($memid=0, $relation_id='',$price_type_prefix='f', $assesment = 'W',$appType='SM')
	{
	    require_once("/home/steelmin/public_html/inc/config1.php");
	    require_once("/home/steelmin/public_html/hf.php");
	    $f_date_cond = '';
		
		$isTMT = qryToCommaSepString ( "
							SELECT id
							FROM `tbl_dom_master`
							WHERE sub_category in( 'Rebar', 'TMT') and supplier='Secondary'
							and id = $relation_id
						");

		$other_cond = '';
		if( $isTMT != '' )
			$other_cond = ' AND cnf_price = "12MM" ';
		
	    $temArr = getUserServices($memid);
	    $userService = $temArr['W']['userService'];
		IF(ISSET($temArr['MA']['userService']))
			 $userService = $temArr['MA']['userService'];
		
		IF($appType=='CM' && ISSET($temArr['C']['userService']))
			 $userService = $temArr['C']['userService'];
		 
	    if($userService == 'Free Trial') {
	        $f_date_cond = date("Y-m-d",strtotime($GLOBALS['currDate'] . '-3 months'));
	        $other_cond .= " AND f_date >= '$f_date_cond'";
	    }

	    $f_price = $price_type_prefix . "_price";
		
		$extra_columns='';
		//if('E_I'==$trade_type) {
		if($relation_id < 1000) {
			$extra_columns = ",sentiments, chart_comments";
		}
	    if($assesment == 'M') {
	        $sql = "SELECT
					CONCAT(YEAR(f_date),'-',MONTH(f_date),'-', 01) AS final_date,
					AVG($f_price) as final_price $extra_columns
					FROM tbl_steelprices
					WHERE relation_id = ".$relation_id."
					$other_cond
					GROUP BY  final_date order by f_date ASC
					";
	    } else if($assesment == 'W') {
	    	$isLogistics = false;
	    	$isLogistics = qryToCommaSepString ( "
							SELECT id
							FROM `tbl_exim_master`
							WHERE LCASE(sub_category) = 'logistics'
							and id = $relation_id
						");

	    	if($isLogistics) {
		        $sql = "SELECT (
		                    SELECT DATE_ADD(monday, INTERVAL 4 DAY)
		                    FROM tbl_year_week
		                    WHERE f_date >= DATE_ADD(monday, INTERVAL 4 DAY)
		                    AND f_date <= DATE_ADD(sunday, INTERVAL 4 DAY)
		                    ) AS final_date,
		                AVG($f_price) as final_price $extra_columns
		                FROM tbl_steelprices
		                WHERE relation_id = ".$relation_id."
		                $other_cond
		                GROUP BY  final_date
		                order by final_date asc
		                ";/*GROUP BY YEAR(f_date),  final_date
		                order by YEAR(f_date), final_date asc*/
		    } else {
		    	$sql = "SELECT (
		                    SELECT monday
		                    FROM tbl_year_week
		                    WHERE f_date >= monday
		                    AND f_date <= sunday
		                    ) AS final_date,
		                AVG($f_price) as final_price $extra_columns
		                FROM tbl_steelprices
		                WHERE relation_id = ".$relation_id."
		                $other_cond
		                GROUP BY  final_date
		                order by final_date asc
						
		                ";/*GROUP BY YEAR(f_date),  final_date
		                order by YEAR(f_date), final_date asc*/
		    }

	        //SELECT MAX(monday) FROM tbl_year_week WHERE DATE_ADD(f_date, INTERVAL 7 DAY) >= monday AND f_date <= sunday
	    } else {
	        $sql = "SELECT f_date as final_date,
					$f_price as final_price $extra_columns
					FROM tbl_steelprices
					WHERE relation_id = ".$relation_id."
					$other_cond
					Group by f_date asc";
	    }
		
		if(isset($_REQUEST['debug'])){
			echo 'getGraphDataForId:'.$sql;
		}
		
	    $result = mysql_query($sql) or die(mysql_error());

	    $_rs_array=array();
	    while($row = mysql_fetch_array( $result)) {
	        if(strtotime($row['final_date'])) {
	            $_rs_array[] = array(strtotime($row['final_date']), round($row['final_price'] , 2),  html_entity_decode($row['chart_comments']));
	        }
	    }
	    require_once("/home/steelmin/public_html/inc/desconfig.php");
	    return $_rs_array;
	}

?>