OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

Rev 83 | Rev 93 | Go to most recent revision | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
18 ilm 1
/*
2
 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
3
 *
4
 * Copyright 2011 OpenConcerto, by ILM Informatique. All rights reserved.
5
 *
6
 * The contents of this file are subject to the terms of the GNU General Public License Version 3
7
 * only ("GPL"). You may not use this file except in compliance with the License. You can obtain a
8
 * copy of the License at http://www.gnu.org/licenses/gpl-3.0.html See the License for the specific
9
 * language governing permissions and limitations under the License.
10
 *
11
 * When distributing the software, include this License Header Notice in each file.
12
 */
13
 
14
 package org.openconcerto.erp.config;
15
 
67 ilm 16
import org.openconcerto.erp.core.sales.quote.element.EtatDevisSQLElement;
61 ilm 17
import org.openconcerto.erp.modules.ModuleManager;
67 ilm 18
import org.openconcerto.erp.modules.ModuleReference;
19 ilm 19
import org.openconcerto.sql.changer.convert.AddFK;
83 ilm 20
import org.openconcerto.sql.changer.convert.ChangeIDToInt;
19 ilm 21
import org.openconcerto.sql.changer.correct.CorrectOrder;
18 ilm 22
import org.openconcerto.sql.changer.correct.FixSerial;
73 ilm 23
import org.openconcerto.sql.model.AliasedTable;
19 ilm 24
import org.openconcerto.sql.model.DBRoot;
25
import org.openconcerto.sql.model.DBSystemRoot;
26
import org.openconcerto.sql.model.SQLBase;
27
import org.openconcerto.sql.model.SQLDataSource;
20 ilm 28
import org.openconcerto.sql.model.SQLField;
83 ilm 29
import org.openconcerto.sql.model.SQLField.Properties;
73 ilm 30
import org.openconcerto.sql.model.SQLInjector;
19 ilm 31
import org.openconcerto.sql.model.SQLName;
18 ilm 32
import org.openconcerto.sql.model.SQLRow;
33
import org.openconcerto.sql.model.SQLRowListRSH;
34
import org.openconcerto.sql.model.SQLRowValues;
73 ilm 35
import org.openconcerto.sql.model.SQLRowValuesListFetcher;
80 ilm 36
import org.openconcerto.sql.model.SQLSchema;
18 ilm 37
import org.openconcerto.sql.model.SQLSelect;
19 ilm 38
import org.openconcerto.sql.model.SQLSyntax;
39
import org.openconcerto.sql.model.SQLSystem;
18 ilm 40
import org.openconcerto.sql.model.SQLTable;
41
import org.openconcerto.sql.model.Where;
57 ilm 42
import org.openconcerto.sql.model.graph.SQLKey;
80 ilm 43
import org.openconcerto.sql.request.Inserter;
83 ilm 44
import org.openconcerto.sql.request.Inserter.Insertion;
81 ilm 45
import org.openconcerto.sql.request.UpdateBuilder;
80 ilm 46
import org.openconcerto.sql.sqlobject.SQLTextCombo;
18 ilm 47
import org.openconcerto.sql.utils.AlterTable;
19 ilm 48
import org.openconcerto.sql.utils.ChangeTable;
49
import org.openconcerto.sql.utils.ReOrder;
80 ilm 50
import org.openconcerto.sql.utils.SQLCreateMoveableTable;
19 ilm 51
import org.openconcerto.sql.utils.SQLCreateTable;
52
import org.openconcerto.sql.utils.SQLUtils;
80 ilm 53
import org.openconcerto.sql.utils.SQLUtils.SQLFactory;
18 ilm 54
import org.openconcerto.ui.DefaultGridBagConstraints;
55
import org.openconcerto.ui.JLabelBold;
19 ilm 56
import org.openconcerto.utils.CollectionUtils;
18 ilm 57
import org.openconcerto.utils.ExceptionHandler;
73 ilm 58
import org.openconcerto.utils.ProductInfo;
59
import org.openconcerto.utils.cc.ITransformer;
18 ilm 60
 
61
import java.awt.GridBagConstraints;
62
import java.awt.GridBagLayout;
63
import java.awt.Insets;
64
import java.awt.event.ActionEvent;
65
import java.awt.event.ActionListener;
61 ilm 66
import java.io.IOException;
67 ilm 67
import java.math.BigDecimal;
18 ilm 68
import java.sql.SQLException;
20 ilm 69
import java.sql.Types;
19 ilm 70
import java.util.ArrayList;
57 ilm 71
import java.util.Arrays;
80 ilm 72
import java.util.Collection;
73
import java.util.Collections;
19 ilm 74
import java.util.EnumSet;
57 ilm 75
import java.util.HashMap;
76
import java.util.HashSet;
18 ilm 77
import java.util.List;
57 ilm 78
import java.util.Map;
18 ilm 79
import java.util.Set;
80
 
81
import javax.swing.JButton;
82
import javax.swing.JLabel;
83
import javax.swing.JOptionPane;
84
import javax.swing.JPanel;
85
import javax.swing.JProgressBar;
86
import javax.swing.JTextField;
87
import javax.swing.SwingUtilities;
88
 
89
public class InstallationPanel extends JPanel {
57 ilm 90
    private static final boolean DEBUG_FK = false;
18 ilm 91
 
80 ilm 92
    static private void insertUndef(final SQLCreateTable ct) throws SQLException {
93
        // check that we can use insertReturnFirstField()
94
        if (ct.getPrimaryKey().size() != 1)
95
            throw new IllegalStateException("Not one and only one field in the PK : " + ct.getPrimaryKey());
96
        final Insertion<?> insertion = new Inserter(ct).insertReturnFirstField("(" + SQLBase.quoteIdentifier(SQLSyntax.ORDER_NAME) + ") VALUES(" + ReOrder.MIN_ORDER + ")", false);
97
        assert insertion.getCount() == 1;
98
        if (insertion.getRows().size() != 1)
99
            throw new IllegalStateException("Missing ID " + insertion.getRows());
100
        SQLTable.setUndefID(ct.getRoot().getSchema(), ct.getName(), ((Number) insertion.getRows().get(0)).intValue());
19 ilm 101
    }
102
 
103
    static private SQLName getTableName(final SQLCreateTable ct) {
104
        return new SQLName(ct.getRoot().getName(), ct.getName());
105
    }
106
 
18 ilm 107
    JProgressBar bar = new JProgressBar();
19 ilm 108
    boolean error;
18 ilm 109
 
110
    public InstallationPanel(final ServerFinderPanel finderPanel) {
111
        super(new GridBagLayout());
112
        setOpaque(false);
113
        GridBagConstraints c = new DefaultGridBagConstraints();
114
        JButton user = new JButton("Créer l'utilisateur");
57 ilm 115
        user.setOpaque(false);
18 ilm 116
        // JButton bd = new JButton("Créer la base de données");
117
        final JButton up = new JButton("Mise à niveau de la base");
57 ilm 118
        up.setOpaque(false);
18 ilm 119
        up.addActionListener(new ActionListener() {
120
 
121
            @Override
122
            public void actionPerformed(ActionEvent e) {
19 ilm 123
                finderPanel.saveConfigFile();
18 ilm 124
                bar.setIndeterminate(true);
125
                up.setEnabled(false);
126
                new Thread(new Runnable() {
127
 
128
                    @Override
129
                    public void run() {
80 ilm 130
                        System.setProperty(SQLSchema.NOAUTO_CREATE_METADATA, "false");
19 ilm 131
                        final ComptaPropsConfiguration conf = ComptaPropsConfiguration.create(true);
18 ilm 132
 
133
                        try {
19 ilm 134
                            final SQLDataSource ds = conf.getSystemRoot().getDataSource();
135
                            System.err.println("SystemRoot:" + conf.getSystemRoot());
136
                            System.err.println("Root:" + conf.getRoot());
137
 
21 ilm 138
                            // FixUnbounded varchar
139
                            fixUnboundedVarchar(conf.getRoot());
140
 
67 ilm 141
                            // FIXME DROP CONSTRAINT UNIQUE ORDRE ON
142
                            // CONTACT_FOURNISSEUR
41 ilm 143
 
19 ilm 144
                            // Mise à jour des taux
145
                            final SQLTable table = conf.getRoot().getTable("VARIABLE_PAYE");
146
                            System.out.println("InstallationPanel.InstallationPanel() UPDATE PAYE");
18 ilm 147
                            updateVariablePaye(table, "SMIC", 9);
148
                            updateVariablePaye(table, "TRANCHE_A", 2946);
149
                            updateVariablePaye(table, "PART_SAL_GarantieMP", 23.83);
150
                            updateVariablePaye(table, "PART_PAT_GarantieMP", 38.98);
151
 
61 ilm 152
                            updateSocieteTable(conf.getRoot());
73 ilm 153
                            updateVille(conf.getRoot().getTable("ADRESSE"));
61 ilm 154
 
73 ilm 155
                            // Vérification des droits existants
156
                            checkRights(conf.getRoot());
157
 
19 ilm 158
                            if (!table.getDBRoot().contains("DEVISE")) {
159
                                System.out.println("InstallationPanel.InstallationPanel() ADD DEVISE");
160
                                try {
161
                                    SQLUtils.executeAtomic(ds, new SQLUtils.SQLFactory<Object>() {
162
                                        @Override
163
                                        public Object create() throws SQLException {
164
                                            final SQLCreateTable createDevise = new SQLCreateTable(table.getDBRoot(), "DEVISE");
165
                                            createDevise.addVarCharColumn("CODE", 128);
166
                                            createDevise.addVarCharColumn("NOM", 128);
167
                                            createDevise.addVarCharColumn("LIBELLE", 128);
168
                                            createDevise.addVarCharColumn("LIBELLE_CENT", 128);
169
                                            createDevise.addColumn("TAUX", "numeric(16,8) default 1");
170
                                            ds.execute(createDevise.asString());
171
 
172
                                            insertUndef(createDevise);
173
 
174
                                            conf.getRoot().getSchema().updateVersion();
175
 
176
                                            return null;
177
                                        }
178
                                    });
179
                                } catch (Exception ex) {
180
                                    throw new IllegalStateException("Erreur lors de la création de la table DEVISE", ex);
181
                                }
182
                            }
183
 
21 ilm 184
                            if (!table.getDBRoot().contains("TYPE_MODELE")) {
185
                                System.out.println("InstallationPanel.InstallationPanel() ADD TYPE_MODELE");
186
                                try {
187
                                    SQLUtils.executeAtomic(ds, new SQLUtils.SQLFactory<Object>() {
188
                                        @Override
189
                                        public Object create() throws SQLException {
190
                                            final SQLCreateTable createTypeModele = new SQLCreateTable(table.getDBRoot(), "TYPE_MODELE");
191
                                            createTypeModele.addVarCharColumn("NOM", 128);
192
                                            createTypeModele.addVarCharColumn("TABLE", 128);
193
                                            createTypeModele.addVarCharColumn("DEFAULT_MODELE", 128);
194
                                            ds.execute(createTypeModele.asString());
195
 
196
                                            insertUndef(createTypeModele);
197
 
198
                                            conf.getRoot().getSchema().updateVersion();
199
 
200
                                            conf.getRoot().refetch();
201
 
202
                                            return null;
203
                                        }
204
                                    });
205
                                    final String[] type = new String[] { "Avoir client", "AVOIR_CLIENT", "Avoir", "Bon de livraison", "BON_DE_LIVRAISON", "BonLivraison", "Commande Client",
206
                                            "COMMANDE_CLIENT", "CommandeClient", "Devis", "DEVIS", "Devis", "Facture", "SAISIE_VENTE_FACTURE", "VenteFacture" };
67 ilm 207
                                    // ('FR', 'Français', 1.000), ('EN',
208
                                    // 'Anglais', 2.000)
21 ilm 209
                                    final List<String> values = new ArrayList<String>();
210
                                    final SQLBase base = table.getDBRoot().getBase();
211
 
212
                                    for (int i = 0; i < type.length; i += 3) {
213
                                        final int order = values.size() + 1;
214
                                        values.add("(" + base.quoteString(type[i]) + ", " + base.quoteString(type[i + 1]) + ", " + base.quoteString(type[i + 2]) + ", " + order + ")");
215
                                    }
216
                                    final String valuesStr = CollectionUtils.join(values, ", ");
217
                                    final String insertVals = "INSERT INTO " + conf.getRoot().getTable("TYPE_MODELE").getSQLName().quote() + "(" + SQLBase.quoteIdentifier("NOM") + ", "
218
                                            + SQLBase.quoteIdentifier("TABLE") + ", " + SQLBase.quoteIdentifier("DEFAULT_MODELE") + ", " + SQLBase.quoteIdentifier(SQLSyntax.ORDER_NAME) + ") VALUES"
219
                                            + valuesStr;
220
 
221
                                    ds.execute(insertVals);
222
                                } catch (Exception ex) {
223
                                    throw new IllegalStateException("Erreur lors de la création de la table TYPE_MODELE", ex);
224
                                }
225
                            }
226
 
67 ilm 227
                            SQLTable.setUndefID(conf.getRoot().getSchema(), "DEVISE", 1);
228
                            SQLTable.setUndefID(conf.getRoot().getSchema(), "TYPE_MODELE", 1);
41 ilm 229
 
19 ilm 230
                            // we need to upgrade all roots
57 ilm 231
                            // ///////////////////////////
65 ilm 232
                            conf.getSystemRoot().mapAllRoots();
19 ilm 233
                            conf.getSystemRoot().refetch();
18 ilm 234
 
235
                            final Set<String> childrenNames = conf.getSystemRoot().getChildrenNames();
19 ilm 236
 
18 ilm 237
                            SwingUtilities.invokeLater(new Runnable() {
238
 
239
                                @Override
240
                                public void run() {
241
                                    bar.setIndeterminate(false);
19 ilm 242
                                    bar.setMaximum(childrenNames.size() + 1);
18 ilm 243
                                }
244
                            });
245
                            int i = 1;
19 ilm 246
                            for (final String childName : childrenNames) {
247
                                System.out.println("InstallationPanel.InstallationPanel() UPDATE SCHEMA " + childName);
18 ilm 248
                                final int barValue = i;
67 ilm 249
 
18 ilm 250
                                SwingUtilities.invokeLater(new Runnable() {
251
 
252
                                    @Override
253
                                    public void run() {
254
                                        bar.setValue(barValue);
255
                                    }
256
                                });
257
                                i++;
19 ilm 258
                                final DBRoot root = conf.getSystemRoot().getRoot(childName);
61 ilm 259
                                try {
260
                                    conf.getSystemRoot().getDataSource().execute("CREATE LANGUAGE plpgsql;");
261
                                } catch (Exception e) {
262
                                    System.err.println("Warning: cannot add language plpgsql" + e.getMessage());
263
                                }
19 ilm 264
                                final SQLTable tableUndef = root.getTable(SQLTable.undefTable);
265
                                if (tableUndef != null && tableUndef.getField("UNDEFINED_ID").isNullable() == Boolean.FALSE) {
266
                                    final AlterTable alterUndef = new AlterTable(tableUndef);
267
                                    alterUndef.alterColumn("TABLENAME", EnumSet.allOf(Properties.class), "varchar(250)", "''", false);
268
                                    alterUndef.alterColumn("UNDEFINED_ID", EnumSet.allOf(Properties.class), "int", null, true);
269
                                    try {
270
                                        ds.execute(alterUndef.asString());
271
                                        tableUndef.getSchema().updateVersion();
272
                                    } catch (SQLException ex) {
273
                                        throw new IllegalStateException("Erreur lors de la modification de UNDEFINED_ID", ex);
274
                                    }
275
                                }
276
 
57 ilm 277
                                if (DEBUG_FK) {
278
                                    findBadForeignKey(root);
279
                                }
73 ilm 280
 
57 ilm 281
                                if (childName.equalsIgnoreCase("Common")) {
282
                                    updateCommon(root);
80 ilm 283
                                } else if (childName.startsWith(conf.getAppName()) || childName.equalsIgnoreCase("Default")
284
                                ) {
19 ilm 285
                                    SQLUtils.executeAtomic(ds, new SQLUtils.SQLFactory<Object>() {
286
                                        @Override
287
                                        public Object create() throws SQLException {
20 ilm 288
                                            fixUnboundedVarchar(root);
25 ilm 289
                                            fixUnboundedNumeric(root);
80 ilm 290
                                            fixCompletion(root);
61 ilm 291
                                            try {
292
                                                updateSocieteSchema(root);
293
                                            } catch (Exception e) {
294
                                                throw new SQLException(e);
295
                                            }
19 ilm 296
                                            updateToV1Dot2(root);
67 ilm 297
                                            updateToV1Dot3(root);
83 ilm 298
                                            updateToV1Dot4(root);
299
                                            updateStyle(root);
300
                                            createBanque(root);
301
                                            createAssocAnalytique(root);
302
                                            updateStock(root);
73 ilm 303
                                            updateVille(root.getTable("ADRESSE"));
19 ilm 304
                                            return null;
305
                                        }
306
                                    });
307
                                }
308
 
18 ilm 309
                            }
19 ilm 310
                            error = false;
61 ilm 311
                        } catch (Throwable e1) {
18 ilm 312
                            ExceptionHandler.handle("Echec de mise à jour", e1);
19 ilm 313
                            error = true;
18 ilm 314
                        }
315
 
316
                        conf.destroy();
317
                        SwingUtilities.invokeLater(new Runnable() {
318
 
319
                            @Override
320
                            public void run() {
321
                                up.setEnabled(true);
41 ilm 322
                                bar.setValue(bar.getMaximum());
19 ilm 323
                                if (!error) {
324
                                    JOptionPane.showMessageDialog(InstallationPanel.this, "Mise à niveau réussie");
325
                                }
18 ilm 326
                            }
327
                        });
80 ilm 328
                        System.setProperty(SQLSchema.NOAUTO_CREATE_METADATA, "true");
18 ilm 329
                    }
330
                }, "Database structure updater").start();
331
 
332
            }
333
 
334
        });
61 ilm 335
        if (finderPanel.getToken() == null) {
336
            c.weightx = 1;
337
            c.gridwidth = GridBagConstraints.REMAINDER;
338
            this.add(new JLabelBold("Création de l'utilisateur openconcerto dans la base"), c);
339
            c.gridy++;
340
            c.weightx = 1;
341
            this.add(new JLabel("Identifiant de connexion de votre base "), c);
342
            c.gridy++;
343
            c.gridwidth = 1;
344
            c.weightx = 0;
345
            this.add(new JLabel("Login"), c);
346
            c.gridx++;
18 ilm 347
 
61 ilm 348
            final JTextField login = new JTextField();
349
            c.weightx = 1;
350
            this.add(login, c);
18 ilm 351
 
61 ilm 352
            c.gridx++;
353
            c.weightx = 0;
354
            this.add(new JLabel("Mot de passe"), c);
355
            c.gridx++;
356
            final JTextField mdp = new JTextField();
357
            c.weightx = 1;
358
            this.add(mdp, c);
18 ilm 359
 
61 ilm 360
            c.gridx = 0;
361
            c.gridy++;
362
            c.weightx = 0;
363
            c.anchor = GridBagConstraints.EAST;
364
            c.gridwidth = GridBagConstraints.REMAINDER;
365
            c.fill = GridBagConstraints.NONE;
366
            this.add(user, c);
367
            c.anchor = GridBagConstraints.WEST;
368
            c.fill = GridBagConstraints.HORIZONTAL;
369
            c.gridwidth = 1;
370
            user.addActionListener(new ActionListener() {
18 ilm 371
 
61 ilm 372
                @Override
373
                public void actionPerformed(ActionEvent e) {
374
                    // TODO Auto-generated method stub
375
                    try {
376
                        if (finderPanel.getServerConfig().createUserIfNeeded(login.getText(), mdp.getText())) {
377
                            JOptionPane.showMessageDialog(InstallationPanel.this, "L'utilisateur openconcerto a été correctement ajouté.");
378
                        } else {
379
                            JOptionPane.showMessageDialog(InstallationPanel.this, "L'utilisateur openconcerto existe déjà dans la base.");
380
                        }
381
                    } catch (Exception e1) {
382
                        // TODO Auto-generated catch block
383
                        e1.printStackTrace();
384
                        JOptionPane.showMessageDialog(InstallationPanel.this, "Une erreur est survenue pendant la connexion au serveur, vérifiez vos paramètres de connexion.");
18 ilm 385
                    }
386
                }
61 ilm 387
            });
18 ilm 388
 
61 ilm 389
            // Injection SQL
390
            // c.gridy++;
391
            // c.weightx = 1;
392
            // c.gridwidth = GridBagConstraints.REMAINDER;
393
            // c.insets = new Insets(10, 3, 2, 2);
394
            // this.add(new TitledSeparator("Injecter la base", true), c);
395
            //
396
            // c.gridy++;
397
            // c.weightx = 0;
398
            // c.gridwidth = 1;
399
            // c.insets = DefaultGridBagConstraints.getDefaultInsets();
400
            // this.add(new JLabel("Fichier"), c);
401
            //
402
            // final JTextField chemin = new JTextField();
403
            // c.gridx++;
404
            // c.weightx = 1;
405
            // this.add(chemin, c);
406
            //
407
            // c.gridx++;
408
            // c.weightx = 0;
409
            // JButton browse = new JButton("...");
410
            // browse.addActionListener(new ActionListener() {
411
            //
412
            // @Override
413
            // public void actionPerformed(ActionEvent e) {
414
            // JFileChooser choose = new JFileChooser();
67 ilm 415
            // if (choose.showOpenDialog(InstallationPanel.this) ==
416
            // JFileChooser.APPROVE_OPTION) {
61 ilm 417
            // chemin.setText(choose.getSelectedFile().getAbsolutePath());
418
            // }
419
            // }
420
            // });
421
            // this.add(browse, c);
422
            //
423
            // c.gridy++;
424
            // c.gridx = 0;
425
            // JButton inject = new JButton("Injecter");
426
            // this.add(inject, c);
427
            // inject.addActionListener(new ActionListener() {
428
            //
429
            // @Override
430
            // public void actionPerformed(ActionEvent e) {
431
            // File f = new File(chemin.getText());
432
            // if (!f.exists()) {
433
            // JOptionPane.showMessageDialog(InstallationPanel.this,
434
            // "Impossible de trouver le fichier "
435
            // + chemin.getText());
436
            // return;
437
            // }
438
            // BufferedReader input = null;
439
            // try {
440
            //
441
            // input = new BufferedReader(new FileReader(f));
442
            // StringBuffer sql = new StringBuffer();
443
            // String s;
444
            // while ((s = input.readLine()) != null) {
445
            // sql.append(s + "\n");
446
            // }
447
            // input.close();
448
            //
449
            // try {
67 ilm 450
            // final SQLServer sqlServer =
451
            // finderPanel.getServerConfig().createSQLServer();
61 ilm 452
            // Number n = (Number)
453
            // sqlServer.getBase("postgres").getDataSource().executeScalar("select COUNT(*) from pg_database WHERE datname='OpenConcerto'");
454
            // if (n.intValue() > 0) {
455
            // JOptionPane.showMessageDialog(InstallationPanel.this,
456
            // "La base OpenConcerto est déjà présente sur le serveur!");
457
            // return;
458
            // }
459
            // // System.err.println(sqlServer.getBase("OpenConcerto"));
460
            // sqlServer.getBase("postgres").getDataSource()
461
            // .execute("CREATE DATABASE \"OpenConcerto\" WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'fr_FR.UTF-8' LC_CTYPE = 'fr_FR.UTF-8';");
462
            //
463
            // sqlServer.getBase("postgres").getDataSource().execute("ALTER DATABASE \"OpenConcerto\" OWNER TO openconcerto;");
464
            //
67 ilm 465
            // SQLUtils.executeScript(sql.toString(),
466
            // sqlServer.getSystemRoot("OpenConcerto"));
61 ilm 467
            // sqlServer.destroy();
468
            // JOptionPane.showMessageDialog(InstallationPanel.this,
469
            // "Création de la base OpenConerto terminée.");
470
            // System.err.println("Création de la base OpenConerto terminée.");
471
            //
472
            // } catch (SQLException e1) {
473
            // // TODO Auto-generated catch block
474
            //
475
            // e1.printStackTrace();
476
            // JOptionPane.showMessageDialog(InstallationPanel.this,
477
            // "Une erreur s'est produite pendant l'injection du script, vérifier la connexion au serveur et le script.");
478
            // }
479
            //
480
            // } catch (FileNotFoundException ex) {
481
            // // TODO Auto-generated catch block
482
            // ex.printStackTrace();
483
            // } catch (IOException ex) {
484
            // // TODO Auto-generated catch block
485
            // ex.printStackTrace();
486
            // } finally {
487
            // if (input != null) {
488
            // try {
489
            // input.close();
490
            // } catch (IOException ex) {
491
            // // TODO Auto-generated catch block
492
            // ex.printStackTrace();
493
            // }
494
            // }
495
            // }
496
            //
497
            // }
498
            // });
18 ilm 499
 
61 ilm 500
            // c.gridy++;
501
            // this.add(bd, c);
25 ilm 502
 
61 ilm 503
            c.gridy++;
504
            c.weightx = 1;
505
            c.gridwidth = GridBagConstraints.REMAINDER;
506
            c.insets = new Insets(10, 3, 2, 2);
507
            this.add(new JLabelBold("Paramètrages de la base de données"), c);
508
            c.gridy++;
509
            c.insets = DefaultGridBagConstraints.getDefaultInsets();
510
            this.add(new JLabel("Création des fonctions SQL nécessaires (plpgsql)."), c);
511
            c.gridy++;
512
            c.weightx = 0;
513
            c.anchor = GridBagConstraints.EAST;
514
            c.gridwidth = GridBagConstraints.REMAINDER;
515
            c.fill = GridBagConstraints.NONE;
57 ilm 516
 
61 ilm 517
            JButton buttonPL = new JButton("Lancer");
518
            buttonPL.setOpaque(false);
519
            buttonPL.addActionListener(new ActionListener() {
25 ilm 520
 
61 ilm 521
                @Override
522
                public void actionPerformed(ActionEvent e) {
523
                    if (!finderPanel.getServerConfig().getType().equals(ServerFinderConfig.POSTGRESQL)) {
25 ilm 524
 
61 ilm 525
                    } else {
526
                        final ComptaPropsConfiguration conf = ComptaPropsConfiguration.create(true);
527
                        try {
528
                            final SQLDataSource ds = conf.getSystemRoot().getDataSource();
529
                            // ds.execute("CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler AS '$libdir/plpgsql' LANGUAGE C;"
530
                            // + "\n"
531
                            // +
532
                            // "CREATE FUNCTION plpgsql_validator(oid) RETURNS void AS '$libdir/plpgsql' LANGUAGE C;"
533
                            // + "\n"
534
                            // +
535
                            // "CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler VALIDATOR plpgsql_validator;");
536
                            ds.execute("CREATE LANGUAGE plpgsql;");
537
 
538
                        } catch (Exception ex) {
539
                            System.err.println("Impossible d'ajouter le langage PLPGSQL. Peut etre est il déjà installé.");
540
                        }
25 ilm 541
                    }
61 ilm 542
                    JOptionPane.showMessageDialog(null, "Paramètrage terminé.");
25 ilm 543
                }
61 ilm 544
            });
545
            this.add(buttonPL, c);
546
        }
25 ilm 547
        c.gridy++;
548
        c.gridx = 0;
549
        c.weightx = 1;
550
        c.fill = GridBagConstraints.HORIZONTAL;
551
        c.anchor = GridBagConstraints.WEST;
552
        c.gridwidth = GridBagConstraints.REMAINDER;
553
        c.insets = new Insets(10, 3, 2, 2);
18 ilm 554
        this.add(new JLabelBold("Mise à niveau de la base OpenConcerto"), c);
555
        c.gridy++;
57 ilm 556
        c.insets = DefaultGridBagConstraints.getDefaultInsets();
557
        this.add(new JLabel("Cette opération est nécessaire à chaque mise à jour du logiciel."), c);
558
        c.gridy++;
559
        this.add(new JLabel("La mise à niveau peut prendre plusieurs minutes."), c);
560
        c.gridy++;
18 ilm 561
        this.add(this.bar, c);
562
        c.gridy++;
563
        c.weightx = 0;
564
        c.anchor = GridBagConstraints.EAST;
565
        c.gridwidth = GridBagConstraints.REMAINDER;
566
        c.fill = GridBagConstraints.NONE;
57 ilm 567
 
18 ilm 568
        this.add(up, c);
569
 
570
        c.anchor = GridBagConstraints.WEST;
571
        c.fill = GridBagConstraints.HORIZONTAL;
572
 
573
        c.weightx = 1;
574
        c.gridwidth = GridBagConstraints.REMAINDER;
575
        c.weighty = 1;
576
        c.gridy++;
577
        final JPanel comp = new JPanel();
578
        comp.setOpaque(false);
579
        this.add(comp, c);
580
    }
581
 
83 ilm 582
    private void addArticleFournisseur(DBRoot root) {
583
        if (!root.contains("ARTICLE_FOURNISSEUR")) {
584
 
585
            SQLCreateTable createBaseFamille = new SQLCreateTable(root, "FAMILLE_ARTICLE_FOURNISSEUR");
586
            createBaseFamille.addVarCharColumn("CODE", 45);
587
            createBaseFamille.addVarCharColumn("NOM", 2048);
588
            createBaseFamille.addForeignColumn("ID_FAMILLE_ARTICLE_FOURNISSEUR_PERE", createBaseFamille);
589
            final SQLDataSource ds = root.getDBSystemRoot().getDataSource();
590
 
591
            try {
592
                ds.execute(createBaseFamille.asString());
593
 
594
                insertUndef(createBaseFamille);
595
                root.refetchTable("FAMILLE_ARTICLE_FOURNISSEUR");
596
                root.getSchema().updateVersion();
597
            } catch (SQLException ex) {
598
                throw new IllegalStateException("Erreur lors de la création de la table FAMILLE_ARTICLE_FOURNISSEUR", ex);
599
            }
600
 
601
            SQLCreateTable createBase = new SQLCreateTable(root, "ARTICLE_FOURNISSEUR");
602
            createBase.addVarCharColumn("CODE", 45);
603
            createBase.addVarCharColumn("CODE_BARRE", 45);
604
            createBase.addVarCharColumn("CODE_DOUANIER", 45);
605
            createBase.addVarCharColumn("NOM", 2048);
606
            createBase.addVarCharColumn("DESCRIPTIF", 2048);
607
            createBase.addVarCharColumn("INFOS", 2048);
608
 
609
            createBase.addColumn("PRIX_METRIQUE_HA_1", "numeric (16,6) DEFAULT 0");
610
            createBase.addColumn("PRIX_METRIQUE_HA_2", "numeric (16,8) DEFAULT 0");
611
            createBase.addColumn("PRIX_METRIQUE_HA_3", "numeric (16,8) DEFAULT 0");
612
 
613
            createBase.addColumn("PRIX_METRIQUE_VT_1", "numeric (16,8) DEFAULT 0");
614
            createBase.addColumn("PRIX_METRIQUE_VT_2", "numeric (16,8) DEFAULT 0");
615
            createBase.addColumn("PRIX_METRIQUE_VT_3", "numeric (16,8) DEFAULT 0");
616
 
617
            createBase.addForeignColumn("ID_METRIQUE_1", root.findTable("METRIQUE", true));
618
            createBase.addForeignColumn("ID_METRIQUE_2", root.findTable("METRIQUE", true));
619
            createBase.addForeignColumn("ID_METRIQUE_3", root.findTable("METRIQUE", true));
620
 
621
            createBase.addColumn("PA_DEVISE", "numeric (16,8) DEFAULT 0");
622
            createBase.addColumn("PV_U_DEVISE", "numeric (16,8) DEFAULT 0");
623
            createBase.addColumn("PA_HT", "numeric (16,8) DEFAULT 0");
624
            createBase.addColumn("PV_HT", "numeric (16,8) DEFAULT 0");
625
            createBase.addColumn("PV_TTC", "numeric (16,2) DEFAULT 0");
626
 
627
            createBase.addForeignColumn("ID_TAXE", root.findTable("TAXE", true));
628
            createBase.addForeignColumn("ID_FAMILLE_ARTICLE_FOURNISSEUR", root.findTable("FAMILLE_ARTICLE_FOURNISSEUR", true));
629
            createBase.addForeignColumn("ID_MODE_VENTE_ARTICLE", root.findTable("MODE_VENTE_ARTICLE", true));
630
            createBase.addForeignColumn("ID_FOURNISSEUR", root.findTable("FOURNISSEUR", true));
631
            createBase.addForeignColumn("ID_PAYS", root.findTable("PAYS", true));
632
            createBase.addForeignColumn("ID_DEVISE", root.findTable("DEVISE", true));
633
            createBase.addForeignColumn("ID_DEVISE_HA", root.findTable("DEVISE", true));
634
            createBase.addForeignColumn("ID_UNITE_VENTE", root.findTable("UNITE_VENTE", true));
635
            createBase.addForeignColumn("ID_COMPTE_PCE", root.findTable("COMPTE_PCE", true));
636
            createBase.addForeignColumn("ID_COMPTE_PCE_ACHAT", root.findTable("COMPTE_PCE", true));
637
            createBase.addForeignColumn("ID_ARTICLE", root.findTable("ARTICLE", true));
638
 
639
            createBase.addColumn("POIDS", "real DEFAULT 0");
640
            createBase.addColumn("VALEUR_METRIQUE_1", "real DEFAULT 0");
641
            createBase.addColumn("VALEUR_METRIQUE_2", "real DEFAULT 0");
642
            createBase.addColumn("VALEUR_METRIQUE_3", "real DEFAULT 0");
643
            createBase.addBooleanColumn("SERVICE", Boolean.FALSE, false);
644
            createBase.addBooleanColumn("OBSOLETE", Boolean.FALSE, false);
645
            createBase.addBooleanColumn("GESTION_STOCK", Boolean.FALSE, false);
646
            createBase.addIntegerColumn("QTE_ACHAT", 1);
647
            createBase.addIntegerColumn("QTE_MIN", 1);
648
 
649
            try {
650
                ds.execute(createBase.asString());
651
 
652
                insertUndef(createBase);
653
                root.refetchTable("ARTICLE_FOURNISSEUR");
654
                root.getSchema().updateVersion();
655
            } catch (SQLException ex) {
656
                throw new IllegalStateException("Erreur lors de la création de la table ARTICLE_FOURNISSEUR", ex);
657
            }
658
        }
659
    }
660
 
661
    private void addContact(DBRoot root) throws SQLException {
662
 
663
        List<String> tables = Arrays.asList("AVOIR_CLIENT", "DEVIS", "BON_DE_LIVRAISON", "COMMANDE_CLIENT", "SAISIE_VENTE_FACTURE");
664
        for (String tableName : tables) {
665
 
666
            final SQLTable table = root.getTable(tableName);
667
            final SQLTable tableContact = root.findTable("CONTACT");
668
            if (!table.contains("ID_CONTACT")) {
669
 
670
                final SQLDataSource dataSource = root.getDBSystemRoot().getDataSource();
671
                final AlterTable alterEcheance = new AlterTable(table);
672
                alterEcheance.addForeignColumn("ID_CONTACT", tableContact);
673
                dataSource.execute(alterEcheance.asString());
674
                table.getSchema().updateVersion();
675
            }
676
        }
677
    }
678
 
73 ilm 679
    private void addForeignKeyFactureOnEcheance(DBRoot root) {
680
 
681
        final SQLTable tableEch = root.getTable("ECHEANCE_CLIENT");
682
        if (!tableEch.contains("ID_SAISIE_VENTE_FACTURE")) {
683
            final SQLTable tableFacture = root.getTable("SAISIE_VENTE_FACTURE");
684
            final SQLTable tableMvt = root.getTable("MOUVEMENT");
685
            String query = "no query";
686
            try {
687
                final SQLDataSource dataSource = root.getDBSystemRoot().getDataSource();
688
                final AlterTable alterEcheance = new AlterTable(tableEch);
689
                alterEcheance.addForeignColumn("ID_SAISIE_VENTE_FACTURE", tableFacture);
690
                dataSource.execute(alterEcheance.asString());
691
                tableEch.getSchema().updateVersion();
692
                tableEch.fetchFields();
81 ilm 693
 
694
                // select MOUVEMENT whose parent has a source FACTURE
695
                final SQLSelect selMvt = new SQLSelect();
696
                final AliasedTable refChild = new AliasedTable(tableMvt, "m1");
697
                final AliasedTable refParent = new AliasedTable(tableMvt, "m2");
698
                selMvt.addSelect(refParent.getField("IDSOURCE"));
699
                selMvt.addBackwardJoin("INNER", refChild.getField("ID_MOUVEMENT_PERE"), refParent.getAlias());
700
                selMvt.addSelect(refChild.getKey());
701
                selMvt.setWhere(new Where(refParent.getField("SOURCE"), "=", tableFacture.getName()));
702
 
703
                final UpdateBuilder build = new UpdateBuilder(tableEch);
704
                build.addVirtualJoin("( " + selMvt.asString() + " )", "mvt", false, tableMvt.getKey().getName(), "ID_MOUVEMENT");
705
                build.setFromVirtualJoinField("ID_SAISIE_VENTE_FACTURE", "mvt", "IDSOURCE");
706
                query = build.asString();
707
                dataSource.execute(query);
73 ilm 708
            } catch (SQLException ex) {
709
                Log.get().severe("Error on query :" + query);
710
                throw new IllegalStateException("Erreur lors de l'ajout des champs sur la table ECHEANCE_CLIENT", ex);
711
            }
712
        }
713
    }
714
 
80 ilm 715
    private void addFieldForPartialInvoice(DBRoot root) throws SQLException {
716
        final SQLTable tableVF = root.getTable("SAISIE_VENTE_FACTURE");
717
        AlterTable alter = new AlterTable(tableVF);
718
        boolean doAlter = false;
719
        if (!tableVF.contains("POURCENT_FACTURABLE")) {
720
            alter.addColumn("POURCENT_FACTURABLE", "numeric (16,8)");
721
            doAlter = true;
722
        }
723
        if (!tableVF.contains("MONTANT_FACTURABLE")) {
724
            alter.addColumn("MONTANT_FACTURABLE", "numeric (16,8)");
725
            doAlter = true;
726
        }
727
 
728
        if (!tableVF.contains("SOLDE")) {
729
            alter.addColumn("SOLDE", "boolean DEFAULT false");
730
            doAlter = true;
731
        }
732
 
733
        if (!tableVF.contains("PARTIAL")) {
734
            alter.addColumn("PARTIAL", "boolean DEFAULT false");
735
            doAlter = true;
736
        }
737
 
738
        if (doAlter) {
739
            root.getDBSystemRoot().getDataSource().execute(alter.asString());
740
            root.refetchTable(tableVF.getName());
741
        }
742
 
743
        // ELT
744
        final SQLTable tableVFElt = root.getTable("SAISIE_VENTE_FACTURE_ELEMENT");
745
        AlterTable alterElt = new AlterTable(tableVFElt);
746
        boolean doAlterElt = false;
747
        if (!tableVFElt.contains("MONTANT_FACTURABLE")) {
748
            alterElt.addColumn("MONTANT_FACTURABLE", "numeric (16,8)");
749
            doAlterElt = true;
750
        }
751
 
752
        if (!tableVFElt.contains("POURCENT_FACTURABLE")) {
753
            alterElt.addColumn("POURCENT_FACTURABLE", "numeric (16,8)");
754
            doAlterElt = true;
755
        }
756
 
757
        if (doAlterElt) {
758
            root.getDBSystemRoot().getDataSource().execute(alterElt.asString());
759
            root.refetchTable(tableVFElt.getName());
760
        }
761
 
762
    }
763
 
83 ilm 764
    private void createAssocAnalytique(DBRoot root) {
765
 
766
        if (!root.contains("ASSOCIATION_ANALYTIQUE")) {
767
 
768
            SQLCreateTable createAssoc = new SQLCreateTable(root, "ASSOCIATION_ANALYTIQUE");
769
            createAssoc.addForeignColumn("ID_ECRITURE", root.findTable("ECRITURE", true));
770
            createAssoc.addForeignColumn("ID_SAISIE_KM_ELEMENT", root.findTable("SAISIE_KM_ELEMENT", true));
771
            createAssoc.addForeignColumn("ID_POSTE_ANALYTIQUE", root.findTable("POSTE_ANALYTIQUE", true));
772
            createAssoc.addColumn("POURCENT", "numeric (16,8) DEFAULT 100");
773
            createAssoc.addColumn("MONTANT", "bigInt DEFAULT 0");
774
            createAssoc.addBooleanColumn("GESTION_AUTO", false, false);
775
 
776
            final SQLDataSource ds = root.getDBSystemRoot().getDataSource();
777
 
778
            try {
779
                ds.execute(createAssoc.asString());
780
 
781
                insertUndef(createAssoc);
782
                root.refetchTable("ASSOCIATION_ANALYTIQUE");
783
                root.getSchema().updateVersion();
784
            } catch (SQLException ex) {
785
                throw new IllegalStateException("Erreur lors de la création de la table ASSOCIATION_ANALYTIQUE", ex);
786
            }
787
 
788
        }
789
    }
790
 
791
    private void updateStock(DBRoot root) throws SQLException {
792
 
793
        final SQLTable tableStock = root.getTable("STOCK");
794
        final SQLDataSource ds = root.getDBSystemRoot().getDataSource();
795
        if (!tableStock.contains("QTE_RECEPT_ATTENTE")) {
796
 
797
            try {
798
 
799
                AlterTable alterElt = new AlterTable(root.getTable("STOCK"));
800
                alterElt.addColumn("QTE_RECEPT_ATTENTE", "real DEFAULT 0");
801
                alterElt.addColumn("QTE_LIV_ATTENTE", "real DEFAULT 0");
802
                ds.execute(alterElt.asString());
803
 
804
                AlterTable alterMvt = new AlterTable(root.getTable("MOUVEMENT_STOCK"));
805
                alterMvt.addBooleanColumn("REEL", Boolean.TRUE, false);
806
 
807
                ds.execute(alterMvt.asString());
808
 
809
                root.getSchema().updateVersion();
810
 
811
            } catch (SQLException ex) {
812
                throw new IllegalStateException("Erreur lors de la mise à jour des tables de stock", ex);
813
            }
814
 
815
        }
816
        // if (!root.contains("ARTICLE_ELEMENT")) {
817
        // final SQLCreateTable createTable = new SQLCreateTable(root, "ARTICLE_ELEMENT");
818
        // createTable.addForeignColumn("ARTICLE");
819
        // createTable.addForeignColumn("ID_ARTICLE_PARENT", root.getTable("ARTICLE"));
820
        // createTable.addIntegerColumn("QTE", 1);
821
        // createTable.addDecimalColumn("QTE_UNITAIRE", 16, 6, BigDecimal.valueOf(1), false);
822
        // createTable.addForeignColumn("UNITE_VENTE");
823
        // insertUndef(createTable);
824
        // ds.execute(createTable.asString());
825
        //
826
        // root.getSchema().updateVersion();
827
        // root.refetchTable("ARTICLE_ELEMENT");
828
        //
829
        // }
830
        //
831
        // if (!root.getTable("ARTICLE").contains("COMPOSED")) {
832
        // AlterTable alterMvt = new AlterTable(root.getTable("ARTICLE"));
833
        // alterMvt.addBooleanColumn("COMPOSED", Boolean.FALSE, false);
834
        //
835
        // ds.execute(alterMvt.asString());
836
        //
837
        // root.getSchema().updateVersion();
838
        // }
839
 
840
    }
841
 
842
    private void createBanque(DBRoot root) throws SQLException {
843
 
844
        if (!root.contains("BANQUE") && !root.contains("BANQUE_POLE_PRODUIT")) {
845
 
846
            SQLCreateTable createBanque = new SQLCreateTable(root, "BANQUE");
847
            createBanque.addForeignColumn("ID_JOURNAL", root.findTable("JOURNAL", true));
848
            createBanque.addVarCharColumn("INFOS", 2048);
849
            createBanque.addVarCharColumn("NUMERO_RUE", 45);
850
            createBanque.addVarCharColumn("RUE", 256);
851
            createBanque.addVarCharColumn("IBAN", 256);
852
            createBanque.addVarCharColumn("BIC", 256);
853
            createBanque.addVarCharColumn("VOIE", 256);
854
            createBanque.addVarCharColumn("VILLE", 256);
855
            createBanque.addVarCharColumn("NOM", 256);
856
            createBanque.addVarCharColumn("DOMICILIATION", 256);
857
            createBanque.addVarCharColumn("CODE", 256);
858
            createBanque.addBooleanColumn("AFFACTURAGE", Boolean.FALSE, false);
859
            createBanque.addForeignColumn("ID_COMPTE_PCE", root.findTable("COMPTE_PCE", true));
860
 
861
            final SQLDataSource ds = root.getDBSystemRoot().getDataSource();
862
 
863
            try {
864
                ds.execute(createBanque.asString());
865
 
866
                insertUndef(createBanque);
867
                root.refetchTable("BANQUE");
868
                {
869
                    AlterTable alterElt = new AlterTable(root.getTable("MODE_REGLEMENT"));
870
                    alterElt.addForeignColumn("ID_BANQUE", root.getTable("BANQUE"));
871
 
872
                    ds.execute(alterElt.asString());
873
                }
874
 
875
                {
876
                    AlterTable alterElt = new AlterTable(root.getTable("CHEQUE_A_ENCAISSER"));
877
                    alterElt.addForeignColumn("ID_BANQUE", root.getTable("BANQUE"));
878
                    ds.execute(alterElt.asString());
879
                }
880
 
881
                {
882
                    AlterTable alterElt = new AlterTable(root.getTable("CHEQUE_FOURNISSEUR"));
883
                    alterElt.addForeignColumn("ID_BANQUE", root.getTable("BANQUE"));
884
                    ds.execute(alterElt.asString());
885
                }
886
 
887
                root.getSchema().updateVersion();
888
 
889
            } catch (SQLException ex) {
890
                throw new IllegalStateException("Erreur lors de la création de la table BANQUE", ex);
891
            }
892
 
893
        }
894
    }
895
 
80 ilm 896
    private void createFactureFournisseur(DBRoot root) throws SQLException {
897
        boolean refetchRoot = false;
898
        if (!root.contains("FACTURE_FOURNISSEUR")) {
899
 
900
            SQLCreateTable createFactureF = new SQLCreateTable(root, "FACTURE_FOURNISSEUR");
901
            createFactureF.addVarCharColumn("NOM", 256);
902
            createFactureF.addVarCharColumn("NUMERO", 45);
903
            createFactureF.addVarCharColumn("INFOS", 2048);
904
            createFactureF.addColumn("DATE", "date");
905
            createFactureF.addForeignColumn("FOURNISSEUR");
906
            createFactureF.addForeignColumn("AVOIR_FOURNISSEUR");
907
            createFactureF.addForeignColumn("COMPTE_PCE");
908
            createFactureF.addForeignColumn("COMMERCIAL");
909
            createFactureF.addForeignColumn("MODE_REGLEMENT");
910
            createFactureF.addForeignColumn("MOUVEMENT");
911
            createFactureF.addForeignColumn("ID_DEVISE", root.findTable("DEVISE", true));
912
            createFactureF.addColumn("T_HT", "bigint DEFAULT 0");
913
            createFactureF.addColumn("T_TVA", "bigint DEFAULT 0");
914
            createFactureF.addColumn("T_TTC", "bigint DEFAULT 0");
915
            createFactureF.addColumn("T_SERVICE", "bigint DEFAULT 0");
916
            createFactureF.addColumn("T_DEVISE", "bigint DEFAULT 0");
917
            createFactureF.addColumn("T_POIDS", "real DEFAULT 0");
918
 
919
            final SQLDataSource ds = root.getDBSystemRoot().getDataSource();
920
 
921
            try {
922
                ds.execute(createFactureF.asString());
923
                insertUndef(createFactureF);
924
                root.getSchema().updateVersion();
925
                root.refetchTable("FACTURE_FOURNISSEUR");
926
                refetchRoot = true;
927
            } catch (SQLException ex) {
928
                throw new IllegalStateException("Erreur lors de la création de la table FACTURE_FOURNISSEUR", ex);
929
            }
930
 
931
        }
932
 
933
        if (!root.contains("FACTURE_FOURNISSEUR_ELEMENT")) {
934
 
935
            SQLCreateTable createFactureF = new SQLCreateTable(root, "FACTURE_FOURNISSEUR_ELEMENT");
936
 
937
            createFactureF.addDecimalColumn("PRIX_METRIQUE_HA_1", 16, 6, BigDecimal.ZERO, false);
938
            createFactureF.addDecimalColumn("PRIX_METRIQUE_HA_2", 16, 6, BigDecimal.ZERO, false);
939
            createFactureF.addDecimalColumn("PRIX_METRIQUE_HA_3", 16, 6, BigDecimal.ZERO, false);
940
            createFactureF.addDecimalColumn("PRIX_METRIQUE_VT_1", 16, 6, BigDecimal.ZERO, false);
941
            createFactureF.addDecimalColumn("PRIX_METRIQUE_VT_2", 16, 6, BigDecimal.ZERO, false);
942
            createFactureF.addDecimalColumn("PRIX_METRIQUE_VT_3", 16, 6, BigDecimal.ZERO, false);
943
            createFactureF.addDecimalColumn("T_PV_HT", 16, 6, BigDecimal.ZERO, false);
944
            createFactureF.addDecimalColumn("T_PA_HT", 16, 6, BigDecimal.ZERO, false);
945
            createFactureF.addDecimalColumn("PV_HT", 16, 6, BigDecimal.ZERO, false);
946
            createFactureF.addDecimalColumn("PA_HT", 16, 6, BigDecimal.ZERO, false);
947
            createFactureF.addDecimalColumn("T_PV_TTC", 16, 6, BigDecimal.ZERO, false);
948
            createFactureF.addDecimalColumn("T_PA_TTC", 16, 6, BigDecimal.ZERO, false);
949
            createFactureF.addDecimalColumn("QTE_UNITAIRE", 16, 6, BigDecimal.ZERO, false);
950
            createFactureF.addDecimalColumn("PA_DEVISE_T", 16, 6, BigDecimal.ZERO, false);
951
            createFactureF.addDecimalColumn("PA_DEVISE", 16, 6, BigDecimal.ZERO, false);
952
            createFactureF.addIntegerColumn("QTE", 1);
953
            createFactureF.addIntegerColumn("QTE_ACHAT", 1);
954
 
955
            createFactureF.addColumn("VALEUR_METRIQUE_1", "real DEFAULT 0");
956
            createFactureF.addColumn("VALEUR_METRIQUE_2", "real DEFAULT 0");
957
            createFactureF.addColumn("VALEUR_METRIQUE_3", "real DEFAULT 0");
958
            createFactureF.addColumn("T_POIDS", "real DEFAULT 0");
959
            createFactureF.addColumn("POIDS", "real DEFAULT 0");
960
            createFactureF.addBooleanColumn("SERVICE", Boolean.FALSE, true);
961
            createFactureF.addVarCharColumn("CODE", 45);
962
            createFactureF.addVarCharColumn("NOM", 256);
963
            createFactureF.addColumn("DATE", "date");
964
            createFactureF.addForeignColumn("STYLE");
965
            createFactureF.addForeignColumn("METRIQUE", "3");
966
            createFactureF.addForeignColumn("METRIQUE", "2");
967
            createFactureF.addForeignColumn("METRIQUE", "1");
968
            createFactureF.addForeignColumn("FACTURE_FOURNISSEUR");
969
            createFactureF.addForeignColumn("TAXE");
970
            createFactureF.addForeignColumn("ID_MODE_VENTE_ARTICLE", root.findTable("MODE_VENTE_ARTICLE").getSQLName(), "ID", "5");
971
            createFactureF.addForeignColumn("UNITE_VENTE");
972
            createFactureF.addForeignColumn("ARTICLE");
973
            createFactureF.addForeignColumn("ID_DEVISE", root.findTable("DEVISE", true));
974
            createFactureF.addForeignColumn("CODE_FOURNISSEUR");
975
 
976
            final SQLDataSource ds = root.getDBSystemRoot().getDataSource();
977
 
978
            try {
979
                ds.execute(createFactureF.asString());
980
                insertUndef(createFactureF);
981
                root.getSchema().updateVersion();
982
                refetchRoot = true;
983
            } catch (SQLException ex) {
984
                throw new IllegalStateException("Erreur lors de la création de la table FACTURE_FOURNISSEUR_ELEMENT", ex);
985
            }
986
 
987
        }
988
 
989
        if (!root.contains("REGLER_MONTANT_ELEMENT")) {
990
 
991
            SQLCreateTable createReglerElt = new SQLCreateTable(root, "REGLER_MONTANT_ELEMENT");
992
 
993
            createReglerElt.addForeignColumn("ECHEANCE_FOURNISSEUR");
994
            createReglerElt.addForeignColumn("REGLER_MONTANT");
995
            createReglerElt.addForeignColumn("MOUVEMENT", "ECHEANCE");
996
            createReglerElt.addColumn("DATE", "date");
997
            createReglerElt.addColumn("MONTANT_REGLE", "bigint DEFAULT 0");
998
            createReglerElt.addColumn("MONTANT_A_REGLER", "bigint DEFAULT 0");
999
 
1000
            final SQLDataSource ds = root.getDBSystemRoot().getDataSource();
1001
 
1002
            try {
1003
                ds.execute(createReglerElt.asString());
1004
                insertUndef(createReglerElt);
1005
                root.getSchema().updateVersion();
1006
                root.refetchTable("REGLER_MONTANT_ELEMENT");
1007
                refetchRoot = true;
1008
            } catch (SQLException ex) {
1009
                throw new IllegalStateException("Erreur lors de la création de la table REGLER_MONTANT_ELEMENT", ex);
1010
            }
1011
 
1012
        }
1013
 
1014
        final SQLTable tableReglerMontant = root.getTable("REGLER_MONTANT");
1015
 
1016
        boolean updateRegler = false;
1017
 
1018
        AlterTable alterElt = new AlterTable(tableReglerMontant);
1019
        if (!tableReglerMontant.contains("ID_FOURNISSEUR")) {
1020
            alterElt.addForeignColumn("ID_FOURNISSEUR", root.getTable("FOURNISSEUR"));
1021
            updateRegler = true;
1022
        }
1023
 
1024
        if (!tableReglerMontant.contains("NOM")) {
1025
            alterElt.addVarCharColumn("NOM", 256);
1026
            updateRegler = true;
1027
        }
1028
 
1029
        if (updateRegler) {
1030
            root.getDBSystemRoot().getDataSource().execute(alterElt.asString());
1031
            root.refetchTable(tableReglerMontant.getName());
1032
        }
1033
 
1034
        if (refetchRoot) {
1035
            root.refetch();
1036
        }
1037
    }
1038
 
25 ilm 1039
    private void fixUnboundedNumeric(DBRoot root) throws SQLException {
1040
 
1041
        final List<AlterTable> alters = new ArrayList<AlterTable>();
73 ilm 1042
        final List<UpdateBuilder> builds = new ArrayList<UpdateBuilder>();
25 ilm 1043
        {
1044
            SQLTable tableAvoir = root.getTable("AVOIR_CLIENT_ELEMENT");
1045
            final AlterTable alter = new AlterTable(tableAvoir);
1046
            SQLField fieldAcompteAvoir = tableAvoir.getField("POURCENT_ACOMPTE");
1047
            if (fieldAcompteAvoir.getType().getSize() > 500) {
1048
                final String fName = fieldAcompteAvoir.getName();
1049
                alter.alterColumn(fName, EnumSet.allOf(Properties.class), "numeric(6,2)", "100", false);
73 ilm 1050
 
1051
                UpdateBuilder build = new UpdateBuilder(tableAvoir);
1052
                build.set(fieldAcompteAvoir.getName(), "100");
1053
                build.setWhere(new Where(fieldAcompteAvoir, "=", (Object) null));
1054
                builds.add(build);
25 ilm 1055
            }
1056
 
83 ilm 1057
            if (tableAvoir.contains("POURCENT_REMISE")) {
1058
                SQLField fieldRemiseAvoir = tableAvoir.getField("POURCENT_REMISE");
1059
                if (fieldRemiseAvoir.getType().getSize() > 500) {
1060
                    final String fName = fieldRemiseAvoir.getName();
1061
                    alter.alterColumn(fName, EnumSet.allOf(Properties.class), "numeric(6,2)", "0", false);
73 ilm 1062
 
83 ilm 1063
                    UpdateBuilder build = new UpdateBuilder(tableAvoir);
1064
                    build.set(fieldRemiseAvoir.getName(), "0");
1065
                    build.setWhere(new Where(fieldRemiseAvoir, "=", (Object) null));
1066
                    builds.add(build);
1067
                }
25 ilm 1068
            }
1069
            if (!alter.isEmpty())
1070
                alters.add(alter);
1071
        }
1072
 
1073
        {
1074
            SQLTable tableFacture = root.getTable("SAISIE_VENTE_FACTURE_ELEMENT");
1075
            final AlterTable alter = new AlterTable(tableFacture);
1076
            SQLField fieldAcompteFacture = tableFacture.getField("POURCENT_ACOMPTE");
1077
            if (fieldAcompteFacture.getType().getSize() > 500) {
1078
                final String fName = fieldAcompteFacture.getName();
1079
                alter.alterColumn(fName, EnumSet.allOf(Properties.class), "numeric(6,2)", "100", false);
73 ilm 1080
 
1081
                UpdateBuilder build = new UpdateBuilder(tableFacture);
1082
                build.set(fieldAcompteFacture.getName(), "100");
1083
                build.setWhere(new Where(fieldAcompteFacture, "=", (Object) null));
1084
                builds.add(build);
25 ilm 1085
            }
1086
 
1087
            SQLField fieldRemiseFacture = tableFacture.getField("POURCENT_REMISE");
1088
            if (fieldRemiseFacture.getType().getSize() > 500) {
1089
                final String fName = fieldRemiseFacture.getName();
1090
                alter.alterColumn(fName, EnumSet.allOf(Properties.class), "numeric(6,2)", "0", false);
73 ilm 1091
 
1092
                UpdateBuilder build = new UpdateBuilder(tableFacture);
1093
                build.set(fieldRemiseFacture.getName(), "0");
1094
                build.setWhere(new Where(fieldRemiseFacture, "=", (Object) null));
1095
                builds.add(build);
25 ilm 1096
            }
1097
 
1098
            if (tableFacture.getFieldsName().contains("REPARTITION_POURCENT")) {
1099
                SQLField fieldRepFacture = tableFacture.getField("REPARTITION_POURCENT");
1100
                if (fieldRepFacture.getType().getSize() > 500) {
1101
                    final String fName = fieldRepFacture.getName();
1102
                    alter.alterColumn(fName, EnumSet.allOf(Properties.class), "numeric(6,2)", "0", false);
73 ilm 1103
 
1104
                    UpdateBuilder build = new UpdateBuilder(tableFacture);
1105
                    build.set(fieldRepFacture.getName(), "0");
1106
                    build.setWhere(new Where(fieldRepFacture, "=", (Object) null));
1107
                    builds.add(build);
25 ilm 1108
                }
1109
            }
1110
 
1111
            if (!alter.isEmpty())
1112
                alters.add(alter);
1113
 
1114
        }
1115
        if (alters.size() > 0) {
1116
            final SQLDataSource ds = root.getDBSystemRoot().getDataSource();
73 ilm 1117
 
1118
            for (UpdateBuilder updateBuilder : builds) {
1119
                ds.execute(updateBuilder.asString());
1120
            }
1121
 
25 ilm 1122
            for (final String sql : ChangeTable.cat(alters, root.getName())) {
1123
                ds.execute(sql);
1124
            }
1125
            root.refetch();
1126
        }
1127
    }
1128
 
20 ilm 1129
    private void fixUnboundedVarchar(DBRoot root) throws SQLException {
1130
        final Set<String> namesSet = CollectionUtils.createSet("NOM", "PRENOM", "SURNOM", "LOGIN", "PASSWORD");
1131
        final List<AlterTable> alters = new ArrayList<AlterTable>();
73 ilm 1132
        final List<UpdateBuilder> builds = new ArrayList<UpdateBuilder>();
20 ilm 1133
        for (final SQLTable t : root.getTables()) {
1134
            final AlterTable alter = new AlterTable(t);
1135
            for (final SQLField f : t.getFields()) {
1136
                if (f.getType().getType() == Types.VARCHAR && f.getType().getSize() == Integer.MAX_VALUE) {
73 ilm 1137
 
1138
                    UpdateBuilder build = new UpdateBuilder(t);
1139
                    build.set(f.getName(), "''");
1140
                    build.setWhere(new Where(f, "=", (Object) null));
1141
                    builds.add(build);
1142
 
20 ilm 1143
                    final String fName = f.getName();
1144
                    final int size;
80 ilm 1145
                    if (t.getName().contains("USER") && namesSet.contains(fName))
21 ilm 1146
                        size = 128;
20 ilm 1147
                    else if (fName.equals("TEL") || fName.startsWith("TEL_"))
21 ilm 1148
                        size = 32;
1149
                    else if (fName.contains("INFO"))
80 ilm 1150
                        size = 4096;
21 ilm 1151
                    else if (fName.contains("FORMULE"))
1152
                        size = 1024;
1153
                    else if (fName.equals("CONTENU"))
1154
                        size = 2048;
20 ilm 1155
                    else
80 ilm 1156
                        // e.g. IDCC.NOM > 350
1157
                        size = 512;
20 ilm 1158
                    alter.alterColumn(fName, EnumSet.allOf(Properties.class), "varchar(" + size + ")", "''", false);
1159
                }
1160
            }
1161
            if (!alter.isEmpty())
1162
                alters.add(alter);
1163
        }
1164
        if (alters.size() > 0) {
1165
            final SQLDataSource ds = root.getDBSystemRoot().getDataSource();
73 ilm 1166
 
1167
            for (UpdateBuilder build : builds) {
1168
                ds.execute(build.asString());
1169
            }
1170
 
20 ilm 1171
            for (final String sql : ChangeTable.cat(alters, root.getName())) {
1172
                ds.execute(sql);
1173
            }
1174
            root.refetch();
1175
        }
67 ilm 1176
 
20 ilm 1177
    }
1178
 
67 ilm 1179
    private void updateToV1Dot3(final DBRoot root) throws SQLException {
1180
        final SQLDataSource ds = root.getDBSystemRoot().getDataSource();
73 ilm 1181
 
1182
        addForeignKeyFactureOnEcheance(root);
80 ilm 1183
        addFieldForPartialInvoice(root);
73 ilm 1184
 
1185
        // Numérotation client
1186
        {
1187
            SQLTable tableNum = root.getTable("NUMEROTATION_AUTO");
1188
            boolean alterNum = false;
1189
            AlterTable t = new AlterTable(tableNum);
1190
            if (!tableNum.getFieldsName().contains("CLIENT_START")) {
1191
                t.addColumn("CLIENT_START", "integer DEFAULT 0");
1192
                alterNum = true;
1193
            }
1194
            if (!tableNum.getFieldsName().contains("CLIENT_FORMAT")) {
1195
                t.addVarCharColumn("CLIENT_FORMAT", 48);
1196
                alterNum = true;
1197
            }
1198
 
1199
            if (alterNum) {
1200
                try {
1201
                    ds.execute(t.asString());
1202
                    tableNum.getSchema().updateVersion();
1203
                    tableNum.fetchFields();
1204
                } catch (SQLException ex) {
1205
                    throw new IllegalStateException("Erreur lors de l'ajout des champs sur la table NUMEROTATION_AUTO", ex);
1206
                }
1207
            }
1208
        }
1209
 
80 ilm 1210
        // Eecheance founisseur
1211
        {
1212
            SQLTable tableEchF = root.getTable("ECHEANCE_FOURNISSEUR");
1213
            AlterTable t = new AlterTable(tableEchF);
1214
            if (!tableEchF.getFieldsName().contains("REG_COMPTA")) {
1215
                t.addColumn("REG_COMPTA", "boolean DEFAULT false");
1216
                try {
1217
                    ds.execute(t.asString());
1218
                    tableEchF.getSchema().updateVersion();
1219
                    tableEchF.fetchFields();
1220
                } catch (SQLException ex) {
1221
                    throw new IllegalStateException("Erreur lors de l'ajout du champ REG_COMPTA sur la table ECHEANCE_FOUNISSEUR", ex);
1222
                }
1223
            }
1224
        }
1225
 
1226
        SQLTable tableTaxe = root.getTable("TAXE");
1227
        boolean containsTaxeDefault = tableTaxe.contains("DEFAULT");
1228
        if (!containsTaxeDefault) {
1229
            AlterTable t = new AlterTable(tableTaxe);
1230
            t.addColumn("DEFAULT", "boolean DEFAULT false");
1231
 
1232
            try {
1233
                ds.execute(t.asString());
1234
                tableTaxe.getSchema().updateVersion();
1235
                tableTaxe.fetchFields();
1236
            } catch (SQLException ex) {
1237
                throw new IllegalStateException("Erreur lors de l'ajout des champs sur la table TAXE", ex);
1238
            }
1239
 
1240
        }
1241
 
1242
        // Ajout de la TVA à 20
1243
        SQLSelect selTVA = new SQLSelect();
1244
        selTVA.addSelect(tableTaxe.getKey(), "COUNT");
1245
        selTVA.setWhere(new Where(tableTaxe.getField("TAUX"), "=", 20));
1246
        Object result = root.getBase().getDataSource().executeScalar(selTVA.asString());
1247
        if (result == null || ((Number) result).longValue() == 0) {
1248
            SQLRowValues rowVals = new SQLRowValues(tableTaxe);
1249
            rowVals.put("NOM", "TVA 20%");
1250
            rowVals.put("TAUX", Float.valueOf(20));
1251
            rowVals.put("DEFAULT", Boolean.TRUE);
1252
            rowVals.commit();
1253
        } else if (!containsTaxeDefault) {
1254
            SQLSelect selTVA20 = new SQLSelect();
1255
            selTVA20.addSelectStar(tableTaxe);
1256
            selTVA20.setWhere(new Where(tableTaxe.getField("TAUX"), "=", 20));
1257
            List<SQLRow> lTVA = SQLRowListRSH.execute(selTVA20);
1258
            if (lTVA != null && lTVA.size() > 0) {
1259
                SQLRowValues rowVals = lTVA.get(0).asRowValues();
1260
                rowVals.put("DEFAULT", Boolean.TRUE);
1261
                rowVals.update();
1262
            }
1263
 
1264
        }
1265
 
67 ilm 1266
        // Article
1267
        {
1268
            SQLTable tableProduct = root.getTable("ARTICLE");
1269
            boolean alterTableProduct = false;
1270
            AlterTable t = new AlterTable(tableProduct);
1271
            if (!tableProduct.getFieldsName().contains("ID_COMPTE_PCE")) {
1272
                t.addForeignColumn("ID_COMPTE_PCE", root.getTable("COMPTE_PCE"));
1273
                alterTableProduct = true;
1274
            }
1275
            if (!tableProduct.getFieldsName().contains("ID_COMPTE_PCE_ACHAT")) {
1276
                t.addForeignColumn("ID_COMPTE_PCE_ACHAT", root.getTable("COMPTE_PCE"));
1277
                alterTableProduct = true;
1278
            }
1279
            if (alterTableProduct) {
1280
                try {
1281
                    ds.execute(t.asString());
1282
                    tableProduct.getSchema().updateVersion();
1283
                    tableProduct.fetchFields();
1284
                } catch (SQLException ex) {
1285
                    throw new IllegalStateException("Erreur lors de l'ajout des champs sur la table ARTICLE", ex);
1286
                }
1287
            }
1288
        }
1289
 
1290
        // Famille Article
1291
        {
1292
            SQLTable tableArticleFamily = root.getTable("FAMILLE_ARTICLE");
1293
            boolean alterArticleFamily = false;
1294
            AlterTable t = new AlterTable(tableArticleFamily);
1295
            if (!tableArticleFamily.getFieldsName().contains("ID_COMPTE_PCE")) {
1296
                t.addForeignColumn("ID_COMPTE_PCE", root.getTable("COMPTE_PCE"));
1297
                alterArticleFamily = true;
1298
            }
1299
            if (!tableArticleFamily.getFieldsName().contains("ID_COMPTE_PCE_ACHAT")) {
1300
                t.addForeignColumn("ID_COMPTE_PCE_ACHAT", root.getTable("COMPTE_PCE"));
1301
                alterArticleFamily = true;
1302
            }
1303
            if (alterArticleFamily) {
1304
                try {
1305
                    ds.execute(t.asString());
1306
                    tableArticleFamily.getSchema().updateVersion();
1307
                    tableArticleFamily.fetchFields();
1308
                } catch (SQLException ex) {
1309
                    throw new IllegalStateException("Erreur lors de l'ajout des champs sur la table FAMILLE_ARTICLE", ex);
1310
                }
1311
            }
1312
        }
1313
 
1314
        // ECRITURE
1315
        {
1316
            SQLTable tableRecords = root.getTable("ECRITURE");
1317
            boolean alterRecords = false;
1318
            AlterTable t = new AlterTable(tableRecords);
1319
            if (!tableRecords.getFieldsName().contains("DATE_EXPORT")) {
1320
                t.addColumn("DATE_EXPORT", "date");
1321
                alterRecords = true;
1322
            }
1323
 
1324
            if (!tableRecords.getFieldsName().contains("CODE_CLIENT")) {
1325
                t.addVarCharColumn("CODE_CLIENT", 256);
1326
                alterRecords = true;
1327
            }
1328
            if (alterRecords) {
1329
                try {
1330
                    ds.execute(t.asString());
1331
                    tableRecords.getSchema().updateVersion();
1332
                    tableRecords.fetchFields();
1333
                } catch (SQLException ex) {
1334
                    throw new IllegalStateException("Erreur lors de l'ajout des champs sur la table ECRITURE", ex);
1335
                }
1336
            }
1337
        }
1338
        addInfoField(root, ds, "AVOIR_FOURNISSEUR");
1339
        addInfoField(root, ds, "AVOIR_CLIENT");
1340
 
1341
        boolean refetchRoot = false;
1342
        if (!root.contains("CODE_FOURNISSEUR")) {
1343
 
1344
            SQLCreateTable createCode = new SQLCreateTable(root, "CODE_FOURNISSEUR");
1345
            createCode.addVarCharColumn("CODE", 256);
1346
            createCode.addForeignColumn("FOURNISSEUR");
1347
            createCode.addForeignColumn("ARTICLE");
1348
            try {
1349
                ds.execute(createCode.asString());
1350
                insertUndef(createCode);
1351
                root.getSchema().updateVersion();
1352
                refetchRoot = true;
1353
            } catch (SQLException ex) {
1354
                throw new IllegalStateException("Erreur lors de la création de la table CODE_FOURNISSEUR", ex);
1355
            }
1356
 
1357
        }
1358
 
1359
        // Chargement des tables fraichement créées
1360
        if (refetchRoot)
1361
            root.refetch();
1362
 
80 ilm 1363
        createFactureFournisseur(root);
1364
 
67 ilm 1365
        addSupplierCode(root, ds, "BON_RECEPTION_ELEMENT");
1366
        addSupplierCode(root, ds, "COMMANDE_ELEMENT");
1367
 
1368
        // Undefined
1369
        SQLTable.setUndefID(root.getSchema(), "ARTICLE_DESIGNATION", 1);
1370
        SQLTable.setUndefID(root.getSchema(), "ARTICLE_TARIF", 1);
1371
        SQLTable.setUndefID(root.getSchema(), "CODE_STATUT_CAT_CONV", 1);
1372
        SQLTable.setUndefID(root.getSchema(), "CONTACT_ADMINISTRATIF", 1);
1373
        SQLTable.setUndefID(root.getSchema(), "CONTACT_FOURNISSEUR", 1);
1374
 
1375
        SQLTable.setUndefID(root.getSchema(), "LANGUE", 1);
1376
        SQLTable.setUndefID(root.getSchema(), "MODELE", 1);
1377
        SQLTable.setUndefID(root.getSchema(), "OBJECTIF_COMMERCIAL", 1);
1378
        SQLTable.setUndefID(root.getSchema(), "TARIF", 1);
1379
 
1380
        SQLTable.setUndefID(root.getSchema(), "UNITE_VENTE", 1);
73 ilm 1381
 
1382
        // Create transfer tables
1383
        ComptaPropsConfiguration.setSocieteSQLInjector(root);
1384
        SQLInjector.createTransferTables(root);
1385
        // Move transfer info to SAISIE_VENTE_FACTURE
1386
        convertTransfer(root, Arrays.asList("COMMANDE_CLIENT", "DEVIS", "BON_DE_LIVRAISON"), "SAISIE_VENTE_FACTURE");
83 ilm 1387
        // Fix keys
1388
        if (root.getServer().getSQLSystem().equals(SQLSystem.H2)) {
1389
            final ChangeIDToInt c = new ChangeIDToInt(root.getDBSystemRoot());
1390
            c.changeAll(root);
1391
            root.getDBSystemRoot().reload(Collections.singleton(root.getName()));
1392
        }
67 ilm 1393
    }
1394
 
73 ilm 1395
    private void convertTransfer(DBRoot root, List<String> tablesSrc, String tableDest) throws SQLException {
1396
        final SQLTable tableDestination = root.getTable(tableDest);
1397
        if (tableDestination.contains("SOURCE") && tableDestination.contains("IDSOURCE")) {
1398
            for (String tableSrc : tablesSrc) {
1399
                convertTransfer(root.getTable(tableSrc), tableDestination);
1400
            }
1401
            final AlterTable alter = new AlterTable(tableDestination);
1402
            alter.dropColumn("SOURCE");
1403
            alter.dropColumn("IDSOURCE");
1404
            final String req = alter.asString();
1405
            root.getDBSystemRoot().getDataSource().execute(req);
1406
            root.refetchTable(tableDest);
1407
        }
1408
    }
1409
 
1410
    private void convertTransfer(final SQLTable tableSource, final SQLTable tableDest) throws SQLException {
1411
        SQLInjector inj = SQLInjector.getInjector(tableSource, tableDest);
1412
        final SQLRowValues vals = new SQLRowValues(tableDest);
1413
        vals.putNulls("SOURCE", "IDSOURCE");
1414
        final SQLRowValuesListFetcher fetcher = new SQLRowValuesListFetcher(vals);
1415
        fetcher.setSelTransf(new ITransformer<SQLSelect, SQLSelect>() {
1416
 
1417
            @Override
1418
            public SQLSelect transformChecked(SQLSelect input) {
1419
                Where w = new Where(tableDest.getField("SOURCE"), "=", tableSource.getName());
1420
                w = w.and(new Where(tableDest.getField("IDSOURCE"), "!=", tableSource.getUndefinedIDNumber()));
1421
                // remove archive idsource
1422
                w = w.and(new Where(tableDest.getField("IDSOURCE"), "=", tableSource.getKey()));
1423
                input.setWhere(w);
1424
                return input;
1425
            }
1426
        });
1427
        List<SQLRowValues> rows = fetcher.fetch();
1428
        for (SQLRowValues sqlRowValues : rows) {
1429
            inj.addTransfert(sqlRowValues.getInt("IDSOURCE"), sqlRowValues.getID());
1430
        }
1431
    }
1432
 
67 ilm 1433
    private void addInfoField(final DBRoot root, final SQLDataSource ds, String tableName) {
1434
        SQLTable tableBL = root.getTable(tableName);
1435
        boolean alterBL = false;
1436
        AlterTable t = new AlterTable(tableBL);
1437
        if (!tableBL.getFieldsName().contains("INFOS")) {
1438
            t.addVarCharColumn("INFOS", 1024);
1439
            alterBL = true;
1440
        }
1441
        if (alterBL) {
1442
            try {
1443
                ds.execute(t.asString());
1444
                tableBL.getSchema().updateVersion();
1445
                tableBL.fetchFields();
1446
            } catch (SQLException ex) {
1447
                throw new IllegalStateException("Erreur lors de l'ajout des champs sur la table AVOIR FOURNISSEUR", ex);
1448
            }
1449
        }
1450
    }
1451
 
1452
    private void addSupplierCode(final DBRoot root, final SQLDataSource ds, String tableName) {
1453
        SQLTable tableBL = root.getTable(tableName);
1454
        boolean alterBL = false;
1455
        AlterTable t = new AlterTable(tableBL);
1456
        if (!tableBL.contains("ID_CODE_FOURNISSEUR")) {
1457
            t.addForeignColumn("ID_CODE_FOURNISSEUR", root.getTable("CODE_FOURNISSEUR"));
1458
            alterBL = true;
1459
        }
1460
        if (alterBL) {
1461
            try {
1462
                ds.execute(t.asString());
1463
                tableBL.getSchema().updateVersion();
1464
                tableBL.fetchFields();
1465
            } catch (SQLException ex) {
1466
                throw new IllegalStateException("Erreur lors de l'ajout des champs sur la table " + tableName, ex);
1467
            }
1468
        }
1469
    }
1470
 
83 ilm 1471
    private void updateToV1Dot4(final DBRoot root) throws SQLException {
1472
        SQLTable tableVFElt = root.getTable("SAISIE_VENTE_FACTURE_ELEMENT");
1473
        patchFieldElt1Dot4(tableVFElt, root);
1474
 
1475
        SQLTable tableDevisElt = root.getTable("DEVIS_ELEMENT");
1476
        patchFieldElt1Dot4(tableDevisElt, root);
1477
 
1478
        SQLTable tableCmdElt = root.getTable("COMMANDE_CLIENT_ELEMENT");
1479
        patchFieldElt1Dot4(tableCmdElt, root);
1480
 
1481
        SQLTable tableBonElt = root.getTable("BON_DE_LIVRAISON_ELEMENT");
1482
        patchFieldElt1Dot4(tableBonElt, root);
1483
 
1484
        SQLTable tableAvoirElt = root.getTable("AVOIR_CLIENT_ELEMENT");
1485
        patchFieldElt1Dot4(tableAvoirElt, root);
1486
 
1487
        addContact(root);
1488
 
1489
        final SQLTable tableDevis = root.getTable("DEVIS");
90 ilm 1490
        AlterTable tDevis = new AlterTable(tableDevis);
1491
        boolean updateDevis = false;
1492
        if (!tableDevis.contains("POURCENT_REMISE")) {
1493
            updateDevis = true;
1494
            tDevis.addColumn("POURCENT_REMISE", "numeric (12,8)");
1495
        }
83 ilm 1496
        if (!tableDevis.contains("MONTANT_REMISE")) {
90 ilm 1497
            updateDevis = true;
1498
            tDevis.addColumn("MONTANT_REMISE", "numeric (16,8)");
1499
        }
1500
        if (!tableDevis.contains("T_HA")) {
1501
            updateDevis = true;
1502
            tDevis.addColumn("T_HA", "bigint", "0", false);
1503
        }
1504
        if (updateDevis) {
1505
            tableDevis.getBase().getDataSource().execute(tDevis.asString());
83 ilm 1506
            tableDevis.getSchema().updateVersion();
1507
            tableDevis.fetchFields();
1508
        }
1509
 
1510
        final SQLTable tableKmElt = root.getTable("SAISIE_KM_ELEMENT");
1511
        if (!tableKmElt.contains("ANALYTIQUE")) {
1512
            AlterTable t = new AlterTable(tableKmElt);
1513
            t.addVarCharColumn("ANALYTIQUE", 256);
1514
            tableKmElt.getBase().getDataSource().execute(t.asString());
1515
            tableKmElt.getSchema().updateVersion();
1516
            tableKmElt.fetchFields();
1517
 
1518
        }
1519
 
1520
        final SQLTable tableAdresse = root.getTable("ADRESSE");
90 ilm 1521
        if (tableAdresse != null) {
83 ilm 1522
            AlterTable t = new AlterTable(tableAdresse);
90 ilm 1523
            boolean updateADr = false;
1524
            if (!tableAdresse.contains("PROVINCE")) {
1525
                t.addVarCharColumn("PROVINCE", 256);
1526
                updateADr = true;
1527
            }
1528
            if (!tableAdresse.contains("LIBELLE")) {
1529
                t.addVarCharColumn("LIBELLE", 256);
1530
                updateADr = true;
1531
            }
1532
            if (!tableAdresse.contains("TYPE")) {
1533
                t.addVarCharColumn("TYPE", 256);
1534
                updateADr = true;
1535
            }
1536
            if (!tableAdresse.contains("EMAIL_CONTACT")) {
1537
                t.addVarCharColumn("EMAIL_CONTACT", 256);
1538
                updateADr = true;
1539
            }
1540
            if (updateADr) {
1541
                tableAdresse.getBase().getDataSource().execute(t.asString());
1542
                tableAdresse.getSchema().updateVersion();
1543
                tableAdresse.fetchFields();
1544
            }
83 ilm 1545
        }
1546
        final SQLTable tableClient = root.getTable("CLIENT");
1547
        if (tableClient != null && !tableClient.contains("BLOQUE_LIVRAISON")) {
1548
            AlterTable t = new AlterTable(tableClient);
1549
            t.addBooleanColumn("BLOQUE_LIVRAISON", false, false);
1550
            if (!tableClient.contains("BLOQUE")) {
1551
                t.addBooleanColumn("BLOQUE", false, false);
1552
            }
1553
            tableClient.getBase().getDataSource().execute(t.asString());
1554
            tableClient.getSchema().updateVersion();
1555
            tableClient.fetchFields();
1556
        }
1557
        final SQLTable tableAssoc = root.getTable("ASSOCIATION_ANALYTIQUE");
1558
        if (tableAssoc != null && !tableAssoc.contains("GESTION_AUTO")) {
1559
            AlterTable t = new AlterTable(tableAssoc);
1560
            t.addBooleanColumn("GESTION_AUTO", false, false);
1561
            tableAssoc.getBase().getDataSource().execute(t.asString());
1562
            tableAssoc.getSchema().updateVersion();
1563
            tableAssoc.fetchFields();
1564
        }
90 ilm 1565
        if (!root.contains("CALENDAR_ITEM")) {
1566
            final SQLCreateTable createTaskGroupTable = new SQLCreateTable(root, "CALENDAR_ITEM_GROUP");
1567
            createTaskGroupTable.addVarCharColumn("NAME", 1024);
1568
            createTaskGroupTable.addVarCharColumn("DESCRIPTION", 1024 * 8);
83 ilm 1569
 
90 ilm 1570
            final SQLCreateTable createTaskTable = new SQLCreateTable(root, "CALENDAR_ITEM");
1571
            createTaskTable.addDateAndTimeColumn("START");
1572
            createTaskTable.addDateAndTimeColumn("END");
1573
            createTaskTable.addLongColumn("DURATION_S", 0L, false);
1574
            createTaskTable.addVarCharColumn("SUMMARY", 1024);
1575
            createTaskTable.addVarCharColumn("DESCRIPTION", 1024 * 8);
1576
            createTaskTable.addVarCharColumn("FLAGS", 1024);
1577
            createTaskTable.addVarCharColumn("STATUS", 128);
1578
            createTaskTable.addForeignColumn(createTaskGroupTable);
1579
            createTaskTable.addLongColumn("SOURCE_ID", null, true);
1580
            createTaskTable.addVarCharColumn("SOURCE_TABLE", 256);
1581
            try {
1582
                root.getDBSystemRoot().getDataSource().execute(createTaskGroupTable.asString());
1583
                insertUndef(createTaskGroupTable);
1584
                root.getDBSystemRoot().getDataSource().execute(createTaskTable.asString());
1585
                insertUndef(createTaskTable);
1586
                tableDevis.getSchema().updateVersion();
1587
            } catch (SQLException ex) {
1588
                throw new IllegalStateException("Erreur lors de la création de la table TASK", ex);
1589
            }
1590
 
1591
        }
83 ilm 1592
        addArticleFournisseur(root);
1593
    }
1594
 
1595
    private void updateStyle(final DBRoot root) throws SQLException {
1596
        SQLTable style = root.getTable("STYLE");
1597
        SQLRowValues rowVals = new SQLRowValues(style);
1598
        rowVals.put("NOM", null);
1599
        SQLRowValuesListFetcher fetcher = SQLRowValuesListFetcher.create(rowVals);
1600
        List<SQLRowValues> list = fetcher.fetch();
1601
        boolean containsInvisible = false;
1602
        for (SQLRowValues sqlRowValues : list) {
1603
            if (sqlRowValues.getString("NOM").equals("Invisible")) {
1604
                containsInvisible = true;
1605
            }
1606
        }
1607
        if (!containsInvisible) {
1608
            SQLRowValues rowValsStyle = new SQLRowValues(style);
1609
            rowValsStyle.put("NOM", "Invisible");
1610
            rowValsStyle.put("CODE", "INV");
1611
            rowValsStyle.insert();
1612
        }
1613
 
1614
    }
1615
 
19 ilm 1616
    private void updateToV1Dot2(final DBRoot root) throws SQLException {
57 ilm 1617
        // bigint -> int ID_METRIQUE BON_DE_LIVRAISON_ELEMENT
1618
        final SQLTable tableLivraisonElement = root.getTable("BON_DE_LIVRAISON_ELEMENT");
1619
        AlterTable alter = new AlterTable(tableLivraisonElement);
1620
        alter.alterColumn("ID_METRIQUE_2", EnumSet.of(Properties.TYPE), "integer", null, null);
1621
        String req3 = alter.asString();
1622
        root.getDBSystemRoot().getDataSource().execute(req3);
1623
 
19 ilm 1624
        final SQLTable tableDevis = root.getTable("DEVIS");
1625
        final SQLDataSource ds = root.getDBSystemRoot().getDataSource();
1626
        if (!tableDevis.getFieldsName().contains("DATE_VALIDITE")) {
1627
            AlterTable t = new AlterTable(tableDevis);
1628
            t.addColumn("DATE_VALIDITE", "date");
1629
            try {
1630
                ds.execute(t.asString());
1631
                tableDevis.getSchema().updateVersion();
1632
            } catch (SQLException ex) {
1633
                throw new IllegalStateException("Erreur lors de l'ajout du champ DATE_VALIDITE à la table DEVIS", ex);
1634
            }
1635
        } else {
1636
            AlterTable t = new AlterTable(tableDevis);
1637
            t.alterColumn("DATE_VALIDITE", EnumSet.allOf(Properties.class), "date", null, true);
1638
            try {
1639
                ds.execute(t.asString());
1640
                tableDevis.getSchema().updateVersion();
1641
            } catch (SQLException ex) {
1642
                throw new IllegalStateException("Erreur lors de l'ajout du champ DATE_VALIDITE à la table DEVIS", ex);
1643
            }
1644
        }
18 ilm 1645
 
61 ilm 1646
        final SQLTable tableEtatDevis = root.getTable("ETAT_DEVIS");
1647
        if (tableEtatDevis.getRow(5) == null && tableEtatDevis.getRowCount() <= 4) {
1648
            SQLRowValues rowVals = new SQLRowValues(tableEtatDevis);
1649
            rowVals.put("NOM", "En cours de rédaction");
1650
            rowVals.commit();
67 ilm 1651
 
61 ilm 1652
        }
1653
 
67 ilm 1654
        SQLRowValues rowValsOrdre = new SQLRowValues(tableEtatDevis);
1655
        rowValsOrdre.put("ORDRE", new BigDecimal(1.505));
1656
        rowValsOrdre.update(EtatDevisSQLElement.EN_ATTENTE);
1657
 
1658
        rowValsOrdre.put("ORDRE", new BigDecimal(2.505));
1659
        rowValsOrdre.update(EtatDevisSQLElement.ACCEPTE);
1660
 
1661
        rowValsOrdre.put("ORDRE", new BigDecimal(3.505));
1662
        rowValsOrdre.update(EtatDevisSQLElement.REFUSE);
1663
 
83 ilm 1664
        rowValsOrdre.put("ORDRE", new BigDecimal(4.505));
67 ilm 1665
        rowValsOrdre.update(EtatDevisSQLElement.EN_COURS);
1666
 
63 ilm 1667
        // Ajout de la TVA à 0
67 ilm 1668
        SQLSelect selTVA = new SQLSelect();
63 ilm 1669
        SQLTable tableTaxe = root.getTable("TAXE");
1670
        selTVA.addSelect(tableTaxe.getKey(), "COUNT");
1671
        selTVA.setWhere(new Where(tableTaxe.getField("TAUX"), "=", 0));
1672
        Object result = root.getBase().getDataSource().executeScalar(selTVA.asString());
1673
        if (result == null || ((Number) result).longValue() == 0) {
1674
            SQLRowValues rowVals = new SQLRowValues(tableTaxe);
1675
            rowVals.put("NOM", "Non applicable");
1676
            rowVals.put("TAUX", Float.valueOf(0));
1677
            rowVals.commit();
1678
        }
1679
 
19 ilm 1680
        // Bon de livraison
1681
        {
1682
            SQLTable tableBL = root.getTable("BON_DE_LIVRAISON");
1683
            boolean alterBL = false;
1684
            AlterTable t = new AlterTable(tableBL);
1685
            if (!tableBL.getFieldsName().contains("SOURCE")) {
1686
                t.addVarCharColumn("SOURCE", 512);
1687
                alterBL = true;
1688
            }
1689
            if (!tableBL.getFieldsName().contains("IDSOURCE")) {
1690
                t.addColumn("IDSOURCE", "integer DEFAULT 1");
1691
                alterBL = true;
1692
            }
41 ilm 1693
 
1694
            if (!tableBL.getFieldsName().contains("DATE_LIVRAISON")) {
1695
                t.addColumn("DATE_LIVRAISON", "date");
1696
                alterBL = true;
1697
            }
19 ilm 1698
            if (alterBL) {
1699
                try {
1700
                    ds.execute(t.asString());
1701
                    tableBL.getSchema().updateVersion();
1702
                    tableBL.fetchFields();
1703
                } catch (SQLException ex) {
1704
                    throw new IllegalStateException("Erreur lors de l'ajout des champs sur la table BON_DE_LIVRAISON", ex);
1705
                }
1706
            }
1707
        }
61 ilm 1708
 
1709
        // Fournisseur
1710
        {
1711
            SQLTable tableBL = root.getTable("FOURNISSEUR");
1712
            boolean alterBL = false;
1713
            AlterTable t = new AlterTable(tableBL);
1714
            if (!tableBL.getFieldsName().contains("ID_COMPTE_PCE_CHARGE")) {
1715
                t.addForeignColumn("ID_COMPTE_PCE_CHARGE", root.getTable("COMPTE_PCE"));
1716
                alterBL = true;
1717
            }
1718
            if (alterBL) {
1719
                try {
1720
                    ds.execute(t.asString());
1721
                    tableBL.getSchema().updateVersion();
1722
                    tableBL.fetchFields();
1723
                } catch (SQLException ex) {
1724
                    throw new IllegalStateException("Erreur lors de l'ajout des champs sur la table FOURNISSEUR", ex);
1725
                }
1726
            }
1727
        }
1728
 
1729
        // Numérotation
1730
        {
1731
            SQLTable tableNum = root.getTable("NUMEROTATION_AUTO");
1732
            boolean alterNum = false;
1733
            AlterTable t = new AlterTable(tableNum);
1734
            if (!tableNum.getFieldsName().contains("AVOIR_F_START")) {
1735
                t.addColumn("AVOIR_F_START", "integer DEFAULT 0");
1736
                alterNum = true;
1737
            }
1738
            if (!tableNum.getFieldsName().contains("AVOIR_F_FORMAT")) {
1739
                t.addVarCharColumn("AVOIR_F_FORMAT", 48);
1740
                alterNum = true;
1741
            }
1742
 
1743
            if (alterNum) {
1744
                try {
1745
                    ds.execute(t.asString());
1746
                    tableNum.getSchema().updateVersion();
1747
                    tableNum.fetchFields();
1748
                } catch (SQLException ex) {
1749
                    throw new IllegalStateException("Erreur lors de l'ajout des champs sur la table NUMEROTATION_AUTO", ex);
1750
                }
1751
            }
1752
        }
1753
 
19 ilm 1754
        SQLTable tableArticle = root.getTable("ARTICLE");
1755
 
1756
        AlterTable t = new AlterTable(tableArticle);
1757
        boolean alterArticle = false;
1758
        if (!tableArticle.getFieldsName().contains("QTE_ACHAT")) {
1759
            t.addColumn("QTE_ACHAT", "integer DEFAULT 1");
1760
            alterArticle = true;
1761
        }
1762
        if (!tableArticle.getFieldsName().contains("DESCRIPTIF")) {
1763
            t.addVarCharColumn("DESCRIPTIF", 2048);
1764
            alterArticle = true;
1765
        }
1766
        if (!tableArticle.getFieldsName().contains("CODE_BARRE")) {
1767
            t.addVarCharColumn("CODE_BARRE", 256);
1768
            alterArticle = true;
1769
        }
1770
        if (!tableArticle.getFieldsName().contains("GESTION_STOCK")) {
1771
            t.addColumn("GESTION_STOCK", "boolean DEFAULT true");
1772
            alterArticle = true;
1773
        }
1774
        if (!tableArticle.getFieldsName().contains("CODE_DOUANIER")) {
1775
            t.addVarCharColumn("CODE_DOUANIER", 256);
1776
            alterArticle = true;
1777
        }
1778
        if (!tableArticle.getFieldsName().contains("QTE_MIN")) {
1779
            t.addColumn("QTE_MIN", "integer DEFAULT 1");
1780
            alterArticle = true;
1781
        }
1782
        if (!tableArticle.getFieldsName().contains("ID_DEVISE")) {
61 ilm 1783
            t.addForeignColumn("ID_DEVISE", root.findTable("DEVISE", true));
19 ilm 1784
            alterArticle = true;
1785
        }
1786
        if (!tableArticle.getFieldsName().contains("ID_FOURNISSEUR")) {
61 ilm 1787
            t.addForeignColumn("ID_FOURNISSEUR", root.findTable("FOURNISSEUR", true));
19 ilm 1788
            alterArticle = true;
1789
        }
1790
        if (!tableArticle.getFieldsName().contains("PV_U_DEVISE")) {
1791
            t.addColumn("PV_U_DEVISE", "bigint default 0");
1792
            alterArticle = true;
1793
        }
1794
        if (!tableArticle.getFieldsName().contains("ID_DEVISE_HA")) {
61 ilm 1795
            t.addForeignColumn("ID_DEVISE_HA", root.findTable("DEVISE", true));
19 ilm 1796
            alterArticle = true;
1797
        }
1798
        if (!tableArticle.getFieldsName().contains("PA_DEVISE")) {
1799
            t.addColumn("PA_DEVISE", "bigint default 0");
1800
            alterArticle = true;
1801
        }
1802
        if (!tableArticle.getFieldsName().contains("ID_PAYS")) {
61 ilm 1803
            t.addForeignColumn("ID_PAYS", root.findTable("PAYS", true));
19 ilm 1804
            alterArticle = true;
1805
        }
1806
        if (alterArticle) {
18 ilm 1807
            try {
19 ilm 1808
                ds.execute(t.asString());
1809
                tableArticle.getSchema().updateVersion();
18 ilm 1810
                tableArticle.fetchFields();
1811
            } catch (SQLException ex) {
19 ilm 1812
                throw new IllegalStateException("Erreur lors de l'ajout des champs sur la table ARTICLE", ex);
18 ilm 1813
            }
1814
        }
1815
 
19 ilm 1816
        // Création de la table Langue
1817
        boolean refetchRoot = false;
61 ilm 1818
        if (!root.contains("OBJECTIF_COMMERCIAL")) {
1819
 
1820
            SQLCreateTable createObjectif = new SQLCreateTable(root, "OBJECTIF_COMMERCIAL");
1821
            createObjectif.addVarCharColumn("MOIS", 32);
1822
            createObjectif.addColumn("ANNEE", "integer");
1823
            createObjectif.addColumn("MARGE_HT", "bigint DEFAULT 0");
1824
            createObjectif.addColumn("POURCENT_MARGE", "numeric (16,8)");
1825
            createObjectif.addColumn("CHIFFRE_AFFAIRE", "bigint DeFAULT 0");
1826
            createObjectif.addForeignColumn("COMMERCIAL");
1827
            try {
1828
                ds.execute(createObjectif.asString());
1829
                insertUndef(createObjectif);
1830
                tableDevis.getSchema().updateVersion();
1831
                refetchRoot = true;
1832
            } catch (SQLException ex) {
1833
                throw new IllegalStateException("Erreur lors de la création de la table OBJECTIF_COMMERCIAL", ex);
1834
            }
1835
 
1836
        }
1837
 
19 ilm 1838
        if (!root.contains("LANGUE")) {
18 ilm 1839
 
19 ilm 1840
            SQLCreateTable createLangue = new SQLCreateTable(root, "LANGUE");
1841
            createLangue.addVarCharColumn("CODE", 256);
1842
            createLangue.addVarCharColumn("NOM", 256);
1843
            createLangue.addVarCharColumn("CHEMIN", 256);
1844
            try {
1845
                ds.execute(createLangue.asString());
1846
                insertUndef(createLangue);
1847
                tableDevis.getSchema().updateVersion();
67 ilm 1848
                root.refetchTable(createLangue.getName());
19 ilm 1849
            } catch (SQLException ex) {
1850
                throw new IllegalStateException("Erreur lors de la création de la table LANGUE", ex);
1851
            }
1852
 
1853
            final String[] langs = new String[] { "FR", "Français", "EN", "Anglais", "SP", "Espagnol", "DE", "Allemand", "NL", "Néerlandais", "IT", "Italien" };
1854
            // ('FR', 'Français', 1.000), ('EN', 'Anglais', 2.000)
1855
            final List<String> values = new ArrayList<String>();
1856
            final SQLBase base = root.getBase();
1857
            for (int i = 0; i < langs.length; i += 2) {
1858
                final int order = values.size() + 1;
1859
                values.add("(" + base.quoteString(langs[i]) + ", " + base.quoteString(langs[i + 1]) + ", " + order + ")");
1860
            }
1861
            final String valuesStr = CollectionUtils.join(values, ", ");
1862
            final String insertVals = "INSERT INTO " + getTableName(createLangue).quote() + "(" + SQLBase.quoteIdentifier("CODE") + ", " + SQLBase.quoteIdentifier("NOM") + ", "
1863
                    + SQLBase.quoteIdentifier(SQLSyntax.ORDER_NAME) + ") VALUES" + valuesStr;
1864
            ds.execute(insertVals);
18 ilm 1865
        }
1866
 
21 ilm 1867
        // Création de la table Modéle
1868
        if (!root.contains("MODELE")) {
1869
 
1870
            SQLCreateTable createModele = new SQLCreateTable(root, "MODELE");
1871
            createModele.addVarCharColumn("NOM", 256);
61 ilm 1872
            createModele.addForeignColumn("ID_TYPE_MODELE", root.findTable("TYPE_MODELE", true));
21 ilm 1873
            try {
1874
                ds.execute(createModele.asString());
1875
                insertUndef(createModele);
1876
                tableDevis.getSchema().updateVersion();
1877
                refetchRoot = true;
1878
            } catch (SQLException ex) {
1879
                throw new IllegalStateException("Erreur lors de la création de la table MODELE", ex);
1880
            }
1881
        }
1882
 
1883
        // Création de la table Modéle
1884
        if (!root.contains("CONTACT_FOURNISSEUR")) {
1885
 
1886
            SQLCreateTable createModele = new SQLCreateTable(root, "CONTACT_FOURNISSEUR");
1887
            createModele.addVarCharColumn("NOM", 256);
1888
            createModele.addVarCharColumn("PRENOM", 256);
1889
            createModele.addVarCharColumn("TEL_DIRECT", 256);
1890
            createModele.addVarCharColumn("TEL_MOBILE", 256);
1891
            createModele.addVarCharColumn("EMAIL", 256);
1892
            createModele.addVarCharColumn("FAX", 256);
1893
            createModele.addVarCharColumn("FONCTION", 256);
1894
            createModele.addVarCharColumn("TEL_PERSONEL", 256);
1895
            createModele.addVarCharColumn("TEL_STANDARD", 256);
1896
            createModele.addForeignColumn("ID_TITRE_PERSONNEL", root.findTable("TITRE_PERSONNEL"));
1897
            createModele.addForeignColumn("ID_FOURNISSEUR", root.findTable("FOURNISSEUR"));
1898
 
1899
            try {
1900
                ds.execute(createModele.asString());
1901
                insertUndef(createModele);
1902
                tableDevis.getSchema().updateVersion();
1903
                refetchRoot = true;
1904
            } catch (SQLException ex) {
1905
                throw new IllegalStateException("Erreur lors de la création de la table MODELE", ex);
1906
            }
1907
        }
1908
 
19 ilm 1909
        // Création de la table Tarif
1910
        if (!root.contains("TARIF")) {
1911
 
1912
            SQLCreateTable createTarif = new SQLCreateTable(root, "TARIF");
1913
            createTarif.addVarCharColumn("NOM", 256);
61 ilm 1914
            createTarif.addForeignColumn("ID_DEVISE", root.findTable("DEVISE", true));
1915
            createTarif.addForeignColumn("ID_TAXE", root.findTable("TAXE", true));
19 ilm 1916
            createTarif.asString();
18 ilm 1917
            try {
19 ilm 1918
                ds.execute(createTarif.asString());
1919
                insertUndef(createTarif);
1920
                tableDevis.getSchema().updateVersion();
67 ilm 1921
                root.refetchTable(createTarif.getName());
18 ilm 1922
            } catch (SQLException ex) {
19 ilm 1923
                throw new IllegalStateException("Erreur lors de la création de la table TARIF", ex);
18 ilm 1924
            }
1925
        }
1926
 
19 ilm 1927
        // Création de la table article Tarif
1928
        if (!root.contains("ARTICLE_TARIF")) {
1929
 
1930
            SQLCreateTable createTarif = new SQLCreateTable(root, "ARTICLE_TARIF");
61 ilm 1931
            createTarif.addForeignColumn("ID_DEVISE", root.findTable("DEVISE", true));
1932
            createTarif.addForeignColumn("ID_TAXE", root.findTable("TAXE", true));
1933
            createTarif.addForeignColumn("ID_TARIF", root.findTable("TARIF", true));
1934
            createTarif.addForeignColumn("ID_ARTICLE", root.findTable("ARTICLE", true));
19 ilm 1935
            createTarif.addColumn("PV_HT", "bigint DEFAULT 0");
1936
            createTarif.addColumn("PV_TTC", "bigint DEFAULT 0");
1937
            createTarif.addColumn("PRIX_METRIQUE_VT_1", "bigint DEFAULT 0");
1938
            createTarif.addColumn("PRIX_METRIQUE_VT_2", "bigint DEFAULT 0");
1939
            createTarif.addColumn("PRIX_METRIQUE_VT_3", "bigint DEFAULT 0");
1940
            createTarif.asString();
18 ilm 1941
            try {
19 ilm 1942
                ds.execute(createTarif.asString());
1943
                insertUndef(createTarif);
1944
                tableDevis.getSchema().updateVersion();
61 ilm 1945
                refetchRoot = true;
19 ilm 1946
            } catch (SQLException ex) {
1947
                throw new IllegalStateException("Erreur lors de la création de la table ARTICLE_TARIF", ex);
18 ilm 1948
            }
1949
        }
1950
 
19 ilm 1951
        // Création de la table article Désignation
1952
        if (!root.contains("ARTICLE_DESIGNATION")) {
1953
 
1954
            SQLCreateTable createTarif = new SQLCreateTable(root, "ARTICLE_DESIGNATION");
61 ilm 1955
            createTarif.addForeignColumn("ID_ARTICLE", root.findTable("ARTICLE", true));
1956
            createTarif.addForeignColumn("ID_LANGUE", root.findTable("LANGUE", true));
19 ilm 1957
            createTarif.addVarCharColumn("NOM", 1024);
1958
            createTarif.asString();
18 ilm 1959
            try {
19 ilm 1960
                ds.execute(createTarif.asString());
1961
                insertUndef(createTarif);
1962
                tableDevis.getSchema().updateVersion();
61 ilm 1963
                refetchRoot = true;
19 ilm 1964
            } catch (SQLException ex) {
1965
                throw new IllegalStateException("Erreur lors de la création de la table ARTICLE_DESIGNATION", ex);
18 ilm 1966
            }
1967
        }
1968
 
61 ilm 1969
        if (!root.contains("UNITE_VENTE")) {
1970
 
1971
            SQLCreateTable createUnite = new SQLCreateTable(root, "UNITE_VENTE");
1972
            createUnite.addVarCharColumn("CODE", 32);
1973
            createUnite.addVarCharColumn("NOM", 256);
1974
            createUnite.addColumn("A_LA_PIECE", "boolean DEFAULT false");
1975
            createUnite.addVarCharColumn("INFOS", 256);
1976
            try {
1977
                ds.execute(createUnite.asString());
1978
                insertUndef(createUnite);
1979
                final String insert = "INSERT into "
1980
                        + getTableName(createUnite).quote()
1981
                        + "(\"CODE\",\"NOM\",\"A_LA_PIECE\",\"ORDRE\") VALUES('pièce','à la pièce',true,1),('m','mètres',false,2),('m²','mètres carré',false,3),('m3','mètres cube',false,4),('l','litres',false,5),('kg','kilos',false,6),('h','heures',false,7),('j','jours',false,8),('mois','mois',false,9)";
1982
                root.getDBSystemRoot().getDataSource().execute(insert);
1983
                tableDevis.getSchema().updateVersion();
1984
                refetchRoot = true;
1985
            } catch (SQLException ex) {
1986
                throw new IllegalStateException("Erreur lors de la création de la table UNITE_VENTE", ex);
1987
            }
1988
 
1989
        }
1990
 
1991
        // Chargement des tables fraichement créées
1992
        if (refetchRoot)
1993
            root.refetch();
1994
 
1995
        if (!tableArticle.getFieldsName().contains("ID_UNITE_VENTE")) {
1996
            AlterTable alterTableArticle = new AlterTable(tableArticle);
1997
            alterTableArticle.addForeignColumn("ID_UNITE_VENTE", root.findTable("UNITE_VENTE", true).getSQLName(), "ID", "2");
1998
            try {
1999
                ds.execute(alterTableArticle.asString());
2000
                tableArticle.getSchema().updateVersion();
2001
                tableArticle.fetchFields();
2002
            } catch (SQLException ex) {
2003
                throw new IllegalStateException("Erreur lors de l'ajout du champ UNITE_VENTE sur la table ARTICLE", ex);
2004
            }
2005
        }
2006
 
19 ilm 2007
        SQLTable tableVFElt = root.getTable("SAISIE_VENTE_FACTURE_ELEMENT");
41 ilm 2008
        addVenteEltField(tableVFElt, root);
18 ilm 2009
 
19 ilm 2010
        SQLTable tableDevisElt = root.getTable("DEVIS_ELEMENT");
41 ilm 2011
        addVenteEltField(tableDevisElt, root);
19 ilm 2012
 
2013
        SQLTable tableCmdElt = root.getTable("COMMANDE_CLIENT_ELEMENT");
41 ilm 2014
        addVenteEltField(tableCmdElt, root);
19 ilm 2015
 
2016
        SQLTable tableBonElt = root.getTable("BON_DE_LIVRAISON_ELEMENT");
41 ilm 2017
        addVenteEltField(tableBonElt, root);
19 ilm 2018
 
2019
        SQLTable tableAvoirElt = root.getTable("AVOIR_CLIENT_ELEMENT");
41 ilm 2020
        addVenteEltField(tableAvoirElt, root);
19 ilm 2021
 
2022
        SQLTable tableCmdFournElt = root.getTable("COMMANDE_ELEMENT");
41 ilm 2023
        addHAElementField(tableCmdFournElt, root);
90 ilm 2024
        if (root.contains("DEMANDE_PRIX_ELEMENT")) {
2025
            SQLTable tableDmdFournElt = root.getTable("DEMANDE_PRIX_ELEMENT");
2026
            addHAElementField(tableDmdFournElt, root);
2027
        }
19 ilm 2028
 
2029
        SQLTable tableBonRecptElt = root.getTable("BON_RECEPTION_ELEMENT");
41 ilm 2030
        addHAElementField(tableBonRecptElt, root);
19 ilm 2031
 
2032
        SQLTable tableBonRecpt = root.getTable("BON_RECEPTION");
2033
        addDeviseHAField(tableBonRecpt, root);
2034
 
2035
        SQLTable tableCommande = root.getTable("COMMANDE");
2036
        addDeviseHAField(tableCommande, root);
2037
 
67 ilm 2038
        patchFieldElt1Dot3(root.getTable("ARTICLE"), root);
2039
        patchFieldElt1Dot3(root.getTable("ARTICLE_TARIF"), root);
2040
 
41 ilm 2041
        if (!tableCommande.getFieldsName().contains("ID_ADRESSE")) {
2042
            AlterTable alterCmd = new AlterTable(tableCommande);
61 ilm 2043
            alterCmd.addForeignColumn("ID_ADRESSE", root.findTable("ADRESSE", true));
41 ilm 2044
            try {
2045
                ds.execute(alterCmd.asString());
2046
                tableCommande.getSchema().updateVersion();
2047
            } catch (SQLException ex) {
2048
                throw new IllegalStateException("Erreur lors de l'ajout des champs sur la table COMMANDE", ex);
2049
            }
2050
 
2051
        }
61 ilm 2052
        if (!tableCommande.getFieldsName().contains("ID_CLIENT")) {
2053
            AlterTable alterCmd = new AlterTable(tableCommande);
2054
            alterCmd.addForeignColumn("ID_CLIENT", root.findTable("CLIENT"));
2055
            try {
2056
                ds.execute(alterCmd.asString());
2057
                tableCommande.getSchema().updateVersion();
2058
            } catch (SQLException ex) {
2059
                throw new IllegalStateException("Erreur lors de l'ajout des champs sur la table COMMANDE", ex);
2060
            }
41 ilm 2061
 
61 ilm 2062
        }
2063
 
19 ilm 2064
        {
21 ilm 2065
            addTotalDeviseField(tableDevis, root);
2066
            addModeleField(tableDevis, root);
2067
 
19 ilm 2068
            SQLTable tableVF = root.getTable("SAISIE_VENTE_FACTURE");
2069
            addTotalDeviseField(tableVF, root);
21 ilm 2070
            addModeleField(tableVF, root);
19 ilm 2071
 
2072
            addTotalDeviseField(tableDevis, root);
21 ilm 2073
            addModeleField(tableDevis, root);
19 ilm 2074
 
2075
            SQLTable tableCmd = root.getTable("COMMANDE_CLIENT");
2076
            addTotalDeviseField(tableCmd, root);
21 ilm 2077
            addModeleField(tableCmd, root);
19 ilm 2078
 
2079
            SQLTable tableBon = root.getTable("BON_DE_LIVRAISON");
2080
            addTotalDeviseField(tableBon, root);
21 ilm 2081
            addModeleField(tableBon, root);
19 ilm 2082
 
2083
            SQLTable tableAvoir = root.getTable("AVOIR_CLIENT");
2084
            addTotalDeviseField(tableAvoir, root);
21 ilm 2085
            addModeleField(tableAvoir, root);
18 ilm 2086
        }
19 ilm 2087
        // Change client
2088
        {
2089
            SQLTable tableClient = root.getTable("CLIENT");
18 ilm 2090
 
19 ilm 2091
            AlterTable tClient = new AlterTable(tableClient);
2092
            boolean alterClient = false;
2093
 
2094
            if (!tableClient.getFieldsName().contains("ID_TARIF")) {
61 ilm 2095
                tClient.addForeignColumn("ID_TARIF", root.findTable("TARIF", true));
19 ilm 2096
                alterClient = true;
2097
            }
2098
            if (!tableClient.getFieldsName().contains("ID_PAYS")) {
61 ilm 2099
                tClient.addForeignColumn("ID_PAYS", root.findTable("PAYS", true));
19 ilm 2100
                alterClient = true;
2101
            }
2102
            if (!tableClient.getFieldsName().contains("ID_LANGUE")) {
61 ilm 2103
                tClient.addForeignColumn("ID_LANGUE", root.findTable("LANGUE", true));
19 ilm 2104
                alterClient = true;
2105
            }
2106
 
2107
            if (!tableClient.getFieldsName().contains("ID_DEVISE")) {
61 ilm 2108
                tClient.addForeignColumn("ID_DEVISE", root.findTable("DEVISE", true));
19 ilm 2109
                alterClient = true;
2110
            }
2111
            if (alterClient) {
2112
                try {
2113
                    ds.execute(tClient.asString());
2114
                    tableClient.getSchema().updateVersion();
2115
                } catch (SQLException ex) {
2116
                    throw new IllegalStateException("Erreur lors de l'ajout des champs sur la table CLIENT", ex);
2117
                }
2118
            }
18 ilm 2119
        }
2120
 
19 ilm 2121
        // Change Pays
2122
        {
2123
            SQLTable tablePays = root.getTable("PAYS");
2124
 
2125
            AlterTable tPays = new AlterTable(tablePays);
2126
            boolean alterPays = false;
2127
 
2128
            if (!tablePays.getFieldsName().contains("ID_TARIF")) {
61 ilm 2129
                tPays.addForeignColumn("ID_TARIF", root.findTable("TARIF", true));
19 ilm 2130
                alterPays = true;
2131
            }
2132
            if (!tablePays.getFieldsName().contains("ID_LANGUE")) {
61 ilm 2133
                tPays.addForeignColumn("ID_LANGUE", root.findTable("LANGUE", true));
19 ilm 2134
                alterPays = true;
2135
            }
2136
            if (alterPays) {
2137
                try {
2138
                    ds.execute(tPays.asString());
2139
                    tablePays.getSchema().updateVersion();
2140
                } catch (SQLException ex) {
2141
                    throw new IllegalStateException("Erreur lors de l'ajout des champs sur la table PAYS", ex);
2142
                }
2143
            }
2144
        }
2145
        // Change Commande
2146
        {
2147
            SQLTable tableCmd = root.getTable("COMMANDE");
2148
 
2149
            AlterTable tCmd = new AlterTable(tableCmd);
2150
            boolean alterCmd = false;
2151
 
2152
            if (!tableCmd.getFieldsName().contains("EN_COURS")) {
2153
                tCmd.addColumn("EN_COURS", "boolean default true");
2154
                alterCmd = true;
2155
            }
2156
            if (alterCmd) {
2157
                try {
2158
                    ds.execute(tCmd.asString());
2159
                    tableCmd.getSchema().updateVersion();
2160
                } catch (SQLException ex) {
2161
                    throw new IllegalStateException("Erreur lors de l'ajout des champs sur la table COMMANDE", ex);
2162
                }
2163
            }
2164
        }
65 ilm 2165
 
2166
        // Change VF
2167
        {
2168
            SQLTable tableVenteFacture = root.getTable("SAISIE_VENTE_FACTURE");
2169
 
2170
            AlterTable tVF = new AlterTable(tableVenteFacture);
2171
            boolean alterVF = false;
2172
 
2173
            if (!tableVenteFacture.getFieldsName().contains("ID_TAXE_PORT")) {
2174
                tVF.addForeignColumn("ID_TAXE_PORT", root.findTable("TAXE"));
2175
                alterVF = true;
2176
            }
2177
            if (alterVF) {
2178
                try {
2179
                    ds.execute(tVF.asString());
2180
                    tableVenteFacture.getSchema().updateVersion();
2181
                } catch (SQLException ex) {
2182
                    throw new IllegalStateException("Erreur lors de l'ajout des champs sur la table SAISIE_VENTE_FACTURE", ex);
2183
                }
2184
            }
2185
        }
2186
 
19 ilm 2187
        // Change Fournisseur
2188
        {
2189
            SQLTable tableFournisseur = root.getTable("FOURNISSEUR");
2190
 
2191
            AlterTable tFourn = new AlterTable(tableFournisseur);
2192
            boolean alterFourn = false;
2193
 
2194
            if (!tableFournisseur.getFieldsName().contains("ID_LANGUE")) {
61 ilm 2195
                tFourn.addForeignColumn("ID_LANGUE", root.findTable("LANGUE", true));
19 ilm 2196
                alterFourn = true;
2197
            }
2198
            if (!tableFournisseur.getFieldsName().contains("ID_DEVISE")) {
61 ilm 2199
                tFourn.addForeignColumn("ID_DEVISE", root.findTable("DEVISE", true));
19 ilm 2200
                alterFourn = true;
2201
            }
2202
            if (!tableFournisseur.getFieldsName().contains("RESPONSABLE")) {
2203
                tFourn.addVarCharColumn("RESPONSABLE", 256);
2204
                alterFourn = true;
2205
            }
2206
            if (!tableFournisseur.getFieldsName().contains("TEL_P")) {
2207
                tFourn.addVarCharColumn("TEL_P", 256);
2208
                alterFourn = true;
2209
            }
2210
            if (!tableFournisseur.getFieldsName().contains("MAIL")) {
2211
                tFourn.addVarCharColumn("MAIL", 256);
2212
                alterFourn = true;
2213
            }
2214
            if (!tableFournisseur.getFieldsName().contains("INFOS")) {
2215
                tFourn.addVarCharColumn("INFOS", 2048);
2216
                alterFourn = true;
2217
            }
2218
 
2219
            if (alterFourn) {
2220
                try {
2221
                    ds.execute(tFourn.asString());
2222
                    tableFournisseur.getSchema().updateVersion();
2223
                } catch (SQLException ex) {
2224
                    throw new IllegalStateException("Erreur lors de l'ajout des champs sur la table FOURNISSEUR", ex);
2225
                }
2226
            }
2227
        }
2228
 
41 ilm 2229
        updateN4DS(root);
2230
 
19 ilm 2231
        root.refetch();
18 ilm 2232
    }
2233
 
41 ilm 2234
    /**
2235
     * Mise à jour du schéma pour N4DS
2236
     *
2237
     * @param root
2238
     * @throws SQLException
2239
     */
2240
    private void updateN4DS(DBRoot root) throws SQLException {
2241
 
2242
        {
2243
            SQLTable table = root.findTable("INFOS_SALARIE_PAYE");
2244
            boolean alter = false;
2245
            AlterTable t = new AlterTable(table);
2246
            if (!table.getFieldsName().contains("CODE_AT")) {
2247
                t.addVarCharColumn("CODE_AT", 18);
2248
                alter = true;
2249
            }
2250
            if (!table.getFieldsName().contains("CODE_SECTION_AT")) {
2251
                t.addVarCharColumn("CODE_SECTION_AT", 18);
2252
                alter = true;
2253
            }
2254
 
2255
            if (alter) {
2256
                try {
2257
                    table.getBase().getDataSource().execute(t.asString());
2258
                    table.getSchema().updateVersion();
2259
                    table.fetchFields();
2260
                } catch (SQLException ex) {
2261
                    throw new IllegalStateException("Erreur lors de l'ajout des champs à la table " + table.getName(), ex);
2262
                }
2263
            }
2264
 
2265
        }
2266
 
2267
        if (!root.contains("CODE_STATUT_CAT_CONV")) {
2268
 
2269
            SQLCreateTable createTarif = new SQLCreateTable(root, "CODE_STATUT_CAT_CONV");
2270
 
2271
            createTarif.addVarCharColumn("CODE", 6);
2272
            createTarif.addVarCharColumn("NOM", 256);
2273
            createTarif.asString();
2274
            try {
2275
                root.getBase().getDataSource().execute(createTarif.asString());
2276
                insertUndef(createTarif);
2277
 
2278
                String insert = "INSERT into " + getTableName(createTarif).quote() + "(\"CODE\",\"NOM\") VALUES ";
2279
                insert += " ('01','agriculteur salarié de son exploitation')";
2280
                insert += ", ('02','artisan ou commerçant salarié de son entreprise')";
2281
                insert += ", ('03','cadre dirigeant (votant au collège employeur des élections prud''''hommales)')";
2282
                insert += ", ('04','autres cadres au sens de la convention collective (ou du statut pour les régimes spéciaux)')";
2283
                insert += ", ('05','profession intermédiaire (technicien, contremaître, agent de maîtrise, clergé)')";
2284
                insert += ", ('06','employé administratif d''''entreprise, de commerce, agent de service')";
2285
                insert += ", ('07','ouvriers qualifiés et non qualifiés y compris ouvriers agricoles');";
2286
                createTarif.getRoot().getDBSystemRoot().getDataSource().execute(insert);
2287
 
2288
                root.getSchema().updateVersion();
2289
                root.refetch();
2290
            } catch (SQLException ex) {
2291
                throw new IllegalStateException("Erreur lors de la création de la table CODE_STATUT_CAT_CONV", ex);
2292
            }
2293
        }
2294
 
2295
        // Création de la table Modéle
2296
        if (!root.contains("CONTACT_ADMINISTRATIF")) {
2297
 
2298
            SQLCreateTable createModele = new SQLCreateTable(root, "CONTACT_ADMINISTRATIF");
2299
            createModele.addVarCharColumn("NOM", 256);
2300
            createModele.addVarCharColumn("PRENOM", 256);
2301
            createModele.addVarCharColumn("TEL_DIRECT", 256);
2302
            createModele.addVarCharColumn("TEL_MOBILE", 256);
2303
            createModele.addVarCharColumn("EMAIL", 256);
2304
            createModele.addVarCharColumn("FAX", 256);
2305
            createModele.addVarCharColumn("FONCTION", 256);
2306
            createModele.addVarCharColumn("TEL_PERSONEL", 256);
2307
            createModele.addVarCharColumn("TEL_STANDARD", 256);
2308
            createModele.addForeignColumn("ID_TITRE_PERSONNEL", root.findTable("TITRE_PERSONNEL"));
2309
            createModele.addColumn("N4DS", "boolean DEFAULT false");
2310
 
2311
            try {
2312
                root.getBase().getDataSource().execute(createModele.asString());
2313
                insertUndef(createModele);
2314
                root.getSchema().updateVersion();
2315
            } catch (SQLException ex) {
2316
                throw new IllegalStateException("Erreur lors de la création de la table MODELE", ex);
2317
            }
2318
        }
2319
 
2320
        {
61 ilm 2321
            SQLTable tableContrat = root.findTable("CONTRAT_SALARIE", true);
41 ilm 2322
            boolean alter2 = false;
2323
            AlterTable t2 = new AlterTable(tableContrat);
2324
            // UGRR
2325
            if (!tableContrat.getFieldsName().contains("CODE_IRC_UGRR")) {
2326
                t2.addVarCharColumn("CODE_IRC_UGRR", 18);
2327
                alter2 = true;
2328
            }
2329
            if (!tableContrat.getFieldsName().contains("NUMERO_RATTACHEMENT_UGRR")) {
2330
                t2.addVarCharColumn("NUMERO_RATTACHEMENT_UGRR", 64);
2331
                alter2 = true;
2332
            }
2333
            // UGRC
2334
            if (!tableContrat.getFieldsName().contains("CODE_IRC_UGRC")) {
2335
                t2.addVarCharColumn("CODE_IRC_UGRC", 18);
2336
                alter2 = true;
2337
            }
2338
            if (!tableContrat.getFieldsName().contains("NUMERO_RATTACHEMENT_UGRC")) {
2339
                t2.addVarCharColumn("NUMERO_RATTACHEMENT_UGRC", 64);
2340
                alter2 = true;
2341
            }
2342
 
2343
            // Retraite Compl
2344
            if (!tableContrat.getFieldsName().contains("CODE_IRC_RETRAITE")) {
2345
                t2.addVarCharColumn("CODE_IRC_RETRAITE", 18);
2346
                alter2 = true;
2347
            }
2348
            if (!tableContrat.getFieldsName().contains("NUMERO_RATTACHEMENT_RETRAITE")) {
2349
                t2.addVarCharColumn("NUMERO_RATTACHEMENT_RETRAITE", 64);
2350
                alter2 = true;
2351
            }
2352
 
2353
            if (!tableContrat.getFieldsName().contains("ID_CODE_STATUT_CAT_CONV")) {
61 ilm 2354
                t2.addForeignColumn("ID_CODE_STATUT_CAT_CONV", root.findTable("CODE_STATUT_CAT_CONV", true));
41 ilm 2355
                alter2 = true;
2356
            }
2357
 
2358
            if (alter2) {
2359
                try {
2360
                    tableContrat.getBase().getDataSource().execute(t2.asString());
2361
                    tableContrat.getSchema().updateVersion();
2362
                    tableContrat.fetchFields();
2363
                } catch (SQLException ex) {
2364
                    throw new IllegalStateException("Erreur lors de l'ajout des champs à la table " + tableContrat.getName(), ex);
2365
                }
2366
            }
2367
 
2368
        }
2369
    }
2370
 
19 ilm 2371
    private void addDeviseHAField(SQLTable table, DBRoot root) throws SQLException {
2372
        boolean alter = false;
2373
        AlterTable t = new AlterTable(table);
2374
        if (!table.getFieldsName().contains("ID_DEVISE")) {
61 ilm 2375
            t.addForeignColumn("ID_DEVISE", root.findTable("DEVISE", true));
19 ilm 2376
            alter = true;
2377
        }
2378
 
2379
        if (!table.getFieldsName().contains("T_DEVISE")) {
2380
            t.addColumn("T_DEVISE", "bigint default 0");
2381
            alter = true;
2382
        }
2383
 
2384
        if (alter) {
2385
            try {
2386
                table.getBase().getDataSource().execute(t.asString());
2387
                table.getSchema().updateVersion();
2388
                table.fetchFields();
2389
            } catch (SQLException ex) {
2390
                throw new IllegalStateException("Erreur lors de l'ajout des champs à la table " + table.getName(), ex);
2391
            }
2392
        }
2393
 
2394
    }
2395
 
67 ilm 2396
    private void patchFieldElt1Dot3(SQLTable table, DBRoot root) {
2397
 
2398
        List<String> cols = Arrays.asList("PV_HT", "PA_DEVISE_T", "T_PV_HT", "T_PA_TTC", "T_PA_HT", "PA_HT", "T_PV_TTC", "PRIX_METRIQUE_HA_2", "PRIX_METRIQUE_HA_1", "PRIX_METRIQUE_HA_3",
73 ilm 2399
                "PRIX_METRIQUE_VT_2", "PRIX_METRIQUE_VT_1", "MONTANT_HT", "MONTANT_INITIAL", "PRIX_METRIQUE_VT_3", "MARGE_HT", "PA_DEVISE", "PV_U_DEVISE", "PV_T_DEVISE", "PV_TTC", "TARIF_Q18_HT",
90 ilm 2400
                "T_PRIX_FINAL_TTC", "PRIX_FINAL_TTC", "PV_UNIT_HT", "PREBILAN", "MARGE_PREBILAN_HT");
67 ilm 2401
 
73 ilm 2402
        if ((table.contains("PV_HT") && table.getField("PV_HT").getType().getDecimalDigits() == 0) || (table.contains("PV_UNIT_HT") && table.getField("PV_UNIT_HT").getType().getDecimalDigits() == 0)) {
67 ilm 2403
            AlterTable t = new AlterTable(table);
2404
            UpdateBuilder builder = new UpdateBuilder(table);
73 ilm 2405
            List<UpdateBuilder> builds = new ArrayList<UpdateBuilder>();
67 ilm 2406
            for (String field : cols) {
2407
                if (table.contains(field)) {
73 ilm 2408
                    UpdateBuilder builderNonNull = new UpdateBuilder(table);
2409
                    builderNonNull.set(field, "0");
2410
                    builderNonNull.setWhere(new Where(table.getField(field), "=", (Object) null));
2411
                    builds.add(builderNonNull);
2412
 
67 ilm 2413
                    builder.set(field, table.getField(field).getSQLName().getRest().quote() + "/100");
2414
                    if (field.contains("TTC")) {
2415
                        t.alterColumn(field, EnumSet.allOf(Properties.class), "numeric(16,2)", "0", false);
2416
                    } else {
2417
                        t.alterColumn(field, EnumSet.allOf(Properties.class), "numeric(16,6)", "0", false);
2418
                    }
2419
                }
2420
            }
2421
 
2422
            try {
73 ilm 2423
 
2424
                for (UpdateBuilder updateBuilder : builds) {
2425
                    table.getBase().getDataSource().execute(updateBuilder.asString());
2426
                }
2427
 
67 ilm 2428
                table.getBase().getDataSource().execute(t.asString());
2429
                table.getSchema().updateVersion();
2430
                table.fetchFields();
2431
            } catch (SQLException ex) {
2432
                throw new IllegalStateException("Erreur lors de l'ajout des champs à la table " + table.getName(), ex);
2433
            }
2434
 
2435
            String req2 = builder.asString();
2436
            root.getDBSystemRoot().getDataSource().execute(req2);
2437
        }
2438
 
2439
    }
2440
 
83 ilm 2441
    private void patchFieldElt1Dot4(SQLTable table, DBRoot root) {
2442
 
2443
        if (!table.contains("MONTANT_REMISE")) {
2444
            AlterTable t = new AlterTable(table);
2445
            t.alterColumn("POURCENT_REMISE", EnumSet.allOf(Properties.class), "numeric(12,8)", "0", true);
2446
            t.addColumn("MONTANT_REMISE", "numeric (16,8)");
2447
 
2448
            try {
2449
 
2450
                table.getBase().getDataSource().execute(t.asString());
2451
                table.getSchema().updateVersion();
2452
                table.fetchFields();
2453
            } catch (SQLException ex) {
2454
                throw new IllegalStateException("Erreur lors de l'ajout des champs à la table " + table.getName(), ex);
2455
            }
2456
        }
2457
        if (!table.contains("NIVEAU")) {
2458
            AlterTable t = new AlterTable(table);
2459
            t.addIntegerColumn("NIVEAU", 1);
2460
            try {
2461
                table.getBase().getDataSource().execute(t.asString());
2462
                table.getSchema().updateVersion();
2463
                table.fetchFields();
2464
            } catch (SQLException ex) {
2465
                throw new IllegalStateException("Erreur lors de l'ajout du niveau à la table " + table.getName(), ex);
2466
            }
2467
        }
2468
 
2469
    }
2470
 
41 ilm 2471
    private void addHAElementField(SQLTable table, DBRoot root) throws SQLException {
67 ilm 2472
 
19 ilm 2473
        boolean alter = false;
2474
        AlterTable t = new AlterTable(table);
2475
        if (!table.getFieldsName().contains("QTE_ACHAT")) {
2476
            t.addColumn("QTE_ACHAT", "integer DEFAULT 1");
2477
            alter = true;
2478
        }
61 ilm 2479
        if (!table.getFieldsName().contains("QTE_UNITAIRE")) {
2480
            t.addColumn("QTE_UNITAIRE", "numeric(16,6) DEFAULT 1");
2481
            alter = true;
2482
        }
2483
        if (!table.getFieldsName().contains("ID_UNITE_VENTE")) {
2484
            t.addForeignColumn("ID_UNITE_VENTE", root.findTable("UNITE_VENTE", true).getSQLName(), "ID", "2");
2485
            alter = true;
2486
        }
2487
        if (!table.getFieldsName().contains("ID_ARTICLE")) {
2488
            t.addForeignColumn("ID_ARTICLE", root.findTable("ARTICLE", true));
2489
            alter = true;
2490
        }
19 ilm 2491
        if (!table.getFieldsName().contains("PA_DEVISE")) {
2492
            t.addColumn("PA_DEVISE", "bigint default 0");
2493
            alter = true;
2494
        }
2495
        if (!table.getFieldsName().contains("ID_DEVISE")) {
61 ilm 2496
            t.addForeignColumn("ID_DEVISE", root.findTable("DEVISE", true));
19 ilm 2497
            alter = true;
2498
        }
2499
 
2500
        if (!table.getFieldsName().contains("PA_DEVISE_T")) {
2501
            t.addColumn("PA_DEVISE_T", "bigint default 0");
2502
            alter = true;
2503
        }
2504
 
67 ilm 2505
        // if (!table.getFieldsName().contains("POURCENT_REMISE")) {
2506
        // t.addColumn("POURCENT_REMISE", "numeric(16,2) DEFAULT 0");
2507
        // alter = true;
2508
        // }
2509
 
19 ilm 2510
        if (alter) {
2511
            try {
2512
                table.getBase().getDataSource().execute(t.asString());
2513
                table.getSchema().updateVersion();
2514
                table.fetchFields();
2515
            } catch (SQLException ex) {
2516
                throw new IllegalStateException("Erreur lors de l'ajout des champs à la table " + table.getName(), ex);
2517
            }
2518
        }
67 ilm 2519
        patchFieldElt1Dot3(table, root);
19 ilm 2520
    }
2521
 
21 ilm 2522
    private void addModeleField(SQLTable table, DBRoot root) throws SQLException {
2523
        boolean alter = false;
2524
        AlterTable t = new AlterTable(table);
2525
        if (!table.getFieldsName().contains("ID_MODELE")) {
2526
            t.addForeignColumn("ID_MODELE", root.findTable("MODELE"));
2527
            alter = true;
2528
        }
2529
 
2530
        if (alter) {
2531
            try {
2532
                table.getBase().getDataSource().execute(t.asString());
2533
                table.getSchema().updateVersion();
2534
                table.fetchFields();
2535
            } catch (SQLException ex) {
2536
                throw new IllegalStateException("Erreur lors de l'ajout des champs à la table " + table.getName(), ex);
2537
            }
2538
        }
2539
    }
2540
 
19 ilm 2541
    private void addTotalDeviseField(SQLTable table, DBRoot root) throws SQLException {
2542
        boolean alter = false;
2543
        AlterTable t = new AlterTable(table);
2544
        if (!table.getFieldsName().contains("T_DEVISE")) {
2545
            t.addColumn("T_DEVISE", "bigint default 0");
2546
            alter = true;
2547
        } else {
2548
            table.getBase().getDataSource().execute("UPDATE " + table.getSQLName().quote() + " SET \"T_DEVISE\"=0 WHERE \"T_DEVISE\" IS NULL");
2549
            t.alterColumn("T_DEVISE", EnumSet.allOf(Properties.class), "bigint", "0", false);
2550
        }
2551
        if (!table.getFieldsName().contains("T_POIDS")) {
2552
            t.addColumn("T_POIDS", "real default 0");
2553
            alter = true;
2554
        }
2555
        if (!table.getFieldsName().contains("ID_TARIF")) {
61 ilm 2556
            t.addForeignColumn("ID_TARIF", root.findTable("TARIF", true));
19 ilm 2557
            alter = true;
2558
        }
2559
 
2560
        if (alter) {
2561
            try {
2562
                table.getBase().getDataSource().execute(t.asString());
2563
                table.getSchema().updateVersion();
2564
                table.fetchFields();
2565
            } catch (SQLException ex) {
2566
                throw new IllegalStateException("Erreur lors de l'ajout des champs à la table " + table.getName(), ex);
2567
            }
2568
        }
2569
    }
2570
 
41 ilm 2571
    private void addVenteEltField(SQLTable table, DBRoot root) throws SQLException {
19 ilm 2572
 
2573
        boolean alter = false;
2574
        AlterTable t = new AlterTable(table);
2575
        if (!table.getFieldsName().contains("QTE_ACHAT")) {
2576
            t.addColumn("QTE_ACHAT", "integer DEFAULT 1");
2577
            alter = true;
2578
        }
61 ilm 2579
        if (!table.getFieldsName().contains("QTE_UNITAIRE")) {
2580
            t.addColumn("QTE_UNITAIRE", "numeric(16,6) DEFAULT 1");
2581
            alter = true;
2582
        }
2583
        if (!table.getFieldsName().contains("ID_UNITE_VENTE")) {
2584
            t.addForeignColumn("ID_UNITE_VENTE", root.findTable("UNITE_VENTE", true).getSQLName(), "ID", "2");
2585
            alter = true;
2586
        }
2587
        if (!table.getFieldsName().contains("ID_ARTICLE")) {
2588
            t.addForeignColumn("ID_ARTICLE", root.findTable("ARTICLE", true));
2589
            alter = true;
2590
        }
19 ilm 2591
        if (!table.getFieldsName().contains("CODE_DOUANIER")) {
2592
            t.addVarCharColumn("CODE_DOUANIER", 256);
2593
            alter = true;
2594
        }
21 ilm 2595
        if (!table.getFieldsName().contains("DESCRIPTIF")) {
2596
            t.addVarCharColumn("DESCRIPTIF", 2048);
2597
            alter = true;
2598
        }
19 ilm 2599
        if (!table.getFieldsName().contains("ID_PAYS")) {
61 ilm 2600
            t.addForeignColumn("ID_PAYS", root.findTable("PAYS", true));
19 ilm 2601
            alter = true;
2602
        }
21 ilm 2603
        if (!table.getFieldsName().contains("MARGE_HT")) {
2604
            t.addColumn("MARGE_HT", "bigint default 0");
2605
            alter = true;
2606
        }
19 ilm 2607
 
2608
        if (!table.getFieldsName().contains("ID_DEVISE")) {
61 ilm 2609
            t.addForeignColumn("ID_DEVISE", root.findTable("DEVISE", true));
19 ilm 2610
            alter = true;
2611
        }
2612
        if (!table.getFieldsName().contains("PV_U_DEVISE")) {
2613
            t.addColumn("PV_U_DEVISE", "bigint default 0");
2614
            alter = true;
2615
        }
2616
        if (!table.getFieldsName().contains("POURCENT_REMISE")) {
2617
            t.addColumn("POURCENT_REMISE", "numeric(6,2) default 0");
2618
            alter = true;
2619
        }
2620
        if (!table.getFieldsName().contains("PV_T_DEVISE")) {
2621
            t.addColumn("PV_T_DEVISE", "bigint default 0");
2622
            alter = true;
2623
        }
2624
        if (!table.getFieldsName().contains("TAUX_DEVISE")) {
2625
            t.addColumn("TAUX_DEVISE", "numeric (16,8) DEFAULT 1");
2626
            alter = true;
2627
        }
80 ilm 2628
 
2629
        if (!table.getFieldsName().contains("POIDS_COLIS_NET")) {
2630
            t.addColumn("POIDS_COLIS_NET", "numeric (16,8) DEFAULT 1");
2631
            alter = true;
2632
        }
2633
 
2634
        if (!table.getFieldsName().contains("T_POIDS_COLIS_NET")) {
2635
            t.addColumn("T_POIDS_COLIS_NET", "numeric (16,8) DEFAULT 1");
2636
            alter = true;
2637
        }
2638
 
2639
        if (!table.getFieldsName().contains("NB_COLIS")) {
2640
            t.addColumn("NB_COLIS", "integer DEFAULT 0");
2641
            alter = true;
2642
        }
19 ilm 2643
        if (alter) {
2644
            try {
2645
                root.getDBSystemRoot().getDataSource().execute(t.asString());
2646
                table.getSchema().updateVersion();
2647
                table.fetchFields();
2648
            } catch (SQLException ex) {
2649
                throw new IllegalStateException("Erreur lors de l'ajout des champs à la table " + table.getName(), ex);
2650
            }
2651
        }
67 ilm 2652
        patchFieldElt1Dot3(table, root);
19 ilm 2653
    }
2654
 
61 ilm 2655
    private void updateSocieteSchema(final DBRoot root) throws IOException, Exception {
19 ilm 2656
        final DBSystemRoot sysRoot = root.getDBSystemRoot();
2657
        final SQLDataSource ds = sysRoot.getDataSource();
2658
        System.out.println("InstallationPanel.InstallationPanel() UPDATE COMMERCIAL " + root);
2659
        // Fix commercial Ordre
57 ilm 2660
 
19 ilm 2661
        SQLTable tableCommercial = root.getTable("COMMERCIAL");
2662
        CorrectOrder orderCorrect = new CorrectOrder(sysRoot);
2663
        orderCorrect.change(tableCommercial);
2664
 
65 ilm 2665
        new AddFK(sysRoot).changeAll(root);
80 ilm 2666
        sysRoot.reload(Collections.singleton(root.getName()));
19 ilm 2667
 
2668
        try {
2669
            // Add article
2670
            final SQLTable tableArticle = root.getTable("ARTICLE");
2671
            if (!tableArticle.getFieldsName().contains("INFOS")) {
2672
                AlterTable t = new AlterTable(tableArticle);
2673
                t.addVarCharColumn("INFOS", 2048);
2674
                try {
2675
                    ds.execute(t.asString());
2676
                } catch (Exception ex) {
2677
                    throw new IllegalStateException("Erreur lors de l'ajout du champ INFO à la table ARTICLE", ex);
2678
                }
2679
            }
2680
 
2681
            if (sysRoot.getServer().getSQLSystem().equals(SQLSystem.POSTGRESQL)) {
2682
                // Fix Caisse serial
2683
                SQLTable tableCaisse = root.getTable("CAISSE");
2684
 
2685
                FixSerial f = new FixSerial(sysRoot);
2686
                try {
2687
                    f.change(tableCaisse);
2688
                } catch (SQLException e2) {
2689
                    throw new IllegalStateException("Erreur lors la mise à jours des sequences de la table CAISSE", e2);
2690
                }
2691
            }
2692
            System.out.println("InstallationPanel.InstallationPanel() UPDATE TICKET_CAISSE " + root);
2693
            // add Mvt on Ticket
2694
            SQLTable tableTicket = root.getTable("TICKET_CAISSE");
2695
            if (!tableTicket.getFieldsName().contains("ID_MOUVEMENT")) {
2696
                AlterTable t = new AlterTable(tableTicket);
2697
                t.addForeignColumn("ID_MOUVEMENT", root.getTable("MOUVEMENT"));
2698
                try {
2699
                    ds.execute(t.asString());
2700
                } catch (Exception ex) {
2701
                    throw new IllegalStateException("Erreur lors de l'ajout du champ ID_MOUVEMENT à la table TICKET_CAISSE", ex);
2702
                }
2703
            }
2704
 
2705
            // Check type de reglement
2706
 
2707
            System.out.println("InstallationPanel.InstallationPanel() UPDATE TYPE_REGLEMENT " + root);
2708
            SQLTable tableReglmt = root.getTable("TYPE_REGLEMENT");
2709
            SQLSelect sel = new SQLSelect(tableReglmt.getBase());
2710
            sel.addSelect(tableReglmt.getKey());
2711
            sel.setWhere(new Where(tableReglmt.getField("NOM"), "=", "Virement"));
2712
            List<Number> l = (List<Number>) ds.executeCol(sel.asString());
2713
            if (l.size() == 0) {
2714
                SQLRowValues rowVals = new SQLRowValues(tableReglmt);
2715
                rowVals.put("NOM", "Virement");
2716
                rowVals.put("COMPTANT", Boolean.FALSE);
2717
                rowVals.put("ECHEANCE", Boolean.FALSE);
2718
                try {
2719
                    rowVals.commit();
2720
                } catch (SQLException e) {
2721
                    throw new IllegalStateException("Erreur lors de l'ajout du type de paiement par virement", e);
2722
                }
2723
            }
2724
 
67 ilm 2725
            SQLSelect sel2 = new SQLSelect();
19 ilm 2726
            sel2.addSelect(tableReglmt.getKey());
2727
            sel2.setWhere(new Where(tableReglmt.getField("NOM"), "=", "CESU"));
67 ilm 2728
            @SuppressWarnings("unchecked")
19 ilm 2729
            List<Number> l2 = (List<Number>) ds.executeCol(sel2.asString());
2730
            if (l2.size() == 0) {
2731
                SQLRowValues rowVals = new SQLRowValues(tableReglmt);
2732
                rowVals.put("NOM", "CESU");
2733
                rowVals.put("COMPTANT", Boolean.FALSE);
2734
                rowVals.put("ECHEANCE", Boolean.FALSE);
2735
                try {
2736
                    rowVals.commit();
2737
                } catch (SQLException e) {
2738
                    throw new IllegalStateException("Erreur lors de l'ajout du type CESU", e);
2739
                }
2740
            }
2741
            System.out.println("InstallationPanel.InstallationPanel() UPDATE FAMILLE_ARTICLE " + root);
2742
            //
2743
            final SQLTable tableFam = root.getTable("FAMILLE_ARTICLE");
2744
            final int nomSize = 256;
2745
            if (tableFam.getField("NOM").getType().getSize() < nomSize) {
2746
                final AlterTable t = new AlterTable(tableFam);
2747
                t.alterColumn("NOM", EnumSet.allOf(Properties.class), "varchar(" + nomSize + ")", "''", false);
2748
                try {
2749
                    ds.execute(t.asString());
2750
                } catch (Exception ex) {
2751
                    throw new IllegalStateException("Erreur lors de la modification du champs NOM sur la table FAMILLE_ARTICLE", ex);
2752
                }
2753
            }
2754
 
2755
            // Suppression des champs 1.0
2756
            System.out.println("InstallationPanel.InstallationPanel() UPDATE FROM 1.0 " + root);
2757
            final List<ChangeTable<?>> changes = new ArrayList<ChangeTable<?>>();
2758
 
61 ilm 2759
            final ModuleManager instance = new ModuleManager();
2760
            instance.setRoot(root);
80 ilm 2761
            final Collection<ModuleReference> refs = instance.getModulesInstalledRemotely();
61 ilm 2762
            final Set<String> allUsedTable = new HashSet<String>();
67 ilm 2763
            for (ModuleReference ref : refs) {
80 ilm 2764
                Set<String> tableNames = instance.getCreatedItems(ref.getID()).get0();
61 ilm 2765
                allUsedTable.addAll(tableNames);
2766
            }
80 ilm 2767
            System.out.println("Tables created by modules:" + allUsedTable);
19 ilm 2768
 
2769
            final List<String> alterRequests = ChangeTable.cat(changes, root.getName());
2770
            try {
2771
                for (final String req : alterRequests) {
2772
                    ds.execute(req);
2773
                }
2774
            } catch (Exception e1) {
2775
                throw new IllegalStateException("Erreur lors de la mise à jour des tables v1.0", e1);
2776
            }
2777
            System.out.println("InstallationPanel.InstallationPanel() UPDATE CAISSE " + root);
2778
            // Undefined
2779
            try {
2780
                SQLTable.setUndefID(tableTicket.getSchema(), tableTicket.getName(), 1);
2781
                SQLTable.setUndefID(tableTicket.getSchema(), "CAISSE", 1);
2782
            } catch (SQLException e1) {
2783
                throw new IllegalStateException("Erreur lors de la mise à jour des indéfinis de la table CAISSE", e1);
2784
            }
83 ilm 2785
        } catch (Exception e) {
2786
            ExceptionHandler.handle("updateSocieteSchema on root " + root + " failed", e);
19 ilm 2787
        } finally {
2788
            // Mise à jour du schéma
2789
            root.getSchema().updateVersion();
2790
            root.refetch();
2791
        }
2792
    }
2793
 
73 ilm 2794
    private void checkRights(DBRoot root) throws SQLException {
80 ilm 2795
        SQLTable table = root.findTable("RIGHT");
73 ilm 2796
        SQLSelect sel = new SQLSelect();
2797
        sel.addSelect(table.getKey());
2798
        sel.addSelect(table.getField("CODE"));
2799
        List<SQLRow> rows = SQLRowListRSH.execute(sel);
2800
        Set<String> codes = new HashSet<String>();
2801
        for (SQLRow row : rows) {
2802
            codes.add(row.getString("CODE"));
2803
        }
2804
 
2805
        if (!codes.contains("UPDATE_ROW")) {
2806
            SQLRowValues rowVals = new SQLRowValues(table);
2807
            rowVals.put("CODE", "UPDATE_ROW");
2808
            rowVals.put("NOM", "Modification d'une ligne");
2809
            String desc = "Autorise un utilisateur à modifier les éléments de la table spécifiée en objet.";
2810
            rowVals.put("DESCRIPTION", desc);
2811
            rowVals.commit();
2812
        }
2813
        if (!codes.contains("DELETE_ROW")) {
2814
            SQLRowValues rowVals = new SQLRowValues(table);
2815
            rowVals.put("CODE", "DELETE_ROW");
2816
            rowVals.put("NOM", "Suppression d'une ligne");
2817
            String desc = "Autorise un utilisateur à supprimer les éléments de la table spécifiée en objet.";
2818
            rowVals.put("DESCRIPTION", desc);
2819
            rowVals.commit();
2820
        }
2821
        if (!codes.contains("INSERT_ROW")) {
2822
            SQLRowValues rowVals = new SQLRowValues(table);
2823
            rowVals.put("CODE", "INSERT_ROW");
2824
            rowVals.put("NOM", "Ajout d'une ligne");
2825
            String desc = "Autorise un utilisateur à ajouter un élément dans la table spécifiée en objet.";
2826
            rowVals.put("DESCRIPTION", desc);
2827
            rowVals.commit();
2828
        }
2829
        // FIXME Probleme avec la property canSaveInList voir canSave() in IListPanel
2830
        // if (!codes.contains("SAVE_ROW")) {
2831
        // SQLRowValues rowVals = new SQLRowValues(table);
2832
        // rowVals.put("CODE", "SAVE_ROW");
2833
        // rowVals.put("NOM", "Export des listes");
2834
        // String desc =
2835
        // "Autorise un utilisateur à exporter le contenu des listes via le bouton représentant une disquette.";
2836
        // rowVals.put("DESCRIPTION", desc);
2837
        // rowVals.commit();
2838
        // }
2839
    }
2840
 
57 ilm 2841
    private void findBadForeignKey(DBRoot root) {
2842
        Set<SQLTable> tables = root.getTables();
2843
        for (SQLTable table : tables) {
2844
            findBadForeignKey(root, table);
2845
        }
2846
 
2847
    }
2848
 
2849
    private void findBadForeignKey(DBRoot root, SQLTable table) {
2850
        System.out.println("====================================== " + table.getName());
2851
        Set<SQLField> ffields = table.getForeignKeys();
2852
        Set<SQLField> allFields = table.getFields();
2853
 
2854
        Set<String> keysString = SQLKey.foreignKeys(table);
2855
        for (String string : keysString) {
2856
            ffields.add(table.getField(string));
2857
        }
2858
 
2859
        if (ffields.size() == 0) {
2860
            System.out.println("No foreign fields");
2861
        }
2862
        System.out.println("Foreign field for table " + table.getName() + ":" + ffields);
2863
        // Map Champs-> Table sur lequel il pointe
2864
        Map<SQLField, SQLTable> map = new HashMap<SQLField, SQLTable>();
2865
        Set<SQLTable> extTables = new HashSet<SQLTable>();
2866
        for (SQLField sqlField : ffields) {
2867
            SQLTable t = null;
2868
            try {
2869
                t = SQLKey.keyToTable(sqlField);
2870
            } catch (Exception e) {
2871
                System.out.println("Ignoring field:" + sqlField.getName());
2872
            }
2873
            if (t == null) {
2874
                System.out.println("Unable to find table for ff " + sqlField.getName());
2875
            } else {
2876
                extTables.add(t);
2877
                map.put(sqlField, t);
2878
            }
2879
        }
2880
        // Verification des datas
2881
        System.out.println("Foreign table for table " + table.getName() + ":" + extTables);
2882
        // Recupere les ids de toutes les tables
2883
        Map<SQLTable, Set<Number>> ids = getIdsForTables(extTables);
2884
 
2885
        //
67 ilm 2886
        SQLSelect s = new SQLSelect(true);
57 ilm 2887
        if (table.getPrimaryKeys().size() != 1) {
2888
            return;
2889
        }
2890
        s.addSelect(table.getKey());
2891
        for (SQLField sqlField : map.keySet()) {
2892
            s.addSelect(sqlField);
2893
        }
2894
        List<Map> result = root.getDBSystemRoot().getDataSource().execute(s.asString());
2895
        for (Map resultRow : result) {
2896
 
2897
            // Pour toutes les lignes
2898
            Set<String> fields = resultRow.keySet();
2899
            for (String field : fields) {
2900
                // Pour tous les champs
2901
                SQLField fField = table.getField(field);
2902
                if (table.getPrimaryKeys().contains(fField)) {
2903
                    continue;
2904
                }
2905
                SQLTable fTable = map.get(fField);
2906
                if (fTable == null) {
2907
                    System.out.println("Error: null table for field" + field);
2908
                    continue;
2909
                }
2910
                Set<Number> values = ids.get(fTable);
2911
 
2912
                final Object id = resultRow.get(field);
2913
                if (id == null) {
2914
                    continue;
2915
                } else if (!values.contains((Number) id)) {
2916
                    System.out.println("Checking row " + resultRow);
2917
                    System.out.println("Error: No id found in table " + fTable.getName() + " for row " + field + "in table " + table.getName() + " " + resultRow + " knowns id:" + values);
2918
                }
2919
            }
2920
        }
2921
        System.out.println("======================================\n");
2922
    }
2923
 
2924
    private Map<SQLTable, Set<Number>> getIdsForTables(Set<SQLTable> extTables) {
2925
        Map<SQLTable, Set<Number>> result = new HashMap<SQLTable, Set<Number>>();
2926
        for (SQLTable sqlTable : extTables) {
2927
            result.put(sqlTable, getIdsForTable(sqlTable));
2928
        }
2929
        return result;
2930
    }
2931
 
2932
    private Set<Number> getIdsForTable(SQLTable table) {
2933
        final DBRoot dbRoot = table.getDBRoot();
67 ilm 2934
        SQLSelect s = new SQLSelect(true);
57 ilm 2935
        s.addSelect(table.getKey());
2936
        List<Number> result = dbRoot.getDBSystemRoot().getDataSource().executeCol(s.asString());
2937
        return new HashSet<Number>(result);
2938
    }
2939
 
80 ilm 2940
    private void updateCommon(final DBRoot root) throws SQLException {
2941
        SQLUtils.executeAtomic(root.getDBSystemRoot().getDataSource(), new SQLFactory<Object>() {
2942
            @Override
2943
            public Object create() throws SQLException {
2944
                fixCompletion(root);
2945
                return null;
2946
            }
2947
        });
61 ilm 2948
 
73 ilm 2949
        if (ProductInfo.getInstance().getName().equalsIgnoreCase("OpenConcerto")) {
61 ilm 2950
 
73 ilm 2951
            final SQLTable tableExercice = root.getTable("EXERCICE_COMMON");
80 ilm 2952
 
2953
            // FIXME UPDATE Base ILM 1.2->1.3 la table EXERCERCIE ne contenait pas la clef
2954
            // ID_SOCIETE_COMMON
2955
            // if (tableExercice.contains("ID_SOCIETE_COMMON")) {
73 ilm 2956
            String reqUp = "UPDATE " + tableExercice.getSQLName().quote() + " SET \"ID_SOCIETE_COMMON\"=1 WHERE ";
2957
            reqUp += new Where(tableExercice.getKey(), 3, 49).getClause();
2958
            root.getDBSystemRoot().getDataSource().execute(reqUp);
57 ilm 2959
 
73 ilm 2960
            String reqUp2 = "UPDATE " + tableExercice.getSQLName().quote() + " SET \"ID_SOCIETE_COMMON\"=1 WHERE ";
2961
            reqUp2 += new Where(tableExercice.getKey(), 53, 57).getClause();
2962
            root.getDBSystemRoot().getDataSource().execute(reqUp2);
80 ilm 2963
            // }
73 ilm 2964
            // rm ID 43 - 47 de SOCIETE_COMMON
2965
            final SQLTable tableSociete = root.getTable("SOCIETE_COMMON");
2966
            String req3 = "DELETE FROM " + tableSociete.getSQLName().quote() + " WHERE ";
2967
            req3 += new Where(tableSociete.getKey(), 43, 47).getClause();
2968
            root.getDBSystemRoot().getDataSource().execute(req3);
2969
 
2970
            // rm ID 3 à 49 de EXERCICE_COMMON
2971
 
2972
            String req1a = "DELETE FROM " + tableExercice.getSQLName().quote() + " WHERE ";
2973
            req1a += new Where(tableExercice.getKey(), 3, 49).getClause();
2974
            root.getDBSystemRoot().getDataSource().execute(req1a);
2975
            // et 53-57
2976
            root.getDBSystemRoot().getDataSource().execute(req1a);
2977
            String req1b = "DELETE FROM " + tableExercice.getSQLName().quote() + " WHERE ";
2978
            req1b += new Where(tableExercice.getKey(), 53, 57).getClause();
2979
            root.getDBSystemRoot().getDataSource().execute(req1b);
2980
            //
2981
 
2982
            // TACHE_COMMON, ID_USER_COMMON_*=0 -> 1
2983
            for (final String f : Arrays.asList("ID_USER_COMMON_TO", "ID_USER_COMMON_CREATE", "ID_USER_COMMON_ASSIGN_BY")) {
2984
                final SQLTable tableTache = root.getTable("TACHE_COMMON");
2985
                final UpdateBuilder updateBuilder = new UpdateBuilder(tableTache);
2986
                updateBuilder.set(f, "1").setWhere(new Where(tableTache.getField(f), "=", 0));
2987
                String req2 = updateBuilder.asString();
2988
                root.getDBSystemRoot().getDataSource().execute(req2);
2989
            }
57 ilm 2990
        }
80 ilm 2991
        final SQLTable tableObjet = root.getTable("OBJET");
2992
        if (tableObjet != null && root.getTable("DOMAINE") == null) {
2993
            if (tableObjet.contains("ID_DOMAINE")) {
2994
                final AlterTable alter = new AlterTable(tableObjet);
2995
                alter.dropColumn("ID_DOMAINE");
2996
                final String req = alter.asString();
2997
                root.getDBSystemRoot().getDataSource().execute(req);
2998
                root.refetchTable(tableObjet.getName());
2999
            }
3000
        }
3001
 
57 ilm 3002
        // FK
65 ilm 3003
        new AddFK(root.getDBSystemRoot()).changeAll(root);
57 ilm 3004
    }
3005
 
80 ilm 3006
    protected void fixCompletion(DBRoot root) throws SQLException {
3007
        final SQLTable completionT = root.getTable(SQLTextCombo.getTableName());
3008
        if (completionT != null && completionT.getPrimaryKeys().size() == 0) {
3009
            final SQLDataSource ds = root.getDBSystemRoot().getDataSource();
3010
 
3011
            final Number oldCount = (Number) ds.executeScalar(new SQLSelect().addSelectFunctionStar("count").addFrom(completionT).asString());
3012
 
3013
            // create the correct table with a new name
3014
            final SQLCreateMoveableTable createTable = SQLTextCombo.getCreateTable(SQLSyntax.get(root));
3015
            createTable.setName(completionT.getName() + "TMP");
3016
            ds.execute(createTable.asString(root.getName()));
3017
 
3018
            // insert into the new table the old rows without duplicates
3019
            final SQLField refF = completionT.getField(SQLTextCombo.getRefFieldName());
3020
            final SQLField valueF = completionT.getField(SQLTextCombo.getValueFieldName());
3021
            final SQLSelect sel = new SQLSelect();
3022
            sel.addSelect(refF).addSelect(valueF);
3023
            sel.addGroupBy(refF).addGroupBy(valueF);
3024
            final int newCount = new Inserter(createTable, root).insertCount(" ( " + sel.asString() + " )");
3025
 
3026
            // replace the old table by the new one
3027
            ds.execute("DROP TABLE " + completionT.getSQL());
3028
            ds.execute("ALTER TABLE " + new SQLName(root.getName(), createTable.getName()).quote() + " RENAME to " + completionT.getQuotedName());
3029
            root.getSchema().updateVersion();
3030
            root.refetchTable(completionT.getName());
3031
 
3032
            System.out.println("Added primary key to " + completionT.getSQL() + " (row count : from " + oldCount + " to " + newCount + ")");
3033
        }
3034
    }
3035
 
41 ilm 3036
    private void updateSocieteTable(DBRoot root) throws SQLException {
3037
        SQLTable table = root.findTable("SOCIETE_COMMON");
3038
        boolean alter = false;
3039
        AlterTable t = new AlterTable(table);
73 ilm 3040
 
41 ilm 3041
        if (!table.getFieldsName().contains("RCS")) {
3042
            t.addVarCharColumn("RCS", 256);
3043
            alter = true;
3044
        }
3045
 
83 ilm 3046
        if (!table.getFieldsName().contains("ID_DEVISE")) {
3047
            t.addForeignColumn("ID_DEVISE", root.getTable("DEVISE"));
3048
            alter = true;
3049
        }
3050
 
41 ilm 3051
        if (!table.getFieldsName().contains("CAPITAL")) {
3052
            t.addColumn("CAPITAL", "bigint DEFAULT 0");
3053
            alter = true;
3054
        }
3055
 
3056
        if (alter) {
3057
            try {
3058
                table.getBase().getDataSource().execute(t.asString());
3059
                table.getSchema().updateVersion();
3060
                table.fetchFields();
3061
            } catch (SQLException ex) {
3062
                throw new IllegalStateException("Erreur lors de l'ajout des champs à la table " + table.getName(), ex);
3063
            }
3064
        }
3065
 
3066
    }
3067
 
73 ilm 3068
    private void updateVille(SQLTable tableAdresse) throws SQLException {
3069
 
3070
        if (tableAdresse != null && tableAdresse.getField("CODE_POSTAL").getType().getJavaType() == Integer.class) {
3071
            String reqRemoveField = "ALTER TABLE " + tableAdresse.getSQLName().quote() + " DROP COLUMN \"CODE_POSTAL\"";
3072
            tableAdresse.getBase().getDataSource().execute(reqRemoveField);
3073
            tableAdresse.getSchema().updateVersion();
3074
            tableAdresse.fetchFields();
3075
 
3076
            String reqAddVarCharField = "ALTER TABLE " + tableAdresse.getSQLName().quote() + " ADD COLUMN \"CODE_POSTAL\" character varying(256) DEFAULT ''::character varying";
3077
            tableAdresse.getBase().getDataSource().execute(reqAddVarCharField);
3078
            tableAdresse.getSchema().updateVersion();
3079
            tableAdresse.fetchFields();
3080
 
3081
            String req = "UPDATE " + tableAdresse.getSQLName().quote() + " SET \"VILLE\"= trim (both ' ' from substring(\"VILLE\" from 0 for (position('(' in \"VILLE\")))) ";
3082
            req += ", \"CODE_POSTAL\"=substring(\"VILLE\" from (position('(' in \"VILLE\")+1) for length(\"VILLE\")-position('(' in \"VILLE\")-1) WHERE \"VILLE\" LIKE '% (%)'";
3083
            tableAdresse.getDBRoot().getDBSystemRoot().getDataSource().execute(req);
3084
        }
3085
    }
3086
 
18 ilm 3087
    private void updateVariablePaye(SQLTable table, String var, double value) throws SQLException {
19 ilm 3088
        if (table == null) {
3089
            throw new IllegalArgumentException("null table");
3090
        }
67 ilm 3091
        SQLSelect sel = new SQLSelect();
18 ilm 3092
        sel.addSelectStar(table);
3093
        sel.setWhere(new Where(table.getField("NOM"), "=", var));
3094
        List<SQLRow> l = (List<SQLRow>) table.getBase().getDataSource().execute(sel.asString(), SQLRowListRSH.createFromSelect(sel));
3095
 
3096
        for (SQLRow sqlRow : l) {
3097
            SQLRowValues rowVals = sqlRow.asRowValues();
3098
            rowVals.put("VALEUR", value);
3099
            rowVals.update();
3100
        }
3101
    }
3102
 
3103
}