OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

Rev 156 | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
142 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.update;
15
 
16
import org.openconcerto.erp.config.InstallationPanel;
156 ilm 17
import org.openconcerto.erp.core.common.ui.AbstractVenteArticleItemTable;
174 ilm 18
import org.openconcerto.erp.core.customerrelationship.mail.EmailTemplateSQLElement;
151 ilm 19
import org.openconcerto.erp.core.finance.payment.element.SDDMessageSQLElement;
20
import org.openconcerto.erp.core.finance.payment.element.SEPAMandateSQLElement;
21
import org.openconcerto.erp.core.finance.payment.element.TypeReglementSQLElement;
22
import org.openconcerto.erp.core.sales.invoice.element.SaisieVenteFactureSQLElement;
144 ilm 23
import org.openconcerto.erp.core.sales.order.ui.TypeFactureCommandeClient;
24
import org.openconcerto.erp.core.sales.pos.element.TicketCaisseSQLElement;
25
import org.openconcerto.erp.core.sales.product.element.ReferenceArticleSQLElement;
149 ilm 26
import org.openconcerto.erp.core.sales.quote.element.DevisLogMailSQLElement;
174 ilm 27
import org.openconcerto.erp.core.supplychain.stock.element.ComposedItemStockUpdater;
156 ilm 28
import org.openconcerto.erp.core.supplychain.stock.element.DepotStockSQLElement;
29
import org.openconcerto.erp.core.supplychain.stock.element.StockItem;
144 ilm 30
import org.openconcerto.sql.changer.convert.AddMDFields;
142 ilm 31
import org.openconcerto.sql.model.DBRoot;
156 ilm 32
import org.openconcerto.sql.model.SQLField;
142 ilm 33
import org.openconcerto.sql.model.SQLField.Properties;
34
import org.openconcerto.sql.model.SQLName;
156 ilm 35
import org.openconcerto.sql.model.SQLRow;
36
import org.openconcerto.sql.model.SQLRowListRSH;
144 ilm 37
import org.openconcerto.sql.model.SQLRowValues;
156 ilm 38
import org.openconcerto.sql.model.SQLSelect;
144 ilm 39
import org.openconcerto.sql.model.SQLSyntax;
40
import org.openconcerto.sql.model.SQLSystem;
142 ilm 41
import org.openconcerto.sql.model.SQLTable;
174 ilm 42
import org.openconcerto.sql.model.SQLTable.Index;
142 ilm 43
import org.openconcerto.sql.model.Where;
156 ilm 44
import org.openconcerto.sql.preferences.SQLPreferences;
142 ilm 45
import org.openconcerto.sql.request.UpdateBuilder;
46
import org.openconcerto.sql.utils.AlterTable;
149 ilm 47
import org.openconcerto.sql.utils.ChangeTable;
142 ilm 48
import org.openconcerto.sql.utils.SQLCreateTable;
144 ilm 49
import org.openconcerto.sql.utils.UniqueConstraintCreatorHelper;
151 ilm 50
import org.openconcerto.utils.CollectionUtils;
156 ilm 51
import org.openconcerto.utils.ExceptionHandler;
142 ilm 52
 
53
import java.math.BigDecimal;
156 ilm 54
import java.sql.Clob;
142 ilm 55
import java.sql.SQLException;
56
import java.sql.Types;
156 ilm 57
import java.util.ArrayList;
142 ilm 58
import java.util.Arrays;
156 ilm 59
import java.util.Collections;
142 ilm 60
import java.util.EnumSet;
156 ilm 61
import java.util.HashMap;
142 ilm 62
import java.util.List;
156 ilm 63
import java.util.Map;
174 ilm 64
import java.util.Set;
156 ilm 65
import java.util.prefs.BackingStoreException;
142 ilm 66
 
156 ilm 67
import org.apache.commons.dbutils.handlers.ArrayListHandler;
68
 
142 ilm 69
public class Updater_1_5 {
70
    private static final String COMPTE_CLIENT_TRANSACTION = "COMPTE_CLIENT_TRANSACTION";
71
 
72
    public static void update(final DBRoot root) throws SQLException {
73
        // Champ obsolete sur compte
74
        SQLTable tableCompte = root.getTable("COMPTE_PCE");
75
        if (!tableCompte.contains("OBSOLETE")) {
76
            final AlterTable alter = new AlterTable(tableCompte);
77
            alter.addBooleanColumn("OBSOLETE", Boolean.FALSE, false);
78
            tableCompte.getBase().getDataSource().execute(alter.asString());
79
            tableCompte.getSchema().updateVersion();
80
            tableCompte.fetchFields();
81
        }
82
 
83
        // Transaction du solde
84
        if (!root.contains(COMPTE_CLIENT_TRANSACTION)) {
85
            final SQLCreateTable createTable = new SQLCreateTable(root, COMPTE_CLIENT_TRANSACTION);
86
            createTable.addForeignColumn("CLIENT");
87
            createTable.addDateAndTimeColumn("DATE");
88
            createTable.addDecimalColumn("MONTANT", 16, 6, BigDecimal.valueOf(0), false);
89
            createTable.addForeignColumn("MODE_REGLEMENT");
90
            createTable.addForeignColumn("MOUVEMENT");
91
            try {
92
                root.getBase().getDataSource().execute(createTable.asString());
93
                InstallationPanel.insertUndef(createTable);
94
                root.refetchTable(COMPTE_CLIENT_TRANSACTION);
95
                root.getSchema().updateVersion();
96
            } catch (SQLException ex) {
97
                throw new IllegalStateException("Erreur lors de la création de la table " + COMPTE_CLIENT_TRANSACTION, ex);
98
            }
99
        }
100
        // Solde
101
        final SQLTable tClient = root.getTable("CLIENT");
102
        if (!tClient.contains("SOLDE_COMPTE")) {
103
            final AlterTable alterClient = new AlterTable(tClient);
104
            alterClient.addDecimalColumn("SOLDE_COMPTE", 16, 6, BigDecimal.valueOf(0), false);
105
            tClient.getBase().getDataSource().execute(alterClient.asString());
106
            tClient.getSchema().updateVersion();
107
            tClient.fetchFields();
108
        }
151 ilm 109
        if (!tClient.contains("DATE")) {
110
            final AlterTable alterClient = new AlterTable(tClient);
111
            alterClient.addColumn("DATE", "date");
112
            tClient.getBase().getDataSource().execute(alterClient.asString());
113
            tClient.getSchema().updateVersion();
114
            tClient.fetchFields();
115
        }
144 ilm 116
        if (!tClient.contains("COMMENTAIRES")) {
117
            final AlterTable alterClient = new AlterTable(tClient);
118
            alterClient.addVarCharColumn("COMMENTAIRES", 2048);
119
            tClient.getBase().getDataSource().execute(alterClient.asString());
120
            tClient.getSchema().updateVersion();
121
            tClient.fetchFields();
122
        }
142 ilm 123
 
124
        final SQLTable tCompteClient = root.getTable("COMPTE_CLIENT_TRANSACTION");
125
        if (!tCompteClient.contains("ID_MOUVEMENT")) {
126
            final AlterTable alterClient = new AlterTable(tCompteClient);
127
            alterClient.addForeignColumn("ID_MOUVEMENT", root.getTable("MOUVEMENT"));
128
            tClient.getBase().getDataSource().execute(alterClient.asString());
129
            tClient.getSchema().updateVersion();
130
            tClient.fetchFields();
131
        }
132
 
133
        final SQLTable tCmdClient = root.getTable("COMMANDE_CLIENT");
134
        if (!tCmdClient.contains("DATE_LIVRAISON_PREV")) {
135
            final AlterTable alterCmdClient = new AlterTable(tCmdClient);
136
            alterCmdClient.addColumn("DATE_LIVRAISON_PREV", "date");
137
            tCmdClient.getBase().getDataSource().execute(alterCmdClient.asString());
138
            tCmdClient.getSchema().updateVersion();
139
            tCmdClient.fetchFields();
140
        }
141
 
149 ilm 142
        if (!tClient.contains("CONDITIONS_LIVRAISON")) {
143
            final AlterTable alterClient = new AlterTable(tClient);
144
            alterClient.addVarCharColumn("CONDITIONS_LIVRAISON", 512);
145
            tClient.getBase().getDataSource().execute(alterClient.asString());
146
            tClient.getSchema().updateVersion();
147
            tClient.fetchFields();
148
        }
149
 
142 ilm 150
        {
151
            // Ajout du champ SANS_VALEUR_ENCAISSEMENT pour gérer les anciens cheques sans le compte
152
            // 511
153
            List<String> tablesCheque = Arrays.asList("CHEQUE_A_ENCAISSER", "CHEQUE_FOURNISSEUR");
154
 
155
            for (String string : tablesCheque) {
156
 
157
                final SQLTable table = root.getTable(string);
158
                if (!table.contains("SANS_VALEUR_ENCAISSEMENT")) {
159
                    AlterTable alterElt = new AlterTable(table);
160
                    alterElt.addBooleanColumn("SANS_VALEUR_ENCAISSEMENT", Boolean.FALSE, false);
161
                    table.getBase().getDataSource().execute(alterElt.asString());
162
                    root.refetchTable(string);
163
                    root.getSchema().updateVersion();
164
                    UpdateBuilder upBuilder = new UpdateBuilder(table);
165
                    upBuilder.setObject("SANS_VALEUR_ENCAISSEMENT", Boolean.TRUE);
166
                    table.getBase().getDataSource().execute(upBuilder.asString());
167
                }
168
            }
169
 
170
            SQLTable tableEncElt = root.getTable("ENCAISSER_MONTANT_ELEMENT");
171
            if (tableEncElt.getField("DATE").getType().getType() == Types.TIMESTAMP) {
172
                AlterTable t = new AlterTable(tableEncElt);
173
                t.alterColumn("DATE", EnumSet.allOf(Properties.class), "date", null, Boolean.TRUE);
174
                tableEncElt.getBase().getDataSource().execute(t.asString());
175
                root.refetchTable(tableEncElt.getName());
176
                root.getSchema().updateVersion();
177
            }
178
        }
179
 
180
        // TVA Intra
181
        final SQLTable tTva = root.getTable("TAXE");
182
        if (!tTva.contains("ID_COMPTE_PCE_COLLECTE_INTRA")) {
183
            final AlterTable alterTaxe = new AlterTable(tTva);
184
            alterTaxe.addForeignColumn("ID_COMPTE_PCE_COLLECTE_INTRA", root.getTable("COMPTE_PCE"));
185
            alterTaxe.addForeignColumn("ID_COMPTE_PCE_DED_INTRA", root.getTable("COMPTE_PCE"));
186
            tTva.getBase().getDataSource().execute(alterTaxe.asString());
187
            tTva.getSchema().updateVersion();
188
            tTva.fetchFields();
189
        }
190
 
191
        if (!root.contains("TAXE_COMPLEMENTAIRE")) {
192
            final SQLCreateTable createTable = new SQLCreateTable(root, "TAXE_COMPLEMENTAIRE");
193
            createTable.addForeignColumn("ID_COMPTE_PCE_PRODUITS", root.getTable("COMPTE_PCE"));
194
            createTable.addForeignColumn("ID_COMPTE_PCE", root.getTable("COMPTE_PCE"));
195
            createTable.addDecimalColumn("POURCENT", 16, 6, BigDecimal.valueOf(0), false);
196
            createTable.addVarCharColumn("CODE", 25);
197
            createTable.addVarCharColumn("NOM", 256);
198
 
199
            try {
200
                root.getBase().getDataSource().execute(createTable.asString());
201
                InstallationPanel.insertUndef(createTable);
202
                root.refetchTable("TAXE_COMPLEMENTAIRE");
203
                root.getSchema().updateVersion();
204
            } catch (SQLException ex) {
205
                throw new IllegalStateException("Erreur lors de la création de la table " + "TAXE_COMPLEMENTAIRE", ex);
206
            }
207
 
208
            SQLTable tableArt = root.getTable("ARTICLE");
209
            final AlterTable alterArt = new AlterTable(tableArt);
210
            alterArt.addForeignColumn("ID_TAXE_COMPLEMENTAIRE", root.getTable("TAXE_COMPLEMENTAIRE"));
211
            tableArt.getBase().getDataSource().execute(alterArt.asString());
212
            tableArt.getSchema().updateVersion();
213
            tableArt.fetchFields();
214
        }
215
 
216
        // GED
217
        if (!root.contains("ATTACHMENT")) {
218
            final SQLCreateTable createTable = new SQLCreateTable(root, "ATTACHMENT");
219
            createTable.addVarCharColumn("SOURCE_TABLE", 128);
220
            createTable.addIntegerColumn("SOURCE_ID", 0);
221
            createTable.addVarCharColumn("NAME", 256);
222
            createTable.addVarCharColumn("MIMETYPE", 256);
223
            createTable.addVarCharColumn("FILENAME", 256);
224
            createTable.addLongColumn("FILESIZE", 0L, false);
225
            createTable.addVarCharColumn("STORAGE_PATH", 256);
226
            createTable.addVarCharColumn("STORAGE_FILENAME", 256);
227
            createTable.addVarCharColumn("DIRECTORY", 256);
228
            createTable.addVarCharColumn("THUMBNAIL", 256);
229
            createTable.addIntegerColumn("THUMBNAIL_WIDTH", 32);
230
            createTable.addIntegerColumn("THUMBNAIL_HEIGHT", 32);
231
            createTable.addVarCharColumn("TAG", 128);
232
            createTable.addIntegerColumn("VERSION", 0);
233
            createTable.addVarCharColumn("HASH", 32);
234
 
235
            try {
236
                root.getBase().getDataSource().execute(createTable.asString());
237
                InstallationPanel.insertUndef(createTable);
238
                root.refetchTable("ATTACHMENT");
239
                root.getSchema().updateVersion();
240
            } catch (SQLException ex) {
241
                throw new IllegalStateException("Erreur lors de la création de la table " + "ATTACHMENT", ex);
242
            }
243
        }
244
        SQLTable tableAttachment = root.getTable("ATTACHMENT");
245
        if (!tableAttachment.contains("DIRECTORY")) {
246
            final AlterTable alter = new AlterTable(tableAttachment);
247
            alter.addVarCharColumn("STORAGE_FILENAME", 256);
248
            alter.addVarCharColumn("DIRECTORY", 256);
249
            tableAttachment.getBase().getDataSource().execute(alter.asString());
250
            tableAttachment.getSchema().updateVersion();
251
            tableAttachment.fetchFields();
252
        }
149 ilm 253
        if (!tableAttachment.contains("ID_PARENT")) {
254
            final AlterTable alter = new AlterTable(tableAttachment);
255
            alter.addForeignColumn("ID_PARENT", tableAttachment);
256
            tableAttachment.getBase().getDataSource().execute(alter.asString());
257
            tableAttachment.getSchema().updateVersion();
258
            tableAttachment.fetchFields();
259
        }
142 ilm 260
 
261
        if (!tableAttachment.contains("VERSION")) {
262
            final AlterTable alter = new AlterTable(tableAttachment);
263
            alter.addIntegerColumn("VERSION", 0);
264
            alter.addVarCharColumn("HASH", 32);
265
            alter.addVarCharColumn("INFOS", 8000);
266
            tableAttachment.getBase().getDataSource().execute(alter.asString());
267
            tableAttachment.getSchema().updateVersion();
268
            tableAttachment.fetchFields();
269
        }
270
 
151 ilm 271
        List<String> gedTable = Arrays.asList("CLIENT", "MOUVEMENT", "FOURNISSEUR", "ARTICLE", "FACTURE_FOURNISSEUR", "SAISIE_VENTE_FACTURE", "SALARIE");
142 ilm 272
        for (String string : gedTable) {
273
            SQLTable tableGED = root.getTable(string);
274
            if (!tableGED.contains("ATTACHMENTS")) {
275
                final AlterTable alter = new AlterTable(tableGED);
276
                alter.addIntegerColumn("ATTACHMENTS", 0);
277
                tableGED.getBase().getDataSource().execute(alter.asString());
278
                tableGED.getSchema().updateVersion();
279
                tableGED.fetchFields();
280
            }
281
        }
282
 
283
        // gestion articles en attente
284
        {
285
 
286
            // Vente
287
            SQLTable tableBLElt = root.getTable("BON_DE_LIVRAISON_ELEMENT");
288
            if (!tableBLElt.contains("ID_COMMANDE_CLIENT_ELEMENT")) {
289
                AlterTable t = new AlterTable(tableBLElt);
290
                t.addForeignColumn("ID_COMMANDE_CLIENT_ELEMENT", root.getTable("COMMANDE_CLIENT_ELEMENT"));
291
                tableBLElt.getBase().getDataSource().execute(t.asString());
292
                root.refetchTable(tableBLElt.getName());
293
                root.getSchema().updateVersion();
294
            }
295
 
296
            SQLTable tableVFElt = root.getTable("SAISIE_VENTE_FACTURE_ELEMENT");
297
            if (!tableVFElt.contains("ID_COMMANDE_CLIENT_ELEMENT")) {
298
                AlterTable t = new AlterTable(tableVFElt);
299
                t.addForeignColumn("ID_COMMANDE_CLIENT_ELEMENT", root.getTable("COMMANDE_CLIENT_ELEMENT"));
300
                tableVFElt.getBase().getDataSource().execute(t.asString());
301
                root.refetchTable(tableVFElt.getName());
302
                root.getSchema().updateVersion();
303
            }
304
 
305
            SQLTable tableCmdElt = root.getTable("COMMANDE_CLIENT_ELEMENT");
306
            if (!tableCmdElt.contains("LIVRE")) {
307
                AlterTable t = new AlterTable(tableCmdElt);
156 ilm 308
                // FIXME mis par défaut à true avant --> à fixer
142 ilm 309
                t.addBooleanColumn("LIVRE_FORCED", Boolean.FALSE, false);
151 ilm 310
                t.addBooleanColumn("LIVRE", Boolean.FALSE, false);
142 ilm 311
                t.addDecimalColumn("QTE_LIVREE", 16, 6, BigDecimal.ZERO, true);
312
                tableCmdElt.getBase().getDataSource().execute(t.asString());
313
                root.refetchTable(tableCmdElt.getName());
314
                root.getSchema().updateVersion();
315
 
316
                // String up = "UPDATE " + new SQLName(root.getName(),
317
                // tableCmdElt.getName()).quote()
318
                // + " SET \"QTE_LIVREE\"=\"QTE\"*\"QTE_UNITAIRE\", \"LIVRE_FORCED\"=true WHERE
319
                // \"ID_COMMANDE_CLIENT\" IN []";
156 ilm 320
                {
321
                    List<String> tablesWorkFlow = Arrays.asList("SAISIE_VENTE_FACTURE", "BON_DE_LIVRAISON");
322
                    for (String tableWorkFlow : tablesWorkFlow) {
323
 
324
                        SQLTable tableCmdCli = root.getTable("COMMANDE_CLIENT");
325
                        SQLTable tableTR = root.getTable("TR_COMMANDE_CLIENT");
326
                        SQLTable tableBL = root.getTable(tableWorkFlow);
327
                        // SQLTable tableFactC = root.getTable("SAISIE_VENTE_FACTURE");
328
                        String sel = "SELECT t.\"ID_COMMANDE_CLIENT\" FROM " + new SQLName(root.getName(), tableTR.getName()).quote() + " t ,"
329
                                + new SQLName(root.getName(), tableCmdCli.getName()).quote() + " c ," + new SQLName(root.getName(), tableBL.getName()).quote()
330
                                + " b WHERE c.\"ID\"=t.\"ID_COMMANDE_CLIENT\"" + " AND b.\"ID\"=t.\"ID_" + tableWorkFlow
331
                                + "\" AND b.\"ARCHIVE\" = 0 AND t.\"ID\" > 1 AND c.\"ID\" > 1 AND  c.\"ARCHIVE\" = 0 GROUP BY t.\"ID_COMMANDE_CLIENT\" HAVING (SUM(b.\""
332
                                + (tableWorkFlow.startsWith("BON") ? "TOTAL_HT" : "T_HT") + "\")>=SUM(c.\"T_HT\") ) ";
333
                        List<Integer> cmd = tableTR.getDBSystemRoot().getDataSource().executeCol(sel);
334
                        UpdateBuilder build = new UpdateBuilder(tableCmdElt);
335
                        build.set("QTE_LIVREE", "\"QTE\"*\"QTE_UNITAIRE\"");
336
                        build.setObject("LIVRE_FORCED", Boolean.TRUE);
337
                        final Where where = new Where(tableCmdElt.getField("ID_COMMANDE_CLIENT"), cmd);
338
                        build.setWhere(where);
339
 
340
                        tableTR.getDBSystemRoot().getDataSource().execute(build.asString());
341
                    }
342
                }
142 ilm 343
            }
151 ilm 344
            // Fix bad default value
345
            if (tableCmdElt.contains("LIVRE")) {
346
                AlterTable t = new AlterTable(tableCmdElt);
347
                t.alterColumnDefault("LIVRE", "false");
348
                tableCmdElt.getBase().getDataSource().execute(t.asString());
349
                root.refetchTable(tableCmdElt.getName());
350
                root.getSchema().updateVersion();
156 ilm 351
                UpdateBuilder upLivre = new UpdateBuilder(tableCmdElt);
352
                upLivre.setObject("LIVRE", Boolean.FALSE);
353
                upLivre.setWhere(new Where(tableCmdElt.getKey(), "=", 1));
354
 
355
                tableCmdElt.getBase().getDataSource().execute(upLivre.asString());
151 ilm 356
            }
357
 
142 ilm 358
            // Achat
359
 
360
            SQLTable tableBRElt = root.getTable("BON_RECEPTION_ELEMENT");
361
            if (!tableBRElt.contains("ID_COMMANDE_ELEMENT")) {
362
                AlterTable t = new AlterTable(tableBRElt);
363
                t.addForeignColumn("ID_COMMANDE_ELEMENT", root.getTable("COMMANDE_ELEMENT"));
364
                tableBRElt.getBase().getDataSource().execute(t.asString());
365
                root.refetchTable(tableBRElt.getName());
366
                root.getSchema().updateVersion();
367
            }
368
 
369
            SQLTable tableCmdFElt = root.getTable("COMMANDE_ELEMENT");
370
            if (!tableCmdFElt.contains("RECU")) {
371
                AlterTable t = new AlterTable(tableCmdFElt);
372
                t.addBooleanColumn("RECU_FORCED", Boolean.FALSE, false);
373
                t.addBooleanColumn("RECU", Boolean.TRUE, false);
374
                t.addDecimalColumn("QTE_RECUE", 16, 6, BigDecimal.ZERO, true);
375
                tableCmdFElt.getBase().getDataSource().execute(t.asString());
376
                root.refetchTable(tableCmdFElt.getName());
377
                root.getSchema().updateVersion();
378
 
379
                // String up = "UPDATE " + new SQLName(root.getName(),
380
                // tableCmdElt.getName()).quote()
381
                // + " SET \"QTE_LIVREE\"=\"QTE\"*\"QTE_UNITAIRE\", \"LIVRE_FORCED\"=true WHERE
382
                // \"ID_COMMANDE_CLIENT\" IN []";
156 ilm 383
                {
384
                    SQLTable tableCmdCli = root.getTable("COMMANDE");
385
                    SQLTable tableTR = root.getTable("TR_COMMANDE");
386
                    SQLTable tableBR = root.getTable("BON_RECEPTION");
387
                    String sel = "SELECT t.\"ID_COMMANDE\" FROM " + new SQLName(root.getName(), tableTR.getName()).quote() + " t ," + new SQLName(root.getName(), tableCmdCli.getName()).quote()
388
                            + " c ," + new SQLName(root.getName(), tableBR.getName()).quote() + " b WHERE c.\"ID\"=t.\"ID_COMMANDE\""
389
                            + " AND b.\"ID\"=t.\"ID_BON_RECEPTION\" AND b.\"ARCHIVE\" = 0 AND t.\"ID\" > 1 AND c.\"ID\" > 1 AND c.\"ARCHIVE\" = 0 GROUP BY t.\"ID_COMMANDE\" HAVING (SUM(b.\"TOTAL_HT\")>=SUM(c.\"T_HT\")) ";
390
 
391
                    System.err.println(sel);
392
                    List<Object> cmd = tableTR.getDBSystemRoot().getDataSource().executeCol(sel);
393
                    UpdateBuilder build = new UpdateBuilder(tableCmdFElt);
394
                    build.set("QTE_RECUE", "\"QTE\"*\"QTE_UNITAIRE\"");
395
                    build.setObject("RECU_FORCED", Boolean.TRUE);
396
                    final Where where = new Where(tableCmdFElt.getField("ID_COMMANDE"), cmd);
397
                    build.setWhere(where);
398
                    System.err.println(build.asString());
399
                    tableTR.getDBSystemRoot().getDataSource().execute(build.asString());
400
                }
142 ilm 401
            }
402
 
156 ilm 403
            // Champ matière
404
            SQLTable tableArt = root.getTable("ARTICLE");
405
            if (!tableArt.contains("MATIERE")) {
406
                final AlterTable alter = new AlterTable(tableArt);
407
                alter.addVarCharColumn("MATIERE", 128);
408
                tableArt.getBase().getDataSource().execute(alter.asString());
409
                tableArt.getSchema().updateVersion();
410
                tableArt.fetchFields();
411
            }
412
 
142 ilm 413
        }
144 ilm 414
        // ----------------------- 1.5.1
142 ilm 415
 
144 ilm 416
        if (!root.contains("FABRICANT")) {
417
            final SQLCreateTable createTableEtat = new SQLCreateTable(root, "FABRICANT");
418
            createTableEtat.addVarCharColumn("NOM", 256);
419
            root.getBase().getDataSource().execute(createTableEtat.asString());
420
            InstallationPanel.insertUndef(createTableEtat);
421
            root.refetchTable("FABRICANT");
422
            root.getSchema().updateVersion();
423
 
424
            final AlterTable alterA = new AlterTable(root.getTable("ARTICLE"));
425
            alterA.addForeignColumn("ID_FABRICANT", root.findTable("FABRICANT"));
426
            root.getBase().getDataSource().execute(alterA.asString());
427
            root.refetchTable("ARTICLE");
428
            root.getSchema().updateVersion();
429
        }
430
 
431
        // CIM
432
        {
433
            if (!root.contains("ETAT_DEMANDE_ACHAT_ELEMENT")) {
434
                final SQLCreateTable createTableEtat = new SQLCreateTable(root, "ETAT_DEMANDE_ACHAT_ELEMENT");
435
                createTableEtat.addVarCharColumn("NOM", 256);
436
                createTableEtat.addIntegerColumn("COLOR", null, true);
437
                root.getBase().getDataSource().execute(createTableEtat.asString());
438
                InstallationPanel.insertUndef(createTableEtat);
439
                root.refetchTable("ETAT_DEMANDE_ACHAT_ELEMENT");
440
                root.getSchema().updateVersion();
441
 
442
                SQLRowValues rowVals = new SQLRowValues(root.getTable("ETAT_DEMANDE_ACHAT_ELEMENT"));
443
                rowVals.put("NOM", "En attente");
444
                rowVals.insert();
445
                rowVals.put("NOM", "Demande de prix");
446
                rowVals.insert();
447
                rowVals.put("NOM", "En commande");
448
                rowVals.insert();
449
                rowVals.put("NOM", "Réceptionnée");
450
                rowVals.insert();
451
                rowVals.put("NOM", "Réception partielle");
452
                rowVals.insert();
453
                rowVals.put("NOM", "A relancer");
454
                rowVals.insert();
455
            }
456
 
457
            if (!root.contains("DEMANDE_ACHAT_ELEMENT")) {
458
                final SQLCreateTable createTableDmd = new SQLCreateTable(root, "DEMANDE_ACHAT_ELEMENT");
459
                createTableDmd.addVarCharColumn("CODE", 256);
460
                createTableDmd.addVarCharColumn("NOM", 256);
461
                createTableDmd.addIntegerColumn("QTE", 1);
462
                createTableDmd.addDecimalColumn("QTE_UNITAIRE", 16, 6, BigDecimal.ONE, false);
463
 
464
                createTableDmd.addVarCharColumn("REPERE", 256);
465
                createTableDmd.addVarCharColumn("REFERENCE", 256);
466
                createTableDmd.addForeignColumn("FABRICANT");
467
                createTableDmd.addColumn("DATE", "date");
468
                createTableDmd.addForeignColumn("ETAT_DEMANDE_ACHAT_ELEMENT");
469
                createTableDmd.addForeignColumn("UNITE_VENTE");
470
                createTableDmd.addForeignColumn("ARTICLE");
471
                createTableDmd.addForeignColumn("FAMILLE_ARTICLE");
472
                createTableDmd.addForeignColumn("FOURNISSEUR");
473
                createTableDmd.addBooleanColumn("EN_STOCK", Boolean.FALSE, false);
474
 
475
                root.getBase().getDataSource().execute(createTableDmd.asString());
476
                InstallationPanel.insertUndef(createTableDmd);
477
                root.refetchTable("DEMANDE_ACHAT_ELEMENT");
478
                root.getSchema().updateVersion();
479
            }
480
 
481
            if (!root.contains("ETAT_DEMANDE_PRIX")) {
482
                final SQLCreateTable createTableEtat = new SQLCreateTable(root, "ETAT_DEMANDE_PRIX");
483
                createTableEtat.addVarCharColumn("NOM", 256);
484
                root.getBase().getDataSource().execute(createTableEtat.asString());
485
                InstallationPanel.insertUndef(createTableEtat);
486
                root.refetchTable("ETAT_DEMANDE_PRIX");
487
                root.getSchema().updateVersion();
488
 
489
                SQLRowValues rowVals = new SQLRowValues(root.getTable("ETAT_DEMANDE_PRIX"));
490
                rowVals.put("NOM", "En attente");
491
                rowVals.insert();
492
                rowVals.put("NOM", "Refusée");
493
                rowVals.insert();
494
                rowVals.put("NOM", "Acceptée");
495
                rowVals.insert();
496
                rowVals.put("NOM", "En cours");
497
                rowVals.insert();
498
            }
499
 
500
            if (!root.contains("DEMANDE_PRIX")) {
501
                final SQLCreateTable createTableDmd = new SQLCreateTable(root, "DEMANDE_PRIX");
502
                createTableDmd.addVarCharColumn("NUMERO", 256);
503
                createTableDmd.addVarCharColumn("OBJET", 1024);
504
                createTableDmd.addForeignColumn("FOURNISSEUR");
505
                // createTableDemande.addForeignColumn("ID_AFFAIRE",
506
                // ctxt.getRoot().findTable("AFFAIRE"));
507
                createTableDmd.addColumn("DATE", "date");
508
                // createTableDemandeM.addColumn("DATE_DISPOSITION", "date");
509
                createTableDmd.addColumn("DATE_BUTOIRE", "date");
510
                createTableDmd.addColumn("T_HT", "bigint DEFAULT 0");
511
                createTableDmd.addColumn("T_TVA", "bigint DEFAULT 0");
512
                createTableDmd.addColumn("T_TTC", "bigint DEFAULT 0");
513
                // createTableDemandeM.addForeignColumn("ID_TAXE", findTableTaxe);
514
 
515
                createTableDmd.addForeignColumn("COMMERCIAL");
516
                createTableDmd.addForeignColumn("ETAT_DEMANDE_PRIX");
517
                // createTableDemandeM.addForeignColumn("ID_ADRESSE",
518
                // ctxt.getRoot().findTable("ADRESSE"));
519
                createTableDmd.addVarCharColumn("INFOS", 1024);
520
 
521
                root.getBase().getDataSource().execute(createTableDmd.asString());
522
                InstallationPanel.insertUndef(createTableDmd);
523
                root.refetchTable("DEMANDE_PRIX");
524
                root.getSchema().updateVersion();
525
 
526
                final SQLCreateTable createTableDemandeMElt = new SQLCreateTable(root, "DEMANDE_PRIX_ELEMENT");
527
                createTableDemandeMElt.addVarCharColumn("NOM", 512);
528
                createTableDemandeMElt.addForeignColumn("DEMANDE_PRIX");
529
                createTableDemandeMElt.addForeignColumn("ID_TAXE", root.getTable("TAXE").getSQLName(), root.getTable("TAXE").getKey().getName(), "2");
530
 
531
                createTableDemandeMElt.addIntegerColumn("QTE", 0);
532
                createTableDemandeMElt.addColumn("VALEUR_METRIQUE_3", "real DEFAULT 0");
533
                createTableDemandeMElt.addColumn("PRIX_METRIQUE_VT_3", "bigint DEFAULT 0");
534
 
535
                createTableDemandeMElt.addDecimalColumn("PA_HT", 16, 6, BigDecimal.ZERO, false);
536
                createTableDemandeMElt.addDecimalColumn("PRIX_METRIQUE_HA_2", 16, 6, BigDecimal.ZERO, false);
537
 
538
                createTableDemandeMElt.addDecimalColumn("PRIX_METRIQUE_HA_1", 16, 6, BigDecimal.ZERO, false);
539
                createTableDemandeMElt.addDecimalColumn("T_PA_TTC", 16, 2, BigDecimal.ZERO, false);
540
                createTableDemandeMElt.addColumn("VALEUR_METRIQUE_1", "real DEFAULT 0");
541
                createTableDemandeMElt.addDecimalColumn("T_PA_HT", 16, 6, BigDecimal.ZERO, false);
542
                createTableDemandeMElt.addColumn("T_POIDS", "real DEFAULT 0");
543
                createTableDemandeMElt.addColumn("VALEUR_METRIQUE_2", "real DEFAULT 0");
544
                createTableDemandeMElt.addDecimalColumn("PRIX_METRIQUE_HA_3", 16, 6, BigDecimal.ZERO, false);
545
 
546
                createTableDemandeMElt.addDecimalColumn("PRIX_METRIQUE_VT_2", 16, 6, BigDecimal.ZERO, false);
547
                createTableDemandeMElt.addDecimalColumn("PRIX_METRIQUE_VT_1", 16, 6, BigDecimal.ZERO, false);
548
                createTableDemandeMElt.addDecimalColumn("T_PV_TTC", 16, 2, BigDecimal.ZERO, false);
549
                createTableDemandeMElt.addForeignColumn("ID_METRIQUE_1", root.findTable("METRIQUE"));
550
                SQLTable findTable = root.findTable("MODE_VENTE_ARTICLE");
551
                createTableDemandeMElt.addForeignColumn("ID_MODE_VENTE_ARTICLE", findTable.getSQLName(), findTable.getKey().getName(), String.valueOf(ReferenceArticleSQLElement.A_LA_PIECE));
552
                createTableDemandeMElt.addForeignColumn("ID_METRIQUE_3", root.findTable("METRIQUE"));
553
                createTableDemandeMElt.addForeignColumn("ID_STYLE", root.findTable("STYLE"));
554
                createTableDemandeMElt.addForeignColumn("ID_METRIQUE_2", root.findTable("METRIQUE"));
555
                createTableDemandeMElt.addIntegerColumn("QTE_ACHAT", 1);
556
                createTableDemandeMElt.addForeignColumn("ID_DEVISE", root.findTable("DEVISE"));
557
                createTableDemandeMElt.addForeignColumn("ID_FAMILLE_ARTICLE", root.findTable("FAMILLE_ARTICLE"));
558
 
559
                createTableDemandeMElt.addVarCharColumn("CODE", 256);
560
                createTableDemandeMElt.addColumn("SERVICE", "boolean DEFAULT false");
561
 
562
                createTableDemandeMElt.addDecimalColumn("T_PV_HT", 16, 6, BigDecimal.ZERO, false);
563
                createTableDemandeMElt.addDecimalColumn("PV_HT", 16, 6, BigDecimal.ZERO, false);
564
                createTableDemandeMElt.addColumn("POIDS", "real DEFAULT 0");
565
                createTableDemandeMElt.addDecimalColumn("PA_DEVISE", 16, 6, BigDecimal.ZERO, false);
566
                createTableDemandeMElt.addDecimalColumn("PA_DEVISE_T", 16, 6, BigDecimal.ZERO, false);
567
 
568
                createTableDemandeMElt.addForeignColumn("ID_ARTICLE", root.findTable("ARTICLE"));
569
                createTableDemandeMElt.addDecimalColumn("QTE_UNITAIRE", 16, 6, BigDecimal.ONE, false);
570
                createTableDemandeMElt.addForeignColumn("ID_UNITE_VENTE", root.findTable("UNITE_VENTE"));
571
                createTableDemandeMElt.addVarCharColumn("DESCRIPTIF", 2048);
572
                createTableDemandeMElt.addIntegerColumn("NIVEAU", 1);
156 ilm 573
                if (root.contains("DEPOT_STOCK")) {
574
                    createTableDemandeMElt.addForeignColumn("DEPOT_STOCK");
575
                }
144 ilm 576
                createTableDemandeMElt.addForeignColumn("ID_ECO_CONTRIBUTION", root.findTable("ECO_CONTRIBUTION"));
577
                createTableDemandeMElt.addDecimalColumn("ECO_CONTRIBUTION", 16, 2, BigDecimal.ZERO, false);
578
                createTableDemandeMElt.addDecimalColumn("T_ECO_CONTRIBUTION", 16, 2, BigDecimal.ZERO, false);
579
                createTableDemandeMElt.addForeignColumn("ID_DEMANDE_ACHAT_ELEMENT", root.findTable("DEMANDE_ACHAT_ELEMENT"));
580
 
581
                root.getBase().getDataSource().execute(createTableDemandeMElt.asString());
582
                InstallationPanel.insertUndef(createTableDemandeMElt);
583
                root.refetchTable("DEMANDE_PRIX_ELEMENT");
584
                root.getSchema().updateVersion();
585
 
586
                final AlterTable alterNumero = new AlterTable(root.getTable("NUMEROTATION_AUTO"));
587
                alterNumero.addVarCharColumn("DMD_PRIX_FORMAT", 128);
588
                alterNumero.addIntegerColumn("DMD_PRIX_START", 1);
589
                root.getBase().getDataSource().execute(alterNumero.asString());
590
                root.refetchTable("NUMEROTATION_AUTO");
591
                root.getSchema().updateVersion();
592
 
593
                final AlterTable alterCmd = new AlterTable(root.getTable("COMMANDE_ELEMENT"));
594
                alterCmd.addForeignColumn("ID_DEMANDE_ACHAT_ELEMENT", root.findTable("DEMANDE_ACHAT_ELEMENT"));
595
                root.getBase().getDataSource().execute(alterCmd.asString());
596
                root.refetchTable("COMMANDE_ELEMENT");
597
                root.getSchema().updateVersion();
598
 
599
                final AlterTable alterF = new AlterTable(root.getTable("FACTURE_FOURNISSEUR_ELEMENT"));
600
                alterF.addForeignColumn("ID_DEMANDE_ACHAT_ELEMENT", root.findTable("DEMANDE_ACHAT_ELEMENT"));
601
                root.getBase().getDataSource().execute(alterF.asString());
602
                root.refetchTable("FACTURE_FOURNISSEUR_ELEMENT");
603
                root.getSchema().updateVersion();
604
 
605
                final AlterTable alterB = new AlterTable(root.getTable("BON_RECEPTION_ELEMENT"));
606
                alterB.addForeignColumn("ID_DEMANDE_ACHAT_ELEMENT", root.findTable("DEMANDE_ACHAT_ELEMENT"));
607
                root.getBase().getDataSource().execute(alterB.asString());
608
                root.refetchTable("BON_RECEPTION_ELEMENT");
609
                root.getSchema().updateVersion();
610
            }
611
        }
612
 
613
        SQLTable tableDmdAChat = root.findTable("DEMANDE_ACHAT_ELEMENT");
614
        if (!tableDmdAChat.contains("ID_FAMILLE_ARTICLE")) {
615
            final AlterTable alterB = new AlterTable(tableDmdAChat);
616
            alterB.addForeignColumn("ID_FAMILLE_ARTICLE", root.findTable("FAMILLE_ARTICLE"));
617
            root.getBase().getDataSource().execute(alterB.asString());
618
            root.refetchTable("DEMANDE_ACHAT_ELEMENT");
619
            root.getSchema().updateVersion();
620
        }
621
        if (!tableDmdAChat.contains("ID_COMMANDE")) {
622
            final AlterTable alterB = new AlterTable(tableDmdAChat);
623
            alterB.addForeignColumn("ID_COMMANDE", root.findTable("COMMANDE"));
624
            root.getBase().getDataSource().execute(alterB.asString());
625
            root.refetchTable("DEMANDE_ACHAT_ELEMENT");
626
            root.getSchema().updateVersion();
627
        }
628
 
629
        if (!tableDmdAChat.contains("ID_BON_RECEPTION")) {
630
            final AlterTable alterB = new AlterTable(tableDmdAChat);
631
            alterB.addForeignColumn("ID_BON_RECEPTION", root.findTable("BON_RECEPTION"));
632
            root.getBase().getDataSource().execute(alterB.asString());
633
            root.refetchTable("DEMANDE_ACHAT_ELEMENT");
634
            root.getSchema().updateVersion();
635
        }
149 ilm 636
        if (!tableDmdAChat.contains("REPRISE")) {
637
            final AlterTable alterB = new AlterTable(tableDmdAChat);
638
            alterB.addBooleanColumn("REPRISE", Boolean.FALSE, false);
639
            root.getBase().getDataSource().execute(alterB.asString());
640
            root.refetchTable("DEMANDE_ACHAT_ELEMENT");
641
            root.getSchema().updateVersion();
642
        }
643
 
156 ilm 644
        if (!tableDmdAChat.contains("ID_COMMANDE")) {
645
            final AlterTable alterB = new AlterTable(tableDmdAChat);
646
            alterB.addForeignColumn("ID_COMMANDE", root.findTable("COMMANDE"));
647
            root.getBase().getDataSource().execute(alterB.asString());
648
            root.refetchTable("DEMANDE_ACHAT_ELEMENT");
649
            root.getSchema().updateVersion();
650
        }
651
 
652
        if (!tableDmdAChat.contains("REPRISE")) {
653
            final AlterTable alterB = new AlterTable(tableDmdAChat);
654
            alterB.addBooleanColumn("REPRISE", Boolean.FALSE, false);
655
            root.getBase().getDataSource().execute(alterB.asString());
656
            root.refetchTable("DEMANDE_ACHAT_ELEMENT");
657
            root.getSchema().updateVersion();
658
        }
659
 
149 ilm 660
        if (!tableDmdAChat.contains("IMPORT")) {
661
            final AlterTable alterB = new AlterTable(tableDmdAChat);
662
            alterB.addBooleanColumn("IMPORT", Boolean.FALSE, false);
663
            root.getBase().getDataSource().execute(alterB.asString());
664
            root.refetchTable("DEMANDE_ACHAT_ELEMENT");
665
            root.getSchema().updateVersion();
666
        }
667
 
156 ilm 668
        if (!tableDmdAChat.contains("ID_BON_RECEPTION")) {
669
            final AlterTable alterB = new AlterTable(tableDmdAChat);
670
            alterB.addForeignColumn("ID_BON_RECEPTION", root.findTable("BON_RECEPTION"));
671
            root.getBase().getDataSource().execute(alterB.asString());
672
            root.refetchTable("DEMANDE_ACHAT_ELEMENT");
673
            root.getSchema().updateVersion();
674
        }
144 ilm 675
        if (!tableDmdAChat.contains("QTE_RECUE")) {
676
            final AlterTable alterB = new AlterTable(tableDmdAChat);
677
            alterB.addIntegerColumn("QTE_RECUE", 0);
678
            root.getBase().getDataSource().execute(alterB.asString());
679
            root.refetchTable("DEMANDE_ACHAT_ELEMENT");
680
            root.getSchema().updateVersion();
681
        }
156 ilm 682
 
144 ilm 683
        if (!tableDmdAChat.contains("REPERE_SOURCE")) {
684
            final AlterTable alterB = new AlterTable(tableDmdAChat);
685
            alterB.addVarCharColumn("REPERE_SOURCE", 128);
686
            alterB.addVarCharColumn("CODE_SOURCE", 128);
687
            alterB.addVarCharColumn("NOM_SOURCE", 128);
688
            root.getBase().getDataSource().execute(alterB.asString());
689
            root.refetchTable("DEMANDE_ACHAT_ELEMENT");
690
            root.getSchema().updateVersion();
691
        }
692
        SQLTable tableEtatDmdAChat = root.findTable("ETAT_DEMANDE_ACHAT_ELEMENT");
693
        if (!tableEtatDmdAChat.contains("COLOR")) {
694
            final AlterTable alterB = new AlterTable(tableEtatDmdAChat);
695
            alterB.addIntegerColumn("COLOR", null, true);
696
            root.getBase().getDataSource().execute(alterB.asString());
697
            root.refetchTable("ETAT_DEMANDE_ACHAT_ELEMENT");
698
            root.getSchema().updateVersion();
699
        }
700
 
701
        if (!tableDmdAChat.contains("T_HT")) {
702
            final AlterTable alterB = new AlterTable(tableDmdAChat);
703
            alterB.addVarCharColumn("NUMERO_SE", 128);
704
            alterB.addVarCharColumn("DESCRIPTIF_SE", 512);
705
            alterB.addVarCharColumn("REVISION", 48);
706
            alterB.addDecimalColumn("P_HT", 16, 6, BigDecimal.ZERO, false);
707
            alterB.addDecimalColumn("T_HT", 16, 6, BigDecimal.ZERO, false);
708
            root.getBase().getDataSource().execute(alterB.asString());
709
            root.refetchTable("DEMANDE_ACHAT_ELEMENT");
710
            root.getSchema().updateVersion();
711
        }
712
 
713
        if (!root.contains("FACTURATION_COMMANDE_CLIENT")) {
714
            final SQLCreateTable createTableEtat = new SQLCreateTable(root, "FACTURATION_COMMANDE_CLIENT");
715
            createTableEtat.addVarCharColumn("NOM", 256);
716
            createTableEtat.addIntegerColumn("TYPE_FACTURE", TypeFactureCommandeClient.GLOBALE.getId());
717
            createTableEtat.addBooleanColumn("CHOICE", Boolean.FALSE, false);
718
            createTableEtat.addForeignColumn("TYPE_REGLEMENT");
719
            createTableEtat.addForeignColumn("COMMANDE_CLIENT");
720
            createTableEtat.addForeignColumn("SAISIE_VENTE_FACTURE");
721
            createTableEtat.addForeignColumn("MODELE");
722
            createTableEtat.addIntegerColumn("AJOURS", 0);
723
            createTableEtat.addDecimalColumn("MONTANT", 16, 8, BigDecimal.ZERO, false);
724
            createTableEtat.addDecimalColumn("POURCENT", 16, 8, BigDecimal.ZERO, false);
725
            createTableEtat.addBooleanColumn("COMPTANT", Boolean.FALSE, false);
726
            createTableEtat.addColumn("DATE_PREVISIONNELLE", "date");
727
            root.getBase().getDataSource().execute(createTableEtat.asString());
728
            InstallationPanel.insertUndef(createTableEtat);
729
            root.refetchTable("FACTURATION_COMMANDE_CLIENT");
730
            root.getSchema().updateVersion();
731
        }
732
        SQLTable tableFacturationCmd = root.getTable("FACTURATION_COMMANDE_CLIENT");
733
        if (!tableFacturationCmd.contains("FIN_MOIS")) {
734
            AlterTable alt = new AlterTable(tableFacturationCmd);
735
            alt.addBooleanColumn("FIN_MOIS", Boolean.FALSE, false);
736
            root.getBase().getDataSource().execute(alt.asString());
737
            root.refetchTable("FACTURATION_COMMANDE_CLIENT");
738
            root.getSchema().updateVersion();
739
        }
740
        if (!tableFacturationCmd.contains("LENJOUR")) {
741
            AlterTable alt = new AlterTable(tableFacturationCmd);
742
            alt.addIntegerColumn("LENJOUR", 0);
743
            root.getBase().getDataSource().execute(alt.asString());
744
            root.refetchTable("FACTURATION_COMMANDE_CLIENT");
745
            root.getSchema().updateVersion();
746
        }
156 ilm 747
 
144 ilm 748
        if (!root.contains("CHIFFRAGE_COMMANDE_CLIENT")) {
749
            final SQLCreateTable createTableEtat = new SQLCreateTable(root, "CHIFFRAGE_COMMANDE_CLIENT");
750
            createTableEtat.addVarCharColumn("NOM", 256);
751
            createTableEtat.addForeignColumn("COMMANDE_CLIENT");
752
            createTableEtat.addForeignColumn("UNITE_VENTE");
753
            createTableEtat.addForeignColumn("FAMILLE_ARTICLE");
754
            createTableEtat.addDecimalColumn("QTE", 16, 8, BigDecimal.ZERO, false);
755
            createTableEtat.addDecimalColumn("PA_HT", 16, 8, BigDecimal.ZERO, false);
756
            createTableEtat.addDecimalColumn("PV_HT", 16, 8, BigDecimal.ZERO, false);
757
            createTableEtat.addDecimalColumn("T_PV_HT", 16, 8, BigDecimal.ZERO, false);
758
            createTableEtat.addDecimalColumn("T_PA_HT", 16, 8, BigDecimal.ZERO, false);
759
            createTableEtat.addDecimalColumn("MARGE", 16, 8, BigDecimal.ZERO, false);
760
            root.getBase().getDataSource().execute(createTableEtat.asString());
761
            InstallationPanel.insertUndef(createTableEtat);
762
            root.refetchTable("CHIFFRAGE_COMMANDE_CLIENT");
763
            root.getSchema().updateVersion();
764
        }
765
 
766
        // VARIABLE_SALARIE
767
        SQLTable tableVarSal = root.findTable("VARIABLE_SALARIE");
768
        if (!tableVarSal.contains("HEURE_ABS_DEFAULT_VAL")) {
769
 
770
            final AlterTable alterB = new AlterTable(root.getTable("VARIABLE_SALARIE"));
771
 
772
            for (org.openconcerto.sql.model.SQLField sqlField : tableVarSal.getContentFields()) {
773
 
774
                String field = sqlField.getName();
775
                if (!field.equalsIgnoreCase("ID_USER_COMMON_CREATE") && !field.equalsIgnoreCase("ID_USER_COMMON_MODIFY") && !field.equalsIgnoreCase("MODIFICATION_DATE")
776
                        && !field.equalsIgnoreCase("CREATION_DATE") && !field.equalsIgnoreCase("HEURE_TRAV")) {
777
                    alterB.addColumn(field + "_DEFAULT_VAL", "real DEFAULT 0");
778
                }
779
            }
780
 
781
            root.getBase().getDataSource().execute(alterB.asString());
782
            root.refetchTable("VARIABLE_SALARIE");
783
            root.getSchema().updateVersion();
784
        }
785
 
786
        SQLTable tableFpaye = root.findTable("FICHE_PAYE");
787
        if (!tableFpaye.contains("TAXE_CM_SAL")) {
788
            final AlterTable alterB = new AlterTable(tableFpaye);
789
            alterB.addDecimalColumn("SAL_BASE_BRUT", 16, 2, BigDecimal.ZERO, false);
790
            alterB.addDecimalColumn("TAXE_CM_SAL", 16, 2, BigDecimal.ZERO, false);
791
            alterB.addDecimalColumn("TAXE_CM_PAT", 16, 2, BigDecimal.ZERO, false);
792
            alterB.addDecimalColumn("SAL_BRUT_COTISABLE", 16, 2, BigDecimal.ZERO, false);
793
            alterB.addDecimalColumn("SAL_BRUT_TAXABLE", 16, 2, BigDecimal.ZERO, false);
794
            root.getBase().getDataSource().execute(alterB.asString());
795
            root.refetchTable("FICHE_PAYE");
796
            root.getSchema().updateVersion();
797
 
798
            SQLTable tableElementPaye = root.getTable("FICHE_PAYE_ELEMENT");
799
 
800
            AlterTable tEltPaye = new AlterTable(tableElementPaye);
801
            tEltPaye.alterColumn("NOM", EnumSet.allOf(Properties.class), "varchar(512)", "''", false);
802
            tableElementPaye.getBase().getDataSource().execute(tEltPaye.asString());
803
            tableElementPaye.getSchema().updateVersion();
804
            tableElementPaye.fetchFields();
805
 
806
        }
807
 
808
        SQLTable tableCpaye = root.findTable("CUMULS_PAYE");
809
        if (!tableCpaye.contains("TAXE_CM_SAL_C")) {
810
            final AlterTable alterB = new AlterTable(tableCpaye);
811
            alterB.addDecimalColumn("TAXE_CM_SAL_C", 16, 2, BigDecimal.ZERO, false);
812
            alterB.addDecimalColumn("TAXE_CM_PAT_C", 16, 2, BigDecimal.ZERO, false);
813
            alterB.addDecimalColumn("SAL_BRUT_COTISABLE_C", 16, 2, BigDecimal.ZERO, false);
814
            alterB.addDecimalColumn("SAL_BRUT_TAXABLE_C", 16, 2, BigDecimal.ZERO, false);
815
            alterB.addDecimalColumn("SAL_BASE_BRUT_C", 16, 2, BigDecimal.ZERO, false);
816
            root.getBase().getDataSource().execute(alterB.asString());
817
            root.refetchTable("CUMULS_PAYE");
818
            root.getSchema().updateVersion();
819
        }
820
 
821
        SQLTable tableEtatCivil = root.findTable("ETAT_CIVIL");
822
        if (!tableEtatCivil.contains("NATIONNALITE")) {
823
            final AlterTable alterB = new AlterTable(tableEtatCivil);
824
            alterB.addVarCharColumn("NATIONNALITE", 256);
825
            alterB.addVarCharColumn("CNPS", 256);
826
            alterB.addVarCharColumn("TYPE_PIECE_IDENTITE", 256);
827
            alterB.addVarCharColumn("NUMERO_PIECE", 256);
828
            alterB.addVarCharColumn("NOM_PERE", 256);
829
            alterB.addVarCharColumn("NOM_MERE", 256);
830
            alterB.addVarCharColumn("CONJOINT_NOM", 256);
831
            alterB.addColumn("CONJOINT_DATE_NAISSANCE", "date");
832
            alterB.addVarCharColumn("CONJOINT_LIEU_NAISSANCE", 256);
833
            alterB.addVarCharColumn("CONJOINT_PROFESSION", 256);
834
            alterB.addColumn("DATE_DEBUT_PIECE", "date");
835
            alterB.addColumn("DATE_FIN_PIECE", "date");
836
            root.getBase().getDataSource().execute(alterB.asString());
837
            root.refetchTable("ETAT_CIVIL");
838
            root.getSchema().updateVersion();
839
        }
840
 
841
        // ARTICLE CLIENT
842
        if (!root.contains("ARTICLE_CODE_CLIENT")) {
843
            final SQLCreateTable createTableEtat = new SQLCreateTable(root, "ARTICLE_CODE_CLIENT");
844
            createTableEtat.addVarCharColumn("NOM", 512);
845
            createTableEtat.addVarCharColumn("CODE", 512);
846
            createTableEtat.addForeignColumn("CLIENT");
847
            createTableEtat.addForeignColumn("ARTICLE");
848
            root.getBase().getDataSource().execute(createTableEtat.asString());
849
            InstallationPanel.insertUndef(createTableEtat);
850
            root.refetchTable("ARTICLE_CODE_CLIENT");
851
            root.getSchema().updateVersion();
852
        }
853
        SQLTable tableClient = root.getTable("CLIENT");
854
        if (!tableClient.contains("CODE_FOURNISSEUR")) {
855
            final AlterTable alter = new AlterTable(tableClient);
856
            alter.addVarCharColumn("CODE_FOURNISSEUR", 256);
857
            tableCompte.getBase().getDataSource().execute(alter.asString());
858
            tableCompte.getSchema().updateVersion();
859
            tableCompte.fetchFields();
860
        }
861
        SQLTable tableArticle = root.getTable("ARTICLE");
862
        if (!tableArticle.contains("TARE")) {
863
            final AlterTable alterArticle = new AlterTable(tableArticle);
864
            alterArticle.addDecimalColumn("TARE", 16, 8, BigDecimal.ZERO, true);
865
            tableArticle.getBase().getDataSource().execute(alterArticle.asString());
866
            tableArticle.getSchema().updateVersion();
867
            tableArticle.fetchFields();
868
        }
869
        if (!tableArticle.contains("IFCO")) {
870
            final AlterTable alterArticle = new AlterTable(tableArticle);
871
            alterArticle.addIntegerColumn("IFCO", 0);
872
            tableArticle.getBase().getDataSource().execute(alterArticle.asString());
873
            tableArticle.getSchema().updateVersion();
874
            tableArticle.fetchFields();
875
        }
876
        SQLTable tableFournisseur = root.getTable("FOURNISSEUR");
877
        if (!tableFournisseur.contains("NUMERO_TVA")) {
878
            final AlterTable alter = new AlterTable(tableFournisseur);
879
            alter.addVarCharColumn("NUMERO_TVA", 128);
880
            alter.addVarCharColumn("SIRET", 128);
881
            tableFournisseur.getBase().getDataSource().execute(alter.asString());
882
            tableFournisseur.getSchema().updateVersion();
883
            tableFournisseur.fetchFields();
884
        }
885
 
886
        // POS
149 ilm 887
        final SQLTable caisseT = root.getTable("CAISSE");
888
        final String registerLogTableName = "CAISSE_JOURNAL";
889
        if (!root.contains(registerLogTableName)) {
144 ilm 890
            final SQLTable receiptT = root.getTable("TICKET_CAISSE");
891
            final SQLTable userT = root.findTable("USER_COMMON", true);
892
 
149 ilm 893
            final SQLCreateTable createLogT = new SQLCreateTable(caisseT.getDBRoot(), registerLogTableName);
144 ilm 894
            createLogT.setCreateOrder(false);
895
            AddMDFields.addFields(createLogT, userT);
896
            createLogT.addForeignColumn("ID_CAISSE", caisseT);
897
            createLogT.addDateAndTimeColumn("DATE");
898
            createLogT.addForeignColumn("ID_USER", userT);
899
            createLogT.addVarCharColumn("EVT", 128);
900
            createLogT.addVarCharColumn("CREATOR", 128);
901
            createLogT.addVarCharColumn("CREATOR_VERSION", 128);
902
            createLogT.addUniqueConstraint("logSequence", Arrays.asList("ID_CAISSE", "DATE"));
903
 
904
            final SQLCreateTable createClotureT = new SQLCreateTable(caisseT.getDBRoot(), "CAISSE_CLOTURE");
905
            createClotureT.setCreateOrder(false);
906
            AddMDFields.addFields(createClotureT, userT);
907
            createClotureT.addForeignColumn("ID_ENTREE_JOURNAL", createLogT);
908
            createClotureT.addVarCharColumn("PERIODE", 32);
909
            final String dateType = createClotureT.getSyntax().getTypeNames(java.sql.Date.class).iterator().next();
910
            createClotureT.addColumn("DEBUT", dateType, null, false);
911
            createClotureT.addColumn("FIN", dateType, null, false);
149 ilm 912
            createClotureT.addDecimalColumn("TOTAL_TTC", 16, 6, BigDecimal.ZERO, false);
144 ilm 913
            // don't use foreign keys, we actually want redundant info so that we can check
914
            // coherence
915
            // nullable to allow for days without any sales
916
            createClotureT.addVarCharColumn("PREMIER_TICKET", 64, false, "null", true);
917
            createClotureT.addVarCharColumn("PREMIER_TICKET_HASH", 128, false, "null", true);
918
            createClotureT.addVarCharColumn("DERNIER_TICKET", 64, false, "null", true);
919
            createClotureT.addVarCharColumn("DERNIER_TICKET_HASH", 128, false, "null", true);
920
            createClotureT.addUniqueConstraint("uniqueness", Arrays.asList("ID_ENTREE_JOURNAL"));
921
 
922
            final AlterTable alterRegister = new AlterTable(caisseT);
923
            alterRegister.addForeignColumn("ID_DERNIERE_ENTREE_JOURNAL", createLogT);
924
            alterRegister.addForeignColumn("ID_DERNIERE_CLOTURE", createClotureT);
925
 
926
            final AlterTable alterReceipt = new AlterTable(receiptT);
927
            alterReceipt.addVarCharColumn("FILE_HASH", 128, false, "null", true);
928
            alterReceipt.addVarCharColumn("FILE_HASH_PREVIOUS", 128, false, "null", true);
929
            alterReceipt.addUniqueConstraint("uniqueNumber", new UniqueConstraintCreatorHelper(Arrays.asList("NUMERO"), TicketCaisseSQLElement.UNARCHIVED_WHERE) {
930
                @Override
931
                public Object getObject(SQLSyntax s) {
932
                    if (s.getSystem() == SQLSystem.H2) {
933
                        return TicketCaisseSQLElement.UniqueNumber_PartialUniqueTrigger.class;
934
                    } else {
935
                        return super.getObject(s);
936
                    }
937
                }
938
            });
939
            alterReceipt.addUniqueConstraint("uniqueDate", new UniqueConstraintCreatorHelper(Arrays.asList("ID_CAISSE", "DATE"), TicketCaisseSQLElement.DATE_WHERE) {
940
                @Override
941
                public Object getObject(SQLSyntax s) {
942
                    if (s.getSystem() == SQLSystem.H2) {
943
                        return TicketCaisseSQLElement.UniqueDate_PartialUniqueTrigger.class;
944
                    } else {
945
                        return super.getObject(s);
946
                    }
947
                }
948
            });
949
 
950
            root.createTables(createLogT, createClotureT);
951
            root.getDBSystemRoot().getDataSource().execute(alterRegister.asString());
952
            root.getDBSystemRoot().getDataSource().execute(alterReceipt.asString());
953
            caisseT.getSchema().updateVersion();
954
            alterRegister.getTable().fetchFields();
955
            alterReceipt.getTable().fetchFields();
956
        }
957
 
149 ilm 958
        SQLTable tableArt = root.getTable("ARTICLE");
959
        if (!tableArt.contains("MASQUE_CAISSE")) {
960
            final AlterTable alterArt = new AlterTable(tableArt);
961
            alterArt.addBooleanColumn("MASQUE_CAISSE", Boolean.FALSE, false);
962
            tableArt.getBase().getDataSource().execute(alterArt.asString());
963
            tableArt.getSchema().updateVersion();
964
            tableArt.fetchFields();
965
        }
966
 
151 ilm 967
        if (!tableArt.contains("ADDITIONAL_TICKET_COPY")) {
968
            final AlterTable alterArt = new AlterTable(tableArt);
969
            alterArt.addBooleanColumn("ADDITIONAL_TICKET_COPY", Boolean.FALSE, false);
970
            tableArt.getBase().getDataSource().execute(alterArt.asString());
971
            tableArt.getSchema().updateVersion();
972
            tableArt.fetchFields();
973
        }
974
 
149 ilm 975
        SQLTable tableDevisAcompte = root.getTable("DEVIS");
976
 
977
        if (!tableDevisAcompte.contains("T_ACOMPTE")) {
978
            final AlterTable alterB = new AlterTable(tableDevisAcompte);
979
            alterB.addLongColumn("T_ACOMPTE", 0L, false);
980
            root.getBase().getDataSource().execute(alterB.asString());
981
            root.refetchTable("DEVIS");
982
            root.getSchema().updateVersion();
983
 
984
            SQLTable tableEncaisse = root.getTable("ENCAISSER_MONTANT");
985
            final AlterTable alterC = new AlterTable(tableEncaisse);
986
            alterC.addBooleanColumn("ACOMPTE", Boolean.FALSE, false);
987
            alterC.addForeignColumn("ID_DEVIS", tableDevisAcompte);
988
            root.getBase().getDataSource().execute(alterC.asString());
989
            root.refetchTable("ENCAISSER_MONTANT");
990
            root.getSchema().updateVersion();
991
 
992
            SQLTable tableVF = root.getTable("SAISIE_VENTE_FACTURE");
993
            final AlterTable alterF = new AlterTable(tableVF);
994
            alterF.addLongColumn("T_ACOMPTE", 0L, false);
995
            root.getBase().getDataSource().execute(alterF.asString());
996
            root.refetchTable("SAISIE_VENTE_FACTURE");
997
            root.getSchema().updateVersion();
998
        }
999
 
144 ilm 1000
        // Paye Simplifiée
1001
        if (!tableFpaye.contains("ALLEGEMENT_COTISATION")) {
1002
            final AlterTable alterB = new AlterTable(tableFpaye);
1003
            alterB.addDecimalColumn("ALLEGEMENT_COTISATION", 16, 2, BigDecimal.ZERO, false);
1004
            alterB.addDecimalColumn("CICE", 16, 2, BigDecimal.ZERO, false);
1005
            alterB.addDecimalColumn("AVANTAGE_NATURE", 16, 2, BigDecimal.ZERO, false);
1006
            alterB.addDecimalColumn("HEURE_TRAV", 16, 2, BigDecimal.ZERO, false);
1007
            root.getBase().getDataSource().execute(alterB.asString());
1008
            root.refetchTable("FICHE_PAYE");
1009
            root.getSchema().updateVersion();
1010
 
1011
            final AlterTable alterC = new AlterTable(tableCpaye);
1012
            alterC.addDecimalColumn("ALLEGEMENT_COTISATION_C", 16, 2, BigDecimal.ZERO, false);
1013
            alterC.addDecimalColumn("CICE_C", 16, 2, BigDecimal.ZERO, false);
1014
            alterC.addDecimalColumn("AVANTAGE_NATURE_C", 16, 2, BigDecimal.ZERO, false);
1015
            root.getBase().getDataSource().execute(alterC.asString());
1016
            root.refetchTable("CUMULS_PAYE");
1017
            root.getSchema().updateVersion();
1018
 
1019
        }
156 ilm 1020
 
144 ilm 1021
        if (!tableFpaye.contains("DETAILS_CONGES")) {
1022
            final AlterTable alterB = new AlterTable(tableFpaye);
1023
            alterB.addVarCharColumn("DETAILS_CONGES", 512);
1024
            root.getBase().getDataSource().execute(alterB.asString());
1025
            root.refetchTable("FICHE_PAYE");
1026
            root.getSchema().updateVersion();
1027
        }
1028
 
1029
        SQLTable tableFPayeElt = root.getTable("FICHE_PAYE_ELEMENT");
1030
        if (!tableFPayeElt.contains("ID_STYLE")) {
1031
            final AlterTable alterB = new AlterTable(tableFPayeElt);
1032
            alterB.addForeignColumn("ID_STYLE", root.getTable("STYLE"));
1033
            root.getBase().getDataSource().execute(alterB.asString());
1034
            root.refetchTable("FICHE_PAYE_ELEMENT");
1035
            root.getSchema().updateVersion();
1036
        }
1037
 
1038
        if (!tableVarSal.contains("IJSS_BRUT")) {
1039
            final AlterTable alterB = new AlterTable(tableVarSal);
1040
            List<String> f = Arrays.asList("IJSS_BRUT", "IJSS_NET", "FRAIS_PRO", "RBT_TRANSPORT");
1041
            for (String field : f) {
1042
                alterB.addColumn(field, "real DEFAULT 0");
1043
                alterB.addColumn(field + "_DEFAULT_VAL", "real DEFAULT 0");
1044
            }
1045
            root.getBase().getDataSource().execute(alterB.asString());
1046
            root.refetchTable(tableVarSal.getName());
1047
            root.getSchema().updateVersion();
1048
        }
1049
 
1050
        SQLTable tableDevis = root.getTable("DEVIS");
1051
        if (!tableDevis.contains("ID_TAXE_PORT")) {
1052
            final AlterTable alterB = new AlterTable(tableDevis);
1053
            alterB.addForeignColumn("ID_TAXE_PORT", root.getTable("TAXE"));
1054
            root.getBase().getDataSource().execute(alterB.asString());
1055
            root.refetchTable(tableDevis.getName());
1056
            root.getSchema().updateVersion();
1057
        }
149 ilm 1058
 
1059
        // 1.5.3
1060
        List<String> tableRemiseF = Arrays.asList("COMMANDE", "BON_RECEPTION", "FACTURE_FOURNISSEUR");
1061
        for (String t : tableRemiseF) {
1062
 
1063
            final SQLTable tableCommande = root.getTable(t);
1064
            AlterTable tCommande = new AlterTable(tableCommande);
1065
            boolean updateCmd = false;
1066
            if (!tableCommande.contains("ID_TAXE_PORT")) {
1067
                updateCmd = true;
1068
                tCommande.addForeignColumn("ID_TAXE_PORT", root.getTable("TAXE"));
1069
            }
1070
 
1071
            if (!tableCommande.contains("PORT_HT")) {
1072
                updateCmd = true;
1073
                tCommande.addLongColumn("PORT_HT", Long.valueOf(0), false);
1074
            }
1075
 
1076
            if (!tableCommande.contains("REMISE_HT")) {
1077
                updateCmd = true;
1078
                tCommande.addLongColumn("REMISE_HT", Long.valueOf(0), false);
1079
            }
1080
 
1081
            if (updateCmd) {
1082
                tableCommande.getBase().getDataSource().execute(tCommande.asString());
1083
                tableCommande.getSchema().updateVersion();
1084
                tableCommande.fetchFields();
1085
            }
1086
        }
1087
 
1088
        List<String> tableElement = Arrays.asList("FACTURE_FOURNISSEUR_ELEMENT", "COMMANDE_ELEMENT", "BON_RECEPTION_ELEMENT", "DEMANDE_PRIX_ELEMENT");
1089
        for (String tableName : tableElement) {
1090
            final SQLTable table = root.getTable(tableName);
1091
            if (!table.contains("ID_FAMILLE_ARTICLE")) {
1092
                AlterTable t = new AlterTable(table);
1093
                t.addForeignColumn("ID_FAMILLE_ARTICLE", root.getTable("FAMILLE_ARTICLE"));
1094
                table.getBase().getDataSource().execute(t.asString());
1095
                root.refetchTable(table.getName());
1096
                root.getSchema().updateVersion();
1097
            }
1098
        }
1099
 
1100
        final SQLTable tableBlElt = root.getTable("BON_RECEPTION_ELEMENT");
1101
        if (!tableBlElt.contains("QTE_ORIGINE")) {
1102
            AlterTable t = new AlterTable(tableBlElt);
1103
            t.addIntegerColumn("QTE_ORIGINE", null, true);
1104
            tableBlElt.getBase().getDataSource().execute(t.asString());
1105
            root.refetchTable(tableBlElt.getName());
1106
            root.getSchema().updateVersion();
1107
        }
1108
        // Caisse 1.5.3
1109
        if (!root.getTable(registerLogTableName).contains("HOST_NAME")) {
1110
            final AlterTable alterRegisterLog = new AlterTable(root.getTable(registerLogTableName));
1111
            // to check for shared register installations
1112
            alterRegisterLog.addVarCharColumn("HOST_NAME", 128, true, null, true);
1113
            alterRegisterLog.addVarCharColumn("HOST_USER", 128, true, null, true);
1114
 
1115
            // no longer needed since we need more than the last entry, just remove the duplicate
1116
            // data and select from CAISSE_JOURNAL
1117
            final AlterTable alterRegister = new AlterTable(caisseT);
1118
            alterRegister.dropColumn("ID_DERNIERE_ENTREE_JOURNAL");
1119
 
1120
            for (final String sql : ChangeTable.cat(Arrays.asList(alterRegisterLog, alterRegister))) {
1121
                root.getDBSystemRoot().getDataSource().execute(sql);
1122
            }
1123
            caisseT.getSchema().updateVersion();
1124
            alterRegisterLog.getTable().fetchFields();
1125
            alterRegister.getTable().fetchFields();
1126
        }
1127
        // Prefs compte AN
1128
        SQLTable table = root.findTable("PREFS_COMPTE");
1129
 
1130
        if (!table.getFieldsName().contains("ID_JOURNAL_AN")) {
151 ilm 1131
            AlterTable t = new AlterTable(table);
149 ilm 1132
            t.addForeignColumn("ID_JOURNAL_AN", root.getTable("JOURNAL"));
1133
            t.addBooleanColumn("CREATE_NUL_SOLDE_ECR", Boolean.TRUE, false);
1134
            table.getBase().getDataSource().execute(t.asString());
1135
            table.getSchema().updateVersion();
1136
            table.fetchFields();
1137
        }
1138
 
151 ilm 1139
        if (!table.getFieldsName().contains("AUTO_LETTRAGE")) {
1140
            AlterTable t = new AlterTable(table);
1141
            t.addBooleanColumn("AUTO_LETTRAGE", Boolean.FALSE, false);
1142
            table.getBase().getDataSource().execute(t.asString());
1143
            table.getSchema().updateVersion();
1144
            table.fetchFields();
1145
        }
156 ilm 1146
        // Etat stock
1147
        if (!root.contains("ETAT_STOCK")) {
151 ilm 1148
 
156 ilm 1149
            try {
1150
                final SQLCreateTable createTable = new SQLCreateTable(root, "ETAT_STOCK");
1151
                createTable.addColumn("DATE", "date");
1152
                createTable.addDecimalColumn("MONTANT_HA", 16, 8, BigDecimal.ZERO, true);
1153
                createTable.addDecimalColumn("MONTANT_VT", 16, 8, BigDecimal.ZERO, true);
1154
                createTable.addBooleanColumn("INVENTAIRE", Boolean.FALSE, false);
1155
                root.getBase().getDataSource().execute(createTable.asString());
1156
                InstallationPanel.insertUndef(createTable);
1157
                root.refetchTable("ETAT_STOCK");
1158
                root.getSchema().updateVersion();
1159
 
1160
                final SQLCreateTable createTableElt = new SQLCreateTable(root, "ETAT_STOCK_ELEMENT");
1161
                createTableElt.addForeignColumn("ARTICLE");
1162
                createTableElt.addForeignColumn("ETAT_STOCK");
1163
                createTableElt.addDecimalColumn("PA", 16, 8, BigDecimal.ZERO, true);
1164
                createTableElt.addDecimalColumn("PV", 16, 8, BigDecimal.ZERO, true);
1165
                createTableElt.addDecimalColumn("T_PA", 16, 8, BigDecimal.ZERO, true);
1166
                createTableElt.addDecimalColumn("T_PV", 16, 8, BigDecimal.ZERO, true);
1167
                createTableElt.addDecimalColumn("QTE", 16, 8, BigDecimal.ZERO, true);
1168
                createTableElt.addDecimalColumn("ECART", 16, 8, BigDecimal.ZERO, true);
1169
                createTableElt.addVarCharColumn("CODE", 256);
1170
                createTableElt.addVarCharColumn("NOM", 2048);
1171
 
1172
                root.getBase().getDataSource().execute(createTableElt.asString());
1173
                InstallationPanel.insertUndef(createTableElt);
1174
                root.refetchTable("ETAT_STOCK_ELEMENT");
1175
                root.getSchema().updateVersion();
1176
 
1177
            } catch (SQLException ex) {
1178
                throw new IllegalStateException("Erreur lors de la création de la table ETAT_STOCK_ELEMENT", ex);
1179
            }
1180
        } else {
1181
            final SQLTable tableEtatStock = root.getTable("ETAT_STOCK_ELEMENT");
1182
            SQLField fieldNom = tableEtatStock.getField("NOM");
1183
            int size = fieldNom.getType().getSize();
1184
            if (size == 512) {
1185
                AlterTable alter = new AlterTable(tableEtatStock);
1186
                alter.alterColumn("NOM", EnumSet.allOf(Properties.class), "varchar(" + 2048 + ")", "''", false);
1187
                tableEtatStock.getBase().getDataSource().execute(alter.asString());
1188
                tableEtatStock.getSchema().updateVersion();
1189
                tableEtatStock.fetchFields();
1190
            }
1191
 
1192
        }
1193
        SQLTable tableMvtStock = root.getTable("MOUVEMENT_STOCK");
1194
        if (!tableMvtStock.contains("ID_ETAT_STOCK")) {
1195
            final AlterTable alter = new AlterTable(tableMvtStock);
1196
            alter.addBooleanColumn("CLOTURE", Boolean.FALSE, false);
1197
            alter.addBooleanColumn("OUVERTURE", Boolean.FALSE, false);
1198
            alter.addForeignColumn("ID_ETAT_STOCK", root.getTable("ETAT_STOCK"));
1199
            tableMvtStock.getBase().getDataSource().execute(alter.asString());
1200
            tableMvtStock.getSchema().updateVersion();
1201
            tableMvtStock.fetchFields();
1202
        }
149 ilm 1203
        SQLTable tableEcr = root.getTable("ECRITURE");
1204
        if (!tableEcr.contains("CLOTURE")) {
1205
            final AlterTable alter = new AlterTable(tableEcr);
1206
            alter.addBooleanColumn("CLOTURE", Boolean.FALSE, false);
1207
            alter.addBooleanColumn("RAN", Boolean.FALSE, false);
1208
            tableEcr.getBase().getDataSource().execute(alter.asString());
1209
            tableEcr.getSchema().updateVersion();
1210
            tableEcr.fetchFields();
1211
        }
1212
 
1213
        // Jour Km Element
1214
        final SQLTable tkmElt = root.getTable("SAISIE_KM_ELEMENT");
1215
        if (!tkmElt.contains("JOUR")) {
1216
            final AlterTable alterKmElt = new AlterTable(tkmElt);
1217
            alterKmElt.addIntegerColumn("JOUR", 1);
1218
            tkmElt.getBase().getDataSource().execute(alterKmElt.asString());
1219
            tkmElt.getSchema().updateVersion();
1220
            tkmElt.fetchFields();
1221
        }
151 ilm 1222
        // Ref bancaires fournisseurs et clients
1223
        for (final SQLTable bankT : Arrays.asList(tableFournisseur, tClient)) {
1224
            if (!bankT.contains("IBAN")) {
1225
                final AlterTable alter = new AlterTable(bankT);
1226
                alter.addVarCharColumn("IBAN", 128);
1227
                alter.addVarCharColumn("BIC", 128);
1228
                bankT.getDBSystemRoot().getDataSource().execute(alter.asString());
1229
                bankT.getSchema().updateVersion();
1230
                bankT.fetchFields();
1231
            }
149 ilm 1232
        }
151 ilm 1233
        final SQLTable typeReglT = root.getTable("TYPE_REGLEMENT");
1234
        if (typeReglT.getRow(TypeReglementSQLElement.PRELEVEMENT) == null) {
1235
            final SQLRowValues directDebitVals = new SQLRowValues(typeReglT).put("NOM", "Prélèvement");
1236
            directDebitVals.put("COMPTANT", Boolean.FALSE).put("ECHEANCE", Boolean.FALSE);
1237
            directDebitVals.setID(TypeReglementSQLElement.PRELEVEMENT).insertVerbatim();
1238
        }
1239
        if (!tableClient.contains("ACCEPTE_EMAIL")) {
1240
            final AlterTable alter = new AlterTable(tableClient);
1241
            alter.addBooleanColumn("ACCEPTE_EMAIL", Boolean.FALSE, false);
1242
            alter.addBooleanColumn("ACCEPTE_COURRIER", Boolean.FALSE, false);
1243
            alter.addBooleanColumn("ACCEPTE_SMS", Boolean.FALSE, false);
1244
            alter.addBooleanColumn("ACCEPTE_TEL", Boolean.FALSE, false);
1245
            exec(alter);
1246
        }
1247
        final SQLTable contactT = root.getTable("CONTACT");
156 ilm 1248
        if (contactT != null && !contactT.contains("DATE_NAISSANCE")) {
151 ilm 1249
            final AlterTable alter = new AlterTable(contactT);
1250
            alter.addColumn("DATE_NAISSANCE", "date");
1251
            exec(alter);
1252
        }
156 ilm 1253
 
151 ilm 1254
        final SQLCreateTable createSDDMsgTable = SDDMessageSQLElement.getCreateTable(root);
1255
        if (createSDDMsgTable != null) {
1256
            final SQLCreateTable createMandate = SEPAMandateSQLElement.getCreateTable(root);
1257
            root.createTables(createSDDMsgTable, createMandate);
1258
            final SQLTable msgT = root.getTable(createSDDMsgTable.getName());
1259
            final SQLTable mandateT = root.getTable(createMandate.getName());
1260
 
1261
            final AlterTable alterFact = new AlterTable(root.getTable(SaisieVenteFactureSQLElement.TABLENAME));
1262
            alterFact.addForeignColumn(SaisieVenteFactureSQLElement.MESSAGE_FIELD_NAME, msgT);
1263
            alterFact.addVarCharColumn(SaisieVenteFactureSQLElement.END2END_FIELD_NAME, 35);
1264
 
1265
            final AlterTable alterModeRegl = new AlterTable(root.getTable("MODE_REGLEMENT"));
1266
            alterModeRegl.addForeignColumn(null, mandateT);
1267
 
1268
            for (final String sql : ChangeTable.cat(Arrays.asList(alterFact, alterModeRegl))) {
1269
                root.getDBSystemRoot().getDataSource().execute(sql);
1270
            }
1271
            root.getSchema().updateVersion();
1272
            root.refetch(CollectionUtils.createSet(alterFact.getName(), alterModeRegl.getName()));
1273
            root.setMetadata(SDDMessageSQLElement.SERIAL_MD, "0");
156 ilm 1274
        } else {
1275
            // Before r24495 InstallationPanel.fixUnboundedVarchar() would change this field to
1276
            // varchar
1277
            final SQLField xmlField = root.getTable(SDDMessageSQLElement.TABLE_NAME).getField("XML");
1278
            if (xmlField.getTypeDecl().contains("char")) {
1279
                final AlterTable alterTable = new AlterTable(xmlField.getTable());
1280
                alterTable.alterColumn(xmlField.getName(), EnumSet.of(Properties.TYPE), alterTable.getSyntax().getTypeNames(Clob.class).iterator().next(), null, null);
1281
                root.getDBSystemRoot().getDataSource().execute(alterTable.asString());
1282
                root.getSchema().updateVersion();
1283
                xmlField.getTable().fetchFields();
1284
            }
151 ilm 1285
        }
1286
 
1287
        final SQLTable vcT = root.getTable("SAISIE_VENTE_COMPTOIR");
1288
        if (!vcT.contains("ID_COMPTE_PCE_PRODUIT")) {
1289
            final AlterTable alter = new AlterTable(vcT);
1290
            alter.addForeignColumn("ID_COMPTE_PCE_PRODUIT", root.getTable("COMPTE_PCE"));
1291
            alter.addForeignColumn("ID_COMPTE_PCE_SERVICE", root.getTable("COMPTE_PCE"));
1292
            exec(alter);
1293
        }
1294
 
156 ilm 1295
        // 1.6
1296
        // gestion depots des cheques
1297
        String chequeDepotTable = "DEPOT_CHEQUE";
1298
        if (!root.contains(chequeDepotTable)) {
1299
 
1300
            // Table depot cheque
1301
            final SQLCreateTable createTableDepotCheque = new SQLCreateTable(root, chequeDepotTable);
1302
            createTableDepotCheque.addVarCharColumn("NOM", 512);
1303
            createTableDepotCheque.addLongColumn("MONTANT", 0L, false);
1304
            createTableDepotCheque.addColumn("DATE", "date");
1305
            if (root.contains("BANQUE")) {
1306
                createTableDepotCheque.addForeignColumn("ID_BANQUE", root.getTable("BANQUE"));
1307
            } else if (root.contains("BANQUE_POLE_PRODUIT")) {
1308
                createTableDepotCheque.addForeignColumn("ID_BANQUE", root.getTable("BANQUE_POLE_PRODUIT"));
1309
            }
1310
            createTableDepotCheque.addForeignColumn("ID_ECRITURE", root.getTable("ECRITURE"));
1311
            createTableDepotCheque.addForeignColumn("ID_MOUVEMENT", root.getTable("MOUVEMENT"));
1312
            root.getBase().getDataSource().execute(createTableDepotCheque.asString());
1313
            InstallationPanel.insertUndef(createTableDepotCheque);
1314
            root.refetchTable(chequeDepotTable);
1315
            root.getSchema().updateVersion();
1316
 
1317
            // Table depot cheque element
1318
            final SQLCreateTable createTableDepotElt = new SQLCreateTable(root, chequeDepotTable + "_ELEMENT");
1319
            createTableDepotElt.addVarCharColumn("NUMERO", 128);
1320
            createTableDepotElt.addVarCharColumn("BANQUE", 128);
1321
            createTableDepotElt.addVarCharColumn("PIECE", 512);
1322
            createTableDepotElt.addLongColumn("MONTANT", 0L, false);
1323
            createTableDepotElt.addColumn("DATE", "date");
1324
            createTableDepotElt.addForeignColumn("ID_CHEQUE_A_ENCAISSER", root.getTable("CHEQUE_A_ENCAISSER"));
1325
            createTableDepotElt.addForeignColumn("ID_ECRITURE", root.getTable("ECRITURE"));
1326
            createTableDepotElt.addForeignColumn("ID_CLIENT", root.getTable("CLIENT"));
1327
            createTableDepotElt.addForeignColumn("ID_DEPOT_CHEQUE", root.getTable(chequeDepotTable));
1328
            root.getBase().getDataSource().execute(createTableDepotElt.asString());
1329
            InstallationPanel.insertUndef(createTableDepotElt);
1330
            root.refetchTable(chequeDepotTable + "_ELEMENT");
1331
            root.getSchema().updateVersion();
1332
        }
1333
 
1334
        final SQLTable articleT = root.getTable("ARTICLE");
1335
        if (!articleT.contains("QTE_UNITAIRE")) {
1336
            final AlterTable alter = new AlterTable(articleT);
1337
            alter.addDecimalColumn("QTE_UNITAIRE", 16, 6, BigDecimal.ONE, false);
1338
            exec(alter);
1339
        }
1340
 
1341
        final SQLTable articleFT = root.getTable("ARTICLE_FOURNISSEUR");
1342
        if (!articleFT.contains("QTE_UNITAIRE")) {
1343
            final AlterTable alter = new AlterTable(articleFT);
1344
            alter.addDecimalColumn("QTE_UNITAIRE", 16, 6, BigDecimal.ONE, false);
1345
            exec(alter);
1346
        }
1347
 
1348
        // Cout de revient
1349
        if (!root.contains("COUT_REVIENT")) {
1350
            final SQLCreateTable createTableCR = new SQLCreateTable(root, "COUT_REVIENT");
1351
            createTableCR.addVarCharColumn("CODE", 48);
1352
            createTableCR.addVarCharColumn("NOM", 256);
1353
            createTableCR.addDecimalColumn("POURCENT", 16, 6, BigDecimal.ZERO, false);
1354
            root.getBase().getDataSource().execute(createTableCR.asString());
1355
            InstallationPanel.insertUndef(createTableCR);
1356
            root.refetchTable("COUT_REVIENT");
1357
            root.getSchema().updateVersion();
1358
 
1359
            final AlterTable alterArticle = new AlterTable(root.getTable("ARTICLE"));
1360
            alterArticle.addForeignColumn("ID_COUT_REVIENT", root.findTable("COUT_REVIENT"));
1361
            exec(alterArticle);
1362
            root.refetchTable("ARTICLE");
1363
            root.getSchema().updateVersion();
1364
        }
1365
 
1366
        if (!root.getTable("ARTICLE").contains("MARGE_WITH_COUT_REVIENT")) {
1367
            final AlterTable alterArticle = new AlterTable(root.getTable("ARTICLE"));
1368
            alterArticle.addBooleanColumn("MARGE_WITH_COUT_REVIENT", Boolean.FALSE, false);
1369
            exec(alterArticle);
1370
            root.refetchTable("ARTICLE");
1371
            root.getSchema().updateVersion();
1372
        }
1373
 
1374
        if (!root.getTable("FACTURE_FOURNISSEUR").contains("NET_A_PAYER")) {
1375
            final AlterTable alterFactF = new AlterTable(root.getTable("FACTURE_FOURNISSEUR"));
1376
            alterFactF.addLongColumn("NET_A_PAYER", 0L, false);
1377
            alterFactF.addLongColumn("AVOIR_TTC", 0L, false);
1378
            exec(alterFactF);
1379
            root.refetchTable("FACTURE_FOURNISSEUR");
1380
            root.getSchema().updateVersion();
1381
        }
1382
 
1383
        // Frais Document
1384
        if (!root.contains("FRAIS_DOCUMENT")) {
1385
            final SQLCreateTable createTableCR = new SQLCreateTable(root, "FRAIS_DOCUMENT");
1386
            createTableCR.addVarCharColumn("CODE", 48);
1387
            createTableCR.addVarCharColumn("NOM", 256);
1388
            createTableCR.addLongColumn("MONTANT_HT", 0L, false);
1389
            createTableCR.addForeignColumn("ID_TAXE", root.getTable("TAXE"));
1390
            createTableCR.addLongColumn("MONTANT_TTC", 0L, false);
1391
            root.getBase().getDataSource().execute(createTableCR.asString());
1392
            InstallationPanel.insertUndef(createTableCR);
1393
            root.refetchTable("FRAIS_DOCUMENT");
1394
 
1395
            final AlterTable alterClient = new AlterTable(root.getTable("CLIENT"));
1396
            alterClient.addForeignColumn("ID_FRAIS_DOCUMENT", root.findTable("FRAIS_DOCUMENT"));
1397
            exec(alterClient);
1398
            root.refetchTable("CLIENT");
1399
 
1400
            List<String> tables = Arrays.asList("DEVIS", "COMMANDE_CLIENT", "BON_DE_LIVRAISON", "SAISIE_VENTE_FACTURE");
1401
            for (String tableToUp : tables) {
1402
                final AlterTable alter = new AlterTable(root.getTable(tableToUp));
1403
                alter.addLongColumn("FRAIS_DOCUMENT_HT", 0L, false);
1404
                alter.addForeignColumn("ID_TAXE_FRAIS_DOCUMENT", root.getTable("TAXE"));
1405
                exec(alter);
1406
                root.refetchTable(tableToUp);
1407
            }
1408
            root.getSchema().updateVersion();
1409
        }
1410
 
1411
        final SQLTable chequeAvoirT = root.getTable("CHEQUE_AVOIR_CLIENT");
1412
 
1413
        String tBanque = root.contains("BANQUE") ? "BANQUE" : "BANQUE_POLE_PRODUIT";
1414
        if (!chequeAvoirT.contains("ID_" + tBanque)) {
1415
 
1416
            final AlterTable alterChqAvoir = new AlterTable(chequeAvoirT);
1417
            alterChqAvoir.addForeignColumn("ID_" + tBanque, root.findTable(tBanque));
1418
            exec(alterChqAvoir);
1419
        }
1420
 
1421
        // Prelevement à la source
1422
        final SQLTable fichePayeT = root.getTable("FICHE_PAYE");
1423
        if (!fichePayeT.contains("ID_PAS")) {
1424
            final SQLCreateTable createTablePas = new SQLCreateTable(root, "PAS");
1425
            createTablePas.addForeignColumn("ID_TYPE_TAUX_PAS", root.findTable("TYPE_TAUX_PAS"));
1426
            createTablePas.addVarCharColumn("CODE_PAS", 256);
1427
            createTablePas.addDecimalColumn("TAUX_PAS", 16, 2, BigDecimal.ZERO, false);
1428
            root.getBase().getDataSource().execute(createTablePas.asString());
1429
            InstallationPanel.insertUndef(createTablePas);
1430
            root.refetchTable("PAS");
1431
            root.getSchema().updateVersion();
1432
 
1433
            final AlterTable alterFichePaye = new AlterTable(fichePayeT);
1434
            alterFichePaye.addForeignColumn("ID_PAS", root.findTable("PAS"));
1435
            alterFichePaye.addDecimalColumn("TOTAL_PAS", 16, 2, BigDecimal.ZERO, false);
1436
            alterFichePaye.addDecimalColumn("NET_AVANT_PAS", 16, 2, BigDecimal.ZERO, false);
1437
            exec(alterFichePaye);
1438
 
1439
            final SQLTable tableSal = root.getTable("SALARIE");
1440
            final AlterTable alterSalarie = new AlterTable(tableSal);
1441
            alterSalarie.addForeignColumn("ID_PAS", root.findTable("PAS"));
1442
            exec(alterSalarie);
1443
 
1444
            final AlterTable alterEtatCivil = new AlterTable(tableEtatCivil);
1445
            alterEtatCivil.addVarCharColumn("NTT", 50);
1446
            exec(alterEtatCivil);
1447
 
1448
            Map<String, SQLRow> typeTauxPasMap = new HashMap<>();
1449
            SQLSelect selTypeTaux = new SQLSelect();
1450
            selTypeTaux.addSelect(root.findTable("TYPE_TAUX_PAS").getKey());
1451
            selTypeTaux.addSelect(root.findTable("TYPE_TAUX_PAS").getField("CODE"));
1452
            List<SQLRow> resultTypeTaux = SQLRowListRSH.execute(selTypeTaux);
1453
            for (SQLRow sqlRow : resultTypeTaux) {
1454
                typeTauxPasMap.put(sqlRow.getString("CODE"), sqlRow);
1455
            }
1456
 
1457
            SQLSelect selSal = new SQLSelect();
1458
            selSal.addSelect(tableSal.getKey());
1459
            List<SQLRow> rowSal = SQLRowListRSH.execute(selSal);
1460
            for (SQLRow sqlRow : rowSal) {
1461
                SQLRowValues rowValsSal = sqlRow.createEmptyUpdateRow();
1462
                final SQLRowValues putRowValues = rowValsSal.putRowValues("ID_PAS");
1463
                putRowValues.put("ID_TYPE_TAUX_PAS", typeTauxPasMap.get("13").getID());
1464
                putRowValues.put("TAUX_PAS", BigDecimal.ZERO);
1465
                putRowValues.commit();
1466
            }
1467
        }
1468
 
1469
        if (!root.contains("ARTICLE_TARIF_FOURNISSEUR")) {
1470
            final SQLCreateTable createTable = new SQLCreateTable(root, "ARTICLE_TARIF_FOURNISSEUR");
1471
 
1472
            createTable.addForeignColumn("ARTICLE");
1473
            createTable.addForeignColumn("FOURNISSEUR");
1474
            createTable.addVarCharColumn("REF_FOURNISSEUR", 500);
1475
            createTable.addVarCharColumn("TYPE_REAPPRO", 256);
1476
            createTable.addVarCharColumn("CONDITIONS", 1024);
1477
            createTable.addVarCharColumn("ACHETEUR", 256);
1478
            createTable.addVarCharColumn("CODE_PAYS_ORIGINE", 256);
1479
            createTable.addDecimalColumn("PRIX_ACHAT_DEVISE_F", 16, 6, BigDecimal.valueOf(0), false);
1480
            createTable.addDecimalColumn("PRIX_ACHAT", 16, 6, BigDecimal.valueOf(0), false);
1481
            createTable.addIntegerColumn("QTE", 1);
1482
            createTable.addDateAndTimeColumn("DATE_PRIX");
1483
            // createTable.addDecimalColumn("COEF_TRANSPORT_PORT", 16, 6, BigDecimal.valueOf(0),
1484
            // false);
1485
            // createTable.addDecimalColumn("COEF_TAXE_D", 16, 6, BigDecimal.valueOf(0), false);
1486
            // createTable.addDecimalColumn("COEF_TRANSPORT_SIEGE", 16, 6, BigDecimal.valueOf(0),
1487
            // false);
1488
            // createTable.addDecimalColumn("COEF_FRAIS_MOULE", 16, 6, BigDecimal.valueOf(0),
1489
            // false);
1490
            // createTable.addDecimalColumn("COEF_FRAIS_INDIRECTS", 16, 6, BigDecimal.valueOf(0),
1491
            // false);
1492
            createTable.addIntegerColumn("DELAI_REAPPRO", 0);
1493
            createTable.addIntegerColumn("DELAI_TRANSPORT", 0);
1494
            createTable.addIntegerColumn("PRIORITE", 0);
1495
            createTable.addDecimalColumn("COEF_PRIX_MINI", 16, 2, BigDecimal.valueOf(0), false);
1496
            root.getBase().getDataSource().execute(createTable.asString());
1497
            InstallationPanel.insertUndef(createTable);
1498
            root.refetchTable("ARTICLE_TARIF_FOURNISSEUR");
1499
            root.getSchema().updateVersion();
1500
        }
1501
 
1502
        // Gestion des catègories comptables
1503
        if (!root.contains("CATEGORIE_COMPTABLE")) {
1504
            final SQLCreateTable createTable = new SQLCreateTable(root, "CATEGORIE_COMPTABLE");
1505
            createTable.addVarCharColumn("NOM", 128);
1506
            createTable.addForeignColumn("ID_TAXE_VENTE", root.getTable("TAXE"));
1507
            createTable.addForeignColumn("ID_TAXE_ACHAT", root.getTable("TAXE"));
1508
            createTable.addForeignColumn("ID_COMPTE_PCE_VENTE", root.getTable("COMPTE_PCE"));
1509
            createTable.addForeignColumn("ID_COMPTE_PCE_ACHAT", root.getTable("COMPTE_PCE"));
1510
 
1511
            try {
1512
                root.getBase().getDataSource().execute(createTable.asString());
1513
                InstallationPanel.insertUndef(createTable);
1514
                root.refetchTable("CATEGORIE_COMPTABLE");
1515
                root.getSchema().updateVersion();
1516
            } catch (SQLException ex) {
1517
                throw new IllegalStateException("Erreur lors de la création de la table " + "CATEGORIE_COMPTABLE", ex);
1518
            }
1519
 
1520
            AlterTable tableClientCat = new AlterTable(root.getTable("CLIENT"));
1521
            tableClientCat.addForeignColumn("ID_CATEGORIE_COMPTABLE", root.getTable("CATEGORIE_COMPTABLE"));
1522
            root.getBase().getDataSource().execute(tableClientCat.asString());
1523
            root.refetchTable("CLIENT");
1524
            root.getSchema().updateVersion();
1525
 
1526
            AlterTable tableF = new AlterTable(root.getTable("FOURNISSEUR"));
1527
            tableF.addForeignColumn("ID_CATEGORIE_COMPTABLE", root.getTable("CATEGORIE_COMPTABLE"));
1528
            root.getBase().getDataSource().execute(tableF.asString());
1529
            root.refetchTable("FOURNISSEUR");
1530
            root.getSchema().updateVersion();
1531
 
1532
            final SQLCreateTable createTableArtCat = new SQLCreateTable(root, "ARTICLE_CATEGORIE_COMPTABLE");
1533
            createTableArtCat.addForeignColumn("ID_CATEGORIE_COMPTABLE", root.getTable("CATEGORIE_COMPTABLE"));
1534
            createTableArtCat.addForeignColumn("ID_ARTICLE", root.getTable("ARTICLE"));
1535
            createTableArtCat.addForeignColumn("ID_TAXE_ACHAT", root.getTable("TAXE"));
1536
            createTableArtCat.addForeignColumn("ID_TAXE_VENTE", root.getTable("TAXE"));
1537
            createTableArtCat.addForeignColumn("ID_COMPTE_PCE_VENTE", root.getTable("COMPTE_PCE"));
1538
            createTableArtCat.addForeignColumn("ID_COMPTE_PCE_ACHAT", root.getTable("COMPTE_PCE"));
1539
 
1540
            try {
1541
                root.getBase().getDataSource().execute(createTableArtCat.asString());
1542
                InstallationPanel.insertUndef(createTableArtCat);
1543
                root.refetchTable("CATEGORIE_COMPTABLE");
1544
                root.getSchema().updateVersion();
1545
            } catch (SQLException ex) {
1546
                throw new IllegalStateException("Erreur lors de la création de la table " + "ARTICLE_CATEGORIE_COMPTABLE", ex);
1547
            }
1548
 
1549
        }
1550
 
1551
        // Article fournisseur secondaire
1552
        if (!root.contains("ARTICLE_FOURNISSEUR_SECONDAIRE")) {
1553
            final SQLCreateTable createTable = new SQLCreateTable(root, "ARTICLE_FOURNISSEUR_SECONDAIRE");
1554
            createTable.addForeignColumn("ARTICLE");
1555
            createTable.addForeignColumn("FOURNISSEUR");
1556
            try {
1557
                root.getBase().getDataSource().execute(createTable.asString());
1558
                InstallationPanel.insertUndef(createTable);
1559
                root.refetchTable("ARTICLE_FOURNISSEUR_SECONDAIRE");
1560
                root.getSchema().updateVersion();
1561
            } catch (SQLException ex) {
1562
                throw new IllegalStateException("Erreur lors de la création de la table ARTICLE_FOURNISSEUR_SECONDAIRE", ex);
1563
            }
1564
        }
1565
 
1566
        if (!root.getTable("ARTICLE").contains("AUTO_PRIX_ACHAT_NOMENCLATURE")) {
1567
            AlterTable t = new AlterTable(root.getTable("ARTICLE"));
1568
            t.addBooleanColumn("AUTO_PRIX_ACHAT_NOMENCLATURE", false, false);
1569
            root.getTable("ARTICLE").getBase().getDataSource().execute(t.asString());
1570
            root.refetchTable(root.getTable("ARTICLE").getName());
1571
            root.getSchema().updateVersion();
1572
        }
1573
 
1574
        // Gestion multidepot
1575
        {
1576
            // Ajout table depot
1577
            if (!root.contains("DEPOT_STOCK")) {
1578
                final SQLCreateTable createTable = new SQLCreateTable(root, "DEPOT_STOCK");
1579
                createTable.addVarCharColumn("CODE", 25);
1580
                createTable.addVarCharColumn("NOM", 256);
1581
                createTable.addVarCharColumn("UI_LOCK", 256);
1582
                createTable.addForeignColumn("ID_USER_UI_LOCK", root.findTable("USER_COMMON"));
1583
                // sqlRowValues.put("UI_LOCK", "ro");
1584
                // sqlRowValues.put("ID_USER_UI_LOCK", 2);
1585
 
1586
                try {
1587
                    root.getBase().getDataSource().execute(createTable.asString());
1588
                    InstallationPanel.insertUndef(createTable);
1589
 
1590
                    root.refetchTable("DEPOT_STOCK");
1591
                    root.getSchema().updateVersion();
1592
 
1593
                    SQLRowValues rowValsDefStock = new SQLRowValues(root.getTable("DEPOT_STOCK"));
1594
                    rowValsDefStock.put("NOM", "Principal");
1595
                    rowValsDefStock.put("UI_LOCK", "ro");
1596
                    rowValsDefStock.commit();
1597
                } catch (SQLException ex) {
1598
                    throw new IllegalStateException("Erreur lors de la création de la table " + "DEPOT_STOCK", ex);
1599
                }
1600
            }
1601
 
1602
            List<String> tableElementDepot = Arrays.asList("FACTURE_FOURNISSEUR_ELEMENT", "DEVIS_ELEMENT", "COMMANDE_ELEMENT", "BON_RECEPTION_ELEMENT", "COMMANDE_CLIENT_ELEMENT",
1603
                    "BON_DE_LIVRAISON_ELEMENT", "SAISIE_VENTE_FACTURE_ELEMENT", "AVOIR_CLIENT_ELEMENT", "DEMANDE_PRIX_ELEMENT");
1604
            for (String tableName : tableElementDepot) {
1605
                final SQLTable tableToAddDepot = root.getTable(tableName);
1606
                if (!tableToAddDepot.contains("ID_DEPOT_STOCK")) {
1607
                    AlterTable t = new AlterTable(tableToAddDepot);
1608
                    t.addForeignColumn("ID_DEPOT_STOCK", root.getTable("DEPOT_STOCK"));
1609
                    tableToAddDepot.getBase().getDataSource().execute(t.asString());
1610
                    root.refetchTable(tableToAddDepot.getName());
1611
                    root.getSchema().updateVersion();
1612
                }
1613
            }
1614
 
1615
            // Depot defaut
1616
            if (!root.getTable("ARTICLE").contains("ID_DEPOT_STOCK")) {
1617
                AlterTable t = new AlterTable(root.getTable("ARTICLE"));
1618
                t.addForeignColumn("ID_DEPOT_STOCK", root.getTable("DEPOT_STOCK"));
1619
                root.getTable("ARTICLE").getBase().getDataSource().execute(t.asString());
1620
                root.refetchTable(root.getTable("ARTICLE").getName());
1621
                root.getSchema().updateVersion();
1622
            }
1623
 
1624
            // Liason depot stock
1625
            if (!root.getTable("STOCK").contains("ID_DEPOT_STOCK")) {
1626
                AlterTable t = new AlterTable(root.getTable("STOCK"));
1627
                t.addForeignColumn("ID_DEPOT_STOCK", root.getTable("DEPOT_STOCK"));
1628
                t.addForeignColumn("ID_ARTICLE", root.getTable("ARTICLE"));
1629
                t.addColumn("QTE_MIN", "real DEFAULT 0");
1630
                root.getTable("STOCK").getBase().getDataSource().execute(t.asString());
1631
                root.refetchTable(root.getTable("STOCK").getName());
1632
                root.getSchema().updateVersion();
1633
            }
1634
 
1635
            // Ajout depot sur mvt
1636
            if (!root.getTable("MOUVEMENT_STOCK").contains("ID_STOCK")) {
1637
                AlterTable t = new AlterTable(root.getTable("MOUVEMENT_STOCK"));
1638
                t.addForeignColumn("ID_STOCK", root.getTable("STOCK"));
1639
                t.addIntegerColumn("SOURCE_ELEMENTID", 1);
1640
                root.getTable("MOUVEMENT_STOCK").getBase().getDataSource().execute(t.asString());
1641
                root.refetchTable(root.getTable("MOUVEMENT_STOCK").getName());
1642
                root.getSchema().updateVersion();
1643
 
1644
                // REQUETE update STOCK.ID_ARTICLE et QTE_MIN
1645
                SQLTable tableStock = root.getTable("STOCK");
1646
                UpdateBuilder buildStockArt = new UpdateBuilder(tableStock);
1647
                buildStockArt.addBackwardVirtualJoin(tableArticle, "ID_STOCK");
1648
                buildStockArt.setFromVirtualJoinField("ID_ARTICLE", tableArticle.getAlias(), "ID");
1649
                buildStockArt.setFromVirtualJoinField("QTE_MIN", tableArticle.getAlias(), "QTE_MIN");
1650
                buildStockArt.setWhere(new Where(tableStock.getKey(), "!=", 1));
1651
 
1652
                // REQUETE UPDATE MVT ID_STOCK
1653
                UpdateBuilder buildMvtStock = new UpdateBuilder(tableMvtStock);
1654
                buildMvtStock.addForwardVirtualJoin(tableArticle, "ID_ARTICLE");
1655
                buildMvtStock.setFromVirtualJoinField("ID_STOCK", tableArticle.getAlias(), "ID_STOCK");
1656
 
1657
                // REQUETE UPDATE STOCK ID_DEPOT_STOCK
1658
                UpdateBuilder buildStock = new UpdateBuilder(tableStock);
1659
                buildStock.setObject("ID_DEPOT_STOCK", DepotStockSQLElement.DEFAULT_ID);
1660
 
1661
                UpdateBuilder buildArticleDepot = new UpdateBuilder(tableArticle);
1662
                buildArticleDepot.setObject("ID_DEPOT_STOCK", DepotStockSQLElement.DEFAULT_ID);
1663
 
1664
                // REQUETE UPDATE ARTICLE_DEPOT_STOCK
1665
                // String req = "INSERT INTO " + root.getTable("ARTICLE_STOCK").getSQLName().quote()
1666
                // + " (" + root.getTable("ARTICLE_STOCK").getField("ID_ARTICLE").getQuotedName() +
1667
                // ",";
1668
                // req += root.getTable("ARTICLE_STOCK").getField("ID_STOCK").getQuotedName() + ", "
1669
                // + root.getTable("ARTICLE_STOCK").getField("QTE_MIN").getQuotedName() + ") SELECT
1670
                // "
1671
                // + root.getTable("ARTICLE").getKey().getQuotedName() + "," +
1672
                // root.getTable("ARTICLE").getField("ID_STOCK").getQuotedName() + ", "
1673
                // + root.getTable("ARTICLE").getField("QTE_MIN").getQuotedName();
1674
                // req += " FROM " + root.getTable("ARTICLE").getSQLName().quote();
1675
                // req += " WHERE " + root.getTable("ARTICLE").getKey().getQuotedName() + " > 1 AND
1676
                // " + root.getTable("ARTICLE").getField("ID_STOCK").getQuotedName() + " >1 ";
1677
                // req += " AND " + root.getTable("ARTICLE").getArchiveField().getQuotedName() + " =
1678
                // 0";
1679
 
1680
                tableStock.getDBSystemRoot().getDataSource().execute(buildArticleDepot.asString());
1681
                tableStock.getDBSystemRoot().getDataSource().execute(buildMvtStock.asString());
1682
                tableStock.getDBSystemRoot().getDataSource().execute(buildStock.asString());
1683
                tableStock.getDBSystemRoot().getDataSource().execute(buildStockArt.asString());
1684
            }
1685
 
1686
        }
1687
 
1688
        // Gestion Livraison depuis devis
1689
        SQLTable tableTrDevis = root.getTable("TR_DEVIS");
1690
        if (!tableTrDevis.contains("ID_BON_DE_LIVRAISON")) {
1691
            AlterTable t = new AlterTable(tableTrDevis);
1692
            t.addForeignColumn("ID_BON_DE_LIVRAISON", root.getTable("BON_DE_LIVRAISON"));
1693
            tableTrDevis.getBase().getDataSource().execute(t.asString());
1694
            root.refetchTable(tableTrDevis.getName());
1695
            root.getSchema().updateVersion();
1696
        }
1697
        SQLTable tableBLElt = root.getTable("BON_DE_LIVRAISON_ELEMENT");
1698
        if (!tableBLElt.contains("ID_DEVIS_ELEMENT")) {
1699
            AlterTable t = new AlterTable(tableBLElt);
1700
            t.addForeignColumn("ID_DEVIS_ELEMENT", root.getTable("DEVIS_ELEMENT"));
1701
            tableBLElt.getBase().getDataSource().execute(t.asString());
1702
            root.refetchTable(tableBLElt.getName());
1703
            root.getSchema().updateVersion();
1704
        }
1705
        SQLTable tableDevisElt = root.getTable("DEVIS_ELEMENT");
1706
        if (!tableDevisElt.contains("QTE_LIVREE")) {
1707
            AlterTable t = new AlterTable(tableDevisElt);
1708
            t.addBooleanColumn("LIVRE_FORCED", Boolean.FALSE, false);
1709
            t.addBooleanColumn("LIVRE", Boolean.FALSE, false);
1710
            t.addDecimalColumn("QTE_LIVREE", 16, 6, BigDecimal.ZERO, true);
1711
            tableDevisElt.getBase().getDataSource().execute(t.asString());
1712
            root.refetchTable(tableDevisElt.getName());
1713
            root.getSchema().updateVersion();
1714
 
1715
            // Move show devise pref in global
1716
            SQLSelect sel = new SQLSelect();
1717
            sel.addSelect(root.getTable("DEVISE_HISTORIQUE").getKey(), "COUNT");
1718
            Number n = (Number) root.getDBSystemRoot().getDataSource().executeScalar(sel.asString());
1719
            if (n.intValue() > 0) {
1720
                SQLPreferences prefs = new SQLPreferences(root);
1721
                prefs.putBoolean(AbstractVenteArticleItemTable.ARTICLE_SHOW_DEVISE, true);
1722
                try {
1723
                    prefs.sync();
1724
                } catch (BackingStoreException e) {
1725
                    // TODO Auto-generated catch block
1726
                    e.printStackTrace();
1727
                }
1728
            }
1729
        }
1730
 
1731
        SQLTable tablePosteAn = root.getTable("POSTE_ANALYTIQUE");
1732
        if (!tablePosteAn.contains("OBSOLETE")) {
1733
            AlterTable t = new AlterTable(tablePosteAn);
1734
            t.addBooleanColumn("OBSOLETE", Boolean.FALSE, false);
1735
            tablePosteAn.getBase().getDataSource().execute(t.asString());
1736
            root.refetchTable(tablePosteAn.getName());
1737
            root.getSchema().updateVersion();
1738
        }
1739
 
1740
        SQLTable tableCheque = root.getTable("CHEQUE_A_ENCAISSER");
1741
        if (!tableCheque.contains("ID_COMPTE_PCE_TIERS")) {
1742
            AlterTable t = new AlterTable(tableCheque);
1743
            t.addVarCharColumn("TIERS", 256);
1744
            t.addForeignColumn("ID_COMPTE_PCE_TIERS", root.getTable("COMPTE_PCE"));
1745
            tableCheque.getBase().getDataSource().execute(t.asString());
1746
            root.refetchTable(tableCheque.getName());
1747
 
1748
            SQLTable tableEnc = root.getTable("ENCAISSER_MONTANT");
1749
            AlterTable tEnc = new AlterTable(tableEnc);
1750
            tEnc.addVarCharColumn("TIERS", 256);
1751
            tEnc.addForeignColumn("ID_COMPTE_PCE_TIERS", root.getTable("COMPTE_PCE"));
1752
            tableEnc.getBase().getDataSource().execute(tEnc.asString());
1753
            root.refetchTable(tableEnc.getName());
1754
            root.getSchema().updateVersion();
1755
 
1756
            SQLTable tableEch = root.getTable("ECHEANCE_CLIENT");
1757
            AlterTable tEch = new AlterTable(tableEch);
1758
            tEch.addVarCharColumn("TIERS", 256);
1759
            tEch.addForeignColumn("ID_COMPTE_PCE_TIERS", root.getTable("COMPTE_PCE"));
1760
            tableEch.getBase().getDataSource().execute(tEch.asString());
1761
            root.refetchTable(tableEch.getName());
1762
            root.getSchema().updateVersion();
1763
 
1764
            SQLTable tableChq = root.getTable("DEPOT_CHEQUE_ELEMENT");
1765
            AlterTable tChq = new AlterTable(tableChq);
1766
            tChq.addVarCharColumn("TIERS", 256);
1767
            tableChq.getBase().getDataSource().execute(tChq.asString());
1768
            root.refetchTable(tableChq.getName());
1769
            root.getSchema().updateVersion();
1770
        }
1771
 
1772
        if (!tableEcr.contains("DATE_ECHEANCE")) {
1773
            AlterTable t = new AlterTable(tableEcr);
1774
            t.addColumn("DATE_ECHEANCE", "date");
1775
            tableEcr.getBase().getDataSource().execute(t.asString());
1776
            root.refetchTable(tableEcr.getName());
1777
            root.getSchema().updateVersion();
1778
        }
1779
 
1780
        if (!tableVarSal.contains("HEURE_ABS_CUMUL_VAL")) {
1781
 
1782
            final AlterTable alterB = new AlterTable(root.getTable("VARIABLE_SALARIE"));
1783
 
1784
            for (org.openconcerto.sql.model.SQLField sqlField : tableVarSal.getContentFields()) {
1785
 
1786
                String field = sqlField.getName();
1787
                if (!field.equalsIgnoreCase("ID_USER_COMMON_CREATE") && !field.equalsIgnoreCase("ID_USER_COMMON_MODIFY") && !field.equalsIgnoreCase("MODIFICATION_DATE")
1788
                        && !field.equalsIgnoreCase("CREATION_DATE") && !field.endsWith("_DEFAULT_VAL")) {
1789
                    alterB.addColumn(field + "_CUMUL_VAL", "real DEFAULT 0");
1790
                }
1791
            }
1792
 
1793
            root.getBase().getDataSource().execute(alterB.asString());
1794
            root.refetchTable("VARIABLE_SALARIE");
1795
            root.getSchema().updateVersion();
1796
        }
174 ilm 1797
        SQLTable tableBL = root.getTable("BON_DE_LIVRAISON");
1798
        if (!tableBL.contains("ID_COMMERCIAL")) {
1799
            final AlterTable alterB = new AlterTable(tableBL);
1800
            alterB.addForeignColumn("ID_COMMERCIAL", root.getTable("COMMERCIAL"));
1801
            root.getBase().getDataSource().execute(alterB.asString());
1802
            root.refetchTable("BON_DE_LIVRAISON");
1803
            root.getSchema().updateVersion();
1804
        }
156 ilm 1805
 
1806
        // fix stock
1807
        {
1808
            SQLTable tableStock = root.getTable("STOCK");
1809
 
1810
            // Doublon depot stock
1811
            SQLSelect sel = new SQLSelect();
1812
            sel.addSelectFunctionStar("COUNT");
1813
            sel.addSelect(tableStock.getField("ID_DEPOT_STOCK"));
1814
            sel.addSelect(tableStock.getField("ID_ARTICLE"));
1815
            sel.addGroupBy(tableStock.getField("ID_DEPOT_STOCK"));
1816
            sel.addGroupBy(tableStock.getField("ID_ARTICLE"));
1817
            sel.setHaving(Where.createRaw("COUNT(*)>1", Collections.emptyList()));
1818
            System.err.println(sel.asString());
1819
 
1820
            List<Object[]> resultStockDoublon = (List<Object[]>) tableStock.getDBSystemRoot().getDataSource().execute(sel.asString(), new ArrayListHandler());
1821
            for (Object[] objects : resultStockDoublon) {
1822
 
1823
                if (((Number) objects[2]).intValue() == 1) {
1824
 
1825
                    UpdateBuilder buildStockArt = new UpdateBuilder(tableStock);
1826
                    buildStockArt.addBackwardVirtualJoin(tableArticle, "ID_STOCK");
1827
                    buildStockArt.setFromVirtualJoinField("ID_ARTICLE", tableArticle.getAlias(), "ID");
1828
                    buildStockArt.setWhere(new Where(tableStock.getKey(), "!=", 1));
1829
                    tableStock.getDBSystemRoot().getDataSource().execute(buildStockArt.asString());
1830
                } else {
1831
                    SQLSelect selD = new SQLSelect();
1832
                    selD.addSelectStar(tableStock);
1833
                    Where w = new Where(tableStock.getField("ID_ARTICLE"), "=", objects[2]);
1834
                    w = w.and(new Where(tableStock.getField("ID_DEPOT_STOCK"), "=", objects[1]));
1835
                    selD.setWhere(w);
1836
                    List<SQLRow> badStock = SQLRowListRSH.execute(selD);
1837
                    if (badStock.size() > 1) {
1838
                        SQLRowValues stock = badStock.get(0).createUpdateRow();
1839
                        List<String> fieldsToMerge = Arrays.asList("QTE_REEL", "QTE_TH", "QTE_RECEPT_ATTENTE", "QTE_LIV_ATTENTE");
1840
                        List<Integer> listBadIds = new ArrayList<>();
1841
                        for (int i = 1; i < badStock.size(); i++) {
1842
                            SQLRow rowBad = badStock.get(i);
1843
                            listBadIds.add(rowBad.getID());
1844
                            for (String field : fieldsToMerge) {
1845
                                stock.put(field, stock.getFloat(field) + rowBad.getFloat(field));
1846
                            }
1847
                            rowBad.createEmptyUpdateRow().put("ARCHIVE", 1).commit();
1848
                        }
1849
                        List<SQLTable> tablesToMErge = Arrays.asList(root.getTable("MOUVEMENT_STOCK"), root.getTable("ARTICLE"));
1850
                        for (SQLTable mergeTable : tablesToMErge) {
1851
                            UpdateBuilder up = new UpdateBuilder(mergeTable);
1852
                            up.setObject("ID_STOCK", stock.getID());
1853
                            up.setWhere(new Where(mergeTable.getField("ID_STOCK"), listBadIds));
1854
                            mergeTable.getDBSystemRoot().getDataSource().execute(up.asString());
1855
                        }
1856
                        stock.commit();
1857
 
1858
                    }
1859
                }
1860
            }
1861
 
1862
            SQLSelect selArt = new SQLSelect();
1863
            selArt.addSelectStar(tableArt);
1864
            Where w = new Where(tableArt.getField("ID_STOCK"), "=", 1);
1865
            selArt.setWhere(w);
1866
            List<SQLRow> badStock = SQLRowListRSH.execute(selArt);
1867
            for (SQLRow sqlRow : badStock) {
1868
                initStock(sqlRow);
1869
            }
1870
        }
1871
 
174 ilm 1872
        // Tarification client par quantite
1873
        if (root.getTable("TARIF_ARTICLE_CLIENT") == null) {
1874
            final SQLCreateTable createTableQtyTarif = new SQLCreateTable(root, "TARIF_ARTICLE_CLIENT");
1875
            createTableQtyTarif.addForeignColumn("ID_ARTICLE", root.getTable("ARTICLE"));
1876
            createTableQtyTarif.addForeignColumn("ID_CLIENT", root.getTable("CLIENT"));
1877
            createTableQtyTarif.addDecimalColumn("QUANTITE", 16, 3, BigDecimal.ONE, false);
1878
            createTableQtyTarif.addDecimalColumn("POURCENT_REMISE", 16, 3, null, true);
1879
            // createTableQtyTarif.addDecimalColumn("PRIX_METRIQUE_VT_1", 16, 6, null, true);
1880
            try {
1881
                root.getBase().getDataSource().execute(createTableQtyTarif.asString());
1882
                InstallationPanel.insertUndef(createTableQtyTarif);
1883
                root.refetchTable("TARIF_ARTICLE_CLIENT");
1884
                root.getSchema().updateVersion();
1885
            } catch (SQLException ex) {
1886
                throw new IllegalStateException("Erreur lors de la création de la table " + "TARIF_QUANTITE", ex);
1887
            }
1888
        }
156 ilm 1889
 
174 ilm 1890
        SQLTable tableTrCmd = root.getTable("TR_COMMANDE");
1891
        if (!tableTrCmd.contains("ID_FACTURE_FOURNISSEUR")) {
1892
            AlterTable t = new AlterTable(tableTrCmd);
1893
            t.addForeignColumn("ID_FACTURE_FOURNISSEUR", root.getTable("FACTURE_FOURNISSEUR"));
1894
            tableTrCmd.getBase().getDataSource().execute(t.asString());
1895
            root.refetchTable(tableTrCmd.getName());
1896
            root.getSchema().updateVersion();
1897
        }
1898
 
1899
        SQLTable tableContrat = root.getTable("CONTRAT_SALARIE");
1900
        if (!tableContrat.contains("COMPLEMENT_PCS")) {
1901
            AlterTable t = new AlterTable(tableContrat);
1902
            t.addVarCharColumn("COMPLEMENT_PCS", 54);
1903
            tableContrat.getBase().getDataSource().execute(t.asString());
1904
            root.refetchTable(tableContrat.getName());
1905
            root.getSchema().updateVersion();
1906
        }
1907
 
1908
        List<String> tablesCatComptable = Arrays.asList("DEVIS", "COMMANDE_CLIENT", "BON_DE_LIVRAISON", "SAISIE_VENTE_FACTURE", "AVOIR_CLIENT");
1909
        for (String tableToUp : tablesCatComptable) {
1910
            final SQLTable tableCatComptToAdd = root.getTable(tableToUp);
1911
            if (!tableCatComptToAdd.contains("ID_CATEGORIE_COMPTABLE")) {
1912
                final AlterTable alter = new AlterTable(tableCatComptToAdd);
1913
                alter.addForeignColumn("ID_CATEGORIE_COMPTABLE", root.getTable("CATEGORIE_COMPTABLE"));
1914
                exec(alter);
1915
                root.refetchTable(tableToUp);
1916
                root.getSchema().updateVersion();
1917
            }
1918
        }
1919
 
1920
 
1921
        // Modèles pour les emails
1922
        if (!root.contains(EmailTemplateSQLElement.TABLE_NAME)) {
1923
            final SQLCreateTable createTable = new SQLCreateTable(root, EmailTemplateSQLElement.TABLE_NAME);
1924
            createTable.addVarCharColumn("NOM", 80);
1925
            createTable.addVarCharColumn("TITRE", 80);
1926
            createTable.addVarCharColumn("TEXTE", 4096);
1927
            createTable.addVarCharColumn("FORMAT_DATE", 20);
1928
            createTable.addBooleanColumn("PAR_DEFAUT", Boolean.FALSE, false);
1929
            try {
1930
                root.getBase().getDataSource().execute(createTable.asString());
1931
                InstallationPanel.insertUndef(createTable);
1932
                root.refetchTable(EmailTemplateSQLElement.TABLE_NAME);
1933
                root.getSchema().updateVersion();
1934
            } catch (SQLException ex) {
1935
                throw new IllegalStateException("Erreur lors de la création de la table " + EmailTemplateSQLElement.TABLE_NAME, ex);
1936
            }
1937
        }
1938
        // Force undefined policy to inDb
1939
        root.setMetadata(SQLTable.UNDEFINED_ID_POLICY, "inDB");
1940
        final Map<String, Number> mapTableNameUndefined = SQLTable.getUndefIDs(root.getSchema());
1941
        final Set<String> tables = root.getSchema().getTableNames();
1942
        for (String tName : tables) {
1943
            if (!mapTableNameUndefined.containsKey(tName)) {
1944
                System.err.println("Updater_1_5.update() adding undefined in db for " + tName);
1945
                SQLTable.setUndefID(root.getSchema(), tName, null);
1946
            }
1947
        }
1948
 
1949
        // Création de la table Modéle
1950
        if (!root.contains("CONTACT_SALARIE")) {
1951
 
1952
            SQLCreateTable createModele = new SQLCreateTable(root, "CONTACT_SALARIE");
1953
            createModele.addVarCharColumn("NOM", 256);
1954
            createModele.addVarCharColumn("PRENOM", 256);
1955
            createModele.addVarCharColumn("TEL_DIRECT", 256);
1956
            createModele.addVarCharColumn("TEL_MOBILE", 256);
1957
            createModele.addVarCharColumn("EMAIL", 256);
1958
            createModele.addVarCharColumn("FAX", 256);
1959
            createModele.addVarCharColumn("FONCTION", 256);
1960
            createModele.addVarCharColumn("TEL_PERSONEL", 256);
1961
            createModele.addVarCharColumn("TEL_STANDARD", 256);
1962
            createModele.addForeignColumn("ID_TITRE_PERSONNEL", root.findTable("TITRE_PERSONNEL"));
1963
            createModele.addForeignColumn("ID_SALARIE", root.findTable("SALARIE"));
1964
 
1965
            try {
1966
                root.getBase().getDataSource().execute(createModele.asString());
1967
                root.refetchTable("CONTACT_SALARIE");
1968
                SQLRowValues rowVals = new SQLRowValues(root.getTable("CONTACT_SALARIE"));
1969
                SQLRow rowInserted = rowVals.commit();
1970
                SQLTable.setUndefID(root.getSchema(), "CONTACT_SALARIE", rowInserted.getID());
1971
                tableDevis.getSchema().updateVersion();
1972
            } catch (SQLException ex) {
1973
                throw new IllegalStateException("Erreur lors de la création de la table MODELE", ex);
1974
            }
1975
        }
1976
 
1977
        final SQLTable tableRgltPaye = root.getTable("REGLEMENT_PAYE");
1978
        if (!tableRgltPaye.contains("IBAN")) {
1979
            final AlterTable alter = new AlterTable(tableRgltPaye);
1980
            alter.addVarCharColumn("IBAN", 128);
1981
            exec(alter);
1982
            root.refetchTable(tableRgltPaye.getName());
1983
            root.getSchema().updateVersion();
1984
        }
1985
        if (!tableRgltPaye.contains("BIC")) {
1986
            final AlterTable alter = new AlterTable(tableRgltPaye);
1987
            alter.addVarCharColumn("BIC", 40);
1988
            exec(alter);
1989
            root.refetchTable(tableRgltPaye.getName());
1990
            root.getSchema().updateVersion();
1991
        }
1992
        final SQLTable tableSalarie = root.getTable("SALARIE");
1993
        if (!tableSalarie.contains("ID_USER_COMMON")) {
1994
            final AlterTable alter = new AlterTable(tableSalarie);
1995
            alter.addForeignColumn("ID_USER_COMMON", root.findTable("USER_COMMON"));
1996
            exec(alter);
1997
            root.refetchTable(tableSalarie.getName());
1998
            root.getSchema().updateVersion();
1999
        }
2000
 
2001
        final SQLTable tableInfosSalarie = root.getTable("INFOS_SALARIE_PAYE");
2002
        if (!tableInfosSalarie.contains("DUREE_FORFAIT")) {
2003
            final AlterTable alter = new AlterTable(tableInfosSalarie);
2004
            alter.addColumn("DUREE_FORFAIT", "real");
2005
            exec(alter);
2006
            root.refetchTable(tableSalarie.getName());
2007
            root.getSchema().updateVersion();
2008
        }
2009
 
142 ilm 2010
    }
2011
 
174 ilm 2012
    public static void initStock(SQLRow rowArticle, int idDepot) {
156 ilm 2013
 
2014
        SQLSelect selStock = new SQLSelect();
174 ilm 2015
        selStock.addSelectStar(rowArticle.getTable().getTable("STOCK"));
2016
        selStock.setWhere(new Where(rowArticle.getTable().getTable("STOCK").getField("ID_ARTICLE"), "=", rowArticle.getID()));
156 ilm 2017
        List<SQLRow> rowsStock = SQLRowListRSH.execute(selStock);
2018
        Map<Integer, SQLRow> initedDepot = new HashMap<>();
2019
        for (SQLRow sqlRow : rowsStock) {
2020
            initedDepot.put(sqlRow.getForeignID("ID_DEPOT_STOCK"), sqlRow);
2021
        }
2022
 
2023
        List<StockItem> stockItems = new ArrayList<StockItem>();
174 ilm 2024
        try {
2025
            if (!initedDepot.keySet().contains(idDepot)) {
2026
                SQLRowValues rowVals = new SQLRowValues(rowArticle.getTable().getTable("STOCK"));
2027
                rowVals.put("ID_ARTICLE", rowArticle.getID());
2028
                rowVals.put("ID_DEPOT_STOCK", idDepot);
156 ilm 2029
 
174 ilm 2030
                SQLRow rowStock = rowVals.commit();
2031
                if ((rowArticle.getObject("ID_DEPOT_STOCK") == null || rowArticle.isForeignEmpty("ID_DEPOT_STOCK")) && idDepot == DepotStockSQLElement.DEFAULT_ID) {
2032
                    rowArticle.createEmptyUpdateRow().put("ID_STOCK", rowStock.getID()).put("ID_DEPOT_STOCK", DepotStockSQLElement.DEFAULT_ID).commit();
2033
                } else if (idDepot == rowArticle.getForeignID("ID_DEPOT_STOCK")) {
2034
                    rowArticle.createEmptyUpdateRow().put("ID_STOCK", rowStock.getID()).commit();
2035
                }
2036
                stockItems.add(new StockItem(rowArticle, rowStock));
156 ilm 2037
 
174 ilm 2038
            } else {
2039
                SQLRow rowExisting = initedDepot.get(idDepot);
2040
                if ((rowArticle.getObject("ID_DEPOT_STOCK") == null || rowArticle.isForeignEmpty("ID_DEPOT_STOCK")) && idDepot == DepotStockSQLElement.DEFAULT_ID) {
2041
                    rowArticle.createEmptyUpdateRow().put("ID_STOCK", rowExisting.getID()).put("ID_DEPOT_STOCK", DepotStockSQLElement.DEFAULT_ID).commit();
2042
                } else if (idDepot == rowArticle.getForeignID("ID_DEPOT_STOCK")) {
2043
                    rowArticle.createEmptyUpdateRow().put("ID_STOCK", rowExisting.getID()).commit();
156 ilm 2044
                }
174 ilm 2045
                stockItems.add(new StockItem(rowArticle, rowExisting));
2046
            }
2047
        } catch (SQLException e) {
2048
            ExceptionHandler.handle("Erreur lors de l'initialisation du stock de l'article", e);
2049
        }
2050
 
2051
        if (rowArticle.getReferentRows(rowArticle.getTable().getTable("ARTICLE_ELEMENT").getField("ID_ARTICLE_PARENT")).size() > 0) {
2052
            ComposedItemStockUpdater up = new ComposedItemStockUpdater(rowArticle.getTable().getDBRoot(), stockItems);
2053
            try {
2054
                up.updateNomenclature(stockItems);
156 ilm 2055
            } catch (SQLException e) {
174 ilm 2056
                ExceptionHandler.handle("Erreur lors de l'actualisation du stock!", e);
156 ilm 2057
            }
2058
        }
2059
    }
2060
 
174 ilm 2061
    public static void initStock(SQLRow row) {
2062
        int foreignID = DepotStockSQLElement.DEFAULT_ID;
2063
        if (row.getObject("ID_DEPOT_STOCK") != null && !row.isForeignEmpty("ID_DEPOT_STOCK")) {
2064
            foreignID = row.getForeignID("ID_DEPOT_STOCK");
2065
        }
2066
        initStock(row, foreignID);
2067
    }
2068
 
151 ilm 2069
    public static void exec(final AlterTable alter) throws SQLException {
2070
        alter.getTable().getDBSystemRoot().getDataSource().execute(alter.asString());
2071
        alter.getTable().getSchema().updateVersion();
2072
        alter.getTable().fetchFields();
2073
    }
142 ilm 2074
}