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