private void btn_save_supply_Click(object sender, EventArgs e)
{
int isDrug = (tb_dosage.Text == "") ? 0 : 1;
DB = new FinalSanitariumMIS.Helpers.DatabaseHelper();
DB2ResultSet rs = DB.QueryWithResultSet("SELECT ITEM_NUMBER FROM SUPPLY WHERE ITEM_NUMBER = " + tb_supply_no.Text);
if (!rs.Read())
{
DB.Query("INSERT INTO SUPPLY VALUES (" +
tb_supply_no.Text + "," + tb_supplier.Text + ",'" + tb_supply_name.Text + "','" + tb_description.Text + "',0," +
tb_reorder_level.Text + ",'" + tb_unit.Text + "'," + tb_cost_per_unit.Text + "," + isDrug + ",'" + tb_dosage.Text + "','" +
cb_method_administration.Text + "')");
}
else
{
DB.Query("UPDATE SUPPLY SET " +
"SUPPLIER_NUMBER = " + tb_supplier.Text + ", ITEM_NAME = '" + tb_supply_name.Text + "', DESCRIPTION = '" + tb_description.Text +
"', REORDER_LEVEL = " + tb_reorder_level.Text + ", UNIT = '" + tb_unit.Text + "', COST_PER_UNIT = " + tb_cost_per_unit.Text +
", ITEM_TYPE = " + isDrug + ", DOSAGE = '" + tb_dosage.Text + "', METHOD_OF_ADMINISTRATION = '" + cb_method_administration.Text +
"' WHERE ITEM_NUMBER = " + dgv_inventory.CurrentRow.Cells[0].Value.ToString());
}
RefreshMasterList();
ClearAllFields();
}
private void RefreshMasterList()
{
String modifier = "";
if (cb_filter.SelectedIndex == 0 || cb_filter.SelectedIndex == -1)
{
modifier = "WHERE " +
" ITEM_NUMBER LIKE '" + tb_search.Text + "%' OR " +
" SUPPLIER_NUMBER LIKE '" + tb_search.Text + "%' OR " +
" ITEM_NAME LIKE '" + tb_search.Text + "%' OR " +
" DESCRIPTION LIKE '" + tb_search.Text + "%' OR " +
" QUANTITY LIKE '" + tb_search.Text + "%' OR " +
" REORDER_LEVEL LIKE '" + tb_search.Text + "%' OR " +
" UNIT LIKE '" + tb_search.Text + "%' OR " +
" COST_PER_UNIT LIKE '" + tb_search.Text + "%' OR " +
" ITEM_TYPE LIKE '" + tb_search.Text + "%' OR " +
" DOSAGE LIKE '" + tb_search.Text + "%' OR " +
" METHOD_OF_ADMINISTRATION LIKE '%" + tb_search.Text + "'";
}
else
{
modifier = "WHERE " + cb_filter.SelectedItem.ToString() + " LIKE '" + tb_search.Text + "%'";
}
DB = new FinalSanitariumMIS.Helpers.DatabaseHelper();
DB2ResultSet rs = DB.QueryWithResultSet("SELECT * FROM SUPPLY " + modifier);
dgv_inventory.Rows.Clear();
while (rs.Read())
{
Object[] d = new Object[11];
d[0] = rs.GetString(0);
d[1] = rs.GetString(2);
d[2] = rs.GetString(3);
d[3] = rs.GetString(4);
d[4] = rs.GetString(5);
d[5] = rs.GetString(6);
d[6] = rs.GetString(7);
d[7] = rs.GetString(1);
d[8] = (rs.GetInt32(8) == 0)? "Supply" : "Drug";
d[9] = rs.GetString(9);
d[10] = rs.GetString(10);
dgv_inventory.Rows.Add(d);
}
}
private void StaffRegistration_Load(object sender, EventArgs e)
{
cbsex.Items.Add("MALE");
cbsex.Items.Add("FEMALE");
DB2ResultSet rsposition = DB.QueryWithResultSet("SELECT POSITION_TITLE FROM POSITION");
while (rsposition.Read())
{
cbPosition.Items.Add(rsposition["POSITION_TITLE"]);
}
DB2ResultSet rsemploy = DB.QueryWithResultSet("SELECT TYPE FROM EMPLOYMENTTYPE");
while (rsemploy.Read())
{
cbEmploymentType.Items.Add(rsemploy["TYPE"]);
}
DB2ResultSet rssalary = DB.QueryWithResultSet("SELECT SALARY_ID, SALARY FROM SALARY");
while (rssalary.Read())
{
cbSalaryPaymnetType.Items.Add(rssalary["SALARY_ID"] + " - Php" + rssalary["SALARY"]);
}
DB2ResultSet rsshift = DB.QueryWithResultSet("SELECT * FROM WORKSHIFTTYPE");
while (rsshift.Read())
{
cbworkshift.Items.Add(rsshift["SHIFT"]);
}
DB2ResultSet max_staffID = DB.QueryWithResultSet("SELECT MAX(STAFF_NUMBER) as MAXID FROM STAFF");
while (max_staffID.Read())
{
try
{
txtstaffnumber.Text = (Convert.ToInt32(max_staffID["MAXID"]) + 1).ToString();
}
catch (Exception er)
{
txtstaffnumber.Text = "10001";
}
}
}
private void btnlogin_Click(object sender, EventArgs e)
{
DB = new FinalSanitariumMIS.Helpers.DatabaseHelper();
DB2ResultSet rs = DB.QueryWithResultSet("SELECT * FROM VW_ACCOUNT WHERE USERNAME = '******' AND PASSCODE = '" + txtpassword.Text + "'");
if (rs.Read())
{
Models.SessionMeta session = new Models.SessionMeta();
this.Visible = false;
new MedDirMainMenu().Visible = true;
}
else
{
MessageBox.Show("Incorrect Login Credentials!");
}
}
private void RefreshMasterList()
{
DB = new FinalSanitariumMIS.Helpers.DatabaseHelper();
DB2ResultSet rs = DB.QueryWithResultSet("SELECT * FROM SUPPLIER");
dgv_masterlist.Rows.Clear();
while (rs.Read())
{
Object[] d = new Object[5];
d[0] = rs.GetString(0);
d[1] = rs.GetString(1);
d[2] = rs.GetString(2);
d[3] = rs.GetString(3);
d[4] = rs.GetString(4);
dgv_masterlist.Rows.Add(d);
}
}
private void RefreshMasterList()
{
// not yet tested , requires view from db
DB = new FinalSanitariumMIS.Helpers.DatabaseHelper();
DB2ResultSet rs = DB.QueryWithResultSet("SELECT * FROM VW_STAFF WHERE POSITION = 'doctor'");
dgv_masterlist.Rows.Clear();
while (rs.Read())
{
Object[] d = new Object[5];
d[0] = rs.GetString(0);
d[1] = rs.GetString(1);
d[2] = rs.GetString(2);
d[3] = rs.GetString(3);
d[4] = rs.GetString(4);
dgv_masterlist.Rows.Add(d);
}
}
private void btn_reigster_Click(object sender, EventArgs e)
{
DB = new FinalSanitariumMIS.Helpers.DatabaseHelper();
DB2ResultSet rs = DB.QueryWithResultSet("SELECT * FROM VW_STAFF WHERE supplier_number = '" + tb_tel_no.Text + "'");
if (rs.Read())
{
//DB.Query("UPDATE STAFF SET " +
// "SUPPLIER_NUMBER = " + tb_supplier_number.Text + "," +
// "SUPPLIER_NAME = '" + tb_supplier_name.Text + "'," +
// "ADDRESS = '" + tb_address.Text + "'," +
// "TELNUMBER = " + tb_tel_no.Text + "," +
// "FAXNUMBER = " + tb_fax_no.Text + " WHERE SUPPLIER_NUMBER = " +
// dgv_masterlist.CurrentRow.Cells[0].Value.ToString());
}
else
{
//DB.Query("INSERT INTO SUPPLIER VALUES (" +
// tb_supplier_number.Text + ",'" + tb_supplier_name.Text + "','" +
// tb_address.Text + "'," + tb_tel_no.Text + "," + tb_fax_no.Text + ")");
}
}
public DB2ResultSet QueryWithResultSet(String sql_query)
{
DB2ResultSet rs = null;
try
{
if (!Connection.IsOpen)
{
Connection.Open();
}
DB2Command command = new DB2Command(sql_query, Connection);
rs = command.ExecuteResultSet(DB2ResultSetOptions.None);
return(rs);
}
catch (DB2Exception ex)
{
MessageBox.Show("Error: " + ex.Message.ToString(), "Database Helper ResultSet");
}
return(rs);
}
private void btn_register_Click(object sender, EventArgs e)
{
// Add Code For Input Constraint Implementation
DB.Query("INSERT INTO STAFF VALUES("
+ Convert.ToInt32(txtstaffnumber.Text) + ",'"
+ txtfirstname.Text + "','"
+ txtlastname.Text + "','"
+ txtaddress.Text + "','"
+ txttelnumber.Text + "','"
+ dpbirthdate.Value.ToString("yyyy-MM-dd") + "','"
+ cbsex.Text + "',"
+ Convert.ToInt32(txtNIN.Text) +
")");
DB2ResultSet max_posID = DB.QueryWithResultSet("SELECT MAX(POSITIONHELD_ID) as MAXID FROM POSITIONHELD");
int posid = 0;
while (max_posID.Read())
{
try
{
posid = Convert.ToInt32(max_posID["MAXID"]) + 1;
}
catch (Exception er)
{
posid = 1;
}
}
DB.Query("INSERT INTO POSITIONHELD VALUES("
+ posid + ","
+ Convert.ToInt32(txtstaffnumber.Text) + ","
+ (cbPosition.SelectedIndex + 1) + ","
+ (cbworkshift.SelectedIndex + 1) + ","
+ (cbSalaryPaymnetType.SelectedIndex + 1) + ",'"
+ dtpWorkStartDate.Value.ToString("yyyy-MM-dd") + "',"
+ (cbEmploymentType.SelectedIndex + 1) +
")");
// positionheld table insert
int qID = 0;
int weID = 0;
if (dgvQualifications.Rows.Count >= 1)
{
foreach (DataGridViewRow row in dgvQualifications.Rows)
{
DB2ResultSet rsqualID = DB.QueryWithResultSet("SELECT MAX(QUALIFICATION_ID) as QUALMAX FROM QUALIFICATION");
while (rsqualID.Read())
{
try
{
qID = Convert.ToInt32(rsqualID["QUALMAX"]) + 1;
}
catch (Exception er)
{
qID = 1;
}
}
DB.Query("INSERT INTO QUALIFICATION VALUES(" + qID + "," + Convert.ToInt32(txtstaffnumber.Text) + ",'" + row.Cells[0].Value.ToString() + "')");
}
}
if (dgvWorkExperience.Rows.Count >= 1)
{
foreach (DataGridViewRow row2 in dgvWorkExperience.Rows)
{
DB2ResultSet rsweID = DB.QueryWithResultSet("SELECT MAX(WORKEXPERIENCE_ID) as WEMAX FROM WORKEXPERIENCE");
while (rsweID.Read())
{
try
{
weID = Convert.ToInt32(rsweID["WEMAX"]) + 1;
}
catch (Exception er)
{
weID = 1;
}
}
DB.Query("INSERT INTO WORKEXPERIENCE VALUES(" + weID + "," + Convert.ToInt32(txtstaffnumber.Text) + ",'" + row2.Cells[0].Value.ToString() + "', '" + row2.Cells[1].Value.ToString() + "', '" + row2.Cells[2].Value.ToString() + "', '" + row2.Cells[3].Value.ToString() + "')");
}
}
dgvQualifications.Rows.Clear();
dgvWorkExperience.Rows.Clear();
emptyAllInputFields();
DB2ResultSet max_staffID = DB.QueryWithResultSet("SELECT MAX(STAFF_NUMBER) as MAXID FROM STAFF");
while (max_staffID.Read())
{
try
{
txtstaffnumber.Text = (Convert.ToInt32(max_staffID["MAXID"]) + 1).ToString();
}
catch (Exception er)
{
txtstaffnumber.Text = "10001";
}
}
}
private void RefreshListofRequesitions()
{
DB = new FinalSanitariumMIS.Helpers.DatabaseHelper();
DB2ResultSet rs = DB.QueryWithResultSet("SELECT * FROM REQUESITION");
}
private void StaffManagement_Load(object sender, EventArgs e)
{
DB2ResultSet ResultSet = DB.QueryWithResultSet("SELECT * FROM staff");
}
