private void button5_Click(object sender, EventArgs e) { richTextBox1.Text = ""; richTextBox1.AppendText("历史物流信息:\n"); richTextBox1.AppendText("物流单号\t\t\t物品\t\t\t重量(kg)\t\t\t发货人\t\t联系电话\t\t\t\t地址\t\t\t\t\t\t\t收货人\t\t联系电话\t\t\t\t地址\t\t\t\t\t\t\t\t交易日期\t\t\t\t签收日期\n"); LogInformation[] ligroup = new LogInformation[20]; LogInformation li = null; int i = -1; conn.Open(); string sql_log = $"SELECT LNo,TNo,LGoods,Weight,DateTime,ArrivalTime FROM logistics WHERE FNo = '{user}'"; MySqlCommand cmd = new MySqlCommand(sql_log, conn); MySqlDataReader reader = cmd.ExecuteReader(); //添加基本信息 while (reader.Read()) { li = new LogInformation(); li.lno = reader.GetString("LNo"); li.tno = reader.GetString("TNo"); li.goods = reader.GetString("LGoods"); li.weight = reader.GetInt32("Weight"); li.timeDeal = reader.GetDateTime("DateTime"); li.timeArrive = reader.GetDateTime("ArrivalTime"); ligroup[++i] = li;//往数组中添加该对象 } reader.Close(); //添加农户信息 string fname, faddress, fcontact; string sql_farmer = $"SELECT FName,FAddress,FContact FROM farmer WHERE FNo = '{user}'"; cmd = new MySqlCommand(sql_farmer, conn); MySqlDataReader reader1 = cmd.ExecuteReader(); reader1.Read(); fname = reader1.GetString("FName"); faddress = reader1.GetString("FAddress"); fcontact = reader1.GetString("FContact"); reader1.Close(); //添加商户信息 i = 0; string sql_tradesman = ""; MySqlDataReader reader2 = null; while (ligroup[i] != null) { sql_tradesman = $"SELECT TName,TAddress,TContact FROM tradesman WHERE TNo = '{ligroup[i].tno}'"; cmd = new MySqlCommand(sql_tradesman, conn); reader2 = cmd.ExecuteReader(); reader2.Read(); ligroup[i].tname = reader2.GetString("TName"); ligroup[i].taddress = reader2.GetString("TAddress"); ligroup[i].tcontact = reader2.GetString("TContact"); richTextBox1.AppendText(ligroup[i].lno + "\t\t\t" + ligroup[i].goods + "\t\t" + ligroup[i].weight + "\t\t\t\t" + fname + "\t\t" + fcontact + "\t\t\t" + faddress + "\t\t" + ligroup[i].tname + "\t\t" + ligroup[i].tcontact + "\t\t\t" + ligroup[i].taddress + "\t\t\t" + ligroup[i].timeDeal.ToLongDateString() + "\t\t\t" + ligroup[i].timeArrive.ToShortDateString() + "\n"); i++; reader2.Close(); } conn.Close(); }
private void button2_Click(object sender, EventArgs e) { string connetStr = "server=127.0.0.1;port=3306;user=wx;password=wuxiao.04092313; database=cls;"; string sql = ""; LogInformation buy_infor = new LogInformation(); if (comboBox1.SelectedItem == null) { label8.Text = "请选择农产品!"; } else { label8.Text = ""; } if (comboBox2.SelectedItem == null) { label9.Text = "请选择农户!"; } else { label9.Text = ""; } int tmp; if (!int.TryParse(textBox1.Text, out tmp)) { label10.Text = "请输入数字!"; } else { int num = 0; num = int.Parse(textBox1.Text); if (num <= 0) { label10.Text = "请输入正整数"; } else { label10.Text = ""; } } SubDate sub = new SubDate(dateTimePicker1.Value, dateTimePicker2.Value); if (sub.dateSub() <= 0) { label11.Text = "请输入正确预计日期!"; } else { label11.Text = ""; } if (label8.Text == "" && label9.Text == "" && label10.Text == "" && label11.Text == "") { buy_infor.tno = user; buy_infor.timeDeal = dateTimePicker1.Value; buy_infor.timeArrive = dateTimePicker2.Value; buy_infor.lno = textBox2.Text; conn.Open(); //获取FNo string sql_fno = $"SELECT FNo FROM farmer WHERE FName = '{comboBox2.SelectedItem}'"; MySqlCommand cmd = new MySqlCommand(sql_fno, conn); MySqlDataReader reader = cmd.ExecuteReader(); reader.Read(); buy_infor.fno = reader.GetString("FNo"); reader.Dispose(); //获取cno string sql_cno = $"SELECT CNo FROM crops WHERE CName = '{comboBox1.SelectedItem}'"; cmd = new MySqlCommand(sql_cno, conn); reader = cmd.ExecuteReader(); reader.Read(); buy_infor.cno = reader.GetString("CNo"); reader.Dispose(); //获取农户该农产品总库存 int total_stock = 0; string pno = ""; string sql_stock = $"SELECT PNo,FStock FROM plante WHERE FNo = '{buy_infor.fno}' AND CNo = '{buy_infor.cno}' AND IsFresh = 0"; cmd = new MySqlCommand(sql_stock, conn); reader = cmd.ExecuteReader(); while (reader.Read()) { total_stock += reader.GetInt32("FStock"); } reader.Dispose(); //判断输入重量是否超值,以及影响到的生产批次pno buy_infor.weight = Convert.ToInt32(textBox1.Text); int weight = buy_infor.weight; if (buy_infor.weight > total_stock) { MessageBox.Show($"收购失败:该农户库存({total_stock.ToString()}kg)不足,无法提供您所需重量的{comboBox1.SelectedItem.ToString()}!", "收购提示", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); } else { reader = cmd.ExecuteReader(); while (reader.Read()) { MySqlConnection conn1 = new MySqlConnection(connetStr); MySqlCommand cmd1 = null; pno = reader.GetString("PNo"); if (reader.GetInt32("FStock") >= weight) { conn1.Open(); //更新plante表中数据 sql = $"UPDATE plante SET FStock = {reader.GetInt32("FStock") - weight} WHERE PNo = '{pno}'"; cmd1 = new MySqlCommand(sql, conn1); cmd1.ExecuteNonQuery(); //deal表中插入/更新数据 sql = $"SELECT TStock FROM deal WHERE TNo = '{buy_infor.tno}' AND PNo = '{pno}'"; cmd1 = new MySqlCommand(sql, conn1); MySqlDataReader reader1 = cmd1.ExecuteReader(); if (reader1.Read()) { int tstock = reader1.GetInt32("TStock"); reader1.Close(); sql = $"UPDATE deal SET TStock = {tstock + weight} WHERE PNo = '{pno}' AND TNo = '{buy_infor.tno}'"; cmd1 = new MySqlCommand(sql, conn1); cmd1.ExecuteNonQuery(); } else { reader1.Close(); sql = $"INSERT INTO deal(PNo,TNo,TStock) VALUES('{pno}','{user}',{weight})"; cmd1 = new MySqlCommand(sql, conn1); cmd1.ExecuteNonQuery(); } //logistics表中插入数据 sql = $"INSERT INTO logistics(LNo,FNo,TNo,LGoods,Weight,DateTime,ArrivalTime) VALUES('{buy_infor.lno}','{buy_infor.fno}','{buy_infor.tno}','{comboBox1.SelectedItem}',{buy_infor.weight},'{buy_infor.timeDeal}','{buy_infor.timeArrive}')"; cmd1 = new MySqlCommand(sql, conn1); cmd1.ExecuteNonQuery(); MessageBox.Show("收购成功!", "收购提示", MessageBoxButtons.OK, MessageBoxIcon.Asterisk); conn1.Close(); break; } else { conn1.Open(); //更新plante表中数据 sql = $"UPDATE plante SET FStock = 0 WHERE PNo = '{pno}'"; cmd1 = new MySqlCommand(sql, conn1); cmd1.ExecuteNonQuery(); //deal表中插入/更新数据 sql = $"SELECT TStock FROM deal WHERE TNo = '{buy_infor.tno}' AND PNo = '{pno}'"; cmd1 = new MySqlCommand(sql, conn1); MySqlDataReader reader1 = cmd1.ExecuteReader(); if (reader1.Read()) { int tstock = reader1.GetInt32("TStock"); reader1.Close(); sql = $"UPDATE deal SET TStock = {tstock + reader.GetInt32("FStock")} WHERE PNo = '{pno}' AND TNo = '{buy_infor.tno}'"; cmd1 = new MySqlCommand(sql, conn1); cmd1.ExecuteNonQuery(); } else { reader1.Close(); sql = $"INSERT INTO deal(PNo,TNo,TStock) VALUES('{pno}','{user}',{reader.GetInt32("FStock")})"; cmd1 = new MySqlCommand(sql, conn1); cmd1.ExecuteNonQuery(); } conn1.Close(); weight -= reader.GetInt32("FStock"); } } } conn.Close(); reader.Close(); } }