OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

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

package org.openconcerto.modules.finance.accounting.exchangerates;

import java.awt.GraphicsEnvironment;
import java.io.IOException;
import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Calendar;
import java.util.Date;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
import java.util.TimeZone;

import javax.swing.JOptionPane;

import org.apache.commons.dbutils.ResultSetHandler;
import org.openconcerto.erp.config.ComptaPropsConfiguration;
import org.openconcerto.erp.core.finance.accounting.model.CurrencyConverter;
import org.openconcerto.erp.modules.AbstractModule;
import org.openconcerto.erp.modules.DBContext;
import org.openconcerto.erp.modules.ModuleFactory;
import org.openconcerto.erp.modules.ModulePreferencePanel;
import org.openconcerto.erp.modules.ModulePreferencePanelDesc;
import org.openconcerto.sql.model.DBRoot;
import org.openconcerto.sql.model.SQLBase;
import org.openconcerto.sql.model.SQLRow;
import org.openconcerto.sql.model.SQLRowListRSH;
import org.openconcerto.sql.model.SQLSelect;
import org.openconcerto.sql.model.SQLTable;
import org.openconcerto.sql.model.Where;
import org.openconcerto.sql.utils.SQLUtils;
import org.openconcerto.utils.PrefType;

public final class Module extends AbstractModule {

    public static final String TABLE_EXPENSE = "EXPENSE";
    public static final String TABLE_EXPENSE_STATE = "EXPENSE_STATE";
    public static final String EXPENSE_RATE_NOT_DOWNLOAD_PREF = "EXPENSE_RATE_NOT_DOWNLOAD_PREF";
    private CurrencyConverter commercialConverter = null;

    public Module(ModuleFactory f) throws IOException {
        super(f);
    }

    @Override
    protected void install(DBContext ctxt) {

    }

    public void setCommercialConverter(CurrencyConverter commercialConverter) {
        this.commercialConverter = commercialConverter;
    }

    @Override
    protected void start() {
        final DBRoot root = ComptaPropsConfiguration.getInstanceCompta().getRootSociete();
        Boolean b = getFactory().getSQLPreferences(root).getBoolean(EXPENSE_RATE_NOT_DOWNLOAD_PREF, false);
        if (!b) {
            final Thread t = new Thread() {
                public void run() {
                    // Wait 1s to prevent too many internet access at startup
                    try {
                        Thread.sleep(1000);
                    } catch (InterruptedException e1) {
                        e1.printStackTrace();
                    }
                    updateRates(root, ComptaPropsConfiguration.getInstanceCompta().getRowSociete().getForeign("ID_DEVISE").getString("CODE"));
                };
            };
            t.setPriority(Thread.MIN_PRIORITY);
            t.setDaemon(true);
            t.start();
        }

    }

    public void updateRates(DBRoot root, String companyCurrencyCode) {
        // Check if update need to be done
        final Calendar c = Calendar.getInstance();
        c.set(Calendar.HOUR_OF_DAY, 0);
        c.set(Calendar.MINUTE, 0);
        c.set(Calendar.SECOND, 0);
        c.set(Calendar.MILLISECOND, 0);

        if (c.get(Calendar.DAY_OF_WEEK) == Calendar.MONDAY) {
            c.set(Calendar.HOUR, -72);
        } else {
            c.add(Calendar.HOUR, -24);
        }
        final Date yesterday = c.getTime();

        final CurrencyConverter converter = new CurrencyConverter(root, companyCurrencyCode, "EUR");
        final SQLSelect select = new SQLSelect();
        final SQLTable t = root.getTable("DEVISE_HISTORIQUE");
        select.addAllSelect(t, Arrays.asList("DATE", "SRC", "ID"));
        Where w = new Where(t.getField("SRC"), "=", "EUR");
        w = w.and(new Where(t.getField("DATE"), "=", yesterday));
        select.setWhere(w);
        select.setLimit(1);
        final List<SQLRow> rows = SQLRowListRSH.execute(select);
        if (!rows.isEmpty()) {
            // Nothing to update
            System.out.println("No exchange rates to download");
            return;
        }
        // Get conversion info
        final ExchangeRatesDownloader d = new ExchangeRatesDownloader();
        try {
            d.downloadAndParse();
            Set<Date> dates = getMissingDates(converter, root);
            System.out.println("Missing date:" + dates);
            List<Report> reports = d.getReports();

            for (Report report : reports) {
                if (dates.contains(report.getDate())) {
                    importReport(d, report, converter, root);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            if (!GraphicsEnvironment.isHeadless()) {
                JOptionPane.showMessageDialog(null, "Impossible de mettre à jour les taux de change");
            }
        }
    }

    @Override
    public List<ModulePreferencePanelDesc> getPrefDescriptors(final DBRoot root) {
        return Arrays.<ModulePreferencePanelDesc> asList(new ModulePreferencePanelDesc("Gestion des devises") {
            @Override
            protected ModulePreferencePanel createPanel() {
                return new ModulePreferencePanel(root, "Gestion des devises") {
                    @Override
                    protected void addViews() {
                        final SQLPrefView<Boolean> view = new SQLPrefView<Boolean>(PrefType.BOOLEAN_TYPE, "Ne pas télécharger les nouveaux taux au lancement du logiciel",
                                EXPENSE_RATE_NOT_DOWNLOAD_PREF);
                        this.addView(view);
                    }
                };
            }
        }.setLocal(false).setKeywords("devise"));
    }

    protected void importReport(ExchangeRatesDownloader d, Report report, CurrencyConverter converter, DBRoot root) {

        System.out.println("Module.importReport() " + report.getDate());

        SQLTable tDeviseHisto = root.getTable("DEVISE_HISTORIQUE");

        final List<String> queries = new ArrayList<String>();
        final List<ResultSetHandler> handlers = new ArrayList<ResultSetHandler>();
        final ResultSetHandler handler = new ResultSetHandler() {

            @Override
            public Object handle(ResultSet rs) throws SQLException {
                if (rs.next()) {
                    return rs.getObject(1);
                }
                return null;
            }
        };
        Date date = Calendar.getInstance().getTime();
        final int size = ExchangeRatesDownloader.supportedCurrencyCodes.size();
        for (int i = 0; i < size; i++) {
            String code = ExchangeRatesDownloader.supportedCurrencyCodes.get(i);
            BigDecimal tauxCommercial;
            if (this.commercialConverter != null) {
                tauxCommercial = commercialConverter.convert(BigDecimal.ONE, report.getMainCurrencyCode(), code, date, true);
            } else {
                try {
                    tauxCommercial = converter.convert(BigDecimal.ONE, report.getMainCurrencyCode(), code, date, true);
                } catch (IllegalStateException e) {
                    // Aucun taux n'existe encore dans la base.
                    tauxCommercial = null;
                }
            }
            if (tauxCommercial == null) {
                tauxCommercial = BigDecimal.ONE;
            }
            tauxCommercial = tauxCommercial.setScale(4, BigDecimal.ROUND_HALF_UP);
            String query = "INSERT INTO " + tDeviseHisto.getSQLName().quote();
            final BigDecimal rate = report.getRate(code);
            if (rate == null) {
                System.err.println("RATE " + code + " NOT Supported");
                // Thread.dumpStack();
            } else {
                final BigDecimal taux = rate.setScale(4, BigDecimal.ROUND_HALF_UP);

                final java.sql.Date date2 = new java.sql.Date(report.getDate().getTime());
                query += " (\"DATE\", \"SRC\", \"DST\", \"TAUX\", \"TAUX_COMMERCIAL\", \"ORDRE\") select " + SQLBase.quoteStringStd(date2.toString()) + ", "
                        + SQLBase.quoteStringStd(report.getMainCurrencyCode()) + ", " + SQLBase.quoteStringStd(code) + ", " + taux + ", " + tauxCommercial + ", COALESCE(MAX(\"ORDRE\"), 0) + 1 ";
                query += "FROM " + tDeviseHisto.getSQLName().quote() + " RETURNING \"ID\"";

                queries.add(query);
                handlers.add(handler);
            }
        }

        try {
            SQLUtils.executeMultiple(tDeviseHisto.getDBSystemRoot(), queries, handlers);

        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    protected Set<Date> getMissingDates(CurrencyConverter converter, DBRoot root) {
        final SQLSelect select = new SQLSelect();

        final SQLTable t = root.getTable("DEVISE_HISTORIQUE");
        select.addAllSelect(t, Arrays.asList("DATE"));
        Where w = new Where(t.getField("SRC"), "=", converter.getCompanyCurrencyCode());
        w = w.and(new Where(t.getField("DATE"), "<", new Date(System.currentTimeMillis())));
        select.setWhere(w);
        select.addGroupBy(t.getField("DATE"));
        select.setLimit(30);
        final List<Date> dates = t.getDBSystemRoot().getDataSource().executeCol(select.asString());

        final Set<Date> result = new HashSet<Date>();
        Calendar c = Calendar.getInstance(TimeZone.getTimeZone("UTC"));
        c.set(Calendar.HOUR_OF_DAY, 0);
        c.set(Calendar.MINUTE, 0);
        c.set(Calendar.SECOND, 0);
        c.set(Calendar.MILLISECOND, 0);
        for (int i = 0; i < 20; i++) {
            c.add(Calendar.HOUR_OF_DAY, -24);
            final Date time = c.getTime();
            System.out.println("::" + time);
            result.add(time);
        }

        for (Date date : dates) {
            Calendar c2 = Calendar.getInstance();
            c2.setTime(date);
            c = Calendar.getInstance(TimeZone.getTimeZone("UTC"));
            c.set(Calendar.YEAR, c2.get(Calendar.YEAR));
            c.set(Calendar.DAY_OF_YEAR, c2.get(Calendar.DAY_OF_YEAR));
            c.set(Calendar.HOUR_OF_DAY, 0);
            c.set(Calendar.MINUTE, 0);
            c.set(Calendar.SECOND, 0);
            c.set(Calendar.MILLISECOND, 0);
            Date dd = c.getTime();
            result.remove(dd);
            System.out.println("--" + dd);
        }
        System.out.println(result.size());

        return result;
    }

    @Override
    protected void stop() {
    }

}