private void button1_Click(object sender, EventArgs e)
{
richTextBox1.Text = "";
richTextBox1.AppendText("保鲜期到期提醒(低于原保质期天数的0.2倍):\n");
richTextBox1.AppendText("农产品\t\t\t库存(kg)\t\t\t剩余保质期(day)\n");
DateTime timeNow = System.DateTime.Now;//获取当前时间
conn.Open();
string sql_date = $"SELECT CNo,ProdDate,FStock FROM plante WHERE FNo = '{user}'";
MySqlCommand cmd1 = new MySqlCommand(sql_date, conn);
MySqlDataReader reader1 = cmd1.ExecuteReader();
while (reader1.Read())
{
cno = reader1.GetString("CNo");
timePro = reader1.GetDateTime("ProdDate");
stock = reader1.GetInt32("FStock");
int i = 0;
while (Common.crops[i] != null)
{
if (Common.crops[i].cno.Equals(cno))
{
freshday = Common.crops[i].freshness;
cname = Common.crops[i].cname;
break;
}
i++;
}
sd = new SubDate(timePro, timeNow);
leave = freshday - sd.dateSub();//计算剩余天数
if (leave > freshday * 0.2)
{
}
else if (leave <= 0)
{
richTextBox1.AppendText(cname + "\t\t\t" + stock + "\t\t\t\t已过期\n");
}
else
{
richTextBox1.AppendText(cname + "\t\t\t" + stock + "\t\t\t\t" + leave + "\n");
}
}
conn.Close();
reader1.Close();
}
public Form4(MySqlConnection conn, string user)
{
InitializeComponent();
richTextBox1.AppendText("保鲜期到期提醒(低于原保质期天数的0.2倍):\n");
richTextBox1.AppendText("农产品\t\t\t库存(kg)\t\t\t剩余保质期(day)\n");
this.conn = conn;
this.user = user;
string name = "";
//设置label1的值
conn.Open();
string sql_tip = $"SELECT FName FROM farmer WHERE FNo = '{user}'";
MySqlCommand cmd = new MySqlCommand(sql_tip, conn);
MySqlDataReader reader = cmd.ExecuteReader();
reader.Read();
name = reader.GetString("FName");
reader.Dispose();
conn.Close();
label1.Text = "欢迎" + name + "~";
//过期智能提醒
DateTime timeNow = System.DateTime.Now;//获取当前时间
conn.Open();
string sql_date = $"SELECT CNo,ProdDate,FStock FROM plante WHERE FNo = '{user}'";
MySqlCommand cmd1 = new MySqlCommand(sql_date, conn);
MySqlDataReader reader1 = cmd1.ExecuteReader();
while (reader1.Read())
{
cno = reader1.GetString("CNo");
timePro = reader1.GetDateTime("ProdDate");
stock = reader1.GetInt32("FStock");
int i = 0;
while (Common.crops[i] != null)
{
if (Common.crops[i].cno.Equals(cno))
{
freshday = Common.crops[i].freshness;
cname = Common.crops[i].cname;
break;
}
i++;
}
sd = new SubDate(timePro, timeNow);
leave = freshday - sd.dateSub();//计算剩余天数
if (leave > freshday * 0.2)
{
}
else if (leave <= 0)
{
richTextBox1.AppendText(cname + "\t\t\t" + stock + "\t\t\t\t已过期\n");
}
else
{
richTextBox1.AppendText(cname + "\t\t\t" + stock + "\t\t\t\t" + leave + "\n");
}
}
conn.Close();
reader1.Close();
}
public Form10(MySqlConnection conn, string user)
{
InitializeComponent();
this.conn = conn;
this.user = user;
string name = "";
//设置label1的值
conn.Open();
string sql_tip = $"SELECT TName FROM tradesman WHERE TNo = '{user}'";
MySqlCommand cmd = new MySqlCommand(sql_tip, conn);
MySqlDataReader reader = cmd.ExecuteReader();
reader.Read();
name = reader.GetString("TName");
reader.Close();
label1.Text = "欢迎" + name + "~";
//保鲜期到期/库存提醒
richTextBox1.AppendText("保鲜期到期/库存不足提醒(低于原保质期天数的0.2倍/库存少于100kg):\n");
richTextBox1.AppendText("农产品\t\t\t库存(kg)\t\t库存提醒\t\t保质期(day)\t\t剩余保质期(day)\n");
DateTime timeNow = System.DateTime.Now;
Tradesman[] freshGroup = new Tradesman[30];
Tradesman tradesFre = null;
int i = -1;
//获取pno数组
string sql_deal = $"SELECT PNo,TStock FROM deal WHERE TNo = '{user}'";
cmd = new MySqlCommand(sql_deal, conn);
MySqlDataReader reader1 = cmd.ExecuteReader();
while (reader1.Read())
{
tradesFre = new Tradesman();
tradesFre.pno = reader1.GetString("PNo");
tradesFre.stock = reader1.GetInt32("TStock");
freshGroup[++i] = tradesFre;
}
reader1.Close();
//获取cno在通过全局常量crops获得cname和fresh信息
i = 0;
int j;
MySqlDataReader reader2 = null;
string sql_getcno = "";
while (freshGroup[i] != null)
{
sql_getcno = $"SELECT CNo,ProdDate FROM plante WHERE PNo = '{freshGroup[i].pno}'";
cmd = new MySqlCommand(sql_getcno, conn);
reader2 = cmd.ExecuteReader();
reader2.Read();
freshGroup[i].cno = reader2.GetString("CNo");
freshGroup[i].timePro = reader2.GetDateTime("ProdDate");
//获取commom.crops中值
j = 0;
while (Common.crops[j] != null)
{
if (Common.crops[j].cno.Equals(freshGroup[i].cno))
{
freshGroup[i].cname = Common.crops[j].cname; //get cname
freshGroup[i].freshness = Common.crops[j].freshness; // get freshness
sd = new SubDate(freshGroup[i].timePro, timeNow);
freshGroup[i].leave = freshGroup[i].freshness - sd.dateSub(); //get剩余保质期
break;
}
j++;
}
if (freshGroup[i].leave <= 0)
{
if (freshGroup[i].stock < 100)
{
richTextBox1.AppendText(freshGroup[i].cname + "\t\t\t" + freshGroup[i].stock + "\t\t\t不足\t\t\t" +
freshGroup[i].freshness + "\t\t\t\t已过期\n");
}
else
{
richTextBox1.AppendText(freshGroup[i].cname + "\t\t\t" + freshGroup[i].stock + "\t\t\t充足\t\t\t" +
freshGroup[i].freshness + "\t\t\t\t已过期\n");
}
freshGroup[i].isFresh = 1;//过期
}
else if (freshGroup[i].leave < freshGroup[i].freshness * 0.2)
{
if (freshGroup[i].stock < 100)
{
richTextBox1.AppendText(freshGroup[i].cname + "\t\t\t" + freshGroup[i].stock + "\t\t\t不足\t\t\t" +
freshGroup[i].freshness + "\t\t\t\t" + freshGroup[i].leave + "\n");
}
else
{
richTextBox1.AppendText(freshGroup[i].cname + "\t\t\t" + freshGroup[i].stock + "\t\t\t充足\t\t\t" +
freshGroup[i].freshness + "\t\t\t\t" + freshGroup[i].leave + "\n");
}
}
else
{
}
i++;
reader2.Dispose();
}
reader2.Close();
//UPDATE plante - IsFresh
string sql_update = "";
i = 0;
while (freshGroup[i] != null)
{
sql_update = $"UPDATE plante SET IsFresh = '{freshGroup[i].isFresh}' WHERE PNo = '{freshGroup[i].pno}'";
cmd = new MySqlCommand(sql_update, conn);
cmd.ExecuteNonQuery();
i++;
}
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();
}
}
private void button1_Click(object sender, EventArgs e)
{
richTextBox1.Text = "";
conn.Open();
MySqlCommand cmd = null;
//保鲜期到期/库存提醒
richTextBox1.AppendText("保鲜期到期/库存不足提醒(低于原保质期天数的0.2倍/库存少于100kg):\n");
richTextBox1.AppendText("农产品\t\t\t库存(kg)\t\t库存提醒\t\t保质期(day)\t\t剩余保质期(day)\n");
DateTime timeNow = System.DateTime.Now;
Tradesman[] freshGroup = new Tradesman[30];
Tradesman tradesFre = null;
int i = -1;
//获取pno数组
string sql_deal = $"SELECT PNo,TStock FROM deal WHERE TNo = '{user}'";
cmd = new MySqlCommand(sql_deal, conn);
MySqlDataReader reader1 = cmd.ExecuteReader();
while (reader1.Read())
{
tradesFre = new Tradesman();
tradesFre.pno = reader1.GetString("PNo");
tradesFre.stock = reader1.GetInt32("TStock");
freshGroup[++i] = tradesFre;
}
reader1.Close();
//获取cno在通过全局常量crops获得cname和fresh信息
i = 0;
int j;
MySqlDataReader reader2 = null;
string sql_getcno = "";
while (freshGroup[i] != null)
{
sql_getcno = $"SELECT CNo,ProdDate FROM plante WHERE PNo = '{freshGroup[i].pno}'";
cmd = new MySqlCommand(sql_getcno, conn);
reader2 = cmd.ExecuteReader();
reader2.Read();
freshGroup[i].cno = reader2.GetString("CNo");
freshGroup[i].timePro = reader2.GetDateTime("ProdDate");
//获取commom.crops中值
j = 0;
while (Common.crops[j] != null)
{
if (Common.crops[j].cno.Equals(freshGroup[i].cno))
{
freshGroup[i].cname = Common.crops[j].cname; //get cname
freshGroup[i].freshness = Common.crops[j].freshness; // get freshness
sd = new SubDate(freshGroup[i].timePro, timeNow);
freshGroup[i].leave = freshGroup[i].freshness - sd.dateSub(); //get剩余保质期
break;
}
j++;
}
if (freshGroup[i].leave <= 0)
{
if (freshGroup[i].stock < 100)
{
richTextBox1.AppendText(freshGroup[i].cname + "\t\t\t" + freshGroup[i].stock + "\t\t\t不足\t\t\t" +
freshGroup[i].freshness + "\t\t\t\t已过期\n");
}
else
{
richTextBox1.AppendText(freshGroup[i].cname + "\t\t\t" + freshGroup[i].stock + "\t\t\t充足\t\t\t" +
freshGroup[i].freshness + "\t\t\t\t已过期\n");
}
freshGroup[i].isFresh = 1;//过期
}
else if (freshGroup[i].leave < freshGroup[i].freshness * 0.2)
{
if (freshGroup[i].stock < 100)
{
richTextBox1.AppendText(freshGroup[i].cname + "\t\t\t" + freshGroup[i].stock + "\t\t\t不足\t\t\t" +
freshGroup[i].freshness + "\t\t\t\t" + freshGroup[i].leave + "\n");
}
else
{
richTextBox1.AppendText(freshGroup[i].cname + "\t\t\t" + freshGroup[i].stock + "\t\t\t充足\t\t\t" +
freshGroup[i].freshness + "\t\t\t\t" + freshGroup[i].leave + "\n");
}
}
else
{
}
i++;
reader2.Dispose();
}
reader2.Close();
conn.Close();
}