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