/*
 * Copyright
 *   2005 axYus - www.axyus.com
 *   2005 C.Marchand - christophe.marchand@axyus.com
 *   2008 JP.Tessier - jean-philippe.tessier@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;

import fr.gouv.finances.dgfip.xemelios.common.Constants;
import fr.gouv.finances.dgfip.xemelios.common.config.RechercheModel;
import fr.gouv.finances.dgfip.xemelios.data.DocumentInfos;
import fr.gouv.finances.cp.xemelios.data.impl.mysql.MySqlDataResultSet;
import fr.gouv.finances.cp.xemelios.data.impl.mysql.PesAllerPatch33;
import fr.gouv.finances.cp.xemelios.data.impl.mysql.RapportPatch33;
import fr.gouv.finances.cp.xemelios.data.impl.mysql.VersementPatch32;
import fr.gouv.finances.dgfip.xemelios.data.impl.sqlconfig.TSql;
import fr.gouv.finances.dgfip.xemelios.data.ui.PatchProgress;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.Statement;
import java.sql.Types;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Enumeration;
import java.util.Hashtable;
import java.util.List;
import java.util.Stack;
import java.util.TreeSet;
import java.util.Vector;

import java.util.logging.Level;
import javax.xml.namespace.NamespaceContext;
import javax.xml.namespace.QName;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.parsers.SAXParser;
import javax.xml.parsers.SAXParserFactory;
import javax.xml.xpath.XPath;
import javax.xml.xpath.XPathConstants;
import javax.xml.xpath.XPathExpressionException;
import javax.xml.xpath.XPathFactory;

import org.apache.commons.lang.StringEscapeUtils;
import org.apache.log4j.Logger;
import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.xml.sax.Attributes;
import org.xml.sax.SAXException;
import org.xml.sax.helpers.DefaultHandler;

import fr.gouv.finances.dgfip.utils.Pair;
import fr.gouv.finances.cp.utils.PropertiesExpansion;
import fr.gouv.finances.cp.utils.xml.marshal.XmlOutputter;
import fr.gouv.finances.cp.xemelios.data.impl.mysql.InputStreamLogger;
import fr.gouv.finances.dgfip.utils.xml.InvalidPathExpressionException;
import fr.gouv.finances.dgfip.utils.xml.PathNotFoundException;
import fr.gouv.finances.dgfip.utils.xml.dompath.DomPath;
import fr.gouv.finances.dgfip.utils.xml.xpath.FunctionResolver;
import fr.gouv.finances.dgfip.xemelios.auth.XemeliosUser;
import fr.gouv.finances.dgfip.xemelios.common.PJRef;
import fr.gouv.finances.dgfip.xemelios.common.config.ChampModel;
import fr.gouv.finances.dgfip.xemelios.common.config.CritereModel;
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.ElementModel;
import fr.gouv.finances.dgfip.xemelios.common.config.EtatModel;
import fr.gouv.finances.dgfip.xemelios.common.config.RecherchePaireModel;
import fr.gouv.finances.dgfip.xemelios.common.config.ListeResultatModel;
import fr.gouv.finances.dgfip.xemelios.common.config.SpecialKeyModel;
import fr.gouv.finances.dgfip.xemelios.data.AbstractDataImpl;
import fr.gouv.finances.dgfip.xemelios.data.DataAccessException;
import fr.gouv.finances.dgfip.xemelios.data.DataConfigurationException;
import fr.gouv.finances.dgfip.xemelios.data.DataLayerManager;
import fr.gouv.finances.dgfip.xemelios.data.DataResultSet;
import fr.gouv.finances.dgfip.xemelios.data.impl.sqlconfig.TCriteria;
import fr.gouv.finances.dgfip.xemelios.data.impl.sqlconfig.TDocument;
import fr.gouv.finances.dgfip.xemelios.data.impl.sqlconfig.TEtat;
import fr.gouv.finances.dgfip.xemelios.data.impl.sqlconfig.TLayer;
import fr.gouv.finances.dgfip.xemelios.data.impl.sqlconfig.TPatch;
import fr.gouv.finances.dgfip.xemelios.data.impl.sqlconfig.TPersistenceConfig;
import fr.gouv.finances.dgfip.xemelios.data.impl.sqlconfig.TSqlIndex;
import fr.gouv.finances.dgfip.xemelios.data.impl.sqlconfig.TTable;
import fr.gouv.finances.cp.xemelios.data.impl.mysql.PayePatch32;
import fr.gouv.finances.cp.xemelios.data.impl.mysql.RepositoryPatch;
import fr.gouv.finances.cp.xemelios.data.impl.mysql.serialization.DataResultSetWrapperModel;
import fr.gouv.finances.cp.xemelios.data.impl.mysql.serialization.DrswParser;
import fr.gouv.finances.cp.xemelios.data.impl.mysql.serialization.ExportJobImpl;
import fr.gouv.finances.cp.xemelios.data.ui.DlgPatchProgress;
import fr.gouv.finances.dgfip.xemelios.export.ConfigModel;
import fr.gouv.finances.dgfip.xemelios.data.ExportableData;
import fr.gouv.finances.dgfip.xemelios.utils.TextWriter;
import fr.gouv.finances.dgfip.xemelios.utils.XmlUtils;


import fr.gouv.finances.dgfip.xemelios.utils.LongueurMax;
import fr.gouv.finances.dgfip.xemelios.auth.UnauthorizedException;
import fr.gouv.finances.dgfip.xemelios.common.config.PersonnalConfigParser;
import fr.gouv.finances.dgfip.xemelios.data.impl.pool.PoolManager;
import fr.gouv.finances.dgfip.xemelios.export.ExportJob;
import java.io.OutputStreamWriter;
import java.lang.reflect.Field;
import java.net.InetAddress;
import java.net.UnknownHostException;
import java.util.Comparator;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;

/**
 * @author chm
 */
public class MySqlDataLayer extends AbstractDataImpl {

    static final transient String PARAM_TABLE_NAME = "PARAMETERS";
    /**
     * The system property designed by this name must reference a fully qualified file name
     */
    public static final transient String PROP_CONFIG_FILE_NAME = "xemelios.mysql.config.filename";
    public static final transient String PROP_MIN_POOL_SIZE = "pool-size-mini";
    private static Logger logger = Logger.getLogger(MySqlDataLayer.class);
    private static final SimpleDateFormat mySqlDateFormat = new SimpleDateFormat("yyyy-MM-dd");
    public static final transient String LAYER_NAME = "mysql";
    private ArrayList<TDocument> checkedConfigs = null;
    private ArrayList<Connection> availableConnections = null,  usedConnections = null;
    private PropertiesExpansion props = null;


    static {
        DataLayerManager.regiterDataImpl(LAYER_NAME, MySqlDataLayer.class);
    }
    private TreeSet<String> createdTables;		// TreeSet has log(n) cost for contains()
    private SAXParserFactory saxFactory = null;
    private XPathFactory xPathFactory = null;
    private DocumentBuilderFactory domFactory = null;
    private int minPoolSize = 1;
    private Object poolLocker = new Object();
    private Hashtable<Thread, String> warnings;
    private PatchProgress progress;

    public MySqlDataLayer() throws DataConfigurationException {
        logger.info("instancing");
//        Exception ex = new Exception();
//        logger.error("trace", ex);
        createdTables = new TreeSet<String>();
        warnings = new Hashtable<Thread, String>();
        initConnections();
        // since 3.2, we add a new Table with application parameters
        // so create ir if necessary
        checkParamTable();
//        dbf = DocumentBuilderFactory.newInstance();
        saxFactory = SAXParserFactory.newInstance();
        saxFactory.setNamespaceAware(true);
        try {
            xPathFactory = XPathFactory.newInstance(XPathFactory.DEFAULT_OBJECT_MODEL_URI);
        } catch (Throwable t) {
            xPathFactory = XPathFactory.newInstance();
        }
        xPathFactory.setXPathFunctionResolver(new FunctionResolver());
        domFactory = DocumentBuilderFactory.newInstance();
        domFactory.setNamespaceAware(true);
        applyPatches();
    }

    public String getLayerName() {
        return LAYER_NAME;
    }

    @Override
    public void reset() {
        super.reset();
        checkedConfigs.clear();
        createdTables.clear();
    }

    public PatchProgress getPatchProgress() {
        if (progress == null) {
            progress = new DlgPatchProgress();
            progress.setVisible(true);
        }
        return progress;
    }

    public void killPatchProgress() {
        if (progress != null) {
            progress.setVisible(false);
            progress.dispose();
            progress = null;
        }
    }

    public boolean importElement(DocumentModel dm, EtatModel em, Pair codeBudget, Pair collectivite, String sourceFileName, String elementName, String strElement, String encoding, XemeliosUser user) throws DataAccessException, DataConfigurationException, UnauthorizedException {
        if(!user.hasRole(XemeliosUser.ROLE_IMPORT)) throw new UnauthorizedException("Vous n'êtes pas autorisé à importer des fichiers");
        if(!user.hasDocument(dm.getId())) throw new UnauthorizedException("Vous n'êtes pas autorisé à importer des "+dm.getTitre());
        if(!user.hasCollectivite(collectivite.key,dm)) throw new UnauthorizedException("Vous n'êtes pas autorisé à importer des "+dm.getTitre()+" pour cette collectivité "+collectivite.libelle!=null?"("+collectivite.libelle+")":"");
        //logger.debug("importing element\n"+strElement);
        TDocument pc = getPersistenceConfig(dm, user).getLayer("mysql").getDocument(dm.getId());
        TEtat sec = pc.getEtat(em.getId());
        Connection con = null;
        PreparedStatement ps = null;
        IndexerHandler ih = null;
        String strWarnings = null;
        try {
            con = getConnection();
            ensureTablesExists(pc, sec, codeBudget, collectivite, con);

            // inserting document
            StringBuilder sb = new StringBuilder();
            sb.append("INSERT INTO ").append(sec.getDocumentTable().getBaseName()).append(" (BUDGET,COLLECTIVITE, INITIAL_DOC_NAME, DOC_ID, DOC, ENCODING) VALUES (?,?,?,?,?,?)");
            ps = con.prepareStatement(sb.toString());
            ps.setString(1, codeBudget.key);
            ps.setString(2, collectivite.key);
            ps.setString(3, sourceFileName);
            ps.setString(4, elementName);
            String strBuffer = strElement;
            byte[] buffer = strBuffer.getBytes(encoding);
            InputStream is = new ByteArrayInputStream(buffer);
            ps.setBinaryStream(5, is, buffer.length);
            ps.setString(6, encoding);
            ps.executeUpdate();
            ps.close();
            // indexing
            ih = new IndexerHandler(sec, codeBudget.key, collectivite.key, elementName, con, dm.getNamespaces());
            ih.reset();
            SAXParser parser = saxFactory.newSAXParser();
            parser.parse(new ByteArrayInputStream(strElement.getBytes(encoding)), ih);
            ih.reset();
            strWarnings = ih.getWarnings();
            ih = null;
        } catch (SQLException sqlEx) {
            sqlEx.printStackTrace();
            throw new DataAccessException(sqlEx);
        } catch (SAXException saxEx) {
            saxEx.printStackTrace();
            throw new DataAccessException(ih.getException() != null ? ih.getException() : saxEx);
        } catch (ParserConfigurationException pcEx) {
            pcEx.printStackTrace();
            throw new DataConfigurationException(pcEx);
        } catch (IOException ioEx) {
            ioEx.printStackTrace();
            throw new DataAccessException(ih.getException() != null ? ih.getException() : ioEx);
        } catch (Throwable t) {
            t.printStackTrace();
        } finally {
            if (con != null) {
                releaseConnection(con);
            }
        }
        if (strWarnings != null) {
            warnings.put(Thread.currentThread(), strWarnings);
        } else {
            warnings.remove(Thread.currentThread());
        }
        return (strWarnings == null);
    }

    public boolean saveRepository(DocumentModel dm, Pair collectivite, Document repository, Pair nomenclatureReference, XemeliosUser user) throws DataConfigurationException, DataAccessException, UnauthorizedException {
        if(!user.hasRole(XemeliosUser.ROLE_IMPORT)) throw new UnauthorizedException("Vous n'êtes pas autorisé à importer des fichiers");
        if(!user.hasDocument(dm.getId())) throw new UnauthorizedException("Vous n'êtes pas autorisé à importer des "+dm.getTitre());
        if(!user.hasCollectivite(collectivite.key,dm)) throw new UnauthorizedException("Vous n'êtes pas autorisé à importer des "+dm.getTitre()+" pour cette collectivité "+collectivite.libelle!=null?"("+collectivite.libelle+")":"");
        TDocument pc = getPersistenceConfig(dm, user).getLayer("mysql").getDocument(dm.getId());
        if (pc.getRepositoryTable() == null) {
            return false;
        }
        Connection con = null;
        PreparedStatement ps = null;
        boolean bRet = false;
        UPDATES_MODES updateMode;
        try {
            con = getConnection();
            String repositoryEncoding = repository.getXmlEncoding();
            String strRepository = TextWriter.toString(repository, repositoryEncoding);
            StringBuilder sb = new StringBuilder();
            if (!doesRepositoryExist(pc, collectivite.key, con, (nomenclatureReference != null ? nomenclatureReference.key : null))) {
                sb.append("INSERT INTO ").append(pc.getRepositoryTable().getBaseName()).append(" (LIBELLE,REPOSITORY,ENCODING,COLLECTIVITE,ID) VALUES (?,?,?,?,?)");
                updateMode=UPDATES_MODES.INSERT;
            } else {
                sb.append("UPDATE ").append(pc.getRepositoryTable().getBaseName()).append(" SET LIBELLE=?, REPOSITORY=?, ENCODING=? WHERE COLLECTIVITE=? AND ID");
                updateMode=UPDATES_MODES.UPDATE;
                if(nomenclatureReference ==null || nomenclatureReference.key==null) sb.append(" IS NULL");
                else sb.append("=?");
            }
            ps = con.prepareStatement(sb.toString());
            byte[] buffer = strRepository.getBytes(repositoryEncoding);
            ByteArrayInputStream is = new ByteArrayInputStream(buffer);
            if (nomenclatureReference != null && nomenclatureReference.libelle != null) {
                ps.setString(1, nomenclatureReference.libelle);
            } else {
                ps.setNull(1, Types.VARCHAR);
            }
            ps.setBinaryStream(2,is,buffer.length);
            ps.setString(3,repositoryEncoding);
            ps.setString(4,collectivite.key);
            if(nomenclatureReference!=null && nomenclatureReference.key!=null){
            	ps.setString(5,nomenclatureReference.key);
            } else if(updateMode==UPDATES_MODES.INSERT) {
            	ps.setNull(5,Types.VARCHAR);
            }
            int modifiedRows = ps.executeUpdate();
            bRet=(modifiedRows==1);
        } catch(SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } catch (UnsupportedEncodingException ueEx) {
            throw new DataConfigurationException("The char encoding of this file is unknown", ueEx);
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (Throwable t) {
                    logger.info("ps closed !", t);
                }
            }
            if (con != null) {
                releaseConnection(con);
            }
        }
        return bRet;
    }

    public void registerBudgetCollectivite(DocumentModel dm, Pair codeBudget, Pair collectivite, Pair[] parentCollectivites, String docName, XemeliosUser user) throws DataConfigurationException, DataAccessException, UnauthorizedException {
        if(!user.hasRole(XemeliosUser.ROLE_IMPORT)) throw new UnauthorizedException("Vous n'êtes pas autorisé à importer des fichiers");
        if(!user.hasDocument(dm.getId())) throw new UnauthorizedException("Vous n'êtes pas autorisé à importer des "+dm.getTitre());
        if(!user.hasCollectivite(collectivite.key,dm)) throw new UnauthorizedException("Vous n'êtes pas autorisé à importer des "+dm.getTitre()+" pour cette collectivité "+collectivite.libelle!=null?"("+collectivite.libelle+")":"");
        TDocument pc = getPersistenceConfig(dm, user).getLayer("mysql").getDocument(dm.getId());
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = getConnection();
            for (TEtat sec : pc.getEtats()) {
                ensureTablesExists(pc, sec, codeBudget, collectivite, con);
            }
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT 1 FROM ").append(pc.getListBcTable().getBaseName()).append(" WHERE BUDGET=? AND COLLECTIVITE=? AND INITIAL_DOC_NAME=?");
            ps = con.prepareStatement(sb.toString());
            ps.setString(1, codeBudget.key);
            ps.setString(2, collectivite.key);
            ps.setString(3, docName);
            ResultSet rs = ps.executeQuery();
            if (!rs.next()) {
                sb = new StringBuilder();
                StringBuffer binds = new StringBuffer();
                sb.append("INSERT INTO ").append(pc.getListBcTable().getBaseName()).append(" (BUDGET,COLLECTIVITE,INITIAL_DOC_NAME,BUDGET_LIB,COLLECTIVITE_LIB");
                binds.append(" VALUES (?,?,?,?,?");
                for(int i=0;i<parentCollectivites.length;i++) {
                    Pair p = parentCollectivites[i];
                    if(p!=null && p.key!=null && p.libelle!=null) {
                        sb.append(",PARENT_COLL").append(i+1).append(",PARENT_COLL").append(i+1).append("_LIB");
                        binds.append(",?,?");
                    }
                }
                binds.append(")");
                sb.append(")").append(binds.toString());
                ps = con.prepareStatement((sb.toString()));
                ps.setString(1, codeBudget.key);
                ps.setString(2, collectivite.key);
                ps.setString(3, docName);
                ps.setString(4, codeBudget.libelle);
                ps.setString(5, collectivite.libelle);
                int i=6;
                for(Pair p:parentCollectivites) {
                    if(p!=null && p.key!=null && p.libelle!=null) {
                        ps.setString(i++,p.key);
                        ps.setString(i++,p.libelle);
                    }
                }
                ps.executeUpdate();
            }
        } catch (SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (Throwable t) {
                    logger.info("ps closed !", t);
                }
            }
            if (con != null) {
                releaseConnection(con);
            }
        }
    }

    public void saveSpecialKeys(DocumentModel dm, Pair budget, Pair collectivite, String docName, Pair sk1, Pair sk2, Pair sk3, XemeliosUser user) throws DataConfigurationException, DataAccessException, UnauthorizedException {
        if(!user.hasRole(XemeliosUser.ROLE_IMPORT)) throw new UnauthorizedException("Vous n'êtes pas autorisé à importer des fichiers");
        if(!user.hasDocument(dm.getId())) throw new UnauthorizedException("Vous n'êtes pas autorisé à importer des "+dm.getTitre());
        if(!user.hasCollectivite(collectivite.key,dm)) throw new UnauthorizedException("Vous n'êtes pas autorisé à importer des "+dm.getTitre()+" pour cette collectivité "+collectivite.libelle!=null?"("+collectivite.libelle+")":"");
        if (sk1 == null && sk2 == null && sk3 == null) {
            return;
        }
        TDocument pc = getPersistenceConfig(dm, user).getLayer("mysql").getDocument(dm.getId());
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = getConnection();
            for (TEtat sec : pc.getEtats()) {
                ensureTablesExists(pc, sec, budget, collectivite, con);
            }
            StringBuilder sb = new StringBuilder();
            sb.append("INSERT INTO ").append(pc.getSpecialKeyTable().getBaseName()).append(" (BUDGET,COLLECTIVITE,INITIAL_DOC_NAME,KEY1,LIB1,KEY2,LIB2,KEY3,LIB3) VALUES(?,?,?,?,?,?,?,?,?)");
            ps = con.prepareStatement(sb.toString());
            ps.setString(1, budget.key);
            ps.setString(2, collectivite.key);
            ps.setString(3, docName);
            if (sk1 != null) {
                ps.setString(4, sk1.key);
                ps.setString(5, sk1.libelle);
            } else {
                ps.setNull(4, Types.VARCHAR);
                ps.setNull(5, Types.VARCHAR);
            }
            if (sk2 != null) {
                ps.setString(6, sk2.key);
                ps.setString(7, sk2.libelle);
            } else {
                ps.setNull(6, Types.VARCHAR);
                ps.setNull(7, Types.VARCHAR);
            }
            if (sk3 != null) {
                ps.setString(8, sk3.key);
                ps.setString(9, sk3.libelle);
            } else {
                ps.setNull(8, Types.VARCHAR);
                ps.setNull(9, Types.VARCHAR);
            }
            ps.executeUpdate();
        } catch (SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (Throwable t) {
                    logger.info("ps closed !", t);
                }
            }
            if (con != null) {
                releaseConnection(con);
            }
        }
    }

    public boolean canSearch(DocumentModel dm, EtatModel em, XemeliosUser user) throws DataConfigurationException, DataAccessException, UnauthorizedException {
        if(!user.hasRole(XemeliosUser.ROLE_SEARCH)) throw new UnauthorizedException("Vous n'êtes pas autorisé à faire des recherches");
        if(!user.hasDocument(dm.getId())) throw new UnauthorizedException("Vous n'êtes pas autorisé à rechercher des "+em.getTitre());
        TDocument pc = getPersistenceConfig(dm, user).getLayer("mysql").getDocument(dm.getId());
        TEtat sec = pc.getEtat(em.getId());
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = getConnection();
            ResultSet rs = null;
            try {
                rs = con.createStatement().executeQuery("SELECT 1 FROM " + sec.getDocumentTable().getBaseName() + " LIMIT 1");
            } catch (SQLException sqlEx) {
                // table doesn't exists
                return false;
            }
            if (rs != null) {
                return rs.next();
            }
            return false;
        } catch (SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (Throwable t) {
                    logger.info("ps closed !", t);
                }
            }
            if (con != null) {
                releaseConnection(con);
            }
        }
    }

    public Vector<Pair> getBudgets(DocumentModel dm, Pair collectivite, XemeliosUser user) throws DataConfigurationException, DataAccessException, UnauthorizedException {
        if(!user.hasRole(XemeliosUser.ROLE_SEARCH)) throw new UnauthorizedException("Vous n'êtes pas autorisé à faire des recherches");
        if(!user.hasDocument(dm.getId())) throw new UnauthorizedException("Vous n'êtes pas autorisé à rechercher des "+dm.getTitre());
        if(!user.hasCollectivite(collectivite.key,dm)) throw new UnauthorizedException("Vous n'êtes pas autorisé à rechercher des "+dm.getTitre()+" pour cette collectivité "+collectivite.libelle!=null?"("+collectivite.libelle+")":"");
        Vector<Pair> vRet = new Vector<Pair>();
        if (dm == null || collectivite == null) {
            return vRet;
        }
        TDocument pc = getPersistenceConfig(dm, user).getLayer("mysql").getDocument(dm.getId());
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = getConnection();
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT DISTINCT BUDGET, BUDGET_LIB FROM ").append(pc.getListBcTable().getBaseName()).append(" WHERE COLLECTIVITE=? ORDER BY BUDGET_LIB");
            ps = con.prepareStatement(sb.toString());
            ps.setString(1, collectivite.key);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                vRet.add(new Pair(rs.getString(1), rs.getString(2)));
            }
        } catch (SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (Throwable t) {
                    logger.info("ps closed !", t);
                }
            }
            if (con != null) {
                releaseConnection(con);
            }
        }
        return vRet;
    }
    public Vector<Pair> getParentCollectivites(DocumentModel dm, int level,Pair[] otherParents, XemeliosUser user) throws DataConfigurationException, DataAccessException, UnauthorizedException {
        if(!user.hasRole(XemeliosUser.ROLE_SEARCH)) throw new UnauthorizedException("Vous n'êtes pas autorisé à faire des recherches");
        if(!user.hasDocument(dm.getId())) throw new UnauthorizedException("Vous n'êtes pas autorisé à rechercher des "+dm.getTitre());
        TreeSet<Pair> vRet = new TreeSet<Pair>(new Comparator<Pair>() {
            public int compare(Pair o1, Pair o2) {
                return o1.libelle.compareTo(o2.libelle);
            }
        });
        TDocument pc = getPersistenceConfig(dm, user).getLayer("mysql").getDocument(dm.getId());
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = getConnection();
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT DISTINCT COLLECTIVITE, PARENT_COLL").append(level).append(", PARENT_COLL").append(level).append("_LIB FROM ").append(pc.getListBcTable().getBaseName());
            if(otherParents!=null && otherParents.length>0) {
                sb.append(" WHERE ");
                for(int i=0;i<otherParents.length;i++) {
                    Pair p = otherParents[i];
                    if(p!=null) {
                        sb.append("PARENT_COLL").append(level+1+i).append("=? ");
                        sb.append("AND ");
                    }
                }
            }
            if(sb.toString().endsWith("AND ")) {
                sb.delete(sb.length()-4, sb.length());
            }
            if(sb.toString().endsWith(" WHERE ")) {
                sb.delete(sb.length()-7,sb.length());
            }
            sb.append(" ORDER BY PARENT_COLL").append(level).append("_LIB ASC");
            ps = con.prepareStatement(sb.toString());
            if(otherParents!=null && otherParents.length>0) {
                int j = 1;
                for(int i=0;i<otherParents.length;i++) {
                    if(otherParents[i]!=null)
                        ps.setString(j++,otherParents[i].key);
                }
            }
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Pair parentColl = new Pair(rs.getString(2), rs.getString(3));
                if(user.hasCollectivite(rs.getString(1),dm))
                    vRet.add(parentColl);
            }
            rs.close();
        } catch (SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } finally {
            if(ps!=null) {
                try { ps.close(); } catch(Exception ex) {}
            }
            if (con != null) {
                releaseConnection(con);
            }
        }
        Vector<Pair> v = new Vector<Pair>();
        v.addAll(vRet);
        return v;

    }
    public int getCollectivitesCount(DocumentModel dm, XemeliosUser user) throws DataConfigurationException, DataAccessException, UnauthorizedException {
        if(!user.hasRole(XemeliosUser.ROLE_SEARCH)) throw new UnauthorizedException("Vous n'êtes pas autorisé à faire des recherches");
        if(!user.hasDocument(dm.getId())) throw new UnauthorizedException("Vous n'êtes pas autorisé à rechercher des "+dm.getTitre());
        int ret = 0;
        TDocument pc = getPersistenceConfig(dm, user).getLayer("mysql").getDocument(dm.getId());
        Connection con = null;
        try {
            con = getConnection();
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT distinct(COLLECTIVITE) FROM ").append(pc.getListBcTable().getBaseName());
            ResultSet rs = con.createStatement().executeQuery(sb.toString());
            while (rs.next()) {
                String collectivite = rs.getString(1);
                if(user.hasCollectivite(collectivite,dm))
                    ret++;
            }
            rs.close();
        } catch (SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } finally {
            if (con != null) {
                releaseConnection(con);
            }
        }
        return ret;
    }
    public Vector<Pair> getCollectivites(DocumentModel dm, Pair[] parents, XemeliosUser user) throws DataConfigurationException, DataAccessException, UnauthorizedException {
        if(!user.hasRole(XemeliosUser.ROLE_SEARCH)) throw new UnauthorizedException("Vous n'êtes pas autorisé à faire des recherches");
        if(!user.hasDocument(dm.getId())) throw new UnauthorizedException("Vous n'êtes pas autorisé à rechercher des "+dm.getTitre());
        Vector<Pair> vRet = new Vector<Pair>();
        TDocument pc = getPersistenceConfig(dm, user).getLayer("mysql").getDocument(dm.getId());
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = getConnection();
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT DISTINCT COLLECTIVITE, COLLECTIVITE_LIB FROM ").append(pc.getListBcTable().getBaseName());
            if(parents!=null && parents.length>0 && parents[0]!=null) {
                sb.append(" WHERE ");
                for(int i=0;i<parents.length;i++) {
                    if(parents[i]!=null) {
                        sb.append("PARENT_COLL").append(i+1).append("=? AND ");

                    }
                }
            }
            if(sb.toString().endsWith("AND ")) {
                sb.delete(sb.length()-4, sb.length());
            }
            if(sb.toString().endsWith(" WHERE ")) {
                sb.delete(sb.length()-7,sb.length());
            }
            sb.append(" ORDER BY COLLECTIVITE_LIB");
            ps = con.prepareStatement(sb.toString());
            if(parents!=null && parents.length>0 && parents[0]!=null) {
                for(int i=0;i<parents.length;i++) {
                    if(parents[i]!=null)
                        ps.setString(i+1,parents[i].key);
                }
            }
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Pair collectivite = new Pair(rs.getString(1), rs.getString(2));
                if(user.hasCollectivite(collectivite.key,dm))
                    vRet.add(collectivite);
            }
            rs.close();
        } catch (SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } finally {
            if(ps!=null) {
                try { ps.close(); } catch(SQLException ex) {}
            }
            if (con != null) {
                releaseConnection(con);
            }
        }
        return vRet;
    }

    public Vector<Pair> getSpecialKeys1(DocumentModel dm, Pair collectivite, Pair budget, boolean distinct, XemeliosUser user) throws DataConfigurationException, DataAccessException, UnauthorizedException {
        if(!user.hasRole(XemeliosUser.ROLE_SEARCH)) throw new UnauthorizedException("Vous n'êtes pas autorisé à faire des recherches");
        if(!user.hasDocument(dm.getId())) throw new UnauthorizedException("Vous n'êtes pas autorisé à rechercher des "+dm.getTitre());
        if(!user.hasCollectivite(collectivite.key,dm)) throw new UnauthorizedException("Vous n'êtes pas autorisé à rechercher des "+dm.getTitre()+" pour cette collectivité "+collectivite.libelle!=null?"("+collectivite.libelle+")":"");
        Vector<Pair> vRet = new Vector<Pair>();
        TDocument pc = getPersistenceConfig(dm, user).getLayer("mysql").getDocument(dm.getId());
        Connection con = null;
        try {
            con = getConnection();
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT ").append(distinct ? "DISTINCT " : " ").append("KEY1,LIB1 FROM ").append(pc.getSpecialKeyTable().getBaseName()).append(" WHERE COLLECTIVITE=? AND BUDGET=?");
            PreparedStatement ps = con.prepareStatement(sb.toString());
            ps.setString(1, collectivite.key);
            ps.setString(2, budget.key);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                vRet.add(new Pair(rs.getString(1), rs.getString(2)));
            }
        } catch (SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } finally {
            if (con != null) {
                releaseConnection(con);
            }
        }
        return vRet;
    }

    public Vector<Pair> getSpecialKeys2(DocumentModel dm, Pair collectivite, Pair budget, Pair key1, boolean distinct, XemeliosUser user) throws DataConfigurationException, DataAccessException, UnauthorizedException {
        if(!user.hasRole(XemeliosUser.ROLE_SEARCH)) throw new UnauthorizedException("Vous n'êtes pas autorisé à faire des recherches");
        if(!user.hasDocument(dm.getId())) throw new UnauthorizedException("Vous n'êtes pas autorisé à rechercher des "+dm.getTitre());
        if(!user.hasCollectivite(collectivite.key,dm)) throw new UnauthorizedException("Vous n'êtes pas autorisé à rechercher des "+dm.getTitre()+" pour cette collectivité "+collectivite.libelle!=null?"("+collectivite.libelle+")":"");
        Vector<Pair> vRet = new Vector<Pair>();
        TDocument pc = getPersistenceConfig(dm, user).getLayer("mysql").getDocument(dm.getId());
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = getConnection();
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT ").append(distinct ? "DISTINCT " : " ").append("KEY2,LIB2 FROM ").append(pc.getSpecialKeyTable().getBaseName()).append(" WHERE KEY1=? AND COLLECTIVITE=? AND BUDGET=? AND KEY2 is not null");
            ps = con.prepareStatement(sb.toString());
            ps.setString(1, key1.key);
            ps.setString(2, collectivite.key);
            ps.setString(3, budget.key);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                vRet.add(new Pair(rs.getString(1), rs.getString(2)));
            }
        } catch (SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (Throwable t) {
                    logger.info("ps closed !", t);
                }
            }
            if (con != null) {
                releaseConnection(con);
            }
        }
        return vRet;
    }

    public Vector<Pair> getSpecialKeys3(DocumentModel dm, Pair collectivite, Pair budget, Pair key1, Pair key2, boolean distinct, XemeliosUser user) throws DataConfigurationException, DataAccessException, UnauthorizedException {
        if(!user.hasRole(XemeliosUser.ROLE_SEARCH)) throw new UnauthorizedException("Vous n'êtes pas autorisé à faire des recherches");
        if(!user.hasDocument(dm.getId())) throw new UnauthorizedException("Vous n'êtes pas autorisé à rechercher des "+dm.getTitre());
        if(!user.hasCollectivite(collectivite.key,dm)) throw new UnauthorizedException("Vous n'êtes pas autorisé à rechercher des "+dm.getTitre()+" pour cette collectivité "+collectivite.libelle!=null?"("+collectivite.libelle+")":"");
        Vector<Pair> vRet = new Vector<Pair>();
        TDocument pc = getPersistenceConfig(dm, user).getLayer("mysql").getDocument(dm.getId());
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = getConnection();
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT ").append(distinct ? "DISTINCT " : " ").append("KEY3,LIB3 FROM ").append(pc.getSpecialKeyTable().getBaseName()).append(" WHERE KEY1=? AND KEY2=? AND COLLECTIVITE=? AND BUDGET=?");
            ps = con.prepareStatement(sb.toString());
            ps.setString(1, key1.key);
            ps.setString(2, key2.key);
            ps.setString(3, collectivite.key);
            ps.setString(4, budget.key);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                vRet.add(new Pair(rs.getString(1), rs.getString(2)));
            }
        } catch (SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (Throwable t) {
                    logger.info("ps closed !", t);
                }
            }
            if (con != null) {
                releaseConnection(con);
            }
        }
        return vRet;
    }

    public Collection<Pair> queryRepository(DocumentModel dm, Pair collectivite, RecherchePaireModel rpm, XemeliosUser user) throws DataConfigurationException, DataAccessException, UnauthorizedException {
        if(!user.hasRole(XemeliosUser.ROLE_SEARCH)) throw new UnauthorizedException("Vous n'êtes pas autorisé à faire des recherches");
        if(!user.hasDocument(dm.getId())) throw new UnauthorizedException("Vous n'êtes pas autorisé à rechercher des "+dm.getTitre());
        if(!user.hasCollectivite(collectivite.key,dm)) throw new UnauthorizedException("Vous n'êtes pas autorisé à rechercher des "+dm.getTitre()+" pour cette collectivité "+collectivite.libelle!=null?"("+collectivite.libelle+")":"");
        Vector<Pair> vRet = new Vector<Pair>();
        TDocument pc = getPersistenceConfig(dm, user).getLayer("mysql").getDocument(dm.getId());
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = getConnection();
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT REPOSITORY FROM ").append(pc.getRepositoryTable().getBaseName()).append(" WHERE COLLECTIVITE=?");
            ps = con.prepareStatement(sb.toString());
            ps.setString(1, collectivite.key);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                byte[] buff = new byte[512];
                ByteArrayOutputStream baos = new ByteArrayOutputStream();
                InputStream is = rs.getBinaryStream(1);
                int length = is.read(buff);
                while (length > 0) {
                    baos.write(buff, 0, length);
                    length = is.read(buff);
                }
                DocumentBuilder domBuilder = domFactory.newDocumentBuilder();
                Document repository = domBuilder.parse(new ByteArrayInputStream(baos.toByteArray()));
                XPath xp = xPathFactory.newXPath();
                xp.setNamespaceContext(dm.getNamespaces());
                DomPath dpCode = rpm.getCodePath().getDomPath(dm.getNamespaces());
                DomPath dpLibelle = rpm.getLibellePath().getDomPath(dm.getNamespaces());
                Object o = xp.evaluate(rpm.getPath().getPath(), repository, XPathConstants.NODESET);
                if(o instanceof NodeList) {
                    NodeList nl = (NodeList)o;
                    for (int i = 0; i < nl.getLength(); i++) {
                        Node node = nl.item(i);
                        String code = (String) dpCode.getValue(node, false);
                        String libelle = (String) dpLibelle.getValue(node, true);
                        vRet.add(new Pair(code, libelle));
                    }
                } else {
                    for(Node node: (List<Node>)o) {
                        String code = (String) dpCode.getValue(node, false);
                        String libelle = (String) dpLibelle.getValue(node, true);
                        vRet.add(new Pair(code, libelle));
                    }
                }
            }
        } catch (SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } catch (IOException ioEx) {
            throw new DataAccessException(ioEx);
        } catch (ParserConfigurationException pcEx) {
            throw new DataConfigurationException(pcEx);
        } catch (SAXException saxEx) {
            throw new DataAccessException(saxEx);
        } catch (XPathExpressionException xpEx) {
            throw new DataConfigurationException(xpEx);
        } catch (InvalidPathExpressionException ipeEx) {
            throw new DataConfigurationException(ipeEx);
        } catch (PathNotFoundException pnfEx) {
            throw new DataConfigurationException(pnfEx);
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (Throwable t) {
                    logger.info("ps closed !", t);
                }
            }
            if (con != null) {
                releaseConnection(con);
            }
        }
        return vRet;
    }

    public Collection<Pair> queryRepository(DocumentModel dm, Pair collectivite, RecherchePaireModel rpm, Pair refNomenclature, XemeliosUser user) throws DataConfigurationException, DataAccessException, UnauthorizedException {
        if(!user.hasRole(XemeliosUser.ROLE_SEARCH)) throw new UnauthorizedException("Vous n'êtes pas autorisé à faire des recherches");
        if(!user.hasDocument(dm.getId())) throw new UnauthorizedException("Vous n'êtes pas autorisé à rechercher des "+dm.getTitre());
        if(!user.hasCollectivite(collectivite.key,dm)) throw new UnauthorizedException("Vous n'êtes pas autorisé à rechercher des "+dm.getTitre()+" pour cette collectivité "+collectivite.libelle!=null?"("+collectivite.libelle+")":"");
        Vector<Pair> vRet = new Vector<Pair>();
        TDocument pc = getPersistenceConfig(dm, user).getLayer("mysql").getDocument(dm.getId());
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = getConnection();
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT REPOSITORY FROM ").append(pc.getRepositoryTable().getBaseName()).append(" WHERE COLLECTIVITE=? AND ID=?");
            ps = con.prepareStatement(sb.toString());
            ps.setString(1, collectivite.key);
            if (refNomenclature != null && refNomenclature.key != null) {
                ps.setString(2, refNomenclature.key);
            } else {
                ps.setNull(2, Types.VARCHAR);
            }
            ResultSet rs = ps.executeQuery();
            if(rs.next()) {
                byte[] buff = new byte[512];
                ByteArrayOutputStream baos = new ByteArrayOutputStream();
                InputStream is = rs.getBinaryStream(1);
                int length = is.read(buff);
                while (length > 0) {
                    baos.write(buff, 0, length);
                    length = is.read(buff);
                }
                DocumentBuilder domBuilder = domFactory.newDocumentBuilder();
                Document repository = domBuilder.parse(new ByteArrayInputStream(baos.toByteArray()));
                XPath xp = xPathFactory.newXPath();
                xp.setNamespaceContext(dm.getNamespaces());
                NodeList nl = (NodeList) xp.evaluate(rpm.getPath().getPath(), repository, XPathConstants.NODESET);
                DomPath dpCode = rpm.getCodePath().getDomPath(dm.getNamespaces());
                DomPath dpLibelle = rpm.getLibellePath().getDomPath(dm.getNamespaces());
                for (int i = 0; i < nl.getLength(); i++) {
                    Node node = nl.item(i);
                    String code = (String) dpCode.getValue(node, false);
                    String libelle = (String) dpLibelle.getValue(node, true);
                    vRet.add(new Pair(code, libelle));
                }
            }
        } catch (SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } catch (IOException ioEx) {
            throw new DataAccessException(ioEx);
        } catch (ParserConfigurationException pcEx) {
            throw new DataConfigurationException(pcEx);
        } catch (SAXException saxEx) {
            throw new DataAccessException(saxEx);
        } catch (XPathExpressionException xpEx) {
            throw new DataConfigurationException(xpEx);
        } catch (InvalidPathExpressionException ipeEx) {
            throw new DataConfigurationException(ipeEx);
        } catch (PathNotFoundException pnfEx) {
            throw new DataConfigurationException(pnfEx);
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (Throwable t) {
                    logger.info("ps closed !", t);
                }
            }
            if (con != null) {
                releaseConnection(con);
            }
        }
        return vRet;
    }

    public boolean isDocumentExists(DocumentModel dm, Pair budget, Pair collectivite, String docName, XemeliosUser user) throws DataConfigurationException, DataAccessException, UnauthorizedException {
        if(! (user.hasRole(XemeliosUser.ROLE_IMPORT) || user.hasRole(XemeliosUser.ROLE_SEARCH)) ) throw new UnauthorizedException("Vous n'êtes pas autorisé à importer des fichiers");
        if(!user.hasDocument(dm.getId())) throw new UnauthorizedException("Vous n'êtes pas autorisé à importer des "+dm.getTitre());
        if(!user.hasCollectivite(collectivite.key,dm)) throw new UnauthorizedException("Vous n'êtes pas autorisé à importer des "+dm.getTitre()+" pour cette collectivité "+collectivite.libelle!=null?"("+collectivite.libelle+")":"");
        TDocument pc = getPersistenceConfig(dm, user).getLayer("mysql").getDocument(dm.getId());
        Connection con = null;
        boolean ret = false;
        try {
            con = getConnection();
            for (TEtat sec : pc.getEtats()) {
                ensureTablesExists(pc, sec, budget, collectivite, con);
                ret |= documentExists(pc, budget, collectivite, docName, con);
                if (ret) {
                    return true;
                }
            }
        } catch (SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } finally {
            if (con != null) {
                releaseConnection(con);
            }
        }
        return ret;
    }

    public void removeDocument(DocumentModel dm, Pair budget, Pair collectivite, String docName, XemeliosUser user) throws DataConfigurationException, DataAccessException, UnauthorizedException {
        if(!user.hasRole(XemeliosUser.ROLE_CLEAN)) throw new UnauthorizedException("Vous n'êtes pas autorisé à supprimer des données");
        if(!user.hasDocument(dm.getId())) throw new UnauthorizedException("Vous n'êtes pas autorisé à supprimer des "+dm.getTitre());
        if(!user.hasCollectivite(collectivite.key,dm)) throw new UnauthorizedException("Vous n'êtes pas autorisé à supprimer des "+dm.getTitre()+" pour cette collectivité "+collectivite.libelle!=null?"("+collectivite.libelle+")":"");
        TDocument pc = getPersistenceConfig(dm, user).getLayer("mysql").getDocument(dm.getId());
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = getConnection();
            for (TEtat sec : pc.getEtats()) {
                ensureTablesExists(pc, sec, budget, collectivite, con);
                StringBuilder sql = null;
                // delete from indexes
                sql = new StringBuilder();
                // bug 283
                sql.append("delete from ");
                for (TTable sti : sec.getIndexTables()) {
                    sql.append("\n").append(sti.getBaseName()).append(",");
                }
                sql.deleteCharAt(sql.length() - 1);
                String docTableName = sec.getDocumentTable().getBaseName();
                sql.append("\nusing ").append(docTableName);
                for (TTable sti : sec.getIndexTables()) {
                    String baseName = sti.getBaseName();
                    sql.append("\nLEFT OUTER JOIN ").append(baseName).append(" ON (").append(baseName).append(".COLLECTIVITE=").append(docTableName).append(".COLLECTIVITE AND ").append(baseName).append(".BUDGET=").append(docTableName).append(".BUDGET AND ").append(baseName).append(".DOC_ID=").append(docTableName).append(".DOC_ID )");
                }
                sql.append("\nwhere ");
                //                for(Enumeration<SqlTableInfo> enumer=sec.getIndexTables();enumer.hasMoreElements();) {
                //                    SqlTableInfo sti = enumer.nextElement();
                //                    String tableName = sti.getBaseName();
                //                    sql.append(tableName).append(".COLLECTIVITE=").append(docTableName).append(".COLLECTIVITE AND ").append(tableName).append(".BUDGET=").append(docTableName).append(".BUDGET AND ").append(tableName).append(".DOC_ID=").append(docTableName).append(".DOC_ID AND ");
                //                }
                sql.append(docTableName).append(".COLLECTIVITE=? AND ").append(docTableName).append(".BUDGET=? AND ").append(docTableName).append(".INITIAL_DOC_NAME=?");
                //logger.debug(sql.toString());
                ps = con.prepareStatement(sql.toString());
                ps.setString(1, collectivite.key);
                ps.setString(2, budget.key);
                ps.setString(3, docName);
                ps.executeUpdate();


                // delete from special keys
                if (pc.getSpecialKeyTable() != null) {
                    sql = new StringBuilder();
                    sql.append("delete from ").append(pc.getSpecialKeyTable().getBaseName()).append(" where COLLECTIVITE=? AND BUDGET=? AND INITIAL_DOC_NAME=?");
                    ps = con.prepareStatement(sql.toString());
                    ps.setString(1, collectivite.key);
                    ps.setString(2, budget.key);
                    ps.setString(3, docName);
                    ps.executeUpdate();
                }
                // delete from documents
                sql = new StringBuilder();
                sql.append("delete from ").append(sec.getDocumentTable().getBaseName()).append(" where COLLECTIVITE=? AND BUDGET=? AND INITIAL_DOC_NAME=?");
                ps = con.prepareStatement(sql.toString());
                ps.setString(1, collectivite.key);
                ps.setString(2, budget.key);
                ps.setString(3, docName);
                ps.executeUpdate();
                // delete from list bud/coll
                sql = new StringBuilder();
                sql.append("delete from ").append(pc.getListBcTable().getBaseName()).append(" where COLLECTIVITE=? AND BUDGET=? AND INITIAL_DOC_NAME=?");
                ps = con.prepareStatement(sql.toString());
                ps.setString(1, collectivite.key);
                ps.setString(2, budget.key);
                ps.setString(3, docName);
                ps.executeUpdate();
            }
        } catch (SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (Throwable t) {
                    logger.info("ps closed !", t);
                }
            }
            if (con != null) {
                releaseConnection(con);
            }
        }
    }

    public String getRepository(DocumentModel dm, Pair collectivite, Pair budget, String idNomenclature, XemeliosUser user) throws DataConfigurationException, DataAccessException, UnauthorizedException {
        if(!user.hasRole(XemeliosUser.ROLE_SEARCH)) throw new UnauthorizedException("Vous n'êtes pas autorisé à faire des recherches");
        if(!user.hasDocument(dm.getId())) throw new UnauthorizedException("Vous n'êtes pas autorisé à rechercher des "+dm.getTitre());
        if(!user.hasCollectivite(collectivite.key,dm)) throw new UnauthorizedException("Vous n'êtes pas autorisé à rechercher des "+dm.getTitre()+" pour cette collectivité "+collectivite.libelle!=null?"("+collectivite.libelle+")":"");
        String ret = null;
        TDocument pc = getPersistenceConfig(dm, user).getLayer("mysql").getDocument(dm.getId());
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = getConnection();
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT REPOSITORY, ENCODING FROM ").append(pc.getRepositoryTable().getBaseName()).append(" WHERE COLLECTIVITE=? AND ");
            if (idNomenclature != null) {
                sb.append("ID=?");
            } else {
                sb.append("ID IS NULL");
            }
            ps = con.prepareStatement(sb.toString());
            ps.setString(1, collectivite.key);
            if (idNomenclature != null) {
                ps.setString(2, idNomenclature);
//            }else{
//            	ps.setNull(2,Types.VARCHAR);
            }
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                String encoding = rs.getString(2);
                byte[] buff = new byte[512];
                ByteArrayOutputStream baos = new ByteArrayOutputStream();
                InputStream is = rs.getBinaryStream(1);
                int length = is.read(buff);
                while (length > 0) {
                    baos.write(buff, 0, length);
                    length = is.read(buff);
                }
                ret = new String(baos.toByteArray(), encoding);
                if ("true".equals(System.getProperty(Constants.SYS_PROP_LOG_DOM_DOC))) {
                    logger.debug(ret);
                }
            }
        } catch (SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } catch (IOException ioEx) {
            throw new DataAccessException(ioEx);
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (Throwable t) {
                    logger.info("ps closed !", t);
                }
            }
            if (con != null) {
                releaseConnection(con);
            }
        }
        return ret;
    }

    public Vector<String> getRepositorys(DocumentModel dm, Pair collectivite, Pair budget, XemeliosUser user) throws DataConfigurationException, DataAccessException, UnauthorizedException {
        if(!user.hasRole(XemeliosUser.ROLE_SEARCH)) throw new UnauthorizedException("Vous n'êtes pas autorisé à faire des recherches");
        if(!user.hasDocument(dm.getId())) throw new UnauthorizedException("Vous n'êtes pas autorisé à rechercher des "+dm.getTitre());
        if(!user.hasCollectivite(collectivite.key,dm)) throw new UnauthorizedException("Vous n'êtes pas autorisé à rechercher des "+dm.getTitre()+" pour cette collectivité "+collectivite.libelle!=null?"("+collectivite.libelle+")":"");
        Vector<String> ret = new Vector<String>();
        TDocument pc = getPersistenceConfig(dm, user).getLayer("mysql").getDocument(dm.getId());
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = getConnection();
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT REPOSITORY, ENCODING FROM ").append(pc.getRepositoryTable().getBaseName()).append(" WHERE COLLECTIVITE=?");
            ps = con.prepareStatement(sb.toString());
            ps.setString(1, collectivite.key);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                String encoding = rs.getString(2);
                byte[] buff = new byte[512];
                ByteArrayOutputStream baos = new ByteArrayOutputStream();
                InputStream is = rs.getBinaryStream(1);
                int length = is.read(buff);
                while (length > 0) {
                    baos.write(buff, 0, length);
                    length = is.read(buff);
                }
                ret.add(new String(baos.toByteArray(), encoding));
            }
        } catch (SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } catch (IOException ioEx) {
            throw new DataAccessException(ioEx);
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (Throwable t) {
                    logger.info("ps closed !", t);
                }
            }
            if (con != null) {
                releaseConnection(con);
            }
        }
        return ret;
    }

    public Vector<String> getListeRepository(DocumentModel dm, Pair collectivite, XemeliosUser user) throws DataConfigurationException, DataAccessException, UnauthorizedException {
        if(!user.hasRole(XemeliosUser.ROLE_SEARCH)) throw new UnauthorizedException("Vous n'êtes pas autorisé à faire des recherches");
        if(!user.hasDocument(dm.getId())) throw new UnauthorizedException("Vous n'êtes pas autorisé à rechercher des "+dm.getTitre());
        if(!user.hasCollectivite(collectivite.key,dm)) throw new UnauthorizedException("Vous n'êtes pas autorisé à rechercher des "+dm.getTitre()+" pour cette collectivité "+collectivite.libelle!=null?"("+collectivite.libelle+")":"");
        Vector<String> ret = new Vector<String>();
        TDocument pc = getPersistenceConfig(dm, user).getLayer("mysql").getDocument(dm.getId());
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = getConnection();
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT REPOSITORY, ENCODING FROM ").append(pc.getRepositoryTable().getBaseName()).append(" WHERE COLLECTIVITE=?");
            ps = con.prepareStatement(sb.toString());
            ps.setString(1, collectivite.key);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                String encoding = rs.getString(2);
                byte[] buff = new byte[512];
                ByteArrayOutputStream baos = new ByteArrayOutputStream();
                InputStream is = rs.getBinaryStream(1);
                int length = is.read(buff);
                while (length > 0) {
                    baos.write(buff, 0, length);
                    length = is.read(buff);
                }
                ret.add(new String(baos.toByteArray(), encoding));
            }
        } catch (SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } catch (IOException ioEx) {
            throw new DataAccessException(ioEx);
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (Throwable t) {
                    logger.info("ps closed !", t);
                }
            }
            if (con != null) {
                releaseConnection(con);
            }
        }
        return ret;
    }

    /**
     * 
     * @param dm : DocumentModel
     * @param collectivite : collectivite pour laquelle on veux les repository
     * @return : Un vector de Pair(code,libelle) de repository
     * @throws DataConfigurationException
     * @throws DataAccessException
     */
    public Vector<Pair> getListeCodeLibelleRepository(DocumentModel dm, Pair collectivite, XemeliosUser user) throws DataConfigurationException, DataAccessException, UnauthorizedException {
        if(!user.hasRole(XemeliosUser.ROLE_SEARCH)) throw new UnauthorizedException("Vous n'êtes pas autorisé à faire des recherches");
        if(!user.hasDocument(dm.getId())) throw new UnauthorizedException("Vous n'êtes pas autorisé à rechercher des "+dm.getTitre());
        if(!user.hasCollectivite(collectivite.key,dm)) throw new UnauthorizedException("Vous n'êtes pas autorisé à rechercher des "+dm.getTitre()+" pour cette collectivité "+collectivite.libelle!=null?"("+collectivite.libelle+")":"");
        Vector<Pair> ret = new Vector<Pair>();
        TDocument pc = getPersistenceConfig(dm, user).getLayer("mysql").getDocument(dm.getId());
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = getConnection();
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT ID, LIBELLE FROM ").append(pc.getRepositoryTable().getBaseName()).append(" WHERE COLLECTIVITE=?");
            ps = con.prepareStatement(sb.toString());
            ps.setString(1, collectivite.key);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                String code = rs.getString(1);
                if (rs.wasNull()) {
                    code = "";
                }
                String libelle = rs.getString(2);
                if (rs.wasNull()) {
                    libelle = "Nomenclature par défaut";
                }
                ret.add(new Pair(code, libelle));
            }
        } catch (SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (Throwable t) {
                    logger.info("ps closed !", t);
                }
            }
            if (con != null) {
                releaseConnection(con);
            }
        }
        return ret;
    }

    public Document getRepositoryAsDom(DocumentModel dm, Pair collectivite, XemeliosUser user) throws DataConfigurationException, DataAccessException, UnauthorizedException {
        return getRepositoryAsDom(dm, collectivite, null, user);
    }

    public Document getRepositoryAsDom(DocumentModel dm, Pair collectivite, String idNomenclature, XemeliosUser user) throws DataConfigurationException, DataAccessException, UnauthorizedException {
        if(!user.hasRole(XemeliosUser.ROLE_SEARCH)) throw new UnauthorizedException("Vous n'êtes pas autorisé à faire des recherches");
        if(!user.hasDocument(dm.getId())) throw new UnauthorizedException("Vous n'êtes pas autorisé à rechercher des "+dm.getTitre());
        if(!user.hasCollectivite(collectivite.key,dm)) throw new UnauthorizedException("Vous n'êtes pas autorisé à rechercher des "+dm.getTitre()+" pour cette collectivité "+collectivite.libelle!=null?"("+collectivite.libelle+")":"");
        Vector<Pair> vRet = new Vector<Pair>();
        TDocument pc = getPersistenceConfig(dm, user).getLayer("mysql").getDocument(dm.getId());
        if (pc.getRepositoryTable() == null) {
            return null;
        }
        Connection con = null;
        PreparedStatement ps = null;
        Document repository = null;
        try {
            con = getConnection();
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT REPOSITORY FROM ").append(pc.getRepositoryTable().getBaseName()).append(" WHERE COLLECTIVITE=? AND ");
            if (idNomenclature != null) {
                sb.append("ID=?");
            } else {
                sb.append("ID IS NULL");
            }
            ps = con.prepareStatement(sb.toString());
            ps.setString(1, collectivite.key);
            if (idNomenclature != null) {
                ps.setString(2, idNomenclature);
//            }else{
//            	ps.setNull(2,Types.VARCHAR);
            }
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                byte[] buff = new byte[512];
                ByteArrayOutputStream baos = new ByteArrayOutputStream();
                InputStream is = rs.getBinaryStream(1);
                int length = is.read(buff);
                while (length > 0) {
                    baos.write(buff, 0, length);
                    length = is.read(buff);
                }
                DocumentBuilder domBuilder = domFactory.newDocumentBuilder();
                repository = domBuilder.parse(new ByteArrayInputStream(baos.toByteArray()));
                if ("true".equals(System.getProperty(Constants.SYS_PROP_LOG_DOM_DOC))) {
                    TransformerFactory tf = TransformerFactory.newInstance();
                    Transformer t = tf.newTransformer();
                    ByteArrayOutputStream baos2 = new ByteArrayOutputStream();
                    t.transform(new DOMSource(repository), new StreamResult(baos2));
                    logger.debug(new String(baos2.toByteArray()));
                }
            }
        } catch (Throwable t) {
            throw new DataConfigurationException("invalid code !", t);
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (Throwable t) {
                    logger.info("ps closed !", t);
                }
            }
            if (con != null) {
                releaseConnection(con);
            }
        }
        return repository;
    }

    public Vector<Document> getRepositorysAsDom(DocumentModel dm, Pair collectivite, XemeliosUser user) throws DataConfigurationException, DataAccessException, UnauthorizedException {
        if(!user.hasRole(XemeliosUser.ROLE_SEARCH)) throw new UnauthorizedException("Vous n'êtes pas autorisé à faire des recherches");
        if(!user.hasDocument(dm.getId())) throw new UnauthorizedException("Vous n'êtes pas autorisé à rechercher des "+dm.getTitre());
        if(!user.hasCollectivite(collectivite.key,dm)) throw new UnauthorizedException("Vous n'êtes pas autorisé à rechercher des "+dm.getTitre()+" pour cette collectivité "+collectivite.libelle!=null?"("+collectivite.libelle+")":"");
        Vector<Document> vRet = new Vector<Document>();
        TDocument pc = getPersistenceConfig(dm, user).getLayer("mysql").getDocument(dm.getId());
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = getConnection();
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT REPOSITORY FROM ").append(pc.getRepositoryTable().getBaseName()).append(" WHERE COLLECTIVITE=?");
            ps = con.prepareStatement(sb.toString());
            ps.setString(1, collectivite.key);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                byte[] buff = new byte[512];
                ByteArrayOutputStream baos = new ByteArrayOutputStream();
                InputStream is = rs.getBinaryStream(1);
                int length = is.read(buff);
                while (length > 0) {
                    baos.write(buff, 0, length);
                    length = is.read(buff);
                }
                DocumentBuilder domBuilder = domFactory.newDocumentBuilder();
                Document doc = domBuilder.parse(new ByteArrayInputStream(baos.toByteArray()));
                vRet.add(doc);
                if ("true".equals(System.getProperty(Constants.SYS_PROP_LOG_DOM_DOC))) {
                    TransformerFactory tf = TransformerFactory.newInstance();
                    Transformer t = tf.newTransformer();
                    ByteArrayOutputStream baos2 = new ByteArrayOutputStream();
                    t.transform(new DOMSource(doc), new StreamResult(baos2));
                    logger.debug(new String(baos2.toByteArray()));
                }
            }
        } catch (Throwable t) {
            throw new DataConfigurationException("invalid code !", t);
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (Throwable t) {
                    logger.info("ps closed !", t);
                }
            }
            if (con != null) {
                releaseConnection(con);
            }
        }
        return vRet;
    }

    public Collection<String> getDistinctValues(ElementModel em, Pair collectivite, Pair budget, String path, XemeliosUser user) throws DataConfigurationException, DataAccessException, UnauthorizedException {
        if(!user.hasRole(XemeliosUser.ROLE_SEARCH)) throw new UnauthorizedException("Vous n'êtes pas autorisé à faire des recherches");
        if(!user.hasDocument(em.getParent().getParent().getId())) throw new UnauthorizedException("Vous n'êtes pas autorisé à rechercher des "+em.getTitre());
        if(!user.hasCollectivite(collectivite.key,em.getParent().getParent())) throw new UnauthorizedException("Vous n'êtes pas autorisé à rechercher des "+em.getTitre()+" pour cette collectivité "+collectivite.libelle!=null?"("+collectivite.libelle+")":"");
        TDocument pc = getPersistenceConfig(em.getParent().getParent(), user).getLayer("mysql").getDocument(em.getParent().getParent().getId());
        TEtat sec = pc.getEtat(em.getParent().getId());
        TSqlIndex si = sec.getSqlIndexByPath(path);
        TTable sti = sec.getIndexTable(si.getTableId());
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT DISTINCT(").append(si.getColumn()).append(") FROM ").append(sti.getBaseName()).append(" ");
        sb.append("WHERE COLLECTIVITE='").append(StringEscapeUtils.escapeSql(collectivite.key)).append("' AND BUDGET='").append(StringEscapeUtils.escapeSql(budget.key)).append("' AND ").append(si.getColumn()).append(" IS NOT NULL ORDER BY 1");

        Vector<String> vRet = new Vector<String>();
        Connection con = null;
        Statement st = null;
        try {
            con = getConnection();
            st = con.createStatement();
            ResultSet rs = st.executeQuery(sb.toString());
            while (rs.next()) {
                vRet.add(rs.getString(1));
            }
        } catch (SQLException sqlEx) {
            throw new DataConfigurationException("invalid code !", sqlEx);
        } finally {
            if (st != null) {
                try {
                    st.close();
                } catch (Throwable t) {
                    logger.info("st closed !", t);
                }
            }
            if (con != null) {
                releaseConnection(con);
            }
        }
        return vRet;
    }

    public boolean removeFichier(DocumentModel dm, String fichier, XemeliosUser user) throws DataConfigurationException, DataAccessException, UnauthorizedException {
        if(!user.hasRole(XemeliosUser.ROLE_CLEAN)) throw new UnauthorizedException("Vous n'êtes pas autorisé à supprimer des données");
        if(!user.hasDocument(dm.getId())) throw new UnauthorizedException("Vous n'êtes pas autorisé à supprimer des "+dm.getTitre());
        TDocument pc = getPersistenceConfig(dm, user).getLayer("mysql").getDocument(dm.getId());
        Connection con = null;
        PreparedStatement ps = null;
        Statement st = null;
        try {
            con = getConnection();
            st = con.createStatement();
            ResultSet rs = st.executeQuery("SELECT TABLE_NAME FROM TABLES_VERSIONS");
            ArrayList<String> existingTables = new ArrayList<String>();
            while (rs.next()) {
                existingTables.add(rs.getString(1));
            }
            // delete from special keys
            StringBuilder sql = null;

            if (pc.getSpecialKeyTable() != null) {
                if (existingTables.contains(pc.getSpecialKeyTable().getBaseName())) {
                    sql = new StringBuilder();
                    sql.append("delete from ").append(pc.getSpecialKeyTable().getBaseName()).append(" where INITIAL_DOC_NAME=?");
                    ps = con.prepareStatement(sql.toString());
                    ps.setString(1, fichier);
                    ps.executeUpdate();
                }
            }
            // delete from list bud/coll
            sql = new StringBuilder();
            if (existingTables.contains(pc.getListBcTable().getBaseName())) {
                sql = new StringBuilder();
                sql.append("delete from ").append(pc.getListBcTable().getBaseName()).append(" where INITIAL_DOC_NAME=?");
                ps = con.prepareStatement(sql.toString());
                ps.setString(1, fichier);
                ps.executeUpdate();
            }
            for (TEtat sec : pc.getEtats()) {
                // delete from index
                for (TTable sti : sec.getIndexTables()) {
                    if (existingTables.contains(sti.getBaseName())) {
                        sql = new StringBuilder();
                        sql.append("delete from ").append(sti.getBaseName()).append(" where DOC_ID in (select DOC_ID from ").append(sec.getDocumentTable().getBaseName()).append(" where INITIAL_DOC_NAME=?)");
                        ps = con.prepareStatement(sql.toString());
                        ps.setString(1, fichier);
                        ps.executeUpdate();
                    }
                }
                // delete from documents
                if (existingTables.contains(sec.getDocumentTable().getBaseName())) {
                    sql = new StringBuilder();
                    sql.append("delete from ").append(sec.getDocumentTable().getBaseName()).append(" where INITIAL_DOC_NAME=?");
                    ps = con.prepareStatement(sql.toString());
                    ps.setString(1, fichier);
                    ps.executeUpdate();
                }
            }
        } catch (SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (Throwable t) {
                    logger.info("ps closed !", t);
                }
            }
            if (st != null) {
                try {
                    st.close();
                } catch (Throwable t) {
                    logger.info("st closed !", t);
                }
            }
            if (con != null) {
                releaseConnection(con);
            }
        }
        return true;
    }

    public boolean removeBudget(DocumentModel dm, Pair collectivite, Pair budget, XemeliosUser user) throws DataConfigurationException, DataAccessException, UnauthorizedException {
        if(!user.hasRole(XemeliosUser.ROLE_CLEAN)) throw new UnauthorizedException("Vous n'êtes pas autorisé à supprimer des données");
        if(!user.hasDocument(dm.getId())) throw new UnauthorizedException("Vous n'êtes pas autorisé à supprimer des "+dm.getTitre());
        if(!user.hasCollectivite(collectivite.key,dm)) throw new UnauthorizedException("Vous n'êtes pas autorisé à supprimer des "+dm.getTitre()+" pour cette collectivité "+collectivite.libelle!=null?"("+collectivite.libelle+")":"");
        TDocument pc = getPersistenceConfig(dm, user).getLayer("mysql").getDocument(dm.getId());
        Connection con = null;
        PreparedStatement ps = null;
        Statement st = null;
        try {
            con = getConnection();
            st = con.createStatement();
            ResultSet rs = st.executeQuery("SELECT TABLE_NAME FROM TABLES_VERSIONS");
            ArrayList<String> existingTables = new ArrayList<String>();
            while (rs.next()) {
                existingTables.add(rs.getString(1));
            }
            // delete from special keys
            StringBuilder sql = null;
            if (pc.getSpecialKeyTable() != null) {
                if (existingTables.contains(pc.getSpecialKeyTable().getBaseName())) {
                    sql = new StringBuilder();
                    sql.append("delete from ").append(pc.getSpecialKeyTable().getBaseName()).append(" where COLLECTIVITE=? AND BUDGET=?");
                    ps = con.prepareStatement(sql.toString());
                    ps.setString(1, collectivite.key);
                    ps.setString(2, budget.key);
                    ps.executeUpdate();
                }
            }
            // delete from list bud/coll
            sql = new StringBuilder();
            if (existingTables.contains(pc.getListBcTable().getBaseName())) {
                sql = new StringBuilder();
                sql.append("delete from ").append(pc.getListBcTable().getBaseName()).append(" where COLLECTIVITE=? AND BUDGET=?");
                ps = con.prepareStatement(sql.toString());
                ps.setString(1, collectivite.key);
                ps.setString(2, budget.key);
                ps.executeUpdate();
            }
            for (TEtat sec : pc.getEtats()) {
                // delete from index
                for (TTable sti : sec.getIndexTables()) {
                    if (existingTables.contains(sti.getBaseName())) {
                        sql = new StringBuilder();
                        sql.append("delete from ").append(sti.getBaseName()).append(" where COLLECTIVITE=? AND BUDGET=?");
                        ps = con.prepareStatement(sql.toString());
                        ps.setString(1, collectivite.key);
                        ps.setString(2, budget.key);
                        ps.executeUpdate();
                    }
                }
                // delete from documents
                if (existingTables.contains(sec.getDocumentTable().getBaseName())) {
                    sql = new StringBuilder();
                    sql.append("delete from ").append(sec.getDocumentTable().getBaseName()).append(" where COLLECTIVITE=? AND BUDGET=?");
                    ps = con.prepareStatement(sql.toString());
                    ps.setString(1, collectivite.key);
                    ps.setString(2, budget.key);
                    ps.executeUpdate();
                }
            }
        } catch (SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (Throwable t) {
                    logger.info("ps closed !", t);
                }
            }
            if (st != null) {
                try {
                    st.close();
                } catch (Throwable t) {
                    logger.info("st closed !", t);
                }
            }
            if (con != null) {
                releaseConnection(con);
            }
        }
        return true;
    }

    public boolean removeCollectivite(DocumentModel dm, Pair collectivite, XemeliosUser user) throws DataConfigurationException, DataAccessException, UnauthorizedException {
        if(!user.hasRole(XemeliosUser.ROLE_CLEAN)) throw new UnauthorizedException("Vous n'êtes pas autorisé à supprimer des données");
        if(!user.hasDocument(dm.getId())) throw new UnauthorizedException("Vous n'êtes pas autorisé à supprimer des "+dm.getTitre());
        if(!user.hasCollectivite(collectivite.key,dm)) throw new UnauthorizedException("Vous n'êtes pas autorisé à supprimer des "+dm.getTitre()+" pour cette collectivité "+collectivite.libelle!=null?"("+collectivite.libelle+")":"");
        TDocument pc = getPersistenceConfig(dm, user).getLayer("mysql").getDocument(dm.getId());
        Connection con = null;
        PreparedStatement ps = null;
        Statement st = null;
        try {
            con = getConnection();
            st = con.createStatement();
            ResultSet rs = st.executeQuery("SELECT TABLE_NAME FROM TABLES_VERSIONS");
            ArrayList<String> existingTables = new ArrayList<String>();
            while (rs.next()) {
                existingTables.add(rs.getString(1));
            }
            StringBuilder sql = null;
            // delete from special keys
            if (pc.getSpecialKeyTable() != null) {
                if (existingTables.contains(pc.getSpecialKeyTable().getBaseName())) {
                    logger.debug("delete from " + pc.getSpecialKeyTable().getBaseName());
                    sql = new StringBuilder();
                    sql.append("delete from ").append(pc.getSpecialKeyTable().getBaseName()).append(" where COLLECTIVITE=?");
                    ps = con.prepareStatement(sql.toString());
                    ps.setString(1, collectivite.key);
                    ps.executeUpdate();
                }
            }
            // delete from list bud/coll
            if (existingTables.contains(pc.getListBcTable().getBaseName())) {
                logger.debug("delete from " + pc.getListBcTable().getBaseName());
                sql = new StringBuilder();
                sql.append("delete from ").append(pc.getListBcTable().getBaseName()).append(" where COLLECTIVITE=?");
                ps = con.prepareStatement(sql.toString());
                ps.setString(1, collectivite.key);
                ps.executeUpdate();
            }
            // delete from repository (it is the only case)
            if (existingTables.contains(pc.getRepositoryTable().getBaseName())) {
                logger.debug("delete from " + pc.getRepositoryTable().getBaseName());
                sql = new StringBuilder();
                sql.append("delete from ").append(pc.getRepositoryTable().getBaseName()).append(" where COLLECTIVITE=?");
                ps = con.prepareStatement(sql.toString());
                ps.setString(1, collectivite.key);
                ps.executeUpdate();
            }
            for (TEtat sec : pc.getEtats()) {
                // delete from index
                for (TTable sti : sec.getIndexTables()) {
                    if (existingTables.contains(sti.getBaseName())) {
                        logger.debug("delete from " + sti.getBaseName());
                        sql = new StringBuilder();
                        sql.append("delete from ").append(sti.getBaseName()).append(" where COLLECTIVITE=?");
                        ps = con.prepareStatement(sql.toString());
                        ps.setString(1, collectivite.key);
                        ps.executeUpdate();
                    }
                }
                // delete from documents
                if (existingTables.contains(sec.getDocumentTable().getBaseName())) {
                    logger.debug("delete from " + sec.getDocumentTable().getBaseName());
                    sql = new StringBuilder();
                    sql.append("delete from ").append(sec.getDocumentTable().getBaseName()).append(" where COLLECTIVITE=?");
                    ps = con.prepareStatement(sql.toString());
                    ps.setString(1, collectivite.key);
                    ps.executeUpdate();
                }
            }
            // delete from attachments
            sql = new StringBuilder();
            sql.append("delete from ATTACHMENTS where COLLECTIVITE=?");
            ps = con.prepareStatement(sql.toString());
            ps.setString(1, collectivite.key);
            ps.executeUpdate();
        } catch (SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (Throwable t) {
                    logger.info("ps closed !", t);
                }
            }
            if (st != null) {
                try {
                    st.close();
                } catch (Throwable t) {
                    logger.info("st closed !", t);
                }
            }
            if (con != null) {
                releaseConnection(con);
            }
        }
        return true;
    }

    public boolean removeDocumentModel(DocumentModel dm, XemeliosUser user) throws DataConfigurationException, DataAccessException, UnauthorizedException {
        if(!user.hasRole(XemeliosUser.ROLE_CLEAN)) throw new UnauthorizedException("Vous n'êtes pas autorisé à supprimer des données");
        if(!user.hasDocument(dm.getId())) throw new UnauthorizedException("Vous n'êtes pas autorisé à supprimer des "+dm.getTitre());
        TDocument pc = getPersistenceConfig(dm, user).getLayer("mysql").getDocument(dm.getId());
        Connection con = null;
        Statement st = null;
        try {
            con = getConnection();
            for (TEtat sec : pc.getEtats()) {
                ensureTablesExists(pc, sec, null, null, con);
                StringBuilder sql = new StringBuilder();
                // delete from index
                for (TTable sti : sec.getIndexTables()) {
                    sql.append("delete from ").append(sti.getBaseName());
                    st = con.createStatement();
                    st.executeUpdate(sql.toString());
                    sql = new StringBuilder();
                    st.close();
                }
                st = con.createStatement();
                // delete from special keys
                if (pc.getSpecialKeyTable() != null) {
                    sql.append("delete from ").append(pc.getSpecialKeyTable().getBaseName());
                    st.executeUpdate(sql.toString());
                }
                // delete from documents
                sql = new StringBuilder();
                sql.append("delete from ").append(sec.getDocumentTable().getBaseName());
                st.executeUpdate(sql.toString());
                // delete from list bud/coll
                sql = new StringBuilder();
                sql.append("delete from ").append(pc.getListBcTable().getBaseName());
                st.executeUpdate(sql.toString());
                // delete from repository (it is the only case)
                sql = new StringBuilder();
                sql.append("delete from ").append(pc.getRepositoryTable().getBaseName());
                st.executeUpdate(sql.toString());
                st.close();
            }
        } catch (SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } finally {
            if (st != null) {
                try {
                    st.close();
                } catch (Throwable t) {
                    logger.info("st closed !", t);
                }
            }
            if (con != null) {
                releaseConnection(con);
            }
        }
        return true;
    }

    public boolean removeSpecialKey1(DocumentModel dm, Pair collectivite, Pair budget, Pair key1, XemeliosUser user) throws DataConfigurationException, DataAccessException, UnauthorizedException {
        if(!user.hasRole(XemeliosUser.ROLE_CLEAN)) throw new UnauthorizedException("Vous n'êtes pas autorisé à supprimer des données");
        if(!user.hasDocument(dm.getId())) throw new UnauthorizedException("Vous n'êtes pas autorisé à supprimer des "+dm.getTitre());
        if(!user.hasCollectivite(collectivite.key,dm)) throw new UnauthorizedException("Vous n'êtes pas autorisé à supprimer des "+dm.getTitre()+" pour cette collectivité "+collectivite.libelle!=null?"("+collectivite.libelle+")":"");
        TDocument pc = getPersistenceConfig(dm, user).getLayer("mysql").getDocument(dm.getId());
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = getConnection();
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT INITIAL_DOC_NAME FROM ").append(pc.getSpecialKeyTable().getBaseName()).append(" WHERE COLLECTIVITE=? AND BUDGET=? AND KEY1=?");
            ps = con.prepareStatement(sb.toString());
            ps.setString(1, collectivite.key);
            ps.setString(2, budget.key);
            ps.setString(3, key1.key);
            ResultSet rs = ps.executeQuery();
            TreeSet<String> al = new TreeSet<String>();
            while (rs.next()) {
                al.add(rs.getString(1));
            }
            for (String docName : al) {
                removeDocument(dm, budget, collectivite, docName, user);
            }
        } catch (SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (Throwable t) {
                    logger.info("ps closed !", t);
                }
            }
            if (con != null) {
                releaseConnection(con);
            }
        }
        return true;
    }

    public boolean removeSpecialKey2(DocumentModel dm, Pair collectivite, Pair budget, Pair key1, Pair key2, XemeliosUser user) throws DataConfigurationException, DataAccessException, UnauthorizedException {
        if(!user.hasRole(XemeliosUser.ROLE_CLEAN)) throw new UnauthorizedException("Vous n'êtes pas autorisé à supprimer des données");
        if(!user.hasDocument(dm.getId())) throw new UnauthorizedException("Vous n'êtes pas autorisé à supprimer des "+dm.getTitre());
        if(!user.hasCollectivite(collectivite.key,dm)) throw new UnauthorizedException("Vous n'êtes pas autorisé à supprimer des "+dm.getTitre()+" pour cette collectivité "+collectivite.libelle!=null?"("+collectivite.libelle+")":"");
        TDocument pc = getPersistenceConfig(dm, user).getLayer("mysql").getDocument(dm.getId());
        Connection con = null;
        PreparedStatement ps = null;
        try {
            logger.debug("deleting " + dm.getId() + "/" + collectivite.libelle + "/" + budget.libelle + "/" + key1.key + "/" + key2.key);
            con = getConnection();
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT INITIAL_DOC_NAME FROM ").append(pc.getSpecialKeyTable().getBaseName()).append(" WHERE COLLECTIVITE=? AND BUDGET=? AND KEY1=? AND KEY2=?");
            ps = con.prepareStatement(sb.toString());
            ps.setString(1, collectivite.key);
            ps.setString(2, budget.key);
            ps.setString(3, key1.key);
            ps.setString(4, key2.key);
            ResultSet rs = ps.executeQuery();
            TreeSet<String> al = new TreeSet<String>();
            while (rs.next()) {
                al.add(rs.getString(1));
            }
            for (String docName : al) {
                removeDocument(dm, budget, collectivite, docName, user);
            }
        } catch (SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (Throwable t) {
                    logger.info("ps closed !", t);
                }
            }
            if (con != null) {
                releaseConnection(con);
            }
        }
        return true;
    }

    public boolean removeSpecialKey3(DocumentModel dm, Pair collectivite, Pair budget, Pair key1, Pair key2, Pair key3, XemeliosUser user) throws DataConfigurationException, DataAccessException, UnauthorizedException {
        if(!user.hasRole(XemeliosUser.ROLE_CLEAN)) throw new UnauthorizedException("Vous n'êtes pas autorisé à supprimer des données");
        if(!user.hasDocument(dm.getId())) throw new UnauthorizedException("Vous n'êtes pas autorisé à supprimer des "+dm.getTitre());
        if(!user.hasCollectivite(collectivite.key,dm)) throw new UnauthorizedException("Vous n'êtes pas autorisé à supprimer des "+dm.getTitre()+" pour cette collectivité "+collectivite.libelle!=null?"("+collectivite.libelle+")":"");
        TDocument pc = getPersistenceConfig(dm, user).getLayer("mysql").getDocument(dm.getId());
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = getConnection();
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT INITIAL_DOC_NAME FROM ").append(pc.getSpecialKeyTable().getBaseName()).append(" WHERE COLLECTIVITE=? AND BUDGET=? AND KEY1=? AND KEY2=? AND KEY3=?");
            ps = con.prepareStatement(sb.toString());
            ps.setString(1, collectivite.key);
            ps.setString(2, budget.key);
            ps.setString(3, key1.key);
            ps.setString(4, key2.key);
            ps.setString(5, key3.key);
            ResultSet rs = ps.executeQuery();
            TreeSet<String> al = new TreeSet<String>();
            while (rs.next()) {
                al.add(rs.getString(1));
            }
            for (String docName : al) {
                removeDocument(dm, budget, collectivite, docName, user);
            }
        } catch (SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (Throwable t) {
                    logger.info("ps closed !", t);
                }
            }
            if (con != null) {
                releaseConnection(con);
            }
        }
        return true;
    }

    public DataResultSet getEmptyDataResultSet() {
        return new MySqlDataResultSet();
    }

    public DataResultSet search(ElementModel elementModel, Pair collectivite, Pair budget, String xPath, ListeResultatModel lrm, Vector<CritereModel> models, XemeliosUser user) throws DataConfigurationException, DataAccessException, UnauthorizedException {
        if(!user.hasRole(XemeliosUser.ROLE_SEARCH)) throw new UnauthorizedException("Vous n'êtes pas autorisé à faire des recherches");
        if(!user.hasDocument(elementModel.getParent().getParent().getId())) throw new UnauthorizedException("Vous n'êtes pas autorisé à rechercher des "+elementModel.getTitre());
        if(!user.hasCollectivite(collectivite.key,elementModel.getParent().getParent())) throw new UnauthorizedException("Vous n'êtes pas autorisé à rechercher des "+elementModel.getTitre()+" pour cette collectivité "+collectivite.libelle!=null?"("+collectivite.libelle+")":"");
        String sql = transformXPathToSql(elementModel, collectivite, budget, xPath, models, user);
        logger.debug(sql);
        DataResultSet rs = null;
        try {
            rs = new MySqlDataResultSet(elementModel, collectivite, budget, xPath, sql, lrm, this, user);
        } catch (SQLException sqlEx) {
            throw new DataConfigurationException(sqlEx);
        }
        return rs;
    }

    public Pair[] getOperators(String datatype) {
        if ("string".equals(datatype)) {
            return new Pair[]{new Pair("starts-with", "commence par"), new Pair("contains", "contient"), new Pair("xem:does-not-contain", "ne contient pas"), new Pair("xem:ends-with", "fini par"), new Pair("xem:StringEquals", "égal à "), new Pair("xem:StringDiffers", "diffère de"), new Pair("xem:StringIsNull", "est nul"), new Pair("xem:StringIsNotNull", "n'est pas nul")};
        } else if ("numeric".equals(datatype)) {
            return new Pair[]{new Pair("=", "="), new Pair(">=", ">="), new Pair(">", ">"), new Pair("<", "<"), new Pair("<=", "<="), new Pair("!=", "!=")};
        } else if ("date".equals(datatype)) {
            return new Pair[]{new Pair("xem:DateBefore", "avant le"), new Pair("xem:DateEquals", "le"), new Pair("xem:DateAfter", "après le")};
        }
        return null;
    }

    public String getDocumentAsString(EtatModel etatModel, Pair collectivite, Pair budget, String docId, XemeliosUser user) throws DataConfigurationException, DataAccessException, UnauthorizedException {
        if(!user.hasRole(XemeliosUser.ROLE_SEARCH)) throw new UnauthorizedException("Vous n'êtes pas autorisé à faire des recherches");
        if(!user.hasDocument(etatModel.getParent().getId())) throw new UnauthorizedException("Vous n'êtes pas autorisé à rechercher des "+etatModel.getTitre());
        if(!user.hasCollectivite(collectivite.key,etatModel.getParent())) throw new UnauthorizedException("Vous n'êtes pas autorisé à rechercher des "+etatModel.getTitre()+" pour cette collectivité "+collectivite.libelle!=null?"("+collectivite.libelle+")":"");
        DocumentModel dm = etatModel.getParent();
        String ret = null;
        TDocument pc = getPersistenceConfig(dm, user).getLayer("mysql").getDocument(etatModel.getParent().getId());
        TEtat sec = pc.getEtat(etatModel.getId());
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = getConnection();
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT DOC, ENCODING FROM ").append(sec.getDocumentTable().getBaseName()).append(" WHERE BUDGET=? AND COLLECTIVITE=? AND DOC_ID=?");
            ps = con.prepareStatement(sb.toString());
            ps.setString(1, budget.key);
            ps.setString(2, collectivite.key);
            ps.setString(3, docId);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                String encoding = rs.getString(2);
                byte[] buff = new byte[512];
                ByteArrayOutputStream baos = new ByteArrayOutputStream();
                InputStream is = rs.getAsciiStream(1);
                int length = is.read(buff);
                while (length > 0) {
                    baos.write(buff, 0, length);
                    length = is.read(buff);
                }
                ret = new String(baos.toByteArray(), encoding);
            }
        } catch (SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } catch (IOException ioEx) {
            throw new DataAccessException(ioEx);
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (Throwable t) {
                    logger.info("ps closed !", t);
                }
            }
            if (con != null) {
                releaseConnection(con);
            }
        }
        return ret;
    }

    public Document getDocumentAsDom(EtatModel etatModel, Pair collectivite, Pair budget, String docId, XemeliosUser user) throws DataConfigurationException, DataAccessException, UnauthorizedException {
        if(!user.hasRole(XemeliosUser.ROLE_SEARCH)) throw new UnauthorizedException("Vous n'êtes pas autorisé à faire des recherches");
        if(!user.hasDocument(etatModel.getParent().getId())) throw new UnauthorizedException("Vous n'êtes pas autorisé à rechercher des "+etatModel.getTitre());
        if(!user.hasCollectivite(collectivite.key,etatModel.getParent())) throw new UnauthorizedException("Vous n'êtes pas autorisé à rechercher des "+etatModel.getTitre()+" pour cette collectivité "+collectivite.libelle!=null?"("+collectivite.libelle+")":"");
        DocumentModel dm = etatModel.getParent();
        Document ret = null;
        TDocument pc = getPersistenceConfig(dm, user).getLayer("mysql").getDocument(etatModel.getParent().getId());
        TEtat sec = pc.getEtat(etatModel.getId());
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = getConnection();
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT DOC FROM ").append(sec.getDocumentTable().getBaseName()).append(" WHERE BUDGET=? AND COLLECTIVITE=? AND DOC_ID=?");
            ps = con.prepareStatement(sb.toString());
            ps.setString(1, budget.key);
            ps.setString(2, collectivite.key);
            ps.setString(3, docId);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                byte[] buff = new byte[512];
                ByteArrayOutputStream baos = new ByteArrayOutputStream();
                InputStream is = rs.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);
                ret = builder.parse(bis);
                if ("true".equals(System.getProperty(Constants.SYS_PROP_LOG_DOM_DOC))) {
                    logger.debug(new String(byteArray));
                }
            }
        } catch (SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } catch (IOException ioEx) {
            throw new DataAccessException(ioEx);
        } catch (ParserConfigurationException pcEx) {
            throw new DataConfigurationException(pcEx);
        } catch (SAXException saxEx) {
            throw new DataAccessException(saxEx);
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (Throwable t) {
                    logger.info("ps closed !", t);
                }
            }
            if (con != null) {
                releaseConnection(con);
            }
        }
        return ret;
    }

    public DocumentInfos getDocumentInfos(EtatModel etatModel, Pair collectivite, Pair budget, String docId, XemeliosUser user) throws DataConfigurationException, DataAccessException, UnauthorizedException {
        if(user!=null) {
            if(!user.hasRole(XemeliosUser.ROLE_SEARCH)) throw new UnauthorizedException("Vous n'êtes pas autorisé à faire des recherches");
            if(!user.hasDocument(etatModel.getParent().getId())) throw new UnauthorizedException("Vous n'êtes pas autorisé à rechercher des "+etatModel.getTitre());
            if(!user.hasCollectivite(collectivite.key,etatModel.getParent())) throw new UnauthorizedException("Vous n'êtes pas autorisé à rechercher des "+etatModel.getTitre()+" pour cette collectivité "+collectivite.libelle!=null?"("+collectivite.libelle+")":"");
        }
        DocumentModel dm = etatModel.getParent();
        Document ret = null;
        String encoding = null;
        TDocument pc = getPersistenceConfig(dm, user).getLayer("mysql").getDocument(etatModel.getParent().getId());
        TEtat sec = pc.getEtat(etatModel.getId());
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = getConnection();
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT DOC, ENCODING FROM ").append(sec.getDocumentTable().getBaseName()).append(" WHERE BUDGET=? AND COLLECTIVITE=? AND DOC_ID=?");
            ps = con.prepareStatement(sb.toString());
            ps.setString(1, budget.key);
            ps.setString(2, collectivite.key);
            ps.setString(3, docId);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                encoding = rs.getString(2);
                byte[] buff = new byte[512];
                ByteArrayOutputStream baos = new ByteArrayOutputStream();
                InputStream is = rs.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);
                ret = builder.parse(bis);
                if ("true".equals(System.getProperty(Constants.SYS_PROP_LOG_DOM_DOC))) {
                    logger.debug(new String(byteArray));
                }
            }
        } catch (SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } catch (IOException ioEx) {
            throw new DataAccessException(ioEx);
        } catch (ParserConfigurationException pcEx) {
            throw new DataConfigurationException(pcEx);
        } catch (SAXException saxEx) {
            throw new DataAccessException(saxEx);
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (Throwable t) {
                    logger.info("ps closed !", t);
                }
            }
            if (con != null) {
                releaseConnection(con);
            }
        }
        if (ret == null) {
            logger.info("ret is null !");
        }
        return new DocumentInfos(docId, ret, encoding);
    }

    @Deprecated
    private Connection createNewConnection() throws SQLException {
        return null;
    }

    public void releaseConnection(Connection con) {
        PoolManager.getInstance().releaseConnection(con);
    }

    public Connection getConnection() throws SQLException {
        return PoolManager.getInstance().getConnection();
    }

    /**
     * Tests if this docName has already been imported
     * @param sec
     * @param codeBudget
     * @param collectivite
     * @param docName
     * @return <tt>true</tt> is this <tt>docName</tt> has been imported
     * @throws SQLException
     */
    protected boolean documentExists(TDocument pc, Pair codeBudget, Pair collectivite, String docName, Connection con) throws SQLException {
        PreparedStatement ps = null;
        boolean bRet = false;
        try {
            StringBuilder sb = new StringBuilder();
            sb.append("select 1 from ").append(pc.getListBcTable().getBaseName()).append(" where INITIAL_DOC_NAME=? AND BUDGET=? AND COLLECTIVITE=? LIMIT 1");
            ps = con.prepareStatement(sb.toString());
            ps.setString(1, docName);
            ps.setString(2, codeBudget.key);
            ps.setString(3, collectivite.key);
            ResultSet rs = ps.executeQuery();
            bRet = rs.next();
            rs.close();
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (Throwable t) {
                    logger.info("ps closed !", t);
                }
            }
        }
        return bRet;
    }

    protected boolean doesRepositoryExist(TDocument pc, String collectivite, Connection con, String idNomenclature) throws SQLException {
        boolean bRet = false;
        if (pc.getRepositoryTable() == null) {
            return false;
        }
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT 1 FROM ").append(pc.getRepositoryTable().getBaseName()).append(" WHERE COLLECTIVITE=? AND ID");
        if (idNomenclature != null) {
            sb.append("=?");
        } else {
            sb.append(" IS NULL");
        }
        PreparedStatement ps = null;
        try {
            ps = con.prepareStatement(sb.toString());
            ps.setString(1, collectivite);
            if (idNomenclature != null) {
                ps.setString(2, idNomenclature);
            }

            ResultSet rs = ps.executeQuery();
            bRet = rs.next();
            rs.close();
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (Throwable t) {
                    logger.info("ps closed !", t);
                }
            }
        }
        return bRet;
    }

    protected void ensureTablesExists(TDocument pc, TEtat sec, Pair codeBudget, Pair collectivite, Connection con) throws SQLException {
        Statement st = con.createStatement();
        createTable(pc.getListBcTable(), st);
        createTable(pc.getRepositoryTable(), st);
        createTable(pc.getSpecialKeyTable(), st);

        createTable(sec.getDocumentTable(), st);
        for (TTable table : sec.getIndexTables()) {
            createTable(table, st);
        }
        st.close();
    }

    private void createTable(TTable sti, Statement st) throws SQLException {
        if (sti == null) {
            return;
        }
        if (!createdTables.contains(sti.getBaseName())) {
            st.executeUpdate(sti.getScript());
            if (st.executeQuery("SELECT 1 FROM TABLES_VERSIONS WHERE TABLE_NAME='" + sti.getBaseName() + "'").next()) {
                st.executeUpdate("UPDATE TABLES_VERSIONS SET VERSION=" + sti.getVersion() + " WHERE TABLE_NAME='" + sti.getBaseName() + "'");
            } else {
                st.executeUpdate("INSERT INTO TABLES_VERSIONS (TABLE_NAME,VERSION) VALUES ('" + sti.getBaseName() + "'," + sti.getVersion() + ")");
            }
            createdTables.add(sti.getBaseName());
        }
    }

    private void updateTableVersion(TTable table, Connection con) throws SQLException {
        String sql = "SELECT VERSION FROM TABLES_VERSIONS WHERE TABLE_NAME=?";
        PreparedStatement ps = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            ps = con.prepareStatement(sql);
            ps.setString(1, table.getBaseName());
            rs = ps.executeQuery();
            if (rs.next()) {
                int existingVersion = rs.getInt(1);
                if (existingVersion >= table.getVersion()) {
                    return;
                }
                TPatch patch = table.getPatchFromVersion(existingVersion);
                st = con.createStatement();
                for (TSql order : patch.getOrders()) {
                    SQLException sqlEx = null;
                    try {
                        st.execute(order.getSql());
                    } catch (SQLException ex) {
                        sqlEx = ex;
                    }
                    if (sqlEx != null && order.isFailOnError()) {
                        throw sqlEx;
                    }
                }
                rs.close();
                sql = "UPDATE TABLES_VERSIONS SET VERSION=? WHERE TABLE_NAME=?";
                ps = con.prepareStatement(sql);
                ps.setInt(1, table.getVersion());
                ps.setString(2, table.getBaseName());
                ps.executeUpdate();
            } else {
                st = con.createStatement();
                createTable(table, st);
            }
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (Throwable t) {
                    logger.info("rs closed !", t);
                }
            }
            if (st != null) {
                try {
                    st.close();
                } catch (Throwable t) {
                    logger.info("st closed !", t);
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (Throwable t) {
                    logger.info("ps closed !", t);
                }
            }
        }
    }

    protected String transformXPathToSql(ElementModel elementModel, Pair collectivite, Pair budget, String xPath, Vector<CritereModel> models, XemeliosUser user) throws DataConfigurationException {
        EtatModel etatModel = elementModel.getParent();
        DocumentModel documentModel = etatModel.getParent();

        TDocument pc = getPersistenceConfig(documentModel, user).getLayer("mysql").getDocument(documentModel.getId());
        TEtat sec = pc.getEtat(etatModel.getId());

        // ensure xPath expr starts with element path
        if (!xPath.substring(0, elementModel.getPath().getPath().length()).equals(elementModel.getPath().getPath())) {
            throw new DataConfigurationException("XPath: " + xPath + " does not start with element[" + elementModel.getId() + "] path");
        }

        StringBuilder sb = new StringBuilder();

        // splitts by OR clause
        ArrayList<CritereModel> part = new ArrayList<CritereModel>();
        boolean firstPart = true;
        if (models != null) {
            for (CritereModel cm : models) {
                TCriteria ci = sec.getCriteria(cm.getId());
                if (ci == null) {
                    throw new DataConfigurationException("no criteria persistence info found for " + cm.getId());
                }
                cm.setAdditionnalData(ci);
                //            String andOr = cm.getProperty("AND_OR");
                part.add(cm);
            }
        }
        appendRequest(part, sb, sec, collectivite, budget, firstPart);
        return sb.toString();
    }

    protected void appendRequest(ArrayList<CritereModel> criterias, StringBuilder sb, TEtat sec, Pair collectivite, Pair budget, boolean firstPart) throws DataConfigurationException {
        TTable mainTable = sec.getMaintable();
        TreeSet<TTable> criteriasUsedTables = new TreeSet<TTable>();

        StringBuilder fromClause = new StringBuilder();
        StringBuilder whereClause = new StringBuilder();
        sb.append("SELECT SQL_CALC_FOUND_ROWS DISTINCT(").append(mainTable.getBaseName()).append(".DOC_ID) FROM ");
        sb.append(sec.getMaintable().getBaseName());

        whereClause.append(" WHERE ");

        whereClause.append(mainTable.getBaseName()).append(".COLLECTIVITE='").append(StringEscapeUtils.escapeSql(collectivite.key)).append("' AND ").append(mainTable.getBaseName()).append(".BUDGET='").append(StringEscapeUtils.escapeSql(budget.key)).append("' AND ( (");
        for (CritereModel cm : criterias) {
            TCriteria sci = (TCriteria) cm.getAdditionnalData();
            if (sci.getSqlIndexId() == null) {
                logger.warn("try to use a mis-defined criteria : " + sci.getId());
                continue;
            }
            TSqlIndex si = sec.getSqlIndexById(sci.getSqlIndexId());
            TTable sti = sec.getIndexTable(si.getTableId());
            if (cm.getProperty("AND_OR") != null) {
                String andOr = cm.getProperty("AND_OR");
                if ("||".equals(andOr)) {
                    whereClause.append(") OR (");
                } else {
                    whereClause.append(" AND ");
                }
            }
            String s = sci.getSubstitutedWhereClause(cm);
            s = s.replaceAll("\\$\\{table\\}", sti.getBaseName());
            s = s.replaceAll("\\$\\{column\\}", si.getColumn());
            whereClause.append(s);
            criteriasUsedTables.add(sti);
        }
        if (whereClause.substring(sb.length() - 6).equals(" AND ")) {
            for (int i = 0; i < 4; i++) {
                whereClause.deleteCharAt(whereClause.length() - 1);
            }
        }
        if (whereClause.substring(sb.length() - 5).equals(" OR ")) {
            for (int i = 0; i < 3; i++) {
                whereClause.deleteCharAt(whereClause.length() - 1);
            }
        }
        whereClause.append(") )");

        for (TTable sti : sec.getIndexTables()) {
            if (!sti.isMainTable() && criteriasUsedTables.contains(sti)) {
                fromClause.append(" LEFT OUTER JOIN ").append(sti.getBaseName()).append(" ON ").append(sec.getMaintable().getBaseName()).append(".DOC_ID=").append(sti.getBaseName()).append(".DOC_ID AND ").append(sec.getMaintable().getBaseName()).append(".COLLECTIVITE=").append(sti.getBaseName()).append(".COLLECTIVITE AND ").append(sec.getMaintable().getBaseName()).append(".BUDGET=").append(sti.getBaseName()).append(".BUDGET");
            }
        }

        sb.append(fromClause.toString()).append(whereClause.toString());
    }

    @Deprecated
    protected String[] evaluateCondition(String cond) {
        String[] ret = new String[3];
        return ret;
    }

    public ResultSet execute(String sql) throws SQLException {
        Connection con = null;
        ResultSet rs = null;
        con = getConnection();
        Statement st = con.createStatement();
        rs = st.executeQuery(sql);
        return rs;
    }

    private void initConnections() throws DataConfigurationException {
        String configFileName = System.getProperty(PROP_CONFIG_FILE_NAME);
        props = new PropertiesExpansion();
//        for (Object key : System.getProperties().keySet()) {
//            props.put(key, System.getProperty((String) key));
//        }
        Connection pCon = null;
        try {
            try {
                props.load(new FileInputStream(configFileName));
            } catch(NullPointerException npEx) {
                // si ça a déjà été initialisé, cela peut être nul
            }
            PoolManager.getInstance().init(props);
            checkedConfigs = new ArrayList<TDocument>();
            pCon = getConnection();
            Statement st = pCon.createStatement();
            st.executeUpdate("CREATE TABLE IF NOT EXISTS TABLES_VERSIONS (TABLE_NAME VARCHAR(64) NOT NULL, VERSION INT NOT NULL)");
            st.executeUpdate("CREATE TABLE IF NOT EXISTS ATTACHMENTS (COLLECTIVITE VARCHAR(15) NOT NULL, PJ_NAME VARCHAR(270) NOT NULL, FILE_NAME VARCHAR(280) NOT NULL, DATA LONGBLOB, INDEX IX_PJ (COLLECTIVITE,PJ_NAME) ) TYPE MYISAM");
            // table des configurations des exports
            st.executeUpdate("CREATE TABLE IF NOT EXISTS CONFIGS_EXPORT (ID int unsigned NOT NULL auto_increment, DOC_ID varchar(100) NOT NULL, ETAT_ID varchar(100) NOT NULL, OWNER varchar(25) NOT NULL, DATE_CREA datetime NOT NULL, CODE varchar(250) NOT NULL, LIBELLE VARCHAR(200) NOT NULL, CONFIG longblob NOT NULL, PRIMARY KEY (ID, DOC_ID, ETAT_ID))");
            try {
                st.executeQuery("SELECT 1 FROM LAST_INPUT_VALUES").close();
            } catch(Exception ex) {
                st.executeUpdate("CREATE TABLE IF NOT EXISTS LAST_INPUT_VALUES(USER_ID VARCHAR(50) NOT NULL, INPUT_ID VARCHAR(250) NOT NULL, VALUE VARCHAR(250), INDEX IX_LAST_INPUT (USER_ID, INPUT_ID) ) TYPE MYISAM");
            }
            try {
                st.executeQuery("SELECT 1 FROM SAVED_REQUESTS").close();
            } catch(Exception ex) {
                st.executeUpdate("CREATE TABLE IF NOT EXISTS SAVED_REQUESTS (ID VARCHAR(250) NOT NULL, REQUEST LONGBLOB, KEY_EM VARCHAR(250) NOT NULL, USER_ID VARCHAR(50) NOT NULL, PUBLIC INTEGER, TITLE VARCHAR(250) NOT NULL, INDEX IX_SR (KEY_EM,USER_ID) ) TYPE MYISAM");
            }
            try {
                st.executeQuery("SELECT 1 FROM XEM_IMPORTS").close();
            } catch(Exception ex) {
                st.executeUpdate("CREATE TABLE IF NOT EXISTS XEM_IMPORTS (UTI_LOGIN VARCHAR(50) not null,IMPORT_ID VARCHAR(200) not null,FILE_NAME VARCHAR(200) not null,FILE_TYPE VARCHAR(200) not null,SUBMIT_DATE TIMESTAMP,END_DATE TIMESTAMP,STATUS integer not null,COLLECTIVITE VARCHAR(15),BUDGET VARCHAR(24),COLLECTIVITE_LIB VARCHAR(250),BUDGET_LIB VARCHAR(250),ERROR_MESSAGE LONGTEXT,CLEANED integer,INDEX IX_IMPORTS(UTI_LOGIN,IMPORT_ID))");
            }
            try {
                st.executeQuery("SELECT 1 FROM XEM_IMPORTS_FILES").close();
            } catch(Exception ex) {
                st.executeUpdate("CREATE TABLE IF NOT EXISTS XEM_IMPORTS_FILES (IMPORT_ID VARCHAR(200) not null,SUB_FILE_NAME LONGTEXT not null,FILE_TYPE VARCHAR(200) not null,STATUS INTEGER,COLLECTIVITE VARCHAR(15),BUDGET VARCHAR(24),COLLECTIVITE_LIB VARCHAR(250),BUDGET_LIB VARCHAR(250),ERROR_MESSAGE LONGTEXT,INDEX IX_IMPORTS_FILES(IMPORT_ID))");
            }
            try {
                st.executeQuery("SELECT 1 FROM EXPORT_JOBS").close();
                ResultSet rs = st.executeQuery("SHOW COLUMNS FROM EXPORT_JOBS WHERE FIELD='GENERATED_FILE_NAME'");
                if(!rs.next()) {
                    st.executeUpdate("ALTER TABLE EXPORT_JOBS ADD GENERATED_FILE_NAME VARCHAR(150)");
                }
                rs = st.executeQuery("SHOW COLUMNS FROM EXPORT_JOBS WHERE FIELD='ELEMENT_ID'");
                if(!rs.next()) {
                    st.executeUpdate("ALTER TABLE EXPORT_JOBS ADD ELEMENT_ID VARCHAR(150)");
                }
            } catch(Exception ex) {
                st.executeUpdate("CREATE TABLE IF NOT EXISTS EXPORT_JOBS (UTI_LOGIN VARCHAR(50) not null,EXPORT_ID int unsigned NOT NULL auto_increment,FILE_NAME VARCHAR(80) not null,EXPORT_NAME VARCHAR(80) not null,DOC_ID VARCHAR(150) not null,ETAT_ID VARCHAR(150) not null,CONFIG_ID int unsigned not null,SIZE int,PRIORITY int,STATUS int not null,SUBMIT_DATE TIMESTAMP,RUN_DATE TIMESTAMP,DOWNLOAD_DATE TIMESTAMP,ERROR_MESSAGE LONGTEXT,DRS longblob not null,CLEANED integer,GENERATED_FILE_NAME VARCHAR(150),ELEMENT_ID VARCHAR(150),INDEX IX_IMPORTS(UTI_LOGIN,EXPORT_ID)) TYPE MYISAM");
            }

            st.close();
            //	      DEBUG
            DatabaseMetaData dmd = pCon.getMetaData();
            logger.info("driver = " + dmd.getDriverName() + " - " + dmd.getDriverVersion());
            logger.info("database = " + dmd.getDatabaseProductName() + " - " + dmd.getDatabaseProductVersion());
//            if (logger.isDebugEnabled()) {
//                Vector<String> v = new Vector<String>();
//                ResultSet rs = pCon.createStatement().executeQuery("SHOW TABLES");
//                while (rs.next()) {
//                    v.add(rs.getString(1));
//                }
//                for (String s : v) {
//                    ResultSet rs2 = pCon.createStatement().executeQuery("SELECT CONCAT('" + s + "   ',CONV(COUNT(*),10,10)) FROM " + s);
//                    if (rs2.next()) {
//                        logger.debug(rs2.getString(1));
//                    }
//                }
//            }
        } catch (Throwable t) {
            logger.error("in initConnections()", t);
            StringBuilder sb = new StringBuilder();
            sb.append("Impossible de se connecter au serveur Xemelios :\npool=").
                    append((props.getProperty("jndi.poolname") != null ? props.getProperty("jndi.poolname") : props.getProperty("database.url")));
            throw new DataConfigurationException(sb.toString(), t);
        } finally {
            if (pCon != null) {
                releaseConnection(pCon);
            }
        }
    }

    public String getLastInputValue(String inputId, XemeliosUser user) {
        Connection con = null;
        PreparedStatement ps = null;
        String ret = null;
        try {
            con = getConnection();
            ps = con.prepareStatement("SELECT VALUE FROM LAST_INPUT_VALUES WHERE USER_ID=? AND INPUT_ID=?");
            ps.setString(1,user.getId());
            ps.setString(2,inputId);
            ResultSet rs = ps.executeQuery();
            if(rs.next()) {
                ret = rs.getString(1);
            }
            rs.close();
        } catch(SQLException sqlEx) {
            logger.error("getLastInputValue(String,XemeliosUser)",sqlEx);
        } finally {
            if(ps!=null) {
                try {
                    ps.close();
                } catch(SQLException sqlEx) {
                    logger.info("getLastInputValue(String,XemeliosUser)",sqlEx);
                }
            }
            if (con != null) {
                releaseConnection(con);
            }
        }
        return ret;
    }

    public void setLastInputValue(String inputId, String value, XemeliosUser user) {
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = getConnection();
            ps = con.prepareStatement("UPDATE LAST_INPUT_VALUES SET VALUE=? WHERE USER_ID=? AND INPUT_ID=?");
            ps.setString(1,value);
            ps.setString(2,user.getId());
            ps.setString(3,inputId);
            int ret = ps.executeUpdate();
            if(ret==0) {
                // la ligne n'était pas présente
                ps.close();
                ps = con.prepareStatement("INSERT INTO LAST_INPUT_VALUES (INPUT_ID,USER_ID,VALUE) VALUES (?,?,?)");
                ps.setString(1,inputId);
                ps.setString(2, user.getId());
                ps.setString(3,value);
                ps.executeUpdate();
            }
        } catch(SQLException sqlEx) {
            logger.error("setLastInputValue(String,String,XemeliosUser)",sqlEx);
        } finally {
            if(ps!=null) {
                try {
                    ps.close();
                } catch(SQLException sqlEx) {
                    logger.info("setLastInputValue(String,String,XemeliosUser)",sqlEx);
                }
            }
            if (con != null) {
                releaseConnection(con);
            }
        }
    }

    public void shutdown() {
        reset();
        PoolManager.getInstance().shutdown();
    }

    public byte[] serializeDataResultSet(DataResultSet drs) {
        if(drs instanceof MySqlDataResultSet) {
            try {
                DataResultSetWrapperModel drsw = new DataResultSetWrapperModel(DataResultSetWrapperModel.QN);
                drsw.setDataResultSet(drs);
                ByteArrayOutputStream baos = new ByteArrayOutputStream();
                OutputStreamWriter osw = new OutputStreamWriter(baos);
                XmlOutputter output = new XmlOutputter(osw, "UTF-8");
                drsw.marshall(output);
                osw.flush();
                return baos.toByteArray();
            } catch (IOException ex) {
                logger.error("serializeDataResultSet(DataResultSet)",ex);
                return null;
            }
        }
        else throw new UnsupportedOperationException("Not supported yet.");
    }

    public DataResultSet deserializeDataResultSet(byte[] buffer) {
        try {
            DrswParser parser = new DrswParser();
            parser.parse(new ByteArrayInputStream(buffer));
            DataResultSetWrapperModel drsw = (DataResultSetWrapperModel)parser.getMarshallable();
            return drsw.getDataResultSet();
        } catch(Exception ex) {
            logger.error("deserializeDataResultSet(byte[])",ex);
            return null;
        }
    }

    private class IndexerHandler extends DefaultHandler {

        private TEtat sec = null;
        private Stack<QName> stack = null;
        private String key1 = null,  key2 = null,  key3 = null,  key4 = null,  key5 = null,  key6 = null,  key7 = null,  key8 = null,  key9 = null,  key10 = null;
        private String pathToResetKey1 = null,  pathToResetKey2 = null,  pathToResetKey3 = null,  pathToResetKey4 = null,  pathToResetKey5 = null,  pathToResetKey6 = null,  pathToResetKey7 = null,  pathToResetKey8 = null,  pathToResetKey9 = null,  pathToResetKey10 = null;
        private String collectivite = null,  budget = null,  docId = null;
        private SQLException sqlEx = null;
        private long indexDuration = 0L;
        private Hashtable<TSqlIndex, Object> cachedData = null;
        private Connection con = null;
        private StringBuffer warnings;
        private Stack<StringBuffer> datas = null;
        private NamespaceContext nsCtx = null;

        public long getIndexDuration() {
            return indexDuration;
        }

        public void reset() {
            indexDuration = 0L;
            if (cachedData != null) {
                cachedData.clear();
            }
            if (stack != null) {
                stack.clear();
            }
            //            sec = null;
            key1 = null;
            key2 = null;
            key3 = null;
            key4 = null;
            key5 = null;
            key6 = null;
            key7 = null;
            key8 = null;
            key9 = null;
            key10 = null;
            pathToResetKey1 = null;
            pathToResetKey2 = null;
            pathToResetKey3 = null;
            pathToResetKey4 = null;
            pathToResetKey5 = null;
            pathToResetKey6 = null;
            pathToResetKey7 = null;
            pathToResetKey8 = null;
            pathToResetKey9 = null;
            pathToResetKey10 = null;
        }

        public IndexerHandler(TEtat sec, String codeBudget, String codeCollectivite, String elementName, Connection con, NamespaceContext nsCtx) throws SQLException {
            super();
            this.sec = sec;
            stack = new Stack<QName>();
            this.collectivite = codeCollectivite;
            this.budget = codeBudget;
            this.docId = elementName;
            cachedData = new Hashtable<TSqlIndex, Object>();
            this.con = con;
            warnings = new StringBuffer();
            datas = new Stack<StringBuffer>();
            this.nsCtx = nsCtx;
            if (nsCtx == null) {
                throw new IllegalArgumentException("nsCtx can not be null)");
            }
        }

        @Override
        public void startElement(String uri, String localName, String qName, Attributes attributes) throws SAXException {
            QName qname = XmlUtils.getQName(uri, localName, qName);
            stack.push(qname);
            datas.push(new StringBuffer());
            String pathTag = XmlUtils.getPath(stack, nsCtx);
            for (int i = 0; i < attributes.getLength(); i++) {
                String prefix = nsCtx.getPrefix(attributes.getURI(i));
                StringBuffer sb = new StringBuffer();
                sb.append(pathTag).append("/@");
                if (prefix != null && prefix.length() > 0) {
                    sb.append(prefix).append(":");
                }
                sb.append(attributes.getLocalName(i));
                String fullPath = sb.toString();
                if (sec.getKey1() != null && sec.getKey1().getPathes().contains(fullPath)) {
                    key1 = attributes.getValue(i);
                    pathToResetKey1 = pathTag;
                }
                if (sec.getKey2() != null && sec.getKey2().getPathes().contains(fullPath)) {
                    key2 = attributes.getValue(i);
                    pathToResetKey2 = pathTag;
                }
                if (sec.getKey3() != null && sec.getKey3().getPathes().contains(fullPath)) {
                    key3 = attributes.getValue(i);
                    pathToResetKey3 = pathTag;
                }
                if (sec.getKey4() != null && sec.getKey4().getPathes().contains(fullPath)) {
                    key4 = attributes.getValue(i);
                    pathToResetKey4 = pathTag;
                }
                if (sec.getKey5() != null && sec.getKey5().getPathes().contains(fullPath)) {
                    key5 = attributes.getValue(i);
                    pathToResetKey5 = pathTag;
                }
                if (sec.getKey6() != null && sec.getKey6().getPathes().contains(fullPath)) {
                    key6 = attributes.getValue(i);
                    pathToResetKey6 = pathTag;
                }
                if (sec.getKey7() != null && sec.getKey7().getPathes().contains(fullPath)) {
                    key7 = attributes.getValue(i);
                    pathToResetKey7 = pathTag;
                }
                if (sec.getKey8() != null && sec.getKey8().getPathes().contains(fullPath)) {
                    key8 = attributes.getValue(i);
                    pathToResetKey8 = pathTag;
                }
                if (sec.getKey9() != null && sec.getKey9().getPathes().contains(fullPath)) {
                    key9 = attributes.getValue(i);
                    pathToResetKey9 = pathTag;
                }
                if (sec.getKey10() != null && sec.getKey10().getPathes().contains(fullPath)) {
                    key10 = attributes.getValue(i);
                    pathToResetKey10 = pathTag;
                }
                TSqlIndex si = sec.getSqlIndexByPath(fullPath);
                if (si != null) {
                    String sValue = attributes.getValue(i);
                    Object value = null;
                    if (TPersistenceConfig.DATATYPE_INTEGER.equals(si.getDatatype())) {
                        try {
                            value = new Integer(Integer.parseInt(sValue.trim()));
                        } catch (NumberFormatException ex) {
                            logger.warn(value + " is not an integer");
                        }
                    } else if (TPersistenceConfig.DATATYPE_DECIMAL.equals(si.getDatatype())) {
                        try {
                            value = new BigDecimal(sValue.trim());
                        } catch (NumberFormatException ex) {
                            logger.warn(value + " is not a decimal");
                        }
                    } else if (TPersistenceConfig.DATATYPE_FLOAT.equals(si.getDatatype())) {
                        try {
                            value = new Float(Float.parseFloat(sValue.trim()));
                        } catch (NumberFormatException ex) {
                            logger.warn(value + " is not a float");
                        }
                    } else if (TPersistenceConfig.DATATYPE_DATE.equals(si.getDatatype())) {
                        try {
                            value = si.getDateValue(sValue.trim());
                        } catch (java.text.ParseException ex) {
                            logger.warn(ex.getMessage());
                        }
                    } else if (TPersistenceConfig.DATATYPE_STRING.equals(si.getDatatype())) {
                        value = si.getFormattedStringValue(sValue);
                    } else if (TPersistenceConfig.DATATYPE_BOOLEAN.equals(si.getDatatype())) {
                        value = si.getBooleanValue(sValue.trim());
                    } else {
                        value = sValue;
                        logger.error("unknown datatype for index " + si.getId() + ": " + si.getDatatype());
                    }
                    if (value != null) {
                        cachedData.put(si, value);
                    }
                }
            }
        }

        @Override
        public void endElement(String uri, String localName, String qName) throws SAXException {
            //FIXME: move index writting after /text() processing
            String pathTag = XmlUtils.getPath(stack, nsCtx);
            if (pathTag.equals(pathToResetKey10)) {
                pathToResetKey10 = null;
                try {
                    writeIndexesOnKey10();
                } catch (SQLException sqlEx) {
                    this.sqlEx = sqlEx;
                    sqlEx.printStackTrace();
                    throw new SAXException("SQLException occurs. See nested exception for details)", sqlEx);
                } catch (DataConfigurationException dcEx) {
                    dcEx.printStackTrace();
                    throw new SAXException("DataConfigurationException occurs. See nested exception for details)", sqlEx);
                }
                key10 = null;
            }
            if (pathTag.equals(pathToResetKey9)) {
                pathToResetKey9 = null;
                try {
                    writeIndexesOnKey9();
                } catch (SQLException sqlEx) {
                    this.sqlEx = sqlEx;
                    sqlEx.printStackTrace();
                    throw new SAXException("SQLException occurs. See nested exception for details)", sqlEx);
                } catch (DataConfigurationException dcEx) {
                    dcEx.printStackTrace();
                    throw new SAXException("DataConfigurationException occurs. See nested exception for details)", sqlEx);
                }
                key9 = null;
            }
            if (pathTag.equals(pathToResetKey8)) {
                pathToResetKey8 = null;
                try {
                    writeIndexesOnKey8();
                } catch (SQLException sqlEx) {
                    this.sqlEx = sqlEx;
                    sqlEx.printStackTrace();
                    throw new SAXException("SQLException occurs. See nested exception for details)", sqlEx);
                } catch (DataConfigurationException dcEx) {
                    dcEx.printStackTrace();
                    throw new SAXException("DataConfigurationException occurs. See nested exception for details)", sqlEx);
                }
                key8 = null;
            }
            if (pathTag.equals(pathToResetKey7)) {
                pathToResetKey7 = null;
                try {
                    writeIndexesOnKey7();
                } catch (SQLException sqlEx) {
                    this.sqlEx = sqlEx;
                    sqlEx.printStackTrace();
                    throw new SAXException("SQLException occurs. See nested exception for details)", sqlEx);
                } catch (DataConfigurationException dcEx) {
                    dcEx.printStackTrace();
                    throw new SAXException("DataConfigurationException occurs. See nested exception for details)", sqlEx);
                }
                key7 = null;
            }
            if (pathTag.equals(pathToResetKey6)) {
                pathToResetKey6 = null;
                try {
                    writeIndexesOnKey6();
                } catch (SQLException sqlEx) {
                    this.sqlEx = sqlEx;
                    sqlEx.printStackTrace();
                    throw new SAXException("SQLException occurs. See nested exception for details)", sqlEx);
                } catch (DataConfigurationException dcEx) {
                    dcEx.printStackTrace();
                    throw new SAXException("DataConfigurationException occurs. See nested exception for details)", sqlEx);
                }
                key6 = null;
            }
            if (pathTag.equals(pathToResetKey5)) {
                pathToResetKey5 = null;
                try {
                    writeIndexesOnKey5();
                } catch (SQLException sqlEx) {
                    this.sqlEx = sqlEx;
                    sqlEx.printStackTrace();
                    throw new SAXException("SQLException occurs. See nested exception for details)", sqlEx);
                } catch (DataConfigurationException dcEx) {
                    dcEx.printStackTrace();
                    throw new SAXException("DataConfigurationException occurs. See nested exception for details)", sqlEx);
                }
                key5 = null;
            }
            if (pathTag.equals(pathToResetKey4)) {
                pathToResetKey4 = null;
                try {
                    writeIndexesOnKey4();
                } catch (SQLException sqlEx) {
                    this.sqlEx = sqlEx;
                    sqlEx.printStackTrace();
                    throw new SAXException("SQLException occurs. See nested exception for details)", sqlEx);
                } catch (DataConfigurationException dcEx) {
                    dcEx.printStackTrace();
                    throw new SAXException("DataConfigurationException occurs. See nested exception for details)", sqlEx);
                }
                key4 = null;
            }
            if (pathTag.equals(pathToResetKey3)) {
                pathToResetKey3 = null;
                try {
                    writeIndexesOnKey3();
                } catch (SQLException sqlEx) {
                    this.sqlEx = sqlEx;
                    sqlEx.printStackTrace();
                    throw new SAXException("SQLException occurs. See nested exception for details)", sqlEx);
                } catch (DataConfigurationException dcEx) {
                    dcEx.printStackTrace();
                    throw new SAXException("DataConfigurationException occurs. See nested exception for details)", sqlEx);
                }
                key3 = null;
            }
            if (pathTag.equals(pathToResetKey2)) {
                pathToResetKey2 = null;
                try {
                    writeIndexesOnKey2();
                } catch (SQLException sqlEx) {
                    this.sqlEx = sqlEx;
                    sqlEx.printStackTrace();
                    throw new SAXException("SQLException occurs. See nested exception for details)", sqlEx);
                } catch (DataConfigurationException dcEx) {
                    dcEx.printStackTrace();
                    throw new SAXException("DataConfigurationException occurs. See nested exception for details)", sqlEx);
                }
                key2 = null;
            }
            if (pathTag.equals(pathToResetKey1)) {
                pathToResetKey1 = null;
                try {
                    writeIndexesOnKey1();
                } catch (SQLException sqlEx) {
                    this.sqlEx = sqlEx;
                    sqlEx.printStackTrace();
                    throw new SAXException("SQLException occurs. See nested exception for details)", sqlEx);
                } catch (DataConfigurationException dcEx) {
                    dcEx.printStackTrace();
                    throw new SAXException("DataConfigurationException occurs. See nested exception for details)", sqlEx);
                }
                key1 = null;
            }
            String fullPath = pathTag + "/text()";
            TSqlIndex si = sec.getSqlIndexByPath(fullPath);
            if (si != null) {
                String sValue = datas.peek().toString();
                Object value = null;
                if (TPersistenceConfig.DATATYPE_INTEGER.equals(si.getDatatype())) {
                    try {
                        value = new Integer(Integer.parseInt(sValue.trim()));
                    } catch (NumberFormatException ex) {
                        logger.warn(sValue + " is not an integer");
                    }
                } else if (TPersistenceConfig.DATATYPE_DECIMAL.equals(si.getDatatype())) {
                    try {
                        value = new BigDecimal(sValue.trim());
                    } catch (NumberFormatException ex) {
                        logger.warn(sValue + " is not a decimal");
                    }
                } else if (TPersistenceConfig.DATATYPE_FLOAT.equals(si.getDatatype())) {
                    try {
                        value = new Float(Float.parseFloat(sValue.trim()));
                    } catch (NumberFormatException ex) {
                        logger.warn(sValue + " is not a float");
                    }
                } else if (TPersistenceConfig.DATATYPE_DATE.equals(si.getDatatype())) {
                    try {
                        value = si.getDateValue(sValue.trim());
                    } catch (java.text.ParseException ex) {
                        logger.warn(ex.getMessage());
                    }
                } else if (TPersistenceConfig.DATATYPE_STRING.equals(si.getDatatype())) {
                    value = si.getFormattedStringValue(sValue);
                } else if (TPersistenceConfig.DATATYPE_BOOLEAN.equals(si.getDatatype())) {
                    String sTmp = sValue.toString();
                    if ("1".equals(si.getBooleanValue(sValue))) {
                        value = Boolean.TRUE;
                    } else {
                        value = Boolean.FALSE;
                    }
                } else {
                    value = sValue;
                    logger.error("unknown datatype for index " + si.getId() + ": " + si.getDatatype());
                }
                if (value != null) {
                    cachedData.put(si, value);
                }
            }
            datas.pop();
            stack.pop();
        }

        @Override
        public void endDocument() throws SAXException {
            try {
                writeElementBasedIndexes();
                cachedData.clear();
            } catch (SQLException sqlEx) {
                sqlEx.printStackTrace();
                this.sqlEx = sqlEx;
                throw new SAXException("SQLException occurs. See nested exception for details)", sqlEx);
            } catch (DataConfigurationException dcEx) {
                dcEx.printStackTrace();
                throw new SAXException("DataConfiguration occurs. See nested exception for details", dcEx);
            }
        }

        protected void writeIndexesOnKey1() throws SQLException, DataConfigurationException {
            try {
                writeIndexesOnKey("key1");
            } catch (SQLException sqlEx) {
                logger.error(sqlEx);
                throw sqlEx;
            } catch (DataConfigurationException dcEx) {
                logger.error(dcEx);
                throw dcEx;
            }
        }

        protected void writeIndexesOnKey2() throws SQLException, DataConfigurationException {
            writeIndexesOnKey("key2");
        }

        protected void writeIndexesOnKey3() throws SQLException, DataConfigurationException {
            writeIndexesOnKey("key3");
        }

        protected void writeIndexesOnKey4() throws SQLException, DataConfigurationException {
            writeIndexesOnKey("key4");
        }

        protected void writeIndexesOnKey5() throws SQLException, DataConfigurationException {
            writeIndexesOnKey("key5");
        }

        protected void writeIndexesOnKey6() throws SQLException, DataConfigurationException {
            writeIndexesOnKey("key6");
        }

        protected void writeIndexesOnKey7() throws SQLException, DataConfigurationException {
            writeIndexesOnKey("key7");
        }

        protected void writeIndexesOnKey8() throws SQLException, DataConfigurationException {
            writeIndexesOnKey("key8");
        }

        protected void writeIndexesOnKey9() throws SQLException, DataConfigurationException {
            writeIndexesOnKey("key9");
        }

        protected void writeIndexesOnKey10() throws SQLException, DataConfigurationException {
            writeIndexesOnKey("key10");
        }

        protected void writeIndexesOnKey(String keyName) throws SQLException, DataConfigurationException {
            // First, try to get index table
            TTable elementTable = null;
            for (TTable ti : sec.getIndexTables()) {
                if (keyName.equals(ti.getBasedOn())) {
                    elementTable = ti;
                    break;
                }
            }
            if (elementTable == null) {
                throw new DataConfigurationException("No table to store " + keyName + "indexes was found !");
            }
            //
            StringBuilder sql = new StringBuilder();
            sql.append("INSERT INTO ").append(elementTable.getBaseName()).append(" (COLLECTIVITE,BUDGET,DOC_ID,");
            Vector<Object> valuesToWrite = new Vector<Object>();
            Vector<TSqlIndex> indexesToWrite = new Vector<TSqlIndex>();
            for (Enumeration<TSqlIndex> enumer = cachedData.keys(); enumer.hasMoreElements();) {
                TSqlIndex index = enumer.nextElement();
                String tableId = index.getTableId();
                TTable sti = sec.getIndexTable(tableId);
                if (sti == elementTable) {
                    // we are on the element-based table
                    sql.append(index.getColumn()).append(",");
                    indexesToWrite.add(index);
                    valuesToWrite.add(cachedData.get(index));
                    cachedData.remove(index);
                }
            }
            if (valuesToWrite.size() > 0) {
                sql.deleteCharAt(sql.length() - 1);
                sql.append(") VALUES (?,?,?,");
                for (int i = 0; i < valuesToWrite.size(); i++) {
                    sql.append("?,");
                }
                sql.deleteCharAt(sql.length() - 1);
                sql.append(")");
                PreparedStatement ps = null;
                try {
                    ps = con.prepareStatement(sql.toString());
                    ps.setString(1, collectivite);
                    ps.setString(2, budget);
                    ps.setString(3, docId);
                    for (int i = 0; i < valuesToWrite.size(); i++) {
                        TSqlIndex index = indexesToWrite.elementAt(i);
                        Object value = valuesToWrite.elementAt(i);
                        if (TPersistenceConfig.DATATYPE_DECIMAL.equals(index.getDatatype())) {
                            ps.setBigDecimal(i + 4, (BigDecimal) value);
                        } else if (TPersistenceConfig.DATATYPE_DATE.equals(index.getDatatype())) {
                            ps.setDate(i + 4, (Date) value);
                        } else if (TPersistenceConfig.DATATYPE_FLOAT.equals(index.getDatatype())) {
                            ps.setFloat(i + 4, ((Float) value).floatValue());
                        } else if (TPersistenceConfig.DATATYPE_INTEGER.equals(index.getDatatype())) {
                            ps.setInt(i + 4, ((Integer) value).intValue());
                        } else if (TPersistenceConfig.DATATYPE_BOOLEAN.equals(index.getDatatype())) {
                            ps.setInt(i + 4, ("true".equals(value) || "1".equals(value)) ? 1 : 0);
                        } else {
                            ps.setString(i + 4, value.toString());
                        }
                        cachedData.remove(index);
                    }
                    ps.executeUpdate();
                    SQLWarning warn = ps.getWarnings();
                    while (warn != null) {
                        warnings.append(warn.getMessage()).append("\n");
                        warn = warn.getNextWarning();
                    }
                } catch (SQLException sqlEx) {
                    sqlEx.printStackTrace();
                } finally {
                    if (ps != null) {
                        try {
                            ps.close();
                        } catch (Throwable t) {
                            logger.info("ps closed !", t);
                        }
                    }
                }
            }
            valuesToWrite.clear();
            indexesToWrite.clear();
        }

        protected void writeElementBasedIndexes() throws SQLException, DataConfigurationException {
            // First, try to get index table
            TTable elementTable = null;
            for (TTable ti : sec.getIndexTables()) {
                if (ti.isMainTable()) {
                    elementTable = ti;
                    break;
                }
            }
            if (elementTable == null) {
                throw new DataConfigurationException("No table to store element indexes was found !");
            }

            StringBuilder sql = new StringBuilder();
            sql.append("INSERT INTO ").append(elementTable.getBaseName()).append(" (COLLECTIVITE,BUDGET,DOC_ID,");
            Vector<Object> valuesToWrite = new Vector<Object>();
            Vector<TSqlIndex> indexesToWrite = new Vector<TSqlIndex>();
            for (Enumeration<TSqlIndex> enumer = cachedData.keys(); enumer.hasMoreElements();) {
                TSqlIndex index = enumer.nextElement();
                String tableId = index.getTableId();
                TTable sti = sec.getIndexTable(tableId);
                if (sti == elementTable) {
                    // we are on the element-based table
                    sql.append(index.getColumn()).append(",");
                    indexesToWrite.add(index);
                    valuesToWrite.add(cachedData.get(index));
                    cachedData.remove(index);
                }
            }

            if (valuesToWrite.size() > 0) {
                sql.deleteCharAt(sql.length() - 1);
                sql.append(") VALUES (?,?,?,");
                for (int i = 0; i < valuesToWrite.size(); i++) {
                    sql.append("?,");
                }
                sql.deleteCharAt(sql.length() - 1);
                sql.append(")");
                PreparedStatement ps = null;
                try {
                    ps = con.prepareStatement(sql.toString());
                    ps.setString(1, collectivite);
                    ps.setString(2, budget);
                    ps.setString(3, docId);
                    for (int i = 0; i < valuesToWrite.size(); i++) {
                        TSqlIndex index = indexesToWrite.elementAt(i);
                        Object value = valuesToWrite.elementAt(i);
                        if (TPersistenceConfig.DATATYPE_DECIMAL.equals(index.getDatatype())) {
                            ps.setBigDecimal(i + 4, (BigDecimal) value);
                        } else if (TPersistenceConfig.DATATYPE_DATE.equals(index.getDatatype())) {
                            ps.setDate(i + 4, (Date) value);
                        } else if (TPersistenceConfig.DATATYPE_FLOAT.equals(index.getDatatype())) {
                            ps.setFloat(i + 4, ((Float) value).floatValue());
                        } else if (TPersistenceConfig.DATATYPE_INTEGER.equals(index.getDatatype())) {
                            ps.setInt(i + 4, ((Integer) value).intValue());
                        } else {
                            ps.setString(i + 4, value.toString());
                        }
                    }
                    ps.executeUpdate();
                    SQLWarning warn = ps.getWarnings();
                    while (warn != null) {
                        warnings.append(warn.getMessage()).append("\n");
                        warn = warn.getNextWarning();
                    }
                } catch (SQLException sqlEx) {
                    logger.error("writeElementBasedIndexes()",sqlEx);
                    logger.error("SQL="+sql);
                    logger.error("values="+valuesToWrite);
                } finally {
                    if (ps != null) {
                        try {
                            ps.close();
                        } catch (Throwable t) {
                            logger.info("ps closed !", t);
                        }
                    }
                }
            }
            valuesToWrite.clear();
            indexesToWrite.clear();
        }

        public Exception getException() {
            return sqlEx;
        }

        /*@Override
        protected void finalize() throws Throwable {
            PoolManager.getInstance().shutdown();
            reset();
            super.finalize();
        }*/

        public String getWarnings() {
            if (warnings.length() > 0) {
                return warnings.toString();
            }
            return null;
        }

        @Override
        public void characters(char[] ch, int start, int length) throws SAXException {
            StringBuffer sb = datas.peek();
            sb.append(ch, start, length);
        }
    }

    @Override
    public TPersistenceConfig getPersistenceConfig(DocumentModel dm, XemeliosUser user) throws DataConfigurationException {
        TPersistenceConfig pc = super.getPersistenceConfig(dm, user);
        TDocument spc = pc.getLayer("mysql").getDocument(dm.getId());
        if (!checkedConfigs.contains(spc)) {
            logger.info("Chargement de la configuration " + dm.getTitre());
            boolean allOk = true;
            boolean newer = false;
            boolean older = false;
            Connection con = null;
            ArrayList<TTable> tables = new ArrayList<TTable>();
            try {
                con = getConnection();
                int ret = 0;
                if (spc.getListBcTable() != null) {
                    ret = checkTableVersion(spc.getListBcTable(), con);
                    allOk = allOk & (ret == 0);
                    if (ret < 0) {
                        older = true;
                    }
                    if (ret > 0) {
                        newer = true;
                    }
                    tables.add(spc.getListBcTable());
                }
                if (spc.getRepositoryTable() != null) {
                    ret = checkTableVersion(spc.getRepositoryTable(), con);
                    allOk = allOk & (ret == 0);
                    if (ret < 0) {
                        older = true;
                    }
                    if (ret > 0) {
                        newer = true;
                    }
                    tables.add(spc.getRepositoryTable());
                }
                if (spc.getSpecialKeyTable() != null) {
                    ret = checkTableVersion(spc.getSpecialKeyTable(), con);
                    allOk = allOk & (ret == 0);
                    if (ret < 0) {
                        older = true;
                    }
                    if (ret > 0) {
                        newer = true;
                    }
                    tables.add(spc.getSpecialKeyTable());
                }
                for (TEtat sec : spc.getEtats()) {
                    ret = checkTableVersion(sec.getDocumentTable(), con);
                    allOk = allOk & (ret == 0);
                    if (ret < 0) {
                        older = true;
                    }
                    if (ret > 0) {
                        newer = true;
                    }
                    tables.add(sec.getDocumentTable());
                    for (TTable table : sec.getIndexTables()) {
                        ret = checkTableVersion(table, con);
                        allOk = allOk & (ret == 0);
                        if (ret < 0) {
                            older = true;
                        }
                        if (ret > 0) {
                            newer = true;
                        }
                        tables.add(table);
                    }
                }
                if (!allOk) {
                    if (older) {
                        StringBuilder msg = new StringBuilder();
                        msg.append("Votre base de donnée contient des tables dont la version est plus récente\nque votre version de Xemelios.\n");
                        msg.append("Il vous faut mettre à jour votre version de Xemelios avant de pouvoir l'utiliser.");
                        throw new DataConfigurationException("Version de Xemelios trop ancienne");
                    }
                    if (newer) {
                        if (true) {
                            for (TTable table : tables) {
                                updateTableVersion(table, con);
                            }
                            checkedConfigs.add(spc);
                        } else {
                            throw new DataConfigurationException("Version de base de donnée trop ancienne");
                        }
                    }
                }
                checkedConfigs.add(spc);
            } catch (SQLException sqlEx) {
                throw new DataConfigurationException(sqlEx);
            } finally {
                if (con != null) {
                    releaseConnection(con);
                }
            }
        }
        return pc;
    }

    /**
     * Compares table version in database and in config file.
     * Returns <li>
     * <ul>x&lt;0 if database version is older than config version</ul>
     * <ul>x=0 if database version is same than config version</ul>
     * <ul>x&gt;0 if database version is newer than config</ul>
     * @param sti
     * @param con
     * @return
     */
    protected static int checkTableVersion(TTable sti, Connection con) {
        if (sti == null) {
            return 0;
        }
        PreparedStatement ps = null;
        int ret = 0;
        try {
            ps = con.prepareStatement("SELECT VERSION FROM TABLES_VERSIONS WHERE TABLE_NAME=?");
            ps.setString(1, sti.getBaseName());
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                ret = (sti.getVersion() - rs.getInt(1));
            }
        } catch (SQLException sqlEx) {
            sqlEx.printStackTrace();
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (Throwable t) {
                }
            }
        }
        return ret;
    }

    public String getLastWarnings() {
        return warnings.get(Thread.currentThread());
    }

    public void importPj(PJRef pj, XemeliosUser user) throws DataConfigurationException, DataAccessException, UnauthorizedException {
        if(!user.hasRole(XemeliosUser.ROLE_IMPORT)) throw new UnauthorizedException("Vous n'êtes pas autorisé à importer des données");
// TODO
//        if(!user.hasCollectivite(pj.getCollectivite())) throw new UnauthorizedException("Vous n'êtes pas autorisé à importer des données pour cette collectivité");
        PreparedStatement ps = null;
        ResultSet rs = null;
        Connection con = null;
        try {
            con = getConnection();
            String s = "SELECT 1 FROM ATTACHMENTS WHERE COLLECTIVITE=? AND PJ_NAME=?";
            ps = con.prepareStatement(s);
            ps.setString(1, pj.getCollectivite());
            ps.setString(2, pj.getPjName());
            rs = ps.executeQuery();
            if (rs.next()) {
                ps.close();
                ps = con.prepareStatement("DELETE FROM ATTACHMENTS WHERE COLLECTIVITE=? AND PJ_NAME=?");
                ps.setString(1, pj.getCollectivite());
                ps.setString(2, pj.getPjName());
                ps.executeUpdate();
            }
            ps.close();
            ps = con.prepareStatement("INSERT INTO ATTACHMENTS (COLLECTIVITE,PJ_NAME,FILE_NAME,DATA) VALUES (?,?,?,?)");
            ps.setString(1, pj.getCollectivite());
            ps.setString(2, pj.getPjName());
            ps.setString(3, pj.getFileName());
            ps.setBinaryStream(4, pj.getDataInputStream(), (int) pj.getUncompressedSize());
            ps.executeUpdate();
        } catch (SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } catch (Exception ex) {
            throw new DataConfigurationException(ex);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (Throwable t) {
                    logger.info("rs closed !", t);
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (Throwable t) {
                    logger.info("ps closed !", t);
                }
            }
            if (con != null) {
                releaseConnection(con);
            }
        }

    }

    public PJRef getPj(Pair collectivite, String pjName, XemeliosUser user) throws DataConfigurationException, DataAccessException, UnauthorizedException {
        if(!user.hasRole(XemeliosUser.ROLE_SEARCH)) throw new UnauthorizedException("Vous n'êtes pas autorisé à faire des recherches");
// TODO
//        if(!user.hasCollectivite(collectivite.key)) throw new UnauthorizedException("Vous n'êtes pas autorisé à rechercher des données pour cette collectivité "+collectivite.libelle!=null?"("+collectivite.libelle+")":"");
        Connection con = null;
        PreparedStatement ps = null;
        PJRef pj = null;
        logger.debug("getting " + pjName + " in " + collectivite.key);
        try {
            con = getConnection();
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT FILE_NAME, DATA FROM ATTACHMENTS WHERE COLLECTIVITE=? AND FILE_NAME=?");
            ps = con.prepareStatement(sb.toString());
            ps.setString(1, collectivite.key);
            ps.setString(2, pjName);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                pj = new PJRef();
                byte[] buff = new byte[512];
                ByteArrayOutputStream baos = new ByteArrayOutputStream();
                InputStream is = rs.getAsciiStream(2);
                int length = is.read(buff);
                while (length > 0) {
                    baos.write(buff, 0, length);
                    length = is.read(buff);
                }
                pj.setData(baos.toByteArray());
                pj.setFileName(rs.getString(1));
            }
        } catch (SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } catch (IOException ioEx) {
            throw new DataAccessException(ioEx);
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (Throwable t) {
                    logger.info("ps closed !", t);
                }
            }
            if (con != null) {
                releaseConnection(con);
            }
        }
        return pj;
    }

    public PJRef getPjByUniqueId(Pair collectivite, String id, XemeliosUser user) throws DataConfigurationException, DataAccessException, UnauthorizedException {
        if(!user.hasRole(XemeliosUser.ROLE_SEARCH)) throw new UnauthorizedException("Vous n'êtes pas autorisé à faire des recherches");
// TODO
//      if(!user.hasCollectivite(collectivite.key)) throw new UnauthorizedException("Vous n'êtes pas autorisé à rechercher des données pour cette collectivité "+collectivite.libelle!=null?"("+collectivite.libelle+")":"");
        Connection con = null;
        PreparedStatement ps = null;
        PJRef pj = null;
        logger.debug("getting " + id + " in " + collectivite.key);
        try {
            con = getConnection();
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT FILE_NAME, DATA FROM ATTACHMENTS WHERE COLLECTIVITE=? AND PJ_NAME=?");
            ps = con.prepareStatement(sb.toString());
            ps.setString(1, collectivite.key);
            ps.setString(2, id);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                pj = new PJRef();
                byte[] buff = new byte[512];
                ByteArrayOutputStream baos = new ByteArrayOutputStream();
                InputStream is = rs.getAsciiStream(2);
                int length = is.read(buff);
                while (length > 0) {
                    baos.write(buff, 0, length);
                    length = is.read(buff);
                }
                pj.setData(baos.toByteArray());
                pj.setFileName(rs.getString(1));
            }
        } catch (SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } catch (IOException ioEx) {
            throw new DataAccessException(ioEx);
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (Throwable t) {
                    logger.info("ps closed !", t);
                }
            }
            if (con != null) {
                releaseConnection(con);
            }
        }
        return pj;
    }

    public boolean isPjAvailable(Pair collectivite, String id, XemeliosUser user) throws DataConfigurationException, DataAccessException, UnauthorizedException {
        if(!user.hasRole(XemeliosUser.ROLE_SEARCH)) throw new UnauthorizedException("Vous n'êtes pas autorisé à faire des recherches");
// TODO
//      if(!user.hasCollectivite(collectivite.key)) throw new UnauthorizedException("Vous n'êtes pas autorisé à rechercher des données pour cette collectivité "+collectivite.libelle!=null?"("+collectivite.libelle+")":"");
        Connection con = null;
        boolean ret = false;
        PreparedStatement ps = null;
        try {
            con = getConnection();
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT 1 FROM ATTACHMENTS WHERE COLLECTIVITE=? AND FILE_NAME=?");
            ps = con.prepareStatement(sb.toString());
            ps.setString(1, collectivite.key);
            ps.setString(2, id);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                ret = true;
            }
            rs.close();
        } catch (SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (Throwable t) {
                    logger.info("ps closed !", t);
                }
            }
            if (con != null) {
                releaseConnection(con);
            }
        }
        return ret;
    }

    public ArrayList<String> getDocumentListFromSpecialKeys(ElementModel em, String collectivite, String budget, String sp1, String sp2, String sp3, XemeliosUser user) throws DataConfigurationException, DataAccessException, UnauthorizedException {
        if(!user.hasRole(XemeliosUser.ROLE_SEARCH)) throw new UnauthorizedException("Vous n'êtes pas autorisé à faire des recherches");
        if(!user.hasDocument(em.getParent().getParent().getId())) throw new UnauthorizedException("Vous n'êtes pas autorisé à rechercher des "+em.getTitre());
        if(!user.hasCollectivite(collectivite,em.getParent().getParent())) throw new UnauthorizedException("Vous n'êtes pas autorisé à rechercher des données pour cette collectivité");
        ArrayList<String> ret = new ArrayList<String>();
        StringBuffer sb = new StringBuffer();
        EtatModel etatModel = em.getParent();
        TDocument pc = getPersistenceConfig(etatModel.getParent(), user).getLayer("mysql").getDocument(etatModel.getParent().getId());
        TEtat sec = pc.getEtat(etatModel.getId());
        sb.append("SELECT DOC_ID FROM ").append(sec.getMaintable().getBaseName()).append(" WHERE COLLECTIVITE=? AND BUDGET=? ");
        DocumentModel dm = etatModel.getParent();
        Vector<SpecialKeyModel> specialKeys = dm.getSpecialKeys();
        if (sp1 != null) {
            // search SPK1 XPath
            SpecialKeyModel skm = null;
            for (SpecialKeyModel sk : specialKeys) {
                if (sk.getPos() == 1) {
                    skm = sk;
                }
            }
            if (skm != null) {
                String path = skm.getPath();
                TSqlIndex si = sec.getSqlIndexByPath(path);
                if (si.getTableId().equals(sec.getMaintable().getId())) {
                    // ok, it is the same table
                    sb.append("AND ").append(si.getColumn()).append("=? ");
                } else {
                    throw new DataConfigurationException(path + " is not indexed in " + sec.getMaintable().getBaseName());
                }
            } else {
                throw new DataConfigurationException("no special key in pos 1 defined for " + dm.getId());
            }
        }
        if (sp2 != null) {
            // search SPK1 XPath
            SpecialKeyModel skm = null;
            for (SpecialKeyModel sk : specialKeys) {
                if (sk.getPos() == 2) {
                    skm = sk;
                }
            }
            if (skm != null) {
                String path = skm.getPath();
                TSqlIndex si = sec.getSqlIndexByPath(path);
                if (si.getTableId().equals(sec.getMaintable().getId())) {
                    // ok, it is the same table
                    sb.append("AND ").append(si.getColumn()).append("=? ");
                } else {
                    throw new DataConfigurationException(path + " is not indexed in " + sec.getMaintable().getBaseName());
                }
            } else {
                throw new DataConfigurationException("no special key in pos 2 defined for " + dm.getId());
            }
        }
        if (sp3 != null) {
            // search SPK3 XPath
            SpecialKeyModel skm = null;
            for (SpecialKeyModel sk : specialKeys) {
                if (sk.getPos() == 3) {
                    skm = sk;
                }
            }
            if (skm != null) {
                String path = skm.getPath();
                TSqlIndex si = sec.getSqlIndexByPath(path);
                if (si.getTableId().equals(sec.getMaintable().getId())) {
                    // ok, it is the same table
                    sb.append("AND ").append(si.getColumn()).append("=? ");
                } else {
                    throw new DataConfigurationException(path + " is not indexed in " + sec.getMaintable().getBaseName());
                }
            } else {
                throw new DataConfigurationException("no special key in pos 3 defined for " + dm.getId());
            }
        }
        logger.debug(sb.toString());
        // well, now sql is complete
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            con = getConnection();
            ps = con.prepareStatement(sb.toString());
            ps.setString(1, collectivite);
            logger.debug("1->" + collectivite);
            ps.setString(2, budget);
            logger.debug("2->" + budget);
            if (sp1 != null) {
                ps.setString(3, sp1);
                logger.debug("3->" + sp1);
            }
            if (sp2 != null) {
                ps.setString(4, sp2);
            }
            if (sp3 != null) {
                ps.setString(5, sp3);
            }
            rs = ps.executeQuery();
            while (rs.next()) {
                ret.add(rs.getString(1));
            }
        } catch (SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (Throwable t) {
                    logger.info("rs closed !", t);
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (Throwable t) {
                    logger.info("ps closed !", t);
                }
            }
            if (con != null) {
                releaseConnection(con);
            }
        }
        return ret;
    }

    public ArrayList<String> getDocumentListFromXPath(ElementModel em, String collectivite, String budget, String sp1, String sp2, String sp3, String xpath, XemeliosUser user) throws DataConfigurationException, DataAccessException, UnauthorizedException {
        if(!user.hasRole(XemeliosUser.ROLE_SEARCH)) throw new UnauthorizedException("Vous n'êtes pas autorisé à faire des recherches");
        if(!user.hasDocument(em.getParent().getParent().getId())) throw new UnauthorizedException("Vous n'êtes pas autorisé à rechercher des "+em.getTitre());
        if(!user.hasCollectivite(collectivite,em.getParent().getParent())) throw new UnauthorizedException("Vous n'êtes pas autorisé à rechercher des données pour cette collectivité");
        ArrayList<String> ret = new ArrayList<String>();
        StringBuffer selectClause = new StringBuffer();
        StringBuffer fromClause = new StringBuffer();
        StringBuffer whereClause = new StringBuffer();
        ArrayList<String> addedTableNames = new ArrayList<String>();
        EtatModel etatModel = em.getParent();
        TDocument pc = getPersistenceConfig(etatModel.getParent(), user).getLayer("mysql").getDocument(etatModel.getParent().getId());
        TEtat sec = pc.getEtat(etatModel.getId());
        selectClause.append("SELECT ").append(sec.getMaintable().getBaseName()).append(".DOC_ID ");
        fromClause.append("FROM ").append(sec.getMaintable().getBaseName()).append(" ");
        addedTableNames.add(sec.getMaintable().getId());
        whereClause.append(" WHERE ").append(sec.getMaintable().getBaseName()).append(".COLLECTIVITE=? AND ").append(sec.getMaintable().getBaseName()).append(".BUDGET=? ");
        DocumentModel dm = etatModel.getParent();
        Vector<SpecialKeyModel> specialKeys = dm.getSpecialKeys();
        if (sp1 != null) {
            // search SPK1 XPath
            SpecialKeyModel skm = null;
            for (SpecialKeyModel sk : specialKeys) {
                if (sk.getPos() == 1) {
                    skm = sk;
                }
            }
            if (skm != null) {
                String path = skm.getPath();
                TSqlIndex si = sec.getSqlIndexByPath(path);
                String tName = sec.getIndexTable(si.getTableId()).getBaseName();
                whereClause.append("AND ").append(tName).append(".").append(si.getColumn()).append("=? ");
                if (!addedTableNames.contains(si.getTableId())) {
                    fromClause.append(" LEFT OUTER JOIN ").append(tName).append(" ON ").append(sec.getMaintable().getBaseName()).append(".DOC_ID=").append(tName).append(".DOC_ID AND ").append(sec.getMaintable().getBaseName()).append(".COLLECTIVITE=").append(tName).append(".COLLECTIVITE AND ").append(sec.getMaintable().getBaseName()).append(".BUDGET=").append(tName).append(".BUDGET");
                    addedTableNames.add(si.getTableId());
                }
            } else {
                throw new DataConfigurationException("no special key in pos 1 defined for " + dm.getId());
            }
        }
        if (sp2 != null) {
            // search SPK1 XPath
            SpecialKeyModel skm = null;
            for (SpecialKeyModel sk : specialKeys) {
                if (sk.getPos() == 2) {
                    skm = sk;
                }
            }
            if (skm != null) {
                String path = skm.getPath();
                TSqlIndex si = sec.getSqlIndexByPath(path);
                String tName = sec.getIndexTable(si.getTableId()).getBaseName();
                whereClause.append("AND ").append(tName).append(".").append(si.getColumn()).append("=? ");
                if (!addedTableNames.contains(si.getTableId())) {
                    fromClause.append(" LEFT OUTER JOIN ").append(tName).append(" ON ").append(sec.getMaintable().getBaseName()).append(".DOC_ID=").append(tName).append(".DOC_ID AND ").append(sec.getMaintable().getBaseName()).append(".COLLECTIVITE=").append(tName).append(".COLLECTIVITE AND ").append(sec.getMaintable().getBaseName()).append(".BUDGET=").append(tName).append(".BUDGET");
                    addedTableNames.add(si.getTableId());
                }
            } else {
                throw new DataConfigurationException("no special key in pos 2 defined for " + dm.getId());
            }
        }
        if (sp3 != null) {
            // search SPK3 XPath
            SpecialKeyModel skm = null;
            for (SpecialKeyModel sk : specialKeys) {
                if (sk.getPos() == 3) {
                    skm = sk;
                }
            }
            if (skm != null) {
                String path = skm.getPath();
                TSqlIndex si = sec.getSqlIndexByPath(path);
                String tName = sec.getIndexTable(si.getTableId()).getBaseName();
                whereClause.append("AND ").append(tName).append(".").append(si.getColumn()).append("=? ");
                if (!addedTableNames.contains(si.getTableId())) {
                    fromClause.append(" LEFT OUTER JOIN ").append(tName).append(" ON ").append(sec.getMaintable().getBaseName()).append(".DOC_ID=").append(tName).append(".DOC_ID AND ").append(sec.getMaintable().getBaseName()).append(".COLLECTIVITE=").append(tName).append(".COLLECTIVITE AND ").append(sec.getMaintable().getBaseName()).append(".BUDGET=").append(tName).append(".BUDGET");
                    addedTableNames.add(si.getTableId());
                }
            } else {
                throw new DataConfigurationException("no special key in pos 3 defined for " + dm.getId());
            }
        }
        if (xpath != null) {
            /*
             * on suppose que le xpath est de la forme /toto/titi/tata[tutu/@V=x1][@tete=x2]...
             * ou /toto/titi/tata est le chemin de l'element a chercher (pour simplifier encore)
             * on va chercher a isoler les chemins et les valeurs :
             * /toto/titi/tata/tutu/@V -> x1
             * /toto/titi/tata/@tete   -> x2
             * Si ces chemins sont indexes, alors on pourra ajouter des clauses where dans le sql.
             * Cette syntaxe devrait etre suffisante pour gÃ©rer la navigation dans le compte de gestion.
             * C'est du bricolage, mais je n'ai pas d'autre solution pour le moment
             */
            logger.debug("xpath is " + xpath);
            if (xpath.startsWith(em.getPath().getPath())) {
                final String shortPath = xpath.substring(em.getPath().getPath().length());
                if (!shortPath.startsWith("[")) {
                    throw new DataConfigurationException("XPath too complicated. This implementation can only deal with XPath formed by <path-to-element>[cond on sub-element][cond on sub element]...");
                }
                // decoupage par groupe de [...]
                String[] conds = splitConditions(shortPath);
                for (String s : conds) {
                    logger.debug(s);
                    // pour le moment, on cherche les opérateurs <= >= = < >
                    s = s.substring(1, s.length() - 1);
                    String op = null;
                    int pos = -1;
                    pos = s.indexOf("<=");
                    if (pos >= 0) {
                        op = "<=";
                    } else {
                        pos = s.indexOf(">=");
                        if (pos >= 0) {
                            op = ">=";
                        } else {
                            pos = s.indexOf('=');
                            if (pos >= 0) {
                                op = "=";
                            } else {
                                pos = s.indexOf('<');
                                if (pos >= 0) {
                                    op = "<";
                                } else {
                                    pos = s.indexOf('>');
                                    if (pos >= 0) {
                                        op = ">";
                                    }
                                }
                            }
                        }
                    }
                    String left = s.substring(0, pos);
                    String right = s.substring(pos + op.length());
                    String indexedPath = em.getPath().getPath() + "/" + left;
                    logger.debug(indexedPath + " " + op + " " + right);
                    //
                    TSqlIndex si = sec.getSqlIndexByPath(indexedPath);
                    if (si != null) {
                        String tName = sec.getIndexTable(si.getTableId()).getBaseName();
                        String value = null;
                        if("string".equals(si.getDatatype())) {
                            value = sqlEscapeString(si.getFormattedStringValue(right));
                            if(!value.startsWith("'") && !value.endsWith("'"))
                                value = "'".concat(value).concat("'");
                        } else {
                            value = sqlEscapeString(right);
                        }
                        whereClause.append("AND ").append(tName).append(".").append(si.getColumn()).append(op).append(value).append(" ");
                        if (!addedTableNames.contains(si.getTableId())) {
                            fromClause.append(" LEFT OUTER JOIN ").append(tName).append(" ON ").append(sec.getMaintable().getBaseName()).append(".DOC_ID=").append(tName).append(".DOC_ID AND ").append(sec.getMaintable().getBaseName()).append(".COLLECTIVITE=").append(tName).append(".COLLECTIVITE AND ").append(sec.getMaintable().getBaseName()).append(".BUDGET=").append(tName).append(".BUDGET");
                            addedTableNames.add(si.getTableId());
                        }
                    } else {
                        throw new DataConfigurationException("path not indexed: " + indexedPath);
                    }
                }
            } else {
                throw new DataConfigurationException("XPath too complicated. only XPath starting with element path are allowed. Here, XPath should start with " + em.getPath().getPath());
            }
        }
        String sql = selectClause.append(fromClause.toString()).append(whereClause.toString()).toString();
        logger.debug(sql);
        // well, now sql is complete
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            con = getConnection();
            ps = con.prepareStatement(sql);
            ps.setString(1, collectivite);
            logger.debug("1(coll)->" + collectivite);
            ps.setString(2, budget);
            logger.debug("2(budg)->" + budget);
            if (sp1 != null) {
                ps.setString(3, sp1);
                logger.debug("3(sp1 )->" + sp1);
            }
            if (sp2 != null) {
                ps.setString(4, sp2);
                logger.debug("4(sp2 )->" + sp2);
            }
            if (sp3 != null) {
                ps.setString(5, sp3);
                logger.debug("5(sp3 )->" + sp3);
            }
            rs = ps.executeQuery();
            while (rs.next()) {
                ret.add(rs.getString(1));
            }
        } catch (SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (Throwable t) {
                    logger.info("rs closed !", t);
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (Throwable t) {
                    logger.info("ps closed !", t);
                }
            }
            if (con != null) {
                releaseConnection(con);
            }
        }
        return ret;
    }

    private static String[] splitConditions(final String cond) throws DataConfigurationException {
        ArrayList<String> ret = new ArrayList<String>();
        String s = cond;
        while (s.length() > 0 && s.charAt(0) == '[') {
            int end = findClosingParenthesis(s, 0, "[]");
            ret.add(s.substring(0, end + 1));
            s = s.substring(end + 1);
        }
        String aRet[] = new String[ret.size()];
        ret.toArray(aRet);
        return aRet;
    }

    /**
     * This method searches for the closing parenthesis that matches the specified one.
     * <br><b>Warning&nbsp;:</b>&nbsp;it assumes that the character pointed out by <code>first</code>
     * <b>is actually</b> an openning parenthesis.
     * @param expr The expression to search through.
     * @param first Index of the openning parenthesis
     * @return Index of the closing parenthesis, if found.
     * @throws ParsingException if no closing parenthesis were found.
     */
    private static int findClosingParenthesis(String expr, int first, String delimitors) throws DataConfigurationException {
        final char OPENNING_DELIM = delimitors.charAt(0);
        final char CLOSING_DELIM = delimitors.charAt(1);
        int len = expr.length();
        int close = -1;
        int pCount = 1;
        for (int i = (first + 1); i < len; i++) {
            char c = expr.charAt(i);
            if (c == OPENNING_DELIM) {
                pCount++;
            } else if (c == CLOSING_DELIM) {
                pCount--;
            }
            if (pCount == 0) {
                close = i;
                break;
            }
        }
        if (close == (-1)) {
            //            ParsingException pEx = new ParsingException(ParsingException.UNBALANCED_PARENTHESIS);
            //            fireParsingEvent(first,expr,pEx,null);
            throw new DataConfigurationException("unbalanced delimitor");
        }
        return close;
    }

    public SimpleDateFormat getDateFormatter() {
        return mySqlDateFormat;
    }

    /**
     * This methods returns the list of operators that can be applied to the given data type,
     * for a MySQL database.
     * @param datatype The type of data we want the operators for
     * @return an array of Pair object. The first part of the pair is the MySQL expression
     * that performs the operation, the second attribute of the pair is the textual
     * representation of the operator.
     */
    public Pair[] getAggregateOperators(String datatype) {
        ArrayList<Pair> result = new ArrayList<Pair>();
        logger.debug("Requested aggregate operators for data type " + datatype);
        if (datatype.equals(ChampModel.DATATYPE_INTEGER) || datatype.equals(ChampModel.DATATYPE_DECIMAL) || datatype.equals(ChampModel.DATATYPE_AMOUNT)) {
            result.add(new Pair("AVG", "Moyenne"));
            result.add(new Pair("SUM", "Somme"));
            result.add(new Pair("MIN", "Minimum"));
            result.add(new Pair("MAX", "Maximum"));
            result.add(new Pair("STDDEV_POP", "Ecart type"));
            result.add(new Pair("VAR_POP", "Variance"));
        } else if (datatype.equals(ChampModel.DATATYPE_DATE)) {
            result.add(new Pair("SEC_TO_TIME(SUM(TIME_TO_SEC", "Somme"));
            result.add(new Pair("SEC_TO_TIME(AVG(TIME_TO_SEC", "Moyenne"));
        }

        Pair[] aRet = new Pair[result.size()];
        result.toArray(aRet);
        return aRet;
    }

    public String calculateAggregate(DocumentModel dm, EtatModel em, String columnXPath, String operator, ChampModel cm, DataResultSet rs, XemeliosUser user)
            throws DataConfigurationException, DataAccessException, UnauthorizedException {
        if(!user.hasRole(XemeliosUser.ROLE_SEARCH)) throw new UnauthorizedException("Vous n'êtes pas autorisé à faire des recherches");
        if(!user.hasDocument(dm.getId())) throw new UnauthorizedException("Vous n'êtes pas autorisé à rechercher des "+em.getTitre());
        if(!user.hasCollectivite(rs.getCollectivite().key,dm)) throw new UnauthorizedException("Vous n'êtes pas autorisé à rechercher des "+em.getTitre()+" pour cette collectivité "+rs.getCollectivite().libelle!=null?"("+rs.getCollectivite().libelle+")":"");
        TPersistenceConfig pers = this.getPersistenceConfig(dm, user);
        TLayer layer = pers.getLayer(this.getLayerName());
        TDocument tdoc = layer.getDocument(dm.getId());
        TEtat tet = tdoc.getEtat(em.getId());
        // parfois, il y a des informations de position dans les xpath d'affichage de colonne, il faut les supprimer
        columnXPath = columnXPath.replaceAll("\\[[0-9]\\]", "");
        logger.debug(columnXPath);
        TSqlIndex tsqlIndex = tet.getSqlIndexByPath(columnXPath);
        if (tsqlIndex == null) {
            throw new DataConfigurationException("Pas d'index sur " + columnXPath);
        }
        String columnName = tsqlIndex.getColumn();
        String tableName = tet.getIndexTable(tsqlIndex.getTableId()).getBaseName();

        if (rs != null) {
            MySqlDataResultSet myRs = (MySqlDataResultSet) rs;
            return myRs.calculateAggregate(tableName + "." + columnName, tableName, operator, cm);
        } else {
            return "";
        }
    }

    private void checkParamTable() {
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            con = getConnection();
            st = con.createStatement();
            rs = st.executeQuery("SELECT 1 FROM " + PARAM_TABLE_NAME);
            rs.close();
        } catch (SQLException sqlEx) {
            if (sqlEx.getErrorCode() == 1146) {
                // Table does not exists
                try {
                    st.executeUpdate("CREATE TABLE " + PARAM_TABLE_NAME + " (PARAM_NAME VARCHAR(100) NOT NULL, PARAM_VALUE VARCHAR(100), UNIQUE INDEX PARAM_IX (PARAM_NAME)) TYPE MYISAM");
                } catch (Throwable t) {
                    logger.error("while creating " + PARAM_TABLE_NAME, t);
                }
            }
        } finally {
            if (st != null) {
                try {
                    st.close();
                } catch (Throwable t) {
                }
            }
            if (con != null) {
                releaseConnection(con);
            }
        }
    }

    /**
     * Returns the value of defined parameter.
     * Returns null if parameter is not defined.
     * @param paramName the Name of parameter to retreive value
     * @return The param value or null if parameter is not defined
     */
    public String getParameterValue(final String paramName) {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String ret = null;
        try {
            con = getConnection();
            ps = con.prepareStatement("SELECT PARAM_VALUE FROM " + PARAM_TABLE_NAME + " WHERE PARAM_NAME=?");
            ps.setString(1, paramName);
            rs = ps.executeQuery();
            if (rs.next()) {
                ret = rs.getString(1);
            }
        } catch (SQLException sqlEx) {
            logger.error("in getParameterValue", sqlEx);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (Throwable t) {
                    logger.info("rs closed !", t);
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (Throwable t) {
                    logger.info("ps closed !", t);
                }
            }
            if (con != null) {
                releaseConnection(con);
            }
        }
        return ret;
    }

    public void setParameterValue(final String paramName, final String paramValue) {
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = getConnection();
            String ret = getParameterValue(paramName);
            if (ret == null) {
                ps = con.prepareStatement("INSERT INTO " + PARAM_TABLE_NAME + " (PARAM_VALUE, PARAM_NAME) VALUES (?, ?)");
            } else {
                ps = con.prepareStatement("UPDATE " + PARAM_TABLE_NAME + " SET PARAM_VALUE=? WHERE PARAM_NAME=?");
            }
            ps.setString(1, paramValue);
            ps.setString(2, paramName);
            ps.executeUpdate();
        } catch (SQLException sqlEx) {
            logger.error("in getParameterValue", sqlEx);
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (Throwable t) {
                    logger.info("ps closed !", t);
                }
            }
            if (con != null) {
                releaseConnection(con);
            }
        }
    }

    private void applyPatches() {
        if(getParameterValue(RepositoryPatch.PARAM_REPOSITORY_PATCH)==null) {
            RepositoryPatch patcher = new RepositoryPatch(this);
            patcher.run();
        }
        if (getParameterValue(PayePatch32.PARAM_PAYE_PATCH_32) == null) {
            PayePatch32 patcher = new PayePatch32(this);
            patcher.run();
        }
        if (getParameterValue(VersementPatch32.PARAM_VERSEMENT_PATCH_32) == null) {
            VersementPatch32 patcher = new VersementPatch32(this);
            patcher.run();
        }
        if (getParameterValue(PesAllerPatch33.PARAM_PESV2_PATCH_33) == null) {
            PesAllerPatch33 patcher = new PesAllerPatch33(this);
            patcher.run();
        }
        if (getParameterValue(RapportPatch33.PARAM_RAPPORT_PATCH_33) == null) {
            RapportPatch33 patcher = new RapportPatch33(this);
            patcher.run();
        }
        // add here other patches
        killPatchProgress();
    }

    public void insertConfigExport(ConfigModel cm, XemeliosUser user) {
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = getConnection();
            ps = con.prepareStatement("INSERT INTO CONFIGS_EXPORT (DOC_ID,ETAT_ID,OWNER,DATE_CREA,CODE,LIBELLE,CONFIG) VALUES (?,?,?,CURRENT_DATE(),?,?,?)");
            int i = 1;
            ps.setString(i++, cm.getDocID());
            ps.setString(i++, cm.getEtatID());
            ps.setString(i++, user.getId());
            ps.setString(i++, cm.getCode());
            ps.setString(i++, cm.getLibelle());
            byte[] buffer = cm.getConfigLines();
            InputStream is = new ByteArrayInputStream(buffer);
            ps.setBinaryStream(i++, is, buffer.length);
            ps.executeUpdate();
        } catch (Exception e) {
            logger.error("in insertConfigExport ", e);
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (Throwable t) {
                    logger.info("ps closed !", t);
                }
            }
            if (con != null) {
                releaseConnection(con);
            }
        }
    }

    public void updateConfigExport(ConfigModel cm, XemeliosUser user) {
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = getConnection();
            ps = con.prepareStatement("UPDATE CONFIGS_EXPORT set CODE=?, LIBELLE=?, CONFIG=? WHERE ID=? AND DOC_ID=? AND ETAT_ID=?");
            int i = 1;
            ps.setString(i++, cm.getCode());
            ps.setString(i++, cm.getLibelle());
            byte[] buffer = cm.getConfigLines();
            InputStream is = new ByteArrayInputStream(buffer);
            ps.setBinaryStream(i++, is, buffer.length);
            ps.setString(i++, cm.getId());
            ps.setString(i++, cm.getDocID());
            ps.setString(i++, cm.getEtatID());
            ps.executeUpdate();
        } catch (Exception e) {
            logger.error("in updateConfigExport ", e);
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (Throwable t) {
                    logger.info("ps closed !", t);
                }
            }
            if (con != null) {
                releaseConnection(con);
            }
        }
    }

    public Vector<ConfigModel> getListeConfigsExport(String docID, String etatID, XemeliosUser user) {
        Connection con = null;
        PreparedStatement ps = null;
        Vector<ConfigModel> ret = new Vector<ConfigModel>();
        try {
            con = getConnection();
            ps = con.prepareStatement("SELECT ID,DOC_ID,ETAT_ID,OWNER,DATE_CREA,CODE,LIBELLE,CONFIG FROM CONFIGS_EXPORT WHERE DOC_ID=? AND ETAT_ID=? ORDER BY CODE");
            int i = 1;
            ps.setString(i++, docID);
            ps.setString(i++, etatID);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                ret.add(new ConfigModel(rs));
            }
        } catch (Exception e) {
            logger.error("in getListeConfigsExport ", e);
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (Throwable t) {
                    logger.info("ps closed !", t);
                }
            }
            if (con != null) {
                releaseConnection(con);
            }
        }
// DEBUG
//        XmlOutputter xo = new XmlOutputter(new OutputStreamWriter(System.out),"ISO-8859-1");
//        for(ConfigModel cm:ret) {
//            cm.marshall(xo);
//        }
//        try { xo.getWriter().flush(); } catch(Throwable t) {}
// END DEBUG
        return ret;
    }
    public ConfigModel getConfigExport(String docId, String etatId, String configId, XemeliosUser user) {
        Connection con = null;
        PreparedStatement ps = null;
        ConfigModel ret = null;
        try {
            con = getConnection();
            ps = con.prepareStatement("SELECT ID,DOC_ID,ETAT_ID,OWNER,DATE_CREA,CODE,LIBELLE,CONFIG FROM CONFIGS_EXPORT WHERE DOC_ID=? AND ETAT_ID=? ANd ID=? ORDER BY CODE");
            int i = 1;
            ps.setString(i++, docId);
            ps.setString(i++, etatId);
            ps.setString(i++,configId);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                ret = new ConfigModel(rs);
            }
        } catch (Exception e) {
            logger.error("in getConfigExport ", e);
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (Throwable t) {
                    logger.info("ps closed !", t);
                }
            }
            if (con != null) {
                releaseConnection(con);
            }
        }
        return ret;

    }

    public void deleteConfigExport(ConfigModel cm, XemeliosUser user) {
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = getConnection();
            ps = con.prepareStatement("DELETE FROM CONFIGS_EXPORT WHERE ID=? AND DOC_ID=? AND ETAT_ID=?");
            int i = 1;
            ps.setString(i++, cm.getId());
            ps.setString(i++, cm.getDocID());
            ps.setString(i++, cm.getEtatID());
            ps.executeUpdate();
        } catch (Exception e) {
            logger.error("in deleteConfigExport ", e);
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (Throwable t) {
                    logger.info("ps closed !", t);
                }
            }
            if (con != null) {
                releaseConnection(con);
            }
        }
    }

    public ExportableData searchFichiers(DocumentModel dm, String coll, String budg, XemeliosUser user) throws DataAccessException, DataConfigurationException, UnauthorizedException {
        if(!user.hasRole(XemeliosUser.ROLE_EXPORT)) throw new UnauthorizedException("Vous n'êtes pas autorisé à faire des exports");
        if(!user.hasDocument(dm.getId())) throw new UnauthorizedException("Vous n'êtes pas autorisé à exporter des "+dm.getTitre());
        if(!user.hasCollectivite(coll,dm)) throw new UnauthorizedException("Vous n'êtes pas autorisé à exporter des "+dm.getTitre()+" pour cette collectivité");
        ExportableData dD = null;
        TDocument pc = getPersistenceConfig(dm, user).getLayer("mysql").getDocument(dm.getId());
        Connection con = null;
        PreparedStatement ps = null;
        boolean found = false;
        try {
            con = getConnection();
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT COLLECTIVITE, COLLECTIVITE_LIB, BUDGET, BUDGET_LIB, INITIAL_DOC_NAME FROM ").append(pc.getListBcTable().getBaseName());
            if (coll != null) {
                sb.append(" WHERE COLLECTIVITE=? ");
            }
            if (budg != null) {
                if (coll == null) {
                    sb.append(" WHERE ");
                } else {
                    sb.append(" AND ");
                }
                sb.append(" BUDGET = ? ");
            }
            sb.append(" ORDER BY COLLECTIVITE_LIB, BUDGET_LIB, INITIAL_DOC_NAME");
            ps = con.prepareStatement(sb.toString());
            int i = 1;
            if (coll != null) {
                ps.setString(i++, coll);
            }
            if (budg != null) {
                ps.setString(i++, budg);
            }
            ResultSet rs = ps.executeQuery();
            String lastColl = "", lastBudg = "";

            while (rs.next()) {
                if (!found) {
                    dD = new ExportableData();
                    dD.setKey(dm.getId());
                    dD.setLevel(ExportableData.LVL_DOC);
                    dD.setLibelle(dm.getTitre());
                    dD.setSelected(false);
                }
                found = true;
                String curColl = rs.getString(1);
                String curBudg = rs.getString(3);

                ExportableData dF = new ExportableData();
                dF.setKey(dm.getId() + "|" + rs.getString(1) + "|" + rs.getString(3) + "|" + rs.getString(5));
                dF.setLevel(ExportableData.LVL_FIC);
                dF.setLibelle(rs.getString(5));
                dF.setSelected(false);

                ExportableData dC = null;
                if (!curColl.equals(lastColl)) {
                    dC = new ExportableData();
                    dC.setKey(dm.getId() + "|" + rs.getString(1));
                    dC.setLevel(ExportableData.LVL_COL);
                    dC.setLibelle(rs.getString(1) + " - " + rs.getString(2));
                    dC.setSelected(false);
                    dD.addChild(dC);
                    // Puisque la collectivitÃ© a changÃ©, il faut considÃ©rer le budget comme diffÃ©rent lui aussi
                    lastBudg = "";
                } else {
                    dC = dD.getChild(dm.getId() + "|" + rs.getString(1));
                }
                ExportableData dB = null;
                if (!curBudg.equals(lastBudg)) {
                    dB = new ExportableData();
                    dB.setKey(dm.getId() + "|" + rs.getString(1) + "|" + rs.getString(3));
                    dB.setLevel(ExportableData.LVL_BUD);
                    dB.setLibelle(rs.getString(3) + " - " + rs.getString(4));
                    dB.setSelected(false);
                    dC.addChild(dB);
                } else {
                    dB = dC.getChild(dm.getId() + "|" + rs.getString(1) + "|" + rs.getString(3));
                }
                dB.addChild(dF);
                lastBudg = curBudg;
                lastColl = curColl;
            }
        } catch (SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (Throwable t) {
                    logger.info("ps closed !", t);
                }
            }
            if (con != null) {
                releaseConnection(con);
            }
        }
        return (found ? dD : null);
    }

    public Vector<ExportableData> searchFichiers(DocumentsModel dms, String coll, String budg, XemeliosUser user) throws DataAccessException, DataConfigurationException, UnauthorizedException {
        Vector<ExportableData> ret = new Vector<ExportableData>();

        for (DocumentModel doc : dms.getDocuments()) {
            try {
                ExportableData tmp = searchFichiers(doc, coll, budg, user);
                if (tmp != null) {
                    ret.add(tmp);
                }
            } catch (DataAccessException dae) {
                if (dae.getMessage().endsWith("doesn't exist")); else {
                    throw dae;
                }
            }
        }

        return ret;
    }

    protected String buildSelectToExport(int whatToExport, ElementModel elementModel, Pair coll, Pair budg, String fic, Pair sk1, Pair sk2, XemeliosUser user) throws DataConfigurationException {
        EtatModel etatModel = elementModel.getParent();
        DocumentModel documentModel = etatModel.getParent();

        TDocument pc = getPersistenceConfig(documentModel, user).getLayer("mysql").getDocument(documentModel.getId());
        TEtat sec = pc.getEtat(etatModel.getId());
        TTable mainTable = sec.getMaintable();
        TTable docTable = sec.getDocumentTable();

        StringBuffer sb = new StringBuffer();

        switch (whatToExport) {
            case EXPORT_BUD: {
                sb.append("SELECT ").append(docTable.getBaseName()).append(".DOC_ID FROM ").append(docTable.getBaseName());
                sb.append(" WHERE ").append(docTable.getBaseName()).append(".COLLECTIVITE='").append(StringEscapeUtils.escapeSql(coll.key)).append("'");
                sb.append(" AND ").append(docTable.getBaseName()).append(".BUDGET='").append(StringEscapeUtils.escapeSql(budg.key)).append("'");
                sb.append(" ");
                break;
            }
            case EXPORT_SK1: {
                sb.append("SELECT ").append(docTable.getBaseName()).append(".DOC_ID FROM ").append(docTable.getBaseName());
                sb.append(" LEFT OUTER JOIN ").append(pc.getSpecialKeyTable().getBaseName()).append(" ON ");
                sb.append(docTable.getBaseName()).append(".INITIAL_DOC_NAME=").append(pc.getSpecialKeyTable().getBaseName()).append(".INITIAL_DOC_NAME ");
                sb.append(" AND ").append(docTable.getBaseName()).append(".COLLECTIVITE=").append(pc.getSpecialKeyTable().getBaseName()).append(".COLLECTIVITE ");
                sb.append(" AND ").append(docTable.getBaseName()).append(".BUDGET=").append(pc.getSpecialKeyTable().getBaseName()).append(".BUDGET ");
                sb.append(" WHERE ").append(docTable.getBaseName()).append(".COLLECTIVITE='").append(StringEscapeUtils.escapeSql(coll.key)).append("'");
                sb.append(" AND ").append(docTable.getBaseName()).append(".BUDGET='").append(StringEscapeUtils.escapeSql(budg.key)).append("'");
                sb.append(" AND ").append(pc.getSpecialKeyTable().getBaseName()).append(".KEY1='").append(StringEscapeUtils.escapeSql(sk1.key)).append("'");
                sb.append(" ");
                break;
            }
            case EXPORT_SK2: {
                sb.append("SELECT ").append(docTable.getBaseName()).append(".DOC_ID FROM ").append(docTable.getBaseName());
                sb.append(" LEFT OUTER JOIN ").append(pc.getSpecialKeyTable().getBaseName()).append(" ON ");
                sb.append(docTable.getBaseName()).append(".INITIAL_DOC_NAME=").append(pc.getSpecialKeyTable().getBaseName()).append(".INITIAL_DOC_NAME ");
                sb.append(" AND ").append(docTable.getBaseName()).append(".COLLECTIVITE=").append(pc.getSpecialKeyTable().getBaseName()).append(".COLLECTIVITE ");
                sb.append(" AND ").append(docTable.getBaseName()).append(".BUDGET=").append(pc.getSpecialKeyTable().getBaseName()).append(".BUDGET ");
                sb.append(" WHERE ").append(docTable.getBaseName()).append(".COLLECTIVITE='").append(StringEscapeUtils.escapeSql(coll.key)).append("'");
                sb.append(" AND ").append(docTable.getBaseName()).append(".BUDGET='").append(StringEscapeUtils.escapeSql(budg.key)).append("'");
                sb.append(" AND ").append(pc.getSpecialKeyTable().getBaseName()).append(".KEY1='").append(StringEscapeUtils.escapeSql(sk1.key)).append("'");
                sb.append(" AND ").append(pc.getSpecialKeyTable().getBaseName()).append(".KEY2='").append(StringEscapeUtils.escapeSql(sk2.key)).append("'");
                sb.append(" ");
                break;
            }
            case EXPORT_FIC: {
                sb.append("SELECT ").append(docTable.getBaseName()).append(".DOC_ID FROM ").append(docTable.getBaseName());
                sb.append(" WHERE ").append(docTable.getBaseName()).append(".COLLECTIVITE='").append(StringEscapeUtils.escapeSql(coll.key)).append("'");
                sb.append(" AND ").append(docTable.getBaseName()).append(".BUDGET='").append(StringEscapeUtils.escapeSql(budg.key)).append("'");
                sb.append(" AND ").append(docTable.getBaseName()).append(".INITIAL_DOC_NAME='").append(StringEscapeUtils.escapeSql(fic)).append("'");
                sb.append(" ");
                break;
            }
        }

        return sb.toString();
    }

    public DataResultSet getDataResultSetForExport(int whatToExport, ElementModel em, Pair coll, Pair budg, String fic, Pair SK1, Pair SK2, XemeliosUser user) throws DataConfigurationException, SQLException, UnauthorizedException {
        if(!user.hasRole(XemeliosUser.ROLE_EXPORT)) throw new UnauthorizedException("Vous n'êtes pas autorisé à faire des exports");
        if(!user.hasDocument(em.getParent().getParent().getId())) throw new UnauthorizedException("Vous n'êtes pas autorisé à exporter des "+em.getTitre());
        if(!user.hasCollectivite(coll.key,em.getParent().getParent())) throw new UnauthorizedException("Vous n'êtes pas autorisé à rechercher des "+em.getTitre()+" pour cette collectivité "+coll.libelle!=null?"("+coll.libelle+")":"");
        String sql = buildSelectToExport(whatToExport, em, coll, budg, fic, SK1, SK2, user);
        ListeResultatModel lrm = em.getListeResultat();

        MySqlDataResultSet drs = new MySqlDataResultSet(em, coll, budg, em.getPath().getPath(), sql, lrm, (MySqlDataLayer) DataLayerManager.getImplementation(), user);

        return drs;
    }

    private class HSWStorageHandle {

        Class[] classes; // liste des classes des objets stockes
        Hashtable<String, PreparedStatement> requetes; // les requetes
        Connection con;

        HSWStorageHandle(Hashtable<String, PreparedStatement> preqs, Class[] pclasses, Connection con) {
            classes = pclasses;
            requetes = preqs;
            this.con = con;
        }
    };

    /**
     *  initTempStorage 
     *  
     *  @param storageId = nom d etable temporaire
     *  @return  Object = un type opaque
     *  (en fait  un  HSWStorageHandle )
     *  
     */
    public Object HSWinitTempStorage(String storageId, String keyId, int maxKeyLength, Vector<java.lang.reflect.Field> fieldList) {
        // Parcours fieldList pour en deduire les types et les longiuers des champs 
        Class[] classes = null;
        int[] sizes = null;
        String names[] = null;

        if (fieldList != null) {
            classes = new Class[fieldList.size()];
            sizes = new int[fieldList.size()];
            names = new String[fieldList.size()];

            for (int i = 0; i < fieldList.size(); i++) {
                Field fld = fieldList.get(i);
                String typeName = fld.getType().toString();
                LongueurMax anno;

                if (typeName.equals("class java.lang.String")) {
                    Integer longueurMax = 255; // surdimensionner par defaut
                    // On va chercher l'annotation pour connaitre la longueur max
                    anno = fld.getAnnotation(LongueurMax.class);
                    if (anno != null) {
                        longueurMax = anno.value();
                    } else {
                        throw new RuntimeException("The field " + fld.getName() + " has no annotation @LongueurMax(value = xxx)");
                    }

                    classes[i] = String.class;
                    sizes[i] = longueurMax;

                } else if (typeName.equals("int") || typeName.equals("class java.lang.Integer")) {
                    Integer longueurMax = 10; // surdimensionner par defaut
                    // On va chercher l'annotation pour connaitre la longueur max
                    anno = fld.getAnnotation(LongueurMax.class);
                    if (anno != null) {
                        longueurMax = anno.value();
                    }
                    classes[i] = Integer.class;
                    sizes[i] = longueurMax;
                } else if (typeName.equals("float") || typeName.equals("class java.lang.Float")) {
                    Integer longueurMax = 10; // surdimensionner par defaut
                    // On va chercher l'annotation pour connaitre la longueur max
                    anno = fld.getAnnotation(LongueurMax.class);
                    if (anno != null) {
                        longueurMax = anno.value();
                    }
                    classes[i] = Float.class;
                    sizes[i] = longueurMax;
                } else if (typeName.equals("double") || typeName.equals("class java.lang.Double")) {
                    Integer longueurMax = 10; // surdimensionner par defaut
                    // On va chercher l'annotation pour connaitre la longueur max
                    anno = fld.getAnnotation(LongueurMax.class);
                    if (anno != null) {
                        longueurMax = anno.value();
                    }
                    classes[i] = Float.class;
                    sizes[i] = longueurMax;
                } else if (typeName.equals("class java.util.Date")) {

                    classes[i] = java.util.Date.class;
                    sizes[i] = 999; // irrelevant;
                }
                names[i] = fld.getName();
            }
        }
        return HSWinitTempStorage(storageId, keyId, maxKeyLength, classes, sizes, names);

    }

    /**
     *  initTempStorage 
     *  
     *  @param storageId = nom d etable temporaire
     *  @return  Object = un type opaque
     *  (en fait  un HSWStorageHandle)
     *  
     */
    public Object HSWinitTempStorage(String storageId, String keyId, int maxKeyLength, Class[] classes, int[] sizes, String names[]) {

        Connection HSWcon = null;
        Hashtable<String, PreparedStatement> reqs = new Hashtable<String, PreparedStatement>();

        StringBuffer sqlCreateBuffer = new StringBuffer();
        StringBuffer sqlInsertBuffer = new StringBuffer();
        StringBuffer sqlInsertValuesBuffer = new StringBuffer();
        StringBuffer sqlSelectBuffer = new StringBuffer();
        StringBuffer sqlExistsBuffer = new StringBuffer();
        StringBuffer sqlCountBuffer = new StringBuffer();
        StringBuffer sqlDeleteBuffer = new StringBuffer();
        StringBuffer sqlDropBuffer = new StringBuffer();

        String virgule = ",";
        sqlCreateBuffer.append("CREATE TEMPORARY TABLE " + storageId + "(\n");
        sqlInsertBuffer.append("INSERT INTO " + storageId + "(\n");
        sqlInsertValuesBuffer.append("VALUES(\n");
        sqlExistsBuffer.append("SELECT COUNT(*) FROM " + storageId + "\n");
        sqlSelectBuffer.append("SELECT \n");
        sqlDeleteBuffer.append("DELETE FROM " + storageId + "\n");
        sqlDropBuffer.append("DROP TABLE " + storageId + "\n");
        sqlCountBuffer.append("SELECT COUNT(*) FROM " + storageId + "\n");

        if (classes != null) {
            // la cle 
            sqlCreateBuffer.append(keyId + " VARCHAR(" + maxKeyLength + ") UNIQUE");
            sqlInsertBuffer.append(keyId);
            sqlInsertValuesBuffer.append("?");
            sqlSelectBuffer.append(keyId);
            sqlExistsBuffer.append("WHERE " + keyId + " = ? ");
            sqlDeleteBuffer.append("WHERE " + keyId + " = ? ");

            for (int i = 0; i < classes.length; i++) {
                Class cls = classes[i];
                Integer longueurMax = sizes[i];
                sqlCreateBuffer.append(virgule);
                sqlCreateBuffer.append("\n");
                sqlCreateBuffer.append(names[i] + " ");

                sqlInsertBuffer.append(virgule);
                sqlInsertBuffer.append("\n");
                sqlInsertBuffer.append(names[i] + " ");
                sqlInsertValuesBuffer.append(virgule);
                sqlInsertValuesBuffer.append("\n");
                sqlInsertValuesBuffer.append("?");


                sqlSelectBuffer.append(virgule);
                sqlSelectBuffer.append("\n");
                sqlSelectBuffer.append(names[i] + " ");

                String typeName = cls.toString();
                if (typeName.equals("class java.lang.String")) {
                    sqlCreateBuffer.append("VARCHAR(" + longueurMax.toString() + ")");

                } else if (typeName.equals("int") || typeName.equals("class java.lang.Integer")) {
                    sqlCreateBuffer.append("INTEGER(" + longueurMax.toString() + ")");
                } else if (typeName.equals("float") || typeName.equals("class java.lang.Float")) {
                    sqlCreateBuffer.append("FLOAT(" + longueurMax.toString() + ")");
                } else if (typeName.equals("double") || typeName.equals("class java.lang.Double")) {
                    sqlCreateBuffer.append("DOUBLE(" + longueurMax.toString() + ")");
                } else if (typeName.equals("class java.util.Date")) {
                    sqlCreateBuffer.append("DATE");
                }
            }
            sqlCreateBuffer.append("\n)\n");
            sqlInsertBuffer.append("\n)\n");
            sqlInsertValuesBuffer.append("\n)\n");

            sqlSelectBuffer.append(" FROM " + storageId + " WHERE  " + keyId + " = ? ");



            try {
                HSWcon = getConnection();


                reqs.put("Create", HSWcon.prepareStatement(sqlCreateBuffer.toString()));
                reqs.put("Insert", HSWcon.prepareStatement(sqlInsertBuffer.toString() + sqlInsertValuesBuffer.toString()));
                reqs.put("Exists", HSWcon.prepareStatement(sqlExistsBuffer.toString()));
                reqs.put("Select", HSWcon.prepareStatement(sqlSelectBuffer.toString()));
                reqs.put("Delete", HSWcon.prepareStatement(sqlDeleteBuffer.toString()));
                reqs.put("Drop", HSWcon.prepareStatement(sqlDropBuffer.toString()));
                reqs.put("Count", HSWcon.prepareStatement(sqlCountBuffer.toString()));
            } catch (SQLException se) {
                logger.error("init : SQL Exception:", se);
            } catch (Exception e) {
                logger.error("init: other exception", e);
            }
        }

        return new HSWStorageHandle(reqs, classes, HSWcon);
    }

    public void HSWdoCreateStorage(Object pStorageHandle) {
        if (pStorageHandle != null && pStorageHandle instanceof HSWStorageHandle) {
            HSWStorageHandle storageHandle = (HSWStorageHandle) pStorageHandle;
            Hashtable<String, PreparedStatement> ht = storageHandle.requetes;


            try {
                PreparedStatement ps = ht.get("Create");// HSWcon.prepareStatement(sqlcreate);
                ps.executeUpdate();
            } catch (SQLException se) {
                logger.error("Create SQL Exception:", se);
            } catch (Exception e) {
                logger.error(e);
            }
        }
    }

    public void HSWdoInsert(Object pStorageHandle, String key, Object[] values) {
        if (pStorageHandle != null && pStorageHandle instanceof HSWStorageHandle) {
            HSWStorageHandle storageHandle = (HSWStorageHandle) pStorageHandle;
            Hashtable<String, PreparedStatement> ht = storageHandle.requetes;
            try {
                PreparedStatement ps = ht.get("Insert"); //HSWcon.prepareStatement(sqlInsert);
                ps.setString(1, key);
                for (int i = 0; i < values.length; ++i) {
                    int j = i + 2;
                    if (values[i] instanceof String) {
                        ps.setString(j, (String) values[i]);
                    //
                    } else if (values[i] instanceof java.lang.Integer) {
                        ps.setInt(j, (Integer) values[i]);
                    //
                    } else if (values[i] instanceof java.lang.Float) {
                        ps.setFloat(j, (Float) values[i]);
                    // S
                    } else if (values[i] instanceof java.util.Date) {
                        java.sql.Date sqlDate = new java.sql.Date(((java.util.Date) values[i]).getTime());
                        ps.setDate(j, sqlDate);
                    }
                }
                ps.executeUpdate();
            } catch (SQLException se) {
                logger.error("doInsert SQL Exception:", se);
            } catch (Exception e) {
                logger.error(e);
            }
        }
    }

    public boolean HSWdoExists(Object pStorageHandle, String key) {
        boolean ret = false;
        PreparedStatement ps = null;
        if (pStorageHandle != null && pStorageHandle instanceof HSWStorageHandle) {
            HSWStorageHandle storageHandle = (HSWStorageHandle) pStorageHandle;
            try {
                Hashtable<String, PreparedStatement> ht = storageHandle.requetes;
                ps = ht.get("Exists");//HSWcon.prepareStatement(sqlexists);
                ps.setString(1, key);
                ResultSet rs = ps.executeQuery();
                if (rs.next()) {
                    int count = rs.getInt(1);
                    if (count > 0) {
                        ret = true;
                    }
                }

            } catch (SQLException se) {
                logger.error("doExists SQL Exception:", se);
            } catch (Exception e) {
                logger.error(e);
//            } finally {
//                if(ps!=null) try { ps.close(); } catch(Throwable t) { logger.info("ps closed !", t);}
            }
        }
        return ret;
    }

    public Object[] HSWdoSelect(Object pStorageHandle, String key, Object[] inoutObj) {
        Object[] ret = null;

        if (pStorageHandle != null && pStorageHandle instanceof HSWStorageHandle) {
            HSWStorageHandle storageHandle = (HSWStorageHandle) pStorageHandle;
            PreparedStatement ps = null;
            try {
                Hashtable<String, PreparedStatement> ht = storageHandle.requetes;
                Class[] classes = storageHandle.classes;
                ps = ht.get("Select"); //HSWcon.prepareStatement(sqlselect);
                ps.setString(1, key);
                ResultSet rs = ps.executeQuery();
                if (rs.next()) {
                    for (int i = 0; i < classes.length; i++) {
                        int j = i + 2;
                        Class cls = classes[i];
                        String typeName = cls.toString();
                        if (typeName.equals("class java.lang.String")) {
                            inoutObj[i] = rs.getString(j);

                        } else if (typeName.equals("int") || typeName.equals("class java.lang.Integer")) {
                            inoutObj[i] = rs.getInt(j);
                        } else if (typeName.equals("float") || typeName.equals("class java.lang.Float")) {
                            inoutObj[i] = rs.getFloat(j);
                        } else if (typeName.equals("double") || typeName.equals("class java.lang.Double")) {
                            inoutObj[i] = rs.getDouble(j);
                        } else if (typeName.equals("class java.util.Date")) {

                            java.sql.Date sqlDate = rs.getDate(j);
                            inoutObj[i] = sqlDate;
                        }
                    }
                    ret = inoutObj;
                }
            } catch (SQLException se) {
                logger.error("doSelect SQL Exception:", se);
            } catch (Exception e) {
                logger.error(e);
//            } finally {
//                if(ps!=null) try { ps.close(); } catch(Throwable t) { logger.info("ps closed !", t);}
            }
        }
        return ret;
    }

    public boolean HSWdoDelete(Object pStorageHandle, String key) {
        boolean ret = false;
        if (pStorageHandle != null && pStorageHandle instanceof HSWStorageHandle) {
            HSWStorageHandle storageHandle = (HSWStorageHandle) pStorageHandle;
            PreparedStatement ps = null;
            try {
                Hashtable<String, PreparedStatement> ht = storageHandle.requetes;
                ps = ht.get("Delete");//HSWcon.prepareStatement(sqldelete);
                ps.setString(1, key);
                ps.executeUpdate();
                ret = true;
            } catch (SQLException se) {
                logger.error("doDelete SQL Exception:", se);
            } catch (Exception e) {
                logger.error(e);
//            } finally {
//                if(ps!=null) try { ps.close(); } catch(Throwable t) { logger.info("ps closed !", t);}
            }
        }
        return ret;
    }

    public int HSWdoCount(Object pStorageHandle) {
        int ret = -1;
        if (pStorageHandle != null && pStorageHandle instanceof HSWStorageHandle) {
            HSWStorageHandle storageHandle = (HSWStorageHandle) pStorageHandle;
            PreparedStatement ps = null;
            try {
                Hashtable<String, PreparedStatement> ht = storageHandle.requetes;
                ps = ht.get("Count");//HSWcon.prepareStatement(sqlcount);
                ResultSet rs = ps.executeQuery();
                if (rs.next()) {
                    ret = rs.getInt(1);
                }
            } catch (SQLException se) {
                logger.error("doCount SQL Exception:", se);
            } catch (Exception e) {
                logger.error(e);
//            } finally {
//                if(ps!=null) try { ps.close(); } catch(Throwable t) { logger.info("ps closed !", t);}
            }
        }
        return ret;
    }

    public boolean HSWdoDrop(Object pStorageHandle) {
        boolean ret = false;
        if (pStorageHandle != null && pStorageHandle instanceof HSWStorageHandle) {
            HSWStorageHandle storageHandle = (HSWStorageHandle) pStorageHandle;
            Connection HSWcon = null;
            PreparedStatement ps = null;
            try {
                Hashtable<String, PreparedStatement> ht = storageHandle.requetes;
                ps = ht.get("Drop"); //HSWcon.prepareStatement(sqldrop);
                HSWcon = storageHandle.con;
                ps.executeUpdate();
            } catch (SQLException se) {
                logger.error("doDrop : SQL Exception:", se);
            } catch (Exception e) {
                logger.error(e);
            } finally {
                if(ps!=null) try { ps.close(); } catch(Throwable t) { logger.info("ps closed !", t);}
                if (HSWcon != null) {
                    releaseConnection(HSWcon);
                    storageHandle.con = null;
                }
            }
        }
        return ret;
    }

    public boolean isLocalComputerServer() {
        String driver = props.getProperty("driver.class");
        // mysql embedded
        if ("com.mysql.embedded.jdbc.MySqlEmbeddedDriver".equals(driver)) {
            return true;
        } else if ("com.mysql.jdbc.Driver".equals(driver)) {
            String url = props.getProperty("database.url");
            // url is jdbc:mysql://localhost/xemelios or 
            url = url.substring(13);
            int pipePos = url.indexOf(':');
            int slashPos = url.indexOf('/');
            if (pipePos < 0) {
                pipePos = 10000;
            }
            if (slashPos < 0) {
                slashPos = 10000;
            }
            int pos = Math.min(pipePos, slashPos);
            if (pos > url.length()) {
                pos = url.length();
            }
            String server = url.substring(0, pos);
            try {
                InetAddress serverAddress = InetAddress.getByName(server);
                InetAddress localAddress = InetAddress.getLocalHost();
                return localAddress.equals(serverAddress);
            } catch (UnknownHostException uhEx) {
                logger.error(null, uhEx);
            }
        } else {
            // unknown driver
            throw new RuntimeException("no support is provided for this driver. Please see MySqlDataLayer.isLocalComputerServer() and provide adapted code.");
        }
        return false;
    }

    public static String sqlEscapeString(String data) {
        StringBuilder ret = new StringBuilder();
        if (data.startsWith("'") && data.endsWith("'")) {
            ret.append("'");
            ret.append(sqlEscapeString(data.substring(1, data.length() - 1)));
            ret.append("'");
        } else {
            ret.append(data.replaceAll("'", "''"));
        }
        return ret.toString();
    }

    private enum UPDATES_MODES {
        UPDATE, INSERT
    }

    public List<RechercheModel> getSavedRequests(ElementModel em, XemeliosUser user, boolean includePublics) {
        ArrayList<RechercheModel> ret = new ArrayList<RechercheModel>();
        String key = em.getKey();
        StringBuffer sql = new StringBuffer("SELECT REQUEST FROM SAVED_REQUESTS WHERE KEY_EM=? AND ");

        if(includePublics) {
            sql.append("(USER_ID=? OR PUBLIC=1)");
        } else {
            sql.append("USER_ID=?");
        }

        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = getConnection();
            ps = con.prepareStatement(sql.toString());
            ps.setString(1,key);
            ps.setString(2,user.getId());
            ResultSet rs = ps.executeQuery();
            PersonnalConfigParser pcp = new PersonnalConfigParser();
            while(rs.next()) {
                InputStream is = rs.getBinaryStream(1);
                ByteArrayOutputStream baos = new ByteArrayOutputStream();
                InputStreamLogger wrapper = new InputStreamLogger(is, baos);
                pcp.parse(wrapper);
                RechercheModel rm = (RechercheModel)pcp.getMarshallable();
                ret.add(rm);
                logger.debug(baos.toString("ISO-8859-1"));
            }
        } catch(SQLException sqlEx) {
            logger.error("getSavedRequests(ElementModel,XemeliosUser,boolean)",sqlEx);
        } catch(SAXException saxEx) {
            logger.error("getSavedRequests(ElementModel,XemeliosUser,boolean)",saxEx);
        } catch(ParserConfigurationException pcEx) {
            logger.error("getSavedRequests(ElementModel,XemeliosUser,boolean)",pcEx);
        } catch(IOException ioEx) {
            logger.error("getSavedRequests(ElementModel,XemeliosUser,boolean)",ioEx);
        } finally {
            if(ps!=null) {
                try {
                    ps.close();
                } catch(SQLException sqlEx) {
                    logger.info("getSavedRequests(ElementModel,XemeliosUser,boolean)",sqlEx);
                }
            }
            if(con!=null) releaseConnection(con);
        }
        return ret;
    }

    public void saveRequest(ElementModel em, RechercheModel recherche, XemeliosUser user) {
        String key = em.getKey();

        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = getConnection();
            UPDATES_MODES updateMode = UPDATES_MODES.INSERT;
            if(recherche.getId()!=null) {
                updateMode = UPDATES_MODES.UPDATE;
            } else {
                String title = recherche.getDisplayName().replaceAll(" ","");
                if(title.length()>200) title = title.substring(0, 199);
                recherche.setId(title.concat(Long.toHexString(System.currentTimeMillis())));
            }
            String sql;
            if(updateMode==UPDATES_MODES.INSERT) {
                sql = "INSERT INTO SAVED_REQUESTS (REQUEST, KEY_EM, USER_ID, TITLE, PUBLIC, ID) VALUES (?,?,?,?,0,?)";
            } else {
                sql = "UPDATE SAVED_REQUESTS SET REQUEST=? WHERE KEY_EM=? AND USER_ID=? AND TITLE=? AND ID=?";
            }
            ps=con.prepareStatement(sql);
            ByteArrayOutputStream baos = new ByteArrayOutputStream();
            OutputStreamWriter out = new OutputStreamWriter(baos);
            XmlOutputter outputter = new XmlOutputter(out, "ISO-8859-1");
            recherche.marshall(outputter);
            out.flush(); out.close();
            InputStream is = new ByteArrayInputStream(baos.toByteArray());
            ps.setBinaryStream(1, is, baos.size());
            ps.setString(2,key);
            ps.setString(3,user.getId());
            ps.setString(4, recherche.getDisplayName());
            ps.setString(5,recherche.getId());
            int ret = ps.executeUpdate();
            if(ret!=1) {
                throw new SQLException("unexpected update result: "+ret+" row(s) updated, only 1 expected");
            }
        } catch(Exception ex) {
            logger.error("saveRequest(ElementModel,RechercheModel,XemeliosUser)",ex);
        } finally {
            if(ps!=null) {
                try {
                    ps.close();
                } catch(SQLException sqlEx) {
                    logger.info("saveRequest(ElementModel,RechercheModel,XemeliosUser)",sqlEx);
                }
            }
            if(con!=null) releaseConnection(con);
        }
    }

    public RechercheModel getSavedRequest(ElementModel em, XemeliosUser user, String searchId) {
        RechercheModel ret = null;
        String key = em.getKey();
        StringBuffer sql = new StringBuffer("SELECT REQUEST FROM SAVED_REQUESTS WHERE KEY_EM=? AND ID=?");
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = getConnection();
            ps = con.prepareStatement(sql.toString());
            ps.setString(1,key);
//            ps.setString(2,user.getId());
            ps.setString(2,searchId);
            ResultSet rs = ps.executeQuery();
            PersonnalConfigParser pcp = new PersonnalConfigParser();
            if(rs.next()) {
                InputStream is = rs.getBinaryStream(1);
                ByteArrayOutputStream baos = new ByteArrayOutputStream();
                InputStreamLogger wrapper = new InputStreamLogger(is, baos);
                pcp.parse(wrapper);
                ret = (RechercheModel)pcp.getMarshallable();
                logger.debug(baos.toString("ISO-8859-1"));
            }
        } catch(SQLException sqlEx) {
            logger.error("getSavedRequest(ElementModel,XemeliosUser,String)",sqlEx);
        } catch(SAXException saxEx) {
            logger.error("getSavedRequest(ElementModel,XemeliosUser,String)",saxEx);
        } catch(ParserConfigurationException pcEx) {
            logger.error("getSavedRequest(ElementModel,XemeliosUser,String)",pcEx);
        } catch(IOException ioEx) {
            logger.error("getSavedRequest(ElementModel,XemeliosUser,String)",ioEx);
        } finally {
            if(ps!=null) {
                try {
                    ps.close();
                } catch(SQLException sqlEx) {
                    logger.info("getSavedRequest(ElementModel,XemeliosUser,String)",sqlEx);
                }
            }
            if(con!=null) releaseConnection(con);
        }
        return ret;
    }

    public void createExportJob(ExportJob job, XemeliosUser user) throws DataConfigurationException, DataAccessException, UnauthorizedException {
        if(!user.hasRole(XemeliosUser.ROLE_EXPORT)) throw new UnauthorizedException("Vous n'avez pas les droits pour réaliser des exports.");
        Connection con = null;
        try {
            con = getConnection();
            String sql = "INSERT INTO EXPORT_JOBS (UTI_LOGIN,FILE_NAME,EXPORT_NAME,DOC_ID,ETAT_ID,ELEMENT_ID,CONFIG_ID,SIZE,STATUS,SUBMIT_DATE,DRS) VALUES (?,?,?,?,?,?,?,?,?,NOW(),?)";
            PreparedStatement ps = con.prepareStatement(sql);
            int i=1;
            ps.setString(i++,user.getId());
            ps.setString(i++,job.getFileName());
            ps.setString(i++,job.getExportName());
            ps.setString(i++,job.getDocumentId());
            ps.setString(i++,job.getEtatId());
            ps.setString(i++,job.getElementId());
            ps.setLong(i++,job.getExportConfigId());
            ps.setInt(i++,job.getSize());
            ps.setInt(i++,ExportJob.STATUS_SUBMIT);
            byte[] buffer = job.getDrs();
            ps.setBinaryStream(i++, new ByteArrayInputStream(buffer), buffer.length);
            ps.executeUpdate();
            ps.close();
        } catch(Exception ex) {
            logger.error("createExportJob(ExportJob,XemeliosUser)",ex);
            throw new DataAccessException(ex);
        } finally {
            if(con!=null) releaseConnection(con);
        }
    }

    public List<ExportJob> getExportJobList(XemeliosUser user) throws DataConfigurationException, DataAccessException, UnauthorizedException {
        ArrayList<ExportJob> ret = new ArrayList<ExportJob>();
        Connection con = null;
        try {
            con = getConnection();
            String sql = "SELECT "+
                "EXPORT_ID, "+
                "FILE_NAME, "+
                "EXPORT_NAME, "+
                "DOC_ID, "+
                "ETAT_ID, "+
                "ELEMENT_ID, "+
                "CONFIG_ID, "+
                "SIZE, "+
                "PRIORITY, "+
                "STATUS, "+
                "SUBMIT_DATE, "+
                "RUN_DATE, "+
                "DOWNLOAD_DATE, "+
                "ERROR_MESSAGE, "+
                "GENERATED_FILE_NAME, "+
                "CLEANED "+
                "FROM EXPORT_JOBS "+
                "WHERE UTI_LOGIN=? "+
                "AND (DOWNLOAD_DATE IS NULL OR DOWNLOAD_DATE<DATE_ADD(CURDATE(),INTERVAL '7' DAY))";
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setString(1,user.getId());
            ResultSet rs = ps.executeQuery();
            while(rs.next()) {
                ret.add(new ExportJobImpl(rs,false));
            }
        } catch(SQLException sqlEx) {
            logger.error("getExportJobs(XemeliosUser)",sqlEx);
        } finally {
            if(con!=null) releaseConnection(con);
        }
        return ret;
    }
    public ExportJob getExportJob(long exportId)  throws DataConfigurationException, DataAccessException, UnauthorizedException {
        ExportJob ret = null;
        Connection con = null;
        try {
            con = getConnection();
            String sql = "SELECT "+
                "EXPORT_ID, "+
                "FILE_NAME, "+
                "EXPORT_NAME, "+
                "DOC_ID, "+
                "ETAT_ID, "+
                "ELEMENT_ID, "+
                "CONFIG_ID, "+
                "SIZE, "+
                "PRIORITY, "+
                "STATUS, "+
                "SUBMIT_DATE, "+
                "RUN_DATE, "+
                "DOWNLOAD_DATE, "+
                "ERROR_MESSAGE, "+
                "GENERATED_FILE_NAME, "+
                "CLEANED, "+
                "DRS "+
                "FROM EXPORT_JOBS "+
                "WHERE EXPORT_ID=? ";
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setLong(1,exportId);
            ResultSet rs = ps.executeQuery();
            while(rs.next()) {
                ret=new ExportJobImpl(rs,true);
            }
        } catch(SQLException sqlEx) {
            logger.error("getExportJobs(XemeliosUser)",sqlEx);
        } finally {
            if(con!=null) releaseConnection(con);
        }
        return ret;
        
    }
}
