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].[Ann](
[AnnID] [Int]  IDENTITY(1,1) NOT NULL ,
[Subject] [NVarChar] (100)  NULL ,
[Content] [NVarChar] (4000)  NULL ,
[BrowseNum] [Int]   NULL CONSTRAINT [DF_Ann_BrowseNum]  DEFAULT (0),
[DateAndTime] [DateTime]   NULL ,
[TrueName] [NVarChar] (50)  NULL ,
[UserID] [Int]   NULL ,

CONSTRAINT [PK_Ann] PRIMARY KEY CLUSTERED 
(
	[AnnID] ASC
))
GO

CREATE TABLE [dbo].[Attend](
[AttendID] [Int]  IDENTITY(1,1) NOT NULL ,
[LessonDegreeID] [Int]   NULL CONSTRAINT [DF_Attend_LessonDegreeID]  DEFAULT (0),
[StuClassID] [Int]   NULL CONSTRAINT [DF_Attend_StuClassID]  DEFAULT (0),
[AtStateID] [Int]   NULL CONSTRAINT [DF_Attend_AtStateID]  DEFAULT (0),
[Lessons] [Int]   NULL CONSTRAINT [DF_Attend_Lessons]  DEFAULT (0),
[SignOn] [DateTime]  NULL ,
[SignOut] [DateTime]  NULL ,
[Remark] [NVarChar] (255)  NULL ,
[Price] [money] NULL CONSTRAINT [DF_Attend_Price]  DEFAULT ((0)),
CONSTRAINT [PK_Attend] PRIMARY KEY CLUSTERED 
(
	[AttendID] ASC
))
GO

CREATE TABLE [dbo].[Batch](
[BatchID] [BigInt]  NOT NULL ,
[BatchTypeID] [Int]   NULL ,
[DateAndTime] [DateTime]   NULL ,
[TotalAmount] [Int]   NULL CONSTRAINT [DF_Batch_TotalAmount]  DEFAULT (0),
[TotalPurchasePrice] [Money]   NULL ,
[ProviderID] [Int]   NULL ,
[ProviderName] [NVarChar] (50)  NULL ,
[BackDate] [DateTime]   NULL ,
[Remark] [NVarChar] (255)  NULL ,
[UserID] [Int]   NULL ,
[TrueName] [NVarChar] (50)  NULL ,

CONSTRAINT [PK_Batch] PRIMARY KEY CLUSTERED 
(
	[BatchID] ASC
))
GO

CREATE TABLE [dbo].[BatchItem](
[BatchItemID] [Int]  IDENTITY(1,1) NOT NULL ,
[BatchID] [BigInt]  NOT NULL ,
[PurchasePrice] [Money]   NULL ,
[Amount] [Int]   NULL ,
[UnitName] [NVarChar] (50)  NULL ,
[CommodityID] [Int]   NULL ,
[CommodityName] [NVarChar] (50)  NULL ,
[StoCommodityID] [Int]   NULL ,

CONSTRAINT [PK_BatchItem] PRIMARY KEY CLUSTERED 
(
	[BatchItemID] ASC
))
GO

CREATE TABLE [dbo].[Bill](
[BillID] [BigInt]  NOT NULL ,
[StudentID] [Int]   NULL CONSTRAINT [DF_Bill_StudentID]  DEFAULT (0),
[StudentName] [NVarChar] (50)  NULL ,
[BillTypeID] [Int]   NULL CONSTRAINT [DF_Bill_BillTypeID]  DEFAULT (0),
[DateAndTime] [DateTime]   NULL ,
[Pay] [Money]   NULL CONSTRAINT [DF_Bill_Pay]  DEFAULT (0),
[Favourable] [Money]   NULL CONSTRAINT [DF_Bill_Favourable]  DEFAULT (0),
[Payed] [Money]   NULL CONSTRAINT [DF_Bill_Payed]  DEFAULT (0),
[ItemsCount] [Int]   NULL CONSTRAINT [DF_Bill_ItemsCount]  DEFAULT (0),
[Remark] [NVarChar] (255)  NULL ,
[PaymentID] [Int]   NULL ,
[PrintCount] [Int]   NULL CONSTRAINT [DF_Bill_PrintCount]  DEFAULT (0),
[PostSchoolID] [Int]   NULL CONSTRAINT [DF_Bill_PostSchoolID]  DEFAULT (0),
[PostSchoolName] [NVarChar] (50)  NULL ,
[UserID] [Int]   NULL CONSTRAINT [DF_Bill_UserID]  DEFAULT (0),
[TrueName] [NVarChar] (50)  NULL ,
[InsertTime] [DateTime]   NULL CONSTRAINT [DF_Bill_InsertTime]  DEFAULT (getdate()),

CONSTRAINT [PK_Bill] PRIMARY KEY CLUSTERED 
(
	[BillID] ASC
))
GO

CREATE TABLE [dbo].[BillItem](
[BillItemID] [Int]  IDENTITY(1,1) NOT NULL ,
[BillID] [BigInt]  NULL ,
[ProductID] [Int]   NULL CONSTRAINT [DF_BillItem_ProductID]  DEFAULT (0),
[ProductName] [NVarChar] (50)  NULL ,
[UnitPrice] [Money]   NULL CONSTRAINT [DF_BillItem_UnitPrice]  DEFAULT (0),
[Amount] [Int]   NULL CONSTRAINT [DF_BillItem_Amount]  DEFAULT (0),
[UnitID] [Int] NULL,
[UnitName] [NVarChar] (50)  NULL ,
[Favorable] [Money] NULL CONSTRAINT [DF_BillItem_Favorable]  DEFAULT (0),
[DealWithState] [Int]   NULL CONSTRAINT [DF_BillItem_DealWithState]  DEFAULT (0),
[Remark] [NVarChar] (255)  NULL ,
[SellID] [Int]   NULL CONSTRAINT [DF_BillItem_SellID]  DEFAULT (0),
[StuClassID] [Int]   NULL ,
[ProductType] [Int]   NULL ,
[StoCommodityID] [Int]   NULL ,
[CourseProgress] [Int]   NULL CONSTRAINT [DF_BillItem_CourseProgress]  DEFAULT (0),
[BackAmount] [Int]   NULL CONSTRAINT [DF_BillItem_BackAmount]  DEFAULT (0),
[ContinueReg] [Int]   NULL CONSTRAINT [DF_BillItem_ContinueReg]  DEFAULT (0),

CONSTRAINT [PK_BillItem] PRIMARY KEY CLUSTERED 
(
	[BillItemID] ASC
))
GO

CREATE TABLE [dbo].[BackLesson](
[ChangeID] [Int]   NOT NULL ,
[BillItemID] [Int]   NOT NULL ,
[Amount] [Int]   NULL ,
[UnitPrice] [Money]   NULL ,

CONSTRAINT [PK_BackLesson] PRIMARY KEY CLUSTERED 
(
	[ChangeID] ASC,
    [BillItemID] ASC
))
GO

CREATE TABLE [dbo].[Change](
[ChangeID] [Int]  IDENTITY(1,1) NOT NULL ,
[ChangeType] [Int]   NULL CONSTRAINT [DF_Change_ChangeType]  DEFAULT (0),
[StuClassID] [Int]   NULL CONSTRAINT [DF_Change_StuClassID]  DEFAULT (0),
[DateAndTime] [DateTime]   NULL ,
[IntoAccount] [Money]   NULL CONSTRAINT [DF_Change_IntoAccount]  DEFAULT (0),
[Lessons] [int]   NULL CONSTRAINT [DF_Change_Lessons]  DEFAULT (0),
[GetDateTime] [DateTime]   NULL ,
[Remark] [NVarChar] (255)  NULL ,
[NewStuClassID] [Int]   NULL CONSTRAINT [DF_Change_NewStuClassID]  DEFAULT (0),
[UserID] [Int]   NULL CONSTRAINT [DF_Change_UserID]  DEFAULT (0),
[TrueName] [NVarChar] (50)  NULL ,
[PaymentID] [Int]   NULL ,

CONSTRAINT [PK_Change] PRIMARY KEY CLUSTERED 
(
	[ChangeID] ASC
))
GO

CREATE TABLE [dbo].[ChatMsg](
[MsgID] [Int]  IDENTITY(1,1) NOT NULL ,
[Content] [NVarChar] (4000)  NULL ,
[DateAndTime] [DateTime]   NULL CONSTRAINT [DF_ChatMsg_DateAndTime]  DEFAULT (getdate()),
[ToUserID] [Int]   NULL ,
[ToTrueName] [NVarChar] (255)  NULL ,
[ToUserOnline] [Int]   NULL ,
[IsRead] [Int]   NULL CONSTRAINT [DF_ChatMsg_IsRead]  DEFAULT (0),
[UserID] [Int]   NULL ,
[TrueName] [NVarChar] (255)  NULL ,
[UserFace] [NVarChar] (100)  NULL ,
[UserOnline] [Int]   NULL ,

CONSTRAINT [PK_ChatMsg] PRIMARY KEY CLUSTERED 
(
	[MsgID] ASC
))
GO

CREATE TABLE [dbo].[Class](
[ClassID] [Int]   NOT NULL ,
[ClassName] [NVarChar] (50)  NULL ,
[CourseID] [Int]   NULL CONSTRAINT [DF_Class_CourseID]  DEFAULT (0),
[CourseName] [NVarChar] (50)  NULL ,
[TeacherID] [Int]   NULL CONSTRAINT [DF_Class_TeacherID]  DEFAULT (0),
[TeacherName] [NVarChar] (50)  NULL ,
[TimeRule] [NVarChar] (4000)  NULL ,
[ClassroomID] [Int]   NULL CONSTRAINT [DF_Class_ClassroomID]  DEFAULT (0),
[ClassroomName] [NVarChar] (50)  NULL ,
[StartDate] [DateTime]   NULL ,
[EndDate] [DateTime]   NULL ,
[PlanCount] [Int]   NULL CONSTRAINT [DF_Class_PlanCount]  DEFAULT (0),
[StuCount] [Int]   NULL CONSTRAINT [DF_Class_StuCount]  DEFAULT (0),
[BookCount] [Int]   NULL CONSTRAINT [DF_Class_BookCount]  DEFAULT (0),
[CourseProgress] [Int]   NULL CONSTRAINT [DF_Class_CourseProgress]  DEFAULT (0),
[Lessons] [Int]   NULL CONSTRAINT [DF_Class_Lessons]  DEFAULT (0),
[ArrangeLessons] [Int]   NULL CONSTRAINT [DF_Class_ArrangeLessons]  DEFAULT (0),
[LessonMinute] [Int]   NULL CONSTRAINT [DF_Class_LessonMinute]  DEFAULT (0),
[LessonCommission] [Money]   NULL CONSTRAINT [DF_Class_LessonCommission]  DEFAULT (0),
[TeacherScore] [SmallInt]   NULL CONSTRAINT [DF_Class_TeacherScore]  DEFAULT (0),
[Opinions] [Int]   NULL CONSTRAINT [DF_Class_Opinions]  DEFAULT (0),
[Remark] [NVarChar] (4000)  NULL ,
[DateAndTime] [DateTime]   NULL CONSTRAINT [DF_Class_DateAndTime]  DEFAULT (getdate()),
[SchoolID] [Int]   NULL CONSTRAINT [DF_Class_SchoolID]  DEFAULT (0),
[SchoolName] [NVarChar] (50)  NULL ,
[UserID] [Int]   NULL CONSTRAINT [DF_Class_UserID]  DEFAULT (0),
[TrueName] [NVarChar] (50)  NULL ,
[ClassType] [Int]   NULL ,

CONSTRAINT [PK_Class] PRIMARY KEY CLUSTERED 
(
	[ClassID] ASC
))
GO

CREATE TABLE [dbo].[Classroom](
[ClassroomID] [Int]  IDENTITY(1,1) NOT NULL ,
[ClassroomName] [NVarChar] (50)  NULL ,
[Seating] [Int]   NULL CONSTRAINT [DF_Classroom_Seating]  DEFAULT (0),
[SchoolID] [Int]   NULL CONSTRAINT [DF_Classroom_SchoolID]  DEFAULT (0),
[LockState] [Int]   NULL CONSTRAINT [DF_Classroom_LockState]  DEFAULT (1),
[Orders] [Int]   NULL CONSTRAINT [DF_Classroom_Orders]  DEFAULT (0),

CONSTRAINT [PK_Classroom] PRIMARY KEY CLUSTERED 
(
	[ClassroomID] ASC
))
GO

CREATE TABLE [dbo].[Commodity](
[CommodityID] [Int]  IDENTITY(1,1) NOT NULL ,
[CommodityName] [NVarChar] (50)  NULL ,
[CommodityTypeID] [Int]   NULL CONSTRAINT [DF_Commodity_CommodityTypeID]  DEFAULT (0),
[CommodityTypeName] [NVarChar] (50)  NULL ,
[UnitName] [NVarChar] (50)  NULL ,
[PurchasePrice] [Money]   NULL CONSTRAINT [DF_Commodity_PurchasePrice]  DEFAULT (0),
[UnitPrice] [Money]   NULL CONSTRAINT [DF_Commodity_UnitPrice]  DEFAULT (0),
[Specification] [NVarChar] (50)  NULL ,
[Amount] [Int]   NULL CONSTRAINT [DF_Commodity_Amount]  DEFAULT (0),
[WarnAmount] [Int]   NULL CONSTRAINT [DF_Commodity_WarnAmount]  DEFAULT (0),
[StoreCount] [Int]   NULL CONSTRAINT [DF_Commodity_StoreCount]  DEFAULT (0),
[AllowPoints] [Int]   NULL ,
[Points] [Int]   NULL ,
[Remark] [NVarChar] (255)  NULL ,
[LockState] [Int]   NULL CONSTRAINT [DF_Commodity_LockState]  DEFAULT (1),
[Barcode] [NVarChar] (50)  NULL ,

CONSTRAINT [PK_Commodity] PRIMARY KEY CLUSTERED 
(
	[CommodityID] ASC
))
GO

CREATE TABLE [dbo].[CommodityType](
[CommodityTypeID] [Int]  IDENTITY(1,1) NOT NULL ,
[CommodityTypeName] [NVarChar] (50)  NULL ,
[LockState] [Int]   NULL CONSTRAINT [DF_CommodityType_LockState]  DEFAULT (1),
[Orders] [Int]   NULL CONSTRAINT [DF_CommodityType_Orders]  DEFAULT (0),

CONSTRAINT [PK_CommodityType] PRIMARY KEY CLUSTERED 
(
	[CommodityTypeID] ASC
))
GO

CREATE TABLE [dbo].[Course](
[CourseID] [Int]  IDENTITY(1,1) NOT NULL ,
[CourseName] [NVarChar] (50)  NULL ,
[CourseTypeID] [Int]   NULL CONSTRAINT [DF_Course_CourseTypeID]  DEFAULT (0),
[CourseTypeName] [NVarChar] (255)  NULL ,
[UnitPrice] [Money]   NULL CONSTRAINT [DF_Course_UnitPrice]  DEFAULT (0),
[UnitID] [Int]   NULL ,
[UnitName] [NVarChar] (20)  NULL ,
[Remark] [NVarChar] (4000)  NULL ,
[LockState] [Int]   NULL CONSTRAINT [DF_Course_LockState]  DEFAULT (1),
[Orders] [Int]   NULL CONSTRAINT [DF_Course_Orders]  DEFAULT (0),

CONSTRAINT [PK_Course] PRIMARY KEY CLUSTERED 
(
	[CourseID] ASC
))
GO

CREATE TABLE [dbo].[CourseType](
[CourseTypeID] [Int]  IDENTITY(1,1) NOT NULL ,
[CourseTypeName] [NVarChar] (50)  NULL ,
[Orders] [Int]   NULL CONSTRAINT [DF_CourseType_Orders]  DEFAULT (0),

CONSTRAINT [PK_CourseType] PRIMARY KEY CLUSTERED 
(
	[CourseTypeID] ASC
))
GO

CREATE TABLE [dbo].[DBInfo](
[VersionID] [VarChar] (20)  NULL ,
[DateAndTime] [DateTime]   NULL ,
[LastBackupDate] [DateTime]   NULL ,
[LastBaseDbDate] [DateTime] NULL,
[LastFingerDate] [DateTime] NULL,
[DefaultReport] [VarChar] (100) NULL,
)
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].[Dictionary](
[DictionaryID] [Int]  IDENTITY(1,1) NOT NULL ,
[DictionaryName] [NVarChar] (50)  NULL ,
[DictionaryType] [Int]   NULL CONSTRAINT [DF_Dictionary_DictionaryType]  DEFAULT (0),
[Orders] [Int]   NULL CONSTRAINT [DF_Dictionary_Orders]  DEFAULT (0),
[LockState] [Int]   NULL CONSTRAINT [DF_Dictionary_LockState]  DEFAULT (1),

CONSTRAINT [PK_Dictionary] PRIMARY KEY CLUSTERED 
(
	[DictionaryID] ASC
))
GO

CREATE TABLE [dbo].[FeeItem](
[FeeItemID] [Int]  IDENTITY(1,1) NOT NULL ,
[FeeItemName] [NVarChar] (50)  NULL ,
[Price] [Money]   NULL CONSTRAINT [DF_FeeItem_Price]  DEFAULT (0),
[LockState] [Int]   NULL CONSTRAINT [DF_FeeItem_LockState]  DEFAULT (1),
[Orders] [Int]   NULL CONSTRAINT [DF_FeeItem_Orders]  DEFAULT (0),

CONSTRAINT [PK_FeeItem] PRIMARY KEY CLUSTERED 
(
	[FeeItemID] ASC
))
GO

CREATE TABLE [dbo].[Finger](
[FingerID] [Int]   NOT NULL ,
[FingerCode] [VarChar] (4000)  NULL ,
[Action] [Int]   NULL ,
[DateAndTime] [DateTime]   NULL ,

CONSTRAINT [PK_Finger] PRIMARY KEY CLUSTERED 
(
	[FingerID] ASC
))
GO

CREATE TABLE [dbo].[GroupUsers](
[UserGroupID] [Int]   NOT NULL ,
[UserID] [Int]   NOT NULL ,
[Orders] [Int]   NULL CONSTRAINT [DF_GroupUsers_Orders]  DEFAULT (0),

CONSTRAINT [PK_GroupUsers] PRIMARY KEY CLUSTERED 
(
	[UserGroupID] ASC,
	[UserID] ASC
))
GO

CREATE TABLE [dbo].[Holiday](
[HolidayID] [Int]  IDENTITY(1,1) NOT NULL ,
[HolidayName] [NVarChar] (50)  NULL ,
[StartDate] [DateTime]   NULL ,
[EndDate] [DateTime]   NULL ,
[Orders] [Int]   NULL CONSTRAINT [DF_Holiday_Orders]  DEFAULT (0),

CONSTRAINT [PK_Holiday] PRIMARY KEY CLUSTERED 
(
	[HolidayID] 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].[Leave](
[LeaveID] [Int]  IDENTITY(1,1) NOT NULL ,
[StudentID] [Int]   NULL CONSTRAINT [DF_Leave_StudentID]  DEFAULT (0),
[StudentName] [NVarChar] (50)  NULL ,
[DateAndTime] [DateTime]   NULL ,
[StartDate] [DateTime]   NULL ,
[EndDate] [DateTime]   NULL ,
[Remark] [NVarChar] (255)  NULL ,
[UserID] [Int]   NULL CONSTRAINT [DF_Leave_UserID]  DEFAULT (0),
[TrueName] [NVarChar] (50)  NULL ,

CONSTRAINT [PK_Leave] PRIMARY KEY CLUSTERED 
(
	[LeaveID] ASC
))
GO

CREATE TABLE [dbo].[LessonDegree](
[LessonDegreeID] [Int]  IDENTITY(1,1) NOT NULL ,
[ClassID] [Int]   NULL CONSTRAINT [DF_LessonDegree_ClassID]  DEFAULT (0),
[DateAndTime] [DateTime]   NULL CONSTRAINT [DF_LessonDegree_DateAndTime]  DEFAULT (getdate()),
[Subject] [NVarChar] (100)  NULL ,
[StartDate] [DateTime]   NULL ,
[EndDate] [DateTime]   NULL ,
[Lessons] [Int]   NULL CONSTRAINT [DF_LessonDegree_Lessons]  DEFAULT (0),
[FactCount] [Int]   NULL CONSTRAINT [DF_LessonDegree_FactCount]  DEFAULT (0),
[LateCount] [Int]   NULL CONSTRAINT [DF_LessonDegree_LateCount]  DEFAULT (0),
[AdvanceCount] [Int]   NULL CONSTRAINT [DF_LessonDegree_AdvanceCount]  DEFAULT (0),
[TruancyCount] [Int]   NULL CONSTRAINT [DF_LessonDegree_TruancyCount]  DEFAULT (0),
[LeaveCount] [Int]   NULL CONSTRAINT [DF_LessonDegree_LeaveCount]  DEFAULT (0),
[LessonType] [Int]   NULL CONSTRAINT [DF_LessonDegree_LessonType]  DEFAULT (0),
[TeacherID] [Int]   NULL CONSTRAINT [DF_LessonDegree_TeacherID]  DEFAULT (0),
[LeStateID] [Int]   NULL CONSTRAINT [DF_LessonDegree_LeStateID]  DEFAULT (0),
[ClassroomID] [Int]   NULL CONSTRAINT [DF_LessonDegree_ClassroomID]  DEFAULT (0),
[Remark] [NVarChar] (255)  NULL ,
[UserID] [Int]   NULL CONSTRAINT [DF_LessonDegree_UserID]  DEFAULT (0),

CONSTRAINT [PK_LessonDegree] PRIMARY KEY CLUSTERED 
(
	[LessonDegreeID] ASC
))
GO

CREATE TABLE [dbo].[Media](
[MediaID] [Int]  IDENTITY(1,1) NOT NULL ,
[MediaName] [NVarChar] (50)  NULL ,
[LockState] [Int]   NULL CONSTRAINT [DF_Media_LockState]  DEFAULT (1),
[Orders] [Int]   NULL CONSTRAINT [DF_Media_Orders]  DEFAULT (0),

CONSTRAINT [PK_Media] PRIMARY KEY CLUSTERED 
(
	[MediaID] ASC
))
GO

CREATE TABLE [dbo].[Online](
[UserID] [Int]   NOT NULL ,
[SessionID] [NVarChar] (50)  NULL ,
[TrueName] [NVarChar] (50)  NULL ,
[UserFace] [NVarChar] (255)  NULL ,
[Location] [NVarChar] (100)  NULL ,
[IP] [NVarChar] (20)  NULL ,
[DateAndTime] [DateTime]   NULL CONSTRAINT [DF_Online_DateAndTime]  DEFAULT (getdate()),
[LastDateTime] [DateTime]   NULL CONSTRAINT [DF_Online_LastDateTime]  DEFAULT (getdate()),

CONSTRAINT [PK_Online] PRIMARY KEY CLUSTERED 
(
	[UserID] ASC
))
GO

CREATE TABLE [dbo].[Payment](
[PaymentID] [Int]  IDENTITY(1,1) NOT NULL ,
[StudentID] [Int]   NULL CONSTRAINT [DF_Payment_StudentID]  DEFAULT (0),
[StudentName] [NVarChar] (50)  NULL ,
[PaymentTypeID] [Int]   NULL CONSTRAINT [DF_Payment_PaymentTypeID]  DEFAULT (0),
[PaymentTypeName] [NVarChar] (255)  NULL ,
[PayMethod] [NVarChar] (50)  NULL ,
[PayMoney] [Money]   NULL CONSTRAINT [DF_Payment_PayMoney]  DEFAULT (0),
[DateAndTime] [DateTime]   NULL ,
[Remark] [NVarChar] (255)  NULL ,
[CreditExpiration] [DateTime]   NULL ,
[PrintCount] [Int]   NULL CONSTRAINT [DF_Payment_PrintCount]  DEFAULT (0),
[PostSchoolID] [Int]   NULL CONSTRAINT [DF_Payment_PostSchoolID]  DEFAULT (0),
[PostSchoolName] [NVarChar] (50)  NULL ,
[UserID] [Int]   NULL CONSTRAINT [DF_Payment_UserID]  DEFAULT (0),
[TrueName] [NVarChar] (50)  NULL ,
[InsertTime] [DateTime]   NULL CONSTRAINT [DF_Payment_InsertTime]  DEFAULT (getdate()),
[LockState] [Int]   NULL CONSTRAINT [DF_Payment_LockState]  DEFAULT (1),

CONSTRAINT [PK_Payment] PRIMARY KEY CLUSTERED 
(
	[PaymentID] ASC
))
GO

CREATE TABLE [dbo].[PaymentLog](
[PaymentLogID] [NVarChar] (30)  NOT NULL ,
[Pay] [Money]   NULL ,
[DateAndTime] [DateTime]   NULL CONSTRAINT [DF_PaymentLog_DateAndTime]  DEFAULT (getdate()),
[PayState] [Int]   NULL CONSTRAINT [DF_PaymentLog_PayState]  DEFAULT (0),
[PaymentID] [Int]   NULL ,
[StudentID] [Int]   NULL ,
[StudentName] [NVarChar] (50)  NULL ,
[AlipayAccount] [NVarChar] (50)  NULL ,

CONSTRAINT [PK_PaymentLog] PRIMARY KEY CLUSTERED 
(
	[PaymentLogID] ASC
))
GO

CREATE TABLE [dbo].[PeriodTime](
[PeriodTimeID] [Int]  IDENTITY(1,1) NOT NULL ,
[StartTime] [NVarChar] (5)  NULL ,
[EndTime] [NVarChar] (5)  NULL ,
[GroupType] [Int]   NULL CONSTRAINT [DF_PeriodTime_GroupType]  DEFAULT (0),
[Orders] [Int]   NULL CONSTRAINT [DF_PeriodTime_Orders]  DEFAULT (0),

CONSTRAINT [PK_PeriodTime] PRIMARY KEY CLUSTERED 
(
	[PeriodTimeID] ASC
))
GO

CREATE TABLE [dbo].[PointsExchange](
[PointsExchangeID] [Int]  IDENTITY(1,1) NOT NULL ,
[StudentID] [Int]   NULL ,
[StudentName] [NVarChar] (50)  NULL ,
[Points] [Int]   NULL ,
[Amount] [Int]   NULL ,
[StoCommodityID] [Int]   NULL ,
[DateAndTime] [DateTime]   NULL CONSTRAINT [DF_PointsExchange_DateAndTime]  DEFAULT (getdate()),
[UserID] [Int]   NULL ,
[TrueName] [NVarChar] (50)  NULL ,
[InsertTime] [DateTime]   NULL CONSTRAINT [DF_PointsExchange_InsertTime]  DEFAULT (getdate()),

CONSTRAINT [PK_PointsExchange] PRIMARY KEY CLUSTERED 
(
	[PointsExchangeID] ASC
))
GO

CREATE TABLE [dbo].[PotCourse](
[CourseID] [Int]  IDENTITY(1,1) NOT NULL ,
[CourseName] [NVarChar] (50)  NULL ,
[LockState] [Int]   NULL CONSTRAINT [DF_PotCourse_LockState]  DEFAULT (1),
[Orders] [Int]   NULL ,

CONSTRAINT [PK_PotCourse] PRIMARY KEY CLUSTERED 
(
	[CourseID] ASC
))
GO

CREATE TABLE [dbo].[Potential](
[PotentialID] [Int]  IDENTITY(1,1) NOT NULL ,
[PotentialName] [NVarChar] (50)  NULL ,
[CourseID] [Int]   NULL CONSTRAINT [DF_Potential_CourseID]  DEFAULT (0),
[CourseName] [NVarChar] (50)  NULL ,
[SourceType] [Int]   NULL CONSTRAINT [DF_Potential_SourceType]  DEFAULT (0),
[DateAndTime] [DateTime]   NULL ,
[Sex] [Int]   NULL CONSTRAINT [DF_Potential_Sex]  DEFAULT (0),
[Appellation] [NVarChar] (50)  NULL ,
[OldStudent] [Int]   NULL CONSTRAINT [DF_Potential_OldStudent]  DEFAULT (0),
[StudentID] [Int]   NULL ,
[Tel] [NVarChar] (50)  NULL ,
[MobileTel] [NVarChar] (50)  NULL ,
[Tel1] [NVarChar] (50)  NULL ,
[Tel2] [NVarChar] (50)  NULL ,
[Tel3] [NVarChar] (50)  NULL ,
[Email] [NVarChar] (150)  NULL ,
[QQ] [VarChar] (15) NULL,
[TimeRule] [NVarChar] (50)  NULL ,
[MediaID] [Int]   NULL CONSTRAINT [DF_Potential_MediaID]  DEFAULT (0),
[MediaName] [NVarChar] (50)  NULL ,
[LastPostName] [NVarChar] (50)  NULL ,
[LastPostTime] [DateTime]   NULL CONSTRAINT [DF_Potential_LastPostTime]  DEFAULT (getdate()),
[ReCount] [Int]   NULL CONSTRAINT [DF_Potential_ReCount]  DEFAULT (0),
[StatusID] [Int]   NULL CONSTRAINT [DF_Potential_StatusID]  DEFAULT (0),
[StatusName] [NVarChar] (50)  NULL ,
[NextReplyDate] [DateTime]   NULL ,
[Remark] [NVarChar] (255)  NULL ,
[UserID] [Int]   NULL CONSTRAINT [DF_Potential_UserID]  DEFAULT (0),
[TrueName] [NVarChar] (50)  NULL ,
[SchoolID] [Int]   NULL CONSTRAINT [DF_Potential_SchoolID]  DEFAULT (0),
[SchoolName] [NVarChar] (50)  NULL ,
[InsertTime] [DateTime]   NULL CONSTRAINT [DF_Potential_InsertTime]  DEFAULT (getdate()),

CONSTRAINT [PK_Potential] PRIMARY KEY CLUSTERED 
(
	[PotentialID] ASC
))
GO

CREATE TABLE [dbo].[Provider](
[ProviderID] [Int]  IDENTITY(1,1) NOT NULL ,
[ProviderName] [NVarChar] (50)  NULL ,
[LinkMan] [NVarChar] (50)  NULL ,
[Tel] [NVarChar] (50)  NULL ,
[Address] [NVarChar] (100)  NULL ,
[LockState] [Int]   NULL CONSTRAINT [DF_Provider_LockState]  DEFAULT (1),
[Orders] [Int]   NULL ,

CONSTRAINT [PK_Provider] PRIMARY KEY CLUSTERED 
(
	[ProviderID] ASC
))
GO

CREATE TABLE [dbo].[Reminder](
[ReminderID] [Int] NOT NULL ,
[Title] [NVarChar] (255)  NULL ,
[Message] [NVarChar] (255)  NULL ,
[CountSql] [NVarChar] (4000)  NULL ,
[ContentSql] [NVarChar] (4000)  NULL ,
[NavigateUrl] [NVarChar] (255)  NULL ,
[Target] [NVarChar] (50)  NULL ,
[LockState] [Int]   NULL ,
[Remark] [NVarChar] (255)  NULL ,
[Orders] [Int]   NULL ,
[Allow] [Int]   NULL ,
[GroupAndUsers] [NVarChar] (4000)  NULL ,

CONSTRAINT [PK_Reminder] PRIMARY KEY CLUSTERED 
(
	[ReminderID] 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

CREATE TABLE [dbo].[Reply](
[ReplyID] [Int]  IDENTITY(1,1) NOT NULL ,
[PotentialID] [Int]   NULL CONSTRAINT [DF_Reply_PotentialID]  DEFAULT (0),
[DateAndTime] [DateTime]   NULL ,
[Content] [NVarChar] (4000)  NULL ,
[StatusID] [Int]   NULL CONSTRAINT [DF_Reply_StatusID]  DEFAULT (0),
[StatusName] [NVarChar] (50)  NULL ,
[UserID] [Int]   NULL CONSTRAINT [DF_Reply_UserID]  DEFAULT (0),
[TrueName] [NVarChar] (50)  NULL ,
[InsertTime] [DateTime]   NULL CONSTRAINT [DF_Reply_InsertTime]  DEFAULT (getdate()),

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

CREATE TABLE [dbo].[Reward](
[RewardID] [Int]  IDENTITY(1,1) NOT NULL ,
[RewardName] [NVarChar] (50)  NULL ,
[LockState] [Int]   NULL CONSTRAINT [DF_Reward_LockState]  DEFAULT (1),
[Orders] [Int]   NULL ,

CONSTRAINT [PK_Reward] PRIMARY KEY CLUSTERED 
(
	[RewardID] ASC
))
GO

CREATE TABLE [dbo].[School](
[SchoolID] [Int]  IDENTITY(1,1) NOT NULL ,
[SchoolName] [NVarChar] (50)  NULL ,
[Tel] [NVarChar] (50)  NULL ,
[Address] [NVarChar] (200)  NULL ,
[LockState] [Int]   NULL CONSTRAINT [DF_School_LockState]  DEFAULT (1),
[Orders] [Int]   NULL CONSTRAINT [DF_School_Orders]  DEFAULT (0),

CONSTRAINT [PK_School] PRIMARY KEY CLUSTERED 
(
	[SchoolID] ASC
))
GO

CREATE TABLE [dbo].[Scrapped](
[ScrappedID] [Int]  IDENTITY(1,1) NOT NULL ,
[ScrappedTypeID] [Int]   NULL CONSTRAINT [DF_Scrapped_ScrappedTypeID]  DEFAULT (0),
[DateAndTime] [DateTime]   NULL ,
[Amount] [Int]   NULL CONSTRAINT [DF_Scrapped_Amount]  DEFAULT (0),
[UnitName] [NVarChar] (50)  NULL ,
[Remark] [NVarChar] (255)  NULL ,
[UserID] [Int]   NULL ,
[TrueName] [NVarChar] (50)  NULL ,
[CommodityID] [Int]   NULL ,
[CommodityName] [NVarChar] (50)  NULL ,
[StoCommodityID] [Int]   NULL ,

CONSTRAINT [PK_Scrapped] PRIMARY KEY CLUSTERED 
(
	[ScrappedID] ASC
))
GO

CREATE TABLE [dbo].[Search](
[SearchID] [Int]   NOT NULL ,
[SearchName] [NVarChar] (100)  NULL ,
[DateAndTime] [DateTime]   NULL CONSTRAINT [DF_Search_DateAndTime]  DEFAULT (getdate()),
[Content] [NVarChar] (4000)  NULL ,
[ShowContent] [NVarChar] (4000)  NULL ,
[SetGroup] [NVarChar] (100)  NULL ,
[LockState] [Int]   NULL CONSTRAINT [DF_Search_LockState]  DEFAULT (1),
[Remark] [NVarChar] (255)  NULL ,
[Orders] [Int]   NULL CONSTRAINT [DF_Search_Orders]  DEFAULT (0),
[Allow] [Int]   NULL CONSTRAINT [DF_Search_Allow]  DEFAULT (0),
[GroupAndUsers] [NVarChar] (4000)  NULL ,

CONSTRAINT [PK_Search] PRIMARY KEY CLUSTERED 
(
	[SearchID] ASC
))
GO

CREATE TABLE [dbo].[SendEvent](
[SendEventID] [Int]   NOT NULL ,
[SendEventName] [NVarChar] (50)  NULL ,
[Content] [NVarChar] (500)  NULL ,
[LockState] [Int]   NULL CONSTRAINT [DF_SendEvent_LockState]  DEFAULT (0),
[DelayMinute] [Int]   NULL CONSTRAINT [DF_SendEvent_DelayMinute]  DEFAULT (0),
)
GO

CREATE TABLE [dbo].[Sending](
[smsID] [Int]  IDENTITY(1,1) NOT NULL ,
[ClientID] [Int]   NULL CONSTRAINT [DF_Sending_ClientID]  DEFAULT (0),
[ClientName] [NVarChar] (50)  NULL ,
[TargetNo] [NVarChar] (50)  NULL ,
[Content] [NVarChar] (500)  NULL ,
[DateAndTime] [DateTime]   NULL CONSTRAINT [DF_Sending_DateAndTime]  DEFAULT (getdate()),
[Errors] [Int]   NULL CONSTRAINT [DF_Sending_Errors]  DEFAULT (0),
[ErrorNum] [Int]   NULL CONSTRAINT [DF_Sending_ErrorNum]  DEFAULT (0),
[DataLevel] [int] NULL CONSTRAINT [DF_Sending_DataLevel] Default (0),
[UserLevel] [int] NULL CONSTRAINT [DF_Sending_UserLevel] Default (0),
[BatchAmount] [int] NULL CONSTRAINT [DF_Sending_BatchAmount]  Default (0),
[UserID] [Int]   NULL CONSTRAINT [DF_Sending_UserID]  DEFAULT (0),
[TrueName] [NVarChar] (50)  NULL ,

CONSTRAINT [PK_Sending] PRIMARY KEY CLUSTERED 
(
	[smsID] ASC
))
GO

CREATE TABLE [dbo].[SendLog](
[smsID] [Int]  NOT NULL ,
[ClientID] [Int]   NULL CONSTRAINT [DF_SendLog_ClientID]  DEFAULT (0),
[ClientName] [NVarChar] (50)  NULL ,
[TargetNo] [NVarChar] (50)  NULL ,
[Content] [NVarChar] (500)  NULL ,
[DateAndTime] [DateTime]   NULL CONSTRAINT [DF_SendLog_DateAndTime]  DEFAULT (getdate()),
[Result] [Int]   NULL CONSTRAINT [DF_SendLog_Result]  DEFAULT (0),
[ErrorNum] [Int]   NULL CONSTRAINT [DF_SendLog_ErrorNum]  DEFAULT (0),
[DataLevel] [int] NULL CONSTRAINT [DF_SendLog_DataLevel] Default (0),
[UserLevel] [int] NULL CONSTRAINT [DF_SendLog_UserLevel] Default (0),
[BatchAmount] [int] NULL CONSTRAINT [DF_SendLog_BatchAmount]  Default (0),
[UserID] [Int]   NULL CONSTRAINT [DF_SendLog_UserID]  DEFAULT (0),
[TrueName] [NVarChar] (50)  NULL ,

CONSTRAINT [PK_SendLog] PRIMARY KEY CLUSTERED 
(
	[smsID] ASC
))
GO

CREATE TABLE [dbo].[SmsReceive](
[SMSID] [Int]  IDENTITY(1,1) NOT NULL ,
[Mobile] [NVarChar] (50)  NULL ,
[Msg] [NVarChar] (4000)  NULL ,
[RepTime] [DateTime]   NULL ,
[ClientID] [Int]   NULL CONSTRAINT [DF_SmsReceive_ClientID]  DEFAULT (0),
[ClientName] [NVarChar] (50)  NULL ,
[UserID] [Int]   NULL CONSTRAINT [DF_SmsReceive_UserID]  DEFAULT (0),
[IsRead] [Int]   NULL CONSTRAINT [DF_SmsReceive_IsRead]  DEFAULT (0),

CONSTRAINT [PK_SmsReceive] PRIMARY KEY CLUSTERED 
(
	[SMSID] ASC
))
GO

CREATE TABLE [dbo].[SmsCom](
[SmsComID] [Int]  IDENTITY(1,1) NOT NULL ,
[ServiceNumber] [NVarChar] (50)  NULL ,
[StateNo] [NVarChar] (50)  NULL ,
[PortName] [NVarChar] (50)  NULL ,
[BaudRate] [Int]   NULL CONSTRAINT [DF_SmsCom_BaudRate]  DEFAULT (0),

CONSTRAINT [PK_SmsCom] PRIMARY KEY CLUSTERED 
(
	[SmsComID] ASC
))
GO

CREATE TABLE [dbo].[SmsTemplate](
[SmsTemplateID] [Int]  IDENTITY(1,1) NOT NULL ,
[SmsTemplateName] [NVarChar] (50)  NULL ,
[UserLevel] [int] NULL CONSTRAINT [DF_SmsTemplate_UserLevel] Default (0),
[Content] [NVarChar] (255)  NULL ,
[Orders] [Int]   NULL CONSTRAINT [DF_SmsTemplate_Orders]  DEFAULT (0),

CONSTRAINT [PK_SmsTemplate] PRIMARY KEY CLUSTERED 
(
	[SmsTemplateID] ASC
))
GO

CREATE TABLE [dbo].[Status](
[StatusID] [Int]  IDENTITY(1,1) NOT NULL ,
[StatusName] [NVarChar] (50)  NULL ,
[LockState] [Int]   NULL CONSTRAINT [DF_Status_LockState]  DEFAULT (1),
[Orders] [Int]   NULL ,

CONSTRAINT [PK_Status] PRIMARY KEY CLUSTERED 
(
	[StatusID] ASC
))
GO

CREATE TABLE [dbo].[StoCommodity](
[StoCommodityID] [Int]  IDENTITY(1,1) NOT NULL ,
[CommodityID] [Int]   NULL ,
[CommodityName] [NVarChar] (50)  NULL ,
[StoreroomID] [Int]   NULL ,
[StoreroomName] [NVarChar] (50)  NULL ,
[Amount] [Int]   NULL CONSTRAINT [DF_StoCommodity_Amount]  DEFAULT (0),

CONSTRAINT [PK_StoCommodity] PRIMARY KEY CLUSTERED 
(
	[StoCommodityID] ASC
))
GO

CREATE TABLE [dbo].[Storeroom](
[StoreroomID] [Int]  IDENTITY(1,1) NOT NULL ,
[StoreroomName] [NVarChar] (50)  NULL ,
[Tel] [NVarChar] (50)  NULL ,
[Address] [NVarChar] (100)  NULL ,
[LockState] [Int]   NULL CONSTRAINT [DF_Storeroom_LockState]  DEFAULT (1),
[Orders] [Int]   NULL ,
[SchoolID] [Int]   NULL ,

CONSTRAINT [PK_Storeroom] PRIMARY KEY CLUSTERED 
(
	[StoreroomID] ASC
))
GO

CREATE TABLE [dbo].[StuClass](
[StuClassID] [Int]  IDENTITY(1,1) NOT NULL ,
[BillItemID] [Int]   NULL CONSTRAINT [DF_StuClass_BillItemID]  DEFAULT (0),
[StudentID] [Int]   NULL CONSTRAINT [DF_StuClass_StudentID]  DEFAULT (0),
[StudentName] [NVarChar] (50)  NULL ,
[ClassID] [Int]   NULL CONSTRAINT [DF_StuClass_ClassID]  DEFAULT (0),
[ClassName] [NVarChar] (50)  NULL ,
[DateAndTime] [DateTime]   NULL ,
[Lessons] [Int]   NULL CONSTRAINT [DF_StuClass_Lessons]  DEFAULT (0),
[CourseProgress] [Int]   NULL CONSTRAINT [DF_StuClass_CourseProgress]  DEFAULT (0),
[ScStateID] [Int]   NULL CONSTRAINT [DF_StuClass_ScStateID]  DEFAULT (0),
[Score] [Decimal] (18,2)  NULL CONSTRAINT [DF_StuClass_Score]  DEFAULT (0),
[Score1] [Decimal] (18,2)  NULL CONSTRAINT [DF_StuClass_Score1]  DEFAULT (0),
[Score2] [Decimal] (18,2)  NULL CONSTRAINT [DF_StuClass_Score2]  DEFAULT (0),
[Score3] [Decimal] (18,2)  NULL CONSTRAINT [DF_StuClass_Score3]  DEFAULT (0),
[Score4] [Decimal] (18,2)  NULL CONSTRAINT [DF_StuClass_Score4]  DEFAULT (0),
[Score5] [Decimal] (18,2)  NULL CONSTRAINT [DF_StuClass_Score5]  DEFAULT (0),
[OpinionDate] [DateTime]   NULL ,
[Opinion] [NVarChar] (4000)  NULL ,
[Grade] [NVarChar] (4)  NULL ,
[TeacherScore] [SmallInt]   NULL CONSTRAINT [DF_StuClass_TeacherScore]  DEFAULT (Null),
[OpinionTeacherDate] [DateTime]   NULL ,
[OpinionTeacher] [NVarChar] (4000)  NULL ,
[FactCount] [Int]   NULL CONSTRAINT [DF_StuClass_FactCount]  DEFAULT (0),
[LateCount] [Int]   NULL CONSTRAINT [DF_StuClass_LateCount]  DEFAULT (0),
[AdvanceCount] [Int]   NULL CONSTRAINT [DF_StuClass_AdvanceCount]  DEFAULT (0),
[TruancyCount] [Int]   NULL CONSTRAINT [DF_StuClass_TruancyCount]  DEFAULT (0),
[LeaveCount] [Int]   NULL CONSTRAINT [DF_StuClass_LeaveCount]  DEFAULT (0),
[FillCount] [Int]   NULL CONSTRAINT [DF_StuClass_FillCount]  DEFAULT (0),
[BookCount] [Int]   NULL CONSTRAINT [DF_StuClass_BookCount]  DEFAULT (0),
[Remark] [NVarChar] (255)  NULL ,
[SchoolID] [Int]   NULL CONSTRAINT [DF_StuClass_SchoolID]  DEFAULT (0),
[SchoolName] [NVarChar] (50)  NULL ,
[PostSchoolID] [Int]   NULL CONSTRAINT [DF_StuClass_PostSchoolID]  DEFAULT (0),
[PostSchoolName] [NVarChar] (50)  NULL ,
[UserID] [Int]   NULL CONSTRAINT [DF_StuClass_UserID]  DEFAULT (0),
[TrueName] [NVarChar] (50)  NULL ,
[InsertTime] [DateTime]   NULL CONSTRAINT [DF_StuClass_InsertTime]  DEFAULT (getdate()),
[ChangeDate] [DateTime]   NULL ,
[ContinueReg] [Int]   NULL CONSTRAINT [DF_StuClass_ContinueReg]  DEFAULT (0),
[CertName] [NVarChar] (50)  NULL ,
[CertNumber] [NVarChar] (50)  NULL ,
[LessonPriceArray] [varchar](2000) NULL,
CONSTRAINT [PK_StuClass] PRIMARY KEY CLUSTERED 
(
	[StuClassID] ASC
))
GO

CREATE TABLE [dbo].[Student](
[StudentID] [Int]   NOT NULL ,
[StudentName] [NVarChar] (50)  NULL ,
[Byname] [NVarChar] (50)  NULL ,
[Appellation] [NVarChar] (50)  NULL ,
[Sex] [Int]   NULL CONSTRAINT [DF_Student_Sex]  DEFAULT (0),
[StudentType] [Int]   NULL CONSTRAINT [DF_Student_StudentType]  DEFAULT (0),
[Birthday] [DateTime]   NULL ,
[SchoolID] [Int]   NULL CONSTRAINT [DF_Student_SchoolID]  DEFAULT (0),
[SchoolName] [NVarChar] (50)  NULL ,
[Photo] [Image]   NULL ,
[ExistPhoto] [Bit]   NOT NULL ,
[Password] [NVarChar] (50)  NULL ,
[DateAndTime] [DateTime]   NULL ,
[Tel] [NVarChar] (50)  NULL ,
[MobileTel] [NVarChar] (50)  NULL ,
[Tel1] [NVarChar] (50)  NULL ,
[Tel2] [NVarChar] (50)  NULL ,
[Tel3] [NVarChar] (50)  NULL ,
[Email] [NVarChar] (150)  NULL ,
[QQ] [VarChar] (15) NULL,
[HomeAddress] [NVarChar] (200)  NULL ,
[PostCode] [NVarChar] (20)  NULL ,
[School] [NVarChar] (50)  NULL ,
[Grade] [NVarChar] (50)  NULL ,
[Class] [NVarChar] (50)  NULL ,
[Company] [NVarChar] (50)  NULL ,
[Headship] [NVarChar] (50)  NULL ,
[IDcard] [NVarChar] (20)  NULL ,
[SignInID] [NVarChar] (20)  NULL ,
[LastClassName] [NVarChar] (50)  NULL ,
[StuClassCount] [Int]   NULL CONSTRAINT [DF_Student_StuClassCount]  DEFAULT (0),
[ConsultantID] [Int]   NULL ,
[ConsultantName] [NVarChar] (50)  NULL ,
[MediaID] [Int]   NULL CONSTRAINT [DF_Student_MediaID]  DEFAULT (0),
[MediaName] [NVarChar] (50)  NULL ,
[Remark] [NVarChar] (4000)  NULL ,
[UserID] [Int]   NULL CONSTRAINT [DF_Student_UserID]  DEFAULT (0),
[TrueName] [NVarChar] (50)  NULL ,
[BillCount] [Int]   NULL CONSTRAINT [DF_Student_BillCount]  DEFAULT (0),
[ConsumedMoney] [Money]   NULL CONSTRAINT [DF_Student_ConsumedMoney]  DEFAULT (0),
[CreditMoney] [Money]   NULL CONSTRAINT [DF_Student_CreditMoney]  DEFAULT (0),
[AvailableMoney] [Money]   NULL CONSTRAINT [DF_Student_AvailableMoney]  DEFAULT (0),
[CreditExpiration] [DateTime]   NULL ,
[CreditRemark] [NVarChar] (4000)  NULL ,
[Points] [Int]   NULL CONSTRAINT [DF_Student_Points]  DEFAULT (0),
[PYSimple] [NVarChar] (50)  NULL ,
[Diploma] [NVarChar] (50)  NULL ,
[VIPLastDate] [DateTime]  NULL ,
[SignInSMS] [Int]   NULL CONSTRAINT [DF_Student_SignInSMS]  DEFAULT (0),
[FatherName] [NVarChar] (50)  NULL ,
[FatherMobileTel] [NVarChar] (50)  NULL ,
[FatherUnit] [NVarChar] (150)  NULL ,
[FatherHeadship] [NVarChar] (50)  NULL ,
[MotherName] [NVarChar] (50)  NULL ,
[MotherMobileTel] [NVarChar] (50)  NULL ,
[MotherUnit] [NVarChar] (150)  NULL ,
[MotherHeadship] [NVarChar] (50)  NULL ,
[FingerState] [Int] NULL CONSTRAINT DF_Student_FingerState DEFAULT (1),
[FingerCode] [VarChar] (4000)  NULL ,
[LastPostName] [NVarChar] (50)  NULL ,
[LastPostTime] [DateTime]   NULL,
[ReCount] [Int]   NULL CONSTRAINT [DF_Student_ReCount]  DEFAULT (0),
[NextReplyDate] [DateTime]   NULL ,
[CertName] [NVarChar] (50)  NULL ,
[CertNumber] [NVarChar] (50)  NULL ,
[InsertTime] [DateTime]   NULL CONSTRAINT [DF_Student_InsertTime]  DEFAULT (getdate()),

CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED 
(
	[StudentID] 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 ,
[NextReplyDate] [DateTime]   NULL ,
[InsertTime] [DateTime]   NULL CONSTRAINT [DF_StuReply_InsertTime]  DEFAULT (getdate()),

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

CREATE TABLE [dbo].[SysLog](
[SysLogID] [Int]  IDENTITY(1,1) NOT NULL ,
[SysLogType] [Int]   NULL CONSTRAINT [DF_SysLog_SysLogType]  DEFAULT (0),
[SourceURL] [NVarChar] (200)  NULL ,
[DateAndTime] [DateTime]   NULL CONSTRAINT [DF_SysLog_DateAndTime]  DEFAULT (getdate()),
[UserID] [Int]   NULL CONSTRAINT [DF_SysLog_UserID]  DEFAULT (0),
[UserName] [NVarChar] (50)  NULL ,
[TrueName] [NVarChar] (50)  NULL ,
[IP] [NVarChar] (20)  NULL ,
[SchoolName] [NVarChar] (50)  NULL ,

CONSTRAINT [PK_SysLog] PRIMARY KEY CLUSTERED 
(
	[SysLogID] ASC
))
GO

CREATE TABLE [dbo].[StuLesson](
[StuLessonID] [Int]  IDENTITY(1,1) NOT NULL ,
[StuClassID] [Int]   NULL ,
[BillItemID] [Int]   NULL ,
[UnitPrice] [Money]   NULL ,
[LessonIndex] [Int]   NULL ,
[AttendID] [Int]   NULL ,
[AtStateID] [Int]   NULL ,
[ContinueReg] [Int]   NULL ,

CONSTRAINT [PK_StuLesson] PRIMARY KEY CLUSTERED 
(
	[StuLessonID] ASC
))
GO

CREATE TABLE [dbo].[StuPoints](
[StuPointsID] [Int]  IDENTITY(1,1) NOT NULL ,
[StudentID] [Int]   NULL ,
[Points] [Int]   NULL ,
[DateAndTime] [DateTime]   NULL ,
[Remark] [NVarChar] (100)  NULL ,
[UserID] [Int]   NULL ,
[TrueName] [NVarChar] (50)  NULL ,
[InsertTime] [DateTime]   NULL CONSTRAINT [DF_StuPoints_InsertTime]  DEFAULT (getdate()),

CONSTRAINT [PK_StuPoints] PRIMARY KEY CLUSTERED 
(
	[StuPointsID] 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

CREATE TABLE [dbo].[Timecard](
[TimecardID] [Int]  IDENTITY(1,1) NOT NULL ,
[DateAndTime] [DateTime]   NULL ,
[AMStart] [DateTime]   NULL ,
[AMEnd] [DateTime]   NULL ,
[PMStart] [DateTime]   NULL ,
[PMEnd] [DateTime]   NULL ,
[NightStart] [DateTime]   NULL ,
[NightEnd] [DateTime]   NULL ,
[UserID] [Int]   NULL CONSTRAINT [DF_Timecard_UserID]  DEFAULT (0),
[TrueName] [NVarChar] (50)  NULL ,

CONSTRAINT [PK_Timecard] PRIMARY KEY CLUSTERED 
(
	[TimecardID] ASC
))
GO

CREATE TABLE [dbo].[TuneOut](
[TuneOutID] [Int]  IDENTITY(1,1) NOT NULL ,
[StoCommodityID] [Int]   NULL ,
[ToStoCommodityID] [Int]   NULL ,
[DateAndTime] [DateTime]   NULL ,
[Amount] [Int]   NULL CONSTRAINT [DF_TuneOut_Amount]  DEFAULT (0),
[Remark] [NVarChar] (255)  NULL ,
[UserID] [Int]   NULL ,
[TrueName] [NVarChar] (50)  NULL ,

CONSTRAINT [PK_TuneOut] PRIMARY KEY CLUSTERED 
(
	[TuneOutID] ASC
))
GO

CREATE TABLE [dbo].[Unit](
[UnitID] [Int]  IDENTITY(1,1) NOT NULL ,
[UnitName] [NVarChar] (50)  NULL ,
[LockState] [Int]   NULL CONSTRAINT [DF_Unit_LockState]  DEFAULT (1),
[Orders] [Int]   NULL CONSTRAINT [DF_Unit_Orders]  DEFAULT (0),

CONSTRAINT [PK_Unit] PRIMARY KEY CLUSTERED 
(
	[UnitID] ASC
))
GO

CREATE TABLE [dbo].[UseCommodity](
[UseCommodityID] [Int]  IDENTITY(1,1) NOT NULL ,
[UseCommodityTypeID] [Int]   NULL CONSTRAINT [DF_UseCommodity_UseCommodityTypeID]  DEFAULT (0),
[StoCommodityID] [Int]   NULL CONSTRAINT [DF_UseCommodity_StoCommodityID]  DEFAULT (0),
[DateAndTime] [DateTime]   NULL ,
[CommodityID] [Int]   NULL CONSTRAINT [DF_UseCommodity_CommodityID]  DEFAULT (0),
[CommodityName] [NVarChar] (50)  NULL ,
[Amount] [Int]   NULL CONSTRAINT [DF_UseCommodity_Amount]  DEFAULT (0),
[UnitName] [NVarChar] (50)  NULL ,
[Remark] [NVarChar] (255)  NULL ,
[ReturnDate] [DateTime]   NULL ,
[ReturnState] [Int]   NULL CONSTRAINT [DF_UseCommodity_ReturnState]  DEFAULT (0),
[ToUserID] [Int]   NULL ,
[ToTrueName] [NVarChar] (50)  NULL ,
[UserID] [Int]   NULL ,
[TrueName] [NVarChar] (50)  NULL ,

CONSTRAINT [PK_UseCommodity] PRIMARY KEY CLUSTERED 
(
	[UseCommodityID] ASC
))
GO

CREATE TABLE [dbo].[UserGroup](
[UserGroupID] [Int]  IDENTITY(1,1) NOT NULL ,
[UserGroupName] [NVarChar] (50)  NULL ,
[Purview] [NVarChar] (4000)  NULL ,
[PurviewGroup] [NVarChar] (50)  NULL ,
[PurviewRole] [NVarChar] (100)  NULL ,
[Remark] [NVarChar] (150)  NULL ,
[UserCount] [Int]   NULL CONSTRAINT [DF_UserGroup_UserCount]  DEFAULT (0),
[Orders] [Int]   NULL CONSTRAINT [DF_UserGroup_Orders]  DEFAULT (0),

CONSTRAINT [PK_UserGroup] PRIMARY KEY CLUSTERED 
(
	[UserGroupID] ASC
))
GO

CREATE TABLE [dbo].[UserReward](
[UserRewardID] [Int]  IDENTITY(1,1) NOT NULL ,
[DateAndTime] [DateTime]   NULL ,
[Pay] [Money]   NULL ,
[RewardID] [Int]   NULL ,
[RewardName] [NVarChar] (50)  NULL ,
[Remark] [NVarChar] (255)  NULL ,
[UserID] [Int]   NULL ,
[TrueName] [NVarChar] (50)  NULL ,
[ToUserID] [Int]   NULL ,

CONSTRAINT [PK_UserReward] PRIMARY KEY CLUSTERED 
(
	[UserRewardID] ASC
))
GO

CREATE TABLE [dbo].[Users](
[UserID] [Int]  IDENTITY(1,1) NOT NULL ,
[UserName] [NVarChar] (50)  NULL ,
[TrueName] [NVarChar] (50)  NULL ,
[Password] [NVarChar] (20)  NULL ,
[Sex] [Int]   NULL CONSTRAINT [DF_Users_Sex]  DEFAULT (0),
[UserFace] [NVarChar] (200)  NULL CONSTRAINT [DF_Users_UserFace]  DEFAULT ('1.gif'),
[Birthday] [DateTime]   NULL ,
[Online] [Int]   NULL CONSTRAINT [DF_Users_Online]  DEFAULT (0),
[Photo] [Image]   NULL ,
[Pluralist] [Int]   NULL CONSTRAINT [DF_Users_Pluralist]  DEFAULT (0),
[IsTeacher] [Int]   NULL CONSTRAINT [DF_Users_IsTeacher]  DEFAULT (0),
[ComeDate] [DateTime]   NULL CONSTRAINT [DF_Users_ComeDate]  DEFAULT (getdate()),
[ContractDate] [DateTime]   NULL ,
[OutDate] [DateTime]   NULL ,
[Course] [NVarChar] (150)  NULL ,
[FinishSchool] [NVarChar] (50)  NULL ,
[Diploma] [NVarChar] (50)  NULL ,
[Resume] [NVarChar] (4000)  NULL ,
[Tel] [NVarChar] (50)  NULL ,
[MobileTel] [NVarChar] (50)  NULL ,
[UserEmail] [NVarChar] (150)  NULL ,
[HomeAddress] [NVarChar] (50)  NULL ,
[PostCode] [NVarChar] (20)  NULL ,
[Headship] [NVarChar] (50)  NULL ,
[IDcard] [NVarChar] (20)  NULL ,
[SignInID] [NVarChar] (50)  NULL ,
[Logins] [Int]   NULL CONSTRAINT [DF_Users_Logins]  DEFAULT (0),
[LastLoginTime] [DateTime]   NULL ,
[LastLoginIP] [NVarChar] (20)  NULL ,
[BasePay] [Money]   NULL CONSTRAINT [DF_Users_BasePay]  DEFAULT (0),
[HourFee] [Money]   NULL CONSTRAINT [DF_Users_HourFee]  DEFAULT (0),
[SchoolID] [Int]   NULL CONSTRAINT [DF_Users_SchoolID]  DEFAULT (0),
[SchoolName] [NVarChar] (50)  NULL ,
[LockState] [Int]   NULL CONSTRAINT [DF_Users_LockState]  DEFAULT (1),
[lockWeb] [Int]   NULL CONSTRAINT [DF_Users_lockWeb]  DEFAULT (0),
[Parents] [Int]   NULL CONSTRAINT [DF_Users_Parents]  DEFAULT (0),
[FingerState] [Int] NULL CONSTRAINT DF_Users_FingerState DEFAULT (1),
[FingerCode] [VarChar] (4000)  NULL ,

CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED 
(
	[UserID] ASC
))
GO


-------------sql查询开始-------------
Insert into Search( SearchID, SearchName, DateAndTime, Content, ShowContent, SetGroup, LockState, Remark, Orders, Allow, GroupAndUsers)values(1, '查询充值信息', '2008-2-24 10:45:12', 'Select 
Payment.PaymentID as 充值号,
Payment.StudentID as 学号,
Payment.StudentName as 姓名,
Payment.PaymentTypeName as 充值类型,
Payment.PayMethod as 收费方式,
Payment.DateAndTime as 日期,
Payment.CreditExpiration as 信用到期,
Payment.PayMoney as 实收金额,
Payment.TrueName as 收款人
from Payment 
where Payment.PaymentTypeID in (1,2,3,4) 
and Payment.PostSchoolID in ( {@SchoolID:校区} )
and Payment.DateAndTime >= {@StartDate:开始日期} 
and Payment.DateAndTime <= {@EndDate:结束日期} 
order by Payment.UserID,Payment.DateAndTime', '', '1,4',1, '',1,0, '')
GO
Insert into Search( SearchID, SearchName, DateAndTime, Content, ShowContent, SetGroup, LockState, Remark, Orders, Allow, GroupAndUsers)values(2, '每日收入汇总', '2008-2-24 10:43:25', 'Select 
convert(varchar(10),Payment.DateAndTime,120)  as 日期,
sum(Payment.PayMoney) as 金额 
from Payment
where Payment.PostSchoolID in ( {@SchoolID:校区} ) 
and Payment.DateAndTime >= {@StartDate:开始日期} 
and Payment.DateAndTime <= {@EndDate:结束日期} 
and Payment.PaymentTypeID in (1,2) 
group by convert(varchar(10),Payment.DateAndTime,120) ', '', '1,4',1, '',2,1, '0$1|0$2')
GO
Insert into Search( SearchID, SearchName, DateAndTime, Content, ShowContent, SetGroup, LockState, Remark, Orders, Allow, GroupAndUsers)values(3, '收银员对账', '2008-2-24 10:50:02', '
Select 
Payment.UserID as 工号,
Payment.TrueName as 姓名,
Sum(Payment.PayMoney) as 金额,
Payment.UserID as ShowKey
from Payment 
where 
Payment.PaymentTypeID in (1,2) 
and Payment.PostSchoolID in ( {@SchoolID:校区} )
and Payment.DateAndTime >= {@StartDate:开始日期}            
and Payment.DateAndTime <= {@EndDate:结束日期} 
Group by Payment.UserID,Payment.TrueName', '
Select 
Payment.PaymentID as 充值号,
Payment.PaymentTypeName as 充值类型,
Payment.PayMethod as 收费方式,
Payment.DateAndTime as 交费日期,
Payment.StudentID as 学号,
Payment.StudentName as 学员,
Payment.PayMoney as 实收金额,
Payment.TrueName as 收款人
from Payment
where Payment.UserID = {@ShowKey} 
and Payment.PostSchoolID in ( {@SchoolID} )
and Payment.DateAndTime >= {@StartDate}                 
and Payment.DateAndTime <= {@EndDate} 
and Payment.PaymentTypeID in (1,2) 
order by Payment.DateAndTime desc', '4',1, '',3,0, '')
GO
Insert into Search( SearchID, SearchName, DateAndTime, Content, ShowContent, SetGroup, LockState, Remark, Orders, Allow, GroupAndUsers)values(4, '查询欠费学员', '2008-11-23 12:15:12', 'Select 
Student.StudentID as 学号,
Student.StudentName as 姓名,
Student.MobileTel as 手机,
Student.CreditMoney as 欠费金额,
Student.CreditExpiration as 到期日期,
Student.TrueName as 录入人
from Student
where Student.SchoolID in ({@SchoolID:校区}) 
and Student.CreditMoney > 0', '', '4',1, '',4,0, '')
GO
Insert into Search( SearchID, SearchName, DateAndTime, Content, ShowContent, SetGroup, LockState, Remark, Orders, Allow, GroupAndUsers)values(5, '查询优惠消费单', '2008-2-24 10:41:18', 'Select 
Bill.StudentID as 学号,
Bill.StudentName as 姓名,
Bill.DateAndTime as 日期,
Bill.Pay as 应交费,
Bill.Payed as 实交费,
Bill.Favourable as 优惠,
Bill.TrueName as 录入人
from Bill
where Bill.PostSchoolID in ( {@SchoolID:校区} )
and Bill.DateAndTime >= {@StartDate:开始日期} 
and Bill.DateAndTime <= {@EndDate:结束日期} 
and Bill.Favourable < 0 ', '', '4',1, '',5,0, '')
GO
Insert into Search( SearchID, SearchName, DateAndTime, Content, ShowContent, SetGroup, LockState, Remark, Orders, Allow, GroupAndUsers)values(6, '有余额学员', '2008-2-24 10:41:49', 'Select 
Student.StudentID as 学号,
Student.StudentName as 姓名,
Student.MobileTel as 手机,
Student.CreditMoney as 借款金额,
Student.ConsumedMoney as 消费总额,
Student.AvailableMoney as 可用金额,
Student.CreditExpiration as 借款到期,
Student.TrueName as 录入人
from Student
where Student.SchoolID in ( {@SchoolID:校区} ) 
and Student.AvailableMoney > 0', '', '4',1, '',6,0, '')
GO
Insert into Search( SearchID, SearchName, DateAndTime, Content, ShowContent, SetGroup, LockState, Remark, Orders, Allow, GroupAndUsers)values(7, '讲师课时费算法1', '2008-10-23 14:57:17', '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', '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', '3,4',1, '按老师每小时薪资计算课时费，重要参数设置，对应员工基本信息“课时费”字段',7,0, '')
GO
Insert into Search( SearchID, SearchName, DateAndTime, Content, ShowContent, SetGroup, LockState, Remark, Orders, Allow, GroupAndUsers)values(8, '交费未选班学员', '2008-2-24 10:43:50', '
Select 
Bill.StudentID as 学号,
Bill.StudentName as 姓名,
BillItem.ProductName as 课程,
BillItem.UnitPrice * BillItem.Amount as 金额,
Bill.DateAndTime as 日期
from Bill,BillItem 
where Bill.BillID = BillItem.BillID 
and Bill.BillTypeID = 1 
and BillItem.ProductType = 1 
and BillItem.DealWithState in (0)
and BillItem.SellID is null
and Bill.PostSchoolID in ( {@SchoolID:校区} )', '', '1,3',1, '',8,0, '')
GO
Insert into Search( SearchID, SearchName, DateAndTime, Content, ShowContent, SetGroup, LockState, Remark, Orders, Allow, GroupAndUsers)values(9, '广告投放效果', '2008-10-22 21:17:26', 'Select 
MediaName as 媒体名称,
Count(*) as 学员数

from Student 
where Student.DateAndTime >= {@StartDate:开始日期} 
and Student.DateAndTime <= {@EndDate:结束日期} 
and Student.SchoolID in ( {@SchoolID:校区} )
group by MediaID,MediaName', '', '3',1, '',9,0, '')
GO
Insert into Search( SearchID, SearchName, DateAndTime, Content, ShowContent, SetGroup, LockState, Remark, Orders, Allow, GroupAndUsers)values(10, '查询考勤钟信息', '2008-2-24 10:40:06', 'Select 
TrueName as 员工,
FormatDate(DateAndTime) as 日期,
FormatTime(AMStart) as 上午上班,
FormatTime(AMEnd) as 上午下班,
FormatTime(PMStart) as 下午上班,
FormatTime(PMEnd) as 下午下班,
FormatTime(NightStart) as 晚上上班,
FormatTime(NightEnd) as 晚上下班
from Timecard
WHERE Timecard.DateAndTime >= {@StartDate:开始日期} 
and Timecard.DateAndTime <= {@EndDate:结束日期} ', '', '3,4,6',1, '',10,0, '')
GO
Insert into Search( SearchID, SearchName, DateAndTime, Content, ShowContent, SetGroup, LockState, Remark, Orders, Allow, GroupAndUsers)values(11, '按班号查询学员', '2008-2-22 19:14:48', 'Select 
Student.StudentID as 学号,
Student.StudentName as 姓名,
GetSex(Student.Sex) as 性别,
FormatDate(Student.DateAndTime) as 报名时间,
FormatDate(StuClass.DateAndTime) as 选班时间,
GetScState(StuClass.ScStateID) as 选班状态, 
FormatDate(Student.Birthday) as 生日, 
Student.Tel as 电话,
Student.Mobiletel as 手机,
Student.Email as 电子邮件,
Student.HomeAddress as 地址,
Student.PostCode as 邮编,
Student.School as 学校,
Student.Grade as 年级,
Student.Company as 工作单位,
Student.Headship as 工作职务,
Student.IDcard as 身份证,
Student.Remark as 备注,
Class.ClassID as 班号,
Class.ClassName as 班级名称,
Student.SchoolName as 校区名称
from Class,StuClass,Student
where Class.ClassID=StuClass.ClassID 
and Student.StudentID=StuClass.StudentID 
and Class.ClassID = {@Key:班号} 
order by Student.StudentID', '', '3',1, '',11,0, '')
GO
Insert into Search( SearchID, SearchName, DateAndTime, Content, ShowContent, SetGroup, LockState, Remark, Orders, Allow, GroupAndUsers)values(12, '老师上课信息', '2008-2-24 10:29:43', 'Select 
FormatDate(LessonDegree.StartDate) as 日期,
FormatTime(LessonDegree.StartDate) as 上课时间,
FormatTime(LessonDegree.EndDate) as 下课时间,
GetLessonType(LessonDegree.LessonType) as 上课类型,
LessonDegree.Lessons as 课时,
LessonDegree.FactCount as 实到,
LessonDegree.LateCount as 迟到,
LessonDegree.AdvanceCount as 早退,
LessonDegree.TruancyCount as 旷课,
LessonDegree.LeaveCount as 请假,
LessonDegree.TeacherID as 教师工号,
Teacher.TrueName as 教师,
Users.TrueName as 录入人  
from LessonDegree,Class,Users,Users as Teacher 
WHERE LessonDegree.ClassID = Class.ClassID 
and LessonDegree.TeacherID = Teacher.UserID 
and LessonDegree.UserID = Users.UserID
and LessonDegree.LeStateID = 1
and LessonDegree.ClassID = {@Key:班号} ', '', '3',1, '',12,0, '')
GO
Insert into Search( SearchID, SearchName, DateAndTime, Content, ShowContent, SetGroup, LockState, Remark, Orders, Allow, GroupAndUsers)values(13, '学员出勤信息', '2008-2-24 10:30:16', 'Select 
StuClass.StudentID as 学号,
StuClass.StudentName as 姓名,
GetLessonType(LessonType) as 上课类型, 
FormatDate(LessonDegree.StartDate) as 日期,
FormatTime(LessonDegree.StartDate) as 上课时间,
FormatTime(LessonDegree.EndDate) as 下课时间,
LessonDegree.Lessons as 课时,
GetAtState(AtStateID) as 点名状态
from Attend,LessonDegree,StuClass
where LessonDegree.LessonDegreeID = Attend.LessonDegreeID 
and StuClass.StuClassID = Attend.StuClassID
and LessonDegree.ClassID = {@Key:班号}', '', '3',1, '',13,0, '')
GO
Insert into Search( SearchID, SearchName, DateAndTime, Content, ShowContent, SetGroup, LockState, Remark, Orders, Allow, GroupAndUsers)values(14, '报名日查询学员', '2008-2-24 10:44:40', 'Select 
Student.StudentID as 学号,
Student.StudentName as 姓名,
GetSex(Student.Sex) as 性别,
FormatDate(Student.DateAndTime) as 报名日期,
FormatDate(Student.Birthday) as 生日, 
Student.Tel as 电话,
Student.Mobiletel as 手机,
Student.Email as 电子邮件,
Student.HomeAddress as 地址,
Student.PostCode as 邮编,
Student.School as 学校,
Student.Grade as 年级,
Student.Company as 工作单位,
Student.Headship as 工作职务,
Student.IDcard as 身份证,
Student.Remark as 备注,
Student.StuClassCount as 绑定班级,
Student.LastClassName as 最新加入班级,
Student.MediaName as 广告方式,
Student.TrueName as 录入人,
Student.SchoolName as 上课校区 
from Student
where Student.SchoolID in ({@SchoolID:校区}) 
and DateAndTime >= {@StartDate:开始日期} 
and DateAndTime <= {@EndDate:结束日期} 
order by Student.DateAndTime desc ', '', '3',1, '',14,0, '')
GO
Insert into Search( SearchID, SearchName, DateAndTime, Content, ShowContent, SetGroup, LockState, Remark, Orders, Allow, GroupAndUsers)values(15, '排课日查询班级', '2008-2-24 10:28:19', '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.DateAndTime >= {@StartDate:开始日期} 
and LessonDegree.DateAndTime <= {@EndDate:结束日期}', '', '3',1, '',15,0, '')
GO
Insert into Search( SearchID, SearchName, DateAndTime, Content, ShowContent, SetGroup, LockState, Remark, Orders, Allow, GroupAndUsers)values(16, '排课日查询学员', '2008-2-24 10:29:06', 'Select distinct 
Student.StudentID as 学号,
Student.StudentName as 姓名,
GetSex(Student.Sex) as 性别,
FormatDate(Student.DateAndTime) as 报名时间,
FormatDate(StuClass.DateAndTime) as 选班时间,
GetScState(StuClass.ScStateID) as 选班状态, 
FormatDate(Student.Birthday) as 生日, 
Student.Tel as 电话,
Student.Mobiletel as 手机,
Student.Email as 电子邮件,
Student.HomeAddress as 地址,
Student.PostCode as 邮编,
Student.School as 学校,
Student.Grade as 年级,
Student.Company as 工作单位,
Student.Headship as 工作职务,
Student.IDcard as 身份证,
Student.Remark as 备注,
Class.ClassID as 班级编号,
Class.ClassName as 班级名称,
Class.SchoolName as 上课校区 
from Class,StuClass,Student,LessonDegree
where Class.ClassID=StuClass.classID 
and LessonDegree.ClassID = Class.ClassID
and Student.StudentID=StuClass.StudentID 
and Class.SchoolID in ( {@SchoolID:校区} ) 
and LessonDegree.DateAndTime >= {@StartDate:开始日期} 
and LessonDegree.DateAndTime <= {@EndDate:结束日期} 

order by Class.ClassID', '', '3',1, '',16,0, '')
GO
Insert into Search( SearchID, SearchName, DateAndTime, Content, ShowContent, SetGroup, LockState, Remark, Orders, Allow, GroupAndUsers)values(17, '已招满班级', '2008-2-24 10:36:17', 'Select 
Class.ClassID as 班号,
Class.ClassName as 班级名称,
Class.TeacherName as 教师,
Class.TimeRule as 上课规律,
Class.ClassroomName as 教室,
Class.StuCount as 已招人数,
Class.PlanCount as 计划招生数,
Class.Lessons as 课时,
FormatDate(Class.StartDate) as 开课时间,
FormatDate(Class.EndDate) as 结束时间,
Class.CourseName as 课程名称,
Class.SchoolName as 上课校区
from Class
where Class.SchoolID in ( {@SchoolID:校区} ) 
and Class.DateAndTime >= {@StartDate:开始日期} 
and Class.DateAndTime <= {@EndDate:结束日期} 
and Class.StuCount >= PlanCount', '', '1,3',1, '',17,0, '')
GO
Insert into Search( SearchID, SearchName, DateAndTime, Content, ShowContent, SetGroup, LockState, Remark, Orders, Allow, GroupAndUsers)values(18, '学员选班信息', '2008-2-24 10:31:06', 'Select 
StuClass.StudentID as 学号,
StuClass.StudentName as 姓名,
FormatDate(StuClass.DateAndTime) as 选班日期,
GetScState(StuClass.ScStateID) as 选班状态,
StuClass.ClassID as 班号,
StuClass.ClassName as 班级名称,
Class.TeacherName as 讲师,
Class.ClassroomName as 教室,
StuClass.Lessons as 课时,
FormatDate(Class.StartDate) as 开课时间,
FormatDate(Class.EndDate) as 结束时间,
Class.CourseName as 课程名称,
Class.SchoolName as 上课校区
from StuClass,Class,Users 
where StuClass.ClassID=Class.ClassID 
and StuClass.UserID=Users.UserID 
and StuClass.SchoolID in ( {@SchoolID:校区} ) 
and StuClass.DateAndTime >= {@StartDate:开始日期} 
and StuClass.DateAndTime <= {@EndDate:结束日期} ', '', '3',1, '',18,0, '')
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 IncomeID,AccountDate,Price,SchoolID from Income where Income = 1 
Union 
Select IncomeID,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 IncomeID,AccountDate,Price,SchoolID from Income where Income = 1 
Union 
Select IncomeID,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 IncomeID,AccountDate,Price,SchoolID,SchoolName from Income where Income = 1 
Union 
Select IncomeID,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

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

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
-------------sql查询结束-------------

-------------sql提醒开始-------------

Insert into Reminder(ReminderID,Title,Message,CountSql,ContentSql,NavigateUrl,Target,LockState,Remark,Orders,Allow,GroupAndUsers)
values(1,'数据库备份提醒','数据库超过两天没有备份了！','select count(*) from DBInfo 
where dateadd(day,2,LastBackupDate) < GetDate()','','Admin/Backup.aspx','_blank',1,'',1,0,'')
GO

Insert into Reminder(ReminderID,Title,Message,CountSql,ContentSql,NavigateUrl,Target,LockState,Remark,Orders,Allow,GroupAndUsers)
values(2,'借款到期提醒','有{0}名欠费学员到期了！','Select 
count(*)
from Student
where Student.SchoolID in ( {@SchoolID} ) 
and Student.CreditMoney > 0
and Student.CreditExpiration <= GetDate()','','Finance/Arrearage.aspx','_blank',1,'',2,0,'')
GO

Insert into Reminder(ReminderID,Title,Message,CountSql,ContentSql,NavigateUrl,Target,LockState,Remark,Orders,Allow,GroupAndUsers)
values(3,'潜在学员回访提醒','有{0}名潜在学员需要回访。','Select Count(*) from Potential where 
NextReplyDate <=  GetDate()
and UserID = {@UserID}','','Consultant/PotentialList.aspx?NextReplyDate=-1','_blank',1,'',3,0,'')
GO

Insert into Reminder(ReminderID,Title,Message,CountSql,ContentSql,NavigateUrl,Target,LockState,Remark,Orders,Allow,GroupAndUsers)
values(4,'库存报警提醒(商品)','有{0}个商品总量库存不足。','Select count(*) from Commodity where Amount <= WarnAmount','','Commodity/CommodityWarn.aspx','_blank',1,'商品在所有仓库的总数不足。',4,0,'')
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

Insert into Reminder(ReminderID,Title,Message,CountSql,ContentSql,NavigateUrl,Target,LockState,Remark,Orders,Allow,GroupAndUsers)
values(6,'学员关怀提醒','有{0}名学员需要关怀。','Select Count(*) from StuReply where 
NextReplyDate <=  GetDate()
and UserID = {@UserID} ','Select 
StuReply.StudentID as 学号,
Student.StudentName as 姓名,
StuReply.DateAndTime as 上次关怀日期,
StuReply.Content as 上次关怀内容
from StuReply,Student where 
StuReply.StudentID = Student.StudentID
and StuReply.NextReplyDate <=  GetDate()
and StuReply.UserID = {@UserID} ','','_blank',1,'',6,0,'')
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
-------------sql提醒结束-------------


------------报表及数据集开始------------

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 DataSet( DataSetID, DataSetName, ParamName, ParamType, [Content], Remark, LockState, Orders)
values( 6, '考勤数据集', 'AttendID', 1, 
'
table:Merger
cmd:  Select 
 LessonDegree.Subject,
 LessonDegree.StartDate,
 LessonDegree.EndDate,
 LessonDegree.LessonType,
 LessonDegree.LeStateID,

 Attend.AtStateID,
 Attend.SignOn,
 Attend.SignOut,
 Attend.Lessons,
 Attend.Remark,

 StuClass.ClassID,
 StuClass.ClassName,
 StuClass.StudentID,
 StuClass.StudentName,
 StuClass.Lessons as TotalLessons,
 StuClass.CourseProgress,

 LessonDegree.TeacherID,
 Users.TrueName as TeacherName,
 Classroom.ClassroomName

from LessonDegree,Attend,StuClass,Users,Classroom 
where Attend.LessonDegreeID = LessonDegree.LessonDegreeID
  and Attend.StuClassID = StuClass.StuClassID
  and Users.UserID = LessonDegree.TeacherID
  and Classroom.ClassroomID = LessonDegree.ClassroomID
  and Attend.AttendID = @AttendID 
go
'
, '', 1, 6)
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

Insert into Report( ReportID, ReportName, DataSetID, DataSetName, [Content], Location, LockState, Remark, Allow, GroupAndUsers, Orders)
values( 13, '考勤单(小票)', 6, '考勤数据集', '', '7', 1, '', 0, '', 13)
GO
------------报表及数据集结束------------




Insert into [School] (SchoolName,Orders)values('学校总部',1)
GO
Insert into UserGroup( UserGroupName, Purview, PurviewGroup, PurviewRole, Remark, UserCount, Orders)values( '超级管理员', '1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1', '1|1|1|1|1|1|1|1|1|1|1', '1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1', '', 1, 1)
GO
Insert into [Users] (UserName,TrueName,[Password],Sex,FingerState,FingerCode,SchoolID,SchoolName)values('admin','admin','7f8d3ee6b2525320',1,1,'',1,'学校总部')
GO
Insert into GroupUsers( UserGroupID, UserID)values(1, 1)
GO

Insert into Status (StatusName,LockState,Orders)values('潜在',1,1)
GO
Insert into Status (StatusName,LockState,Orders)values('战败',1,2)
GO
Insert into Status (StatusName,LockState,Orders)values('成功',1,3)
GO
Insert into PotCourse (CourseName,Orders)values('不确定',1)
GO


Insert into [Unit] (UnitName,LockState,Orders)values('本',1,1)
GO
Insert into [Unit] (UnitName,LockState,Orders)values('套',1,2)
GO


Insert into Reward (RewardName,LockState,Orders)values('招生',1,1)
GO
Insert into Reward (RewardName,LockState,Orders)values('全勤',1,2)
GO

Insert into Media (MediaName,LockState,Orders)values('宣传单',1,1)
GO

Insert into Provider (ProviderName,LockState,Orders)values('供应商',1,1)
GO

Insert into Storeroom (StoreroomName,LockState,SchoolID,Orders)values('总库',1,1,1)
GO

Insert into SendEvent(SendEventID,SendEventName,[Content])values(1,'学员签到后','')
GO
Insert into SendEvent(SendEventID,SendEventName,[Content])values(2,'学员签退后','')
GO
Insert into SendEvent(SendEventID,SendEventName,[Content])values(3,'录入报名信息后','')
GO
Insert into SendEvent(SendEventID,SendEventName,[Content])values(4,'录入选班信息后','')
GO
Insert into SendEvent(SendEventID,SendEventName,[Content])values(5,'录入充值信息后','')
GO
Insert into SendEvent(SendEventID,SendEventName,[Content])values(6,'录入消费信息后','')
GO

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

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


Insert into DBInfo (VersionID,DateAndTime,LastBackupDate,LastBaseDbDate,LastFingerDate,DefaultReport)values('2.0','2012-11-17',GetDate(),GetDate(),GetDate(),'8|11|13|0|0|0|0|0|0|0|0|0|0|0')
