OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

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

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