[Dolibarr-dev] Patch compatibilité Mysql 2.3

[Raphaël Bertrand (Résultic)]

Bonjour,

Voici un patch remplaçant les 3 requêtes comportant encore du UNION par 
du code php équivalent, afin de les rendre compatible avec Mysql 2.3.

--
*Raphaël Bertrand*
Résultic - Management & Informatique
Siège : 11 rue Tronchet - 69006 LYON
Bureaux : 11 pl Maréchal Lyautey - 69006 LYON
Fixe. 08 74 77 00 70
Fax. 08 25 24 85 02
E-Mail : address@hidden

Index: comm/remx.php
===================================================================
RCS file: /sources/dolibarr/dolibarr/htdocs/comm/remx.php,v
retrieving revision 1.24
diff -u -r1.24 remx.php
--- comm/remx.php       29 Jan 2008 19:03:29 -0000      1.24
+++ comm/remx.php       20 Jun 2008 13:19:13 -0000
@@ -1,6 +1,7 @@
 <?PHP
 /* Copyright (C) 2001-2004 Rodolphe Quiedeville <address@hidden>
  * Copyright (C) 2004-2007 Laurent Destailleur  <address@hidden>
+ * Copyright (C) 2008 Raphael Bertrand (Resultic)   <address@hidden>
  *
  * This program is free software; you can redistribute it and/or modify
  * it under the terms of the GNU General Public License as published by
@@ -249,7 +250,7 @@
      * Liste ristournes appliquées (=liees a une ligne de facture ou facture)
      */
     // Remises liees a lignes de factures
-    $sql = "(SELECT rc.rowid, rc.amount_ht, rc.amount_tva, rc.amount_ttc, 
rc.tva_tx,";
+    $sql = "SELECT rc.rowid, rc.amount_ht, rc.amount_tva, rc.amount_ttc, 
rc.tva_tx,";
        $sql.= $db->pdate("rc.datec")." as dc, rc.description, 
rc.fk_facture_line, rc.fk_facture,";
     $sql.= " rc.fk_facture_source,";
        $sql.= " u.login, u.rowid as user_id,";
@@ -263,28 +264,31 @@
     $sql.= " WHERE rc.fk_soc =". $objsoc->id;
     $sql.= " AND rc.fk_facture_line = fc.rowid";
     $sql.= " AND fc.fk_facture = f.rowid";
-    $sql.= " AND rc.fk_user = u.rowid)";
-       $sql.= " UNION ";
+    $sql.= " AND rc.fk_user = u.rowid";
+       $sql.= " ORDER BY dc DESC";
+       //$sql.= " UNION ";
     // Remises liees a factures
-       $sql.= "(SELECT rc.rowid, rc.amount_ht, rc.amount_tva, rc.amount_ttc, 
rc.tva_tx,";
-       $sql.= $db->pdate("rc.datec")." as dc, rc.description, 
rc.fk_facture_line, rc.fk_facture,";
-    $sql.= " rc.fk_facture_source,";
-       $sql.= " u.login, u.rowid as user_id,";
-    $sql.= " f.rowid, f.facnumber,";
-       $sql.= " fa.facnumber as ref, fa.type as type";
-    $sql.= " FROM ".MAIN_DB_PREFIX."facture as f";
-    $sql.= " , ".MAIN_DB_PREFIX."user as u";
-    $sql.= " , ".MAIN_DB_PREFIX."societe_remise_except as rc";
-    $sql.= " LEFT JOIN ".MAIN_DB_PREFIX."facture as fa ON rc.fk_facture_source 
= fa.rowid";
-    $sql.= " WHERE rc.fk_soc =". $objsoc->id;
-    $sql.= " AND rc.fk_facture = f.rowid";
-    $sql.= " AND rc.fk_user = u.rowid)";
-
-    $sql.= " ORDER BY dc DESC";
+       $sql2= "SELECT rc.rowid, rc.amount_ht, rc.amount_tva, rc.amount_ttc, 
rc.tva_tx,";
+       $sql2.= $db->pdate("rc.datec")." as dc, rc.description, 
rc.fk_facture_line, rc.fk_facture,";
+    $sql2.= " rc.fk_facture_source,";
+       $sql2.= " u.login, u.rowid as user_id,";
+    $sql2.= " f.rowid, f.facnumber,";
+       $sql2.= " fa.facnumber as ref, fa.type as type";
+    $sql2.= " FROM ".MAIN_DB_PREFIX."facture as f";
+    $sql2.= " , ".MAIN_DB_PREFIX."user as u";
+    $sql2.= " , ".MAIN_DB_PREFIX."societe_remise_except as rc";
+    $sql2.= " LEFT JOIN ".MAIN_DB_PREFIX."facture as fa ON 
rc.fk_facture_source = fa.rowid";
+    $sql2.= " WHERE rc.fk_soc =". $objsoc->id;
+    $sql2.= " AND rc.fk_facture = f.rowid";
+    $sql2.= " AND rc.fk_user = u.rowid";
+       
+       $sql2.= " ORDER BY dc DESC";
 
     $resql=$db->query($sql);
-    if ($resql)
-    {
+       $resql2=null;
+    if ($resql) $resql2=$db->query($sql2);
+       if ($resql2)
+    { 
         print_titre($langs->trans("DiscountAlreadyCounted"));
         print '<table class="noborder" width="100%">';
         print '<tr class="liste_titre"><td 
width="120">'.$langs->trans("Date").'</td>';
@@ -298,11 +302,32 @@
         print '</tr>';
 
         $var = true;
-        $i = 0 ;
+               $tab_sqlobj=array();
+               $tab_sqlobjOrder=array();
         $num = $db->num_rows($resql);
+               for ($i = 0;$i < $num;$i++)
+                       {
+                       $sqlobj = $db->fetch_object($resql);
+                       $tab_sqlobj[] = $sqlobj;
+                       $tab_sqlobjOrder[]=$sqlobj->dc;
+                       }
+               $db->free($resql);      
+               
+               $num = $db->num_rows($resql2);
+               for ($i = 0;$i < $num;$i++)
+                       {
+                       $sqlobj = $db->fetch_object($resql2);
+                       $tab_sqlobj[] = $sqlobj;
+                       $tab_sqlobjOrder[]= $sqlobj->dc;
+                       }
+               $db->free($resql2);
+               array_multisort ($tab_sqlobjOrder,SORT_DESC,$tab_sqlobj);
+               
+               $num = sizeOf($tab_sqlobj);
+               $i = 0 ;
         while ($i < $num )
         {
-            $obj = $db->fetch_object($resql);
+            $obj = array_shift($tab_sqlobj);
             $var = !$var;
             print "<tr $bc[$var]>";
             print '<td>'.dolibarr_print_date($obj->dc,'dayhour').'</td>';
@@ -330,7 +355,6 @@
             print '</tr>';
             $i++;
         }
-        $db->free($resql);
         print "</table>";
     }
     else
@@ -343,4 +367,4 @@
 $db->close();
 
 llxFooter('$Date: 2008/01/29 19:03:29 $ - $Revision: 1.24 $');
-?>
+?>
\ No newline at end of file
Index: compta/bank/treso.php
===================================================================
RCS file: /sources/dolibarr/dolibarr/htdocs/compta/bank/treso.php,v
retrieving revision 1.12
diff -u -r1.12 treso.php
--- compta/bank/treso.php       12 Feb 2008 01:33:44 -0000      1.12
+++ compta/bank/treso.php       20 Jun 2008 13:19:43 -0000
@@ -1,5 +1,6 @@
 <?php
 /* Copytight (C) 2005-2007 Regis Houssin        <address@hidden>
+ * Copyright (C) 2008 Raphael Bertrand (Resultic)   <address@hidden>
  *
  * This program is free software; you can redistribute it and/or modify
  * it under the terms of the GNU General Public License as published by
@@ -150,25 +151,64 @@
        $sql.= " FROM ".MAIN_DB_PREFIX."facture as f";
        $sql.= " LEFT JOIN ".MAIN_DB_PREFIX."societe as s ON f.fk_soc = 
s.rowid";
        $sql.= " WHERE f.paye = 0 AND fk_statut = 1";
-       $sql.= " UNION DISTINCT";
-       $sql.= " SELECT ff.rowid as facid, ff.facnumber, (-1*ff.total_ttc), 
ff.type, ".$db->pdate("ff.date_lim_reglement")." as dlr,";
-       $sql.= " s.rowid as socid, s.nom, s.fournisseur";
-       $sql.= " FROM ".MAIN_DB_PREFIX."facture_fourn as ff";
-       $sql.= " LEFT JOIN ".MAIN_DB_PREFIX."societe as s ON ff.fk_soc = 
s.rowid";
-       $sql.= " WHERE ff.paye = 0 AND fk_statut = 1";
        $sql.= " ORDER BY dlr ASC";
+       //$sql.= " UNION DISTINCT";
+       $sql2= " SELECT ff.rowid as facid, ff.facnumber, (-1*ff.total_ttc), 
ff.type, ".$db->pdate("ff.date_lim_reglement")." as dlr,";
+       $sql2.= " s.rowid as socid, s.nom, s.fournisseur";
+       $sql2.= " FROM ".MAIN_DB_PREFIX."facture_fourn as ff";
+       $sql2.= " LEFT JOIN ".MAIN_DB_PREFIX."societe as s ON ff.fk_soc = 
s.rowid";
+       $sql2.= " WHERE ff.paye = 0 AND fk_statut = 1";
+       $sql2.= " ORDER BY dlr ASC";
+       
 
        $result = $db->query($sql);
-       if ($result)
+       $result2=false;
+       if ($result) 
        {
-               $var=False;
+               $result2=$db->query($sql2);
+       }
+       if ($result2)
+       {
+               $tab_sqlobj=array();
                $num = $db->num_rows($result);
+               for ($i = 0;$i < $num;$i++)
+                       {
+                       $sqlobj = $db->fetch_object($result);
+                       $tab_sqlobj[] = $sqlobj;
+                       $tab_sqlobjOrder[]= $sqlobj->dlr;
+                       }
+               $db->free($result);     
+               
+               $num = $db->num_rows($result2);
+               for ($i = 0;$i < $num;$i++)
+                       {
+                       $sqlobj = $db->fetch_object($result2);
+                       $tab_sqlobj[] = $sqlobj;
+                       $tab_sqlobjOrder[]= $sqlobj->dlr;
+                       }
+               $db->free($result2);
+               array_multisort ($tab_sqlobjOrder,$tab_sqlobj);
+               
+               //Apply distinct filter
+               foreach ($tab_sqlobj as $key=>$value) { 
+                 $tab_sqlobj[$key] = "'" . serialize($value) . "'"; 
+               } 
+               $tab_sqlobj = array_unique($tab_sqlobj); 
+               foreach ($tab_sqlobj as $key=>$value) { 
+                 $tab_sqlobj[$key] = unserialize(trim($value, "'")); 
+               } 
+               
+               $num = sizeOf($tab_sqlobj);
+       
+               $var=False;
+               //$num = $db->num_rows($result);
                $i = 0;
                while ($i < $num)
                {
                        $paiement = '';
                        $var=!$var;
-                       $obj = $db->fetch_object($result);
+                       //$obj = $db->fetch_object($result);
+                       $obj = array_shift($tab_sqlobj);
                        
                        $societestatic->id = $obj->socid;
                        $societestatic->nom = $obj->nom;
@@ -225,7 +265,7 @@
                        print "</tr>";
                        $i++;
                }
-               $db->free($result);
+               //$db->free($result);
        }
        else
        {
Index: compta/propal.php
===================================================================
RCS file: /sources/dolibarr/dolibarr/htdocs/compta/propal.php,v
retrieving revision 1.137
diff -u -r1.137 propal.php
--- compta/propal.php   25 Apr 2008 04:35:01 -0000      1.137
+++ compta/propal.php   20 Jun 2008 13:19:19 -0000
@@ -3,6 +3,7 @@
  * Copyright (C) 2004-2008 Laurent Destailleur  <address@hidden>
  * Copyright (C) 2004      Eric Seigne          <address@hidden>
  * Copyright (C) 2005-2007 Regis Houssin        <address@hidden>
+ * Copyright (C) 2008 Raphael Bertrand (Resultic)   <address@hidden>
  *
  * This program is free software; you can redistribute it and/or modify
  * it under the terms of the GNU General Public License as published by
@@ -466,7 +467,6 @@
     
        if ($propal->statut <> 4 && $user->societe_id == 0)
        {
-
                if ($propal->statut == 2 && $user->rights->facture->creer)
                {
                        print '<a class="butAction" 
href="facture.php?propalid='.$propal->id."&action=create&socid=$socid&viewstatut=$viewstatut&sortfield=$sortfield&$sortorder\">".$langs->trans("BuildBill")."</a>";
@@ -543,18 +543,45 @@
        $sql.= " FROM ".MAIN_DB_PREFIX."facture as f";
        $sql.= ", ".MAIN_DB_PREFIX."fa_pr as fp";
        $sql.= " WHERE fp.fk_facture = f.rowid AND fp.fk_propal = ".$propal->id;
-       $sql.= " UNION ";
+       //$sql.= " UNION ";
        // Cas des factures lier via la commande
-       $sql.= "SELECT f.facnumber, f.total,".$db->pdate("f.datef")." as df, 
f.rowid as facid, f.fk_user_author, f.fk_statut, f.paye";
-       $sql.= " FROM ".MAIN_DB_PREFIX."facture as f";
-       $sql.= ", ".MAIN_DB_PREFIX."co_pr as cp, ".MAIN_DB_PREFIX."co_fa as cf";
-       $sql.= " WHERE cp.fk_propale = ".$propal->id." AND cf.fk_commande = 
cp.fk_commande AND cf.fk_facture = f.rowid";
+       $sql2= "SELECT f.facnumber, f.total,".$db->pdate("f.datef")." as df, 
f.rowid as facid, f.fk_user_author, f.fk_statut, f.paye";
+       $sql2.= " FROM ".MAIN_DB_PREFIX."facture as f";
+       $sql2.= ", ".MAIN_DB_PREFIX."co_pr as cp, ".MAIN_DB_PREFIX."co_fa as 
cf";
+       $sql2.= " WHERE cp.fk_propale = ".$propal->id." AND cf.fk_commande = 
cp.fk_commande AND cf.fk_facture = f.rowid";
 
        dolibarr_syslog("propal.php::liste factures sql=".$sql);
-       $resql = $db->query($sql);
-       if ($resql)
+    $resql=$db->query($sql);
+       $resql2=null;
+    if ($resql) 
+               {
+                       dolibarr_syslog("propal.php::liste factures 
sql2=".$sql2);
+                       $resql2=$db->query($sql2);
+               }
+       if ($resql2)
     {
-               $num_fac_asso = $db->num_rows($resql);
+               $tab_sqlobj=array();
+        $num_fac_asso = $db->num_rows($resql);
+               for ($i = 0;$i < $num_fac_asso;$i++)
+                       {
+                       $sqlobj = $db->fetch_object($resql);
+                       $tab_sqlobj[] = $sqlobj;
+                       //$tab_sqlobjOrder[]= $sqlobj->dc;
+                       }
+               $db->free($resql);      
+               
+               $num_fac_asso = $db->num_rows($resql2);
+               for ($i = 0;$i < $num_fac_asso;$i++)
+                       {
+                       $sqlobj = $db->fetch_object($resql2);
+                       $tab_sqlobj[] = $sqlobj;
+                       //$tab_sqlobjOrder[]= $sqlobj->dc;
+                       }
+               $db->free($resql2);
+               //array_multisort ($tab_sqlobjOrder,$tab_sqlobj);
+               
+               $num_fac_asso = sizeOf($tab_sqlobj);
+               //$num_fac_asso = $db->num_rows($resql);
                $i = 0; $total = 0;
                if ($somethingshown) { print '<br>'; $somethingshown=1; }
                if ($num_fac_asso > 1) 
print_titre($langs->trans("RelatedBills"));
@@ -573,7 +600,8 @@
                $var=True;
                while ($i < $num_fac_asso)
                {
-                       $objp = $db->fetch_object($resql);
+                       //$objp = $db->fetch_object($resql);
+                       $objp = array_shift($tab_sqlobj);
                        $var=!$var;
                        print "<tr $bc[$var]>";
                        print '<td><a 
href="../compta/facture.php?facid='.$objp->facid.'">'.img_object($langs->trans("ShowBill"),"bill").'
 '.$objp->facnumber.'</a></td>';
@@ -588,7 +616,7 @@
                print "<td align=\"right\">".price($total)."</td>";
                print "<td>&nbsp;</td></tr>\n";
                print "</table>";
-               $db->free();
+               //$db->free();
        }
 
 
Index: propal.class.php
===================================================================
RCS file: /sources/dolibarr/dolibarr/htdocs/propal.class.php,v
retrieving revision 1.240
diff -u -r1.240 propal.class.php
--- propal.class.php    1 May 2008 18:35:55 -0000       1.240
+++ propal.class.php    20 Jun 2008 13:19:30 -0000
@@ -5,6 +5,7 @@
  * Copyright (C) 2005      Marc Barilley / Ocebo <address@hidden>
  * Copyright (C) 2005-2007 Regis Houssin         <address@hidden>
  * Copyright (C) 2006      Andre Cianfarani      <address@hidden>
+ * Copyright (C) 2008 Raphael Bertrand (Resultic)   <address@hidden>
  *
  * This program is free software; you can redistribute it and/or modify
  * it under the terms of the GNU General Public License as published by
@@ -1337,27 +1338,56 @@
                $sql.= " FROM ".MAIN_DB_PREFIX."facture as f";
                $sql.= ", ".MAIN_DB_PREFIX."fa_pr as fp";
                $sql.= " WHERE fp.fk_facture = f.rowid AND fp.fk_propal = ".$id;
-               $sql.= " UNION ";
-               // Cas des factures lier via la commande
-               $sql.= "SELECT f.rowid, f.facnumber";
-               $sql.= " FROM ".MAIN_DB_PREFIX."facture as f";
-               $sql.= ", ".MAIN_DB_PREFIX."co_pr as cp, 
".MAIN_DB_PREFIX."co_fa as cf";
-               $sql.= " WHERE cp.fk_propale = ".$id." AND cf.fk_commande = 
cp.fk_commande AND cf.fk_facture = f.rowid";
+               //$sql.= " UNION ";
+               // Cas des factures liees via la commande
+               $sql2= "SELECT f.rowid, f.facnumber";
+               $sql2.= " FROM ".MAIN_DB_PREFIX."facture as f";
+               $sql2.= ", ".MAIN_DB_PREFIX."co_pr as cp, 
".MAIN_DB_PREFIX."co_fa as cf";
+               $sql2.= " WHERE cp.fk_propale = ".$id." AND cf.fk_commande = 
cp.fk_commande AND cf.fk_facture = f.rowid";
 
         //$sql = "SELECT fk_facture FROM ".MAIN_DB_PREFIX."fa_pr as fp";
         //$sql .= " WHERE fk_propal = " . $id;
         dolibarr_syslog("Propal::InvoiceArrayList sql=".$sql);
                $resql=$this->db->query($sql);
-               if ($resql)
+               $resql2=null;
+           if ($resql) 
+               {
+                       dolibarr_syslog("Propal::InvoiceArrayList sql2=".$sql2);
+                       $resql2=$this->db->query($sql2);
+               }
+               if ($resql2)
         {
-            $nump = $this->db->num_rows($resql);
-
+                       $tab_sqlobj=array();
+                       $nump = $this->db->num_rows($resql);
+                       for ($i = 0;$i < $nump;$i++)
+                               {
+                               $sqlobj = $this->db->fetch_object($resql);
+                               $tab_sqlobj[] = $sqlobj;
+                               //$tab_sqlobjOrder[]= $sqlobj->dc;
+                               }
+                       $this->db->free($resql);        
+                                       $nump = $this->db->num_rows($resql2);
+                                       
+                       for ($i = 0;$i < $nump;$i++)
+                               {
+                               $sqlobj = $this->db->fetch_object($resql2);
+                               $tab_sqlobj[] = $sqlobj;
+                               //$tab_sqlobjOrder[]= $sqlobj->dc;
+                               }
+                       $this->db->free($resql2);
+                       //array_multisort ($tab_sqlobjOrder,$tab_sqlobj);
+                       
+            //$nump = $this->db->num_rows($resql);
+                       $nump = sizeOf($tab_sqlobj);
+                       
             if ($nump)
             {
                 $i = 0;
                 while ($i < $nump)
                 {
-                    $obj = $this->db->fetch_object($resql);
+                    //$obj = $this->db->fetch_object($resql);
+                                       $obj = array_shift($tab_sqlobj);
+                                       
                     $ga[$obj->rowid] = $obj->facnumber;
                     $i++;
                 }