<?php

	if(!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');
	if(isset($_REQUEST['as_inputSearch']) && $_REQUEST['as_inputSearch'] != '') {
		$AS_INPUTSEARCH = strtoupper($_REQUEST['as_inputSearch']);
		
		$query = "SELECT DISTINCT";
		$query .= " `pr`.`id`";
		$query .= ", `pr`.`unit_id` as unitid";
		$query .= ", `pr`.`code`";
		$query .= ", `pr`.`name`";
		$query .= ", `pr`.`selling_price`";
		$query .= ", `pr`.`ems_price`";
		$query .= ", `pr`.`purchase_price`";
		$query .= ", `pr`.`vat_id`";
		$query .= ", `pr`.`vat_name`";
		$query .= ", `pr`.`vat_value`";
		$query .= ", `pr`.`exchange_rate_id` as `currency_id`";
		$query .= ", `pr`.`product_category_id` as `category_id`";
		$query .= ", `pr`.`usage_unit_id` as `unit_id`";
		$query .= ", `pr`.`carton_dimensions_1` as `cd1`";
		$query .= ", `pr`.`carton_dimensions_2` as `cd2`";
		$query .= ", `pr`.`carton_dimensions_3` as `cd3`";
		$query .= ", `pr`.`pieces_per_carton` as `ppc`";
		$query .= ", `pr`.`moq` as `moq`";

		 
		$query .= " FROM";
		$query .= " `ecmproducts` as `pr`";
				 
		$query .= " WHERE";
		$query .= " (";
		$query .= "UPPER(`pr`.`code`) LIKE '%$AS_INPUTSEARCH'";
		$query .= " || UPPER(`pr`.`code`) LIKE '$AS_INPUTSEARCH%'";
		$query .= " || UPPER(`pr`.`code`) LIKE '%$AS_INPUTSEARCH%'";
		$query .= " || UPPER(`pr`.`name`) LIKE '%$AS_INPUTSEARCH'";
		$query .= " || UPPER(`pr`.`name`) LIKE '$AS_INPUTSEARCH%'";
		$query .= " || UPPER(`pr`.`name`) LIKE '%$AS_INPUTSEARCH%'";			
		$query .= ")";
		$query .= " AND `pr`.`deleted`='0'";
				
		$result = $GLOBALS['db']->query($query);
		
		global $sugar_config;
		$defaultCurrency = $sugar_config['default_currency_symbol'];
		$currencies = array ( -99 => $defaultCurrency );

		$arr = array();
		if($result)
			while($row = $GLOBALS['db']->fetchByAssoc($result)) {
				$row['guid']=create_guid();			
				$row['price'] = $row['ems_price'];	
				$row['quantity']=(int)$row['moq'];
				$w=$GLOBALS['db']->query("select c.ecmproduct_id as product_id,c.ecmcomponent_id as id,c.name,c.code,c.quantity, p.product_category_id as cat, p.purchase_price as price from ecmproductcomponents as c INNER JOIN ecmproducts as p ON p.id=c.ecmcomponent_id where c.ecmproduct_id='".$row['id']."' and c.deleted='0'");
				while($r=$GLOBALS['db']->fetchByAssoc($w)){
					$r['component']=1;
					$r['guid']=$row['guid'];
					$r['component_quantity']=$r['quantity'];
					$r['category_id']=$r['cat'];
					
					$row['components'][]=$r;			
				}
				if(array_key_exists($row['currency_id'],$currencies))
					$row['currency_symbol']  = $currencies[$row['currency_id']];
				$arr[] = $row;
			}
			
		if(count($arr) > 0) { 
			$json = getJSONobj();
			echo str_replace("&quot;", '\"', $json->encode($arr));
		}
	}


?>