protected void rptBooks_ItemCommand(object source, RepeaterCommandEventArgs e)
{
if (e.CommandName == "Delete")
{
string isbn = e.CommandArgument.ToString().Trim();
string sqlISdelete = "select * from Library.dbo.Repertory where Risbn='" + e.CommandArgument.ToString().Trim() + "' and Rstate=0 ";
string sqlDel = "delete Library.dbo.Books where ISBN='" + isbn + "'";
DataTable dt = Class_Login.SelectT(sqlISdelete);
if (dt.Rows.Count > 0)
{
Response.Write("<script>window.alert('此书不能删除，有用户在阅！')</script>");
return;
}
else
{
int i;
i = Class_Login.Operate(sqlDel);
if (i != 0 || i != -1)
{
string sqlLid = "select mid from Library.dbo.Manager where Mname='" + Session["Succeed_main"].ToString().Trim() + "'";
string Lid = Class_Login.SelectHead(sqlLid);
string sqlAddlog = "insert into Library.dbo.OperateLog values('" + Lid + "','" + Session["Succeed_main"].ToString().Trim() + "',Datename(year,GetDate())+'-'+Datename(month,GetDate())+'-'+Datename(day,GetDate())+'-'+DATENAME(WEEKDAY,GETDATE())+'-'+DATENAME(HOUR,GETDATE())+':'+DATENAME(MINUTE,GETDATE())+':'+DATENAME(SECOND,GETDATE()),'删除,ISBN为" + isbn + "的图书')";
Class_Login.Operate(sqlAddlog);
Response.Write("<script>window.alert('删除成功!');location.href='books.aspx'</script>");
}
else
{
Response.Write("<script>window.alert('删除失败!数据库删除失败0 -1 ')</script>");
}
}
}
}
protected void rptNowborrow_ItemCommand(object source, RepeaterCommandEventArgs e)
{
if (e.CommandName == "return")
{
if (Session["Succeed_name"] == null)
{
Response.Write("<script>window.alert('还书失败，请登录！');location.href='Login.aspx';</script>");
}
else
{
string trid = e.CommandArgument.ToString().Trim();
string sqlreturn0 = "update Library .dbo.Repertory set Rstate =1 ,Ruid=-1 where Rid =" + trid + "";
int ts = Class_Login.Operate(sqlreturn0);
if (ts == 0 || ts == -1)
{
Response.Write("<script>alert('还书失败！归零出错')</scipt>");
return;
}
string sqladdone = "update Library .dbo.Books set Blent += 1 where ISBN = (select Risbn from Library.dbo.Repertory where Rid = " + trid + " )";
ts = Class_Login.Operate(sqladdone);
if (ts == 0 || ts == -1)
{
Response.Write("<script>alert('还书失败！BOOKS加一出错')</scipt>");
return;
}
Response.Write("<script>window.alert('还书成功！');location.href='NowBorrow.aspx';</script>");
}
}
}
protected void rptSearchmain_ItemCommand(object source, RepeaterCommandEventArgs e)
{
if (e.CommandName == "Borrow")
{
if (Session["Succeed_name"] == null)
{
Response.Write("<script>window.alert('借阅失败，请登录！');location.href='Login.aspx';</script>");
}
else
{
string bookname = e.CommandArgument.ToString();
string sqlisbn = "select ISBN from Library .dbo .Books where Bname='" + bookname + "'";
string tisbn = Class_Login.SelectHead(sqlisbn);
string sqluid = "select id from Library .dbo.Users where Username ='******'";
int uid = Convert.ToInt32(Class_Login.SelectHead(sqluid));
string sqlleft = "select Blent from Library.dbo.Books where ISBN = " + tisbn + "";
string left = Class_Login.SelectHead(sqlleft);
if (Convert.ToInt32(left) <= 0)
{
Response.Write("<script>window.alert('借阅失败，库存为零！')</script>");
return;
}
else
{
string sqldt = "select Rid from Library.dbo.Repertory where Rstate = 1 and Risbn = " + tisbn + "";
string trid = Class_Login.SelectHead(sqldt);
string sqlbind = "update Library .dbo.Repertory set Ruid =" + uid + " , Rstate=0 where Rid =" + trid + "";
int ts = Class_Login.Operate(sqlbind);
if (ts == 0 || ts == -1)
{
Response.Write("<script>window.alert('借阅失败，请重新借阅！绑定失败')</script>");
return;
}
string sqlhistory = "insert into Library.dbo.History values (" + uid + ", " + trid + "," + tisbn + ")";
ts = Class_Login.Operate(sqlhistory);
if (ts == 0 || ts == -1)
{
Response.Write("<script>window.alert('借阅失败，请重新借阅！历史失败')</script>");
return;
}
string sqlupdatebook = "update Library.dbo .Books set Blent-=1 where ISBN = " + tisbn + "";
ts = Class_Login.Operate(sqlupdatebook);
if (ts == 0 || ts == -1)
{
Response.Write("<script>window.alert('借阅失败，请重新借阅！数据-1失败')</script>");
return;
}
Response.Write("<script>window.alert('借阅成功！请妥善保管图书！');location.href='Search.aspx';</script>");
}
}
}
}
protected void rptUsers_ItemCommand(object source, RepeaterCommandEventArgs e)
{
if (e.CommandName == "Reset")
{
string sqlReset = "update Library.dbo.Users set Password='******' where id='" + e.CommandArgument.ToString().Trim() + "'";
int result = Class_Login.Operate(sqlReset);
if (result != 1)
{
Response.Write("<script>window.alert('密码重置失败！请重新重置！')</script>");
return;
}
else
{
string sqlLid = "select mid from Library.dbo.Manager where Mname='" + Session["Succeed_main"].ToString().Trim() + "'";
string Lid = Class_Login.SelectHead(sqlLid);
string sqlAddlog = "insert into Library.dbo.OperateLog values('" + Lid + "','" + Session["Succeed_main"].ToString().Trim() + "',Datename(year,GetDate())+'-'+Datename(month,GetDate())+'-'+Datename(day,GetDate())+'-'+DATENAME(WEEKDAY,GETDATE())+'-'+DATENAME(HOUR,GETDATE())+':'+DATENAME(MINUTE,GETDATE())+':'+DATENAME(SECOND,GETDATE()),'重置学号为" + e.CommandArgument.ToString().Trim() + "的学生密码')";
Class_Login.Operate(sqlAddlog);
Response.Write("<script>window.alert('密码重置成功！密码为:123456789')</script>");
}
}
else if (e.CommandName == "Delete")
{
string sqlSearch = "select Library.dbo.Repertory.Rstate from Repertory where Ruid='" + e.CommandArgument.ToString().Trim() + "' and Rstate=0";
try
{
DataTable dt = Class_Login.SelectT(sqlSearch);
if (dt.Rows.Count > 0)
{
Session["Userid"] = e.CommandArgument.ToString().Trim();
Response.Write("<script>window.alert('不能删除此用户！该用户还有未还图书');location.href='UserNotBrrow.aspx';</script>");
}
else
{
string sqlDEL = "delete from Library.dbo.Users where id='" + e.CommandArgument.ToString().Trim() + "'";
if (Class_Login.Operate(sqlDEL) == 1)
{
string sqlLid = "select mid from Library.dbo.Manager where Mname='" + Session["Succeed_main"].ToString().Trim() + "'";
string Lid = Class_Login.SelectHead(sqlLid);
string sqlAddlog = "insert into Library.dbo.OperateLog values('" + Lid + "','" + Session["Succeed_main"].ToString().Trim() + "',Datename(year,GetDate())+'-'+Datename(month,GetDate())+'-'+Datename(day,GetDate())+'-'+DATENAME(WEEKDAY,GETDATE())+'-'+DATENAME(HOUR,GETDATE())+':'+DATENAME(MINUTE,GETDATE())+':'+DATENAME(SECOND,GETDATE()),'删除,学号为" + e.CommandArgument.ToString().Trim() + "的学生')";
Class_Login.Operate(sqlAddlog);
Response.Write("<script>window.alert('删除成功！');location.href='Users.aspx';</script>");
}
else
{
Response.Write("<script>window.alert('删除失败，请重试！')</script>");
}
}
}
catch (Exception ex)
{
Response.Write("<script>window.alert('删除失败，请重试！')</script>");
}
}
}
protected void rptLog_ItemCommand(object source, RepeaterCommandEventArgs e)
{
if (e.CommandName == "Delete")
{
string sqldel = "delete from Library.dbo.OperateLog where Ltime='" + e.CommandArgument.ToString().Trim() + "'";
if (Class_Login.Operate(sqldel) == 1)
{
Response.Write("<script>window.alert('删除成功！');location.href='ManLog.aspx';</script>");
}
else
{
Response.Write("<script>window.alert('删除失败,请重试！');location.href='ManLog.aspx';</script>");
}
}
}
protected void rptre_ItemCommand(object source, RepeaterCommandEventArgs e)
{
if (e.CommandName == "Delete")
{
string sqldelstate = "select Library.dbo.Repertory.Rstate from Library.dbo.Repertory where Rid='" + e.CommandArgument.ToString().Trim() + "' ";
string sqldel = "delete from Library.dbo.Repertory where Rid='" + e.CommandArgument.ToString().Trim().ToString().Trim() + "'";
string sqloldcount = "select Library .dbo.Books.Bcount from Library .dbo.Books where ISBN =(select Library .dbo.Repertory.Risbn from Library .dbo.Repertory where rid='" + e.CommandArgument.ToString().Trim() + "') ";
string sqloldlent = "select Library .dbo.Books.Blent from Library .dbo.Books where ISBN =(select Library .dbo.Repertory.Risbn from Library .dbo.Repertory where rid='" + e.CommandArgument.ToString().Trim() + "') ";
int oldcount = Convert.ToInt32(Class_Login.SelectHead(sqloldcount));
int oldlent = Convert.ToInt32(Class_Login.SelectHead(sqloldlent));
string sqlBooksupdata = "update Library .dbo.Books set Bcount =" + (oldcount - 1) + " ,Blent=" + (oldlent - 1) + " where ISBN =(select Library .dbo.Repertory.Risbn from Library .dbo.Repertory where rid='" + e.CommandArgument.ToString().Trim() + "')";
// string sqlBooksupdata = "update Library .dbo.Books set Bcount + = -1 ,Blent + = - 1 where ISBN =(select Library .dbo.Repertory.Risbn from Library .dbo.Repertory where rid='" + e.CommandArgument.ToString().Trim() + "')";
if (Convert.ToInt32(Class_Login.SelectHead(sqldelstate)) == 1)
{
int i;
i = Class_Login.Operate(sqlBooksupdata);
if (i != 0 || i != -1)
{
Class_Login.Operate(sqldel);
string sqlLid = "select mid from Library.dbo.Manager where Mname='" + Session["Succeed_main"].ToString().Trim() + "'";
string Lid = Class_Login.SelectHead(sqlLid);
string sqlAddlog = "insert into Library.dbo.OperateLog values('" + Lid + "','" + Session["Succeed_main"].ToString().Trim() + "',Datename(year,GetDate())+'-'+Datename(month,GetDate())+'-'+Datename(day,GetDate())+'-'+DATENAME(WEEKDAY,GETDATE())+'-'+DATENAME(HOUR,GETDATE())+':'+DATENAME(MINUTE,GETDATE())+':'+DATENAME(SECOND,GETDATE()),'删除,书号" + e.CommandArgument.ToString().Trim() + "的图书')";
Class_Login.Operate(sqlAddlog);
Response.Write("<script>window.alert('删除成功!');location.href='adminre.aspx';</script>");
}
else
{
Response.Write("<script>window.alert('删除失败!数据库删除失败0 -1 ')</script>");
}
}
else
{
Response.Write("<script>window.alert('删除失败!state=0')</script>");
}
}
}
public int PwdChange(string pwd, string name)
{
string sql = "update Users set Password = upper(substring(sys.fn_sqlvarbasetostr(HashBytes('SHA1', '" + pwd + "')), 3, 32)) where Username= '******'";
return(Class_Login.Operate(sql));
}
protected void btnADD_Click(object sender, EventArgs e)
{
if (txtISBN.Text.Trim() == "" || txtName.Text.Trim() == "" || txtClass.Text.Trim() == "" || txtAuthor.Text.Trim() == "" || txtText.Text.Trim() == "" || txtCount.Text.Trim() == "" || Convert.ToInt32(txtCount.Text.Trim()) <= 0)
{
Response.Write("<script>window.alert('请填写完整信息！')</script>");
return;
}
else
{
string sqlCheckISBN = "select Library.dbo.Books.ISBN from Library.dbo.Books where ISBN='" + txtISBN.Text.Trim() + "'";
DataTable dt = Class_Login.SelectT(sqlCheckISBN);
int cc = dt.Rows.Count;
if (cc != 0)
{
Response.Write("<script>window.alert('图书已存在，请点击修改！')</script>");
return;
}
else
{
string sqlAddtoBooks = "insert into Library.dbo.Books values('" + txtISBN.Text.Trim() + "','" + txtName.Text.Trim() + "','" + txtClass.Text.Trim() + "','" + txtAuthor.Text.Trim() + "','" + txtText.Text.Trim() + "'," + Convert.ToInt32(txtCount.Text.Trim()) + "," + Convert.ToInt32(txtCount.Text.Trim()) + ")";
// string sqlAutoRid = "declare @i int declare @j int declare @s char(10) set @i=(select max(rid) from library.dbo.repertory where risbn='" + txtISBN.Text.Trim() + "')+1 set @s='" + txtISBN.Text.Trim() + "' set @j=0 while @j<" + Convert.ToInt32(txtCount.Text.Trim()) + " begin insert into library.dbo.repertory values(@i,@s,-1,1) set @j=@j+1 end";
string sqlDel = "delete Library.dbo.Books where ISBN='" + txtISBN.Text.Trim() + "'";
int pd = Class_Login.Operate(sqlAddtoBooks);
if (pd == 0 || pd == -1)
{
Response.Write("<script>window.alert('增加失败！')</script>");
return;
}
else
{
////开始自动添加仓库编号
//int jd = Class_Login.Operate(sqlAutoRid);
//if (jd == 0 || jd == -1)
//{
// Response.Write("<script>window.alert('增加失败！')</script>");
// Class_Login.Operate(sqlDel); //删除books的记录
// return;
//}
////自动添加完毕
//循环增加数据开始
//string sqlmaxrid = "select max(Rid) from Library.dbo.repertory where Risbn=" + txtISBN.Text.Trim() + "";
//int maxrid = Convert.ToInt32(Class_Login.SelectHead(sqlmaxrid));
int maxrid = Convert.ToInt32(txtISBN.Text.Trim() + "0001");
string sqlinsert = "";
for (int i = 0; i < Convert.ToInt32(txtCount.Text.Trim()); i++)
{
sqlinsert = "insert into library.dbo.repertory values(" + (maxrid++) + "," + txtISBN.Text.Trim() + ",-1,1) ";
if (Class_Login.Operate(sqlinsert) != 1)
{
Class_Login.Operate(sqlDel); //删除books的记录
Response.Write("<script>window.alert('增加失败！')</script>");
return;
}
}
string sqlLid = "select mid from Library.dbo.Manager where Mname='" + Session["Succeed_main"].ToString().Trim() + "'";
string Lid = Class_Login.SelectHead(sqlLid);
string sqlAddlog = "insert into Library.dbo.OperateLog values('" + Lid + "','" + Session["Succeed_main"].ToString().Trim() + "',Datename(year,GetDate())+'-'+Datename(month,GetDate())+'-'+Datename(day,GetDate())+'-'+DATENAME(WEEKDAY,GETDATE())+'-'+DATENAME(HOUR,GETDATE())+':'+DATENAME(MINUTE,GETDATE())+':'+DATENAME(SECOND,GETDATE()),'添加图书,ISBN=" + txtISBN.Text.Trim() + ",书号:" + txtISBN.Text.Trim() + "0001 到" + (maxrid - 1).ToString() + "的图书')";
Class_Login.Operate(sqlAddlog);
Response.Write("<script>window.alert('增加成功！')</script>");
//循环增加结束
}
}
}
}
public int Register(string Username, string Password, string Email, string Phone) //写入到数据库Users
{
string sql = "insert into Users values('" + Username + "',upper(substring(sys.fn_sqlvarbasetostr(HashBytes('SHA1', '" + Password + "')), 3, 32)),'" + Email + "','" + Phone + "',str(year(GETDATE()),4)+'-'+str(month(GETDATE()),2)+'-'+str(day(getdate()),2))";
return(Class_Login.Operate(sql));
}
protected void btnedit_Click(object sender, EventArgs e)
{
if (txtName.Text.Trim() == "" || txtClass.Text.Trim() == "" || txtAuthor.Text.Trim() == "" || txtText.Text.Trim() == "" || txtCount.Text.Trim() == "")
{
Response.Write("<script>window.alert('请填写完整信息！')</script>");
return;
}
else
{
string isbn = Request.QueryString["ISBN"].ToString().Trim();
string sqloldcount = "select Library .dbo.Books.Bcount from Library.dbo.Books where ISBN=" + isbn + "";
int oldcount = Convert.ToInt32(Class_Login.SelectHead(sqloldcount));
string sqlmaxcount = "select Library .dbo.Repertory .Risbn from Library .dbo.Repertory where Risbn ='" + isbn + "' and Rstate =0";//大于状态为0的数量
string sqlupbooks = "update Library.dbo.Books set Bname= '" + txtName.Text.Trim() + "', Bclass = '" + txtClass.Text.Trim() + "',Bauthor= '" + txtAuthor.Text.Trim() + "',Btext= '" + txtText.Text.Trim() + "',Bcount =" + Convert.ToInt32(txtCount.Text.Trim()) + ",Blent+= " + (Convert.ToInt32(txtCount.Text.Trim()) - oldcount) + " where ISBN='" + isbn + "'";
//string sqlAutoRid = "declare @i int declare @j int declare @s char(10) set @i=(select max(rid) from library.dbo.repertory where risbn='" + isbn + "')+1 set @s='" + isbn + "' set @j=0 while @j<" + (Convert.ToInt32(txtCount.Text.Trim()) - oldcount) + " begin insert into library.dbo.repertory values(@i,@s,-1,1) set @j=@j+1 end";
string sqlmaxrid = "select max(Rid) from Library.dbo.repertory where Risbn=" + isbn + "";
string tmaxrid = Class_Login.SelectHead(sqlmaxrid);
int maxrid = Convert.ToInt32(tmaxrid);
DataTable dt = Class_Login.SelectT(sqlmaxcount);
int mincount = dt.Rows.Count;
if (Convert.ToInt32(txtCount.Text.Trim()) < mincount)
{
Response.Write("<script>window.alert('数量不能少于当前读者已借阅本书的数量！')</script>");
return;
}
else
{
//更新books表
int i;
i = Class_Login.Operate(sqlupbooks);
if (i != 0 || i != -1)
{
//自动添加到仓库
//int jd = Class_Login.Operate(sqlAutoRid);
//if (jd == 0 || jd == -1)
//{
// Response.Write("<script>window.alert('仓库增加失败！')</script>");
// return;
//}
string sqlinsert = "";
for (int j = 0; j < Convert.ToInt32(txtCount.Text.Trim()) - oldcount; j++)
{
sqlinsert = "insert into library.dbo.repertory values(" + (++maxrid) + "," + isbn + ",-1,1) ";
if (Class_Login.Operate(sqlinsert) != 1)
{
Response.Write("<script>window.alert('增加失败！')</script>");
return;
}
}
//添加完毕
string sqlLid = "select mid from Library.dbo.Manager where Mname='" + Session["Succeed_main"].ToString().Trim() + "'";
string Lid = Class_Login.SelectHead(sqlLid);
string sqlAddlog = "insert into Library.dbo.OperateLog values('" + Lid + "','" + Session["Succeed_main"].ToString().Trim() + "',Datename(year,GetDate())+'-'+Datename(month,GetDate())+'-'+Datename(day,GetDate())+'-'+DATENAME(WEEKDAY,GETDATE())+'-'+DATENAME(HOUR,GETDATE())+':'+DATENAME(MINUTE,GETDATE())+':'+DATENAME(SECOND,GETDATE()),'增加图书,ISBN=" + isbn + ",书号:" + tmaxrid + " 到" + maxrid.ToString() + "的图书')";
Class_Login.Operate(sqlAddlog);
Response.Write("<script>window.alert('修改成功!')</script>");
}
else
{
Response.Write("<script>window.alert('修改失败!books数据库更新失败 ')</script>");
}
}
}
}