course_sql = "select 學號,姓名,名次,"
	total_score ="("
	select_course = CourseSelectCount
	if rtrim(courseno.value)="所有課程" then
		for i = 0 to courseSelectCount - 1
 			course_sql = course_sql +"'"+rtrim(GetCourseName(i))+"'=str("+courseSelectArray(0,i)+",7,1),"
 	
			total_score = total_score + courseSelectArray(0,i)+"+"
		next
		
    else
			
		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),"
				total_score = total_score + courseNoArray(0,i)+"+"
				
				exit for
			end if
		next  
		'找出本學期是否開設了此課程,通過計算是否在開設數組中CourseSelectArrary中進行判斷
		findOpned = false
		for j = 0 to CourseSelectCount-1
			if courseNoArray(0,i)=CourseSelectArray(0,j) then
	
				findOpend = true
				exit for
			end if
		next
		if findOpend then		
			select_course = 1	
		else
			 '表明此課程沒有成績
				msgbox "沒有輸入成績,請選擇其它課程!"
				exit sub
		end if 
		
	end if 
	if select_course=0 then
		msgbox "沒有輸入成績,請選擇其它課程或成績類型!"
		exit sub
	end if
	
	
	total_score = total_score + "0)"
	avg_score = total_score+"/"+cstr(select_course) 
	course_sql = course_sql + "'總成績'=str("+total_score+",6,1),'平均成績'=str("+avg_score+",6,2)   from score "
		if search_type="CLASS" then
				course_sql = course_sql +" where 學號 in (select 學號 from student where rtrim(班號)='"+rtrim(classno.value)+"') and "
			end if 
		if search_type="GRADE" then
				course_sql = course_sql +" where 學號 in (select 學號 from student where 班號 in (select 班號 from class where year(入學年度)='"+rtrim(gradeno.value)+"')) and "
			end if
		if search_type="XH" then
				course_sql = course_sql +" where rtrim(學號) >= rtrim("+startno.value+") and rtrim(學號) <= rtrim("+endno.value+") and "
			end if
		
			     
		course_sql = course_sql + " 學年='"+yearno.value+"' and 學期='"+termno.value+"' and 成績類型='"+scoreno.value+"'"
	
	rds1.SQL = course_sql
	rds1.ExecuteOptions = 1
	rds1.refresh
	
		
	if rds1.Recordset.recordcount = 0 then
		msgbox "對不起,成績還沒有輸入無法查詢!"
    	exit sub
	end if
	scoreSort()
	rds1.Recordset.movefirst
	for i=0 to rds1.recordset.recordcount-1 
	      xhOrderArray(0,i)=rds1.Recordset.fields(0)
	      rds1.Recordset.movenext	
	next
	'xhOrderArray = rds1.recordset.Getrows(rds1.recordset.recordcount,0,0) 
	xhsort()
	
end sub

'========按班、年級及學號取某學年某學期所開所的課程======
sub get_course(get_type)
	courseSelectCount = 0
	
	select case get_type 
		case "CLASS"
			rds2.SQL = " select 課程號 from class_course where 班號='"+classno.value+"' and "&_
						" 學年='"+yearno.value+"' and 學期='"+termno.value+"'"	
		case "XH"
			rds2.SQL = "select a.課程號 from class_course as a,student as b where b.學號>='"+rtrim(startno.value)+"' and "&_
					   " b.學號<='"+rtrim(endno.value)+"' and b.班號=a.班號 and "&_
					   " 學年='"+yearno.value+"' and 學期='"+termno.value+"' group by a.課程號"
			
		case "GRADE" 
			rds2.SQL = " select 課程號 from class_course  where 班號 in (select 班號 from class where year(入學年度)='"+rtrim(gradeno.value)+"') and "&_ 
						" 學年='"+yearno.value+"' and 學期='"+termno.value+"' group by 課程號"
		
	end select
	'msgbox rds2.sql
	rds2.ExecuteOptions = 1
	rds2.Refresh
	courseSelectCount = rds2.Recordset.recordcount
	if courseSelectCount <=0 then
		exit sub 
	end if 
	
	rds2.Recordset.movefirst
	for i=0 to rds2.recordset.recordcount-1 
	      courseSelectArray(0,i)=rds2.Recordset.fields(0)
	      rds2.Recordset.movenext	
	next

	'courseSelectArray= rds2.recordset.Getrows(rds2.recordset.recordcount,0,0) 
	
end sub
				
'==============根據序列號從開設課程數組中取課程名稱=================
function  GetCourseName(cno)
	
	GetCourseName = "No Find"
	for loopcount = 0 to courseCount-1
	
		if courseNoArray(0,loopcount) = courseSelectArray(0,cno) then
			GetCourseName = courseArray(0,loopcount)
			exit for
		end if
	next
	
end function
	

'===============================================
sub xhSort()
	if len(trim(rds1.SQL)) <=0 then
		msgbox "沒有查詢結果,無法進行排序!"
		exit sub
	end if
	
	rds1.SQL = course_sql + " order by 學號"
	'msgbox rds1.sql
	rds1.ExecuteOptions=1
	rds1.refresh
	
	 xhFillOrder()
	
end sub
'==============================================================

'===============================================
sub scoreSort()
   if len(trim(rds1.SQL)) <=0 then
	msgbox "沒有查詢結果,無法進行排序!"
		exit sub
	end if
	
	rds1.SQL = course_sql + " order by "+total_score + " DESC"
	rds1.ExecuteOptions=1
	rds1.refresh
	
	scoreFillOrder()
	
	
end sub
'==============================================================
sub scoreFillOrder() '成績排序，填寫名次
	loopcount = rds1.Recordset.recordcount
	
	rds1.recordset.Movefirst
	
	for i = 0 to loopcount-1 
		rds1.recordset.fields(2).value = cstr(i+1)
		rds1.Recordset.movenext
	next
	rds1.recordset.Movefirst 	

end sub

sub xhFillOrder() '學號排序，填寫名次
	
	loopcount = rds1.Recordset.recordcount
	rds1.recordset.Movefirst	
	for i = 0 to loopcount-1
		for j = 0 to loopcount - 1
			if rds1.Recordset.fields(0).value = xhOrderArray(0,j) then
					rds1.recordset.fields(2).value = cstr(j+1)
					exit for
			end if
		next 
		rds1.Recordset.movenext
	next

end sub

'==================
sub printing()

   set xk_tjh=CreateObject("Scripting.FileSystemObject")
     
   if xk_tjh.fileexists("d:\xkprint.htm")=true then
    
     xk_tjh.DeleteFile "d:\xkprint.htm"
    
     Grid1.ExportToFile "d:\xkprint.htm",true
   else
    
     Grid1.ExportToFile "d:\xkprint.htm",true
   end if  


end sub



'=================

sub printing1()

 vp.MarginLeft=800


    vp.AbortWindowPos=1
    
    
    
	
	vp.Preview = true
	
	vp.StartDoc 
	
	rds1.recordset.movefirst
	

   print_lineno = 0  
   
  
   vp.Header = "|商業學校"+"違紀統計表"+"|"
'   vp.Footer = " ||第%d頁"
   formatstr = "<+950|<+900"
   headstr = "" 

   scoreCount = rds1.Recordset.fields.count-2
   
   for k=0 to rds1.Recordset.fields.count-1 
	   
    
     
     if k >1 and k<scoreCount then
		formatstr = formatstr+ "|<+900"
	 end if
	 
	 
	 if K>=scoreCount then
		formatstr = formatstr+ "|<+900"
	 end if
	  
	 headstr = headstr + trim(rds1.Recordset.fields(k).name)+"|"
	 
    ' msgbox headstr
    next 
   
	'print_lineno = print_lineno + 1
    rds1.Recordset.movefirst
    
    
    while not rds1.Recordset.eof
	onelinestr =""
	
     For j = 0 To  rds1.Recordset.fields.count-1 

      If isnull(rds1.Recordset.fields(j))=false  then   ' or trim(rds1.Recordset.fields(j))<>"" then
    
      
         if IsNumeric(rds1.Recordset.fields(j)) = true  Then
    
                   'xlSheet.Cells(print_lineno, j + 1) = trim(cstr(rds2.Recordset.fields(j)))
                   if j <>0 and j <>scoreCount +1 then
					onelinestr = onelinestr + trim(cstr(rds1.Recordset.fields(j))) +"|"
				  ' msgbox trim(cstr(formatnumber(rds1.Recordset.fields(j),0)))
				 '   msgbox onelinestr
				    else
					onelinestr = onelinestr + trim(cstr(rds1.Recordset.fields(j))) +"|"
				'	msgbox onelinestr
				   end if
				   
               
         else        

          'xlSheet.Cells(print_lineno, j + 1) = trim(cstr(rds2.Recordset.fields(j)))
                   onelinestr = onelinestr + trim(cstr(rds1.Recordset.fields(j))) +"|"
               '    msgbox onelinestr

         End If
     else  ' It is Null
		  onelinestr = onelinestr + "|"  
         ' msgbox onelinestr
    
    end if   

    Next
   
    print_lineno=print_lineno+1
    if print_lineno = 1 then
		vp.AddTable formatstr,headstr,onelinestr,,,false
    
    else
		vp.AddTable formatstr,headstr,onelinestr,,,true
	end if 
    rds1.Recordset.movenext
    
 wend 
 
    vp.EndDoc 
	vp.SaveDoc  "c:\xkprint.prn"
	
		
	window.open "http://<%=REQUEST.SERVERVARIABLES("SERVER_NAME")%>/shangye/public/print.asp"
	
end sub 




sub returning()
    history.go(-1)

end sub
'==============================================================
sub grid1_beforeColupdate(colindex,keyascii,cancel)
	
	'msgbox grid1.columns.item(2)
	
	if colindex=2 then
	
	   rds2.SQL="select 違紀項目 from wjset where 代碼='"&grid1.columns.item(2)&"'  "
	   
	   rds2.ExecuteOptions = 1
	   
	   rds2.Refresh 
	
	  grid1.columns.item(2).value=rds2.Recordset.fields(0)
	
	end if
	
	
end sub

</script>
<object classid="clsid:6E84D662-9599-11D2-9367-20CC03C10627" height="4" id="vp" width="1"
VIEWASTEXT>
</object>
</body>
</html>