/*
 * GestionLlamadas.java
 *
 * Created on 20 de noviembre de 2007, 12:54
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package modelo;

import java.sql.*;
import java.util.*;
import javabeans.*;
import java.text.*;
public class GestionLlamadas {
   Datos dt;
   
   public GestionLlamadas(String driver, String cadenacon) {
        dt=new Datos(driver,cadenacon);
        
   }
   public ArrayList<LlamadaBean> getLlamadas(String password){
       
       String query = "select * from llamadas where ";
       query+="telefono in (select telefono from telefonos where ";
       query+="password ='"+password+"') order by telefono";
       return getListado(query); 
   } 
   private ArrayList<LlamadaBean> getListado(String sql){
        DateFormat feuropa=DateFormat.getDateInstance(DateFormat.SHORT,Locale.getDefault());
        ArrayList<LlamadaBean> llamadas=new ArrayList<LlamadaBean>();
        try{
         Connection cn=dt.getConexion();
         Statement st =cn.createStatement();
         ResultSet rs = st.executeQuery(sql); 
         while(rs.next()){
             LlamadaBean llamada=new LlamadaBean();
             llamada.setTelefono(rs.getInt("telefono"));
             llamada.setDestino(rs.getInt("destino"));
             llamada.setDuracion(rs.getInt("duracion"));
             llamada.setTarifa(this.getTarifa(rs.getInt("idtipotarifa")));
             llamada.setCoste(rs.getDouble("coste"));
             llamada.setFecha(feuropa.format(rs.getDate("fecha")));
             llamadas.add(llamada);    
         }
         dt.cierraConexion(cn);
        }
        catch(Exception e){
             e.printStackTrace();
        }
        finally{
            return llamadas;
        }    
   }
   public double getFactura(String password){
       String query = "select sum(coste) as total from llamadas where ";
       query+="telefono in (select telefono from telefonos where ";
       query+="password ='"+password+"')";
       double total=0.0;
        try{
         Connection cn=dt.getConexion();
         Statement st =cn.createStatement();
         ResultSet rs = st.executeQuery(query); 
         if(rs.next()){
            total=rs.getDouble("total");
         }
         dt.cierraConexion(cn);
        }
        catch(Exception e){
             e.printStackTrace();
        }
        finally{
            return total;
        }       
   }
   private String getTarifa(int id){
       String tarifa=null;
       try{
         Connection cn=dt.getConexion();
         String query = "select nombretarifa from tarifas ";
         query +="where idtipotarifa="+id;
         Statement st =cn.createStatement();
         ResultSet rs = st.executeQuery(query);
         if(rs.next()){
             tarifa=rs.getString("nombretarifa");
         }
         dt.cierraConexion(cn);
       }
       catch(Exception e){
            e.printStackTrace();
       }
       finally{
           return tarifa;
       }
   }
}
