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