public z_cargos getz_cargos(int grado, int id_compania) { var myz_cargos = new z_cargos(); var myBase = new CnxBase(); string reqSQL = "SELECT id_cargo,z_cargos.id_voluntario,grado,orden_antiguedad,cargo_antiguedad,llamado_oficial, reemplaza_a FROM z_cargos, z_voluntarios WHERE (grado=" + grado + " and reemplaza_a=0 and activo=true and z_cargos.id_voluntario=z_voluntarios.id_voluntario and z_voluntarios.id_compania=" + id_compania + ") order by orden_antiguedad"; try { NpgsqlConnection myConn = myBase.OpenConnection(myBase.cnxString); var myCommand = new NpgsqlCommand(reqSQL, myConn); NpgsqlDataReader myReader = myCommand.ExecuteReader(); if (myReader.Read()) { myz_cargos.id_cargo = Convert.ToInt32(myReader[0]); myz_cargos.id_voluntario = Convert.ToInt32(myReader[1]); myz_cargos.grado = Convert.ToInt32(myReader[2]); myz_cargos.orden_antiguedad = Convert.ToInt32(myReader[3]); myz_cargos.cargo_antiguedad = Convert.ToInt32(myReader[4]); myz_cargos.llamado_oficial = Convert.ToInt32(myReader[5]); myz_cargos.reemplaza_a = Convert.ToInt32(myReader[6]); } myBase.CloseConnection(myConn); } catch (Exception myErr) { throw (new Exception(myErr + reqSQL)); } return(myz_cargos); }
public z_cargos getObjectz_cargos_llam(Int32 myID) { var myz_cargos = new z_cargos(); var myBase = new CnxBase(); // se añade join con tabla z_oficiales string reqSQL = "SELECT id_cargo,id_voluntario,z_oficiales.grado,orden_antiguedad,cargo_antiguedad,llamado_oficial,reemplaza_a,z_cargos.id_oficial FROM z_cargos, z_oficiales WHERE (llamado_oficial=" + myID + ") AND z_cargos.id_oficial = z_oficiales.id_oficial"; try { NpgsqlConnection myConn = myBase.OpenConnection(myBase.cnxString); var myCommand = new NpgsqlCommand(reqSQL, myConn); NpgsqlDataReader myReader = myCommand.ExecuteReader(); if (myReader.Read()) { myz_cargos.id_cargo = Convert.ToInt32(myReader[0]); myz_cargos.id_voluntario = Convert.ToInt32(myReader[1]); myz_cargos.grado = Convert.ToInt32(myReader[2]); myz_cargos.orden_antiguedad = Convert.ToInt32(myReader[3]); myz_cargos.cargo_antiguedad = Convert.ToInt32(myReader[4]); myz_cargos.llamado_oficial = Convert.ToInt32(myReader[5]); myz_cargos.reemplaza_a = Convert.ToInt32(myReader[6]); myz_cargos.id_oficial = Convert.ToInt32(myReader[7]); } myBase.CloseConnection(myConn); } catch (Exception myErr) { throw (new Exception(myErr + reqSQL)); } return(myz_cargos); }
/// <summary> /// modify a record /// </summary> public void modifyz_cargos(z_cargos myz_cargos) { var myBase = new CnxBase(); string reqSQL = string.Format("UPDATE z_cargos SET id_voluntario={0},grado={1},orden_antiguedad={2},cargo_antiguedad={3},llamado_oficial={4},id_oficial={5},activo={6} WHERE (id_cargo={7})", myz_cargos.id_voluntario, myz_cargos.grado, myz_cargos.orden_antiguedad, myz_cargos.cargo_antiguedad, myz_cargos.llamado_oficial, myz_cargos.id_oficial, myz_cargos.activo, myz_cargos.id_cargo); try { NpgsqlConnection myConn = myBase.OpenConnection(myBase.cnxString); var myCommand = new NpgsqlCommand(reqSQL, myConn); myCommand.ExecuteNonQuery(); myBase.CloseConnection(myConn); } catch (Exception myErr) { throw (new Exception(myErr + reqSQL)); } }
/// <summary> /// add a record /// </summary> public int addz_cargos(z_cargos myz_cargos) { var myBase = new CnxBase(); int id; string reqSQL = string.Format( "INSERT INTO z_cargos (id_voluntario,grado,orden_antiguedad,cargo_antiguedad,llamado_oficial,id_oficial,activo) VALUES ({0},0,(select coalesce((select max(orden_antiguedad) from z_cargos where id_oficial={1} group by id_oficial),0) +1 as orden),(select coalesce((select max(cargo_antiguedad) from z_cargos where id_oficial={1} group by id_oficial),0) +1 as cargo),{2},{1},{3}); select currval('z_cargos_id_cargo_seq')", myz_cargos.id_voluntario, myz_cargos.id_oficial, myz_cargos.llamado_oficial, myz_cargos.activo); try { NpgsqlConnection myConn = myBase.OpenConnection(myBase.cnxString); var myCommand = new NpgsqlCommand(reqSQL, myConn); id = Convert.ToInt32(myCommand.ExecuteScalar()); myBase.CloseConnection(myConn); return(id); } catch (Exception myErr) { throw (new Exception(myErr + reqSQL)); } }