OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

Rev 174 | 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;
177 ilm 17
import org.openconcerto.erp.core.common.element.BanqueSQLElement;
156 ilm 18
import org.openconcerto.erp.core.common.ui.AbstractVenteArticleItemTable;
174 ilm 19
import org.openconcerto.erp.core.customerrelationship.mail.EmailTemplateSQLElement;
151 ilm 20
import org.openconcerto.erp.core.finance.payment.element.SDDMessageSQLElement;
21
import org.openconcerto.erp.core.finance.payment.element.SEPAMandateSQLElement;
22
import org.openconcerto.erp.core.finance.payment.element.TypeReglementSQLElement;
23
import org.openconcerto.erp.core.sales.invoice.element.SaisieVenteFactureSQLElement;
144 ilm 24
import org.openconcerto.erp.core.sales.order.ui.TypeFactureCommandeClient;
25
import org.openconcerto.erp.core.sales.pos.element.TicketCaisseSQLElement;
26
import org.openconcerto.erp.core.sales.product.element.ReferenceArticleSQLElement;
149 ilm 27
import org.openconcerto.erp.core.sales.quote.element.DevisLogMailSQLElement;
174 ilm 28
import org.openconcerto.erp.core.supplychain.stock.element.ComposedItemStockUpdater;
156 ilm 29
import org.openconcerto.erp.core.supplychain.stock.element.DepotStockSQLElement;
30
import org.openconcerto.erp.core.supplychain.stock.element.StockItem;
144 ilm 31
import org.openconcerto.sql.changer.convert.AddMDFields;
142 ilm 32
import org.openconcerto.sql.model.DBRoot;
156 ilm 33
import org.openconcerto.sql.model.SQLField;
142 ilm 34
import org.openconcerto.sql.model.SQLField.Properties;
35
import org.openconcerto.sql.model.SQLName;
156 ilm 36
import org.openconcerto.sql.model.SQLRow;
37
import org.openconcerto.sql.model.SQLRowListRSH;
144 ilm 38
import org.openconcerto.sql.model.SQLRowValues;
156 ilm 39
import org.openconcerto.sql.model.SQLSelect;
144 ilm 40
import org.openconcerto.sql.model.SQLSyntax;
41
import org.openconcerto.sql.model.SQLSystem;
142 ilm 42
import org.openconcerto.sql.model.SQLTable;
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);
177 ilm 373
                t.addBooleanColumn("RECU", Boolean.FALSE, false);
142 ilm 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
                }
177 ilm 401
            } else {
402
                // Fix bad default value
403
                String defaultValue = tableCmdFElt.getField("RECU").getDefaultValue();
404
                if (defaultValue != null && defaultValue.equals("true")) {
405
                    AlterTable t = new AlterTable(tableCmdFElt);
406
                    t.alterColumn("RECU", EnumSet.allOf(Properties.class), "boolean", "false", false);
407
                    tableCmdFElt.getBase().getDataSource().execute(t.asString());
408
                    root.refetchTable(tableCmdFElt.getName());
409
                    root.getSchema().updateVersion();
410
                    UpdateBuilder build = new UpdateBuilder(tableCmdFElt);
411
                    build.setObject("RECU", Boolean.FALSE);
412
                    build.set("QTE_RECUE", "\"QTE\"*\"QTE_UNITAIRE\"");
413
 
414
                    Where w = Where.createRaw(tableCmdFElt.getField("QTE_RECUE").getQuotedName() + " < (" + tableCmdFElt.getField("QTE").getQuotedName() + "*"
415
                            + tableCmdFElt.getField("QTE_UNITAIRE").getQuotedName() + ")", tableCmdFElt.getField("QTE_UNITAIRE"), tableCmdFElt.getField("QTE"), tableCmdFElt.getField("QTE_RECUE"));
416
                    build.setWhere(w.or(new Where(tableCmdFElt.getKey(), "=", 1)));
417
                    System.err.println(build.asString());
418
                    tableCmdFElt.getDBSystemRoot().getDataSource().execute(build.asString());
419
                }
420
 
142 ilm 421
            }
422
 
156 ilm 423
            // Champ matière
424
            SQLTable tableArt = root.getTable("ARTICLE");
425
            if (!tableArt.contains("MATIERE")) {
426
                final AlterTable alter = new AlterTable(tableArt);
427
                alter.addVarCharColumn("MATIERE", 128);
428
                tableArt.getBase().getDataSource().execute(alter.asString());
429
                tableArt.getSchema().updateVersion();
430
                tableArt.fetchFields();
431
            }
432
 
142 ilm 433
        }
144 ilm 434
        // ----------------------- 1.5.1
142 ilm 435
 
144 ilm 436
        if (!root.contains("FABRICANT")) {
437
            final SQLCreateTable createTableEtat = new SQLCreateTable(root, "FABRICANT");
438
            createTableEtat.addVarCharColumn("NOM", 256);
439
            root.getBase().getDataSource().execute(createTableEtat.asString());
440
            InstallationPanel.insertUndef(createTableEtat);
441
            root.refetchTable("FABRICANT");
442
            root.getSchema().updateVersion();
443
 
444
            final AlterTable alterA = new AlterTable(root.getTable("ARTICLE"));
445
            alterA.addForeignColumn("ID_FABRICANT", root.findTable("FABRICANT"));
446
            root.getBase().getDataSource().execute(alterA.asString());
447
            root.refetchTable("ARTICLE");
448
            root.getSchema().updateVersion();
449
        }
450
 
451
        // CIM
452
        {
453
            if (!root.contains("ETAT_DEMANDE_ACHAT_ELEMENT")) {
454
                final SQLCreateTable createTableEtat = new SQLCreateTable(root, "ETAT_DEMANDE_ACHAT_ELEMENT");
455
                createTableEtat.addVarCharColumn("NOM", 256);
456
                createTableEtat.addIntegerColumn("COLOR", null, true);
457
                root.getBase().getDataSource().execute(createTableEtat.asString());
458
                InstallationPanel.insertUndef(createTableEtat);
459
                root.refetchTable("ETAT_DEMANDE_ACHAT_ELEMENT");
460
                root.getSchema().updateVersion();
461
 
462
                SQLRowValues rowVals = new SQLRowValues(root.getTable("ETAT_DEMANDE_ACHAT_ELEMENT"));
463
                rowVals.put("NOM", "En attente");
464
                rowVals.insert();
465
                rowVals.put("NOM", "Demande de prix");
466
                rowVals.insert();
467
                rowVals.put("NOM", "En commande");
468
                rowVals.insert();
469
                rowVals.put("NOM", "Réceptionnée");
470
                rowVals.insert();
471
                rowVals.put("NOM", "Réception partielle");
472
                rowVals.insert();
473
                rowVals.put("NOM", "A relancer");
474
                rowVals.insert();
475
            }
476
 
477
            if (!root.contains("DEMANDE_ACHAT_ELEMENT")) {
478
                final SQLCreateTable createTableDmd = new SQLCreateTable(root, "DEMANDE_ACHAT_ELEMENT");
479
                createTableDmd.addVarCharColumn("CODE", 256);
480
                createTableDmd.addVarCharColumn("NOM", 256);
481
                createTableDmd.addIntegerColumn("QTE", 1);
482
                createTableDmd.addDecimalColumn("QTE_UNITAIRE", 16, 6, BigDecimal.ONE, false);
483
 
484
                createTableDmd.addVarCharColumn("REPERE", 256);
485
                createTableDmd.addVarCharColumn("REFERENCE", 256);
486
                createTableDmd.addForeignColumn("FABRICANT");
487
                createTableDmd.addColumn("DATE", "date");
488
                createTableDmd.addForeignColumn("ETAT_DEMANDE_ACHAT_ELEMENT");
489
                createTableDmd.addForeignColumn("UNITE_VENTE");
490
                createTableDmd.addForeignColumn("ARTICLE");
491
                createTableDmd.addForeignColumn("FAMILLE_ARTICLE");
492
                createTableDmd.addForeignColumn("FOURNISSEUR");
493
                createTableDmd.addBooleanColumn("EN_STOCK", Boolean.FALSE, false);
494
 
495
                root.getBase().getDataSource().execute(createTableDmd.asString());
496
                InstallationPanel.insertUndef(createTableDmd);
497
                root.refetchTable("DEMANDE_ACHAT_ELEMENT");
498
                root.getSchema().updateVersion();
499
            }
500
 
501
            if (!root.contains("ETAT_DEMANDE_PRIX")) {
502
                final SQLCreateTable createTableEtat = new SQLCreateTable(root, "ETAT_DEMANDE_PRIX");
503
                createTableEtat.addVarCharColumn("NOM", 256);
504
                root.getBase().getDataSource().execute(createTableEtat.asString());
505
                InstallationPanel.insertUndef(createTableEtat);
506
                root.refetchTable("ETAT_DEMANDE_PRIX");
507
                root.getSchema().updateVersion();
508
 
509
                SQLRowValues rowVals = new SQLRowValues(root.getTable("ETAT_DEMANDE_PRIX"));
510
                rowVals.put("NOM", "En attente");
511
                rowVals.insert();
512
                rowVals.put("NOM", "Refusée");
513
                rowVals.insert();
514
                rowVals.put("NOM", "Acceptée");
515
                rowVals.insert();
516
                rowVals.put("NOM", "En cours");
517
                rowVals.insert();
518
            }
519
 
520
            if (!root.contains("DEMANDE_PRIX")) {
521
                final SQLCreateTable createTableDmd = new SQLCreateTable(root, "DEMANDE_PRIX");
522
                createTableDmd.addVarCharColumn("NUMERO", 256);
523
                createTableDmd.addVarCharColumn("OBJET", 1024);
524
                createTableDmd.addForeignColumn("FOURNISSEUR");
525
                // createTableDemande.addForeignColumn("ID_AFFAIRE",
526
                // ctxt.getRoot().findTable("AFFAIRE"));
527
                createTableDmd.addColumn("DATE", "date");
528
                // createTableDemandeM.addColumn("DATE_DISPOSITION", "date");
529
                createTableDmd.addColumn("DATE_BUTOIRE", "date");
530
                createTableDmd.addColumn("T_HT", "bigint DEFAULT 0");
531
                createTableDmd.addColumn("T_TVA", "bigint DEFAULT 0");
532
                createTableDmd.addColumn("T_TTC", "bigint DEFAULT 0");
533
                // createTableDemandeM.addForeignColumn("ID_TAXE", findTableTaxe);
534
 
535
                createTableDmd.addForeignColumn("COMMERCIAL");
536
                createTableDmd.addForeignColumn("ETAT_DEMANDE_PRIX");
537
                // createTableDemandeM.addForeignColumn("ID_ADRESSE",
538
                // ctxt.getRoot().findTable("ADRESSE"));
539
                createTableDmd.addVarCharColumn("INFOS", 1024);
540
 
541
                root.getBase().getDataSource().execute(createTableDmd.asString());
542
                InstallationPanel.insertUndef(createTableDmd);
543
                root.refetchTable("DEMANDE_PRIX");
544
                root.getSchema().updateVersion();
545
 
546
                final SQLCreateTable createTableDemandeMElt = new SQLCreateTable(root, "DEMANDE_PRIX_ELEMENT");
547
                createTableDemandeMElt.addVarCharColumn("NOM", 512);
548
                createTableDemandeMElt.addForeignColumn("DEMANDE_PRIX");
549
                createTableDemandeMElt.addForeignColumn("ID_TAXE", root.getTable("TAXE").getSQLName(), root.getTable("TAXE").getKey().getName(), "2");
550
 
551
                createTableDemandeMElt.addIntegerColumn("QTE", 0);
552
                createTableDemandeMElt.addColumn("VALEUR_METRIQUE_3", "real DEFAULT 0");
553
                createTableDemandeMElt.addColumn("PRIX_METRIQUE_VT_3", "bigint DEFAULT 0");
554
 
555
                createTableDemandeMElt.addDecimalColumn("PA_HT", 16, 6, BigDecimal.ZERO, false);
556
                createTableDemandeMElt.addDecimalColumn("PRIX_METRIQUE_HA_2", 16, 6, BigDecimal.ZERO, false);
557
 
558
                createTableDemandeMElt.addDecimalColumn("PRIX_METRIQUE_HA_1", 16, 6, BigDecimal.ZERO, false);
559
                createTableDemandeMElt.addDecimalColumn("T_PA_TTC", 16, 2, BigDecimal.ZERO, false);
560
                createTableDemandeMElt.addColumn("VALEUR_METRIQUE_1", "real DEFAULT 0");
561
                createTableDemandeMElt.addDecimalColumn("T_PA_HT", 16, 6, BigDecimal.ZERO, false);
562
                createTableDemandeMElt.addColumn("T_POIDS", "real DEFAULT 0");
563
                createTableDemandeMElt.addColumn("VALEUR_METRIQUE_2", "real DEFAULT 0");
564
                createTableDemandeMElt.addDecimalColumn("PRIX_METRIQUE_HA_3", 16, 6, BigDecimal.ZERO, false);
565
 
566
                createTableDemandeMElt.addDecimalColumn("PRIX_METRIQUE_VT_2", 16, 6, BigDecimal.ZERO, false);
567
                createTableDemandeMElt.addDecimalColumn("PRIX_METRIQUE_VT_1", 16, 6, BigDecimal.ZERO, false);
568
                createTableDemandeMElt.addDecimalColumn("T_PV_TTC", 16, 2, BigDecimal.ZERO, false);
569
                createTableDemandeMElt.addForeignColumn("ID_METRIQUE_1", root.findTable("METRIQUE"));
570
                SQLTable findTable = root.findTable("MODE_VENTE_ARTICLE");
571
                createTableDemandeMElt.addForeignColumn("ID_MODE_VENTE_ARTICLE", findTable.getSQLName(), findTable.getKey().getName(), String.valueOf(ReferenceArticleSQLElement.A_LA_PIECE));
572
                createTableDemandeMElt.addForeignColumn("ID_METRIQUE_3", root.findTable("METRIQUE"));
573
                createTableDemandeMElt.addForeignColumn("ID_STYLE", root.findTable("STYLE"));
574
                createTableDemandeMElt.addForeignColumn("ID_METRIQUE_2", root.findTable("METRIQUE"));
575
                createTableDemandeMElt.addIntegerColumn("QTE_ACHAT", 1);
576
                createTableDemandeMElt.addForeignColumn("ID_DEVISE", root.findTable("DEVISE"));
577
                createTableDemandeMElt.addForeignColumn("ID_FAMILLE_ARTICLE", root.findTable("FAMILLE_ARTICLE"));
578
 
579
                createTableDemandeMElt.addVarCharColumn("CODE", 256);
580
                createTableDemandeMElt.addColumn("SERVICE", "boolean DEFAULT false");
581
 
582
                createTableDemandeMElt.addDecimalColumn("T_PV_HT", 16, 6, BigDecimal.ZERO, false);
583
                createTableDemandeMElt.addDecimalColumn("PV_HT", 16, 6, BigDecimal.ZERO, false);
584
                createTableDemandeMElt.addColumn("POIDS", "real DEFAULT 0");
585
                createTableDemandeMElt.addDecimalColumn("PA_DEVISE", 16, 6, BigDecimal.ZERO, false);
586
                createTableDemandeMElt.addDecimalColumn("PA_DEVISE_T", 16, 6, BigDecimal.ZERO, false);
587
 
588
                createTableDemandeMElt.addForeignColumn("ID_ARTICLE", root.findTable("ARTICLE"));
589
                createTableDemandeMElt.addDecimalColumn("QTE_UNITAIRE", 16, 6, BigDecimal.ONE, false);
590
                createTableDemandeMElt.addForeignColumn("ID_UNITE_VENTE", root.findTable("UNITE_VENTE"));
591
                createTableDemandeMElt.addVarCharColumn("DESCRIPTIF", 2048);
592
                createTableDemandeMElt.addIntegerColumn("NIVEAU", 1);
156 ilm 593
                if (root.contains("DEPOT_STOCK")) {
594
                    createTableDemandeMElt.addForeignColumn("DEPOT_STOCK");
595
                }
144 ilm 596
                createTableDemandeMElt.addForeignColumn("ID_ECO_CONTRIBUTION", root.findTable("ECO_CONTRIBUTION"));
597
                createTableDemandeMElt.addDecimalColumn("ECO_CONTRIBUTION", 16, 2, BigDecimal.ZERO, false);
598
                createTableDemandeMElt.addDecimalColumn("T_ECO_CONTRIBUTION", 16, 2, BigDecimal.ZERO, false);
599
                createTableDemandeMElt.addForeignColumn("ID_DEMANDE_ACHAT_ELEMENT", root.findTable("DEMANDE_ACHAT_ELEMENT"));
600
 
601
                root.getBase().getDataSource().execute(createTableDemandeMElt.asString());
602
                InstallationPanel.insertUndef(createTableDemandeMElt);
603
                root.refetchTable("DEMANDE_PRIX_ELEMENT");
604
                root.getSchema().updateVersion();
605
 
606
                final AlterTable alterNumero = new AlterTable(root.getTable("NUMEROTATION_AUTO"));
607
                alterNumero.addVarCharColumn("DMD_PRIX_FORMAT", 128);
608
                alterNumero.addIntegerColumn("DMD_PRIX_START", 1);
609
                root.getBase().getDataSource().execute(alterNumero.asString());
610
                root.refetchTable("NUMEROTATION_AUTO");
611
                root.getSchema().updateVersion();
612
 
613
                final AlterTable alterCmd = new AlterTable(root.getTable("COMMANDE_ELEMENT"));
614
                alterCmd.addForeignColumn("ID_DEMANDE_ACHAT_ELEMENT", root.findTable("DEMANDE_ACHAT_ELEMENT"));
615
                root.getBase().getDataSource().execute(alterCmd.asString());
616
                root.refetchTable("COMMANDE_ELEMENT");
617
                root.getSchema().updateVersion();
618
 
619
                final AlterTable alterF = new AlterTable(root.getTable("FACTURE_FOURNISSEUR_ELEMENT"));
620
                alterF.addForeignColumn("ID_DEMANDE_ACHAT_ELEMENT", root.findTable("DEMANDE_ACHAT_ELEMENT"));
621
                root.getBase().getDataSource().execute(alterF.asString());
622
                root.refetchTable("FACTURE_FOURNISSEUR_ELEMENT");
623
                root.getSchema().updateVersion();
624
 
625
                final AlterTable alterB = new AlterTable(root.getTable("BON_RECEPTION_ELEMENT"));
626
                alterB.addForeignColumn("ID_DEMANDE_ACHAT_ELEMENT", root.findTable("DEMANDE_ACHAT_ELEMENT"));
627
                root.getBase().getDataSource().execute(alterB.asString());
628
                root.refetchTable("BON_RECEPTION_ELEMENT");
629
                root.getSchema().updateVersion();
630
            }
631
        }
632
 
633
        SQLTable tableDmdAChat = root.findTable("DEMANDE_ACHAT_ELEMENT");
634
        if (!tableDmdAChat.contains("ID_FAMILLE_ARTICLE")) {
635
            final AlterTable alterB = new AlterTable(tableDmdAChat);
636
            alterB.addForeignColumn("ID_FAMILLE_ARTICLE", root.findTable("FAMILLE_ARTICLE"));
637
            root.getBase().getDataSource().execute(alterB.asString());
638
            root.refetchTable("DEMANDE_ACHAT_ELEMENT");
639
            root.getSchema().updateVersion();
640
        }
641
        if (!tableDmdAChat.contains("ID_COMMANDE")) {
642
            final AlterTable alterB = new AlterTable(tableDmdAChat);
643
            alterB.addForeignColumn("ID_COMMANDE", root.findTable("COMMANDE"));
644
            root.getBase().getDataSource().execute(alterB.asString());
645
            root.refetchTable("DEMANDE_ACHAT_ELEMENT");
646
            root.getSchema().updateVersion();
647
        }
648
 
649
        if (!tableDmdAChat.contains("ID_BON_RECEPTION")) {
650
            final AlterTable alterB = new AlterTable(tableDmdAChat);
651
            alterB.addForeignColumn("ID_BON_RECEPTION", root.findTable("BON_RECEPTION"));
652
            root.getBase().getDataSource().execute(alterB.asString());
653
            root.refetchTable("DEMANDE_ACHAT_ELEMENT");
654
            root.getSchema().updateVersion();
655
        }
149 ilm 656
        if (!tableDmdAChat.contains("REPRISE")) {
657
            final AlterTable alterB = new AlterTable(tableDmdAChat);
658
            alterB.addBooleanColumn("REPRISE", Boolean.FALSE, false);
659
            root.getBase().getDataSource().execute(alterB.asString());
660
            root.refetchTable("DEMANDE_ACHAT_ELEMENT");
661
            root.getSchema().updateVersion();
662
        }
663
 
156 ilm 664
        if (!tableDmdAChat.contains("ID_COMMANDE")) {
665
            final AlterTable alterB = new AlterTable(tableDmdAChat);
666
            alterB.addForeignColumn("ID_COMMANDE", root.findTable("COMMANDE"));
667
            root.getBase().getDataSource().execute(alterB.asString());
668
            root.refetchTable("DEMANDE_ACHAT_ELEMENT");
669
            root.getSchema().updateVersion();
670
        }
671
 
672
        if (!tableDmdAChat.contains("REPRISE")) {
673
            final AlterTable alterB = new AlterTable(tableDmdAChat);
674
            alterB.addBooleanColumn("REPRISE", Boolean.FALSE, false);
675
            root.getBase().getDataSource().execute(alterB.asString());
676
            root.refetchTable("DEMANDE_ACHAT_ELEMENT");
677
            root.getSchema().updateVersion();
678
        }
679
 
149 ilm 680
        if (!tableDmdAChat.contains("IMPORT")) {
681
            final AlterTable alterB = new AlterTable(tableDmdAChat);
682
            alterB.addBooleanColumn("IMPORT", Boolean.FALSE, false);
683
            root.getBase().getDataSource().execute(alterB.asString());
684
            root.refetchTable("DEMANDE_ACHAT_ELEMENT");
685
            root.getSchema().updateVersion();
686
        }
687
 
156 ilm 688
        if (!tableDmdAChat.contains("ID_BON_RECEPTION")) {
689
            final AlterTable alterB = new AlterTable(tableDmdAChat);
690
            alterB.addForeignColumn("ID_BON_RECEPTION", root.findTable("BON_RECEPTION"));
691
            root.getBase().getDataSource().execute(alterB.asString());
692
            root.refetchTable("DEMANDE_ACHAT_ELEMENT");
693
            root.getSchema().updateVersion();
694
        }
144 ilm 695
        if (!tableDmdAChat.contains("QTE_RECUE")) {
696
            final AlterTable alterB = new AlterTable(tableDmdAChat);
697
            alterB.addIntegerColumn("QTE_RECUE", 0);
698
            root.getBase().getDataSource().execute(alterB.asString());
699
            root.refetchTable("DEMANDE_ACHAT_ELEMENT");
700
            root.getSchema().updateVersion();
701
        }
156 ilm 702
 
144 ilm 703
        if (!tableDmdAChat.contains("REPERE_SOURCE")) {
704
            final AlterTable alterB = new AlterTable(tableDmdAChat);
705
            alterB.addVarCharColumn("REPERE_SOURCE", 128);
706
            alterB.addVarCharColumn("CODE_SOURCE", 128);
707
            alterB.addVarCharColumn("NOM_SOURCE", 128);
708
            root.getBase().getDataSource().execute(alterB.asString());
709
            root.refetchTable("DEMANDE_ACHAT_ELEMENT");
710
            root.getSchema().updateVersion();
711
        }
712
        SQLTable tableEtatDmdAChat = root.findTable("ETAT_DEMANDE_ACHAT_ELEMENT");
713
        if (!tableEtatDmdAChat.contains("COLOR")) {
714
            final AlterTable alterB = new AlterTable(tableEtatDmdAChat);
715
            alterB.addIntegerColumn("COLOR", null, true);
716
            root.getBase().getDataSource().execute(alterB.asString());
717
            root.refetchTable("ETAT_DEMANDE_ACHAT_ELEMENT");
718
            root.getSchema().updateVersion();
719
        }
720
 
721
        if (!tableDmdAChat.contains("T_HT")) {
722
            final AlterTable alterB = new AlterTable(tableDmdAChat);
723
            alterB.addVarCharColumn("NUMERO_SE", 128);
724
            alterB.addVarCharColumn("DESCRIPTIF_SE", 512);
725
            alterB.addVarCharColumn("REVISION", 48);
726
            alterB.addDecimalColumn("P_HT", 16, 6, BigDecimal.ZERO, false);
727
            alterB.addDecimalColumn("T_HT", 16, 6, BigDecimal.ZERO, false);
728
            root.getBase().getDataSource().execute(alterB.asString());
729
            root.refetchTable("DEMANDE_ACHAT_ELEMENT");
730
            root.getSchema().updateVersion();
731
        }
732
 
733
        if (!root.contains("FACTURATION_COMMANDE_CLIENT")) {
734
            final SQLCreateTable createTableEtat = new SQLCreateTable(root, "FACTURATION_COMMANDE_CLIENT");
735
            createTableEtat.addVarCharColumn("NOM", 256);
736
            createTableEtat.addIntegerColumn("TYPE_FACTURE", TypeFactureCommandeClient.GLOBALE.getId());
737
            createTableEtat.addBooleanColumn("CHOICE", Boolean.FALSE, false);
738
            createTableEtat.addForeignColumn("TYPE_REGLEMENT");
739
            createTableEtat.addForeignColumn("COMMANDE_CLIENT");
740
            createTableEtat.addForeignColumn("SAISIE_VENTE_FACTURE");
741
            createTableEtat.addForeignColumn("MODELE");
742
            createTableEtat.addIntegerColumn("AJOURS", 0);
743
            createTableEtat.addDecimalColumn("MONTANT", 16, 8, BigDecimal.ZERO, false);
744
            createTableEtat.addDecimalColumn("POURCENT", 16, 8, BigDecimal.ZERO, false);
745
            createTableEtat.addBooleanColumn("COMPTANT", Boolean.FALSE, false);
746
            createTableEtat.addColumn("DATE_PREVISIONNELLE", "date");
747
            root.getBase().getDataSource().execute(createTableEtat.asString());
748
            InstallationPanel.insertUndef(createTableEtat);
749
            root.refetchTable("FACTURATION_COMMANDE_CLIENT");
750
            root.getSchema().updateVersion();
751
        }
752
        SQLTable tableFacturationCmd = root.getTable("FACTURATION_COMMANDE_CLIENT");
753
        if (!tableFacturationCmd.contains("FIN_MOIS")) {
754
            AlterTable alt = new AlterTable(tableFacturationCmd);
755
            alt.addBooleanColumn("FIN_MOIS", Boolean.FALSE, false);
756
            root.getBase().getDataSource().execute(alt.asString());
757
            root.refetchTable("FACTURATION_COMMANDE_CLIENT");
758
            root.getSchema().updateVersion();
759
        }
760
        if (!tableFacturationCmd.contains("LENJOUR")) {
761
            AlterTable alt = new AlterTable(tableFacturationCmd);
762
            alt.addIntegerColumn("LENJOUR", 0);
763
            root.getBase().getDataSource().execute(alt.asString());
764
            root.refetchTable("FACTURATION_COMMANDE_CLIENT");
765
            root.getSchema().updateVersion();
766
        }
156 ilm 767
 
144 ilm 768
        if (!root.contains("CHIFFRAGE_COMMANDE_CLIENT")) {
769
            final SQLCreateTable createTableEtat = new SQLCreateTable(root, "CHIFFRAGE_COMMANDE_CLIENT");
770
            createTableEtat.addVarCharColumn("NOM", 256);
771
            createTableEtat.addForeignColumn("COMMANDE_CLIENT");
772
            createTableEtat.addForeignColumn("UNITE_VENTE");
773
            createTableEtat.addForeignColumn("FAMILLE_ARTICLE");
774
            createTableEtat.addDecimalColumn("QTE", 16, 8, BigDecimal.ZERO, false);
775
            createTableEtat.addDecimalColumn("PA_HT", 16, 8, BigDecimal.ZERO, false);
776
            createTableEtat.addDecimalColumn("PV_HT", 16, 8, BigDecimal.ZERO, false);
777
            createTableEtat.addDecimalColumn("T_PV_HT", 16, 8, BigDecimal.ZERO, false);
778
            createTableEtat.addDecimalColumn("T_PA_HT", 16, 8, BigDecimal.ZERO, false);
779
            createTableEtat.addDecimalColumn("MARGE", 16, 8, BigDecimal.ZERO, false);
780
            root.getBase().getDataSource().execute(createTableEtat.asString());
781
            InstallationPanel.insertUndef(createTableEtat);
782
            root.refetchTable("CHIFFRAGE_COMMANDE_CLIENT");
783
            root.getSchema().updateVersion();
784
        }
785
 
786
        // VARIABLE_SALARIE
787
        SQLTable tableVarSal = root.findTable("VARIABLE_SALARIE");
788
        if (!tableVarSal.contains("HEURE_ABS_DEFAULT_VAL")) {
789
 
790
            final AlterTable alterB = new AlterTable(root.getTable("VARIABLE_SALARIE"));
791
 
792
            for (org.openconcerto.sql.model.SQLField sqlField : tableVarSal.getContentFields()) {
793
 
794
                String field = sqlField.getName();
795
                if (!field.equalsIgnoreCase("ID_USER_COMMON_CREATE") && !field.equalsIgnoreCase("ID_USER_COMMON_MODIFY") && !field.equalsIgnoreCase("MODIFICATION_DATE")
796
                        && !field.equalsIgnoreCase("CREATION_DATE") && !field.equalsIgnoreCase("HEURE_TRAV")) {
797
                    alterB.addColumn(field + "_DEFAULT_VAL", "real DEFAULT 0");
798
                }
799
            }
800
 
801
            root.getBase().getDataSource().execute(alterB.asString());
802
            root.refetchTable("VARIABLE_SALARIE");
803
            root.getSchema().updateVersion();
804
        }
805
 
806
        SQLTable tableFpaye = root.findTable("FICHE_PAYE");
807
        if (!tableFpaye.contains("TAXE_CM_SAL")) {
808
            final AlterTable alterB = new AlterTable(tableFpaye);
809
            alterB.addDecimalColumn("SAL_BASE_BRUT", 16, 2, BigDecimal.ZERO, false);
810
            alterB.addDecimalColumn("TAXE_CM_SAL", 16, 2, BigDecimal.ZERO, false);
811
            alterB.addDecimalColumn("TAXE_CM_PAT", 16, 2, BigDecimal.ZERO, false);
812
            alterB.addDecimalColumn("SAL_BRUT_COTISABLE", 16, 2, BigDecimal.ZERO, false);
813
            alterB.addDecimalColumn("SAL_BRUT_TAXABLE", 16, 2, BigDecimal.ZERO, false);
814
            root.getBase().getDataSource().execute(alterB.asString());
815
            root.refetchTable("FICHE_PAYE");
816
            root.getSchema().updateVersion();
817
 
818
            SQLTable tableElementPaye = root.getTable("FICHE_PAYE_ELEMENT");
819
 
820
            AlterTable tEltPaye = new AlterTable(tableElementPaye);
821
            tEltPaye.alterColumn("NOM", EnumSet.allOf(Properties.class), "varchar(512)", "''", false);
822
            tableElementPaye.getBase().getDataSource().execute(tEltPaye.asString());
823
            tableElementPaye.getSchema().updateVersion();
824
            tableElementPaye.fetchFields();
825
 
826
        }
827
 
177 ilm 828
        if (!tableFpaye.contains("CSG_REDUITE")) {
829
            final AlterTable alterB = new AlterTable(tableFpaye);
830
            alterB.addDecimalColumn("CSG_REDUITE", 16, 2, BigDecimal.ZERO, false);
831
            alterB.addDecimalColumn("SAL_BRUT_CSG", 16, 2, BigDecimal.ZERO, false);
832
            alterB.addDecimalColumn("SAL_BRUT_CSG_REDUITE", 16, 2, BigDecimal.ZERO, false);
833
            root.getBase().getDataSource().execute(alterB.asString());
834
            root.refetchTable("FICHE_PAYE");
835
            root.getSchema().updateVersion();
836
        }
837
 
144 ilm 838
        SQLTable tableCpaye = root.findTable("CUMULS_PAYE");
839
        if (!tableCpaye.contains("TAXE_CM_SAL_C")) {
840
            final AlterTable alterB = new AlterTable(tableCpaye);
841
            alterB.addDecimalColumn("TAXE_CM_SAL_C", 16, 2, BigDecimal.ZERO, false);
842
            alterB.addDecimalColumn("TAXE_CM_PAT_C", 16, 2, BigDecimal.ZERO, false);
843
            alterB.addDecimalColumn("SAL_BRUT_COTISABLE_C", 16, 2, BigDecimal.ZERO, false);
844
            alterB.addDecimalColumn("SAL_BRUT_TAXABLE_C", 16, 2, BigDecimal.ZERO, false);
845
            alterB.addDecimalColumn("SAL_BASE_BRUT_C", 16, 2, BigDecimal.ZERO, false);
846
            root.getBase().getDataSource().execute(alterB.asString());
847
            root.refetchTable("CUMULS_PAYE");
848
            root.getSchema().updateVersion();
849
        }
850
 
851
        SQLTable tableEtatCivil = root.findTable("ETAT_CIVIL");
852
        if (!tableEtatCivil.contains("NATIONNALITE")) {
853
            final AlterTable alterB = new AlterTable(tableEtatCivil);
854
            alterB.addVarCharColumn("NATIONNALITE", 256);
855
            alterB.addVarCharColumn("CNPS", 256);
856
            alterB.addVarCharColumn("TYPE_PIECE_IDENTITE", 256);
857
            alterB.addVarCharColumn("NUMERO_PIECE", 256);
858
            alterB.addVarCharColumn("NOM_PERE", 256);
859
            alterB.addVarCharColumn("NOM_MERE", 256);
860
            alterB.addVarCharColumn("CONJOINT_NOM", 256);
861
            alterB.addColumn("CONJOINT_DATE_NAISSANCE", "date");
862
            alterB.addVarCharColumn("CONJOINT_LIEU_NAISSANCE", 256);
863
            alterB.addVarCharColumn("CONJOINT_PROFESSION", 256);
864
            alterB.addColumn("DATE_DEBUT_PIECE", "date");
865
            alterB.addColumn("DATE_FIN_PIECE", "date");
866
            root.getBase().getDataSource().execute(alterB.asString());
867
            root.refetchTable("ETAT_CIVIL");
868
            root.getSchema().updateVersion();
869
        }
870
 
871
        // ARTICLE CLIENT
872
        if (!root.contains("ARTICLE_CODE_CLIENT")) {
873
            final SQLCreateTable createTableEtat = new SQLCreateTable(root, "ARTICLE_CODE_CLIENT");
874
            createTableEtat.addVarCharColumn("NOM", 512);
875
            createTableEtat.addVarCharColumn("CODE", 512);
876
            createTableEtat.addForeignColumn("CLIENT");
877
            createTableEtat.addForeignColumn("ARTICLE");
878
            root.getBase().getDataSource().execute(createTableEtat.asString());
879
            InstallationPanel.insertUndef(createTableEtat);
880
            root.refetchTable("ARTICLE_CODE_CLIENT");
881
            root.getSchema().updateVersion();
882
        }
883
        SQLTable tableClient = root.getTable("CLIENT");
884
        if (!tableClient.contains("CODE_FOURNISSEUR")) {
885
            final AlterTable alter = new AlterTable(tableClient);
886
            alter.addVarCharColumn("CODE_FOURNISSEUR", 256);
887
            tableCompte.getBase().getDataSource().execute(alter.asString());
888
            tableCompte.getSchema().updateVersion();
889
            tableCompte.fetchFields();
890
        }
891
        SQLTable tableArticle = root.getTable("ARTICLE");
892
        if (!tableArticle.contains("TARE")) {
893
            final AlterTable alterArticle = new AlterTable(tableArticle);
894
            alterArticle.addDecimalColumn("TARE", 16, 8, BigDecimal.ZERO, true);
895
            tableArticle.getBase().getDataSource().execute(alterArticle.asString());
896
            tableArticle.getSchema().updateVersion();
897
            tableArticle.fetchFields();
898
        }
899
        if (!tableArticle.contains("IFCO")) {
900
            final AlterTable alterArticle = new AlterTable(tableArticle);
901
            alterArticle.addIntegerColumn("IFCO", 0);
902
            tableArticle.getBase().getDataSource().execute(alterArticle.asString());
903
            tableArticle.getSchema().updateVersion();
904
            tableArticle.fetchFields();
905
        }
177 ilm 906
        if (!tableArticle.contains("DLC")) {
907
            final AlterTable alterArticle = new AlterTable(tableArticle);
908
            alterArticle.addColumn("DLC", "date");
909
            tableArticle.getBase().getDataSource().execute(alterArticle.asString());
910
            tableArticle.getSchema().updateVersion();
911
            tableArticle.fetchFields();
912
        }
144 ilm 913
        SQLTable tableFournisseur = root.getTable("FOURNISSEUR");
914
        if (!tableFournisseur.contains("NUMERO_TVA")) {
915
            final AlterTable alter = new AlterTable(tableFournisseur);
916
            alter.addVarCharColumn("NUMERO_TVA", 128);
917
            alter.addVarCharColumn("SIRET", 128);
918
            tableFournisseur.getBase().getDataSource().execute(alter.asString());
919
            tableFournisseur.getSchema().updateVersion();
920
            tableFournisseur.fetchFields();
921
        }
922
 
923
        // POS
149 ilm 924
        final SQLTable caisseT = root.getTable("CAISSE");
925
        final String registerLogTableName = "CAISSE_JOURNAL";
926
        if (!root.contains(registerLogTableName)) {
144 ilm 927
            final SQLTable receiptT = root.getTable("TICKET_CAISSE");
928
            final SQLTable userT = root.findTable("USER_COMMON", true);
929
 
149 ilm 930
            final SQLCreateTable createLogT = new SQLCreateTable(caisseT.getDBRoot(), registerLogTableName);
144 ilm 931
            createLogT.setCreateOrder(false);
932
            AddMDFields.addFields(createLogT, userT);
933
            createLogT.addForeignColumn("ID_CAISSE", caisseT);
934
            createLogT.addDateAndTimeColumn("DATE");
935
            createLogT.addForeignColumn("ID_USER", userT);
936
            createLogT.addVarCharColumn("EVT", 128);
937
            createLogT.addVarCharColumn("CREATOR", 128);
938
            createLogT.addVarCharColumn("CREATOR_VERSION", 128);
939
            createLogT.addUniqueConstraint("logSequence", Arrays.asList("ID_CAISSE", "DATE"));
940
 
941
            final SQLCreateTable createClotureT = new SQLCreateTable(caisseT.getDBRoot(), "CAISSE_CLOTURE");
942
            createClotureT.setCreateOrder(false);
943
            AddMDFields.addFields(createClotureT, userT);
944
            createClotureT.addForeignColumn("ID_ENTREE_JOURNAL", createLogT);
945
            createClotureT.addVarCharColumn("PERIODE", 32);
946
            final String dateType = createClotureT.getSyntax().getTypeNames(java.sql.Date.class).iterator().next();
947
            createClotureT.addColumn("DEBUT", dateType, null, false);
948
            createClotureT.addColumn("FIN", dateType, null, false);
149 ilm 949
            createClotureT.addDecimalColumn("TOTAL_TTC", 16, 6, BigDecimal.ZERO, false);
144 ilm 950
            // don't use foreign keys, we actually want redundant info so that we can check
951
            // coherence
952
            // nullable to allow for days without any sales
953
            createClotureT.addVarCharColumn("PREMIER_TICKET", 64, false, "null", true);
954
            createClotureT.addVarCharColumn("PREMIER_TICKET_HASH", 128, false, "null", true);
955
            createClotureT.addVarCharColumn("DERNIER_TICKET", 64, false, "null", true);
956
            createClotureT.addVarCharColumn("DERNIER_TICKET_HASH", 128, false, "null", true);
957
            createClotureT.addUniqueConstraint("uniqueness", Arrays.asList("ID_ENTREE_JOURNAL"));
958
 
959
            final AlterTable alterRegister = new AlterTable(caisseT);
960
            alterRegister.addForeignColumn("ID_DERNIERE_ENTREE_JOURNAL", createLogT);
961
            alterRegister.addForeignColumn("ID_DERNIERE_CLOTURE", createClotureT);
962
 
963
            final AlterTable alterReceipt = new AlterTable(receiptT);
964
            alterReceipt.addVarCharColumn("FILE_HASH", 128, false, "null", true);
965
            alterReceipt.addVarCharColumn("FILE_HASH_PREVIOUS", 128, false, "null", true);
966
            alterReceipt.addUniqueConstraint("uniqueNumber", new UniqueConstraintCreatorHelper(Arrays.asList("NUMERO"), TicketCaisseSQLElement.UNARCHIVED_WHERE) {
967
                @Override
968
                public Object getObject(SQLSyntax s) {
969
                    if (s.getSystem() == SQLSystem.H2) {
970
                        return TicketCaisseSQLElement.UniqueNumber_PartialUniqueTrigger.class;
971
                    } else {
972
                        return super.getObject(s);
973
                    }
974
                }
975
            });
976
            alterReceipt.addUniqueConstraint("uniqueDate", new UniqueConstraintCreatorHelper(Arrays.asList("ID_CAISSE", "DATE"), TicketCaisseSQLElement.DATE_WHERE) {
977
                @Override
978
                public Object getObject(SQLSyntax s) {
979
                    if (s.getSystem() == SQLSystem.H2) {
980
                        return TicketCaisseSQLElement.UniqueDate_PartialUniqueTrigger.class;
981
                    } else {
982
                        return super.getObject(s);
983
                    }
984
                }
985
            });
986
 
987
            root.createTables(createLogT, createClotureT);
988
            root.getDBSystemRoot().getDataSource().execute(alterRegister.asString());
989
            root.getDBSystemRoot().getDataSource().execute(alterReceipt.asString());
990
            caisseT.getSchema().updateVersion();
991
            alterRegister.getTable().fetchFields();
992
            alterReceipt.getTable().fetchFields();
993
        }
994
 
149 ilm 995
        SQLTable tableArt = root.getTable("ARTICLE");
996
        if (!tableArt.contains("MASQUE_CAISSE")) {
997
            final AlterTable alterArt = new AlterTable(tableArt);
998
            alterArt.addBooleanColumn("MASQUE_CAISSE", Boolean.FALSE, false);
999
            tableArt.getBase().getDataSource().execute(alterArt.asString());
1000
            tableArt.getSchema().updateVersion();
1001
            tableArt.fetchFields();
1002
        }
1003
 
151 ilm 1004
        if (!tableArt.contains("ADDITIONAL_TICKET_COPY")) {
1005
            final AlterTable alterArt = new AlterTable(tableArt);
1006
            alterArt.addBooleanColumn("ADDITIONAL_TICKET_COPY", Boolean.FALSE, false);
1007
            tableArt.getBase().getDataSource().execute(alterArt.asString());
1008
            tableArt.getSchema().updateVersion();
1009
            tableArt.fetchFields();
1010
        }
1011
 
149 ilm 1012
        SQLTable tableDevisAcompte = root.getTable("DEVIS");
1013
 
1014
        if (!tableDevisAcompte.contains("T_ACOMPTE")) {
1015
            final AlterTable alterB = new AlterTable(tableDevisAcompte);
1016
            alterB.addLongColumn("T_ACOMPTE", 0L, false);
1017
            root.getBase().getDataSource().execute(alterB.asString());
1018
            root.refetchTable("DEVIS");
1019
            root.getSchema().updateVersion();
1020
 
1021
            SQLTable tableEncaisse = root.getTable("ENCAISSER_MONTANT");
1022
            final AlterTable alterC = new AlterTable(tableEncaisse);
1023
            alterC.addBooleanColumn("ACOMPTE", Boolean.FALSE, false);
1024
            alterC.addForeignColumn("ID_DEVIS", tableDevisAcompte);
1025
            root.getBase().getDataSource().execute(alterC.asString());
1026
            root.refetchTable("ENCAISSER_MONTANT");
1027
            root.getSchema().updateVersion();
1028
 
1029
            SQLTable tableVF = root.getTable("SAISIE_VENTE_FACTURE");
1030
            final AlterTable alterF = new AlterTable(tableVF);
1031
            alterF.addLongColumn("T_ACOMPTE", 0L, false);
1032
            root.getBase().getDataSource().execute(alterF.asString());
1033
            root.refetchTable("SAISIE_VENTE_FACTURE");
1034
            root.getSchema().updateVersion();
1035
        }
1036
 
144 ilm 1037
        // Paye Simplifiée
1038
        if (!tableFpaye.contains("ALLEGEMENT_COTISATION")) {
1039
            final AlterTable alterB = new AlterTable(tableFpaye);
1040
            alterB.addDecimalColumn("ALLEGEMENT_COTISATION", 16, 2, BigDecimal.ZERO, false);
1041
            alterB.addDecimalColumn("CICE", 16, 2, BigDecimal.ZERO, false);
1042
            alterB.addDecimalColumn("AVANTAGE_NATURE", 16, 2, BigDecimal.ZERO, false);
1043
            alterB.addDecimalColumn("HEURE_TRAV", 16, 2, BigDecimal.ZERO, false);
1044
            root.getBase().getDataSource().execute(alterB.asString());
1045
            root.refetchTable("FICHE_PAYE");
1046
            root.getSchema().updateVersion();
1047
 
1048
            final AlterTable alterC = new AlterTable(tableCpaye);
1049
            alterC.addDecimalColumn("ALLEGEMENT_COTISATION_C", 16, 2, BigDecimal.ZERO, false);
1050
            alterC.addDecimalColumn("CICE_C", 16, 2, BigDecimal.ZERO, false);
1051
            alterC.addDecimalColumn("AVANTAGE_NATURE_C", 16, 2, BigDecimal.ZERO, false);
1052
            root.getBase().getDataSource().execute(alterC.asString());
1053
            root.refetchTable("CUMULS_PAYE");
1054
            root.getSchema().updateVersion();
1055
 
1056
        }
156 ilm 1057
 
144 ilm 1058
        if (!tableFpaye.contains("DETAILS_CONGES")) {
1059
            final AlterTable alterB = new AlterTable(tableFpaye);
1060
            alterB.addVarCharColumn("DETAILS_CONGES", 512);
1061
            root.getBase().getDataSource().execute(alterB.asString());
1062
            root.refetchTable("FICHE_PAYE");
1063
            root.getSchema().updateVersion();
1064
        }
1065
 
1066
        SQLTable tableFPayeElt = root.getTable("FICHE_PAYE_ELEMENT");
1067
        if (!tableFPayeElt.contains("ID_STYLE")) {
1068
            final AlterTable alterB = new AlterTable(tableFPayeElt);
1069
            alterB.addForeignColumn("ID_STYLE", root.getTable("STYLE"));
1070
            root.getBase().getDataSource().execute(alterB.asString());
1071
            root.refetchTable("FICHE_PAYE_ELEMENT");
1072
            root.getSchema().updateVersion();
1073
        }
1074
 
177 ilm 1075
        boolean upVar = false;
1076
        List<String> f = Arrays.asList("IJSS_BRUT", "IJSS_NET", "FRAIS_PRO", "RBT_TRANSPORT", "HEURE_CHOM", "TAUX_CHOM", "HEURE_INDEM", "ECRETEMENT_CSG", "IJSS_BRUT_SECU_PAS");
1077
        final AlterTable alterVarSal = new AlterTable(tableVarSal);
1078
        for (String field : f) {
1079
            if (!tableVarSal.contains(field)) {
1080
                upVar = true;
1081
                alterVarSal.addColumn(field, "real DEFAULT 0");
1082
                alterVarSal.addColumn(field + "_DEFAULT_VAL", "real DEFAULT 0");
1083
                alterVarSal.addColumn(field + "_CUMUL_VAL", "real DEFAULT 0");
144 ilm 1084
            }
177 ilm 1085
        }
1086
        if (upVar) {
1087
            root.getBase().getDataSource().execute(alterVarSal.asString());
144 ilm 1088
            root.refetchTable(tableVarSal.getName());
1089
            root.getSchema().updateVersion();
1090
        }
1091
 
1092
        SQLTable tableDevis = root.getTable("DEVIS");
1093
        if (!tableDevis.contains("ID_TAXE_PORT")) {
1094
            final AlterTable alterB = new AlterTable(tableDevis);
1095
            alterB.addForeignColumn("ID_TAXE_PORT", root.getTable("TAXE"));
1096
            root.getBase().getDataSource().execute(alterB.asString());
1097
            root.refetchTable(tableDevis.getName());
1098
            root.getSchema().updateVersion();
1099
        }
149 ilm 1100
 
1101
        // 1.5.3
1102
        List<String> tableRemiseF = Arrays.asList("COMMANDE", "BON_RECEPTION", "FACTURE_FOURNISSEUR");
1103
        for (String t : tableRemiseF) {
1104
 
1105
            final SQLTable tableCommande = root.getTable(t);
1106
            AlterTable tCommande = new AlterTable(tableCommande);
1107
            boolean updateCmd = false;
1108
            if (!tableCommande.contains("ID_TAXE_PORT")) {
1109
                updateCmd = true;
1110
                tCommande.addForeignColumn("ID_TAXE_PORT", root.getTable("TAXE"));
1111
            }
1112
 
1113
            if (!tableCommande.contains("PORT_HT")) {
1114
                updateCmd = true;
1115
                tCommande.addLongColumn("PORT_HT", Long.valueOf(0), false);
1116
            }
1117
 
1118
            if (!tableCommande.contains("REMISE_HT")) {
1119
                updateCmd = true;
1120
                tCommande.addLongColumn("REMISE_HT", Long.valueOf(0), false);
1121
            }
1122
 
1123
            if (updateCmd) {
1124
                tableCommande.getBase().getDataSource().execute(tCommande.asString());
1125
                tableCommande.getSchema().updateVersion();
1126
                tableCommande.fetchFields();
1127
            }
1128
        }
1129
 
1130
        List<String> tableElement = Arrays.asList("FACTURE_FOURNISSEUR_ELEMENT", "COMMANDE_ELEMENT", "BON_RECEPTION_ELEMENT", "DEMANDE_PRIX_ELEMENT");
1131
        for (String tableName : tableElement) {
1132
            final SQLTable table = root.getTable(tableName);
1133
            if (!table.contains("ID_FAMILLE_ARTICLE")) {
1134
                AlterTable t = new AlterTable(table);
1135
                t.addForeignColumn("ID_FAMILLE_ARTICLE", root.getTable("FAMILLE_ARTICLE"));
1136
                table.getBase().getDataSource().execute(t.asString());
1137
                root.refetchTable(table.getName());
1138
                root.getSchema().updateVersion();
1139
            }
1140
        }
1141
 
1142
        final SQLTable tableBlElt = root.getTable("BON_RECEPTION_ELEMENT");
1143
        if (!tableBlElt.contains("QTE_ORIGINE")) {
1144
            AlterTable t = new AlterTable(tableBlElt);
1145
            t.addIntegerColumn("QTE_ORIGINE", null, true);
1146
            tableBlElt.getBase().getDataSource().execute(t.asString());
1147
            root.refetchTable(tableBlElt.getName());
1148
            root.getSchema().updateVersion();
1149
        }
1150
        // Caisse 1.5.3
1151
        if (!root.getTable(registerLogTableName).contains("HOST_NAME")) {
1152
            final AlterTable alterRegisterLog = new AlterTable(root.getTable(registerLogTableName));
1153
            // to check for shared register installations
1154
            alterRegisterLog.addVarCharColumn("HOST_NAME", 128, true, null, true);
1155
            alterRegisterLog.addVarCharColumn("HOST_USER", 128, true, null, true);
1156
 
1157
            // no longer needed since we need more than the last entry, just remove the duplicate
1158
            // data and select from CAISSE_JOURNAL
1159
            final AlterTable alterRegister = new AlterTable(caisseT);
1160
            alterRegister.dropColumn("ID_DERNIERE_ENTREE_JOURNAL");
1161
 
1162
            for (final String sql : ChangeTable.cat(Arrays.asList(alterRegisterLog, alterRegister))) {
1163
                root.getDBSystemRoot().getDataSource().execute(sql);
1164
            }
1165
            caisseT.getSchema().updateVersion();
1166
            alterRegisterLog.getTable().fetchFields();
1167
            alterRegister.getTable().fetchFields();
1168
        }
1169
        // Prefs compte AN
1170
        SQLTable table = root.findTable("PREFS_COMPTE");
1171
 
1172
        if (!table.getFieldsName().contains("ID_JOURNAL_AN")) {
151 ilm 1173
            AlterTable t = new AlterTable(table);
149 ilm 1174
            t.addForeignColumn("ID_JOURNAL_AN", root.getTable("JOURNAL"));
1175
            t.addBooleanColumn("CREATE_NUL_SOLDE_ECR", Boolean.TRUE, false);
1176
            table.getBase().getDataSource().execute(t.asString());
1177
            table.getSchema().updateVersion();
1178
            table.fetchFields();
1179
        }
1180
 
177 ilm 1181
        if (!table.getFieldsName().contains("ID_JOURNAL_CB_ATTENTE")) {
1182
            AlterTable t = new AlterTable(table);
1183
            t.addForeignColumn("ID_JOURNAL_CB_ATTENTE", root.getTable("JOURNAL"));
1184
            table.getBase().getDataSource().execute(t.asString());
1185
            table.getSchema().updateVersion();
1186
            table.fetchFields();
1187
        }
1188
 
1189
        if (!table.getFieldsName().contains("ID_COMPTE_PCE_CB_ATTENTE")) {
1190
            AlterTable t = new AlterTable(table);
1191
            t.addForeignColumn("ID_COMPTE_PCE_CB_ATTENTE", root.getTable("COMPTE_PCE"));
1192
            table.getBase().getDataSource().execute(t.asString());
1193
            table.getSchema().updateVersion();
1194
            table.fetchFields();
1195
        }
1196
 
151 ilm 1197
        if (!table.getFieldsName().contains("AUTO_LETTRAGE")) {
1198
            AlterTable t = new AlterTable(table);
1199
            t.addBooleanColumn("AUTO_LETTRAGE", Boolean.FALSE, false);
1200
            table.getBase().getDataSource().execute(t.asString());
1201
            table.getSchema().updateVersion();
1202
            table.fetchFields();
1203
        }
156 ilm 1204
        // Etat stock
1205
        if (!root.contains("ETAT_STOCK")) {
151 ilm 1206
 
156 ilm 1207
            try {
1208
                final SQLCreateTable createTable = new SQLCreateTable(root, "ETAT_STOCK");
1209
                createTable.addColumn("DATE", "date");
1210
                createTable.addDecimalColumn("MONTANT_HA", 16, 8, BigDecimal.ZERO, true);
1211
                createTable.addDecimalColumn("MONTANT_VT", 16, 8, BigDecimal.ZERO, true);
1212
                createTable.addBooleanColumn("INVENTAIRE", Boolean.FALSE, false);
1213
                root.getBase().getDataSource().execute(createTable.asString());
1214
                InstallationPanel.insertUndef(createTable);
1215
                root.refetchTable("ETAT_STOCK");
1216
                root.getSchema().updateVersion();
1217
 
1218
                final SQLCreateTable createTableElt = new SQLCreateTable(root, "ETAT_STOCK_ELEMENT");
1219
                createTableElt.addForeignColumn("ARTICLE");
1220
                createTableElt.addForeignColumn("ETAT_STOCK");
1221
                createTableElt.addDecimalColumn("PA", 16, 8, BigDecimal.ZERO, true);
1222
                createTableElt.addDecimalColumn("PV", 16, 8, BigDecimal.ZERO, true);
1223
                createTableElt.addDecimalColumn("T_PA", 16, 8, BigDecimal.ZERO, true);
1224
                createTableElt.addDecimalColumn("T_PV", 16, 8, BigDecimal.ZERO, true);
1225
                createTableElt.addDecimalColumn("QTE", 16, 8, BigDecimal.ZERO, true);
1226
                createTableElt.addDecimalColumn("ECART", 16, 8, BigDecimal.ZERO, true);
1227
                createTableElt.addVarCharColumn("CODE", 256);
1228
                createTableElt.addVarCharColumn("NOM", 2048);
1229
 
1230
                root.getBase().getDataSource().execute(createTableElt.asString());
1231
                InstallationPanel.insertUndef(createTableElt);
1232
                root.refetchTable("ETAT_STOCK_ELEMENT");
1233
                root.getSchema().updateVersion();
1234
 
1235
            } catch (SQLException ex) {
1236
                throw new IllegalStateException("Erreur lors de la création de la table ETAT_STOCK_ELEMENT", ex);
1237
            }
1238
        } else {
1239
            final SQLTable tableEtatStock = root.getTable("ETAT_STOCK_ELEMENT");
1240
            SQLField fieldNom = tableEtatStock.getField("NOM");
1241
            int size = fieldNom.getType().getSize();
1242
            if (size == 512) {
1243
                AlterTable alter = new AlterTable(tableEtatStock);
1244
                alter.alterColumn("NOM", EnumSet.allOf(Properties.class), "varchar(" + 2048 + ")", "''", false);
1245
                tableEtatStock.getBase().getDataSource().execute(alter.asString());
1246
                tableEtatStock.getSchema().updateVersion();
1247
                tableEtatStock.fetchFields();
1248
            }
1249
 
1250
        }
1251
        SQLTable tableMvtStock = root.getTable("MOUVEMENT_STOCK");
1252
        if (!tableMvtStock.contains("ID_ETAT_STOCK")) {
1253
            final AlterTable alter = new AlterTable(tableMvtStock);
1254
            alter.addBooleanColumn("CLOTURE", Boolean.FALSE, false);
1255
            alter.addBooleanColumn("OUVERTURE", Boolean.FALSE, false);
1256
            alter.addForeignColumn("ID_ETAT_STOCK", root.getTable("ETAT_STOCK"));
1257
            tableMvtStock.getBase().getDataSource().execute(alter.asString());
1258
            tableMvtStock.getSchema().updateVersion();
1259
            tableMvtStock.fetchFields();
1260
        }
149 ilm 1261
        SQLTable tableEcr = root.getTable("ECRITURE");
1262
        if (!tableEcr.contains("CLOTURE")) {
1263
            final AlterTable alter = new AlterTable(tableEcr);
1264
            alter.addBooleanColumn("CLOTURE", Boolean.FALSE, false);
1265
            alter.addBooleanColumn("RAN", Boolean.FALSE, false);
1266
            tableEcr.getBase().getDataSource().execute(alter.asString());
1267
            tableEcr.getSchema().updateVersion();
1268
            tableEcr.fetchFields();
1269
        }
1270
 
1271
        // Jour Km Element
1272
        final SQLTable tkmElt = root.getTable("SAISIE_KM_ELEMENT");
1273
        if (!tkmElt.contains("JOUR")) {
1274
            final AlterTable alterKmElt = new AlterTable(tkmElt);
1275
            alterKmElt.addIntegerColumn("JOUR", 1);
1276
            tkmElt.getBase().getDataSource().execute(alterKmElt.asString());
1277
            tkmElt.getSchema().updateVersion();
1278
            tkmElt.fetchFields();
1279
        }
151 ilm 1280
        // Ref bancaires fournisseurs et clients
1281
        for (final SQLTable bankT : Arrays.asList(tableFournisseur, tClient)) {
1282
            if (!bankT.contains("IBAN")) {
1283
                final AlterTable alter = new AlterTable(bankT);
1284
                alter.addVarCharColumn("IBAN", 128);
1285
                alter.addVarCharColumn("BIC", 128);
1286
                bankT.getDBSystemRoot().getDataSource().execute(alter.asString());
1287
                bankT.getSchema().updateVersion();
1288
                bankT.fetchFields();
1289
            }
149 ilm 1290
        }
151 ilm 1291
        final SQLTable typeReglT = root.getTable("TYPE_REGLEMENT");
1292
        if (typeReglT.getRow(TypeReglementSQLElement.PRELEVEMENT) == null) {
1293
            final SQLRowValues directDebitVals = new SQLRowValues(typeReglT).put("NOM", "Prélèvement");
1294
            directDebitVals.put("COMPTANT", Boolean.FALSE).put("ECHEANCE", Boolean.FALSE);
1295
            directDebitVals.setID(TypeReglementSQLElement.PRELEVEMENT).insertVerbatim();
1296
        }
1297
        if (!tableClient.contains("ACCEPTE_EMAIL")) {
1298
            final AlterTable alter = new AlterTable(tableClient);
1299
            alter.addBooleanColumn("ACCEPTE_EMAIL", Boolean.FALSE, false);
1300
            alter.addBooleanColumn("ACCEPTE_COURRIER", Boolean.FALSE, false);
1301
            alter.addBooleanColumn("ACCEPTE_SMS", Boolean.FALSE, false);
1302
            alter.addBooleanColumn("ACCEPTE_TEL", Boolean.FALSE, false);
1303
            exec(alter);
1304
        }
1305
        final SQLTable contactT = root.getTable("CONTACT");
156 ilm 1306
        if (contactT != null && !contactT.contains("DATE_NAISSANCE")) {
151 ilm 1307
            final AlterTable alter = new AlterTable(contactT);
1308
            alter.addColumn("DATE_NAISSANCE", "date");
1309
            exec(alter);
1310
        }
156 ilm 1311
 
151 ilm 1312
        final SQLCreateTable createSDDMsgTable = SDDMessageSQLElement.getCreateTable(root);
1313
        if (createSDDMsgTable != null) {
1314
            final SQLCreateTable createMandate = SEPAMandateSQLElement.getCreateTable(root);
1315
            root.createTables(createSDDMsgTable, createMandate);
1316
            final SQLTable msgT = root.getTable(createSDDMsgTable.getName());
1317
            final SQLTable mandateT = root.getTable(createMandate.getName());
1318
 
1319
            final AlterTable alterFact = new AlterTable(root.getTable(SaisieVenteFactureSQLElement.TABLENAME));
1320
            alterFact.addForeignColumn(SaisieVenteFactureSQLElement.MESSAGE_FIELD_NAME, msgT);
1321
            alterFact.addVarCharColumn(SaisieVenteFactureSQLElement.END2END_FIELD_NAME, 35);
1322
 
1323
            final AlterTable alterModeRegl = new AlterTable(root.getTable("MODE_REGLEMENT"));
1324
            alterModeRegl.addForeignColumn(null, mandateT);
1325
 
1326
            for (final String sql : ChangeTable.cat(Arrays.asList(alterFact, alterModeRegl))) {
1327
                root.getDBSystemRoot().getDataSource().execute(sql);
1328
            }
1329
            root.getSchema().updateVersion();
1330
            root.refetch(CollectionUtils.createSet(alterFact.getName(), alterModeRegl.getName()));
1331
            root.setMetadata(SDDMessageSQLElement.SERIAL_MD, "0");
156 ilm 1332
        } else {
1333
            // Before r24495 InstallationPanel.fixUnboundedVarchar() would change this field to
1334
            // varchar
1335
            final SQLField xmlField = root.getTable(SDDMessageSQLElement.TABLE_NAME).getField("XML");
1336
            if (xmlField.getTypeDecl().contains("char")) {
1337
                final AlterTable alterTable = new AlterTable(xmlField.getTable());
1338
                alterTable.alterColumn(xmlField.getName(), EnumSet.of(Properties.TYPE), alterTable.getSyntax().getTypeNames(Clob.class).iterator().next(), null, null);
1339
                root.getDBSystemRoot().getDataSource().execute(alterTable.asString());
1340
                root.getSchema().updateVersion();
1341
                xmlField.getTable().fetchFields();
1342
            }
151 ilm 1343
        }
1344
 
1345
        final SQLTable vcT = root.getTable("SAISIE_VENTE_COMPTOIR");
1346
        if (!vcT.contains("ID_COMPTE_PCE_PRODUIT")) {
1347
            final AlterTable alter = new AlterTable(vcT);
1348
            alter.addForeignColumn("ID_COMPTE_PCE_PRODUIT", root.getTable("COMPTE_PCE"));
1349
            alter.addForeignColumn("ID_COMPTE_PCE_SERVICE", root.getTable("COMPTE_PCE"));
1350
            exec(alter);
1351
        }
1352
 
156 ilm 1353
        // 1.6
1354
        // gestion depots des cheques
1355
        String chequeDepotTable = "DEPOT_CHEQUE";
1356
        if (!root.contains(chequeDepotTable)) {
1357
 
1358
            // Table depot cheque
1359
            final SQLCreateTable createTableDepotCheque = new SQLCreateTable(root, chequeDepotTable);
1360
            createTableDepotCheque.addVarCharColumn("NOM", 512);
1361
            createTableDepotCheque.addLongColumn("MONTANT", 0L, false);
1362
            createTableDepotCheque.addColumn("DATE", "date");
1363
            if (root.contains("BANQUE")) {
1364
                createTableDepotCheque.addForeignColumn("ID_BANQUE", root.getTable("BANQUE"));
1365
            } else if (root.contains("BANQUE_POLE_PRODUIT")) {
1366
                createTableDepotCheque.addForeignColumn("ID_BANQUE", root.getTable("BANQUE_POLE_PRODUIT"));
1367
            }
1368
            createTableDepotCheque.addForeignColumn("ID_ECRITURE", root.getTable("ECRITURE"));
1369
            createTableDepotCheque.addForeignColumn("ID_MOUVEMENT", root.getTable("MOUVEMENT"));
1370
            root.getBase().getDataSource().execute(createTableDepotCheque.asString());
1371
            InstallationPanel.insertUndef(createTableDepotCheque);
1372
            root.refetchTable(chequeDepotTable);
1373
            root.getSchema().updateVersion();
1374
 
1375
            // Table depot cheque element
1376
            final SQLCreateTable createTableDepotElt = new SQLCreateTable(root, chequeDepotTable + "_ELEMENT");
1377
            createTableDepotElt.addVarCharColumn("NUMERO", 128);
1378
            createTableDepotElt.addVarCharColumn("BANQUE", 128);
1379
            createTableDepotElt.addVarCharColumn("PIECE", 512);
1380
            createTableDepotElt.addLongColumn("MONTANT", 0L, false);
1381
            createTableDepotElt.addColumn("DATE", "date");
1382
            createTableDepotElt.addForeignColumn("ID_CHEQUE_A_ENCAISSER", root.getTable("CHEQUE_A_ENCAISSER"));
1383
            createTableDepotElt.addForeignColumn("ID_ECRITURE", root.getTable("ECRITURE"));
1384
            createTableDepotElt.addForeignColumn("ID_CLIENT", root.getTable("CLIENT"));
1385
            createTableDepotElt.addForeignColumn("ID_DEPOT_CHEQUE", root.getTable(chequeDepotTable));
1386
            root.getBase().getDataSource().execute(createTableDepotElt.asString());
1387
            InstallationPanel.insertUndef(createTableDepotElt);
1388
            root.refetchTable(chequeDepotTable + "_ELEMENT");
1389
            root.getSchema().updateVersion();
1390
        }
1391
 
1392
        final SQLTable articleT = root.getTable("ARTICLE");
1393
        if (!articleT.contains("QTE_UNITAIRE")) {
1394
            final AlterTable alter = new AlterTable(articleT);
1395
            alter.addDecimalColumn("QTE_UNITAIRE", 16, 6, BigDecimal.ONE, false);
1396
            exec(alter);
1397
        }
1398
 
1399
        final SQLTable articleFT = root.getTable("ARTICLE_FOURNISSEUR");
1400
        if (!articleFT.contains("QTE_UNITAIRE")) {
1401
            final AlterTable alter = new AlterTable(articleFT);
1402
            alter.addDecimalColumn("QTE_UNITAIRE", 16, 6, BigDecimal.ONE, false);
1403
            exec(alter);
1404
        }
1405
 
1406
        // Cout de revient
1407
        if (!root.contains("COUT_REVIENT")) {
1408
            final SQLCreateTable createTableCR = new SQLCreateTable(root, "COUT_REVIENT");
1409
            createTableCR.addVarCharColumn("CODE", 48);
1410
            createTableCR.addVarCharColumn("NOM", 256);
1411
            createTableCR.addDecimalColumn("POURCENT", 16, 6, BigDecimal.ZERO, false);
1412
            root.getBase().getDataSource().execute(createTableCR.asString());
1413
            InstallationPanel.insertUndef(createTableCR);
1414
            root.refetchTable("COUT_REVIENT");
1415
            root.getSchema().updateVersion();
1416
 
1417
            final AlterTable alterArticle = new AlterTable(root.getTable("ARTICLE"));
1418
            alterArticle.addForeignColumn("ID_COUT_REVIENT", root.findTable("COUT_REVIENT"));
1419
            exec(alterArticle);
1420
            root.refetchTable("ARTICLE");
1421
            root.getSchema().updateVersion();
1422
        }
1423
 
1424
        if (!root.getTable("ARTICLE").contains("MARGE_WITH_COUT_REVIENT")) {
1425
            final AlterTable alterArticle = new AlterTable(root.getTable("ARTICLE"));
1426
            alterArticle.addBooleanColumn("MARGE_WITH_COUT_REVIENT", Boolean.FALSE, false);
1427
            exec(alterArticle);
1428
            root.refetchTable("ARTICLE");
1429
            root.getSchema().updateVersion();
1430
        }
1431
 
1432
        if (!root.getTable("FACTURE_FOURNISSEUR").contains("NET_A_PAYER")) {
1433
            final AlterTable alterFactF = new AlterTable(root.getTable("FACTURE_FOURNISSEUR"));
1434
            alterFactF.addLongColumn("NET_A_PAYER", 0L, false);
1435
            alterFactF.addLongColumn("AVOIR_TTC", 0L, false);
1436
            exec(alterFactF);
1437
            root.refetchTable("FACTURE_FOURNISSEUR");
1438
            root.getSchema().updateVersion();
1439
        }
1440
 
1441
        // Frais Document
1442
        if (!root.contains("FRAIS_DOCUMENT")) {
1443
            final SQLCreateTable createTableCR = new SQLCreateTable(root, "FRAIS_DOCUMENT");
1444
            createTableCR.addVarCharColumn("CODE", 48);
1445
            createTableCR.addVarCharColumn("NOM", 256);
1446
            createTableCR.addLongColumn("MONTANT_HT", 0L, false);
1447
            createTableCR.addForeignColumn("ID_TAXE", root.getTable("TAXE"));
1448
            createTableCR.addLongColumn("MONTANT_TTC", 0L, false);
1449
            root.getBase().getDataSource().execute(createTableCR.asString());
1450
            InstallationPanel.insertUndef(createTableCR);
1451
            root.refetchTable("FRAIS_DOCUMENT");
1452
 
1453
            final AlterTable alterClient = new AlterTable(root.getTable("CLIENT"));
1454
            alterClient.addForeignColumn("ID_FRAIS_DOCUMENT", root.findTable("FRAIS_DOCUMENT"));
1455
            exec(alterClient);
1456
            root.refetchTable("CLIENT");
1457
 
1458
            List<String> tables = Arrays.asList("DEVIS", "COMMANDE_CLIENT", "BON_DE_LIVRAISON", "SAISIE_VENTE_FACTURE");
1459
            for (String tableToUp : tables) {
1460
                final AlterTable alter = new AlterTable(root.getTable(tableToUp));
1461
                alter.addLongColumn("FRAIS_DOCUMENT_HT", 0L, false);
1462
                alter.addForeignColumn("ID_TAXE_FRAIS_DOCUMENT", root.getTable("TAXE"));
1463
                exec(alter);
1464
                root.refetchTable(tableToUp);
1465
            }
1466
            root.getSchema().updateVersion();
1467
        }
1468
 
1469
        final SQLTable chequeAvoirT = root.getTable("CHEQUE_AVOIR_CLIENT");
1470
 
1471
        String tBanque = root.contains("BANQUE") ? "BANQUE" : "BANQUE_POLE_PRODUIT";
1472
        if (!chequeAvoirT.contains("ID_" + tBanque)) {
1473
 
1474
            final AlterTable alterChqAvoir = new AlterTable(chequeAvoirT);
1475
            alterChqAvoir.addForeignColumn("ID_" + tBanque, root.findTable(tBanque));
1476
            exec(alterChqAvoir);
1477
        }
1478
 
1479
        // Prelevement à la source
1480
        final SQLTable fichePayeT = root.getTable("FICHE_PAYE");
1481
        if (!fichePayeT.contains("ID_PAS")) {
1482
            final SQLCreateTable createTablePas = new SQLCreateTable(root, "PAS");
1483
            createTablePas.addForeignColumn("ID_TYPE_TAUX_PAS", root.findTable("TYPE_TAUX_PAS"));
1484
            createTablePas.addVarCharColumn("CODE_PAS", 256);
1485
            createTablePas.addDecimalColumn("TAUX_PAS", 16, 2, BigDecimal.ZERO, false);
1486
            root.getBase().getDataSource().execute(createTablePas.asString());
1487
            InstallationPanel.insertUndef(createTablePas);
1488
            root.refetchTable("PAS");
1489
            root.getSchema().updateVersion();
1490
 
1491
            final AlterTable alterFichePaye = new AlterTable(fichePayeT);
1492
            alterFichePaye.addForeignColumn("ID_PAS", root.findTable("PAS"));
1493
            alterFichePaye.addDecimalColumn("TOTAL_PAS", 16, 2, BigDecimal.ZERO, false);
1494
            alterFichePaye.addDecimalColumn("NET_AVANT_PAS", 16, 2, BigDecimal.ZERO, false);
1495
            exec(alterFichePaye);
1496
 
1497
            final SQLTable tableSal = root.getTable("SALARIE");
1498
            final AlterTable alterSalarie = new AlterTable(tableSal);
1499
            alterSalarie.addForeignColumn("ID_PAS", root.findTable("PAS"));
1500
            exec(alterSalarie);
1501
 
1502
            final AlterTable alterEtatCivil = new AlterTable(tableEtatCivil);
1503
            alterEtatCivil.addVarCharColumn("NTT", 50);
1504
            exec(alterEtatCivil);
1505
 
1506
            Map<String, SQLRow> typeTauxPasMap = new HashMap<>();
1507
            SQLSelect selTypeTaux = new SQLSelect();
1508
            selTypeTaux.addSelect(root.findTable("TYPE_TAUX_PAS").getKey());
1509
            selTypeTaux.addSelect(root.findTable("TYPE_TAUX_PAS").getField("CODE"));
1510
            List<SQLRow> resultTypeTaux = SQLRowListRSH.execute(selTypeTaux);
1511
            for (SQLRow sqlRow : resultTypeTaux) {
1512
                typeTauxPasMap.put(sqlRow.getString("CODE"), sqlRow);
1513
            }
1514
 
1515
            SQLSelect selSal = new SQLSelect();
1516
            selSal.addSelect(tableSal.getKey());
1517
            List<SQLRow> rowSal = SQLRowListRSH.execute(selSal);
1518
            for (SQLRow sqlRow : rowSal) {
1519
                SQLRowValues rowValsSal = sqlRow.createEmptyUpdateRow();
1520
                final SQLRowValues putRowValues = rowValsSal.putRowValues("ID_PAS");
1521
                putRowValues.put("ID_TYPE_TAUX_PAS", typeTauxPasMap.get("13").getID());
1522
                putRowValues.put("TAUX_PAS", BigDecimal.ZERO);
1523
                putRowValues.commit();
1524
            }
1525
        }
1526
 
1527
        if (!root.contains("ARTICLE_TARIF_FOURNISSEUR")) {
1528
            final SQLCreateTable createTable = new SQLCreateTable(root, "ARTICLE_TARIF_FOURNISSEUR");
1529
 
1530
            createTable.addForeignColumn("ARTICLE");
1531
            createTable.addForeignColumn("FOURNISSEUR");
1532
            createTable.addVarCharColumn("REF_FOURNISSEUR", 500);
1533
            createTable.addVarCharColumn("TYPE_REAPPRO", 256);
1534
            createTable.addVarCharColumn("CONDITIONS", 1024);
1535
            createTable.addVarCharColumn("ACHETEUR", 256);
1536
            createTable.addVarCharColumn("CODE_PAYS_ORIGINE", 256);
1537
            createTable.addDecimalColumn("PRIX_ACHAT_DEVISE_F", 16, 6, BigDecimal.valueOf(0), false);
1538
            createTable.addDecimalColumn("PRIX_ACHAT", 16, 6, BigDecimal.valueOf(0), false);
1539
            createTable.addIntegerColumn("QTE", 1);
1540
            createTable.addDateAndTimeColumn("DATE_PRIX");
1541
            // createTable.addDecimalColumn("COEF_TRANSPORT_PORT", 16, 6, BigDecimal.valueOf(0),
1542
            // false);
1543
            // createTable.addDecimalColumn("COEF_TAXE_D", 16, 6, BigDecimal.valueOf(0), false);
1544
            // createTable.addDecimalColumn("COEF_TRANSPORT_SIEGE", 16, 6, BigDecimal.valueOf(0),
1545
            // false);
1546
            // createTable.addDecimalColumn("COEF_FRAIS_MOULE", 16, 6, BigDecimal.valueOf(0),
1547
            // false);
1548
            // createTable.addDecimalColumn("COEF_FRAIS_INDIRECTS", 16, 6, BigDecimal.valueOf(0),
1549
            // false);
1550
            createTable.addIntegerColumn("DELAI_REAPPRO", 0);
1551
            createTable.addIntegerColumn("DELAI_TRANSPORT", 0);
1552
            createTable.addIntegerColumn("PRIORITE", 0);
1553
            createTable.addDecimalColumn("COEF_PRIX_MINI", 16, 2, BigDecimal.valueOf(0), false);
1554
            root.getBase().getDataSource().execute(createTable.asString());
1555
            InstallationPanel.insertUndef(createTable);
1556
            root.refetchTable("ARTICLE_TARIF_FOURNISSEUR");
1557
            root.getSchema().updateVersion();
1558
        }
1559
 
1560
        // Gestion des catègories comptables
1561
        if (!root.contains("CATEGORIE_COMPTABLE")) {
1562
            final SQLCreateTable createTable = new SQLCreateTable(root, "CATEGORIE_COMPTABLE");
1563
            createTable.addVarCharColumn("NOM", 128);
1564
            createTable.addForeignColumn("ID_TAXE_VENTE", root.getTable("TAXE"));
1565
            createTable.addForeignColumn("ID_TAXE_ACHAT", root.getTable("TAXE"));
1566
            createTable.addForeignColumn("ID_COMPTE_PCE_VENTE", root.getTable("COMPTE_PCE"));
1567
            createTable.addForeignColumn("ID_COMPTE_PCE_ACHAT", root.getTable("COMPTE_PCE"));
1568
 
1569
            try {
1570
                root.getBase().getDataSource().execute(createTable.asString());
1571
                InstallationPanel.insertUndef(createTable);
1572
                root.refetchTable("CATEGORIE_COMPTABLE");
1573
                root.getSchema().updateVersion();
1574
            } catch (SQLException ex) {
1575
                throw new IllegalStateException("Erreur lors de la création de la table " + "CATEGORIE_COMPTABLE", ex);
1576
            }
1577
 
1578
            AlterTable tableClientCat = new AlterTable(root.getTable("CLIENT"));
1579
            tableClientCat.addForeignColumn("ID_CATEGORIE_COMPTABLE", root.getTable("CATEGORIE_COMPTABLE"));
1580
            root.getBase().getDataSource().execute(tableClientCat.asString());
1581
            root.refetchTable("CLIENT");
1582
            root.getSchema().updateVersion();
1583
 
1584
            AlterTable tableF = new AlterTable(root.getTable("FOURNISSEUR"));
1585
            tableF.addForeignColumn("ID_CATEGORIE_COMPTABLE", root.getTable("CATEGORIE_COMPTABLE"));
1586
            root.getBase().getDataSource().execute(tableF.asString());
1587
            root.refetchTable("FOURNISSEUR");
1588
            root.getSchema().updateVersion();
1589
 
1590
            final SQLCreateTable createTableArtCat = new SQLCreateTable(root, "ARTICLE_CATEGORIE_COMPTABLE");
1591
            createTableArtCat.addForeignColumn("ID_CATEGORIE_COMPTABLE", root.getTable("CATEGORIE_COMPTABLE"));
1592
            createTableArtCat.addForeignColumn("ID_ARTICLE", root.getTable("ARTICLE"));
1593
            createTableArtCat.addForeignColumn("ID_TAXE_ACHAT", root.getTable("TAXE"));
1594
            createTableArtCat.addForeignColumn("ID_TAXE_VENTE", root.getTable("TAXE"));
1595
            createTableArtCat.addForeignColumn("ID_COMPTE_PCE_VENTE", root.getTable("COMPTE_PCE"));
1596
            createTableArtCat.addForeignColumn("ID_COMPTE_PCE_ACHAT", root.getTable("COMPTE_PCE"));
1597
 
1598
            try {
1599
                root.getBase().getDataSource().execute(createTableArtCat.asString());
1600
                InstallationPanel.insertUndef(createTableArtCat);
1601
                root.refetchTable("CATEGORIE_COMPTABLE");
1602
                root.getSchema().updateVersion();
1603
            } catch (SQLException ex) {
1604
                throw new IllegalStateException("Erreur lors de la création de la table " + "ARTICLE_CATEGORIE_COMPTABLE", ex);
1605
            }
1606
 
1607
        }
1608
 
1609
        // Article fournisseur secondaire
1610
        if (!root.contains("ARTICLE_FOURNISSEUR_SECONDAIRE")) {
1611
            final SQLCreateTable createTable = new SQLCreateTable(root, "ARTICLE_FOURNISSEUR_SECONDAIRE");
1612
            createTable.addForeignColumn("ARTICLE");
1613
            createTable.addForeignColumn("FOURNISSEUR");
1614
            try {
1615
                root.getBase().getDataSource().execute(createTable.asString());
1616
                InstallationPanel.insertUndef(createTable);
1617
                root.refetchTable("ARTICLE_FOURNISSEUR_SECONDAIRE");
1618
                root.getSchema().updateVersion();
1619
            } catch (SQLException ex) {
1620
                throw new IllegalStateException("Erreur lors de la création de la table ARTICLE_FOURNISSEUR_SECONDAIRE", ex);
1621
            }
1622
        }
1623
 
1624
        if (!root.getTable("ARTICLE").contains("AUTO_PRIX_ACHAT_NOMENCLATURE")) {
1625
            AlterTable t = new AlterTable(root.getTable("ARTICLE"));
1626
            t.addBooleanColumn("AUTO_PRIX_ACHAT_NOMENCLATURE", false, false);
1627
            root.getTable("ARTICLE").getBase().getDataSource().execute(t.asString());
1628
            root.refetchTable(root.getTable("ARTICLE").getName());
1629
            root.getSchema().updateVersion();
1630
        }
1631
 
1632
        // Gestion multidepot
1633
        {
1634
            // Ajout table depot
1635
            if (!root.contains("DEPOT_STOCK")) {
1636
                final SQLCreateTable createTable = new SQLCreateTable(root, "DEPOT_STOCK");
1637
                createTable.addVarCharColumn("CODE", 25);
1638
                createTable.addVarCharColumn("NOM", 256);
1639
                createTable.addVarCharColumn("UI_LOCK", 256);
1640
                createTable.addForeignColumn("ID_USER_UI_LOCK", root.findTable("USER_COMMON"));
1641
                // sqlRowValues.put("UI_LOCK", "ro");
1642
                // sqlRowValues.put("ID_USER_UI_LOCK", 2);
1643
 
1644
                try {
1645
                    root.getBase().getDataSource().execute(createTable.asString());
1646
                    InstallationPanel.insertUndef(createTable);
1647
 
1648
                    root.refetchTable("DEPOT_STOCK");
1649
                    root.getSchema().updateVersion();
1650
 
1651
                    SQLRowValues rowValsDefStock = new SQLRowValues(root.getTable("DEPOT_STOCK"));
1652
                    rowValsDefStock.put("NOM", "Principal");
1653
                    rowValsDefStock.put("UI_LOCK", "ro");
1654
                    rowValsDefStock.commit();
1655
                } catch (SQLException ex) {
1656
                    throw new IllegalStateException("Erreur lors de la création de la table " + "DEPOT_STOCK", ex);
1657
                }
1658
            }
1659
 
1660
            List<String> tableElementDepot = Arrays.asList("FACTURE_FOURNISSEUR_ELEMENT", "DEVIS_ELEMENT", "COMMANDE_ELEMENT", "BON_RECEPTION_ELEMENT", "COMMANDE_CLIENT_ELEMENT",
1661
                    "BON_DE_LIVRAISON_ELEMENT", "SAISIE_VENTE_FACTURE_ELEMENT", "AVOIR_CLIENT_ELEMENT", "DEMANDE_PRIX_ELEMENT");
1662
            for (String tableName : tableElementDepot) {
1663
                final SQLTable tableToAddDepot = root.getTable(tableName);
1664
                if (!tableToAddDepot.contains("ID_DEPOT_STOCK")) {
1665
                    AlterTable t = new AlterTable(tableToAddDepot);
1666
                    t.addForeignColumn("ID_DEPOT_STOCK", root.getTable("DEPOT_STOCK"));
1667
                    tableToAddDepot.getBase().getDataSource().execute(t.asString());
1668
                    root.refetchTable(tableToAddDepot.getName());
1669
                    root.getSchema().updateVersion();
1670
                }
1671
            }
1672
 
1673
            // Depot defaut
1674
            if (!root.getTable("ARTICLE").contains("ID_DEPOT_STOCK")) {
1675
                AlterTable t = new AlterTable(root.getTable("ARTICLE"));
1676
                t.addForeignColumn("ID_DEPOT_STOCK", root.getTable("DEPOT_STOCK"));
1677
                root.getTable("ARTICLE").getBase().getDataSource().execute(t.asString());
1678
                root.refetchTable(root.getTable("ARTICLE").getName());
1679
                root.getSchema().updateVersion();
1680
            }
1681
 
1682
            // Liason depot stock
1683
            if (!root.getTable("STOCK").contains("ID_DEPOT_STOCK")) {
1684
                AlterTable t = new AlterTable(root.getTable("STOCK"));
1685
                t.addForeignColumn("ID_DEPOT_STOCK", root.getTable("DEPOT_STOCK"));
1686
                t.addForeignColumn("ID_ARTICLE", root.getTable("ARTICLE"));
1687
                t.addColumn("QTE_MIN", "real DEFAULT 0");
1688
                root.getTable("STOCK").getBase().getDataSource().execute(t.asString());
1689
                root.refetchTable(root.getTable("STOCK").getName());
1690
                root.getSchema().updateVersion();
1691
            }
1692
 
1693
            // Ajout depot sur mvt
1694
            if (!root.getTable("MOUVEMENT_STOCK").contains("ID_STOCK")) {
1695
                AlterTable t = new AlterTable(root.getTable("MOUVEMENT_STOCK"));
1696
                t.addForeignColumn("ID_STOCK", root.getTable("STOCK"));
1697
                t.addIntegerColumn("SOURCE_ELEMENTID", 1);
1698
                root.getTable("MOUVEMENT_STOCK").getBase().getDataSource().execute(t.asString());
1699
                root.refetchTable(root.getTable("MOUVEMENT_STOCK").getName());
1700
                root.getSchema().updateVersion();
1701
 
1702
                // REQUETE update STOCK.ID_ARTICLE et QTE_MIN
1703
                SQLTable tableStock = root.getTable("STOCK");
1704
                UpdateBuilder buildStockArt = new UpdateBuilder(tableStock);
1705
                buildStockArt.addBackwardVirtualJoin(tableArticle, "ID_STOCK");
1706
                buildStockArt.setFromVirtualJoinField("ID_ARTICLE", tableArticle.getAlias(), "ID");
1707
                buildStockArt.setFromVirtualJoinField("QTE_MIN", tableArticle.getAlias(), "QTE_MIN");
1708
                buildStockArt.setWhere(new Where(tableStock.getKey(), "!=", 1));
1709
 
1710
                // REQUETE UPDATE MVT ID_STOCK
1711
                UpdateBuilder buildMvtStock = new UpdateBuilder(tableMvtStock);
1712
                buildMvtStock.addForwardVirtualJoin(tableArticle, "ID_ARTICLE");
1713
                buildMvtStock.setFromVirtualJoinField("ID_STOCK", tableArticle.getAlias(), "ID_STOCK");
1714
 
1715
                // REQUETE UPDATE STOCK ID_DEPOT_STOCK
1716
                UpdateBuilder buildStock = new UpdateBuilder(tableStock);
1717
                buildStock.setObject("ID_DEPOT_STOCK", DepotStockSQLElement.DEFAULT_ID);
1718
 
1719
                UpdateBuilder buildArticleDepot = new UpdateBuilder(tableArticle);
1720
                buildArticleDepot.setObject("ID_DEPOT_STOCK", DepotStockSQLElement.DEFAULT_ID);
1721
 
1722
                // REQUETE UPDATE ARTICLE_DEPOT_STOCK
1723
                // String req = "INSERT INTO " + root.getTable("ARTICLE_STOCK").getSQLName().quote()
1724
                // + " (" + root.getTable("ARTICLE_STOCK").getField("ID_ARTICLE").getQuotedName() +
1725
                // ",";
1726
                // req += root.getTable("ARTICLE_STOCK").getField("ID_STOCK").getQuotedName() + ", "
1727
                // + root.getTable("ARTICLE_STOCK").getField("QTE_MIN").getQuotedName() + ") SELECT
1728
                // "
1729
                // + root.getTable("ARTICLE").getKey().getQuotedName() + "," +
1730
                // root.getTable("ARTICLE").getField("ID_STOCK").getQuotedName() + ", "
1731
                // + root.getTable("ARTICLE").getField("QTE_MIN").getQuotedName();
1732
                // req += " FROM " + root.getTable("ARTICLE").getSQLName().quote();
1733
                // req += " WHERE " + root.getTable("ARTICLE").getKey().getQuotedName() + " > 1 AND
1734
                // " + root.getTable("ARTICLE").getField("ID_STOCK").getQuotedName() + " >1 ";
1735
                // req += " AND " + root.getTable("ARTICLE").getArchiveField().getQuotedName() + " =
1736
                // 0";
1737
 
1738
                tableStock.getDBSystemRoot().getDataSource().execute(buildArticleDepot.asString());
1739
                tableStock.getDBSystemRoot().getDataSource().execute(buildMvtStock.asString());
1740
                tableStock.getDBSystemRoot().getDataSource().execute(buildStock.asString());
1741
                tableStock.getDBSystemRoot().getDataSource().execute(buildStockArt.asString());
1742
            }
1743
 
1744
        }
1745
 
1746
        // Gestion Livraison depuis devis
1747
        SQLTable tableTrDevis = root.getTable("TR_DEVIS");
1748
        if (!tableTrDevis.contains("ID_BON_DE_LIVRAISON")) {
1749
            AlterTable t = new AlterTable(tableTrDevis);
1750
            t.addForeignColumn("ID_BON_DE_LIVRAISON", root.getTable("BON_DE_LIVRAISON"));
1751
            tableTrDevis.getBase().getDataSource().execute(t.asString());
1752
            root.refetchTable(tableTrDevis.getName());
1753
            root.getSchema().updateVersion();
1754
        }
1755
        SQLTable tableBLElt = root.getTable("BON_DE_LIVRAISON_ELEMENT");
1756
        if (!tableBLElt.contains("ID_DEVIS_ELEMENT")) {
1757
            AlterTable t = new AlterTable(tableBLElt);
1758
            t.addForeignColumn("ID_DEVIS_ELEMENT", root.getTable("DEVIS_ELEMENT"));
1759
            tableBLElt.getBase().getDataSource().execute(t.asString());
1760
            root.refetchTable(tableBLElt.getName());
1761
            root.getSchema().updateVersion();
1762
        }
1763
        SQLTable tableDevisElt = root.getTable("DEVIS_ELEMENT");
1764
        if (!tableDevisElt.contains("QTE_LIVREE")) {
1765
            AlterTable t = new AlterTable(tableDevisElt);
1766
            t.addBooleanColumn("LIVRE_FORCED", Boolean.FALSE, false);
1767
            t.addBooleanColumn("LIVRE", Boolean.FALSE, false);
1768
            t.addDecimalColumn("QTE_LIVREE", 16, 6, BigDecimal.ZERO, true);
1769
            tableDevisElt.getBase().getDataSource().execute(t.asString());
1770
            root.refetchTable(tableDevisElt.getName());
1771
            root.getSchema().updateVersion();
1772
 
1773
            // Move show devise pref in global
1774
            SQLSelect sel = new SQLSelect();
1775
            sel.addSelect(root.getTable("DEVISE_HISTORIQUE").getKey(), "COUNT");
1776
            Number n = (Number) root.getDBSystemRoot().getDataSource().executeScalar(sel.asString());
1777
            if (n.intValue() > 0) {
1778
                SQLPreferences prefs = new SQLPreferences(root);
1779
                prefs.putBoolean(AbstractVenteArticleItemTable.ARTICLE_SHOW_DEVISE, true);
1780
                try {
1781
                    prefs.sync();
1782
                } catch (BackingStoreException e) {
1783
                    // TODO Auto-generated catch block
1784
                    e.printStackTrace();
1785
                }
1786
            }
1787
        }
1788
 
1789
        SQLTable tablePosteAn = root.getTable("POSTE_ANALYTIQUE");
1790
        if (!tablePosteAn.contains("OBSOLETE")) {
1791
            AlterTable t = new AlterTable(tablePosteAn);
1792
            t.addBooleanColumn("OBSOLETE", Boolean.FALSE, false);
1793
            tablePosteAn.getBase().getDataSource().execute(t.asString());
1794
            root.refetchTable(tablePosteAn.getName());
1795
            root.getSchema().updateVersion();
1796
        }
1797
 
1798
        SQLTable tableCheque = root.getTable("CHEQUE_A_ENCAISSER");
1799
        if (!tableCheque.contains("ID_COMPTE_PCE_TIERS")) {
1800
            AlterTable t = new AlterTable(tableCheque);
1801
            t.addVarCharColumn("TIERS", 256);
1802
            t.addForeignColumn("ID_COMPTE_PCE_TIERS", root.getTable("COMPTE_PCE"));
1803
            tableCheque.getBase().getDataSource().execute(t.asString());
1804
            root.refetchTable(tableCheque.getName());
1805
 
1806
            SQLTable tableEnc = root.getTable("ENCAISSER_MONTANT");
1807
            AlterTable tEnc = new AlterTable(tableEnc);
1808
            tEnc.addVarCharColumn("TIERS", 256);
1809
            tEnc.addForeignColumn("ID_COMPTE_PCE_TIERS", root.getTable("COMPTE_PCE"));
1810
            tableEnc.getBase().getDataSource().execute(tEnc.asString());
1811
            root.refetchTable(tableEnc.getName());
1812
            root.getSchema().updateVersion();
1813
 
1814
            SQLTable tableEch = root.getTable("ECHEANCE_CLIENT");
1815
            AlterTable tEch = new AlterTable(tableEch);
1816
            tEch.addVarCharColumn("TIERS", 256);
1817
            tEch.addForeignColumn("ID_COMPTE_PCE_TIERS", root.getTable("COMPTE_PCE"));
1818
            tableEch.getBase().getDataSource().execute(tEch.asString());
1819
            root.refetchTable(tableEch.getName());
1820
            root.getSchema().updateVersion();
1821
 
1822
            SQLTable tableChq = root.getTable("DEPOT_CHEQUE_ELEMENT");
1823
            AlterTable tChq = new AlterTable(tableChq);
1824
            tChq.addVarCharColumn("TIERS", 256);
1825
            tableChq.getBase().getDataSource().execute(tChq.asString());
1826
            root.refetchTable(tableChq.getName());
1827
            root.getSchema().updateVersion();
1828
        }
1829
 
1830
        if (!tableEcr.contains("DATE_ECHEANCE")) {
1831
            AlterTable t = new AlterTable(tableEcr);
1832
            t.addColumn("DATE_ECHEANCE", "date");
1833
            tableEcr.getBase().getDataSource().execute(t.asString());
1834
            root.refetchTable(tableEcr.getName());
1835
            root.getSchema().updateVersion();
1836
        }
1837
 
1838
        if (!tableVarSal.contains("HEURE_ABS_CUMUL_VAL")) {
1839
 
1840
            final AlterTable alterB = new AlterTable(root.getTable("VARIABLE_SALARIE"));
1841
 
1842
            for (org.openconcerto.sql.model.SQLField sqlField : tableVarSal.getContentFields()) {
1843
 
1844
                String field = sqlField.getName();
1845
                if (!field.equalsIgnoreCase("ID_USER_COMMON_CREATE") && !field.equalsIgnoreCase("ID_USER_COMMON_MODIFY") && !field.equalsIgnoreCase("MODIFICATION_DATE")
1846
                        && !field.equalsIgnoreCase("CREATION_DATE") && !field.endsWith("_DEFAULT_VAL")) {
1847
                    alterB.addColumn(field + "_CUMUL_VAL", "real DEFAULT 0");
1848
                }
1849
            }
1850
 
1851
            root.getBase().getDataSource().execute(alterB.asString());
1852
            root.refetchTable("VARIABLE_SALARIE");
1853
            root.getSchema().updateVersion();
1854
        }
174 ilm 1855
        SQLTable tableBL = root.getTable("BON_DE_LIVRAISON");
1856
        if (!tableBL.contains("ID_COMMERCIAL")) {
1857
            final AlterTable alterB = new AlterTable(tableBL);
1858
            alterB.addForeignColumn("ID_COMMERCIAL", root.getTable("COMMERCIAL"));
1859
            root.getBase().getDataSource().execute(alterB.asString());
1860
            root.refetchTable("BON_DE_LIVRAISON");
1861
            root.getSchema().updateVersion();
1862
        }
156 ilm 1863
 
1864
        // fix stock
1865
        {
1866
            SQLTable tableStock = root.getTable("STOCK");
1867
 
1868
            // Doublon depot stock
1869
            SQLSelect sel = new SQLSelect();
1870
            sel.addSelectFunctionStar("COUNT");
1871
            sel.addSelect(tableStock.getField("ID_DEPOT_STOCK"));
1872
            sel.addSelect(tableStock.getField("ID_ARTICLE"));
1873
            sel.addGroupBy(tableStock.getField("ID_DEPOT_STOCK"));
1874
            sel.addGroupBy(tableStock.getField("ID_ARTICLE"));
1875
            sel.setHaving(Where.createRaw("COUNT(*)>1", Collections.emptyList()));
1876
            System.err.println(sel.asString());
1877
 
1878
            List<Object[]> resultStockDoublon = (List<Object[]>) tableStock.getDBSystemRoot().getDataSource().execute(sel.asString(), new ArrayListHandler());
1879
            for (Object[] objects : resultStockDoublon) {
1880
 
1881
                if (((Number) objects[2]).intValue() == 1) {
1882
 
1883
                    UpdateBuilder buildStockArt = new UpdateBuilder(tableStock);
1884
                    buildStockArt.addBackwardVirtualJoin(tableArticle, "ID_STOCK");
1885
                    buildStockArt.setFromVirtualJoinField("ID_ARTICLE", tableArticle.getAlias(), "ID");
1886
                    buildStockArt.setWhere(new Where(tableStock.getKey(), "!=", 1));
1887
                    tableStock.getDBSystemRoot().getDataSource().execute(buildStockArt.asString());
1888
                } else {
1889
                    SQLSelect selD = new SQLSelect();
1890
                    selD.addSelectStar(tableStock);
1891
                    Where w = new Where(tableStock.getField("ID_ARTICLE"), "=", objects[2]);
1892
                    w = w.and(new Where(tableStock.getField("ID_DEPOT_STOCK"), "=", objects[1]));
1893
                    selD.setWhere(w);
1894
                    List<SQLRow> badStock = SQLRowListRSH.execute(selD);
1895
                    if (badStock.size() > 1) {
1896
                        SQLRowValues stock = badStock.get(0).createUpdateRow();
1897
                        List<String> fieldsToMerge = Arrays.asList("QTE_REEL", "QTE_TH", "QTE_RECEPT_ATTENTE", "QTE_LIV_ATTENTE");
1898
                        List<Integer> listBadIds = new ArrayList<>();
1899
                        for (int i = 1; i < badStock.size(); i++) {
1900
                            SQLRow rowBad = badStock.get(i);
1901
                            listBadIds.add(rowBad.getID());
1902
                            for (String field : fieldsToMerge) {
1903
                                stock.put(field, stock.getFloat(field) + rowBad.getFloat(field));
1904
                            }
1905
                            rowBad.createEmptyUpdateRow().put("ARCHIVE", 1).commit();
1906
                        }
1907
                        List<SQLTable> tablesToMErge = Arrays.asList(root.getTable("MOUVEMENT_STOCK"), root.getTable("ARTICLE"));
1908
                        for (SQLTable mergeTable : tablesToMErge) {
1909
                            UpdateBuilder up = new UpdateBuilder(mergeTable);
1910
                            up.setObject("ID_STOCK", stock.getID());
1911
                            up.setWhere(new Where(mergeTable.getField("ID_STOCK"), listBadIds));
1912
                            mergeTable.getDBSystemRoot().getDataSource().execute(up.asString());
1913
                        }
1914
                        stock.commit();
1915
 
1916
                    }
1917
                }
1918
            }
1919
 
1920
            SQLSelect selArt = new SQLSelect();
1921
            selArt.addSelectStar(tableArt);
1922
            Where w = new Where(tableArt.getField("ID_STOCK"), "=", 1);
1923
            selArt.setWhere(w);
1924
            List<SQLRow> badStock = SQLRowListRSH.execute(selArt);
1925
            for (SQLRow sqlRow : badStock) {
1926
                initStock(sqlRow);
1927
            }
1928
        }
1929
 
174 ilm 1930
        // Tarification client par quantite
1931
        if (root.getTable("TARIF_ARTICLE_CLIENT") == null) {
1932
            final SQLCreateTable createTableQtyTarif = new SQLCreateTable(root, "TARIF_ARTICLE_CLIENT");
1933
            createTableQtyTarif.addForeignColumn("ID_ARTICLE", root.getTable("ARTICLE"));
1934
            createTableQtyTarif.addForeignColumn("ID_CLIENT", root.getTable("CLIENT"));
1935
            createTableQtyTarif.addDecimalColumn("QUANTITE", 16, 3, BigDecimal.ONE, false);
1936
            createTableQtyTarif.addDecimalColumn("POURCENT_REMISE", 16, 3, null, true);
1937
            // createTableQtyTarif.addDecimalColumn("PRIX_METRIQUE_VT_1", 16, 6, null, true);
1938
            try {
1939
                root.getBase().getDataSource().execute(createTableQtyTarif.asString());
1940
                InstallationPanel.insertUndef(createTableQtyTarif);
1941
                root.refetchTable("TARIF_ARTICLE_CLIENT");
1942
                root.getSchema().updateVersion();
1943
            } catch (SQLException ex) {
1944
                throw new IllegalStateException("Erreur lors de la création de la table " + "TARIF_QUANTITE", ex);
1945
            }
1946
        }
156 ilm 1947
 
174 ilm 1948
        SQLTable tableTrCmd = root.getTable("TR_COMMANDE");
1949
        if (!tableTrCmd.contains("ID_FACTURE_FOURNISSEUR")) {
1950
            AlterTable t = new AlterTable(tableTrCmd);
1951
            t.addForeignColumn("ID_FACTURE_FOURNISSEUR", root.getTable("FACTURE_FOURNISSEUR"));
1952
            tableTrCmd.getBase().getDataSource().execute(t.asString());
1953
            root.refetchTable(tableTrCmd.getName());
1954
            root.getSchema().updateVersion();
1955
        }
1956
 
1957
        SQLTable tableContrat = root.getTable("CONTRAT_SALARIE");
1958
        if (!tableContrat.contains("COMPLEMENT_PCS")) {
1959
            AlterTable t = new AlterTable(tableContrat);
1960
            t.addVarCharColumn("COMPLEMENT_PCS", 54);
1961
            tableContrat.getBase().getDataSource().execute(t.asString());
1962
            root.refetchTable(tableContrat.getName());
1963
            root.getSchema().updateVersion();
1964
        }
177 ilm 1965
        if (!tableContrat.contains("SPECTACLE_OBJET")) {
1966
            AlterTable t = new AlterTable(tableContrat);
1967
            t.addVarCharColumn("SPECTACLE_OBJET", 54);
1968
            tableContrat.getBase().getDataSource().execute(t.asString());
1969
            root.refetchTable(tableContrat.getName());
1970
            root.getSchema().updateVersion();
1971
        }
174 ilm 1972
 
177 ilm 1973
        if (!tableContrat.contains("SPECTACLE_OBJET")) {
1974
            AlterTable t = new AlterTable(tableContrat);
1975
            t.addVarCharColumn("SPECTACLE_OBJET", 54);
1976
            tableContrat.getBase().getDataSource().execute(t.asString());
1977
            root.refetchTable(tableContrat.getName());
1978
            root.getSchema().updateVersion();
1979
        }
1980
        if (!tableContrat.contains("SPECTACLE_JOUR_CONTRAT")) {
1981
            AlterTable t = new AlterTable(tableContrat);
1982
            t.addDecimalColumn("SPECTACLE_JOUR_CONTRAT", 16, 2, null, true);
1983
            tableContrat.getBase().getDataSource().execute(t.asString());
1984
            root.refetchTable(tableContrat.getName());
1985
            root.getSchema().updateVersion();
1986
        }
1987
 
174 ilm 1988
        List<String> tablesCatComptable = Arrays.asList("DEVIS", "COMMANDE_CLIENT", "BON_DE_LIVRAISON", "SAISIE_VENTE_FACTURE", "AVOIR_CLIENT");
1989
        for (String tableToUp : tablesCatComptable) {
1990
            final SQLTable tableCatComptToAdd = root.getTable(tableToUp);
1991
            if (!tableCatComptToAdd.contains("ID_CATEGORIE_COMPTABLE")) {
1992
                final AlterTable alter = new AlterTable(tableCatComptToAdd);
1993
                alter.addForeignColumn("ID_CATEGORIE_COMPTABLE", root.getTable("CATEGORIE_COMPTABLE"));
1994
                exec(alter);
1995
                root.refetchTable(tableToUp);
1996
                root.getSchema().updateVersion();
1997
            }
1998
        }
1999
 
2000
 
2001
        // Modèles pour les emails
2002
        if (!root.contains(EmailTemplateSQLElement.TABLE_NAME)) {
2003
            final SQLCreateTable createTable = new SQLCreateTable(root, EmailTemplateSQLElement.TABLE_NAME);
2004
            createTable.addVarCharColumn("NOM", 80);
2005
            createTable.addVarCharColumn("TITRE", 80);
2006
            createTable.addVarCharColumn("TEXTE", 4096);
2007
            createTable.addVarCharColumn("FORMAT_DATE", 20);
2008
            createTable.addBooleanColumn("PAR_DEFAUT", Boolean.FALSE, false);
2009
            try {
2010
                root.getBase().getDataSource().execute(createTable.asString());
2011
                InstallationPanel.insertUndef(createTable);
2012
                root.refetchTable(EmailTemplateSQLElement.TABLE_NAME);
2013
                root.getSchema().updateVersion();
2014
            } catch (SQLException ex) {
2015
                throw new IllegalStateException("Erreur lors de la création de la table " + EmailTemplateSQLElement.TABLE_NAME, ex);
2016
            }
2017
        }
2018
        // Force undefined policy to inDb
2019
        root.setMetadata(SQLTable.UNDEFINED_ID_POLICY, "inDB");
177 ilm 2020
        final Map<String, Number> mapTableNameUndefined = SQLTable.getUndefinedIDs(root.getSchema());
174 ilm 2021
        final Set<String> tables = root.getSchema().getTableNames();
2022
        for (String tName : tables) {
2023
            if (!mapTableNameUndefined.containsKey(tName)) {
2024
                System.err.println("Updater_1_5.update() adding undefined in db for " + tName);
2025
                SQLTable.setUndefID(root.getSchema(), tName, null);
2026
            }
2027
        }
2028
 
2029
        // Création de la table Modéle
2030
        if (!root.contains("CONTACT_SALARIE")) {
2031
 
2032
            SQLCreateTable createModele = new SQLCreateTable(root, "CONTACT_SALARIE");
2033
            createModele.addVarCharColumn("NOM", 256);
2034
            createModele.addVarCharColumn("PRENOM", 256);
2035
            createModele.addVarCharColumn("TEL_DIRECT", 256);
2036
            createModele.addVarCharColumn("TEL_MOBILE", 256);
2037
            createModele.addVarCharColumn("EMAIL", 256);
2038
            createModele.addVarCharColumn("FAX", 256);
2039
            createModele.addVarCharColumn("FONCTION", 256);
2040
            createModele.addVarCharColumn("TEL_PERSONEL", 256);
2041
            createModele.addVarCharColumn("TEL_STANDARD", 256);
2042
            createModele.addForeignColumn("ID_TITRE_PERSONNEL", root.findTable("TITRE_PERSONNEL"));
2043
            createModele.addForeignColumn("ID_SALARIE", root.findTable("SALARIE"));
2044
 
2045
            try {
2046
                root.getBase().getDataSource().execute(createModele.asString());
2047
                root.refetchTable("CONTACT_SALARIE");
2048
                SQLRowValues rowVals = new SQLRowValues(root.getTable("CONTACT_SALARIE"));
2049
                SQLRow rowInserted = rowVals.commit();
2050
                SQLTable.setUndefID(root.getSchema(), "CONTACT_SALARIE", rowInserted.getID());
2051
                tableDevis.getSchema().updateVersion();
2052
            } catch (SQLException ex) {
2053
                throw new IllegalStateException("Erreur lors de la création de la table MODELE", ex);
2054
            }
2055
        }
2056
 
2057
        final SQLTable tableRgltPaye = root.getTable("REGLEMENT_PAYE");
2058
        if (!tableRgltPaye.contains("IBAN")) {
2059
            final AlterTable alter = new AlterTable(tableRgltPaye);
2060
            alter.addVarCharColumn("IBAN", 128);
2061
            exec(alter);
2062
            root.refetchTable(tableRgltPaye.getName());
2063
            root.getSchema().updateVersion();
2064
        }
2065
        if (!tableRgltPaye.contains("BIC")) {
2066
            final AlterTable alter = new AlterTable(tableRgltPaye);
2067
            alter.addVarCharColumn("BIC", 40);
2068
            exec(alter);
2069
            root.refetchTable(tableRgltPaye.getName());
2070
            root.getSchema().updateVersion();
2071
        }
2072
        final SQLTable tableSalarie = root.getTable("SALARIE");
2073
        if (!tableSalarie.contains("ID_USER_COMMON")) {
2074
            final AlterTable alter = new AlterTable(tableSalarie);
2075
            alter.addForeignColumn("ID_USER_COMMON", root.findTable("USER_COMMON"));
2076
            exec(alter);
2077
            root.refetchTable(tableSalarie.getName());
2078
            root.getSchema().updateVersion();
2079
        }
2080
 
2081
        final SQLTable tableInfosSalarie = root.getTable("INFOS_SALARIE_PAYE");
2082
        if (!tableInfosSalarie.contains("DUREE_FORFAIT")) {
2083
            final AlterTable alter = new AlterTable(tableInfosSalarie);
2084
            alter.addColumn("DUREE_FORFAIT", "real");
2085
            exec(alter);
2086
            root.refetchTable(tableSalarie.getName());
2087
            root.getSchema().updateVersion();
2088
        }
177 ilm 2089
        boolean upDimensionArt = false;
174 ilm 2090
 
177 ilm 2091
        final AlterTable alterDimensionArt = new AlterTable(tableArt);
2092
        if (!tableArt.contains("LONGUEUR")) {
2093
            alterDimensionArt.addDecimalColumn("LONGUEUR", 16, 8, null, true);
2094
            upDimensionArt = true;
2095
        }
2096
        if (!tableArt.contains("LARGEUR")) {
2097
            alterDimensionArt.addDecimalColumn("LARGEUR", 16, 8, null, true);
2098
            upDimensionArt = true;
2099
        }
2100
        if (!tableArt.contains("HAUTEUR")) {
2101
            alterDimensionArt.addDecimalColumn("HAUTEUR", 16, 8, null, true);
2102
            upDimensionArt = true;
2103
        }
2104
        if (upDimensionArt) {
2105
            tableArt.getBase().getDataSource().execute(alterDimensionArt.asString());
2106
            tableArt.getSchema().updateVersion();
2107
            tableArt.fetchFields();
2108
        }
2109
 
2110
        List<String> tableElementWithTable = Arrays.asList("FACTURE_FOURNISSEUR_ELEMENT", "DEVIS_ELEMENT", "COMMANDE_ELEMENT", "BON_RECEPTION_ELEMENT", "COMMANDE_CLIENT_ELEMENT",
2111
                "BON_DE_LIVRAISON_ELEMENT", "SAISIE_VENTE_FACTURE_ELEMENT", "AVOIR_CLIENT_ELEMENT", "DEMANDE_PRIX_ELEMENT");
2112
        for (String tableName : tableElementWithTable) {
2113
            final SQLTable tableToAddDimension = root.getTable(tableName);
2114
            boolean upDimensionArtItem = false;
2115
 
2116
            final AlterTable alterDimensionArtItem = new AlterTable(tableToAddDimension);
2117
            if (!tableToAddDimension.contains("LONGUEUR")) {
2118
                alterDimensionArtItem.addDecimalColumn("LONGUEUR", 16, 8, null, true);
2119
                upDimensionArtItem = true;
2120
            }
2121
            if (!tableToAddDimension.contains("LARGEUR")) {
2122
                alterDimensionArtItem.addDecimalColumn("LARGEUR", 16, 8, null, true);
2123
                upDimensionArtItem = true;
2124
            }
2125
            if (!tableToAddDimension.contains("HAUTEUR")) {
2126
                alterDimensionArtItem.addDecimalColumn("HAUTEUR", 16, 8, null, true);
2127
                upDimensionArtItem = true;
2128
            }
2129
            if (upDimensionArtItem) {
2130
                tableToAddDimension.getBase().getDataSource().execute(alterDimensionArtItem.asString());
2131
                tableToAddDimension.getSchema().updateVersion();
2132
                tableToAddDimension.fetchFields();
2133
            }
2134
        }
2135
 
2136
        if (!tTva.contains("DEFAULT_ACHAT")) {
2137
            final AlterTable alterTaxe = new AlterTable(tTva);
2138
            alterTaxe.addBooleanColumn("DEFAULT_ACHAT", Boolean.FALSE, false);
2139
            tTva.getBase().getDataSource().execute(alterTaxe.asString());
2140
            tTva.getSchema().updateVersion();
2141
            tTva.fetchFields();
2142
        }
2143
        SQLTable tableTypeRglt = root.getTable("TYPE_REGLEMENT");
2144
        if (!tableTypeRglt.contains("SEPA")) {
2145
            final AlterTable alterTaxe = new AlterTable(tableTypeRglt);
2146
            alterTaxe.addBooleanColumn("SEPA", Boolean.FALSE, false);
2147
            tableTypeRglt.getBase().getDataSource().execute(alterTaxe.asString());
2148
            tableTypeRglt.getSchema().updateVersion();
2149
            tableTypeRglt.fetchFields();
2150
            UpdateBuilder upSEPA = new UpdateBuilder(tableTypeRglt);
2151
            upSEPA.setObject("SEPA", Boolean.TRUE);
2152
            upSEPA.setObject("ECHEANCE", Boolean.TRUE);
2153
            upSEPA.setWhere(new Where(tableTypeRglt.getField("NOM"), "=", "Prélèvement"));
2154
            tTva.getBase().getDataSource().execute(upSEPA.asString());
2155
        }
2156
 
2157
        SQLTable tableEch = root.getTable("ECHEANCE_CLIENT");
2158
        if (!tableEch.contains("ID_SEPA_MANDATE")) {
2159
 
2160
            final AlterTable alterEch = new AlterTable(tableEch);
2161
            alterEch.addForeignColumn("ID_SEPA_MANDATE", root.getTable("SEPA_MANDATE"));
2162
            alterEch.addForeignColumn("ID_SDD_MESSAGE", root.getTable(SDDMessageSQLElement.TABLE_NAME));
2163
            alterEch.addVarCharColumn("SDD_EndToEndId", 35);
2164
            alterEch.addBooleanColumn("FICHIER_CREE", Boolean.FALSE, false);
2165
            alterEch.addBooleanColumn("REJETER", Boolean.FALSE, false);
2166
            alterEch.addVarCharColumn("ETS", 256);
2167
            alterEch.addForeignColumn("ID_" + BanqueSQLElement.TABLENAME, root.getTable(BanqueSQLElement.TABLENAME));
2168
            tableEch.getBase().getDataSource().execute(alterEch.asString());
2169
            tableEch.getSchema().updateVersion();
2170
            tableEch.fetchFields();
2171
 
2172
        }
2173
        if (!tClient.contains("ID_SEPA_MANDATE_DEFAULT")) {
2174
            final AlterTable alterClient = new AlterTable(tClient);
2175
            alterClient.addForeignColumn("ID_SEPA_MANDATE_DEFAULT", root.getTable("SEPA_MANDATE"));
2176
            tClient.getBase().getDataSource().execute(alterClient.asString());
2177
            tClient.getSchema().updateVersion();
2178
            tClient.fetchFields();
2179
        }
2180
 
2181
        if (!tableArt.contains("AUTO_PRIX_MIN_VENTE_NOMENCLATURE")) {
2182
            final AlterTable alter = new AlterTable(tableArt);
2183
            alter.addBooleanColumn("AUTO_PRIX_MIN_VENTE_NOMENCLATURE", Boolean.FALSE, false);
2184
            tableArt.getBase().getDataSource().execute(alter.asString());
2185
            tableArt.getSchema().updateVersion();
2186
            tableArt.fetchFields();
2187
        }
2188
        if (!tableArt.contains("DERNIER_DATE_ACHAT")) {
2189
            final AlterTable alter = new AlterTable(tableArt);
2190
            alter.addColumn("DERNIER_DATE_ACHAT", "date");
2191
            tableArt.getBase().getDataSource().execute(alter.asString());
2192
            tableArt.getSchema().updateVersion();
2193
            tableArt.fetchFields();
2194
            ReferenceArticleSQLElement.updateDateAchat(tableArt, null);
2195
        }
2196
 
2197
        SQLTable tableFactF = root.getTable("FACTURE_FOURNISSEUR");
2198
        if (!tableFactF.contains("DATE_REGLEMENT")) {
2199
            final AlterTable alter = new AlterTable(tableFactF);
2200
            alter.addColumn("DATE_REGLEMENT", "date");
2201
            tableFactF.getBase().getDataSource().execute(alter.asString());
2202
            tableFactF.getSchema().updateVersion();
2203
            tableFactF.fetchFields();
2204
        }
2205
 
2206
        SQLTable tableEchF = root.getTable("ECHEANCE_FOURNISSEUR");
2207
        if (!tableEchF.contains("ID_FACTURE_FOURNISSEUR")) {
2208
            final AlterTable alter = new AlterTable(tableEchF);
2209
            alter.addForeignColumn("ID_FACTURE_FOURNISSEUR", tableFactF);
2210
            tableEchF.getBase().getDataSource().execute(alter.asString());
2211
            tableEchF.getSchema().updateVersion();
2212
            tableEchF.fetchFields();
2213
        }
2214
 
2215
        List<String> achatItems = Arrays.asList("DEMANDE_PRIX_ELEMENT", "COMMANDE_ELEMENT", "BON_RECEPTION_ELEMENT", "FACTURE_FOURNISSEUR_ELEMENT");
2216
        for (String string : achatItems) {
2217
            boolean alter = false;
2218
            SQLTable tableItems = root.getTable(string);
2219
            final AlterTable t = new AlterTable(tableItems);
2220
 
2221
            if (!tableItems.getFieldsName().contains("POIDS_COLIS_NET")) {
2222
                t.addColumn("POIDS_COLIS_NET", "numeric (16,8) DEFAULT 1");
2223
                alter = true;
2224
            }
2225
 
2226
            if (!tableItems.getFieldsName().contains("T_POIDS_COLIS_NET")) {
2227
                t.addColumn("T_POIDS_COLIS_NET", "numeric (16,8) DEFAULT 1");
2228
                alter = true;
2229
            }
2230
 
2231
            if (!tableItems.getFieldsName().contains("NB_COLIS")) {
2232
                t.addColumn("NB_COLIS", "integer DEFAULT 0");
2233
                alter = true;
2234
            }
2235
            if (alter) {
2236
                tableItems.getBase().getDataSource().execute(t.asString());
2237
                tableItems.getSchema().updateVersion();
2238
                tableItems.fetchFields();
2239
            }
2240
        }
2241
 
2242
        for (String tableName : tableElementWithTable) {
2243
            final SQLTable tableToAddTare = root.getTable(tableName);
2244
            boolean upTareArtItem = false;
2245
 
2246
            final AlterTable alterTareArtItem = new AlterTable(tableToAddTare);
2247
            if (!tableToAddTare.contains("TARE")) {
2248
                alterTareArtItem.addDecimalColumn("TARE", 16, 8, null, true);
2249
                alterTareArtItem.alterColumn("POIDS_COLIS_NET", EnumSet.allOf(Properties.class), "numeric(16,8)", "0", true);
2250
                alterTareArtItem.alterColumn("T_POIDS_COLIS_NET", EnumSet.allOf(Properties.class), "numeric(16,8)", "0", true);
2251
 
2252
                alterTareArtItem.addDecimalColumn("T_POIDS_BRUT", 16, 8, BigDecimal.ZERO, true);
2253
 
2254
                upTareArtItem = true;
2255
            }
2256
 
2257
            if (upTareArtItem) {
2258
                tableToAddTare.getBase().getDataSource().execute(alterTareArtItem.asString());
2259
                tableToAddTare.getSchema().updateVersion();
2260
                tableToAddTare.fetchFields();
2261
 
2262
                int id = tableToAddTare.getUndefinedID();
2263
                if (id != SQLRow.NONEXISTANT_ID) {
2264
                    UpdateBuilder build = new UpdateBuilder(tableToAddTare);
2265
                    build.setObject("POIDS_COLIS_NET", BigDecimal.ZERO);
2266
                    build.setWhere(new Where(tableToAddTare.getKey(), "=", id));
2267
                    tableToAddTare.getDBSystemRoot().getDataSource().execute(build.asString());
2268
                }
2269
            }
2270
        }
2271
 
2272
        // Fix nb char MOUVEMENT_STOCK.NOM
2273
        if (tableMvtStock.getField("NOM").getType().getSize() == 45) {
2274
            AlterTable alterMvt = new AlterTable(tableMvtStock);
2275
            alterMvt.alterColumn("NOM", EnumSet.allOf(Properties.class), "varchar(512)", "''", false);
2276
            tableMvtStock.getDBSystemRoot().getDataSource().execute(alterMvt.asString());
2277
            tableMvtStock.getSchema().updateVersion();
2278
        }
2279
 
2280
        SQLTable tableEtatStock = root.getTable("ETAT_STOCK");
2281
        SQLTable tableDepotStock = root.getTable("DEPOT_STOCK");
2282
        if (!tableEtatStock.contains("ID_DEPOT_STOCK")) {
2283
            final AlterTable alter = new AlterTable(tableEtatStock);
2284
            alter.addForeignColumn("ID_DEPOT_STOCK", tableDepotStock);
2285
            tableEtatStock.getBase().getDataSource().execute(alter.asString());
2286
            tableEtatStock.getSchema().updateVersion();
2287
            tableEtatStock.fetchFields();
2288
        }
142 ilm 2289
    }
2290
 
174 ilm 2291
    public static void initStock(SQLRow rowArticle, int idDepot) {
156 ilm 2292
 
2293
        SQLSelect selStock = new SQLSelect();
174 ilm 2294
        selStock.addSelectStar(rowArticle.getTable().getTable("STOCK"));
2295
        selStock.setWhere(new Where(rowArticle.getTable().getTable("STOCK").getField("ID_ARTICLE"), "=", rowArticle.getID()));
156 ilm 2296
        List<SQLRow> rowsStock = SQLRowListRSH.execute(selStock);
2297
        Map<Integer, SQLRow> initedDepot = new HashMap<>();
2298
        for (SQLRow sqlRow : rowsStock) {
2299
            initedDepot.put(sqlRow.getForeignID("ID_DEPOT_STOCK"), sqlRow);
2300
        }
2301
 
2302
        List<StockItem> stockItems = new ArrayList<StockItem>();
174 ilm 2303
        try {
2304
            if (!initedDepot.keySet().contains(idDepot)) {
2305
                SQLRowValues rowVals = new SQLRowValues(rowArticle.getTable().getTable("STOCK"));
2306
                rowVals.put("ID_ARTICLE", rowArticle.getID());
2307
                rowVals.put("ID_DEPOT_STOCK", idDepot);
156 ilm 2308
 
174 ilm 2309
                SQLRow rowStock = rowVals.commit();
2310
                if ((rowArticle.getObject("ID_DEPOT_STOCK") == null || rowArticle.isForeignEmpty("ID_DEPOT_STOCK")) && idDepot == DepotStockSQLElement.DEFAULT_ID) {
2311
                    rowArticle.createEmptyUpdateRow().put("ID_STOCK", rowStock.getID()).put("ID_DEPOT_STOCK", DepotStockSQLElement.DEFAULT_ID).commit();
2312
                } else if (idDepot == rowArticle.getForeignID("ID_DEPOT_STOCK")) {
2313
                    rowArticle.createEmptyUpdateRow().put("ID_STOCK", rowStock.getID()).commit();
2314
                }
2315
                stockItems.add(new StockItem(rowArticle, rowStock));
156 ilm 2316
 
174 ilm 2317
            } else {
2318
                SQLRow rowExisting = initedDepot.get(idDepot);
2319
                if ((rowArticle.getObject("ID_DEPOT_STOCK") == null || rowArticle.isForeignEmpty("ID_DEPOT_STOCK")) && idDepot == DepotStockSQLElement.DEFAULT_ID) {
2320
                    rowArticle.createEmptyUpdateRow().put("ID_STOCK", rowExisting.getID()).put("ID_DEPOT_STOCK", DepotStockSQLElement.DEFAULT_ID).commit();
2321
                } else if (idDepot == rowArticle.getForeignID("ID_DEPOT_STOCK")) {
2322
                    rowArticle.createEmptyUpdateRow().put("ID_STOCK", rowExisting.getID()).commit();
156 ilm 2323
                }
174 ilm 2324
                stockItems.add(new StockItem(rowArticle, rowExisting));
2325
            }
2326
        } catch (SQLException e) {
2327
            ExceptionHandler.handle("Erreur lors de l'initialisation du stock de l'article", e);
2328
        }
2329
 
2330
        if (rowArticle.getReferentRows(rowArticle.getTable().getTable("ARTICLE_ELEMENT").getField("ID_ARTICLE_PARENT")).size() > 0) {
2331
            ComposedItemStockUpdater up = new ComposedItemStockUpdater(rowArticle.getTable().getDBRoot(), stockItems);
2332
            try {
2333
                up.updateNomenclature(stockItems);
156 ilm 2334
            } catch (SQLException e) {
174 ilm 2335
                ExceptionHandler.handle("Erreur lors de l'actualisation du stock!", e);
156 ilm 2336
            }
2337
        }
2338
    }
2339
 
174 ilm 2340
    public static void initStock(SQLRow row) {
2341
        int foreignID = DepotStockSQLElement.DEFAULT_ID;
2342
        if (row.getObject("ID_DEPOT_STOCK") != null && !row.isForeignEmpty("ID_DEPOT_STOCK")) {
2343
            foreignID = row.getForeignID("ID_DEPOT_STOCK");
2344
        }
2345
        initStock(row, foreignID);
2346
    }
2347
 
151 ilm 2348
    public static void exec(final AlterTable alter) throws SQLException {
2349
        alter.getTable().getDBSystemRoot().getDataSource().execute(alter.asString());
2350
        alter.getTable().getSchema().updateVersion();
2351
        alter.getTable().fetchFields();
2352
    }
142 ilm 2353
}