// TODO自增ID 加入数据库 private int _addIntoDatabase(NnStock stock) { _initStockCoordinate(stock);// 检测是否是临时坐标,并确保临时坐标不会重复 int count = 0; using (OleDbCommand cmd = new OleDbCommand("", m_connection)) { cmd.CommandText = "insert into stock_new([_date], workNo, orderId, quality, coordinate, purity, mw,comments) " + $"values(@de,@wn,@oi,@qt,@cd,@pt,@mw,@cs)"; cmd.Parameters.AddWithValue("de", DateTime.Now.ToString()); cmd.Parameters.AddWithValue("wn", stock.WorkNo); cmd.Parameters.AddWithValue("oi", stock.OrderId); cmd.Parameters.AddWithValue("qt", stock.Quality); cmd.Parameters.AddWithValue("cd", stock.Coordinate); cmd.Parameters.AddWithValue("pt", stock.Purity); cmd.Parameters.AddWithValue("mw", stock.Mw); cmd.Parameters.AddWithValue("cs", stock.Comments); try { count = cmd.ExecuteNonQuery(); } catch (Exception e) { Console.WriteLine(e.ToString()); } if (count > 0) { _updateCoordinate(stock.Coordinate, true); // 移除临时库存 _deleteTemporaryStock(stock); } } return(count); }
/// <summary> /// 移除临时库存 /// </summary> /// <param name="stock"></param> private void _deleteTemporaryStock(NnStock stock) { List <NnStock> list = new List <NnStock>(); using (OleDbCommand cmd = new OleDbCommand("SELECT * FROM stock_new WHERE [orderId]=@v1", m_connection)) { cmd.Parameters.AddWithValue("v1", stock.OrderId); using (OleDbDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { NnStock sk = new NnStock(); sk.InitStockNewByDb(reader); if (sk.Coordinate.StartsWith("L-") && sk.Coordinate != stock.Coordinate) { list.Add(sk); } } } } foreach (var v in list) { v.Cause = "坐标替换" + v.Coordinate + "->" + stock.Coordinate; _removeFromDatabase(v); } }
/// <summary> /// 检测是否是临时坐标,并确保临时坐标不会重复 /// </summary> private void _initStockCoordinate(NnStock stock) { if (!stock.Coordinate.StartsWith("L-")) { return; } int count = SelectStockCountByDate(DateTime.Now); stock.Coordinate += $"-{count + 1}{mRandom.Next(0, 100)}"; }
/// <summary> /// 通过OrderId和Coordinate搜索 /// </summary> private bool _searchByOrderIdAndCoordinate(string s, StockSearcher ss) { bool isHas = false; try { using (OleDbCommand cmd = new OleDbCommand("SELECT * FROM stock_new WHERE [coordinate]=@v1", m_connection)) { cmd.Parameters.AddWithValue("v1", s); using (OleDbDataReader reader = cmd.ExecuteReader()) { if (reader.Read()) { NnStock ns = new NnStock(); ns.InitStockNewByDb(reader); ns.OriginalString = s; ss.Add(ns); return(true); } } cmd.CommandText = "SELECT * FROM stock_new WHERE [orderId]=@v1"; using (OleDbDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { NnStock ns = new NnStock(); ns.InitStockNewByDb(reader); ns.OriginalString = s; isHas = true; ss.Add(ns); } } cmd.CommandText = "SELECT * FROM stock_old WHERE [orderId]=@v1"; using (OleDbDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { NnStock ns = new NnStock(); ns.InitStockOldByDb(reader); ns.OriginalString = s; ss.Add(ns); } } } } catch (Exception e) { Console.WriteLine(e.ToString()); } return(isHas); }
/// <summary> /// 将更改提交到数据库 /// </summary> /// <param name="stock"></param> /// <returns></returns> public int Submit(NnStock stock) { switch (stock.State) { case NnStock.StockState.Insert: // 添加 return(_addIntoDatabase(stock)); case NnStock.StockState.Update: // 更新 return(_updateForDatabase(stock)); case NnStock.StockState.Delete: // 移除 return(_removeFromDatabase(stock)); default: return(0); } }
// 从数据库移除 private int _removeFromDatabase(NnStock stock) { int count = 0; using (OleDbCommand cmd = new OleDbCommand("select * from stock_new where coordinate=@cd", m_connection)) { cmd.Parameters.AddWithValue("cd", stock.Coordinate); try { NnStock sk = null; using (OleDbDataReader reader = cmd.ExecuteReader()) { if (reader.Read()) { sk = new NnStock(); sk.InitStockNewByDb(reader); } } if (sk == null) { return(count); } cmd.Parameters.Clear(); cmd.CommandText = $"insert into stock_old (dateAdd,dateRemove,workNo,orderId,quality,purity,mw,cause) values(@dta,@dtn,@wn,@oi,@qt,@pt,@mw,@ca)"; cmd.Parameters.AddWithValue("dta", sk.DateAdd.ToString()); cmd.Parameters.AddWithValue("dtn", DateTime.Now.ToString()); cmd.Parameters.AddWithValue("wn", sk.WorkNo); cmd.Parameters.AddWithValue("oi", sk.OrderId); cmd.Parameters.AddWithValue("qt", sk.Quality); cmd.Parameters.AddWithValue("pt", sk.Purity); cmd.Parameters.AddWithValue("mw", sk.Mw); cmd.Parameters.AddWithValue("ca", stock.Cause); count = cmd.ExecuteNonQuery(); if (count > 0) { cmd.Parameters.Clear(); cmd.CommandText = "delete from stock_new where coordinate=@cd"; cmd.Parameters.AddWithValue("cd", stock.Coordinate); cmd.ExecuteNonQuery(); _updateCoordinate(stock.Coordinate, false); } } catch (Exception e) { Console.WriteLine(e.ToString()); } } return(count); }
private void _getSearchValues(string s, StockSearcher ss) { bool isHas; if (!s.Contains('-'))// 如果是workNo { isHas = _searchByWorkNo(s, ss); } else// 否则 { isHas = _searchByOrderIdAndCoordinate(s, ss); } if (!isHas) { NnStock ns = new NnStock() { StockSearchState = SearchState.None, OriginalString = s }; ss.Add(ns); } }
// 更新数据库数据 private int _updateForDatabase(NnStock stock) { int count = 0; using (OleDbCommand cmd = new OleDbCommand("select * from stock_new where coordinate=@cd", m_connection)) { cmd.Parameters.AddWithValue("cd", stock.Coordinate); try { NnStock sk = null; using (OleDbDataReader reader = cmd.ExecuteReader()) { if (reader.Read()) { sk = new NnStock(); sk.InitStockNewByDb(reader); sk.Purity = stock.Purity; sk.Mw = stock.Mw; if (stock.Quality > 0) { sk.Quality = stock.Quality; } } } if (sk == null) { return(count); } cmd.Parameters.Clear(); cmd.CommandText = "update stock_new set quality=@qt,purity=@pt,mw=@mw where coordinate=@cd"; cmd.Parameters.AddWithValue("qt", sk.Quality); cmd.Parameters.AddWithValue("pt", sk.Purity); cmd.Parameters.AddWithValue("mw", sk.Mw); cmd.Parameters.AddWithValue("cd", sk.Coordinate); count = cmd.ExecuteNonQuery(); } catch (Exception e) { Console.WriteLine(e.ToString()); } } return(count); }