/*
 * 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.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;

/**
 *
 * @author christophe.marchand
 */
public class NumLiassePatcher extends AbstractImportPatcherImpl {
    private static final Logger logger = Logger.getLogger(NumLiassePatcher.class);
    private static final transient String SCRIPT = "CREATE TEMPORARY TABLE IF NOT EXISTS INDEX_PATCH (\n" +
            "ANNEE VARCHAR(2),\n"+
            "TRIMESTRE VARCHAR(1),\n"+
            "COMPTABLE VARCHAR(6),\n"+
            "MINISTERE VARCHAR(3),\n"+
            "LOCAL VARCHAR(4),\n"+
            "NATIONAL VARCHAR(6),\n"+
            "INDEX IX_PATCH(ANNEE,TRIMESTRE,COMPTABLE,MINISTERE,LOCAL)\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 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();
        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);
            st.execute("TRUNCATE TABLE INDEX_PATCH");
            PreparedStatement ps = conPatch.prepareStatement("INSERT INTO INDEX_PATCH(ANNEE,TRIMESTRE,COMPTABLE,MINISTERE,LOCAL,NATIONAL) 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<6;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()",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.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 ministere = rsLiasses.getString(2);
            String annee = rsLiasses.getString(3);
            String trimestre = rsLiasses.getString(4);
            String numLiasseDeb = rsLiasses.getString(5);
            String numLiasseFin = rsLiasses.getString(6);
            PreparedStatement ps = conPatch.prepareStatement("SELECT NATIONAL FROM INDEX_PATCH WHERE ANNEE=? AND TRIMESTRE=? AND COMPTABLE=? AND MINISTERE=? AND LOCAL>=? AND LOCAL<=?");
            ps.setString(1,annee.substring(2));
            ps.setString(2,trimestre);
            ps.setString(3,comptable);
            ps.setString(4,ministere);
            ps.setString(5, numLiasseDeb);
            ps.setString(6, numLiasseFin);
            ResultSet rs2 = ps.executeQuery();
//logger.debug("querying ["+annee.substring(2)+","+trimestre+","+comptable+","+ministere+","+numLiasseDeb+","+numLiasseFin+"]");
            StringBuilder numLiassesNat = new StringBuilder();
            ArrayList<String> liasses = new ArrayList<String>();
            while(rs2.next()) {
                numLiassesNat.append(rs2.getString(1)).append(" ");
                liasses.add(rs2.getString(1));
            }
            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) VALUES(?,?,?,?)");
                ps.setString(1,collectivite);
                ps.setString(2,budget);
                ps.setString(3,docId);
                for(String l:liasses) {
                    ps.setString(4,l);
                    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();
                    trans.setParameter("liasses",numLiassesNat.toString());
                    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;
    }

}
