/// <summary>
/// Creates a customer with an associated registration invoice
/// </summary>
/// <param name="customer">Customer</param>
/// <param name="factura">Invoice</param>
/// <param name="cargo">Charge</param>
/// <returns>True if operation was succesful, false otherwise</returns>
public static bool CreateCustomerWithInitialFinancialInformation(Customer customer, Factura factura, Cargo cargo)
{
var dbCon = DBConnection.Instance();
MySqlTransaction transaction = null;
string queryCustomer = "INSERT INTO clientes(cedula, nombre, apellido, telefono, celular, correo, direccion, barrio, mac, precinto, nodo, saldo, descuento, tv, internet, megas, fechaAFiliacionTV, fechaAfiliacionInternet, estadoTV, estadoInternet) " +
"VALUES(@cedula, @nombre,@apellido, @telefono,@celular,@correo,@direccion,@barrio,@mac,@precinto,@nodo,@saldo,@descuento,@tv, @internet,@megas,@fechaAfiliacionTV,@fechaAfiliacionInternet,@estadoTV,@estadoInternet)";
string queryFactura = "INSERT INTO facturas(NumeroFactura, fecha, fechaLimite, mesCargado, clientes_cedula) VALUES(@NumeroFactura, @fecha, @fechaLimite, @mesCargado, @clientes_cedula)";
string queryCargos = "INSERT INTO cargos(concepto, valor, pagado, facturas_NumeroFactura) VALUES(@concepto, @valor, @pagado, @facturas_NumeroFactura)";
try
{
if (dbCon.IsConnect())
{
MySqlCommand command = dbCon.Connection.CreateCommand();
transaction = dbCon.Connection.BeginTransaction();
command.Connection = dbCon.Connection;
command.Transaction = transaction;
command.CommandText = queryCustomer;
command.Parameters.AddWithValue("@cedula", customer.Cedula);
command.Parameters.AddWithValue("@nombre", customer.Nombre);
command.Parameters.AddWithValue("@apellido", customer.Apellido);
command.Parameters.AddWithValue("@telefono", customer.Telefono);
command.Parameters.AddWithValue("@celular", customer.Celular);
command.Parameters.AddWithValue("@correo", customer.Correo);
command.Parameters.AddWithValue("@direccion", customer.Direccion);
command.Parameters.AddWithValue("@barrio", customer.Barrio);
command.Parameters.AddWithValue("@mac", customer.MAC);
command.Parameters.AddWithValue("@precinto", customer.Precinto);
command.Parameters.AddWithValue("@nodo", customer.Nodo);
command.Parameters.AddWithValue("@saldo", customer.Saldo);
command.Parameters.AddWithValue("@descuento", customer.Descuento);
command.Parameters.AddWithValue("@tv", customer.HasTV);
command.Parameters.AddWithValue("@internet", customer.HasInternet);
command.Parameters.AddWithValue("@megas", customer.Megas);
command.Parameters.AddWithValue("@fechaAfiliacionTV", customer.FechaAfiliacionTV);
command.Parameters.AddWithValue("@fechaAfiliacionInternet", customer.FechaAfiliacionInternet);
command.Parameters.AddWithValue("@estadoTV", customer.EstadoTV.ToString());
command.Parameters.AddWithValue("@estadoInternet", customer.EstadoInternet.ToString());
command.ExecuteNonQuery();
command.CommandText = queryFactura;
command.Parameters.AddWithValue("@NumeroFactura", factura.NumeroFactura);
command.Parameters.AddWithValue("@fecha", factura.Fecha);
command.Parameters.AddWithValue("@fechaLimite", factura.FechaLimite);
command.Parameters.AddWithValue("@mesCargado", factura.MesCargado);
command.Parameters.AddWithValue("@clientes_cedula", customer.Cedula);
command.ExecuteNonQuery();
command.CommandText = queryCargos;
command.Parameters.AddWithValue("@concepto", cargo.Concepto.ToString());
command.Parameters.AddWithValue("@valor", cargo.Valor);
command.Parameters.AddWithValue("@pagado", cargo.Pagado);
command.Parameters.AddWithValue("@facturas_NumeroFactura", factura.NumeroFactura);
command.ExecuteNonQuery();
transaction.Commit();
return(true);
}
} catch (MySqlException e)
{
try
{
transaction.Rollback();
} catch (Exception)
{
throw;
}
throw;
}
return(false);
}
/// <summary>
/// Creates invoices on database
/// </summary>
/// <param name="items">A list with the invoices and its corresponding customer</param>
/// <returns>True if operation was succesful, and the number of invoices that were inserted on the database</returns>
public static (bool, int) CreateInvoicesWithCharges(List <Tuple <Factura, Customer> > items)
{
var dbCon = DBConnection.Instance();
MySqlTransaction transaction = null;
int n = 0;
string query = "INSERT INTO facturas(NumeroFactura, fecha, fechaLimite, mesCargado, clientes_cedula) VALUES (@NumeroFactura, @fecha, @fechaLimite, @MesCargado, @clientes_cedula)";
string query2 = "INSERT INTO cargos(concepto, valor, pagado, facturas_numeroFactura) VALUES (@concepto, @valor, @pagado, @facturas_NumeroFactura)";
string query3 = "UPDATE clientes SET saldo = @newSaldo WHERE cedula = @cedula";
try
{
if (dbCon.IsConnect())
{
MySqlCommand command = dbCon.Connection.CreateCommand();
transaction = dbCon.Connection.BeginTransaction();
command.Connection = dbCon.Connection;
command.Transaction = transaction;
foreach (Tuple <Factura, Customer> item in items)
{
if (CheckForExistenceFactura(item.Item1.NumeroFactura, dbCon.Connection))
{
continue;
}
command.CommandText = query;
command.Parameters.Clear();
command.Parameters.AddWithValue("@NumeroFactura", item.Item1.NumeroFactura);
command.Parameters.AddWithValue("@fecha", item.Item1.Fecha);
command.Parameters.AddWithValue("@fechaLimite", item.Item1.FechaLimite);
command.Parameters.AddWithValue("@MesCargado", item.Item1.MesCargado.ToString());
command.Parameters.AddWithValue("@clientes_cedula", item.Item1._Customer_cedula);
command.ExecuteNonQuery();
command.CommandText = query3;
command.Parameters.AddWithValue("@newSaldo", item.Item2.Saldo);
command.Parameters.AddWithValue("@cedula", item.Item1._Customer_cedula);
command.ExecuteNonQuery();
foreach (Cargo cargo in item.Item1.cargos)
{
if (cargo.repeat)
{
continue;
}
command.CommandText = query2;
command.Parameters.Clear();
command.Parameters.AddWithValue("@concepto", cargo.Concepto.ToString());
command.Parameters.AddWithValue("@valor", cargo.Valor);
command.Parameters.AddWithValue("@pagado", cargo.Pagado);
command.Parameters.AddWithValue("@facturas_NumeroFactura", item.Item1.NumeroFactura);
command.ExecuteNonQuery();
}
++n;
}
transaction.Commit();
return(true, n);
}
} catch (MySqlException)
{
try
{
transaction.Rollback();
}
catch (Exception)
{
throw;
}
throw;
}
return(false, 0);
}
/// <summary>
/// Executes a payment transaction
/// </summary>
/// <param name="cargosActualizados">The values of the updated charges</param>
/// <param name="cedula_cliente">Customer ID</param>
/// <param name="total">The value to be paid</param>
/// <param name="discount">The discount to be applied</param>
/// <param name="_cargo">The charge where the discount should apply</param>
/// <returns>True if transaction was succesful, False otherwise</returns>
public static bool Pay(List <Cargo> cargosActualizados, string cedula_cliente, int total, int discount = 0, Cargo _cargo = null)
{
var dbCon = DBConnection.Instance();
MySqlTransaction transaction = null;
string query = "UPDATE cargos SET pagado = @newPagado WHERE ID = @ID";
string query2 = "UPDATE clientes SET saldo = @newSaldo WHERE cedula = @cedula";
string queryPrevious = "SELECT saldo FROM clientes WHERE cedula = @cedula";
string descuentoquery = "UPDATE cargos SET valor = @newValor WHERE ID = @ID";
try
{
if (dbCon.IsConnect())
{
MySqlCommand command = dbCon.Connection.CreateCommand();
transaction = dbCon.Connection.BeginTransaction();
command.Connection = dbCon.Connection;
command.Transaction = transaction;
command.CommandText = queryPrevious;
command.Parameters.AddWithValue("@cedula", cedula_cliente);
var reader = command.ExecuteReader();
int saldo = 0;
while (reader.Read())
{
saldo = reader.GetInt32("saldo");
}
reader.Close();
int newSaldo = saldo - total;
newSaldo -= discount;
if (newSaldo < 0)
{
throw (new Exception("Error, saldo invalido"));
}
command.CommandText = query2;
command.Parameters.Clear();
command.Parameters.AddWithValue("@newSaldo", newSaldo);
command.Parameters.AddWithValue("@cedula", cedula_cliente);
command.ExecuteNonQuery();
if (!(_cargo is null))
{
command.CommandText = descuentoquery;
command.Parameters.Clear();
command.Parameters.AddWithValue("@newValor", _cargo.Valor);
command.Parameters.AddWithValue("@ID", _cargo.ID);
command.ExecuteNonQuery();
}
foreach (Cargo cargo in cargosActualizados)
{
command.CommandText = query;
command.Parameters.Clear();
command.Parameters.AddWithValue("@newPagado", cargo.Pagado);
command.Parameters.AddWithValue("@ID", cargo.ID);
command.ExecuteNonQuery();
}
transaction.Commit();
return(true);
}
} catch (MySqlException)
{
try
{
transaction.Rollback();
}
catch (Exception)
{
throw;
}
throw;
}
return(false);
}
/// <summary>
/// Returns the charges that correspond to the passed year, month and node.
/// </summary>
/// <param name="year">Year</param>
/// <param name="mes">Month</param>
/// <param name="nodo">Node</param>
/// <returns>A list where each entry is a charge</returns>
public static List <Cargo> GetCargosBasedOn(int year, int mes = 0, int?nodo = null)
{
List <Cargo> cargos = new List <Cargo>();
var dbCon = DBConnection.Instance();
string query = "SELECT cargos.Concepto, cargos.valor,cargos.pagado FROM cargos, facturas, clientes WHERE facturas.NumeroFactura = cargos.facturas_NumeroFactura and facturas.clientes_cedula = clientes.cedula and month(facturas.fecha) = @mes" +
" and year(facturas.fecha) = @year and nodo = @nodo";
string query2 = "SELECT cargos.Concepto, cargos.valor,cargos.pagado FROM cargos, facturas, clientes WHERE facturas.NumeroFactura = cargos.facturas_NumeroFactura and facturas.clientes_cedula = clientes.cedula and month(facturas.fecha) = @mes" +
" and year(facturas.fecha) = @year";
string query3 = "SELECT cargos.Concepto, cargos.valor,cargos.pagado FROM cargos, facturas, clientes WHERE facturas.NumeroFactura = cargos.facturas_NumeroFactura and facturas.clientes_cedula = clientes.cedula" +
" and year(facturas.fecha) = @year and nodo = @nodo";
string query4 = "SELECT cargos.Concepto, cargos.valor,cargos.pagado FROM cargos, facturas, clientes WHERE facturas.NumeroFactura = cargos.facturas_NumeroFactura and facturas.clientes_cedula = clientes.cedula" +
" and year(facturas.fecha) = @year";
try
{
if (dbCon.IsConnect())
{
var cmd = dbCon.Connection.CreateCommand();
cmd.Connection = dbCon.Connection;
if (mes == 0 && nodo is null)
{
cmd.CommandText = query4;
cmd.Parameters.AddWithValue("@year", year);
}
else if (mes == 0)
{
cmd.CommandText = query3;
cmd.Parameters.AddWithValue("@year", year);
cmd.Parameters.AddWithValue("@nodo", nodo);
}
else if (nodo is null)
{
cmd.CommandText = query2;
cmd.Parameters.AddWithValue("@year", year);
cmd.Parameters.AddWithValue("@mes", mes);
}
else
{
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@year", year);
cmd.Parameters.AddWithValue("@mes", mes);
cmd.Parameters.AddWithValue("@nodo", nodo);
}
var reader = cmd.ExecuteReader();
while (reader.Read())
{
Enum.TryParse(reader.GetString("Concepto"), out Concepto concepto);
int valor = reader.GetInt32("valor");
int pagado = reader.GetInt32("pagado");
Cargo cargo = new Cargo(valor, pagado, concepto);
cargos.Add(cargo);
}
reader.Close();
}
} catch (MySqlException)
{
throw;
}
return(cargos);
}
/// <summary>
/// Returns the customer whose id corresponds to the one passed in
/// </summary>
/// <param name="cedula_cliente">Customer id</param>
/// <returns>A object customer if one was found</returns>
public static Customer GetCustomer(string cedula_cliente)
{
Customer customer = null;
var dbCon = DBConnection.Instance();
string query = "SELECT * FROM clientes WHERE cedula = @cedula";
try
{
if (dbCon.IsConnect())
{
var cmd = new MySqlCommand(query, dbCon.Connection);
cmd.Parameters.AddWithValue("@cedula", cedula_cliente);
var reader = cmd.ExecuteReader();
while (reader.Read())
{
string cedula = reader.GetString("cedula");
string nombre = reader.GetString("nombre");
string apellido = reader.GetString("apellido");
string telefono = reader.IsDBNull(3) ? null : reader.GetString("telefono");
string celular = reader.IsDBNull(4) ? null : reader.GetString("celular");
string correo = reader.IsDBNull(5) ? null : reader.GetString("correo");
string direccion = reader.GetString("direccion");
string barrio = reader.GetString("barrio");
string mac = reader.IsDBNull(8) ? null : reader.GetString("mac");
string precinto = reader.IsDBNull(9) ? null : reader.GetString("precinto");
int nodo = reader.GetInt32("nodo");
int megas = reader.GetInt32("megas");
int descuento = reader.GetInt32("descuento");
bool tv = reader.GetBoolean("tv");
bool internet = reader.GetBoolean("internet");
DateTime?fechaAfiliacionTV;
DateTime?fechaAfiliacionInternet;
if (reader.IsDBNull(15))
{
fechaAfiliacionTV = null;
}
else
{
fechaAfiliacionTV = reader.GetDateTime("fechaAfiliacionTV");
}
if (reader.IsDBNull(16))
{
fechaAfiliacionInternet = null;
}
else
{
fechaAfiliacionInternet = reader.GetDateTime("fechaAfiliacionInternet");
}
Enum.TryParse(reader.GetString("estadoTV"), out Estado estadoTV);
Enum.TryParse(reader.GetString("estadoInternet"), out Estado estadoInternet);
int saldo = reader.GetInt32("saldo");
customer = new Customer(cedula, nombre, apellido, telefono, celular, correo, direccion, barrio, mac, precinto, nodo, megas, saldo, descuento, tv, internet, fechaAfiliacionTV, fechaAfiliacionInternet,
estadoTV, estadoInternet);
}
reader.Close();
}
}
catch (MySqlException)
{
throw;
}
return(customer);
}
