 
/****** Object:  Table [dbo].[usersinroles]    Script Date: 10/20/2010 05:44:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[usersinroles](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[username] [nvarchar](50) NULL,
	[role] [nvarchar](50) NULL,
 CONSTRAINT [PK_usersinroles] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[hr_usersExt]    Script Date: 10/20/2010 05:44:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[hr_usersExt](
	[extid] [int] IDENTITY(1,1) NOT NULL,
	[samaccount] [nvarchar](50) NOT NULL,
	[cnname] [nvarchar](50) NULL,
	[sex] [nvarchar](50) NULL,
	[avtor] [nvarchar](500) NULL,
	[staffcode] [nvarchar](50) NULL,
	[Isstaff] [int] NULL,
	[issec] [int] NULL,
	[job] [nvarchar](3000) NULL,
	[ismgr] [int] NULL,
	[idcard] [nvarchar](50) NULL,
	[cardno] [nvarchar](50) NULL,
	[xueli] [nvarchar](50) NULL,
	[zhuanye] [nvarchar](50) NULL,
	[orginpath] [nvarchar](300) NULL,
	[status] [nvarchar](50) NULL,
	[inCompany] [datetime] NULL,
	[outCompany] [datetime] NULL,
 CONSTRAINT [PK__hr_usersExt__2C3393D0] PRIMARY KEY CLUSTERED 
(
	[extid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[hr_relation]    Script Date: 10/20/2010 05:44:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[hr_relation](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[mgrname] [nvarchar](50) NULL,
	[mgrdisplayname] [nvarchar](50) NULL,
	[mgrdepartment] [nvarchar](50) NULL,
	[secname] [nvarchar](50) NULL,
	[secdisplayname] [nvarchar](50) NULL,
	[secdepartment] [nvarchar](50) NULL,
 CONSTRAINT [PK_hr_relation] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[hr_docs]    Script Date: 10/20/2010 05:44:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[hr_docs](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[samaccount] [nvarchar](50) NULL,
	[history] [image] NULL,
	[filename] [nvarchar](500) NULL,
	[ext] [nvarchar](50) NULL,
	[length] [bigint] NULL,
	[contenttype] [nvarchar](50) NULL,
	[size] [decimal](18, 0) NULL,
	[doctype] [nvarchar](50) NULL,
	[docname] [nvarchar](50) NULL,
	[docno] [nvarchar](50) NULL,
	[createdate] [datetime] NULL,
	[isdelete] [nvarchar](50) NULL,
 CONSTRAINT [PK_hr_history] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[hr_config]    Script Date: 10/20/2010 05:44:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[hr_config](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[name] [nvarchar](50) NULL,
	[types] [nchar](10) NULL,
 CONSTRAINT [PK_hr_config] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Hr_AdUsers2]    Script Date: 10/20/2010 05:44:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Hr_AdUsers2](
	[id] [int] IDENTITY(-1,-1) NOT NULL,
	[SAMAccountname] [nvarchar](100) NOT NULL,
	[FirstName] [nvarchar](100) NULL,
	[LastName] [nvarchar](100) NULL,
	[Initials] [nvarchar](100) NULL,
	[DisplayName] [nvarchar](100) NULL,
	[Description] [nvarchar](500) NULL,
	[Office] [nvarchar](100) NULL,
	[TelephoneNumber] [nvarchar](100) NULL,
	[otherTelephone] [nvarchar](100) NULL,
	[mail] [nvarchar](100) NULL,
	[wwwHomePage] [nvarchar](300) NULL,
	[url] [nvarchar](300) NULL,
	[fax] [nvarchar](100) NULL,
	[ipphone] [nvarchar](100) NULL,
	[UserAccountControl] [nvarchar](100) NULL,
	[AccountExpires] [nvarchar](100) NULL,
	[StreetAddress] [nvarchar](100) NULL,
	[postOfficeBox] [nvarchar](100) NULL,
	[City] [nvarchar](100) NULL,
	[State] [nvarchar](100) NULL,
	[Zip] [nvarchar](100) NULL,
	[Country] [nvarchar](100) NULL,
	[memberOf] [nvarchar](100) NULL,
	[Title] [nvarchar](100) NULL,
	[Department] [nvarchar](100) NULL,
	[Company] [nvarchar](100) NULL,
	[Info] [nvarchar](500) NULL,
	[ADPath] [nvarchar](600) NULL,
 CONSTRAINT [PK__Hr_AdUsers2__2A4B4B5E] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Hr_AdUsers]    Script Date: 10/20/2010 05:44:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Hr_AdUsers](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[SAMAccountname] [nvarchar](100) NOT NULL,
	[FirstName] [nvarchar](100) NULL,
	[LastName] [nvarchar](100) NULL,
	[Initials] [nvarchar](100) NULL,
	[DisplayName] [nvarchar](100) NULL,
	[Description] [nvarchar](500) NULL,
	[Office] [nvarchar](100) NULL,
	[TelephoneNumber] [nvarchar](100) NULL,
	[otherTelephone] [nvarchar](100) NULL,
	[mail] [nvarchar](100) NULL,
	[wwwHomePage] [nvarchar](300) NULL,
	[url] [nvarchar](300) NULL,
	[fax] [nvarchar](100) NULL,
	[ipphone] [nvarchar](100) NULL,
	[UserAccountControl] [nvarchar](100) NULL,
	[AccountExpires] [nvarchar](100) NULL,
	[StreetAddress] [nvarchar](100) NULL,
	[postOfficeBox] [nvarchar](100) NULL,
	[City] [nvarchar](100) NULL,
	[State] [nvarchar](100) NULL,
	[Zip] [nvarchar](100) NULL,
	[Country] [nvarchar](100) NULL,
	[memberOf] [nvarchar](100) NULL,
	[Title] [nvarchar](100) NULL,
	[Department] [nvarchar](100) NULL,
	[Company] [nvarchar](100) NULL,
	[Info] [nvarchar](500) NULL,
	[ADPath] [nvarchar](600) NULL,
 CONSTRAINT [PK__Hr_AdUsers__2A4B4B5E] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  View [dbo].[v_title]    Script Date: 10/20/2010 05:44:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[v_title] AS SELECT * FROM hr_config WHERE types = 'title'
GO
/****** Object:  View [dbo].[v_status]    Script Date: 10/20/2010 05:44:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[v_status] AS SELECT id, name, types FROM dbo.hr_config WHERE (types = 'status')
GO
/****** Object:  View [dbo].[v_office]    Script Date: 10/20/2010 05:44:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[v_office]
AS
SELECT     id, name, types
FROM         dbo.hr_config
WHERE     (types = 'office')
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[40] 4[20] 2[20] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "hr_config"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 106
               Right = 169
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 1440
         Alias = 900
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'v_office'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'v_office'
GO
/****** Object:  View [dbo].[v_jcsz]    Script Date: 10/20/2010 05:44:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[v_jcsz] AS SELECT * FROM hr_config WHERE types =' jcsz '
GO
/****** Object:  View [dbo].[v_idcard]    Script Date: 10/20/2010 05:44:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[v_idcard] AS SELECT * FROM hr_config WHERE types = 'idcard'
GO
/****** Object:  View [dbo].[v_dept]    Script Date: 10/20/2010 05:44:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[v_dept] AS SELECT * FROM hr_config WHERE types = 'dept '
GO
/****** Object:  View [dbo].[v_adusers]    Script Date: 10/20/2010 05:44:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[v_adusers] AS SELECT * FROM hr_adusers UNION SELECT * FROM hr_adusers2
GO
/****** Object:  View [dbo].[v_zysz]    Script Date: 10/20/2010 05:44:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[v_zysz] AS SELECT * FROM hr_config WHERE types = 'zysz'
GO
/****** Object:  View [dbo].[v_xueli]    Script Date: 10/20/2010 05:44:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[v_xueli] AS SELECT * FROM hr_config WHERE types = ' xueli'
GO
/****** Object:  View [dbo].[v_users]    Script Date: 10/20/2010 05:44:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[v_users]
AS
SELECT     TOP (100) PERCENT dbo.v_adusers.id, dbo.v_adusers.SAMAccountname, dbo.v_adusers.FirstName, dbo.v_adusers.LastName, dbo.v_adusers.Initials,
                       dbo.v_adusers.DisplayName, dbo.v_adusers.Description, dbo.v_adusers.Office, dbo.v_adusers.TelephoneNumber, dbo.v_adusers.otherTelephone, 
                      dbo.v_adusers.mail, dbo.v_adusers.wwwHomePage, dbo.v_adusers.url, dbo.v_adusers.fax, dbo.v_adusers.ipphone, 
                      dbo.v_adusers.UserAccountControl, dbo.v_adusers.AccountExpires, dbo.v_adusers.StreetAddress, dbo.v_adusers.postOfficeBox, dbo.v_adusers.City, 
                      dbo.v_adusers.State, dbo.v_adusers.Zip, dbo.v_adusers.Country, dbo.v_adusers.memberOf, dbo.v_adusers.Title, dbo.v_adusers.Department, 
                      dbo.v_adusers.Company, dbo.v_adusers.Info, dbo.v_adusers.ADPath, dbo.hr_usersExt.extid, dbo.hr_usersExt.samaccount, dbo.hr_usersExt.cnname, 
                      dbo.hr_usersExt.sex, dbo.hr_usersExt.avtor, dbo.hr_usersExt.staffcode, dbo.hr_usersExt.Isstaff, dbo.hr_usersExt.issec, dbo.hr_usersExt.job, 
                      dbo.hr_usersExt.ismgr, dbo.hr_usersExt.idcard, dbo.hr_usersExt.cardno, dbo.hr_usersExt.xueli, dbo.hr_usersExt.zhuanye, dbo.hr_usersExt.orginpath, 
                      dbo.hr_usersExt.status, dbo.hr_usersExt.inCompany, dbo.hr_usersExt.outCompany
FROM         dbo.v_adusers LEFT OUTER JOIN
                      dbo.hr_usersExt ON dbo.hr_usersExt.samaccount = dbo.v_adusers.SAMAccountname
WHERE     (dbo.v_adusers.UserAccountControl = 512) OR
                      (dbo.v_adusers.UserAccountControl = 514)
ORDER BY dbo.v_adusers.SAMAccountname
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[40] 4[20] 2[20] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "v_adusers"
            Begin Extent = 
               Top = 6
               Left = 214
               Bottom = 121
               Right = 391
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "hr_usersExt"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 121
               Right = 176
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 1440
         Alias = 900
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'v_users'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'v_users'
GO
/****** Object:  Default [DF_hr_docs_createdate]    Script Date: 10/20/2010 05:44:26 ******/
ALTER TABLE [dbo].[hr_docs] ADD  CONSTRAINT [DF_hr_docs_createdate]  DEFAULT (getdate()) FOR [createdate]
GO
/****** Object:  Default [DF_hr_docs_isdelete]    Script Date: 10/20/2010 05:44:26 ******/
ALTER TABLE [dbo].[hr_docs] ADD  CONSTRAINT [DF_hr_docs_isdelete]  DEFAULT ((0)) FOR [isdelete]
GO
/****** Object:  Default [DF_hr_usersExt_samaccount]    Script Date: 10/20/2010 05:44:26 ******/
ALTER TABLE [dbo].[hr_usersExt] ADD  CONSTRAINT [DF_hr_usersExt_samaccount]  DEFAULT ('') FOR [samaccount]
GO
/****** Object:  Default [DF_hr_usersExt_cnname]    Script Date: 10/20/2010 05:44:26 ******/
ALTER TABLE [dbo].[hr_usersExt] ADD  CONSTRAINT [DF_hr_usersExt_cnname]  DEFAULT ('') FOR [cnname]
GO
/****** Object:  Default [DF_hr_usersExt_sex]    Script Date: 10/20/2010 05:44:26 ******/
ALTER TABLE [dbo].[hr_usersExt] ADD  CONSTRAINT [DF_hr_usersExt_sex]  DEFAULT ('') FOR [sex]
GO
/****** Object:  Default [DF_hr_usersExt_avtor]    Script Date: 10/20/2010 05:44:26 ******/
ALTER TABLE [dbo].[hr_usersExt] ADD  CONSTRAINT [DF_hr_usersExt_avtor]  DEFAULT ('') FOR [avtor]
GO
/****** Object:  Default [DF_hr_usersExt_staffcode]    Script Date: 10/20/2010 05:44:26 ******/
ALTER TABLE [dbo].[hr_usersExt] ADD  CONSTRAINT [DF_hr_usersExt_staffcode]  DEFAULT ('') FOR [staffcode]
GO
/****** Object:  Default [DF_hr_usersExt_Isstaff]    Script Date: 10/20/2010 05:44:26 ******/
ALTER TABLE [dbo].[hr_usersExt] ADD  CONSTRAINT [DF_hr_usersExt_Isstaff]  DEFAULT ((1)) FOR [Isstaff]
GO
