public bool SaveDisbursement(string purpose, double amount, List <PaymentDetail> disburseDetails) { if (DBCon.State == ConnectionState.Open) { dbClose(); } dbOpen(); MySqlTransaction trans = DBCon.BeginTransaction(); try { MySqlCommand cmd = new MySqlCommand(); cmd.Connection = DBCon; cmd.Transaction = trans; cmd.CommandText = "INSERT INTO disbursement(purpose, amount) VALUES ('" + purpose + "', " + amount + ")"; cmd.ExecuteNonQuery(); long id = cmd.LastInsertedId; foreach (PaymentDetail dd in disburseDetails) { cmd.CommandText = "INSERT INTO disbursement_detail(iddisbursement, feecode, amount) VALUES (" + id + ", '" + dd.Code + "', " + dd.Amount + ")"; cmd.ExecuteNonQuery(); } trans.Commit(); dbClose(); return(true); } catch (Exception) { trans.Rollback(); return(false); } }
public bool SavePaymentBreakdown() { if (DBCon.State == ConnectionState.Open) { dbClose(); } //bool b = false; dbOpen(); MySqlTransaction trans = DBCon.BeginTransaction(); try { foreach (Fee f in Fees) { MySqlCommand cmd = new MySqlCommand(); cmd.Connection = DBCon; cmd.CommandText = "UPDATE assessment_detail SET amount_paid=amount_paid+" + f.Paid + " WHERE idfee=" + f.Id + " AND idassessment=" + AssessmentId; cmd.Transaction = trans; cmd.ExecuteNonQuery(); } SavePayment(); trans.Commit(); dbClose(); return(true); } catch (Exception ex) { MessageBox.Show(ex.Message); trans.Rollback(); return(false); } }
public bool DeleteAssessmentDetail(long idassessment, string feecode) { if (DBCon.State == ConnectionState.Open) { dbClose(); } dbOpen(); MySqlTransaction trans = DBCon.BeginTransaction(); try { MySqlCommand cmd = new MySqlCommand(); cmd.Connection = DBCon; cmd.Transaction = trans; cmd.CommandText = "DELETE FROM assessment_detail WHERE idassessment=" + idassessment + " AND feecode='" + feecode + "'"; cmd.ExecuteNonQuery(); cmd.CommandText = "SELECT SUM(amount) FROM assessment_detail WHERE idassessment=" + idassessment; object res = cmd.ExecuteScalar(); double total = 0; Double.TryParse(res.ToString(), out total); cmd.CommandText = "SELECT SUM(amount) FROM payment WHERE idassessment=" + idassessment; res = cmd.ExecuteScalar(); double paid; double balance; if (Double.TryParse(res.ToString(), out paid)) { balance = total - paid; } else { balance = total; } cmd.CommandText = "UPDATE assessment SET total=" + total + ", balance=" + balance + " WHERE idassessment=" + idassessment; cmd.ExecuteNonQuery(); trans.Commit(); dbClose(); return(true); } catch (Exception ex) { MessageBox.Show(ex.Message); trans.Rollback(); return(false); } }
public bool SavePaymentOther() { if (DBCon.State == ConnectionState.Open) { dbClose(); } dbOpen(); MySqlTransaction trans = DBCon.BeginTransaction(); try { MySqlCommand cmd = new MySqlCommand(); cmd.Connection = DBCon; cmd.Transaction = trans; //insert payment to database cmd.CommandText = "INSERT INTO payment(idcashier, idassessment, ornumber, amount, paymenttype, payer, payment.division) VALUES ('" + CashierId + "'," + AssessmentId + ", '" + ORNumber + "', " + Amount + ", " + PaymentType + ", '" + Payer + "', " + Division + ")"; cmd.ExecuteNonQuery(); long lastpaymentId = cmd.LastInsertedId; //get last payment id inserted //insert payment details foreach (PaymentDetail fee in PaymentDetail) { cmd.CommandText = "INSERT INTO payment_detail(feecode,idpayment,amount) VALUES('" + fee.Code + "'," + lastpaymentId + "," + fee.Amount + ")"; cmd.ExecuteNonQuery(); } trans.Commit(); dbClose(); return(true); } catch (Exception) { trans.Rollback(); dbClose(); return(false); } }
public bool SavePayment() { if (DBCon.State == ConnectionState.Open) { dbClose(); } dbOpen(); MySqlTransaction trans = DBCon.BeginTransaction(); try { MySqlCommand cmd = new MySqlCommand(); cmd.Connection = DBCon; cmd.Transaction = trans; //insert payment to database cmd.CommandText = "INSERT INTO payment(idcashier, idassessment, ornumber, amount, paymenttype) VALUES ('" + CashierId + "'," + AssessmentId + ", '" + ORNumber + "', " + Amount + ", " + PaymentType + ")"; cmd.ExecuteNonQuery(); long lastpaymentId = cmd.LastInsertedId; //get last payment id inserted //get sum of items in the assessment cmd.CommandText = "SELECT SUM(amount) FROM assessment_detail WHERE idassessment=" + AssessmentId; object res = cmd.ExecuteScalar(); double total = 0; Double.TryParse(res.ToString(), out total); //get sum of payment on same assessment cmd.CommandText = "SELECT SUM(amount) FROM payment WHERE idassessment=" + AssessmentId; res = cmd.ExecuteScalar(); double paid; double balance; if (Double.TryParse(res.ToString(), out paid)) { balance = total - paid; } else { balance = total; } //update assessment details for balance and total cmd.CommandText = "UPDATE assessment SET total=" + total + ", balance=" + balance + " WHERE idassessment=" + AssessmentId; cmd.ExecuteNonQuery(); //insert payment details foreach (PaymentDetail fee in PaymentDetail) { cmd.CommandText = "INSERT INTO payment_detail(feecode,idpayment,amount) VALUES('" + fee.Code + "'," + lastpaymentId + "," + fee.Amount + ")"; cmd.ExecuteNonQuery(); } //change pending to enrolled on first payment cmd.CommandText = "SELECT registration.idregistration as regid, status FROM registration INNER JOIN assessment ON registration.idregistration=assessment.idregistration WHERE idassessment=" + AssessmentId + " AND status='PENDING'"; string status = string.Empty; long idreg = 0; using (MySqlDataReader reader = cmd.ExecuteReader()) { if (reader.HasRows) { reader.Read(); status = reader["status"].ToString(); idreg = Convert.ToUInt32(reader["regid"].ToString()); } } if (status.Equals("PENDING")) { cmd.CommandText = "UPDATE registration SET status='ENROLLED' WHERE idregistration=" + idreg; cmd.ExecuteNonQuery(); } trans.Commit(); dbClose(); return(true); } catch (Exception) { trans.Rollback(); dbClose(); return(false); } }
public bool UpdateTuition(long idassessment, double amount, double discount, int discounttype) {//0=discount, 1= subsidy if (DBCon.State == ConnectionState.Open) { dbClose(); } dbOpen(); MySqlTransaction trans = DBCon.BeginTransaction(); try { MySqlCommand cmd = new MySqlCommand(); cmd.Connection = DBCon; cmd.Transaction = trans; bool hasTuition = false; string feecode = ""; cmd.CommandText = "SELECT feetype, assessment_detail.feecode as code, amount FROM assessment_detail INNER JOIN fee ON assessment_detail.feecode=fee.feecode WHERE feetype=1 AND idassessment=" + idassessment; using (MySqlDataReader reader = cmd.ExecuteReader()) { if (reader.HasRows) { reader.Read(); feecode = reader["code"].ToString(); hasTuition = true; } } if (hasTuition) { cmd.CommandText = "UPDATE assessment_detail SET amount=" + amount + " WHERE idassessment=" + idassessment + " AND feecode='" + feecode + "'"; cmd.ExecuteNonQuery(); cmd.CommandText = "SELECT SUM(amount) FROM assessment_detail WHERE idassessment=" + idassessment; object res = cmd.ExecuteScalar(); double total = 0; Double.TryParse(res.ToString(), out total); cmd.CommandText = "SELECT SUM(amount) FROM payment WHERE idassessment=" + idassessment; res = cmd.ExecuteScalar(); double paid; double balance; if (Double.TryParse(res.ToString(), out paid)) { balance = total - paid; } else { balance = total; } if (discounttype == 0) { cmd.CommandText = "UPDATE assessment SET total=" + total + ", balance=" + balance + ", discount=" + discount + " WHERE idassessment=" + idassessment; } else { cmd.CommandText = "UPDATE assessment SET total=" + total + ", balance=" + balance + ", subsidy=" + discount + " WHERE idassessment=" + idassessment; } cmd.ExecuteNonQuery(); trans.Commit(); dbClose(); return(true); } else { throw new Exception(); } } catch (Exception) { trans.Rollback(); return(false); } }