private void button3_Click(object sender, EventArgs e)
{
try
{
int reader_datalist_index = reader_datalist.CurrentRow.Index;
string rrid = reader_datalist.Rows[reader_datalist_index].Cells[0].Value.ToString().Trim();
string rname = reader_datalist.Rows[reader_datalist_index].Cells[1].Value.ToString().Trim();
string rsex = reader_datalist.Rows[reader_datalist_index].Cells[2].Value.ToString().Trim();
string rbcnum = reader_datalist.Rows[reader_datalist_index].Cells[3].Value.ToString().Trim();
string rbhnum = reader_datalist.Rows[reader_datalist_index].Cells[4].Value.ToString().Trim();
string rwdep = reader_datalist.Rows[reader_datalist_index].Cells[5].Value.ToString().Trim();
string rtel = reader_datalist.Rows[reader_datalist_index].Cells[6].Value.ToString().Trim();
string sql_fetch_account_psw = "select account, psw from logintable where rid = '" + rrid + "'";
string account = OprSql.Queue(sql_fetch_account_psw, "account and psw").Tables["account and psw"].Rows[0][0].ToString();
string psw = OprSql.Queue(sql_fetch_account_psw, "account and psw").Tables["account and psw"].Rows[0][1].ToString();
ModifyReader subForm = new ModifyReader(rrid, rname, rsex, rbcnum, rbhnum, rwdep, rtel, account, psw);
subForm.Owner = this;
subForm.Show();
}
catch (Exception alter_e)
{
MessageBox.Show("未选中行");
return;
}
button4_Click_1(this, e);
}
private void Alter_reader_btn(object sender, EventArgs e)
{
string rrid = alter_reader_rid.Text.Trim();
string account = alter_reader_account.Text.Trim();
string rsex = alter_reader_rsex.Text.Trim();
int rbcnum = int.Parse(alter_reader_rbcnum.Text);
string rname = alter_reader_rname.Text.Trim();
string rwdep = alter_reader_rwdep.Text.Trim();
string rtel = alter_reader_rtel.Text.Trim();
string psw = alter_reader_psw.Text.Trim();
int rbhnum = rbhnum_p;
string sql_alter_readertable = "update readertable" +
" set rname = '" + rname + "', rsex = " + "'" + rsex + "', rbcnum = " + rbcnum + ", rbhnum = " + rbhnum + ", rwdep = '" + rwdep + "', rtel = '" + rtel + "' where rrid = '" + rrid + "'";
string sql_alter_logintable = "update logintable set account = '" + account + "', psw = '" + psw + "' where rid = '" + rrid + "'";
try
{
OprSql.ExecuteSql(sql_alter_readertable);
OprSql.ExecuteSql(sql_alter_logintable);
}
catch
{
MessageBox.Show("违反约束或与数据库连接失败，修改失败");
return;
}
MessageBox.Show("修改成功");
ReaderManagement.form.reflash();
this.Close();
}
public void reflash()
{
string sql = "select bf.id 借阅单号,bf.brid 书号 , bname 书名,bdate 借书日期,bterminaldate 应归还日期,returndate 归还日期,fine 罚款 from borinfotable bf,booktable bt where bf.returndate is null and bf.brid = bt.bid and rrrid='" + Reader.form.getRid() + "'";
DataTable dt1 = OprSql.Queue(sql, "borinfo").Tables["borinfo"];
if (dt1.Rows.Count != 0)
{
rbdatalist.DataSource = dt1;
}
}
private void button4_Click_1(object sender, EventArgs e)
{
string rrid = textBox1.Text.Trim();
string rname = textBox2.Text.Trim();
string rsex = textBox3.Text.Trim();
string rwdep = textBox6.Text.Trim();
string rtel = textBox5.Text.Trim();
sql = "select rrid 借书证号, rname 姓名, rsex 性别, rbcnum 可借数量, rbhnum 已借数量, rwdep 工作部门, rtel 联系电话 from readertable where rrid like '%" + rrid + "%' and (rname like '%" + rname + "%'or rname is NULL) and (rsex like '%" + rsex + "%' or rsex is NULL) and (rwdep like '%" + rwdep + "%' or rwdep is NULL) and (rtel like '%" + rtel + "%' or rtel is NULL)";
sql_borinfo = "select borinfotable.id 借阅记录号, rrrid 借书证号, rname 姓名, brid 书号, bname 书名 from readertable, booktable, borinfotable " +
"where rrid like '%" + rrid + "%' and rrid = rrrid and bid = brid and rname like '%" + rname + "'";
this.reader_datalist.DataSource = OprSql.Queue(sql, "readers").Tables["readers"];
this.reader_borinfo.DataSource = OprSql.Queue(sql_borinfo, "borinfo").Tables["borinfo"];
}
// 查询按钮
private void button4_Click(object sender, EventArgs e)
{
searchsql = "select bid 书号, bname 书名 ,bpress 出版社, author 作者,bnum-bhbnum 可借 from booktable where bborrow=1 and bnum != 0"; //不显示不可借，书本被删除(书本数量为0)
// 如果没有填写，默认查询所有的书本
string temp = "";
foreach (var t in this.Controls)
{
if (t is TextBox && !string.IsNullOrEmpty((t as TextBox).Text.Trim()))
{
temp += "and " + (t as TextBox).Name.ToString() + " like '%" +
(t as TextBox).Text.Trim() + "%' ";
}
}
searchsql += temp;
datalist.DataSource = OprSql.Queue(searchsql, "searchtable").Tables["searchtable"];
}
private void button4_Click(object sender, EventArgs e)
{
string rrid = textBox1.Text.Trim();
string rname = textBox2.Text.Trim();
string rsex = textBox3.Text.Trim();
string rwdep = textBox6.Text.Trim();
string rtel = textBox5.Text.Trim();
string sql = "select * from readertable where rrid like '%" + rrid + "%' and (rname like '%" + rname + "%'or rname is NULL) and (rsex like '%" + rsex + "%' or rsex is NULL) and (rwdep like '%" + rwdep + "%' or rwdep is NULL) and (rtel like '%" + rtel + "%' or rtel is NULL)";
try
{
this.reader_datalist.DataSource = OprSql.Queue(sql, "readers").Tables["readers"];
}
catch
{
MessageBox.Show("连接数据库失败");
}
}
private void button2_Click(object sender, EventArgs e)
{
string rrid;
try
{
int delete_readerlist_index = reader_datalist.CurrentRow.Index;
rrid = reader_datalist.Rows[delete_readerlist_index].Cells[0].Value.ToString();
}
catch (Exception index_e)
{
MessageBox.Show("未选定行");
return;
}
string sql_delete_logintable = "delete logintable where rid = '" + rrid + "'";
string sql_delete_readertable = "delete readertable where rrid = '" + rrid + "'";
//查看待删除读者是否有借阅记录，有就无法删除
string sql_test = "select * from borinfotable where rrrid = '" + rrid + "' and returndate is NULL";
int test_rows = OprSql.Queue(sql_test, "test_delete").Tables["test_delete"].Rows.Count;
if (test_rows > 0)
{
MessageBox.Show("该读者有未归还书籍，不能删除");
return;
}
try
{
string sql_delete_borinfotable = "delete from borinfotable where rrrid = '" + rrid + "'";
OprSql.ExecuteSql(sql_delete_borinfotable);
OprSql.ExecuteSql(sql_delete_logintable);
OprSql.ExecuteSql(sql_delete_readertable);
}
catch
{
MessageBox.Show("无法与数据库连接，删除失败");
return;
}
MessageBox.Show("删除成功！");
button4_Click_1(this, e);
}
/* 登录按钮
*
* 1.验证用户名和密码中的信息，主要是是否存在空值的情况，给出提示
*
* 2.查询相关的数据库中的信息，看又没有相关的用户，没有给出提示
*
* 3. 否则跳转到相应的Form中
*
*/
private void button1_Click(object sender, EventArgs e)
{
//1. 获取相关的信息验证
string ln = loginname.Text.Trim();
string pw = psw.Text.Trim();
if (ln == "" || pw == "")
{
MessageBox.Show("账号密码不能为空");
return;
}
string sqlSearch = "select * from logintable where account='" + ln +
"' and psw='" + pw + "'";
DataTable dt = OprSql.Queue(sqlSearch, "logintable").Tables["logintable"];
if (dt.Rows.Count == 0)
{
MessageBox.Show("账号或者密码错误！");
return;
}
else
{
DataRow dr = dt.Rows[0];
if (int.Parse(dr["authority"].ToString()) == 1)
{ //如果是1进入管理员 2进入读者
Administrator subForm = new Administrator();
this.Hide();
subForm.Show();
}
else
{
Reader readerForm = new Reader(dr["rid"].ToString().Trim());
this.Hide();
readerForm.Show();
}
}
}
private void button1_Click(object sender, EventArgs e)
{
string bid = bid1.Text.Trim();
string bname = bname1.Text.Trim();
string author = author1.Text.Trim();
string bpress = bpress1.Text.Trim();
string bnum = bnum1.Text.Trim();
string bhbnum = bhbnum1.Text.Trim();
string bborrow = bhbnum1.Text.Trim();
if (bid == "" || bname == "" || author == "" || bpress == "" || bnum == "" || bhbnum == "" || bborrow == "")
{
MessageBox.Show("图书信息必须齐全！");
return;
}
string sqlSearch = "select * from booktable where bid='" + bid +
"'";
DataTable dt = OprSql.Queue(sqlSearch, "logintable").Tables["logintable"];
if (dt.Rows.Count == 1)
{
MessageBox.Show("图书信息输入错误！");
bid1.Text = "";
bname1.Text = "";
bpress1.Text = "";
author1.Text = "";
bnum1.Text = "";
bhbnum1.Text = "";
bborrow1.Text = "";
return;
}
else
{
string sql = "insert into booktable values('" + bid + "','" + bname + "','" + bpress + "','" + author + "','" + bnum + "','" + bhbnum + "','" + bborrow + "')";
BooksManagement.ExecuteSql(sql);
this.Close();
}
}
private void rb_bt_Click(object sender, EventArgs e)
{
if (rbdatalist.CurrentRow == null)
{
MessageBox.Show("请正确选择你想还的书籍！");
return;
}
int index = rbdatalist.CurrentRow.Index;
string borid = rbdatalist.Rows[index].Cells[0].Value.ToString().Trim();
string datetime = DateTime.Parse(DateTime.Now.ToShortDateString()).ToString("yyyy-MM-dd");
string sql = "update borinfotable set returndate = '" + datetime + "' where id =" + borid;
if (OprSql.ExecuteSql(sql) > 0)
{
MessageBox.Show("还书成功！");
rbdatalist.DataSource = null;// 清空图书
reflash();
}
else
{
MessageBox.Show("还书失败！");
}
}
public void reflash()
{
this.reader_datalist.DataSource = OprSql.Queue(sql, "readers").Tables["readers"];
this.reader_borinfo.DataSource = OprSql.Queue(sql_borinfo, "borinfo").Tables["borinfo"];
}
private void button1_Click_1(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(add_reader_rrid.Text))
{
MessageBox.Show("借书证号不能为空！");
return;
}
if (string.IsNullOrEmpty(add_reader_account.Text) || string.IsNullOrEmpty(add_reader_psw.Text))
{
MessageBox.Show("账号密码不能为空！");
return;
}
string rrid = add_reader_rrid.Text.Trim();
string rname = add_reader_rname.Text.Trim();
//性别默认为m
string rsex = "m";
if (add_reader_rsex.Text.Trim() != "f" && add_reader_rsex.Text.Trim() != "m")
{
MessageBox.Show("无效性别，已设为默认值m");
}
else if (add_reader_rsex.Text.Trim() != "f")
{
rsex = "f";
}
int rbcnum = 16;
if (add_reader_rbcnum.Text.Trim() != "")
{
rbcnum = int.Parse(add_reader_rbcnum.Text);
}
int rbhnum = 0;
string rwdep = add_reader_rdep.Text.Trim();
string rtel = add_reader_psw.Text.Trim();
string sql = "insert into readertable(rrid, rname, rsex, rbcnum, rbhnum, rwdep, rtel)" +
" values ('" + rrid + "', '" + rname + "', '" + rsex + "', "
+ rbcnum + ", " + rbhnum + ", '" + rwdep + "', '" + rtel + "')";
//logintable
string account = add_reader_account.Text.Trim();
string psw = add_reader_psw.Text.Trim();
int authority = 2;
string rid = rrid;
string sql_login = "******" +
"values('" + account + "', '" + psw + "', " + authority + ", '" + rid + "')";
try
{
OprSql.ExecuteSql(sql);
OprSql.ExecuteSql(sql_login);
}
catch
{
MessageBox.Show("添加了重复的借书证号或账户名，新增失败");
return;
}
this.Close();
}
private void button1_Click(object sender, EventArgs e)
{ // 借阅
//先判断借书是否到上限
//判断是否有罚款 否提示交罚款 有借阅成功
string readersql = "select * from readertable where rrid = '" + Reader.form.getRid() + "'";
DataTable dreader = OprSql.Queue(readersql, "readertable").Tables["readertable"];
if (int.Parse(dreader.Rows[0]["rbcnum"].ToString()) -
int.Parse(dreader.Rows[0]["rbhnum"].ToString()) == 0)
{
// 到借阅上限，无法再借
MessageBox.Show("无法再借");
return;
}
// 是否有罚款
string sql = "select * from borinfotable where fine != 0 and rrrid='" +
Reader.form.getRid() + "'";
DataTable dt = OprSql.Queue(sql, "bortable").Tables["bortable"];
if (dt.Rows.Count != 0)
{
MessageBox.Show("有罚款不能借阅");
if (MessageBox.Show("是否提交罚款？", "提示", MessageBoxButtons.YesNo)
== DialogResult.Yes)
{
// 提交罚款后，将所有的罚款进行更新为0
string updateFine = "update borinfotable set fine=0 where fine != 0 and rrrid='" + Reader.form.getRid() + "'";
if (OprSql.ExecuteSql(updateFine) > 0)
{
MessageBox.Show("交款成功!");
}
else
{
MessageBox.Show("交款失败，请再次尝试!");
}
}
else
{
// 拒绝提交罚款
return;
}
}
else
{
// 借书部分
if (datalist.CurrentRow == null)
{
MessageBox.Show("请先查询！");
return;
}
int index = datalist.CurrentRow.Index;
string bookid = datalist.Rows[index].Cells[0].Value.ToString().Trim();
string nowString = DateTime.Parse(DateTime.Now.ToShortDateString()).ToString("yyyy-MM-dd");
string terminalString = DateTime.Parse(DateTime.Now.AddDays(60).ToShortDateString()).ToString("yyyy-MM-dd");
// 更改表，使得同一个人可以借多本书
string borsql = "insert into borinfotable(rrrid,brid,bdate,bterminaldate,returndate,fine) values('" + Reader.form.getRid() + "','" + bookid + "','" + nowString + "','" +
terminalString + "'," + "null" + "," + 0 + ")";
// 判断是否还有书本可借
if (int.Parse(datalist.Rows[index].Cells[4].Value.ToString().Trim()) == 0)
{
MessageBox.Show("此本书已经被借完，请等候！");
return;
}
try
{
// 已经借阅，不可重复借阅
string hasborrow = "select * from borinfotable where rrrid='" + Reader.form.getRid() + "' and brid='" +
bookid + "' and returndate is null";
if (OprSql.Queue(hasborrow, "hasbortable").Tables["hasbortable"].Rows.Count != 0)
{
MessageBox.Show("您已经借过此书，为了保证资源合理利用，请借其他书");
return;
}
if (OprSql.ExecuteSql(borsql) > 0)
{
MessageBox.Show("借书成功！");
datalist.DataSource = OprSql.Queue(searchsql, "searchtable").Tables["searchtable"];
}
}
catch (Exception ex)
{
MessageBox.Show("借书失败!");
}
}
}