/*
 * Copyright
 *   2007 axYus - www.axyus.com
 *   2009 c.Marhcand - 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.dgfip.xemelios.edmn.patchers;

import fr.gouv.finances.dgfip.xemelios.common.FileInfo;
import fr.gouv.finances.dgfip.xemelios.data.impl.pool.PoolManager;
import fr.gouv.finances.dgfip.xemelios.importers.AbstractImportPatcherImpl;
import java.io.BufferedReader;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileReader;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;
import javax.xml.transform.stream.StreamSource;
import net.sf.saxon.TransformerFactoryImpl;
import org.apache.log4j.Logger;
import org.w3c.dom.Document;
import org.w3c.dom.Element;

/**
 *
 * @author christophe.marchand
 */
public class NumLiassePatcher extends AbstractImportPatcherImpl {
    private static final Logger logger = Logger.getLogger(NumLiassePatcher.class);
    private static final String TABLE_INDEX = "INDEX_PATCH";
    private static final String SCRIPT_INDEX = "CREATE TABLE IF NOT EXISTS "+TABLE_INDEX+" (\n"+
        "IND_ANNEE_GEST VARCHAR(2) not null,\n"+
        "IND_TRI_GEST VARCHAR(1) not null,\n"+
        "IND_COMPTABLE VARCHAR(6) not null,\n"+
        "IND_IMPUTATION VARCHAR(1) not null,\n"+
        "IND_MINISTERE VARCHAR(2) not null,\n"+
        "IND_COMPTESPE VARCHAR(5) not null,\n"+
        "IND_LOCALE VARCHAR(4) not null,\n"+
        "IND_ANNEE_TRSP VARCHAR(2) not null,\n"+
        "IND_TRI_TRSP VARCHAR(1) not null,\n"+
        "IND_SECTION_BUDG VARCHAR(2),\n"+
        "IND_CPTESPENAT VARCHAR(5) not null,\n"+
        "IND_NATIONALE VARCHAR(6),\n"+
        "INDEX IX_INDEX (IND_ANNEE_GEST,IND_TRI_GEST,IND_COMPTABLE,IND_IMPUTATION,IND_MINISTERE,IND_LOCALE)\n"+
    ")";
    private static final transient String REQUEST = "SELECT edmn.COLLECTIVITE, edmn.BUDGET, edmn.DOC_ID FROM EDMN_ETATEDMN_INDEX_EDMN edmn LEFT OUTER JOIN EDMN_ETATEDMN_IX_EDMN_LIASSES liasse ON edmn.COLLECTIVITE=liasse.COLLECTIVITE and edmn.BUDGET=liasse.BUDGET and edmn.DOC_ID=liasse.DOC_ID "+
//            "where edmn.MINISTERE='23' and edmn.COMPTABLE='075000' and edmn.NUM_LIASSE_DEB='0018' and edmn.ORDO='062075' and edmn.CHAP_BUDG='0225' and edmn.NUM_LIASSE_DEB='0018' "+
            "GROUP BY edmn.COLLECTIVITE, edmn.BUDGET, edmn.DOC_ID HAVING count(liasse.DOC_ID)=0";
    private static final transient String REQUEST_COUNT = "SELECT count(*) from ( SELECT edmn.COLLECTIVITE, edmn.BUDGET, edmn.DOC_ID FROM EDMN_ETATEDMN_INDEX_EDMN edmn LEFT OUTER JOIN EDMN_ETATEDMN_IX_EDMN_LIASSES liasse ON edmn.COLLECTIVITE=liasse.COLLECTIVITE and edmn.BUDGET=liasse.BUDGET and edmn.DOC_ID=liasse.DOC_ID GROUP BY edmn.COLLECTIVITE, edmn.BUDGET, edmn.DOC_ID HAVING count(liasse.DOC_ID)=0 ) as toto";
    protected DocumentBuilderFactory domFactory;
    protected TransformerFactory transformerFactory;
    public NumLiassePatcher() {
        super();
        domFactory = DocumentBuilderFactory.newInstance();
        domFactory.setNamespaceAware(true);
        transformerFactory = TransformerFactoryImpl.newInstance();
    }
    public FileInfo run() {
        getImportServiceProvider().startLongWait();
        File indexFile = (File)getParameter("index.file");
        Connection conPatch = null, con2 = null, con3=null;
        int rowPatched = 0;
        try {
            conPatch = PoolManager.getInstance().getConnection();
            con2 = PoolManager.getInstance().getConnection();
            con3 = PoolManager.getInstance().getConnection();
            Statement st = conPatch.createStatement();
            st.execute(SCRIPT_INDEX);
            st.execute("TRUNCATE TABLE "+TABLE_INDEX);
            PreparedStatement ps = conPatch.prepareStatement("INSERT INTO "+TABLE_INDEX+" (IND_ANNEE_GEST,IND_TRI_GEST,IND_COMPTABLE,IND_IMPUTATION,IND_MINISTERE,IND_COMPTESPE,IND_LOCALE,IND_ANNEE_TRSP,IND_TRI_TRSP,IND_SECTION_BUDG,IND_CPTESPENAT,IND_NATIONALE) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)");
            BufferedReader br = new BufferedReader(new FileReader(indexFile));
            String line = br.readLine();
            while(line!=null && line.length()>0) {
                String[] els = line.split(";");
                for(int i=0;i<12;i++) ps.setString(i+1,els[i]);
//logger.debug("inserting ["+els[0]+","+els[1]+","+els[2]+","+els[3]+","+els[4]+","+els[5]+"]");
                ps.executeUpdate();
                line = br.readLine();
            }
            ResultSet rs = con2.createStatement().executeQuery(REQUEST_COUNT);
            rs.next();
            int rows = rs.getInt(1);

            getImportServiceProvider().endLongWait();
            br.close();
            rs = con2.createStatement().executeQuery(REQUEST);
            int rowFetched = 0;
            while(rs.next()) {
                final int progress = (int)((rowFetched+1)*100.0/rows);
                getImportServiceProvider().pushCurrentProgress(progress);
                String collectivite = rs.getString(1);
                String budget = rs.getString(2);
                String docId  =rs.getString(3);
                if(patchDocument(collectivite,budget,docId,conPatch,con3))
                    rowPatched++;
            }
        } catch(Exception ex) {
            logger.error("run() - indexFile="+indexFile.getAbsolutePath(),ex);
        } finally {
            if(conPatch!=null) {
//                try { conPatch.createStatement().executeUpdate("DROP TABLE INDEX_PATCH"); }catch(Exception ex) {}
                PoolManager.getInstance().releaseConnection(conPatch);
            }
            if(con2!=null) PoolManager.getInstance().releaseConnection(con2);
            if(con3!=null) PoolManager.getInstance().releaseConnection(con3);
            getImportServiceProvider().endLongWait();
        }
        FileInfo ret = new FileInfo();
        ret.setMessage(rowPatched+" mandats corrigés pour les liasses nationales\n");
        return ret;
    }

    private boolean patchDocument(String collectivite,String budget,String docId,Connection conPatch,Connection con2) throws Exception {
        boolean ret = false;
        PreparedStatement psLiassesLocales = con2.prepareStatement("SELECT edmn.COMPTABLE, edmn.IMPUTATION, edmn.MINISTERE, edmn.ANNEE_BUDG, edmn.TRIMESTRE, edmn.NUM_LIASSE_DEB, edmn.NUM_LIASSE_FIN FROM EDMN_ETATEDMN_INDEX_EDMN edmn where edmn.COLLECTIVITE=? AND edmn.BUDGET=? AND edmn.DOC_ID=?");
        psLiassesLocales.setString(1,collectivite);
        psLiassesLocales.setString(2, budget);
        psLiassesLocales.setString(3, docId);
        ResultSet rsLiasses = psLiassesLocales.executeQuery();
        if(rsLiasses.next()) {
            String comptable = rsLiasses.getString(1);
            String imputation = rsLiasses.getString(2);
            String ministere = rsLiasses.getString(3);
            String annee = rsLiasses.getString(4);
            String trimestre = rsLiasses.getString(5);
            String numLiasseDeb = rsLiasses.getString(6);
            String numLiasseFin = rsLiasses.getString(7);
            StringBuffer numLiasses = new StringBuffer();
            int first = 0;
            int last = 0;
            try {
                first = Integer.parseInt(numLiasseDeb);
            } catch (NumberFormatException nfEx) { }
            try {
                last = Integer.parseInt(numLiasseFin);
            } catch (NumberFormatException nfEx) { }
            numLiasses.append("(");
            for (int i = first; i < last; i++) {
                numLiasses.append("'").append(fmtNumLiasse(i)).append("',");
            }
            numLiasses.append("'").append(fmtNumLiasse(last)).append("')");
            String sql = "SELECT IND_ANNEE_GEST, IND_TRI_GEST, IND_COMPTABLE, IND_IMPUTATION, IND_MINISTERE, IND_COMPTESPE, IND_LOCALE, IND_ANNEE_TRSP, IND_TRI_TRSP, IND_SECTION_BUDG, IND_CPTESPENAT, IND_NATIONALE FROM " + TABLE_INDEX + " WHERE IND_ANNEE_GEST=? AND IND_TRI_GEST=? AND IND_COMPTABLE=? AND IND_IMPUTATION=? AND IND_MINISTERE=? AND IND_LOCALE IN " + numLiasses.toString() + " ORDER BY IND_LOCALE";
            PreparedStatement ps = conPatch.prepareStatement(sql);
            ps.setString(1, annee.substring(2));
            ps.setString(2, trimestre);
            ps.setString(3, comptable);
            ps.setString(4, imputation);
            ps.setString(5, ministere);
            ResultSet rs2 = ps.executeQuery();
//logger.debug("querying ["+annee.substring(2)+","+trimestre+","+comptable+","+imputation+","+ministere+","+numLiasseDeb+","+numLiasseFin+"]");
//            StringBuilder numLiassesNat = new StringBuilder();
            ArrayList<Liasse> liasses = new ArrayList<Liasse>();
            while(rs2.next()) {
//                numLiassesNat.append(rs2.getString(1)).append(" ");
//                liasses.add(rs2.getString(1));
                liasses.add(new Liasse(rs2));
            }
            rs2.close();
            ps.close();
            if(liasses.size()>0) {
//logger.debug("patching ["+collectivite+","+budget+","+docId+"]");
//                numLiassesNat.deleteCharAt(numLiassesNat.length()-1);
                ps = con2.prepareStatement("INSERT INTO EDMN_ETATEDMN_IX_EDMN_LIASSES (COLLECTIVITE,BUDGET,DOC_ID,NUM_LIASSE,ANNEE_GESTION) VALUES(?,?,?,?,?)");
                ps.setString(1,collectivite);
                ps.setString(2,budget);
                ps.setString(3,docId);
                ps.setString(5,annee);
                for(Liasse l:liasses) {
                    ps.setString(4,l.getNumNat());
                    ps.executeUpdate();
                }
                ps.close();
                ps = con2.prepareStatement("SELECT DOC, ENCODING FROM EDMN_ETATEDMN_DOCUMENTS WHERE COLLECTIVITE=? AND BUDGET=? AND DOC_ID=?");
                ps.setString(1,collectivite);
                ps.setString(2,budget);
                ps.setString(3,docId);
                rs2 = ps.executeQuery();
                if(rs2.next()) {
                    String encoding = rs2.getString(2);
                    byte[] buff = new byte[512];
                    ByteArrayOutputStream baos = new ByteArrayOutputStream();
                    InputStream is = rs2.getAsciiStream(1);
                    int length = is.read(buff);
                    while (length > 0) {
                        baos.write(buff, 0, length);
                        length = is.read(buff);
                    }
                    DocumentBuilder builder = domFactory.newDocumentBuilder();
                    byte[] byteArray = baos.toByteArray();
                    InputStream bis = new ByteArrayInputStream(byteArray);
                    Document doc = builder.parse(bis);

                    Transformer trans = transformerFactory.newTransformer(new StreamSource(this.getClass().getClassLoader().getResourceAsStream("fr/gouv/finances/dgfip/xemelios/edmn/patchers/numLiassePatcher.xsl")));
                    ByteArrayOutputStream newDoc = new ByteArrayOutputStream();
                    // préparation de l'objet Liasses
                    DocumentBuilder domBuilder = domFactory.newDocumentBuilder();
                    Document dom = domBuilder.newDocument();
                    Element elLiasses = (Element)dom.createElementNS("http://projets.admisource.gouv.fr/xemelios/namespaces#edmn","Liasses");
                    dom.appendChild(elLiasses);
                    for(Liasse l:liasses) {
                        elLiasses.appendChild(l.getDomNode(dom));
                    }
//Transformer dbgTrans = transformerFactory.newTransformer();
//dbgTrans.transform(new DOMSource(dom), new StreamResult(System.out));
                    trans.setParameter("liasses",new DOMSource(dom));
                    trans.setOutputProperty(OutputKeys.ENCODING, encoding);
                    trans.transform(new DOMSource(doc), new StreamResult(newDoc));
                    ps.close();
                    ps = con2.prepareStatement("UPDATE EDMN_ETATEDMN_DOCUMENTS SET DOC=? WHERE COLLECTIVITE=? AND BUDGET=? AND DOC_ID=?");
                    ps.setString(2,collectivite);
                    ps.setString(3,budget);
                    ps.setString(4,docId);
                    byte[] newDocArray = newDoc.toByteArray();
                    InputStream isNewDoc = new ByteArrayInputStream(newDocArray);
                    ps.setBinaryStream(1, isNewDoc, newDocArray.length);
                    if(ps.executeUpdate()>0) ret = true;
                    ps.close();
                }
                rs2.close();
            }
        }
        rsLiasses.close();
        psLiassesLocales.close();
        return ret;
    }
    protected static String fmtNumLiasse(int i) {
        String v = Integer.toString(i);
        while (v.length() < 4) {
            v = "0".concat(v);
        }
        return v;
    }

    private class Liasse {
        // IND_ANNEE_GEST, IND_TRI_GEST, IND_COMPTABLE, IND_IMPUTATION, IND_MINISTERE, IND_COMPTESPE, IND_LOCALE, IND_ANNEE_TRSP, IND_TRI_TRSP, IND_SECTION_BUDG, IND_CPTESPENAT, IND_NATIONALE
        private String anneeGestion, triGestion;
        private String comptable;
        private String imputation, ministere;
        private String compteSpeTresor, numLocal;
        private String anneeTrsp, triTrsp;
        private String sectionBudg, cpteSpeNat;
        private String numNat;

        public Liasse(ResultSet rs) throws SQLException {
            super();
            int i=1;
            anneeGestion = rs.getString(i++);
            triGestion = rs.getString(i++);
            comptable = rs.getString(i++);
            imputation = rs.getString(i++);
            ministere = rs.getString(i++);
            compteSpeTresor = rs.getString(i++);
            numLocal = rs.getString(i++);
            anneeTrsp = rs.getString(i++);
            triTrsp = rs.getString(i++);
            sectionBudg = rs.getString(i++);
            cpteSpeNat = rs.getString(i++);
            numNat = rs.getString(i++);
        }

        public String getAnneeGestion() {
            return anneeGestion;
        }

        public String getAnneeTrsp() {
            return anneeTrsp;
        }

        public String getComptable() {
            return comptable;
        }

        public String getCompteSpeTresor() {
            return compteSpeTresor;
        }

        public String getCpteSpeNat() {
            return cpteSpeNat;
        }

        public String getImputation() {
            return imputation;
        }

        public String getMinistere() {
            return ministere;
        }

        public String getNumLocal() {
            return numLocal;
        }

        public String getNumNat() {
            return numNat;
        }

        public String getSectionBudg() {
            return sectionBudg;
        }

        public String getTriGestion() {
            return triGestion;
        }

        public String getTriTrsp() {
            return triTrsp;
        }
        public Element getDomNode(Document dom) {
            Element el = dom.createElementNS("http://projets.admisource.gouv.fr/xemelios/namespaces#edmn","Liasse");
            el.setAttribute("anneeGest", anneeGestion);
            el.setAttribute("triGest", triGestion);
            el.setAttribute("cpteSpeTres", compteSpeTresor);
            el.setAttribute("local", numLocal);
            el.setAttribute("anneeTrsp",anneeTrsp);
            el.setAttribute("triTrsp",triTrsp);
            el.setAttribute("sectionBudg",sectionBudg);
            el.setAttribute("cpteSpeNat", cpteSpeNat);
            el.setAttribute("national",numNat);
            String codeBarre = anneeGestion + "." + triGestion + "." + comptable + "." + imputation+ministere +"."+compteSpeTresor+"."+numLocal;
            String etq = sectionBudg + "." + anneeTrsp + "." + triTrsp+"."+cpteSpeNat+"."+numNat;
            el.setAttribute("codeBarre", codeBarre);
            el.setAttribute("etq",etq);
            return el;
        }

    }
}
