alter table Reminder drop constraint PK_Reminder
GO

alter table Reminder add tempID int;
GO

update Reminder set tempID=ReminderID;
GO

alter table Reminder drop column ReminderID;
GO

exec sp_rename 'Reminder.tempID', 'ReminderID', 'column'
GO

Alter Table [Reminder] Alter Column [ReminderID] Int not null
GO

alter table Reminder add constraint PK_Reminder primary key (ReminderID) 
GO



if (select object_id('DF_Student_FingerState')) is null
Alter table [Student] add constraint DF_Student_FingerState DEFAULT ((1)) for FingerState
GO

if (select object_id('DF_Users_FingerState')) is null
Alter table [Users] add constraint DF_Users_FingerState DEFAULT ((1)) for FingerState
GO

update Student set FingerState = 1,FingerCode = '' where FingerState is null
GO

update Users set FingerState = 1,FingerCode = '' where FingerState is null
GO

Alter Table [Potential] ADD [Tel1] nvarchar(50) 
GO

Alter Table [Potential] ADD [Tel2] nvarchar(50) 
GO

Alter Table [Potential] ADD [Tel3] nvarchar(50) 
GO

update Potential set Tel1 = '',Tel2 = '',Tel3 = ''
GO

Alter Table [Student] ADD [Tel1] nvarchar(50) 
GO

Alter Table [Student] ADD [Tel2] nvarchar(50) 
GO

Alter Table [Student] ADD [Tel3] nvarchar(50) 
GO

Alter Table [Student] ADD [LastPostName] nvarchar(50) 
GO

Alter Table [Student] ADD [LastPostTime] datetime
GO

Alter Table [Student] ADD [ReCount] Int constraint DF_Student_ReCount DEFAULT (0)
GO

Alter Table [Student] ADD [NextReplyDate] datetime
GO

Alter Table [Student] ADD [CertName] nvarchar(50)
GO

Alter Table [Student] ADD [CertNumber] nvarchar(50)
GO

update Student set Tel1 = '',Tel2 = '',Tel3 = '',ReCount=0,LastPostName='',CertName='',CertNumber=''
GO

Alter Table [StuClass] ADD [CertName] nvarchar(50)
GO

Alter Table [StuClass] ADD [CertNumber] nvarchar(50)
GO

update StuClass set CertName='',CertNumber=''
GO




CREATE TABLE [dbo].[Account](
[AccountID] [Int]  IDENTITY(1,1) NOT NULL ,
[AccountName] [NVarChar] (50)  NULL ,
[Remark] [NVarChar] (255)  NULL ,
[LockState] [Int]   NULL CONSTRAINT [DF_Account_LockState]  DEFAULT (1),
[Orders] [Int]   NULL CONSTRAINT [DF_Account_Orders]  DEFAULT (0),

CONSTRAINT [PK_Account] PRIMARY KEY CLUSTERED 
(
	[AccountID] ASC
))
GO

CREATE TABLE [dbo].[Income](
[IncomeID] [Int]  IDENTITY(1,1) NOT NULL ,
[AccountDate] [DateTime]   NULL ,
[DateAndTime] [DateTime]   NULL CONSTRAINT [DF_Income_DateAndTime]  DEFAULT (getdate()),
[Income] [Int]   NULL ,
[IncomeClassID] [Int]   NULL ,
[IncomeClassName] [NVarChar] (50)  NULL ,
[Price] [Money]   NULL ,
[AccountID] [Int]   NULL ,
[AccountName] [NVarChar] (50)  NULL ,
[Remark] [NVarChar] (4000)  NULL ,
[SchoolID] [Int]   NULL ,
[SchoolName] [NVarChar] (50)  NULL ,
[TrueName] [NVarChar] (50)  NULL ,
[UserID] [Int]   NULL ,

CONSTRAINT [PK_Income] PRIMARY KEY CLUSTERED 
(
	[IncomeID] ASC
))
GO

CREATE TABLE [dbo].[IncomeClass](
[IncomeClassID] [Int]  IDENTITY(1,1) NOT NULL ,
[IncomeClassName] [NVarChar] (50)  NULL ,
[Income] [Int]   NULL CONSTRAINT [DF_IncomeClass_Income]  DEFAULT (1),
[LockState] [Int]   NULL CONSTRAINT [DF_IncomeClass_LockState]  DEFAULT (1),
[Orders] [Int]   NULL CONSTRAINT [DF_IncomeClass_Orders]  DEFAULT (0),

CONSTRAINT [PK_IncomeClass] PRIMARY KEY CLUSTERED 
(
	[IncomeClassID] ASC
))
GO

CREATE TABLE [dbo].[StuReply](
[ReplyID] [Int]  IDENTITY(1,1) NOT NULL ,
[StudentID] [Int]   NULL ,
[DateAndTime] [DateTime]   NULL ,
[Content] [NVarChar] (4000)  NULL ,
[UserID] [Int]   NULL CONSTRAINT [DF_StuReply_UserID]  DEFAULT (0),
[TrueName] [NVarChar] (50)  NULL ,
[InsertTime] [DateTime]   NULL CONSTRAINT [DF_StuReply_InsertTime]  DEFAULT (getdate()),

CONSTRAINT [PK_StuReply] PRIMARY KEY CLUSTERED 
(
	[ReplyID] ASC
))
GO

CREATE TABLE [dbo].[Task](
[TaskID] [Int]  IDENTITY(1,1) NOT NULL ,
[TaskName] [NVarChar] (255)  NULL ,
[ToUserID] [Int]   NULL ,
[ToTrueName] [NVarChar] (50)  NULL ,
[TaskContent] [NVarChar] (4000)  NULL ,
[DateAndTime] [DateTime]   NULL CONSTRAINT [DF_Task_DateAndTime]  DEFAULT (getdate()),
[StartDate] [DateTime]   NULL ,
[EndDate] [DateTime]   NULL ,
[SMS] [Int]   NULL CONSTRAINT [DF_Task_SMS]  DEFAULT (0),
[StatusID] [Int]   NULL CONSTRAINT [DF_Task_StatusID]  DEFAULT (0),
[StatusName] [NVarChar] (50)  NULL ,
[TrueName] [NVarChar] (50)  NULL ,
[UserID] [Int]   NULL ,
[ReCount] [Int]   NULL CONSTRAINT [DF_Task_ReCount]  DEFAULT (0),
[LastPostTime] [DateTime]   NULL ,

CONSTRAINT [PK_Task] PRIMARY KEY CLUSTERED 
(
	[TaskID] ASC
))
GO

CREATE TABLE [dbo].[TaskReply](
[ReplyID] [Int]  IDENTITY(1,1) NOT NULL ,
[TaskID] [Int]   NULL CONSTRAINT [DF_TaskReply_TaskID]  DEFAULT (0),
[DateAndTime] [DateTime]   NULL CONSTRAINT [DF_TaskReply_DateAndTime]  DEFAULT (getdate()),
[Content] [NVarChar] (4000)  NULL ,
[StatusID] [Int]   NULL CONSTRAINT [DF_TaskReply_StatusID]  DEFAULT (0),
[StatusName] [NVarChar] (50)  NULL ,
[UserID] [Int]   NULL CONSTRAINT [DF_TaskReply_UserID]  DEFAULT (0),
[TrueName] [NVarChar] (50)  NULL ,

CONSTRAINT [PK_TaskReply] PRIMARY KEY CLUSTERED 
(
	[ReplyID] ASC
))
GO

Insert into IncomeClass (IncomeClassName,Income,LockState,Orders) values('学费',1,1,1)
GO

Insert into Account (AccountName,Remark,LockState,Orders) values('现金','',1,1)
GO

update Search set SearchID = 200,Orders=200 where SearchID = 19
GO

update Search set SearchID = 199,Orders=199 where SearchID = 20
GO

update Search set SearchID = 188,Orders=188 where SearchID = 21
GO

Insert into Search (SearchID,SearchName,[Content],ShowContent,SetGroup,LockState,Remark,Allow,GroupAndUsers,Orders)
values
(19,'汇总日收支信息',
'
Select 
AccountDate as 日期, 
sum(Price) as 金额,
AccountDate as ShowKey
 
from 
( Select AccountDate,Price,SchoolID from Income where Income = 1 
Union 
Select AccountDate,-Price,SchoolID from Income where Income = 0) as tb 
where SchoolID in ( {@SchoolID:校区} )
and AccountDate >= {@StartDate:开始日期} 
and AccountDate <= {@EndDate:结束日期} 
group by AccountDate
',
'
Select AccountDate as 日期,
(case when Income = 0 Then ''支出'' else ''收入'' end) as 类型,
IncomeClassName as 帐目名称,
Price as 金额,
AccountName as 帐户,
Remark as 备注,
SchoolName as 校区
from Income 
where SchoolID in ( {@SchoolID} )
and AccountDate = {@ShowKey} 
',4,1,'数据源来自记帐表。',0,'',19)
GO

Insert into Search (SearchID,SearchName,[Content],ShowContent,SetGroup,LockState,Remark,Allow,GroupAndUsers,Orders)
values
(20,'汇总指定日期内收支信息',
'
Select 
sum(Price) as 金额 ,
'''' as ShowKey
from 
( Select AccountDate,Price,SchoolID from Income where Income = 1 
Union 
Select AccountDate,-Price,SchoolID from Income where Income = 0) as tb 
where SchoolID in ( {@SchoolID:校区} )
and AccountDate >= {@StartDate:开始日期} 
and AccountDate <= {@EndDate:结束日期} 
',
'
Select AccountDate as 日期,
(case when Income = 0 Then ''支出'' else ''收入'' end) as 类型,
IncomeClassName as 帐目名称,
Price as 金额,
AccountName as 帐户,
Remark as 备注,
SchoolName as 校区
from Income 
where SchoolID in ( {@SchoolID} )
and AccountDate >= {@StartDate} 
and AccountDate <= {@EndDate} 
',4,1,'数据源来自记帐表。',0,'',20)
GO

Insert into Search (SearchID,SearchName,[Content],ShowContent,SetGroup,LockState,Remark,Allow,GroupAndUsers,Orders)
values
(21,'汇总各校区收支信息',
'
Select 
SchoolName as 校区, 
sum(Price) as 金额,
SchoolID as ShowKey
 
from 
( Select AccountDate,Price,SchoolID,SchoolName from Income where Income = 1 
Union 
Select AccountDate,-Price,SchoolID,SchoolName from Income where Income = 0) as tb 
where AccountDate >= {@StartDate:开始日期} 
and AccountDate <= {@EndDate:结束日期} 
group by SchoolName,SchoolID
',
'
Select AccountDate as 日期,
(case when Income = 0 Then ''支出'' else ''收入'' end) as 类型,
IncomeClassName as 帐目名称,
Price as 金额,
AccountName as 帐户,
Remark as 备注,
SchoolName as 校区
from Income 
where SchoolID = {@ShowKey} 
and AccountDate >= {@StartDate} 
and AccountDate <= {@EndDate} 
',4,1,'数据源来自记帐表。',0,'',21)
GO

update Reminder set ReminderID = 200,Orders=200 where ReminderID = 5
GO

Insert into Reminder(ReminderID,Title,Message,CountSql,ContentSql,NavigateUrl,Target,LockState,Remark,Orders,Allow,GroupAndUsers)
values(5,'剩余4课时续费提醒','有{0}名学员需要续费','Select 
count(*)
from StuClass 
where 
 (StuClass.Lessons - StuClass.CourseProgress) > 0
and (StuClass.Lessons - StuClass.CourseProgress) <= 4
and StuClass.ScStateID in (1,2)',
'
Select 
Student.StudentID as 学号,
Student.StudentName as 姓名,
Student.MobileTel as 手机号,
StuClass.ClassID as 班号,
StuClass.ClassName as 班级名称,
Class.TeacherName as 授课老师,
(StuClass.Lessons - StuClass.CourseProgress) as 剩余课时
from Student,StuClass,Class 
where 
Student.StudentID = StuClass.StudentID
and StuClass.ClassID = Class.ClassID
and (StuClass.Lessons - StuClass.CourseProgress) > 0
and (StuClass.Lessons - StuClass.CourseProgress) <= 4
and StuClass.ScStateID in (1,2)
','','_blank',1,'',5,0,'')
GO

Update DBInfo set DateAndTime='2011-12-14'
GO