//BUSCAR A UNO
public Envio buscarEnvio(int cod)
{
NpgsqlConnection conn = DAO.getInstanceDAO();
conn.Open();
string sql = "SELECT e.\"COD\", to_char(e.\"FechaInicio\", 'DD-MM-YYYY'), to_char(e.\"FechaLlegada\", 'DD-MM-YYYY'), " +
"e.\"Monto\", em.\"Nombre\", es.\"Nombre\" " +
"FROM \"Envio\" e, \"Empleado\" em, \"Estatus\" es " +
"WHERE em.\"CI\" = e.\"FK-EmpleadoE\" and es.\"COD\" = e.\"FK-EstatusE\" and e.\"COD\" = " + cod + " ";
NpgsqlCommand cmd = new NpgsqlCommand(sql, conn);
NpgsqlDataReader dr = cmd.ExecuteReader();
Envio data = new Envio();
while (dr.Read())
{
System.Diagnostics.Debug.WriteLine("connection established");
data.cod = Int32.Parse(dr[0].ToString());
data.fechaInicio = dr[1].ToString();
data.fechaLlegada = dr[2].ToString();
data.monto = Int32.Parse(dr[3].ToString());
data.NombreEmpleadoE = dr[4].ToString();
data.NombreEstatusE = dr[5].ToString();
}
dr.Close();
conn.Close();
return(data);
}
//ELIMINAR Traslado
public int eliminarTraslado(int cod)
{
NpgsqlConnection conn = DAO.getInstanceDAO();
conn.Open();
String sql2 = "SELECT e.\"CODVeh-Rut\" " +
"FROM \"Traslado\" e " +
"WHERE \"CODEnvio\" = " + cod + " ";
NpgsqlCommand cmd2 = new NpgsqlCommand(sql2, conn);
NpgsqlDataReader dr = cmd2.ExecuteReader();
Envio data = new Envio();
while (dr.Read())
{
System.Diagnostics.Debug.WriteLine("connection established");
data.cod = Int32.Parse(dr[0].ToString());
}
dr.Close();
String sql = "DELETE FROM \"Traslado\" WHERE \"CODEnvio\" = " + cod + ";" +
"DELETE FROM \"Veh-Rut\" WHERE \"COD\" = " + data.cod + ";";
NpgsqlCommand cmd = new NpgsqlCommand(sql, conn);
int resp = cmd.ExecuteNonQuery(); //CONTROLAR EXCEPTION DE UNIQUE
conn.Close();
return(resp);
}
//BUSCAR ULTIMO VEH-RUT
public Envio buscarUltimoVehRut()
{
NpgsqlConnection conn = DAO.getInstanceDAO();
conn.Open();
string sql = "SELECT e.\"COD\" " +
"FROM \"Veh-Rut\" e " +
"Order by \"COD\" DESC " +
"LIMIT 1 ";
NpgsqlCommand cmd = new NpgsqlCommand(sql, conn);
NpgsqlDataReader dr = cmd.ExecuteReader();
Envio data = new Envio();
while (dr.Read())
{
System.Diagnostics.Debug.WriteLine("connection established");
data.cod = Int32.Parse(dr[0].ToString());
}
dr.Close();
conn.Close();
return(data);
}
//DETALLES DE ENVIO
public Envio detallesEnvio(int cod)
{
NpgsqlConnection conn = DAO.getInstanceDAO();
conn.Open();
string sql = "Select e.\"COD\", em.\"Nombre\"||' '||em.\"Apellido\", p.\"COD\", pg.\"COD\", vr.\"PlacaA\", " +
"vr.\"PlacaB\", vr.\"PlacaT\", vr.\"CODRuta\", c2.\"Nombre\" ||' '||c2.\"Apellido\", " +
"c.\"Nombre\" ||' '||c.\"Apellido\" " +
"FROM \"Paquete\" p, \"Envio\" e, \"Pago\" pg, \"Veh-Rut\" vr, \"Cliente\" c, " +
"\"Cliente\" c2, \"Empleado\" em, \"Traslado\" t " +
"Where em.\"CI\"= e.\"FK-EmpleadoE\" and e.\"COD\"= p.\"FK-EnvioP\" and pg.\"FK-EnvioP\"= e.\"COD\" and " +
"t.\"CODEnvio\"=e.\"COD\" and t.\"CODVeh-Rut\"=vr.\"COD\" and c.\"CI\" = p.\"FK-Cliente2\" and " +
"c2.\"CI\" = p.\"FK-Cliente1\" and e.\"COD\" = " + cod + " ; ";
NpgsqlCommand cmd = new NpgsqlCommand(sql, conn);
NpgsqlDataReader dr = cmd.ExecuteReader();
Envio data = new Envio();
while (dr.Read())
{
System.Diagnostics.Debug.WriteLine("connection established");
data.cod = Int32.Parse(dr[0].ToString());
data.NombreEmpleadoE = dr[1].ToString();
data.codPaquete = Int32.Parse(dr[2].ToString());
data.codPago = Int32.Parse(dr[3].ToString());
data.codVehiculo = dr[4].ToString();
if (data.codVehiculo == "")
{
data.codVehiculo = dr[5].ToString();
}
if (data.codVehiculo == "")
{
data.codVehiculo = dr[6].ToString();
}
data.codRuta = dr[7].ToString();
data.nombreClienteO = dr[8].ToString();
data.nombreClienteD = dr[9].ToString();
}
dr.Close();
if (data.cod == 0)
{
sql = "Select e.\"COD\", em.\"Nombre\"||' '||em.\"Apellido\", p.\"COD\", vr.\"PlacaA\", " +
"vr.\"PlacaB\", vr.\"PlacaT\", vr.\"CODRuta\", c2.\"Nombre\" ||' '||c2.\"Apellido\", " +
"c.\"Nombre\" ||' '||c.\"Apellido\" " +
"FROM \"Paquete\" p, \"Envio\" e, \"Veh-Rut\" vr, \"Cliente\" c, " +
"\"Cliente\" c2, \"Empleado\" em, \"Traslado\" t " +
"Where em.\"CI\"= e.\"FK-EmpleadoE\" and e.\"COD\"= p.\"FK-EnvioP\" and " +
"t.\"CODEnvio\"=e.\"COD\" and t.\"CODVeh-Rut\"=vr.\"COD\" and c.\"CI\" = p.\"FK-Cliente2\" and " +
"c2.\"CI\" = p.\"FK-Cliente1\" and e.\"COD\" = " + cod + " ; ";
cmd = new NpgsqlCommand(sql, conn);
dr = cmd.ExecuteReader();
data = new Envio();
while (dr.Read())
{
System.Diagnostics.Debug.WriteLine("connection established");
data.cod = Int32.Parse(dr[0].ToString());
data.NombreEmpleadoE = dr[1].ToString();
data.codPaquete = Int32.Parse(dr[2].ToString());
data.codPago = 0;
data.codVehiculo = dr[3].ToString();
if (data.codVehiculo == "")
{
data.codVehiculo = dr[4].ToString();
}
if (data.codVehiculo == "")
{
data.codVehiculo = dr[5].ToString();
}
data.codRuta = dr[6].ToString();
data.nombreClienteO = dr[7].ToString();
data.nombreClienteD = dr[8].ToString();
}
dr.Close();
}
if ((data.codVehiculo == null) && (data.codRuta == null))
{
sql = "Select em.\"Nombre\"||' '||em.\"Apellido\", p.\"COD\", pg.\"COD\", c2.\"Nombre\" ||' '||c2.\"Apellido\", " +
"c.\"Nombre\" ||' '||c.\"Apellido\" " +
"FROM \"Paquete\" p, \"Envio\" e, \"Pago\" pg, \"Cliente\" c, " +
"\"Cliente\" c2, \"Empleado\" em " +
"Where em.\"CI\"= e.\"FK-EmpleadoE\" and e.\"COD\"= p.\"FK-EnvioP\" and pg.\"FK-EnvioP\"= e.\"COD\" and " +
"c.\"CI\" = p.\"FK-Cliente2\" and " +
"c2.\"CI\" = p.\"FK-Cliente1\" and e.\"COD\" = " + cod + " ; ";
cmd = new NpgsqlCommand(sql, conn);
dr = cmd.ExecuteReader();
data = new Envio();
while (dr.Read())
{
System.Diagnostics.Debug.WriteLine("connection established");
data.NombreEmpleadoE = dr[0].ToString();
data.codPaquete = Int32.Parse(dr[1].ToString());
data.codPago = Int32.Parse(dr[2].ToString());
data.nombreClienteO = dr[3].ToString();
data.nombreClienteD = dr[4].ToString();
data.codVehiculo = "-";
data.codRuta = "-";
}
dr.Close();
}
conn.Close();
return(data);
}