Alter Table [DBInfo] ADD [DefaultReport] VarChar (100)
GO

CREATE TABLE [dbo].[DataSet](
[DataSetID] [Int]   NOT NULL ,
[DataSetName] [NVarChar] (255)  NULL ,
[ParamName] [NVarChar] (50)  NULL ,
[ParamType] [Int]   NULL CONSTRAINT [DF_DataSet_ParamType]  DEFAULT (1),
[Content] [VarChar] (8000)  NULL ,
[Remark] [NVarChar] (500)  NULL ,
[LockState] [Int]   NULL CONSTRAINT [DF_DataSet_LockState]  DEFAULT (1),
[Orders] [Int]   NULL CONSTRAINT [DF_DataSet_Orders]  DEFAULT (0),

CONSTRAINT [PK_DataSet] PRIMARY KEY CLUSTERED 
(
	[DataSetID] ASC
))
GO

CREATE TABLE [dbo].[Report](
[ReportID] [Int]   NOT NULL ,
[ReportName] [NVarChar] (50)  NULL ,
[DataSetID] [Int]   NULL ,
[DataSetName] [NVarChar] (50)  NULL ,
[Content] [VarChar] (8000)  NULL ,
[Location] [NVarChar] (100)  NULL ,
[LockState] [Int]   NULL CONSTRAINT [DF_Report_LockState]  DEFAULT (1),
[Remark] [NVarChar] (500)  NULL ,
[Allow] [Int]   NULL CONSTRAINT [DF_Report_Allow]  DEFAULT (0),
[GroupAndUsers] [VarChar] (4000)  NULL ,
[Orders] [Int]   NULL CONSTRAINT [DF_Report_Orders]  DEFAULT (0),

CONSTRAINT [PK_Report] PRIMARY KEY CLUSTERED 
(
	[ReportID] ASC
))
GO

Insert into DataSet( DataSetID, DataSetName, ParamName, ParamType, [Content], Remark, LockState, Orders)
values( 1, '选班数据集', 'StuClassID', 2, 
'
table:StuClass
cmd: Select * from StuClass where StuClassID in ( @StuClassID ) 
go

table:Class
cmd: Select * from Class where ClassID in ( Select ClassID from StuClass where StuClassID in ( @StuClassID ))
go

table:Student
cmd: Select * from Student where StudentID in ( Select StudentID from StuClass where StuClassID in ( @StuClassID ))
go
'
, '', 1, 1)
GO

Insert into DataSet( DataSetID, DataSetName, ParamName, ParamType, [Content], Remark, LockState, Orders)
values( 2, '班级数据集', 'ClassID', 1, 
'
table:Class
cmd: Select Class.* from Class where Class.ClassID = @ClassID 
go

table:StuClass
cmd: Select StuClass.* from StuClass where StuClass.ClassID = @ClassID 
go

table:Student
cmd: Select Student.* from StuClass,Student where StuClass.StudentID = Student.StudentID and StuClass.ClassID = @ClassID 
go

table:School
cmd: Select School.SchoolID,School.SchoolName,School.Tel,School.Address from School,Class where School.SchoolID = Class.SchoolID and Class.ClassID = @ClassID 
go
'
, '', 1, 2)
GO

Insert into DataSet( DataSetID, DataSetName, ParamName, ParamType, [Content], Remark, LockState, Orders)
values( 3, '班级课表数据集', 'ClassID', 1, 
'
table:Class
cmd: Select Class.* from Class where Class.ClassID = @ClassID 
go

table:LessonDegree
cmd: Select distinct LessonDegree.*,Classroom.ClassroomName,Users.TrueName as TeacherName 
     from LessonDegree,StuClass,Classroom,Users 
     where LessonDegree.ClassID = StuClass.ClassID 
     and LessonDegree.ClassroomID = Classroom.ClassroomID 
     and LessonDegree.TeacherID = Users.UserID 
     and StuClass.ClassID = @ClassID 
go
'
, '', 1, 3)
GO

Insert into DataSet( DataSetID, DataSetName, ParamName, ParamType, [Content], Remark, LockState, Orders)
values( 4, '消费单数据集', 'BillID', 1, 
'
table:Bill
cmd: Select * from Bill where BillID = @BillID 
go

table:BillItem
cmd: Select * from BillItem where BillID = @BillID 
go

table:Student
cmd: Select Student.* from Student,Bill where Student.StudentID = Bill.StudentID and Bill.BillID = @BillID 
go

table:StuClass
cmd: Select StuClass.* from StuClass,BillItem where StuClass.BillItemID = BillItem.BillItemID and BillItem.BillID= @BillID 
go

table:Class
cmd: Select Class.* from StuClass,BillItem,Class where StuClass.BillItemID = BillItem.BillItemID and StuClass.ClassID = Class.ClassID and BillItem.BillID= @BillID 
go

table:School
cmd: Select School.SchoolID,School.SchoolName,School.Tel,School.Address from School,Bill where School.SchoolID = Bill.PostSchoolID and Bill.BillID = @BillID 
go
'
, '', 1, 4)
GO

Insert into DataSet( DataSetID, DataSetName, ParamName, ParamType, [Content], Remark, LockState, Orders)
values( 5, '充值单数据集', 'PaymentID', 1, 
'
table:Payment
cmd: Select * from Payment where PaymentID = @PaymentID 
go

table:Student
cmd:  Select Student.* from Student,Payment where Student.StudentID = Payment.StudentID and Payment.PaymentID = @PaymentID 
go

table:School
cmd:  Select School.SchoolID,School.SchoolName,School.Tel,School.Address from School,Payment where School.SchoolID = Payment.PostSchoolID and Payment.PaymentID = @PaymentID 
go
'
, '', 1, 5)
GO

Insert into Report( ReportID, ReportName, DataSetID, DataSetName, [Content], Location, LockState, Remark, Allow, GroupAndUsers, Orders)
values( 1, '学员证', 1, '选班数据集', '', '3', 1, '', 0, '', 1)
GO

Insert into Report( ReportID, ReportName, DataSetID, DataSetName, [Content], Location, LockState, Remark, Allow, GroupAndUsers, Orders)
values( 2, '听课证', 1, '选班数据集', '', '3,4', 1, '', 0, '', 2)
GO

Insert into Report( ReportID, ReportName, DataSetID, DataSetName, [Content], Location, LockState, Remark, Allow, GroupAndUsers, Orders)
values( 3, '成绩单', 1, '选班数据集', '', '3,4', 1, '', 0, '', 3)
GO

Insert into Report( ReportID, ReportName, DataSetID, DataSetName, [Content], Location, LockState, Remark, Allow, GroupAndUsers, Orders)
values( 4, '班级点名表', 2, '班级数据集', '', '2', 1, '', 0, '', 4)
GO

Insert into Report( ReportID, ReportName, DataSetID, DataSetName, [Content], Location, LockState, Remark, Allow, GroupAndUsers, Orders)
values( 5, '班级课程表', 3, '班级课表数据集', '', '2', 1, '', 0, '', 5)
GO

Insert into Report( ReportID, ReportName, DataSetID, DataSetName, [Content], Location, LockState, Remark, Allow, GroupAndUsers, Orders)
values( 6, '班级学员名单', 2, '班级数据集', '', '2', 1, '', 0, '', 6)
GO

Insert into Report( ReportID, ReportName, DataSetID, DataSetName, [Content], Location, LockState, Remark, Allow, GroupAndUsers, Orders)
values( 7, '消费单(喷墨)', 4, '消费单数据集', '', '5', 1, '', 0, '', 7)
GO

Insert into Report( ReportID, ReportName, DataSetID, DataSetName, [Content], Location, LockState, Remark, Allow, GroupAndUsers, Orders)
values( 8, '消费单(小票)', 4, '消费单数据集', '', '5', 1, '', 0, '', 8)
GO

Insert into Report( ReportID, ReportName, DataSetID, DataSetName, [Content], Location, LockState, Remark, Allow, GroupAndUsers, Orders)
values( 9, '消费单(针式)', 4, '消费单数据集', '', '5', 1, '', 0, '', 9)
GO

Insert into Report( ReportID, ReportName, DataSetID, DataSetName, [Content], Location, LockState, Remark, Allow, GroupAndUsers, Orders)
values( 10, '充值单(喷墨)', 5, '充值单数据集', '', '6', 1, '', 0, '', 10)
GO

Insert into Report( ReportID, ReportName, DataSetID, DataSetName, [Content], Location, LockState, Remark, Allow, GroupAndUsers, Orders)
values( 11, '充值单(小票)', 5, '充值单数据集', '', '6', 1, '', 0, '', 11)
GO

Insert into Report( ReportID, ReportName, DataSetID, DataSetName, [Content], Location, LockState, Remark, Allow, GroupAndUsers, Orders)
values( 12, '充值单(针式)', 5, '充值单数据集', '', '6', 1, '', 0, '', 12)
GO

Update DBInfo set DateAndTime='2012-02-27',DefaultReport='7|10'
GO