WebSVN - OpenConcerto - Diff - Rev 151 and 156 - /trunk/OpenConcerto/src/org/openconcerto/erp/config/update/Updater_1

 /*
  * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
+ *
  * Copyright 2011 OpenConcerto, by ILM Informatique. All rights reserved.
+ *
  * The contents of this file are subject to the terms of the GNU General Public License Version 3
  * only ("GPL"). You may not use this file except in compliance with the License. You can obtain a
  * copy of the License at http://www.gnu.org/licenses/gpl-3.0.html See the License for the specific
  * language governing permissions and limitations under the License.
+ *
  * When distributing the software, include this License Header Notice in each file.
  */
  package org.openconcerto.erp.config.update;
 import org.openconcerto.erp.config.InstallationPanel;
+import org.openconcerto.erp.core.common.ui.AbstractVenteArticleItemTable;
 import org.openconcerto.erp.core.finance.payment.element.SDDMessageSQLElement;
 import org.openconcerto.erp.core.finance.payment.element.SEPAMandateSQLElement;
 import org.openconcerto.erp.core.finance.payment.element.TypeReglementSQLElement;
 import org.openconcerto.erp.core.sales.invoice.element.SaisieVenteFactureSQLElement;
 import org.openconcerto.erp.core.sales.order.ui.TypeFactureCommandeClient;
 import org.openconcerto.erp.core.sales.pos.element.TicketCaisseSQLElement;
 import org.openconcerto.erp.core.sales.product.element.ReferenceArticleSQLElement;
 import org.openconcerto.erp.core.sales.quote.element.DevisLogMailSQLElement;
+import org.openconcerto.erp.core.supplychain.stock.element.DepotStockSQLElement;
+import org.openconcerto.erp.core.supplychain.stock.element.StockItem;
 import org.openconcerto.sql.changer.convert.AddMDFields;
 import org.openconcerto.sql.model.DBRoot;
+import org.openconcerto.sql.model.SQLField;
 import org.openconcerto.sql.model.SQLField.Properties;
 import org.openconcerto.sql.model.SQLName;
+import org.openconcerto.sql.model.SQLRow;
+import org.openconcerto.sql.model.SQLRowListRSH;
 import org.openconcerto.sql.model.SQLRowValues;
+import org.openconcerto.sql.model.SQLSelect;
 import org.openconcerto.sql.model.SQLSyntax;
 import org.openconcerto.sql.model.SQLSystem;
 import org.openconcerto.sql.model.SQLTable;
 import org.openconcerto.sql.model.Where;
+import org.openconcerto.sql.preferences.SQLPreferences;
 import org.openconcerto.sql.request.UpdateBuilder;
 import org.openconcerto.sql.utils.AlterTable;
 import org.openconcerto.sql.utils.ChangeTable;
 import org.openconcerto.sql.utils.SQLCreateTable;
 import org.openconcerto.sql.utils.UniqueConstraintCreatorHelper;
 import org.openconcerto.utils.CollectionUtils;
+import org.openconcerto.utils.ExceptionHandler;
 import java.math.BigDecimal;
+import java.sql.Clob;
 import java.sql.SQLException;
 import java.sql.Types;
+import java.util.ArrayList;
 import java.util.Arrays;
+import java.util.Collections;
 import java.util.EnumSet;
+import java.util.HashMap;
 import java.util.List;
+import java.util.Map;
+import java.util.prefs.BackingStoreException;
+import org.apache.commons.dbutils.handlers.ArrayListHandler;
 public class Updater_1_5 {
     private static final String COMPTE_CLIENT_TRANSACTION = "COMPTE_CLIENT_TRANSACTION";
     public static void update(final DBRoot root) throws SQLException {
         // Champ obsolete sur compte
         SQLTable tableCompte = root.getTable("COMPTE_PCE");
         if (!tableCompte.contains("OBSOLETE")) {
             final AlterTable alter = new AlterTable(tableCompte);
             alter.addBooleanColumn("OBSOLETE", Boolean.FALSE, false);
             tableCompte.getBase().getDataSource().execute(alter.asString());
             tableCompte.getSchema().updateVersion();
             tableCompte.fetchFields();
+        }
         // Transaction du solde
         if (!root.contains(COMPTE_CLIENT_TRANSACTION)) {
             final SQLCreateTable createTable = new SQLCreateTable(root, COMPTE_CLIENT_TRANSACTION);
             createTable.addForeignColumn("CLIENT");
             createTable.addDateAndTimeColumn("DATE");
             createTable.addDecimalColumn("MONTANT", 16, 6, BigDecimal.valueOf(0), false);
             createTable.addForeignColumn("MODE_REGLEMENT");
             createTable.addForeignColumn("MOUVEMENT");
             try {
                 root.getBase().getDataSource().execute(createTable.asString());
                 InstallationPanel.insertUndef(createTable);
                 root.refetchTable(COMPTE_CLIENT_TRANSACTION);
                 root.getSchema().updateVersion();
             } catch (SQLException ex) {
                 throw new IllegalStateException("Erreur lors de la création de la table " + COMPTE_CLIENT_TRANSACTION, ex);
+            }
+        }
         // Solde
         final SQLTable tClient = root.getTable("CLIENT");
         if (!tClient.contains("SOLDE_COMPTE")) {
             final AlterTable alterClient = new AlterTable(tClient);
             alterClient.addDecimalColumn("SOLDE_COMPTE", 16, 6, BigDecimal.valueOf(0), false);
             tClient.getBase().getDataSource().execute(alterClient.asString());
             tClient.getSchema().updateVersion();
             tClient.fetchFields();
+        }
         if (!tClient.contains("DATE")) {
             final AlterTable alterClient = new AlterTable(tClient);
             alterClient.addColumn("DATE", "date");
             tClient.getBase().getDataSource().execute(alterClient.asString());
             tClient.getSchema().updateVersion();
             tClient.fetchFields();
+        }
         if (!tClient.contains("COMMENTAIRES")) {
             final AlterTable alterClient = new AlterTable(tClient);
             alterClient.addVarCharColumn("COMMENTAIRES", 2048);
             tClient.getBase().getDataSource().execute(alterClient.asString());
             tClient.getSchema().updateVersion();
             tClient.fetchFields();
+        }
         final SQLTable tCompteClient = root.getTable("COMPTE_CLIENT_TRANSACTION");
         if (!tCompteClient.contains("ID_MOUVEMENT")) {
             final AlterTable alterClient = new AlterTable(tCompteClient);
             alterClient.addForeignColumn("ID_MOUVEMENT", root.getTable("MOUVEMENT"));
             tClient.getBase().getDataSource().execute(alterClient.asString());
             tClient.getSchema().updateVersion();
             tClient.fetchFields();
+        }
         final SQLTable tCmdClient = root.getTable("COMMANDE_CLIENT");
         if (!tCmdClient.contains("DATE_LIVRAISON_PREV")) {
             final AlterTable alterCmdClient = new AlterTable(tCmdClient);
             alterCmdClient.addColumn("DATE_LIVRAISON_PREV", "date");
             tCmdClient.getBase().getDataSource().execute(alterCmdClient.asString());
             tCmdClient.getSchema().updateVersion();
             tCmdClient.fetchFields();
+        }
         if (!tClient.contains("CONDITIONS_LIVRAISON")) {
             final AlterTable alterClient = new AlterTable(tClient);
             alterClient.addVarCharColumn("CONDITIONS_LIVRAISON", 512);
             tClient.getBase().getDataSource().execute(alterClient.asString());
             tClient.getSchema().updateVersion();
             tClient.fetchFields();
+        }
+        {
             // Ajout du champ SANS_VALEUR_ENCAISSEMENT pour gérer les anciens cheques sans le compte
             // 511
             List<String> tablesCheque = Arrays.asList("CHEQUE_A_ENCAISSER", "CHEQUE_FOURNISSEUR");
             for (String string : tablesCheque) {
                 final SQLTable table = root.getTable(string);
                 if (!table.contains("SANS_VALEUR_ENCAISSEMENT")) {
                     AlterTable alterElt = new AlterTable(table);
                     alterElt.addBooleanColumn("SANS_VALEUR_ENCAISSEMENT", Boolean.FALSE, false);
                     table.getBase().getDataSource().execute(alterElt.asString());
                     root.refetchTable(string);
                     root.getSchema().updateVersion();
                     UpdateBuilder upBuilder = new UpdateBuilder(table);
                     upBuilder.setObject("SANS_VALEUR_ENCAISSEMENT", Boolean.TRUE);
                     table.getBase().getDataSource().execute(upBuilder.asString());
+                }
+            }
             SQLTable tableEncElt = root.getTable("ENCAISSER_MONTANT_ELEMENT");
             if (tableEncElt.getField("DATE").getType().getType() == Types.TIMESTAMP) {
                 AlterTable t = new AlterTable(tableEncElt);
                 t.alterColumn("DATE", EnumSet.allOf(Properties.class), "date", null, Boolean.TRUE);
                 tableEncElt.getBase().getDataSource().execute(t.asString());
                 root.refetchTable(tableEncElt.getName());
                 root.getSchema().updateVersion();
+            }
+        }
         // TVA Intra
         final SQLTable tTva = root.getTable("TAXE");
         if (!tTva.contains("ID_COMPTE_PCE_COLLECTE_INTRA")) {
             final AlterTable alterTaxe = new AlterTable(tTva);
             alterTaxe.addForeignColumn("ID_COMPTE_PCE_COLLECTE_INTRA", root.getTable("COMPTE_PCE"));
             alterTaxe.addForeignColumn("ID_COMPTE_PCE_DED_INTRA", root.getTable("COMPTE_PCE"));
             tTva.getBase().getDataSource().execute(alterTaxe.asString());
             tTva.getSchema().updateVersion();
             tTva.fetchFields();
+        }
         if (!root.contains("TAXE_COMPLEMENTAIRE")) {
             final SQLCreateTable createTable = new SQLCreateTable(root, "TAXE_COMPLEMENTAIRE");
             createTable.addForeignColumn("ID_COMPTE_PCE_PRODUITS", root.getTable("COMPTE_PCE"));
             createTable.addForeignColumn("ID_COMPTE_PCE", root.getTable("COMPTE_PCE"));
             createTable.addDecimalColumn("POURCENT", 16, 6, BigDecimal.valueOf(0), false);
             createTable.addVarCharColumn("CODE", 25);
             createTable.addVarCharColumn("NOM", 256);
             try {
                 root.getBase().getDataSource().execute(createTable.asString());
                 InstallationPanel.insertUndef(createTable);
                 root.refetchTable("TAXE_COMPLEMENTAIRE");
                 root.getSchema().updateVersion();
             } catch (SQLException ex) {
                 throw new IllegalStateException("Erreur lors de la création de la table " + "TAXE_COMPLEMENTAIRE", ex);
+            }
             SQLTable tableArt = root.getTable("ARTICLE");
             final AlterTable alterArt = new AlterTable(tableArt);
             alterArt.addForeignColumn("ID_TAXE_COMPLEMENTAIRE", root.getTable("TAXE_COMPLEMENTAIRE"));
             tableArt.getBase().getDataSource().execute(alterArt.asString());
             tableArt.getSchema().updateVersion();
             tableArt.fetchFields();
+        }
         // GED
         if (!root.contains("ATTACHMENT")) {
             final SQLCreateTable createTable = new SQLCreateTable(root, "ATTACHMENT");
             createTable.addVarCharColumn("SOURCE_TABLE", 128);
             createTable.addIntegerColumn("SOURCE_ID", 0);
             createTable.addVarCharColumn("NAME", 256);
             createTable.addVarCharColumn("MIMETYPE", 256);
             createTable.addVarCharColumn("FILENAME", 256);
             createTable.addLongColumn("FILESIZE", 0L, false);
             createTable.addVarCharColumn("STORAGE_PATH", 256);
             createTable.addVarCharColumn("STORAGE_FILENAME", 256);
             createTable.addVarCharColumn("DIRECTORY", 256);
             createTable.addVarCharColumn("THUMBNAIL", 256);
             createTable.addIntegerColumn("THUMBNAIL_WIDTH", 32);
             createTable.addIntegerColumn("THUMBNAIL_HEIGHT", 32);
             createTable.addVarCharColumn("TAG", 128);
             createTable.addIntegerColumn("VERSION", 0);
             createTable.addVarCharColumn("HASH", 32);
             try {
                 root.getBase().getDataSource().execute(createTable.asString());
                 InstallationPanel.insertUndef(createTable);
                 root.refetchTable("ATTACHMENT");
                 root.getSchema().updateVersion();
             } catch (SQLException ex) {
                 throw new IllegalStateException("Erreur lors de la création de la table " + "ATTACHMENT", ex);
+            }
+        }
         SQLTable tableAttachment = root.getTable("ATTACHMENT");
         if (!tableAttachment.contains("DIRECTORY")) {
             final AlterTable alter = new AlterTable(tableAttachment);
             alter.addVarCharColumn("STORAGE_FILENAME", 256);
             alter.addVarCharColumn("DIRECTORY", 256);
             tableAttachment.getBase().getDataSource().execute(alter.asString());
             tableAttachment.getSchema().updateVersion();
             tableAttachment.fetchFields();
+        }
         if (!tableAttachment.contains("ID_PARENT")) {
             final AlterTable alter = new AlterTable(tableAttachment);
             alter.addForeignColumn("ID_PARENT", tableAttachment);
             tableAttachment.getBase().getDataSource().execute(alter.asString());
             tableAttachment.getSchema().updateVersion();
             tableAttachment.fetchFields();
+        }
         if (!tableAttachment.contains("VERSION")) {
             final AlterTable alter = new AlterTable(tableAttachment);
             alter.addIntegerColumn("VERSION", 0);
             alter.addVarCharColumn("HASH", 32);
             alter.addVarCharColumn("INFOS", 8000);
             tableAttachment.getBase().getDataSource().execute(alter.asString());
             tableAttachment.getSchema().updateVersion();
             tableAttachment.fetchFields();
+        }
         List<String> gedTable = Arrays.asList("CLIENT", "MOUVEMENT", "FOURNISSEUR", "ARTICLE", "FACTURE_FOURNISSEUR", "SAISIE_VENTE_FACTURE", "SALARIE");
         for (String string : gedTable) {
             SQLTable tableGED = root.getTable(string);
             if (!tableGED.contains("ATTACHMENTS")) {
                 final AlterTable alter = new AlterTable(tableGED);
                 alter.addIntegerColumn("ATTACHMENTS", 0);
                 tableGED.getBase().getDataSource().execute(alter.asString());
                 tableGED.getSchema().updateVersion();
                 tableGED.fetchFields();
+            }
+        }
         // gestion articles en attente
+        {
             // Vente
             SQLTable tableBLElt = root.getTable("BON_DE_LIVRAISON_ELEMENT");
             if (!tableBLElt.contains("ID_COMMANDE_CLIENT_ELEMENT")) {
                 AlterTable t = new AlterTable(tableBLElt);
                 t.addForeignColumn("ID_COMMANDE_CLIENT_ELEMENT", root.getTable("COMMANDE_CLIENT_ELEMENT"));
                 tableBLElt.getBase().getDataSource().execute(t.asString());
                 root.refetchTable(tableBLElt.getName());
                 root.getSchema().updateVersion();
+            }
             SQLTable tableVFElt = root.getTable("SAISIE_VENTE_FACTURE_ELEMENT");
             if (!tableVFElt.contains("ID_COMMANDE_CLIENT_ELEMENT")) {
                 AlterTable t = new AlterTable(tableVFElt);
                 t.addForeignColumn("ID_COMMANDE_CLIENT_ELEMENT", root.getTable("COMMANDE_CLIENT_ELEMENT"));
                 tableVFElt.getBase().getDataSource().execute(t.asString());
                 root.refetchTable(tableVFElt.getName());
                 root.getSchema().updateVersion();
+            }
             SQLTable tableCmdElt = root.getTable("COMMANDE_CLIENT_ELEMENT");
             if (!tableCmdElt.contains("LIVRE")) {
                 AlterTable t = new AlterTable(tableCmdElt);
+                // FIXME mis par défaut à true avant --> à fixer
                 t.addBooleanColumn("LIVRE_FORCED", Boolean.FALSE, false);
                 t.addBooleanColumn("LIVRE", Boolean.FALSE, false);
                 t.addDecimalColumn("QTE_LIVREE", 16, 6, BigDecimal.ZERO, true);
                 tableCmdElt.getBase().getDataSource().execute(t.asString());
                 root.refetchTable(tableCmdElt.getName());
                 root.getSchema().updateVersion();
+                // String up = "UPDATE " + new SQLName(root.getName(),
+                // tableCmdElt.getName()).quote()
+                // + " SET \"QTE_LIVREE\"=\"QTE\"*\"QTE_UNITAIRE\", \"LIVRE_FORCED\"=true WHERE
+                // \"ID_COMMANDE_CLIENT\" IN []";
+                {
+                    List<String> tablesWorkFlow = Arrays.asList("SAISIE_VENTE_FACTURE", "BON_DE_LIVRAISON");
+                    for (String tableWorkFlow : tablesWorkFlow) {
-                SQLTable tableCmdCli = root.getTable("COMMANDE_CLIENT");
+                        SQLTable tableCmdCli = root.getTable("COMMANDE_CLIENT");
-                SQLTable tableTR = root.getTable("TR_COMMANDE_CLIENT");
+                        SQLTable tableTR = root.getTable("TR_COMMANDE_CLIENT");
-                SQLTable tableBL = root.getTable("BON_DE_LIVRAISON");
+                        SQLTable tableBL = root.getTable(tableWorkFlow);
-                SQLTable tableFactC = root.getTable("SAISIE_VENTE_FACTURE");
+                        // SQLTable tableFactC = root.getTable("SAISIE_VENTE_FACTURE");
-                String sel = "SELECT t.\"ID_COMMANDE_CLIENT\" FROM " + new SQLName(root.getName(), tableTR.getName()).quote() + " t ," + new SQLName(root.getName(), tableCmdCli.getName()).quote()
+                        String sel = "SELECT t.\"ID_COMMANDE_CLIENT\" FROM " + new SQLName(root.getName(), tableTR.getName()).quote() + " t ,"
-                        + " c ," + new SQLName(root.getName(), tableBL.getName()).quote() + " b ," + new SQLName(root.getName(), tableFactC.getName()).quote()
+                                + new SQLName(root.getName(), tableCmdCli.getName()).quote() + " c ," + new SQLName(root.getName(), tableBL.getName()).quote()
-                        + " f WHERE c.\"ID\"=t.\"ID_COMMANDE_CLIENT\" AND f.\"ID\"=t.\"ID_SAISIE_VENTE_FACTURE\""
+                                + " b WHERE c.\"ID\"=t.\"ID_COMMANDE_CLIENT\"" + " AND b.\"ID\"=t.\"ID_" + tableWorkFlow
+                                + "\" AND b.\"ARCHIVE\" = 0 AND t.\"ID\" > 1 AND c.\"ID\" > 1 AND  c.\"ARCHIVE\" = 0 GROUP BY t.\"ID_COMMANDE_CLIENT\" HAVING (SUM(b.\""
-                        + " AND b.\"ID\"=t.\"ID_BON_DE_LIVRAISON\" AND b.\"ARCHIVE\" = 0 AND f.\"ID\" > 1 AND t.\"ID\" > 1 AND c.\"ID\" > 1 AND f.\"ARCHIVE\" = 0 AND t.\"ARCHIVE\" = 0 AND c.\"ARCHIVE\" = 0 GROUP BY t.\"ID_COMMANDE_CLIENT\" HAVING (SUM(b.\"TOTAL_HT\")>=SUM(c.\"T_HT\") OR SUM(f.\"T_HT\")>=SUM(c.\"T_HT\")) ";
+                                + (tableWorkFlow.startsWith("BON") ? "TOTAL_HT" : "T_HT") + "\")>=SUM(c.\"T_HT\") ) ";
-                List<Object> cmd = tableTR.getDBSystemRoot().getDataSource().executeCol(sel);
+                        List<Integer> cmd = tableTR.getDBSystemRoot().getDataSource().executeCol(sel);
-                UpdateBuilder build = new UpdateBuilder(tableCmdElt);
+                        UpdateBuilder build = new UpdateBuilder(tableCmdElt);
-                build.set("QTE_LIVREE", "\"QTE\"*\"QTE_UNITAIRE\"");
+                        build.set("QTE_LIVREE", "\"QTE\"*\"QTE_UNITAIRE\"");
-                build.setObject("LIVRE_FORCED", Boolean.TRUE);
+                        build.setObject("LIVRE_FORCED", Boolean.TRUE);
-                final Where where = new Where(tableCmdElt.getField("ID_COMMANDE_CLIENT"), cmd);
+                        final Where where = new Where(tableCmdElt.getField("ID_COMMANDE_CLIENT"), cmd);
-                build.setWhere(where);
+                        build.setWhere(where);
-                // String up = "UPDATE " + new SQLName(root.getName(),
-                // tableCmdElt.getName()).quote()
-                // + " SET \"QTE_LIVREE\"=\"QTE\"*\"QTE_UNITAIRE\", \"LIVRE_FORCED\"=true WHERE
-                // \"ID_COMMANDE_CLIENT\" IN []";
-            }
+                        tableTR.getDBSystemRoot().getDataSource().execute(build.asString());
+                    }
+                }
+            }
             // Fix bad default value
             if (tableCmdElt.contains("LIVRE")) {
                 AlterTable t = new AlterTable(tableCmdElt);
                 t.alterColumnDefault("LIVRE", "false");
                 tableCmdElt.getBase().getDataSource().execute(t.asString());
                 root.refetchTable(tableCmdElt.getName());
                 root.getSchema().updateVersion();
+                UpdateBuilder upLivre = new UpdateBuilder(tableCmdElt);
+                upLivre.setObject("LIVRE", Boolean.FALSE);
+                upLivre.setWhere(new Where(tableCmdElt.getKey(), "=", 1));
+                tableCmdElt.getBase().getDataSource().execute(upLivre.asString());
+            }
             // Achat
             SQLTable tableBRElt = root.getTable("BON_RECEPTION_ELEMENT");
             if (!tableBRElt.contains("ID_COMMANDE_ELEMENT")) {
                 AlterTable t = new AlterTable(tableBRElt);
                 t.addForeignColumn("ID_COMMANDE_ELEMENT", root.getTable("COMMANDE_ELEMENT"));
                 tableBRElt.getBase().getDataSource().execute(t.asString());
                 root.refetchTable(tableBRElt.getName());
                 root.getSchema().updateVersion();
+            }
             SQLTable tableCmdFElt = root.getTable("COMMANDE_ELEMENT");
             if (!tableCmdFElt.contains("RECU")) {
                 AlterTable t = new AlterTable(tableCmdFElt);
                 t.addBooleanColumn("RECU_FORCED", Boolean.FALSE, false);
                 t.addBooleanColumn("RECU", Boolean.TRUE, false);
                 t.addDecimalColumn("QTE_RECUE", 16, 6, BigDecimal.ZERO, true);
                 tableCmdFElt.getBase().getDataSource().execute(t.asString());
                 root.refetchTable(tableCmdFElt.getName());
                 root.getSchema().updateVersion();
+                // String up = "UPDATE " + new SQLName(root.getName(),
+                // tableCmdElt.getName()).quote()
+                // + " SET \"QTE_LIVREE\"=\"QTE\"*\"QTE_UNITAIRE\", \"LIVRE_FORCED\"=true WHERE
+                // \"ID_COMMANDE_CLIENT\" IN []";
+                {
-                SQLTable tableCmdCli = root.getTable("COMMANDE");
+                    SQLTable tableCmdCli = root.getTable("COMMANDE");
-                SQLTable tableTR = root.getTable("TR_COMMANDE");
+                    SQLTable tableTR = root.getTable("TR_COMMANDE");
-                SQLTable tableBR = root.getTable("BON_RECEPTION");
+                    SQLTable tableBR = root.getTable("BON_RECEPTION");
-                String sel = "SELECT t.\"ID_COMMANDE\" FROM " + new SQLName(root.getName(), tableTR.getName()).quote() + " t ," + new SQLName(root.getName(), tableCmdCli.getName()).quote() + " c ,"
+                    String sel = "SELECT t.\"ID_COMMANDE\" FROM " + new SQLName(root.getName(), tableTR.getName()).quote() + " t ," + new SQLName(root.getName(), tableCmdCli.getName()).quote()
-                        + new SQLName(root.getName(), tableBR.getName()).quote() + " b WHERE c.\"ID\"=t.\"ID_COMMANDE\""
+                            + " c ," + new SQLName(root.getName(), tableBR.getName()).quote() + " b WHERE c.\"ID\"=t.\"ID_COMMANDE\""
-                        + " AND b.\"ID\"=t.\"ID_BON_RECEPTION\" AND b.\"ARCHIVE\" = 0 AND t.\"ID\" > 1 AND c.\"ID\" > 1 AND t.\"ARCHIVE\" = 0 AND c.\"ARCHIVE\" = 0 GROUP BY t.\"ID_COMMANDE\" HAVING (SUM(b.\"TOTAL_HT\")>=SUM(c.\"T_HT\")) ";
+                            + " AND b.\"ID\"=t.\"ID_BON_RECEPTION\" AND b.\"ARCHIVE\" = 0 AND t.\"ID\" > 1 AND c.\"ID\" > 1 AND c.\"ARCHIVE\" = 0 GROUP BY t.\"ID_COMMANDE\" HAVING (SUM(b.\"TOTAL_HT\")>=SUM(c.\"T_HT\")) ";
+                    System.err.println(sel);
-                List<Object> cmd = tableTR.getDBSystemRoot().getDataSource().executeCol(sel);
+                    List<Object> cmd = tableTR.getDBSystemRoot().getDataSource().executeCol(sel);
-                UpdateBuilder build = new UpdateBuilder(tableCmdFElt);
+                    UpdateBuilder build = new UpdateBuilder(tableCmdFElt);
-                build.set("QTE_RECUE", "\"QTE\"*\"QTE_UNITAIRE\"");
+                    build.set("QTE_RECUE", "\"QTE\"*\"QTE_UNITAIRE\"");
-                build.setObject("RECU_FORCED", Boolean.TRUE);
+                    build.setObject("RECU_FORCED", Boolean.TRUE);
-                final Where where = new Where(tableCmdFElt.getField("ID_COMMANDE"), cmd);
+                    final Where where = new Where(tableCmdFElt.getField("ID_COMMANDE"), cmd);
-                build.setWhere(where);
+                    build.setWhere(where);
-                // String up = "UPDATE " + new SQLName(root.getName(),
+                    System.err.println(build.asString());
+                    tableTR.getDBSystemRoot().getDataSource().execute(build.asString());
+                }
+            }
+            // Champ matière
-                // tableCmdElt.getName()).quote()
+            SQLTable tableArt = root.getTable("ARTICLE");
+            if (!tableArt.contains("MATIERE")) {
+                final AlterTable alter = new AlterTable(tableArt);
+                alter.addVarCharColumn("MATIERE", 128);
-                // + " SET \"QTE_LIVREE\"=\"QTE\"*\"QTE_UNITAIRE\", \"LIVRE_FORCED\"=true WHERE
+                tableArt.getBase().getDataSource().execute(alter.asString());
+                tableArt.getSchema().updateVersion();
-                // \"ID_COMMANDE_CLIENT\" IN []";
+                tableArt.fetchFields();
+            }
+        }
         // ----------------------- 1.5.1
         if (!root.contains("FABRICANT")) {
             final SQLCreateTable createTableEtat = new SQLCreateTable(root, "FABRICANT");
             createTableEtat.addVarCharColumn("NOM", 256);
             root.getBase().getDataSource().execute(createTableEtat.asString());
             InstallationPanel.insertUndef(createTableEtat);
             root.refetchTable("FABRICANT");
             root.getSchema().updateVersion();
             final AlterTable alterA = new AlterTable(root.getTable("ARTICLE"));
             alterA.addForeignColumn("ID_FABRICANT", root.findTable("FABRICANT"));
             root.getBase().getDataSource().execute(alterA.asString());
             root.refetchTable("ARTICLE");
             root.getSchema().updateVersion();
+        }
         // CIM
+        {
             if (!root.contains("ETAT_DEMANDE_ACHAT_ELEMENT")) {
                 final SQLCreateTable createTableEtat = new SQLCreateTable(root, "ETAT_DEMANDE_ACHAT_ELEMENT");
                 createTableEtat.addVarCharColumn("NOM", 256);
                 createTableEtat.addIntegerColumn("COLOR", null, true);
                 root.getBase().getDataSource().execute(createTableEtat.asString());
                 InstallationPanel.insertUndef(createTableEtat);
                 root.refetchTable("ETAT_DEMANDE_ACHAT_ELEMENT");
                 root.getSchema().updateVersion();
                 SQLRowValues rowVals = new SQLRowValues(root.getTable("ETAT_DEMANDE_ACHAT_ELEMENT"));
                 rowVals.put("NOM", "En attente");
                 rowVals.insert();
                 rowVals.put("NOM", "Demande de prix");
                 rowVals.insert();
                 rowVals.put("NOM", "En commande");
                 rowVals.insert();
                 rowVals.put("NOM", "Réceptionnée");
                 rowVals.insert();
                 rowVals.put("NOM", "Réception partielle");
                 rowVals.insert();
                 rowVals.put("NOM", "A relancer");
                 rowVals.insert();
+            }
             if (!root.contains("DEMANDE_ACHAT_ELEMENT")) {
                 final SQLCreateTable createTableDmd = new SQLCreateTable(root, "DEMANDE_ACHAT_ELEMENT");
                 createTableDmd.addVarCharColumn("CODE", 256);
                 createTableDmd.addVarCharColumn("NOM", 256);
                 createTableDmd.addIntegerColumn("QTE", 1);
                 createTableDmd.addDecimalColumn("QTE_UNITAIRE", 16, 6, BigDecimal.ONE, false);
                 createTableDmd.addVarCharColumn("REPERE", 256);
                 createTableDmd.addVarCharColumn("REFERENCE", 256);
                 createTableDmd.addForeignColumn("FABRICANT");
                 createTableDmd.addColumn("DATE", "date");
                 createTableDmd.addForeignColumn("ETAT_DEMANDE_ACHAT_ELEMENT");
                 createTableDmd.addForeignColumn("UNITE_VENTE");
                 createTableDmd.addForeignColumn("ARTICLE");
                 createTableDmd.addForeignColumn("FAMILLE_ARTICLE");
                 createTableDmd.addForeignColumn("FOURNISSEUR");
                 createTableDmd.addBooleanColumn("EN_STOCK", Boolean.FALSE, false);
                 root.getBase().getDataSource().execute(createTableDmd.asString());
                 InstallationPanel.insertUndef(createTableDmd);
                 root.refetchTable("DEMANDE_ACHAT_ELEMENT");
                 root.getSchema().updateVersion();
+            }
             if (!root.contains("ETAT_DEMANDE_PRIX")) {
                 final SQLCreateTable createTableEtat = new SQLCreateTable(root, "ETAT_DEMANDE_PRIX");
                 createTableEtat.addVarCharColumn("NOM", 256);
                 root.getBase().getDataSource().execute(createTableEtat.asString());
                 InstallationPanel.insertUndef(createTableEtat);
                 root.refetchTable("ETAT_DEMANDE_PRIX");
                 root.getSchema().updateVersion();
                 SQLRowValues rowVals = new SQLRowValues(root.getTable("ETAT_DEMANDE_PRIX"));
                 rowVals.put("NOM", "En attente");
                 rowVals.insert();
                 rowVals.put("NOM", "Refusée");
                 rowVals.insert();
                 rowVals.put("NOM", "Acceptée");
                 rowVals.insert();
                 rowVals.put("NOM", "En cours");
                 rowVals.insert();
+            }
             if (!root.contains("DEMANDE_PRIX")) {
                 final SQLCreateTable createTableDmd = new SQLCreateTable(root, "DEMANDE_PRIX");
                 createTableDmd.addVarCharColumn("NUMERO", 256);
                 createTableDmd.addVarCharColumn("OBJET", 1024);
                 createTableDmd.addForeignColumn("FOURNISSEUR");
                 // createTableDemande.addForeignColumn("ID_AFFAIRE",
                 // ctxt.getRoot().findTable("AFFAIRE"));
                 createTableDmd.addColumn("DATE", "date");
                 // createTableDemandeM.addColumn("DATE_DISPOSITION", "date");
                 createTableDmd.addColumn("DATE_BUTOIRE", "date");
                 createTableDmd.addColumn("T_HT", "bigint DEFAULT 0");
                 createTableDmd.addColumn("T_TVA", "bigint DEFAULT 0");
                 createTableDmd.addColumn("T_TTC", "bigint DEFAULT 0");
                 // createTableDemandeM.addForeignColumn("ID_TAXE", findTableTaxe);
                 createTableDmd.addForeignColumn("COMMERCIAL");
                 createTableDmd.addForeignColumn("ETAT_DEMANDE_PRIX");
                 // createTableDemandeM.addForeignColumn("ID_ADRESSE",
                 // ctxt.getRoot().findTable("ADRESSE"));
                 createTableDmd.addVarCharColumn("INFOS", 1024);
                 root.getBase().getDataSource().execute(createTableDmd.asString());
                 InstallationPanel.insertUndef(createTableDmd);
                 root.refetchTable("DEMANDE_PRIX");
                 root.getSchema().updateVersion();
                 final SQLCreateTable createTableDemandeMElt = new SQLCreateTable(root, "DEMANDE_PRIX_ELEMENT");
                 createTableDemandeMElt.addVarCharColumn("NOM", 512);
                 createTableDemandeMElt.addForeignColumn("DEMANDE_PRIX");
                 createTableDemandeMElt.addForeignColumn("ID_TAXE", root.getTable("TAXE").getSQLName(), root.getTable("TAXE").getKey().getName(), "2");
                 createTableDemandeMElt.addIntegerColumn("QTE", 0);
                 createTableDemandeMElt.addColumn("VALEUR_METRIQUE_3", "real DEFAULT 0");
                 createTableDemandeMElt.addColumn("PRIX_METRIQUE_VT_3", "bigint DEFAULT 0");
                 createTableDemandeMElt.addDecimalColumn("PA_HT", 16, 6, BigDecimal.ZERO, false);
                 createTableDemandeMElt.addDecimalColumn("PRIX_METRIQUE_HA_2", 16, 6, BigDecimal.ZERO, false);
                 createTableDemandeMElt.addDecimalColumn("PRIX_METRIQUE_HA_1", 16, 6, BigDecimal.ZERO, false);
                 createTableDemandeMElt.addDecimalColumn("T_PA_TTC", 16, 2, BigDecimal.ZERO, false);
                 createTableDemandeMElt.addColumn("VALEUR_METRIQUE_1", "real DEFAULT 0");
                 createTableDemandeMElt.addDecimalColumn("T_PA_HT", 16, 6, BigDecimal.ZERO, false);
                 createTableDemandeMElt.addColumn("T_POIDS", "real DEFAULT 0");
                 createTableDemandeMElt.addColumn("VALEUR_METRIQUE_2", "real DEFAULT 0");
                 createTableDemandeMElt.addDecimalColumn("PRIX_METRIQUE_HA_3", 16, 6, BigDecimal.ZERO, false);
                 createTableDemandeMElt.addDecimalColumn("PRIX_METRIQUE_VT_2", 16, 6, BigDecimal.ZERO, false);
                 createTableDemandeMElt.addDecimalColumn("PRIX_METRIQUE_VT_1", 16, 6, BigDecimal.ZERO, false);
                 createTableDemandeMElt.addDecimalColumn("T_PV_TTC", 16, 2, BigDecimal.ZERO, false);
                 createTableDemandeMElt.addForeignColumn("ID_METRIQUE_1", root.findTable("METRIQUE"));
                 SQLTable findTable = root.findTable("MODE_VENTE_ARTICLE");
                 createTableDemandeMElt.addForeignColumn("ID_MODE_VENTE_ARTICLE", findTable.getSQLName(), findTable.getKey().getName(), String.valueOf(ReferenceArticleSQLElement.A_LA_PIECE));
                 createTableDemandeMElt.addForeignColumn("ID_METRIQUE_3", root.findTable("METRIQUE"));
                 createTableDemandeMElt.addForeignColumn("ID_STYLE", root.findTable("STYLE"));
                 createTableDemandeMElt.addForeignColumn("ID_METRIQUE_2", root.findTable("METRIQUE"));
                 createTableDemandeMElt.addIntegerColumn("QTE_ACHAT", 1);
                 createTableDemandeMElt.addForeignColumn("ID_DEVISE", root.findTable("DEVISE"));
                 createTableDemandeMElt.addForeignColumn("ID_FAMILLE_ARTICLE", root.findTable("FAMILLE_ARTICLE"));
                 createTableDemandeMElt.addVarCharColumn("CODE", 256);
                 createTableDemandeMElt.addColumn("SERVICE", "boolean DEFAULT false");
                 createTableDemandeMElt.addDecimalColumn("T_PV_HT", 16, 6, BigDecimal.ZERO, false);
                 createTableDemandeMElt.addDecimalColumn("PV_HT", 16, 6, BigDecimal.ZERO, false);
                 createTableDemandeMElt.addColumn("POIDS", "real DEFAULT 0");
                 createTableDemandeMElt.addDecimalColumn("PA_DEVISE", 16, 6, BigDecimal.ZERO, false);
                 createTableDemandeMElt.addDecimalColumn("PA_DEVISE_T", 16, 6, BigDecimal.ZERO, false);
                 createTableDemandeMElt.addForeignColumn("ID_ARTICLE", root.findTable("ARTICLE"));
                 createTableDemandeMElt.addDecimalColumn("QTE_UNITAIRE", 16, 6, BigDecimal.ONE, false);
                 createTableDemandeMElt.addForeignColumn("ID_UNITE_VENTE", root.findTable("UNITE_VENTE"));
                 createTableDemandeMElt.addVarCharColumn("DESCRIPTIF", 2048);
                 createTableDemandeMElt.addIntegerColumn("NIVEAU", 1);
+                if (root.contains("DEPOT_STOCK")) {
+                    createTableDemandeMElt.addForeignColumn("DEPOT_STOCK");
+                }
                 createTableDemandeMElt.addForeignColumn("ID_ECO_CONTRIBUTION", root.findTable("ECO_CONTRIBUTION"));
                 createTableDemandeMElt.addDecimalColumn("ECO_CONTRIBUTION", 16, 2, BigDecimal.ZERO, false);
                 createTableDemandeMElt.addDecimalColumn("T_ECO_CONTRIBUTION", 16, 2, BigDecimal.ZERO, false);
                 createTableDemandeMElt.addForeignColumn("ID_DEMANDE_ACHAT_ELEMENT", root.findTable("DEMANDE_ACHAT_ELEMENT"));
                 root.getBase().getDataSource().execute(createTableDemandeMElt.asString());
                 InstallationPanel.insertUndef(createTableDemandeMElt);
                 root.refetchTable("DEMANDE_PRIX_ELEMENT");
                 root.getSchema().updateVersion();
                 final AlterTable alterNumero = new AlterTable(root.getTable("NUMEROTATION_AUTO"));
                 alterNumero.addVarCharColumn("DMD_PRIX_FORMAT", 128);
                 alterNumero.addIntegerColumn("DMD_PRIX_START", 1);
                 root.getBase().getDataSource().execute(alterNumero.asString());
                 root.refetchTable("NUMEROTATION_AUTO");
                 root.getSchema().updateVersion();
                 final AlterTable alterCmd = new AlterTable(root.getTable("COMMANDE_ELEMENT"));
                 alterCmd.addForeignColumn("ID_DEMANDE_ACHAT_ELEMENT", root.findTable("DEMANDE_ACHAT_ELEMENT"));
                 root.getBase().getDataSource().execute(alterCmd.asString());
                 root.refetchTable("COMMANDE_ELEMENT");
                 root.getSchema().updateVersion();
                 final AlterTable alterF = new AlterTable(root.getTable("FACTURE_FOURNISSEUR_ELEMENT"));
                 alterF.addForeignColumn("ID_DEMANDE_ACHAT_ELEMENT", root.findTable("DEMANDE_ACHAT_ELEMENT"));
                 root.getBase().getDataSource().execute(alterF.asString());
                 root.refetchTable("FACTURE_FOURNISSEUR_ELEMENT");
                 root.getSchema().updateVersion();
                 final AlterTable alterB = new AlterTable(root.getTable("BON_RECEPTION_ELEMENT"));
                 alterB.addForeignColumn("ID_DEMANDE_ACHAT_ELEMENT", root.findTable("DEMANDE_ACHAT_ELEMENT"));
                 root.getBase().getDataSource().execute(alterB.asString());
                 root.refetchTable("BON_RECEPTION_ELEMENT");
                 root.getSchema().updateVersion();
+            }
+        }
         SQLTable tableDmdAChat = root.findTable("DEMANDE_ACHAT_ELEMENT");
         if (!tableDmdAChat.contains("ID_FAMILLE_ARTICLE")) {
             final AlterTable alterB = new AlterTable(tableDmdAChat);
             alterB.addForeignColumn("ID_FAMILLE_ARTICLE", root.findTable("FAMILLE_ARTICLE"));
             root.getBase().getDataSource().execute(alterB.asString());
             root.refetchTable("DEMANDE_ACHAT_ELEMENT");
             root.getSchema().updateVersion();
+        }
         if (!tableDmdAChat.contains("ID_COMMANDE")) {
             final AlterTable alterB = new AlterTable(tableDmdAChat);
             alterB.addForeignColumn("ID_COMMANDE", root.findTable("COMMANDE"));
             root.getBase().getDataSource().execute(alterB.asString());
             root.refetchTable("DEMANDE_ACHAT_ELEMENT");
             root.getSchema().updateVersion();
+        }
         if (!tableDmdAChat.contains("ID_BON_RECEPTION")) {
             final AlterTable alterB = new AlterTable(tableDmdAChat);
             alterB.addForeignColumn("ID_BON_RECEPTION", root.findTable("BON_RECEPTION"));
             root.getBase().getDataSource().execute(alterB.asString());
             root.refetchTable("DEMANDE_ACHAT_ELEMENT");
             root.getSchema().updateVersion();
+        }
         if (!tableDmdAChat.contains("REPRISE")) {
             final AlterTable alterB = new AlterTable(tableDmdAChat);
             alterB.addBooleanColumn("REPRISE", Boolean.FALSE, false);
             root.getBase().getDataSource().execute(alterB.asString());
             root.refetchTable("DEMANDE_ACHAT_ELEMENT");
             root.getSchema().updateVersion();
+        }
+        if (!tableDmdAChat.contains("ID_COMMANDE")) {
+            final AlterTable alterB = new AlterTable(tableDmdAChat);
+            alterB.addForeignColumn("ID_COMMANDE", root.findTable("COMMANDE"));
+            root.getBase().getDataSource().execute(alterB.asString());
+            root.refetchTable("DEMANDE_ACHAT_ELEMENT");
+            root.getSchema().updateVersion();
+        }
+        if (!tableDmdAChat.contains("REPRISE")) {
+            final AlterTable alterB = new AlterTable(tableDmdAChat);
+            alterB.addBooleanColumn("REPRISE", Boolean.FALSE, false);
+            root.getBase().getDataSource().execute(alterB.asString());
+            root.refetchTable("DEMANDE_ACHAT_ELEMENT");
+            root.getSchema().updateVersion();
+        }
         if (!tableDmdAChat.contains("IMPORT")) {
             final AlterTable alterB = new AlterTable(tableDmdAChat);
             alterB.addBooleanColumn("IMPORT", Boolean.FALSE, false);
             root.getBase().getDataSource().execute(alterB.asString());
             root.refetchTable("DEMANDE_ACHAT_ELEMENT");
             root.getSchema().updateVersion();
+        }
+        if (!tableDmdAChat.contains("ID_BON_RECEPTION")) {
+            final AlterTable alterB = new AlterTable(tableDmdAChat);
+            alterB.addForeignColumn("ID_BON_RECEPTION", root.findTable("BON_RECEPTION"));
+            root.getBase().getDataSource().execute(alterB.asString());
+            root.refetchTable("DEMANDE_ACHAT_ELEMENT");
+            root.getSchema().updateVersion();
+        }
         if (!tableDmdAChat.contains("QTE_RECUE")) {
             final AlterTable alterB = new AlterTable(tableDmdAChat);
             alterB.addIntegerColumn("QTE_RECUE", 0);
             root.getBase().getDataSource().execute(alterB.asString());
             root.refetchTable("DEMANDE_ACHAT_ELEMENT");
             root.getSchema().updateVersion();
+        }
         if (!tableDmdAChat.contains("REPERE_SOURCE")) {
             final AlterTable alterB = new AlterTable(tableDmdAChat);
             alterB.addVarCharColumn("REPERE_SOURCE", 128);
             alterB.addVarCharColumn("CODE_SOURCE", 128);
             alterB.addVarCharColumn("NOM_SOURCE", 128);
             root.getBase().getDataSource().execute(alterB.asString());
             root.refetchTable("DEMANDE_ACHAT_ELEMENT");
             root.getSchema().updateVersion();
+        }
         SQLTable tableEtatDmdAChat = root.findTable("ETAT_DEMANDE_ACHAT_ELEMENT");
         if (!tableEtatDmdAChat.contains("COLOR")) {
             final AlterTable alterB = new AlterTable(tableEtatDmdAChat);
             alterB.addIntegerColumn("COLOR", null, true);
             root.getBase().getDataSource().execute(alterB.asString());
             root.refetchTable("ETAT_DEMANDE_ACHAT_ELEMENT");
             root.getSchema().updateVersion();
+        }
         if (!tableDmdAChat.contains("T_HT")) {
             final AlterTable alterB = new AlterTable(tableDmdAChat);
             alterB.addVarCharColumn("NUMERO_SE", 128);
             alterB.addVarCharColumn("DESCRIPTIF_SE", 512);
             alterB.addVarCharColumn("REVISION", 48);
             alterB.addDecimalColumn("P_HT", 16, 6, BigDecimal.ZERO, false);
             alterB.addDecimalColumn("T_HT", 16, 6, BigDecimal.ZERO, false);
             root.getBase().getDataSource().execute(alterB.asString());
             root.refetchTable("DEMANDE_ACHAT_ELEMENT");
             root.getSchema().updateVersion();
+        }
         if (!root.contains("FACTURATION_COMMANDE_CLIENT")) {
             final SQLCreateTable createTableEtat = new SQLCreateTable(root, "FACTURATION_COMMANDE_CLIENT");
             createTableEtat.addVarCharColumn("NOM", 256);
             createTableEtat.addIntegerColumn("TYPE_FACTURE", TypeFactureCommandeClient.GLOBALE.getId());
             createTableEtat.addBooleanColumn("CHOICE", Boolean.FALSE, false);
             createTableEtat.addForeignColumn("TYPE_REGLEMENT");
             createTableEtat.addForeignColumn("COMMANDE_CLIENT");
             createTableEtat.addForeignColumn("SAISIE_VENTE_FACTURE");
             createTableEtat.addForeignColumn("MODELE");
             createTableEtat.addIntegerColumn("AJOURS", 0);
             createTableEtat.addDecimalColumn("MONTANT", 16, 8, BigDecimal.ZERO, false);
             createTableEtat.addDecimalColumn("POURCENT", 16, 8, BigDecimal.ZERO, false);
             createTableEtat.addBooleanColumn("COMPTANT", Boolean.FALSE, false);
             createTableEtat.addColumn("DATE_PREVISIONNELLE", "date");
             root.getBase().getDataSource().execute(createTableEtat.asString());
             InstallationPanel.insertUndef(createTableEtat);
             root.refetchTable("FACTURATION_COMMANDE_CLIENT");
             root.getSchema().updateVersion();
+        }
         SQLTable tableFacturationCmd = root.getTable("FACTURATION_COMMANDE_CLIENT");
         if (!tableFacturationCmd.contains("FIN_MOIS")) {
             AlterTable alt = new AlterTable(tableFacturationCmd);
             alt.addBooleanColumn("FIN_MOIS", Boolean.FALSE, false);
             root.getBase().getDataSource().execute(alt.asString());
             root.refetchTable("FACTURATION_COMMANDE_CLIENT");
             root.getSchema().updateVersion();
+        }
         if (!tableFacturationCmd.contains("LENJOUR")) {
             AlterTable alt = new AlterTable(tableFacturationCmd);
             alt.addIntegerColumn("LENJOUR", 0);
             root.getBase().getDataSource().execute(alt.asString());
             root.refetchTable("FACTURATION_COMMANDE_CLIENT");
             root.getSchema().updateVersion();
+        }
         if (!root.contains("CHIFFRAGE_COMMANDE_CLIENT")) {
             final SQLCreateTable createTableEtat = new SQLCreateTable(root, "CHIFFRAGE_COMMANDE_CLIENT");
             createTableEtat.addVarCharColumn("NOM", 256);
             createTableEtat.addForeignColumn("COMMANDE_CLIENT");
             createTableEtat.addForeignColumn("UNITE_VENTE");
             createTableEtat.addForeignColumn("FAMILLE_ARTICLE");
             createTableEtat.addDecimalColumn("QTE", 16, 8, BigDecimal.ZERO, false);
             createTableEtat.addDecimalColumn("PA_HT", 16, 8, BigDecimal.ZERO, false);
             createTableEtat.addDecimalColumn("PV_HT", 16, 8, BigDecimal.ZERO, false);
             createTableEtat.addDecimalColumn("T_PV_HT", 16, 8, BigDecimal.ZERO, false);
             createTableEtat.addDecimalColumn("T_PA_HT", 16, 8, BigDecimal.ZERO, false);
             createTableEtat.addDecimalColumn("MARGE", 16, 8, BigDecimal.ZERO, false);
             root.getBase().getDataSource().execute(createTableEtat.asString());
             InstallationPanel.insertUndef(createTableEtat);
             root.refetchTable("CHIFFRAGE_COMMANDE_CLIENT");
             root.getSchema().updateVersion();
+        }
         // VARIABLE_SALARIE
         SQLTable tableVarSal = root.findTable("VARIABLE_SALARIE");
         if (!tableVarSal.contains("HEURE_ABS_DEFAULT_VAL")) {
             final AlterTable alterB = new AlterTable(root.getTable("VARIABLE_SALARIE"));
             for (org.openconcerto.sql.model.SQLField sqlField : tableVarSal.getContentFields()) {
                 String field = sqlField.getName();
                 if (!field.equalsIgnoreCase("ID_USER_COMMON_CREATE") && !field.equalsIgnoreCase("ID_USER_COMMON_MODIFY") && !field.equalsIgnoreCase("MODIFICATION_DATE")
                         && !field.equalsIgnoreCase("CREATION_DATE") && !field.equalsIgnoreCase("HEURE_TRAV")) {
                     alterB.addColumn(field + "_DEFAULT_VAL", "real DEFAULT 0");
+                }
+            }
             root.getBase().getDataSource().execute(alterB.asString());
             root.refetchTable("VARIABLE_SALARIE");
             root.getSchema().updateVersion();
+        }
         SQLTable tableFpaye = root.findTable("FICHE_PAYE");
         if (!tableFpaye.contains("TAXE_CM_SAL")) {
             final AlterTable alterB = new AlterTable(tableFpaye);
             alterB.addDecimalColumn("SAL_BASE_BRUT", 16, 2, BigDecimal.ZERO, false);
             alterB.addDecimalColumn("TAXE_CM_SAL", 16, 2, BigDecimal.ZERO, false);
             alterB.addDecimalColumn("TAXE_CM_PAT", 16, 2, BigDecimal.ZERO, false);
             alterB.addDecimalColumn("SAL_BRUT_COTISABLE", 16, 2, BigDecimal.ZERO, false);
             alterB.addDecimalColumn("SAL_BRUT_TAXABLE", 16, 2, BigDecimal.ZERO, false);
             root.getBase().getDataSource().execute(alterB.asString());
             root.refetchTable("FICHE_PAYE");
             root.getSchema().updateVersion();
             SQLTable tableElementPaye = root.getTable("FICHE_PAYE_ELEMENT");
             AlterTable tEltPaye = new AlterTable(tableElementPaye);
             tEltPaye.alterColumn("NOM", EnumSet.allOf(Properties.class), "varchar(512)", "''", false);
             tableElementPaye.getBase().getDataSource().execute(tEltPaye.asString());
             tableElementPaye.getSchema().updateVersion();
             tableElementPaye.fetchFields();
+        }
         SQLTable tableCpaye = root.findTable("CUMULS_PAYE");
         if (!tableCpaye.contains("TAXE_CM_SAL_C")) {
             final AlterTable alterB = new AlterTable(tableCpaye);
             alterB.addDecimalColumn("TAXE_CM_SAL_C", 16, 2, BigDecimal.ZERO, false);
             alterB.addDecimalColumn("TAXE_CM_PAT_C", 16, 2, BigDecimal.ZERO, false);
             alterB.addDecimalColumn("SAL_BRUT_COTISABLE_C", 16, 2, BigDecimal.ZERO, false);
             alterB.addDecimalColumn("SAL_BRUT_TAXABLE_C", 16, 2, BigDecimal.ZERO, false);
             alterB.addDecimalColumn("SAL_BASE_BRUT_C", 16, 2, BigDecimal.ZERO, false);
             root.getBase().getDataSource().execute(alterB.asString());
             root.refetchTable("CUMULS_PAYE");
             root.getSchema().updateVersion();
+        }
         SQLTable tableEtatCivil = root.findTable("ETAT_CIVIL");
         if (!tableEtatCivil.contains("NATIONNALITE")) {
             final AlterTable alterB = new AlterTable(tableEtatCivil);
             alterB.addVarCharColumn("NATIONNALITE", 256);
             alterB.addVarCharColumn("CNPS", 256);
             alterB.addVarCharColumn("TYPE_PIECE_IDENTITE", 256);
             alterB.addVarCharColumn("NUMERO_PIECE", 256);
             alterB.addVarCharColumn("NOM_PERE", 256);
             alterB.addVarCharColumn("NOM_MERE", 256);
             alterB.addVarCharColumn("CONJOINT_NOM", 256);
             alterB.addColumn("CONJOINT_DATE_NAISSANCE", "date");
             alterB.addVarCharColumn("CONJOINT_LIEU_NAISSANCE", 256);
             alterB.addVarCharColumn("CONJOINT_PROFESSION", 256);
             alterB.addColumn("DATE_DEBUT_PIECE", "date");
             alterB.addColumn("DATE_FIN_PIECE", "date");
             root.getBase().getDataSource().execute(alterB.asString());
             root.refetchTable("ETAT_CIVIL");
             root.getSchema().updateVersion();
+        }
         // ARTICLE CLIENT
         if (!root.contains("ARTICLE_CODE_CLIENT")) {
             final SQLCreateTable createTableEtat = new SQLCreateTable(root, "ARTICLE_CODE_CLIENT");
             createTableEtat.addVarCharColumn("NOM", 512);
             createTableEtat.addVarCharColumn("CODE", 512);
             createTableEtat.addForeignColumn("CLIENT");
             createTableEtat.addForeignColumn("ARTICLE");
             root.getBase().getDataSource().execute(createTableEtat.asString());
             InstallationPanel.insertUndef(createTableEtat);
             root.refetchTable("ARTICLE_CODE_CLIENT");
             root.getSchema().updateVersion();
+        }
         SQLTable tableClient = root.getTable("CLIENT");
         if (!tableClient.contains("CODE_FOURNISSEUR")) {
             final AlterTable alter = new AlterTable(tableClient);
             alter.addVarCharColumn("CODE_FOURNISSEUR", 256);
             tableCompte.getBase().getDataSource().execute(alter.asString());
             tableCompte.getSchema().updateVersion();
             tableCompte.fetchFields();
+        }
         SQLTable tableArticle = root.getTable("ARTICLE");
         if (!tableArticle.contains("TARE")) {
             final AlterTable alterArticle = new AlterTable(tableArticle);
             alterArticle.addDecimalColumn("TARE", 16, 8, BigDecimal.ZERO, true);
             tableArticle.getBase().getDataSource().execute(alterArticle.asString());
             tableArticle.getSchema().updateVersion();
             tableArticle.fetchFields();
+        }
         if (!tableArticle.contains("IFCO")) {
             final AlterTable alterArticle = new AlterTable(tableArticle);
             alterArticle.addIntegerColumn("IFCO", 0);
             tableArticle.getBase().getDataSource().execute(alterArticle.asString());
             tableArticle.getSchema().updateVersion();
             tableArticle.fetchFields();
+        }
         SQLTable tableFournisseur = root.getTable("FOURNISSEUR");
         if (!tableFournisseur.contains("NUMERO_TVA")) {
             final AlterTable alter = new AlterTable(tableFournisseur);
             alter.addVarCharColumn("NUMERO_TVA", 128);
             alter.addVarCharColumn("SIRET", 128);
             tableFournisseur.getBase().getDataSource().execute(alter.asString());
             tableFournisseur.getSchema().updateVersion();
             tableFournisseur.fetchFields();
+        }
         // POS
         final SQLTable caisseT = root.getTable("CAISSE");
         final String registerLogTableName = "CAISSE_JOURNAL";
         if (!root.contains(registerLogTableName)) {
             final SQLTable receiptT = root.getTable("TICKET_CAISSE");
             final SQLTable userT = root.findTable("USER_COMMON", true);
             final SQLCreateTable createLogT = new SQLCreateTable(caisseT.getDBRoot(), registerLogTableName);
             createLogT.setCreateOrder(false);
             AddMDFields.addFields(createLogT, userT);
             createLogT.addForeignColumn("ID_CAISSE", caisseT);
             createLogT.addDateAndTimeColumn("DATE");
             createLogT.addForeignColumn("ID_USER", userT);
             createLogT.addVarCharColumn("EVT", 128);
             createLogT.addVarCharColumn("CREATOR", 128);
             createLogT.addVarCharColumn("CREATOR_VERSION", 128);
             createLogT.addUniqueConstraint("logSequence", Arrays.asList("ID_CAISSE", "DATE"));
             final SQLCreateTable createClotureT = new SQLCreateTable(caisseT.getDBRoot(), "CAISSE_CLOTURE");
             createClotureT.setCreateOrder(false);
             AddMDFields.addFields(createClotureT, userT);
             createClotureT.addForeignColumn("ID_ENTREE_JOURNAL", createLogT);
             createClotureT.addVarCharColumn("PERIODE", 32);
             final String dateType = createClotureT.getSyntax().getTypeNames(java.sql.Date.class).iterator().next();
             createClotureT.addColumn("DEBUT", dateType, null, false);
             createClotureT.addColumn("FIN", dateType, null, false);
             createClotureT.addDecimalColumn("TOTAL_TTC", 16, 6, BigDecimal.ZERO, false);
             // don't use foreign keys, we actually want redundant info so that we can check
             // coherence
             // nullable to allow for days without any sales
             createClotureT.addVarCharColumn("PREMIER_TICKET", 64, false, "null", true);
             createClotureT.addVarCharColumn("PREMIER_TICKET_HASH", 128, false, "null", true);
             createClotureT.addVarCharColumn("DERNIER_TICKET", 64, false, "null", true);
             createClotureT.addVarCharColumn("DERNIER_TICKET_HASH", 128, false, "null", true);
             createClotureT.addUniqueConstraint("uniqueness", Arrays.asList("ID_ENTREE_JOURNAL"));
             final AlterTable alterRegister = new AlterTable(caisseT);
             alterRegister.addForeignColumn("ID_DERNIERE_ENTREE_JOURNAL", createLogT);
             alterRegister.addForeignColumn("ID_DERNIERE_CLOTURE", createClotureT);
             final AlterTable alterReceipt = new AlterTable(receiptT);
             alterReceipt.addVarCharColumn("FILE_HASH", 128, false, "null", true);
             alterReceipt.addVarCharColumn("FILE_HASH_PREVIOUS", 128, false, "null", true);
             alterReceipt.addUniqueConstraint("uniqueNumber", new UniqueConstraintCreatorHelper(Arrays.asList("NUMERO"), TicketCaisseSQLElement.UNARCHIVED_WHERE) {
                 @Override
                 public Object getObject(SQLSyntax s) {
                     if (s.getSystem() == SQLSystem.H2) {
                         return TicketCaisseSQLElement.UniqueNumber_PartialUniqueTrigger.class;
                     } else {
                         return super.getObject(s);
+                    }
+                }
             });
             alterReceipt.addUniqueConstraint("uniqueDate", new UniqueConstraintCreatorHelper(Arrays.asList("ID_CAISSE", "DATE"), TicketCaisseSQLElement.DATE_WHERE) {
                 @Override
                 public Object getObject(SQLSyntax s) {
                     if (s.getSystem() == SQLSystem.H2) {
                         return TicketCaisseSQLElement.UniqueDate_PartialUniqueTrigger.class;
                     } else {
                         return super.getObject(s);
+                    }
+                }
             });
             root.createTables(createLogT, createClotureT);
             root.getDBSystemRoot().getDataSource().execute(alterRegister.asString());
             root.getDBSystemRoot().getDataSource().execute(alterReceipt.asString());
             caisseT.getSchema().updateVersion();
             alterRegister.getTable().fetchFields();
             alterReceipt.getTable().fetchFields();
+        }
         SQLTable tableArt = root.getTable("ARTICLE");
         if (!tableArt.contains("MASQUE_CAISSE")) {
             final AlterTable alterArt = new AlterTable(tableArt);
             alterArt.addBooleanColumn("MASQUE_CAISSE", Boolean.FALSE, false);
             tableArt.getBase().getDataSource().execute(alterArt.asString());
             tableArt.getSchema().updateVersion();
             tableArt.fetchFields();
+        }
         if (!tableArt.contains("ADDITIONAL_TICKET_COPY")) {
             final AlterTable alterArt = new AlterTable(tableArt);
             alterArt.addBooleanColumn("ADDITIONAL_TICKET_COPY", Boolean.FALSE, false);
             tableArt.getBase().getDataSource().execute(alterArt.asString());
             tableArt.getSchema().updateVersion();
             tableArt.fetchFields();
+        }
         SQLTable tableDevisAcompte = root.getTable("DEVIS");
         if (!tableDevisAcompte.contains("T_ACOMPTE")) {
             final AlterTable alterB = new AlterTable(tableDevisAcompte);
             alterB.addLongColumn("T_ACOMPTE", 0L, false);
             root.getBase().getDataSource().execute(alterB.asString());
             root.refetchTable("DEVIS");
             root.getSchema().updateVersion();
             SQLTable tableEncaisse = root.getTable("ENCAISSER_MONTANT");
             final AlterTable alterC = new AlterTable(tableEncaisse);
             alterC.addBooleanColumn("ACOMPTE", Boolean.FALSE, false);
             alterC.addForeignColumn("ID_DEVIS", tableDevisAcompte);
             root.getBase().getDataSource().execute(alterC.asString());
             root.refetchTable("ENCAISSER_MONTANT");
             root.getSchema().updateVersion();
             SQLTable tableVF = root.getTable("SAISIE_VENTE_FACTURE");
             final AlterTable alterF = new AlterTable(tableVF);
             alterF.addLongColumn("T_ACOMPTE", 0L, false);
             root.getBase().getDataSource().execute(alterF.asString());
             root.refetchTable("SAISIE_VENTE_FACTURE");
             root.getSchema().updateVersion();
+        }
         // Paye Simplifiée
         if (!tableFpaye.contains("ALLEGEMENT_COTISATION")) {
             final AlterTable alterB = new AlterTable(tableFpaye);
             alterB.addDecimalColumn("ALLEGEMENT_COTISATION", 16, 2, BigDecimal.ZERO, false);
             alterB.addDecimalColumn("CICE", 16, 2, BigDecimal.ZERO, false);
             alterB.addDecimalColumn("AVANTAGE_NATURE", 16, 2, BigDecimal.ZERO, false);
             alterB.addDecimalColumn("HEURE_TRAV", 16, 2, BigDecimal.ZERO, false);
             root.getBase().getDataSource().execute(alterB.asString());
             root.refetchTable("FICHE_PAYE");
             root.getSchema().updateVersion();
             final AlterTable alterC = new AlterTable(tableCpaye);
             alterC.addDecimalColumn("ALLEGEMENT_COTISATION_C", 16, 2, BigDecimal.ZERO, false);
             alterC.addDecimalColumn("CICE_C", 16, 2, BigDecimal.ZERO, false);
             alterC.addDecimalColumn("AVANTAGE_NATURE_C", 16, 2, BigDecimal.ZERO, false);
             root.getBase().getDataSource().execute(alterC.asString());
             root.refetchTable("CUMULS_PAYE");
             root.getSchema().updateVersion();
+        }
         if (!tableFpaye.contains("DETAILS_CONGES")) {
             final AlterTable alterB = new AlterTable(tableFpaye);
             alterB.addVarCharColumn("DETAILS_CONGES", 512);
             root.getBase().getDataSource().execute(alterB.asString());
             root.refetchTable("FICHE_PAYE");
             root.getSchema().updateVersion();
+        }
         SQLTable tableFPayeElt = root.getTable("FICHE_PAYE_ELEMENT");
         if (!tableFPayeElt.contains("ID_STYLE")) {
             final AlterTable alterB = new AlterTable(tableFPayeElt);
             alterB.addForeignColumn("ID_STYLE", root.getTable("STYLE"));
             root.getBase().getDataSource().execute(alterB.asString());
             root.refetchTable("FICHE_PAYE_ELEMENT");
             root.getSchema().updateVersion();
+        }
         if (!tableVarSal.contains("IJSS_BRUT")) {
             final AlterTable alterB = new AlterTable(tableVarSal);
             List<String> f = Arrays.asList("IJSS_BRUT", "IJSS_NET", "FRAIS_PRO", "RBT_TRANSPORT");
             for (String field : f) {
                 alterB.addColumn(field, "real DEFAULT 0");
                 alterB.addColumn(field + "_DEFAULT_VAL", "real DEFAULT 0");
+            }
             root.getBase().getDataSource().execute(alterB.asString());
             root.refetchTable(tableVarSal.getName());
             root.getSchema().updateVersion();
+        }
         SQLTable tableDevis = root.getTable("DEVIS");
         if (!tableDevis.contains("ID_TAXE_PORT")) {
             final AlterTable alterB = new AlterTable(tableDevis);
             alterB.addForeignColumn("ID_TAXE_PORT", root.getTable("TAXE"));
             root.getBase().getDataSource().execute(alterB.asString());
             root.refetchTable(tableDevis.getName());
             root.getSchema().updateVersion();
+        }
         // 1.5.3
         List<String> tableRemiseF = Arrays.asList("COMMANDE", "BON_RECEPTION", "FACTURE_FOURNISSEUR");
         for (String t : tableRemiseF) {
             final SQLTable tableCommande = root.getTable(t);
             AlterTable tCommande = new AlterTable(tableCommande);
             boolean updateCmd = false;
             if (!tableCommande.contains("ID_TAXE_PORT")) {
                 updateCmd = true;
                 tCommande.addForeignColumn("ID_TAXE_PORT", root.getTable("TAXE"));
+            }
             if (!tableCommande.contains("PORT_HT")) {
                 updateCmd = true;
                 tCommande.addLongColumn("PORT_HT", Long.valueOf(0), false);
+            }
             if (!tableCommande.contains("REMISE_HT")) {
                 updateCmd = true;
                 tCommande.addLongColumn("REMISE_HT", Long.valueOf(0), false);
+            }
             if (updateCmd) {
                 tableCommande.getBase().getDataSource().execute(tCommande.asString());
                 tableCommande.getSchema().updateVersion();
                 tableCommande.fetchFields();
+            }
+        }
         List<String> tableElement = Arrays.asList("FACTURE_FOURNISSEUR_ELEMENT", "COMMANDE_ELEMENT", "BON_RECEPTION_ELEMENT", "DEMANDE_PRIX_ELEMENT");
         for (String tableName : tableElement) {
             final SQLTable table = root.getTable(tableName);
             if (!table.contains("ID_FAMILLE_ARTICLE")) {
                 AlterTable t = new AlterTable(table);
                 t.addForeignColumn("ID_FAMILLE_ARTICLE", root.getTable("FAMILLE_ARTICLE"));
                 table.getBase().getDataSource().execute(t.asString());
                 root.refetchTable(table.getName());
                 root.getSchema().updateVersion();
+            }
+        }
         final SQLTable tableBlElt = root.getTable("BON_RECEPTION_ELEMENT");
         if (!tableBlElt.contains("QTE_ORIGINE")) {
             AlterTable t = new AlterTable(tableBlElt);
             t.addIntegerColumn("QTE_ORIGINE", null, true);
             tableBlElt.getBase().getDataSource().execute(t.asString());
             root.refetchTable(tableBlElt.getName());
             root.getSchema().updateVersion();
+        }
         // Caisse 1.5.3
         if (!root.getTable(registerLogTableName).contains("HOST_NAME")) {
             final AlterTable alterRegisterLog = new AlterTable(root.getTable(registerLogTableName));
             // to check for shared register installations
             alterRegisterLog.addVarCharColumn("HOST_NAME", 128, true, null, true);
             alterRegisterLog.addVarCharColumn("HOST_USER", 128, true, null, true);
             // no longer needed since we need more than the last entry, just remove the duplicate
             // data and select from CAISSE_JOURNAL
             final AlterTable alterRegister = new AlterTable(caisseT);
             alterRegister.dropColumn("ID_DERNIERE_ENTREE_JOURNAL");
             for (final String sql : ChangeTable.cat(Arrays.asList(alterRegisterLog, alterRegister))) {
                 root.getDBSystemRoot().getDataSource().execute(sql);
+            }
             caisseT.getSchema().updateVersion();
             alterRegisterLog.getTable().fetchFields();
             alterRegister.getTable().fetchFields();
+        }
         // Prefs compte AN
         SQLTable table = root.findTable("PREFS_COMPTE");
         if (!table.getFieldsName().contains("ID_JOURNAL_AN")) {
             AlterTable t = new AlterTable(table);
             t.addForeignColumn("ID_JOURNAL_AN", root.getTable("JOURNAL"));
             t.addBooleanColumn("CREATE_NUL_SOLDE_ECR", Boolean.TRUE, false);
             table.getBase().getDataSource().execute(t.asString());
             table.getSchema().updateVersion();
             table.fetchFields();
+        }
         if (!table.getFieldsName().contains("AUTO_LETTRAGE")) {
             AlterTable t = new AlterTable(table);
             t.addBooleanColumn("AUTO_LETTRAGE", Boolean.FALSE, false);
             table.getBase().getDataSource().execute(t.asString());
             table.getSchema().updateVersion();
             table.fetchFields();
+        }
+        // Etat stock
+        if (!root.contains("ETAT_STOCK")) {
+            try {
+                final SQLCreateTable createTable = new SQLCreateTable(root, "ETAT_STOCK");
+                createTable.addColumn("DATE", "date");
+                createTable.addDecimalColumn("MONTANT_HA", 16, 8, BigDecimal.ZERO, true);
+                createTable.addDecimalColumn("MONTANT_VT", 16, 8, BigDecimal.ZERO, true);
+                createTable.addBooleanColumn("INVENTAIRE", Boolean.FALSE, false);
+                root.getBase().getDataSource().execute(createTable.asString());
+                InstallationPanel.insertUndef(createTable);
+                root.refetchTable("ETAT_STOCK");
+                root.getSchema().updateVersion();
+                final SQLCreateTable createTableElt = new SQLCreateTable(root, "ETAT_STOCK_ELEMENT");
+                createTableElt.addForeignColumn("ARTICLE");
+                createTableElt.addForeignColumn("ETAT_STOCK");
+                createTableElt.addDecimalColumn("PA", 16, 8, BigDecimal.ZERO, true);
+                createTableElt.addDecimalColumn("PV", 16, 8, BigDecimal.ZERO, true);
+                createTableElt.addDecimalColumn("T_PA", 16, 8, BigDecimal.ZERO, true);
+                createTableElt.addDecimalColumn("T_PV", 16, 8, BigDecimal.ZERO, true);
+                createTableElt.addDecimalColumn("QTE", 16, 8, BigDecimal.ZERO, true);
+                createTableElt.addDecimalColumn("ECART", 16, 8, BigDecimal.ZERO, true);
+                createTableElt.addVarCharColumn("CODE", 256);
+                createTableElt.addVarCharColumn("NOM", 2048);
+                root.getBase().getDataSource().execute(createTableElt.asString());
+                InstallationPanel.insertUndef(createTableElt);
+                root.refetchTable("ETAT_STOCK_ELEMENT");
+                root.getSchema().updateVersion();
+            } catch (SQLException ex) {
+                throw new IllegalStateException("Erreur lors de la création de la table ETAT_STOCK_ELEMENT", ex);
+            }
+        } else {
+            final SQLTable tableEtatStock = root.getTable("ETAT_STOCK_ELEMENT");
+            SQLField fieldNom = tableEtatStock.getField("NOM");
+            int size = fieldNom.getType().getSize();
+            if (size == 512) {
+                AlterTable alter = new AlterTable(tableEtatStock);
+                alter.alterColumn("NOM", EnumSet.allOf(Properties.class), "varchar(" + 2048 + ")", "''", false);
+                tableEtatStock.getBase().getDataSource().execute(alter.asString());
+                tableEtatStock.getSchema().updateVersion();
+                tableEtatStock.fetchFields();
+            }
+        }
+        SQLTable tableMvtStock = root.getTable("MOUVEMENT_STOCK");
+        if (!tableMvtStock.contains("ID_ETAT_STOCK")) {
+            final AlterTable alter = new AlterTable(tableMvtStock);
+            alter.addBooleanColumn("CLOTURE", Boolean.FALSE, false);
+            alter.addBooleanColumn("OUVERTURE", Boolean.FALSE, false);
+            alter.addForeignColumn("ID_ETAT_STOCK", root.getTable("ETAT_STOCK"));
+            tableMvtStock.getBase().getDataSource().execute(alter.asString());
+            tableMvtStock.getSchema().updateVersion();
+            tableMvtStock.fetchFields();
+        }
         SQLTable tableEcr = root.getTable("ECRITURE");
         if (!tableEcr.contains("CLOTURE")) {
             final AlterTable alter = new AlterTable(tableEcr);
             alter.addBooleanColumn("CLOTURE", Boolean.FALSE, false);
             alter.addBooleanColumn("RAN", Boolean.FALSE, false);
             tableEcr.getBase().getDataSource().execute(alter.asString());
             tableEcr.getSchema().updateVersion();
             tableEcr.fetchFields();
+        }
         // Jour Km Element
         final SQLTable tkmElt = root.getTable("SAISIE_KM_ELEMENT");
         if (!tkmElt.contains("JOUR")) {
             final AlterTable alterKmElt = new AlterTable(tkmElt);
             alterKmElt.addIntegerColumn("JOUR", 1);
             tkmElt.getBase().getDataSource().execute(alterKmElt.asString());
             tkmElt.getSchema().updateVersion();
             tkmElt.fetchFields();
+        }
         // Ref bancaires fournisseurs et clients
         for (final SQLTable bankT : Arrays.asList(tableFournisseur, tClient)) {
             if (!bankT.contains("IBAN")) {
                 final AlterTable alter = new AlterTable(bankT);
                 alter.addVarCharColumn("IBAN", 128);
                 alter.addVarCharColumn("BIC", 128);
                 bankT.getDBSystemRoot().getDataSource().execute(alter.asString());
                 bankT.getSchema().updateVersion();
                 bankT.fetchFields();
+            }
+        }
         final SQLTable typeReglT = root.getTable("TYPE_REGLEMENT");
         if (typeReglT.getRow(TypeReglementSQLElement.PRELEVEMENT) == null) {
             final SQLRowValues directDebitVals = new SQLRowValues(typeReglT).put("NOM", "Prélèvement");
             directDebitVals.put("COMPTANT", Boolean.FALSE).put("ECHEANCE", Boolean.FALSE);
             directDebitVals.setID(TypeReglementSQLElement.PRELEVEMENT).insertVerbatim();
+        }
         if (!tableClient.contains("ACCEPTE_EMAIL")) {
             final AlterTable alter = new AlterTable(tableClient);
             alter.addBooleanColumn("ACCEPTE_EMAIL", Boolean.FALSE, false);
             alter.addBooleanColumn("ACCEPTE_COURRIER", Boolean.FALSE, false);
             alter.addBooleanColumn("ACCEPTE_SMS", Boolean.FALSE, false);
             alter.addBooleanColumn("ACCEPTE_TEL", Boolean.FALSE, false);
             exec(alter);
+        }
         final SQLTable contactT = root.getTable("CONTACT");
-        if (!contactT.contains("DATE_NAISSANCE")) {
+        if (contactT != null && !contactT.contains("DATE_NAISSANCE")) {
             final AlterTable alter = new AlterTable(contactT);
             alter.addColumn("DATE_NAISSANCE", "date");
             exec(alter);
+        }
         final SQLCreateTable createSDDMsgTable = SDDMessageSQLElement.getCreateTable(root);
         if (createSDDMsgTable != null) {
             final SQLCreateTable createMandate = SEPAMandateSQLElement.getCreateTable(root);
             root.createTables(createSDDMsgTable, createMandate);
             final SQLTable msgT = root.getTable(createSDDMsgTable.getName());
             final SQLTable mandateT = root.getTable(createMandate.getName());
             final AlterTable alterFact = new AlterTable(root.getTable(SaisieVenteFactureSQLElement.TABLENAME));
             alterFact.addForeignColumn(SaisieVenteFactureSQLElement.MESSAGE_FIELD_NAME, msgT);
             alterFact.addVarCharColumn(SaisieVenteFactureSQLElement.END2END_FIELD_NAME, 35);
             final AlterTable alterModeRegl = new AlterTable(root.getTable("MODE_REGLEMENT"));
             alterModeRegl.addForeignColumn(null, mandateT);
             for (final String sql : ChangeTable.cat(Arrays.asList(alterFact, alterModeRegl))) {
                 root.getDBSystemRoot().getDataSource().execute(sql);
+            }
             root.getSchema().updateVersion();
             root.refetch(CollectionUtils.createSet(alterFact.getName(), alterModeRegl.getName()));
             root.setMetadata(SDDMessageSQLElement.SERIAL_MD, "0");
+        } else {
+            // Before r24495 InstallationPanel.fixUnboundedVarchar() would change this field to
+            // varchar
+            final SQLField xmlField = root.getTable(SDDMessageSQLElement.TABLE_NAME).getField("XML");
+            if (xmlField.getTypeDecl().contains("char")) {
+                final AlterTable alterTable = new AlterTable(xmlField.getTable());
+                alterTable.alterColumn(xmlField.getName(), EnumSet.of(Properties.TYPE), alterTable.getSyntax().getTypeNames(Clob.class).iterator().next(), null, null);
+                root.getDBSystemRoot().getDataSource().execute(alterTable.asString());
+                root.getSchema().updateVersion();
+                xmlField.getTable().fetchFields();
+            }
+        }
         final SQLTable vcT = root.getTable("SAISIE_VENTE_COMPTOIR");
         if (!vcT.contains("ID_COMPTE_PCE_PRODUIT")) {
             final AlterTable alter = new AlterTable(vcT);
             alter.addForeignColumn("ID_COMPTE_PCE_PRODUIT", root.getTable("COMPTE_PCE"));
             alter.addForeignColumn("ID_COMPTE_PCE_SERVICE", root.getTable("COMPTE_PCE"));
             exec(alter);
+        }
+        // 1.6
+        // gestion depots des cheques
+        String chequeDepotTable = "DEPOT_CHEQUE";
+        if (!root.contains(chequeDepotTable)) {
+            // Table depot cheque
+            final SQLCreateTable createTableDepotCheque = new SQLCreateTable(root, chequeDepotTable);
+            createTableDepotCheque.addVarCharColumn("NOM", 512);
+            createTableDepotCheque.addLongColumn("MONTANT", 0L, false);
+            createTableDepotCheque.addColumn("DATE", "date");
+            if (root.contains("BANQUE")) {
+                createTableDepotCheque.addForeignColumn("ID_BANQUE", root.getTable("BANQUE"));
+            } else if (root.contains("BANQUE_POLE_PRODUIT")) {
+                createTableDepotCheque.addForeignColumn("ID_BANQUE", root.getTable("BANQUE_POLE_PRODUIT"));
+            }
+            createTableDepotCheque.addForeignColumn("ID_ECRITURE", root.getTable("ECRITURE"));
+            createTableDepotCheque.addForeignColumn("ID_MOUVEMENT", root.getTable("MOUVEMENT"));
+            root.getBase().getDataSource().execute(createTableDepotCheque.asString());
+            InstallationPanel.insertUndef(createTableDepotCheque);
+            root.refetchTable(chequeDepotTable);
+            root.getSchema().updateVersion();
+            // Table depot cheque element
+            final SQLCreateTable createTableDepotElt = new SQLCreateTable(root, chequeDepotTable + "_ELEMENT");
+            createTableDepotElt.addVarCharColumn("NUMERO", 128);
+            createTableDepotElt.addVarCharColumn("BANQUE", 128);
+            createTableDepotElt.addVarCharColumn("PIECE", 512);
+            createTableDepotElt.addLongColumn("MONTANT", 0L, false);
+            createTableDepotElt.addColumn("DATE", "date");
+            createTableDepotElt.addForeignColumn("ID_CHEQUE_A_ENCAISSER", root.getTable("CHEQUE_A_ENCAISSER"));
+            createTableDepotElt.addForeignColumn("ID_ECRITURE", root.getTable("ECRITURE"));
+            createTableDepotElt.addForeignColumn("ID_CLIENT", root.getTable("CLIENT"));
+            createTableDepotElt.addForeignColumn("ID_DEPOT_CHEQUE", root.getTable(chequeDepotTable));
+            root.getBase().getDataSource().execute(createTableDepotElt.asString());
+            InstallationPanel.insertUndef(createTableDepotElt);
+            root.refetchTable(chequeDepotTable + "_ELEMENT");
+            root.getSchema().updateVersion();
+        }
+        final SQLTable articleT = root.getTable("ARTICLE");
+        if (!articleT.contains("QTE_UNITAIRE")) {
+            final AlterTable alter = new AlterTable(articleT);
+            alter.addDecimalColumn("QTE_UNITAIRE", 16, 6, BigDecimal.ONE, false);
+            exec(alter);
+        }
+        final SQLTable articleFT = root.getTable("ARTICLE_FOURNISSEUR");
+        if (!articleFT.contains("QTE_UNITAIRE")) {
+            final AlterTable alter = new AlterTable(articleFT);
+            alter.addDecimalColumn("QTE_UNITAIRE", 16, 6, BigDecimal.ONE, false);
+            exec(alter);
+        }
+        // Cout de revient
+        if (!root.contains("COUT_REVIENT")) {
+            final SQLCreateTable createTableCR = new SQLCreateTable(root, "COUT_REVIENT");
+            createTableCR.addVarCharColumn("CODE", 48);
+            createTableCR.addVarCharColumn("NOM", 256);
+            createTableCR.addDecimalColumn("POURCENT", 16, 6, BigDecimal.ZERO, false);
+            root.getBase().getDataSource().execute(createTableCR.asString());
+            InstallationPanel.insertUndef(createTableCR);
+            root.refetchTable("COUT_REVIENT");
+            root.getSchema().updateVersion();
+            final AlterTable alterArticle = new AlterTable(root.getTable("ARTICLE"));
+            alterArticle.addForeignColumn("ID_COUT_REVIENT", root.findTable("COUT_REVIENT"));
+            exec(alterArticle);
+            root.refetchTable("ARTICLE");
+            root.getSchema().updateVersion();
+        }
+        if (!root.getTable("ARTICLE").contains("MARGE_WITH_COUT_REVIENT")) {
+            final AlterTable alterArticle = new AlterTable(root.getTable("ARTICLE"));
+            alterArticle.addBooleanColumn("MARGE_WITH_COUT_REVIENT", Boolean.FALSE, false);
+            exec(alterArticle);
+            root.refetchTable("ARTICLE");
+            root.getSchema().updateVersion();
+        }
+        if (!root.getTable("FACTURE_FOURNISSEUR").contains("NET_A_PAYER")) {
+            final AlterTable alterFactF = new AlterTable(root.getTable("FACTURE_FOURNISSEUR"));
+            alterFactF.addLongColumn("NET_A_PAYER", 0L, false);
+            alterFactF.addLongColumn("AVOIR_TTC", 0L, false);
+            exec(alterFactF);
+            root.refetchTable("FACTURE_FOURNISSEUR");
+            root.getSchema().updateVersion();
+        }
+        // Frais Document
+        if (!root.contains("FRAIS_DOCUMENT")) {
+            final SQLCreateTable createTableCR = new SQLCreateTable(root, "FRAIS_DOCUMENT");
+            createTableCR.addVarCharColumn("CODE", 48);
+            createTableCR.addVarCharColumn("NOM", 256);
+            createTableCR.addLongColumn("MONTANT_HT", 0L, false);
+            createTableCR.addForeignColumn("ID_TAXE", root.getTable("TAXE"));
+            createTableCR.addLongColumn("MONTANT_TTC", 0L, false);
+            root.getBase().getDataSource().execute(createTableCR.asString());
+            InstallationPanel.insertUndef(createTableCR);
+            root.refetchTable("FRAIS_DOCUMENT");
+            final AlterTable alterClient = new AlterTable(root.getTable("CLIENT"));
+            alterClient.addForeignColumn("ID_FRAIS_DOCUMENT", root.findTable("FRAIS_DOCUMENT"));
+            exec(alterClient);
+            root.refetchTable("CLIENT");
+            List<String> tables = Arrays.asList("DEVIS", "COMMANDE_CLIENT", "BON_DE_LIVRAISON", "SAISIE_VENTE_FACTURE");
+            for (String tableToUp : tables) {
+                final AlterTable alter = new AlterTable(root.getTable(tableToUp));
+                alter.addLongColumn("FRAIS_DOCUMENT_HT", 0L, false);
+                alter.addForeignColumn("ID_TAXE_FRAIS_DOCUMENT", root.getTable("TAXE"));
+                exec(alter);
+                root.refetchTable(tableToUp);
+            }
+            root.getSchema().updateVersion();
+        }
+        final SQLTable chequeAvoirT = root.getTable("CHEQUE_AVOIR_CLIENT");
+        String tBanque = root.contains("BANQUE") ? "BANQUE" : "BANQUE_POLE_PRODUIT";
+        if (!chequeAvoirT.contains("ID_" + tBanque)) {
+            final AlterTable alterChqAvoir = new AlterTable(chequeAvoirT);
+            alterChqAvoir.addForeignColumn("ID_" + tBanque, root.findTable(tBanque));
+            exec(alterChqAvoir);
+        }
+        // Prelevement à la source
+        final SQLTable fichePayeT = root.getTable("FICHE_PAYE");
+        if (!fichePayeT.contains("ID_PAS")) {
+            final SQLCreateTable createTablePas = new SQLCreateTable(root, "PAS");
+            createTablePas.addForeignColumn("ID_TYPE_TAUX_PAS", root.findTable("TYPE_TAUX_PAS"));
+            createTablePas.addVarCharColumn("CODE_PAS", 256);
+            createTablePas.addDecimalColumn("TAUX_PAS", 16, 2, BigDecimal.ZERO, false);
+            root.getBase().getDataSource().execute(createTablePas.asString());
+            InstallationPanel.insertUndef(createTablePas);
+            root.refetchTable("PAS");
+            root.getSchema().updateVersion();
+            final AlterTable alterFichePaye = new AlterTable(fichePayeT);
+            alterFichePaye.addForeignColumn("ID_PAS", root.findTable("PAS"));
+            alterFichePaye.addDecimalColumn("TOTAL_PAS", 16, 2, BigDecimal.ZERO, false);
+            alterFichePaye.addDecimalColumn("NET_AVANT_PAS", 16, 2, BigDecimal.ZERO, false);
+            exec(alterFichePaye);
+            final SQLTable tableSal = root.getTable("SALARIE");
+            final AlterTable alterSalarie = new AlterTable(tableSal);
+            alterSalarie.addForeignColumn("ID_PAS", root.findTable("PAS"));
+            exec(alterSalarie);
+            final AlterTable alterEtatCivil = new AlterTable(tableEtatCivil);
+            alterEtatCivil.addVarCharColumn("NTT", 50);
+            exec(alterEtatCivil);
+            Map<String, SQLRow> typeTauxPasMap = new HashMap<>();
+            SQLSelect selTypeTaux = new SQLSelect();
+            selTypeTaux.addSelect(root.findTable("TYPE_TAUX_PAS").getKey());
+            selTypeTaux.addSelect(root.findTable("TYPE_TAUX_PAS").getField("CODE"));
+            List<SQLRow> resultTypeTaux = SQLRowListRSH.execute(selTypeTaux);
+            for (SQLRow sqlRow : resultTypeTaux) {
+                typeTauxPasMap.put(sqlRow.getString("CODE"), sqlRow);
+            }
+            SQLSelect selSal = new SQLSelect();
+            selSal.addSelect(tableSal.getKey());
+            List<SQLRow> rowSal = SQLRowListRSH.execute(selSal);
+            for (SQLRow sqlRow : rowSal) {
+                SQLRowValues rowValsSal = sqlRow.createEmptyUpdateRow();
+                final SQLRowValues putRowValues = rowValsSal.putRowValues("ID_PAS");
+                putRowValues.put("ID_TYPE_TAUX_PAS", typeTauxPasMap.get("13").getID());
+                putRowValues.put("TAUX_PAS", BigDecimal.ZERO);
+                putRowValues.commit();
+            }
+        }
+        if (!root.contains("ARTICLE_TARIF_FOURNISSEUR")) {
+            final SQLCreateTable createTable = new SQLCreateTable(root, "ARTICLE_TARIF_FOURNISSEUR");
+            createTable.addForeignColumn("ARTICLE");
+            createTable.addForeignColumn("FOURNISSEUR");
+            createTable.addVarCharColumn("REF_FOURNISSEUR", 500);
+            createTable.addVarCharColumn("TYPE_REAPPRO", 256);
+            createTable.addVarCharColumn("CONDITIONS", 1024);
+            createTable.addVarCharColumn("ACHETEUR", 256);
+            createTable.addVarCharColumn("CODE_PAYS_ORIGINE", 256);
+            createTable.addDecimalColumn("PRIX_ACHAT_DEVISE_F", 16, 6, BigDecimal.valueOf(0), false);
+            createTable.addDecimalColumn("PRIX_ACHAT", 16, 6, BigDecimal.valueOf(0), false);
+            createTable.addIntegerColumn("QTE", 1);
+            createTable.addDateAndTimeColumn("DATE_PRIX");
+            // createTable.addDecimalColumn("COEF_TRANSPORT_PORT", 16, 6, BigDecimal.valueOf(0),
+            // false);
+            // createTable.addDecimalColumn("COEF_TAXE_D", 16, 6, BigDecimal.valueOf(0), false);
+            // createTable.addDecimalColumn("COEF_TRANSPORT_SIEGE", 16, 6, BigDecimal.valueOf(0),
+            // false);
+            // createTable.addDecimalColumn("COEF_FRAIS_MOULE", 16, 6, BigDecimal.valueOf(0),
+            // false);
+            // createTable.addDecimalColumn("COEF_FRAIS_INDIRECTS", 16, 6, BigDecimal.valueOf(0),
+            // false);
+            createTable.addIntegerColumn("DELAI_REAPPRO", 0);
+            createTable.addIntegerColumn("DELAI_TRANSPORT", 0);
+            createTable.addIntegerColumn("PRIORITE", 0);
+            createTable.addDecimalColumn("COEF_PRIX_MINI", 16, 2, BigDecimal.valueOf(0), false);
+            root.getBase().getDataSource().execute(createTable.asString());
+            InstallationPanel.insertUndef(createTable);
+            root.refetchTable("ARTICLE_TARIF_FOURNISSEUR");
+            root.getSchema().updateVersion();
+        }
+        // Gestion des catègories comptables
+        if (!root.contains("CATEGORIE_COMPTABLE")) {
+            final SQLCreateTable createTable = new SQLCreateTable(root, "CATEGORIE_COMPTABLE");
+            createTable.addVarCharColumn("NOM", 128);
+            createTable.addForeignColumn("ID_TAXE_VENTE", root.getTable("TAXE"));
+            createTable.addForeignColumn("ID_TAXE_ACHAT", root.getTable("TAXE"));
+            createTable.addForeignColumn("ID_COMPTE_PCE_VENTE", root.getTable("COMPTE_PCE"));
+            createTable.addForeignColumn("ID_COMPTE_PCE_ACHAT", root.getTable("COMPTE_PCE"));
+            try {
+                root.getBase().getDataSource().execute(createTable.asString());
+                InstallationPanel.insertUndef(createTable);
+                root.refetchTable("CATEGORIE_COMPTABLE");
+                root.getSchema().updateVersion();
+            } catch (SQLException ex) {
+                throw new IllegalStateException("Erreur lors de la création de la table " + "CATEGORIE_COMPTABLE", ex);
+            }
+            AlterTable tableClientCat = new AlterTable(root.getTable("CLIENT"));
+            tableClientCat.addForeignColumn("ID_CATEGORIE_COMPTABLE", root.getTable("CATEGORIE_COMPTABLE"));
+            root.getBase().getDataSource().execute(tableClientCat.asString());
+            root.refetchTable("CLIENT");
+            root.getSchema().updateVersion();
+            AlterTable tableF = new AlterTable(root.getTable("FOURNISSEUR"));
+            tableF.addForeignColumn("ID_CATEGORIE_COMPTABLE", root.getTable("CATEGORIE_COMPTABLE"));
+            root.getBase().getDataSource().execute(tableF.asString());
+            root.refetchTable("FOURNISSEUR");
+            root.getSchema().updateVersion();
+            final SQLCreateTable createTableArtCat = new SQLCreateTable(root, "ARTICLE_CATEGORIE_COMPTABLE");
+            createTableArtCat.addForeignColumn("ID_CATEGORIE_COMPTABLE", root.getTable("CATEGORIE_COMPTABLE"));
+            createTableArtCat.addForeignColumn("ID_ARTICLE", root.getTable("ARTICLE"));
+            createTableArtCat.addForeignColumn("ID_TAXE_ACHAT", root.getTable("TAXE"));
+            createTableArtCat.addForeignColumn("ID_TAXE_VENTE", root.getTable("TAXE"));
+            createTableArtCat.addForeignColumn("ID_COMPTE_PCE_VENTE", root.getTable("COMPTE_PCE"));
+            createTableArtCat.addForeignColumn("ID_COMPTE_PCE_ACHAT", root.getTable("COMPTE_PCE"));
+            try {
+                root.getBase().getDataSource().execute(createTableArtCat.asString());
+                InstallationPanel.insertUndef(createTableArtCat);
+                root.refetchTable("CATEGORIE_COMPTABLE");
+                root.getSchema().updateVersion();
+            } catch (SQLException ex) {
+                throw new IllegalStateException("Erreur lors de la création de la table " + "ARTICLE_CATEGORIE_COMPTABLE", ex);
+            }
+        }
+        // Article fournisseur secondaire
+        if (!root.contains("ARTICLE_FOURNISSEUR_SECONDAIRE")) {
+            final SQLCreateTable createTable = new SQLCreateTable(root, "ARTICLE_FOURNISSEUR_SECONDAIRE");
+            createTable.addForeignColumn("ARTICLE");
+            createTable.addForeignColumn("FOURNISSEUR");
+            try {
+                root.getBase().getDataSource().execute(createTable.asString());
+                InstallationPanel.insertUndef(createTable);
+                root.refetchTable("ARTICLE_FOURNISSEUR_SECONDAIRE");
+                root.getSchema().updateVersion();
+            } catch (SQLException ex) {
+                throw new IllegalStateException("Erreur lors de la création de la table ARTICLE_FOURNISSEUR_SECONDAIRE", ex);
+            }
+        }
+        if (!root.getTable("ARTICLE").contains("AUTO_PRIX_ACHAT_NOMENCLATURE")) {
+            AlterTable t = new AlterTable(root.getTable("ARTICLE"));
+            t.addBooleanColumn("AUTO_PRIX_ACHAT_NOMENCLATURE", false, false);
+            root.getTable("ARTICLE").getBase().getDataSource().execute(t.asString());
+            root.refetchTable(root.getTable("ARTICLE").getName());
+            root.getSchema().updateVersion();
+        }
+        // Gestion multidepot
+        {
+            // Ajout table depot
+            if (!root.contains("DEPOT_STOCK")) {
+                final SQLCreateTable createTable = new SQLCreateTable(root, "DEPOT_STOCK");
+                createTable.addVarCharColumn("CODE", 25);
+                createTable.addVarCharColumn("NOM", 256);
+                createTable.addVarCharColumn("UI_LOCK", 256);
+                createTable.addForeignColumn("ID_USER_UI_LOCK", root.findTable("USER_COMMON"));
+                // sqlRowValues.put("UI_LOCK", "ro");
+                // sqlRowValues.put("ID_USER_UI_LOCK", 2);
+                try {
+                    root.getBase().getDataSource().execute(createTable.asString());
+                    InstallationPanel.insertUndef(createTable);
+                    root.refetchTable("DEPOT_STOCK");
+                    root.getSchema().updateVersion();
+                    SQLRowValues rowValsDefStock = new SQLRowValues(root.getTable("DEPOT_STOCK"));
+                    rowValsDefStock.put("NOM", "Principal");
+                    rowValsDefStock.put("UI_LOCK", "ro");
+                    rowValsDefStock.commit();
+                } catch (SQLException ex) {
+                    throw new IllegalStateException("Erreur lors de la création de la table " + "DEPOT_STOCK", ex);
+                }
+            }
+            List<String> tableElementDepot = Arrays.asList("FACTURE_FOURNISSEUR_ELEMENT", "DEVIS_ELEMENT", "COMMANDE_ELEMENT", "BON_RECEPTION_ELEMENT", "COMMANDE_CLIENT_ELEMENT",
+                    "BON_DE_LIVRAISON_ELEMENT", "SAISIE_VENTE_FACTURE_ELEMENT", "AVOIR_CLIENT_ELEMENT", "DEMANDE_PRIX_ELEMENT");
+            for (String tableName : tableElementDepot) {
+                final SQLTable tableToAddDepot = root.getTable(tableName);
+                if (!tableToAddDepot.contains("ID_DEPOT_STOCK")) {
+                    AlterTable t = new AlterTable(tableToAddDepot);
+                    t.addForeignColumn("ID_DEPOT_STOCK", root.getTable("DEPOT_STOCK"));
+                    tableToAddDepot.getBase().getDataSource().execute(t.asString());
+                    root.refetchTable(tableToAddDepot.getName());
+                    root.getSchema().updateVersion();
+                }
+            }
+            // Depot defaut
+            if (!root.getTable("ARTICLE").contains("ID_DEPOT_STOCK")) {
+                AlterTable t = new AlterTable(root.getTable("ARTICLE"));
+                t.addForeignColumn("ID_DEPOT_STOCK", root.getTable("DEPOT_STOCK"));
+                root.getTable("ARTICLE").getBase().getDataSource().execute(t.asString());
+                root.refetchTable(root.getTable("ARTICLE").getName());
+                root.getSchema().updateVersion();
+            }
+            // Liason depot stock
+            if (!root.getTable("STOCK").contains("ID_DEPOT_STOCK")) {
+                AlterTable t = new AlterTable(root.getTable("STOCK"));
+                t.addForeignColumn("ID_DEPOT_STOCK", root.getTable("DEPOT_STOCK"));
+                t.addForeignColumn("ID_ARTICLE", root.getTable("ARTICLE"));
+                t.addColumn("QTE_MIN", "real DEFAULT 0");
+                root.getTable("STOCK").getBase().getDataSource().execute(t.asString());
+                root.refetchTable(root.getTable("STOCK").getName());
+                root.getSchema().updateVersion();
+            }
+            // Ajout depot sur mvt
+            if (!root.getTable("MOUVEMENT_STOCK").contains("ID_STOCK")) {
+                AlterTable t = new AlterTable(root.getTable("MOUVEMENT_STOCK"));
+                t.addForeignColumn("ID_STOCK", root.getTable("STOCK"));
+                t.addIntegerColumn("SOURCE_ELEMENTID", 1);
+                root.getTable("MOUVEMENT_STOCK").getBase().getDataSource().execute(t.asString());
+                root.refetchTable(root.getTable("MOUVEMENT_STOCK").getName());
+                root.getSchema().updateVersion();
+                // REQUETE update STOCK.ID_ARTICLE et QTE_MIN
+                SQLTable tableStock = root.getTable("STOCK");
+                UpdateBuilder buildStockArt = new UpdateBuilder(tableStock);
+                buildStockArt.addBackwardVirtualJoin(tableArticle, "ID_STOCK");
+                buildStockArt.setFromVirtualJoinField("ID_ARTICLE", tableArticle.getAlias(), "ID");
+                buildStockArt.setFromVirtualJoinField("QTE_MIN", tableArticle.getAlias(), "QTE_MIN");
+                buildStockArt.setWhere(new Where(tableStock.getKey(), "!=", 1));
+                // REQUETE UPDATE MVT ID_STOCK
+                UpdateBuilder buildMvtStock = new UpdateBuilder(tableMvtStock);
+                buildMvtStock.addForwardVirtualJoin(tableArticle, "ID_ARTICLE");
+                buildMvtStock.setFromVirtualJoinField("ID_STOCK", tableArticle.getAlias(), "ID_STOCK");
+                // REQUETE UPDATE STOCK ID_DEPOT_STOCK
+                UpdateBuilder buildStock = new UpdateBuilder(tableStock);
+                buildStock.setObject("ID_DEPOT_STOCK", DepotStockSQLElement.DEFAULT_ID);
+                UpdateBuilder buildArticleDepot = new UpdateBuilder(tableArticle);
+                buildArticleDepot.setObject("ID_DEPOT_STOCK", DepotStockSQLElement.DEFAULT_ID);
+                // REQUETE UPDATE ARTICLE_DEPOT_STOCK
+                // String req = "INSERT INTO " + root.getTable("ARTICLE_STOCK").getSQLName().quote()
+                // + " (" + root.getTable("ARTICLE_STOCK").getField("ID_ARTICLE").getQuotedName() +
+                // ",";
+                // req += root.getTable("ARTICLE_STOCK").getField("ID_STOCK").getQuotedName() + ", "
+                // + root.getTable("ARTICLE_STOCK").getField("QTE_MIN").getQuotedName() + ") SELECT
+                // "
+                // + root.getTable("ARTICLE").getKey().getQuotedName() + "," +
+                // root.getTable("ARTICLE").getField("ID_STOCK").getQuotedName() + ", "
+                // + root.getTable("ARTICLE").getField("QTE_MIN").getQuotedName();
+                // req += " FROM " + root.getTable("ARTICLE").getSQLName().quote();
+                // req += " WHERE " + root.getTable("ARTICLE").getKey().getQuotedName() + " > 1 AND
+                // " + root.getTable("ARTICLE").getField("ID_STOCK").getQuotedName() + " >1 ";
+                // req += " AND " + root.getTable("ARTICLE").getArchiveField().getQuotedName() + " =
+                // 0";
+                tableStock.getDBSystemRoot().getDataSource().execute(buildArticleDepot.asString());
+                tableStock.getDBSystemRoot().getDataSource().execute(buildMvtStock.asString());
+                tableStock.getDBSystemRoot().getDataSource().execute(buildStock.asString());
+                tableStock.getDBSystemRoot().getDataSource().execute(buildStockArt.asString());
+            }
+        }
+        // Gestion Livraison depuis devis
+        SQLTable tableTrDevis = root.getTable("TR_DEVIS");
+        if (!tableTrDevis.contains("ID_BON_DE_LIVRAISON")) {
+            AlterTable t = new AlterTable(tableTrDevis);
+            t.addForeignColumn("ID_BON_DE_LIVRAISON", root.getTable("BON_DE_LIVRAISON"));
+            tableTrDevis.getBase().getDataSource().execute(t.asString());
+            root.refetchTable(tableTrDevis.getName());
+            root.getSchema().updateVersion();
+        }
+        SQLTable tableBLElt = root.getTable("BON_DE_LIVRAISON_ELEMENT");
+        if (!tableBLElt.contains("ID_DEVIS_ELEMENT")) {
+            AlterTable t = new AlterTable(tableBLElt);
+            t.addForeignColumn("ID_DEVIS_ELEMENT", root.getTable("DEVIS_ELEMENT"));
+            tableBLElt.getBase().getDataSource().execute(t.asString());
+            root.refetchTable(tableBLElt.getName());
+            root.getSchema().updateVersion();
+        }
+        SQLTable tableDevisElt = root.getTable("DEVIS_ELEMENT");
+        if (!tableDevisElt.contains("QTE_LIVREE")) {
+            AlterTable t = new AlterTable(tableDevisElt);
+            t.addBooleanColumn("LIVRE_FORCED", Boolean.FALSE, false);
+            t.addBooleanColumn("LIVRE", Boolean.FALSE, false);
+            t.addDecimalColumn("QTE_LIVREE", 16, 6, BigDecimal.ZERO, true);
+            tableDevisElt.getBase().getDataSource().execute(t.asString());
+            root.refetchTable(tableDevisElt.getName());
+            root.getSchema().updateVersion();
+            // Move show devise pref in global
+            SQLSelect sel = new SQLSelect();
+            sel.addSelect(root.getTable("DEVISE_HISTORIQUE").getKey(), "COUNT");
+            Number n = (Number) root.getDBSystemRoot().getDataSource().executeScalar(sel.asString());
+            if (n.intValue() > 0) {
+                SQLPreferences prefs = new SQLPreferences(root);
+                prefs.putBoolean(AbstractVenteArticleItemTable.ARTICLE_SHOW_DEVISE, true);
+                try {
+                    prefs.sync();
+                } catch (BackingStoreException e) {
+                    // TODO Auto-generated catch block
+                    e.printStackTrace();
+                }
+            }
+        }
+        SQLTable tablePosteAn = root.getTable("POSTE_ANALYTIQUE");
+        if (!tablePosteAn.contains("OBSOLETE")) {
+            AlterTable t = new AlterTable(tablePosteAn);
+            t.addBooleanColumn("OBSOLETE", Boolean.FALSE, false);
+            tablePosteAn.getBase().getDataSource().execute(t.asString());
+            root.refetchTable(tablePosteAn.getName());
+            root.getSchema().updateVersion();
+        }
+        SQLTable tableCheque = root.getTable("CHEQUE_A_ENCAISSER");
+        if (!tableCheque.contains("ID_COMPTE_PCE_TIERS")) {
+            AlterTable t = new AlterTable(tableCheque);
+            t.addVarCharColumn("TIERS", 256);
+            t.addForeignColumn("ID_COMPTE_PCE_TIERS", root.getTable("COMPTE_PCE"));
+            tableCheque.getBase().getDataSource().execute(t.asString());
+            root.refetchTable(tableCheque.getName());
+            SQLTable tableEnc = root.getTable("ENCAISSER_MONTANT");
+            AlterTable tEnc = new AlterTable(tableEnc);
+            tEnc.addVarCharColumn("TIERS", 256);
+            tEnc.addForeignColumn("ID_COMPTE_PCE_TIERS", root.getTable("COMPTE_PCE"));
+            tableEnc.getBase().getDataSource().execute(tEnc.asString());
+            root.refetchTable(tableEnc.getName());
+            root.getSchema().updateVersion();
+            SQLTable tableEch = root.getTable("ECHEANCE_CLIENT");
+            AlterTable tEch = new AlterTable(tableEch);
+            tEch.addVarCharColumn("TIERS", 256);
+            tEch.addForeignColumn("ID_COMPTE_PCE_TIERS", root.getTable("COMPTE_PCE"));
+            tableEch.getBase().getDataSource().execute(tEch.asString());
+            root.refetchTable(tableEch.getName());
+            root.getSchema().updateVersion();
+            SQLTable tableChq = root.getTable("DEPOT_CHEQUE_ELEMENT");
+            AlterTable tChq = new AlterTable(tableChq);
+            tChq.addVarCharColumn("TIERS", 256);
+            tableChq.getBase().getDataSource().execute(tChq.asString());
+            root.refetchTable(tableChq.getName());
+            root.getSchema().updateVersion();
+        }
+        if (!tableEcr.contains("DATE_ECHEANCE")) {
+            AlterTable t = new AlterTable(tableEcr);
+            t.addColumn("DATE_ECHEANCE", "date");
+            tableEcr.getBase().getDataSource().execute(t.asString());
+            root.refetchTable(tableEcr.getName());
+            root.getSchema().updateVersion();
+        }
+        if (!tableVarSal.contains("HEURE_ABS_CUMUL_VAL")) {
+            final AlterTable alterB = new AlterTable(root.getTable("VARIABLE_SALARIE"));
+            for (org.openconcerto.sql.model.SQLField sqlField : tableVarSal.getContentFields()) {
+                String field = sqlField.getName();
+                if (!field.equalsIgnoreCase("ID_USER_COMMON_CREATE") && !field.equalsIgnoreCase("ID_USER_COMMON_MODIFY") && !field.equalsIgnoreCase("MODIFICATION_DATE")
+                        && !field.equalsIgnoreCase("CREATION_DATE") && !field.endsWith("_DEFAULT_VAL")) {
+                    alterB.addColumn(field + "_CUMUL_VAL", "real DEFAULT 0");
+                }
+            }
+            root.getBase().getDataSource().execute(alterB.asString());
+            root.refetchTable("VARIABLE_SALARIE");
+            root.getSchema().updateVersion();
+        }
+        // fix stock
+        {
+            SQLTable tableStock = root.getTable("STOCK");
+            // Doublon depot stock
+            SQLSelect sel = new SQLSelect();
+            sel.addSelectFunctionStar("COUNT");
+            sel.addSelect(tableStock.getField("ID_DEPOT_STOCK"));
+            sel.addSelect(tableStock.getField("ID_ARTICLE"));
+            sel.addGroupBy(tableStock.getField("ID_DEPOT_STOCK"));
+            sel.addGroupBy(tableStock.getField("ID_ARTICLE"));
+            sel.setHaving(Where.createRaw("COUNT(*)>1", Collections.emptyList()));
+            System.err.println(sel.asString());
+            List<Object[]> resultStockDoublon = (List<Object[]>) tableStock.getDBSystemRoot().getDataSource().execute(sel.asString(), new ArrayListHandler());
+            for (Object[] objects : resultStockDoublon) {
+                if (((Number) objects[2]).intValue() == 1) {
+                    UpdateBuilder buildStockArt = new UpdateBuilder(tableStock);
+                    buildStockArt.addBackwardVirtualJoin(tableArticle, "ID_STOCK");
+                    buildStockArt.setFromVirtualJoinField("ID_ARTICLE", tableArticle.getAlias(), "ID");
+                    buildStockArt.setWhere(new Where(tableStock.getKey(), "!=", 1));
+                    tableStock.getDBSystemRoot().getDataSource().execute(buildStockArt.asString());
+                } else {
+                    SQLSelect selD = new SQLSelect();
+                    selD.addSelectStar(tableStock);
+                    Where w = new Where(tableStock.getField("ID_ARTICLE"), "=", objects[2]);
+                    w = w.and(new Where(tableStock.getField("ID_DEPOT_STOCK"), "=", objects[1]));
+                    selD.setWhere(w);
+                    List<SQLRow> badStock = SQLRowListRSH.execute(selD);
+                    if (badStock.size() > 1) {
+                        SQLRowValues stock = badStock.get(0).createUpdateRow();
+                        List<String> fieldsToMerge = Arrays.asList("QTE_REEL", "QTE_TH", "QTE_RECEPT_ATTENTE", "QTE_LIV_ATTENTE");
+                        List<Integer> listBadIds = new ArrayList<>();
+                        for (int i = 1; i < badStock.size(); i++) {
+                            SQLRow rowBad = badStock.get(i);
+                            listBadIds.add(rowBad.getID());
+                            for (String field : fieldsToMerge) {
+                                stock.put(field, stock.getFloat(field) + rowBad.getFloat(field));
+                            }
+                            rowBad.createEmptyUpdateRow().put("ARCHIVE", 1).commit();
+                        }
+                        List<SQLTable> tablesToMErge = Arrays.asList(root.getTable("MOUVEMENT_STOCK"), root.getTable("ARTICLE"));
+                        for (SQLTable mergeTable : tablesToMErge) {
+                            UpdateBuilder up = new UpdateBuilder(mergeTable);
+                            up.setObject("ID_STOCK", stock.getID());
+                            up.setWhere(new Where(mergeTable.getField("ID_STOCK"), listBadIds));
+                            mergeTable.getDBSystemRoot().getDataSource().execute(up.asString());
+                        }
+                        stock.commit();
+                    }
+                }
+            }
+            SQLSelect selArt = new SQLSelect();
+            selArt.addSelectStar(tableArt);
+            Where w = new Where(tableArt.getField("ID_STOCK"), "=", 1);
+            selArt.setWhere(w);
+            List<SQLRow> badStock = SQLRowListRSH.execute(selArt);
+            for (SQLRow sqlRow : badStock) {
+                initStock(sqlRow);
+            }
+        }
+    }
+    public static void initStock(SQLRow row) {
+        SQLSelect sel = new SQLSelect();
+        sel.addSelectStar(row.getTable().getTable("DEPOT_STOCK"));
+        List<SQLRow> rowsDepot = SQLRowListRSH.execute(sel);
+        SQLSelect selStock = new SQLSelect();
+        selStock.addSelectStar(row.getTable().getTable("STOCK"));
+        selStock.setWhere(new Where(row.getTable().getTable("STOCK").getField("ID_ARTICLE"), "=", row.getID()));
+        List<SQLRow> rowsStock = SQLRowListRSH.execute(selStock);
+        Map<Integer, SQLRow> initedDepot = new HashMap<>();
+        for (SQLRow sqlRow : rowsStock) {
+            initedDepot.put(sqlRow.getForeignID("ID_DEPOT_STOCK"), sqlRow);
+        }
+        List<StockItem> stockItems = new ArrayList<StockItem>();
+        for (SQLRow sqlRow : rowsDepot) {
+            try {
+                if (!initedDepot.keySet().contains(sqlRow.getID())) {
+                    SQLRowValues rowVals = new SQLRowValues(row.getTable().getTable("STOCK"));
+                    rowVals.put("ID_ARTICLE", row.getID());
+                    rowVals.put("ID_DEPOT_STOCK", sqlRow.getID());
+                    SQLRow rowStock = rowVals.commit();
+                    if ((row.getObject("ID_DEPOT_STOCK") == null || row.isForeignEmpty("ID_DEPOT_STOCK")) && sqlRow.getID() == DepotStockSQLElement.DEFAULT_ID) {
+                        row.createEmptyUpdateRow().put("ID_STOCK", rowStock.getID()).put("ID_DEPOT_STOCK", DepotStockSQLElement.DEFAULT_ID).commit();
+                    } else if (sqlRow.getID() == row.getForeignID("ID_DEPOT_STOCK")) {
+                        row.createEmptyUpdateRow().put("ID_STOCK", rowStock.getID()).commit();
+                    }
+                    stockItems.add(new StockItem(row, rowStock));
+                } else {
+                    SQLRow rowExisting = initedDepot.get(sqlRow.getID());
+                    if ((row.getObject("ID_DEPOT_STOCK") == null || row.isForeignEmpty("ID_DEPOT_STOCK")) && sqlRow.getID() == DepotStockSQLElement.DEFAULT_ID) {
+                        row.createEmptyUpdateRow().put("ID_STOCK", rowExisting.getID()).put("ID_DEPOT_STOCK", DepotStockSQLElement.DEFAULT_ID).commit();
+                    } else if (sqlRow.getID() == row.getForeignID("ID_DEPOT_STOCK")) {
+                        row.createEmptyUpdateRow().put("ID_STOCK", rowExisting.getID()).commit();
+                    }
+                    stockItems.add(new StockItem(row, rowExisting));
+                }
+            } catch (SQLException e) {
+                ExceptionHandler.handle("Erreur lors de l'initialisation du stock de l'article", e);
+            }
+        }
+        // if
+        // (row.getReferentRows(row.getTable().getTable("ARTICLE_ELEMENT").getField("ID_ARTICLE_PARENT")).size()
+        // > 0) {
+        // ComposedItemStockUpdater up = new ComposedItemStockUpdater(row.getTable().getDBRoot(),
+        // stockItems);
+        // try {
+        // up.updateNomenclature(stockItems);
+        // } catch (SQLException e) {
+        // ExceptionHandler.handle("Erreur lors de l'actualisation du stock!", e);
+        // }
+        // }
+    }
     public static void exec(final AlterTable alter) throws SQLException {
         alter.getTable().getDBSystemRoot().getDataSource().execute(alter.asString());
         alter.getTable().getSchema().updateVersion();
         alter.getTable().fetchFields();
+    }
+}

Dépôt officiel du code source de l'ERP OpenConcerto

svn://code.openconcerto.org/openconcerto

(root)/trunk/OpenConcerto/src/org/openconcerto/erp/config/update/Updater_1_5.java - Rev 151 → 156