OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

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

Rev Author Line No. Line
104 ilm 1
package org.openconcerto.modules.finance.accounting.exchangerates;
2
 
147 ilm 3
import java.awt.GraphicsEnvironment;
104 ilm 4
import java.io.IOException;
5
import java.math.BigDecimal;
6
import java.sql.ResultSet;
7
import java.sql.SQLException;
8
import java.util.ArrayList;
9
import java.util.Arrays;
10
import java.util.Calendar;
11
import java.util.Date;
12
import java.util.HashSet;
13
import java.util.List;
14
import java.util.Set;
15
import java.util.TimeZone;
16
 
17
import javax.swing.JOptionPane;
18
 
19
import org.apache.commons.dbutils.ResultSetHandler;
20
import org.openconcerto.erp.config.ComptaPropsConfiguration;
21
import org.openconcerto.erp.core.finance.accounting.model.CurrencyConverter;
22
import org.openconcerto.erp.modules.AbstractModule;
23
import org.openconcerto.erp.modules.DBContext;
24
import org.openconcerto.erp.modules.ModuleFactory;
25
import org.openconcerto.erp.modules.ModulePreferencePanel;
26
import org.openconcerto.erp.modules.ModulePreferencePanelDesc;
27
import org.openconcerto.sql.model.DBRoot;
28
import org.openconcerto.sql.model.SQLBase;
29
import org.openconcerto.sql.model.SQLRow;
30
import org.openconcerto.sql.model.SQLRowListRSH;
31
import org.openconcerto.sql.model.SQLSelect;
32
import org.openconcerto.sql.model.SQLTable;
33
import org.openconcerto.sql.model.Where;
34
import org.openconcerto.sql.utils.SQLUtils;
35
import org.openconcerto.utils.PrefType;
36
 
37
public final class Module extends AbstractModule {
38
 
39
    public static final String TABLE_EXPENSE = "EXPENSE";
40
    public static final String TABLE_EXPENSE_STATE = "EXPENSE_STATE";
41
    public static final String EXPENSE_RATE_NOT_DOWNLOAD_PREF = "EXPENSE_RATE_NOT_DOWNLOAD_PREF";
42
    private CurrencyConverter commercialConverter = null;
43
 
44
    public Module(ModuleFactory f) throws IOException {
45
        super(f);
46
    }
47
 
48
    @Override
49
    protected void install(DBContext ctxt) {
50
 
51
    }
52
 
53
    public void setCommercialConverter(CurrencyConverter commercialConverter) {
54
        this.commercialConverter = commercialConverter;
55
    }
56
 
57
    @Override
58
    protected void start() {
59
        final DBRoot root = ComptaPropsConfiguration.getInstanceCompta().getRootSociete();
164 ilm 60
        Boolean b = getFactory().getSQLPreferences(root).getBoolean(EXPENSE_RATE_NOT_DOWNLOAD_PREF, false);
104 ilm 61
        if (!b) {
62
            final Thread t = new Thread() {
63
                public void run() {
64
                    // Wait 1s to prevent too many internet access at startup
65
                    try {
66
                        Thread.sleep(1000);
67
                    } catch (InterruptedException e1) {
68
                        e1.printStackTrace();
69
                    }
70
                    updateRates(root, ComptaPropsConfiguration.getInstanceCompta().getRowSociete().getForeign("ID_DEVISE").getString("CODE"));
71
                };
72
            };
73
            t.setPriority(Thread.MIN_PRIORITY);
74
            t.setDaemon(true);
75
            t.start();
76
        }
77
 
78
    }
79
 
80
    public void updateRates(DBRoot root, String companyCurrencyCode) {
81
        // Check if update need to be done
82
        final Calendar c = Calendar.getInstance();
83
        c.set(Calendar.HOUR_OF_DAY, 0);
84
        c.set(Calendar.MINUTE, 0);
85
        c.set(Calendar.SECOND, 0);
86
        c.set(Calendar.MILLISECOND, 0);
87
 
88
        if (c.get(Calendar.DAY_OF_WEEK) == Calendar.MONDAY) {
89
            c.set(Calendar.HOUR, -72);
90
        } else {
91
            c.add(Calendar.HOUR, -24);
92
        }
93
        final Date yesterday = c.getTime();
94
 
95
        final CurrencyConverter converter = new CurrencyConverter(root, companyCurrencyCode, "EUR");
96
        final SQLSelect select = new SQLSelect();
97
        final SQLTable t = root.getTable("DEVISE_HISTORIQUE");
98
        select.addAllSelect(t, Arrays.asList("DATE", "SRC", "ID"));
99
        Where w = new Where(t.getField("SRC"), "=", "EUR");
100
        w = w.and(new Where(t.getField("DATE"), "=", yesterday));
101
        select.setWhere(w);
102
        select.setLimit(1);
103
        final List<SQLRow> rows = SQLRowListRSH.execute(select);
104
        if (!rows.isEmpty()) {
105
            // Nothing to update
106
            System.out.println("No exchange rates to download");
107
            return;
108
        }
109
        // Get conversion info
110
        final ExchangeRatesDownloader d = new ExchangeRatesDownloader();
111
        try {
112
            d.downloadAndParse();
113
            Set<Date> dates = getMissingDates(converter, root);
114
            System.out.println("Missing date:" + dates);
115
            List<Report> reports = d.getReports();
116
 
117
            for (Report report : reports) {
118
                if (dates.contains(report.getDate())) {
119
                    importReport(d, report, converter, root);
120
                }
121
            }
122
        } catch (Exception e) {
123
            e.printStackTrace();
147 ilm 124
            if (!GraphicsEnvironment.isHeadless()) {
125
                JOptionPane.showMessageDialog(null, "Impossible de mettre à jour les taux de change");
126
            }
104 ilm 127
        }
128
    }
129
 
130
    @Override
164 ilm 131
    public List<ModulePreferencePanelDesc> getPrefDescriptors(final DBRoot root) {
104 ilm 132
        return Arrays.<ModulePreferencePanelDesc> asList(new ModulePreferencePanelDesc("Gestion des devises") {
133
            @Override
134
            protected ModulePreferencePanel createPanel() {
164 ilm 135
                return new ModulePreferencePanel(root, "Gestion des devises") {
104 ilm 136
                    @Override
137
                    protected void addViews() {
138
                        final SQLPrefView<Boolean> view = new SQLPrefView<Boolean>(PrefType.BOOLEAN_TYPE, "Ne pas télécharger les nouveaux taux au lancement du logiciel",
139
                                EXPENSE_RATE_NOT_DOWNLOAD_PREF);
140
                        this.addView(view);
141
                    }
142
                };
143
            }
144
        }.setLocal(false).setKeywords("devise"));
145
    }
146
 
147
    protected void importReport(ExchangeRatesDownloader d, Report report, CurrencyConverter converter, DBRoot root) {
148
 
149
        System.out.println("Module.importReport() " + report.getDate());
150
 
151
        SQLTable tDeviseHisto = root.getTable("DEVISE_HISTORIQUE");
152
 
153
        final List<String> queries = new ArrayList<String>();
154
        final List<ResultSetHandler> handlers = new ArrayList<ResultSetHandler>();
155
        final ResultSetHandler handler = new ResultSetHandler() {
156
 
157
            @Override
158
            public Object handle(ResultSet rs) throws SQLException {
159
                if (rs.next()) {
160
                    return rs.getObject(1);
161
                }
162
                return null;
163
            }
164
        };
165
        Date date = Calendar.getInstance().getTime();
166
        final int size = ExchangeRatesDownloader.supportedCurrencyCodes.size();
167
        for (int i = 0; i < size; i++) {
168
            String code = ExchangeRatesDownloader.supportedCurrencyCodes.get(i);
169
            BigDecimal tauxCommercial;
170
            if (this.commercialConverter != null) {
171
                tauxCommercial = commercialConverter.convert(BigDecimal.ONE, report.getMainCurrencyCode(), code, date, true);
172
            } else {
147 ilm 173
                try {
174
                    tauxCommercial = converter.convert(BigDecimal.ONE, report.getMainCurrencyCode(), code, date, true);
175
                } catch (IllegalStateException e) {
176
                    // Aucun taux n'existe encore dans la base.
177
                    tauxCommercial = null;
178
                }
104 ilm 179
            }
180
            if (tauxCommercial == null) {
181
                tauxCommercial = BigDecimal.ONE;
182
            }
183
            tauxCommercial = tauxCommercial.setScale(4, BigDecimal.ROUND_HALF_UP);
184
            String query = "INSERT INTO " + tDeviseHisto.getSQLName().quote();
185
            final BigDecimal rate = report.getRate(code);
186
            if (rate == null) {
187
                System.err.println("RATE " + code + " NOT Supported");
188
                // Thread.dumpStack();
189
            } else {
190
                final BigDecimal taux = rate.setScale(4, BigDecimal.ROUND_HALF_UP);
191
 
192
                final java.sql.Date date2 = new java.sql.Date(report.getDate().getTime());
193
                query += " (\"DATE\", \"SRC\", \"DST\", \"TAUX\", \"TAUX_COMMERCIAL\", \"ORDRE\") select " + SQLBase.quoteStringStd(date2.toString()) + ", "
194
                        + SQLBase.quoteStringStd(report.getMainCurrencyCode()) + ", " + SQLBase.quoteStringStd(code) + ", " + taux + ", " + tauxCommercial + ", COALESCE(MAX(\"ORDRE\"), 0) + 1 ";
195
                query += "FROM " + tDeviseHisto.getSQLName().quote() + " RETURNING \"ID\"";
196
 
197
                queries.add(query);
198
                handlers.add(handler);
199
            }
200
        }
201
 
202
        try {
203
            SQLUtils.executeMultiple(tDeviseHisto.getDBSystemRoot(), queries, handlers);
204
 
205
        } catch (Exception e) {
206
            e.printStackTrace();
207
        }
208
 
209
    }
210
 
211
    protected Set<Date> getMissingDates(CurrencyConverter converter, DBRoot root) {
212
        final SQLSelect select = new SQLSelect();
213
 
214
        final SQLTable t = root.getTable("DEVISE_HISTORIQUE");
215
        select.addAllSelect(t, Arrays.asList("DATE"));
216
        Where w = new Where(t.getField("SRC"), "=", converter.getCompanyCurrencyCode());
217
        w = w.and(new Where(t.getField("DATE"), "<", new Date(System.currentTimeMillis())));
218
        select.setWhere(w);
219
        select.addGroupBy(t.getField("DATE"));
220
        select.setLimit(30);
221
        final List<Date> dates = t.getDBSystemRoot().getDataSource().executeCol(select.asString());
222
 
223
        final Set<Date> result = new HashSet<Date>();
224
        Calendar c = Calendar.getInstance(TimeZone.getTimeZone("UTC"));
225
        c.set(Calendar.HOUR_OF_DAY, 0);
226
        c.set(Calendar.MINUTE, 0);
227
        c.set(Calendar.SECOND, 0);
228
        c.set(Calendar.MILLISECOND, 0);
229
        for (int i = 0; i < 20; i++) {
230
            c.add(Calendar.HOUR_OF_DAY, -24);
231
            final Date time = c.getTime();
232
            System.out.println("::" + time);
233
            result.add(time);
234
        }
235
 
236
        for (Date date : dates) {
237
            Calendar c2 = Calendar.getInstance();
238
            c2.setTime(date);
239
            c = Calendar.getInstance(TimeZone.getTimeZone("UTC"));
240
            c.set(Calendar.YEAR, c2.get(Calendar.YEAR));
241
            c.set(Calendar.DAY_OF_YEAR, c2.get(Calendar.DAY_OF_YEAR));
242
            c.set(Calendar.HOUR_OF_DAY, 0);
243
            c.set(Calendar.MINUTE, 0);
244
            c.set(Calendar.SECOND, 0);
245
            c.set(Calendar.MILLISECOND, 0);
246
            Date dd = c.getTime();
247
            result.remove(dd);
248
            System.out.println("--" + dd);
249
        }
250
        System.out.println(result.size());
251
 
252
        return result;
253
    }
254
 
255
    @Override
256
    protected void stop() {
257
    }
258
 
259
}