public string ToClass(string str)
{
string flag = "";
MySQLConnection DBConn;
DBConn = new MySQLConnection(new MySQLConnectionString(Form2.Ip, Form2.KuName, Form2.Username, Form2.Password, Form2.Port).AsString);
DBConn.Open();
MySQLCommand DBComm = new MySQLCommand("select CLASS from config", DBConn);
MySQLDataReader DBReader = DBComm.ExecuteReaderEx();
while (DBReader.Read())
{
flag = DBReader.GetString(0);
DBComm = new MySQLCommand("select keyword from config where CLASS='" + DBReader.GetString(0) + "'", DBConn);
MySQLDataReader DBReader1 = DBComm.ExecuteReaderEx();
if (DBReader1.Read())
{
if (str.Contains(DBReader1.GetString(0)))
{
flag = DBReader.GetString(0);
DBConn.Close();
return(flag);
}
}
}
DBConn.Close();
return(flag);
}
private void timer5_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();
bool s = true;
while (DBReader.Read())
{
if (DBReader.GetString(2) == "E" || DBReader.GetString(2) == "")
{
tSSL_C.Text = "開啟";
s = false;
break;
}
}
if (s)
{
tSSL_C.Text = "關閉";
}
DBConn.Close();
}
public void Find()
{
//在異常事件記錄抓未處理資料出來
DBConn.Open();
MySQLCommand DBComm = new MySQLCommand("select * from `RFID_Project`.`event_record`", DBConn);
MySQLCommand firstCmd = new MySQLCommand("set names big5", DBConn);
firstCmd.ExecuteNonQuery();
MySQLDataReader DBReader = DBComm.ExecuteReaderEx();
while (DBReader.Read())
{
if (DBReader.GetString(5) == Global.event_place && DBReader.GetString(3) == Global.event_stu && DBReader.GetString(7) == "n") //依位置判斷未處理的異常事件
{
tB_place.Text = DBReader.GetString(5);
tB_temp.Text = DBReader.GetString(6);
tB_date.Text = DBReader.GetString(1);
tB_time.Text = DBReader.GetString(2);
Time = DBReader.GetString(2);
//抓學生資料
string stu_id = DBReader.GetString(3);
MySQLCommand DBCom = new MySQLCommand("select * from `RFID_Project`.`student`", DBConn);
MySQLDataReader DBReader_stu = DBCom.ExecuteReaderEx();
DBReader_stu.Read();
do
{
if (DBReader_stu.GetString(0) == stu_id)
{
tB_id.Text = DBReader_stu.GetString(0);
tB_name.Text = DBReader_stu.GetString(2);
tB_sex.Text = DBReader_stu.GetString(5);
tB_tagId.Text = DBReader_stu.GetString(1);
tB_class.Text = DBReader_stu.GetString(6);
tB_CWHO.Text = DBReader_stu.GetString(8);
tB_CTEL.Text = DBReader_stu.GetString(9);
tB_address.Text = DBReader_stu.GetString(7);
pB_photo.ImageLocation = DBReader_stu.GetString(4); //圖片
//抓老師資料
MySQLCommand DBComT = new MySQLCommand("select * from `RFID_Project`.`teacher`", DBConn);
MySQLDataReader DBReader_teacher = DBComT.ExecuteReaderEx();
DBReader_teacher.Read();
do
{
if (DBReader_teacher.GetString(2) == DBReader_stu.GetString(6))
{
tB_teacher.Text = DBReader_teacher.GetString(1);
tB_tTEL.Text = DBReader_teacher.GetString(4);
}
} while (DBReader_teacher.Read());
}
} while (DBReader_stu.Read());
break;
}
}
}
/*public int ConMysql()
* {
* MySQLConnection DBConn;
* DBConn = new MySQLConnection(new MySQLConnectionString(Form2.Ip, Form2.KuName, Form2.Username, Form2.Password, Form2.Port).AsString);
* DBConn.Open();
* return 0;
* }
*
* public int CloseMysql(MySQLConnection DBConn)
* {
* DBConn.Close();
* return 0;
* }*/
public static int Joinclass()
{
if (Form3.key != "")
{
MySQLConnection DBConn;
DBConn = new MySQLConnection(new MySQLConnectionString(Form2.Ip, Form2.KuName, Form2.Username, Form2.Password, Form2.Port).AsString);
DBConn.Open();
MySQLCommand DBComm = new MySQLCommand("select CLASS from config where CLASS='" + Form3.cla + "'", DBConn);
MySQLDataReader DBReader = DBComm.ExecuteReaderEx();
if (!DBReader.Read())
{
DBComm = new MySQLCommand("insert into config(CLASS) values('" + Form3.cla + "')", DBConn);
DBComm.ExecuteNonQuery();
}
else if (Form3.cla != DBReader.GetString(0))
{
DBComm = new MySQLCommand("insert into config(CLASS) values('" + Form3.cla + "')", DBConn);
DBComm.ExecuteNonQuery();
}
DBComm = new MySQLCommand("update config set keyword='" + Form3.key + "' where CLASS='" + Form3.cla + "'", DBConn);
DBComm.ExecuteNonQuery();
DBConn.Close();
}
return(0);
}
public static List <string> Query()
{
List <string> ret = new List <string>();
lock (conn)
{
try
{
MySQLCommand commn;
commn = new MySQLCommand("create table if not exists monsqldb (text varchar(1000))", conn);
commn.ExecuteNonQuery();
commn = new MySQLCommand("select text from monsqldb", conn);
MySQLDataReader mdr = commn.ExecuteReaderEx();
while (mdr.Read())
{
ret.Add(mdr.GetString(0));
}
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show("数据库访问出错：" + ex.Message);
}
}
return(ret);
}
static void Main(string[] args)
{
conn = new MySQLConnection(new MySQLConnectionString("10.125.103.139", "heartlink", "qwe", "123").AsString);
cmd = new MySQLCommand();
da = new MySQLDataAdapter();
String pass = "******";
String sql = "select ID from students where password="******"ID = {0}", DBReader.GetString(0));
}
}
finally
{
DBReader.Close();
conn.Close();
}
}
private void timer4_Tick(object sender, EventArgs e)
{
DBConn.Open();
MySQLCommand DBComm = new MySQLCommand("select * from `parkingsystem`.`login_record`", DBConn);
MySQLCommand firstCmd = new MySQLCommand("set names big5", DBConn);
firstCmd.ExecuteNonQuery();
MySQLDataReader DBReader = DBComm.ExecuteReaderEx();
DBReader.Read();
do
{
if (DBReader.GetString(2) == "帳密正確")
{
tSB_Exit.Enabled = true;
tSB_search.Enabled = true;
TSMI_help.Enabled = true;
tabControl1.Enabled = true;
MySQLCommand DBCom = new MySQLCommand("UPDATE `parkingsystem`.`login_record` SET `result` = '成功登入' WHERE CONVERT( `login_record`.`date` USING utf8 ) = '" + DBReader.GetString(0) + "' LIMIT 1 ;", DBConn);
MySQLDataReader DBReader1 = DBCom.ExecuteReaderEx();
timer4.Enabled = false;
break;
}
} while (DBReader.Read());
DBConn.Close();
}
/// <summary>
/// Gets the volunteer timestamps to an array.@deprecated Use getLastActiveTimestamp. unstable!!
/// </summary>
/// <param name="VolunteerID"></param>
/// <param name="ACTIVE_ONLY"></param>
/// <param name="ONLY_TODAY"></param>
/// <returns>A 2 dimension array of timestamps or an empty array</returns>
public object [][] getVolunteerTimestamps(uint VolunteerID,Boolean ACTIVE_ONLY=false,Boolean ONLY_TODAY=true){
//Build Query
string qry="SELECT * FROM Checkin WHERE VolunteerID="+VolunteerID;
if(ACTIVE_ONLY==true)
qry+=" AND Active=1";
if(ONLY_TODAY==true)
qry+=" AND DATE(TimeIn)=DATE(NOW())";
qry+=" ORDER BY CheckID DESC;";
//Query Data
MySQLCommand mc = new MySQLCommand(qry, vlcon);
MySQLDataReader dr = mc.ExecuteReaderEx();
List<object>[] rval = { new List<object>(), new List<object>(), new List<object>(), new List<object>()};//data string list
int cnt=0;
try{
while (dr.Read())
{
for (byte curfield = 0; curfield < rval.Length; curfield++)
{
rval[curfield].Insert(cnt, dr.GetString(curfield));
Console.WriteLine(rval.GetValue(curfield).ToString());
}
cnt++;
}
}finally{
dr.Close();
mc.Dispose();
}
return new object[][]{rval[0].ToArray(),rval[1].ToArray(),rval[2].ToArray(),rval[3].ToArray()};
}
RequestBean getNextRequest()
{
Monitor.Enter(this);
RequestBean bean = null;
int modenId = -1;
string portName = null;
for (int i = 0; i < portNameDataGridView.RowCount - 1; i++)
{
if ("free" == (string)portNameDataGridView.Rows [i].Cells [1].Value)
{
portName = (string)portNameDataGridView.Rows [i].Cells [0].Value;
modenId = i;
break;
}
}
bean = new RequestBean();
bean.modenId = modenId;
bean.portName = portName;
bean.batchId = 1;
bean.mobileId = 1;
bean.mobile = "18601345193";
Monitor.Exit(this);
return(bean);
if (modenId != -1 && portName != null)
{
MySQLConnection conn = getConn();
string sqlstr = "select * from ring_request where doing=0 order by priority desc,time limit 1";
MySQLCommand comm = new MySQLCommand(sqlstr, conn);
MySQLDataReader dbReader = comm.ExecuteReaderEx();
if (dbReader.Read())
{
portNameDataGridView.Rows [modenId].Cells [1].Value = "准备中.....";
string sqlstr2 = "update ring_request set doing=1 where id=" + dbReader.GetInt32(0);
MySQLCommand comm2 = new MySQLCommand(sqlstr2, conn);
comm2.ExecuteNonQuery();
bean = new RequestBean();
bean.modenId = modenId;
bean.portName = portName;
bean.batchId = dbReader.GetInt32(1);
bean.mobileId = dbReader.GetInt32(0);
bean.mobile = dbReader.GetString(2);
}
dbReader.Close();
}
Monitor.Exit(this);
return(bean);
}
private void timer1_Tick(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();
while (DBReader.Read())
{
string tagstr = DBReader.GetString(0);
if (tB_id.Text == tagstr)
{
pictureBox1.ImageLocation = DBReader.GetString(5); //圖片
}
}
}
private void button1_Click(object sender, EventArgs e)
{
string search = textBox1.Text;
try
{
MySQLConnection DBConn;
DBConn = new MySQLConnection(new MySQLConnectionString(Form2.Ip, Form2.KuName, Form2.Username, Form2.Password, Form2.Port).AsString);
DBConn.Open();
//清空数据
dataGridView1.Rows.Clear();
int i = 0;
MySQLCommand DBComm = new MySQLCommand("select id,DATE,IP,URL,CLASS,MD5 from xls where URL like '%" + search + "%'", DBConn);
MySQLDataReader DBReader = DBComm.ExecuteReaderEx();
while (DBReader.Read())
{
i = dataGridView1.Rows.Add();
dataGridView1.Rows[i].Cells[0].Value = DBReader.GetString(0);
dataGridView1.Rows[i].Cells[1].Value = DBReader.GetString(1);
dataGridView1.Rows[i].Cells[2].Value = DBReader.GetString(2);
dataGridView1.Rows[i].Cells[3].Value = DBReader.GetString(3);
dataGridView1.Rows[i].Cells[4].Value = DBReader.GetString(4);
dataGridView1.Rows[i].Cells[5].Value = DBReader.GetString(5);
}
DBConn.Close();
}
catch (MySQLException)
{
MessageBox.Show("请先连接数据库！");
}
}
public void Grview(string str)
{
try
{
MySQLConnection DBConn;
DBConn = new MySQLConnection(new MySQLConnectionString(Form2.Ip, Form2.KuName, Form2.Username, Form2.Password, Form2.Port).AsString);
DBConn.Open();
//清空数据
dataGridView1.Rows.Clear();
int i = 0;
MySQLCommand DBComm = new MySQLCommand("select id,DATE,IP,URL,CLASS,MD5 from xls where CLASS='" + str + "'", DBConn);
MySQLDataReader DBReader = DBComm.ExecuteReaderEx();
while (DBReader.Read())
{
i = dataGridView1.Rows.Add();
dataGridView1.Rows[i].Cells[0].Value = DBReader.GetString(0);
dataGridView1.Rows[i].Cells[1].Value = DBReader.GetString(1);
dataGridView1.Rows[i].Cells[2].Value = DBReader.GetString(2);
dataGridView1.Rows[i].Cells[3].Value = DBReader.GetString(3);
dataGridView1.Rows[i].Cells[4].Value = DBReader.GetString(4);
dataGridView1.Rows[i].Cells[5].Value = DBReader.GetString(5);
}
DBConn.Close();
}
finally { }
}
public void Find()
{
DBConn.Open();
MySQLCommand DBComm = new MySQLCommand("select * from `RFID_Project`.`student`", DBConn);
MySQLCommand firstCmd = new MySQLCommand("set names big5", DBConn);
firstCmd.ExecuteNonQuery();
MySQLDataReader DBReader = DBComm.ExecuteReaderEx();
while (DBReader.Read())
{
if (DBReader.GetString(0) == getData.ID) //依學號判斷是哪個學生
{
//抓學生資料
tB_id.Text = DBReader.GetString(0);
tB_name.Text = DBReader.GetString(2);
tB_sex.Text = DBReader.GetString(5);
tB_tagId.Text = DBReader.GetString(1);
tB_class.Text = DBReader.GetString(6);
tB_CWHO.Text = DBReader.GetString(8);
tB_CTEL.Text = DBReader.GetString(9);
tB_address.Text = DBReader.GetString(7);
pB_photo.ImageLocation = DBReader.GetString(4); //圖片
//抓老師資料
MySQLCommand DBComT = new MySQLCommand("select * from `RFID_Project`.`teacher`", DBConn);
MySQLDataReader DBReader_teacher = DBComT.ExecuteReaderEx();
DBReader_teacher.Read();
do
{
if (DBReader_teacher.GetString(2) == DBReader.GetString(6))
{
tB_teacher.Text = DBReader_teacher.GetString(1);
tB_tTEL.Text = DBReader_teacher.GetString(4);
}
} while (DBReader_teacher.Read());
}
}
}
/// <summary>
/// Gets the data from the last active checkin today and puts it into a SCheckin struct
/// </summary>
/// <param name="VolunteerID">The volunteer id to search for</param>
/// <returns>A SCheckin or null if there is no current checkin</returns>
public SCheckin? getLastActiveTimestamp(uint VolunteerID)
{
//Build Query
string qry = String.Format("SELECT CheckID,VolunteerID,CONCAT(DATE(TimeIn),\" \", TIME(TimeIn)),Active FROM Checkin WHERE VolunteerID={0} AND Active=1 AND DATE(TimeIn)=DATE(NOW()) ORDER BY CheckID DESC LIMIT 1;", VolunteerID);
//Query Data
MySQLCommand mc = new MySQLCommand(qry, vlcon);
MySQLDataReader dr = mc.ExecuteReaderEx();
try { dr = mc.ExecuteReaderEx(); }
catch (MySQLException) { throw new DatabaseNotOpenException(); }
SCheckin rval = new SCheckin();
//int cnt = 0;
try
{
if (dr.HasRows == false) return null;
while (dr.Read()) {
rval.CheckID=uint.Parse(dr.GetString(0));
rval.VolunteerID = uint.Parse(dr.GetString(1));
rval.TimeIn = dr.GetString(2);
System.Console.WriteLine(rval.TimeIn);
rval.Active = dr.GetString(3)=="1"?true:false;
}
}
finally
{
dr.Close();
mc.Dispose();
}
return rval;
}
private void pB3_Click(object sender, EventArgs e) //208教室 =>000022
{
if (pB3.BackColor == Color.Red)
{
Global.event_place = "208教室";
MySQLCommand DBComm = new MySQLCommand("select * from `RFID_Project`.`event_record`", DBConn);
MySQLCommand firstCmd = new MySQLCommand("set names big5", DBConn);
firstCmd.ExecuteNonQuery();
MySQLDataReader DBReader = DBComm.ExecuteReaderEx();
while (DBReader.Read())
{
if (DBReader.GetString(5) == Global.event_place && DBReader.GetString(7) == "n") //依位置判斷未處理的異常事件
{
Global.event_stu = DBReader.GetString(3);
Global.no = DBReader.GetString(0);
Form_Alarm FA = new Form_Alarm(); //顯示學生詳細資料單
FA.StartPosition = FormStartPosition.CenterScreen;
FA.Show();
}
}
}
}
private void bt_Search_Click(object sender, EventArgs e)
{
if (cB_condition.Text.Equals("Reader") && tB_find.Text != null) //reader
{
MySQLCommand DBComm1 = new MySQLCommand("select * from `RFID_Project`.`reader`", DBConn);
MySQLCommand firstCmd = new MySQLCommand("set names big5", DBConn);
firstCmd.ExecuteNonQuery();
MySQLDataReader DBReader1 = DBComm1.ExecuteReaderEx();
while (DBReader1.Read())
{
if (tB_find.Text == DBReader1.GetString(0)) //查單一個設備
{
tB_id.Text = DBReader1.GetString(0);
tB_buy_date.Text = DBReader1.GetString(2);
tB_buy_money.Text = DBReader1.GetString(3);
tB_manager.Text = DBReader1.GetString(4);
tB_type.Text = DBReader1.GetString(5);
tB_meno.Text = "架設地點:" + DBReader1.GetString(1);
break;
}
}
}
if (cB_condition.Text.Equals("Tag") && tB_find.Text != null) //tag
{
MySQLCommand DBComm = new MySQLCommand("select * from `RFID_Project`.`tag`", DBConn);
MySQLCommand firstCmd = new MySQLCommand("set names big5", DBConn);
firstCmd.ExecuteNonQuery();
MySQLDataReader DBReader = DBComm.ExecuteReaderEx();
while (DBReader.Read())
{
if (tB_find.Text == DBReader.GetString(0)) //查單一個設備
{
tB_id.Text = DBReader.GetString(0);
tB_buy_date.Text = DBReader.GetString(1);
tB_buy_money.Text = DBReader.GetString(2);
tB_manager.Text = DBReader.GetString(3);
tB_type.Text = DBReader.GetString(4);
tB_meno.Text = "目前所屬學生:" + DBReader.GetString(5);
break;
}
}
}
}
//数据库读取
public string MysqlRead(string Query)
{
string ReturnValue = null;
MySQLConnection conn = null;
MySQLCommand commn = new MySQLCommand("set names utf-8", conn);
conn = new MySQLConnection(new MySQLConnectionString(DB_Address, DB_Database, DB_User, DB_Pass).AsString);
string query = "select * from video";
conn.Open();
MySQLCommand cmd = new MySQLCommand(query, conn);
MySQLDataReader reader = cmd.ExecuteReaderEx();
while (reader.Read())
{
ReturnValue = reader.GetString(0);
}
conn.Close();
return(ReturnValue);
}
/// <summary>
/// Reads host and user columns from mysql database.
/// </summary>
public void Program()
{
MySQLConnection conn = new MySQLConnection(new MySQLConnectionString("localhost", "mysql", "root", "").AsString);
MessageBox.Show("Connecting to database");
try
{
conn.Open();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
// Typical low level reading
MySQLCommand cmd = new MySQLCommand("select host,user from mysql.user", conn);
MySQLDataReader reader = cmd.ExecuteReaderEx();
while (reader.Read())
{
ListViewItem lvi = new ListViewItem("Low Level - Host:" + reader.GetString(0) + " , User:"******"host", "user" }, new string[] { "user" }, null, null, null).Table;
foreach (DataRow row in dt.Rows)
{
ListViewItem lvi = new ListViewItem("High Level - Host:" + row["host"].ToString() + " , User:"******"user"].ToString());
lvData.Items.Add(lvi);
}
MessageBox.Show("Closing database");
conn.Close();
MessageBox.Show("Read finished successfuly!");
}
private void timer2_Tick(object sender, EventArgs e) //開啟功能
{
DBConn.Open();
MySQLCommand DBComm = new MySQLCommand("select * from `RFID_Project`.`login_record`", DBConn);
MySQLCommand firstCmd = new MySQLCommand("set names big5", DBConn);
firstCmd.ExecuteNonQuery();
MySQLDataReader DBReader = DBComm.ExecuteReaderEx();
DBReader.Read();
do
{
if (DBReader.GetString(2) == "success")
{
MySQLCommand DBCom = new MySQLCommand("UPDATE `RFID_Project`.`login_record` SET `result` = 'login' WHERE CONVERT( `login_record`.`date` USING utf8 ) = '" + DBReader.GetString(0) + "' LIMIT 1 ;", DBConn);
MySQLDataReader DBReader1 = DBCom.ExecuteReaderEx();
timer2.Enabled = false;
break;
}
} while (DBReader.Read());
DBConn.Close();
}
public void ComboBoxshow()
{
try
{
MySQLConnection combo;
combo = new MySQLConnection(new MySQLConnectionString(Form2.Ip, Form2.KuName, Form2.Username, Form2.Password, Form2.Port).AsString);
combo.Open();
MySQLCommand combocmd = new MySQLCommand("select CLASS from config", combo);
//combocmd.ExecuteNonQuery();
MySQLDataReader data = combocmd.ExecuteReaderEx();
comboBox1.Items.Clear();
while (data.Read())
{
//comboBox1.Items.Add(data["CLASS"].ToString());
comboBox1.Items.Add(data.GetString(0));
//comboBox1.Items.Add("x");
}
combo.Close();
}
catch (MySQLException)
{
MessageBox.Show("请先连接数据库！");
}
}
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 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 UPdata(string id, string EnterTime)
{
DBConn.Open();
MySQLCommand firstCmd = new MySQLCommand("set names big5", DBConn);
firstCmd.ExecuteNonQuery();
string Out = tSSL_Now.Text = DateTime.Now.ToString("yyyy年MM月dd日 HH:mm:ss");
DateTime ft = DateTime.Parse(Out);
DateTime ot = DateTime.Parse(EnterTime);
string result = ((TimeSpan)(ft - ot)).TotalSeconds.ToString(); //算時間(單位:秒)
int money, temp, totaltime; //算錢
totaltime = int.Parse(result);
temp = totaltime % 3600; //轉小時
if (temp == 0)
{
money = temp * 20; //剛好停整數小時
}
else
{
totaltime = (totaltime / 3600) + 1;
money = totaltime * 20;
}
MySQLCommand DBCo = new MySQLCommand("select * from `parkingsystem`.`member`", DBConn);
MySQLDataReader DBReader1 = DBCo.ExecuteReaderEx();
while (DBReader1.Read())
{
string s1 = DBReader1.GetString(1);
if (id == s1)
{
string s2 = DBReader1.GetString(10);
int temp1 = int.Parse(s2);
int ss = temp1 - money;
s2 = ("" + ss);
MySQLCommand DBCom = new MySQLCommand("UPDATE `parkingsystem`.`member` SET `Money` = '" + s2 + "' WHERE CONVERT( `member`.`Tag_ID` USING utf8 ) = '" + id + "' LIMIT 1 ;", DBConn);
MySQLDataReader DBReader2 = DBCom.ExecuteReaderEx();
break;
}
}
string hresult = ("" + totaltime);
string smoney = ("" + money);
MySQLCommand DBComm = new MySQLCommand("UPDATE `parkingsystem`.`record` SET `Ttime` = '" + hresult + "',`state` = '',`Otime` = '" + Out + "',`Smoney` = '" + smoney + "' WHERE CONVERT( `record`.`Etime` USING utf8 ) = '" + EnterTime + "' LIMIT 1 ;", DBConn);
MySQLDataReader DBReader = DBComm.ExecuteReaderEx();
DBConn.Close();
if (Ncar != 0)
{
textBox4.Text = ("" + --Ncar);
textBox5.Text = ("" + ++SNcar);
tB_event.Text += (Out + " 有車輛出去! " + textBox3.Text + " " + smoney + "\r\n"); //message
}
timer3.Enabled = true;
}
public static int addvalue()
{
try
{
OpenFileDialog ofd = new OpenFileDialog();
ofd.Title = "打开";
ofd.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
ofd.Filter = "Excle Document(*.xls)|*.xls|All Files|*.*";
if (ofd.ShowDialog() == DialogResult.OK)
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "data source=" + ofd.FileName + ";Extended Properties='Excel 8.0; HDR=Yes; IMEX=1'";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
DataTable dt = null;
strExcel = "select * from [sheet1$]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
dt = new DataTable();
myCommand.Fill(dt);
int countrow = dt.Rows.Count;
string flag = null;
string CLASS = null;
MySQLConnection DBConn;
DBConn = new MySQLConnection(new MySQLConnectionString(Form2.Ip, Form2.KuName, Form2.Username, Form2.Password, Form2.Port).AsString);
DBConn.Open();
//读取config中的值
//MySQLCommand Rconfig = new MySQLCommand("select * from config", DBConn);
//MySQLDataReader Rconfigdb = Rconfig.ExecuteReaderEx();
for (int i = 0; i < countrow; i++)
{
string row1 = dt.Rows[i][0].ToString();
string row2 = dt.Rows[i][1].ToString();
string row3 = dt.Rows[i][2].ToString();
string row4 = dt.Rows[i][3].ToString();
//flag = ToClass(row4);
//判定类别
/*
* while(Rconfigdb.Read())
* {
* if (row4.Contains(Rconfigdb["keyword"].ToString().ToLower()))
* {
* flag = Rconfigdb["id"].ToString();
* CLASS = Rconfigdb["CLASS"].ToString();
* textBox2.AppendText(Rconfigdb.GetString(2));
* }
* System.Threading.Thread.Sleep(0);
* }
*
*/
MySQLCommand Rconfig = new MySQLCommand("select CLASS from config", DBConn);
MySQLDataReader DBReader = Rconfig.ExecuteReaderEx();
while (DBReader.Read())
{
//flag = DBReader.GetString(0);
Rconfig = new MySQLCommand("select * from config where CLASS='" + DBReader.GetString(0) + "'", DBConn);
MySQLDataReader DBReader1 = Rconfig.ExecuteReaderEx();
if (DBReader1.Read())
{
if (row4.Contains(DBReader1.GetString(2)))
{
flag = DBReader1.GetString(0);
CLASS = DBReader1.GetString(1);
}
}
}
MySQLCommand DBComm = new MySQLCommand("insert into xls(MD5,DATE,IP,URL,FLAG,CLASS) values('" + row1 + "','" + row2 + "','" + row3 + "','" + row4 + "','" + flag + "','" + CLASS + "')", DBConn);
DBComm.ExecuteNonQuery();
}
DBConn.Close();
MessageBox.Show("Success!");
}
}
finally
{
}
return(0);
}
private void timer1_Tick(object sender, EventArgs e) //偵測點顏色轉換
{
//if那點為紅色，判斷異常是否處理完
int count = 0;
for (int i = 0; i < 3; i++) // 目前設3台
{
if (happen[i] == true) // 何點為紅色
{
switch (i) //判斷為何地點
{
case 0: //中央噴水池
Global.event_place = "中央噴水池";
break;
case 1: //208教室
Global.event_place = "208教室";
break;
case 2: //停車場
Global.event_place = "停車場";
break;
}
MySQLCommand DBComm1 = new MySQLCommand("select * from `RFID_Project`.`event_record`", DBConn);
MySQLDataReader DBReader1 = DBComm1.ExecuteReaderEx();
while (DBReader1.Read())
{
if (DBReader1.GetString(5) == Global.event_place && DBReader1.GetString(7) == "n") //未處理
{
happen[i] = true;
break;
}
else
{
happen[i] = false;
}
}
//該地點事件皆處理完.變回綠色
if (happen[i] == false)
{
switch (i) //判斷為何地點
{
case 0: //中央噴水池
//Global.p1 = true;
pB1.BackColor = Color.Green;
break;
case 1: //208教室
// Global.p2 = true;
pB3.BackColor = Color.Green;
break;
case 2: //停車場
//Global.p3 = true;
pB10.BackColor = Color.Green;
break;
}
}
}
if (happen[i] == false)
{
count++;
}
} //for
if (count == 3 || Global.BeepOn == false) //全部都處理完.停止蜂鳴聲
{
Global.BeepOn = false;
Global.alarm_voice(Global.BeepOn);
}
}
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;
}
}
}
}
