// TRANSACTION RELATED INTERFACE
public static void addTransaction(Transaction transaction)
{
//openConnection();
// TODO: adjust price of product
MySqlCommand command = l_DBConn.CreateCommand();
String query = null;
bool isNew = true;
if (transaction.getId() != null)
{
query = "SELECT * FROM transaction WHERE id='" + transaction.getId() + "'";
command.CommandText = query;
MySqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
isNew = false;
}
reader.Close();
}
query = "REPLACE transaction(id, money_receive,money_change,date,total_price) VALUES('" +
transaction.getId() + "','" + transaction.getMoneyReceive() + "','" + transaction.getMoneyChange() +
"','" + transaction.getDate() + "','" + transaction.getTotalPrice() + "')";
command.CommandText = query;
command.ExecuteNonQuery();
var transactionId = command.LastInsertedId;
// add Transaction-Product relation if this is new transaction
if (isNew)
{
Dictionary<string, Dictionary<int, double>> shopping_bag = transaction.getShoppingBag();
for (int i = 0; i < shopping_bag.Count; i++)
{
string productId = shopping_bag.ElementAt(i).Key;
Dictionary<int, double> amountPrice = shopping_bag.ElementAt(i).Value;
query = "INSERT INTO product_transaction_relation VALUES('" +
productId + "','" + transactionId +
"','" + amountPrice.ElementAt(0).Key +
"','" + amountPrice.ElementAt(0).Value + "')";
command.CommandText = query;
command.ExecuteNonQuery();
// remove number in stock of product
removeProductNumberInStock(productId, amountPrice.ElementAt(i).Key);
// add number of product sold today
}
}
//closeConnection();
}