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

import fr.gouv.finances.dgfip.utils.Pair;
import fr.gouv.finances.dgfip.xemelios.auth.Collectivite;
import fr.gouv.finances.dgfip.xemelios.data.impl.pool.PoolManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.List;
import org.apache.log4j.Logger;
/**
 *
 * @author chm
 */
public class DataHelper {
    private static final Logger logger = Logger.getLogger(DataHelper.class);

    public static ArrayList<Profil> getProfils() {
        ArrayList<Profil> ret = new ArrayList<Profil>();
        Connection con = null;
        Statement st = null;
        PreparedStatement ps = null;
        try {
            con = PoolManager.getInstance().getConnection();
            st = con.createStatement();
            ResultSet rs1 = st.executeQuery("SELECT PRF_CODE, PRF_LIBELLE FROM AUTH_PROFIL");
            ps = con.prepareStatement("SELECT FCT_CODE FROM AUTH_PRF_FCT WHERE PRF_CODE=?");
            while(rs1.next()) {
                Profil p = new Profil(rs1.getString(1),rs1.getString(2));
                ps.setString(1, p.getCode());
                ResultSet rs2 = ps.executeQuery();
                while(rs2.next()) p.addFonctionnalite(rs2.getString(1));
                rs2.close();
                ret.add(p);
            }
            rs1.close();
        } catch(SQLException sqlEx) {
            logger.error("getProfils()",sqlEx);
        } finally {
            if(ps!=null) {
                try {
                    ps.clearParameters();
                    ps.close();
                } catch(Throwable t) {}
            }
            if(st!=null) {
                try {
                    st.close();
                } catch(Throwable t) {}
            }
            if(con!=null) PoolManager.getInstance().releaseConnection(con);
        }
        return ret;
    }
    public static List<Pair> getProfilsAsPair() {
        ArrayList<Pair> ret = new ArrayList<Pair>();
        Connection con = null;
        Statement st = null;
        PreparedStatement ps = null;
        try {
            con = PoolManager.getInstance().getConnection();
            st = con.createStatement();
            ResultSet rs1 = st.executeQuery("SELECT PRF_CODE, PRF_LIBELLE FROM AUTH_PROFIL");
            while(rs1.next()) {
                Pair p = new Pair(rs1.getString(1),rs1.getString(2));
                ret.add(p);
            }
            rs1.close();
        } catch(SQLException sqlEx) {
            logger.error("getProfilsAsPair()",sqlEx);
        } finally {
            if(ps!=null) {
                try {
                    ps.clearParameters();
                    ps.close();
                } catch(Throwable t) {}
            }
            if(st!=null) {
                try {
                    st.close();
                } catch(Throwable t) {}
            }
            if(con!=null) PoolManager.getInstance().releaseConnection(con);
        }
        return ret;
    }

    public static void saveProfil(Profil profil) {
        Connection con = null;
        PreparedStatement ps = null;
        Statement st = null;
        try {
            con = PoolManager.getInstance().getConnection();
            ps = con.prepareStatement("DELETE FROM AUTH_PRF_FCT WHERE PRF_CODE=?");
            ps.setString(1, profil.getCode());
            ps.executeUpdate();
            ps = con.prepareStatement("INSERT INTO AUTH_PRF_FCT (PRF_CODE, FCT_CODE) VALUES (?,?)");
            ps.setString(1,profil.getCode());
            for(String fonct:profil.getFonctionnalites()) {
                ps.setString(2,fonct);
                ps.executeUpdate();
            }
        } catch(SQLException sqlEx) {
            logger.error("saveProfil(Profil)",sqlEx);
        } finally {
            if(ps!=null) {
                try {
                    ps.clearParameters();
                    ps.close();
                } catch(Throwable t) {}
            }
            if(st!=null) {
                try {
                    st.close();
                } catch(Throwable t) {}
            }
            if(con!=null) PoolManager.getInstance().releaseConnection(con);
        }
    }
    public static boolean removeProfil(Profil profil) {
        Connection con = null;
        PreparedStatement ps = null;
        Statement st = null;
        try {
            con = PoolManager.getInstance().getConnection();
            ps = con.prepareStatement("SELECT * FROM AUTH_UTI_PRF WHERE PRF_CODE=?");
            ps.setString(1, profil.getCode());
            ResultSet rs = ps.executeQuery();
            if(rs.next()) {
                return false;
            }
            ps = con.prepareStatement("DELETE FROM AUTH_UTI_PRF WHERE PRF_CODE=?");
            ps.setString(1,profil.getCode());
            ps.executeUpdate();
            ps = con.prepareStatement("DELETE FROM AUTH_PRF_FCT WHERE PRF_CODE=?");
            ps.setString(1,profil.getCode());
            ps.executeUpdate();
            ps = con.prepareStatement("DELETE FROM AUTH_PROFIL WHERE PRF_CODE=?");
            ps.setString(1,profil.getCode());
            ps.executeUpdate();
        } catch(SQLException sqlEx) {
            logger.error("saveProfil(Profil)",sqlEx);
        } finally {
            if(ps!=null) {
                try {
                    ps.clearParameters();
                    ps.close();
                } catch(Throwable t) {}
            }
            if(st!=null) {
                try {
                    st.close();
                } catch(Throwable t) {}
            }
            if(con!=null) PoolManager.getInstance().releaseConnection(con);
        }
        return true;
    }
    public static boolean createProfil(Profil profil) {
        Connection con = null;
        PreparedStatement ps = null;
        Statement st = null;
        try {
            con = PoolManager.getInstance().getConnection();
            ps = con.prepareStatement("SELECT * FROM AUTH_PROFIL WHERE PRF_CODE=?");
            ps.setString(1, profil.getCode());
            ResultSet rs = ps.executeQuery();
            if(rs.next()) {
                return false;
            }
            ps = con.prepareStatement("INSERT INTO AUTH_PROFIL (PRF_CODE,PRF_LIBELLE) VALUES (?,?)");
            ps.setString(1,profil.getCode());
            ps.setString(2,profil.getLibelle());
            ps.executeUpdate();
        } catch(SQLException sqlEx) {
            logger.error("createProfil(Profil)",sqlEx);
        } finally {
            if(ps!=null) {
                try {
                    ps.clearParameters();
                    ps.close();
                } catch(Throwable t) {}
            }
            if(st!=null) {
                try {
                    st.close();
                } catch(Throwable t) {}
            }
            if(con!=null) PoolManager.getInstance().releaseConnection(con);
        }
        return true;
    }
    public static ArrayList<StdUser> getUsers(String pattern) {
        ArrayList<StdUser> ret = new ArrayList<StdUser>();
        String liker = transformPatternToSql(pattern);
        Connection con = null;
        Statement st = null;
        PreparedStatement ps = null;
        try {
            con = PoolManager.getInstance().getConnection();
            ps = con.prepareStatement("SELECT UTI_LOGIN, UTI_NOM, UTI_PRENOM,UTI_ALL_FONC,UTI_ALL_DOC, UTI_ALL_COL, UTI_ID FROM AUTH_UTILISATEUR WHERE UTI_LOGIN LIKE ? OR UTI_NOM LIKE ? OR UTI_PRENOM LIKE ?");
            ps.setString(1,liker);
            ps.setString(2,liker);
            ps.setString(3,liker);
            ResultSet rs = ps.executeQuery();
            while(rs.next()) {
                StdUser p = new StdUser(rs.getString(1),rs.getString(2),rs.getString(3),rs.getBoolean(4),rs.getBoolean(5),rs.getBoolean(6), rs.getInt(7));
                ret.add(p);
            }
            rs.close();
        } catch(SQLException sqlEx) {
            logger.error("getUsers()",sqlEx);
        } finally {
            if(ps!=null) {
                try {
                    ps.clearParameters();
                    ps.close();
                } catch(Throwable t) {}
            }
            if(st!=null) {
                try {
                    st.close();
                } catch(Throwable t) {}
            }
            if(con!=null) PoolManager.getInstance().releaseConnection(con);
        }
        return ret;
    }
    public static void saveUser(StdUser user) {
        Connection con = null;
        PreparedStatement ps = null;
        Statement st = null;
        try {
            con = PoolManager.getInstance().getConnection();
            ps = con.prepareStatement("UPDATE AUTH_UTILISATEUR SET UTI_LOGIN=?, UTI_NOM=?, UTI_PRENOM=?, UTI_ALL_FONC=?, UTI_ALL_DOC=?, UTI_ALL_COL=? WHERE UTI_ID=?");
            ps.setString(1, user.getLogin());
            ps.setString(2, user.getNom());
            ps.setString(3,user.getPrenom());
            ps.setInt(4, user.isAllFonctionalities()?1:0);
            ps.setInt(5, user.isAlldocuments()?1:0);
            ps.setInt(6, user.isAllCollectivites()?1:0);
            ps.setLong(7,user.getId());
            ps.executeUpdate();
        } catch(SQLException sqlEx) {
            logger.error("saveUser(StdUser)",sqlEx);
        } finally {
            if(ps!=null) {
                try {
                    ps.clearParameters();
                    ps.close();
                } catch(Throwable t) {}
            }
            if(st!=null) {
                try {
                    st.close();
                } catch(Throwable t) {}
            }
            if(con!=null) PoolManager.getInstance().releaseConnection(con);
        }
    }
    public static void saveUser(StdUser user, String password) {
        Connection con = null;
        PreparedStatement ps = null;
        Statement st = null;
        try {
            con = PoolManager.getInstance().getConnection();
            if(user.getId()>-1) {
                ps = con.prepareStatement("UPDATE AUTH_UTILISATEUR SET UTI_LOGIN=?, UTI_NOM=?, UTI_PRENOM=?, UTI_ALL_FONC=?, UTI_ALL_DOC=?, UTI_ALL_COL=?, UTI_PASSWORD=? WHERE UTI_ID=?");
                ps.setString(1, user.getLogin());
                ps.setString(2, user.getNom());
                ps.setString(3,user.getPrenom());
                ps.setInt(4, user.isAllFonctionalities()?1:0);
                ps.setInt(5, user.isAlldocuments()?1:0);
                ps.setInt(6, user.isAllCollectivites()?1:0);
                ps.setString(7, password);
                ps.setLong(8,user.getId());
                ps.executeUpdate();
            } else {
                ps = con.prepareStatement("INSERT INTO AUTH_UTILISATEUR (UTI_LOGIN,UTI_NOM,UTI_PRENOM,UTI_ALL_FONC,UTI_ALL_DOC,UTI_ALL_COL,UTI_PASSWORD,UTI_ACTIF) VALUES (?,?,?,?,?,?,?,1)");
                ps.setString(1, user.getLogin());
                ps.setString(2, user.getNom());
                ps.setString(3,user.getPrenom());
                ps.setInt(4, user.isAllFonctionalities()?1:0);
                ps.setInt(5, user.isAlldocuments()?1:0);
                ps.setInt(6, user.isAllCollectivites()?1:0);
                ps.setString(7, password);
                ps.executeUpdate();
                ps.close();
                ps = con.prepareStatement("SELECT MAX(UTI_ID) FROM AUTH_UTILISATEUR WHERE UTI_LOGIN=?");
                ps.setString(1,user.getLogin());
                ResultSet rs = ps.executeQuery();
                if(rs.next()) user.setId(rs.getLong(1));
            }
        } catch(SQLException sqlEx) {
            logger.error("saveUser(StdUser,String)",sqlEx);
        } finally {
            if(ps!=null) {
                try {
                    ps.clearParameters();
                    ps.close();
                } catch(Throwable t) {}
            }
            if(st!=null) {
                try {
                    st.close();
                } catch(Throwable t) {}
            }
            if(con!=null) PoolManager.getInstance().releaseConnection(con);
        }
    }
    public static void removeUser(StdUser user) {
        Connection con = null;
        PreparedStatement ps = null;
        Statement st = null;
        try {
            con = PoolManager.getInstance().getConnection();
            ps = con.prepareStatement("DELETE FROM AUTH_UTI_PRF WHERE UTI_ID=?");
            ps.setLong(1,user.getId());
            ps.executeUpdate();
            ps = con.prepareStatement("DELETE FROM AUTH_HAB_COL WHERE UTI_ID=?");
            ps.setLong(1,user.getId());
            ps.executeUpdate();
            ps = con.prepareStatement("DELETE FROM AUTH_HAB_DOC WHERE UTI_ID=?");
            ps.setLong(1,user.getId());
            ps.executeUpdate();
            ps = con.prepareStatement("DELETE FROM AUTH_UTILISATEUR WHERE UTI_ID=?");
            ps.setLong(1,user.getId());
            ps.executeUpdate();
        } catch(SQLException sqlEx) {
            logger.error("removeUser(StdUser)",sqlEx);
        } finally {
            if(ps!=null) {
                try {
                    ps.clearParameters();
                    ps.close();
                } catch(Throwable t) {}
            }
            if(st!=null) {
                try {
                    st.close();
                } catch(Throwable t) {}
            }
            if(con!=null) PoolManager.getInstance().releaseConnection(con);
        }
    }
    public static List<Pair> getUserProfils(long userId) {
        ArrayList<Pair> ret = new ArrayList<Pair>();
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = PoolManager.getInstance().getConnection();
            ps = con.prepareStatement("SELECT UTI.PRF_CODE, PRF.PRF_LIBELLE FROM AUTH_UTI_PRF UTI LEFT JOIN AUTH_PROFIL PRF ON UTI.PRF_CODE=PRF.PRF_CODE WHERE UTI.UTI_ID=?");
            ps.setLong(1,userId);
            ResultSet rs = ps.executeQuery();
            while(rs.next()) {
                ret.add(new Pair(rs.getString(1),rs.getString(2)));
            }
        } catch(SQLException sqlEx) {
            logger.error("getUserProfils(String)",sqlEx);
        } finally {
            if(ps!=null) {
                try {
                    ps.clearParameters();
                    ps.close();
                } catch(Throwable t) {}
            }
            if(con!=null) PoolManager.getInstance().releaseConnection(con);
        }
        return ret;
    }
    public static List<Pair> getUserDocuments(long userId) {
        ArrayList<Pair> ret = new ArrayList<Pair>();
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = PoolManager.getInstance().getConnection();
            ps = con.prepareStatement("SELECT DOC_CODE FROM AUTH_HAB_DOC WHERE UTI_ID=?");
            ps.setLong(1,userId);
            ResultSet rs = ps.executeQuery();
            while(rs.next()) {
                ret.add(new Pair(rs.getString(1),rs.getString(1)));
            }
        } catch(SQLException sqlEx) {
            logger.error("getUserDocuments(String)",sqlEx);
        } finally {
            if(ps!=null) {
                try {
                    ps.clearParameters();
                    ps.close();
                } catch(Throwable t) {}
            }
            if(con!=null) PoolManager.getInstance().releaseConnection(con);
        }
        return ret;
    }
    public static ArrayList<CollectiviteLib> getUserCollectivites(long userId) {
        ArrayList<CollectiviteLib> ret = new ArrayList<CollectiviteLib>();
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = PoolManager.getInstance().getConnection();
            ps = con.prepareStatement("SELECT HAB.TID_CODE, HAB.COL_CLEF, COLL.COL_LIBELLE FROM AUTH_HAB_COL HAB LEFT JOIN AUTH_COLLECTIVITE COLL ON HAB.TID_CODE=COLL.TID_CODE AND HAB.COL_CLEF=COLL.COL_CLEF WHERE UTI_ID=?");
            ps.setLong(1,userId);
            ResultSet rs = ps.executeQuery();
            while(rs.next()) {
                ret.add(new CollectiviteLib(rs.getString(1),rs.getString(2),rs.getString(3)));
            }
        } catch(SQLException sqlEx) {
            logger.error("getUserCollectivites(long)",sqlEx);
        } finally {
            if(ps!=null) {
                try {
                    ps.clearParameters();
                    ps.close();
                } catch(Throwable t) {}
            }
            if(con!=null) PoolManager.getInstance().releaseConnection(con);
        }
        return ret;
    }
    public static void createCollectivite(CollectiviteLib coll) {
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = PoolManager.getInstance().getConnection();
            ps = con.prepareStatement("INSERT INTO AUTH_COLLECTIVITE (TID_CODE,COL_CLEF,COL_LIBELLE) VALUES (?,?,?)");
            ps.setString(1,coll.getNatureIdentifiant());
            ps.setString(2,coll.getClef());
            ps.setString(3,coll.getLibelle());
            ps.executeUpdate();
        } catch(SQLException sqlEx) {
            logger.error("createCollectivite(CollectiviteLib)",sqlEx);
        } finally {
            if(ps!=null) {
                try {
                    ps.clearParameters();
                    ps.close();
                } catch(Throwable t) {}
            }
            if(con!=null) PoolManager.getInstance().releaseConnection(con);
        }

    }
    public static void updateCollectivite(CollectiviteLib coll) {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs;
        try {
            con = PoolManager.getInstance().getConnection();
            ps = con.prepareStatement("SELECT 1 FROM AUTH_COLLECTIVITE WHERE TID_CODE=? AND COL_CLEF=?");
            ps.setString(1,coll.getNatureIdentifiant());
            ps.setString(2,coll.getClef());
            rs = ps.executeQuery();
            if(rs.next()) {
                ps = con.prepareStatement("UPDATE AUTH_COLLECTIVITE SET COL_LIBELLE=? WHERE TID_CODE=? AND COL_CLEF=?");
                ps.setString(1,coll.getLibelle());
                ps.setString(2,coll.getNatureIdentifiant());
                ps.setString(3,coll.getClef());
                ps.executeUpdate();
            } else {
                ps = con.prepareStatement("INSERT INTO AUTH_COLLECTIVITE (TID_CODE,COL_CLEF,COL_LIBELLE) VALUES (?,?,?)");
                ps.setString(1,coll.getNatureIdentifiant());
                ps.setString(2,coll.getClef());
                ps.setString(3,coll.getLibelle());
                ps.executeUpdate();
            }
        } catch(SQLException sqlEx) {
            logger.error("updateCollectivite(CollectiviteLib)",sqlEx);
        } finally {
            if(ps!=null) {
                try {
                    ps.clearParameters();
                    ps.close();
                } catch(Throwable t) {}
            }
            if(con!=null) PoolManager.getInstance().releaseConnection(con);
        }

    }
    public static CollectiviteResponse searchCollectivites(String natureIdentifiant, String code, String libelle, int page, int rowsPerPage) {
        ArrayList<DataHelper.CollectiviteLib> ret = new ArrayList<DataHelper.CollectiviteLib>();
        Connection con = null;
        PreparedStatement ps = null;
        int foundRows = 0;
        try {
            con = PoolManager.getInstance().getConnection();
            StringBuffer sb = new StringBuffer();
            sb.append("SELECT SQL_CALC_FOUND_ROWS TID_CODE, COL_CLEF, COL_LIBELLE FROM AUTH_COLLECTIVITE WHERE");
            if(!"*".equals(natureIdentifiant)) {
                sb.append(" TID_CODE=? AND");
            }
            if(code!=null) {
                sb.append(" COL_CLEF LIKE ? AND");
            }
            if(libelle!=null) {
                sb.append(" COL_LIBELLE LIKE ? AND");
            }
            if(sb.toString().endsWith(" AND")) {
                sb.delete(sb.length()-4, sb.length());
            }
            if(sb.toString().endsWith(" WHERE")) {
                sb.delete(sb.length()-6, sb.length());
            }
            sb.append(" ORDER BY 1, 2, 3 LIMIT ").append(page*rowsPerPage).append(", ").append(rowsPerPage);
            logger.debug(sb.toString());
            ps = con.prepareStatement(sb.toString());
            int pos = 1;
            if(!"*".equals(natureIdentifiant)) {
                ps.setString(pos++,natureIdentifiant);
            }
            if(code!=null) {
                ps.setString(pos++,transformPatternToSql(code));
            }
            if(libelle!=null) {
                ps.setString(pos++,transformPatternToSql(libelle));
            }
            ResultSet rs = ps.executeQuery();
            while(rs.next()) {
                ret.add(new CollectiviteLib(rs.getString(1),rs.getString(2),rs.getString(3)));
            }
            rs = rs.getStatement().getConnection().createStatement().executeQuery("SELECT FOUND_ROWS()");
            rs.next();
            foundRows = rs.getInt(1);
        } catch(SQLException sqlEx) {
            logger.error("searchCollectivites(String,String,String,int,int)",sqlEx);
        } finally {
            if(ps!=null) {
                try {
                    ps.clearParameters();
                    ps.close();
                } catch(Throwable t) {}
            }
            if(con!=null) PoolManager.getInstance().releaseConnection(con);
        }
        return new CollectiviteResponse(ret,foundRows);
    }
    public static void saveUserProfils(StdUser user) {
        Connection con = null;
        PreparedStatement ps = null;
        Statement st = null;
        try {
            con = PoolManager.getInstance().getConnection();
            ps = con.prepareStatement("DELETE FROM AUTH_UTI_PRF WHERE UTI_ID=?");
            ps.setLong(1, user.getId());
            ps.executeUpdate();
            ps = con.prepareStatement("INSERT INTO AUTH_UTI_PRF (UTI_ID, PRF_CODE) VALUES (?,?)");
            ps.setLong(1,user.getId());
            for(Pair p:user.getProfils()) {
                ps.setString(2,p.key);
                ps.executeUpdate();
            }
        } catch(SQLException sqlEx) {
            logger.error("saveUserProfil(StdUser)",sqlEx);
        } finally {
            if(ps!=null) {
                try {
                    ps.clearParameters();
                    ps.close();
                } catch(Throwable t) {}
            }
            if(st!=null) {
                try {
                    st.close();
                } catch(Throwable t) {}
            }
            if(con!=null) PoolManager.getInstance().releaseConnection(con);
        }
    }
    public static void saveUserDocuments(StdUser user) {
        Connection con = null;
        PreparedStatement ps = null;
        Statement st = null;
        try {
            con = PoolManager.getInstance().getConnection();
            ps = con.prepareStatement("DELETE FROM AUTH_HAB_DOC WHERE UTI_ID=?");
            ps.setLong(1, user.getId());
            ps.executeUpdate();
            ps = con.prepareStatement("INSERT INTO AUTH_HAB_DOC (UTI_ID, DOC_CODE) VALUES (?,?)");
            ps.setLong(1,user.getId());
            for(Pair p:user.getDocuments()) {
                ps.setString(2,p.key);
                ps.executeUpdate();
            }
        } catch(SQLException sqlEx) {
            logger.error("saveUserDocuments(StdUser)",sqlEx);
        } finally {
            if(ps!=null) {
                try {
                    ps.clearParameters();
                    ps.close();
                } catch(Throwable t) {}
            }
            if(st!=null) {
                try {
                    st.close();
                } catch(Throwable t) {}
            }
            if(con!=null) PoolManager.getInstance().releaseConnection(con);
        }
    }
    public static void saveUserCollectivites(StdUser user) {
        Connection con = null;
        PreparedStatement ps = null;
        Statement st = null;
        try {
            con = PoolManager.getInstance().getConnection();
            ps = con.prepareStatement("DELETE FROM AUTH_HAB_COL WHERE UTI_ID=?");
            ps.setLong(1, user.getId());
            ps.executeUpdate();
            ps = con.prepareStatement("INSERT INTO AUTH_HAB_COL (UTI_ID, TID_CODE, COL_CLEF) VALUES (?,?,?)");
            ps.setLong(1,user.getId());
            for(CollectiviteLib col:user.getCollectivites()) {
                ps.setString(2,col.natureIdentifiant);
                ps.setString(3,col.clef);
                ps.executeUpdate();
            }
        } catch(SQLException sqlEx) {
            logger.error("saveUserCollectivites(StdUser)",sqlEx);
        } finally {
            if(ps!=null) {
                try {
                    ps.clearParameters();
                    ps.close();
                } catch(Throwable t) {}
            }
            if(st!=null) {
                try {
                    st.close();
                } catch(Throwable t) {}
            }
            if(con!=null) PoolManager.getInstance().releaseConnection(con);
        }
    }

    public static class Profil implements Comparable {
        private String code, libelle;
        private ArrayList<String> fonctionnalites;

        public Profil(String code, String libelle) {
            super();
            this.code=code;
            this.libelle=libelle;
            fonctionnalites = new ArrayList<String>();
        }

        public String getCode() {
            return code;
        }

        public void setCode(String code) {
            this.code = code;
        }

        public ArrayList<String> getFonctionnalites() {
            return fonctionnalites;
        }

        public void addFonctionnalite(String fonct) {
            this.fonctionnalites.add(fonct);
        }

        public String getLibelle() {
            return libelle;
        }

        public void setLibelle(String libelle) {
            this.libelle = libelle;
        }
        public String toString() {
            return libelle.concat(" (").concat(code).concat(")");
        }

        public int compareTo(Object o) {
            if(o instanceof Profil) {
                return getCode().compareTo(((Profil)o).getCode());
            }
            return -1;
        }
    }

    public static class StdUser implements Comparable {
        private String login, prenom, nom;
        private boolean allFonctionalities, alldocuments, allCollectivites;
        private long id = -1;
        private List<Pair> profils = null;
        private ArrayList<CollectiviteLib> collectivites = null;
        private List<Pair> documents = null;
        public StdUser() {
            super();
        }
        public StdUser(String login, String nom, String prenom, boolean allFonctionnalities, boolean allDocuments, boolean allCollectivites, long id) {
            this();
            this.login=login;
            this.prenom=prenom;
            this.nom=nom;
            this.allCollectivites=allCollectivites;
            this.allFonctionalities=allFonctionnalities;
            this.alldocuments=allDocuments;
            this.id=id;
        }

        public int compareTo(Object o) {
            return login.compareTo(((StdUser)o).login);
        }

        @Override
        public String toString() {
            StringBuffer ret = new StringBuffer();
            if(prenom!=null) ret.append(prenom).append(" ");
            if(nom!=null) ret.append(nom).append(" ");
            if(login!=null) {
                if(prenom!=null || nom!=null) ret.append("(");
                ret.append(login);
                if(prenom!=null || nom!=null) ret.append(")");
            } else {
                ret.append("<non définit>");
            }
            return ret.toString();
        }

        public boolean isAllCollectivites() {
            return allCollectivites;
        }

        public void setAllCollectivites(boolean allCollectivites) {
            this.allCollectivites = allCollectivites;
        }

        public boolean isAllFonctionalities() {
            return allFonctionalities;
        }

        public void setAllFonctionalities(boolean allFonctionalities) {
            this.allFonctionalities = allFonctionalities;
        }

        public boolean isAlldocuments() {
            return alldocuments;
        }

        public void setAlldocuments(boolean alldocuments) {
            this.alldocuments = alldocuments;
        }

        public String getLogin() {
            return login;
        }

        public void setLogin(String login) {
            this.login = login;
        }

        public String getNom() {
            return nom;
        }

        public void setNom(String nom) {
            this.nom = nom;
        }

        public String getPrenom() {
            return prenom;
        }

        public void setPrenom(String prenom) {
            this.prenom = prenom;
        }

        public long getId() {
            return id;
        }

        public void setId(long id) {
            this.id = id;
        }

        public ArrayList<CollectiviteLib> getCollectivites() {
            if(collectivites==null) {
                setCollectivites(getUserCollectivites(id));
            }
            return collectivites;
        }

        public void setCollectivites(ArrayList<CollectiviteLib> collectivites) {
            this.collectivites = collectivites;
        }

        public List<Pair> getDocuments() {
            if(documents==null) {
                documents = getUserDocuments(getId());
            }
            return documents;
        }

        public void setDocuments(List<Pair> documents) {
            this.documents = documents;
        }

        public List<Pair> getProfils() {
            if(profils==null) {
                profils = getUserProfils(getId());
            }
            return profils;
        }

        public void setProfils(List<Pair> profils) {
            this.profils = profils;
        }
    }
    public static class CollectiviteLib implements Comparable {
        private String natureIdentifiant;
        private String clef;
        private String libelle;
        
        public CollectiviteLib (String natureIdentifiant, String clef, String libelle) {
            super();
            this.natureIdentifiant=natureIdentifiant;
            this.clef=clef;
            this.libelle=libelle;
        }

        public String getClef() {
            return clef;
        }

        public String getLibelle() {
            return libelle;
        }

        public String getNatureIdentifiant() {
            return natureIdentifiant;
        }

        public int compareTo(Object o) {
            if(o instanceof CollectiviteLib) {
                CollectiviteLib other = (CollectiviteLib) o;
                return other.natureIdentifiant.compareTo(natureIdentifiant) + other.clef.compareTo(clef);
            } else if(o instanceof Collectivite) {
                Collectivite other = (Collectivite)o;
                return other.getNatureIdentifiant().compareTo(natureIdentifiant) + other.getCollectivite().compareTo(clef);
            } else return -1;
        }
    }
    public static class CollectiviteResponse {
        private ArrayList<CollectiviteLib> data;
        private int foundRecords;
        public CollectiviteResponse(ArrayList<CollectiviteLib> data, int foundRows) {
            super();
            this.data=data;
            this.foundRecords=foundRows;
        }

        public ArrayList<CollectiviteLib> getData() {
            return data;
        }

        public int getFoundRecords() {
            return foundRecords;
        }
    }
    private static String transformPatternToSql(String pattern) {
        StringBuilder sb = new StringBuilder();
        boolean containsMatcher = false;
        for(char c:pattern.toCharArray()) {
            switch(c) {
                case '\'': {
                    sb.append("''");
                    break;
                }
                case '*': {
                    sb.append("%");
                    containsMatcher = true;
                    break;
                }
                case '_': {
                    sb.append("\\_");
                    break;
                }
                case '?': {
                    sb.append("_");
                    containsMatcher=true;
                    break;
                }
                case '%': {
                    sb.append("\\%");
                    break;
                }
                case '\\': {
                    sb.append("\\\\");
                    break;
                }
                default: sb.append(c);
            }
        }
        //return pattern.replaceAll("'", "''").replaceAll("*", "%").replaceAll("?", "_");
        if(!containsMatcher) {
            sb.insert(0, "%").append("%");
        }
        return sb.toString();
    }
}
