public static bool UpdateTransaction(CashboxTransaction originalTransaction, DateTime date, string description, decimal amount) { string query = "START TRANSACTION; "; query += "SET @box_id = (SELECT cashbox_id FROM transacction WHERE transacction_id = " + originalTransaction.ID + "); "; query += string.Format("UPDATE cashbox SET balance = balance - {0} WHERE cashbox_id = @box_id; ", originalTransaction.Amount); query += "UPDATE transacction SET "; query += "transacction_date = " + PonerComillas(date.ToString("yyyy-MM-dd HH:mm:ss")) + ", "; query += "description = " + PonerComillas(description) + ", "; query += "amount = " + amount + " "; query += "WHERE transacction_id = " + originalTransaction.ID + "; "; query += string.Format("UPDATE cashbox SET balance = balance + {0} WHERE cashbox_id = @box_id; ", amount); query += "COMMIT"; return(ModificarTabla(query)); }
/// <summary> /// Este metodo consulta las transacciones segun la ruta de categorias pasada como parametros /// </summary> /// <param name="categories"></param> /// <returns></returns> public static List <CashboxTransaction> RecoverTransaction(List <Category> categories, DateTime since, DateTime until) { until = until.AddHours(23).AddMinutes(59).AddSeconds(59); List <CashboxTransaction> result = new List <CashboxTransaction>(); string query = ""; if (categories.Count == 1) { query = WriteQueryTableClass1(categories[0].ID, since, until); } else if (categories.Count == 2) { string table1 = WriteQueryTableClass1(categories[0].ID, since, until); string table2 = WriteQueryTableClass1(categories[1].ID, since, until); query = WriteQueryTableClass2(table1, table2); } else { string table1 = WriteQueryTableClass1(categories[0].ID, since, until); string table2 = WriteQueryTableClass1(categories[1].ID, since, until); query = WriteQueryTableClass2(table1, table2); for (int index = 2; index < categories.Count; index++) { table2 = WriteQueryTableClass1(categories[index].ID, since, until); query = WriteQueryTableClass2(query, table2); } } if (HacerConsulta(query)) { while (reader.Read()) { int id = reader.GetInt32("transacction_id"); DateTime date = reader.GetDateTime("transacction_date"); string description = reader.GetString("description"); decimal amount = reader.GetDecimal("amount"); CashboxTransaction t = new CashboxTransaction(id, date, description, amount, false); //Si se busca por categoría no encontrará ninguna con transferencia igual a true result.Add(t); } } return(result); }
public static CashboxTransaction RecuperarUltimaTransaccion(int user_id, int cashbox_id) { CashboxTransaction transaction = null; string query = "SELECT transacction_id, transacction_date, description, amount, transfer FROM transacction WHERE "; query += string.Format("user_id = {0} AND cashbox_id = {1} ", user_id, cashbox_id); query += "ORDER BY transacction_id DESC LIMIT 1"; if (HacerConsulta(query)) { reader.Read(); int id = reader.GetInt32("transacction_id"); DateTime tDate = reader.GetDateTime("transacction_date"); string description = reader.GetString("description"); decimal amount = reader.GetDecimal("amount"); bool isATransfer = reader.GetBoolean("transfer"); transaction = new CashboxTransaction(id, tDate, description, amount, isATransfer); } return(transaction); }