private double GetCheckinScore(string tech_id, string reg_id)
{
MySQLDatabase db = new MySQLDatabase();
db.DBServer = _db.DBServer;
db.DBUser = _db.DBUser;
db.DBPassword = _db.DBPassword;
db.DBName = _db.DBName;
db.Connect();
// Calculate checkin's score, the fomular is (15/10) = (20/x)
double checkinScore = 0; // This will calculate later.
// Get a number of checkin's dates
int countChkDate = 0;
db.SQLCommand = "SELECT COUNT(date) FROM checkin_date WHERE tech_id='" + tech_id + "'";
db.Query();
if (db.Result.Read())
{
countChkDate = Convert.ToInt16(db.Result.GetValue(0));
}
else
{
countChkDate = 0;
}
// Get setting checkin score
double settingChkinScore = 0;
db.SQLCommand = "SELECT checkin FROM score_rating WHERE tech_id='" + tech_id + "'";
db.Query();
if (db.Result.Read())
{
settingChkinScore = Convert.ToInt16(db.Result["checkin"]);
}
else
{
settingChkinScore = 0;
}
// Get a number of checkin
double countChkNormal = 0;
db.SQLCommand = "SELECT COUNT(reg_id) FROM checkin WHERE reg_id='" + reg_id + "' AND status IN ('normal', 'absence')";
db.Query();
if (db.Result.Read())
{
countChkNormal = Convert.ToInt16(db.Result.GetValue(0));
}
else
{
countChkNormal = 0;
}
double countChkLate = 0;
db.SQLCommand = "SELECT COUNT(reg_id) FROM checkin WHERE reg_id='" + reg_id + "' AND status='late'";
db.Query();
if (db.Result.Read())
{
countChkLate = Convert.ToInt16(db.Result.GetValue(0)) * 0.5;
}
else
{
countChkNormal = 0;
}
db.Close();
// Then calculate the score
if (countChkDate != 0)
{
checkinScore = (settingChkinScore * (countChkNormal + countChkLate)) / countChkDate;
}
return checkinScore;
}
private void btnGenOPD_Click(object sender, EventArgs e)
{
try
{
txtVNOPD.Text = "";
cmbVSDateOPD.Items.Clear();
m_map.Clear();
if (m_db != null && m_db.IsConnect())
{
// Get HN from ipt table.
m_db.SQLCommand = "SELECT * FROM ovst WHERE hn='" + txtHNOPD.Text + "' ORDER BY vstdate DESC, vsttime DESC;";
if (m_db.Query() == false)
{
MessageBox.Show("There is no data for hn = " + txtHNOPD.Text + ". Please check.");
m_db.Result.Close();
// Clear
chkBlankOPD.Enabled = false;
dgViewOPD.Rows.Clear();
cmbVSDateOPD.Text = "";
pictureBoxOPD.Image = null;
return;
}
// Fill data in combo box
MySQLDatabase db = new MySQLDatabase();
db.DBServer = m_server;
db.DBUser = m_username;
db.DBPassword = m_password;
db.DBName = m_dbName;
db.Connect();
db.SQLCommand = "USE " + m_dbName + ";";
db.Query();
db.Result.Close();
string vstdate = null;
string vsttime = null;
string time = null;
string vn = null;
string hn = null;
string fname = null;
string lname = null;
while (m_db.Result.Read())
{
// Retrieve ovst's information
vn = (string)m_db.Result["vn"];
hn = (string)m_db.Result["hn"];
vstdate = ((DateTime)m_db.Result["vstdate"]).ToString("dd-MM-yyyy");
vsttime = (m_db.Result["vsttime"]).ToString();
// Retrieve patient's information
db.SQLCommand = "SELECT * FROM patient WHERE hn='" + hn + "';";
db.Query();
db.Result.Read();
fname = (string)db.Result["fname"];
lname = (string)db.Result["lname"];
db.Result.Close();
// Build timestamp
time = vstdate + " " + vsttime;
// Add item to combo box
cmbVSDateOPD.Items.Add(time);
// Add to map
m_map.Add(time, new OVST { vn = vn, hn = hn, fname = fname, lname = lname });
}
cmbVSDateOPD.SelectedIndex = 0;
m_db.Result.Close();
db.Close();
// Disable checkbox if no item to be printed.
if (m_map.Count == 0)
{
chkBlankOPD.Enabled = false;
}
else
{
chkBlankOPD.Enabled = true;
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private bool Check80OfCheckin(string tech_id, string reg_id)
{
// Get Checkin Count.
MySQLDatabase db = new MySQLDatabase();
db.DBServer = _db.DBServer;
db.DBUser = _db.DBUser;
db.DBPassword = _db.DBPassword;
db.DBName = _db.DBName;
db.Connect();
// Calculate checkin's score, the fomular is (15/10) = (20/x)
double checkinScore = 0; // This will calculate later.
// Get a number of checkin's dates
int countChkDate = 0;
db.SQLCommand = "SELECT COUNT(date) FROM checkin_date WHERE tech_id='" + tech_id + "'";
db.Query();
if (db.Result.Read())
{
countChkDate = Convert.ToInt16(db.Result.GetValue(0));
}
else
{
countChkDate = 0;
}
// Get a number of checkins.
int countChk = 0;
db.SQLCommand = "SELECT COUNT(reg_id) FROM checkin WHERE reg_id='" + reg_id + "'";
db.Query();
if (db.Result.Read())
{
countChk = Convert.ToInt16(db.Result.GetValue(0));
}
else
{
countChk = 0;
}
db.Close();
// Calculate
int countNeedCheckin = (80 * countChkDate) / 100;
if (countChk >= countNeedCheckin)
{
return false;
}
return true;
}
private void LoadRegisterToDGV(string sqlCmd)
{
AddColumnForStudentDGV();
// Clear DGV
dgvStudent.Rows.Clear();
// Query all teacher.
_db.SQLCommand = sqlCmd;
_db.Query();
if (_db.Result.HasRows == false)
{
//MessageBox.Show("ไม่มีรายการที่ต้องการแสดง", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
//return;
}
// Insert rows to DGV
int order = 0;
while (_db.Result.Read())
{
order++;
if ((string)_db.Result.GetValue(2) != "")
{
dgvStudent.Rows.Add(
order.ToString(),
_db.Result["std_id"],
_db.Result["std_name"],
"scaned",
_db.Result["reg_id"],
LoadImage("finger2.png")
);
}
else
{
dgvStudent.Rows.Add(
order.ToString(),
_db.Result["std_id"],
_db.Result["std_name"],
"noscan",
_db.Result["reg_id"],
LoadImage("nofinger2.png")
);
}
}
int tech_id = (int)dgvSubject.CurrentRow.Cells["TechID"].Value;
// Load others groups
_db.SQLCommand = "SELECT * FROM checkin_date WHERE tech_id='" + tech_id + "' ORDER BY date";
_db.Query();
if (_db.Result.HasRows == true)
{
while (_db.Result.Read() == true)
{
// Image column
DataGridViewImageColumn imgCol = new DataGridViewImageColumn();
imgCol.HeaderText = _db.Result["date"] as string;
imgCol.Width = 70;
int idx = dgvStudent.Columns.Add(imgCol);
// Status column
DataGridViewTextBoxColumn statusCol = new DataGridViewTextBoxColumn();
statusCol.HeaderText = _db.Result["date"] as string;
statusCol.Width = 70;
statusCol.Visible = false; // Status column
int idxStatus = dgvStudent.Columns.Add(statusCol);
MySQLDatabase localDB = new MySQLDatabase();
localDB.DBUser = _db.DBUser;
localDB.DBPassword = _db.DBPassword;
localDB.DBServer = _db.DBServer;
localDB.DBName = _db.DBName;
localDB.Connect();
foreach (DataGridViewRow item in dgvStudent.Rows)
{
string regID = Convert.ToString(item.Cells[4].Value); // TechID
string hisDate = imgCol.HeaderText;
// Must use a new db connection.
localDB.SQLCommand = "SELECT * FROM checkin WHERE date='" + hisDate + "' AND reg_id='" + regID + "'";
localDB.Query();
if (localDB.Result.HasRows == true)
{
localDB.Result.Read();
if (localDB.Result["status"] as string == "normal")
{
item.Cells[idx].Value = LoadImage("normal.png");
item.Cells[idxStatus].Value = "normal";
}
else if (localDB.Result["status"] as string == "absence")
{
item.Cells[idx].Value = LoadImage("absence.png");
item.Cells[idxStatus].Value = "absence";
}
else
{
item.Cells[idx].Value = LoadImage("late.png");
item.Cells[idxStatus].Value = "late";
}
}
else
{
item.Cells[idx].Value = LoadImage("uncheck.png");
item.Cells[idxStatus].Value = "uncheck";
}
}
localDB.Close();
}
}
}
private bool IsForce80Checkin(string tech_id)
{
MySQLDatabase db = new MySQLDatabase();
db.DBServer = _db.DBServer;
db.DBUser = _db.DBUser;
db.DBPassword = _db.DBPassword;
db.DBName = _db.DBName;
db.Connect();
db.SQLCommand = "SELECT force_f_checkin FROM score_rating";
db.Query();
bool ret = false;
if (db.Result.Read())
{
if ((int)db.Result["force_f_checkin"] == 1)
{
ret = true;
}
}
db.Close();
return ret;
}
private double GetLabScore(string stdID)
{
double labScore = 0.0;
MySQLDatabase localDB = new MySQLDatabase();
localDB.DBServer = _db.DBServer;
localDB.DBName = _db.DBName;
localDB.DBUser = _db.DBUser;
localDB.DBPassword = _db.DBPassword;
localDB.Connect();
// Get Lab Subject of Lec Subject
localDB.SQLCommand = "SELECT * FROM subject WHERE sub_lec='" + dgvSubject.CurrentRow.Cells["SubLec"].Value as string + "' AND sub_lab<>''";
localDB.Query();
while (localDB.Result.Read())
{
// STD + Lab + Year Sub ID, look up in registration
MySQLDatabase regDB = new MySQLDatabase();
regDB.DBServer = _db.DBServer;
regDB.DBName = _db.DBName;
regDB.DBUser = _db.DBUser;
regDB.DBPassword = _db.DBPassword;
regDB.Connect();
regDB.SQLCommand = "SELECT * FROM registration WHERE sub_id='" + (int)localDB.Result["id"] + "' AND std_id='" + stdID + "' AND year='" + dgvSubject.CurrentRow.Cells["SubYear"].Value + "'";
regDB.Query();
while (regDB.Result.Read())
{
// REGID look up in score
int regid = (int)regDB.Result["reg_id"];
MySQLDatabase scoreDB = new MySQLDatabase();
scoreDB.DBServer = _db.DBServer;
scoreDB.DBName = _db.DBName;
scoreDB.DBUser = _db.DBUser;
scoreDB.DBPassword = _db.DBPassword;
scoreDB.Connect();
scoreDB.SQLCommand = "SELECT * FROM score WHERE reg_id='" + regid + "'";
scoreDB.Query();
// A = Sum all score
double all_score = 0;
if (scoreDB.Result.Read())
{
all_score = (int)scoreDB.Result["mid"] + (int)scoreDB.Result["final"] + (int)scoreDB.Result["score1"] +
(int)scoreDB.Result["score2"] + (int)scoreDB.Result["score3"] + (int)scoreDB.Result["score4"] +
(int)scoreDB.Result["score5"];
// TechID + SubID + Year, look up in teaching
MySQLDatabase techingDB = new MySQLDatabase();
techingDB.DBServer = _db.DBServer;
techingDB.DBName = _db.DBName;
techingDB.DBUser = _db.DBUser;
techingDB.DBPassword = _db.DBPassword;
techingDB.Connect();
techingDB.SQLCommand = "SELECT * FROM teaching WHERE tech_id='" + TechID + "' AND sub_id='" + localDB.Result["id"].ToString() + "' AND year='" + dgvSubject.CurrentRow.Cells["SubYear"].Value + "'";
techingDB.Query();
techingDB.Result.Read();
all_score += GetCheckinScore(techingDB.Result["id"].ToString(), regid.ToString());
techingDB.Close();
}
// B = Get Lab's score from score_rating
scoreDB.SQLCommand = "SELECT * FROM score_rating WHERE tech_id='" + dgvSubject.CurrentRow.Cells["TechID"].Value + "'";
scoreDB.Query();
double scoreLab = 0;
if (scoreDB.Result.Read())
{
scoreLab = (int)scoreDB.Result["score_lab"];
}
// Real score = (A * B)/100
labScore = (all_score * scoreLab) / 100;
scoreDB.Close();
}
regDB.Close();
}
localDB.Close();
return labScore;
}
private string GetGradeFromScore(double score)
{
MySQLDatabase db = new MySQLDatabase();
db.DBServer = _db.DBServer;
db.DBUser = _db.DBUser;
db.DBPassword = _db.DBPassword;
db.DBName = _db.DBName;
db.Connect();
string grade = "";
_db.SQLCommand = "SELECT a, bp, b, cp, c, dp, d, f, score_type FROM score_rating WHERE tech_id='" + dgvSubject.CurrentRow.Cells["TechID"].Value + "'";
_db.Query();
if (_db.Result.Read())
{
if (score >= Convert.ToInt16(_db.Result["a"]))
{
grade = "A";
}
else if (score >= Convert.ToInt16(_db.Result["bp"]))
{
grade = "B+";
}
else if (score >= Convert.ToInt16(_db.Result["b"]))
{
grade = "B";
}
else if (score >= Convert.ToInt16(_db.Result["cp"]))
{
grade = "C+";
}
else if (score >= Convert.ToInt16(_db.Result["c"]))
{
grade = "C";
}
else if (score >= Convert.ToInt16(_db.Result["dp"]))
{
grade = "D+";
}
else if (score >= Convert.ToInt16(_db.Result["f"]))
{
if (_db.Result["score_type"].ToString() == "grade")
{
if (score >= Convert.ToInt16(_db.Result["d"]))
{
grade = "D";
}
else
{
grade = "F";
}
}
else
{
grade = "D";
}
}
else
{
grade = "F";
}
}
else
{
grade = "F";
}
db.Close();
return grade;
}
