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

import org.openconcerto.erp.core.common.ui.AbstractVenteArticleItemTable;

import org.openconcerto.erp.core.supplychain.stock.element.DepotStockSQLElement;

import org.openconcerto.erp.core.supplychain.stock.element.StockItem;

import org.openconcerto.sql.model.SQLField;

import org.openconcerto.sql.model.SQLRow;

import org.openconcerto.sql.model.SQLRowListRSH;

import org.openconcerto.sql.model.SQLSelect;

import org.openconcerto.sql.preferences.SQLPreferences;

import org.openconcerto.utils.ExceptionHandler;

import java.sql.Clob;

import java.util.ArrayList;

import java.util.Collections;

import java.util.HashMap;

import java.util.Map;

import java.util.prefs.BackingStoreException;

import org.apache.commons.dbutils.handlers.ArrayListHandler;

                // FIXME mis par défaut à true avant --> à fixer

                    List<String> tablesWorkFlow = Arrays.asList("SAISIE_VENTE_FACTURE", "BON_DE_LIVRAISON");

                    for (String tableWorkFlow : tablesWorkFlow) {

                        SQLTable tableCmdCli = root.getTable("COMMANDE_CLIENT");

                        SQLTable tableTR = root.getTable("TR_COMMANDE_CLIENT");

                        SQLTable tableBL = root.getTable(tableWorkFlow);

                        // 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() + " c ," + new SQLName(root.getName(), tableBL.getName()).quote()

                                + " 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.\""

                                + (tableWorkFlow.startsWith("BON") ? "TOTAL_HT" : "T_HT") + "\")>=SUM(c.\"T_HT\") ) ";

                        List<Integer> cmd = tableTR.getDBSystemRoot().getDataSource().executeCol(sel);

                        UpdateBuilder build = new UpdateBuilder(tableCmdElt);

                        build.set("QTE_LIVREE", "\"QTE\"*\"QTE_UNITAIRE\"");

                        build.setObject("LIVRE_FORCED", Boolean.TRUE);

                        final Where where = new Where(tableCmdElt.getField("ID_COMMANDE_CLIENT"), cmd);

                        build.setWhere(where);

                        tableTR.getDBSystemRoot().getDataSource().execute(build.asString());

                    }

                }

            }

                UpdateBuilder upLivre = new UpdateBuilder(tableCmdElt);

                upLivre.setObject("LIVRE", Boolean.FALSE);

                upLivre.setWhere(new Where(tableCmdElt.getKey(), "=", 1));

                tableCmdElt.getBase().getDataSource().execute(upLivre.asString());

                {

                    SQLTable tableCmdCli = root.getTable("COMMANDE");

                    SQLTable tableTR = root.getTable("TR_COMMANDE");

                    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 ," + 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 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);

                    UpdateBuilder build = new UpdateBuilder(tableCmdFElt);

                    build.set("QTE_RECUE", "\"QTE\"*\"QTE_UNITAIRE\"");

                    build.setObject("RECU_FORCED", Boolean.TRUE);

                    final Where where = new Where(tableCmdFElt.getField("ID_COMMANDE"), cmd);

                    build.setWhere(where);

                    System.err.println(build.asString());

                    tableTR.getDBSystemRoot().getDataSource().execute(build.asString());

                }

            }

            // Champ matière

            SQLTable tableArt = root.getTable("ARTICLE");

            if (!tableArt.contains("MATIERE")) {

                final AlterTable alter = new AlterTable(tableArt);

                alter.addVarCharColumn("MATIERE", 128);

                tableArt.getBase().getDataSource().execute(alter.asString());

                tableArt.getSchema().updateVersion();

                tableArt.fetchFields();

                if (root.contains("DEPOT_STOCK")) {

                    createTableDemandeMElt.addForeignColumn("DEPOT_STOCK");

                }

        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("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();

        }

        // 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();

        }

        } 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();

            }

        // 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);

        }