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