WebSVN - OpenConcerto - Diff - Rev 174 and 177 - /trunk/OpenConcerto/src/org/openconcerto/erp/config/update/Updater_1

  */
  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.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.SQLTable.Index;
 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;
             SQLTable tableCmdFElt = root.getTable("COMMANDE_ELEMENT");
             if (!tableCmdFElt.contains("RECU")) {
                 AlterTable t = new AlterTable(tableCmdFElt);
                 t.addBooleanColumn("RECU_FORCED", Boolean.FALSE, false);
-                t.addBooleanColumn("RECU", Boolean.TRUE, false);
+                t.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();
                     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 value
+                String 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);
+                    build.set("QTE_RECUE", "\"QTE\"*\"QTE_UNITAIRE\"");
+                    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ère
             SQLTable tableArt = root.getTable("ARTICLE");
             if (!tableArt.contains("MATIERE")) {
             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();
+        }
         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);
             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();
+        }
         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);
             root.getBase().getDataSource().execute(alterB.asString());
             root.refetchTable("FICHE_PAYE_ELEMENT");
             root.getSchema().updateVersion();
+        }
-        if (!tableVarSal.contains("IJSS_BRUT")) {
+        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 alterB = new AlterTable(tableVarSal);
+        final AlterTable alterVarSal = new AlterTable(tableVarSal);
-            List<String> f = Arrays.asList("IJSS_BRUT", "IJSS_NET", "FRAIS_PRO", "RBT_TRANSPORT");
-            for (String field : f) {
+        for (String field : f) {
+            if (!tableVarSal.contains(field)) {
+                upVar = true;
-                alterB.addColumn(field, "real DEFAULT 0");
+                alterVarSal.addColumn(field, "real DEFAULT 0");
-                alterB.addColumn(field + "_DEFAULT_VAL", "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(alterB.asString());
+            root.getBase().getDataSource().execute(alterVarSal.asString());
             root.refetchTable(tableVarSal.getName());
             root.getSchema().updateVersion();
+        }
         SQLTable tableDevis = root.getTable("DEVIS");
             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();
             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")) {
                 throw new IllegalStateException("Erreur lors de la création de la table " + EmailTemplateSQLElement.TABLE_NAME, ex);
+            }
+        }
         // Force undefined policy to inDb
         root.setMetadata(SQLTable.UNDEFINED_ID_POLICY, "inDB");
-        final Map<String, Number> mapTableNameUndefined = SQLTable.getUndefIDs(root.getSchema());
+        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);
             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.NOM
+        if (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();
+        }
+    }
     public static void initStock(SQLRow rowArticle, int idDepot) {
         SQLSelect selStock = new SQLSelect();

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 174 → 177