OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

Rev 21 | Rev 41 | 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
 
19 ilm 16
import org.openconcerto.sql.changer.convert.AddFK;
17
import org.openconcerto.sql.changer.correct.CorrectOrder;
18 ilm 18
import org.openconcerto.sql.changer.correct.FixSerial;
19 ilm 19
import org.openconcerto.sql.model.DBRoot;
20
import org.openconcerto.sql.model.DBSystemRoot;
21
import org.openconcerto.sql.model.SQLBase;
22
import org.openconcerto.sql.model.SQLDataSource;
20 ilm 23
import org.openconcerto.sql.model.SQLField;
19 ilm 24
import org.openconcerto.sql.model.SQLField.Properties;
25
import org.openconcerto.sql.model.SQLName;
18 ilm 26
import org.openconcerto.sql.model.SQLRow;
27
import org.openconcerto.sql.model.SQLRowListRSH;
28
import org.openconcerto.sql.model.SQLRowValues;
29
import org.openconcerto.sql.model.SQLSelect;
19 ilm 30
import org.openconcerto.sql.model.SQLSyntax;
31
import org.openconcerto.sql.model.SQLSystem;
18 ilm 32
import org.openconcerto.sql.model.SQLTable;
33
import org.openconcerto.sql.model.Where;
19 ilm 34
import org.openconcerto.sql.model.graph.DatabaseGraph;
35
import org.openconcerto.sql.model.graph.Link;
18 ilm 36
import org.openconcerto.sql.utils.AlterTable;
19 ilm 37
import org.openconcerto.sql.utils.ChangeTable;
38
import org.openconcerto.sql.utils.DropTable;
39
import org.openconcerto.sql.utils.ReOrder;
40
import org.openconcerto.sql.utils.SQLCreateTable;
41
import org.openconcerto.sql.utils.SQLUtils;
18 ilm 42
import org.openconcerto.ui.DefaultGridBagConstraints;
43
import org.openconcerto.ui.JLabelBold;
19 ilm 44
import org.openconcerto.utils.CollectionUtils;
18 ilm 45
import org.openconcerto.utils.ExceptionHandler;
46
 
47
import java.awt.GridBagConstraints;
48
import java.awt.GridBagLayout;
49
import java.awt.Insets;
50
import java.awt.event.ActionEvent;
51
import java.awt.event.ActionListener;
52
import java.sql.SQLException;
20 ilm 53
import java.sql.Types;
19 ilm 54
import java.util.ArrayList;
55
import java.util.EnumSet;
18 ilm 56
import java.util.List;
57
import java.util.Set;
58
 
59
import javax.swing.JButton;
60
import javax.swing.JLabel;
61
import javax.swing.JOptionPane;
62
import javax.swing.JPanel;
63
import javax.swing.JProgressBar;
64
import javax.swing.JTextField;
65
import javax.swing.SwingUtilities;
66
 
67
public class InstallationPanel extends JPanel {
68
 
19 ilm 69
    static private void insertUndef(final SQLCreateTable ct) {
70
        final String insert = "INSERT into " + getTableName(ct).quote() + "(" + SQLBase.quoteIdentifier(SQLSyntax.ORDER_NAME) + ") VALUES(" + ReOrder.MIN_ORDER + ")";
71
        ct.getRoot().getDBSystemRoot().getDataSource().execute(insert);
72
    }
73
 
74
    static private SQLName getTableName(final SQLCreateTable ct) {
75
        return new SQLName(ct.getRoot().getName(), ct.getName());
76
    }
77
 
18 ilm 78
    JProgressBar bar = new JProgressBar();
19 ilm 79
    boolean error;
18 ilm 80
 
81
    public InstallationPanel(final ServerFinderPanel finderPanel) {
82
        super(new GridBagLayout());
83
        setOpaque(false);
84
        GridBagConstraints c = new DefaultGridBagConstraints();
85
        JButton user = new JButton("Créer l'utilisateur");
86
 
87
        // JButton bd = new JButton("Créer la base de données");
88
        final JButton up = new JButton("Mise à niveau de la base");
89
        up.addActionListener(new ActionListener() {
90
 
91
            @Override
92
            public void actionPerformed(ActionEvent e) {
19 ilm 93
                finderPanel.saveConfigFile();
18 ilm 94
                bar.setIndeterminate(true);
95
                up.setEnabled(false);
96
                new Thread(new Runnable() {
97
 
98
                    @Override
99
                    public void run() {
19 ilm 100
                        final ComptaPropsConfiguration conf = ComptaPropsConfiguration.create(true);
18 ilm 101
 
102
                        try {
19 ilm 103
                            final SQLDataSource ds = conf.getSystemRoot().getDataSource();
104
                            System.err.println("SystemRoot:" + conf.getSystemRoot());
105
                            System.err.println("Root:" + conf.getRoot());
106
 
21 ilm 107
                            // FixUnbounded varchar
108
                            fixUnboundedVarchar(conf.getRoot());
109
 
19 ilm 110
                            // Mise à jour des taux
111
                            final SQLTable table = conf.getRoot().getTable("VARIABLE_PAYE");
112
                            System.out.println("InstallationPanel.InstallationPanel() UPDATE PAYE");
18 ilm 113
                            updateVariablePaye(table, "SMIC", 9);
114
                            updateVariablePaye(table, "TRANCHE_A", 2946);
115
                            updateVariablePaye(table, "PART_SAL_GarantieMP", 23.83);
116
                            updateVariablePaye(table, "PART_PAT_GarantieMP", 38.98);
117
 
19 ilm 118
                            if (!table.getDBRoot().contains("DEVISE")) {
119
                                System.out.println("InstallationPanel.InstallationPanel() ADD DEVISE");
120
                                try {
121
                                    SQLUtils.executeAtomic(ds, new SQLUtils.SQLFactory<Object>() {
122
                                        @Override
123
                                        public Object create() throws SQLException {
124
                                            final SQLCreateTable createDevise = new SQLCreateTable(table.getDBRoot(), "DEVISE");
125
                                            createDevise.addVarCharColumn("CODE", 128);
126
                                            createDevise.addVarCharColumn("NOM", 128);
127
                                            createDevise.addVarCharColumn("LIBELLE", 128);
128
                                            createDevise.addVarCharColumn("LIBELLE_CENT", 128);
129
                                            createDevise.addColumn("TAUX", "numeric(16,8) default 1");
130
                                            ds.execute(createDevise.asString());
131
 
132
                                            insertUndef(createDevise);
133
 
134
                                            conf.getRoot().getSchema().updateVersion();
135
 
136
                                            return null;
137
                                        }
138
                                    });
139
                                } catch (Exception ex) {
140
                                    throw new IllegalStateException("Erreur lors de la création de la table DEVISE", ex);
141
                                }
142
                            }
143
 
21 ilm 144
                            if (!table.getDBRoot().contains("TYPE_MODELE")) {
145
                                System.out.println("InstallationPanel.InstallationPanel() ADD TYPE_MODELE");
146
                                try {
147
                                    SQLUtils.executeAtomic(ds, new SQLUtils.SQLFactory<Object>() {
148
                                        @Override
149
                                        public Object create() throws SQLException {
150
                                            final SQLCreateTable createTypeModele = new SQLCreateTable(table.getDBRoot(), "TYPE_MODELE");
151
                                            createTypeModele.addVarCharColumn("NOM", 128);
152
                                            createTypeModele.addVarCharColumn("TABLE", 128);
153
                                            createTypeModele.addVarCharColumn("DEFAULT_MODELE", 128);
154
                                            ds.execute(createTypeModele.asString());
155
 
156
                                            insertUndef(createTypeModele);
157
 
158
                                            conf.getRoot().getSchema().updateVersion();
159
 
160
                                            conf.getRoot().refetch();
161
 
162
                                            return null;
163
                                        }
164
                                    });
165
                                    final String[] type = new String[] { "Avoir client", "AVOIR_CLIENT", "Avoir", "Bon de livraison", "BON_DE_LIVRAISON", "BonLivraison", "Commande Client",
166
                                            "COMMANDE_CLIENT", "CommandeClient", "Devis", "DEVIS", "Devis", "Facture", "SAISIE_VENTE_FACTURE", "VenteFacture" };
167
                                    // ('FR', 'Français', 1.000), ('EN', 'Anglais', 2.000)
168
                                    final List<String> values = new ArrayList<String>();
169
                                    final SQLBase base = table.getDBRoot().getBase();
170
 
171
                                    for (int i = 0; i < type.length; i += 3) {
172
                                        final int order = values.size() + 1;
173
                                        values.add("(" + base.quoteString(type[i]) + ", " + base.quoteString(type[i + 1]) + ", " + base.quoteString(type[i + 2]) + ", " + order + ")");
174
                                    }
175
                                    final String valuesStr = CollectionUtils.join(values, ", ");
176
                                    final String insertVals = "INSERT INTO " + conf.getRoot().getTable("TYPE_MODELE").getSQLName().quote() + "(" + SQLBase.quoteIdentifier("NOM") + ", "
177
                                            + SQLBase.quoteIdentifier("TABLE") + ", " + SQLBase.quoteIdentifier("DEFAULT_MODELE") + ", " + SQLBase.quoteIdentifier(SQLSyntax.ORDER_NAME) + ") VALUES"
178
                                            + valuesStr;
179
 
180
                                    ds.execute(insertVals);
181
                                } catch (Exception ex) {
182
                                    throw new IllegalStateException("Erreur lors de la création de la table TYPE_MODELE", ex);
183
                                }
184
                            }
185
 
19 ilm 186
                            // we need to upgrade all roots
18 ilm 187
                            conf.getSystemRoot().getRootsToMap().clear();
19 ilm 188
                            conf.getSystemRoot().refetch();
18 ilm 189
 
190
                            final Set<String> childrenNames = conf.getSystemRoot().getChildrenNames();
19 ilm 191
 
18 ilm 192
                            SwingUtilities.invokeLater(new Runnable() {
193
 
194
                                @Override
195
                                public void run() {
196
                                    bar.setIndeterminate(false);
19 ilm 197
                                    bar.setMaximum(childrenNames.size() + 1);
18 ilm 198
                                }
199
                            });
200
                            int i = 1;
19 ilm 201
                            for (final String childName : childrenNames) {
202
                                System.out.println("InstallationPanel.InstallationPanel() UPDATE SCHEMA " + childName);
18 ilm 203
                                final int barValue = i;
204
                                SwingUtilities.invokeLater(new Runnable() {
205
 
206
                                    @Override
207
                                    public void run() {
208
                                        bar.setValue(barValue);
209
                                    }
210
                                });
211
                                i++;
19 ilm 212
                                final DBRoot root = conf.getSystemRoot().getRoot(childName);
213
                                final SQLTable tableUndef = root.getTable(SQLTable.undefTable);
214
                                if (tableUndef != null && tableUndef.getField("UNDEFINED_ID").isNullable() == Boolean.FALSE) {
215
                                    final AlterTable alterUndef = new AlterTable(tableUndef);
216
                                    alterUndef.alterColumn("TABLENAME", EnumSet.allOf(Properties.class), "varchar(250)", "''", false);
217
                                    alterUndef.alterColumn("UNDEFINED_ID", EnumSet.allOf(Properties.class), "int", null, true);
218
                                    try {
219
                                        ds.execute(alterUndef.asString());
220
                                        tableUndef.getSchema().updateVersion();
221
                                    } catch (SQLException ex) {
222
                                        throw new IllegalStateException("Erreur lors de la modification de UNDEFINED_ID", ex);
223
                                    }
224
                                }
225
 
226
                                if (childName.startsWith(conf.getAppName()) || childName.equalsIgnoreCase("Default")) {
227
                                    SQLUtils.executeAtomic(ds, new SQLUtils.SQLFactory<Object>() {
228
                                        @Override
229
                                        public Object create() throws SQLException {
20 ilm 230
                                            fixUnboundedVarchar(root);
25 ilm 231
                                            fixUnboundedNumeric(root);
19 ilm 232
                                            updateSocieteSchema(root);
233
                                            updateToV1Dot2(root);
234
                                            return null;
235
                                        }
236
                                    });
237
                                }
238
 
18 ilm 239
                            }
19 ilm 240
                            error = false;
18 ilm 241
                        } catch (Exception e1) {
242
                            ExceptionHandler.handle("Echec de mise à jour", e1);
19 ilm 243
                            error = true;
18 ilm 244
                        }
245
 
246
                        conf.destroy();
247
                        SwingUtilities.invokeLater(new Runnable() {
248
 
249
                            @Override
250
                            public void run() {
251
                                up.setEnabled(true);
19 ilm 252
 
253
                                if (!error) {
254
                                    JOptionPane.showMessageDialog(InstallationPanel.this, "Mise à niveau réussie");
255
                                }
18 ilm 256
                            }
257
                        });
258
 
259
                    }
260
                }, "Database structure updater").start();
261
 
262
            }
263
 
264
        });
265
 
266
        c.weightx = 1;
267
        c.gridwidth = GridBagConstraints.REMAINDER;
268
        this.add(new JLabelBold("Création de l'utilisateur openconcerto dans la base"), c);
269
        c.gridy++;
270
        c.weightx = 1;
271
        this.add(new JLabel("Identifiant de connexion de votre base "), c);
272
        c.gridy++;
273
        c.gridwidth = 1;
274
        c.weightx = 0;
275
        this.add(new JLabel("Login"), c);
276
        c.gridx++;
277
 
278
        final JTextField login = new JTextField();
279
        c.weightx = 1;
280
        this.add(login, c);
281
 
282
        c.gridx++;
283
        c.weightx = 0;
284
        this.add(new JLabel("Mot de passe"), c);
285
        c.gridx++;
286
        final JTextField mdp = new JTextField();
287
        c.weightx = 1;
288
        this.add(mdp, c);
289
 
290
        c.gridx = 0;
291
        c.gridy++;
292
        c.weightx = 0;
293
        c.anchor = GridBagConstraints.EAST;
294
        c.gridwidth = GridBagConstraints.REMAINDER;
295
        c.fill = GridBagConstraints.NONE;
296
        this.add(user, c);
297
        c.anchor = GridBagConstraints.WEST;
298
        c.fill = GridBagConstraints.HORIZONTAL;
299
        c.gridwidth = 1;
300
        user.addActionListener(new ActionListener() {
301
 
302
            @Override
303
            public void actionPerformed(ActionEvent e) {
304
                // TODO Auto-generated method stub
305
                try {
306
                    if (finderPanel.getServerConfig().createUserIfNeeded(login.getText(), mdp.getText())) {
307
                        JOptionPane.showMessageDialog(InstallationPanel.this, "L'utilisateur openconcerto a été correctement ajouté.");
308
                    } else {
309
                        JOptionPane.showMessageDialog(InstallationPanel.this, "L'utilisateur openconcerto existe déjà dans la base.");
310
                    }
311
                } catch (Exception e1) {
312
                    // TODO Auto-generated catch block
313
                    e1.printStackTrace();
314
                    JOptionPane.showMessageDialog(InstallationPanel.this, "Une erreur est survenue pendant la connexion au serveur, vérifiez vos paramètres de connexion.");
315
                }
316
            }
317
        });
318
 
319
        // Injection SQL
320
        // c.gridy++;
321
        // c.weightx = 1;
322
        // c.gridwidth = GridBagConstraints.REMAINDER;
323
        // c.insets = new Insets(10, 3, 2, 2);
324
        // this.add(new TitledSeparator("Injecter la base", true), c);
325
        //
326
        // c.gridy++;
327
        // c.weightx = 0;
328
        // c.gridwidth = 1;
329
        // c.insets = DefaultGridBagConstraints.getDefaultInsets();
330
        // this.add(new JLabel("Fichier"), c);
331
        //
332
        // final JTextField chemin = new JTextField();
333
        // c.gridx++;
334
        // c.weightx = 1;
335
        // this.add(chemin, c);
336
        //
337
        // c.gridx++;
338
        // c.weightx = 0;
339
        // JButton browse = new JButton("...");
340
        // browse.addActionListener(new ActionListener() {
341
        //
342
        // @Override
343
        // public void actionPerformed(ActionEvent e) {
344
        // JFileChooser choose = new JFileChooser();
345
        // if (choose.showOpenDialog(InstallationPanel.this) == JFileChooser.APPROVE_OPTION) {
346
        // chemin.setText(choose.getSelectedFile().getAbsolutePath());
347
        // }
348
        // }
349
        // });
350
        // this.add(browse, c);
351
        //
352
        // c.gridy++;
353
        // c.gridx = 0;
354
        // JButton inject = new JButton("Injecter");
355
        // this.add(inject, c);
356
        // inject.addActionListener(new ActionListener() {
357
        //
358
        // @Override
359
        // public void actionPerformed(ActionEvent e) {
360
        // File f = new File(chemin.getText());
361
        // if (!f.exists()) {
362
        // JOptionPane.showMessageDialog(InstallationPanel.this, "Impossible de trouver le fichier "
363
        // + chemin.getText());
364
        // return;
365
        // }
366
        // BufferedReader input = null;
367
        // try {
368
        //
369
        // input = new BufferedReader(new FileReader(f));
370
        // StringBuffer sql = new StringBuffer();
371
        // String s;
372
        // while ((s = input.readLine()) != null) {
373
        // sql.append(s + "\n");
374
        // }
375
        // input.close();
376
        //
377
        // try {
378
        // final SQLServer sqlServer = finderPanel.getServerConfig().createSQLServer();
379
        // Number n = (Number)
380
        // sqlServer.getBase("postgres").getDataSource().executeScalar("select COUNT(*) from pg_database WHERE datname='OpenConcerto'");
381
        // if (n.intValue() > 0) {
382
        // JOptionPane.showMessageDialog(InstallationPanel.this,
383
        // "La base OpenConcerto est déjà présente sur le serveur!");
384
        // return;
385
        // }
386
        // // System.err.println(sqlServer.getBase("OpenConcerto"));
387
        // sqlServer.getBase("postgres").getDataSource()
388
        // .execute("CREATE DATABASE \"OpenConcerto\" WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'fr_FR.UTF-8' LC_CTYPE = 'fr_FR.UTF-8';");
389
        //
390
        // sqlServer.getBase("postgres").getDataSource().execute("ALTER DATABASE \"OpenConcerto\" OWNER TO openconcerto;");
391
        //
392
        // SQLUtils.executeScript(sql.toString(), sqlServer.getSystemRoot("OpenConcerto"));
393
        // sqlServer.destroy();
394
        // JOptionPane.showMessageDialog(InstallationPanel.this,
395
        // "Création de la base OpenConerto terminée.");
396
        // System.err.println("Création de la base OpenConerto terminée.");
397
        //
398
        // } catch (SQLException e1) {
399
        // // TODO Auto-generated catch block
400
        //
401
        // e1.printStackTrace();
402
        // JOptionPane.showMessageDialog(InstallationPanel.this,
403
        // "Une erreur s'est produite pendant l'injection du script, vérifier la connexion au serveur et le script.");
404
        // }
405
        //
406
        // } catch (FileNotFoundException ex) {
407
        // // TODO Auto-generated catch block
408
        // ex.printStackTrace();
409
        // } catch (IOException ex) {
410
        // // TODO Auto-generated catch block
411
        // ex.printStackTrace();
412
        // } finally {
413
        // if (input != null) {
414
        // try {
415
        // input.close();
416
        // } catch (IOException ex) {
417
        // // TODO Auto-generated catch block
418
        // ex.printStackTrace();
419
        // }
420
        // }
421
        // }
422
        //
423
        // }
424
        // });
425
 
426
        // c.gridy++;
427
        // this.add(bd, c);
25 ilm 428
 
18 ilm 429
        c.gridy++;
430
        c.weightx = 1;
431
        c.gridwidth = GridBagConstraints.REMAINDER;
432
        c.insets = new Insets(10, 3, 2, 2);
25 ilm 433
        this.add(new JLabelBold("Paramètrages de la base de données"), c);
434
        c.gridy++;
435
        c.weightx = 0;
436
        c.anchor = GridBagConstraints.EAST;
437
        c.gridwidth = GridBagConstraints.REMAINDER;
438
        c.fill = GridBagConstraints.NONE;
439
        c.insets = DefaultGridBagConstraints.getDefaultInsets();
440
        JButton buttonPL = new JButton("Lancer");
441
        buttonPL.addActionListener(new ActionListener() {
442
 
443
            @Override
444
            public void actionPerformed(ActionEvent e) {
445
                if (!finderPanel.getServerConfig().getType().equals(ServerFinderConfig.POSTGRESQL)) {
446
 
447
                } else {
448
                    final ComptaPropsConfiguration conf = ComptaPropsConfiguration.create(true);
449
                    try {
450
                        final SQLDataSource ds = conf.getSystemRoot().getDataSource();
451
                        ds.execute("CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler AS '$libdir/plpgsql' LANGUAGE C;" + "\n"
452
                                + "CREATE FUNCTION plpgsql_validator(oid) RETURNS void AS '$libdir/plpgsql' LANGUAGE C;" + "\n"
453
                                + "CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler VALIDATOR plpgsql_validator;");
454
                    } catch (Exception ex) {
455
                        System.err.println("Impossible d'ajouter le langage PLPGSQL. Peut etre est il déjà installé.");
456
                    }
457
                }
458
                JOptionPane.showConfirmDialog(null, "Paramètrage terminé.");
459
            }
460
        });
461
        this.add(buttonPL, c);
462
 
463
        c.gridy++;
464
        c.gridx = 0;
465
        c.weightx = 1;
466
        c.fill = GridBagConstraints.HORIZONTAL;
467
        c.anchor = GridBagConstraints.WEST;
468
        c.gridwidth = GridBagConstraints.REMAINDER;
469
        c.insets = new Insets(10, 3, 2, 2);
18 ilm 470
        this.add(new JLabelBold("Mise à niveau de la base OpenConcerto"), c);
471
        c.gridy++;
472
        this.add(this.bar, c);
473
        c.gridy++;
474
        c.weightx = 0;
475
        c.anchor = GridBagConstraints.EAST;
476
        c.gridwidth = GridBagConstraints.REMAINDER;
477
        c.fill = GridBagConstraints.NONE;
478
        c.insets = DefaultGridBagConstraints.getDefaultInsets();
479
        this.add(up, c);
480
 
481
        c.anchor = GridBagConstraints.WEST;
482
        c.fill = GridBagConstraints.HORIZONTAL;
483
 
484
        c.weightx = 1;
485
        c.gridwidth = GridBagConstraints.REMAINDER;
486
        c.weighty = 1;
487
        c.gridy++;
488
        final JPanel comp = new JPanel();
489
        comp.setOpaque(false);
490
        this.add(comp, c);
491
    }
492
 
25 ilm 493
    private void fixUnboundedNumeric(DBRoot root) throws SQLException {
494
 
495
        final List<AlterTable> alters = new ArrayList<AlterTable>();
496
        {
497
            SQLTable tableAvoir = root.getTable("AVOIR_CLIENT_ELEMENT");
498
            final AlterTable alter = new AlterTable(tableAvoir);
499
            SQLField fieldAcompteAvoir = tableAvoir.getField("POURCENT_ACOMPTE");
500
            if (fieldAcompteAvoir.getType().getSize() > 500) {
501
                final String fName = fieldAcompteAvoir.getName();
502
                alter.alterColumn(fName, EnumSet.allOf(Properties.class), "numeric(6,2)", "100", false);
503
            }
504
 
505
            SQLField fieldRemiseAvoir = tableAvoir.getField("POURCENT_REMISE");
506
            if (fieldRemiseAvoir.getType().getSize() > 500) {
507
                final String fName = fieldRemiseAvoir.getName();
508
                alter.alterColumn(fName, EnumSet.allOf(Properties.class), "numeric(6,2)", "0", false);
509
            }
510
 
511
            if (!alter.isEmpty())
512
                alters.add(alter);
513
        }
514
 
515
        {
516
            SQLTable tableFacture = root.getTable("SAISIE_VENTE_FACTURE_ELEMENT");
517
            final AlterTable alter = new AlterTable(tableFacture);
518
            SQLField fieldAcompteFacture = tableFacture.getField("POURCENT_ACOMPTE");
519
            if (fieldAcompteFacture.getType().getSize() > 500) {
520
                final String fName = fieldAcompteFacture.getName();
521
                alter.alterColumn(fName, EnumSet.allOf(Properties.class), "numeric(6,2)", "100", false);
522
            }
523
 
524
            SQLField fieldRemiseFacture = tableFacture.getField("POURCENT_REMISE");
525
            if (fieldRemiseFacture.getType().getSize() > 500) {
526
                final String fName = fieldRemiseFacture.getName();
527
                alter.alterColumn(fName, EnumSet.allOf(Properties.class), "numeric(6,2)", "0", false);
528
            }
529
 
530
            if (tableFacture.getFieldsName().contains("REPARTITION_POURCENT")) {
531
                SQLField fieldRepFacture = tableFacture.getField("REPARTITION_POURCENT");
532
                if (fieldRepFacture.getType().getSize() > 500) {
533
                    final String fName = fieldRepFacture.getName();
534
                    alter.alterColumn(fName, EnumSet.allOf(Properties.class), "numeric(6,2)", "0", false);
535
                }
536
            }
537
 
538
            if (!alter.isEmpty())
539
                alters.add(alter);
540
 
541
        }
542
        if (alters.size() > 0) {
543
            final SQLDataSource ds = root.getDBSystemRoot().getDataSource();
544
            for (final String sql : ChangeTable.cat(alters, root.getName())) {
545
                ds.execute(sql);
546
            }
547
            root.refetch();
548
        }
549
    }
550
 
20 ilm 551
    private void fixUnboundedVarchar(DBRoot root) throws SQLException {
552
        final Set<String> namesSet = CollectionUtils.createSet("NOM", "PRENOM", "SURNOM", "LOGIN", "PASSWORD");
553
        final List<AlterTable> alters = new ArrayList<AlterTable>();
554
        for (final SQLTable t : root.getTables()) {
555
            final AlterTable alter = new AlterTable(t);
556
            for (final SQLField f : t.getFields()) {
557
                if (f.getType().getType() == Types.VARCHAR && f.getType().getSize() == Integer.MAX_VALUE) {
558
                    final String fName = f.getName();
559
                    final int size;
560
                    if (namesSet.contains(fName))
21 ilm 561
                        size = 128;
20 ilm 562
                    else if (fName.equals("TEL") || fName.startsWith("TEL_"))
21 ilm 563
                        size = 32;
564
                    else if (fName.contains("INFO"))
565
                        size = 2048;
566
                    else if (fName.contains("FORMULE"))
567
                        size = 1024;
568
                    else if (fName.equals("CONTENU"))
569
                        size = 2048;
20 ilm 570
                    else
21 ilm 571
                        size = 256;
20 ilm 572
                    alter.alterColumn(fName, EnumSet.allOf(Properties.class), "varchar(" + size + ")", "''", false);
573
                }
574
            }
575
            if (!alter.isEmpty())
576
                alters.add(alter);
577
        }
578
        if (alters.size() > 0) {
579
            final SQLDataSource ds = root.getDBSystemRoot().getDataSource();
580
            for (final String sql : ChangeTable.cat(alters, root.getName())) {
581
                ds.execute(sql);
582
            }
583
            root.refetch();
584
        }
585
    }
586
 
19 ilm 587
    private void updateToV1Dot2(final DBRoot root) throws SQLException {
588
        final SQLTable tableDevis = root.getTable("DEVIS");
589
        final SQLDataSource ds = root.getDBSystemRoot().getDataSource();
590
        if (!tableDevis.getFieldsName().contains("DATE_VALIDITE")) {
591
            AlterTable t = new AlterTable(tableDevis);
592
            t.addColumn("DATE_VALIDITE", "date");
593
            try {
594
                ds.execute(t.asString());
595
                tableDevis.getSchema().updateVersion();
596
            } catch (SQLException ex) {
597
                throw new IllegalStateException("Erreur lors de l'ajout du champ DATE_VALIDITE à la table DEVIS", ex);
598
            }
599
        } else {
600
            AlterTable t = new AlterTable(tableDevis);
601
            t.alterColumn("DATE_VALIDITE", EnumSet.allOf(Properties.class), "date", null, true);
602
            try {
603
                ds.execute(t.asString());
604
                tableDevis.getSchema().updateVersion();
605
            } catch (SQLException ex) {
606
                throw new IllegalStateException("Erreur lors de l'ajout du champ DATE_VALIDITE à la table DEVIS", ex);
607
            }
608
        }
18 ilm 609
 
19 ilm 610
        // Bon de livraison
611
        {
612
            SQLTable tableBL = root.getTable("BON_DE_LIVRAISON");
613
            boolean alterBL = false;
614
            AlterTable t = new AlterTable(tableBL);
615
            if (!tableBL.getFieldsName().contains("SOURCE")) {
616
                t.addVarCharColumn("SOURCE", 512);
617
                alterBL = true;
618
            }
619
            if (!tableBL.getFieldsName().contains("IDSOURCE")) {
620
                t.addColumn("IDSOURCE", "integer DEFAULT 1");
621
                alterBL = true;
622
            }
623
            if (alterBL) {
624
                try {
625
                    ds.execute(t.asString());
626
                    tableBL.getSchema().updateVersion();
627
                    tableBL.fetchFields();
628
                } catch (SQLException ex) {
629
                    throw new IllegalStateException("Erreur lors de l'ajout des champs sur la table BON_DE_LIVRAISON", ex);
630
                }
631
            }
632
        }
633
        SQLTable tableArticle = root.getTable("ARTICLE");
634
 
635
        AlterTable t = new AlterTable(tableArticle);
636
        boolean alterArticle = false;
637
        if (!tableArticle.getFieldsName().contains("QTE_ACHAT")) {
638
            t.addColumn("QTE_ACHAT", "integer DEFAULT 1");
639
            alterArticle = true;
640
        }
641
        if (!tableArticle.getFieldsName().contains("DESCRIPTIF")) {
642
            t.addVarCharColumn("DESCRIPTIF", 2048);
643
            alterArticle = true;
644
        }
645
        if (!tableArticle.getFieldsName().contains("CODE_BARRE")) {
646
            t.addVarCharColumn("CODE_BARRE", 256);
647
            alterArticle = true;
648
        }
649
        if (!tableArticle.getFieldsName().contains("GESTION_STOCK")) {
650
            t.addColumn("GESTION_STOCK", "boolean DEFAULT true");
651
            alterArticle = true;
652
        }
653
        if (!tableArticle.getFieldsName().contains("CODE_DOUANIER")) {
654
            t.addVarCharColumn("CODE_DOUANIER", 256);
655
            alterArticle = true;
656
        }
657
        if (!tableArticle.getFieldsName().contains("QTE_MIN")) {
658
            t.addColumn("QTE_MIN", "integer DEFAULT 1");
659
            alterArticle = true;
660
        }
661
        if (!tableArticle.getFieldsName().contains("ID_DEVISE")) {
662
            t.addForeignColumn("ID_DEVISE", root.findTable("DEVISE"));
663
            alterArticle = true;
664
        }
665
        if (!tableArticle.getFieldsName().contains("ID_FOURNISSEUR")) {
666
            t.addForeignColumn("ID_FOURNISSEUR", root.findTable("FOURNISSEUR"));
667
            alterArticle = true;
668
        }
669
        if (!tableArticle.getFieldsName().contains("PV_U_DEVISE")) {
670
            t.addColumn("PV_U_DEVISE", "bigint default 0");
671
            alterArticle = true;
672
        }
673
        if (!tableArticle.getFieldsName().contains("ID_DEVISE_HA")) {
674
            t.addForeignColumn("ID_DEVISE_HA", root.findTable("DEVISE"));
675
            alterArticle = true;
676
        }
677
        if (!tableArticle.getFieldsName().contains("PA_DEVISE")) {
678
            t.addColumn("PA_DEVISE", "bigint default 0");
679
            alterArticle = true;
680
        }
681
        if (!tableArticle.getFieldsName().contains("ID_PAYS")) {
682
            t.addForeignColumn("ID_PAYS", root.findTable("PAYS"));
683
            alterArticle = true;
684
        }
685
        if (alterArticle) {
18 ilm 686
            try {
19 ilm 687
                ds.execute(t.asString());
688
                tableArticle.getSchema().updateVersion();
18 ilm 689
                tableArticle.fetchFields();
690
            } catch (SQLException ex) {
19 ilm 691
                throw new IllegalStateException("Erreur lors de l'ajout des champs sur la table ARTICLE", ex);
18 ilm 692
            }
693
        }
694
 
19 ilm 695
        // Création de la table Langue
696
        boolean refetchRoot = false;
697
        if (!root.contains("LANGUE")) {
18 ilm 698
 
19 ilm 699
            SQLCreateTable createLangue = new SQLCreateTable(root, "LANGUE");
700
            createLangue.addVarCharColumn("CODE", 256);
701
            createLangue.addVarCharColumn("NOM", 256);
702
            createLangue.addVarCharColumn("CHEMIN", 256);
703
            try {
704
                ds.execute(createLangue.asString());
705
                insertUndef(createLangue);
706
                tableDevis.getSchema().updateVersion();
707
                refetchRoot = true;
708
            } catch (SQLException ex) {
709
                throw new IllegalStateException("Erreur lors de la création de la table LANGUE", ex);
710
            }
711
 
712
            final String[] langs = new String[] { "FR", "Français", "EN", "Anglais", "SP", "Espagnol", "DE", "Allemand", "NL", "Néerlandais", "IT", "Italien" };
713
            // ('FR', 'Français', 1.000), ('EN', 'Anglais', 2.000)
714
            final List<String> values = new ArrayList<String>();
715
            final SQLBase base = root.getBase();
716
            for (int i = 0; i < langs.length; i += 2) {
717
                final int order = values.size() + 1;
718
                values.add("(" + base.quoteString(langs[i]) + ", " + base.quoteString(langs[i + 1]) + ", " + order + ")");
719
            }
720
            final String valuesStr = CollectionUtils.join(values, ", ");
721
            final String insertVals = "INSERT INTO " + getTableName(createLangue).quote() + "(" + SQLBase.quoteIdentifier("CODE") + ", " + SQLBase.quoteIdentifier("NOM") + ", "
722
                    + SQLBase.quoteIdentifier(SQLSyntax.ORDER_NAME) + ") VALUES" + valuesStr;
723
            ds.execute(insertVals);
18 ilm 724
        }
725
 
21 ilm 726
        // Création de la table Modéle
727
        if (!root.contains("MODELE")) {
728
 
729
            SQLCreateTable createModele = new SQLCreateTable(root, "MODELE");
730
            createModele.addVarCharColumn("NOM", 256);
731
            createModele.addForeignColumn("ID_TYPE_MODELE", root.findTable("TYPE_MODELE"));
732
            try {
733
                ds.execute(createModele.asString());
734
                insertUndef(createModele);
735
                tableDevis.getSchema().updateVersion();
736
                refetchRoot = true;
737
            } catch (SQLException ex) {
738
                throw new IllegalStateException("Erreur lors de la création de la table MODELE", ex);
739
            }
740
        }
741
 
742
        // Création de la table Modéle
743
        if (!root.contains("CONTACT_FOURNISSEUR")) {
744
 
745
            SQLCreateTable createModele = new SQLCreateTable(root, "CONTACT_FOURNISSEUR");
746
            createModele.addVarCharColumn("NOM", 256);
747
            createModele.addVarCharColumn("PRENOM", 256);
748
            createModele.addVarCharColumn("TEL_DIRECT", 256);
749
            createModele.addVarCharColumn("TEL_MOBILE", 256);
750
            createModele.addVarCharColumn("EMAIL", 256);
751
            createModele.addVarCharColumn("FAX", 256);
752
            createModele.addVarCharColumn("FONCTION", 256);
753
            createModele.addVarCharColumn("TEL_PERSONEL", 256);
754
            createModele.addVarCharColumn("TEL_STANDARD", 256);
755
            createModele.addForeignColumn("ID_TITRE_PERSONNEL", root.findTable("TITRE_PERSONNEL"));
756
            createModele.addForeignColumn("ID_FOURNISSEUR", root.findTable("FOURNISSEUR"));
757
 
758
            try {
759
                ds.execute(createModele.asString());
760
                insertUndef(createModele);
761
                tableDevis.getSchema().updateVersion();
762
                refetchRoot = true;
763
            } catch (SQLException ex) {
764
                throw new IllegalStateException("Erreur lors de la création de la table MODELE", ex);
765
            }
766
        }
767
 
19 ilm 768
        // Création de la table Tarif
769
        if (!root.contains("TARIF")) {
770
 
771
            SQLCreateTable createTarif = new SQLCreateTable(root, "TARIF");
772
            createTarif.addVarCharColumn("NOM", 256);
773
            createTarif.addForeignColumn("ID_DEVISE", root.findTable("DEVISE"));
774
            createTarif.addForeignColumn("ID_TAXE", root.findTable("TAXE"));
775
            createTarif.asString();
18 ilm 776
            try {
19 ilm 777
                ds.execute(createTarif.asString());
778
                insertUndef(createTarif);
779
                tableDevis.getSchema().updateVersion();
780
                refetchRoot = true;
18 ilm 781
            } catch (SQLException ex) {
19 ilm 782
                throw new IllegalStateException("Erreur lors de la création de la table TARIF", ex);
18 ilm 783
            }
784
        }
19 ilm 785
        if (refetchRoot)
786
            root.refetch();
18 ilm 787
 
19 ilm 788
        // Création de la table article Tarif
789
        if (!root.contains("ARTICLE_TARIF")) {
790
 
791
            SQLCreateTable createTarif = new SQLCreateTable(root, "ARTICLE_TARIF");
792
            createTarif.addForeignColumn("ID_DEVISE", root.findTable("DEVISE"));
793
            createTarif.addForeignColumn("ID_TAXE", root.findTable("TAXE"));
794
            createTarif.addForeignColumn("ID_TARIF", root.findTable("TARIF"));
795
            createTarif.addForeignColumn("ID_ARTICLE", root.findTable("ARTICLE"));
796
            createTarif.addColumn("PV_HT", "bigint DEFAULT 0");
797
            createTarif.addColumn("PV_TTC", "bigint DEFAULT 0");
798
            createTarif.addColumn("PRIX_METRIQUE_VT_1", "bigint DEFAULT 0");
799
            createTarif.addColumn("PRIX_METRIQUE_VT_2", "bigint DEFAULT 0");
800
            createTarif.addColumn("PRIX_METRIQUE_VT_3", "bigint DEFAULT 0");
801
            createTarif.asString();
18 ilm 802
            try {
19 ilm 803
                ds.execute(createTarif.asString());
804
                insertUndef(createTarif);
805
                tableDevis.getSchema().updateVersion();
806
            } catch (SQLException ex) {
807
                throw new IllegalStateException("Erreur lors de la création de la table ARTICLE_TARIF", ex);
18 ilm 808
            }
809
        }
810
 
19 ilm 811
        // Création de la table article Désignation
812
        if (!root.contains("ARTICLE_DESIGNATION")) {
813
 
814
            SQLCreateTable createTarif = new SQLCreateTable(root, "ARTICLE_DESIGNATION");
815
            createTarif.addForeignColumn("ID_ARTICLE", root.findTable("ARTICLE"));
816
            createTarif.addForeignColumn("ID_LANGUE", root.findTable("LANGUE"));
817
            createTarif.addVarCharColumn("NOM", 1024);
818
            createTarif.asString();
18 ilm 819
            try {
19 ilm 820
                ds.execute(createTarif.asString());
821
                insertUndef(createTarif);
822
                tableDevis.getSchema().updateVersion();
823
            } catch (SQLException ex) {
824
                throw new IllegalStateException("Erreur lors de la création de la table ARTICLE_DESIGNATION", ex);
18 ilm 825
            }
826
        }
827
 
19 ilm 828
        SQLTable tableVFElt = root.getTable("SAISIE_VENTE_FACTURE_ELEMENT");
829
        addTarifField(tableVFElt, root);
18 ilm 830
 
19 ilm 831
        SQLTable tableDevisElt = root.getTable("DEVIS_ELEMENT");
832
        addTarifField(tableDevisElt, root);
833
 
834
        SQLTable tableCmdElt = root.getTable("COMMANDE_CLIENT_ELEMENT");
835
        addTarifField(tableCmdElt, root);
836
 
837
        SQLTable tableBonElt = root.getTable("BON_DE_LIVRAISON_ELEMENT");
838
        addTarifField(tableBonElt, root);
839
 
840
        SQLTable tableAvoirElt = root.getTable("AVOIR_CLIENT_ELEMENT");
841
        addTarifField(tableAvoirElt, root);
842
 
843
        SQLTable tableCmdFournElt = root.getTable("COMMANDE_ELEMENT");
844
        addTotalDeviseHAField(tableCmdFournElt, root);
845
 
846
        SQLTable tableBonRecptElt = root.getTable("BON_RECEPTION_ELEMENT");
847
        addTotalDeviseHAField(tableBonRecptElt, root);
848
 
849
        SQLTable tableBonRecpt = root.getTable("BON_RECEPTION");
850
        addDeviseHAField(tableBonRecpt, root);
851
 
852
        SQLTable tableCommande = root.getTable("COMMANDE");
853
        addDeviseHAField(tableCommande, root);
854
 
855
        {
21 ilm 856
            addTotalDeviseField(tableDevis, root);
857
            addModeleField(tableDevis, root);
858
 
19 ilm 859
            SQLTable tableVF = root.getTable("SAISIE_VENTE_FACTURE");
860
            addTotalDeviseField(tableVF, root);
21 ilm 861
            addModeleField(tableVF, root);
19 ilm 862
 
863
            addTotalDeviseField(tableDevis, root);
21 ilm 864
            addModeleField(tableDevis, root);
19 ilm 865
 
866
            SQLTable tableCmd = root.getTable("COMMANDE_CLIENT");
867
            addTotalDeviseField(tableCmd, root);
21 ilm 868
            addModeleField(tableCmd, root);
19 ilm 869
 
870
            SQLTable tableBon = root.getTable("BON_DE_LIVRAISON");
871
            addTotalDeviseField(tableBon, root);
21 ilm 872
            addModeleField(tableBon, root);
19 ilm 873
 
874
            SQLTable tableAvoir = root.getTable("AVOIR_CLIENT");
875
            addTotalDeviseField(tableAvoir, root);
21 ilm 876
            addModeleField(tableAvoir, root);
18 ilm 877
        }
19 ilm 878
        // Change client
879
        {
880
            SQLTable tableClient = root.getTable("CLIENT");
18 ilm 881
 
19 ilm 882
            AlterTable tClient = new AlterTable(tableClient);
883
            boolean alterClient = false;
884
 
885
            if (!tableClient.getFieldsName().contains("ID_TARIF")) {
886
                tClient.addForeignColumn("ID_TARIF", root.findTable("TARIF"));
887
                alterClient = true;
888
            }
889
            if (!tableClient.getFieldsName().contains("ID_PAYS")) {
890
                tClient.addForeignColumn("ID_PAYS", root.findTable("PAYS"));
891
                alterClient = true;
892
            }
893
            if (!tableClient.getFieldsName().contains("ID_LANGUE")) {
894
                tClient.addForeignColumn("ID_LANGUE", root.findTable("LANGUE"));
895
                alterClient = true;
896
            }
897
 
898
            if (!tableClient.getFieldsName().contains("ID_DEVISE")) {
899
                tClient.addForeignColumn("ID_DEVISE", root.findTable("DEVISE"));
900
                alterClient = true;
901
            }
902
            if (alterClient) {
903
                try {
904
                    ds.execute(tClient.asString());
905
                    tableClient.getSchema().updateVersion();
906
                } catch (SQLException ex) {
907
                    throw new IllegalStateException("Erreur lors de l'ajout des champs sur la table CLIENT", ex);
908
                }
909
            }
18 ilm 910
        }
911
 
19 ilm 912
        // Change Pays
913
        {
914
            SQLTable tablePays = root.getTable("PAYS");
915
 
916
            AlterTable tPays = new AlterTable(tablePays);
917
            boolean alterPays = false;
918
 
919
            if (!tablePays.getFieldsName().contains("ID_TARIF")) {
920
                tPays.addForeignColumn("ID_TARIF", root.findTable("TARIF"));
921
                alterPays = true;
922
            }
923
            if (!tablePays.getFieldsName().contains("ID_LANGUE")) {
924
                tPays.addForeignColumn("ID_LANGUE", root.findTable("LANGUE"));
925
                alterPays = true;
926
            }
927
            if (alterPays) {
928
                try {
929
                    ds.execute(tPays.asString());
930
                    tablePays.getSchema().updateVersion();
931
                } catch (SQLException ex) {
932
                    throw new IllegalStateException("Erreur lors de l'ajout des champs sur la table PAYS", ex);
933
                }
934
            }
935
        }
936
        // Change Commande
937
        {
938
            SQLTable tableCmd = root.getTable("COMMANDE");
939
 
940
            AlterTable tCmd = new AlterTable(tableCmd);
941
            boolean alterCmd = false;
942
 
943
            if (!tableCmd.getFieldsName().contains("EN_COURS")) {
944
                tCmd.addColumn("EN_COURS", "boolean default true");
945
                alterCmd = true;
946
            }
947
            if (alterCmd) {
948
                try {
949
                    ds.execute(tCmd.asString());
950
                    tableCmd.getSchema().updateVersion();
951
                } catch (SQLException ex) {
952
                    throw new IllegalStateException("Erreur lors de l'ajout des champs sur la table COMMANDE", ex);
953
                }
954
            }
955
        }
956
        // Change Fournisseur
957
        {
958
            SQLTable tableFournisseur = root.getTable("FOURNISSEUR");
959
 
960
            AlterTable tFourn = new AlterTable(tableFournisseur);
961
            boolean alterFourn = false;
962
 
963
            if (!tableFournisseur.getFieldsName().contains("ID_LANGUE")) {
964
                tFourn.addForeignColumn("ID_LANGUE", root.findTable("LANGUE"));
965
                alterFourn = true;
966
            }
967
            if (!tableFournisseur.getFieldsName().contains("ID_DEVISE")) {
968
                tFourn.addForeignColumn("ID_DEVISE", root.findTable("DEVISE"));
969
                alterFourn = true;
970
            }
971
            if (!tableFournisseur.getFieldsName().contains("RESPONSABLE")) {
972
                tFourn.addVarCharColumn("RESPONSABLE", 256);
973
                alterFourn = true;
974
            }
975
            if (!tableFournisseur.getFieldsName().contains("TEL_P")) {
976
                tFourn.addVarCharColumn("TEL_P", 256);
977
                alterFourn = true;
978
            }
979
            if (!tableFournisseur.getFieldsName().contains("MAIL")) {
980
                tFourn.addVarCharColumn("MAIL", 256);
981
                alterFourn = true;
982
            }
983
            if (!tableFournisseur.getFieldsName().contains("INFOS")) {
984
                tFourn.addVarCharColumn("INFOS", 2048);
985
                alterFourn = true;
986
            }
987
 
988
            if (alterFourn) {
989
                try {
990
                    ds.execute(tFourn.asString());
991
                    tableFournisseur.getSchema().updateVersion();
992
                } catch (SQLException ex) {
993
                    throw new IllegalStateException("Erreur lors de l'ajout des champs sur la table FOURNISSEUR", ex);
994
                }
995
            }
996
        }
997
 
998
        root.refetch();
18 ilm 999
    }
1000
 
19 ilm 1001
    private void addDeviseHAField(SQLTable table, DBRoot root) throws SQLException {
1002
        boolean alter = false;
1003
        AlterTable t = new AlterTable(table);
1004
        if (!table.getFieldsName().contains("ID_DEVISE")) {
1005
            t.addForeignColumn("ID_DEVISE", root.findTable("DEVISE"));
1006
            alter = true;
1007
        }
1008
 
1009
        if (!table.getFieldsName().contains("T_DEVISE")) {
1010
            t.addColumn("T_DEVISE", "bigint default 0");
1011
            alter = true;
1012
        }
1013
 
1014
        if (alter) {
1015
            try {
1016
                table.getBase().getDataSource().execute(t.asString());
1017
                table.getSchema().updateVersion();
1018
                table.fetchFields();
1019
            } catch (SQLException ex) {
1020
                throw new IllegalStateException("Erreur lors de l'ajout des champs à la table " + table.getName(), ex);
1021
            }
1022
        }
1023
 
1024
    }
1025
 
1026
    private void addTotalDeviseHAField(SQLTable table, DBRoot root) throws SQLException {
1027
        boolean alter = false;
1028
        AlterTable t = new AlterTable(table);
1029
        if (!table.getFieldsName().contains("QTE_ACHAT")) {
1030
            t.addColumn("QTE_ACHAT", "integer DEFAULT 1");
1031
            alter = true;
1032
        }
1033
        if (!table.getFieldsName().contains("PA_DEVISE")) {
1034
            t.addColumn("PA_DEVISE", "bigint default 0");
1035
            alter = true;
1036
        }
1037
        if (!table.getFieldsName().contains("ID_DEVISE")) {
1038
            t.addForeignColumn("ID_DEVISE", root.findTable("DEVISE"));
1039
            alter = true;
1040
        }
1041
 
1042
        if (!table.getFieldsName().contains("PA_DEVISE_T")) {
1043
            t.addColumn("PA_DEVISE_T", "bigint default 0");
1044
            alter = true;
1045
        }
1046
 
1047
        if (alter) {
1048
            try {
1049
                table.getBase().getDataSource().execute(t.asString());
1050
                table.getSchema().updateVersion();
1051
                table.fetchFields();
1052
            } catch (SQLException ex) {
1053
                throw new IllegalStateException("Erreur lors de l'ajout des champs à la table " + table.getName(), ex);
1054
            }
1055
        }
1056
 
1057
    }
1058
 
21 ilm 1059
    private void addModeleField(SQLTable table, DBRoot root) throws SQLException {
1060
        boolean alter = false;
1061
        AlterTable t = new AlterTable(table);
1062
        if (!table.getFieldsName().contains("ID_MODELE")) {
1063
            t.addForeignColumn("ID_MODELE", root.findTable("MODELE"));
1064
            alter = true;
1065
        }
1066
 
1067
        if (alter) {
1068
            try {
1069
                table.getBase().getDataSource().execute(t.asString());
1070
                table.getSchema().updateVersion();
1071
                table.fetchFields();
1072
            } catch (SQLException ex) {
1073
                throw new IllegalStateException("Erreur lors de l'ajout des champs à la table " + table.getName(), ex);
1074
            }
1075
        }
1076
    }
1077
 
19 ilm 1078
    private void addTotalDeviseField(SQLTable table, DBRoot root) throws SQLException {
1079
        boolean alter = false;
1080
        AlterTable t = new AlterTable(table);
1081
        if (!table.getFieldsName().contains("T_DEVISE")) {
1082
            t.addColumn("T_DEVISE", "bigint default 0");
1083
            alter = true;
1084
        } else {
1085
            table.getBase().getDataSource().execute("UPDATE " + table.getSQLName().quote() + " SET \"T_DEVISE\"=0 WHERE \"T_DEVISE\" IS NULL");
1086
            t.alterColumn("T_DEVISE", EnumSet.allOf(Properties.class), "bigint", "0", false);
1087
        }
1088
        if (!table.getFieldsName().contains("T_POIDS")) {
1089
            t.addColumn("T_POIDS", "real default 0");
1090
            alter = true;
1091
        }
1092
        if (!table.getFieldsName().contains("ID_TARIF")) {
1093
            t.addForeignColumn("ID_TARIF", root.findTable("TARIF"));
1094
            alter = true;
1095
        }
1096
 
1097
        if (alter) {
1098
            try {
1099
                table.getBase().getDataSource().execute(t.asString());
1100
                table.getSchema().updateVersion();
1101
                table.fetchFields();
1102
            } catch (SQLException ex) {
1103
                throw new IllegalStateException("Erreur lors de l'ajout des champs à la table " + table.getName(), ex);
1104
            }
1105
        }
1106
    }
1107
 
1108
    private void addTarifField(SQLTable table, DBRoot root) throws SQLException {
1109
 
1110
        boolean alter = false;
1111
        AlterTable t = new AlterTable(table);
1112
        if (!table.getFieldsName().contains("QTE_ACHAT")) {
1113
            t.addColumn("QTE_ACHAT", "integer DEFAULT 1");
1114
            alter = true;
1115
        }
1116
        if (!table.getFieldsName().contains("CODE_DOUANIER")) {
1117
            t.addVarCharColumn("CODE_DOUANIER", 256);
1118
            alter = true;
1119
        }
21 ilm 1120
        if (!table.getFieldsName().contains("DESCRIPTIF")) {
1121
            t.addVarCharColumn("DESCRIPTIF", 2048);
1122
            alter = true;
1123
        }
19 ilm 1124
        if (!table.getFieldsName().contains("ID_PAYS")) {
1125
            t.addForeignColumn("ID_PAYS", root.findTable("PAYS"));
1126
            alter = true;
1127
        }
21 ilm 1128
        if (!table.getFieldsName().contains("MARGE_HT")) {
1129
            t.addColumn("MARGE_HT", "bigint default 0");
1130
            alter = true;
1131
        }
19 ilm 1132
 
1133
        if (!table.getFieldsName().contains("ID_DEVISE")) {
1134
            t.addForeignColumn("ID_DEVISE", root.findTable("DEVISE"));
1135
            alter = true;
1136
        }
1137
        if (!table.getFieldsName().contains("PV_U_DEVISE")) {
1138
            t.addColumn("PV_U_DEVISE", "bigint default 0");
1139
            alter = true;
1140
        }
1141
        if (!table.getFieldsName().contains("POURCENT_REMISE")) {
1142
            t.addColumn("POURCENT_REMISE", "numeric(6,2) default 0");
1143
            alter = true;
1144
        }
1145
        if (!table.getFieldsName().contains("PV_T_DEVISE")) {
1146
            t.addColumn("PV_T_DEVISE", "bigint default 0");
1147
            alter = true;
1148
        }
1149
        if (!table.getFieldsName().contains("TAUX_DEVISE")) {
1150
            t.addColumn("TAUX_DEVISE", "numeric (16,8) DEFAULT 1");
1151
            alter = true;
1152
        }
1153
        if (alter) {
1154
            try {
1155
                root.getDBSystemRoot().getDataSource().execute(t.asString());
1156
                table.getSchema().updateVersion();
1157
                table.fetchFields();
1158
            } catch (SQLException ex) {
1159
                throw new IllegalStateException("Erreur lors de l'ajout des champs à la table " + table.getName(), ex);
1160
            }
1161
        }
1162
    }
1163
 
1164
    private void updateSocieteSchema(final DBRoot root) throws SQLException {
1165
        final DBSystemRoot sysRoot = root.getDBSystemRoot();
1166
        final SQLDataSource ds = sysRoot.getDataSource();
1167
        System.out.println("InstallationPanel.InstallationPanel() UPDATE COMMERCIAL " + root);
1168
        // Fix commercial Ordre
1169
        SQLTable tableCommercial = root.getTable("COMMERCIAL");
1170
        CorrectOrder orderCorrect = new CorrectOrder(sysRoot);
1171
        orderCorrect.change(tableCommercial);
1172
 
1173
        new AddFK(sysRoot).change(root);
1174
        root.getSchema().updateVersion();
1175
        root.refetch();
1176
        // load graph now so that it's coherent with the structure
1177
        // that way we can add foreign columns after without refreshing
1178
        // 1. root.refetch() clears the graph
1179
        // 2. we add some foreign field (the graph is still null)
1180
        // 3. we use a method that needs the graph
1181
        // 4. the graph is created and throws an exception when it wants to use the new field not in
1182
        // the structure
1183
        sysRoot.getGraph();
1184
 
1185
        try {
1186
            // Add article
1187
            final SQLTable tableArticle = root.getTable("ARTICLE");
1188
            if (!tableArticle.getFieldsName().contains("INFOS")) {
1189
                AlterTable t = new AlterTable(tableArticle);
1190
                t.addVarCharColumn("INFOS", 2048);
1191
                try {
1192
                    ds.execute(t.asString());
1193
                } catch (Exception ex) {
1194
                    throw new IllegalStateException("Erreur lors de l'ajout du champ INFO à la table ARTICLE", ex);
1195
                }
1196
            }
1197
 
1198
            if (sysRoot.getServer().getSQLSystem().equals(SQLSystem.POSTGRESQL)) {
1199
                // Fix Caisse serial
1200
                SQLTable tableCaisse = root.getTable("CAISSE");
1201
 
1202
                FixSerial f = new FixSerial(sysRoot);
1203
                try {
1204
                    f.change(tableCaisse);
1205
                } catch (SQLException e2) {
1206
                    throw new IllegalStateException("Erreur lors la mise à jours des sequences de la table CAISSE", e2);
1207
                }
1208
            }
1209
            System.out.println("InstallationPanel.InstallationPanel() UPDATE TICKET_CAISSE " + root);
1210
            // add Mvt on Ticket
1211
            SQLTable tableTicket = root.getTable("TICKET_CAISSE");
1212
            if (!tableTicket.getFieldsName().contains("ID_MOUVEMENT")) {
1213
                AlterTable t = new AlterTable(tableTicket);
1214
                t.addForeignColumn("ID_MOUVEMENT", root.getTable("MOUVEMENT"));
1215
                try {
1216
                    ds.execute(t.asString());
1217
                } catch (Exception ex) {
1218
                    throw new IllegalStateException("Erreur lors de l'ajout du champ ID_MOUVEMENT à la table TICKET_CAISSE", ex);
1219
                }
1220
            }
1221
 
1222
            // Check type de reglement
1223
 
1224
            System.out.println("InstallationPanel.InstallationPanel() UPDATE TYPE_REGLEMENT " + root);
1225
            SQLTable tableReglmt = root.getTable("TYPE_REGLEMENT");
1226
            SQLSelect sel = new SQLSelect(tableReglmt.getBase());
1227
            sel.addSelect(tableReglmt.getKey());
1228
            sel.setWhere(new Where(tableReglmt.getField("NOM"), "=", "Virement"));
1229
            List<Number> l = (List<Number>) ds.executeCol(sel.asString());
1230
            if (l.size() == 0) {
1231
                SQLRowValues rowVals = new SQLRowValues(tableReglmt);
1232
                rowVals.put("NOM", "Virement");
1233
                rowVals.put("COMPTANT", Boolean.FALSE);
1234
                rowVals.put("ECHEANCE", Boolean.FALSE);
1235
                try {
1236
                    rowVals.commit();
1237
                } catch (SQLException e) {
1238
                    throw new IllegalStateException("Erreur lors de l'ajout du type de paiement par virement", e);
1239
                }
1240
            }
1241
 
1242
            SQLSelect sel2 = new SQLSelect(tableReglmt.getBase());
1243
            sel2.addSelect(tableReglmt.getKey());
1244
            sel2.setWhere(new Where(tableReglmt.getField("NOM"), "=", "CESU"));
1245
            List<Number> l2 = (List<Number>) ds.executeCol(sel2.asString());
1246
            if (l2.size() == 0) {
1247
                SQLRowValues rowVals = new SQLRowValues(tableReglmt);
1248
                rowVals.put("NOM", "CESU");
1249
                rowVals.put("COMPTANT", Boolean.FALSE);
1250
                rowVals.put("ECHEANCE", Boolean.FALSE);
1251
                try {
1252
                    rowVals.commit();
1253
                } catch (SQLException e) {
1254
                    throw new IllegalStateException("Erreur lors de l'ajout du type CESU", e);
1255
                }
1256
            }
1257
            System.out.println("InstallationPanel.InstallationPanel() UPDATE FAMILLE_ARTICLE " + root);
1258
            //
1259
            final SQLTable tableFam = root.getTable("FAMILLE_ARTICLE");
1260
            final int nomSize = 256;
1261
            if (tableFam.getField("NOM").getType().getSize() < nomSize) {
1262
                final AlterTable t = new AlterTable(tableFam);
1263
                t.alterColumn("NOM", EnumSet.allOf(Properties.class), "varchar(" + nomSize + ")", "''", false);
1264
                try {
1265
                    ds.execute(t.asString());
1266
                } catch (Exception ex) {
1267
                    throw new IllegalStateException("Erreur lors de la modification du champs NOM sur la table FAMILLE_ARTICLE", ex);
1268
                }
1269
            }
1270
 
1271
            // Suppression des champs 1.0
1272
            System.out.println("InstallationPanel.InstallationPanel() UPDATE FROM 1.0 " + root);
1273
            final List<ChangeTable<?>> changes = new ArrayList<ChangeTable<?>>();
1274
 
1275
            List<String> tablesToRemove = new ArrayList<String>();
1276
            tablesToRemove.add("AFFAIRE");
1277
            tablesToRemove.add("RAPPORT");
1278
            tablesToRemove.add("CODE_MISSION");
1279
            tablesToRemove.add("FICHE_RENDEZ_VOUS");
1280
            tablesToRemove.add("NATURE_MISSION");
1281
            tablesToRemove.add("AVIS_INTERVENTION");
1282
            tablesToRemove.add("POURCENT_CCIP");
1283
            tablesToRemove.add("SECRETAIRE");
1284
            tablesToRemove.add("FICHE_RENDEZ_VOUS_ELEMENT");
1285
            tablesToRemove.add("POURCENT_SERVICE");
1286
            tablesToRemove.add("PROPOSITION");
1287
            tablesToRemove.add("AFFAIRE_ELEMENT");
1288
            tablesToRemove.add("PROPOSITION_ELEMENT");
1289
            tablesToRemove.add("POLE_PRODUIT");
1290
            tablesToRemove.add("BANQUE_POLE_PRODUIT");
1291
            tablesToRemove.add("AFFACTURAGE");
1292
            tablesToRemove.add("SECTEUR_ACTIVITE");
1293
 
1294
            final DatabaseGraph graph = sysRoot.getGraph();
1295
            for (String tableName : tablesToRemove) {
1296
                if (root.contains(tableName)) {
1297
 
1298
                    final SQLTable table = root.getTable(tableName);
1299
                    for (final Link link : graph.getReferentLinks(table)) {
1300
                        if (!(link.getSource().getDBRoot() == root && tablesToRemove.contains(link.getSource().getName()))) {
1301
                            final AlterTable alter = new AlterTable(link.getSource());
1302
                            alter.dropForeignColumns(link);
1303
                            changes.add(alter);
1304
                        }
1305
                    }
1306
                    changes.add(new DropTable(table));
1307
                }
1308
            }
1309
 
1310
            final List<String> alterRequests = ChangeTable.cat(changes, root.getName());
1311
            try {
1312
                for (final String req : alterRequests) {
1313
                    ds.execute(req);
1314
                }
1315
            } catch (Exception e1) {
1316
                throw new IllegalStateException("Erreur lors de la mise à jour des tables v1.0", e1);
1317
            }
1318
            System.out.println("InstallationPanel.InstallationPanel() UPDATE CAISSE " + root);
1319
            // Undefined
1320
            try {
1321
                SQLTable.setUndefID(tableTicket.getSchema(), tableTicket.getName(), 1);
1322
                SQLTable.setUndefID(tableTicket.getSchema(), "CAISSE", 1);
1323
            } catch (SQLException e1) {
1324
                throw new IllegalStateException("Erreur lors de la mise à jour des indéfinis de la table CAISSE", e1);
1325
            }
1326
        } finally {
1327
            // Mise à jour du schéma
1328
            root.getSchema().updateVersion();
1329
            root.refetch();
1330
        }
1331
    }
1332
 
18 ilm 1333
    private void updateVariablePaye(SQLTable table, String var, double value) throws SQLException {
19 ilm 1334
        if (table == null) {
1335
            throw new IllegalArgumentException("null table");
1336
        }
18 ilm 1337
        SQLSelect sel = new SQLSelect(table.getBase());
1338
        sel.addSelectStar(table);
1339
        sel.setWhere(new Where(table.getField("NOM"), "=", var));
1340
        List<SQLRow> l = (List<SQLRow>) table.getBase().getDataSource().execute(sel.asString(), SQLRowListRSH.createFromSelect(sel));
1341
 
1342
        for (SQLRow sqlRow : l) {
1343
            SQLRowValues rowVals = sqlRow.asRowValues();
1344
            rowVals.put("VALEUR", value);
1345
            rowVals.update();
1346
        }
1347
    }
1348
 
1349
}