source: ether_eccad/trunk/ECCAD_INTERFACE/WEB-INF/src/org/medias/eccad/persistance/jdbc/ColorTabDAOjdbc.java @ 68

package org.medias.eccad.persistance.jdbc;

import java.awt.Color;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;


import org.medias.eccad.helpers.LoggerPerso;
import org.medias.eccad.modele.ColorMap;
import org.medias.eccad.modele.ColorTab;
import org.medias.eccad.persistance.dao.ColorTabDAO;
import org.medias.eccad.persistance.exception.PersistanceException;

public class ColorTabDAOjdbc extends GeneriqueDAOjdbc implements ColorTabDAO {

        
        
        
        public ColorTab getColorMapByGrille(long id_grille) throws PersistanceException {
                ColorTab colortab = new ColorTab();
                colortab = getTableDefault(id_grille, colortab);
                //colortab = getMinMax(oid, colortab);
                
                return colortab;
        }
        
        public List<ColorMap> getListColorTab() throws PersistanceException {
                String sql = " select cm_name, " 
                           + " from colormap "
                           + " order by cm_id;";
                           //+ " join grille using (id_param) "
                           
                
                Statement requete = null;
                ResultSet resultat = null;
                Connection conn = getConnection();
                
                LoggerPerso.log(ColorTabDAOjdbc.class, LoggerPerso.DEBUG, " récupération liste de colortab " + sql);
                
                try {
                        requete = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                        resultat = requete.executeQuery(sql);
                } catch (SQLException e) {
                        closeConnection();
                        throw new PersistanceException(e);
                }
                
                List<ColorMap> liste_colormap = new LinkedList<ColorMap>();
                
                try {
                        while (resultat.next()) {
                                liste_colormap.add(new ColorMap(resultat.getLong("cm_id"), resultat.getString("cm_name"), resultat.getInt("pc_defaut")));
                        }
                }
                catch (Exception e) {
                        throw new PersistanceException(e);
                }
                finally {
                        closeConnection();
                }
                
                return liste_colormap;
        }
        
        public List<ColorMap> getListColorTabByGrille(long id_grille) throws PersistanceException {
                String sql = " select distinct cm_id, cm_name, pc_defaut" 
                           + " from colormap join param_color using (cm_id) "
                           + " join grille using (id_param) "
                           + " where id_grille = " + id_grille + " order by pc_defaut desc;";
                
                Statement requete = null;
                ResultSet resultat = null;
                Connection conn = getConnection();
                
                LoggerPerso.log(ColorTabDAOjdbc.class, LoggerPerso.DEBUG, " récupération liste de colortab " + sql);
                
                try {
                        requete = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                        resultat = requete.executeQuery(sql);
                } catch (SQLException e) {
                        closeConnection();
                        throw new PersistanceException(e);
                }
                
                List<ColorMap> liste_colormap = new LinkedList<ColorMap>();
                
                try {
                        while (resultat.next()) {
                                liste_colormap.add(new ColorMap(resultat.getLong("cm_id"), resultat.getString("cm_name"), resultat.getInt("pc_defaut")));
                        }
                }
                catch (Exception e) {
                        throw new PersistanceException(e);
                }
                finally {
                        closeConnection();
                }
                
                return liste_colormap;
        }
        
        private ColorTab getTableDefault(long id_grille, ColorTab colortab) throws PersistanceException {
                Statement requete = null;
                String sql = " select distinct col_r, col_g, col_b, ci_min, ci_max" 
                                   + " from color join color_index using (col_id)"
                                   + " join param_color using (cm_id) "
                                   + " join grille   using (id_param) "
                                   + " where id_grille = " + id_grille +" and pc_defaut = 1 order by ci_max;";
                Connection conn = getConnection();
                
                ResultSet resultat = null;
                
                
                LoggerPerso.log(ColorTabDAOjdbc.class, LoggerPerso.DEBUG, "*************************************REQUETE = " + sql);
                try {
                        requete = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                        resultat = requete.executeQuery(sql);
                } catch (SQLException e) {
                        closeConnection();
                        throw new PersistanceException(e);
                }
                
                List<Color> liste_couleur = new LinkedList<Color>();
                List<Float> liste_min = new LinkedList<Float>();
                int i=0;

                
                
                try {
                        while (resultat.next()) {
                                liste_couleur.add(new Color(resultat.getInt("col_r"), resultat.getInt("col_g"), resultat.getInt("col_b")));
                                liste_min.add(resultat.getFloat("ci_min"));
                                colortab.setMax(resultat.getFloat("ci_max"));
                                i++;
                        }
                }
                catch (SQLException sqle) {
                        throw new PersistanceException(sqle);
                }
                finally {
                        closeConnection();      
                }
                Color[] color_tab = new Color[i];
                float[] tab_min   = new float[i];
                

                for (i=0; i<liste_min.size(); i++) {
                        tab_min[i] = liste_min.get(i);
                        color_tab[i] = liste_couleur.get(i);
                }
                colortab.setTab_min(tab_min);
                colortab.setTab_couleur(color_tab);
                
                return colortab;
        }

        public ColorTab getColorMapByParam(int id_param) {
                // TODO Auto-generated method stub
                return null;
        }

        public ColorTab getColorMapByID(long id) throws PersistanceException {
                Statement requete = null;
                ColorTab colortab = new ColorTab();
                
                String sql = " select distinct col_r, col_g, col_b, ci_min, ci_max, cm_name" 
                                   + " from color join color_index using (col_id) join colormap using (cm_id)"
                                   + " where cm_id = " + id +" order by ci_min;";
                Connection conn = getConnection();
                
                ResultSet resultat = null;
                
                
                LoggerPerso.log(ColorTabDAOjdbc.class, LoggerPerso.DEBUG, "REQUETE = " + sql);
                try {
                        requete = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                        resultat = requete.executeQuery(sql);
                } catch (SQLException e) {
                        closeConnection();
                        throw new PersistanceException(e);
                }
                
                List<Color> liste_couleur = new LinkedList<Color>();
                List<Float> liste_min = new LinkedList<Float>();
                int i=0;

                
                
                try {
                        
                        while (resultat.next()) {
                                colortab.setName(resultat.getString("cm_name"));
                                liste_couleur.add(new Color(resultat.getInt("col_r"), resultat.getInt("col_g"), resultat.getInt("col_b")));
                                liste_min.add(resultat.getFloat("ci_min"));
                                colortab.setMax(resultat.getFloat("ci_max"));
                                i++;
                        }
                }
                catch (SQLException sqle) {
                        throw new PersistanceException(sqle);
                }
                finally {
                        closeConnection();      
                }
                Color[] color_tab = new Color[i];
                float[] tab_min   = new float[i];
                

                for (i=0; i<liste_min.size(); i++) {
                        tab_min[i] = liste_min.get(i);
                        color_tab[i] = liste_couleur.get(i);
                }
                colortab.setTab_min(tab_min);
                colortab.setTab_couleur(color_tab);
                
                return colortab;
        }

        public List<ColorMap> getListColorMapDynamique() throws PersistanceException {
                String sql = " select distinct clm_id, clm_name" 
                           + " from class_map "
                           + " where clm_name ilike '%exponential%' or clm_name ilike '%linear%' or clm_name ilike '%logarithm%' order by clm_name;";
                
                
                
                Statement requete = null;
                ResultSet resultat = null;
                Connection conn = getConnection();
                
                LoggerPerso.log(ColorTabDAOjdbc.class, LoggerPerso.DEBUG, " récupération liste de colortab " + sql);
                
                try {
                        requete = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                        resultat = requete.executeQuery(sql);
                } catch (SQLException e) {
                        closeConnection();
                        throw new PersistanceException(e);
                }
                
                List<ColorMap> liste_colormap = new LinkedList<ColorMap>();
                
                try {
                        while (resultat.next()) {
                                liste_colormap.add(new ColorMap(resultat.getLong("cm_id"), resultat.getString("cm_name"), 0));
                        }
                }
                catch (Exception e) {
                        throw new PersistanceException(e);
                }
                finally {
                        closeConnection();
                }
                
                return liste_colormap;
        }

}