update Search set [Content]='
Select distinct 
Class.ClassID as 班号, 
Class.ClassName as 班级名称, 
Class.TeacherName as 教师, 
FormatDate(Class.StartDate) as 开课时间, 
Class.TimeRule as 上课规律, 
FormatDate(Class.EndDate) as 结束时间, 
Class.StuCount as 已招人数, 
Class.PlanCount as 计划招生人数, 
Class.ClassroomName as 教室, 
Class.CourseProgress as 已上课时, 
Class.Lessons as 总课时, 
Class.LessonMinute as 每课时分钟, 
Class.LessonCommission as 每课时提成, 
Class.CourseName as 课程名称, 
Class.SchoolName as 校区名称, 
Class.Remark as 班级说明 
from Class,LessonDegree 
where LessonDegree.ClassID = Class.ClassID 
and Class.SchoolID in ( {@SchoolID:校区} ) 
and LessonDegree.StartDate >= {@StartDate:开始日期} 
and LessonDegree.StartDate <= {@EndDate:结束日期} 
and LessonDegree.LeStateID = 1
',SearchName = '按上课日查询班级信息',Remark = '数据基准为班级课程表，不包含还未实际上课的记录。' where SearchID = 15
GO

update Search set [Content]='
Select distinct 
Student.StudentID as 学号, 
Student.StudentName as 姓名, 
GetSex(Student.Sex) as 性别, 
FormatDate(Student.DateAndTime) as 报名时间, 
Student.Tel as 电话, 
Student.Mobiletel as 手机, 
Student.Email as 电子邮件, 
Student.School as 学校, 
Student.Grade as 年级, 
StuClass.ClassID as 班级编号, 
StuClass.ClassName as 班级名称, 
StuClass.SchoolName as 上课校区, 
FormatDate(StuClass.DateAndTime) as 选班时间, 
GetScState(StuClass.ScStateID) as 选班状态, 
(StuClass.Lessons - StuClass.CourseProgress) as 剩余课时 

from LessonDegree,Attend,StuClass,Student 

where LessonDegree.LessonDegreeID = Attend.LessonDegreeID 
and Attend.StuClassID = StuClass.StuClassID 
and StuClass.StudentID=Student.StudentID 
and StuClass.SchoolID in ( {@SchoolID:校区} ) 
and LessonDegree.StartDate >= {@StartDate:开始日期} 
and LessonDegree.StartDate <= {@EndDate:结束日期} 
and Attend.AtStateID in (1,2,3) 
order by StuClass.ClassID
',SearchName = '按上课日查询出勤学员',Remark = '不包含旷课、请假学员。' where SearchID = 16
GO

update Search set [Content]='
Select 
AccountDate as 日期, 
sum(Price) as 金额, 
AccountDate as ShowKey 
from Income 
where SchoolID in ( {@SchoolID:校区} ) 
and AccountDate >= {@StartDate:开始日期} 
and AccountDate <= {@EndDate:结束日期} 
group by AccountDate
' where SearchID = 19
GO

update Search set [Content]='
Select 
sum(Price) as 金额, 
'''' as ShowKey 
from Income 
where SchoolID in ( {@SchoolID:校区} ) 
and AccountDate >= {@StartDate:开始日期} 
and AccountDate <= {@EndDate:结束日期} 
' where SearchID = 20
GO

update Search set [Content]='
Select 
SchoolName as 校区, 
sum(Price) as 金额, 
SchoolID as ShowKey 
from Income 
where AccountDate >= {@StartDate:开始日期} 
and AccountDate <= {@EndDate:结束日期} 
group by SchoolName,SchoolID
' where SearchID = 21
GO

update Income set Price = -Price where Income = 0
GO

ALTER TABLE Ann ALTER COLUMN [Content] VarChar(MAX)
GO

Update DBInfo set DateAndTime='2014-04-22'
GO














