/// <summary>
/// Los contactos no mantiene relaciones por lo que se utiliza directamente un drop en ellos
/// </summary>
public void DropContacto(Modelo.Contacto c, int Lista)
{
SqlConnection connection = null;
try
{
connection = GetConnection();
connection.Open();
SqlCommand cmd = connection.CreateCommand();
string insertContacto = "DELETE FROM DesgloseContactos WHERE Persona = @IdPersona " +
"AND ListaContactos = @IdLista";
cmd.CommandText = insertContacto;
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@IdPersona", c.IdPersona);
cmd.Parameters.AddWithValue("@IdLista", Lista);
cmd.ExecuteNonQuery();
connection.Close();
}
catch (Exception ex)
{
if (connection != null)
{
connection.Close();
}
throw ex;
}
}
/// <summary>
/// Actualiza los datos de un contacto en la base de datos
/// </summary>
/// <param name="c">Contacto a actualizar con los datos nuevos que tendrá</param>
public void UpdateContacto(Modelo.Contacto c)
{
SqlConnection connection = null;
SqlTransaction transaction = null;
try
{
connection = GetConnection();
connection.Open();
SqlCommand cmd = connection.CreateCommand();
string insertContacto = "UPDATE Personas SET " +
"Nombre = @Nombre," +
"ApellidoPaterno = @ApellidoPaterno, " +
"ApellidoMaterno = @ApellidoMaterno, " +
"Telefono = @Telefono, " +
"CorreoElectronico = @CorreoElectronico, " +
"Calle = @Calle, " +
"Numero = @Numero, " +
"Colonia = @Colonia " +
"WHERE IdPersona = @IdPersona";
cmd.CommandText = insertContacto;
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@Nombre", c.Nombre);
cmd.Parameters.AddWithValue("@ApellidoPaterno", c.ApellidoPaterno);
cmd.Parameters.AddWithValue("@ApellidoMaterno", c.ApellidoMaterno);
cmd.Parameters.AddWithValue("@Telefono", c.Telefono);
cmd.Parameters.AddWithValue("@CorreoElectronico", c.CorreoElectronico);
cmd.Parameters.AddWithValue("@Calle", c.Calle);
cmd.Parameters.AddWithValue("@Numero", c.Numero);
cmd.Parameters.AddWithValue("@Colonia", c.Colonia);
cmd.Parameters.AddWithValue("@IdPersona", c.IdPersona);
Utils.ClearNullParameterValues(cmd.Parameters);
cmd.ExecuteNonQuery();
connection.Close();
}
catch (Exception ex)
{
if (transaction != null)
{
transaction.Rollback();
}
if (connection != null)
{
connection.Close();
}
throw ex;
}
}
/// <summary>
/// Inserta un contacto en la lista especificada usando un comando ya otorgado
/// </summary>
/// <param name="c">Objeto de la clase <see cref="Contacto"/> a insertar</param>
/// <param name="Lista">Lista de usuarios destino</param>
/// <param name="cmd">Comando <see cref="SqlCommand"/> con transaccion y conexion
/// ya configuradas</param>
/// <remarks>
/// Se utiliza este metodo principalmente para la primera insercion de un proveedor o
/// cliente. Esto permitirá mantener todo el proceso dentro de la misma transaccion
/// </remarks>
public static void InsertarContacto(Modelo.Contacto c, int Lista, SqlCommand cmd)
{
string insertContacto = "INSERT INTO Personas VALUES(" +
"@Nombre," +
"@ApellidoPaterno," +
"@ApellidoMaterno," +
"@Telefono," +
"@CorreoElectronico," +
"@Calle," +
"@Numero," +
"@Colonia" +
")" + Environment.NewLine +
"SELECT CAST(SCOPE_IDENTITY() as int)";
cmd.CommandText = insertContacto;
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@Nombre", c.Nombre);
cmd.Parameters.AddWithValue("@ApellidoPaterno", c.ApellidoPaterno);
cmd.Parameters.AddWithValue("@ApellidoMaterno", c.ApellidoMaterno);
cmd.Parameters.AddWithValue("@Telefono", c.Telefono);
cmd.Parameters.AddWithValue("@CorreoElectronico", c.CorreoElectronico);
cmd.Parameters.AddWithValue("@Calle", c.Calle);
cmd.Parameters.AddWithValue("@Numero", c.Numero);
cmd.Parameters.AddWithValue("@Colonia", c.Colonia);
Utils.ClearNullParameterValues(cmd.Parameters);
int idPersona = (int)cmd.ExecuteScalar();
insertContacto = "INSERT INTO Contactos VALUES (@IdPersona)" + Environment.NewLine +
"SELECT CAST(SCOPE_IDENTITY() as int)";
cmd.CommandText = insertContacto;
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@IdPersona", idPersona);
int idContacto = (int)cmd.ExecuteScalar();
insertContacto = "INSERT INTO DesgloseContactos VALUES(" +
"@IdLista," +
"@IdContacto" +
")";
cmd.CommandText = insertContacto;
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@IdLista", Lista);
cmd.Parameters.AddWithValue("@IdContacto", idContacto);
Utils.ClearNullParameterValues(cmd.Parameters);
cmd.ExecuteNonQuery();
}
public Cliente GetById(int idCliente)
{
Cliente c = null;
SqlConnection connection = null;
SqlCommand cmd = null;
SqlDataReader reader = null;
try
{
connection = GetConnection();
connection.Open();
cmd = connection.CreateCommand();
cmd.CommandText = "SELECT * FROM [Ventas].[Clientes] WHERE IdCliente = @IdCliente";
cmd.Parameters.AddWithValue("@IdCliente", idCliente);
reader = cmd.ExecuteReader();
if (reader.Read())
{
c = new Cliente();
c.IdCliente = reader.GetInt32(0);
c.Nombre = reader.GetString(1);
c.Telefono = reader.GetString(2);
c.CorreoElectronico = reader.GetString(3);
c.Calle = reader.GetString(4);
c.Numero = reader.GetString(5);
c.Colonia = reader.GetString(6);
if (reader.GetInt32(7) == 1)
{
c.RegimenFiscal = RegimenesFiscales.PersonaMoral;
}
else if (reader.GetInt32(7) == 2)
{
c.RegimenFiscal = RegimenesFiscales.PersonaFisica;
}
c.RFC = reader.GetString(8);
c.Estatus = (bool)reader[9];
c.ListaContactos = new Modelo.ListaContactos
{
IdListaContactos = reader.GetInt32(10)
};
ControladorContacto contContacto = new ControladorContacto();
DataTable dt = contContacto.GetAll(c.ListaContactos.IdListaContactos);
foreach (DataRow row in dt.Rows)
{
Modelo.Contacto contacto = new Modelo.Contacto()
{
IdContacto = (int)row["IdContacto"],
IdPersona = (int)row["IdPersona"],
Nombre = row["Nombre"] != DBNull.Value ? (string)row["Nombre"] : null,
ApellidoPaterno = row["ApellidoPaterno"] != DBNull.Value ? (string)row["ApellidoPaterno"] : null,
ApellidoMaterno = row["ApellidoMaterno"] != DBNull.Value ? (string)row["ApellidoMaterno"] : null,
Telefono = row["Telefono"] != DBNull.Value ? (string)row["Telefono"] : null,
CorreoElectronico = row["CorreoElectronico"] != DBNull.Value ? (string)row["CorreoElectronico"] : null,
Calle = row["Calle"] != DBNull.Value ? (string)row["Calle"] : null,
Numero = row["Numero"] != DBNull.Value ? (string)row["Numero"] : null,
Colonia = row["Colonia"] != DBNull.Value ? (string)row["Colonia"] : null
};
c.ListaContactos.Add(contacto);
}
}
return(c);
}
catch (Exception ex)
{
if (connection != null)
{
connection.Close();
connection.Dispose();
}
throw ex;
}
}
public Proveedor GetById(int idProveedor)
{
Proveedor p = null;
SqlConnection connection = null;
SqlCommand cmd = null;
SqlDataReader reader = null;
try
{
connection = GetConnection();
connection.Open();
cmd = connection.CreateCommand();
cmd.CommandText = "SELECT * FROM [Compras].[Proveedores] WHERE IdProveedor = @IdProveedor";
cmd.Parameters.AddWithValue("@IdProveedor", idProveedor);
reader = cmd.ExecuteReader();
if (reader.Read())
{
p = new Proveedor()
{
IdProveedor = (int)reader["IdProveedor"],
Nombre = (string)reader["Nombre"],
Telefono = (string)reader["Telefono"],
CorreoElectronico = (string)reader["CorreoElectronico"],
Calle = (string)reader["Calle"],
Numero = (string)reader["Numero"],
Colonia = (string)reader["Colonia"],
Contactos = new Modelo.ListaContactos
{
IdListaContactos = (int)reader["ListaContactos"]
}
};
ControladorContacto contContacto = new ControladorContacto();
DataTable dt = contContacto.GetAll(p.Contactos.IdListaContactos);
foreach (DataRow row in dt.Rows)
{
Modelo.Contacto contacto = new Modelo.Contacto()
{
IdContacto = (int)row["IdContacto"],
IdPersona = (int)row["IdPersona"],
Nombre = row["Nombre"] != DBNull.Value ? (string)row["Nombre"] : null,
ApellidoPaterno = row["ApellidoPaterno"] != DBNull.Value ? (string)row["ApellidoPaterno"] : null,
ApellidoMaterno = row["ApellidoMaterno"] != DBNull.Value ? (string)row["ApellidoMaterno"] : null,
Telefono = row["Telefono"] != DBNull.Value ? (string)row["Telefono"] : null,
CorreoElectronico = row["CorreoElectronico"] != DBNull.Value ? (string)row["CorreoElectronico"] : null,
Calle = row["Calle"] != DBNull.Value ? (string)row["Calle"] : null,
Numero = row["Numero"] != DBNull.Value ? (string)row["Numero"] : null,
Colonia = row["Colonia"] != DBNull.Value ? (string)row["Colonia"] : null
};
p.Contactos.Add(contacto);
}
}
return(p);
}
catch (Exception ex)
{
if (connection != null)
{
connection.Close();
connection.Dispose();
}
throw ex;
}
}
