/*
 * Copyright
 *   2007 axYus - www.axyus.com
 *   2007 C.Marchand - christophe.marchand@axyus.com
 *
 * This file is part of XEMELIOS.
 *
 * XEMELIOS is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation; either version 2 of the License, or
 * (at your option) any later version.
 *
 * XEMELIOS is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with XEMELIOS; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
 */
package fr.gouv.finances.cp.xemelios.plugins.paye;

import fr.gouv.finances.cp.utils.StartUrl;
import fr.gouv.finances.dgfip.utils.xml.dompath.DomPath;
import fr.gouv.finances.dgfip.utils.xml.xpath.FunctionResolver;
import fr.gouv.finances.dgfip.xemelios.common.ToolException;
import fr.gouv.finances.dgfip.xemelios.common.config.PropertyModel;
import fr.gouv.finances.dgfip.xemelios.data.DataConfigurationException;
import fr.gouv.finances.dgfip.xemelios.data.DataLayerManager;
import fr.gouv.finances.cp.xemelios.data.impl.MySqlDataLayer;
import fr.gouv.finances.dgfip.xemelios.data.impl.sqlconfig.TDocument;
import fr.gouv.finances.dgfip.xemelios.data.impl.sqlconfig.TEtat;
import fr.gouv.finances.cp.xemelios.plugins.ExportPluginRunner;
import fr.gouv.finances.dgfip.xemelios.auth.XemeliosUser;
import fr.gouv.finances.dgfip.xemelios.plugins.ExportPlugin;
import java.io.File;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.io.Writer;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.swing.filechooser.FileFilter;
import javax.xml.xpath.XPath;
import javax.xml.xpath.XPathConstants;
import javax.xml.xpath.XPathFactory;
import org.apache.log4j.Logger;
import org.w3c.dom.Document;
import org.w3c.dom.Node;

/**
 *
 * @author chm
 */
public class Totalisations extends ExportPlugin {

    private static final Logger logger = Logger.getLogger(Totalisations.class);
    private MySqlDataLayer layer = null;
    private TDocument pc = null;
    private TEtat ec = null;
    private XPathFactory xpf = null;
    private XPath xpath = null;
    private final String CRLF;
    // sequence-wide variables
    private String groupBy = null,  request = null;
    private String output = null;
    private Document repository;
    private DomPath dpLibelle = null;
    private DomPath dpCodeNatureEmpl = null;
    private DomPath dpCodeNatureSal = null;
    private Writer writer;

    /** Creates a new instance of Totalisations */
    public Totalisations(XemeliosUser user) {
        super(user);
        CRLF = System.getProperty("line.separator");
        try {
            xpf = XPathFactory.newInstance(XPathFactory.DEFAULT_OBJECT_MODEL_URI);
        } catch (Throwable t) {
            xpf = XPathFactory.newInstance();
        }
        xpf.setXPathFunctionResolver(new FunctionResolver());
        xpath = xpf.newXPath();
    }

    public Long doExport() throws Exception {
        //setProgressVisible(true);
        xpath.setNamespaceContext(getDocumentModel().getNamespaces());
        output = getParameters().get("VALEUR:output");
        dpLibelle = new DomPath("n:Libelle/@V", getDocumentModel().getNamespaces());
        dpCodeNatureEmpl = new DomPath("n:CodeNatureEmp/@V", getDocumentModel().getNamespaces());
        dpCodeNatureSal = new DomPath("n:CodeNatureSal/@V", getDocumentModel().getNamespaces());
        String rq = null;
//        if("RUBRIQUE".equals(groupBy)) {
        repository = layer.getRepositoryAsDom(getDocumentModel(), getCollectivite(), getRefenrenceNomenclature() == null ? null : getRefenrenceNomenclature().key, getUser());
        rq = getParameterizedRequest(request);
//        }
        logger.debug(rq);
        writeHeader();
        long count = 0L;
        Connection con = null;
        try {
            ResultSet rs = layer.execute(rq);
            try {
                con = rs.getStatement().getConnection();
            } catch (Throwable t) {
            }
            while (rs.next()) {
                writeLine(rs);
                count++;
            }
        } catch (SQLException sqlEx) {
            logger.error("", sqlEx);
        } finally {
            if (con != null) {
                layer.releaseConnection(con);
            }
        }
        writeFooter();
        getWriter().flush();
        //setProgressVisible(false);
        return count;
    }

    protected void writeHeader() throws IOException {
        StringBuffer buff = new StringBuffer();
        if ("HTML".equals(output)) {
            buff.append("<?xml version=\"1.0\" encoding=\"ISO-8859-1\"?>").append(CRLF).append("<!DOCTYPE html PUBLIC \"-//W3C//DTD XHTML 1.0 Strict//EN\" \"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd\">").append(CRLF);
            buff.append("<html xmlns=\"http://www.w3.org/1999/xhtml\">").append(CRLF);
            buff.append("\t<head>").append(CRLF).append("\t\t<title>Totalisation des lignes de paye par ").append("RUBRIQUE".equals(groupBy) ? "rubriques" : "caisses").append("</title>").append(CRLF);
            buff.append("\t\t<style type=\"text/css\" media=\"all\">").append(CRLF);
            buff.append("\t\t\tth { background-color: #BBBBBB; border-style: solid none solid solid; border-width: 1px}").append(CRLF);
            buff.append("\t\t\tth.right { border-right-style: solid; }").append(CRLF);
            buff.append("\t\t\ttd { border-style: none none solid solid; border-width: 1px}").append(CRLF);
            buff.append("\t\t\ttd.mt { text-align: right; }").append(CRLF);
            buff.append("\t\t\ttd.right { border-right-style: solid; }").append(CRLF);
            buff.append("\t\t\ttable,tr { padding: 0 0 0 0; }").append(CRLF);
            buff.append("\t\t</style>").append(CRLF).append("\t</head>").append(CRLF);
            buff.append("\t<body>").append(CRLF);
            buff.append("\t\t<table cellspacing=\"0\">").append(CRLF).append("\t\t\t<thead>").append(CRLF);
            if ("RUBRIQUE".equals(groupBy)) {
                buff.append("\t\t\t\t<tr><th>Code Rubrique</th><th>Libellé</th><th>Montant</th><th>Code Nature (part employeur)</th><th class=\"right\">Code Nature (part salariale)</th></tr>").append(CRLF);
            } else {
                buff.append("\t\t\t\t<tr><th>Code Caisse</th><th>Libellé</th><th class=\"right\">Montant</th></tr>").append(CRLF);
            }
            buff.append("\t\t\t</thead>").append(CRLF);
            buff.append("\t\t\t<tbody>").append(CRLF);
        } else {
            if ("RUBRIQUE".equals(groupBy)) {
                buff.append("Code Rubrique;Libellé;Montant;Code Nature(part employeur);Code Nateur (part salariale)").append(CRLF);
            } else {
                buff.append("Code Caisse;Libellé;Montant").append(CRLF);
            }
        }
        getWriter().write(buff.toString(), 0, buff.length());
    }

    protected void writeFooter() throws IOException {
        StringBuffer buff = new StringBuffer();
        if ("HTML".equals(output)) {
            buff.append("\t\t\t</tbody>").append(CRLF).append("\t\t</table>").append(CRLF).append("\t</body>").append(CRLF).append("</html>");
        }
        getWriter().write(buff.toString(), 0, buff.length());
    }

    protected void writeLine(ResultSet rs) throws SQLException, IOException {
        StringBuffer buff = new StringBuffer();
        if ("RUBRIQUE".equals(groupBy)) {
            String code = rs.getString(1);
            String libelle = rs.getString(2);
            BigDecimal bd = rs.getBigDecimal(3);
            TripleString ts = getInfosFromRepository(code);
            String codeNatureE = ts.getS2();
            String codeNatureS = ts.getS3();
            if (libelle == null) {
                libelle = ts.getS1();
            }
            if ("HTML".equals(output)) {
                buff.append("\t\t\t\t<tr><td>").append(code != null ? code : "").append("</td><td>").append(libelle != null ? libelle : "").append("</td><td class=\"mt\">").append(bd).append("</td><td>").append(codeNatureE != null ? codeNatureE : "").append("</td><td class=\"right\">").append(codeNatureS != null ? codeNatureS : "").append("</td></tr>").append(CRLF);
            } else {
                buff.append("\"").append(code != null ? code : "").append("\";\"").append(libelle != null ? libelle : "").append("\";").append(bd).append(";\"").append(codeNatureE != null ? codeNatureE : "").append("\";\"").append(codeNatureS != null ? codeNatureS : "").append(CRLF);
            }
        } else {
            String codeCaisse = rs.getString(1);
            BigDecimal mt = rs.getBigDecimal(2);
            String libelle = getLibelleCaisseFromRepository(codeCaisse);
            if ("HTML".equals(output)) {
                buff.append("\t\t\t\t<tr><td>").append(codeCaisse != null ? codeCaisse : "").append("</td><td>").append(libelle != null ? libelle : "").append("</td><td class=\"mt right\">").append(mt).append("</td></tr>").append(CRLF);
            } else {
                buff.append(codeCaisse != null ? codeCaisse : "").append(";\"").append(libelle != null ? libelle : "").append("\";").append(mt).append(CRLF);
            }
        }
        getWriter().write(buff.toString(), 0, buff.length());
        if(getProgressListener()!=null)
            getProgressListener().pushProgress();
    }

    private TripleString getInfosFromRepository(String codeRubrique) {
        if (repository == null) {
            return new TripleString();
        }
        try {
            Node node = (Node) xpath.evaluate("/n:DocumentPaye/n:Nomenclatures/n:NomenclatureRubriquePaye/n:Correspondance[n:Code/@V='" + codeRubrique + "']", repository, XPathConstants.NODE);
            return new TripleString((String) dpLibelle.getValue(node, true), (String) dpCodeNatureEmpl.getValue(node, true), (String) dpCodeNatureSal.getValue(node, true));
        } catch (Exception ex) {
            logger.error("while xpathing:", ex);
        }
        return new TripleString();
    }

    private String getLibelleCaisseFromRepository(String codeCaisse) {
        if (repository == null) {
            return "";
        }
        try {
            String s = (String) xpath.evaluate("/n:DocumentPaye/n:Nomenclatures/n:NomenclatureCaisse/n:Correspondance[n:Code/@V='" + codeCaisse + "']/n:Libelle/@V", repository);
            return s;
        } catch (Exception ex) {
            logger.error("while xpathing:", ex);
        }
        return "";
    }

    public void computeParameters() throws Exception {
        for (PropertyModel pm : getPluginModel().getProperties()) {
            if ("group-by".equals(pm.getName())) {
                groupBy = pm.getValue();
            } else if ("request-rubrique".equals(pm.getName())) {
                request = pm.getValue();
            }
        }
        layer = (MySqlDataLayer) DataLayerManager.getImplementation();
        pc = layer.getPersistenceConfig(getDocumentModel(), getUser()).getLayer("mysql").getDocument(getDocumentModel().getId());
        ec = pc.getEtat(getEtatModel().getId());
    }

    public String getDefaultFileExtension() {
        return getParameters().get("VALEUR:output");
    }

    protected String getParameterizedRequest(final String rq) throws DataConfigurationException {
        String data = rq;
        // first, change table names
        int posParam = data.indexOf("${");
        while (posParam >= 0) {
            int posEnd = data.indexOf('}', posParam);
            if (posEnd < 0) {
                throw new DataConfigurationException("Invalid request: " + rq);
            }
            String paramName = data.substring(posParam + 2, posEnd);
            String paramValue = null;
            if (paramName.startsWith("table-")) {
                String typeTable = paramName.substring(6);
                if (typeTable.startsWith("index-")) {
                    String indexId = typeTable.substring(6);
                    paramValue = ec.getIndexTable(indexId).getBaseName();
                } else {
                    throw new DataConfigurationException("can only deal with index tables: ${" + paramName + "}");
                }
            } else if (paramName.equals("collectivite")) {
                paramValue = getCollectivite().key;
            } else if (paramName.equals("budget")) {
                paramValue = getBudget().key;
            } else {
                throw new DataConfigurationException("can only deal with tables parameters: ${" + paramName + "}");
            }
            if (paramValue == null) {
                throw new DataConfigurationException("no value found for param ${" + paramName + "}");
            }
            data = data.replaceAll("\\$\\{" + paramName + "\\}", paramValue);
            // next round
            posParam = data.indexOf("${");
        }
//        logger.debug(data);

        // next, change parameters
        int pos = data.indexOf("##");
        while (pos >= 0) {
            int endPos = data.indexOf("##", pos + 2);
            if (endPos < 0) {
                throw new DataConfigurationException("invalid expression near " + data.substring(pos));
            }
            String pattern = data.substring(pos, endPos + 2);
            String paramName = pattern.substring(2, pattern.length() - 2);
//            logger.debug("itemName="+itemName);
            String paramValue = getParameters().get(paramName);
            if (paramValue != null) {
                if ("VALEUR:mois".equals(paramName)) {
                    data = data.replace(pattern, getMois(paramValue));
                } else {
                    data = data.replace(pattern, paramValue);
                }
            } else {
                data = data.replace(pattern, "\"\"");
            }
            pos = data.indexOf("##");
        }
        if ("RUBRIQUE".equals(groupBy)) {
            String rub = getParameters().get("VALEUR:Rub");
//            logger.debug("Rub="+rub);
            if (rub != null && !"TOUS".equals(rub)) {
                data = data + " AND " + ec.getIndexTable("T2").getBaseName() + ".CODE='" + rub + "'";
            }
            data = data + " GROUP BY 1,2";
        } else {
            String caisse = getParameters().get("VALEUR:caisse");
            if (caisse != null && !"TOUS".equals(caisse)) {
                data = data + " AND " + ec.getIndexTable("T2").getBaseName() + ".CODE_CAISSE='" + caisse + "'";
            }
            data = data + " GROUP BY 1";
        }
        return data;
    }

    private String getMois(String mois) {
        logger.debug("mois=" + mois);
        if ("TOUS".equals(mois)) {
            return "'01','02','03','04','05','06','07','08','09','10','11','12'";
        } else {
            return "'" + mois + "'";
        }
    }

//    @Override
//    public FileFilter getFileFilter() {
//        FileFilter retValue;
//        String fileExt = getDefaultFileExtension();
//        if("HTML".equals(fileExt)) {
//            retValue = new FileFilter() {
//                @Override
//                public boolean accept(File f) {
//                    return f.isDirectory() || f.getName().toLowerCase().endsWith("html");
//                }
//                @Override
//                public String getDescription() {
//                    return "Fichiers HTML";
//                }
//            };
//        } else {
//            retValue = new FileFilter() {
//                @Override
//                public boolean accept(File f) {
//                    return f.isDirectory() || f.getName().toLowerCase().endsWith("csv");
//                }
//                @Override
//                public String getDescription() {
//                    return "Fichiers CSV";
//                }
//            };
//        }
//        return retValue;
//    }
    private Writer getWriter() {
        if (writer == null) {
            writer = new OutputStreamWriter(getOutputStream());
        }
        return writer;
    }

//    @Override
//    public void done() {
//        super.done();
//        if("HTML".equals(output)) {
////            (MainWindow)(getOwner()).s
////            getFileName()
//            try {
//                StartUrl.startUrl(getFileName());
//            } catch(ToolException tEx) {
//                logger.error("while displaying "+getFileName(),tEx);
//            }
//        }
//    }
    private static class TripleString {

        String s1 = null;
        String s2 = null;
        String s3 = null;

        public TripleString() {
            super();
        }

        public TripleString(String s1, String s2, String s3) {
            this();
            this.s1 = s1;
            this.s2 = s2;
            this.s3 = s3;
        }

        public String getS1() {
            return s1;
        }

        public String getS2() {
            return s2;
        }

        public String getS3() {
            return s3;
        }

        public void setS1(String s1) {
            this.s1 = s1;
        }

        public void setS2(String s2) {
            this.s2 = s2;
        }

        public void setS3(String s3) {
            this.s3 = s3;
        }
    }
}
