// 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);
}
