private void metroTileAddFeeStructure_Click(object sender, EventArgs e)
{
db = new SimsOracle();
int rows = 0;
if (metroComboBoxOccurences.Text == "")
{
MessageBox.Show("You have to select one of the fee occurence options first");
}
else if (metroTextBoxFeeName.Text == "")
{
MessageBox.Show("Enter fee category name to continue");
}
else if (metroTextBoxFeeAmount.Text == "")
{
MessageBox.Show("Enter fee amount to continue");
}
else
{
try
{
string sql = "INSERT INTO SIMS.FEE " +
"(FEE_CATEGORY, FEE_AMOUNT, FEE_OCCURENCE) " +
"VALUES (:FEE_CATEGORY, :FEE_AMOUNT, :FEE_OCCURENCE)";
OracleCommand cmd = new OracleCommand(sql, db.Connection);
cmd.Parameters.Add("FEE_CATEGORY", metroTextBoxFeeName.Text);
cmd.Parameters.Add("FEE_AMOUNT", metroTextBoxFeeAmount.Text);
cmd.Parameters.Add("FEE_OCCURENCE", metroComboBoxOccurences.Text);
rows = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show("Database error!\n" + ex.Message.ToString());
}
finally
{
db.CloseDatabase();
}
}
if (rows > 0)
{
MetroMessageBox.Show(ParentForm, "", "Fee Strucure Setup Captured Successful", MessageBoxButtons.OK, MessageBoxIcon.Question);
metroComboBoxOccurences.ResetText();
metroTextBoxFeeName.Clear();
metroTextBoxFeeAmount.Clear();
}
else
{
MetroMessageBox.Show(ParentForm, " ", "Details not captured!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
db.CloseDatabase();
}
private int generateSalary()
{
SimsOracle db = new SimsOracle();
int rows = 0;
try
{
decimal lessons = computeLessons(db);
decimal pay = computeBasicPay(lessons);
string sql = "INSERT INTO SIMS.SALARY " +
" (START_DATE, END_DATE, LESSONS, RATE, BASIC_PAY, EMPLOYEE_ID)" +
" VALUES" +
"(to_date('" + comboBoxStart.Text.ToString() + "', 'YYYY-MM-DD HH24:MI:SS'), to_date('" + comboBoxEnd.Text.ToString() + "', 'YYYY-MM-DD HH24:MI:SS'), :LESSONS, :RATE, :BASIC_PAY, :EMPLOYEE_ID)";
OracleCommand cmd = new OracleCommand(sql, db.Connection);
cmd.Parameters.Add("LESSONS", lessons);
cmd.Parameters.Add("RATE", TextBoxRate.Text);
cmd.Parameters.Add("BASIC_PAY", pay);
cmd.Parameters.Add("EMPLOYEE_ID", LabelStaffNo.Text);
rows = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show("Error generating salary:\n" + ex.Message.ToString());
}
finally
{
db.CloseDatabase();
}
return(rows);
}
private void metroTileAddExpense_Click(object sender, EventArgs e)
{
db = new SimsOracle();
int rows = 0;
if (metroTextBoxExpType.Text == "")
{
MetroMessageBox.Show(ParentForm, "", "You need to enter Expense Type", MessageBoxButtons.OK, MessageBoxIcon.Question);
}
else
{
try
{
string sql = "INSERT INTO SIMS.EXPENSE " +
"(EXPENSE_TYPE, DESCRIPTION) " +
"VALUES (:EXPENSE_TYPE, :DESCRIPTION)";
OracleCommand cmd = new OracleCommand(sql, db.Connection);
cmd.Parameters.Add("EXPENSE_TYPE", metroTextBoxExpType.Text);
cmd.Parameters.Add("DESCRIPTION", metroTextBoxDescription.Text);
rows = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show("Database error!\n" + ex.Message.ToString());
}
finally
{
db.CloseDatabase();
}
}
if (rows > 0)
{
MetroMessageBox.Show(ParentForm, "", "Expense Details Captured Successful", MessageBoxButtons.OK, MessageBoxIcon.Question);
metroTextBoxExpType.Clear();
metroTextBoxDescription.Clear();
this.Refresh();
}
else
{
MetroMessageBox.Show(ParentForm, " ", "Details not captured!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
db.CloseDatabase();
}
public int addNewStudent(Student stu)
{
db = new SimsOracle();
int rows = 0;
var datestring = stu.admittedDate.Value.ToShortDateString();
try
{
string query = "INSERT INTO SIMS.STUDENT " +
"(NAME, SURNAME, GENDER," +
"PHONE, ENROLLED_DATE, CITIZEN_ID, ADDRESS," +
"SUBURB, CITY, ZIPCODE, EMAIL, CENTRE) " +
"VALUES (:NAME, :SURNAME, :GENDER," +
":PHONE, :ENROLLED_DATE, :CITIZEN_ID, :ADDRESS," +
":SUBURB, :CITY, :ZIPCODE, :EMAIL, :CENTRE" +
")";
OracleCommand cmd = new OracleCommand(query, db.Connection);
cmd.Parameters.Add("FIRST_NAME", stu.firstName);
cmd.Parameters.Add("LAST_NAME", stu.lastName);
cmd.Parameters.Add("STUDENT_GENDER", stu.gender);
cmd.Parameters.Add("PHONE_NUMBER", stu.contactNumber);
cmd.Parameters.Add("ENROLLED_DATE", OracleDbType.Date).Value = DateTime.Parse(datestring);
cmd.Parameters.Add("STUDENT_CITIZEN_ID", stu.studentCitizenID);
cmd.Parameters.Add("ADDRESS_LINE1", stu.addressLine1);
cmd.Parameters.Add("SUBURB", stu.suburb);
cmd.Parameters.Add("CITY", stu.city);
cmd.Parameters.Add("ZIP_CODE", stu.zipCode);
cmd.Parameters.Add("EMAIL_ADDRESS", stu.emailAddress);
cmd.Parameters.Add("CENTRE", stu.centre);
rows = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show("Database error!\n" + ex.Message.ToString());
}
finally
{
db.CloseDatabase();
}
db.CloseDatabase();
return(rows);
}
private void fillEndDate()
{
SimsOracle db = new SimsOracle();
string sql_end = "SELECT SIMS.STAFF_ATTENDANCE.TIME_IN " +
" FROM SIMS.STAFF_ATTENDANCE " +
" WHERE (EMPLOYEE_ID = '" + employee_id + "') " +
" ORDER BY SIMS.STAFF_ATTENDANCE.TIME_IN desc";
try
{
da = new OracleDataAdapter(sql_end, db.Connection);
DataTable dt = new DataTable();
da.Fill(dt);
comboBoxEnd.DataSource = dt;
}
catch (Exception ex)
{
MessageBox.Show("Error loading start dates:\n" + ex.Message.ToString());
}
db.CloseDatabase();
}
internal void displaySubjects()
{
SimsOracle db = new SimsOracle();
string s = "SELECT SIMS.STUDENT_ENROLLMENT.SUBJECT_CODE, SIMS.SUBJECT.SUBJECT_NAME, SIMS.STUDENT_ENROLLMENT.ACADEMIC_YEAR " +
" FROM SIMS.STUDENT_ENROLLMENT INNER JOIN " +
"SIMS.SUBJECT ON SIMS.STUDENT_ENROLLMENT.SUBJECT_CODE = SIMS.SUBJECT.SUBJECT_CODE " +
"WHERE (ADMISSION_NO = '" + admission_no + "')";
try
{
OracleDataAdapter da = new OracleDataAdapter(s, db.Connection);
DataTable dt = new DataTable();
da.Fill(dt);
subjectDGV.DataSource = dt;
}
catch (Exception ex)
{
MessageBox.Show("Error loading subjects:\n" + ex.Message.ToString());
}
db.CloseDatabase();
}
private void fillStartDate()
{
SimsOracle db = new SimsOracle();
var id = Convert.ToDecimal(employee_id);
string sql_start = "SELECT SIMS.STAFF_ATTENDANCE.TIME_IN " +
" FROM SIMS.STAFF_ATTENDANCE " +
" WHERE (EMPLOYEE_ID = '" + employee_id + "')";
try
{
clock_timesTA.FillByEmployeeID(this.clockDS.CLOCK_TIMES, id);
da = new OracleDataAdapter(sql_start, db.Connection);
DataTable dt = new DataTable();
da.Fill(dt);
comboBoxStart.DataSource = dt;
}
catch (Exception ex)
{
MessageBox.Show("Error loading start dates:\n" + ex.Message.ToString());
}
db.CloseDatabase();
}
internal void payTuitionFee()
{
db = new SimsOracle();
int rows = 0;
if (TextBoxAdminNo.Text == "" || !isAdmissionNoValid(TextBoxAdminNo.Text))
{
MessageBox.Show("Admission No. " + TextBoxAdminNo.Text + " is not valid");
}
else if (TextBoxYear.Text == "" || TextBoxYear.Text.Length > 4 || TextBoxYear.Text.Length != 4 || TextBoxYear.Text.Length < 0)
{
MessageBox.Show("Academic year is not valid");
}
else if (!isYearRegistered(TextBoxYear.Text))
{
MessageBox.Show("Student: " + TextBoxAdminNo.Text + " is not registered for academic year: " + TextBoxYear.Text);
}
else if (TextBoxPayAmount.Text == "")
{
MessageBox.Show("Enter payment amount");
}
else if (ComboBoxPayType.Text == "")
{
MessageBox.Show("Select type of payment");
}
else
{
var datestring = DateTimePayment.Value.ToShortDateString();
int feeAmount = Convert.ToInt32(student_feeTA.FeeAmount(TextBoxAdminNo.Text, TextBoxYear.Text));
int newBalance;
if (checkLearnerPayment())
{
newBalance = Convert.ToInt32(student_feeTA.Balance(TextBoxAdminNo.Text, TextBoxYear.Text)) - Convert.ToInt32(TextBoxPayAmount.Text);
}
else
{
newBalance = feeAmount - Convert.ToInt32(TextBoxPayAmount.Text);
}
try
{
string sql = "INSERT INTO STUDENT_PAYMENT " +
"(PAYMENT_AMOUNT, PAYMENT_TYPE, BALANCE, PAYMENT_DATE, CAPTURED_DATE, ADMISSION_NO, ACADEMIC_YEAR)" +
"VALUES (" +
":PAYMENT_AMOUNT, :PAYMENT_TYPE, :BALANCE, :PAYMENT_DATE, :CAPTURED_DATE, :ADMISSION_NO, :ACADEMIC_YEAR )";
OracleCommand cmd = new OracleCommand(sql, db.Connection);
cmd.Parameters.Add("PAYMENT_AMOUNT", TextBoxPayAmount.Text);
cmd.Parameters.Add("PAYMENT_TYPE", ComboBoxPayType.Text);
cmd.Parameters.Add("BALANCE", newBalance);
cmd.Parameters.Add("PAYMENT_DATE", OracleDbType.Date).Value = DateTime.Parse(datestring);
cmd.Parameters.Add("CAPTURED_DATE", OracleDbType.Date).Value = DateTime.Now;
cmd.Parameters.Add("ADMISSION_NO", TextBoxAdminNo.Text);
cmd.Parameters.Add("ACADEMIC_YEAR", TextBoxYear.Text);
rows = cmd.ExecuteNonQuery();
db.CloseDatabase();
}
catch (Exception ex)
{
MessageBox.Show("Database error!\n" + ex.Message.ToString());
}
finally
{
db.CloseDatabase();
}
if (rows > 0)
{
MetroMessageBox.Show(ParentForm, "", "Student Payment Captured Successful", MessageBoxButtons.OK, MessageBoxIcon.Question);
student_feeTA.UpdateBalance(newBalance, TextBoxAdminNo.Text, TextBoxYear.Text);
ClearControls();
}
else
{
MetroMessageBox.Show(ParentForm, " ", "Student Payment not captured!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
db.CloseDatabase();
}
}
private void metroTileAddExpPayment_Click(object sender, EventArgs e)
{
db = new SimsOracle();
int rows = 0;
if (metroTextBoxExpAmount.Text == "")
{
MetroMessageBox.Show(ParentForm, "", "You need to enter expense amount", MessageBoxButtons.OK, MessageBoxIcon.Question);
}
else if (metroComboBoxExpenseType.Text == "")
{
MetroMessageBox.Show(ParentForm, "", "Select one of the expense type options", MessageBoxButtons.OK, MessageBoxIcon.Question);
}
else
{
var datestring = metroDateTimeExpense.Value.ToShortDateString();
try
{
string query = "INSERT INTO SIMS.EXPENSE_PAYMENT " +
"(EXPENSE_AMOUNT, EXPENSE_NOTE, payment_date, captured_date, expense_id)" +
"VALUES (" +
":EXPENSE_AMOUNT, " +
":EXPENSE_NOTE, " +
":PAYMENT_DATE, " +
":CAPTURED_DATE, " +
"(SELECT EXPENSE.EXPENSE_ID FROM EXPENSE WHERE EXPENSE.EXPENSE_TYPE = '" + metroComboBoxExpenseType.Text + "'))";
OracleCommand cmd = new OracleCommand(query, db.Connection);
cmd.Parameters.Add("EXPENSE_AMOUNT", metroTextBoxExpAmount.Text);
cmd.Parameters.Add("EXPENSE_NOTE", metroTextBoxExpNote.Text);
cmd.Parameters.Add("PAYMENT_DATE", OracleDbType.Date).Value = DateTime.Parse(datestring);
cmd.Parameters.Add("CAPTURED_DATE", OracleDbType.Date).Value = DateTime.Now;
rows = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show("Database error!\n" + ex.Message.ToString());
}
finally
{
db.CloseDatabase();
}
}
if (rows > 0)
{
MetroMessageBox.Show(ParentForm, "", "Expense Payment Captured Successful", MessageBoxButtons.OK, MessageBoxIcon.Question);
metroTextBoxExpAmount.Clear();
metroTextBoxExpNote.Clear();
metroDateTimeExpense.ResetText();
metroComboBoxExpenseType.ResetText();
this.Refresh();
}
else
{
MetroMessageBox.Show(ParentForm, " ", "Expense Payment not captured!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
db.CloseDatabase();
}
private void metroTileAdd_Click(object sender, EventArgs e)
{
db = new SimsOracle();
int rows = 0;
if (TextBoxFirstName.Text == "")
{
MessageBox.Show("You must enter employee first name");
}
else if (TextBoxLastName.Text == "")
{
MessageBox.Show("You must enter employee last name");
}
else if (!checkIf.isValidIDNumber(TextBoxCitizenID.Text))
{
MessageBox.Show("ID number format is not valid");
}
else if (!checkIf.isValidPhoneNumber(TextBoxContact.Text))
{
MessageBox.Show("Contact number format is not valid");
}
else if (ComboBoxRole.Text == "")
{
MessageBox.Show("You must select a job role");
}
else if (ComboBoxGender.Text == "")
{
MessageBox.Show("You must select a gender");
}
else if (TextBoxAddress.Text == "")
{
MessageBox.Show("Enter employee address");
}
else if (TextBoxCity.Text == "")
{
MessageBox.Show("Enter employee city");
}
else if (TextBoxZipCode.Text == "" || TextBoxZipCode.Text.Length > 4 || TextBoxZipCode.Text.Length < 0)
{
MessageBox.Show("Employee postal code is not valid");
}
else if (TextBoxEmail.Text != "" && !checkIf.isValidEmail(TextBoxEmail.Text))
{
MessageBox.Show("Email address format is not valid");
}
else
{
var dateString = DateTimeHireStaff.Value.ToShortDateString();
try
{
string sql = "INSERT INTO SIMS.EMPLOYEE" +
"(NAME, SURNAME, GENDER, CITIZEN_ID, PHONE, ROLE, HIRE_DATE, EMAIL, ADDRESS, CITY, ZIPCODE)" +
" VALUES " +
"(:NAME, :SURNAME, :GENDER, :CITIZEN_ID, :PHONE, :ROLE, :HIRE_DATE, :EMAIL, :ADDRESS, :CITY, :ZIPCODE)";
cmd = new OracleCommand(sql, db.Connection);
cmd.Parameters.Add("NAME", TextBoxFirstName.Text);
cmd.Parameters.Add("SURNAME", TextBoxLastName.Text);
cmd.Parameters.Add("GENDER", ComboBoxGender.Text);
cmd.Parameters.Add("CITIZEN_ID", TextBoxCitizenID.Text);
cmd.Parameters.Add("PHONE", TextBoxContact.Text);
cmd.Parameters.Add("ROLE", ComboBoxRole.Text);
cmd.Parameters.Add("HIRE_DATE", OracleDbType.Date).Value = DateTime.Parse(dateString);
cmd.Parameters.Add("EMAIL", TextBoxEmail.Text);
cmd.Parameters.Add("ADDRESS", TextBoxAddress.Text);
cmd.Parameters.Add("CITY", TextBoxCity.Text);
cmd.Parameters.Add("ZIPCODE", TextBoxZipCode.Text);
rows = cmd.ExecuteNonQuery();
if (rows > 0)
{
MetroMessageBox.Show(ParentForm, "", "Staff Details Captured Successful", MessageBoxButtons.OK, MessageBoxIcon.Question);
ClearControls();
}
else
{
MetroMessageBox.Show(ParentForm, " ", "Staff Details not captured!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
catch (Exception ex)
{
MessageBox.Show("Error!\n" + ex.Message.ToString());
}
finally
{
db.CloseDatabase();
}
}
db.CloseDatabase();
}
internal void registerStudent()
{
db = new SimsOracle();
int rows = 0;
int count = 0;
if (checkAllConditions()) // check all pre-conditions
{
try
{
for (int i = 0; i < subjectsDGV.Rows.Count; i++)
{
if (subjectsDGV.Rows[i].Selected) // perform operation to selected row
{
string subject_code = Convert.ToString(subjectsDGV.Rows[i].Cells[0].Value);
student_enrollmentTA.AdminNoSubjCodeDuplicate(regDS.STUDENT_ENROLLMENT, metroTextBoxAdminNo.Text, subject_code, metroTextBoxYear.Text);
if (regDS.STUDENT_ENROLLMENT.Rows.Count > 0) // check subject registration duplicates
{
MessageBox.Show(subject_code + " is already registered to admission number: " + metroTextBoxAdminNo.Text);
}
else
{
string sql = "INSERT INTO SIMS.STUDENT_ENROLLMENT" +
"(ADMISSION_NO, SUBJECT_CODE, ACADEMIC_YEAR, GRADE, COST)" +
" VALUES" +
"(:ADMISSION_NO, :SUBJECT_CODE, :ACADEMIC_YEAR, :GRADE, :COST)";
OracleCommand cmd = new OracleCommand(sql, db.Connection);
cmd.Parameters.Add("ADMISSION_NO", metroTextBoxAdminNo.Text);
cmd.Parameters.Add("SUBJECT_CODE", subjectsDGV.Rows[i].Cells[0].Value);
cmd.Parameters.Add("ACADEMIC_YEAR", metroTextBoxYear.Text);
cmd.Parameters.Add("GRADE", metroTextBoxGrade.Text);
/* The first subject will cost R500 extra than the subsequent subject plus R500 registration fee*/
count = Convert.ToInt32(student_enrollmentTA.CountRegisterdSubjects(metroTextBoxAdminNo.Text, metroTextBoxYear.Text));
if (count > 0)
{
cmd.Parameters.Add("COST", subjectsDGV.Rows[i].Cells[2].Value);
}
else
{
double cost = Convert.ToDouble(subjectsDGV.Rows[i].Cells[2].Value);
cmd.Parameters.Add("COST", cost + 1000);
}
rows = cmd.ExecuteNonQuery();
}
}
}
}
catch (Exception ex)
{
MessageBox.Show("Error!\n" + ex.Message.ToString());
}
finally
{
db.CloseDatabase();
}
db.CloseDatabase();
} // end if(checkAllConditions)
if (rows > 0)
{
MetroMessageBox.Show(ParentForm, "", "Registration captured successful", MessageBoxButtons.OK, MessageBoxIcon.Question);
if (isLearnerRegistered())
{
updateStudentFee(); // update tuition fee for already registered student
}
else
{
insertStudentFee(); // new registration fee captured
}
ClearControls();
}
}