1using System;
  2using System.Collections;
  3using System.ComponentModel;
  4using System.Data;
  5using System.Data.SqlClient;
  6using System.Drawing;
  7using System.Web;
  8using System.Web.SessionState;
  9using System.Web.UI;
 10using System.Web.UI.WebControls;
 11using System.Web.UI.HtmlControls;
 12using System.Configuration;
 13
 14namespace STGROUP
 15...{
 16    /**//// <summary>
 17    /// grade_manage 的摘要说明。
 18    /// </summary>
 19    public class ST_grade_manage : System.Web.UI.Page
 20    ...{
 21        protected System.Web.UI.WebControls.DropDownList st_ddl_way;
 22        protected System.Web.UI.WebControls.TextBox st_tbx_name;
 23        protected System.Web.UI.WebControls.Button st_btn_search;
 24        protected System.Web.UI.WebControls.DataGrid st_dtg_grade; 
 25        protected System.Web.UI.WebControls.Label st_lbl_note;
 26        protected System.Web.UI.WebControls.DropDownList st_ddl_stat;
 27        protected System.Web.UI.WebControls.TextBox st_tbx_area;
 28        protected System.Web.UI.WebControls.TextBox st_tbx_classid;
 29        protected System.Web.UI.WebControls.DropDownList st_ddl_content;
 30        protected System.Web.UI.WebControls.Button st_btn_count;
 31        protected System.Web.UI.WebControls.TextBox st_tbx_year;
 32        protected System.Web.UI.WebControls.Label st_lbl_average;
 33        protected System.Web.UI.WebControls.Label st_lbl_high;
 34        protected System.Web.UI.WebControls.Label st_lbl_a;
 35        protected System.Web.UI.WebControls.Label st_lbl_unpass;
 36        protected System.Web.UI.WebControls.Label st_lbl_all;
 37        protected System.Web.UI.WebControls.TextBox st_tbx_term;
 38        SqlConnection st_conn;
 39        protected System.Web.UI.WebControls.Button st_btn_exit;
 40        protected System.Web.UI.WebControls.LinkButton st_lbtn_addgrade;
 41        string st_sqlstr;
 42        private void Page_Load(object sender, System.EventArgs e)
 43        ...{
 44            // 在此处放置用户代码以初始化页面
 45            string st_connstr= ConfigurationSettings.AppSettings["st_dbconn"];
 46            st_conn=new SqlConnection(st_connstr);    
 47        }
 48
 49        Web Form Designer generated code#region Web Form Designer generated code
 50        override protected void OnInit(EventArgs e)
 51        ...{
 52            //
 53            // CODEGEN：该调用是 ASP.NET Web 窗体设计器所必需的。
 54            //
 55            InitializeComponent();
 56            base.OnInit(e);
 57        }
 58        
 59        /**//// <summary>
 60        /// 设计器支持所需的方法 - 不要使用代码编辑器修改
 61        /// 此方法的内容。
 62        /// </summary>
 63        private void InitializeComponent()
 64        ...{    
 65            this.st_btn_search.Click += new System.EventHandler(this.st_btn_search_Click);
 66            this.st_btn_count.Click += new System.EventHandler(this.st_btn_count_Click);
 67            this.st_lbtn_addgrade.Click += new System.EventHandler(this.st_lbtn_addgrade_Click);
 68            this.st_btn_exit.Click += new System.EventHandler(this.st_btn_exit_Click);
 69            this.Load += new System.EventHandler(this.Page_Load);
 70
 71        }
 72        #endregion
 73
 74
 75        private void st_btn_search_Click(object sender, System.EventArgs e)
 76        ...{
 77            ST_BindGrid();
 78
 79        }
 80        public void DataGrid_Page(object sender,DataGridPageChangedEventArgs e)
 81        ...{
 82            st_dtg_grade.CurrentPageIndex=e.NewPageIndex;
 83            ST_BindGrid();
 84        }
 85        public void ST_BindGrid()
 86        ...{
 87            if(st_ddl_way.SelectedItem.Text=="按学号")
 88            ...{
 89                if(st_tbx_term.Text=="所有")
 90                ...{
 91                    st_sqlstr="select ST_student_course.ST_ID,ST_student_course.ST_Student_id,ST_student.ST_Student_name,ST_student.ST_Student_classid,ST_student_course.ST_Course_id,ST_course.ST_Course_name,ST_teacher.ST_Teacher_name,ST_student_course.ST_Student_grade from ST_course,ST_student,ST_teacher,ST_student_course,ST_teacher_course where ST_student_course.ST_Student_id='"+st_tbx_name.Text+"' and ST_student_course.ST_Course_id=ST_course.ST_Course_id and ST_student_course.ST_Student_id=ST_student.ST_Student_id and ST_student_course.ST_Course_id=ST_teacher_course.ST_Course_id and ST_teacher_course.ST_Teacher_id=ST_teacher.ST_Teacher_id and ST_teacher_course.ST_Class_id=ST_student.ST_Student_classid"; 
 92                }
 93                else
 94                ...{
 95                    st_sqlstr="select ST_student_course.ST_ID,ST_student_course.ST_Student_id,ST_student.ST_Student_name,ST_student.ST_Student_classid,ST_student_course.ST_Course_id,ST_course.ST_Course_name,ST_teacher.ST_Teacher_name,ST_student_course.ST_Student_grade from ST_course,ST_student,ST_teacher,ST_student_course,ST_teacher_course where ST_student_course.ST_Student_id='"+st_tbx_name.Text+"' and ST_student_course.ST_Course_year='"+st_tbx_term.Text+"' and ST_student_course.ST_Course_id=ST_course.ST_Course_id and ST_student_course.ST_Student_id=ST_student.ST_Student_id and ST_student_course.ST_Course_id=ST_teacher_course.ST_Course_id and ST_teacher_course.ST_Teacher_id=ST_teacher.ST_Teacher_id and ST_teacher_course.ST_Class_id=ST_student.ST_Student_classid"; 
 96                }
 97            }
 98            else if(st_ddl_way.SelectedItem.Text=="按课号")
 99            ...{
100                if(st_tbx_term.Text=="所有")
101                ...{
102                    st_sqlstr="select ST_student_course.ST_ID,ST_student_course.ST_Student_id,ST_student.ST_Student_name,ST_student.ST_Student_classid,ST_student_course.ST_Course_id,ST_course.ST_Course_name,ST_teacher.ST_Teacher_name,ST_student_course.ST_Student_grade from ST_course,ST_student,ST_teacher,ST_student_course,ST_teacher_course where ST_student_course.ST_Course_id='"+st_tbx_name.Text+"' and ST_student_course.ST_Course_id=ST_course.ST_Course_id and ST_student_course.ST_Student_id=ST_student.ST_Student_id and ST_student_course.ST_Course_id=ST_teacher_course.ST_Course_id and ST_teacher_course.ST_Teacher_id=ST_teacher.ST_Teacher_id and ST_teacher_course.ST_Class_id=ST_student.ST_Student_classid"; 
103                }
104                else
105                ...{
106                    st_sqlstr="select ST_student_course.ST_ID,ST_student_course.ST_Student_id,ST_student.ST_Student_name,ST_student.ST_Student_classid,ST_student_course.ST_Course_id,ST_course.ST_Course_name,ST_teacher.ST_Teacher_name,ST_student_course.ST_Student_grade from ST_course,ST_student,ST_teacher,ST_student_course,ST_teacher_course where ST_student_course.ST_Course_id='"+st_tbx_name.Text+"' and ST_student_course.ST_Course_year='"+st_tbx_term.Text+"' and ST_student_course.ST_Course_id=ST_course.ST_Course_id and ST_student_course.ST_Student_id=ST_student.ST_Student_id and ST_student_course.ST_Course_id=ST_teacher_course.ST_Course_id and ST_teacher_course.ST_Teacher_id=ST_teacher.ST_Teacher_id and ST_teacher_course.ST_Class_id=ST_student.ST_Student_classid"; 
107                }
108            }
109            else if(st_ddl_way.SelectedItem.Text=="按课程名")
110            ...{
111                if(st_tbx_term.Text=="所有")
112                ...{
113                    st_sqlstr="select ST_student_course.ST_ID,ST_student_course.ST_Student_id,ST_student.ST_Student_name,ST_student.ST_Student_classid,ST_student_course.ST_Course_id,ST_course.ST_Course_name,ST_teacher.ST_Teacher_name,ST_student_course.ST_Student_grade from ST_course,ST_student,ST_teacher,ST_student_course,ST_teacher_course where ST_course.ST_Course_name='"+st_tbx_name.Text+"' and ST_student_course.ST_Course_id=ST_course.ST_Course_id and ST_student_course.ST_Student_id=ST_student.ST_Student_id and ST_student_course.ST_Course_id=ST_teacher_course.ST_Course_id and ST_teacher_course.ST_Teacher_id=ST_teacher.ST_Teacher_id and ST_teacher_course.ST_Class_id=ST_student.ST_Student_classid"; 
114            
115                }
116                else
117                ...{
118                    st_sqlstr="select ST_student_course.ST_ID,ST_student_course.ST_Student_id,ST_student.ST_Student_name,ST_student.ST_Student_classid,ST_student_course.ST_Course_id,ST_course.ST_Course_name,ST_teacher.ST_Teacher_name,ST_student_course.ST_Student_grade from ST_course,ST_student,ST_teacher,ST_student_course,ST_teacher_course where ST_course.ST_Course_name='"+st_tbx_name.Text+"' and ST_student_course.ST_Course_year='"+st_tbx_term.Text+"' and ST_student_course.ST_Course_id=ST_course.ST_Course_id and ST_student_course.ST_Student_id=ST_student.ST_Student_id and ST_student_course.ST_Course_id=ST_teacher_course.ST_Course_id and ST_teacher_course.ST_Teacher_id=ST_teacher.ST_Teacher_id and ST_teacher_course.ST_Class_id=ST_student.ST_Student_classid"; 
119                }
120            }
121            else if(st_ddl_way.SelectedItem.Text=="按班级")
122            ...{
123                if(st_tbx_term.Text=="所有")
124                ...{
125                    st_sqlstr="select ST_student_course.ST_ID,ST_student_course.ST_Student_id,ST_student.ST_Student_name,ST_student.ST_Student_classid,ST_student_course.ST_Course_id,ST_course.ST_Course_name,ST_teacher.ST_Teacher_name,ST_student_course.ST_Student_grade from ST_course,ST_student,ST_teacher,ST_student_course,ST_teacher_course where ST_student.ST_Student_classid='"+st_tbx_name.Text+"'and ST_student_course.ST_Course_id=ST_course.ST_Course_id and ST_student_course.ST_Student_id=ST_student.ST_Student_id and ST_student_course.ST_Course_id=ST_teacher_course.ST_Course_id and ST_teacher_course.ST_Teacher_id=ST_teacher.ST_Teacher_id and ST_teacher_course.ST_Class_id=ST_student.ST_Student_classid"; 
126            
127                }
128                else
129                ...{
130                    st_sqlstr="select ST_student_course.ST_ID,ST_student_course.ST_Student_id,ST_student.ST_Student_name,ST_student.ST_Student_classid,ST_student_course.ST_Course_id,ST_course.ST_Course_name,ST_teacher.ST_Teacher_name,ST_student_course.ST_Student_grade from ST_course,ST_student,ST_teacher,ST_student_course,ST_teacher_course where ST_student.ST_Student_classid='"+st_tbx_name.Text+"' and ST_student_course.ST_Course_year='"+st_tbx_term.Text+"' and ST_student_course.ST_Course_id=ST_course.ST_Course_id and ST_student_course.ST_Student_id=ST_student.ST_Student_id and ST_student_course.ST_Course_id=ST_teacher_course.ST_Course_id and ST_teacher_course.ST_Teacher_id=ST_teacher.ST_Teacher_id and ST_teacher_course.ST_Class_id=ST_student.ST_Student_classid"; 
131                }
132            }
133            else if(st_ddl_way.SelectedItem.Text=="按教师号")
134            ...{
135                if(st_tbx_term.Text=="所有")
136                ...{
137                    st_sqlstr="select ST_student_course.ST_ID,ST_student_course.ST_Student_id,ST_student.ST_Student_name,ST_student.ST_Student_classid,ST_student_course.ST_Course_id,ST_course.ST_Course_name,ST_teacher.ST_Teacher_name,ST_student_course.ST_Student_grade from ST_course,ST_student,ST_teacher,ST_student_course,ST_teacher_course where ST_teacher.ST_Teacher_id='"+st_tbx_name.Text+"' and ST_student_course.ST_Course_id=ST_course.ST_Course_id and ST_student_course.ST_Student_id=ST_student.ST_Student_id and ST_student_course.ST_Course_id=ST_teacher_course.ST_Course_id and ST_teacher_course.ST_Teacher_id=ST_teacher.ST_Teacher_id and ST_teacher_course.ST_Class_id=ST_student.ST_Student_classid"; 
138            
139                }
140                else
141                ...{
142                    st_sqlstr="select ST_student_course.ST_ID,ST_student_course.ST_Student_id,ST_student.ST_Student_name,ST_student.ST_Student_classid,ST_student_course.ST_Course_id,ST_course.ST_Course_name,ST_teacher.ST_Teacher_name,ST_student_course.ST_Student_grade from ST_course,ST_student,ST_teacher,ST_student_course,ST_teacher_course where ST_teacher.ST_Teacher_id='"+st_tbx_name.Text+"' and ST_student_course.ST_Course_year='"+st_tbx_term.Text+"' and ST_student_course.ST_Course_id=ST_course.ST_Course_id and ST_student_course.ST_Student_id=ST_student.ST_Student_id and ST_student_course.ST_Course_id=ST_teacher_course.ST_Course_id and ST_teacher_course.ST_Teacher_id=ST_teacher.ST_Teacher_id and ST_teacher_course.ST_Class_id=ST_student.ST_Student_classid"; 
143                }
144            }
145            
146            SqlCommand st_comm=new SqlCommand(st_sqlstr,st_conn);
147            st_conn.Open();
148            SqlDataReader dr=st_comm.ExecuteReader();
149            if(dr.Read())
150            ...{
151                st_dtg_grade.Visible=true;
152                st_conn.Close();
153                st_conn.Open();
154                SqlDataAdapter st_da=new SqlDataAdapter(st_sqlstr,st_conn);
155                DataSet st_ds=new DataSet();
156                st_da.Fill(st_ds);
157                st_dtg_grade.DataSource=st_ds;
158                st_dtg_grade.DataBind();
159            }
160            else
161            ...{
162                st_dtg_grade.Visible=false;
163                st_lbl_note.Text="无纪录！";
164            }
165            st_conn.Close();
166        }
167        public void DataGrid_cancel(object sender,DataGridCommandEventArgs e)
168        ...{
169            
170            st_dtg_grade.EditItemIndex=-1;
171            ST_BindGrid();
172            
173        }
174        public void DataGrid_edit(object sender,DataGridCommandEventArgs e)
175        ...{
176            
177            st_dtg_grade.EditItemIndex=(int)e.Item.ItemIndex;
178            ST_BindGrid();
179            
180        }
181        public void DataGrid_update(object sender,DataGridCommandEventArgs e)
182        ...{
183            string st_sqlstr="update ST_student_course set ST_Student_grade=@Student_grade where ST_Student_id=@Student_id and ST_Course_id=@Course_id and ST_ID=@id";
184            SqlCommand st_comm=new SqlCommand(st_sqlstr,st_conn);
185            st_comm.Parameters.Add(new SqlParameter("@Student_grade",SqlDbType.Int,4));
186            st_comm.Parameters.Add(new SqlParameter("@Course_id",SqlDbType.VarChar,50));
187            st_comm.Parameters.Add(new SqlParameter("@Student_id",SqlDbType.VarChar,50));
188            st_comm.Parameters.Add(new SqlParameter("@id",SqlDbType.Char,10));
189            string st_colvalue=((TextBox)e.Item.Cells[7].Controls[0]).Text;
190            st_comm.Parameters["@Student_grade"].Value=st_colvalue;
191            st_comm.Parameters["@Student_id"].Value=e.Item.Cells[1].Text;
192            st_comm.Parameters["@Course_id"].Value=e.Item.Cells[4].Text;
193            st_comm.Parameters["@Student_grade"].Value=st_colvalue;
194            st_comm.Parameters["@id"].Value=st_dtg_grade.DataKeys[(int)e.Item.ItemIndex];
195            st_comm.Connection.Open();
196            try
197            ...{
198                st_comm.ExecuteNonQuery();
199                st_lbl_note.Text="编辑成功";
200                st_dtg_grade.EditItemIndex=-1;
201            }
202            catch(SqlException)
203            ...{
204                st_lbl_note.Text="编辑失败";
205                st_lbl_note.Style["color"]="red";
206            }
207            st_comm.Connection.Close();        
208            ST_BindGrid();
209            
210        }
211        
212        public void DataGrid_delete(object sender,DataGridCommandEventArgs e)
213        ...{
214            
215            string st_sqlstr="delete from ST_student_course where ST_ID=@id";
216            SqlCommand st_comm=new SqlCommand(st_sqlstr,st_conn);
217            st_comm.Parameters.Add(new SqlParameter("@id",SqlDbType.Char,10));            
218            st_comm.Parameters["@id"].Value=st_dtg_grade.DataKeys[(int)e.Item.ItemIndex];
219            st_comm.Connection.Open();
220            try
221            ...{
222                st_comm.ExecuteNonQuery();
223                st_lbl_note.Text="删除成功";
224                
225            }
226            catch(SqlException)
227            ...{
228                st_lbl_note.Text="删除失败";
229                st_lbl_note.Style["color"]="red";
230            }
231            st_comm.Connection.Close();            
232            ST_BindGrid();
233        }
234
235        private void st_btn_count_Click(object sender, System.EventArgs e)
236        ...{
237            if(st_ddl_stat.SelectedItem.Value=="系别")
238            ...{
239                    
240               if(st_ddl_content.SelectedItem.Text=="总人数")
241               ...{
242                   st_sqlstr="select AVG(ST_Student_grade),MAX(ST_Student_grade),COUNT(*) from ST_student_course,ST_student,ST_class where ST_Course_id='"+st_tbx_classid.Text+"' and ST_student_course.ST_Course_year='"+st_tbx_year.Text+"'and ST_student_course.ST_Student_id=ST_student.ST_Student_id and ST_student.ST_Student_classid=ST_class.ST_Class_id and ST_class.ST_Class_department='"+st_tbx_area.Text+"' ";
243               }
244               else if(st_ddl_content.SelectedItem.Text=="优秀人数")
245               ...{
246                   st_sqlstr="select AVG(ST_Student_grade),MAX(ST_Student_grade),COUNT(*) from ST_student_course,ST_student,ST_class where ST_Course_id='"+st_tbx_classid.Text+"' and ST_student_course.ST_Course_year='"+st_tbx_year.Text+"'and ST_student_course.ST_Student_id=ST_student.ST_Student_id and ST_student.ST_Student_classid=ST_class.ST_Class_id and ST_class.ST_Class_department='"+st_tbx_area.Text+"'and ST_Student_grade>=85 ";
247               }
248               else if(st_ddl_content.SelectedItem.Text=="不及格人数")
249               ...{
250                   st_sqlstr="select AVG(ST_Student_grade),MAX(ST_Student_grade),COUNT(*) from ST_student_course,ST_student,ST_class where ST_Course_id='"+st_tbx_classid.Text+"' and ST_student_course.ST_Course_year='"+st_tbx_year.Text+"'and ST_student_course.ST_Student_id=ST_student.ST_Student_id and ST_student.ST_Student_classid=ST_class.ST_Class_id and ST_class.ST_Class_department='"+st_tbx_area.Text+"'and ST_Student_grade<60 ";
251               }
252                else
253               ...{
254                 st_sqlstr="select AVG(ST_Student_grade),MAX(ST_Student_grade) from ST_student_course,ST_student,ST_class where ST_Course_id='"+st_tbx_classid.Text+"' and ST_student_course.ST_Course_year='"+st_tbx_year.Text+"'and ST_student_course.ST_Student_id=ST_student.ST_Student_id and ST_student.ST_Student_classid=class.ST_Class_id and ST_class.ST_Class_department='"+st_tbx_area.Text+"' ";
255               }
256