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

package org.openconcerto.modules.reports.olap;

import java.awt.GridBagConstraints;
import java.awt.GridBagLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import javax.swing.JButton;
import javax.swing.JLabel;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTextArea;
import javax.swing.SwingConstants;
import javax.swing.SwingWorker;
import javax.swing.event.DocumentEvent;
import javax.swing.event.DocumentListener;

import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.openconcerto.erp.config.ComptaPropsConfiguration;
import org.openconcerto.modules.reports.olap.renderer.Matrix;
import org.openconcerto.sql.model.SQLDataSource;
import org.openconcerto.ui.DefaultGridBagConstraints;
import org.openconcerto.ui.warning.JLabelWarning;

public class OLAPSQLPanel extends JPanel {

    private OLAPRenderer renderer;

    final JLabelWarning w = new JLabelWarning();
    private JButton bExecute;
    final JTextArea textMDX;

    OLAPSQLPanel(OLAPRenderer renderer) {
        this.setOpaque(false);

        this.renderer = renderer;
        this.setLayout(new GridBagLayout());
        GridBagConstraints c = new DefaultGridBagConstraints();
        c.fill = GridBagConstraints.BOTH;
        // Colonnes
        c.gridx = 0;
        c.gridy = 0;
        c.weightx = 1;
        c.weighty = 0;
        JLabel columnsLabel = new JLabel("Requête SQL (select uniquement)", SwingConstants.LEFT);
        this.add(columnsLabel, c);
        c.weighty = 1;
        c.gridy++;
        c.weightx = 1;
        textMDX = new JTextArea();
        textMDX.setText("select \"NOM\",\"PV_HT\" from \"ARTICLE\" where \"PV_HT\" <300 AND \"ID\">1");
        textMDX.setLineWrap(true);
        JScrollPane scroll = new JScrollPane(textMDX);

        scroll.setOpaque(false);
        this.add(scroll, c);

        // Lignes
        c.gridx = 0;
        c.gridy++;
        c.weightx = 0;
        c.weighty = 0;
        bExecute = new JButton("Executer");
        bExecute.setOpaque(false);
        c.fill = GridBagConstraints.NONE;
        c.anchor = GridBagConstraints.WEST;
        this.add(bExecute, c);
        bExecute.addActionListener(new ActionListener() {

            @Override
            public void actionPerformed(ActionEvent e) {
                executeQuery(textMDX.getText());

            }
        });
        c.gridy++;
        w.setOpaque(false);
        w.setVisible(false);
        this.add(w, c);

        textMDX.getDocument().addDocumentListener(new DocumentListener() {

            @Override
            public void removeUpdate(DocumentEvent e) {
                w.setVisible(false);

            }

            @Override
            public void insertUpdate(DocumentEvent e) {
                w.setVisible(false);

            }

            @Override
            public void changedUpdate(DocumentEvent e) {
                w.setVisible(false);

            }
        });

    }

    protected void executeQuery(final String req) {
        String lower = req.toLowerCase();
        if (lower.contains("update") || lower.contains("delete") || lower.contains("insert") || lower.contains("add") || lower.contains("alter") || lower.contains("drop") || lower.contains("exec")) {
            return;
        }

        this.textMDX.setEditable(false);
        this.bExecute.setEnabled(false);

        SwingWorker<Matrix, Object> worker = new SwingWorker<Matrix, Object>() {

            @Override
            protected Matrix doInBackground() throws Exception {
                try {
                    SimpleDateFormat df = new SimpleDateFormat("dd/MM/yyyy");
                    // voir pour utiliser createDataSource avec un user spécifique en lecture seule
                    SQLDataSource src = ComptaPropsConfiguration.getInstanceCompta().getRootSociete().getDBSystemRoot().getDataSource();
                    List result = (List) src.execute(req, new ArrayListHandler());
                    int height = result.size();

                    int width = 0;
                    if (height > 0) {
                        width = ((Object[]) result.get(0)).length;
                    }
                    final Matrix matrix = new Matrix(width, height);
                    System.err.println("OLAPSQLPanel.executeQuery() MATRIX :" + matrix.getWidth() + " x " + matrix.getHeight());
                    for (int y = 0; y < height; y++) {
                        Object o = result.get(y);
                        Object[] l = (Object[]) o;

                        for (int x = 0; x < width; x++) {
                            Object obj = l[x];
                            if (obj == null) {
                                obj = "";
                            }
                            if (obj instanceof BigDecimal) {
                                obj = ((BigDecimal) obj).toPlainString();
                            }
                            if (obj instanceof Date) {
                                obj = df.format((Date) obj);
                            }
                            matrix.set(x, y, obj.toString());
                        }
                    }

                    return matrix;
                } catch (Exception e) {

                    e.printStackTrace();
                    w.setVisible(true);

                    w.setText(e.getCause().getMessage());
                    return null;
                }
            }

            protected void done() {
                Matrix matrix;
                try {
                    matrix = get();
                    renderer.setMatrix(matrix);
                } catch (Exception e) {
                    e.printStackTrace();
                }
                textMDX.setEditable(true);
                bExecute.setEnabled(true);
            }
        };
        renderer.setWaitState(true);
        worker.execute();

    }
}