public CargarFinca CargarPorId(int id)
{
CargarFinca cargarFinca = null;
conectar.Open();
SqlCommand cm = new SqlCommand(" SELECT " +
" 'id' = F.FIN_ID," +
" 'fin_descripcion' = F.FIN_DESC," +
" 'per_id' = F.PER_ID," +
" 'nombres' = CONCAT(PE.PER_APELLIDO1,' ', PE.PER_APELLIDO2, ' ', PE.PER_NOMBRE1, ' ', PE.PER_NOMBRE2)," +
" 'ver_id' = F.VER_ID," +
" 'ver_descripcion' = V.VER_DESC" +
" FROM COM.FINCA F" +
" INNER JOIN COM.PERSONA PE ON PE.PER_ID = F.PER_ID" +
" INNER JOIN COM.VEREDA V ON V.VER_ID = F.VER_ID" +
" WHERE F.FIN_ID = " + id, conectar);
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(cm);
da.Fill(ds, "fincas");
DataTable dt = ds.Tables[0];
foreach (DataRow dr in dt.Rows)
{
cargarFinca = getCargarFinca(dr);
}
conectar.Close();
return(cargarFinca);
}
public CargarFinca Agregar(Finca finca)
{
conectar.Open();
SqlCommand cm = new SqlCommand("INSERT INTO COM.FINCA (PER_ID, VER_ID, FIN_DESC) VALUES ('" + finca.per_id + "', '" + finca.ver_id + "', '" + finca.fin_desc + "')", conectar);
cm.ExecuteNonQuery();
CargarFinca cargarFinca = null;
cm = new SqlCommand(" SELECT TOP 1 " +
" 'id' = F.FIN_ID," +
" 'fin_descripcion' = F.FIN_DESC," +
" 'per_id' = F.PER_ID," +
" 'nombres' = CONCAT(PE.PER_APELLIDO1,' ', PE.PER_APELLIDO2, ' ', PE.PER_NOMBRE1, ' ', PE.PER_NOMBRE2)," +
" 'ver_id' = F.VER_ID," +
" 'ver_descripcion' = V.VER_DESC" +
" FROM COM.FINCA F" +
" INNER JOIN COM.PERSONA PE ON PE.PER_ID = F.PER_ID" +
" INNER JOIN COM.VEREDA V ON V.VER_ID = F.VER_ID" +
" ORDER BY F.FIN_ID DESC ", conectar);
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(cm);
da.Fill(ds, "finca");
DataTable dt = ds.Tables[0];
foreach (DataRow dr in dt.Rows)
{
cargarFinca = getCargarFinca(dr);
}
conectar.Close();
return(cargarFinca);
}
public CargarFinca Actualizar(Finca finca)
{
conectar.Open();
SqlCommand cm = new SqlCommand("UPDATE COM.FINCA SET PER_ID = @PERSONA, VER_ID = @VEREDA, FIN_DESC = @FINCA WHERE FIN_ID = @ID", conectar);
cm.Parameters.AddWithValue("PERSONA", finca.per_id);
cm.Parameters.AddWithValue("VEREDA", finca.ver_id);
cm.Parameters.AddWithValue("FINCA", finca.fin_desc);
cm.Parameters.AddWithValue("ID", finca.fin_id);
cm.ExecuteNonQuery();
CargarFinca cargarFinca = null;
cm = new SqlCommand(" SELECT " +
" 'id' = F.FIN_ID," +
" 'fin_descripcion' = F.FIN_DESC," +
" 'per_id' = F.PER_ID," +
" 'nombres' = CONCAT(PE.PER_APELLIDO1,' ', PE.PER_APELLIDO2, ' ', PE.PER_NOMBRE1, ' ', PE.PER_NOMBRE2)," +
" 'ver_id' = F.VER_ID," +
" 'ver_descripcion' = V.VER_DESC" +
" FROM COM.FINCA F" +
" INNER JOIN COM.PERSONA PE ON PE.PER_ID = F.PER_ID" +
" INNER JOIN COM.VEREDA V ON V.VER_ID = F.VER_ID" +
" WHERE F.FIN_ID = " + finca.fin_id, conectar);
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(cm);
da.Fill(ds, "finca");
DataTable dt = ds.Tables[0];
foreach (DataRow dr in dt.Rows)
{
cargarFinca = getCargarFinca(dr);
}
conectar.Close();
return(cargarFinca);
}
private CargarMonitoreo ReturnMonitoreo(DataRow dr)
{
CargarMonitoreo monitoreo;
Plaga plaga = new Plaga
{
id = Convert.ToInt32(dr["plaga_id"]),
nombre = dr["plaga_nombre"].ToString(),
descripcion = dr["plaga_descripcion"].ToString()
};
CargarFinca finca = new CargarFinca
{
id = Convert.ToInt32(dr["finca_id"]),
descripcion = dr["finca_nombre"].ToString(),
persona = new Persona
{
id = Convert.ToInt32(dr["per_id"]),
nombres = dr["per_nombres"].ToString()
},
vereda = new Vereda
{
id = Convert.ToInt32(dr["vereda_id"]),
descripcion = dr["vereda_nombre"].ToString()
}
};
monitoreo = new CargarMonitoreo
{
id = Convert.ToInt32(dr["id"]),
plaga = plaga,
finca = finca,
moni_fecha = dr["fecha"].ToString(),
usuario = dr["vereda_id"].ToString(),
fecha_creacion = dr["fecha_creacion"].ToString()
};
return(monitoreo);
}
