//取消
protected void Cancel_Click(object sender, EventArgs e)
{
string order_id = "", date_year = "", semester = "", supply_person = "";
string book_id = "", campus = "";
order_id = TextBox1.Text;
date_year = DropDownList1.SelectedValue;
semester = DropDownList2.SelectedValue;
supply_person = DropDownList3.SelectedValue;
string sql1 = "select book_id,campus from form_order where date_year='" + date_year + "' and semester='" + semester + "' and order_id='" + order_id + "'";
SqlDataReader read = SQLHelper.ExecuteReader(sql1);
while (read.Read())
{
book_id = read["book_id"].ToString();
campus = read["campus"].ToString();
string sql = "update book_demand set state_id=0 where date_year='" + date_year + "' and semester='" + semester + "' and book_id='" + book_id + "' and campus='" + campus + "'";
SQLHelper.ExecuteNonQuery(sql);
string sql2 = "delete from form_order where order_id='" + order_id + "' and date_year='" + date_year + "' and semester='" + semester + "'";
SQLHelper.ExecuteNonQuery(sql2);
}
JScript.AjaxAlertAndLocationHref(this.Page, "已取消订单", "查看订单.aspx");
}
//保存
protected void Save_Click(object sender, EventArgs e)
{
//得到订单相关信息
string order_id = "", order_date = "", operate_person = "", date_year = "", semester = "", supply_person = "", supplier_phone = "";
string book_id = "", book_name = "", book_editor = "", press_time = "", press_name = "", book_price = "", plan_num = "", auto_num = "", total_num = "", total_price = "", campus = "", done_time = DateTime.Now.ToString("yyyyMMdd");;
order_id = TextBox1.Text;
order_date = TextBox2.Text;
operate_person = TextBox3.Text;
date_year = DropDownList1.SelectedValue;
semester = DropDownList2.SelectedValue;
supply_person = DropDownList3.SelectedValue;
supplier_phone = TextBox8.Text;
string sql1 = "select * from form_order where date_year='" + date_year + "' and semester='" + semester + "' and order_id='" + order_id + "'";
SqlDataReader read = SQLHelper.ExecuteReader(sql1);
while (read.Read())
{
book_id = read["book_id"].ToString();
book_name = read["book_name"].ToString();
press_time = read["press_time"].ToString();
press_name = read["press_name"].ToString();
book_price = read["book_price"].ToString();
book_editor = read["book_editor"].ToString();
plan_num = read["plan_num"].ToString();
auto_num = read["auto_num"].ToString();
total_num = read["total_num"].ToString();
total_price = read["total_price"].ToString();
campus = read["campus"].ToString();
string sql = "insert into finally_order_information (order_id,order_date,operate_person,date_year,semester,supply_person,supplier_phone,book_id,book_name,book_editor,book_price,press_name,press_time,plan_num,auto_num,total_num,total_price,state_id,campus,done_time)";
sql += "values('" + order_id + "','" + order_date + "','" + operate_person + "','" + date_year + "','" + semester + "','" + supply_person + "','" + supplier_phone + "','" + book_id + "','" + book_name + "','" + book_editor + "','" + book_price + "','" + press_name + "','" + press_time + "','" + plan_num + "','" + auto_num + "','" + total_num + "','" + total_price + "','1','" + campus + "','" + done_time + "')";
SQLHelper.ExecuteNonQuery(sql);
string sql2 = "delete from form_order where order_id='" + order_id + "' and date_year='" + date_year + "' and semester='" + semester + "'";
SQLHelper.ExecuteNonQuery(sql2);
}
JScript.AjaxAlertAndLocationHref(this.Page, "订单保存成功", "查看订单.aspx");
}
protected void Return_Button_Click(object sender, EventArgs e)
{
try
{
for (int i = 0; i < GridView1.Rows.Count; i++)
{
CheckBox cb = GridView1.Rows[i].Cells[0].FindControl("CheckBox1") as CheckBox;
if (cb.Checked == true)
{
Label order_id = GridView1.Rows[i].Cells[0].FindControl("Label1") as Label;
Label book_id = GridView1.Rows[i].Cells[0].FindControl("Label7") as Label;
string sql4 = "select * from Storage_management_end where book_id=" + book_id.Text + " and order_id=" + order_id.Text + "";
SqlDataReader read = SQLHelper.ExecuteReader(sql4);
for (; read.Read();)
{
string sql2 = "insert into return_history (order_id,operate_person,operater_phone,date_year ,semester ,supply_person,supplier_phone,book_id ,book_name,press_name,book_price,plan_num,auto_num,total_price,order_discount ,arrived_discount ,sell_discount ,arrived_amount ,unarrived_amount ,arrival_amount ,arrived_date ,Campus ,Storage_location ,out_num,remain_num ,order_date, press_time,state_id,total_num,return_num,return_date)";
sql2 += "values('" + read["order_id"].ToString() + "','" + read["operate_person"].ToString() + "','" + read["operater_phone"].ToString() + "','" + read["date_year"].ToString() + "','" + read["semester"].ToString() + "','" + read["supply_person"].ToString() + "','" + read["supplier_phone"].ToString() + "','" + read["book_id"].ToString() + "','" + read["book_name"].ToString() + "','" + read["press_name"].ToString() + "','" + read["book_price"].ToString() + "','" + read["plan_num"].ToString() + "','" + read["auto_num"].ToString() + "','" + read["total_price"].ToString() + "','" + read["order_discount"].ToString() + "','" + read["arrived_discount"].ToString() + "','" + read["sell_discount"].ToString() + "','" + read["arrived_amount"].ToString() + "','" + read["unarrived_amount"].ToString() + "','" + read["arrival_amount"].ToString() + "','" + read["arrived_date"].ToString() + "','" + read["Campus"].ToString() + "','" + read["Storage_location"].ToString() + "','" + read["out_num"].ToString() + "','" + read["remain_num"].ToString() + "','" + read["order_date"].ToString() + "','" + read["press_time"].ToString() + "','" + read["state_id"].ToString() + "','" + read["total_num"].ToString() + "','" + TextBox3.Text.ToString() + "','" + System.DateTime.Now.ToString("d") + "')";
SQLHelper.ExecuteNonQuery(sql2);
}
string sql = "delete from Storage_management_end where order_id=" + order_id.Text + " and book_id=" + book_id.Text;
SQLHelper.ExecuteNonQuery(sql);
}
}
// Button1_Click(sender,e);
//刷新数据表
//Button1_Click(sender, e);
string sql5 = "select order_id,order_date,date_year,operate_person,supply_person,supplier_phone,book_id,book_name,book_price,remain_num from Storage_management_end where book_id like'%" + TextBox1.Text + "%'and book_name like '%" + TextBox2.Text + "%'";
DataTable dt = SQLHelper.GetDataTable(sql5);
GridView1.DataSource = dt;
GridView1.DataBind();
JScript.AjaxAlertAndLocationHref(this.Page, "退书成功", "退库.aspx");
// WebMessageBox.Show("退货成功！");
}
catch (Exception ex)
{
WebMessageBox.Show(ex.Message);
}
}
//提交
protected void Submit_Click(object sender, EventArgs e)
{
try
{
string t = DateTime.Now.ToString("yyyyMMdd");
for (int i = 0; i <= GridView1.Rows.Count - 1; i++)
{
CheckBox cbox = (CheckBox)GridView1.Rows[i].FindControl("CheckBox1");
if (cbox.Checked)
{
SQLHelper.ExecuteNonQuery("update finally_order_information set "
+ "state_id=2,"
+ "done_time='" + t + "' "
+ "where "
+ "order_id=" + GridView1.DataKeys[i].Value);
}
}
JScript.AjaxAlertAndLocationHref(this.Page, "提交成功", "提交订单.aspx");
}
catch (Exception ex)
{
WebMessageBox.Show(ex.Message);
}
}
//提交
protected void Button3_Click(object sender, EventArgs e)
{
string ati = "";
string atn = "";
string username = HttpContext.Current.Request.Cookies["user"].Values["name"];
string sql0 = "select * from tbl_UserInformation where user_login= '******'";
SqlDataReader read1 = SQLHelper.ExecuteReader(sql0);
for (; read1.Read();)
{
ati = read1["teacher_id"].ToString();
atn = read1["teacher_name"].ToString();
}
string dy = DropDownList1.SelectedValue;
string se = DropDownList2.SelectedValue;
string tc = TextBox1.Text;
string ti = TextBox2.Text;
string tn = TextBox3.Text;
string ci = TextBox4.Text;
string cn = TextBox5.Text;
string cp = DropDownList3.SelectedValue;
//将book_message_mid中的数据写入book_message中
string sql1 = "select * from book_message_mid where "
+ "date_year='" + dy + "' "
+ "and semester='" + se + "' "
+ "and teacher_id='" + ti + "' "
+ "and apply_teacher_id='" + ati + "' "
+ "and course_id= '" + ci + "'";
SqlDataReader read = SQLHelper.ExecuteReader(sql1);
while (read.Read())
{
string bi = read["book_id"].ToString();
string bn = read["book_name"].ToString();
string be = read["book_editor"].ToString();
string pn = read["press_name"].ToString();
string pt = read["press_time"].ToString();
string pd = read["press_date"].ToString();
string bp = read["book_price"].ToString();
//判断此书是否在book_message中已有
string si = "";
string tbi = "";
int flag = 0;//原始状态insert
string sql2 = "select book_id,state_id from book_message where "
+ "date_year='" + dy + "' "
+ "and semester='" + se + "' "
+ "and teacher_id='" + ti + "' "
+ "and apply_teacher_id='" + ati + "' "
+ "and course_id='" + ci + "'";
SqlDataReader read2 = SQLHelper.ExecuteReader(sql2);
for (; read2.Read();)
{
tbi = read2["book_id"].ToString();
si = read2["state_id"].ToString();
if (tbi == bi)
{
if (si == "1")
{
flag = 1;//update
break;
}
else
{
flag = 2;//nothing
break;
}
}
}
if (flag == 1)
{
string sql = "update book_message set "
+ "book_name='" + bn + "',"
+ "book_price='" + bp + "',"
+ "book_editor='" + be + "',"
+ "press_name='" + pn + "',"
+ "press_date='" + pd + "',"
+ "press_time='" + pt + "',"
+ "state_id=2,"
+ "university_advise='' "
+ "where "
+ "date_year='" + dy + "' "
+ "and semester='" + se + "' "
+ "and teacher_id='" + ti + "' "
+ "and apply_teacher_id='" + ati + "' "
+ "and course_id='" + ci + "' "
+ "and book_id='" + bi + "'";
SQLHelper.ExecuteNonQuery(sql);
}
else if (flag == 0)
{
string sql = "insert into book_message "
+ "(date_year,semester,"
+ "teacher_college,teacher_id,teacher_name,"
+ "course_id,course_name,"
+ "book_id,book_name,"
+ "book_price,book_editor,"
+ "press_name,press_time,press_date,"
+ "university_advise,state_id,Campus,"
+ "apply_teacher_id,apply_teacher_name)";
sql += "values('" + dy + "','" + se + "',"
+ "'" + tc + "','" + ti + "','" + tn + "',"
+ "'" + ci + "','" + cn + "',"
+ "'" + bi + "','" + bn + "','" + bp + "','" + be + "',"
+ "'" + pn + "','" + pt + "','" + pd + "',"
+ "'',2,'" + cp + "','" + ati + "','" + atn + "')";
SQLHelper.ExecuteNonQuery(sql);
}
}
//删除book_message_mid中该课程的记录
string sql3 = "delete from book_message_mid where "
+ "date_year='" + dy + "' "
+ "and semester='" + se + "' "
+ "and teacher_id='" + ti + "' "
+ "and apply_teacher_id='" + ati + "' "
+ "and course_id='" + ci + "'";
SQLHelper.ExecuteNonQuery(sql3);
JScript.AjaxAlertAndLocationHref(this.Page, "提交成功", "个人查看申领.aspx?ci=" + ci + "&ti=" + ti + "&dy=" + dy + "&se=" + se);
}
//保存
protected void Button5_Click(object sender, EventArgs e)
{
int flag = 0;
string mbi = "";
string bi = "";
string bn = "";
string bp = "";
string be = "";
string ba = "";
string bc = "";
string pn = "";
string pt = "";
string pd = "";
string ad = "";
string adsi = "";
string dy = DropDownList1.SelectedValue;
string se = DropDownList2.SelectedValue;
//读出Rbook_message_mid中的信息
string sql1 = "select * from Rbook_message_mid where course_id='" + DropCourseId.SelectedValue + "' and date_year='" + dy + "' and semester='" + se + "'";
SqlDataReader read1 = SQLHelper.ExecuteReader(sql1);
while (read1.Read())
{
mbi = read1["book_id"].ToString();
bn = read1["book_name"].ToString();
bp = read1["book_price"].ToString();
be = read1["book_editor"].ToString();
ba = read1["book_award"].ToString();
bc = read1["book_catagory"].ToString();
pn = read1["press_name"].ToString();
pt = read1["press_time"].ToString();
pd = read1["press_date"].ToString();
ad = read1["addition"].ToString();
adsi = read1["add_state_id"].ToString();
//判断此书是否在Rbook_message中已有
string si = "";
string sql2 = "select book_id,state_id from Rbook_message where course_id='" + DropCourseId.SelectedValue + "' and date_year='" + dy + "' and semester='" + se + "'";
SqlDataReader read2 = SQLHelper.ExecuteReader(sql2);
for (; read2.Read();)
{
bi = read2["book_id"].ToString();
si = read2["state_id"].ToString();
if (mbi == bi)
{
if (si == "1")
{
flag = 1;//update
}
else
{
flag = 2;//nothing
}
}
}
//update
if (flag == 1)
{
string sql = "update Rbook_message set book_name='" + bn + "',book_price='" + bp + "',book_catagory='" + bc + "',book_award='" + ba + "',book_editor='" + be + "',press_name='" + pn + "',press_date='" + pd + "',press_time='" + pt + "',state_id=1,college_advise='',university_advise='',addition='" + ad + "',add_state_id=" + adsi + " where course_id='" + DropCourseId.SelectedValue + "'and book_id='" + mbi + "' and date_year='" + dy + "' and semester='" + se + "'";
SQLHelper.ExecuteNonQuery(sql);
}
//insert
else if (flag == 0)
{
string tc = TextBox1.Text;
string tm = TextBox2.Text;
string ti = TextBox3.Text;
string tn = TextBox4.Text;
string ph = TextBox5.Text;
string ci = DropCourseId.SelectedValue;
string cn = DropCourseName.SelectedValue;
string sql = "insert into Rbook_message(teacher_college,teacher_major,teacher_id,teacher_name,phone_number,course_id,course_name,book_id,book_name,book_price,book_catagory,book_award,book_editor,press_name,press_date,press_time,state_id,addition,add_state_id,date_year,semester)";
sql += "values('" + tc + "','" + tm + "','" + ti + "','" + tn + "','" + ph + "','" + ci + "','" + cn + "','" + mbi + "','" + bn + "','" + bp + "','" + bc + "','" + ba + "','" + be + "','" + pn + "','" + pd + "','" + pt + "',1,'" + ad + "','" + adsi + "','" + dy + "','" + se + "')";
SQLHelper.ExecuteNonQuery(sql);
}
flag = 0;
}
//最终都删去这门课程对应的信息
SQLHelper.ExecuteNonQuery("delete from Rbook_message_mid where course_id=" + DropCourseId.SelectedValue);
JScript.AjaxAlertAndLocationHref(this.Page, "保存成功", "查看登记.aspx?ci=" + DropCourseId.SelectedValue + "&cn=" + DropCourseName.SelectedValue);
}
//领取
protected void GetBook_Click(object sender, EventArgs e)
{
//判断教材数量合法与否
for (int i = 0; i <= GridView1.Rows.Count - 1; i++)
{
CheckBox cbox = (CheckBox)GridView1.Rows[i].FindControl("CheckBox1");
if (cbox.Checked)
{
//领取前再查一遍库存剩余量
string sql3 = "select remain_num from Storage_management_end where "
+ "id='" + GridView1.DataKeys[i].Value + "' " //统计这本书的需求册数(用BookID锁定)
+ "and state_id= 1"; //代表还可以领
int total_num = 0;
SqlDataReader reader3 = SQLHelper.ExecuteReader(sql3);
while (reader3.Read())
{
total_num = Convert.ToInt32(reader3[0].ToString());//计算总量
break;
}
int StoreNum = total_num;//当前最新剩余量
TextBox tbgn = (TextBox)GridView1.Rows[i].FindControl("TbGetNum");
int GetNum = Convert.ToInt32(tbgn.Text);
if (StoreNum < GetNum || GetNum <= 0)//库存不能大于领取，领取不能为负
{
WebMessageBox.Show("领取教材数量不合法！");
}
}
}
for (int i = 0; i <= GridView1.Rows.Count - 1; i++)
{
CheckBox cbox = (CheckBox)GridView1.Rows[i].FindControl("CheckBox1");
if (cbox.Checked)
{
string id = GridView1.DataKeys[i].Value.ToString();//以id为准
int gn = Convert.ToInt32((GridView1.Rows[i].FindControl("tbGetNum") as TextBox).Text);
//读出写进历史中的关于教材的基本信息
string StuID = "", StuName = "";
string BookID = "", BookName = "", Author = "", Publish = "";
string UnitPrice = "", Version = "", InfoDate = "", campusName = "";
string sell_price = "", sell_discount = "", Storage_location = "", get_num = "";
string sql1 = "select * "
+ "from Storage_management_end "
+ "where id='" + id + "' ";
SqlDataReader read1 = SQLHelper.ExecuteReader(sql1);
for (; read1.Read();)
{
BookID = read1["book_id"].ToString();
BookName = read1["book_name"].ToString();
Author = read1["book_editor"].ToString();
Publish = read1["press_name"].ToString();
UnitPrice = read1["book_price"].ToString();
Version = read1["press_time"].ToString();
sell_discount = read1["sell_discount"].ToString();
sell_price = (Convert.ToDouble(UnitPrice) * Convert.ToDouble(sell_discount) / 100).ToString();
Storage_location = read1["Storage_location"].ToString();
break;
}
get_num = gn.ToString();
StuID = TbStuId.Text;
StuName = TbStuName.Text;
if (StuID == "" || StuName == "")
{
WebMessageBox.Show("请输入学生信息！");
}
//对Storage_management_end处理
string sql = "select * " //1.找到这本书库存剩余量最小的库存，默认从库存量最小的地方取书
+ "from Storage_management_end "
+ "where "
+ "id='" + id + "'";
SqlDataReader read = SQLHelper.ExecuteReader(sql);
while (read.Read())
{
string remain_num = read["remain_num"].ToString();
string out_num = read["out_num"].ToString();
//读取记录历史的价格方面的信息
if (Convert.ToInt32(remain_num) == gn) //2.判断库存是否足数
{
string str_out = Convert.ToString(Convert.ToInt32(out_num) + Convert.ToInt32(remain_num)); //3.更新此最小库存
SQLHelper.ExecuteNonQuery("update Storage_management_end set remain_num=0,out_num='" + str_out + "',state_id=2 where id='" + id + "'");
}
else
{
string str_out = Convert.ToString(Convert.ToInt32(out_num) + Convert.ToInt32(gn)); //3.更新此最小库存
string str_rmn = Convert.ToString(Convert.ToInt32(remain_num) - Convert.ToInt32(gn)); //4.更新剩余量
SQLHelper.ExecuteNonQuery("update Storage_management_end set remain_num='" + str_rmn + "',out_num='" + str_out + "' where id='" + id + "'");
}
//记录历史，写入Abook_message_class_history
InfoDate = DateTime.Now.ToString("yyyyMMdd"); // 2008-9-4时间
string sql3 = "insert into Abook_message_stu_history "
+ "values('" + StuID + "','" + StuName + "','" + BookID + "','" + BookName + "',"
+ "'" + Author + "','" + Publish + "','" + UnitPrice + "','" + Version + "','" + InfoDate + "','" + campusName + "','" + Storage_location + "',"
+ "'" + sell_discount + "','" + sell_price + "','" + get_num + "')";
SQLHelper.ExecuteNonQuery(sql3);
}
}
}
JScript.AjaxAlertAndLocationHref(this.Page, "出售成功", "学生领书.aspx");
}
protected void OK_Click(object sender, EventArgs e)
{
try
{
//判断输入合法性
string sql1 = "select count(*) from Abook_message_stu where BookID='" + BookID + "'and ClassName='" + ClassName + "'and get_id=1";//统计符合这条信息的条数
string need_num = "";
SqlDataReader reader1 = SQLHelper.ExecuteReader(sql1);
while (reader1.Read())
{
need_num = reader1[0].ToString();//该班级共领了多少书
}
string cancel_num = ReturnNum.Text;
int gcancel_num = 0;
for (int i = 0; i < GridView2.Rows.Count; i++)
{
CheckBox cbox = (CheckBox)GridView2.Rows[i].FindControl("CheckBox1");
if (cbox.Checked)
{
TextBox gcn = GridView2.Rows[i].FindControl("TextBox9") as TextBox; //退订册数
gcancel_num += Convert.ToInt32(gcn.Text); //要退订的册数
}
}
if (Convert.ToInt32(need_num) < Convert.ToInt32(cancel_num) || gcancel_num != Convert.ToInt32(cancel_num))
{
gcancel_num = 0;
WebMessageBox.Show("退订册数不合法！");
}
//得到价格,用于历史记录
string sql5 = "select book_price,sell_discount from Storage_management_end where book_id='" + BookID + "'";
SqlDataReader read5 = SQLHelper.ExecuteReader(sql5);
while (read5.Read())
{
Unit_price = read5["book_price"].ToString();
Sell_discount = read5["sell_discount"].ToString();
Sell_price = (Convert.ToDouble(Unit_price) * Convert.ToDouble(Sell_discount) * 0.01).ToString();
break;
}
//退订操作
//改变Storage_management_end里的状态(out_num和remain_num)
for (int i = 0; i < GridView2.Rows.Count; i++)
{
CheckBox cbox = (CheckBox)GridView2.Rows[i].FindControl("CheckBox1");
if (cbox.Checked)
{
Label id = GridView2.Rows[i].FindControl("Label2") as Label;
TextBox cn = GridView2.Rows[i].FindControl("TextBox9") as TextBox;//退订册数
string sql2 = "select remain_num,out_num from Storage_management_end where id='" + id.Text + "'";
SqlDataReader read2 = SQLHelper.ExecuteReader(sql2);
while (read2.Read())
{
string str_brn = read2["remain_num"].ToString();
int in_rn = Convert.ToInt32(str_brn);
in_rn += Convert.ToInt32(cn.Text);
string str_frn = Convert.ToString(in_rn);//最终剩余量
string str_bon = read2["out_num"].ToString();
int in_on = Convert.ToInt32(str_bon);
in_on -= Convert.ToInt32(cn.Text);
string str_fon = Convert.ToString(in_on);//最终已发量
SQLHelper.ExecuteNonQuery("update Storage_management_end set remain_num='" + str_frn + "',out_num='" + str_fon + "' where id=" + id.Text);
}
}
}
//在Abook_message_stu中更改相应个数的状态
//Label bi = GridView2.Rows[0].FindControl("Label9") as Label;
//BookID = bi.Text;
string sql3 = "select stuID from Abook_message_stu where BookID='" + BookID + "' and ClassName='" + ClassName + "' and get_id=1";
SqlDataReader read3 = SQLHelper.ExecuteReader(sql3);
for (int k = 0; read3.Read() && k < Convert.ToInt32(ReturnNum.Text); k++)
{
string stuID = read3[0].ToString();
SQLHelper.ExecuteNonQuery("update Abook_message_stu set get_id=0 where stuID=" + stuID + " and BookID='" + BookID + "' and get_id=1");
}
//记录历史，写入Abook_message_class_history
string time = DateTime.Now.ToString("yyyy-MM-dd");// 2008-9-4时间
string number = "-" + ReturnNum.Text;
string TotalPrice = (Convert.ToDouble(number) * Convert.ToDouble(Unit_price) * Convert.ToDouble(Sell_discount) * 0.01).ToString();
string sql4 = "insert into Abook_message_class_history(ClassName,BookID,BookName,number,Author,Publish,Version,campusName,InfoDate,Unit_price,sell_discount,sell_price,total_price)";
sql4 += "values('" + ClassName + "','" + BookID + "','" + BookName + "','" + number + "','" + Author + "','" + Publish + "','" + Version + "','" + campusName + "','" + time + "','" + Unit_price + "','" + Sell_discount + "','" + Sell_price + "','" + TotalPrice + "')";
SQLHelper.ExecuteNonQuery(sql4);
JScript.AjaxAlertAndLocationHref(this.Page, "退书成功", "班级退书.aspx?ClassName=" + TbClassName.Text);
}
catch (Exception ex)
{
WebMessageBox.Show(ex.Message);
}
}
protected void btnSave_Click(object sender, EventArgs e)
{
try
{
StringBuilder module = new StringBuilder();
int isInsertitem = 0;
for (int i = 0; i < this.GvData.Rows.Count; i++)
{
int moduleid = Convert.ToInt32(this.GvData.Rows[i].Cells[0].Text);
DataTable dt = SQLHelper.GetDataTable("select id from UserPurview where moduleid = " + moduleid + " and roleid = " + hidId.Value + " ");
if (dt.Rows.Count <= 0) //不存在此角色的信息，先将roleid和moduleid写入
{
SQLHelper.ExecuteNonQuery("insert into UserPurview (roleid,moduleid)values(" + hidId.Value + "," + moduleid + ")");
}
//IsSelected
if ((this.GvData.Rows[i].Cells[2].FindControl("cbMenu") as CheckBox).Checked)
{
SQLHelper.ExecuteNonQuery("update UserPurview set IsSelected=1 where moduleid = " + moduleid + " and roleid = " + hidId.Value);
isInsertitem++;
}
else
{
SQLHelper.ExecuteNonQuery("update UserPurview set IsSelected=0 where moduleid = " + moduleid + " and roleid = " + hidId.Value);
}
if (isInsertitem > 0)
{
module.Append(moduleid + ",");
}
isInsertitem = 0;
}
//循环结束
//无任何权限,将purview表中的数据删除
if (module.Length == 0)
{
SQLHelper.ExecuteNonQuery("delete from purview where roleid = " + hidId.Value);
Response.Redirect("RoleManagement.aspx");
}
//增加父结点menuid
string purview = module.Remove(module.Length - 1, 1).ToString(); //删除最后的逗号
DataTable GetPID = SQLHelper.GetDataTable("select parentid from menu where menuid in (" + purview + ") and parentid != 0 group by parentid");
//三级菜单判断
int menuid1 = 0;
int menuid2 = 0;
for (int i = 0; i < GetPID.Rows.Count; i++)
{
if (GetPID.Rows[i]["parentid"].ToString() == "1")
{
menuid1 = 1; // 1 已存在
}
if (GetPID.Rows[i]["parentid"].ToString() == "2")
{
menuid2 = 1; // 1 已存在
}
}
for (int i = 0; i < GetPID.Rows.Count; i++)
{
purview = purview.Insert(0, GetPID.Rows[i]["parentid"].ToString() + ",");
}
if (menuid1 == 0 && menuid2 == 1)
{
purview = purview.Insert(0, "1,");
}
//权限更新
if ((int)SQLHelper.ExecuteScalar("select count(*) from purview where roleid = " + hidId.Value) <= 0)
{
SQLHelper.ExecuteNonQuery("insert into purview(roleid) values( '" + hidId.Value + "')");
}
SQLHelper.ExecuteNonQuery("update purview set permission = '" + purview + "' where roleid = " + hidId.Value);
//获取角色名
string rolename = SQLHelper.GetDataTable("select role_na from tbl_role where id = '" + hidId.Value + "'").Rows[0][0].ToString();
//记录日志
Log.writeLog(Request.Cookies["user"].Values["id"], Request.Cookies["user"].Values["name"], "Change Authority", "Change Authority of Role:" + rolename + " by " + Request.Cookies["user"].Values["name"]);
//给用户提示,并返回角色管理页面
JScript.AjaxAlertAndLocationHref(this.Page, "Authority Successfully", "RoleManagement.aspx");
}
catch (System.Exception ex)
{
JScript.AjaxAlertAndLocationHref(this.Page, "Sorry,there is an error! Reasons:" + ex.Message.ToString(), "RoleManagement.aspx");
}
}
//提交
protected void Button8_Click(object sender, EventArgs e)
{
string dy = DropDownList1.SelectedValue;
string se = DropDownList2.SelectedValue;
string tc = TextBox1.Text;
string ati = TextBox2.Text;
string atn = TextBox3.Text;
string ci = DropCourseId.SelectedValue;
string cn = DropCourseName.SelectedValue;
string cp = DropDownList3.SelectedValue;
string ti = TBTeacherId.Text;
string tn = TBTeacherName.Text;
//将book_message_mid中的数据写入book_message中
string sql1 = "select * from book_message_mid where "
+ "date_year='" + dy + "' and "
+ "semester='" + se + "' "
+ "and teacher_id='" + ti + "' "
+ "and apply_teacher_id='" + ati + "' "
+ "and course_id= '" + ci + "'";
SqlDataReader read = SQLHelper.ExecuteReader(sql1);
while (read.Read())
{
string bi = read["book_id"].ToString();
string bn = read["book_name"].ToString();
string be = read["book_editor"].ToString();
string pn = read["press_name"].ToString();
string pt = read["press_time"].ToString();
string pd = read["press_date"].ToString();
string bp = read["book_price"].ToString();
string ad = read["addition"].ToString();
string asi = read["add_state_id"].ToString();
//判断此书是否在book_message中已有
string si = "";
string tbi = "";
int flag = 0;//原始状态insert
string sql2 = "select book_id,state_id from book_message where "
+ "date_year='" + dy + "' "
+ "and semester='" + se + "' "
+ "and teacher_id='" + ti + "' "
+ "and apply_teacher_id='" + ati + "' "
+ "and course_id='" + ci + "'";
SqlDataReader read2 = SQLHelper.ExecuteReader(sql2);
for (; read2.Read();)
{
tbi = read2["book_id"].ToString();
si = read2["state_id"].ToString();
if (tbi == bi)
{
if (si == "1")
{
flag = 1;//update
break;
}
else
{
flag = 2;//nothing
break;
}
}
}
if (flag == 1)
{
string sql = "update book_message set "
+ "book_name='" + bn + "',"
+ "book_price='" + bp + "',"
+ "book_editor='" + be + "',"
+ "press_name='" + pn + "',"
+ "press_date='" + pd + "',"
+ "press_time='" + pt + "',"
+ "state_id=2,"
+ "university_advise='',"
+ "addition='" + ad + "',"
+ "add_state_id='" + asi + "' "
+ "where "
+ "date_year='" + dy + "' "
+ "and semester='" + se + "' "
+ "and teacher_id='" + ti + "' "
+ "and apply_teacher_id='" + ati + "' "
+ "and course_id='" + ci + "' "
+ "and Campus='" + cp + "'";
SQLHelper.ExecuteNonQuery(sql);
}
else if (flag == 0)
{
string sql = "insert into book_message (date_year,semester,teacher_college,teacher_id,teacher_name,course_id,course_name,book_id,book_name,book_price,book_editor,press_name,press_time,press_date,university_advise,state_id,addition,add_state_id,Campus,apply_teacher_id,apply_teacher_name)";
sql += "values('" + dy + "','" + se + "','" + tc + "','" + ti + "','" + tn + "','" + ci + "','" + cn + "','" + bi + "','" + bn + "','" + bp + "','" + be + "','" + pn + "','" + pt + "','" + pd + "','',2,'" + ad + "','0','" + cp + "','" + ati + "','" + atn + "')";
SQLHelper.ExecuteNonQuery(sql);
}
}
JScript.AjaxAlertAndLocationHref(this.Page, "提交成功", "个人查看申领.aspx?ci=" + ci + "&ti=" + ti + "&dy=" + dy + "&se=" + se);
}
//领取Button
protected void GetBook_Click(object sender, EventArgs e)
{
//判断教材数量合法与否
for (int i = 0; i <= GridView1.Rows.Count - 1; i++)
{
CheckBox cbox = (CheckBox)GridView1.Rows[i].FindControl("CheckBox1");
if (cbox.Checked)
{
//领取前再查一遍库存剩余量
string sql3 = "select remain_num from Storage_management_end where "
+ "book_id='" + GridView1.Rows[i].Cells[3].Text.ToString() + "' " //统计这本书的需求册数(用BookID锁定)
+ "and state_id= 1"; //代表还可以领
int total_num = 0;
SqlDataReader reader3 = SQLHelper.ExecuteReader(sql3);
while (reader3.Read())
{
total_num += Convert.ToInt32(reader3[0].ToString());//计算总量
}
int StoreNum = total_num;//当前最新剩余量
TextBox tbgn = (TextBox)GridView1.Rows[i].FindControl("tbGetNum");
int GetNum = Convert.ToInt32(tbgn.Text);
if (StoreNum < GetNum || GetNum <= 0)//库存不能大于领取，但是领取可以大于需求册数
{
WebMessageBox.Show("领取教材数量不合法！");
}
}
}
for (int i = 0; i <= GridView1.Rows.Count - 1; i++)
{
CheckBox cbox = (CheckBox)GridView1.Rows[i].FindControl("CheckBox1");
if (cbox.Checked)
{
string bi = GridView1.DataKeys[i].Value.ToString();//以book_id为准
int gn = Convert.ToInt32((GridView1.Rows[i].FindControl("tbGetNum") as TextBox).Text);
int agn = gn;
//读出写进历史中的关于教材的基本信息
string BookName = "", Author = "", Publish = "", Version = "", campusName = "", Sell_discount = "", Unit_price = "", Sell_price = "", Storage_location = "";
string ClassName = Class.Text;
string BookID = bi;
string sql1 = "select BookName,Author,Publish,Version,campusName "
+ "from Abook_message_stu "
+ "where BookID='" + BookID + "' "
+ "and ClassName=" + ClassName;
SqlDataReader read1 = SQLHelper.ExecuteReader(sql1);
for (; read1.Read();)
{
BookName = read1["BookName"].ToString();
Author = read1["Author"].ToString();
Publish = read1["Publish"].ToString();
Version = read1["Version"].ToString();
campusName = read1["campusName"].ToString();
break;
}
//对Storage_management_end处理
while (gn != 0)
{
string sql_min = "select * "//1.找到这本书库存剩余量最小的库存，默认从库存量最小的地方取书
+ "from Storage_management_end "
+ "where "
+ "remain_num = (select min(remain_num)from Storage_management_end where remain_num <> 0 and book_id='" + BookID + "')"
+ "and book_id='" + BookID + "'";
SqlDataReader read_min = SQLHelper.ExecuteReader(sql_min);
while (read_min.Read() && gn > 0)
{
int beforegn = gn;
string id = read_min["id"].ToString();
string remain_num = read_min["remain_num"].ToString();
string out_num = read_min["out_num"].ToString();
//读取记录历史的价格方面的信息
Sell_discount = read_min["sell_discount"].ToString(); //出售折扣
Unit_price = read_min["book_price"].ToString(); //单价
Sell_price = (Convert.ToDouble(Sell_discount) * Convert.ToDouble(Unit_price) * 0.01).ToString(); //实际售价
Storage_location = read_min["Storage_location"].ToString();
if (Convert.ToInt32(remain_num) <= gn) //2.判断库存是否足数
{
string str_out = Convert.ToString(Convert.ToInt32(out_num) + Convert.ToInt32(remain_num)); //3.更新此最小库存
SQLHelper.ExecuteNonQuery("update Storage_management_end set remain_num=0,out_num='" + str_out + "',state_id=2 where id='" + id + "'");
gn -= Convert.ToInt32(remain_num); //4.更新需求量
}
else
{
string str_out = Convert.ToString(Convert.ToInt32(out_num) + Convert.ToInt32(gn)); //3.更新此最小库存
string str_rmn = Convert.ToString(Convert.ToInt32(remain_num) - Convert.ToInt32(gn)); //4.更新剩余量
SQLHelper.ExecuteNonQuery("update Storage_management_end set remain_num='" + str_rmn + "',out_num='" + str_out + "' where id='" + id + "'");
gn = 0; //5.更新需求量
}
//记录历史，写入Abook_message_class_history
string time = DateTime.Now.ToString("yyyyMMdd");// 2008-9-4时间
string TotalNum = (Convert.ToDouble(beforegn - gn) * 0.01 * Convert.ToDouble(Unit_price) * Convert.ToDouble(Sell_discount)).ToString();
string sql3 = "insert into Abook_message_class_history"
+ "(ClassName,BookID,BookName,number,Author,Publish,Version,campusName,Storage_location,InfoDate,Unit_price,sell_discount,sell_price,total_price)"
+ "values('" + ClassName + "','" + BookID + "','" + BookName + "','" + Convert.ToString(beforegn - gn) + "',"
+ "'" + Author + "','" + Publish + "','" + Version + "','" + campusName + "','" + Storage_location + "',"
+ "'" + time + "','" + Unit_price + "','" + Sell_discount + "','" + Sell_price + "','" + TotalNum + "')";
SQLHelper.ExecuteNonQuery(sql3);
}
}
//对Abook_message_stu处理
string sql2 = "select stuID from Abook_message_stu where BookID='" + bi + "' and ClassName='" + Class.Text + "'";
SqlDataReader read2 = SQLHelper.ExecuteReader(sql2);
for (int k = 0; read2.Read() && k < agn; k++)
{
string stuID = read2[0].ToString();
SQLHelper.ExecuteNonQuery("update Abook_message_stu set get_id=1 where stuID='" + stuID + "' and BookID='" + bi + "'");
}
}
}
JScript.AjaxAlertAndLocationHref(this.Page, "领取成功", "班级历史.aspx?ClassName=" + Class.Text);
}
//领取
protected void Button2_Click(object sender, EventArgs e)
{
for (int i = 0; i <= GridView1.Rows.Count - 1; i++)
{
CheckBox cbox = (CheckBox)GridView1.Rows[i].FindControl("CheckBox3");
if (cbox.Checked)
{
//领取前再查一遍库存剩余量
string sql3 = "select remain_num from Storage_management_end where "
+ "book_id='" + GridView1.Rows[i].Cells[5].Text.ToString() + "' " //统计这本书的需求册数(用BookID锁定)
+ "and state_id= 1"; //代表还可以领
int total_num = 0;
SqlDataReader reader3 = SQLHelper.ExecuteReader(sql3);
while (reader3.Read())
{
total_num += Convert.ToInt32(reader3[0].ToString());//计算总量
}
int StoreNum = total_num; //当前最新剩余量
if (total_num <= 0) //库存不能大于领取，但是领取可以大于需求册数
{
WebMessageBox.Show("" + GridView1.Rows[i].Cells[5].Text.ToString() + "《" + GridView1.Rows[i].Cells[6].Text.ToString() + "》库存不足！");
}
}
}
for (int i = 0; i <= GridView1.Rows.Count - 1; i++)
{
CheckBox cbox2 = (CheckBox)GridView1.Rows[i].FindControl("CheckBox3");
if (cbox2.Checked)
{
//库存的改变
string bi = GridView1.Rows[i].Cells[5].Text.ToString();
string sql2 = "select remain_num,out_num,id from Storage_management_end where "
+ "remain_num = (select min(remain_num)from Storage_management_end where remain_num <> 0 and book_id='" + bi + "')"
+ "and book_id='" + bi + "'";
SqlDataReader read1 = SQLHelper.ExecuteReader(sql2);
string r = "";
string o = "";
string id = "";
while (read1.Read())
{
r = read1["remain_num"].ToString();
o = read1["out_num"].ToString();
id = read1["id"].ToString();
}
if (Convert.ToDouble(r) > 0)
{
string R = (Convert.ToDouble(r) - 1).ToString(); //R为剩余教材数量
string O = (Convert.ToDouble(o) + 1).ToString(); //O为出库教材数量
SQLHelper.ExecuteNonQuery("update Storage_management_end set remain_num='" + R + "',out_num='" + O + "' where id=" + id);
if (R == "0")
{
SQLHelper.ExecuteNonQuery("update Storage_management_end set state_id=2 where id=" + id);
}
}
else
{
WebMessageBox.Show("名称为" + bi + "的书库存为零！");
}
//记录历史到Abook_message_history
SQLHelper.ExecuteNonQuery("update Abook_message set get_id=1 where id=" + GridView1.DataKeys[i].Value);
string num = "select * from Abook_message where teacher_id= '" + GridView1.Rows[i].Cells[2].Text.ToString() + "' and book_id='" + bi + "'";
SqlDataReader read = SQLHelper.ExecuteReader(num);
while (read.Read())
{
string year = read["year"].ToString();
string semester = read["semester"].ToString();
string course_id = read["course_id"].ToString();
string course_name = read["course_name"].ToString();
string teacher_id = read["teacher_id"].ToString();
string teacher_name = read["teacher_name"].ToString();
string teacher_college = read["teacher_college"].ToString();
string teacher_major = read["teacher_major"].ToString();
string book_id = read["book_id"].ToString();
string book_name = read["book_name"].ToString();
string book_catagory = read["book_catagory"].ToString();
string book_editor = read["book_editor"].ToString();
string book_price = read["book_price"].ToString();
string press_name = read["press_name"].ToString();
string press_time = read["press_time"].ToString();
string press_date = read["press_date"].ToString();
string ct = DateTime.Now.ToString("yyyyMMdd");// 2008-9-4时间
string sql1 = "insert into Abook_message_history"
+ "(year,semester,course_id,course_name,"
+ "teacher_id,teacher_name,teacher_college,teacher_major,"
+ "book_id,book_name,book_catagory,book_editor,book_price,"
+ "press_name,press_time,press_date,get_time)"
+ "values('" + year + "','" + semester + "','" + course_id + "','" + course_name + "',"
+ "'" + teacher_id + "','" + teacher_name + "','" + teacher_college + "','" + teacher_major + "',"
+ "'" + book_id + "','" + book_name + "','" + book_catagory + "','" + book_editor + "','" + book_price + "',"
+ "'" + press_name + "','" + press_time + "','" + press_date + "','" + ct + "')";
SQLHelper.ExecuteNonQuery(sql1);
}
}
JScript.AjaxAlertAndLocationHref(this.Page, "领取成功", "教师领书.aspx");
}
}