WebSVN - OpenConcerto - Diff - Rev 151 and 156 - /trunk/OpenConcerto/src/org/openconcerto/erp/config/InstallationPanel.java

 import org.openconcerto.erp.core.humanresources.payroll.report.LignePayeSimplifiee;
 import org.openconcerto.erp.core.sales.order.ui.EtatCommandeClient;
 import org.openconcerto.erp.core.sales.quote.element.EtatDevisSQLElement;
 import org.openconcerto.erp.modules.ModuleManager;
 import org.openconcerto.erp.modules.ModuleReference;
+import org.openconcerto.sql.Configuration;
 import org.openconcerto.sql.changer.convert.AddFK;
 import org.openconcerto.sql.changer.convert.ChangeIDToInt;
 import org.openconcerto.sql.changer.correct.CorrectOrder;
 import org.openconcerto.sql.changer.correct.FixSerial;
 import org.openconcerto.sql.element.GroupSQLComponent;
                     @Override
                     public void run() {
                         System.setProperty(SQLSchema.NOAUTO_CREATE_METADATA, "false");
                         final ComptaPropsConfiguration conf = ComptaPropsConfiguration.create(true);
+                        final DBSystemRoot systemRoot = conf.getSystemRoot();
+                        if (systemRoot.getChild(conf.getRootNameValue().getValue()) == null) {
+                            conf.destroy();
+                            SwingUtilities.invokeLater(new Runnable() {
+                                @Override
+                                public void run() {
+                                    up.setEnabled(true);
+                                    bar.setValue(bar.getMaximum());
+                                    JOptionPane.showMessageDialog(InstallationPanel.this, "Votre base de données n'est pas initialisée");
+                                }
+                            });
+                            return;
+                        }
                         updateDatabase(conf);
                         conf.destroy();
                         SwingUtilities.invokeLater(new Runnable() {
             c.gridwidth = 1;
             user.addActionListener(new ActionListener() {
                 @Override
                 public void actionPerformed(ActionEvent e) {
-                    // TODO Auto-generated method stub
                     try {
                         if (finderPanel.getServerConfig().createUserIfNeeded(login.getText(), mdp.getText())) {
                             JOptionPane.showMessageDialog(InstallationPanel.this, "L'utilisateur openconcerto a été correctement ajouté.");
                         } else {
                             JOptionPane.showMessageDialog(InstallationPanel.this, "L'utilisateur openconcerto existe déjà dans la base.");
+                        }
                     } catch (Exception e1) {
-                        // TODO Auto-generated catch block
                         e1.printStackTrace();
                         JOptionPane.showMessageDialog(InstallationPanel.this, "Une erreur est survenue pendant la connexion au serveur, vérifiez vos paramètres de connexion.");
+                    }
+                }
             });
         final List<AlterTable> alters = new ArrayList<AlterTable>();
         final List<UpdateBuilder> builds = new ArrayList<UpdateBuilder>();
         for (final SQLTable t : root.getTables()) {
             final AlterTable alter = new AlterTable(t);
             for (final SQLField f : t.getFields()) {
+                // on PG, CLOB are text and the JDBC driver returns Types.VARCHAR, not CLOB. So test
+                // if the type name contains "char", since we only want unbounded varchar, not
+                // text/clob.
-                if (f.getType().getType() == Types.VARCHAR && f.getType().getSize() == Integer.MAX_VALUE) {
+                if (f.getType().getType() == Types.VARCHAR && f.getType().getSize() == Integer.MAX_VALUE && f.getTypeDecl().contains("char")) {
                     UpdateBuilder build = new UpdateBuilder(t);
-                    build.set(f.getName(), "''");
+                    build.setObject(f.getName(), "");
-                    build.setWhere(new Where(f, "=", (Object) null));
+                    build.setWhere(Where.isNull(f));
                     builds.add(build);
                     final String fName = f.getName();
                     final int size;
                     if (t.getName().contains("USER") && namesSet.contains(fName))
         checkPrefsComptable(root);
         checkDepartementExists(root, "Aisne", "Laon", "Picardie", "02");
         if (root.contains("TARIF_AGENCE")) {
+            SQLTable tableCmdFA = root.getTable("COMMANDE");
+            if (!tableCmdFA.contains("INCOTERM")) {
+                AlterTable t = new AlterTable(tableCmdFA);
+                t.addVarCharColumn("INCOTERM", 256);
+                tableCmdFA.getBase().getDataSource().execute(t.asString());
+                tableCmdFA.getSchema().updateVersion();
+                tableCmdFA.fetchFields();
+            }
+            SQLTable tableArticleA = root.getTable("ARTICLE");
+            if (!tableArticleA.contains("CLOSED_KIT")) {
+                AlterTable t = new AlterTable(tableArticleA);
+                t.addBooleanColumn("CLOSED_KIT", Boolean.FALSE, true);
+                tableArticleA.getBase().getDataSource().execute(t.asString());
+                tableArticleA.getSchema().updateVersion();
+                tableArticleA.fetchFields();
+            }
+            if (!tableArticleA.contains("CLOSED_KIT_DESC")) {
+                AlterTable t = new AlterTable(tableArticleA);
+                t.addVarCharColumn("CLOSED_KIT_DESC", 4096);
+                tableArticleA.getBase().getDataSource().execute(t.asString());
+                tableArticleA.getSchema().updateVersion();
+                tableArticleA.fetchFields();
+            }
+            SQLTable tableCmdFEltA = root.getTable("COMMANDE_ELEMENT");
+            if (!tableCmdFEltA.contains("INCOTERM")) {
+                AlterTable t = new AlterTable(tableCmdFEltA);
+                t.addVarCharColumn("INCOTERM", 256);
+                tableCmdFEltA.getBase().getDataSource().execute(t.asString());
+                tableCmdFEltA.getSchema().updateVersion();
+                tableCmdFEltA.fetchFields();
+            }
+            if (root.getTable("VILLE") == null) {
+                // INSERT INTO "OpenConcerto49"."VILLE" ("CODE_POSTAL","NOM") SELECT
+                // "CODE_POSTAL",
+                // "VILLE" FROM "OpenConcerto49"."ADRESSE" a WHERE a."ARCHIVE"=0
+                final SQLCreateTable createTableVille = new SQLCreateTable(root, "VILLE");
+                createTableVille.addVarCharColumn("NOM", 2048);
+                createTableVille.addVarCharColumn("CODE_POSTAL", 2048);
+                createTableVille.addLongColumn("X_LAMBERT", 0L, true);
+                createTableVille.addLongColumn("Y_LAMBERT", 0L, true);
+                createTableVille.addLongColumn("POPULATION", 0L, true);
+                try {
+                    root.getBase().getDataSource().execute(createTableVille.asString());
+                    insertUndef(createTableVille);
+                    root.refetchTable("VILLE");
+                    root.getSchema().updateVersion();
+                } catch (SQLException ex2) {
+                    throw new IllegalStateException("Erreur lors de la création de la table " + "VILLE", ex2);
+                }
+            }
+            if (root.getTable("ARTICLE_PRIX_MIN_VENTE") == null) {
+                final SQLCreateTable createTablePrixMin = new SQLCreateTable(root, "ARTICLE_PRIX_MIN_VENTE");
+                createTablePrixMin.addForeignColumn("ARTICLE");
+                createTablePrixMin.addIntegerColumn("QTE", 1);
+                createTablePrixMin.addDecimalColumn("PRIX", 16, 8, BigDecimal.ZERO, true);
+                createTablePrixMin.addDateAndTimeColumn("DATE");
+                try {
+                    root.getBase().getDataSource().execute(createTablePrixMin.asString());
+                    insertUndef(createTablePrixMin);
+                    root.refetchTable("ARTICLE_PRIX_MIN_VENTE");
+                    root.getSchema().updateVersion();
+                } catch (SQLException ex2) {
+                    throw new IllegalStateException("Erreur lors de la création de la table " + "ARTICLE_PRIX_MIN_VENTE", ex2);
+                }
+            }
+            {
+                final SQLTable tableTarifAgence = root.getTable("TARIF_AGENCE");
+                AlterTable tTarifAgence = new AlterTable(tableTarifAgence);
+                boolean updateTarifAgence = false;
+                if (!tableTarifAgence.contains("PRC_AGENCE")) {
+                    updateTarifAgence = true;
+                    tTarifAgence.addDecimalColumn("PRC_AGENCE", 16, 8, BigDecimal.ZERO, true);
+                }
+                if (!tableTarifAgence.contains("CLOSED_KIT")) {
+                    updateTarifAgence = true;
+                    tTarifAgence.addBooleanColumn("CLOSED_KIT", false, true);
+                }
+                if (!tableTarifAgence.contains("PRIX_MIN_VENTE")) {
+                    updateTarifAgence = true;
+                    tTarifAgence.addDecimalColumn("PRIX_MIN_VENTE", 16, 8, BigDecimal.ZERO, true);
+                }
+                if (updateTarifAgence) {
+                    tableTarifAgence.getBase().getDataSource().execute(tTarifAgence.asString());
+                    tableTarifAgence.getSchema().updateVersion();
+                    tableTarifAgence.fetchFields();
+                }
+            }
+            {
+                final SQLTable tableClient = root.getTable("CLIENT");
+                AlterTable tClient = new AlterTable(tableClient);
+                boolean updateClient = false;
+                if (!tableClient.contains("ALG_REGISTRE")) {
+                    updateClient = true;
+                    tClient.addVarCharColumn("ALG_REGISTRE", 512);
+                }
+                if (!tableClient.contains("ALG_MATRICULE")) {
+                    updateClient = true;
+                    tClient.addVarCharColumn("ALG_MATRICULE", 512);
+                }
+                if (!tableClient.contains("ALG_ARTICLE")) {
+                    updateClient = true;
+                    tClient.addVarCharColumn("ALG_ARTICLE", 512);
+                }
+                if (updateClient) {
+                    tableClient.getBase().getDataSource().execute(tClient.asString());
+                    tableClient.getSchema().updateVersion();
+                    tableClient.fetchFields();
+                }
+            }
+            final SQLTable tableMvtStock = root.getTable("MOUVEMENT_STOCK");
+            if (!tableMvtStock.contains("PRICE")) {
+                AlterTable tMvt = new AlterTable(tableMvtStock);
+                tMvt.addDecimalColumn("PRICE", 16, 6, BigDecimal.ZERO, true);
+                tableMvtStock.getBase().getDataSource().execute(tMvt.asString());
+                tableMvtStock.getSchema().updateVersion();
+                tableMvtStock.fetchFields();
+            }
+            final SQLTable tableArticle = root.getTable("ARTICLE");
+            if (!tableArticle.contains("AUTO_PRIX_MIN_VENTE_NOMENCLATURE")) {
+                AlterTable tArt = new AlterTable(tableArticle);
+                tArt.addBooleanColumn("AUTO_PRIX_MIN_VENTE_NOMENCLATURE", false, true);
+                tableMvtStock.getBase().getDataSource().execute(tArt.asString());
+                tableMvtStock.getSchema().updateVersion();
+                tableMvtStock.fetchFields();
+            }
+            SQLTable tableContact = root.getTable("CONTACT");
+            if (!tableContact.contains("TYPE")) {
+                AlterTable t = new AlterTable(tableContact);
+                t.addVarCharColumn("TYPE", 256);
+                t.addVarCharColumn("SERVICE", 256);
+                t.addVarCharColumn("PAYS", 256);
+                t.addForeignColumn("ID_ADRESSE", root.getTable("ADRESSE"));
+                tableContact.getBase().getDataSource().execute(t.asString());
+                tableContact.getSchema().updateVersion();
+                tableContact.fetchFields();
+            }
+            if (!root.contains("PERSONNEL_AFHYMAT")) {
+                final SQLCreateTable createTablePers = new SQLCreateTable(root, "PERSONNEL_AFHYMAT");
+                createTablePers.addVarCharColumn("NOM", 256);
+                createTablePers.addVarCharColumn("PRENOM", 256);
+                createTablePers.addVarCharColumn("FONCTION", 256);
+                try {
+                    root.getBase().getDataSource().execute(createTablePers.asString());
+                    insertUndef(createTablePers);
+                    root.refetchTable("PERSONNEL_AFHYMAT");
+                    root.getSchema().updateVersion();
+                } catch (SQLException ex) {
+                    throw new IllegalStateException("Erreur lors de la création de la table " + "PERSONNEL_AFHYMAT", ex);
+                }
+                final SQLCreateTable createTable = new SQLCreateTable(root, "PERSONNEL_AFHYMAT_COUT");
+                for (int i = 1; i <= 12; i++) {
+                    createTable.addDecimalColumn("SAL_MONTH_" + i, 16, 2, BigDecimal.ZERO, false);
+                    createTable.addDecimalColumn("COM_MONTH_" + i, 16, 2, BigDecimal.ZERO, false);
+                }
+                createTable.addIntegerColumn("ANNEE", 0);
+                createTable.addForeignColumn("ID_PERSONNEL_AFHYMAT", root.getTable("PERSONNEL_AFHYMAT"));
+                try {
+                    root.getBase().getDataSource().execute(createTable.asString());
+                    insertUndef(createTable);
+                    root.refetchTable("PERSONNEL_AFHYMAT_COUT");
+                    root.getSchema().updateVersion();
+                } catch (SQLException ex) {
+                    throw new IllegalStateException("Erreur lors de la création de la table " + "PERSONNEL_AFHYMAT_COUT", ex);
+                }
+            }
             List<String> tablesUiLocked = Arrays.asList("BON_DE_LIVRAISON", "COMMANDE_CLIENT", "SAISIE_VENTE_FACTURE", "AVOIR_CLIENT", "COMMANDE", "BON_RECEPTION", "FACTURE_FOURNISSEUR");
             for (String tableName : tablesUiLocked) {
                 final SQLTable table = root.getTable(tableName);
                 AlterTable t = new AlterTable(table);
                     throw new IllegalStateException("Erreur lors de la création de la table " + "RELIQUAT_BL", ex);
+                }
+            }
             if (!root.getTable("ARTICLE").contains("TRANSPORT")) {
-                final SQLTable tableArticle = root.getTable("ARTICLE");
                 AlterTable alterArticle = new AlterTable(tableArticle);
                 alterArticle.addBooleanColumn("TRANSPORT", false, false);
                 tableArticle.getBase().getDataSource().execute(alterArticle.asString());
                 tableArticle.getSchema().updateVersion();
                 tableArticle.fetchFields();
+                }
+            }
+            {
                 if (root.getName().endsWith("54")) {
-                    final SQLTable tableArticle = root.getTable("ARTICLE");
                     AlterTable tArt = new AlterTable(tableArticle);
                     if (!tableArticle.contains("LABEL_DOUANE")) {
                         tArt.addVarCharColumn("LABEL_DOUANE", 512);
                         tableArticle.getBase().getDataSource().execute(tArt.asString());
                         tableArticle.getSchema().updateVersion();
             createTable.addDecimalColumn("TAUX_COMMERCIAL", 12, 6, new BigDecimal(1), false);
             try {
                 root.getDBSystemRoot().getDataSource().execute(createTable.asString());
                 insertUndef(createTable);
                 tableDevis.getSchema().updateVersion();
+                root.refetchTable("DEVISE_HISTORIQUE");
             } catch (SQLException ex) {
                 throw new IllegalStateException("Erreur lors de la création de la table TASK", ex);
+            }
+        }
+            }
+        }
         patchFieldElt1Dot3(table, root);
+    }
-    private void updateSocieteSchema(final DBRoot root) throws IOException, Exception {
+    private void updateSocieteSchema(final Configuration conf, final DBRoot root) throws IOException, Exception {
         final DBSystemRoot sysRoot = root.getDBSystemRoot();
         final SQLDataSource ds = sysRoot.getDataSource();
         System.out.println("InstallationPanel.InstallationPanel() UPDATE COMMERCIAL " + root);
         // Fix commercial Ordre
             // Suppression des champs 1.0
             System.out.println("InstallationPanel.InstallationPanel() UPDATE FROM 1.0 " + root);
             final List<ChangeTable<?>> changes = new ArrayList<ChangeTable<?>>();
             final ModuleManager instance = new ModuleManager();
-            instance.setRoot(root);
+            instance.setup(root, conf);
             final Collection<ModuleReference> refs = instance.getModulesInstalledRemotely();
             final Set<String> allUsedTable = new HashSet<String>();
             for (ModuleReference ref : refs) {
                 Set<String> tableNames = instance.getCreatedItems(ref.getID()).get0();
                 allUsedTable.addAll(tableNames);
+            }
             System.out.println("Tables created by modules:" + allUsedTable);
+            final Set<String> notMigrated = instance.migrateOldTransientDirs();
+            if (!notMigrated.isEmpty())
+                System.out.println("Couldn't migrate old backed up/failed modules: " + notMigrated);
             final List<String> alterRequests = ChangeTable.cat(changes, root.getName());
             try {
                 for (final String req : alterRequests) {
                     ds.execute(req);
+                }
             rowVals.update();
+        }
+    }
     public void updateDatabase(final ComptaPropsConfiguration conf) {
         try {
-            final SQLDataSource ds = conf.getSystemRoot().getDataSource();
+            final DBSystemRoot systemRoot = conf.getSystemRoot();
-            System.err.println("SystemRoot:" + conf.getSystemRoot());
+            System.err.println("SystemRoot:" + systemRoot);
+            final DBRoot rootCommon = conf.getRoot();
-            System.err.println("Root:" + conf.getRoot());
+            System.err.println("Root:" + rootCommon);
+            final SQLDataSource ds = systemRoot.getDataSource();
             // FixUnbounded varchar
-            fixUnboundedVarchar(conf.getRoot());
+            fixUnboundedVarchar(rootCommon);
             // FIXME DROP CONSTRAINT UNIQUE ORDRE ON
             // CONTACT_FOURNISSEUR
-            checkCompteDefault(conf.getRoot());
+            checkCompteDefault(rootCommon);
-            updateSocieteTable(conf.getRoot());
+            updateSocieteTable(rootCommon);
-            updateVille(conf.getRoot().getTable("ADRESSE"));
+            updateVille(rootCommon.getTable("ADRESSE"));
             // Champ user 1.4.1
-            final SQLTable tableUser = conf.getRoot().getTable("USER_COMMON");
+            final SQLTable tableUser = rootCommon.getTable("USER_COMMON");
             if (tableUser != null) {
                 final AlterTable alter = new AlterTable(tableUser);
                 boolean alterUser = false;
                 if (!tableUser.contains("DISABLED")) {
                     alter.addBooleanColumn("DISABLED", Boolean.FALSE, false);
                     alterUser = true;
+                }
                 if (alterUser) {
                     final String req = alter.asString();
-                    conf.getRoot().getDBSystemRoot().getDataSource().execute(req);
+                    rootCommon.getDBSystemRoot().getDataSource().execute(req);
-                    conf.getRoot().refetchTable(tableUser.getName());
+                    rootCommon.refetchTable(tableUser.getName());
-                    conf.getRoot().getSchema().updateVersion();
+                    rootCommon.getSchema().updateVersion();
+                }
+            }
             // Champ Paye
-            final SQLTable tableCaisse = conf.getRoot().getTable("CAISSE_COTISATION");
+            final SQLTable tableCaisse = rootCommon.getTable("CAISSE_COTISATION");
             if (!tableCaisse.contains("NUMERO_COMPTE_PCE")) {
                 final AlterTable alter = new AlterTable(tableCaisse);
                 alter.addVarCharColumn("NUMERO_COMPTE_PCE", 128);
                 alter.addVarCharColumn("NUMERO_COMPTE_PCE_CHARGES", 128);
                 final String req = alter.asString();
-                conf.getRoot().getDBSystemRoot().getDataSource().execute(req);
+                rootCommon.getDBSystemRoot().getDataSource().execute(req);
-                conf.getRoot().refetchTable(tableCaisse.getName());
+                rootCommon.refetchTable(tableCaisse.getName());
-                conf.getRoot().getSchema().updateVersion();
+                rootCommon.getSchema().updateVersion();
+            }
-            final SQLTable tableRC = conf.getRoot().getTable("RUBRIQUE_COTISATION");
+            final SQLTable tableRC = rootCommon.getTable("RUBRIQUE_COTISATION");
             if (!tableRC.contains("PART_CSG_SANS_ABATTEMENT")) {
                 final AlterTable alter = new AlterTable(tableRC);
                 alter.addBooleanColumn("PART_CSG_SANS_ABATTEMENT", Boolean.FALSE, false);
                 final String req = alter.asString();
-                conf.getRoot().getDBSystemRoot().getDataSource().execute(req);
+                rootCommon.getDBSystemRoot().getDataSource().execute(req);
-                conf.getRoot().refetchTable(tableRC.getName());
+                rootCommon.refetchTable(tableRC.getName());
-                conf.getRoot().getSchema().updateVersion();
+                rootCommon.getSchema().updateVersion();
+            }
             // Paye simplifiee
             List<SQLRow> rowRubriqueReduGvt = new ArrayList<>();
+            {
-                final SQLTable tableRCom = conf.getRoot().getTable("RUBRIQUE_COMM");
+                final SQLTable tableRCom = rootCommon.getTable("RUBRIQUE_COMM");
                 if (!tableRCom.contains("REDUCTION_GVT_COM")) {
                     final AlterTable alter = new AlterTable(tableRCom);
                     alter.addBooleanColumn("REDUCTION_GVT_COM", Boolean.FALSE, false);
                     final String req = alter.asString();
-                    conf.getRoot().getDBSystemRoot().getDataSource().execute(req);
+                    rootCommon.getDBSystemRoot().getDataSource().execute(req);
-                    conf.getRoot().refetchTable(tableRCom.getName());
+                    rootCommon.refetchTable(tableRCom.getName());
-                    conf.getRoot().getSchema().updateVersion();
+                    rootCommon.getSchema().updateVersion();
                     SQLRowValues rowValsCommChom = new SQLRowValues(tableRCom);
                     rowValsCommChom.put("TAUX_SAL", "1.45;");
                     rowValsCommChom.put("NB_BASE", "SAL_BRUT;");
                     rowValsCommChom.put("NOM", "Assurance chômage reduction 2018");
                     rowValsCommCSG.put("REDUCTION_GVT_COM", Boolean.TRUE);
                     rowValsCommCSG.putRowValues("ID_PERIODE_VALIDITE").put("JANVIER", Boolean.TRUE);
                     rowRubriqueReduGvt.add(rowValsCommCSG.commit());
                     SQLSelect sel = new SQLSelect();
-                    sel.addSelect(conf.getRoot().getTable("PROFIL_PAYE").getKey());
+                    sel.addSelect(rootCommon.getTable("PROFIL_PAYE").getKey());
                     List<SQLRow> rowsProfil = SQLRowListRSH.execute(sel);
                     int pos = 70;
                     for (SQLRow rowRub : rowRubriqueReduGvt) {
                         for (SQLRow rowProfil : rowsProfil) {
-                            SQLRowValues rowValsRubGvt = new SQLRowValues(conf.getRoot().getTable("PROFIL_PAYE_ELEMENT"));
+                            SQLRowValues rowValsRubGvt = new SQLRowValues(rootCommon.getTable("PROFIL_PAYE_ELEMENT"));
                             rowValsRubGvt.put("ID_PROFIL_PAYE", rowProfil.getID());
                             rowValsRubGvt.put("POSITION", pos);
                             rowValsRubGvt.put("IDSOURCE", rowRub.getID());
                             rowValsRubGvt.put("SOURCE", rowRub.getTable().getName());
                             rowValsRubGvt.put("NOM", rowRub.getString("NOM"));
+                        }
                         pos++;
+                    }
+                }
-                final SQLTable tableRB = conf.getRoot().getTable("RUBRIQUE_BRUT");
+                final SQLTable tableRB = rootCommon.getTable("RUBRIQUE_BRUT");
                 if (!tableRB.contains("AVANTAGE_NATURE")) {
                     final AlterTable alter = new AlterTable(tableRB);
                     alter.addBooleanColumn("AVANTAGE_NATURE", Boolean.FALSE, false);
                     final String req = alter.asString();
-                    conf.getRoot().getDBSystemRoot().getDataSource().execute(req);
+                    rootCommon.getDBSystemRoot().getDataSource().execute(req);
-                    conf.getRoot().refetchTable(tableRB.getName());
+                    rootCommon.refetchTable(tableRB.getName());
-                    conf.getRoot().getSchema().updateVersion();
+                    rootCommon.getSchema().updateVersion();
+                }
                 if (!tableRC.contains("LIGNE_PAYE_SIMPLIFIEE")) {
                     final AlterTable alter = new AlterTable(tableRC);
                     alter.addBooleanColumn("ALLEGEMENT_COTISATION", Boolean.FALSE, false);
                     alter.addVarCharColumn("LIGNE_PAYE_SIMPLIFIEE", 256);
                     final String req = alter.asString();
-                    conf.getRoot().getDBSystemRoot().getDataSource().execute(req);
+                    rootCommon.getDBSystemRoot().getDataSource().execute(req);
-                    conf.getRoot().refetchTable(tableRC.getName());
+                    rootCommon.refetchTable(tableRC.getName());
-                    conf.getRoot().getSchema().updateVersion();
+                    rootCommon.getSchema().updateVersion();
                     Map<String, String> liaison = new HashMap<String, String>();
                     liaison.put("COTCSA", LignePayeSimplifiee.SANTE_SECURITE.getId());
                     liaison.put("COTMALADIE", LignePayeSimplifiee.SANTE_SECURITE.getId());
                     liaison.put("COTAF", LignePayeSimplifiee.FAMILLE_ALLOCATIONS.getId());
                     liaison.put("COTVEUV", LignePayeSimplifiee.SANTE_SECURITE.getId());
                     liaison.put("COTCSGIMP", LignePayeSimplifiee.CSG_IMP.getId());
                     liaison.put("COTCSGDED", LignePayeSimplifiee.CSG_NON_IMP.getId());
                     liaison.put("COTFILLON", LignePayeSimplifiee.ALLEGEMENT_COTISATIONS.getId());
                     liaison.put("COTFNGS", LignePayeSimplifiee.ASSURANCE_CHOMAGE_CHOMAGE.getId());
-                    liaison.put("COTCADRET1", LignePayeSimplifiee.RETRAITE_COMPLEMENTAIRE_TA.getId());
+                    liaison.put("COTCADRET1", LignePayeSimplifiee.RETRAITE_COMPLEMENTAIRE_T1.getId());
-                    liaison.put("COTCADRET2", LignePayeSimplifiee.RETRAITE_COMPLEMENTAIRE_TB.getId());
+                    liaison.put("COTCADRET2", LignePayeSimplifiee.RETRAITE_COMPLEMENTAIRE_T2.getId());
-                    liaison.put("COT_CADRE_RET_C_A", LignePayeSimplifiee.RETRAITE_COMPLEMENTAIRE_TA.getId());
+                    liaison.put("COT_CADRE_RET_C_A", LignePayeSimplifiee.RETRAITE_COMPLEMENTAIRE_T1.getId());
-                    liaison.put("COT_CADRE_RET_C_B", LignePayeSimplifiee.RETRAITE_COMPLEMENTAIRE_TB.getId());
+                    liaison.put("COT_CADRE_RET_C_B", LignePayeSimplifiee.RETRAITE_COMPLEMENTAIRE_T2.getId());
-                    liaison.put("COT_CADRE_RET_C_C", LignePayeSimplifiee.RETRAITE_COMPLEMENTAIRE_TC.getId());
+                    liaison.put("COT_CADRE_RET_C_C", LignePayeSimplifiee.RETRAITE_COMPLEMENTAIRE_T2.getId());
                     liaison.put("COTAPEC", LignePayeSimplifiee.ASSURANCE_CHOMAGE_APEC.getId());
                     liaison.put("COTAPECFORF", LignePayeSimplifiee.ASSURANCE_CHOMAGE_APEC.getId());
-                    liaison.put("COTCET", LignePayeSimplifiee.RETRAITE_COMPLEMENTAIRE_TA.getId());
+                    liaison.put("COTCET", LignePayeSimplifiee.RETRAITE_COMPLEMENTAIRE_T1.getId());
                     liaison.put("COTPREVCADRETA", LignePayeSimplifiee.SANTE_COMPLEMENAIRE_DECES.getId());
-                    liaison.put("COTGMP", LignePayeSimplifiee.RETRAITE_COMPLEMENTAIRE_GMP.getId());
+                    liaison.put("COTGMP", LignePayeSimplifiee.RETRAITE_COMPLEMENTAIRE_GMP_2019_REMOVE.getId());
                     liaison.put("COTCRDSIMP", LignePayeSimplifiee.CSG_IMP.getId());
-                    liaison.put("COTGMP2", LignePayeSimplifiee.RETRAITE_COMPLEMENTAIRE_GMP.getId());
+                    liaison.put("COTGMP2", LignePayeSimplifiee.RETRAITE_COMPLEMENTAIRE_GMP_2019_REMOVE.getId());
                     liaison.put("TAXEAPPR", LignePayeSimplifiee.AUTRES_CONTRIBUTIONS.getId());
                     liaison.put("CDA", LignePayeSimplifiee.AUTRES_CONTRIBUTIONS.getId());
                     liaison.put("FOR", LignePayeSimplifiee.AUTRES_CONTRIBUTIONS.getId());
                     liaison.put("COTAPECTA", LignePayeSimplifiee.ASSURANCE_CHOMAGE_APEC.getId());
                     liaison.put("COTPREV", LignePayeSimplifiee.RETRAITE_SUPPLEMENTAIRE.getId());
                     liaison.put("COT_MUTUELLE", LignePayeSimplifiee.SANTE_COMPLEMENAIRE_SANTE.getId());
                     liaison.put("COT_PENIBLE", LignePayeSimplifiee.AUTRES_CONTRIBUTIONS.getId());
                     SQLSelect sel = new SQLSelect();
-                    sel.addSelect(conf.getRoot().findTable("RUBRIQUE_COTISATION").getField("CODE"));
+                    sel.addSelect(rootCommon.findTable("RUBRIQUE_COTISATION").getField("CODE"));
-                    sel.addSelect(conf.getRoot().findTable("RUBRIQUE_COTISATION").getField("NOM"));
+                    sel.addSelect(rootCommon.findTable("RUBRIQUE_COTISATION").getField("NOM"));
-                    sel.addSelect(conf.getRoot().findTable("RUBRIQUE_COTISATION").getKey());
+                    sel.addSelect(rootCommon.findTable("RUBRIQUE_COTISATION").getKey());
                     List<SQLRow> rubCotis = SQLRowListRSH.execute(sel);
                     for (SQLRow sqlRow : rubCotis) {
                         final String code = sqlRow.getString("CODE");
                         if (liaison.containsKey(code)) {
+                        }
+                    }
+                }
+            }
-            final SQLTable tableRnet = conf.getRoot().getTable("RUBRIQUE_NET");
+            final SQLTable tableRnet = rootCommon.getTable("RUBRIQUE_NET");
             if (!tableRnet.contains("NUMERO_COMPTE_PCE_CHARGES")) {
                 final AlterTable alter = new AlterTable(tableRnet);
                 alter.addVarCharColumn("NUMERO_COMPTE_PCE_CHARGES", 128);
                 final String req = alter.asString();
-                conf.getRoot().getDBSystemRoot().getDataSource().execute(req);
+                rootCommon.getDBSystemRoot().getDataSource().execute(req);
-                conf.getRoot().refetchTable(tableRnet.getName());
+                rootCommon.refetchTable(tableRnet.getName());
-                conf.getRoot().getSchema().updateVersion();
+                rootCommon.getSchema().updateVersion();
+            }
             if (!tableRC.contains("PART_PAT_IMPOSABLE")) {
                 final AlterTable alter = new AlterTable(tableRC);
                 alter.addBooleanColumn("PART_PAT_IMPOSABLE", Boolean.FALSE, false);
                 final String req = alter.asString();
-                conf.getRoot().getDBSystemRoot().getDataSource().execute(req);
+                rootCommon.getDBSystemRoot().getDataSource().execute(req);
-                conf.getRoot().refetchTable(tableRC.getName());
+                rootCommon.refetchTable(tableRC.getName());
-                conf.getRoot().getSchema().updateVersion();
+                rootCommon.getSchema().updateVersion();
+            }
             if (!tableRC.contains("REDUCTION_FILLON")) {
                 final AlterTable alter = new AlterTable(tableRC);
                 alter.addBooleanColumn("REDUCTION_FILLON", Boolean.FALSE, false);
                 final String req = alter.asString();
-                conf.getRoot().getDBSystemRoot().getDataSource().execute(req);
+                rootCommon.getDBSystemRoot().getDataSource().execute(req);
-                conf.getRoot().refetchTable(tableRC.getName());
+                rootCommon.refetchTable(tableRC.getName());
-                conf.getRoot().getSchema().updateVersion();
+                rootCommon.getSchema().updateVersion();
+            }
             // Vérification des droits existants
-            checkRights(conf.getRoot());
+            checkRights(rootCommon);
-            if (!conf.getRoot().contains("DEVISE")) {
+            if (!rootCommon.contains("DEVISE")) {
                 System.out.println("InstallationPanel.InstallationPanel() ADD DEVISE");
                 try {
                     SQLUtils.executeAtomic(ds, new SQLUtils.SQLFactory<Object>() {
                         @Override
                         public Object create() throws SQLException {
-                            final SQLCreateTable createDevise = new SQLCreateTable(conf.getRoot(), "DEVISE");
+                            final SQLCreateTable createDevise = new SQLCreateTable(rootCommon, "DEVISE");
                             createDevise.addVarCharColumn("CODE", 128);
                             createDevise.addVarCharColumn("NOM", 128);
                             createDevise.addVarCharColumn("LIBELLE", 128);
                             createDevise.addVarCharColumn("LIBELLE_CENT", 128);
                             createDevise.addDecimalColumn("TAUX", 16, 8, BigDecimal.ONE, false);
                             createDevise.addDecimalColumn("TAUX_COMMERCIAL", 16, 8, BigDecimal.ONE, false);
                             ds.execute(createDevise.asString());
                             insertUndef(createDevise);
-                            conf.getRoot().getSchema().updateVersion();
+                            rootCommon.getSchema().updateVersion();
                             return null;
+                        }
                     });
                 } catch (Exception ex) {
                     throw new IllegalStateException("Erreur lors de la création de la table DEVISE", ex);
+                }
-            } else if (!conf.getRoot().getTable("DEVISE").contains("TAUX_COMMERCIAL")) {
+            } else if (!rootCommon.getTable("DEVISE").contains("TAUX_COMMERCIAL")) {
-                final SQLTable tDevise = conf.getRoot().getTable("DEVISE");
+                final SQLTable tDevise = rootCommon.getTable("DEVISE");
                 final AlterTable alterDevise = new AlterTable(tDevise);
                 alterDevise.addDecimalColumn("TAUX_COMMERCIAL", 16, 8, BigDecimal.ONE, false);
                 ds.execute(alterDevise.asString());
                 tDevise.getSchema().updateVersion();
+            }
-            if (!conf.getRoot().contains("TYPE_MODELE")) {
+            if (!rootCommon.contains("TYPE_MODELE")) {
                 System.out.println("InstallationPanel.InstallationPanel() ADD TYPE_MODELE");
                 try {
                     SQLUtils.executeAtomic(ds, new SQLUtils.SQLFactory<Object>() {
                         @Override
                         public Object create() throws SQLException {
-                            final SQLCreateTable createTypeModele = new SQLCreateTable(conf.getRoot(), "TYPE_MODELE");
+                            final SQLCreateTable createTypeModele = new SQLCreateTable(rootCommon, "TYPE_MODELE");
                             createTypeModele.addVarCharColumn("NOM", 128);
                             createTypeModele.addVarCharColumn("TABLE", 128);
                             createTypeModele.addVarCharColumn("DEFAULT_MODELE", 128);
                             ds.execute(createTypeModele.asString());
                             insertUndef(createTypeModele);
-                            conf.getRoot().getSchema().updateVersion();
+                            rootCommon.getSchema().updateVersion();
-                            conf.getRoot().refetch();
+                            rootCommon.refetch();
                             return null;
+                        }
                     });
                     final String[] type = new String[] { "Avoir client", "AVOIR_CLIENT", "Avoir", "Bon de livraison", "BON_DE_LIVRAISON", "BonLivraison", "Commande Client", "COMMANDE_CLIENT",
                             "CommandeClient", "Devis", "DEVIS", "Devis", "Facture", "SAISIE_VENTE_FACTURE", "VenteFacture" };
                     // ('FR', 'Français', 1.000), ('EN',
                     // 'Anglais', 2.000)
                     final List<String> values = new ArrayList<String>();
-                    final SQLBase base = conf.getRoot().getBase();
+                    final SQLBase base = rootCommon.getBase();
                     for (int i = 0; i < type.length; i += 3) {
                         final int order = values.size() + 1;
                         values.add("(" + base.quoteString(type[i]) + ", " + base.quoteString(type[i + 1]) + ", " + base.quoteString(type[i + 2]) + ", " + order + ")");
+                    }
                     final String valuesStr = CollectionUtils.join(values, ", ");
-                    final String insertVals = "INSERT INTO " + conf.getRoot().getTable("TYPE_MODELE").getSQLName().quote() + "(" + SQLBase.quoteIdentifier("NOM") + ", "
+                    final String insertVals = "INSERT INTO " + rootCommon.getTable("TYPE_MODELE").getSQLName().quote() + "(" + SQLBase.quoteIdentifier("NOM") + ", " + SQLBase.quoteIdentifier("TABLE")
-                            + SQLBase.quoteIdentifier("TABLE") + ", " + SQLBase.quoteIdentifier("DEFAULT_MODELE") + ", " + SQLBase.quoteIdentifier(SQLSyntax.ORDER_NAME) + ") VALUES" + valuesStr;
+                            + ", " + SQLBase.quoteIdentifier("DEFAULT_MODELE") + ", " + SQLBase.quoteIdentifier(SQLSyntax.ORDER_NAME) + ") VALUES" + valuesStr;
                     ds.execute(insertVals);
                 } catch (Exception ex) {
                     throw new IllegalStateException("Erreur lors de la création de la table TYPE_MODELE", ex);
+                }
+            }
-            SQLTable.setUndefID(conf.getRoot().getSchema(), "DEVISE", 1);
+            SQLTable.setUndefID(rootCommon.getSchema(), "DEVISE", 1);
-            SQLTable.setUndefID(conf.getRoot().getSchema(), "TYPE_MODELE", 1);
+            SQLTable.setUndefID(rootCommon.getSchema(), "TYPE_MODELE", 1);
             // DSN
             final DSNInstallationUtils dsnUtils = new DSNInstallationUtils();
-            dsnUtils.updateDSNCommonTable(conf.getRoot());
+            dsnUtils.updateDSNCommonTable(rootCommon);
+            SQLRow rowRubNetPAS = createPasCommon(rootCommon);
             // ECo contribution
             final EcoContributionInstallationUtils ecoUtils = new EcoContributionInstallationUtils();
-            ecoUtils.updateEcoCommonTable(conf.getRoot());
+            ecoUtils.updateEcoCommonTable(rootCommon);
             // we need to upgrade all roots
             // ///////////////////////////
-            conf.getSystemRoot().mapAllRoots();
+            systemRoot.mapAllRoots();
-            conf.getSystemRoot().refetch();
+            systemRoot.refetch();
-            final Set<String> childrenNames = conf.getSystemRoot().getChildrenNames();
+            final Set<String> childrenNames = systemRoot.getChildrenNames();
             SwingUtilities.invokeLater(new Runnable() {
                 @Override
                 public void run() {
                     public void run() {
                         bar.setValue(barValue);
+                    }
                 });
                 i++;
-                final DBRoot root = conf.getSystemRoot().getRoot(childName);
+                final DBRoot root = systemRoot.getRoot(childName);
                 try {
-                    conf.getSystemRoot().getDataSource().execute("CREATE LANGUAGE plpgsql;");
+                    systemRoot.getDataSource().execute("CREATE LANGUAGE plpgsql;");
                 } catch (Exception e) {
                     System.err.println("Warning: cannot add language plpgsql" + e.getMessage());
+                }
                 final SQLTable tableUndef = root.getTable(SQLTable.undefTable);
                 if (tableUndef != null && tableUndef.getField("UNDEFINED_ID").isNullable() == Boolean.FALSE) {
                 } else if (childName.startsWith(conf.getAppName()) || childName.equalsIgnoreCase("Default")
                 ) {
                     SQLUtils.executeAtomic(ds, new SQLUtils.SQLFactory<Object>() {
                         @Override
                         public Object create() throws SQLException {
-                            fixUnboundedVarchar(root);
                             fixUnboundedNumeric(root);
                             fixCompletion(root);
                             try {
-                                updateSocieteSchema(root);
+                                updateSocieteSchema(conf, root);
                             } catch (Exception e) {
                                 throw new SQLException(e);
+                            }
                             updateToV1Dot2(root);
                             updateToV1Dot3(root);
                             createAssocAnalytique(root, conf);
                             updateStock(root);
                             updateVille(root.getTable("ADRESSE"));
                             Updater_1_5.update(root);
                             SQLTable tableFpaye = root.getTable("FICHE_PAYE");
                             if (!tableFpaye.contains("REDUCTION_GVT")) {
                                 final AlterTable alterB = new AlterTable(tableFpaye);
                                 alterB.addDecimalColumn("REDUCTION_GVT", 16, 2, BigDecimal.ZERO, false);
                                 root.getBase().getDataSource().execute(alterB.asString());
                                         rowValsRubGvt.commit();
+                                    }
                                     pos++;
+                                }
+                            }
+                            if (rowRubNetPAS != null) {
+                                SQLSelect sel = new SQLSelect();
+                                sel.addSelect(root.getTable("SALARIE").getKey());
+                                sel.addSelect(root.getTable("SALARIE").getField("ID_FICHE_PAYE"));
+                                List<SQLRow> rowsSal = SQLRowListRSH.execute(sel);
+                                int pos = 80;
+                                for (SQLRow rowSal : rowsSal) {
+                                    SQLRowValues rowValsRubPas = new SQLRowValues(root.getTable("FICHE_PAYE_ELEMENT"));
+                                    rowValsRubPas.put("ID_FICHE_PAYE", rowSal.getForeignID("ID_FICHE_PAYE"));
+                                    rowValsRubPas.put("POSITION", pos);
+                                    rowValsRubPas.put("IDSOURCE", rowRubNetPAS.getID());
+                                    rowValsRubPas.put("SOURCE", rowRubNetPAS.getTable().getName());
+                                    rowValsRubPas.put("NOM", rowRubNetPAS.getString("NOM"));
+                                    rowValsRubPas.put("IN_PERIODE", Boolean.TRUE);
+                                    rowValsRubPas.put("IMPRESSION", Boolean.FALSE);
+                                    rowValsRubPas.put("VALIDE", Boolean.FALSE);
+                                    rowValsRubPas.commit();
+                                }
+                            }
+                            // at the end to let Updater_1_5 change field types from varchar to text
+                            fixUnboundedVarchar(root);
                             return null;
+                        }
                     });
             ExceptionHandler.handle("Echec de mise à jour", e1);
             error = true;
+        }
+    }
+    public SQLRow createPasCommon(DBRoot root) throws SQLException {
+        SQLRow rowRub = null;
+        SQLTable tableRubNet = root.getTable("RUBRIQUE_NET");
+        SQLTable caisseCot = root.getTable("CAISSE_COTISATION");
+        if (!tableRubNet.contains("PAS")) {
+            AlterTable alterTableCaisseCot = new AlterTable(caisseCot);
+            alterTableCaisseCot.addBooleanColumn("DGFIP", Boolean.FALSE, true);
+            root.getBase().getDataSource().execute(alterTableCaisseCot.asString());
+            root.refetchTable("CAISSE_COTISATION");
+            root.getSchema().updateVersion();
+            SQLRowValues rowValsCaisse = new SQLRowValues(caisseCot);
+            rowValsCaisse.put("ORG_PROTECTION_SOCIALE", Boolean.TRUE);
+            rowValsCaisse.put("DGFIP", Boolean.TRUE);
+            rowValsCaisse.put("NOM", "DGFIP");
+            SQLRow rowCaisseDGFIP = rowValsCaisse.commit();
+            AlterTable alterTableNet = new AlterTable(tableRubNet);
+            alterTableNet.addBooleanColumn("PAS", Boolean.FALSE, true);
+            alterTableNet.addBooleanColumn("PAS_CDD", Boolean.FALSE, true);
+            root.getBase().getDataSource().execute(alterTableNet.asString());
+            root.refetchTable("RUBRIQUE_NET");
+            root.getSchema().updateVersion();
+            SQLRowValues rowValsPASCDD = new SQLRowValues(tableRubNet);
+            rowValsPASCDD.put("ID_CAISSE_COTISATION", rowCaisseDGFIP.getID());
+            rowValsPASCDD.put("TAUX", "TAUX_PAS;");
+            rowValsPASCDD.put("BASE", "Math.max(0.0D,(Math.round(NET_IMP*100.0)/100.0)-597);");
+            rowValsPASCDD.put("NOM", "Prélévement à la source CDD moins de 2 mois ");
+            rowValsPASCDD.put("CODE", "PAS_NET_CDD");
+            rowValsPASCDD.put("MONTANT", "Math.round(Math.max(0.0D,((Math.round(NET_IMP*100.0)/100.0)-597)) * TAUX_PAS)/100.0;");
+            rowValsPASCDD.put("ID_IMPRESSION_RUBRIQUE", 4);
+            rowValsPASCDD.put("ID_TYPE_RUBRIQUE_NET", 3);
+            rowValsPASCDD.put("PAS_CDD", Boolean.TRUE);
+            rowValsPASCDD.putRowValues("ID_PERIODE_VALIDITE").put("JANVIER", Boolean.TRUE);
+            rowValsPASCDD.commit();
+            SQLRowValues rowValsPAS = new SQLRowValues(tableRubNet);
+            rowValsPAS.put("ID_CAISSE_COTISATION", rowCaisseDGFIP.getID());
+            rowValsPAS.put("TAUX", "TAUX_PAS;");
+            rowValsPAS.put("BASE", "Math.round(NET_IMP*100.0)/100.0;");
+            rowValsPAS.put("NOM", "Prélévement à la source");
+            rowValsPAS.put("CODE", "PAS_NET");
+            rowValsPAS.put("MONTANT", "Math.round((Math.round(NET_IMP*100.0)/100.0) * TAUX_PAS)/100.0;");
+            rowValsPAS.put("ID_IMPRESSION_RUBRIQUE", 4);
+            rowValsPAS.put("ID_TYPE_RUBRIQUE_NET", 3);
+            rowValsPAS.put("PAS", Boolean.TRUE);
+            rowValsPAS.putRowValues("ID_PERIODE_VALIDITE").put("JANVIER", Boolean.TRUE);
+            rowRub = rowValsPAS.commit();
+            SQLSelect sel = new SQLSelect();
+            sel.addSelect(root.findTable("PROFIL_PAYE").getKey());
+            List<SQLRow> rowsProfil = SQLRowListRSH.execute(sel);
+            int pos = 80;
+            for (SQLRow rowProfil : rowsProfil) {
+                SQLRowValues rowValsRubGvt = new SQLRowValues(root.findTable("PROFIL_PAYE_ELEMENT"));
+                rowValsRubGvt.put("ID_PROFIL_PAYE", rowProfil.getID());
+                rowValsRubGvt.put("POSITION", pos);
+                rowValsRubGvt.put("IDSOURCE", rowRub.getID());
+                rowValsRubGvt.put("SOURCE", rowRub.getTable().getName());
+                rowValsRubGvt.put("NOM", rowRub.getString("NOM"));
+                rowValsRubGvt.commit();
+            }
+        }
+        return rowRub;
+    }
+}

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

svn://code.openconcerto.org/openconcerto

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