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



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


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



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



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

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

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

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


CREATE TABLE [siteOtherProperty] (
	[pkId] [int] IDENTITY (1, 1) NOT NULL ,
	[newsDefaultClick] [int] NULL ,
	[showNewHours] [int] NULL ,
	[showHotClick] [int] NULL ,
	CONSTRAINT [PK_siteOtherProperty] PRIMARY KEY  CLUSTERED 
	(
		[pkId]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO



CREATE TABLE [AspProperty] (
	[pkId] [int] IDENTITY (1, 1) NOT NULL ,
	[url] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
	[space] [int] NULL ,
	[usedImagesSpace] [float] NULL ,
	[usedBackupSpace] [float] NULL ,
	[usedSpace] [float] NULL ,
	[buildByAsp] [int] NULL ,
	CONSTRAINT [AspProperty_PK] PRIMARY KEY  CLUSTERED 
	(
		[pkId]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO



CREATE TABLE [SignUpBuildField] (
	[PkId] [int] IDENTITY (1, 1) NOT NULL ,
	[TableName] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
	[Name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[Type] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[Size] [int] NULL ,
	[ShowName] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
	[ShowType] [varchar] (250) COLLATE Chinese_PRC_CI_AS NULL ,
	[ShowValue] [varchar] (1024) COLLATE Chinese_PRC_CI_AS NULL ,
	[ShowComments] [varchar] (1024) COLLATE Chinese_PRC_CI_AS NULL ,
	[AllowNull] [int] NULL ,
	[IsSystem] [int] NULL ,
	[IsOnly] [int] NULL ,
	[IsAddMark] [int] NULL ,
	[AddMarkField] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	CONSTRAINT [PK_SignUpBuildField] PRIMARY KEY  CLUSTERED 
	(
		[PkId]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO


CREATE TABLE [SignUpField] (
	[PkId] [int] IDENTITY (1, 1) NOT NULL ,
	[Name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[Type] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[Size] [int] NULL ,
	[ShowName] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
	[ShowType] [varchar] (250) COLLATE Chinese_PRC_CI_AS NULL ,
	[ShowValue] [varchar] (1024) COLLATE Chinese_PRC_CI_AS NULL ,
	[ShowComments] [varchar] (1024) COLLATE Chinese_PRC_CI_AS NULL ,
	[AllowNull] [int] NULL ,
	[IsSystem] [int] NULL ,
	[IsOnly] [int] NULL ,
	CONSTRAINT [PK_SignUpField] PRIMARY KEY  CLUSTERED 
	(
		[PkId]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO


CREATE TABLE [SignUpRelation] (
	[PkId] [int] IDENTITY (1, 1) NOT NULL ,
	[BigClassId] [int] NULL ,
	[TableName] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
	CONSTRAINT [PK_SignUpRelation] PRIMARY KEY  CLUSTERED 
	(
		[PkId]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO


CREATE VIEW [fpf_employee]
AS
SELECT     NULL AS employeeid, NULL AS employeecode, NULL AS employeename, NULL AS sex, '2000-9-1' AS birthday, NULL AS deptid, NULL 
                      AS schooldeptid, NULL AS isdelete, '2000-9-1' AS modifieddate, NULL AS nation, NULL AS state, NULL AS caegory, NULL AS curlive

GO

CREATE VIEW [fpf_department] (deptid,deptcode,deptname,type,level,parentid,
			isdelete,schooldeptid,modifieddate ) AS
 	select '000000000001','000000000001', unitName, 1,0,0,-1,0,1
	from unitInfo

GO

CREATE VIEW [fpf_user]
AS
SELECT     pkId AS id, userName AS loginname, password, fullName AS nickname, email, '1' AS type, '000000000001' AS deptid, - 1 AS isdelete, 
                      1 AS modifieddate, examine AS isactive, examine AS islock, NULL AS employeeid, NULL AS activer, '2000-9-1' AS activedate
FROM         Users
WHERE     (userName <> 'default') AND (userName <> 'admin')

GO