20090111修改內容表

1.加入SavedQuery表.
2.加入PickList表
3.表SystemUser 加入FilterLevel欄位
4.加入一些sample資料
5.加入v_FunAcl視圖
6.完成簡單的layout代碼

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

CREATE TABLE [ACL] (
    [ACLId] [uniqueidentifier] NOT NULL ,
    [UserId] [uniqueidentifier] NULL ,
    [FunId] [uniqueidentifier] NULL ,
    [FunAct] [int] NULL
) ON [PRIMARY]
GO



/****** Object:  Table [dbo].[ACL]    Script Date: 2009/1/11 と 03:33:58 ******/
ALTER TABLE [ACL] WITH NOCHECK ADD
    CONSTRAINT [PK_ACLList] PRIMARY KEY  CLUSTERED
    (
        [ACLId]
    )  ON [PRIMARY]
GO







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

CREATE TABLE [SystemUser] (
    [SystemUserId] [uniqueidentifier] NOT NULL ,
    [FirstName] [nvarchar] (50)  NULL ,
    [LastName] [nvarchar] (50)  NULL ,
    [FullName] [nvarchar] (100)  NULL ,
    [Password] [nvarchar] (50)  NULL ,
    [FilterLevel] [tinyint] NULL
) ON [PRIMARY]
GO



/****** Object:  Table [dbo].[SystemUser]    Script Date: 2009/1/11 下午 03:07:37 ******/
ALTER TABLE [SystemUser] WITH NOCHECK ADD
    CONSTRAINT [PK_SystemUser] PRIMARY KEY  CLUSTERED
    (
        [SystemUserId]
    )  ON [PRIMARY]
GO


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

CREATE TABLE [SavedQuery] (
    [SavedQueryId] [uniqueidentifier] NOT NULL ,
    [Name] [nvarchar] (100)  NULL ,
    [Description] [nvarchar] (500)  NULL ,
    [FetchSQL] [ntext]  NULL ,
    [LayoutXml] [ntext]  NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO



/****** Object:  Table [dbo].[SavedQuery]    Script Date: 2009/1/11 下午 03:08:37 ******/
ALTER TABLE [SavedQuery] WITH NOCHECK ADD
    CONSTRAINT [PK_SavedQuery] PRIMARY KEY  CLUSTERED
    (
        [SavedQueryId]
    )  ON [PRIMARY]
GO

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

CREATE TABLE [PickList] (
    [PickListId] [uniqueidentifier] NOT NULL ,
    [TypeName] [nvarchar] (100)  NULL ,
    [TypeDescription] [nvarchar] (100)  NULL ,
    [Code] [int] NULL ,
    [CodeName] [nvarchar] (100)  NULL
) ON [PRIMARY]
GO



/****** Object:  Table [dbo].[PickList]    Script Date: 2009/1/11 下午 03:08:51 ******/
ALTER TABLE [PickList] WITH NOCHECK ADD
    CONSTRAINT [PK_PickList] PRIMARY KEY  CLUSTERED
    (
        [PickListId]
    )  ON [PRIMARY]
GO






SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

/****** Object:  View dbo.V_FunACL    Script Date: 2009/1/11 下午 03:12:38 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[V_FunACL]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[V_FunACL]
GO

CREATE vIEW V_FunACL AS
SELECT ACL.ACLId, ACL.Userid, SystemUser.FirstName, SystemUser.LastName,
    SystemUser.FullName,  SystemUser.FilterLevel , ACL.FunId, FunList.FunCode, FunList.FunName, ACL.FunAct 
    ,ACL.FunAct &1 as [Insert], ACL.FunAct &2 as [Update] ,ACL.FunAct &4 as [Delete]
    ,ACL.FunAct &8 as [Print] ,ACL.FunAct &16 as [Browse]
FROM ACL
    LEFT JOIN FunList ON ACL.FunId = FunList.FunId
    LEFT JOIN SystemUser ON SystemUser.SystemUserId= ACL.UserId



GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

DELETE from systemuser
IF NOT EXISTS(select * from systemuser)
BEGIN
INSERT INTO systemuser ( SystemUserId , FirstName , LastName , FullName , Password , FilterLevel  )  VALUES ( N'{2678FCC0-232D-46D5-8128-B32BA3E0B1BA}' , N'sa' , N'sa' , N'sa' , N'' , 0  )
END
GO

DELETE from acl
IF NOT EXISTS(select * from acl)
BEGIN
INSERT INTO acl ( ACLId , UserId , FunId , FunAct  )  VALUES ( N'{4A79BE3F-296D-4A25-93EE-458AFA0E7CD2}' , N'{2678FCC0-232D-46D5-8128-B32BA3E0B1BA}' , N'{6E57FFB0-7B65-40AE-A6D8-595D573711AA}' , 511  )
END
GO
DELETE from funlist
IF NOT EXISTS(select * from funlist)
BEGIN
INSERT INTO funlist ( FunId , FunCode , FunName  )  VALUES ( N'{0FA5BD68-50A0-4FAE-9C87-564D0E52C29F}' , N'BaseData' , N'基本資料檔'  )
INSERT INTO funlist ( FunId , FunCode , FunName  )  VALUES ( N'{6E57FFB0-7B65-40AE-A6D8-595D573711AA}' , N'ACL' , N'權限基本檔'  )
END
GO
DELETE from picklist
IF NOT EXISTS(select * from picklist)
BEGIN
INSERT INTO picklist ( PickListId , TypeName , TypeDescription , Code , CodeName  )  VALUES ( N'{B4B6CC94-EB79-457E-9C07-3AD92295D8B7}' , N'FilterLevel' , N'FilterLevel' , 3 , N'個人層級'  )
INSERT INTO picklist ( PickListId , TypeName , TypeDescription , Code , CodeName  )  VALUES ( N'{2F6AD5A3-7BC1-4776-A055-442281BD32A4}' , N'FilterLevel' , N'FilterLevel' , 1 , N'公司層級'  )
INSERT INTO picklist ( PickListId , TypeName , TypeDescription , Code , CodeName  )  VALUES ( N'{97E0D761-F33E-4F5C-8CE6-63EC450E0113}' , N'FilterLevel' , N'FilterLevel' , 2 , N'部門層級'  )
INSERT INTO picklist ( PickListId , TypeName , TypeDescription , Code , CodeName  )  VALUES ( N'{EFFD91CC-C65F-4C0C-B71F-C4D17031608E}' , N'FilterLevel' , N'FilterLevel' , 0 , N'系統層級'  )
END
GO

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

CREATE TABLE [CheckInCatego] (
    [CheckInCategoId] [uniqueidentifier] NULL ,
    [Code] [nvarchar] (100)  NULL ,
    [Name] [nvarchar] (100)  NULL
) ON [PRIMARY]
GO


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

CREATE TABLE [CheckInStatus] (
    [CheckInStatusId] [uniqueidentifier] NOT NULL ,
    [Code] [nvarchar] (100)  NULL ,
    [Name] [nvarchar] (100)  NULL
) ON [PRIMARY]
GO



/****** Object:  Table [dbo].[CheckInStatus]    Script Date: 2009/1/11 と 03:33:24 ******/
ALTER TABLE [CheckInStatus] WITH NOCHECK ADD
    CONSTRAINT [PK_ResidentRoomStatus] PRIMARY KEY  CLUSTERED
    (
        [CheckInStatusId]
    )  ON [PRIMARY]
GO



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

CREATE TABLE [CheckInTimeRange] (
    [CheckInRangeId] [uniqueidentifier] NOT NULL ,
    [SerialNo] [int] NULL ,
    [TimeStart] [nvarchar] (30)  NULL ,
    [TimeEnd] [nvarchar] (30)  NULL ,
    [TimeStyle] [int] NULL ,
    [HoursAllowed] [int] NULL ,
    [CheckOutStyle] [int] NULL ,
    [CheckOutTime] [nvarchar] (30)  NULL
) ON [PRIMARY]
GO



/****** Object:  Table [dbo].[CheckInTimeRange]    Script Date: 2009/1/11 と 03:33:43 ******/
ALTER TABLE [CheckInTimeRange] WITH NOCHECK ADD
    CONSTRAINT [PK_ResidentTimeRangeId] PRIMARY KEY  CLUSTERED
    (
        [CheckInRangeId]
    )  ON [PRIMARY]
GO








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

CREATE TABLE [RcvCatego] (
    [RcvCategoId] [uniqueidentifier] NOT NULL ,
    [Code] [nvarchar] (100)  NULL ,
    [Name] [nvarchar] (100)  NULL
) ON [PRIMARY]
GO



/****** Object:  Table [dbo].[RcvCatego]    Script Date: 2009/1/11 と 03:34:30 ******/
ALTER TABLE [RcvCatego] WITH NOCHECK ADD
    CONSTRAINT [PK_ReceiptsCatego] PRIMARY KEY  CLUSTERED
    (
        [RcvCategoId]
    )  ON [PRIMARY]
GO


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

CREATE TABLE [RoomPriceCatego] (
    [RoomPriceCategoId] [uniqueidentifier] NOT NULL ,
    [RoomId] [uniqueidentifier] NULL ,
    [StartDate] [nvarchar] (30)  NULL ,
    [UnitPrice] [decimal](18, 6) NULL ,
    [Discount] [decimal](18, 6) NULL ,
    [Description] [nvarchar] (500)  NULL
) ON [PRIMARY]
GO



/****** Object:  Table [dbo].[RoomPriceCatego]    Script Date: 2009/1/11 と 03:35:38 ******/
ALTER TABLE [RoomPriceCatego] WITH NOCHECK ADD
    CONSTRAINT [PK_RoomPrice] PRIMARY KEY  CLUSTERED
    (
        [RoomPriceCategoId]
    )  ON [PRIMARY]
GO


DELETE from syserrinfo where msgid like 'BaseData%'
IF NOT EXISTS(select * from syserrinfo where msgid like 'BaseData%')
BEGIN
INSERT INTO syserrinfo ( MessageId , LangID , MsgID , NativeCode , Message , IconStyle , BtnStyle , Title , Memo  )  VALUES ( N'{72B76F5E-3E83-4AF7-9EE3-63A48488A72A}' , N'CHN' , N'BaseData_IID_1001' , 0 , N'基本資料檔' , 48 , 0 , N' ' , N' '  )
END
GO