
Insert into Search (SearchID,SearchName,[Content],ShowContent,SetGroup,LockState,Remark,Allow,GroupAndUsers,Orders)
values
(23,'讲师课时费算法2',
'
Select 
LessonDegree.TeacherID as 工号, 
Users.TrueName as 姓名,
Sum(LessonDegree.Lessons) as 课时,
Sum(LessonDegree.Lessons * Class.LessonCommission) 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
order by LessonDegree.TeacherID
',
'
Select 
LessonDegree.ClassID as 班号, 
Class.ClassName as 班级,
Class.LessonMinute as 每课时分钟,
Class.LessonCommission as 每课时薪金,
Sum(LessonDegree.Lessons) as 课时,
Sum(LessonDegree.Lessons * Class.LessonCommission) 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,Class.LessonCommission
',3,1,'按班级每课时提成计算老师课时费，重要参数设置，对应班级基本信息“每课时提成”字段',0,'',23)
GO


Insert into Reminder(ReminderID,Title,Message,CountSql,ContentSql,NavigateUrl,Target,LockState,Remark,Orders,Allow,GroupAndUsers)
values(7,'库存报警提醒(仓库)','有{0}个仓库商品库存不足。',
'
Select Count(*) 
from Storeroom,StoCommodity,Commodity 
where StoCommodity.StoreroomID = Storeroom.StoreroomID 
and StoCommodity.CommodityID = Commodity.CommodityID 
and StoCommodity.Amount <= Commodity.WarnAmount
and StoCommodity.Amount <> 0
and Commodity.LockState = 1
and Storeroom.SchoolID in ( {@SchoolID} )
',
'
Select 
Commodity.CommodityID as 商品编号,
Commodity.CommodityName as 商品名称,
Storeroom.StoreroomName as 仓库名称,
StoCommodity.Amount as 库存
from Storeroom,StoCommodity,Commodity 
where StoCommodity.StoreroomID = Storeroom.StoreroomID 
and StoCommodity.CommodityID = Commodity.CommodityID 
and StoCommodity.Amount <= Commodity.WarnAmount
and StoCommodity.Amount <> 0
and Commodity.LockState = 1
and Storeroom.SchoolID in ( {@SchoolID} )
',
'','_blank',1,'显示商品在每个仓库库存不足信息，当库存为0时不显示。',7,0,'')
GO


Insert into Reminder(ReminderID,Title,Message,CountSql,ContentSql,NavigateUrl,Target,LockState,Remark,Orders,Allow,GroupAndUsers)
values(8,'物品归还提醒','有{0}条物品需要归还记录',
'
Select Count(*) from UseCommodity where ReturnDate < GetDate() and ReturnState = 0
',
'
Select 
Commodity.CommodityID as 商品编号,
Commodity.CommodityName as 商品名称,
UseCommodity.Amount as 数量,
Commodity.UnitName as 单位,
UseCommodity.ToTrueName as 领用人,
StoCommodity.StoreroomName as 仓库,
UseCommodity.DateAndTime as 办理时间,
UseCommodity.TrueName as 经办人

from UseCommodity,StoCommodity,Commodity 
where UseCommodity.StoCommodityID = StoCommodity.StoCommodityID 
and StoCommodity.CommodityID = Commodity.CommodityID
and UseCommodity.ReturnDate < GetDate() 
and UseCommodity.ReturnState = 0
',
'','_blank',1,'',8,0,'')
GO


Insert into Reminder(ReminderID,Title,Message,CountSql,ContentSql,NavigateUrl,Target,LockState,Remark,Orders,Allow,GroupAndUsers)
values(9,'员工合同到期提醒','有{0}名员工合同到期',
'
Select 
count(*)
from Users
where Users.LockState = 1 and Users.ContractDate < GetDate()
',
'
Select 
UserID as 工号,
TrueName as 姓名,
Tel as 电话,
Mobiletel as 手机,
ContractDate as 到期时间
from Users
where Users.LockState = 1 and Users.ContractDate < GetDate()
',
'','_blank',1,'',9,0,'')
GO


Update Search set SearchName = '讲师课时费算法1',Remark='按老师每小时薪资计算课时费，重要参数设置，对应员工基本信息“课时费”字段' where SearchID = 7
GO

Update Reminder set Title = '库存报警提醒(商品)',Message = '有{0}个商品总量库存不足。',Remark = '商品在所有仓库的总数不足。' where ReminderID = 4
GO

Alter Table [Users] ADD [ContractDate][DateTime] null
GO

Update DBInfo set DateAndTime='2012-11-17'
GO














