//验证
private bool Verification()
{
SqlConnection con = SqlConnect.getConn();
try
{
SqlCommand cmd = new SqlCommand("SELECT * FROM T_People WHERE P_Id = @id and P_Password = @key", con);
SqlParameter ID = new SqlParameter("@id", SqlDbType.VarChar, 20);
ID.Value = UserID.Text;
cmd.Parameters.Add(ID);
SqlParameter KEY = new SqlParameter("@key", SqlDbType.VarChar, 20);
KEY.Value = UserPa.Text;
cmd.Parameters.Add(KEY);
SqlDataAdapter DA = new SqlDataAdapter(cmd);
DataSet Ds = new DataSet();
DA.Fill(Ds, "ReaderLogin");
if (Ds.Tables["ReaderLogin"].Rows.Count == 0)
{
MessageBox.Show("用户名或密码不正确!");
return(false);
}
DataRow r = Ds.Tables["ReaderLogin"].Rows[0];
MessageBox.Show("登录成功!\n欢迎:" + r["P_Name"].ToString());
return(true);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
MessageBox.Show("数据库连接失败!\n请与前台工作人员联系");
return(false);
}
finally
{
con.Close();
}
}
private void button_ReadInfo_Click(object sender, EventArgs e)
{
int booknum = (int)BookNum.Value;
//输出
if (booknum == 0)
{
MessageBox.Show("请输入还书数量!");
return;
}
if (BookList == null)
{
BookList = new List <BookLabel>();
}
else
{
BookList.Clear();
}
//读取书籍信息
BookList = reader.GetReader(booknum);
if (BookList == null)
{
MessageBox.Show("未感应到任何书籍!");
return;
}
///测试
//booknum = 2;
//BookList.Add(new BookLabel("L0001", true));
//BookList.Add(new BookLabel("L0002", true));
//剔除没被借走的书籍
BookList.RemoveAll(book => book.SIG == true);
if (BookList.Count() == 0)
{
MessageBox.Show("未感应到任何书籍!");
return;
}
SqlConnection con = SqlConnect.getConn();
try
{
if (!DS.Tables.Contains("books"))//判断是否有表
{
DataTable bookTable = new DataTable("books");
DS.Tables.Add(bookTable);
}
else
{
DS.Tables["books"].Clear();
}
//循环遍历读到的标签号，其中剔除掉已经被借走的书籍
for (int i = 0; i < BookList.Count(); i++)
{
//创建查询语句
string SelectText = "select * from V_Return where L_Id = " + "'" + BookList[i].ID + "' and BR_IsReturn = 0";
//定义创建sqlcommand并赋值给da
SqlCommand cmd = new SqlCommand();
cmd.CommandText = SelectText;
cmd.Connection = con;
da.SelectCommand = cmd;
con.Open();
//获取一本书
da.Fill(DS, "books");
con.Close();
}
//将读到绑定到GridView上
GridView_Return.AutoGenerateColumns = false;
//设置列的对应关系
GridView_Return.Columns["Col1_BookName"].DataPropertyName = "B_Name";
GridView_Return.Columns["Col1_Label"].DataPropertyName = "L_Id";
GridView_Return.Columns["Col1_BookId"].DataPropertyName = "B_Id";
GridView_Return.Columns["Col1_Reader"].DataPropertyName = "P_Id";
//GridView_Return.Columns["Col1_Start"].DataPropertyName = "BR_Start";
//绑定
GridView_Return.DataSource = DS;
GridView_Return.DataMember = "books";
DateTime dateTime = DateTime.Now;
DateTime startTime;
DateTime deadlineTime;
for (int i = 0; i < GridView_Return.Rows.Count; i++)
{
startTime = (DateTime)DS.Tables["books"].Rows[i]["BR_Start"];
deadlineTime = startTime.AddDays(30);
GridView_Return.Rows[i].Cells["Col1_Deadline"].Value = deadlineTime.ToString();
GridView_Return.Rows[i].Cells["Col1_Start"].Value = startTime.ToString();
TimeSpan daysSpan = new TimeSpan(dateTime.Ticks - startTime.Ticks);
DS.Tables["books"].Rows[i]["BR_Time"] = (int)daysSpan.TotalDays;
if (dateTime.CompareTo(deadlineTime) > 0)
{
GridView_Return.Rows[i].Cells["Col1_Status"].Value = "已超时";
}
else
{
GridView_Return.Rows[i].Cells["Col1_Status"].Value = "借阅中";
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
Console.WriteLine("读取书籍失败!");
return;
}
finally
{
con.Close();
}
}
//提交按钮
private void Submmit_Click(object sender, EventArgs e)
{
if (BookList == null)
{
MessageBox.Show("请先读取书籍后再提交！");
return;
}
//先写卡
for (int i = 0; i < BookList.Count(); i++)
{
BookList[i].SIG = true;
}
if (!reader.SetReader(BookList))
{
MessageBox.Show("写标签失败！请不要在操作期间移动书本！");
return;
}
//再写数据库
//新建连接对象
SqlConnection conn = SqlConnect.getConn();
conn.Open();
for (int i = 0; i < DS.Tables["books"].Rows.Count; i++)
{
//拼接命令字符串
string updateQuery = "update T_Borrow set BR_IsReturn=1,BR_Time = @ReadTime where P_Id=@userId and L_Id=@labelId and BR_Start=@starttime";
//新建命令对象
SqlCommand cmd = new SqlCommand(updateQuery, conn);
//添加参数
cmd.Parameters.Add(new SqlParameter("@ReadTime", DS.Tables["books"].Rows[i]["BR_Time"]));
cmd.Parameters.Add(new SqlParameter("@userId", DS.Tables["books"].Rows[i]["P_Id"]));
cmd.Parameters.Add(new SqlParameter("@labelId", DS.Tables["books"].Rows[i]["L_Id"]));
cmd.Parameters.Add(new SqlParameter("@starttime", DS.Tables["books"].Rows[i]["BR_Start"]));
//保存执行结果
cmd.ExecuteNonQuery();
}
conn.Close();
//图书馆藏数-1
for (int i = 0; i < GridView_Return.Rows.Count; i++)
{
string bookId = GridView_Return.Rows[i].Cells["Col1_BookId"].Value.ToString();
try
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "updata T_Book set B_CountNow = B_CountNow+1 where B_Id ='" + bookId + "'";
//MessageBox.Show(cmd.CommandText); //for test
cmd.ExecuteNonQuery();
}
catch
{
Console.WriteLine("更新图书数目失败！");
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
MessageBox.Show("还书成功!点击退出登录!");
//返回主界面
main.ReturnMainWin();
}
//读取书籍信息并显示
private void button_ReadInfo_Click(object sender, EventArgs e)
{
int booknum = (int)BookNum.Value;
//输出
Console.WriteLine("booknum = " + booknum.ToString());
if (booknum == 0)
{
MessageBox.Show("请输入借书数量!");
return;
}
if (BookList == null)
{
BookList = new List <BookLabel>();
}
else
{
BookList.Clear();
}
//读取书籍
BookList = reader.GetReader(booknum);
if (BookList == null)
{
MessageBox.Show("未感应到任何书籍!");
return;
}
///测试
/*booknum = 3;
* BookList.Add(new BookLabel("L0001",false));
* BookList.Add(new BookLabel("L0002", false));
* BookList.Add(new BookLabel("L0004", true));*/
//剔除已经被借走的书籍
BookList.RemoveAll(book => book.SIG == false);
if (BookList.Count() == 0)
{
MessageBox.Show("未感应到任何书籍!");
return;
}
SqlConnection con = SqlConnect.getConn();
SqlCommandBuilder sqlBuilderda = new SqlCommandBuilder(da);
try
{
if (!DS.Tables.Contains("books"))//判断是否有表
{
DataTable bookTable = new DataTable("books");
DS.Tables.Add(bookTable);
}
else
{
DS.Tables["books"].Clear();
}
//循环遍历读到的标签号
for (int i = 0; i < BookList.Count(); i++)
{
//创建查询语句
string SelectText = "select L_Id,B_Id,B_Name from V_Book where L_Id = " + "'" + BookList[i].ID + "'";
//定义创建sqlcommand并赋值给da
SqlCommand cmd = new SqlCommand();
cmd.CommandText = SelectText;
cmd.Connection = con;
da.SelectCommand = cmd;
//if (!DS.Tables.Contains("Rowtemp"))//判断是否有表
//{
// DataTable bookTable = new DataTable("Rowtemp");
// DS.Tables.Add(bookTable);
//}
//else
// DS.Tables["Rowtemp"].Clear();
con.Open();
//获取一本书
//da.Fill(DS, "Rowtemp");
da.Fill(DS, "books");
con.Close();
//DataRow row = DS.Tables["Rowtemp"].Rows[0];
//将这一行添加到books里
//DS.Tables["books"].Rows.Add(row);
}
//将读到绑定到GridView上
GridView_Borrow.AutoGenerateColumns = false;
//设置列的对应关系
GridView_Borrow.Columns["Col1_BookName"].DataPropertyName = "B_Name";
GridView_Borrow.Columns["Col1_Label"].DataPropertyName = "L_Id";
GridView_Borrow.Columns["Col1_BookId"].DataPropertyName = "B_Id";
GridView_Borrow.DataSource = DS;
GridView_Borrow.DataMember = "books";
for (int i = 0; i < GridView_Borrow.Rows.Count; i++)
{
GridView_Borrow.Rows[i].Cells["Col1_Time"].Value = "30天";
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
Console.WriteLine("读取书籍失败!");
return;
}
finally
{
con.Close();
}
}
//提交按钮
private void Submmit_Click(object sender, EventArgs e)
{
if (BookList == null)
{
MessageBox.Show("请先读取书籍后再提交！");
return;
}
//先写卡
for (int i = 0; i < BookList.Count(); i++)
{
BookList[i].SIG = false;
}
if (!reader.SetReader(BookList))
{
MessageBox.Show("失败！请勿移动书本！请重新读取后尝试！");
return;
}
//再写数据库
//取值
string userId = pId;
string bookId;
string labelId;
string date = DateTime.Now.ToString();
int Time = 30;
int isReturn = 0;
//新建连接对象
SqlConnection conn = SqlConnect.getConn();
//借阅记录入库
for (int i = 0; i < GridView_Borrow.Rows.Count; i++)
{
labelId = GridView_Borrow.Rows[i].Cells["Col1_Label"].Value.ToString();
bookId = GridView_Borrow.Rows[i].Cells["Col1_BookId"].Value.ToString();
try
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "insert into T_Borrow(P_Id,L_Id,B_Id,BR_Start,BR_Time,BR_IsReturn) values ('" + userId + "','" + labelId + "','" + bookId + "','" + date + "'," + Time + "," + isReturn.ToString() + ")";
//MessageBox.Show(cmd.CommandText); //for test
int num1 = cmd.ExecuteNonQuery();
if (num1 > 0)
{
Console.WriteLine("入库成功！");
}
else
{
Console.WriteLine("系统故障，入库失败！");
}
}
catch
{
Console.WriteLine("插入失败，请检测输入是否合法！");
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
//图书馆藏数-1
for (int i = 0; i < GridView_Borrow.Rows.Count; i++)
{
bookId = GridView_Borrow.Rows[i].Cells["Col1_BookId"].Value.ToString();
try
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "updata T_Book set B_CountNow = B_CountNow-1 where B_Id ='" + bookId + "'";
//MessageBox.Show(cmd.CommandText); //for test
cmd.ExecuteNonQuery();
}
catch
{
Console.WriteLine("更新图书数目失败！");
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
MessageBox.Show("借阅成功!点击退出登录!");
//返回主界面
main.ReturnMainWin();
}
private void GridViewShow()
{
SqlConnection con = SqlConnect.getConn();
string SelectText;
try
{
//创建查询语句
SelectText = "select P_Name from T_People where P_Id = '" + pId + "'";
//定义创建sqlcommand并赋值给da
SqlCommand cmdName = new SqlCommand();
cmdName.CommandText = SelectText;
cmdName.Connection = con;
da.SelectCommand = cmdName;
con.Open();
da.Fill(DS, "name");
con.Close();
if (DS.Tables["name"].Rows.Count == 0)
{
throw new Exception("没有这人名字...");
}
label_Name.Text = DS.Tables["name"].Rows[0][0].ToString() + "您好!";
//创建查询语句
SelectText = "select * from V_Return where P_Id = '" + pId + "'";
//定义创建sqlcommand并赋值给da
SqlCommand cmd = new SqlCommand();
cmd.CommandText = SelectText;
cmd.Connection = con;
da.SelectCommand = cmd;
con.Open();
da.Fill(DS, "books");
con.Close();
//将读到绑定到GridView上
GridView_Sch.AutoGenerateColumns = false;
//设置列的对应关系
GridView_Sch.Columns["Col_BookName"].DataPropertyName = "B_Name";
GridView_Sch.Columns["Col_BookId"].DataPropertyName = "B_Id";
GridView_Sch.Columns["Col_Reader"].DataPropertyName = "P_Id";
GridView_Sch.Columns["Col_ReadDay"].DataPropertyName = "BR_Time";
//GridView_Return.Columns["Col1_Start"].DataPropertyName = "BR_Start";
//绑定
GridView_Sch.DataSource = DS;
GridView_Sch.DataMember = "books";
DateTime dateTime = DateTime.Now;
DateTime startTime;
DateTime deadlineTime;
int nowBook = 0;
int timeoutBook = 0;
for (int i = 0; i < GridView_Sch.Rows.Count; i++)
{
startTime = (DateTime)DS.Tables["books"].Rows[i]["BR_Start"];
deadlineTime = startTime.AddDays(30);
GridView_Sch.Rows[i].Cells["Col_Deadline"].Value = deadlineTime.ToString();
GridView_Sch.Rows[i].Cells["Col_Start"].Value = startTime.ToString();
if ((int)DS.Tables["books"].Rows[i]["BR_Isreturn"] == 1)
{
GridView_Sch.Rows[i].Cells["Col_Status"].Value = "已归还";
}
else
{
if (dateTime.CompareTo(deadlineTime) > 0)
{
GridView_Sch.Rows[i].Cells["Col_Status"].Value = "已超时";
timeoutBook += 1;
nowBook += 1;
}
else
{
GridView_Sch.Rows[i].Cells["Col_Status"].Value = "借阅中";
nowBook += 1;
}
}
}
//显示当前借书
label_Now.Text = nowBook.ToString() + "本";
label_Timeout.Text = timeoutBook.ToString() + "本";
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
Console.WriteLine("读取书籍失败!");
return;
}
finally
{
con.Close();
}
}