private void InsertUser(string userName, string password, int id)
{
SimsOracle db = new SimsOracle();
int rows = 0;
usersTA.FillByUser(this.employeeDS.SIMS_USERS, id);
if (employeeDS.SIMS_USERS.Rows.Count > 0)
{
MessageBox.Show("Username: "******" is already added");
}
else
{
try
{
string sql = "INSERT INTO SIMS_USERS " +
"(USER_NAME, PASSWORD, EMPLOYEE_ID) " +
" VALUES " +
"(:USER_NAME, :PASSWORD, :EMPLOYEE_ID)";
OracleCommand cmd = new OracleCommand(sql, db.Connection);
cmd.Parameters.Add("USER_NAME", userName);
cmd.Parameters.Add("PASSWORD", password);
cmd.Parameters.Add("EMPLOYEE_ID", id);
rows = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show("Error inserting user login details:\n" + ex.Message.ToString());
}
}
if (rows > 0)
{
MessageBox.Show("User login details has been added, you can now login using:\n" + userName + " and " + password + " as login credentials");
}
}
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 metroTileAdd_Click_1(object sender, EventArgs e)
{
SimsOracle db = new SimsOracle();
int rows = 0;
subjectTA.FillBySubjectCode(this.subjectDS.SUBJECT, TextBoxCode.Text.ToUpper());
if (subjectDS.SUBJECT.Rows.Count > 0)
{
MessageBox.Show("Subject with code: " + TextBoxCode.Text + " is already added");
ClearControls();
}
else if (TextBoxName.Text == "")
{
MessageBox.Show("Enter subject name or description");
}
else if (TextBoxCode.Text == "")
{
MessageBox.Show("Enter subject code");
}
else if (TextBoxCode.Text.Length != 4)
{
MessageBox.Show("Subject code must contain exactly four characters");
}
else if (TextBoxCost.Text == "")
{
MessageBox.Show("Enter subject cost");
}
else if (ComboBoxCategory.Text == "")
{
MessageBox.Show("Select subject category");
}
else
{
try
{
string sql = "INSERT INTO SUBJECT " +
"(SUBJECT_CODE, SUBJECT_NAME, SUBJECT_COST, CATEGORY) " +
" VALUES " +
"(:SUBJECT_CODE, :SUBJECT_NAME, :SUBJECT_COST, :CATEGORY)";
OracleCommand cmd = new OracleCommand(sql, db.Connection);
cmd.Parameters.Add("SUBJECT_CODE", TextBoxCode.Text.ToUpper());
cmd.Parameters.Add("SUBJECT_NAME", TextBoxName.Text);
cmd.Parameters.Add("SUBJECT_COST", TextBoxCost.Text);
cmd.Parameters.Add("CATEGORY", ComboBoxCategory.Text);
rows = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show("Error adding a school subject:\n" + ex.Message.ToString());
}
}
if (rows > 0)
{
MessageBox.Show("Subject added successfully.");
ClearControls();
}
}
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 void metroTileEnter_Click(object sender, EventArgs e)
{
SimsOracle db = new SimsOracle();
int rows = 0;
if (TextBoxName.Text == "")
{
MessageBox.Show("You should enter centre name");
TextBoxName.Focus();
}
else if (TextBoxAddress.Text == "")
{
MessageBox.Show("You should enter centre address");
TextBoxAddress.Focus();
}
else if (TextBoxClasses.Text == "")
{
MessageBox.Show("You should enter number of classes");
TextBoxClasses.Focus();
}
else if (!checkValue.isValidInteger(TextBoxClasses.Text))
{
MessageBox.Show("The number format is not valid");
TextBoxClasses.Focus();
}
else
{
try
{
string sql = "INSERT INTO CENTRE " +
"(NAME, ADDRESS, CLASSES) " +
" VALUES " +
"(:NAME, :ADDRESS, :CLASSES)";
OracleCommand cmd = new OracleCommand(sql, db.Connection);
cmd.Parameters.Add("NAME", TextBoxName.Text);
cmd.Parameters.Add("ADDRESS", TextBoxAddress.Text);
cmd.Parameters.Add("CLASSES", TextBoxClasses.Text);
rows = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show("Error adding a centre:\n" + ex.Message.ToString());
}
if (rows > 0)
{
MessageBox.Show("Centre added successfully");
ClearControls();
}
else
{
MessageBox.Show("Centre was not added, there might have been unexpected error");
}
}
}
private void metroTileSubmit_Click(object sender, EventArgs e)
{
SimsOracle db = new SimsOracle();
int rows = 0;
var time_in = dateTimeIn.Value.ToShortTimeString();
var time_out = dateTimeOut.Value.ToShortTimeString();
var attendanceDate = dateTimeAttendance.Value.ToShortDateString();
if (isSignedIn())
{
MessageBox.Show(labelName.Text.ToString() + " " + labelSurname.Text.ToString() + " is already signed in for today.\n" + "To edit times go to (Employee Module -> Edit Attendance Times)");
}
else if (textBoxLessons.Text == "")
{
MessageBox.Show("You should enter number of lessons");
}
else
{
try
{
string sql = "INSERT INTO STAFF_ATTENDANCE " +
"(EMPLOYEE_ID, SUBJECT, TIME_IN, TIME_OUT, NUM_LESSONS, HOURS, ATT_DATE) " +
" VALUES " +
" (:EMPLOYEE_ID, :SUBJECT, :TIME_IN, :TIME_OUT, :NUM_LESSONS, :HOURS, :ATT_DATE) ";
OracleCommand cmd = new OracleCommand(sql, db.Connection);
cmd.Parameters.Add("EMPLOYEE_ID", LabelEmployeeID.Text);
cmd.Parameters.Add("SUBJECT", comboBoxSubject.Text);
cmd.Parameters.Add("TIME_IN", OracleDbType.Date).Value = DateTime.Parse(time_in);
cmd.Parameters.Add("TIME_OUT", OracleDbType.Date).Value = DateTime.Parse(time_out);
cmd.Parameters.Add("NUM_LESSONS", textBoxLessons.Text);
cmd.Parameters.Add("HOURS", getHours());
cmd.Parameters.Add("ATT_DATE", attendanceDate);
rows = cmd.ExecuteNonQuery();
}
catch (OracleException ex)
{
MessageBox.Show("Error:\n" + ex.Message.ToString());
}
if (rows > 0)
{
MessageBox.Show("Thank you, attendance is recorded successful");
if (isNotPresent())
{
insertAttendee();
}
else
{
updateAttendees();
}
}
}
}
private decimal computeLessons(SimsOracle db)
{
decimal lessons = 0;
string sql = "SELECT SUM(num_lessons) AS total_lessons " +
" FROM SIMS.STAFF_ATTENDANCE " +
" WHERE time_in BETWEEN to_date('" + comboBoxStart.Text.ToString() + "', 'YYYY-MM-DD HH24:MI:SS') " +
" AND to_date('" + comboBoxEnd.Text.ToString() + "', 'YYYY-MM-DD HH24:MI:SS') " +
" AND (employee_id = '" + employee_id + "')";
OracleCommand cmd = new OracleCommand(sql, db.Connection);
lessons = Convert.ToDecimal(cmd.ExecuteScalar());
return(lessons);
}
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();
}
/**
* Method to update the student fee amount and balance of registered student after adding another subject
*/
private void updateStudentFee()
{
db = new SimsOracle();
int enrolCost = Convert.ToInt32(student_enrollmentTA.FeeCostSum(metroTextBoxAdminNo.Text, metroTextBoxYear.Text)); // updated cost of all registered subjects so far
int feeCost = Convert.ToInt32(student_feeTA.FeeAmount(metroTextBoxAdminNo.Text, metroTextBoxYear.Text)); // fee cost of all initial registered subjects exclusive of newly added subjects
int addedCost = enrolCost - feeCost; // additional cost after registering or adding another subject
int preBalance = Convert.ToInt32(student_feeTA.Balance(metroTextBoxAdminNo.Text, metroTextBoxYear.Text)); // previous balance before adding another subject for registration
int newBalance = preBalance + addedCost; // current balance after registration of another subject
try
{
string sql = "UPDATE SIMS.STUDENT_FEE " +
"SET AMOUNT = '" + enrolCost + "', " +
"BALANCE = '" + newBalance + "' ";
OracleCommand cmd = new OracleCommand(sql, db.Connection);
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show("Error!\n" + ex.Message.ToString());
}
}
/**
* Method to insert the student fee amount and balance of registered student
*/
private void insertStudentFee()
{
int rows = 0;
db = new SimsOracle();
int feeCost = Convert.ToInt32(student_enrollmentTA.FeeCostSum(metroTextBoxAdminNo.Text, metroTextBoxYear.Text));
int balance = 0;
if (checkLearnerPayment())
{
int preBalance = Convert.ToInt32(student_paymentTA.Balance(metroTextBoxAdminNo.Text, metroTextBoxYear.Text));
balance = feeCost - preBalance;
}
else
{
balance = feeCost;
}
try
{
string sql = "INSERT INTO SIMS.STUDENT_FEE " +
"(ADMISSION_NO, AMOUNT, BALANCE, ACADEMIC_YEAR) " +
" VALUES " +
"(:ADMISSION_NO, :AMOUNT, :BALANCE, :ACADEMIC_YEAR)";
OracleCommand cmd = new OracleCommand(sql, db.Connection);
cmd.Parameters.Add("ADMISSION_NO", metroTextBoxAdminNo.Text);
cmd.Parameters.Add("AMOUNT", feeCost);
cmd.Parameters.Add("BALANCE", balance);
cmd.Parameters.Add("ACADEMIC_YEAR", metroTextBoxYear.Text);
rows = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show("Error!\n" + ex.Message.ToString());
}
}
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();
}
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();
}
}
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();
}
}
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();
}
