public GoodsManagementSubsystem() { InitializeComponent(); //行状态事件,用户添加新行时进行标记 dgvAddItem.UserAddedRow += (x, y) => isAdd = true; //窗体加载时的动作 this.Load += (x, y) => { serialPort = new PL2303(); DGVDataSource = USDataAccess.Select("select id as 商品代码,name as 商品名,price as 价格,manufacturer as 生产厂家,productiondate as 生产日期,validuntil as 有效期,count(commodityId) as 库存数 from [terminal] as t1 join [commodity] as t2 on t1.commodityId = t2.id group by commodityId;"); }; //串口随窗体焦点改变而开/关 this.Activated += (x, y) => { serialPort?.Open(); timerBindingCard.Enabled = true; }; this.Deactivate += (x, y) => { serialPort?.Close(); timerBindingCard.Enabled = false; }; //记录输入的查询内容 tbConditionAtMgr.LostFocus += (x, y) => queryCondition = $"'{tbConditionAtMgr.Text}'"; tbConditionAtDel.LostFocus += (x, y) => queryCondition = $"'{tbConditionAtDel.Text}'"; tbConditionAtBinding.LostFocus += (x, y) => queryCondition = $"'{tbConditionAtBinding.Text}'"; //输入时自动清空原有内容 tbConditionAtMgr.Click += (x, y) => tbConditionAtMgr.Text = ""; tbConditionAtDel.Click += (x, y) => tbConditionAtDel.Text = ""; tbConditionAtBinding.Click += (x, y) => tbConditionAtBinding.Text = ""; }
/// <summary> /// 绑定RFID卡与商品 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btnBindingRfid_Click(object sender, EventArgs e) { if (curCardId == null) { MessageBox.Show("请放卡!"); return; } if (dgvBindingItem.CurrentRow == null) { MessageBox.Show("请选择商品!"); } if (curCardId.Equals("")) { Random rand = new Random(); int temp = rand.Next(1, 10000); for (; USDataAccess.Select($"select * from [terminal] where [cardId] like '{temp}';").Rows.Count > 0;) { temp = rand.Next(1, 10000); } curCardId = temp.ToString(); } for (; !serialPort.ReadAllData().Contains("addr:0x0001 para:0x");) { serialPort.Write($"CM+WRITE -addr=0x01 -value=0x{curCardId}"); } USDataAccess.Insert($"insert into [terminal] values(0,'{curCardId}',{dgvBindingItem.CurrentRow.Cells[0].Value.ToString()});"); curCardId = null; }
/// <summary> /// 当用户改变了单元格的值时对数据库进行更新 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void dgvMgrItem_CellValueChanged(object sender, DataGridViewCellEventArgs e) { string id = dgvMgrItem.Rows[e.RowIndex].Cells[0].Value.ToString(); string newCellValue = dgvMgrItem.Rows[e.RowIndex].Cells[e.ColumnIndex].Value.ToString(); //USDataAccess.Update("update [commodity] set [xx] = xxxx where [xxx] = 'xxx';"); USDataAccess.Update($"update [commodity] set {USDataAccess.CommodityColumns[e.ColumnIndex]} = '{newCellValue}' where [id] = {id};"); BeginInvoke(new MethodInvoker(() => DGVDataSource = USDataAccess.Select("select id as 商品代码, name as 商品名, price as 价格, manufacturer as 生产厂家, productiondate as 生产日期, validuntil as 有效期, count(commodityId) as 库存数 from[terminal] as t1 join[commodity] as t2 on t1.commodityId = t2.id group by commodityId;"))); }
/// <summary> /// 用户删除行时获取id并从数据库中移除 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void dgvDelItem_UserDeletingRow(object sender, DataGridViewRowCancelEventArgs e) { string delId = dgvDelItem.CurrentRow.Cells[0].Value.ToString(); BeginInvoke(new MethodInvoker( () => { USDataAccess.Delete($"delete from [commodity] where [id] = {delId};"); DGVDataSource = USDataAccess.Select("select id as 商品代码, name as 商品名, price as 价格, manufacturer as 生产厂家, productiondate as 生产日期, validuntil as 有效期, count(commodityId) as 库存数 from[terminal] as t1 join[commodity] as t2 on t1.commodityId = t2.id group by commodityId;"); } )); }
/// <summary> /// 从数据库中查询相关信息并刷新datagridview的数据源 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void Search_Click(object sender, EventArgs e) { if (tbQueryCondition.Text.Equals("") || cmbQuerySpecies.Text.Equals("请选择查询类别")) { MessageBox.Show("请设定查询条件!"); return; } try { dgvDisplay.DataSource = USDataAccess.Select($"select tid as 所在货柜, name as 商品名, price as 价格, manufacturer as 生产厂家, productiondate as 生产日期, validuntil as 有效期, count(commodityId) as 库存数 from[terminal] as t1 join[commodity] as t2 on t1.commodityId = t2.id and tid != 0 where {querySpecies} '{tbQueryCondition.Text}' group by commodityId;"); } catch (Exception) { MessageBox.Show("查询失败,请检查输入是否正确."); } }
/// <summary> /// 定时器, /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void timerBindingCard_Tick(object sender, EventArgs e) { if (dgvBindingItem.CurrentRow != null && dgvBindingItem.CurrentRow.Index >= 0) { try { //读取RFID标签数据 foreach (var str in serialPort.ReadAllData().Split('\n')) { if (str.Contains("addr:0x0001 read data:0x")) { string cardId = str.Substring(24, 4); //查询数据库中有无此rfid标签的识别码 DataTable table = USDataAccess.Select($"select * from [terminal] where [cardId] like '{cardId}'"); if (table.Rows.Count == 0) { curCardId = ""; rtbShowAtBinding.Text = "卡片尚未绑定."; return; } curCardId = cardId; //卡如果属于已经绑定的卡,则将商品id与表中数据比较然后输出商品信息 string cid = table.Rows[0][2].ToString(); foreach (DataGridViewRow row in dgvBindingItem.Rows) { if (row.Cells[0].Value.ToString().Equals(cid)) { rtbShowAtBinding.Text = "rfid标签当前绑定的商品为:\n"; foreach (DataGridViewCell cell in row.Cells) { rtbShowAtBinding.AppendText(dgvBindingItem.Columns[cell.ColumnIndex].Name + ":\t"); rtbShowAtBinding.AppendText(cell.Value + "\n"); } } } } } serialPort.Write("CM+READ -addr=0x01"); } catch (UnauthorizedAccessException) { MessageBox.Show("遇到了一些问题,请检查串口是否被占用!", "异常"); return; } } }// end of timer
/// <summary> /// /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btnSearch_Click(object sender, EventArgs e) { if (queryCondition == null || querySpecies == null) { MessageBox.Show("请设定查询条件!"); return; } try { DGVDataSource = USDataAccess.Select($"select id as 商品代码, name as 商品名, price as 价格, manufacturer as 生产厂家, productiondate as 生产日期, validuntil as 有效期, count(commodityId) as 库存数 from[terminal] as t1 join[commodity] as t2 on t1.commodityId = t2.id where {querySpecies + queryCondition} group by commodityId;"); } catch (Exception) { MessageBox.Show("查询失败,请检查输入是否正确."); } querySpecies = queryCondition = null; }
/// <summary> /// 定时器,负责检查货柜上商品变动情况 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void timerSerialPort_Tick(object sender, EventArgs e) { try { //读取数据,看看有没有新的RFID标签添加到货柜上 foreach (var str in serialPort.ReadAllData().Split('\n')) { if (str.Contains("addr:0x0001 read data:0x")) { //查询对应卡id的信息 string cardId = str.Substring(24, 4); //判断rfid标签是否绑定 if (cardId.Equals("0000")) { return; } var dataTable = USDataAccess.Select($"select * from [terminal] where [cardId] like '{cardId}';"); if (dataTable.Rows.Count > 0) { //判断是添加商品到货柜还是拿走商品 if (int.Parse(dataTable.Rows[0][0].ToString()) == 0) { USDataAccess.Update($"update [terminal] set [tid] = {curTid} where [cardId] like '{cardId}';"); } else { USDataAccess.Delete($"delete from [terminal] where [cardId] like '{cardId}';"); serialPort.Write("CM+WRITE -addr=0x01 -value=0x0000"); } dgvDisplay.DataSource = USDataAccess.Select($"select tis as 所在货柜, name as 商品名, price as 价格, manufacturer as 生产厂家, productiondate as 生产日期, validuntil as 有效期, count(commodityId) as 库存数 from[terminal] as t1 join[commodity] as t2 on t1.commodityId = t2.id and tid = {curTid} group by commodityId;"); } } } serialPort.Write("CM+READ -addr=0x01"); } catch (UnauthorizedAccessException) { MessageBox.Show("遇到了一些问题,请检查串口是否被占用!", "异常"); return; } }
public SmartShelvesTerminal() { InitializeComponent(); //debug USDataAccess.Delete("delete from [terminal] where tid = 0;"); //登记并显示货柜 curTid = USDataAccess.Select("select distinct [tid] from [terminal] where [tid] != 0;").Rows.Count + 1; //相关事件 tbQueryCondition.Click += (x, y) => tbQueryCondition.Text = ""; this.Load += (x, y) => { serialPort = new PL2303(); dgvDisplay.DataSource = USDataAccess.Select($"select tid as 所在货柜, name as 商品名, price as 价格, manufacturer as 生产厂家, productiondate as 生产日期, validuntil as 有效期, count(commodityId) as 库存数 from[terminal] as t1 join[commodity] as t2 on t1.commodityId = t2.id and tid = {curTid} group by commodityId;"); }; this.Activated += (x, y) => { serialPort?.Open(); timerSerialPort.Enabled = true; }; this.Deactivate += (x, y) => { serialPort?.Close(); timerSerialPort.Enabled = false; }; }
/// <summary> /// 添加新的数据行到数据库 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void dgvAddItem_RowEnter(object sender, DataGridViewCellEventArgs e) { if (dgvAddItem.CurrentRow != null && dgvAddItem.CurrentRow.Index == rowCount && isAdd == true) { string values = ""; foreach (DataGridViewCell item in dgvAddItem.Rows[rowCount].Cells) { if (item.Value.Equals(System.DBNull.Value)) { MessageBox.Show("数据不完整!", "添加新行失败"); dgvAddItem.ClearSelection(); dgvAddItem.Rows[item.RowIndex].Selected = true; item.Selected = true; return; } if (item.ValueType.Equals(typeof(int))) { values += item.Value.ToString() + ','; } else { values += $"'{item.Value.ToString()}',"; } } try { USDataAccess.Insert($"insert into [commodity] values({values.Trim(',')});"); BeginInvoke(new MethodInvoker(() => DGVDataSource = USDataAccess.Select("select id as 商品代码, name as 商品名, price as 价格, manufacturer as 生产厂家, productiondate as 生产日期, validuntil as 有效期, count(commodityId) as 库存数 from[terminal] as t1 join[commodity] as t2 on t1.commodityId = t2.id group by commodityId;"))); isAdd = false; } catch (Exception) { throw; } } }