public void UpdateProveedor(Proveedor p)
{
SqlConnection connection = null;
SqlCommand cmd = null;
try
{
connection = GetConnection();
connection.Open();
cmd = connection.CreateCommand();
string InsertProveedor = "UPDATE [Compras].[Proveedores] SET " + Environment.NewLine +
"Nombre = @Nombre, " + Environment.NewLine +
"Telefono = @Telefono, " + Environment.NewLine +
"CorreoElectronico = @CorreoElectronico, " + Environment.NewLine +
"Calle = @Calle, " + Environment.NewLine +
"Numero = @Numero, " + Environment.NewLine +
"Colonia = @Colonia " + Environment.NewLine +
"WHERE IdProveedor = @IdProveedor";
cmd.CommandText = InsertProveedor;
cmd.Parameters.AddWithValue("@Nombre", p.Nombre);
cmd.Parameters.AddWithValue("@Telefono", p.Telefono);
cmd.Parameters.AddWithValue("@CorreoElectronico", p.CorreoElectronico);
cmd.Parameters.AddWithValue("@Calle", p.Calle);
cmd.Parameters.AddWithValue("@Numero", p.Numero);
cmd.Parameters.AddWithValue("@Colonia", p.Colonia);
cmd.Parameters.AddWithValue("@IdProveedor", p.IdProveedor);
cmd.ExecuteNonQuery();
if (p.Contactos != null && p.Contactos.Count > 0)
{
foreach (Modelo.Contacto c in from con in p.Contactos where con.IdContacto == 0 select con)
{
ControladorContacto.InsertarContacto(c, p.Contactos.IdListaContactos, cmd);
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (connection != null)
{
connection.Close();
}
}
}
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 void InsertarCliente(Cliente c)
{
SqlConnection connection = null;
SqlTransaction transaction = null;
SqlCommand cmd = null;
try
{
connection = GetConnection();
connection.Open();
cmd = connection.CreateCommand();
transaction = connection.BeginTransaction();
cmd.Transaction = transaction;
cmd.CommandText = Utils.InsertListaContactos;
int idLista = (int)cmd.ExecuteScalar();
string InsertCliente = "INSERT INTO [Ventas].[Clientes](" + Environment.NewLine +
" Nombre," + Environment.NewLine +
" Telefono," + Environment.NewLine +
" CorreoElectronico," + Environment.NewLine +
" Calle," + Environment.NewLine +
" Numero," + Environment.NewLine +
" Colonia," + Environment.NewLine +
" RegimenFiscal," + Environment.NewLine +
" RFC," + Environment.NewLine +
" Estatus," + Environment.NewLine +
" ListaContactos" + Environment.NewLine +
")VALUES(" + Environment.NewLine +
" @Nombre," + Environment.NewLine +
" @Telefono," + Environment.NewLine +
" @CorreoElectronico," + Environment.NewLine +
" @Calle," + Environment.NewLine +
" @Numero," + Environment.NewLine +
" @Colonia," + Environment.NewLine +
" @RegimenFiscal," + Environment.NewLine +
" @RFC," + Environment.NewLine +
" 1," + Environment.NewLine +
" @ListaContactos" + Environment.NewLine +
")" + Environment.NewLine +
"SELECT CAST(SCOPE_IDENTITY() as int)";
cmd.CommandText = InsertCliente;
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@Nombre", c.Nombre);
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("@RegimenFiscal", c.RegimenFiscal);
cmd.Parameters.AddWithValue("@RFC", c.RFC);
cmd.Parameters.AddWithValue("@ListaContactos", idLista);
int IdCliente = (int)cmd.ExecuteScalar();
if (c.ListaContactos != null && c.ListaContactos.Count > 0)
{
foreach (Modelo.Contacto contacto in c.ListaContactos)
{
ControladorContacto.InsertarContacto(contacto, idLista, cmd);
}
}
transaction.Commit();
}
catch (Exception ex)
{
if (transaction != null)
{
transaction.Rollback();
}
if (connection != null)
{
connection.Close();
}
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;
}
}
public void InsertarProveedor(Proveedor p)
{
SqlConnection connection = null;
SqlTransaction transaction = null;
SqlCommand cmd = null;
try
{
connection = GetConnection();
connection.Open();
cmd = connection.CreateCommand();
transaction = connection.BeginTransaction();
cmd.Transaction = transaction;
cmd.CommandText = Utils.InsertListaContactos;
int idLista = (int)cmd.ExecuteScalar();
string InsertProveedor = "INSERT INTO [Compras].[Proveedores](" + Environment.NewLine +
" Nombre," + Environment.NewLine +
" Telefono," + Environment.NewLine +
" CorreoElectronico," + Environment.NewLine +
" Calle," + Environment.NewLine +
" Numero," + Environment.NewLine +
" Colonia," + Environment.NewLine +
" Estatus," + Environment.NewLine +
" ListaContactos" + Environment.NewLine +
")VALUES(" + Environment.NewLine +
" @Nombre," + Environment.NewLine +
" @Telefono," + Environment.NewLine +
" @CorreoElectronico," + Environment.NewLine +
" @Calle," + Environment.NewLine +
" @Numero," + Environment.NewLine +
" @Colonia," + Environment.NewLine +
" 1," + Environment.NewLine +
" @ListaContactos" + Environment.NewLine +
")" + Environment.NewLine;
cmd.CommandText = InsertProveedor;
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@Nombre", p.Nombre);
cmd.Parameters.AddWithValue("@Telefono", p.Telefono);
cmd.Parameters.AddWithValue("@CorreoElectronico", p.CorreoElectronico);
cmd.Parameters.AddWithValue("@Calle", p.Calle);
cmd.Parameters.AddWithValue("@Numero", p.Numero);
cmd.Parameters.AddWithValue("@Colonia", p.Colonia);
cmd.Parameters.AddWithValue("@ListaContactos", idLista);
cmd.ExecuteNonQuery();
if (p.Contactos != null && p.Contactos.Count > 0)
{
foreach (Modelo.Contacto c in p.Contactos)
{
ControladorContacto.InsertarContacto(c, idLista, cmd);
}
}
transaction.Commit();
}
catch (Exception ex)
{
if (transaction != null)
{
transaction.Rollback();
}
if (connection != null)
{
connection.Close();
}
throw ex;
}
}
