/*
 * 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 Lesser General Public License as published by
 * the Free Software Foundation; either version 3 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 Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser 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.cp.utils.xml.dompath.DomPath;
import fr.gouv.finances.cp.utils.xml.xpath.FunctionResolver;
import fr.gouv.finances.cp.xemelios.common.ToolException;
import fr.gouv.finances.cp.xemelios.common.config.PropertyModel;
import fr.gouv.finances.cp.xemelios.data.DataConfigurationException;
import fr.gouv.finances.cp.xemelios.data.DataLayerManager;
import fr.gouv.finances.cp.xemelios.data.impl.MySqlDataLayer;
import fr.gouv.finances.cp.xemelios.data.impl.sqlconfig.TDocument;
import fr.gouv.finances.cp.xemelios.data.impl.sqlconfig.TEtat;
import fr.gouv.finances.cp.xemelios.plugins.ExportPlugin;
import java.io.File;
import java.io.IOException;
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;

    
    /** Creates a new instance of Totalisations */
    public Totalisations() {
        super();
        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());
            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();
        //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());
        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()).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+"'";
    }

    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;
    }

    @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; }
    }
}
