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 182
Rev 180 | Go to most recent revision | Blame | Compare with Previous | Last modification | View Log | RSS feed
/** DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.** Copyright 2011-2019 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.element.BanqueSQLElement;import org.openconcerto.erp.core.common.ui.AbstractVenteArticleItemTable;import org.openconcerto.erp.core.customerrelationship.mail.EmailTemplateSQLElement;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.ComposedItemStockUpdater;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.changer.convert.SetFKDefault;import org.openconcerto.sql.model.AliasedTable;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.SQLUpdate;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.Set;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 compteSQLTable 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();}// Champ obsolete sur compteSQLTable tableTitre = root.getTable("TITRE_PERSONNEL");if (!tableTitre.contains("OBSOLETE")) {final AlterTable alter = new AlterTable(tableTitre);alter.addBooleanColumn("OBSOLETE", Boolean.FALSE, false);tableTitre.getBase().getDataSource().execute(alter.asString());tableTitre.getSchema().updateVersion();tableTitre.fetchFields();final UpdateBuilder updBuilder = new UpdateBuilder(tableTitre).setObject(tableTitre.getField("OBSOLETE"), Boolean.TRUE);updBuilder.setWhere(new Where(tableTitre.getField("CODE"), "=", "Mlle"));tableTitre.getBase().getDataSource().execute(updBuilder.asString());final UpdateBuilder updBuilder2 = new UpdateBuilder(tableTitre).setObject(tableTitre.getField("CODE"), "M.");updBuilder2.setWhere(new Where(tableTitre.getField("CODE"), "=", "Mr"));tableTitre.getBase().getDataSource().execute(updBuilder2.asString());}// Transaction du soldeif (!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);}}// Soldefinal 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();}if (!tClient.contains("ALERTE")) {final AlterTable alterClient = new AlterTable(tClient);alterClient.addVarCharColumn("ALERTE", 4096);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// 511List<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 Intrafinal 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();}// GEDif (!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);createTable.addBooleanColumn("ENCRYPTED", Boolean.FALSE, false);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();}if (!tableAttachment.contains("ENCRYPTED")) {final AlterTable alter = new AlterTable(tableAttachment);alter.addBooleanColumn("ENCRYPTED", Boolean.FALSE, false);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{// VenteSQLTable 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 --> à fixert.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 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());}}}// Fix bad default valueif (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());}// AchatSQLTable 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.FALSE, 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 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());}} else {// Fix bad default valueString defaultValue = tableCmdFElt.getField("RECU").getDefaultValue();if (defaultValue != null && defaultValue.equals("true")) {AlterTable t = new AlterTable(tableCmdFElt);t.alterColumn("RECU", EnumSet.allOf(Properties.class), "boolean", "false", false);tableCmdFElt.getBase().getDataSource().execute(t.asString());root.refetchTable(tableCmdFElt.getName());root.getSchema().updateVersion();UpdateBuilder build = new UpdateBuilder(tableCmdFElt);build.setObject("RECU", Boolean.FALSE);Where w = Where.createRaw(tableCmdFElt.getField("QTE_RECUE").getQuotedName() + " < (" + tableCmdFElt.getField("QTE").getQuotedName() + "*"+ tableCmdFElt.getField("QTE_UNITAIRE").getQuotedName() + ")", tableCmdFElt.getField("QTE_UNITAIRE"), tableCmdFElt.getField("QTE"), tableCmdFElt.getField("QTE_RECUE"));build.setWhere(w.or(new Where(tableCmdFElt.getKey(), "=", 1)));System.err.println(build.asString());tableCmdFElt.getDBSystemRoot().getDataSource().execute(build.asString());}}// Champ matièreSQLTable 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();}}// ----------------------- 1.5.1if (!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_SALARIESQLTable 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") && !tableVarSal.contains(field + "_DEFAULT_VAL")) {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();}if (!tableFpaye.contains("CSG_REDUITE")) {final AlterTable alterB = new AlterTable(tableFpaye);alterB.addDecimalColumn("CSG_REDUITE", 16, 2, BigDecimal.ZERO, false);alterB.addDecimalColumn("SAL_BRUT_CSG", 16, 2, BigDecimal.ZERO, false);alterB.addDecimalColumn("SAL_BRUT_CSG_REDUITE", 16, 2, BigDecimal.ZERO, false);root.getBase().getDataSource().execute(alterB.asString());root.refetchTable("FICHE_PAYE");root.getSchema().updateVersion();}if (!tableFpaye.contains("PERTE_TPT")) {final AlterTable alterB = new AlterTable(tableFpaye);alterB.addDecimalColumn("PERTE_TPT", 16, 2, BigDecimal.ZERO, false);root.getBase().getDataSource().execute(alterB.asString());root.refetchTable("FICHE_PAYE");root.getSchema().updateVersion();}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 CLIENTif (!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();}if (!tableArticle.contains("DLC")) {final AlterTable alterArticle = new AlterTable(tableArticle);alterArticle.addColumn("DLC", "date");tableArticle.getBase().getDataSource().execute(alterArticle.asString());tableArticle.getSchema().updateVersion();tableArticle.fetchFields();}if (!tableArticle.contains("OPTION")) {final AlterTable alterArticle = new AlterTable(tableArticle);alterArticle.addBooleanColumn("OPTION", Boolean.FALSE, false);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();}if (!tableFournisseur.contains("CONDITIONS_PORT")) {final AlterTable alter = new AlterTable(tableFournisseur);alter.addVarCharColumn("CONDITIONS_PORT", 128);tableFournisseur.getBase().getDataSource().execute(alter.asString());tableFournisseur.getSchema().updateVersion();tableFournisseur.fetchFields();}// POSfinal 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 salescreateClotureT.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) {@Overridepublic 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) {@Overridepublic 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();}SQLTable tableCommandeAcompte = root.getTable("COMMANDE_CLIENT");if (!tableCommandeAcompte.contains("T_ACOMPTE")) {final AlterTable alterB = new AlterTable(tableCommandeAcompte);alterB.addLongColumn("T_ACOMPTE", 0L, false);root.getBase().getDataSource().execute(alterB.asString());root.refetchTable("COMMANDE_CLIENT");root.getSchema().updateVersion();SQLTable tableEncaisse = root.getTable("ENCAISSER_MONTANT");final AlterTable alterC = new AlterTable(tableEncaisse);alterC.addForeignColumn("ID_COMMANDE_CLIENT", tableDevisAcompte);root.getBase().getDataSource().execute(alterC.asString());root.refetchTable("ENCAISSER_MONTANT");root.getSchema().updateVersion();}if (!tableDevisAcompte.contains("DATE_LIVRAISON")) {final AlterTable alterB = new AlterTable(tableDevisAcompte);alterB.addColumn("DATE_LIVRAISON", "date");alterB.addVarCharColumn("TRANSFORMATION", 200);root.getBase().getDataSource().execute(alterB.asString());root.refetchTable("DEVIS");root.getSchema().updateVersion();}// Paye Simplifiéeif (!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();}boolean upVar = false;List<String> f = Arrays.asList("IJSS_BRUT", "IJSS_NET", "FRAIS_PRO", "RBT_TRANSPORT", "HEURE_CHOM", "TAUX_CHOM", "HEURE_INDEM", "ECRETEMENT_CSG", "IJSS_BRUT_SECU_PAS");final AlterTable alterVarSal = new AlterTable(tableVarSal);for (String field : f) {if (!tableVarSal.contains(field)) {upVar = true;alterVarSal.addColumn(field, "real DEFAULT 0");alterVarSal.addColumn(field + "_DEFAULT_VAL", "real DEFAULT 0");alterVarSal.addColumn(field + "_CUMUL_VAL", "real DEFAULT 0");}}if (upVar) {root.getBase().getDataSource().execute(alterVarSal.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.3List<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.3if (!root.getTable(registerLogTableName).contains("HOST_NAME")) {final AlterTable alterRegisterLog = new AlterTable(root.getTable(registerLogTableName));// to check for shared register installationsalterRegisterLog.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_JOURNALfinal 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 ANSQLTable 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("ID_JOURNAL_CB_ATTENTE")) {AlterTable t = new AlterTable(table);t.addForeignColumn("ID_JOURNAL_CB_ATTENTE", root.getTable("JOURNAL"));table.getBase().getDataSource().execute(t.asString());table.getSchema().updateVersion();table.fetchFields();}if (!table.getFieldsName().contains("ID_COMPTE_PCE_CB_ATTENTE")) {AlterTable t = new AlterTable(table);t.addForeignColumn("ID_COMPTE_PCE_CB_ATTENTE", root.getTable("COMPTE_PCE"));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 stockif (!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 Elementfinal 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 clientsfor (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 != 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// varcharfinal 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 chequesString chequeDepotTable = "DEPOT_CHEQUE";if (!root.contains(chequeDepotTable)) {// Table depot chequefinal 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 elementfinal 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 revientif (!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 Documentif (!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 sourcefinal 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 comptablesif (!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 secondaireif (!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 multidepotfinal SQLTable userT = root.findTable("USER_COMMON");{// Ajout table depotif (!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", userT);// 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 defautif (!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 stockif (!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 mvtif (!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_MINSQLTable 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_STOCKUpdateBuilder buildMvtStock = new UpdateBuilder(tableMvtStock);buildMvtStock.addForwardVirtualJoin(tableArticle, "ID_ARTICLE");buildMvtStock.setFromVirtualJoinField("ID_STOCK", tableArticle.getAlias(), "ID_STOCK");// REQUETE UPDATE STOCK ID_DEPOT_STOCKUpdateBuilder 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 devisSQLTable 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 globalSQLSelect 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 blocke.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") && !tableVarSal.contains(field + "_CUMUL_VAL")) {alterB.addColumn(field + "_CUMUL_VAL", "real DEFAULT 0");}}root.getBase().getDataSource().execute(alterB.asString());root.refetchTable("VARIABLE_SALARIE");root.getSchema().updateVersion();}SQLTable tableBL = root.getTable("BON_DE_LIVRAISON");if (!tableBL.contains("ID_COMMERCIAL")) {final AlterTable alterB = new AlterTable(tableBL);alterB.addForeignColumn("ID_COMMERCIAL", root.getTable("COMMERCIAL"));root.getBase().getDataSource().execute(alterB.asString());root.refetchTable("BON_DE_LIVRAISON");root.getSchema().updateVersion();}if (!tableBL.contains("VERROU_FACTURATION")) {final AlterTable alterB = new AlterTable(tableBL);alterB.addBooleanColumn("VERROU_FACTURATION", Boolean.FALSE, false);root.getBase().getDataSource().execute(alterB.asString());root.refetchTable("BON_DE_LIVRAISON");root.getSchema().updateVersion();}// fix stock{SQLTable tableStock = root.getTable("STOCK");// Doublon depot stockSQLSelect 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();}}}// Remove useless since 1.7.1// 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);// }}// Tarification client par quantiteif (root.getTable("TARIF_ARTICLE_CLIENT") == null) {final SQLCreateTable createTableQtyTarif = new SQLCreateTable(root, "TARIF_ARTICLE_CLIENT");createTableQtyTarif.addForeignColumn("ID_ARTICLE", root.getTable("ARTICLE"));createTableQtyTarif.addForeignColumn("ID_CLIENT", root.getTable("CLIENT"));createTableQtyTarif.addDecimalColumn("QUANTITE", 16, 3, BigDecimal.ONE, false);createTableQtyTarif.addDecimalColumn("POURCENT_REMISE", 16, 3, null, true);// createTableQtyTarif.addDecimalColumn("PRIX_METRIQUE_VT_1", 16, 6, null, true);try {root.getBase().getDataSource().execute(createTableQtyTarif.asString());InstallationPanel.insertUndef(createTableQtyTarif);root.refetchTable("TARIF_ARTICLE_CLIENT");root.getSchema().updateVersion();} catch (SQLException ex) {throw new IllegalStateException("Erreur lors de la création de la table " + "TARIF_QUANTITE", ex);}}SQLTable tableTrCmd = root.getTable("TR_COMMANDE");if (!tableTrCmd.contains("ID_FACTURE_FOURNISSEUR")) {AlterTable t = new AlterTable(tableTrCmd);t.addForeignColumn("ID_FACTURE_FOURNISSEUR", root.getTable("FACTURE_FOURNISSEUR"));tableTrCmd.getBase().getDataSource().execute(t.asString());root.refetchTable(tableTrCmd.getName());root.getSchema().updateVersion();}SQLTable tableContrat = root.getTable("CONTRAT_SALARIE");if (!tableContrat.contains("COMPLEMENT_PCS")) {AlterTable t = new AlterTable(tableContrat);t.addVarCharColumn("COMPLEMENT_PCS", 54);tableContrat.getBase().getDataSource().execute(t.asString());root.refetchTable(tableContrat.getName());root.getSchema().updateVersion();}if (!tableContrat.contains("SPECTACLE_OBJET")) {AlterTable t = new AlterTable(tableContrat);t.addVarCharColumn("SPECTACLE_OBJET", 54);tableContrat.getBase().getDataSource().execute(t.asString());root.refetchTable(tableContrat.getName());root.getSchema().updateVersion();}if (!tableContrat.contains("SPECTACLE_OBJET")) {AlterTable t = new AlterTable(tableContrat);t.addVarCharColumn("SPECTACLE_OBJET", 54);tableContrat.getBase().getDataSource().execute(t.asString());root.refetchTable(tableContrat.getName());root.getSchema().updateVersion();}if (!tableContrat.contains("SPECTACLE_JOUR_CONTRAT")) {AlterTable t = new AlterTable(tableContrat);t.addDecimalColumn("SPECTACLE_JOUR_CONTRAT", 16, 2, null, true);tableContrat.getBase().getDataSource().execute(t.asString());root.refetchTable(tableContrat.getName());root.getSchema().updateVersion();}List<String> tablesCatComptable = Arrays.asList("DEVIS", "COMMANDE_CLIENT", "BON_DE_LIVRAISON", "SAISIE_VENTE_FACTURE", "AVOIR_CLIENT");for (String tableToUp : tablesCatComptable) {final SQLTable tableCatComptToAdd = root.getTable(tableToUp);if (!tableCatComptToAdd.contains("ID_CATEGORIE_COMPTABLE")) {final AlterTable alter = new AlterTable(tableCatComptToAdd);alter.addForeignColumn("ID_CATEGORIE_COMPTABLE", root.getTable("CATEGORIE_COMPTABLE"));exec(alter);root.refetchTable(tableToUp);root.getSchema().updateVersion();}}// Modèles pour les emailsif (!root.contains(EmailTemplateSQLElement.TABLE_NAME)) {final SQLCreateTable createTable = new SQLCreateTable(root, EmailTemplateSQLElement.TABLE_NAME);createTable.addVarCharColumn("NOM", 80);createTable.addVarCharColumn("TITRE", 80);createTable.addVarCharColumn("TEXTE", 4096);createTable.addVarCharColumn("FORMAT_DATE", 20);createTable.addBooleanColumn("PAR_DEFAUT", Boolean.FALSE, false);try {root.getBase().getDataSource().execute(createTable.asString());InstallationPanel.insertUndef(createTable);root.refetchTable(EmailTemplateSQLElement.TABLE_NAME);root.getSchema().updateVersion();} catch (SQLException ex) {throw new IllegalStateException("Erreur lors de la création de la table " + EmailTemplateSQLElement.TABLE_NAME, ex);}}// Force undefined policy to inDbroot.setMetadata(SQLTable.UNDEFINED_ID_POLICY, "inDB");final Map<String, Number> mapTableNameUndefined = SQLTable.getUndefinedIDs(root.getSchema());final Set<String> tables = root.getSchema().getTableNames();for (String tName : tables) {if (!mapTableNameUndefined.containsKey(tName)) {System.err.println("Updater_1_5.update() adding undefined in db for " + tName);SQLTable.setUndefID(root.getSchema(), tName, null);}}// Création de la table Modéleif (!root.contains("CONTACT_SALARIE")) {SQLCreateTable createModele = new SQLCreateTable(root, "CONTACT_SALARIE");createModele.addVarCharColumn("NOM", 256);createModele.addVarCharColumn("PRENOM", 256);createModele.addVarCharColumn("TEL_DIRECT", 256);createModele.addVarCharColumn("TEL_MOBILE", 256);createModele.addVarCharColumn("EMAIL", 256);createModele.addVarCharColumn("FAX", 256);createModele.addVarCharColumn("FONCTION", 256);createModele.addVarCharColumn("TEL_PERSONEL", 256);createModele.addVarCharColumn("TEL_STANDARD", 256);createModele.addForeignColumn("ID_TITRE_PERSONNEL", root.findTable("TITRE_PERSONNEL"));createModele.addForeignColumn("ID_SALARIE", root.findTable("SALARIE"));try {root.getBase().getDataSource().execute(createModele.asString());root.refetchTable("CONTACT_SALARIE");SQLRowValues rowVals = new SQLRowValues(root.getTable("CONTACT_SALARIE"));SQLRow rowInserted = rowVals.commit();SQLTable.setUndefID(root.getSchema(), "CONTACT_SALARIE", rowInserted.getID());tableDevis.getSchema().updateVersion();} catch (SQLException ex) {throw new IllegalStateException("Erreur lors de la création de la table MODELE", ex);}}final SQLTable tableRgltPaye = root.getTable("REGLEMENT_PAYE");if (!tableRgltPaye.contains("IBAN")) {final AlterTable alter = new AlterTable(tableRgltPaye);alter.addVarCharColumn("IBAN", 128);exec(alter);root.refetchTable(tableRgltPaye.getName());root.getSchema().updateVersion();}if (!tableRgltPaye.contains("BIC")) {final AlterTable alter = new AlterTable(tableRgltPaye);alter.addVarCharColumn("BIC", 40);exec(alter);root.refetchTable(tableRgltPaye.getName());root.getSchema().updateVersion();}final SQLTable tableSalarie = root.getTable("SALARIE");if (!tableSalarie.contains("ID_USER_COMMON")) {final AlterTable alter = new AlterTable(tableSalarie);alter.addForeignColumn("ID_USER_COMMON", userT);exec(alter);root.refetchTable(tableSalarie.getName());root.getSchema().updateVersion();}final SQLTable tableInfosSalarie = root.getTable("INFOS_SALARIE_PAYE");if (!tableInfosSalarie.contains("DUREE_FORFAIT")) {final AlterTable alter = new AlterTable(tableInfosSalarie);alter.addColumn("DUREE_FORFAIT", "real");exec(alter);root.refetchTable(tableSalarie.getName());root.getSchema().updateVersion();}boolean upDimensionArt = false;final AlterTable alterDimensionArt = new AlterTable(tableArt);if (!tableArt.contains("LONGUEUR")) {alterDimensionArt.addDecimalColumn("LONGUEUR", 16, 8, null, true);upDimensionArt = true;}if (!tableArt.contains("LARGEUR")) {alterDimensionArt.addDecimalColumn("LARGEUR", 16, 8, null, true);upDimensionArt = true;}if (!tableArt.contains("HAUTEUR")) {alterDimensionArt.addDecimalColumn("HAUTEUR", 16, 8, null, true);upDimensionArt = true;}if (upDimensionArt) {tableArt.getBase().getDataSource().execute(alterDimensionArt.asString());tableArt.getSchema().updateVersion();tableArt.fetchFields();}List<String> tableElementWithTable = 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 : tableElementWithTable) {final SQLTable tableToAddDimension = root.getTable(tableName);boolean upDimensionArtItem = false;final AlterTable alterDimensionArtItem = new AlterTable(tableToAddDimension);if (!tableToAddDimension.contains("LONGUEUR")) {alterDimensionArtItem.addDecimalColumn("LONGUEUR", 16, 8, null, true);upDimensionArtItem = true;}if (!tableToAddDimension.contains("LARGEUR")) {alterDimensionArtItem.addDecimalColumn("LARGEUR", 16, 8, null, true);upDimensionArtItem = true;}if (!tableToAddDimension.contains("HAUTEUR")) {alterDimensionArtItem.addDecimalColumn("HAUTEUR", 16, 8, null, true);upDimensionArtItem = true;}if (upDimensionArtItem) {tableToAddDimension.getBase().getDataSource().execute(alterDimensionArtItem.asString());tableToAddDimension.getSchema().updateVersion();tableToAddDimension.fetchFields();}}if (!tTva.contains("DEFAULT_ACHAT")) {final AlterTable alterTaxe = new AlterTable(tTva);alterTaxe.addBooleanColumn("DEFAULT_ACHAT", Boolean.FALSE, false);tTva.getBase().getDataSource().execute(alterTaxe.asString());tTva.getSchema().updateVersion();tTva.fetchFields();}SQLTable tableTypeRglt = root.getTable("TYPE_REGLEMENT");if (!tableTypeRglt.contains("SEPA")) {final AlterTable alterTaxe = new AlterTable(tableTypeRglt);alterTaxe.addBooleanColumn("SEPA", Boolean.FALSE, false);tableTypeRglt.getBase().getDataSource().execute(alterTaxe.asString());tableTypeRglt.getSchema().updateVersion();tableTypeRglt.fetchFields();UpdateBuilder upSEPA = new UpdateBuilder(tableTypeRglt);upSEPA.setObject("SEPA", Boolean.TRUE);upSEPA.setObject("ECHEANCE", Boolean.TRUE);upSEPA.setWhere(new Where(tableTypeRglt.getField("NOM"), "=", "Prélèvement"));tTva.getBase().getDataSource().execute(upSEPA.asString());}SQLTable tableEch = root.getTable("ECHEANCE_CLIENT");if (!tableEch.contains("ID_SEPA_MANDATE")) {final AlterTable alterEch = new AlterTable(tableEch);alterEch.addForeignColumn("ID_SEPA_MANDATE", root.getTable("SEPA_MANDATE"));alterEch.addForeignColumn("ID_SDD_MESSAGE", root.getTable(SDDMessageSQLElement.TABLE_NAME));alterEch.addVarCharColumn("SDD_EndToEndId", 35);alterEch.addBooleanColumn("FICHIER_CREE", Boolean.FALSE, false);alterEch.addBooleanColumn("REJETER", Boolean.FALSE, false);alterEch.addVarCharColumn("ETS", 256);alterEch.addForeignColumn("ID_" + BanqueSQLElement.TABLENAME, root.getTable(BanqueSQLElement.TABLENAME));tableEch.getBase().getDataSource().execute(alterEch.asString());tableEch.getSchema().updateVersion();tableEch.fetchFields();}if (!tClient.contains("ID_SEPA_MANDATE_DEFAULT")) {final AlterTable alterClient = new AlterTable(tClient);alterClient.addForeignColumn("ID_SEPA_MANDATE_DEFAULT", root.getTable("SEPA_MANDATE"));tClient.getBase().getDataSource().execute(alterClient.asString());tClient.getSchema().updateVersion();tClient.fetchFields();}if (!tableArt.contains("AUTO_PRIX_MIN_VENTE_NOMENCLATURE")) {final AlterTable alter = new AlterTable(tableArt);alter.addBooleanColumn("AUTO_PRIX_MIN_VENTE_NOMENCLATURE", Boolean.FALSE, false);tableArt.getBase().getDataSource().execute(alter.asString());tableArt.getSchema().updateVersion();tableArt.fetchFields();}if (!tableArt.contains("DERNIER_DATE_ACHAT")) {final AlterTable alter = new AlterTable(tableArt);alter.addColumn("DERNIER_DATE_ACHAT", "date");tableArt.getBase().getDataSource().execute(alter.asString());tableArt.getSchema().updateVersion();tableArt.fetchFields();ReferenceArticleSQLElement.updateDateAchat(tableArt, null);}SQLTable tableFactF = root.getTable("FACTURE_FOURNISSEUR");if (!tableFactF.contains("DATE_REGLEMENT")) {final AlterTable alter = new AlterTable(tableFactF);alter.addColumn("DATE_REGLEMENT", "date");tableFactF.getBase().getDataSource().execute(alter.asString());tableFactF.getSchema().updateVersion();tableFactF.fetchFields();}SQLTable tableEchF = root.getTable("ECHEANCE_FOURNISSEUR");if (!tableEchF.contains("ID_FACTURE_FOURNISSEUR")) {final AlterTable alter = new AlterTable(tableEchF);alter.addForeignColumn("ID_FACTURE_FOURNISSEUR", tableFactF);tableEchF.getBase().getDataSource().execute(alter.asString());tableEchF.getSchema().updateVersion();tableEchF.fetchFields();}List<String> achatItems = Arrays.asList("DEMANDE_PRIX_ELEMENT", "COMMANDE_ELEMENT", "BON_RECEPTION_ELEMENT", "FACTURE_FOURNISSEUR_ELEMENT");for (String string : achatItems) {boolean alter = false;SQLTable tableItems = root.getTable(string);final AlterTable t = new AlterTable(tableItems);if (!tableItems.getFieldsName().contains("POIDS_COLIS_NET")) {t.addColumn("POIDS_COLIS_NET", "numeric (16,8) DEFAULT 1");alter = true;}if (!tableItems.getFieldsName().contains("T_POIDS_COLIS_NET")) {t.addColumn("T_POIDS_COLIS_NET", "numeric (16,8) DEFAULT 1");alter = true;}if (!tableItems.getFieldsName().contains("NB_COLIS")) {t.addColumn("NB_COLIS", "integer DEFAULT 0");alter = true;}if (alter) {tableItems.getBase().getDataSource().execute(t.asString());tableItems.getSchema().updateVersion();tableItems.fetchFields();}}for (String tableName : tableElementWithTable) {final SQLTable tableToAddTare = root.getTable(tableName);boolean upTareArtItem = false;final AlterTable alterTareArtItem = new AlterTable(tableToAddTare);if (!tableToAddTare.contains("TARE")) {alterTareArtItem.addDecimalColumn("TARE", 16, 8, null, true);alterTareArtItem.alterColumn("POIDS_COLIS_NET", EnumSet.allOf(Properties.class), "numeric(16,8)", "0", true);alterTareArtItem.alterColumn("T_POIDS_COLIS_NET", EnumSet.allOf(Properties.class), "numeric(16,8)", "0", true);alterTareArtItem.addDecimalColumn("T_POIDS_BRUT", 16, 8, BigDecimal.ZERO, true);upTareArtItem = true;}if (upTareArtItem) {tableToAddTare.getBase().getDataSource().execute(alterTareArtItem.asString());tableToAddTare.getSchema().updateVersion();tableToAddTare.fetchFields();int id = tableToAddTare.getUndefinedID();if (id != SQLRow.NONEXISTANT_ID) {UpdateBuilder build = new UpdateBuilder(tableToAddTare);build.setObject("POIDS_COLIS_NET", BigDecimal.ZERO);build.setWhere(new Where(tableToAddTare.getKey(), "=", id));tableToAddTare.getDBSystemRoot().getDataSource().execute(build.asString());}}}// Fix nb char MOUVEMENT_STOCK.NOMif (tableMvtStock.getField("NOM").getType().getSize() == 45) {AlterTable alterMvt = new AlterTable(tableMvtStock);alterMvt.alterColumn("NOM", EnumSet.allOf(Properties.class), "varchar(512)", "''", false);tableMvtStock.getDBSystemRoot().getDataSource().execute(alterMvt.asString());tableMvtStock.getSchema().updateVersion();}SQLTable tableEtatStock = root.getTable("ETAT_STOCK");SQLTable tableDepotStock = root.getTable("DEPOT_STOCK");if (!tableEtatStock.contains("ID_DEPOT_STOCK")) {final AlterTable alter = new AlterTable(tableEtatStock);alter.addForeignColumn("ID_DEPOT_STOCK", tableDepotStock);tableEtatStock.getBase().getDataSource().execute(alter.asString());tableEtatStock.getSchema().updateVersion();tableEtatStock.fetchFields();}// Création de la table Agenceif (!root.contains("AGENCE")) {SQLCreateTable createAgence = new SQLCreateTable(root, "AGENCE");createAgence.addVarCharColumn("DESIGNATION", 256);createAgence.addVarCharColumn("TEL_1", 256);createAgence.addVarCharColumn("EMAIL", 256);createAgence.addVarCharColumn("FAX", 256);createAgence.addForeignColumn("ID_CLIENT", root.findTable("CLIENT"));createAgence.addForeignColumn("ID_ADRESSE", root.findTable("ADRESSE"));try {// testroot.getBase().getDataSource().execute(createAgence.asString());root.refetchTable("AGENCE");SQLRowValues rowVals = new SQLRowValues(root.getTable("AGENCE"));SQLRow rowInserted = rowVals.commit();SQLTable.setUndefID(root.getSchema(), "AGENCE", rowInserted.getID());tableDevis.getSchema().updateVersion();} catch (SQLException ex) {throw new IllegalStateException("Erreur lors de la création de la table AGENCE", ex);}}// Tarification client par famille articleif (root.getTable("TARIF_FAMILLE_ARTICLE_CLIENT") == null) {final SQLCreateTable createTableQtyTarif = new SQLCreateTable(root, "TARIF_FAMILLE_ARTICLE_CLIENT");createTableQtyTarif.addForeignColumn("ID_FAMILLE_ARTICLE", root.getTable("FAMILLE_ARTICLE"));createTableQtyTarif.addForeignColumn("ID_CLIENT", root.getTable("CLIENT"));createTableQtyTarif.addDecimalColumn("QUANTITE", 16, 3, BigDecimal.ONE, false);createTableQtyTarif.addDecimalColumn("POURCENT_REMISE", 16, 3, null, true);// createTableQtyTarif.addDecimalColumn("PRIX_METRIQUE_VT_1", 16, 6, null, true);try {root.getBase().getDataSource().execute(createTableQtyTarif.asString());InstallationPanel.insertUndef(createTableQtyTarif);root.refetchTable("TARIF_FAMILLE_ARTICLE_CLIENT");root.getSchema().updateVersion();} catch (SQLException ex) {throw new IllegalStateException("Erreur lors de la création de la table " + "TARIF_FAMILLE_ARTICLE_CLIENT", ex);}}// Tarification client par famille articleif (root.getTable("TARIF_FAMILLE_ARTICLE_CLIENT") == null) {final SQLCreateTable createTableQtyTarif = new SQLCreateTable(root, "TARIF_FAMILLE_ARTICLE_CLIENT");createTableQtyTarif.addForeignColumn("ID_FAMILLE_ARTICLE", root.getTable("FAMILLE_ARTICLE"));createTableQtyTarif.addForeignColumn("ID_CLIENT", root.getTable("CLIENT"));createTableQtyTarif.addDecimalColumn("QUANTITE", 16, 3, BigDecimal.ONE, false);createTableQtyTarif.addDecimalColumn("POURCENT_REMISE", 16, 3, null, true);// createTableQtyTarif.addDecimalColumn("PRIX_METRIQUE_VT_1", 16, 6, null, true);try {root.getBase().getDataSource().execute(createTableQtyTarif.asString());InstallationPanel.insertUndef(createTableQtyTarif);root.refetchTable("TARIF_FAMILLE_ARTICLE_CLIENT");root.getSchema().updateVersion();} catch (SQLException ex) {throw new IllegalStateException("Erreur lors de la création de la table " + "TARIF_FAMILLE_ARTICLE_CLIENT", ex);}}SQLTable tableAvoirC = root.getTable("AVOIR_CLIENT");if (!tableAvoirC.contains("ID_TAXE_PORT")) {final AlterTable alterB = new AlterTable(tableAvoirC);alterB.addForeignColumn("ID_TAXE_PORT", root.getTable("TAXE"));root.getBase().getDataSource().execute(alterB.asString());root.refetchTable(tableAvoirC.getName());root.getSchema().updateVersion();}SQLTable tableVF = root.getTable("SAISIE_VENTE_FACTURE");if (!tableVF.contains("ID_FACTURATION_COMMANDE_CLIENT")) {final AlterTable alter = new AlterTable(tableVF);final SQLTable tableFacturationCommandeClient = tableVF.getDBRoot().getTable("FACTURATION_COMMANDE_CLIENT");alter.addForeignColumn("ID_FACTURATION_COMMANDE_CLIENT", tableFacturationCommandeClient);tableVF.getBase().getDataSource().execute(alter.asString());tableVF.getSchema().updateVersion();tableVF.fetchFields();// update ID_FACTURATION_COMMANDEUpdateBuilder builder = new UpdateBuilder(tableVF);AliasedTable ref = new AliasedTable(tableFacturationCommandeClient, "tf");builder.addBackwardVirtualJoin(ref, "ID_SAISIE_VENTE_FACTURE");builder.setFromVirtualJoinField("ID_FACTURATION_COMMANDE_CLIENT", "tf", tableFacturationCommandeClient.getKey().getName());tableVF.getBase().getDataSource().execute(builder.asString());}if (!root.getTable("NUMEROTATION_AUTO").contains("CODE_LETTRAGE_PARTIEL")) {final AlterTable alterNumero = new AlterTable(root.getTable("NUMEROTATION_AUTO"));alterNumero.addVarCharColumn("CODE_LETTRAGE_PARTIEL", 256);root.getBase().getDataSource().execute(alterNumero.asString());root.refetchTable("NUMEROTATION_AUTO");root.getSchema().updateVersion();}if (!root.getTable("ECRITURE").contains("LETTRAGE_PARTIEL")) {final AlterTable alterEcriture = new AlterTable(root.getTable("ECRITURE"));alterEcriture.addVarCharColumn("LETTRAGE_PARTIEL", 256);root.getBase().getDataSource().execute(alterEcriture.asString());root.refetchTable("ECRITURE");root.getSchema().updateVersion();}SQLTable tableFactureF = root.getTable("FACTURE_FOURNISSEUR");if (!tableFactureF.contains("ID_AVOIR_FOURNISSEUR_2")) {final AlterTable alter = new AlterTable(tableFactureF);final SQLTable tableAvoirF = tableVF.getDBRoot().getTable("AVOIR_FOURNISSEUR");alter.addForeignColumn("ID_AVOIR_FOURNISSEUR_2", tableAvoirF);tableFactureF.getBase().getDataSource().execute(alter.asString());tableFactureF.getSchema().updateVersion();tableVF.fetchFields();}SQLTable tableNumAuto = root.getTable("NUMEROTATION_AUTO");if (!tableNumAuto.contains("ARTICLE_FORMAT")) {final AlterTable alterNumero = new AlterTable(tableNumAuto);alterNumero.addVarCharColumn("ARTICLE_FORMAT", 128);alterNumero.addIntegerColumn("ARTICLE_START", 1);root.getBase().getDataSource().execute(alterNumero.asString());root.refetchTable("NUMEROTATION_AUTO");root.getSchema().updateVersion();}if (!tableArticle.contains("VIRTUEL")) {final SQLCreateTable createTableDeclinaisonCouleur = new SQLCreateTable(root, "ARTICLE_DECLINAISON_COULEUR");createTableDeclinaisonCouleur.addVarCharColumn("NOM", 256);root.getBase().getDataSource().execute(createTableDeclinaisonCouleur.asString());InstallationPanel.insertUndef(createTableDeclinaisonCouleur);root.refetchTable("ARTICLE_DECLINAISON_COULEUR");root.getSchema().updateVersion();final SQLCreateTable createTableDeclinaisonTaille = new SQLCreateTable(root, "ARTICLE_DECLINAISON_TAILLE");createTableDeclinaisonTaille.addVarCharColumn("NOM", 256);root.getBase().getDataSource().execute(createTableDeclinaisonTaille.asString());InstallationPanel.insertUndef(createTableDeclinaisonTaille);root.refetchTable("ARTICLE_DECLINAISON_TAILLE");root.getSchema().updateVersion();final AlterTable alter = new AlterTable(tableArticle);alter.addBooleanColumn("VIRTUEL", false, false);alter.addForeignColumn("ID_ARTICLE_VIRTUEL_PERE", tableArticle);alter.addForeignColumn("ID_ARTICLE_DECLINAISON_TAILLE", root.getTable("ARTICLE_DECLINAISON_TAILLE"));alter.addForeignColumn("ID_ARTICLE_DECLINAISON_COULEUR", root.getTable("ARTICLE_DECLINAISON_COULEUR"));tableArticle.getBase().getDataSource().execute(alter.asString());tableArticle.getSchema().updateVersion();tableArticle.fetchFields();}for (String string : achatItems) {boolean alter = false;SQLTable tableItems = root.getTable(string);final AlterTable t = new AlterTable(tableItems);if (!tableItems.getFieldsName().contains("ID_ARTICLE_DECLINAISON_TAILLE")) {t.addForeignColumn("ID_ARTICLE_DECLINAISON_TAILLE", root.getTable("ARTICLE_DECLINAISON_TAILLE"));alter = true;}if (!tableItems.getFieldsName().contains("ID_ARTICLE_DECLINAISON_COULEUR")) {t.addForeignColumn("ID_ARTICLE_DECLINAISON_COULEUR", root.getTable("ARTICLE_DECLINAISON_COULEUR"));alter = true;}if (alter) {tableItems.getBase().getDataSource().execute(t.asString());tableItems.getSchema().updateVersion();tableItems.fetchFields();}}for (String tableName : tableElementWithTable) {final SQLTable tableToDecl = root.getTable(tableName);boolean alter = false;final AlterTable alterDecl = new AlterTable(tableToDecl);if (!tableToDecl.getFieldsName().contains("ID_ARTICLE_DECLINAISON_TAILLE")) {alterDecl.addForeignColumn("ID_ARTICLE_DECLINAISON_TAILLE", root.getTable("ARTICLE_DECLINAISON_TAILLE"));alter = true;}if (!tableToDecl.getFieldsName().contains("ID_ARTICLE_DECLINAISON_COULEUR")) {alterDecl.addForeignColumn("ID_ARTICLE_DECLINAISON_COULEUR", root.getTable("ARTICLE_DECLINAISON_COULEUR"));alter = true;}if (alter) {tableToDecl.getBase().getDataSource().execute(alterDecl.asString());tableToDecl.getSchema().updateVersion();tableToDecl.fetchFields();}}// Création de la table Tarif promotionif (!root.contains("TARIF_PROMOTION")) {SQLCreateTable createTarif = new SQLCreateTable(root, "TARIF_PROMOTION");createTarif.addVarCharColumn("NOM", 256);createTarif.addColumn("START", "date");createTarif.addColumn("END", "date");root.getBase().getDataSource().execute(createTarif.asString());InstallationPanel.insertUndef(createTarif);tableDevis.getSchema().updateVersion();root.refetchTable(createTarif.getName());}// Création de la table article Tarif promotionif (!root.contains("ARTICLE_TARIF_PROMOTION")) {SQLCreateTable createTarif = new SQLCreateTable(root, "ARTICLE_TARIF_PROMOTION");createTarif.addForeignColumn("ID_DEVISE", root.findTable("DEVISE", true));createTarif.addForeignColumn("ID_TAXE", root.findTable("TAXE", true));createTarif.addForeignColumn("ID_TARIF_PROMOTION", root.findTable("TARIF_PROMOTION", true));createTarif.addForeignColumn("ID_ARTICLE", root.findTable("ARTICLE", true));createTarif.addIntegerColumn("QTE", 1);createTarif.addDecimalColumn("PV_HT", 16, 6, BigDecimal.ZERO, false);createTarif.addDecimalColumn("PV_TTC", 16, 6, BigDecimal.ZERO, false);createTarif.addDecimalColumn("PRIX_METRIQUE_VT_1", 16, 6, BigDecimal.ZERO, false);createTarif.addDecimalColumn("PRIX_METRIQUE_VT_2", 16, 6, BigDecimal.ZERO, false);createTarif.addDecimalColumn("PRIX_METRIQUE_VT_3", 16, 6, BigDecimal.ZERO, false);root.getBase().getDataSource().execute(createTarif.asString());InstallationPanel.insertUndef(createTarif);tableDevis.getSchema().updateVersion();root.refetchTable(createTarif.getName());}if (!root.contains("TAXE_CATEGORIE_COMPTABLE")) {final SQLCreateTable createTableTaxeCat = new SQLCreateTable(root, "TAXE_CATEGORIE_COMPTABLE");createTableTaxeCat.addForeignColumn("ID_CATEGORIE_COMPTABLE", root.getTable("CATEGORIE_COMPTABLE"));createTableTaxeCat.addForeignColumn("ID_TAXE", root.getTable("TAXE"));createTableTaxeCat.addForeignColumn("ID_COMPTE_PCE_VENTE", root.getTable("COMPTE_PCE"));createTableTaxeCat.addForeignColumn("ID_COMPTE_PCE_ACHAT", root.getTable("COMPTE_PCE"));try {root.getBase().getDataSource().execute(createTableTaxeCat.asString());InstallationPanel.insertUndef(createTableTaxeCat);root.refetchTable("TAXE_CATEGORIE_COMPTABLE");root.getSchema().updateVersion();} catch (SQLException ex) {throw new IllegalStateException("Erreur lors de la création de la table " + "TAXE_CATEGORIE_COMPTABLE", ex);}}if (!root.contains("LOT_RECEPTION")) {SQLCreateTable createLotReception = new SQLCreateTable(root, "LOT_RECEPTION");createLotReception.addDateAndTimeColumn("DATE_RECEPTION");createLotReception.addVarCharColumn("FOURNISSEUR", 256);createLotReception.addVarCharColumn("ARTICLE", 256);createLotReception.addForeignColumn("ID_ARTICLE", root.findTable("ARTICLE", true));createLotReception.addForeignColumn("ID_DEPOT_STOCK", root.findTable("DEPOT_STOCK", true));createLotReception.addDecimalColumn("QUANTITE", 16, 6, BigDecimal.ONE, false);createLotReception.addVarCharColumn("NUMERO_LOT", 256);createLotReception.addVarCharColumn("NUMERO_SERIE", 256);createLotReception.addDateAndTimeColumn("DLC");createLotReception.addDateAndTimeColumn("DLUO");createLotReception.addForeignColumn("ID_BON_RECEPTION_ELEMENT", root.findTable("BON_RECEPTION_ELEMENT", true));root.getBase().getDataSource().execute(createLotReception.asString());InstallationPanel.insertUndef(createLotReception);SQLCreateTable createLotLivraison = new SQLCreateTable(root, "LOT_LIVRAISON");createLotLivraison.addDateAndTimeColumn("DATE_LIVRAISON");createLotLivraison.addVarCharColumn("CLIENT", 256);createLotLivraison.addVarCharColumn("ARTICLE", 256);createLotLivraison.addForeignColumn("ID_ARTICLE", root.findTable("ARTICLE", true));createLotLivraison.addForeignColumn("ID_DEPOT_STOCK", root.findTable("DEPOT_STOCK", true));createLotLivraison.addDecimalColumn("QUANTITE", 16, 6, BigDecimal.ONE, false);createLotLivraison.addVarCharColumn("NUMERO_LOT", 256);createLotLivraison.addVarCharColumn("NUMERO_SERIE", 256);createLotLivraison.addDateAndTimeColumn("DLC");createLotLivraison.addDateAndTimeColumn("DLUO");createLotLivraison.addForeignColumn("ID_BON_DE_LIVRAISON_ELEMENT", root.findTable("BON_DE_LIVRAISON_ELEMENT", true));root.getBase().getDataSource().execute(createLotLivraison.asString());InstallationPanel.insertUndef(createLotLivraison);}if (!root.contains("LOT")) {SQLCreateTable createLot = new SQLCreateTable(root, "LOT");createLot.addForeignColumn("ID_STOCK", root.findTable("STOCK", true));createLot.addDecimalColumn("QUANTITE", 16, 6, BigDecimal.ONE, false);createLot.addVarCharColumn("NUMERO_LOT", 256);createLot.addVarCharColumn("NUMERO_SERIE", 256);createLot.addDateAndTimeColumn("DLC");createLot.addDateAndTimeColumn("DLUO");root.getBase().getDataSource().execute(createLot.asString());InstallationPanel.insertUndef(createLot);SQLCreateTable createTransfertStock = new SQLCreateTable(root, "TRANSFERT_STOCK");createTransfertStock.addDateAndTimeColumn("DATE");createTransfertStock.addForeignColumn("ID_ARTICLE", root.findTable("ARTICLE", true));createTransfertStock.addForeignColumn("ID_DEPOT_STOCK_SOURCE", root.findTable("DEPOT_STOCK", true));createTransfertStock.addForeignColumn("ID_DEPOT_STOCK_DEST", root.findTable("DEPOT_STOCK", true));createTransfertStock.addDecimalColumn("QUANTITE", 16, 6, BigDecimal.ONE, false);createTransfertStock.addVarCharColumn("NUMEROS_SERIE", 50000);createTransfertStock.addVarCharColumn("NUMEROS_LOT", 50000);createTransfertStock.addVarCharColumn("CONTEXTE", 256);root.getBase().getDataSource().execute(createTransfertStock.asString());InstallationPanel.insertUndef(createTransfertStock);final AlterTable alterArticle = new AlterTable(tableArticle);alterArticle.addBooleanColumn("NUMERO_LOT_REQUIS", false, false);alterArticle.addBooleanColumn("NUMERO_SERIE_REQUIS", false, false);alterArticle.addBooleanColumn("DLC_REQUIS", false, false);alterArticle.addBooleanColumn("DLUO_REQUIS", false, false);tableArticle.getBase().getDataSource().execute(alterArticle.asString());tableArticle.getSchema().updateVersion();tableArticle.fetchFields();final AlterTable alterBonReceptionElement = new AlterTable(root.getTable("BON_RECEPTION_ELEMENT"));alterBonReceptionElement.addForeignColumn("ID_LOT", createLot);tableArticle.getBase().getDataSource().execute(alterBonReceptionElement.asString());final AlterTable alterBonLivraisonElement = new AlterTable(root.getTable("BON_DE_LIVRAISON"));alterBonLivraisonElement.addForeignColumn("ID_LOT", createLot);tableArticle.getBase().getDataSource().execute(alterBonLivraisonElement.asString());tableArticle.getSchema().updateVersion();root.refetch();}SQLTable tableBR = root.getTable("BON_RECEPTION");SQLTable tableModele = root.getTable("MODELE");if (!tableBR.contains("ID_MODELE")) {AlterTable a = new AlterTable(tableBR);a.addForeignColumn("ID_MODELE", tableModele);root.getBase().getDataSource().execute(a.asString());tableBR.getSchema().updateVersion();root.refetchTable(tableBR.getName());}SQLTable typeModele = tableModele.getForeignTable("ID_TYPE_MODELE");SQLSelect sel = new SQLSelect();sel.addSelect(typeModele.getKey());sel.setWhere(new Where(typeModele.getField("TABLE"), "=", "BON_RECEPTION"));List<SQLRow> l = SQLRowListRSH.execute(sel);if (l.isEmpty()) {SQLRowValues rowVals = new SQLRowValues(typeModele);rowVals.put("TABLE", "BON_RECEPTION");rowVals.put("NOM", "Bon de réception");rowVals.put("DEFAULT_MODELE", "BonReception");rowVals.commit();}// Fix undefinednew SetFKDefault(root.getDBSystemRoot()).changeAll(root);List<SQLUpdate> ups = new ArrayList<>();if (userT.getUndefinedIDNumber() != null) {for (SQLField userF : root.getDBSystemRoot().getGraph().getReferentKeys(userT)) {if (userF.getTable().getDBRoot() == root) {SQLUpdate up = new SQLUpdate(Where.isNull(userF));up.add(userF, userT.getUndefinedIDNumber());ups.add(up);}}}if (!ups.isEmpty()) {SQLUpdate.executeMultipleWithBatch(root.getDBSystemRoot(), ups);System.err.println("Updater_1_5.update() fixed " + ups.size() + " error(s) on FK to USER_COMMON");} else {System.err.println("Updater_1_5.update() no error on FK to USER_COMMON");}}public static void initStock(SQLRow rowArticle, int idDepot) {SQLSelect selStock = new SQLSelect();selStock.addSelectStar(rowArticle.getTable().getTable("STOCK"));selStock.setWhere(new Where(rowArticle.getTable().getTable("STOCK").getField("ID_ARTICLE"), "=", rowArticle.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>();try {if (!initedDepot.keySet().contains(idDepot)) {SQLRowValues rowVals = new SQLRowValues(rowArticle.getTable().getTable("STOCK"));rowVals.put("ID_ARTICLE", rowArticle.getID());rowVals.put("ID_DEPOT_STOCK", idDepot);SQLRow rowStock = rowVals.commit();if ((rowArticle.getObject("ID_DEPOT_STOCK") == null || rowArticle.isForeignEmpty("ID_DEPOT_STOCK")) && idDepot == DepotStockSQLElement.DEFAULT_ID) {rowArticle.createEmptyUpdateRow().put("ID_STOCK", rowStock.getID()).put("ID_DEPOT_STOCK", DepotStockSQLElement.DEFAULT_ID).commit();} else if (idDepot == rowArticle.getForeignID("ID_DEPOT_STOCK")) {rowArticle.createEmptyUpdateRow().put("ID_STOCK", rowStock.getID()).commit();}stockItems.add(new StockItem(rowArticle, rowStock));} else {SQLRow rowExisting = initedDepot.get(idDepot);if ((rowArticle.getObject("ID_DEPOT_STOCK") == null || rowArticle.isForeignEmpty("ID_DEPOT_STOCK")) && idDepot == DepotStockSQLElement.DEFAULT_ID) {rowArticle.createEmptyUpdateRow().put("ID_STOCK", rowExisting.getID()).put("ID_DEPOT_STOCK", DepotStockSQLElement.DEFAULT_ID).commit();} else if (idDepot == rowArticle.getForeignID("ID_DEPOT_STOCK")) {rowArticle.createEmptyUpdateRow().put("ID_STOCK", rowExisting.getID()).commit();}stockItems.add(new StockItem(rowArticle, rowExisting));}} catch (SQLException e) {ExceptionHandler.handle("Erreur lors de l'initialisation du stock de l'article", e);}if (rowArticle.getReferentRows(rowArticle.getTable().getTable("ARTICLE_ELEMENT").getField("ID_ARTICLE_PARENT")).size() > 0) {ComposedItemStockUpdater up = new ComposedItemStockUpdater(rowArticle.getTable().getDBRoot(), stockItems);try {up.updateNomenclature(stockItems);} catch (SQLException e) {ExceptionHandler.handle("Erreur lors de l'actualisation du stock!", e);}}}public static void initStock(SQLRow row) {int foreignID = DepotStockSQLElement.DEFAULT_ID;if (row.getObject("ID_DEPOT_STOCK") != null && !row.isForeignEmpty("ID_DEPOT_STOCK")) {foreignID = row.getForeignID("ID_DEPOT_STOCK");}initStock(row, foreignID);}public static void exec(final AlterTable alter) throws SQLException {alter.getTable().getDBSystemRoot().getDataSource().execute(alter.asString());alter.getTable().getSchema().updateVersion();alter.getTable().fetchFields();}}