//double beforTotal = 0;
//string[] dbFields = { "materialname", "barcode", "materialname", "specification", "supplier", "total", "remainnum", "price" };
private void btnQuery_Click(object sender, EventArgs e)
{
string txt = txtQuery.Text;
//int itemIndex = cmbQuery.SelectedIndex;
//if (itemIndex < 0)
//{
// return;
//}
//query string
string queryString = "select * from material where barcode = '" + txt + "'";
if (txt == string.Empty)
{
return;
}
try
{
DataTable dt = DataDBInfo.QueryDBInfo(queryString);
//Set txt(s) 对textBox赋值
if (dt.Rows.Count > 0)
{
DataRow dr = dt.Rows[0];
txtBarCode.Text = dr["barcode"].ToString();
txtCategoryOne.Text = dr["categoryone"].ToString();
txtCategoryTwo.Text = dr["categorytwo"].ToString();
txtCategoryThree.Text = dr["categorythree"].ToString();
txtMaterialName.Text = dr["materialname"].ToString();
txtSpecification.Text = dr["specification"].ToString();
txtAddNum.Text = dr["remainnum"].ToString();
txtNote.Text = dr["note"].ToString();
txtPrice.Text = dr["price"].ToString();
txtSupplier.Text = dr["supplier"].ToString();
txtSpecificationModle1.Text = dr["specificationmodle"].ToString();
txtBrand.Text = dr["brand"].ToString();
//string num = txtAddNum.Text;
//string price = txtPrice.Text;
//int num1 = Convert.ToInt32(num);
//int price1 = Convert.ToInt32(price);
// beforTotal =Convert.ToInt32(dr["total"].ToString()) ;
if (txtPrice.Text == "")
{
txtPrice.Text = "0";
}
if (txtThresHodl.Text == "")
{
txtThresHodl.Text = "0";
}
}
else
{
MessageBox.Show("数据库中没有此编码，请添加！");
}
}
catch (System.Exception ex)
{
Console.WriteLine(ex.Message);
}
}
/// <summary>
/// 初始化类别2 ListBox
/// </summary>
private void InitListBoxTwo()
{
listBoxCateTwo.Items.Clear();
if (categoryOne.Length <= 0)
{
return;
}
try
{
string queryString = "select distinct categorytwo from material where categoryone = '" + categoryOne + "' order by categorytwo";
DataTable dt = DataDBInfo.QueryDBInfo(queryString);
//Set listBox
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
listBoxCateTwo.Items.Add(dr[0].ToString());
}
}
else
{
Console.WriteLine("没有查找到Categorytwo的物料");
}
categoryTwo = listBoxCateTwo.Items[0].ToString();
listBoxCateTwo.SelectedIndex = 0;
}
catch (System.Exception ex)
{
Console.WriteLine(ex.Message);
}
}
private void CreateNodeTwo(TreeNode node, string categoryOne)
{
try
{
string queryString = "select distinct categorytwo from material where categoryone = '" + categoryOne + "' order by categorytwo";
DataTable dt = DataDBInfo.QueryDBInfo(queryString);
//Set listBox
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
string categoryTwo = dr[0].ToString();
TreeNode subNode = new TreeNode(categoryTwo);
node.Nodes.Add(subNode);
subNode.Tag = "2";
CreateNodeThree(subNode, categoryOne, categoryTwo);
}
}
else
{
Console.WriteLine("没有查找到Categorytwo的物料");
}
}
catch (System.Exception ex)
{
Console.WriteLine(ex.Message);
}
}
/// <summary>
/// 初始化类别1 ListBox
/// </summary>
private void InitListBoxOne()
{
listBoxCateOne.Items.Clear();
try
{
string queryString = "select distinct categoryone from material order by categoryone";
DataTable dt = DataDBInfo.QueryDBInfo(queryString);
//Set listBox
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
listBoxCateOne.Items.Add(dr[0].ToString());
}
}
else
{
Console.WriteLine("没有查找到CategoryOne的物料");
}
categoryOne = listBoxCateOne.Items[0].ToString();
listBoxCateOne.SelectedIndex = 0;
}
catch (System.Exception ex)
{
Console.WriteLine(ex.Message);
}
}
private void CreateTreeNode(TreeNode tNode)
{
try
{
string queryString = "select distinct categoryone from material order by categoryone";
DataTable dt = DataDBInfo.QueryDBInfo(queryString);
//Set listBox
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
string categoryOne = dr[0].ToString();
TreeNode node = new TreeNode(categoryOne);
tNode.Nodes.Add(node);
node.Tag = "1";
CreateNodeTwo(node, categoryOne);
}
}
else
{
Console.WriteLine("没有查找到CategoryOne的物料");
}
}
catch (System.Exception ex)
{
Console.WriteLine(ex.Message);
}
}
private void CreateNodeFour(TreeNode node, string categoryOne, string categoryTwo, string categoryThree)
{
try
{
string queryString = "select barcode, materialname, specification from material where categoryone = '" + categoryOne + "' and categorytwo = '" + categoryTwo + "' and categoryThree = '" + categoryThree + "' order by barcode";
DataTable dt = DataDBInfo.QueryDBInfo(queryString);
//Set listBox
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
string barCode = dr["barcode"].ToString();
string materialName = dr["materialname"].ToString();
TreeNode subNode = new TreeNode(barCode + " " + materialName);
subNode.Tag = barCode;
subNode.ToolTipText = dr["specification"].ToString();
node.Nodes.Add(subNode);
}
}
else
{
Console.WriteLine("没有查找到Categorytwo的物料");
}
}
catch (System.Exception ex)
{
Console.WriteLine(ex.Message);
}
}
/// <summary>
/// 查找barcode对应的库存数量
/// </summary>
/// <param name="barCode"></param>
/// <returns></returns>
public static string GetRemainNumByBarCode(string barCode)
{
string condition = "barcode = " + barCode;
string queryString = "select * from material where barcode = '" + barCode + "'";
try
{
DataTable dt = DataDBInfo.QueryDBInfo(queryString);
//Set txt(s) 对textBox赋值
if (dt.Rows.Count > 0)
{
return(dt.Rows[0][6].ToString());
}
else
{
return("0");
}
}
catch (System.Exception ex)
{
Console.WriteLine(ex.Message);
return("0");
}
}
private void queryDBInfoAndAddtoDgv(string queryString)
{
//query DB data
try
{
DataTable dt = DataDBInfo.QueryDBInfo(queryString);
//Set dgvList
if (dt.Rows.Count > 0)
{
dgvMaterialList.Rows.Clear();
foreach (DataRow dr in dt.Rows) ///遍历所有的行
{
string barcode = dr[0].ToString();
//int num = Convert.ToInt32(dr["remainnum"].ToString());
//double numPrice = Convert.ToInt32(dr["price"].ToString());
//double numTotal = num * numPrice;
//if (CheckBarCodeExist(barcode, dgvMaterialList))
//{
// continue;
//}
int index = dgvMaterialList.Rows.Add();
dgvMaterialList.Rows[index].Cells["barcode"].Value = barcode;
dgvMaterialList.Rows[index].Cells["materialname"].Value = dr["materialname"].ToString();
dgvMaterialList.Rows[index].Cells["specification"].Value = dr["specification"].ToString();
dgvMaterialList.Rows[index].Cells["specificationmodle"].Value = dr["specificationmodle"].ToString();
dgvMaterialList.Rows[index].Cells["remainnum"].Value = dr["remainnum"].ToString();
dgvMaterialList.Rows[index].Cells["supplier"].Value = dr["supplier"].ToString();
dgvMaterialList.Rows[index].Cells["total"].Value = dr["total"].ToString();
dgvMaterialList.Rows[index].Cells["categoryone"].Value = dr["categoryone"].ToString();
dgvMaterialList.Rows[index].Cells["categorytwo"].Value = dr["categorytwo"].ToString();
dgvMaterialList.Rows[index].Cells["categorythree"].Value = dr["categorythree"].ToString();
dgvMaterialList.Rows[index].Cells["price"].Value = dr["price"].ToString();
dgvMaterialList.Rows[index].Cells["note"].Value = dr["note"].ToString();
dgvMaterialList.Rows[index].Cells["warn"].Value = dr["threshold"].ToString();
dgvMaterialList.Rows[index].Cells["brand"].Value = dr["brand"].ToString();
int nThreshold = int.Parse(dr["threshold"].ToString());
int nRemain = int.Parse(dr["remainnum"].ToString());
if (nRemain <= nThreshold)
{
dgvMaterialList.Rows[index].DefaultCellStyle.ForeColor = Color.Red;
}
}
}
else
{
MessageBox.Show("没有此物料或者查询类别不对！");
}
}
catch (System.Exception ex)
{
Console.WriteLine(ex.Message);
}
}
private void AddHistoryToList(string queryString)
{
//query DB data
try
{
System.Data.DataTable dt = DataDBInfo.QueryDBInfo(queryString);
//Set dgvList
if (dt.Rows.Count > 0)
{
dgvOutList.Rows.Clear();
foreach (DataRow dr in dt.Rows) ///遍历所有的行
{
int index = dgvOutList.Rows.Add();
//dgvOutList.Rows[index].Cells[0].Value = dr[0].ToString();
//dgvOutList.Rows[index].Cells[1].Value = dr[1].ToString();
//dgvOutList.Rows[index].Cells[2].Value = dr[2].ToString();
//dgvOutList.Rows[index].Cells[3].Value = dr[8].ToString();
//dgvOutList.Rows[index].Cells[4].Value = dr[3].ToString();
//dgvOutList.Rows[index].Cells[5].Value = dr[5].ToString();
//dgvOutList.Rows[index].Cells[6].Value = dr[6].ToString();
//dgvOutList.Rows[index].Cells[7].Value = dr[7].ToString();
//dgvOutList.Rows[index].Cells[8].Value = dr[4].ToString();
//dgvOutList.Rows[index].Cells[9].Value = dr[13].ToString();
//dgvOutList.Rows[index].Cells[10].Value = dr[11].ToString();
//dgvOutList.Rows[index].Cells[11].Value = dr[10].ToString();
//dgvOutList.Rows[index].Cells[12].Value = dr[9].ToString();
//dgvOutList.Rows[index].Cells[13].Value = dr[12].ToString();
dgvOutList.Rows[index].Cells["barcode"].Value = dr["barcode"].ToString();
dgvOutList.Rows[index].Cells["materialname"].Value = dr["materialname"].ToString();
dgvOutList.Rows[index].Cells["specification"].Value = dr["specification"].ToString();
dgvOutList.Rows[index].Cells["specificationmodle"].Value = dr["specificationmodle"].ToString();
dgvOutList.Rows[index].Cells["inouttype"].Value = dr["inouttype"].ToString();
dgvOutList.Rows[index].Cells["inoutnum"].Value = dr["inoutnum"].ToString();
dgvOutList.Rows[index].Cells["total"].Value = dr["total"].ToString();
dgvOutList.Rows[index].Cells["operatetime"].Value = dr["operatetime"].ToString();
dgvOutList.Rows[index].Cells["operater"].Value = dr["operater"].ToString();
dgvOutList.Rows[index].Cells["whereabouts"].Value = dr["whereabouts"].ToString();
dgvOutList.Rows[index].Cells["price"].Value = dr["price"].ToString();
dgvOutList.Rows[index].Cells["note"].Value = dr["note"].ToString();
dgvOutList.Rows[index].Cells["supplier"].Value = dr["supplier"].ToString();
dgvOutList.Rows[index].Cells["brand"].Value = dr["brand"].ToString();
}
}
}
catch (System.Exception ex)
{
Console.WriteLine(ex.Message);
}
}
public static void ImportToDB()
{
FileStream fs = new FileStream("888888.csv" /*fileExcelPath*/, FileMode.Open, FileAccess.Read, FileShare.None);
StreamReader sr = new StreamReader(fs, System.Text.Encoding.GetEncoding(936));
string str = "";
string[] material;
string queryString = "";
while (str != null)
{
str = sr.ReadLine();
if (str == null)
{
break;
}
material = str.Split(',');
if (material[0].Length == 0 || material[0] == "物料类别（一级）")
{
continue;
}
Console.WriteLine(str);
queryString = "insert into material values('";
queryString += material[5] + "','" +
material[0] + "','" +
material[1] + "','" +
material[2] + "','" +
material[3] + "','" +
material[4] + "','" +
material[6] + "')";
Console.WriteLine(queryString);
try
{
DataDBInfo.ExecuteSQLQuery(queryString);
}
catch (System.Exception ex)
{
Console.WriteLine(ex.Message);
Console.WriteLine(queryString);
}
}
}
/// <summary>
/// 查找barcode物料信息
/// </summary>
/// <param name="barCode"></param>
/// <returns></returns>
public static DataTable GetMaterialInfoByBarCode(string barCode)
{
string condition = "barcode = " + barCode;
string queryString = "select * from material where barcode = '" + barCode + "'";
try
{
DataTable dt = DataDBInfo.QueryDBInfo(queryString);
return(dt);
}
catch (System.Exception ex)
{
Console.WriteLine(ex.Message);
}
return(null);
}
/// <summary>
/// 初始化类别4 ListBox
/// </summary>
private void InitListBoxFour()
{
listBoxCateFour.Items.Clear();
if (categoryTwo.Length <= 0)
{
return;
}
try
{
string queryString = "select barcode, materialname, specification from material where categoryone = '" + categoryOne + "' and categorytwo = '" + categoryTwo + "' and categoryThree = '" + categoryThree + "' order by materialname";
DataTable dt = DataDBInfo.QueryDBInfo(queryString);
//Set listBox
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
MaterialItem item = new MaterialItem();
item.itemString = dr["materialname"].ToString();
item.barcode = dr["barcode"].ToString();
item.specification = dr["specification"].ToString();
int index = listBoxCateFour.Items.Add(item);
}
}
else
{
Console.WriteLine("没有查找到Categorytwo的物料");
}
listBoxCateFour.SelectedIndex = 0;
}
catch (System.Exception ex)
{
Console.WriteLine(ex.Message);
}
}
private void btnOutMaterial_Click(object sender, EventArgs e)
{
if (dgvOutList.Rows.Count == 0)
{
return;
}
//遍历循环要添加的列
foreach (DataGridViewRow row in dgvOutList.Rows)
{
int addNum = 0;
string addNums = row.Cells["InNum"].Value.ToString();
double numprice = Convert.ToDouble(row.Cells["price"].Value.ToString());
if (addNums != string.Empty)
{
addNum = int.Parse(addNums);
}
double sumnum = addNum * numprice;
double total = 0.0;
if (row.Cells["total"].Value.ToString() != string.Empty)
{
total = Convert.ToDouble(row.Cells["total"].Value.ToString());
}
double totalnum = total - sumnum;
string strTotal = Convert.ToString(totalnum);
string queryString = "update material set remainnum = remainnum - " + addNum + ",total=" + strTotal + " where barcode = '" + row.Cells["barcode"].Value.ToString() + "'";
int rowAffect = DataDBInfo.ExecuteSQLQuery(queryString);
if (rowAffect == 0)
{
queryString = "insert into material (barcode,materialname,specification,specificationmodle,remainnum,categoryone,categorytwo,categorythree,whereabouts,price,total,note) values('"
+ row.Cells["barcode"].Value.ToString() + "','"
+ row.Cells["materialname"].Value.ToString() + "','"
+ row.Cells["specification"].Value.ToString() + "','"
+ row.Cells["specificationmodle"].Value.ToString() + "','"
+ row.Cells["remainnum"].Value.ToString() + "','"
+ row.Cells["categoryone"].Value.ToString() + "','"
+ row.Cells["categorytwo"].Value.ToString() + "','"
+ row.Cells["categorythree"].Value.ToString() + "','"
+ row.Cells["whereabouts"].Value.ToString() + "','"
+ row.Cells["price"].Value.ToString() + "','"
+ row.Cells["total"].Value.ToString() + "','"
+ row.Cells["note"].Value.ToString() + "')";
DataDBInfo.ExecuteSQLQuery(queryString);
}
string historyString = "insert into history(barcode,note,materialname,price,specification,specificationmodle,inouttype,inoutnum,operatetime,operater,whereabouts) Values('"
+ row.Cells["barcode"].Value.ToString() + "','"
+ row.Cells["note"].Value.ToString() + "','"
+ row.Cells["materialname"].Value.ToString() + "','"
+ row.Cells["price"].Value.ToString() + "','"
+ row.Cells["specification"].Value.ToString() + "','"
+ row.Cells["specificationmodle"].Value.ToString() + "','"
+ "出库','"
+ row.Cells["InNum"].Value.ToString() + "','"
+ DateTime.Now.ToString() + "','"
+ LoginDataInfo.LoginData.UserName + "','"
+ row.Cells["whereabouts"].Value.ToString() + "')";
DataDBInfo.ExecuteSQLQuery(historyString);
}
MessageBox.Show("出库成功");
//清空列表
dgvOutList.Rows.Clear();
}
private void btnAdd_Click(object sender, EventArgs e)
{
string supplier = txtSupplier.Text;
string brand = txtBrand.Text;
if ((supplier == null || supplier.Equals("")) && (brand == null || brand.Equals("")))
{
MessageBox.Show("供应商或品牌不能为空！！");
return;
}
string num = txtAddNum.Text;
string price = txtPrice.Text;
if (num.Equals("") || price.Equals(""))
{
MessageBox.Show("数量或单价不能为空");
return;
}
double num1 = 0;
double price1 = 0.0;
if (Regex.IsMatch(num, @"^[+-]?\d*[.]?\d*$") && Regex.IsMatch(price, @"^[+-]?\d*[.]?\d*$"))
{
num1 = Convert.ToDouble(num);
price1 = Convert.ToDouble(price);
}
else
{
MessageBox.Show("单价或数量为非数字！！");
return;
}
double total = 0.0;
if (num1 == 0 || price1 == 0)
{
total = 0.0;
}
else
{
total = num1 * price1;
}
//检查编码的有效性
string barcode = txtBarCode.Text;
//if (barcode.Length <= 5)
//{
// return;
//}
//判断编码是否已经存在
if (frmMain.CheckBarCodeExist(barcode, dgvInList))
{
foreach (DataGridViewRow row in dgvInList.Rows)
{
if (row.Cells["barcode"].Value.ToString() == barcode)
{
row.Cells["InNum"].Value = txtAddNum.Text;
}
row.Cells["categoryone"].Value = txtCategoryOne.Text;
row.Cells["categorytwo"].Value = txtCategoryTwo.Text;
row.Cells["categorythree"].Value = txtCategoryThree.Text;
row.Cells["materialname"].Value = txtMaterialName.Text;
row.Cells["specification"].Value = txtSpecification.Text;
row.Cells["specificationModle"].Value = txtSpecificationModle1.Text;
row.Cells["price"].Value = txtPrice.Text;
row.Cells["supplier"].Value = txtSupplier.Text;
row.Cells["brand"].Value = txtBrand.Text;
row.Cells["note"].Value = txtNote.Text;
row.Cells["total"].Value = total;
}
return;
}
int index = dgvInList.Rows.Add();
dgvInList.Rows[index].Cells["barcode"].Value = barcode;
dgvInList.Rows[index].Cells["categoryone"].Value = txtCategoryOne.Text;
dgvInList.Rows[index].Cells["categorytwo"].Value = txtCategoryTwo.Text;
dgvInList.Rows[index].Cells["categorythree"].Value = txtCategoryThree.Text;
dgvInList.Rows[index].Cells["materialname"].Value = txtMaterialName.Text;
dgvInList.Rows[index].Cells["specification"].Value = txtSpecification.Text;
dgvInList.Rows[index].Cells["specificationmodle"].Value = txtSpecificationModle1.Text;
dgvInList.Rows[index].Cells["remainnum"].Value = DataDBInfo.GetRemainNumByBarCode(barcode);
dgvInList.Rows[index].Cells["InNum"].Value = txtAddNum.Text;
dgvInList.Rows[index].Cells["note"].Value = txtNote.Text;
dgvInList.Rows[index].Cells["price"].Value = txtPrice.Text;
dgvInList.Rows[index].Cells["supplier"].Value = txtSupplier.Text;
dgvInList.Rows[index].Cells["brand"].Value = txtBrand.Text;
dgvInList.Rows[index].Cells["total"].Value = total;
}
private void btnAddInMaterial_Click(object sender, EventArgs e)
{
if (dgvInList.Rows.Count == 0)
{
return;
}
//遍历循环要添加的列
foreach (DataGridViewRow row in dgvInList.Rows)
{ //获取第一行编码
string txt = row.Cells["barcode"].Value.ToString();
//query string
string queryString1 = "select * from material where barcode = '" + txt + "'";
DataTable dt = DataDBInfo.QueryDBInfo(queryString1);
//之前的总价
double beforTotal = 0.0;
//
if (dt.Rows.Count > 0)
{
DataRow dr = dt.Rows[0];
if (Convert.ToInt32(dr["total"].ToString()) == 0)
{
beforTotal = 0;
}
else
{
beforTotal = Convert.ToInt32(dr["total"].ToString());
}
}
int addNum = 0;
int addNums1 = Convert.ToInt32(row.Cells["InNum"].Value.ToString());
int remainnum = Convert.ToInt32(row.Cells["remainnum"].Value.ToString());
double numPrice1 = Convert.ToDouble(row.Cells["price"].Value.ToString());
double numTotal1 = beforTotal + (addNums1 * numPrice1);
numPrice1 = numTotal1 / (remainnum + addNums1);
string numPrice = Convert.ToString(numPrice1);
string numTotal = Convert.ToString(numTotal1);
string addNums = row.Cells["InNum"].Value.ToString();
string numSupplier = row.Cells["supplier"].Value.ToString();
string numBrand = row.Cells["brand"].Value.ToString();
if (addNums != string.Empty)
{
addNum = int.Parse(addNums);
}
string queryString = "update material set remainnum = remainnum + " + addNum + ",supplier='" + numSupplier + "', brand='" + numBrand + "',price= '" + numPrice + "',total='" + numTotal + "' where barcode = '" + row.Cells[0].Value.ToString() + "'";
int rowAffect = DataDBInfo.ExecuteSQLQuery(queryString);
if (rowAffect == 0)
{
queryString = "insert into material (barcode,categoryone,categorytwo,categorythree,threshold,materialname,specification,specificationmodle,supplier,brand,price,total,remainnum,note) values('"
+ row.Cells["barcode"].Value.ToString() + "','"
+ row.Cells["categoryone"].Value.ToString() + "','"
+ row.Cells["categorytwo"].Value.ToString() + "','"
+ row.Cells["categorythree"].Value.ToString() + "','"
+ txtThresHodl.Text + "','"
+ row.Cells["materialname"].Value.ToString() + "','"
+ row.Cells["specification"].Value.ToString() + "','"
+ row.Cells["specificationModle"].Value.ToString() + "','"
+ row.Cells["supplier"].Value.ToString() + "','"
+ row.Cells["brand"].Value.ToString() + "','"
+ row.Cells["price"].Value.ToString() + "','"
+ row.Cells["total"].Value.ToString() + "','"
+ row.Cells["InNum"].Value.ToString() + "','"
+ row.Cells["note"].Value.ToString() + "')";
DataDBInfo.ExecuteSQLQuery(queryString);
}
string historyString = "insert into history(barcode,specificationmodle,materialname,supplier,total,brand,price,note,specification,inouttype,Inoutnum,operatetime,operater) Values('"
+ row.Cells["barcode"].Value.ToString() + "','"
+ row.Cells["specificationmodle"].Value.ToString() + "','"
+ row.Cells["materialname"].Value.ToString() + "','"
+ row.Cells["supplier"].Value.ToString() + "','"
+ row.Cells["total"].Value.ToString() + "','"
+ row.Cells["brand"].Value.ToString() + "','"
+ row.Cells["price"].Value.ToString() + "','"
+ row.Cells["note"].Value.ToString() + "','"
+ row.Cells["specification"].Value.ToString() + "','"
+ "入库','"
+ row.Cells["InNum"].Value.ToString() + "','"
+ DateTime.Now.ToString() + "','"
+ LoginDataInfo.LoginData.UserName + "')";
DataDBInfo.ExecuteSQLQuery(historyString);
}
//提示入库成功
if (DialogResult.Yes == MessageBox.Show("物料入库成功，是否要导出入库记录！", "Exprot", MessageBoxButtons.YesNo))
{
ExportToFiles();
}
//入库后清空列表
dgvInList.Rows.Clear();
}
//定义查询数据库字段名称
//string[] dbFields = { "materialname", "barcode", "categoryone", "categorytwo", "categorythree", "materialname", "specification", "supplier", "total", "remainnum", "price" };
private void btnQuery_Click(object sender, EventArgs e)
{
string txt = txtQuery.Text;
//int itemIndex = cmbQuery.SelectedIndex;
//if (itemIndex < 0)
//{
// return;
//}
//query string
string queryString = "select * from material where barcode like '%" + txt + "%'";
if (txt == string.Empty)
{
queryString = "select * from material";
}
//先清空列表
dgvMaterialList.Rows.Clear();
//query string
try
{
DataTable dt = DataDBInfo.QueryDBInfo(queryString);
int dtt = dt.Rows.Count;
//Set txt(s) 对textBox赋值
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows) ///遍历所有的行
{
string barcode = dr[0].ToString();
//int num = Convert.ToInt32(dr["remainnum"].ToString());
//double numPrice = Convert.ToInt32(dr["price"].ToString());
//double numTotal = num * numPrice;
//if (CheckBarCodeExist(barcode, dgvMaterialList))
//{
// continue;
//}
int index = dgvMaterialList.Rows.Add();
dgvMaterialList.Rows[index].Cells["barcode1"].Value = barcode;
dgvMaterialList.Rows[index].Cells["materialname1"].Value = dr["materialname"].ToString();
dgvMaterialList.Rows[index].Cells["specification1"].Value = dr["specification"].ToString();
dgvMaterialList.Rows[index].Cells["specification1Modle"].Value = dr["specificationmodle"].ToString();
dgvMaterialList.Rows[index].Cells["remainnum1"].Value = dr["remainnum"].ToString();
dgvMaterialList.Rows[index].Cells["categoryone1"].Value = dr["categoryone"].ToString();
dgvMaterialList.Rows[index].Cells["categorytwo1"].Value = dr["categorytwo"].ToString();
dgvMaterialList.Rows[index].Cells["categorythree1"].Value = dr["categorythree"].ToString();
dgvMaterialList.Rows[index].Cells["note1"].Value = dr["note"].ToString();
dgvMaterialList.Rows[index].Cells["total1"].Value = dr["total"].ToString();
dgvMaterialList.Rows[index].Cells["price1"].Value = dr["price"].ToString();
}
}
else
{
MessageBox.Show("数据库中没有此编码！");
}
}
catch (System.Exception ex)
{
Console.WriteLine(ex.Message);
}
}
private void btnAdd_Click(object sender, EventArgs e)
{
string whereabouts = txtWhereabouts.Text;
if (whereabouts == null || whereabouts.Equals(""))
{
MessageBox.Show("客户名称未填写");
return;
}
//检查编码的有效性
string barcode = txtQuery.Text;
//判断编码是否已经存在
if (frmMain.CheckBarCodeExist(barcode, dgvOutList))
{
foreach (DataGridViewRow row in dgvOutList.Rows)
{
if (row.Cells["barcode"].Value.ToString() == barcode)
{
row.Cells["InNum"].Value = txtAddCount.Text;
row.Cells["whereabouts"].Value = txtWhereabouts.Text;
}
}
return;
}
DataTable dt = DataDBInfo.GetMaterialInfoByBarCode(barcode);
if (dt == null || dt.Rows.Count == 0)
{
Console.WriteLine("查询BarCode对应物料信息错误！");
return;
}
//判读库存数量和出库数量
double nRemain = double.Parse(dt.Rows[0]["remainnum"].ToString());
if (!Regex.IsMatch(txtAddCount.Text, @"^[+-]?\d*[.]?\d*$"))
{
MessageBox.Show("出库数量填写有误");
return;
}
double outNum = double.Parse(txtAddCount.Text);
if (outNum > nRemain)
{
MessageBox.Show(barcode + " 库存数量不足！");
outNum = nRemain;
}
//int num = Convert.ToInt32(dt.Rows[0]["remainnum"].ToString());
//double numPrice = Convert.ToInt32(dt.Rows[0]["price"].ToString());
//double numTotal = num * numPrice;
int index = dgvOutList.Rows.Add();
dgvOutList.Rows[index].Cells["barcode"].Value = barcode;
dgvOutList.Rows[index].Cells["materialname"].Value = dt.Rows[0]["materialname"].ToString();
dgvOutList.Rows[index].Cells["specification"].Value = dt.Rows[0]["specification"].ToString();
dgvOutList.Rows[index].Cells["specificationModle"].Value = dt.Rows[0]["specificationmodle"].ToString();
dgvOutList.Rows[index].Cells["remainnum"].Value = dt.Rows[0]["remainnum"].ToString();
dgvOutList.Rows[index].Cells["whereabouts"].Value = txtWhereabouts.Text;
dgvOutList.Rows[index].Cells["categoryone"].Value = dt.Rows[0]["categoryone"].ToString();
dgvOutList.Rows[index].Cells["categorytwo"].Value = dt.Rows[0]["categorytwo"].ToString();
dgvOutList.Rows[index].Cells["categorythree"].Value = dt.Rows[0]["categorythree"].ToString();
dgvOutList.Rows[index].Cells["InNum"].Value = outNum.ToString();
dgvOutList.Rows[index].Cells["note"].Value = dt.Rows[0]["note"].ToString();
dgvOutList.Rows[index].Cells["price"].Value = dt.Rows[0]["price"].ToString();
dgvOutList.Rows[index].Cells["total"].Value = dt.Rows[0]["total"].ToString();
}
private void btnModify_Click(object sender, EventArgs e)
{
//set dtRow
string num1 = txtRemainNum.Text;
string price = txtPrice.Text;
if (!Regex.IsMatch(num1, @"^[+-]?\d*[.]?\d*$") || !Regex.IsMatch(price, @"^[+-]?\d*[.]?\d*$"))
{
MessageBox.Show("单价或数量为非数字！！");
return;
}
string barCode = dtRow.Cells["barcode"].Value.ToString();
dtRow.Cells["barcode"].Value = txtBarCode.Text;
dtRow.Cells["materialname"].Value = txtMaterialName.Text;
dtRow.Cells["specification"].Value = txtSpecification.Text;
dtRow.Cells["specificationmodle"].Value = txtSpecificationModle.Text;
dtRow.Cells["remainnum"].Value = txtRemainNum.Text;
dtRow.Cells["categoryone"].Value = txtCategoryOne.Text;
dtRow.Cells["categorytwo"].Value = txtCategoryTwo.Text;
dtRow.Cells["categorythree"].Value = txtCategoryThree.Text;
dtRow.Cells["note"].Value = txtNote.Text;
dtRow.Cells["price"].Value = txtPrice.Text;
dtRow.Cells["warn"].Value = txtThresHold.Text;
dtRow.Cells["supplier"].Value = txtSupplier.Text;
dtRow.Cells["brand"].Value = txtBrand.Text;
//dtRow.Cells["threshold"].Value = txtThresHold.Text;
try
{
//SetDB modify
double num = Convert.ToDouble(dtRow.Cells["remainnum"].Value);
double numPrice = Convert.ToInt32(dtRow.Cells["price"].Value);
double numTotal = num * numPrice;
string queryString = "update material set barcode = '" + dtRow.Cells["barcode"].Value.ToString() +
"',materialname = '" + dtRow.Cells["materialname"].Value.ToString() +
"',specification = '" + dtRow.Cells["specification"].Value.ToString() +
"',brand = '" + dtRow.Cells["brand"].Value.ToString() +
"',supplier = '" + dtRow.Cells["supplier"].Value.ToString() +
"',specificationmodle = '" + dtRow.Cells["specificationmodle"].Value.ToString() +
"',remainnum = " + dtRow.Cells["remainnum"].Value.ToString() +
",categoryone = '" + dtRow.Cells["categoryone"].Value.ToString() +
"',categorytwo = '" + dtRow.Cells["categorytwo"].Value.ToString() +
"',categorythree = '" + dtRow.Cells["categorythree"].Value.ToString() +
"',threshold = " + txtThresHold.Text +
",price = '" + dtRow.Cells["price"].Value.ToString() +
"',note = '" + dtRow.Cells["note"].Value.ToString() +
"',total = '" + numTotal +
"' where barcode = '" + barCode + "'";
DataDBInfo.ExecuteSQLQuery(queryString);
//modify History
string historyString = "insert into history(barcode,brand,supplier,materialname,inoutnum,supplier,total,price,note,specification,specificationmodle,inouttype,operatetime,operater) Values('"
+ dtRow.Cells["barcode"].Value.ToString() + "','"
+ dtRow.Cells["brand"].Value.ToString() + "','"
+ dtRow.Cells["supplier"].Value.ToString() + "','"
+ dtRow.Cells["materialname"].Value.ToString() + "','"
+ dtRow.Cells["remainnum"].Value.ToString() + "','"
+ dtRow.Cells["supplier"].Value.ToString() + "','"
+ numTotal + "','"
+ dtRow.Cells["price"].Value.ToString() + "','"
+ dtRow.Cells["note"].Value.ToString() + "','"
+ dtRow.Cells["specification"].Value.ToString() + "','"
+ dtRow.Cells["specificationmodle"].Value.ToString() + "','"
+ "修改','"
+ DateTime.Now.ToString() + "','"
+ LoginDataInfo.LoginData.UserName + "')";
DataDBInfo.ExecuteSQLQuery(historyString);
}
catch (System.Exception ex)
{
Console.WriteLine(ex.Message);
}
//close window
this.Close();
}
