/// <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); }