/*
 * Copyright
 *   2009 axYus - www.axyus.com
 *   2009 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.data.impl.mysql;

import fr.gouv.finances.cp.xemelios.data.impl.MySqlDataLayer;
import fr.gouv.finances.dgfip.xemelios.common.config.DocumentModel;
import fr.gouv.finances.dgfip.xemelios.common.config.DocumentsModel;
import fr.gouv.finances.dgfip.xemelios.common.config.Loader;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.xpath.XPath;
import javax.xml.xpath.XPathFactory;
import org.apache.log4j.Logger;
import org.w3c.dom.Document;

/**
 *
 * @author christophe.marchand
 */
public class CgCollocPatch40 {
    private static final Logger logger = Logger.getLogger(CgCollocPatch40.class);
    private MySqlDataLayer impl = null;

    public CgCollocPatch40(MySqlDataLayer impl) {
        super();
        this.impl=impl;
    }

    public void run() {
         Connection con = null;
         try {
            con = impl.getConnection();
            // check if document table exists for CG-COLLOC
            boolean isDocumentTableExists = false;
            try {
                con.createStatement().execute("SELECT COLLECTIVITE FROM CG_LIST_BUDG_COLL LIMIT 1");
                isDocumentTableExists = true;
            } catch(Throwable t) {}
            if(!isDocumentTableExists) return;
            ResultSet rs = con.createStatement().executeQuery("SELECT COLLECTIVITE, BUDGET, INITIAL_DOC_NAME FROM CG_LIST_BUDG_COLL WHERE PARENT_COLL1 IS NULL");
            DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
            dbf.setNamespaceAware(true);
            XPathFactory xpf = XPathFactory.newInstance();
            XPath xp = xpf.newXPath();
            DocumentsModel docs =  Loader.getDocumentsInfos(null);
            DocumentModel dm = docs.getDocumentById("compteGestion");
            if(dm==null) return;
            xp.setNamespaceContext(dm.getNamespaces());
            while(rs.next()) {
                String collectivite = rs.getString(1);
                String budget = rs.getString(2);
                String initialDocName = rs.getString(3);
                PreparedStatement ps = con.prepareStatement("SELECT DOC,DOC_ID,ENCODING FROM CG_BALANCEGENERALE_DOCUMENTS WHERE COLLECTIVITE=? AND BUDGET=? AND INITIAL_DOC_NAME=? LIMIT 1");
                ps.setString(1,collectivite);
                ps.setString(2,budget);
                ps.setString(3,initialDocName);
                ResultSet rs2 = ps.executeQuery();
                if(rs2.next()) {
                    logger.debug("DOC_ID="+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 = dbf.newDocumentBuilder();
                    byte[] byteArray = baos.toByteArray();
                    InputStream bis = new ByteArrayInputStream(byteArray);
                    Document doc = builder.parse(bis);
                    String parentColl1 = xp.evaluate("/n:CompteGestion/n:Entete/n:Infos/n:Tresorerie/@Codique", doc);
                    String parentColl1Lib = xp.evaluate("/n:CompteGestion/n:Entete/n:Infos/n:Tresorerie/@LibelleCodique",doc);
                    String parentColl2 = xp.evaluate("/n:CompteGestion/n:Entete/n:Infos/n:Tresorerie/@Dpt", doc);
                    ps = con.prepareStatement("UPDATE CG_LIST_BUDG_COLL SET PARENT_COLL1=?, PARENT_COLL1_LIB=?, PARENT_COLL2=?, PARENT_COLL2_LIB=? WHERE COLLECTIVITE=? AND BUDGET=? AND INITIAL_DOC_NAME=?");
                    ps.setString(1,parentColl1);
                    ps.setString(2,parentColl1Lib);
                    ps.setString(3,parentColl2);
                    ps.setString(4,parentColl2);
                    ps.setString(5,collectivite);
                    ps.setString(6,budget);
                    ps.setString(7,initialDocName);
                    ps.executeUpdate();
                }
                rs2.close();
            }
            rs.close();
         } catch (Throwable t) {
            logger.error("in run()", t);
         } finally {
            if (con != null) {
               impl.releaseConnection(con);
            }
         }

    }
}
