Bonjour,
Je suis en train de contruire un nouvel export avec un champ calculé, mais j’ai trouvé dans le code de la classe Export ce que je pense être un bug :
htdocs/exports/class/export.class.php : (line 207)
function build_sql($indice, $array_selected, $array_filterValue)
{
// Build the sql request
$sql=$this-\>array_export_sql_start[$indice];
$i=0;
//print_r($array_selected);
foreach ($this-\>array_export_fields[$indice] as $key =\> $value)
{
if (! array_key_exists($key, $array_selected)) continue; // Field not selected
if ($i \> 0) $sql.=', ';
else $i++;
$newfield=$key.' as '.str_replace(array('.', '-'),'_',$key);;
$sql.=$newfield;
}
$sql.=$this-\>array_export_sql_end[$indice];
//construction du filtrage si le parametrage existe
if (is_array($array_filterValue))
{
$sqlWhere='';
// pour ne pas a gerer le nombre de condition
foreach ($array_filterValue as $key =\> $value)
{
$sqlWhere.=" and ".$this-\>build_filterQuery($this-\>array_export_TypeFields[0][$key], $key, $array_filterValue[$key]);
}
$sql.=$sqlWhere;
}
L’ajout du champ se fait uniquement avec la variable $key (et pas $value qui pourrait être l’alias SQL non ???).
Ceci se traduit pas l’impossibilité de mettre un champ calculé (erreur SQL), car alors dans le SQL généré nous avons : ifnull(…) as ifnull(…)
Exemple avec ma requête :
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(concat(u_firstname, ' ', u_name),concat(u2_firstname, ' ', u2_name)), c.rowid a' at line 1 \- sql=SELECT DISTINCT s.rowid as s_rowid, s.nom as s_nom, s.address as s_address, s.cp as s_cp, s.ville as s_ville, cp.code as cp_code, s.tel as s_tel, ifnull(concat(u.firstname, ' ', u.name),concat(u2.firstname, ' ', u2.name)) as ifnull(concat(u_firstname, ' ', u_name),concat(u2_firstname, ' ', u2_name)), c.rowid as c_rowid, c.ref as c_ref, c.ref_client as c_ref_client, c.fk_soc as c_fk_soc, c.datec as c_datec, c.datep as c_datep, c.fin_validite as c_fin_validite, c.remise_percent as c_remise_percent, c.total_ht as c_total_ht, c.total as c_total, c.fk_statut as c_fk_statut, c.note as c_note, c.date_livraison as c_date_livraison, pcf.avancement as pcf_avancement, pcf.commande as pcf_commande, pcf.statut as pcf_statut, pcf.version as pcf_version FROM llx_societe as s LEFT JOIN llx_c_pays as cp ON s.fk_pays = cp.rowid LEFT JOIN llx_societe_commerciaux as sc ON s.rowid = sc.fk_user LEFT JOIN llx_user as u ON sc.fk_user = u.rowid, llx_propal as c LEFT JOIN llx_propal_customfields as pcf ON c.rowid = pcf.fk_propal, llx_user as u2 WHERE c.fk_soc = s.rowid AND c.fk_user_author = u2.rowid AND c.entity = 1 and date_format(c.datep,'%Y') = 2013
où l’on voit que le champ calculé devient
ifnull(concat(u.firstname, ' ', u.name),concat(u2.firstname, ' ', u2.name)) AS ifnull(concat(u_firstname, ' ', u_name),concat(u2_firstname, ' ', u2_name))
Cyril.