public void CheckPassword()
{
// 檢查帳密正確性
// 權限設定還沒寫
DBConn.Open();
MySQLCommand DBComm = new MySQLCommand("select * from `RFID_Project`.`user`", DBConn);
MySQLCommand firstCmd = new MySQLCommand("set names big5", DBConn);
firstCmd.ExecuteNonQuery();
MySQLDataReader DBReader = DBComm.ExecuteReaderEx();
bool error = true;
DBReader.Read();
do
{
string id = ("" + DBReader.GetValue(0)); //id
string passoord = ("" + DBReader.GetValue(1)); //password
string enter = System.DateTime.Now.ToString("yyyy/MM/dd/ HH:mm:ss");
if (tB_Id.Text == id && tB_Password.Text == passoord)
{
error = false;
MySQLCommand DBCom = new MySQLCommand("INSERT INTO `RFID_Project`.`login_record` (`date`,`id`,`result`)VALUES ('" + enter + "','" + tB_Id.Text + "','" + "success" + "');", DBConn); //登錄記錄
MySQLDataReader DBReader1 = DBCom.ExecuteReaderEx();
this.DialogResult = DialogResult.OK;
this.Close();
}
} while (DBReader.Read());
if (error)
{
MessageBox.Show("登錄檔作業失敗!! " + "\r\n" + "帳號或密碼有錯!請再確認");
}
DBConn.Close();
}
private void sure_Click(object sender, EventArgs e)
{
DBConn.Open();
MySQLCommand DBComm = new MySQLCommand("select * from `parkingsystem`.`manager`", DBConn);
MySQLCommand firstCmd = new MySQLCommand("set names big5", DBConn);
firstCmd.ExecuteNonQuery();
MySQLDataReader DBReader = DBComm.ExecuteReaderEx();
bool error = true;
DBReader.Read();
do
{
string id = ("" + DBReader.GetValue(0)); //id
string passoord = ("" + DBReader.GetValue(2)); //password
string enter = System.DateTime.Now.ToString("yyyy年MM月dd日 HH:mm:ss");
if (tB_id.Text == id && tB_password.Text == passoord)
{
error = false;
MySQLCommand DBCom = new MySQLCommand("INSERT INTO `parkingsystem`.`login_record` (`date`,`id`,`result`)VALUES ('" + enter + "','" + tB_id.Text + "','" + "帳密正確" + "');", DBConn);
MySQLDataReader DBReader1 = DBCom.ExecuteReaderEx();
this.Close();
}
} while (DBReader.Read());
if (error)
{
MessageBox.Show("登錄檔作業失敗!! =" + "\r\n" + "帳號或密碼有錯!請再確認");
}
}
protected Dictionary <object, T> queryForDictionary(string sql, string keyName)
{
MySQLConnection conn = null;
Dictionary <object, T> objDictionary = null;
try
{
conn = this.dbMgr.DBConns.PopDBConnection();
MySQLCommand cmd = new MySQLCommand(sql, conn);
MySQLDataReader reader = cmd.ExecuteReaderEx();
int columnNum = reader.FieldCount;
objDictionary = new Dictionary <object, T>();
string[] nameArray = new string[columnNum];
while (reader.Read())
{
int index = 0;
T obj = Activator.CreateInstance <T>();
object key = null;
for (int i = 0; i < columnNum; i++)
{
int _index = index++;
if (null == nameArray[_index])
{
nameArray[_index] = reader.GetName(_index);
}
string columnName = nameArray[_index];
object columnValue = reader.GetValue(_index);
this.setValue(obj, columnName, columnValue);
if (null != key)
{
if (keyName.Equals(reader.GetName(_index)) || keyName == reader.GetName(_index))
{
key = reader.GetValue(_index);
}
}
}
if (null != key)
{
objDictionary.Add(key, obj);
}
}
GameDBManager.SystemServerSQLEvents.AddEvent(string.Format("+SQL: {0}", sql), EventLevels.Important);
cmd.Dispose();
}
catch (Exception)
{
LogManager.WriteLog(LogTypes.Error, string.Format("查询数据库失败: {0}", sql), null, true);
return(null);
}
finally
{
if (null != conn)
{
this.dbMgr.DBConns.PushDBConnection(conn);
}
}
return(objDictionary);
}
/// <summary>
/// 查询返回多个实例
/// </summary>
/// <param name="sql">sql语句</param>
/// <returns>List<T>形式查询结果集s</returns>
protected List <T> queryForList(string sql)
{
MySQLConnection conn = null;
List <T> list = null;
try
{
conn = dbMgr.DBConns.PopDBConnection();
MySQLCommand cmd = new MySQLCommand(sql, conn);
MySQLDataReader reader = cmd.ExecuteReaderEx();
int columnNum = reader.FieldCount;
//list = new List<T>();
while (reader.Read())
{
//索引下标
int index = 0;
T obj = Activator.CreateInstance <T>();
for (int i = 0; i < columnNum; i++)
{
int _index = index++;
string columnName = reader.GetName(_index);
Object columnValue = reader.GetValue(_index);
if (null == list)
{
list = new List <T>();
}
setValue(obj, columnName, columnValue);
}
list.Add(obj);
}
GameDBManager.SystemServerSQLEvents.AddEvent(string.Format("+SQL: {0}", sql), EventLevels.Important);
cmd.Dispose();
cmd = null;
}
catch (Exception e)
{
LogManager.WriteLog(LogTypes.Error, string.Format("查询数据库失败: {0},exception:{1}", sql, e));
return(null);
}
finally
{
if (null != conn)
{
dbMgr.DBConns.PushDBConnection(conn);
}
}
return(list);
}
public void Save_recoder()
{
DBConn.Open();
MySQLCommand DBComm = new MySQLCommand("select * from `parkingsystem`.`record`", DBConn);
MySQLCommand firstCmd = new MySQLCommand("set names big5", DBConn);
firstCmd.ExecuteNonQuery();
MySQLDataReader DBReader = DBComm.ExecuteReaderEx();
DBReader.Read();
bool s = true;
do
{
string compareID = ("" + DBReader.GetValue(0)); //Tad_ID
string state = ("" + DBReader.GetValue(2)); //目前狀態
if (compareID == textBox1.Text)
{
if (state == "P") //要出去
{
string Etime = ("" + DBReader.GetValue(3)); //進入時間
UPdata(compareID, Etime);
s = false;
break;
}
else if (state == "E")//正在進入
{
s = false;
break;
}
else if (state == "")//正在出去
{
s = false;
break;
}
}
} while (DBReader.Read());
if (s)
{
save(); //要進來
}
DBConn.Close();
}
public bool Check()
{
DBConn.Open();
MySQLCommand DBComm = new MySQLCommand("select * from `RFID_Project`.`user`", DBConn);
MySQLCommand firstCmd = new MySQLCommand("set names big5", DBConn);
firstCmd.ExecuteNonQuery();
MySQLDataReader DBReader = DBComm.ExecuteReaderEx();
DBReader.Read();
do
{
string id = ("" + DBReader.GetValue(0)); //id
string passoord = ("" + DBReader.GetValue(1)); //password
if (tB_Id.Text == id)
{
if (tB_OldPW.Text == passoord) // 檢查舊密碼
{
if (tB_NewPW.Text == tB_NewRPW.Text && tB_NewPW.Text != "" && tB_NewRPW.Text != "") // 新密碼兩欄位是否相同
{
//更新資料庫
MySQLCommand DBCom = new MySQLCommand("UPDATE `RFID_Project`.`user` SET `password` = '" + tB_NewPW.Text + "' WHERE CONVERT( `user`.`id` USING utf8 ) = '" + DBReader.GetString(0) + "' LIMIT 1 ;", DBConn);
MySQLDataReader DBReader1 = DBCom.ExecuteReaderEx();
return(true);
}
}
}
}while (DBReader.Read());
if (tB_NewPW.Text.Length <= 4)
{
MessageBox.Show("密碼長度需大於4個字元");
return(false);
}
else
{
MessageBox.Show("欄位有錯 ! 請再檢查一次");
}
return(false);
}
private void timer3_Tick(object sender, EventArgs e) //存入歷史記錄
{
DBConn.Open();
MySQLCommand DBComm = new MySQLCommand("select * from `parkingsystem`.`record`", DBConn);
MySQLCommand firstCmd = new MySQLCommand("set names big5", DBConn);
firstCmd.ExecuteNonQuery();
MySQLDataReader DBReader = DBComm.ExecuteReaderEx();
DBReader.Read();
do
{
string time = ("" + DBReader.GetValue(3)); //key
string state = ("" + DBReader.GetValue(2)); //目前狀態
if (state == "")
{
MySQLCommand DBCom = new MySQLCommand("UPDATE `parkingsystem`.`record` SET `state` = 'S' WHERE CONVERT( `record`.`Etime` USING utf8 ) = '" + time + "' LIMIT 1 ;", DBConn);
MySQLDataReader DBReader1 = DBCom.ExecuteReaderEx();
}
} while (DBReader.Read());
timer3.Enabled = false;
}
public void Save_state()
{
DBConn.Open();
MySQLCommand DBComm = new MySQLCommand("select * from `parkingsystem`.`record`", DBConn);
MySQLCommand firstCmd = new MySQLCommand("set names big5", DBConn);
firstCmd.ExecuteNonQuery();
MySQLDataReader DBReader = DBComm.ExecuteReaderEx();
DBReader.Read();
do
{
string time = ("" + DBReader.GetValue(3)); //key
string state = ("" + DBReader.GetValue(2)); //目前狀態
if (state == "E")
{
MySQLCommand DBCom = new MySQLCommand("UPDATE `parkingsystem`.`record` SET `state` = 'P' WHERE CONVERT( `record`.`Etime` USING utf8 ) = '" + time + "' LIMIT 1 ;", DBConn);
MySQLDataReader DBReader1 = DBCom.ExecuteReaderEx();
}
} while (DBReader.Read());
DBConn.Close();
}
protected T queryForObject(string sql)
{
MySQLConnection conn = null;
T obj = default(T);
try
{
conn = this.dbMgr.DBConns.PopDBConnection();
MySQLCommand cmd = new MySQLCommand(sql, conn);
MySQLDataReader reader = cmd.ExecuteReaderEx();
int columnNum = reader.FieldCount;
if (reader.Read())
{
int index = 0;
for (int i = 0; i < columnNum; i++)
{
int _index = index++;
string columnName = reader.GetName(_index);
object columnValue = reader.GetValue(_index);
if (null == obj)
{
obj = Activator.CreateInstance <T>();
}
this.setValue(obj, columnName, columnValue);
}
}
GameDBManager.SystemServerSQLEvents.AddEvent(string.Format("+SQL: {0}", sql), EventLevels.Important);
cmd.Dispose();
}
catch (Exception)
{
LogManager.WriteLog(LogTypes.Error, string.Format("查询数据库失败: {0}", sql), null, true);
return(default(T));
}
finally
{
if (null != conn)
{
this.dbMgr.DBConns.PushDBConnection(conn);
}
}
return(obj);
}
public UserControl_Monitor()
{
InitializeComponent();
DBConn = new MySQLConnection(new MySQLConnectionString("140.134.208.84", "RFID_Project", "root", "FCUIECS", 3306).AsString); //連資料庫
DBConn.Open();
Global.database = true;
dataGridView1.AllowUserToAddRows = false;
close.Enabled = false; //一開始"關閉"的按鈕是不能按的
tSB_On.Enabled = false; //一開始"開始偵測"的按鈕是不能按的
tSB_Off.Enabled = false; //一開始"關閉偵測"的按鈕是不能按的
try
{
MySQLCommand DBComm = new MySQLCommand("select no from `RFID_Project`.`event_record` order by no desc", DBConn);
MySQLDataReader DBReader = DBComm.ExecuteReaderEx();
DBReader.Read();
event_no = Convert.ToInt32(DBReader.GetValue(0)) + 1;
}
catch
{
event_no = 1;
}
}
private void timer2_Tick(object sender, EventArgs e) //持續偵測
{
string Tag = "";
DBConn.Open();
MySQLCommand DBComm = new MySQLCommand("select * from `parkingsystem`.`member`", DBConn);
MySQLCommand firstCmd = new MySQLCommand("set names big5", DBConn);
firstCmd.ExecuteNonQuery();
request("T" + (char)13);
delaytime(200);
String result = response();
String result1 = response1();
String result2 = response2();
string sPattern = "[\\da-fA-F]{4} [\\da-fA-F]{4} [\\da-fA-F]{4} [\\da-fA-F]{4} [\\da-fA-F]{4} [\\da-fA-F]{4}";
MatchCollection matchs = Regex.Matches(result, sPattern, RegexOptions.IgnoreCase); //抓一大段其中的一段字串
MatchCollection matchs1 = Regex.Matches(result1, sPattern, RegexOptions.IgnoreCase);
MatchCollection matchs2 = Regex.Matches(result2, sPattern, RegexOptions.IgnoreCase);
MySQLDataReader DBReader = DBComm.ExecuteReaderEx();
foreach (Match m in matchs)
{
Tag = m.Value;
}
int F = 0;
foreach (Match m in matchs1) //1F
{
if (m.Value != "")
{
F = F + 1; //掃到
}
DBReader.Read();
do
{
Save_state();
} while (DBReader.Read());
}
tB_1F.Text = ("" + F); //message
if (F >= 25)
{
state_1F.Text = "車位已滿";
}
F = 0;
foreach (Match m in matchs2) //2F
{
if (m.Value != "")
{
F = F + 1; //掃到
}
DBReader.Read();
do
{
Save_state();
} while (DBReader.Read());
}
tB_2F.Text = ("" + F);
if (F >= 25)
{
state_2F.Text = "車位已滿";
}
tSSL_state.Text = "偵測中...";
if (Tag != "")
{ //掃到
textBox1.Text = Tag;
try
{
while (DBReader.Read())
{
string tagstr = DBReader.GetString(1);
if (textBox1.Text == tagstr)
{
textBox2.Text = ("" + DBReader.GetValue(2)); //MID
textBox3.Text = ("" + DBReader.GetValue(3)); //carID
Save_recoder();
break;
}
}
}
finally //釋放資源
{
DBReader.Close();
}
DBConn.Close();
} //if
}
public void receive(object o)
{
ReaderConnect r = o as ReaderConnect;
String recData;
Boolean repeat = false;
while (r.client.Connected)
{
if (r.stream.CanRead)
{
try
{
Byte[] data = new Byte[256];
Int32 bytes = r.stream.Read(data, 0, data.Length);
if (!r.stream.DataAvailable)
{
recData = System.Text.Encoding.ASCII.GetString(data, 0, bytes);
if (recData.Length == 23)
{
this.Invoke(new UpdateMessage(printMessage), new object[] { recData });
//string test = "溫度: " + recData.Substring(18,3);
//this.Invoke(new UpdateMessage(printMessage), new object[] { test });
//this.Invoke(new UpdateMessage(printMessage), new object[] { "temp_up: " + Global.temp_up + " temp_down: " + Global.temp_down }); //test體溫上下限
if (Convert.ToInt32(recData.Substring(18, 3)) > Global.temp_up || Convert.ToInt32(recData.Substring(18, 3)) < Global.temp_down)
{
Global.BeepOn = true;
Global.alarm_voice(Global.BeepOn); //蜂鳴聲
switch (recData.Substring(1, 6)) //判斷為何地點
{
case "000011": //中央噴水池
pB1.BackColor = Color.Red;
happen[0] = true;
break;
case "000022": //208教室
pB3.BackColor = Color.Red;
happen[1] = true;
break;
case "000033": //停車場
pB10.BackColor = Color.Red;
happen[2] = true;
break;
}
//讀取資料庫的資料 找學生
MySQLCommand DBComm = new MySQLCommand("select * from `RFID_Project`.`student`", DBConn);
MySQLCommand firstCmd = new MySQLCommand("set names big5", DBConn);
firstCmd.ExecuteNonQuery();
MySQLDataReader DBReader = DBComm.ExecuteReaderEx();
DBReader.Read();
do
{
string id = DBReader.GetValue(1).ToString();
//this.Invoke(new UpdateMessage(printMessage), new object[] { " id= " + id });
//this.Invoke(new UpdateMessage(printMessage), new object[] { " recData.Substring(9, 6)= " + recData.Substring(9, 6) });
if (recData.Substring(9, 6) == id)
{
record = recData; //map專用訊息
//讀取資料庫的資料 找地點
MySQLCommand DBComm_reader = new MySQLCommand("select * from `RFID_Project`.`reader`", DBConn);
MySQLDataReader DBReader_reader = DBComm_reader.ExecuteReaderEx();
DBReader_reader.Read();
do
{
string reader_id = DBReader_reader.GetValue(0).ToString();
if (recData.Substring(1, 6) == reader_id)
{
string date = System.DateTime.Now.ToString("yyyy/MM/dd");
string time = System.DateTime.Now.ToString("HH:mm:ss");
string temp = recData.Substring(18, 2) + "." + recData.Substring(20, 1);
string teacher = "";
//抓老師資料
MySQLCommand DBComT = new MySQLCommand("select * from `RFID_Project`.`teacher`", DBConn);
MySQLDataReader DBReader_teacher = DBComT.ExecuteReaderEx();
DBReader_teacher.Read();
do
{
if (DBReader_teacher.GetString(0) == DBReader.GetString(3))
{
teacher = DBReader_teacher.GetString(1);
}
} while (DBReader_teacher.Read());
//判斷事件是否重複
try
{
MySQLCommand DBComRecord = new MySQLCommand("select * from `RFID_Project`.`event_record`", DBConn);
MySQLDataReader DBReader_Record = DBComRecord.ExecuteReaderEx();
DBReader_Record.Read();
do
{
MessageBox.Show(DBReader.GetString(0) + " / " + DBReader_Record.GetString(3));
if (date == DBReader_Record.GetString(1) && DBReader.GetString(0) == DBReader_Record.GetString(3) && DBReader_reader.GetString(1) == DBReader_Record.GetString(5))
{
repeat = true;
break;
}
else
{
repeat = false;
}
} while (DBReader_Record.Read());
}
catch
{
repeat = false;
}
if (repeat == false)
{
this.Invoke(new UpdateDatagridview(printDatagridview), new object[] { date, time, temp, DBReader.GetValue(0), DBReader.GetValue(2), DBReader_reader.GetValue(1), teacher });
Global.event_count++;
this.Invoke(new UpdateCount(printCount), new object[] { });
//記錄至資料庫. 要按處理鍵.結果才會改成y
MySQLCommand DBComm_DB = new MySQLCommand("INSERT INTO `RFID_Project`.`event_record` (`no`,`date`,`time`,`stu_id`,`stu_name`,`place`,`temp`,`result`)VALUES ('" + event_no + "','" + date + "','" + time + "','" + DBReader.GetValue(0) + "','" + DBReader.GetValue(2) + "','" + DBReader_reader.GetValue(1) + "','" + recData.Substring(18, 3) + "','" + "n" + "');", DBConn); //登錄記錄
MySQLDataReader DBReader_DB = DBComm_DB.ExecuteReaderEx();
event_no++;
}
Thread.Sleep(0);
break;
}
} while (DBReader_reader.Read());
}
} while (DBReader.Read());
}
}
else
{
this.Invoke(new UpdateMessage(printMessage), new object[] { recData });
}
}
else
{
Thread.Sleep(0);
}
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
break;
}
}
}
}