//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);
}
}
/// <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);
}
}
//定义查询数据库字段名称
//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 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();
}
