-

我做的第一个学生管理系统

end sub

'============根据课程号,学号,年级,班号分别进行不及格成绩统计==============
sub sta_handle(sta_type)
	
		if sta_type ="COURSE" then
			course_sql = "select 学号,姓名,"
			for i = 0 to CourseCount -1
					if rtrim(courseArray(0,i)) = rtrim(courseno.value) then
						course_sql = course_sql +"'"+rtrim(courseArray(0,i))+"'=str("+courseNoArray(0,i)+",7,1) "
						
						exit for
					end if
			next  	
			course_sql = course_sql + " from score where " + courseNoArray(0,i)+"<60 and "&_
							 "学年='"+trim(yearno.value)+"' and 学期='"+trim(termno.value)+"'"
		else
				 get_course(sta_type)
				 course_sql ="  select 学号,姓名,'不及格门数'="+cstr(courseSelectCount)+"-("	 
				
			    for i = 0 to courseSelectCount -1 
					course_sql = course_sql + " floor("+trim(courseSelectArray(0,i))+"/60.0) + "
				next 
				course_sql = course_sql + "0) from score "
				course_sql = course_sql + " where 学年='"+yearno.value+"' and 学期='"+termno.value+"' and 成绩类型='"+scoreno.value+"' and "	
				select case sta_type
					case "CLASS"
					     course_sql = course_sql +" 学号 in (select 学号 from student where rtrim(班号)='"+rtrim(classno.value)+"')"
			        case "GRADE"  
					     course_SQL = course_SQL +"学号 in (select 学号 from student where 班号 in (select 班号 from class where year(入学年度)='"+rtrim(gradeno.value)+"'))"
			        case "XH"  
						 course_sql = course_sql +" rtrim(学号) >= rtrim("+startno.value+") and rtrim(学号) <= rtrim("+endno.value+")"									 
				end select
		end if 
	
		rds1.sql=course_sql
			msgbox rds1.sql
		rds1.ExecuteOptions=1
		rds1.refresh
				
end sub
'==========各分数段学生统计=============================
sub fsd_sta()
		if len(trim(classno.value))=0 and class_radio.checked then
				msgbox "对不起！请先输入要统计的班号！"
				exit sub
		end if 
		
		if len(trim(gradeno.value))=0 and grade_radio.checked then
				msgbox "对不起！请先输入要统计的年级！"
				exit sub
		end if 
		if len(trim(startno.value))=0 and xh_radio.checked then
				msgbox "对不起！请先输入要统计的学号！"
				exit sub
		end if 
		if len(trim(courseno.value))=0 and kc_radio.checked then
				msgbox "对不起！请先输入要统计的班号！"
				exit sub
		end if 
		
			if len(trim(yearno.value))=0 then
				msgbox "对不起！请先输入要查询的学年！"
				exit sub
			end if
			
		if xh_radio.checked then	
			if len(rtrim(startno.value)) > 0 then
				if len(rtrim(endno.value)) > 0 then
					if rtrim(endno.value) < rtrim(startno.value) then
						msgbox "终止学号必须不小于起始学号！"
						exit sub
					end if
				else
					endno.value = startno.value
				end if
			end if
		end if 
			
			if kc_radio.checked  then
				call fsd_handle("COURSE")
				exit sub
			end if
			
			if xh_radio.checked then
				call fsd_handle("XH")
				exit sub
			end if
				
			if grade_radio.checked  then
				call fsd_handle("GRADE")
				exit sub
			end if
			
			if class_radio.checked  then
				call fsd_handle("CLASS")
				exit sub
			end if


end sub

'============根据课程号,学号,年级,班号分别进行不及格,及格,中,良,优等成绩统计==============
sub fsd_handle(sta_type)
	
		if sta_type ="COURSE" then
			course_sql = "select 学号,姓名,"
			for i = 0 to CourseCount -1
					if rtrim(courseArray(0,i)) = rtrim(courseno.value) then
						course_sql = course_sql +"'"+rtrim(courseArray(0,i))+"'=str("+courseNoArray(0,i)+",7,1) "
						
						exit for
					end if
			next  	
			course_sql = course_sql + " from score where " + courseNoArray(0,i)+"<60 and "&_
							 "学年='"+trim(yearno.value)+"' and 学期='"+trim(termno.value)+"'"
		else
				 get_course(sta_type)
				 course_sql ="  select 学号,姓名,'不及格'="+cstr(courseSelectCount)+"-("	 
				
			    for i = 0 to courseSelectCount -1 
					course_sql = course_sql + " floor("+trim(courseSelectArray(0,i))+"/60.0) + "
				next 
				course_sql = course_sql + "0), '及格'=str(-("
				for i = 0 to courseSelectCount -1 
					course_sql = course_sql + " sign("+trim(courseSelectArray(0,i))+"-69.9) - sign("+trim(courseSelectArray(0,i))+"-59.9) + "
				next
				
				course_sql = course_sql + "0)/2,2,0), '中'=str(-("
				for i = 0 to courseSelectCount -1 
					course_sql = course_sql + " sign("+trim(courseSelectArray(0,i))+"-79.9) - sign("+trim(courseSelectArray(0,i))+"-69.9) + "
				next
				
				course_sql = course_sql + "0)/2,2,0), '良'=str(-("
				for i = 0 to courseSelectCount -1 
					course_sql = course_sql + " sign("+trim(courseSelectArray(0,i))+"-89.9) - sign("+trim(courseSelectArray(0,i))+"-79.9) + "
				next
				
				course_sql = course_sql + "0)/2,2,0), '优'=str(-("
				for i = 0 to courseSelectCount -1 
					course_sql = course_sql + " sign("+trim(courseSelectArray(0,i))+"-100.9) - sign("+trim(courseSelectArray(0,i))+"-89.9) + "
				next
				
				course_sql = course_sql + "0)/2,2,0) from score "
				course_sql = course_sql + " where 学年='"+yearno.value+"' and 学期='"+termno.value+"' and 成绩类型='"+scoreno.value+"' and "	
				select case sta_type
					case "CLASS"
					     course_sql = course_sql +" 学号 in (select 学号 from student where rtrim(班号)='"+rtrim(classno.value)+"')"
			        case "GRADE"  
					     course_SQL = course_SQL +"学号 in (select 学号 from student where 班号 in (select 班号 from class where year(入学年度)='"+rtrim(gradeno.value)+"'))"
			        case "XH"  
						 course_sql = course_sql +" rtrim(学号) >= rtrim("+startno.value+") and rtrim(学号) <= rtrim("+endno.value+")"									 
				end select
		end if 
		
		rds1.sql=course_sql
		rds1.ExecuteOptions=1
		rds1.refresh
				
end sub

'==========各学期总分统计=============================
sub zxf_sta()

end sub


 
'==================取所开的课程====================================
sub get_course(search_type)
	
	courseSelectCount = 0
	
	
	for i = 0 to courseCount - 1
		
			'找出本学期开设了哪些课程,通过计算总分是否为零进行判断
			rds2.SQL = "select sum("+courseNoArray(0,i)+") from score "
			if search_type="CLASS" then
				rds2.SQL = rds2.SQL +" where 学号 in (select 学号 from student where rtrim(班号)='"+rtrim(classno.value)+"') and "