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

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.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() throws PersistanceException {
                ColorTab colortab = new ColorTab();
                colortab = getTableDefault(colortab);
                //colortab = getMinMax(oid, colortab);
                
                return colortab;
        }
        
        public List<ColorMap> getListClassMap() throws PersistanceException {
                String sql = " select clm_id, clm_name, num_order from class_map order by clm_id";              
                
                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("clm_id"), resultat.getString("clm_name"), resultat.getInt("num_order")));
                        }
                }
                catch (Exception e) {
                        throw new PersistanceException(e);
                }
                finally {
                        closeConnection();
                }
                
                return liste_colormap;
        }
        
        public List<ColorMap> getListColorTabByGrille() 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;";*/
                
                String sql = " select clm_id as cm_id, clm_name as cm_name, num_order as pc_defaut from class_map order by clm_id";
                
                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(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;";*/

                String sql = " select distinct col_r, col_g, col_b, class_index.num_order"
                                   + " from color join class_index using (col_id)"
                                   + " join class_map using (clm_id) "
                                   + " where class_map.num_order=1 order by class_index.num_order;";
                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;

                float ci_min=-1;
                float ci_max=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"));
                                
                                liste_min.add(ci_min);
                                colortab.setMax(ci_max);
                                
                                if (ci_min==-1) ci_min=0;
                                else ci_min=ci_min+10;
                                
                                ci_max=ci_max+10;
                                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, long idClass) 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;";*/

                String sql="select distinct col_r, col_g, col_b, clm_name as cm_name, ci.num_order, val_min, val_max" 
                        +" from color as co, class_index as ci, class_map as cm, class_distribution as cd" 
                        +" where co.col_id=ci.col_id and ci.clm_id=cm.clm_id and cm.clm_id="+id+" and cd.scl_id="+idClass
                        +" and cd.num_order=ci.num_order order by ci.num_order;";
                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("val_min"));
                                colortab.setMax(resultat.getFloat("val_max"));
//                              LoggerPerso.log(ColorTabDAOjdbc.class, LoggerPerso.DEBUG, "NAME="+resultat.getString("cm_name")+", RANGE = " +resultat.getFloat("val_min")+" - "+resultat.getFloat("val_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 order by clm_id";
                           //+ " where clm_name like '%exponential%' or clm_name like '%linear%' or clm_name like '%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("clm_id"), resultat.getString("clm_name"), 0));
                        }
                }
                catch (Exception e) {
                        throw new PersistanceException(e);
                }
                finally {
                        closeConnection();
                }
                
                return liste_colormap;
        }*/

}