if exists (select * from dbo.sysobjects where id = object_id(N'[Tasks]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [Tasks]
GO

CREATE TABLE [Tasks] (
	[pkId] [int] IDENTITY (1, 1) NOT NULL ,
	[NO] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Title] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Department] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[JoinTime] [datetime] NOT NULL ,
	[ADUserName1] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[ADUserName1Time] [datetime] NOT NULL ,
	[UserID] [int] NOT NULL ,
	[PassTime] [datetime] NOT NULL ,
	[PassUserID] [int] NOT NULL ,
	[LeadingPassUserID] [int] NULL ,
	[LeadingPassTime] [datetime] NULL ,
	[OverTime] [datetime] NOT NULL ,
	[OverUserID] [int] NOT NULL ,
	[ResponsiblePersons] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[RPSex] [int] NOT NULL ,
	[RPNational] [int] NOT NULL ,
	[RPBirthday] [datetime] NOT NULL ,
	[RPPositions] [int] NOT NULL ,
	[RPTitle] [int] NOT NULL ,
	[RPExpertise] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[RPEducation] [int] NOT NULL ,
	[RPWorkDepartment] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[RPAddress] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[RPPostCode] [char] (6) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[RPPhone] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[RPEmail] [varchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Participants] [ntext] COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[FinalResultsExpected] [int] NOT NULL ,
	[RelatedResearchResults] [ntext] COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[ResearchUndertakens] [ntext] COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[DesignVerificationIssues] [ntext] COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[CompletedTheFeasibilityAnalysis] [ntext] COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[MainResults] [ntext] COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[FinalResearchResults] [ntext] COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[TaskApplyDepartmentViews] [ntext] COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[ADUserName2] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[ADUserName2Time] [datetime] NOT NULL ,
	[TotalGroupViews] [ntext] COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[TotalGroupViewsUserName] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[TotalGroupViewsUserNameTime] [datetime] NOT NULL ,
	[LeadingGroupViews] [ntext] COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[LeadingGroupViewsUserName] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[LeadingGroupViewsUserNameTime] [datetime] NOT NULL ,
	[Status] [int] NOT NULL ,
	[DocFile] [nvarchar] (1000) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[BigClassID] [int] NOT NULL ,
	[Integrity] [int] NOT NULL ,
	CONSTRAINT [PK_TASKS] PRIMARY KEY  CLUSTERED 
	(
		[pkId]
	)  ON [PRIMARY] 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO


if exists (select * from dbo.sysobjects where id = object_id(N'[School]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [School]
GO

CREATE TABLE [School] (
	[pkId] [int] IDENTITY (1, 1) NOT NULL ,
	[SchoolName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
	[Address] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
	[Contact] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
	[Telephone] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
	CONSTRAINT [PK_School] PRIMARY KEY  CLUSTERED 
	(
		[pkId]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO


if exists (select * from dbo.sysobjects where id = object_id(N'[SchoolUsers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [SchoolUsers]
GO

CREATE TABLE [SchoolUsers] (
	[pkId] [int] IDENTITY (1000, 1) NOT NULL ,
	[UserId] [int] NULL ,
	[SchoolId] [int] NULL ,
	[CanVoteFirst] [int] NULL ,
	[CanVoteSecond] [int] NULL ,
	[FirstSchoolInfo] [int] NULL ,
	[First] [int] NULL ,
	[Second] [int] NULL ,
	[Third] [int] NULL ,
	[Forth] [int] NULL ,
	[Fifth] [int] NULL ,
	[Sixth] [int] NULL ,
	[SecondResear] [int] NULL ,
	[Department] [int] NULL ,
	[IsAdmin] [int] NULL ,
	CONSTRAINT [PK_SchoolUsers] PRIMARY KEY  CLUSTERED 
	(
		[pkId]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO


if exists (select * from dbo.sysobjects where id = object_id(N'[messageForward]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [messageForward]
GO

CREATE TABLE [messageForward] (
	[pkId] [int] IDENTITY (1, 1) NOT NULL ,
	[messageId] [int] NOT NULL ,
	[ForwardToUserName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Status] [int] NULL ,
	CONSTRAINT [PK_messageForward] PRIMARY KEY  CLUSTERED 
	(
		[pkId]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO


