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