public int getCantViajesFecha(Aeronave otraNave, DateTime fechaSalida)
{
try
{
//
// Open the SqlConnection.
//
con.Open();
//
// The following code uses an SqlCommand based on the SqlConnection.
//
SqlCommand cmd = new SqlCommand(String.Format("SELECT [GD2C2015].[JANADIAN_DATE].[Viajes_Fecha_Aeronave] ({0},'{1}') as Cant", otraNave.getId, fechaSalida), con);
DataTable dt = new DataTable();
dt.TableName = "Tabla";
dt.Load(cmd.ExecuteReader());
if (dt.Rows.Count == 0)
{
con.Close();
return 1;
}
foreach (DataRow Fila in dt.Rows)
{
int count = Convert.ToInt32(Fila["Cant"]);
con.Close();
return count;
}
con.Close();
}
catch (Exception exAlta)
{
exAlta.ToString();
con.Close();
return 1;
}
return 1;
}
internal Aeronave getAeronaveByMatricula(string matricula)
{
Aeronave aeronave = null;
try
{
//
// Open the SqlConnection.
//
con.Open();
//
// The following code uses an SqlCommand based on the SqlConnection.
//
SqlCommand cmd = new SqlCommand(String.Format("SELECT TOP 1 a.Id,a.Matricula,a.Modelo , a.KG_Disponibles, f.Nombre as Fabricante, t.Nombre as Tipo_Servicio, Fecha_Alta, Baja_Fuera_Servicio, Baja_Vida_Util, Fecha_Baja_Definitiva, Cant_Butacas_Ventanilla, Cant_Butacas_Pasillo,Habilitado FROM [GD2C2015].[JANADIAN_DATE].[Aeronave] a INNER JOIN [GD2C2015].[JANADIAN_DATE].[Fabricante] f ON (f.Id=a.Fabricante) INNER JOIN [GD2C2015].[JANADIAN_DATE].[Tipo_Servicio] t ON (t.Id=a.Tipo_Servicio) WHERE a.Matricula = '{0}' ", matricula), con);
DataTable dt = new DataTable();
dt.TableName = "Tabla";
dt.Load(cmd.ExecuteReader());
if (dt.Rows.Count == 0)
{
con.Close();
return null;
}
foreach (DataRow Fila in dt.Rows)
{
aeronave = new Aeronave(Convert.ToInt32(Fila["Id"]), Convert.ToString(Fila["Matricula"]), Convert.ToString(Fila["Modelo"]), Convert.ToDecimal(Fila["KG_Disponibles"]), Convert.ToString(Fila["Fabricante"]), Convert.ToInt32(Fila["Cant_Butacas_Ventanilla"]), Convert.ToInt32(Fila["Cant_Butacas_Pasillo"]), Convert.ToString(Fila["Tipo_Servicio"]));
}
con.Close();
}
catch (Exception exAlta)
{
con.Close();
throw (new Exception(exAlta.ToString()));
}
return aeronave;
}
private List<Aeronave> obtenerAeronavesSimilares(string modelo, int fabricante, int tipoServicio, Decimal kgDisponibles, int butacasVentanilla, int butacasPasillo, int idAeronave)
{
List<Aeronave> aeronavesSimilares = new List<Aeronave>();
try
{
//
// Open the SqlConnection.
//
con.Open();
//
// The following code uses an SqlCommand based on the SqlConnection.
//
SqlCommand cmd = new SqlCommand(String.Format("SELECT a.Id,a.Matricula,a.Modelo,a.KG_Disponibles,f.Nombre as Fabricante,t.Nombre as Tipo_Servicio,Fecha_Alta,Baja_Fuera_Servicio,Baja_Vida_Util,Fecha_Baja_Definitiva,Cant_Butacas_Ventanilla,Cant_Butacas_Pasillo,Habilitado FROM JANADIAN_DATE.Aeronave a INNER JOIN [GD2C2015].[JANADIAN_DATE].[Fabricante] f ON (f.Id=a.Fabricante) INNER JOIN [GD2C2015].[JANADIAN_DATE].[Tipo_Servicio] t ON (t.Id=a.Tipo_Servicio) WHERE a.ID<>{0} AND a.Fabricante={1} AND a.Tipo_Servicio={2} AND a.Modelo='{3}' AND a.Habilitado=1 and KG_Disponibles>={4} and Cant_Butacas_Ventanilla>={5} and Cant_Butacas_Pasillo>={6}", idAeronave, fabricante, tipoServicio, modelo, kgDisponibles, butacasVentanilla, butacasPasillo), con);
DataTable dt = new DataTable();
dt.TableName = "Tabla";
dt.Load(cmd.ExecuteReader());
if (dt.Rows.Count == 0)
{
con.Close();
return aeronavesSimilares;
}
foreach (DataRow Fila in dt.Rows)
{
Aeronave aeronave = new Aeronave(Convert.ToInt32(Fila["Id"]), Convert.ToString(Fila["Matricula"]), Convert.ToString(Fila["Modelo"]), Convert.ToDecimal(Fila["KG_Disponibles"]), Convert.ToString(Fila["Fabricante"]), Convert.ToInt32(Fila["Cant_Butacas_Ventanilla"]), Convert.ToInt32(Fila["Cant_Butacas_Pasillo"]), Convert.ToString(Fila["Tipo_Servicio"]));
aeronavesSimilares.Add(aeronave);
}
con.Close();
}
catch (Exception exAlta)
{
con.Close();
throw (new Exception(exAlta.ToString()));
}
return aeronavesSimilares;
}
private void migrarViajeAOtraAeronave(int idAeronave, Viaje v, Aeronave otraNave)
{
try
{
con.Open();
SqlCommand updateAeronave = new SqlCommand(String.Format("EXEC [GD2C2015].[JANADIAN_DATE].[reemplazarAeronaveViaje] {0},{1},{2}", idAeronave, v.getId, otraNave.getId), con);
updateAeronave.ExecuteNonQuery();
con.Close();
}
catch (Exception eUpdate)
{
Console.WriteLine(eUpdate.ToString());
con.Close();
throw (new Exception());
}
}
internal void modificarAeronave(Aeronave aeronaveSel)
{
try
{
con.Open();
int fabricante = 0;
int tipoServ = 0;
SqlCommand cmd = new SqlCommand(String.Format("SELECT TOP 1 Id FROM [GD2C2015].[JANADIAN_DATE].[Fabricante] WHERE Nombre='{0}' ", aeronaveSel.getFabricante), con);
DataTable dt = new DataTable();
dt.TableName = "Tabla1";
dt.Load(cmd.ExecuteReader());
if (dt.Rows.Count == 0)
{
con.Close();
throw (new Exception("No existe el fabricante"));
}
foreach (DataRow Fila in dt.Rows)
{
fabricante = Convert.ToInt32(Fila["Id"]);
}
cmd = new SqlCommand(String.Format("SELECT TOP 1 Id FROM [GD2C2015].[JANADIAN_DATE].[Tipo_Servicio] WHERE Nombre='{0}' ", aeronaveSel.getTipoServicio), con);
dt = new DataTable();
dt.TableName = "Tabla1";
dt.Load(cmd.ExecuteReader());
if (dt.Rows.Count == 0)
{
con.Close();
throw (new Exception("No existe el tipo de servicio"));
}
foreach (DataRow Fila in dt.Rows)
{
tipoServ = Convert.ToInt32(Fila["Id"]);
}
SqlCommand updateAeronave = new SqlCommand(String.Format("UPDATE [GD2C2015].[JANADIAN_DATE].[Aeronave] SET Modelo='{0}',Matricula='{2}',KG_Disponibles={3},Tipo_Servicio={4},Fabricante={5},Cant_Butacas_Ventanilla={6},Cant_Butacas_Pasillo={7} WHERE Id={8}", aeronaveSel.getModelo, aeronaveSel.getHabilitado ? "1" : "0", aeronaveSel.getMatricula, aeronaveSel.getKGDisponibles, tipoServ, fabricante, aeronaveSel.getCantidadButacasVentanilla, aeronaveSel.getCantidadButacasPasillo, aeronaveSel.getId), con);
updateAeronave.ExecuteNonQuery();
con.Close();
}
catch (Exception exAlta)
{
con.Close();
throw (new Exception(exAlta.ToString()));
}
}
internal void insertarViaje(Viaje v, Aeronave a, Ruta r)
{
try
{
con.Open();
SqlCommand insertRol = new SqlCommand(String.Format("INSERT INTO [GD2C2015].[JANADIAN_DATE].[Viaje] (FechaSalida,Fecha_Llegada_Estimada,Aeronave,Ruta) VALUES ('{0}','{1}',{2},{3})", v.getFechaSalida, v.getFechaLlegadaEstimada, a.getId, r.getId), con);
insertRol.ExecuteNonQuery();
con.Close();
}
catch (Exception exAlta)
{
con.Close();
throw (new Exception(exAlta.ToString()));
}
}
internal Viaje getViaje(Aeronave nave, object origen, object destino)
{
Viaje viaje = null;
try
{
//
// Open the SqlConnection.
//
con.Open();
//
// The following code uses an SqlCommand based on the SqlConnection.
//
SqlCommand cmd = new SqlCommand(String.Format("SELECT TOP 1 v.[Id],[FechaSalida],[Fecha_Llegada_Estimada],[FechaLlegada],[Aeronave],[Ruta] FROM [GD2C2015].[JANADIAN_DATE].[Viaje] v INNER JOIN [GD2C2015].[JANADIAN_DATE].[Ruta] r ON (v.Ruta=r.Id) INNER JOIN [GD2C2015].[JANADIAN_DATE].[Ciudad] c ON (r.Ciudad_Origen=c.Id) INNER JOIN [GD2C2015].[JANADIAN_DATE].[Ciudad] c2 ON (r.Ciudad_Destino=c2.Id) WHERE v.Aeronave={0} AND c.Nombre='{1}' AND c2.Nombre='{2}' AND FechaLlegada IS NULL ", nave.getId, origen.ToString(), destino.ToString()), con);
DataTable dt = new DataTable();
dt.TableName = "Tabla";
dt.Load(cmd.ExecuteReader());
if (dt.Rows.Count == 0)
{
con.Close();
return null;
}
foreach (DataRow Fila in dt.Rows)
{
viaje = new Viaje(Convert.ToInt32(Fila["Id"]), Convert.ToInt32(Fila["Aeronave"]), Convert.ToInt32(Fila["Ruta"]), Convert.ToDateTime(Fila["FechaSalida"]), Fila["FechaLlegada"].GetType().ToString().Contains("Null") ? new DateTime() : Convert.ToDateTime(Fila["FechaLlegada"]), Convert.ToDateTime(Fila["Fecha_Llegada_Estimada"]));
}
con.Close();
}
catch (Exception exAlta)
{
con.Close();
throw (new Exception(exAlta.ToString()));
}
return viaje;
}