public bool AddTransaction(Transaction transaction)
{
MySqlConnection cnn = DBUtility.getConnection();
if (cnn != null)
{
cnn.Open();
MySqlTransaction tran = cnn.BeginTransaction();
try
{
const string SQL = @"INSERT INTO
transactions(
transactiondate
, incomeamount
, expenseamount
, createdby
, remark
)
VALUES(
CURRENT_TIMESTAMP()
, @incomeamount
, @expenseamount
, @createdby
, @remark
);";
MySqlCommand command = new MySqlCommand(SQL, cnn);
command.Prepare();
command.Parameters.AddWithValue("@incomeamount", transaction.Incomeamount);
command.Parameters.AddWithValue("@expenseamount", transaction.Expenseamount);
command.Parameters.AddWithValue("@createdby", transaction.Createdby.Staffid);
command.Parameters.AddWithValue("@remark", transaction.Remark);
if (command.ExecuteNonQuery() > 0)
{
tran.Commit();
// MessageBox.Show("True");
return true;
}
}
catch (MySqlException e)
{
Console.WriteLine(e);
MessageBox.Show(e.ToString());
tran.Rollback();
}
finally
{
cnn.Close();
}
}
return false;
}
public bool UpdateTransaction(Transaction transaction)
{
MySqlConnection cnn = DBUtility.getConnection();
if (cnn != null)
{
cnn.Open();
MySqlTransaction trans = cnn.BeginTransaction();
try
{
const string SQL = @"UPDATE
transactions
SET
incomeamount = @incomeamount
, expenseamount = @expenseamount
, createdby = @createdby
, remark = @remark
WHERE
transactionid = @transactionid;";
MySqlCommand command = new MySqlCommand(SQL, cnn);
command.Prepare();
command.Parameters.AddWithValue("@incomeamount", transaction.Incomeamount);
command.Parameters.AddWithValue("@expenseamount", transaction.Expenseamount);
command.Parameters.AddWithValue("@createdby", transaction.Createdby.Staffid);
command.Parameters.AddWithValue("@remark", transaction.Remark);
command.Parameters.AddWithValue("@transactionid", transaction.Transactionid);
if (command.ExecuteNonQuery() > 0)
{
trans.Commit();
return true;
}
}
catch (MySqlException e)
{
Console.WriteLine(e);
trans.Rollback();
}
finally
{
cnn.Close();
}
}
return false;
}
public void cancelInvoice(int invoiceid)
{
/*
* Increase Quantity in Stock
* Get Total Amount
* Remove invoiceDetail by invoiceid
* Remove invoice by invoiceid
* Add tansaction withdrawal with Remark: Cancel Invoice...
*/
MySqlConnection con = DBUtility.getConnection();
if (con != null)
con.Open();
{
try
{
Invoice invoice = getInvoice(invoiceid);
decimal total = 0;
for (int i = 0; i < invoice.InvoiceDetail.Count; i++)
{
InvoiceDetail invoiceDetail = ((InvoiceDetail)invoice.InvoiceDetail[i]);
total += (invoiceDetail.Priceout * invoiceDetail.Quantity * (100 - invoiceDetail.Dicount));
String addProductSql = "UPDATE StoreProduct SET Quantity=Quantity+@quantity WHERE ProductId=@productid AND StoreId=(SELECT StoreID FROM Staffs WHERE Staffid=(SELECT Staffid FROM Invoices WHERE Invoiceid=@invoiceid))";
MySqlCommand addProductCommand = new MySqlCommand(addProductSql, con);
addProductCommand.Prepare();
addProductCommand.Parameters.AddWithValue("@quantity", ((InvoiceDetail)invoice.InvoiceDetail[i]).Quantity);
addProductCommand.Parameters.AddWithValue("@invoiceid", invoiceid);
addProductCommand.Parameters.AddWithValue("@productid", ((InvoiceDetail)invoice.InvoiceDetail[i]).Product.Productid);
addProductCommand.ExecuteNonQuery();
}
String removeDetailSql = "DELETE FROM InvoiceDetail WHERE InvoiceId=" + invoiceid;
MySqlCommand removeDetailCommand = new MySqlCommand(removeDetailSql, con);
removeDetailCommand.ExecuteNonQuery();
String removeInvoiceSql = "DELETE FROM Invoices WHERE InvoiceId=" + invoiceid;
MySqlCommand removeInvoiceCommand = new MySqlCommand(removeInvoiceSql, con);
removeInvoiceCommand.ExecuteNonQuery();
Transaction t = new Transaction();
t.Incomeamount = 0;
t.Expenseamount = total;
t.Createdby = UserSession.Session.Staff;
t.Remark = "Cancel on InvoiceID: " + invoiceid;
new TransactionDAO().AddTransaction(t);
}
catch (MySqlException e)
{
Console.WriteLine(e.ToString());
}
finally
{
con.Close();
}
}
}
