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