private void 查询ToolStripMenuItem1_Click(object sender, EventArgs e)
{
string sql = "select [User].UserName,[User].ID,[User].CardID,ReaderType.UserType from [User],ReaderType where ReaderType.UserName=[User].UserName and ReaderType.UserType='读者'";
DataTable dataTable = SQLServer.ExecuteQuery(sql);
dataGridView1.DataSource = dataTable;
dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
}
private void loadData()
{
string sql1 = "select UserName ,BookNum from bookReturning1";
DataTable dt1 = SQLServer.ExecuteQuery(sql1);
dataGridView1.DataSource = dt1;
dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
}
private void load_data()
{
string sql = "select * from bookUserWanted1";
DataTable dt = SQLServer.ExecuteQuery(sql);
dataGridView1.DataSource = dt;
dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
}
private void FormAdminBookInfo_Load_1(object sender, EventArgs e)
{
string sql = "select * from Book";
DataTable dt = SQLServer.ExecuteQuery(sql);
dataGridView1.DataSource = dt;
dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
}
private void 注册_Click(object sender, EventArgs e)
{
string username = txt_username.Text.Trim();
string identitycard = "";
string cardid = "";
string userworkplace = " ";
string sex = " ";
string useraddress = " ";
string birthday = " ";
string usertype = "读者";
string password = txt_password.Text.Trim();
string passwordConfirm = txt_password_confirm.Text.Trim();
string number = "5";
if (username == "" || password == "" || passwordConfirm == "")
{
MessageBox.Show("请填入所有信息");
}
else if (password != passwordConfirm)
{
MessageBox.Show("两次输入的密码不一致");
}
else if (CodeLevel(password) < 2)
{
}
else
{
if (!check_user_exist(username))//检测用户名是否存在，此处为用户名不存在的条件
{
string sql2 = "insert into ReaderType (UserName ,UserType)values(";
sql2 += "'" + username + "',";
sql2 += "'" + usertype + "')";
string sql1 = "insert into [User] (UserName,CardID,ID,UserWorkPlace,Code,UserAddress,Sex ,Birthday,Number)values(";
sql1 += "'" + username + "',";
sql1 += "'" + cardid + "',";
sql1 += "'" + identitycard + "',";
sql1 += "'" + userworkplace + "',";
sql1 += "'" + password + "',";
sql1 += "'" + useraddress + "',";
sql1 += "'" + sex + "',";
sql1 += "'" + birthday + "',";
sql1 += "'" + number + "')";
SQLServer.ExecuteNonQuery(sql1);
try
{
SQLServer.ExecuteNonQuery(sql2);
MessageBox.Show("注册成功！", "提示");
}
catch (Exception ex)
{
MessageBox.Show("注册失败！", "提示");
Console.WriteLine(ex);
}
}
}
}
private void 管理借书信息ToolStripMenuItem_Click(object sender, EventArgs e)
{
还书.Visible = false;
string sql = "select ordertable.UserName ,ordertable.OrderNum,ordertable.OrderBookName from ordertable";
DataTable dt = SQLServer.ExecuteQuery(sql);
dataGridView1.DataSource = dt;
dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
}
private string get_user_borrowed_num(string username)
{
string sql5 = "select Number from [User] where UserName="******"'" + username + "'";
DataTable dt = SQLServer.ExecuteQuery(sql5);
string number = dt.Rows[0]["Number"].ToString();
return(number);
}
private void load_data()
{
string sql = "select ordertable.UserName,ordertable.OrderNum,ordertable.OrderBookName from ordertable where ordertable.UserName ="******"'" + username + "'";
DataTable dt = SQLServer.ExecuteQuery(sql);
dataGridView1.DataSource = dt;
dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
}
private void button1_Click(object sender, EventArgs e)
{
string sql = "update message set Reply=";
sql += "'" + textBox1.Text.ToString() + "'";
sql += "where UserMessage=";
sql += "'" + message + "'";
DataTable dataTable = SQLServer.ExecuteQuery(sql);
MessageBox.Show("回复成功！", "提示");
}
/// <summary>
/// 检测密码是否合法
/// </summary>
/// <param name="password"></param>
/// <param name="password1"></param>
/// <param name="password2"></param>
/// <returns></returns>
private bool check_pass(string password, string password1, string password2)
{
if (password == "")
{
MessageBox.Show("原始密码不能为空");
return(false);
}
else if (password1 == "")
{
MessageBox.Show("新密码不能为空");
return(false);
}
else if (password2 == "")
{
MessageBox.Show("重复密码不能为空");
return(false);
}
else
{
if (password1 != password2)
{
MessageBox.Show("两次输入的密码不同");
return(false);
}
else
{
if (password == password1)
{
MessageBox.Show("新密码不能与原始密码相同");
return(false);
}
else
{
string sql = "select * from [User] where UserName ="******"'" + username + "'";
sql += "and Code= ";
sql += "'" + password + "'";
DataTable dt = SQLServer.ExecuteQuery(sql);
if (dt.Rows.Count > 0)
{
return(true);
}
else
{
MessageBox.Show("原始密码输入错误");
return(false);
}
}
}
}
}
private void 借书ToolStripMenuItem_Click(object sender, EventArgs e)
{
if (!check_is_binded(username))
{
MessageBox.Show("您还未绑定身份，不能使用该功能！");
}
else
{
if (get_user_borrowed_num(username) == "5")
{
MessageBox.Show("您已经借了5本书，达到上限");
}
else if (dataGridView1.SelectedRows.Count > 0)
{
MessageBoxButtons messButton = MessageBoxButtons.OKCancel;
DialogResult dr = MessageBox.Show("确定要借这本书吗?", "确定", messButton);
if (dr == DialogResult.OK)
{
if (!check_book_user_exist(username, dataGridView1.SelectedRows[0].Cells["BookNum"].Value.ToString()))
{
string sql = "insert into ordertable(UserName,OrderNum,OrderBookName)values( ";
sql += "'" + username + "',";
sql += "'" + dataGridView1.SelectedRows[0].Cells["BookNum"].Value.ToString() + "',";
sql += "'" + dataGridView1.SelectedRows[0].Cells["BookName"].Value.ToString() + "')";
string sql2 = "update [User] set Number = Number +1 where UserName = "******"'" + username + "'";
try
{
SQLServer.ExecuteNonQuery(sql);
MessageBox.Show("借书成功！");
load_data();
SQLServer.ExecuteNonQuery(sql2);
FormUserBorrowedBooks formBorrowedBooks = new FormUserBorrowedBooks(username);
Visible = false;
formBorrowedBooks.ShowDialog();
Visible = true;
}
catch (Exception ex)
{
MessageBox.Show("借书失败");
Console.WriteLine(ex);
}
}
else
{
MessageBox.Show("您已经借过这本书");
}
}
}
}
}
private bool check_pass(string username, string username1, string username2)
{
if (username == "")
{
MessageBox.Show("原始用户名不能为空");
return(false);
}
else if (username1 == "")
{
MessageBox.Show("新用户名不能为空");
return(false);
}
else if (username2 == "")
{
MessageBox.Show("重复新用户名不能为空");
return(false);
}
else
{
if (username1 != username2)
{
MessageBox.Show("两次输入的用户名不同");
return(false);
}
else
{
if (username == username1)
{
MessageBox.Show("新密码不能与原始密码相同");
return(false);
}
else
{
string sql = "select * from [User] where UserName ="******"'" + username + "'";
DataTable dt = SQLServer.ExecuteQuery(sql);
if (dt.Rows.Count > 0)
{
return(true);
}
else
{
MessageBox.Show("原始用户名输入错误");
return(false);
}
}
}
}
}
/// <summary>
/// 检查学生信息是否绑定过其他用户
/// </summary>
/// <param name="studentno"></param>
/// <returns></returns>
private bool checkStudentsticked(string cardid)
{
string sql = "select * from [User] where CardID =";
sql += "'" + cardid + "'";
DataTable dt = SQLServer.ExecuteQuery(sql);
if (dt.Rows.Count > 0)
{
return(true);
}
else
{
return(false);
}
}
private void 重置密码ToolStripMenuItem_Click(object sender, EventArgs e)
{
if (dataGridView1.SelectedRows.Count > 0)
{
MessageBoxButtons messButton = MessageBoxButtons.OKCancel;
DialogResult dr = MessageBox.Show("确定要重置该用户的密码吗?", "确定", messButton);
if (dr == DialogResult.OK)
{
string thisusername = dataGridView1.SelectedRows[0].Cells["UserName"].Value.ToString();
string sql = "update [User] set Code ='a123456'";
sql += "where UserName = "******"'" + thisusername + "'";
SQLServer.ExecuteNonQuery(sql);
MessageBox.Show("重置成功！");
}
}
}
private bool check_pass(string bookid)
{
string sql = "select * from Book where BookNum =";
sql += "'" + bookid + "'";
DataTable dt = SQLServer.ExecuteQuery(sql);
if (dt.Rows.Count > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 检测是否已绑定个人信息
/// </summary>
/// <param name="username"></param>
/// <returns></returns>
private bool check_is_binded(string username)
{
string sql = "select * from [User] where UserName ="******"'" + username + "'";
sql += "and CardID =''";
sql += "and ID=''";
DataTable dt = SQLServer.ExecuteQuery(sql);
if (dt.Rows.Count > 0)
{
return(false);
}
else
{
return(true);
}
}
/// <summary>
/// 检测用户名是否已存在
/// </summary>
/// <param name="username"></param>
/// <returns></returns>
private bool check_user_exist(string username)
{
string sql = "select UserName , Code from [User] where UserName ="******"'" + username + "'";
DataTable dt = SQLServer.ExecuteQuery(sql);
if (dt.Rows.Count > 0)
{
MessageBox.Show("用户名被占用");
return(true);
}
else
{
return(false);
}
}
private void 新书通报ToolStripMenuItem_Click(object sender, EventArgs e)
{
string sql = "select BookName,BookAuthor,BookPress,Edition,ISBN from NBook ";
DataTable dataTable = SQLServer.ExecuteQuery(sql);
dataGridView1.DataSource = dataTable;
dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
dataGridView1.Columns["BookName"].HeaderText = "书名";
dataGridView1.Columns["BookAuthor"].HeaderText = "作者";
dataGridView1.Columns["BookPress"].HeaderText = "出版社";
dataGridView1.Columns["Edition"].HeaderText = "版次";
dataGridView1.Columns["ISBN"].HeaderText = "ISBN号";
dataGridView1.Columns["BookName"].Width = 70; //设置列宽度
dataGridView1.Columns["BookAuthor"].Width = 70; //设置列宽度
dataGridView1.Columns["BookPress"].Width = 80; //设置列宽度
dataGridView1.Columns["Edition"].Width = 70; //设置列宽度
dataGridView1.Columns["ISBN"].Width = 90; //设置列宽度
}
private bool check_book_user_exist(string username, string bookid)
{
string sql = "select * from ordertable where UserName = "******"'" + username + "'";
sql += "and OrderNum = ";
sql += "'" + bookid + "'";
DataTable dt = SQLServer.ExecuteQuery(sql);
if (dt.Rows.Count > 0)
{
return(true);
}
else
{
return(false);
}
}
private void 用户角色管理ToolStripMenuItem_Click(object sender, EventArgs e)
{
FormChangeReaderType formChangeReaderType = new FormChangeReaderType(username);
Visible = false;
formChangeReaderType.ShowDialog();
Visible = true;
string sql = "select UserName from ReaderType where UserName="******"'" + username + "'";
sql += "and UserType='管理员'";
DataTable dataTable = SQLServer.ExecuteQuery(sql);
if (dataTable.Rows.Count == 0)
{
this.Close();
}
}
private void button1_Click(object sender, EventArgs e)
{
string message = textBox1.Text.Trim().ToString();
string reply = " ";
string sql = "insert into message(UserMessage,Reply) values (";
sql += "'" + message + "',";
sql += "'" + reply + "')";
try
{
SQLServer.ExecuteNonQuery(sql);
MessageBox.Show("留言成功！", "提示");
}
catch (Exception ex)
{
MessageBox.Show("留言失败！");
Console.WriteLine(ex);
}
}
private void 除ToolStripMenuItem_Click(object sender, EventArgs e)
{
if (dataGridView1.SelectedRows.Count > 0)
{
MessageBoxButtons messButton = MessageBoxButtons.OKCancel;
DialogResult dr = MessageBox.Show("确定要删除该用户吗?", "确定", messButton);
if (dr == DialogResult.OK)
{
string thisusername = dataGridView1.SelectedRows[0].Cells["UserName"].Value.ToString();
string sql = "delete from [User] where UserName="******"'" + thisusername + "'";
string sql2 = "delete from ReaderType where UserName="******"'" + thisusername + "'";
SQLServer.ExecuteNonQuery(sql);
SQLServer.ExecuteNonQuery(sql2);
MessageBox.Show("删除成功！");
}
}
}
private void button2_Click(object sender, EventArgs e)
{
if (dataGridView1.SelectedRows.Count > 0)
{
if (dataGridView1.SelectedRows[0].Cells["UserType"].Value.ToString() != "读者" && dataGridView1.SelectedRows[0].Cells["UserName"].Value.ToString() != "admin" && dataGridView1.SelectedRows[0].Cells["UserName"].Value.ToString() != username)
{
MessageBoxButtons messButton = MessageBoxButtons.OKCancel;
DialogResult dr = MessageBox.Show("确定要移除该管理员权限吗?", "确定", messButton);
if (dr == DialogResult.OK)
{
string thisusername = dataGridView1.SelectedRows[0].Cells["UserName"].Value.ToString();
string sql = "update ReaderType set UserType ='读者'";
sql += "where UserName = "******"'" + thisusername + "'";
SQLServer.ExecuteNonQuery(sql);
MessageBox.Show("移除成功！");
}
}
else if (dataGridView1.SelectedRows[0].Cells["UserType"].Value.ToString() != "读者" && dataGridView1.SelectedRows[0].Cells["UserName"].Value.ToString() == username && username != "admin")
{
MessageBoxButtons messButton = MessageBoxButtons.OKCancel;
DialogResult dr = MessageBox.Show("确定要移除您的管理员权限吗?", "确定", messButton);
if (dr == DialogResult.OK)
{
string thisusername = dataGridView1.SelectedRows[0].Cells["UserName"].Value.ToString();
string sql = "update ReaderType set UserType ='读者'";
sql += "where UserName = "******"'" + thisusername + "'";
SQLServer.ExecuteNonQuery(sql);
MessageBox.Show("移除成功！");
this.Close();
}
}
else if (dataGridView1.SelectedRows[0].Cells["UserName"].Value.ToString() == "admin")
{
MessageBox.Show("该用户管理员权限不可移除", "提示");
}
else
{
MessageBox.Show("该用户已经是读者", "提示");
}
}
}
private void FormUserMain_Load(object sender, EventArgs e)
{
string sql = "select BookNum,BookName,BookAuthor,BookPress,ISBN from Book ";
DataTable dataTable = SQLServer.ExecuteQuery(sql);
dataGridView1.DataSource = dataTable;
dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
dataGridView1.Columns["BookNum"].HeaderText = "书目号";
dataGridView1.Columns["BookName"].HeaderText = "书名";
dataGridView1.Columns["BookAuthor"].HeaderText = "作者";
dataGridView1.Columns["BookPress"].HeaderText = "出版社";
dataGridView1.Columns["ISBN"].HeaderText = "ISBN号";
// dataGridView1.Columns[0].Width = 80;//设置列宽度
dataGridView1.Columns["BookNum"].Width = 40; //设置列宽度
dataGridView1.Columns["BookName"].Width = 40; //设置列宽度
dataGridView1.Columns["BookAuthor"].Width = 40; //设置列宽度
dataGridView1.Columns["BookPress"].Width = 40; //设置列宽度
dataGridView1.Columns["ISBN"].Width = 40; //设置列宽度
}
/// <summary>
/// 检查读者的用户名，密码，以及身份
/// </summary>
/// <param name="username"></param>
/// <param name="password"></param>
/// <returns></returns>
private bool check_password(string username, string password)
{
string sql = "select * from [User] where Code =";
sql += "'" + password + "'";
sql += "and UserName in(select UserName from ReaderType where Username="******"'" + username + "'";
sql += "and UserType='读者')";
DataTable dt = SQLServer.ExecuteQuery(sql);
if (dt.Rows.Count > 0)
{
return(true);
}
else
{
return(false);
}
}
private void 确认绑定_Click_1(object sender, EventArgs e)
{
string cardid = txt_cardid.Text.Trim();
string identitycard = txt_identitycard.Text.Trim();
if (cardid == "" || identitycard == "")
{
MessageBox.Show("请填入所有信息");
}
else if (!checkStudentExist(cardid, identitycard))
{
if (!checkStudentsticked(cardid))
{
string sql = "update [User] set CardID =";
sql += "'" + cardid + "'";
sql += ",ID=";
sql += "'" + identitycard + "'";
sql += "where UserName = "******"'" + username + "'";
try
{
SQLServer.ExecuteNonQuery(sql);
MessageBox.Show("绑定成功！");
Close();
}
catch (Exception ex)
{
MessageBox.Show("绑定失败！");
Console.WriteLine(ex);
}
}
else
{
MessageBox.Show("该学生信息已经绑定过");
}
}
else
{
MessageBox.Show("学生信息有错误");
}
}
private void 查询ToolStripMenuItem_Click(object sender, EventArgs e)
{
label1.Visible = true;
label2.Visible = true;
txt_bookname.Visible = true;
txt_press.Visible = true;
借阅ToolStripMenuItem.Visible = true;
if (txt_bookname.Text == "" && txt_press.Text == "")
{
string sql = "select BookNum,BookName,BookAuthor,BookPress,ISBN from Book ";
DataTable dataTable = SQLServer.ExecuteQuery(sql);
dataGridView1.DataSource = dataTable;
dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
}
else
{
string sql = "select BookNum,BookName,BookAuthor,BookPress,ISBN from Book where ";
if (txt_bookname.Text != "")
{
sql += "BookName like ";
sql += "'%" + txt_bookname.Text.Trim() + "%'";
if (txt_press.Text != "")
{
sql += "and BookAuthor like";
sql += "'%" + txt_press.Text.Trim() + "%'";
}
}
else
{
sql += "BookAuthor like";
sql += "'%" + txt_press.Text.Trim() + "%'";
}
DataTable dataTable = SQLServer.ExecuteQuery(sql);
if (dataTable.Rows.Count == 0)
{
MessageBox.Show("没有符合条件的书");
}
dataGridView1.DataSource = dataTable;
dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
}
}
private void 查询ToolStripMenuItem_Click(object sender, EventArgs e)
{
if (textBox_UserName.Text == "" && textBox_CardID.Text == "")
{
string sql = "select [User].UserName,[User].ID,[User].CardID,ReaderType.UserType from [User],ReaderType where ReaderType.UserName=[User].UserName";
DataTable dataTable = SQLServer.ExecuteQuery(sql);
dataGridView1.DataSource = dataTable;
dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
}
else
{
string sql = "select [User].UserName,[User].ID,[User].CardID,ReaderType.UserType from [User],ReaderType where ";
if (textBox_UserName.Text != "")
{
sql += "[User].UserName like ";
sql += "'%" + textBox_UserName.Text.Trim() + "%'";
sql += "and ReaderType.UserName like ";
sql += "'%" + textBox_UserName.Text.Trim() + "%'";
sql += "and ReaderType.UserName=[User].UserName ";
if (textBox_CardID.Text != "")
{
sql += "and [User].CardID like";
sql += "'%" + textBox_CardID.Text.Trim() + "%'";
}
}
else
{
sql += "[User].CardID like";
sql += "'%" + textBox_CardID.Text.Trim() + "%'";
sql += "and ReaderType.UserName=[User].UserName ";
}
DataTable dataTable = SQLServer.ExecuteQuery(sql);
if (dataTable.Rows.Count == 0)
{
MessageBox.Show("没有符合条件的读者");
}
dataGridView1.DataSource = dataTable;
dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
}
}
private void 添加新书_Click(object sender, EventArgs e)
{
string bookid = txt_bookid.Text.Trim();
string bookname = txt_bookname.Text.Trim();
string pressname = txt_press.Text.Trim();
string bookid1 = txt_classification.Text.Trim();
string isbn = txt_number.Text.Trim();
if (bookid == "" || bookname == "" || pressname == "" || bookid1 == "" || isbn == "")
{
MessageBox.Show("请填入所有的信息");
}
else
{
if (!check_pass(bookid))
{
string sql = "insert into Book (BookNum,BookName,BookAuthor,BookID,ISBN)values(";
sql += "'" + bookid + "',";
sql += "'" + bookname + "',";
sql += "'" + pressname + "',";
sql += "'" + bookid1 + "',";
sql += "'" + isbn + "')";
try
{
SQLServer.ExecuteNonQuery(sql);
MessageBox.Show("增书成功！");
}
catch (Exception ex)
{
MessageBox.Show("增书失败！");
Console.WriteLine(ex);
}
}
else
{
MessageBox.Show("该编号已经被占用");
}
}
}
private void button1_Click(object sender, EventArgs e)
{
if (dataGridView1.SelectedRows.Count > 0)
{
if (dataGridView1.SelectedRows[0].Cells["UserType"].Value.ToString() != "管理员")
{
MessageBoxButtons messButton = MessageBoxButtons.OKCancel;
DialogResult dr = MessageBox.Show("确定要将该用户设为管理员吗?", "确定", messButton);
if (dr == DialogResult.OK)
{
string thisusername = dataGridView1.SelectedRows[0].Cells["UserName"].Value.ToString();
string sql = "update ReaderType set UserType ='管理员'";
sql += "where UserName = "******"'" + thisusername + "'";
SQLServer.ExecuteNonQuery(sql);
MessageBox.Show("设置成功！");
}
}
else
{
MessageBox.Show("该用户已经是管理员", "提示");
}
}
}