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));
}
}
