/*
 * Copyright
 *   2007 axYus - www.axyus.com
 *   2005 C.Marchand - christophe.marchand@axyus.com
 *
 * This file is part of XEMELIOS.
 *
 * XEMELIOS is free software; you can redistribute it and/or modify
 * it under the terms of the GNU Lesser General Public License as published by
 * the Free Software Foundation; either version 3 of the License, or
 * (at your option) any later version.
 *
 * XEMELIOS is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public License
 * along with XEMELIOS; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
 */

package fr.gouv.finances.cp.xemelios.data.impl.mysql;

import fr.gouv.finances.cp.utils.xml.marshal.InvalidXmlDefinition;
import fr.gouv.finances.cp.utils.xml.marshal.XmlAttributes;
import fr.gouv.finances.cp.utils.xml.marshal.XmlMarshallable;
import fr.gouv.finances.cp.utils.xml.marshal.XmlOutputter;
import fr.gouv.finances.dgfip.xemelios.common.Constants;
import fr.gouv.finances.dgfip.xemelios.common.config.DocumentModel;
import fr.gouv.finances.dgfip.xemelios.data.DocumentInfos;
import fr.gouv.finances.cp.xemelios.data.impl.MySqlDataLayer;
import fr.gouv.finances.cp.xemelios.data.impl.mysql.serialization.MysqlDrsParser;
import fr.gouv.finances.cp.xemelios.data.impl.mysql.serialization.UserModel;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Hashtable;
import java.util.LinkedList;
import java.util.NoSuchElementException;
import java.util.StringTokenizer;

import javax.xml.namespace.QName;
import javax.xml.xpath.XPath;
import javax.xml.xpath.XPathConstants;
import javax.xml.xpath.XPathFactory;

import org.apache.log4j.Logger;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.NodeList;

import fr.gouv.finances.dgfip.utils.Amount;
import fr.gouv.finances.dgfip.utils.Pair;
import fr.gouv.finances.dgfip.utils.xml.XWay;
import fr.gouv.finances.dgfip.utils.xml.xpath.FunctionResolver;
import fr.gouv.finances.dgfip.xemelios.auth.UnauthorizedException;
import fr.gouv.finances.dgfip.xemelios.auth.XemeliosUser;
import fr.gouv.finances.dgfip.xemelios.common.config.ChampModel;
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.ListeResultatModel;
import fr.gouv.finances.dgfip.xemelios.common.config.XPathModel;
import fr.gouv.finances.dgfip.xemelios.data.DataAccessException;
import fr.gouv.finances.dgfip.xemelios.data.DataConfigurationException;
import fr.gouv.finances.dgfip.xemelios.data.DataResultSet;
import fr.gouv.finances.dgfip.xemelios.data.impl.sqlconfig.TDocument;
import fr.gouv.finances.dgfip.xemelios.data.impl.sqlconfig.TElement;
import fr.gouv.finances.dgfip.xemelios.data.impl.sqlconfig.TEtat;
import fr.gouv.finances.dgfip.xemelios.data.impl.sqlconfig.TSqlIndex;
import fr.gouv.finances.dgfip.xemelios.data.CachedData;
import fr.gouv.finances.dgfip.xemelios.data.DataHandler;
import fr.gouv.finances.dgfip.xemelios.data.DataLayerManager;
import java.io.ByteArrayOutputStream;
import java.io.OutputStreamWriter;
import java.nio.charset.Charset;
import java.util.List;
import org.xml.sax.Attributes;
import org.xml.sax.SAXException;

/**
 * MySql specific implementation of {@link fr.gouv.finances.cp.xemelios.data.DataResultSet}
 * @author chm
 */
public class MySqlDataResultSet implements DataResultSet, XmlMarshallable {
    public static final QName QN = new QName("mysql-drs");
    
    private static Logger logger = Logger.getLogger(MySqlDataResultSet.class);
    // TODO : set cache size from outside parameters
    private static final int CACHE_SIZE = 1;
    
//    private DocumentModel documentModel = null;
    private EtatModel etatModel = null;
    private ElementModel elementModel = null;
    private Pair collectivite=null, budget=null;
    private String xPath = null, sql = null;
    private String whereClause = "", fromClause = "", selectClause = "";
    private MySqlDataLayer implementation = null;
    private int pageSize=0;
    private int currentPage = 0;
    private ResultSet rs = null;
    private boolean hasNextPage=false, hasPreviousPage=false;
    private int estimatedSize = 0;
    private int estimatedPageCount = -1;
    private int estimatedMainElementSize = 0;
    
    // environment
    private ListeResultatModel lrm = null;
    private CachedData cache = null;
    private DocumentCache documentCache = null;
    
    // received data
    private ArrayList<String> docIds = null;
    
    // iterating data
    private int currentDocIndex = -1;
    private Object currentList = null;
    private int currentItemInList = -1;
    private boolean hasNextItem = false;
    private String currentDocId=null;
    private XemeliosUser user;
    
    private XPathFactory xpf = null;

    // for marshalling / unmarshalling
    private String documentId=null, etatId=null, elementId=null;
    
    public MySqlDataResultSet(ElementModel elementModel,Pair collectivite,Pair budget,String xPath,String sql,ListeResultatModel lrm,MySqlDataLayer impl,XemeliosUser user) throws SQLException, DataConfigurationException {
        this();
        this.elementModel=elementModel;
        this.etatModel=elementModel.getParent();
//        this.documentModel=etatModel.getParent();
        this.collectivite=collectivite;
        this.budget=budget;
        this.xPath=xPath;
        this.sql=sql;
        this.lrm=lrm;
        this.implementation=impl;
        this.pageSize=elementModel.getMaxDisplay();
        if(lrm==null)
            logger.debug("lrm is null");
        else if(lrm.getTriDefaut()==null)
            logger.debug("lrm.getTriDefaut() id null");
        loadIds();
    }
    public MySqlDataResultSet() {
        super();
        documentCache = new DocumentCache(CACHE_SIZE);
        try {
            xpf = new net.sf.saxon.xpath.XPathFactoryImpl();
        } catch(Throwable t) {
            logger.error("instanciating xpf:",t);
            xpf = XPathFactory.newInstance();
        }
        xpf.setXPathFunctionResolver(new FunctionResolver());
    }
    public MySqlDataResultSet(QName qn) {
        this();
    }
    
    public int getCount() {
        return docIds.size();
    }
    public int getEstimatedSize() {
        return estimatedSize;
    }
    protected void loadIds() throws SQLException, DataConfigurationException {
        cleanAllCaches();
        StringBuilder currentRequest = new StringBuilder(sql);
        
        selectClause = sql.substring(0, sql.indexOf("FROM"));
        fromClause = sql.substring(sql.indexOf("FROM"), sql.indexOf("WHERE"));
        whereClause = sql.substring(sql.indexOf("WHERE"));
        
        TDocument spc = implementation.getPersistenceConfig(etatModel.getParent(),user).getLayer(implementation.getLayerName()).getDocument(etatModel.getParent().getId());
        TEtat sEtatc = spc.getEtat(etatModel.getId());
        TElement sec = sEtatc.getElement(elementModel.getId());
        if(sec!=null && sec.getSpecialCond()!=null) {
            // check if special cond references a table that is not in FROM clause
            String sCond = sec.getSpecialCond().trim();
            // for the moment, all special conds are TABLE.COLUMN IS NOT NULL like
            String tableName = sCond.substring(0,sCond.indexOf('.'));
            if(fromClause.indexOf(tableName)<0) {
                // so, we add it
                String sTmp = fromClause.substring("FROM".length()).trim();
                String mainTableName = null;
                if(sTmp.indexOf(' ')>=0) mainTableName = sTmp.substring(0,sTmp.indexOf(' ')).trim();
                else mainTableName = sTmp;
                StringBuffer sb = new StringBuffer(fromClause);
                sb.append(" LEFT OUTER JOIN ").append(tableName).append(" ON ").append(mainTableName).append(".DOC_ID=").append(tableName).append(".DOC_ID AND ").append(mainTableName).append(".COLLECTIVITE=").append(tableName).append(".COLLECTIVITE AND ").append(mainTableName).append(".BUDGET=").append(tableName).append(".BUDGET");
                fromClause = sb.toString();
                currentRequest = new StringBuilder();
                currentRequest.append(selectClause).append(fromClause).append(" ").append(whereClause);
            }
            currentRequest.append(" AND ").append(sCond);
        }
        if(lrm!=null && lrm.getTriDefaut()!=null) {
            String tris = lrm.getTriDefaut();
            String ordres = lrm.getOrdre();
            
            StringTokenizer trisTokenizer = new StringTokenizer(tris,",");
            StringTokenizer ordresTokenizer = new StringTokenizer(ordres,",");
            if(currentRequest.indexOf("ORDER BY")<0) {
                currentRequest.append(" ORDER BY ");
            } else {
                // correction bug 278
                currentRequest.append(", ");
            }
            while(trisTokenizer.hasMoreElements()) {
                String tri = trisTokenizer.nextToken();
                String ordre = ordresTokenizer.hasMoreTokens()?ordresTokenizer.nextToken():"ASC";
                if(ordre==null || ordre.length()==0) ordre="ASC";
                ChampModel cm = lrm.getChamps().get(tri);
                if(cm!=null) {
                    // index pathes are not prefixed, so drop any prefixes
                    // now, they are !
                    String path = elementModel.getPath().getPath()+"/"+cm.getPath().getPath();
//                    path = path.replaceAll("[a-zA-Z0-9]:","");
                    path = XWay.compactXPath(path);
                    try {
                        TSqlIndex si = sEtatc.getSqlIndexByPath(path);
                        if(si==null) {
                            logger.error("index not found for path "+path+"\n won't sort !");
                            continue;
                        }
                        currentRequest.append(sEtatc.getIndexTable(si.getTableId()).getBaseName()).append(".").append(si.getColumn()).append(" ").append(ordre).append(",");
                        
                        // check if table name is in from clause
                        String tableName = sEtatc.getIndexTable(si.getTableId()).getBaseName();
                        if(fromClause.indexOf(tableName)<0) {
                            // so, we add it
                            String sTmp = fromClause.substring("FROM".length()).trim();
                            String mainTableName = null;
                            if(sTmp.indexOf(' ')>=0) mainTableName = sTmp.substring(0,sTmp.indexOf(' ')).trim();
                            else mainTableName = sTmp;
                            StringBuffer sb = new StringBuffer(fromClause);
                            sb.append(" LEFT OUTER JOIN ").append(tableName).append(" ON ").append(mainTableName).append(".DOC_ID=").append(tableName).append(".DOC_ID AND ").append(mainTableName).append(".COLLECTIVITE=").append(tableName).append(".COLLECTIVITE AND ").append(mainTableName).append(".BUDGET=").append(tableName).append(".BUDGET");
                            fromClause = sb.toString();
                            String saveOrderBy = currentRequest.toString();
                            saveOrderBy = saveOrderBy.substring(saveOrderBy.indexOf(" ORDER BY"));
                            currentRequest = new StringBuilder();
                            currentRequest.append(selectClause).append(fromClause).append(" ").append(whereClause).append(saveOrderBy);
                        }
                    } catch(Throwable t) {
                        // TODO
                    }
                } else {
                    throw new DataConfigurationException("order by clause of "+elementModel.getId()+" references an unknown champ :"+tri);
                }
            }
            if(currentRequest.substring(currentRequest.length()-1).equals(",")) currentRequest.deleteCharAt(currentRequest.length()-1);
            if(currentRequest.substring(currentRequest.length()-9).equals("ORDER BY ")) currentRequest.delete(currentRequest.length()-10,currentRequest.length());
        }
        currentRequest.append(" LIMIT ").append(currentPage*pageSize).append(", ").append(pageSize);
        logger.debug(currentRequest);
        Connection conToRelease = null;
        try {
            rs=implementation.execute(currentRequest.toString());
            conToRelease = rs.getStatement().getConnection();
            //estimatedSize = rsc.getEstimatedSize();
            docIds = new ArrayList<String>();
            while(rs.next()) {
                docIds.add(rs.getString(1));
            }
            Statement st = rs.getStatement().getConnection().createStatement();
            String sqlCount = null;
            if(sec!=null) {
                String sTmp = sec.getSqlCount();
                if(sTmp!=null) {
                    String tmp = currentRequest.toString();
                    tmp = tmp.substring(tmp.indexOf("FROM"));
                    String limitedFromClause = tmp.substring(0,tmp.indexOf("LIMIT"));
                    sqlCount = "SELECT "+sTmp+" "+limitedFromClause;
                }
            }
//            if(sqlCount==null) sqlCount = "SELECT FOUND_ROWS()";
//            String sqlCount = "SELECT COUNT( DISTINCT(CGM14_BC_IX_LIGNES.SERIAL)) "+fromClause;
            //ResultSet rs2 = st.executeQuery("SELECT FOUND_ROWS()");
            ResultSet rs2 = st.executeQuery("SELECT FOUND_ROWS()");
            if(rs2.next()) {
                estimatedMainElementSize=rs2.getInt(1);
            }
            if(sqlCount!=null) {
                logger.debug("sqlCount="+sqlCount);
                rs2 = st.executeQuery(sqlCount);
                if(rs2.next()) estimatedSize = rs2.getInt(1);
                else estimatedSize = estimatedMainElementSize;
            } else estimatedSize = estimatedMainElementSize;
            st.close();
            hasNextPage=(docIds.size()==pageSize);
            hasPreviousPage=(currentPage>0);
            hasNextItem = docIds.size()>0;
            currentDocIndex=-1;
            
            rs.getStatement().close();
        } catch(SQLException sqlEx) {
            throw sqlEx;
        } finally {
            // must release connection because implementation.execute do not release it anymore
            if(conToRelease!=null) implementation.releaseConnection(conToRelease);
        }
        if(cache!=null && logger.isDebugEnabled()) {
            logger.debug("cache contains "+cache.getSize()+" entries");
        }
        cleanAllCaches();
    }
    public boolean hasNextPage() { return hasNextPage; }
    public boolean hasPreviousPage() { return hasPreviousPage; }
    public boolean hasNext() {
        return hasNextItem;
    }
    @SuppressWarnings("unchecked")
    public DataHandler next() {
        if(!hasNextItem) throw new NoSuchElementException("iterating has ended");
        if(currentList==null) {
            currentDocId = docIds.get(++currentDocIndex);
//logger.debug("docId="+currentDocId);
            try {
                Document document = getDocument(currentDocId);
                XPath xp = xpf.newXPath();
//logger.debug(xp.getClass().getName());
//logger.debug(etatModel.getParent().getNamespaces().toString());
                xp.setNamespaceContext(etatModel.getParent().getNamespaces());
                currentList = xp.evaluate(xPath,document,XPathConstants.NODESET);
                currentItemInList=-1;
            } catch(Exception ex) {
                logger.error("in next():",ex);
                throw new NoSuchElementException("an error occurs. See logs for details.");
            }
        }
        Element el = null;
        try {
            if(currentList instanceof NodeList) {
                NodeList nl = (NodeList)currentList;
                el = (Element)nl.item(++currentItemInList);
            } else {
                @SuppressWarnings("unchecked")
                ArrayList<Element> lst = (ArrayList<Element>)currentList;
                el = lst.get(++currentItemInList);
            }
        } catch(IndexOutOfBoundsException iEx) {}
        if(currentList instanceof NodeList) {
            if(currentItemInList==((NodeList)currentList).getLength()-1) {
                currentList=null;
                hasNextItem=((currentDocIndex+1)<docIds.size());
            }
        } else {
            if(currentItemInList==((List<Element>)currentList).size()-1) {
                currentList=null;
                hasNextItem=((currentDocIndex+1)<docIds.size());
            }
        }
        if(el!=null)
            return new DataHandler(lrm,el,this,currentDocId,cache);
        else {
            ByteArrayOutputStream baos = new ByteArrayOutputStream();
            Charset cs = Charset.forName("UTF-8");
            OutputStreamWriter osw = new OutputStreamWriter(baos,cs);
            XmlOutputter output = new XmlOutputter(osw,cs.name());
            marshall(output);
            try {
                osw.flush();
                osw.close();
            } catch (IOException ex) {}
            logger.fatal("SQL REQUEST RETURNS MORE THAN XPATH DOES\n"+baos.toString());
//            if(logger.isDebugEnabled()) {
//            	try {
//            		logger.debug(implementation.getDocumentAsString(etatModel,collectivite,budget,currentDocId));
//            		logger.debug("XPATH="+xPath);
//            	} catch(Throwable t) {}
//            }
            hasNextItem=false;
            return null;
        }
    }
    public Document getDocument(String docId) throws DataConfigurationException, DataAccessException, UnauthorizedException {
        Document ret = documentCache.getDocument(docId);
        if(ret==null) {
            DocumentInfos di = implementation.getDocumentInfos(etatModel,collectivite,budget,docId,user);
            addDocumentToCache(docId,di);
            ret = di.getDoc();
        }
        return ret;
    }
    public String getDocumentEncoding(String docId) throws DataConfigurationException, DataAccessException {
        return documentCache.getEncoding(docId);
    }
    public void setEnvironment(ListeResultatModel lrm, CachedData cache) {
        this.lrm=lrm;
        this.cache=cache;
    }
    public void remove() {
        throw new UnsupportedOperationException("this operation is not allowed on MySqlDataResultSet");
    }
    public Pair getBudget() {
        return budget;
    }
    public void setBudget(Pair budget) {
        this.budget = budget;
    }
    public Pair getCollectivite() {
        return collectivite;
    }
    public void setCollectivite(Pair collectivite) {
        this.collectivite = collectivite;
    }
    public EtatModel getEtatModel() {
        return etatModel;
    }
    public void setEtatModel(EtatModel etatModel) {
        this.etatModel = etatModel;
    }
    public void nextPage() throws DataAccessException, DataConfigurationException {
        if(hasNextPage()) {
            currentPage++;
            try {
                loadIds();
            } catch(SQLException sqlEx) {
                throw new DataAccessException(sqlEx);
            }
        }
    }
    public void previousPage() throws DataAccessException, DataConfigurationException {
        if(hasPreviousPage()) {
            currentPage--;
            try {
                loadIds();
            } catch(SQLException sqlEx) {
                throw new DataAccessException(sqlEx);
            }
        }
    }
    public void firstPage() throws DataAccessException, DataConfigurationException {
//        if(hasPreviousPage()) {
        currentPage=0;
        try {
            loadIds();
        } catch(SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        }
//        }
    }
    public void lastPage() throws DataAccessException, DataConfigurationException {
        if(hasNextPage()) {
            currentPage=getEstimatedPageCount()-1;
            try {
                loadIds();
            } catch(SQLException sqlEx) {
                throw new DataAccessException(sqlEx);
            }
        }
    }
    public void setPage(int page) throws DataAccessException, DataConfigurationException {
        currentPage=page;
        try {
            loadIds();
        } catch(SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        }
    }
    public int getEstimatedPageCount() {
        if(estimatedPageCount==(-1)) {
            if(pageSize==0 || estimatedSize==0) {
                estimatedPageCount=0;
            } else {
//                estimatedPageCount=estimatedSize/pageSize;
                estimatedPageCount=estimatedMainElementSize/pageSize;
                if(estimatedSize % pageSize >0) estimatedPageCount++;
            }
        }
        return estimatedPageCount;
    }
    public int getCurrentPage() { return currentPage; }
    public ListeResultatModel getListeResultatModel() { return lrm; }
    public void setListeResultatModel(ListeResultatModel l) { this.lrm = l; }
    public void clear() {
        cleanAllCaches();
    }
    protected void cleanAllCaches() {
        if(cache!=null) cache.flush();
        if(documentCache!=null) documentCache.clear();
    }
    protected void addDocumentToCache(String docId,DocumentInfos doc) {
        documentCache.putDocument(docId,doc);
    }
    
    /**
     * Actually performs the operation on the data of the given column. Operations can
     * be average, sum, max, min, etc...
     * Needs the datatype, because there is a special case for the dates: we need to
     * convert them to number to do the computation and then back to dates for formatting,
     * which requires 2 additional closing parenthesis.
     * @param columnName the name of the column in the persistence model (database)
     * @param tableName Name of the table in which the column is to be found
     * @param operator the Operator to apply on the data (e.g. AVG, SUM, MAX...)
     * @param cm
     * @return
     */
    public String calculateAggregate(String columnName, String tableName, String operator, ChampModel cm) {
        String result = "";
        ResultSet rset = null;
        StringBuffer sb = new StringBuffer();
        // ORIGINAL
//        sb.append("SELECT ").append(operator).append("(").append(columnName).append(")");
//        if(cm.getDatatype().equals(ChampModel.DATATYPE_DATE)) {
//            sb.append("))");
//        }
//        sb.append(" FROM ").append(tableName).append(" WHERE ").append(tableName).append(".COLLECTIVITE='");
//        sb.append(collectivite.key).append("' AND ").append(tableName).append(".BUDGET='").append(budget.key).append("' AND DOC_ID IN (\n");
//        sb.append(selectClause.replaceAll("SQL_CALC_FOUND_ROWS ","")).append(" ").append(fromClause).append(" ").append(whereClause).append("\n)");
        
        // TEST
        // cas 1 : element importable
        if(elementModel.equals(elementModel.getParent().getImportableElement())) {
            sb.append("SELECT ").append(operator).append("(").append(columnName).append(")");
            if(cm.getDatatype().equals(ChampModel.DATATYPE_DATE)) {
                sb.append("))");
            }
            sb.append(" FROM ").append(tableName).append(" WHERE ").append(tableName).append(".COLLECTIVITE='");
            sb.append(collectivite.key).append("' AND ").append(tableName).append(".BUDGET='").append(budget.key).append("' AND DOC_ID IN (\n");
            sb.append(selectClause.replaceAll("SQL_CALC_FOUND_ROWS ","")).append(" ").append(fromClause).append(" ").append(whereClause).append("\n)");
        } else {
            // sinon
            sb.append("SELECT ").append(operator).append("(").append(columnName).append(")");
            if(cm.getDatatype().equals(ChampModel.DATATYPE_DATE)) {
                sb.append("))");
            }
            String sTmp = sql.substring(sql.indexOf("FROM "));
            int loc = sTmp.indexOf("LIMIT ");
            if(loc>0) {
                sTmp = sTmp.substring(0,loc);
            }
            int whereLoc = sTmp.indexOf("WHERE ");
            String where = whereLoc>0 ? sTmp.substring(whereLoc) : "";
            if(whereLoc>0) sTmp = sTmp.substring(0,whereLoc);
            if(sTmp.indexOf(tableName)<0) {
                String sTmp2 = sTmp.substring("FROM".length()).trim();
                String mainTableName = null;
                if(sTmp2.indexOf(' ')>=0) mainTableName = sTmp2.substring(0,sTmp2.indexOf(' ')).trim();
                else mainTableName = sTmp2;
                StringBuffer sb2 = new StringBuffer(sTmp);
                sb2.append(" LEFT OUTER JOIN ").append(tableName).append(" ON ").append(mainTableName).append(".DOC_ID=").append(tableName).append(".DOC_ID AND ").append(mainTableName).append(".COLLECTIVITE=").append(tableName).append(".COLLECTIVITE AND ").append(mainTableName).append(".BUDGET=").append(tableName).append(".BUDGET");
                sTmp = sb2.toString();
            }
            sb.append(" ").append(sTmp).append(" ").append(where);
        }
        logger.debug(sb.toString());
        
        Connection conn = null;
//        Object computed = null;
        try {
            rset = implementation.execute(sb.toString());
            if(rset.getStatement()!=null)
                conn = rset.getStatement().getConnection();
            if(rset.next()) {
                result = rset.getString(1);
                Class classToReturn;
                classToReturn = cm.getDataClass();
                if(classToReturn.equals(Float.class)) {
                    try {
                        Float computed = new Float(Float.parseFloat(result));
                    } catch(Exception nfEx) { }
                } else if(classToReturn.equals(Integer.class)) {
                    try {
                        Integer computed = new Integer(Integer.parseInt(result));
                    } catch(Exception nfEx) { }
                } else if(classToReturn.equals(Amount.class)) {
                    try {
                        Amount computed = new Amount(result);
                        result = computed.stringRepresentation();
                    } catch(Exception nfEx) { }
                } else if(classToReturn.equals(java.util.Date.class) && result!=null) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    Date computed = sdf.parse(result);
                    sdf = new SimpleDateFormat(System.getProperty(Constants.SYS_PROP_DATE_FORMAT,"yyyy-MM-dd"));
                    result = sdf.format(computed);
                }
            }
            rset.close();
            logger.debug("resultat : "+result);
//            if(cm.getDatatype().equals(ChampModel.DATATYPE_DATE)) {
                // TODO formater la donnée en fonction du type de donnée
                //SimpleDateFormat sdf = new SimpleDateFormat(cm.get);
//            }
        } catch(SQLException sqle) {
            sqle.printStackTrace();
        } catch(ParseException pEx) {
            pEx.printStackTrace();
        } finally {
            // must release connection because implementation.execute do not release it anymore
            if(conn!=null) implementation.releaseConnection(conn);
        }
        
        return result;
    }

    public void addCharacterData(String cData) throws SAXException { }

    public void addChild(XmlMarshallable child, QName tag) throws SAXException {
        if(tag.equals(MysqlDrsParser.XPATH)) {
            XPathModel xp = (XPathModel)child;
            xPath = xp.getPath();
        } else if(tag.equals(MysqlDrsParser.FROM_CLAUSE)) {
            XPathModel xp = (XPathModel)child;
            fromClause = xp.getPath();
        } else if(tag.equals(MysqlDrsParser.SELECT_CLAUSE)) {
            XPathModel xp = (XPathModel)child;
            selectClause = xp.getPath();
        } else if(tag.equals(MysqlDrsParser.SQL)) {
            XPathModel xp = (XPathModel)child;
            sql = xp.getPath();
        } else if(tag.equals(MysqlDrsParser.WHERE_CLAUSE)) {
            XPathModel xp = (XPathModel)child;
            whereClause = xp.getPath();
        } else if(ListeResultatModel.QN.equals(tag)) {
            lrm = (ListeResultatModel)child;
        } else if(tag.equals(UserModel.QN)) {
            UserModel um = (UserModel)child;
            user = new LocalUser(um);
        }
    }

    public XmlMarshallable getAttributes(XmlAttributes attributes) throws SAXException {
        documentId = attributes.getValue("doc-id");
        etatId = attributes.getValue("etat-id");
        elementId = attributes.getValue("element-id");
        pageSize = attributes.getIntValue("page-size");
        currentPage = attributes.getIntValue("current-page");
        estimatedSize = attributes.getIntValue("estimated-size");
        estimatedPageCount = attributes.getIntValue("estimated-page-count");
        estimatedMainElementSize = attributes.getIntValue("estimated-main-element-size");
        String sTmp = attributes.getValue("collectivite");
        if(sTmp!=null) {
            collectivite = new Pair(sTmp,sTmp);
            sTmp=null;
        }
        sTmp = attributes.getValue("budget");
        if(sTmp!=null) {
            budget = new Pair(sTmp,sTmp);
            sTmp=null;
        }
        return this;
    }

    public XmlMarshallable getChildToModify(String uri, String localName, String qName, Attributes atts) {
        return null;
    }

    public void marshall(XmlOutputter output) {
        output.startTag(QN);
        output.addAttribute("doc-id", etatModel.getParent().getId());
        output.addAttribute("etat-id",etatModel.getId());
        output.addAttribute("element-id", elementModel.getId());
        output.addAttribute("page-size", pageSize);
        output.addAttribute("current-page", currentPage);
        output.addAttribute("estimated-size", estimatedSize);
        output.addAttribute("estimated-page-count", estimatedPageCount);
        output.addAttribute("estimated-main-element-size", estimatedMainElementSize);
        output.addAttribute("collectivite", collectivite.key);
        output.addAttribute("budget", budget.key);

        output.startTag(MysqlDrsParser.XPATH);
        output.addCharacterData(xPath);
        output.endTag(MysqlDrsParser.XPATH);

        output.startTag(MysqlDrsParser.FROM_CLAUSE);
        output.addCharacterData(fromClause);
        output.endTag(MysqlDrsParser.FROM_CLAUSE);

        output.startTag(MysqlDrsParser.SELECT_CLAUSE);
        output.addCharacterData(selectClause);
        output.endTag(MysqlDrsParser.SELECT_CLAUSE);

        output.startTag(MysqlDrsParser.SQL);
        output.addCharacterData(sql);
        output.endTag(MysqlDrsParser.SQL);

        output.startTag(MysqlDrsParser.WHERE_CLAUSE);
        output.addCharacterData(whereClause);
        output.endTag(MysqlDrsParser.WHERE_CLAUSE);

        lrm.marshall(output);

        if(user!=null) {
            UserModel um = new UserModel(UserModel.QN);
            um.setId(user.getId());
            um.setLibelle(user.getDisplayName());
            um.setCollectivite(collectivite.key);
            um.setDocument(etatModel.getParent().getId());
            if(user.hasRole(XemeliosUser.ROLE_BROWSE)) um.getRoles().add(XemeliosUser.ROLE_BROWSE);
            if(user.hasRole(XemeliosUser.ROLE_CLEAN)) um.getRoles().add(XemeliosUser.ROLE_CLEAN);
            if(user.hasRole(XemeliosUser.ROLE_CONNECT)) um.getRoles().add(XemeliosUser.ROLE_CONNECT);
            if(user.hasRole(XemeliosUser.ROLE_EXPORT)) um.getRoles().add(XemeliosUser.ROLE_EXPORT);
            if(user.hasRole(XemeliosUser.ROLE_IMPORT)) um.getRoles().add(XemeliosUser.ROLE_IMPORT);
            if(user.hasRole(XemeliosUser.ROLE_SEARCH)) um.getRoles().add(XemeliosUser.ROLE_SEARCH);
            um.marshall(output);
        }
        output.endTag(QN);
    }

    public void validate() throws InvalidXmlDefinition {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public QName getQName() {
        throw new UnsupportedOperationException("Not supported yet.");
    }
    
    protected class DocumentCache {
        private int maxCacheSize;
        private int actualCacheSize=0;
        private Hashtable<String,Document> cache;
        private Hashtable<String,String> encodings;
        private LinkedList<String> chain;
        private final Object locker = new Object();
        public DocumentCache(int cacheSize) {
            super();
            this.maxCacheSize=cacheSize;
            cache=new Hashtable<String,Document>();
            chain=new LinkedList<String>();
            encodings = new Hashtable<String,String>();
        }
        public void clear() {
            synchronized(locker) {
                cache.clear();
                chain.clear();
                actualCacheSize=0;
            }
        }
        public Document getDocument(String docId) {
            Document ret = cache.get(docId);
            if(ret!=null) {
                synchronized(locker) {
                    chain.remove(docId);
                    chain.addFirst(docId);
                }
            }
            return ret;
        }
        public void putDocument(String docId,DocumentInfos doc) {
            if(cache.contains(docId)) {
                synchronized(locker) {
                    cache.remove(docId);
                    chain.remove(docId);
                    actualCacheSize--;
                }
            }
            synchronized(locker) {
                while(actualCacheSize>=maxCacheSize) {
                    String docToRemove = chain.removeLast();
                    cache.remove(docToRemove);
                    actualCacheSize--;
                }
            }
            synchronized(locker) {
                cache.put(docId,doc.getDoc());
                chain.addFirst(docId);
                encodings.put(docId,doc.getEncoding());
                actualCacheSize++;
            }
        }
        public String getEncoding(String docId) {
            String encoding = encodings.get(docId);
            if(encoding==null) {
                getDocument(docId);
                encoding = encodings.get(docId);
            }
            return encoding;
        }
    }
    
    public ElementModel getElementModel() {
        return elementModel;
    }
    
    public MySqlDataLayer getImplementation() {
        return implementation;
    }
    
    public ListeResultatModel getLrm() {
        return lrm;
    }
    
    public String getSql() {
        return sql;
    }
    
    public String getXPath() {
        return xPath;
    }
    
    public String getCurrentDocId() { return currentDocId; }
    @Override
    public MySqlDataResultSet clone() {
        return this;
    }

    public void finishInit(DocumentsModel docs) {
        DocumentModel dm = docs.getDocumentById(documentId);
        etatModel = dm.getEtatById(etatId);
        elementModel = etatModel.getElementById(elementId);
        if(lrm.getParent()==null) {
            lrm.setParent(elementModel);
        }
        if(implementation==null) {
            try {
                implementation = (MySqlDataLayer)DataLayerManager.getImplementation();
            } catch(Throwable t) {
                logger.error("finishInit(DocumentsModel)",t);
            }
        }
    }

    private class LocalUser implements XemeliosUser {
        private UserModel user;
        public LocalUser(UserModel user) {
            super();
            this.user=user;
        }

        public String getId() {
            return user.getId();
        }

        public String getDisplayName() {
            return user.getLibelle();
        }

        public boolean hasRole(String role) {
            return user.getRoles().contains(role);
        }

        public boolean hasDocument(String document) {
            return document.equals(user.getDocument());
        }

        public boolean hasCollectivite(String collectivite, DocumentModel dm) {
            return dm.getId().equals(user.getDocument()) && collectivite.equals(user.getCollectivite());
        }

    }
}
