/// <summary>
/// Obtener Lista de Clientes
/// </summary>
/// <param name="page"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public static ClienteList Get(int page, int pageSize)
{
ClienteList res = new ClienteList();
SqlCommand cmd = null;
SqlDataReader dr = null;
string query = @"SELECT * FROM Cliente
INNER JOIN Persona ON Cliente.IdPersona=Persona.IdPersona
WHERE Persona.Borrado=0 ORDER BY Nombre ASC
OFFSET @pageSize * (@page - 1) ROWS FETCH NEXT @pageSize ROWS ONLY";
try
{
cmd = Methods.CreateBasicCommand(query);
cmd.Parameters.AddWithValue("@page", page);
cmd.Parameters.AddWithValue("@pageSize", pageSize);
dr = Methods.ExecuteDataReaderCommand(cmd);
while (dr.Read())
{
res.Add(new Cliente()
{
IdPersona = dr.GetInt32(0),
Nit = dr.GetString(1),
Razon = dr.GetString(2),
FechaRegistro = dr.GetDateTime(3),
Ci = dr.GetString(5),
Nombre = dr.GetString(6),
PrimerApellido = dr.GetString(7),
SegundoApellido = dr.GetString(8),
Sexo = SexoDal.Get(dr.GetInt32(9)),
FechaNacimiento = dr.GetDateTime(10),
Usuario = UsuarioDal.Get(dr.GetInt32(11)),
Correos = CorreoDal.GetList(dr.GetInt32(0)),
Telefonos = TelefonoDal.GetList(dr.GetInt32(0)),
Direcciones = DireccionDal.GetList(dr.GetInt32(0))
});
}
}
catch (SqlException ex)
{
//Methods.GenerateLogsRelease("KeyValuePacienteListDal", "Obtener", ex.Message + " " + ex.StackTrace);
throw ex;
}
catch (Exception ex)
{
//Methods.GenerateLogsRelease("KeyValuePacienteListDal", "Obtener", ex.Message + " " + ex.StackTrace);
throw ex;
}
finally
{
cmd.Connection.Close();
}
return(res);
}
/// <summary>
/// Obtiene la informacion de un personal
/// </summary>
/// <param name="id">identificador del personal </param>
/// <returns></returns>
public static Personal Get(int id)
{
Personal res = new Personal();
SqlCommand cmd = null;
SqlDataReader dr = null;
string query = "SELECT * FROM Personal WHERE IdPersona = @id";
try
{
cmd = Methods.CreateBasicCommand(query);
cmd.Parameters.AddWithValue("@id", id);
dr = Methods.ExecuteDataReaderCommand(cmd);
while (dr.Read())
{
res = new Personal()
{
IdPersona = dr.GetInt32(0),
CodPersonal = dr.GetString(1),
FechaIngreso = dr.GetDateTime(2),
Cargo = CargoDal.Get(dr.GetInt32(3)),
Sueldo = dr.GetDecimal(4)
};
}
Persona persona = PersonaDal.Get(res.IdPersona);
res.Ci = persona.Ci;
res.Nombre = persona.Nombre;
res.PrimerApellido = persona.PrimerApellido;
res.SegundoApellido = persona.SegundoApellido;
res.Sexo = persona.Sexo;
res.FechaNacimiento = persona.FechaNacimiento;
res.Usuario = persona.Usuario;
res.Borrado = persona.Borrado;
res.Correos = CorreoDal.GetList(res.IdPersona);
res.Telefonos = TelefonoDal.GetList(res.IdPersona);
res.Direcciones = DireccionDal.GetList(res.IdPersona);
}
catch (Exception ex)
{
Methods.GenerateLogsRelease("PersonalDal", "Obtener", string.Format("{0} {1} Error: {2}", DateTime.Now.ToShortDateString(), DateTime.Now.ToShortTimeString(), ex.Message));
throw ex;
}
finally
{
cmd.Connection.Close();
}
return(res);
}
/// <summary>
/// Obtiene un Cliente de la base de datos
/// </summary>
/// <param name="ci"></param>
/// <returns></returns>
public static Cliente GetCI(string ci)
{
Cliente res = new Cliente();
SqlCommand cmd = null;
SqlDataReader dr = null;
string query = @"Select Persona.*,
Cliente.Nit, Cliente.Razon, Cliente.FechaRegistro
FROM Persona
INNER JOIN Cliente ON Persona.IdPersona=Cliente.IdPersona
WHERE Persona.Ci=@ci";
try
{
cmd = Methods.CreateBasicCommand(query);
cmd.Parameters.AddWithValue("@ci", ci);
dr = Methods.ExecuteDataReaderCommand(cmd);
while (dr.Read())
{
res = new Cliente()
{
IdPersona = dr.GetInt32(0),
Ci = dr.GetString(1),
Nombre = dr.GetString(2),
PrimerApellido = dr.GetString(3),
SegundoApellido = dr.GetString(4),
Sexo = SexoDal.Get(dr.GetInt32(5)),
FechaNacimiento = dr.GetDateTime(6),
Usuario = UsuarioDal.Get(dr.GetInt32(7)),
Borrado = dr.GetBoolean(8),
Direcciones = DireccionDal.GetList(dr.GetInt32(0)),
Telefonos = TelefonoDal.GetList(dr.GetInt32(0)),
Correos = CorreoDal.GetList(dr.GetInt32(0)),
Nit = dr.GetString(9),
Razon = dr.GetString(10),
FechaRegistro = dr.GetDateTime(11)
};
}
}
catch (Exception ex)
{
Methods.GenerateLogsRelease("ClienteDal", "Obtenet(Get)", string.Format("{0} {1} Error: {2}", DateTime.Now.ToShortDateString(), DateTime.Now.ToShortTimeString(), ex.Message));
throw ex;
}
finally
{
cmd.Connection.Close();
}
return(res);
}
/// <summary>
/// Obtiene la informacion de una persona
/// </summary>
/// <param name="id">identificador de persona </param>
/// <returns></returns>
public static Persona Get(int id)
{
Persona res = new Persona();
SqlCommand cmd = null;
SqlDataReader dr = null;
string query = "SELECT * FROM Persona WHERE IdPersona = @id";
try
{
cmd = Methods.CreateBasicCommand(query);
cmd.Parameters.AddWithValue("@id", id);
dr = Methods.ExecuteDataReaderCommand(cmd);
while (dr.Read())
{
res = new Persona()
{
IdPersona = dr.GetInt32(0),
Ci = dr.GetString(1),
Nombre = dr.GetString(2),
PrimerApellido = dr.GetString(3),
SegundoApellido = dr.GetString(4),
Sexo = SexoDal.Get(dr.GetInt32(5)),
FechaNacimiento = dr.GetDateTime(6),
Correos = CorreoDal.GetList(id),
Usuario = UsuarioDal.Get(dr.GetInt32(7)),
Direcciones = DireccionDal.GetList(id),
Telefonos = TelefonoDal.GetList(id),
Borrado = dr.GetBoolean(8)
};
}
}
catch (Exception ex)
{
Methods.GenerateLogsRelease("PersonaDal", "Obtener", string.Format("{0} {1} Error: {2}", DateTime.Now.ToShortDateString(), DateTime.Now.ToShortTimeString(), ex.Message));
throw ex;
}
finally
{
cmd.Connection.Close();
}
return(res);
}