private String getSeconds(EmployeeBadge e)
{
Debug.WriteLine("getSeconds called");
DBConnect db = new DBConnect();
MySqlConnection conn = db.getConnection();
string query = "SELECT * FROM `access` where rfid='" + e.rfid + "'" + " and session_id='" + e.session + "'" + " and type='" + BadgeType.EXIT + "'";
MySqlCommand cmd = new MySqlCommand(query, conn);
MySqlDataReader dataReader = cmd.ExecuteReader();
DateTime d;
string secondExit = "";
string secondEnter = "";
if (!dataReader.Read())
{
conn.Close();
Debug.WriteLine("Entry for {0} {1} {2} not found in 'access' table", e.rfid, e.session, BadgeType.EXIT);
throw new Exception("Not found session or rfid or type wrong.");
}
else
{
d = (DateTime)dataReader["date"];
secondExit = dataReader["time"].ToString();
}
conn.Close();
string datesql = d.Year + "-" + d.Month + "-" + d.Day;
query = "SELECT * FROM `access` where rfid='" + e.rfid + "'" + " and type='" + BadgeType.ENTER + "'" + " and date='" + datesql + "'";
db = new DBConnect();
conn = db.getConnection();
cmd = new MySqlCommand(query, conn);
dataReader = cmd.ExecuteReader();
Double maxNow = 0.0;
int control = 0;
while (dataReader.Read())
{
if (control == 0)
{
control++;
}
if (Double.Parse(dataReader["time"].ToString()) > maxNow)
{
secondEnter = dataReader["time"].ToString();
maxNow = Double.Parse(dataReader["time"].ToString());
}
}
if (control == 0)
{
conn.Close();
throw new Exception("You are trying to cheat the system.");
}
conn.Close();
//compute difference
Double fexit = 0.0, fenter = 0.0;
fexit = Double.Parse(secondExit);
fenter = Double.Parse(secondEnter);
Double doublesecondsOfWork = fexit - fenter;
int secondOfWork = (int)doublesecondsOfWork;
Debug.WriteLine("calculated seconds: " + secondOfWork.ToString());
TimeSpan span = TimeSpan.FromSeconds(secondOfWork);
string s = span.ToString(@"hh\:mm\:ss");
string[] arr = s.Split(":".ToArray());
return(arr[0] + " h " + arr[1] + " min " + arr[2] + " sec ");
}
public ResponseMessageColor faceDetectionAndConcludeSession(EmployeeBadge employee, string name, string surname, bool isentering)
{
DBConnect db = new DBConnect();
MySqlConnection conn = db.getConnection();
string query = "SELECT * FROM colors where color='GREEN' and rfid='" + employee.rfid + "'";
MySqlCommand cmd = new MySqlCommand(query, conn);
MySqlDataReader dataReader = cmd.ExecuteReader();
int count = 0;
while (dataReader.Read())
{
count++;
}
conn.Close();
if (count < 5)
{
if (isentering)
{
return(new ResponseMessageColor(200, "Welcome " + name + " " + surname, "YELLOW"));
}
else
{
return(new ResponseMessageColor(200, "You worked " + getSeconds(employee), "YELLOW"));
}
}
string pathpicture = getPictureUri(employee.rfid, employee.session);
Image <Bgr, byte> receivedImg = new Image <Bgr, byte>(pathpicture);
Image <Gray, byte> normalizedImg = receivedImg.Convert <Gray, Byte>();
Rectangle[] rectangles = classifier.DetectMultiScale(normalizedImg, 1.4, 1, new Size(100, 100), new Size(800, 800));
foreach (Rectangle r in rectangles)
{
receivedImg.Draw(r, new Bgr(Color.Red), 2);
}
receivedImg.Save("photoFaced.jpg");
if (rectangles.Length <= 0)
{
// NEVER REACHED THIS CODE ! ! ! ! !
//put into red folder
/* ChangePicture c = new ChangePicture();
* c.rfid = employee.rfid;
* c.session_id = employee.session;
* c.color = ColorType.RED.ToString();
* changeColor(c);*/
// putSessionPhotoIntoRightFolder(employee, ColorType.RED);
return(new ResponseMessageColor(404, "Never reached this code."));
}
//face recognition
normalizedImg = receivedImg.Copy(rectangles[0]).Convert <Gray, byte>().Resize(64, 64, Emgu.CV.CvEnum.Inter.Cubic);
normalizedImg._EqualizeHist();
Classifier_Train eigenRecog = new Classifier_Train(ROOT + "\\" + employee.rfid + "\\" + ColorType.GREEN.ToString());
string labelName = eigenRecog.Recognize(normalizedImg, greenValue);
//eigenRecog.AddTrainingImage(normalizedImg, "Unknown");
if (labelName == null || labelName == "" || labelName == "Unknown")
{
labelName = eigenRecog.Recognize(normalizedImg, yellowValue);
if (labelName == null || labelName == "" || labelName == "Unknown")
{
//put into red folder
ChangePicture c = new ChangePicture();
c.rfid = employee.rfid;
c.session_id = employee.session;
c.color = ColorType.RED.ToString();
changeColor(c);
//putSessionPhotoIntoRightFolder(employee, ColorType.RED);
if (isentering)
{
return(new ResponseMessageColor(200, "Welcome " + name + " " + surname));
}
else
{
return(new ResponseMessageColor(200, "You worked " + getSeconds(employee).ToString()));
}
}
else
{
//put into yellow folder
ChangePicture c = new ChangePicture();
c.rfid = employee.rfid;
c.session_id = employee.session;
c.color = ColorType.YELLOW.ToString();
changeColor(c);
//putSessionPhotoIntoRightFolder(employee, ColorType.YELLOW);
if (isentering)
{
return(new ResponseMessageColor(200, "Welcome " + name + " " + surname, "YELLOW"));
}
else
{
return(new ResponseMessageColor(200, "You worked " + getSeconds(employee), "YELLOW"));
}
}
}
else
{
//put into green folder
ChangePicture c = new ChangePicture();
c.rfid = employee.rfid;
c.session_id = employee.session;
c.color = ColorType.GREEN.ToString();
changeColor(c);
eigenRecog.AddTrainingImage(normalizedImg, employee.rfid, employee.rfid + ".bmp");
if (isentering)
{
return(new ResponseMessageColor(200, "Welcome " + name + " " + surname, "GREEN"));
}
else
{
return(new ResponseMessageColor(200, "You worked " + getSeconds(employee), "GREEN"));
}
}
}
public ResponseMessageColor enterBadge(EmployeeBadge e)
{
Debug.WriteLine("enterBadge called");
DBConnect db = new DBConnect();
MySqlConnection conn = db.getConnection();
string query = "SELECT * FROM user where rfid='" + e.rfid + "'";
MySqlCommand cmd = new MySqlCommand(query, conn);
MySqlDataReader dataReader = cmd.ExecuteReader();
string name = "none", surname = "none";
if (dataReader.Read())
{
name = dataReader["name"].ToString();
surname = dataReader["surname"].ToString();
Debug.WriteLine("RFID belongs to " + name + " " + surname);
}
else
{
conn.Close();
Debug.WriteLine("Person with that RFID not found");
return(new ResponseMessageColor(201, "Not found RFID."));
}
conn.Close();
db = new DBConnect();
conn = db.getConnection();
query = "SELECT * FROM sessions_user where session_id='" + e.session + "'";
cmd = new MySqlCommand(query, conn);
dataReader = cmd.ExecuteReader();
if (!dataReader.Read())
{
conn.Close();
Debug.WriteLine("Session '" + e.session + "' not found");
return(new ResponseMessageColor(201, "Not found session."));
}
conn.Close();
if (!checkFace(PATHDIR + "\\" + e.session + ".bmp"))
{
File.Delete(PATHDIR + "\\" + e.session + ".bmp");
db = new DBConnect();
conn = db.getConnection();
MySqlCommand cmd3 = new MySqlCommand();
cmd3.Connection = conn;
cmd3.CommandText = "delete from sessions_user where session_id =?a ";
cmd3.Parameters.Add("?a", MySqlDbType.VarChar).Value = e.session;
try
{
cmd3.ExecuteNonQuery();
}
catch (MySqlException ex)
{
Debug.WriteLine("Failed to delete from sessions_user, error code: " + ex.Number);
}
conn.Close();
return(new ResponseMessageColor(404, "Face Not found."));
}
db = new DBConnect();
conn = db.getConnection();
query = "SELECT COUNT(*) FROM access where rfid='" + e.rfid + "'";
cmd = new MySqlCommand(query, conn);
int count = Convert.ToInt32(cmd.ExecuteScalar());
if (count % 2 != 0)
{
conn.Close();
db = new DBConnect();
conn = db.getConnection();
MySqlCommand cmd3 = new MySqlCommand();
cmd3.Connection = conn;
cmd3.CommandText = "delete from sessions_user where session_id =?a ";
cmd3.Parameters.Add("?a", MySqlDbType.VarChar).Value = e.session;
try
{
cmd3.ExecuteNonQuery();
}
catch (MySqlException ex)
{
Debug.WriteLine("Failed to delete from sessions_user, error code: " + ex.Number);
}
conn.Close();
File.Delete(PATHDIR + "\\" + e.session + ".bmp");
return(new ResponseMessageColor(201, "You cannot enter again."));
}
conn.Close();
db = new DBConnect();
conn = db.getConnection();
MySqlCommand cmd2 = new MySqlCommand();
cmd2.Connection = conn;
cmd2.CommandText = "INSERT INTO access(rfid,date,time,type,session_id) VALUES(?a,?b,?c,?d,?e)";
cmd2.Parameters.Add("?a", MySqlDbType.VarChar).Value = e.rfid;
DateTime d = DateTime.Now;
cmd2.Parameters.Add("?b", MySqlDbType.Date).Value = d.Date;
cmd2.Parameters.Add("?c", MySqlDbType.VarChar).Value = (d - DateTime.Today).TotalSeconds;
cmd2.Parameters.Add("?d", MySqlDbType.Enum).Value = BadgeType.ENTER;
cmd2.Parameters.Add("?e", MySqlDbType.LongBlob).Value = e.session;
try
{
cmd2.ExecuteNonQuery();
Debug.WriteLine("INSERT for RFID " + e.rfid + ", date " + d.Date.ToShortDateString() + ", time " + (d - DateTime.Today).TotalSeconds.ToString() + " session_id " + e.session + " type ENTER into access executed");
}
catch (MySqlException ex)
{
Debug.WriteLine("Insertion execution failed, error code: " + ex.Number);
}
conn.Close();
db = new DBConnect();
conn = db.getConnection();
MySqlCommand cmd6 = new MySqlCommand();
cmd6.Connection = conn;
cmd6.CommandText = "delete from sessions_user where session_id =?a ";
cmd6.Parameters.Add("?a", MySqlDbType.VarChar).Value = e.session;
cmd6.ExecuteNonQuery();
conn.Close();
concludeSession(e.rfid, e.session);
db = new DBConnect();
conn = db.getConnection();
cmd2 = new MySqlCommand();
cmd2.Connection = conn;
cmd2.CommandText = "INSERT INTO colors(session_id,rfid,color) VALUES(?a,?b,?c)";
cmd2.Parameters.Add("?a", MySqlDbType.VarChar).Value = e.session;
cmd2.Parameters.Add("?b", MySqlDbType.VarChar).Value = e.rfid;
cmd2.Parameters.Add("?c", MySqlDbType.Enum).Value = ColorType.YELLOW;
try
{
cmd2.ExecuteNonQuery();
}
catch (MySqlException ex)
{
Debug.WriteLine("Insertion execution failed, error code: " + ex.Number);
}
conn.Close();
return(faceDetectionAndConcludeSession(e, name, surname, true));
}
