static List <KeyValuePair <string, string> > getComunas(int idp)
{
List <KeyValuePair <string, string> > lkv = new List <KeyValuePair <string, string> >();
try
{
SqlCommand cmd = new SqlCommand("select * from tbl_comuna where id_provincia =@idp ");
cmd.Connection = CDB.GetConnection();
cmd.CommandType = System.Data.CommandType.Text;
cmd.Parameters.AddWithValue("@idp", idp);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
string key = dr["id_comuna"].ToString();
string value = dr["nombre_comuna"].ToString();
lkv.Add(new KeyValuePair <string, string>(key, value));
}
}
catch (Exception ex)
{
Console.WriteLine("ERROR INSIDE checkSessionKey:" + ex.ToString());
}
return(lkv);
}
static List <KeyValuePair <string, string> > getRegiones()
{
List <KeyValuePair <string, string> > lkv = new List <KeyValuePair <string, string> >();
try
{
SqlCommand cmd = new SqlCommand("select * from tbl_region");
cmd.Connection = CDB.GetConnection();
cmd.CommandType = System.Data.CommandType.Text;
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
string key = dr["id_region"].ToString();
string value = dr["nombre_region"].ToString();
lkv.Add(new KeyValuePair <string, string>(key, value));
}
}
catch (Exception ex)
{
Console.WriteLine("ERROR INSIDE checkSessionKey:" + ex.ToString());
}
return(lkv);
}
public int Create()
{
int IdOferta = 0;
SqlConnection cnn = CDB.GetConnection();
try
{
SqlCommand cmd = new SqlCommand("sp_oferta_ins");
cmd.Connection = cnn;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@id_usuario", IDUsuario);
cmd.Parameters.AddWithValue("@id_profesion", IDProfesion);
cmd.Parameters.AddWithValue("@disponibilidad", Disponibilidad);
cmd.Parameters.AddWithValue("@remoto", Remoto);
cmd.Parameters.AddWithValue("@aconvenir", Aconvenir);
cmd.Parameters.AddWithValue("@hora", Hora);
cmd.Parameters.AddWithValue("@dia", Dia);
cmd.Parameters.AddWithValue("@mes", Mes);
cmd.Parameters.AddWithValue("@servicio", Servicio);
cmd.Parameters.AddWithValue("@region", Region);
cmd.Parameters.AddWithValue("@ciudad", Ciudad);
cmd.Parameters.AddWithValue("@comuna", Comuna);
cmd.Parameters.AddWithValue("@direccion", Direccion);
cmd.Parameters.AddWithValue("@lat", Lat);
cmd.Parameters.AddWithValue("@lng", Lng);
SqlParameter p = new SqlParameter("@idoferta", System.Data.SqlDbType.Int);
p.Direction = System.Data.ParameterDirection.Output;
cmd.Parameters.Add(p);
cmd.ExecuteNonQuery();
IdOferta = (int)p.Value;
}
catch (Exception ex)
{
Console.WriteLine("addcarDay exception:" + ex.ToString());
}
finally
{
if (cnn != null)
{
cnn.Close();
}
}
return(IdOferta);
}
public static List <CProfesion> List()
{
List <CProfesion> lp = new List <CProfesion>();
SqlConnection cnn = CDB.GetConnection();
SqlDataReader dr = null;
try
{
SqlCommand cmd = new SqlCommand("select [id_p],[create_date],[nombre] ,[estado] from tbl_profesion");
cmd.Connection = cnn;
//cmd.Parameters.AddWithValue("@id_car", idcar);
cmd.CommandType = System.Data.CommandType.Text;
dr = cmd.ExecuteReader();
while (dr.Read())
{
int idp = int.Parse(dr["id_p"].ToString());
string nombre = dr["nombre"].ToString();
lp.Add(new CProfesion {
IDProfesion = idp, Nombre = nombre
});
}
dr.Close();
}
catch (Exception ex)
{
Console.WriteLine("addcarDay exception:" + ex.ToString());
}
finally
{
if (dr != null && !dr.IsClosed)
{
dr.Close();
}
if (cnn != null)
{
cnn.Close();
}
}
return(lp);
}
public bool Update()
{
bool bolExito = true;
SqlConnection cnn = CDB.GetConnection();
try
{
SqlCommand cmd = new SqlCommand("sp_user_upd");
cmd.Connection = cnn;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@idu", Id);
cmd.Parameters.AddWithValue("@nombre", Nombre);
cmd.Parameters.AddWithValue("@apaterno", APaterno);
cmd.Parameters.AddWithValue("@amaterno", AMaterno);
cmd.Parameters.AddWithValue("@mail", Mail);
cmd.Parameters.AddWithValue("@clave", Clave);
cmd.Parameters.AddWithValue("@rating", Rating);
cmd.Parameters.AddWithValue("@link_foto", Avatar);
cmd.Parameters.AddWithValue("@link_cv", CV);
cmd.Parameters.AddWithValue("@descripcion", Descripcion);
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine(" exception:" + ex.ToString());
bolExito = false;
}
finally
{
if (cnn != null)
{
cnn.Close();
}
}
return(bolExito);
}
public int Create()
{
int IdUser = 0;
SqlConnection cnn = CDB.GetConnection();
try
{
SqlCommand cmd = new SqlCommand("sp_user_ins");
cmd.Connection = cnn;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@nombre", Nombre);
cmd.Parameters.AddWithValue("@apaterno", APaterno);
cmd.Parameters.AddWithValue("@amaterno", AMaterno);
cmd.Parameters.AddWithValue("@mail", Mail);
cmd.Parameters.AddWithValue("@clave", Clave);
SqlParameter p = new SqlParameter("@idu", System.Data.SqlDbType.Int);
p.Direction = System.Data.ParameterDirection.Output;
cmd.Parameters.Add(p);
cmd.ExecuteNonQuery();
IdUser = (int)p.Value;
}
catch (Exception ex)
{
Console.WriteLine(" exception:" + ex.ToString());
}
finally
{
if (cnn != null)
{
cnn.Close();
}
}
return(IdUser);
}
public int Create()
{
int IdProfesion = 0;
SqlConnection cnn = CDB.GetConnection();
try
{
SqlCommand cmd = new SqlCommand("sp_profesion_ins");
cmd.Connection = cnn;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@nombre", Nombre);
cmd.Parameters.AddWithValue("@estado", DateTime.Now);
SqlParameter p = new SqlParameter("@id_p", System.Data.SqlDbType.Int);
p.Direction = System.Data.ParameterDirection.Output;
cmd.Parameters.Add(p);
cmd.ExecuteNonQuery();
IdProfesion = (int)p.Value;
}
catch (Exception ex)
{
Console.WriteLine(" exception:" + ex.ToString());
}
finally
{
if (cnn != null)
{
cnn.Close();
}
}
return(IdProfesion);
}
public static List <COfertaResult> getOfertas(COfertaSearch par)
{
List <COfertaResult> ls = new List <COfertaResult>();
SqlDataReader dr = null;
SqlConnection cnn = CDB.GetConnection();
try
{
SqlCommand cmd = new SqlCommand("sp_oferta_search");
cmd.Connection = cnn;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@id_usuario", par.IDUsuario);
cmd.Parameters.AddWithValue("@id_profesion", par.IDProfesion);
cmd.Parameters.AddWithValue("@disponibilidad", par.Disponibilidad);
cmd.Parameters.AddWithValue("@remoto", par.Remoto);
cmd.Parameters.AddWithValue("@aconvenir", par.Aconvenir);
cmd.Parameters.AddWithValue("@hora", par.Hora);
cmd.Parameters.AddWithValue("@dia", par.Dia);
cmd.Parameters.AddWithValue("@mes", par.Mes);
cmd.Parameters.AddWithValue("@servicio", par.Servicio);
cmd.Parameters.AddWithValue("@region", par.Region);
cmd.Parameters.AddWithValue("@ciudad", par.Ciudad);
cmd.Parameters.AddWithValue("@comuna", par.Comuna);
cmd.Parameters.AddWithValue("@direccion", par.Direccion);
cmd.Parameters.AddWithValue("@lat", par.Lat);
cmd.Parameters.AddWithValue("@lng", par.Lng);
dr = cmd.ExecuteReader();
while (dr.Read())
{
COfertaResult r = new COfertaResult();
r.Oferta.Aconvenir = bool.Parse(dr["aconvenir"].ToString());
r.Oferta.Ciudad = int.Parse(dr["ciudad"].ToString());
r.Oferta.Comuna = int.Parse(dr["comuna"].ToString());
r.Oferta.CreaterDate = DateTime.Parse(dr["create_date"].ToString());
r.Oferta.Dia = int.Parse(dr["dia"].ToString());
r.Oferta.Direccion = dr["direccion"].ToString();
r.Oferta.Disponibilidad = int.Parse(dr["disponibilidad"].ToString());
r.Oferta.Hora = int.Parse(dr["hora"].ToString());
r.Oferta.IDOferta = int.Parse(dr["id_oferta"].ToString());
r.Oferta.IDProfesion = int.Parse(dr["id_profesion"].ToString());
r.Oferta.IDUsuario = int.Parse(dr["id_usuario"].ToString());
r.Oferta.Lat = double.Parse(dr["lat"].ToString());
r.Oferta.Lng = double.Parse(dr["lng"].ToString());
r.Oferta.Mes = int.Parse(dr["mes"].ToString());
r.Oferta.Region = int.Parse(dr["region"].ToString());
r.Oferta.Remoto = bool.Parse(dr["remoto"].ToString());
r.Oferta.Servicio = int.Parse(dr["servicio"].ToString());
r.User.Id = int.Parse(dr["id_usuario"].ToString());
r.User.Nombre = dr["nombre"].ToString();
r.User.Mail = dr["mail"].ToString();
r.User.Rating = int.Parse(dr["rating"].ToString());
r.User.Avatar = dr["link_foto"].ToString();
ls.Add(r);
}
dr.Close();
}
catch (Exception ex)
{
Console.WriteLine("addcarDay exception:" + ex.ToString());
}
finally
{
if (cnn != null)
{
cnn.Close();
}
}
return(ls);
}
static public CUser Load(int id, string mail, string clave, string key)
{
CUser user = new CUser();
user.Valido = false;
SqlConnection cnn = CDB.GetConnection();
SqlDataReader dr = null;
try
{
SqlCommand cmd = new SqlCommand("sp_user_get");
cmd.Connection = cnn;
cmd.Parameters.AddWithValue("@id", id);
cmd.Parameters.AddWithValue("@mail", mail);
cmd.Parameters.AddWithValue("@clave", clave);
cmd.Parameters.AddWithValue("@key", key);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
dr = cmd.ExecuteReader();
if (dr.Read())
{
int id_usuario = int.Parse(dr["id_usuario"].ToString());
string nombre = dr["nombre"].ToString();
string apaterno = dr["apaterno"].ToString();
string amaterno = dr["amaterno"].ToString();
mail = dr["mail"].ToString();
clave = dr["clave"].ToString();
string link_foto = dr["link_foto"].ToString();
string link_cv = dr["link_cv"].ToString();
string descripcion = dr["descripcion"].ToString();
string sessionkey = dr["sessionkey"].ToString();
int rating = int.Parse(dr["rating"].ToString());
user.Id = id_usuario;
user.Mail = mail;
user.AMaterno = amaterno;
user.APaterno = apaterno;
user.CV = link_cv;
user.Avatar = link_foto;
user.Descripcion = descripcion;
user.Key = sessionkey;
user.Valido = true;
user.Nombre = nombre;
user.Rating = rating;
}
dr.Close();
}
catch (Exception ex)
{
Console.WriteLine("addcarDay exception:" + ex.ToString());
}
finally
{
if (dr != null && !dr.IsClosed)
{
dr.Close();
}
if (cnn != null)
{
cnn.Close();
}
}
return(user);
}
