update Search set SearchID = SearchID+300,Orders=SearchID+300 where SearchID > 21
GO

Insert into Search (SearchID,SearchName,[Content],ShowContent,SetGroup,LockState,Remark,Allow,GroupAndUsers,Orders)
values
(22,'查询班级出勤率',
'
Select 
Class.ClassID as 班号,
Class.ClassName as 班级名称,
Class.TeacherName as 老师,
sum(FactCount) as 实到人次,
sum(TruancyCount) as 旷课人次,
sum(LeaveCount) as 请假人次,
round(sum(FactCount)/sum((FactCount+TruancyCount+LeaveCount))*100,2) as 出勤率
from Class,LessonDegree 
where Class.ClassID = LessonDegree.ClassID 
and LeStateID = 1
and LessonDegree.StartDate >= {@StartDate:开始日期} 
and LessonDegree.EndDate <= {@EndDate:结束日期} 
and Class.SchoolID in ( {@SchoolID:校区} )
group by Class.ClassID,Class.ClassName,Class.TeacherName
',
'',3,1,'出勤率：按百分比计算。',0,'',22)
GO

update Search set [Content]='
Select 
LessonDegree.TeacherID as 工号, 
Users.TrueName as 姓名,
Sum(LessonDegree.Lessons) as 课时,
cast(Sum(LessonDegree.Lessons*Class.LessonMinute) as decimal) /60 as 小时,
cast(Sum(LessonDegree.Lessons*Class.LessonMinute) as decimal) /60 *Users.HourFee as 工资,
LessonDegree.TeacherID as ShowKey

from LessonDegree,Class,Users
where LessonDegree.ClassID = Class.ClassID 
and LessonDegree.TeacherID = Users.UserID
and LessonDegree.LeStateID = 1
and LessonDegree.StartDate >= {@StartDate:开始日期} 
and LessonDegree.StartDate <= {@EndDate:结束日期} 
group by LessonDegree.TeacherID,Users.TrueName,Users.HourFee
',[ShowContent]='
Select 
LessonDegree.ClassID as 班号, 
Class.ClassName as 班级,
Class.LessonMinute as 每课时分钟,
Sum(LessonDegree.Lessons) as 课时,
cast(Sum(LessonDegree.Lessons*Class.LessonMinute) as decimal) /60 as 小时,
cast(Sum(LessonDegree.Lessons*Class.LessonMinute) as decimal) /60 *Users.HourFee as 工资
from LessonDegree,Class,Users
where LessonDegree.ClassID = Class.ClassID 
and LessonDegree.TeacherID = Users.UserID
and LessonDegree.LeStateID = 1
and LessonDegree.TeacherID = {@ShowKey} 
and LessonDegree.StartDate >= {@StartDate} 
and LessonDegree.StartDate <= {@EndDate} 
group by LessonDegree.ClassID,Class.ClassName,Class.LessonMinute,Users.HourFee
' where SearchID = 7
GO

Alter Table [Sending] ADD [BatchAmount] [int] CONSTRAINT [DF_Sending_BatchAmount]  Default 0 
GO
Alter Table [SendLog] ADD [BatchAmount] [int] CONSTRAINT [DF_SendLog_BatchAmount]  Default 0 
GO
Update Sending set BatchAmount = 0
GO
Update SendLog set BatchAmount = 0
GO

Update DBInfo set DateAndTime='2012-10-28'
GO














