Приведенный ниже запрос отлично работает, когда я делаю это на сервере MySQL, но во время работы в Prestashop выдает ошибку «подзапрос возвращает более одной строки», выделенный жирным шрифтом.
$sql='SELECT g.`id_group`,gl.`name`,oh.`date_add`,o.`id_order`,o.`invoice_number`,FORMAT(o.`total_paid`,2) AS total_paid,osl.`name` AS order_status,o.`credit_days`,DATEDIFF(DATE_ADD((**SELECT oha.`date_add` FROM '._DB_PREFIX_.'order_history oha WHERE o.`id_order` = oha.`id_order` AND oha.`id_order_state`=40**) ,INTERVAL o.`credit_days` DAY),NOW()) AS aging
FROM '._DB_PREFIX_.'orders o
LEFT JOIN '._DB_PREFIX_.'order_history oh ON (o.`id_order` = oh.`id_order`)
LEFT JOIN '._DB_PREFIX_.'order_state_lang osl ON (osl.`id_order_state` = oh.`id_order_state`)
LEFT JOIN '._DB_PREFIX_.'customer c ON(c.`id_customer` = o.`id_customer`)
LEFT JOIN '._DB_PREFIX_.'group g ON(g.`id_group`=c.`id_default_group`)
LEFT JOIN '._DB_PREFIX_.'group_lang gl ON(gl.`id_group`=g.`id_group`)
WHERE
oh.id_Order_history IN (
SELECT MAX(oha.`id_order_history`) FROM '._DB_PREFIX_.'order_history AS oha
WHERE o.id_order = oha.id_order GROUP BY oha.id_order
)
AND oh.`id_order_state` IN (35,37,39)
AND gl.`id_lang`=1
AND osl.`id_lang`=1';
if($id_group)
{
$sql .=" AND g.`id_group`=".$id_group;
}
if($from & $to)
{
$to.=" 23:59:59";
$sql .=" AND oh.`date_add` BETWEEN '".$from."' AND '".$to."'";
}
$result=Db::getInstance()->ExecuteS($sql);
Задача ещё не решена.
Других решений пока нет …