Need your help on an SQL query for a duplicated getCustomersOrders function (asap plz)

[Taher Alami (STAR)]

Hi all,

 

I need your help to customize the old getCustomerOrders function to a duplicated function getCustomerSells

 

I've added a custom field "customer_id" on the product table. I need now a joint sql query to select the orders that concerns a customer sold by a costumer. That means i need the same as the following but i have to show the products that was added by a customer. To help me do that all you have to know is that there is a field "customer_id" on the product table.

 

Thank you in advance.

 

I'll really appreciate your help.

 

 

 

static public function getCustomerSells($id_customer, $showHiddenStatus = false, $limit = 0)
{
 global $cookie;
 $res = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('
	SELECT o.*, (SELECT SUM(od.`product_quantity`) FROM `'._DB_PREFIX_.'order_detail` od WHERE od.`id_order` = o.`id_order`) nb_products
	FROM `'._DB_PREFIX_.'orders` o
	WHERE o.`id_customer` = '.(int)$id_customer.'
	GROUP BY o.`id_order`
	ORDER BY o.`date_add` DESC
	'.(($limit != 0) ? ' LIMIT 0, '.intval($limit) : '')
 );
 if (!$res)
  return array();
 foreach ($res AS $key => $val)
 {
  $res2 = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('
  SELECT os.`id_order_state`, osl.`name` AS order_state, os.`invoice`
  FROM `'._DB_PREFIX_.'order_history` oh
  LEFT JOIN `'._DB_PREFIX_.'order_state` os ON (os.`id_order_state` = oh.`id_order_state`)
  INNER JOIN `'._DB_PREFIX_.'order_state_lang` osl ON (os.`id_order_state` = osl.`id_order_state` AND osl.`id_lang` = '.(int)($cookie->id_lang).')
  WHERE oh.`id_order` = '.(int)($val['id_order']).(!$showHiddenStatus ? ' AND os.`hidden` != 1' : '').'
  ORDER BY oh.`date_add` DESC, oh.`id_order_history` DESC
  LIMIT 1');
  if ($res2)
$res[$key] = array_merge($res[$key], $res2[0]);
 }
 return $res;
}

 

This code, once modified will help me have a page, mysells.php that will show as the same view as history.php the products that a customer have sold.