outstate.sql

一套三层结构的文件管理OA系统源码

-- SQL Manager 2005 Lite for SQL Server (2.6.0.1)
-- ---------------------------------------
-- Host      : VV
-- Database  : OutState
-- Version   : Microsoft SQL Server  8.00.194


CREATE DATABASE [OutState]
COLLATE Chinese_PRC_CI_AS
GO

USE [OutState]
GO

--
-- Definition for table Comm_db_Company : 
--

CREATE TABLE [dbo].[Comm_db_Company] (
  [CompanyNum]int IDENTITY(1, 1) NOT NULL,
  [CompanyCode]varchar(10) COLLATE Chinese_PRC_CI_AS NULL,
  [CompanyName]varchar(30) COLLATE Chinese_PRC_CI_AS NULL
)
ON [PRIMARY]
GO

--
-- Definition for table Comm_db_Dept : 
--

CREATE TABLE [dbo].[Comm_db_Dept] (
  [DeptNum]int IDENTITY(1, 1) NOT NULL,
  [DeptName]varchar(30) COLLATE Chinese_PRC_CI_AS NULL
)
ON [PRIMARY]
GO

--
-- Definition for table Comm_db_Menu : 
--

CREATE TABLE [dbo].[Comm_db_Menu] (
  [ItemNo]varchar(20) COLLATE Chinese_PRC_CI_AS NOT NULL,
  [MenuCode]varchar(20) COLLATE Chinese_PRC_CI_AS NULL,
  [MenuName]varchar(30) COLLATE Chinese_PRC_CI_AS NULL
)
ON [PRIMARY]
GO

--
-- Definition for table Comm_db_User : 
--

CREATE TABLE [dbo].[Comm_db_User] (
  [UserID]varchar(20) COLLATE Chinese_PRC_CI_AS NOT NULL,
  [UserName]varchar(20) COLLATE Chinese_PRC_CI_AS NULL,
  [Psw]varchar(20) COLLATE Chinese_PRC_CI_AS NULL,
  [UserClass]varchar(10) COLLATE Chinese_PRC_CI_AS NULL,
  [EMail]varchar(40) COLLATE Chinese_PRC_CI_AS NULL,
  [DeptName]varchar(30) COLLATE Chinese_PRC_CI_AS NULL,
  [OnLine]bit CONSTRAINT [DF_Comm_db_User_IsWork] DEFAULT 0 NULL,
  [UseState]bit CONSTRAINT [DF_Comm_db_User_UseState] DEFAULT 1 NULL,
  [AtOut]bit CONSTRAINT [DF_Comm_db_User_AtOut] DEFAULT 0 NULL
)
ON [PRIMARY]
GO

EXEC sp_addextendedproperty 'MS_Description', N'是否在线（0：不在，1：在线）', 'user', 'dbo', 'table', 'Comm_db_User', 'column', 'OnLine'
GO

EXEC sp_addextendedproperty 'MS_Description', N'该用户是否取消（0：取消；1：使用）', 'user', 'dbo', 'table', 'Comm_db_User', 'column', 'UseState'
GO

EXEC sp_addextendedproperty 'MS_Description', N'是否在在外面 0:不在；1：在外面', 'user', 'dbo', 'table', 'Comm_db_User', 'column', 'AtOut'
GO

--
-- Definition for table Comm_db_UserGrant_Menu : 
--

CREATE TABLE [dbo].[Comm_db_UserGrant_Menu] (
  [UserID]varchar(20) COLLATE Chinese_PRC_CI_AS NOT NULL,
  [MenuCode]varchar(30) COLLATE Chinese_PRC_CI_AS NOT NULL
)
ON [PRIMARY]
GO

--
-- Definition for table File_db_Condition_Body : 
--

CREATE TABLE [dbo].[File_db_Condition_Body] (
  [ISO_Docnum]varchar(30) COLLATE Chinese_PRC_CI_AS NOT NULL,
  [Item]int NOT NULL,
  [StateOK]varchar(20) COLLATE Chinese_PRC_CI_AS NULL,
  [StateNO]varchar(20) COLLATE Chinese_PRC_CI_AS NULL,
  [OkGoTo]int NULL,
  [NoGoTo]int NULL,
  [State1]varchar(20) COLLATE Chinese_PRC_CI_AS NULL,
  [State2]varchar(20) COLLATE Chinese_PRC_CI_AS NULL,
  [State3]varchar(20) COLLATE Chinese_PRC_CI_AS NULL,
  [OkMan]int CONSTRAINT [DF_File_db_Condition_Body_OkMan] DEFAULT 1 NULL,
  [Remark]varchar(200) COLLATE Chinese_PRC_CI_AS NULL
)
ON [PRIMARY]
GO

EXEC sp_addextendedproperty 'MS_Description', N'审核OK时的状态', 'user', 'dbo', 'table', 'File_db_Condition_Body', 'column', 'StateOK'
GO

EXEC sp_addextendedproperty 'MS_Description', N'不审核No时的状态', 'user', 'dbo', 'table', 'File_db_Condition_Body', 'column', 'StateNO'
GO

EXEC sp_addextendedproperty 'MS_Description', N'至少人数（要几个同意后才可跳转）', 'user', 'dbo', 'table', 'File_db_Condition_Body', 'column', 'OkMan'
GO

--
-- Definition for table File_db_Condition_Relation : 
--

CREATE TABLE [dbo].[File_db_Condition_Relation] (
  [ISO_Docnum]varchar(30) COLLATE Chinese_PRC_CI_AS NOT NULL,
  [Item]int NOT NULL,
  [FromUserID]varchar(20) COLLATE Chinese_PRC_CI_AS NOT NULL,
  [ToUserID]varchar(20) COLLATE Chinese_PRC_CI_AS NOT NULL,
  [StoreUserID]varchar(20) COLLATE Chinese_PRC_CI_AS NULL
)
ON [PRIMARY]
GO

--
-- Definition for table File_db_Doc_OtherFile : 
--

CREATE TABLE [dbo].[File_db_Doc_OtherFile] (
  [ISO_Docnum]varchar(30) COLLATE Chinese_PRC_CI_AS NOT NULL,
  [DocnumNo]varchar(30) COLLATE Chinese_PRC_CI_AS NOT NULL,
  [Doc_Title]varchar(100) COLLATE Chinese_PRC_CI_AS NOT NULL,
  [Doc_ReMark]varchar(200) COLLATE Chinese_PRC_CI_AS NULL,
  [CreateUser]varchar(20) COLLATE Chinese_PRC_CI_AS NOT NULL,
  [CreateDate]smalldatetime CONSTRAINT [DF_File_Doc_OtherFile_CreateDate] DEFAULT getdate() NOT NULL
)
ON [PRIMARY]
GO

--
-- Definition for table File_db_Doc_QingJia : 
--

CREATE TABLE [dbo].[File_db_Doc_QingJia] (
  [ISO_Docnum]varchar(30) COLLATE Chinese_PRC_CI_AS NOT NULL,
  [DocnumNo]varchar(30) COLLATE Chinese_PRC_CI_AS NOT NULL,
  [Doc_Title]varchar(30) COLLATE Chinese_PRC_CI_AS NOT NULL,
  [Doc_ReMark]varchar(100) COLLATE Chinese_PRC_CI_AS NULL,
  [CreateUser]varchar(20) COLLATE Chinese_PRC_CI_AS NOT NULL,
  [CreateDate]smalldatetime CONSTRAINT [DF_File_Doc_QingJia_CreateDate] DEFAULT getdate() NOT NULL
)
ON [PRIMARY]
GO

--
-- Definition for table File_db_Docnum_Type : 
--

CREATE TABLE [dbo].[File_db_Docnum_Type] (
  [ISO_Docnum]varchar(30) COLLATE Chinese_PRC_CI_AS NOT NULL,
  [DocTitle]varchar(30) COLLATE Chinese_PRC_CI_AS NOT NULL,
  [CustomTable]bit CONSTRAINT [DF_File_db_Docnum_Type_CustomTable] DEFAULT 0 NOT NULL,
  [IsWork]bit CONSTRAINT [DF_File_db_Docnum_Type_IsWork] DEFAULT 1 NOT NULL,
  [ReMark]varchar(100) COLLATE Chinese_PRC_CI_AS NULL
)
ON [PRIMARY]
GO

EXEC sp_addextendedproperty 'MS_Description', N'是否再使用该工作流  0：不用；1：在用', 'user', 'dbo', 'table', 'File_db_Docnum_Type', 'column', 'IsWork'
GO

--
-- Definition for table File_db_Relation : 
--

CREATE TABLE [dbo].[File_db_Relation] (
  [RelationID]int IDENTITY(1, 1) NOT NULL,
  [RelationName]varchar(30) COLLATE Chinese_PRC_CI_AS NOT NULL,
  [Remark]varchar(100) COLLATE Chinese_PRC_CI_AS NULL
)
ON [PRIMARY]
GO

--
-- Definition for table File_db_WorkFlow_All : 
--

CREATE TABLE [dbo].[File_db_WorkFlow_All] (
  [ISO_Docnum]varchar(30) COLLATE Chinese_PRC_CI_AS NOT NULL,
  [DocnumNo]varchar(30) COLLATE Chinese_PRC_CI_AS NOT NULL,
  [Item]int NOT NULL,
  [ToUserID]varchar(20) COLLATE Chinese_PRC_CI_AS NOT NULL,
  [FromUserID]varchar(20) COLLATE Chinese_PRC_CI_AS NOT NULL,
  [MyResult]varchar(20) COLLATE Chinese_PRC_CI_AS NULL,
  [StateOK]varchar(20) COLLATE Chinese_PRC_CI_AS NULL,
  [StateNo]varchar(20) COLLATE Chinese_PRC_CI_AS NULL,
  [OkGoTo]int NULL,
  [NoGoTo]int NULL,
  [OkMan]int CONSTRAINT [DF_File_db_WorkFlow_All_OkMan] DEFAULT 1 NULL,
  [IsWork]bit CONSTRAINT [DF_File_db_WorkFlow_All_IsWork] DEFAULT 0 NOT NULL,
  [MyReason]varchar(100) COLLATE Chinese_PRC_CI_AS NULL,
  [CreateDate]smalldatetime CONSTRAINT [DF_File_db_WorkFlow_All_CreateDate] DEFAULT getdate() NOT NULL
)
ON [PRIMARY]
GO

EXEC sp_addextendedproperty 'MS_Description', N'需哪个用户审核', 'user', 'dbo', 'table', 'File_db_WorkFlow_All', 'column', 'ToUserID'
GO

EXEC sp_addextendedproperty 'MS_Description', N'从哪个用户发出', 'user', 'dbo', 'table', 'File_db_WorkFlow_All', 'column', 'FromUserID'
GO

EXEC sp_addextendedproperty 'MS_Description', N'是否要操作--0：不用；1：要', 'user', 'dbo', 'table', 'File_db_WorkFlow_All', 'column', 'IsWork'
GO

--
-- Definition for table Out_db_Clock : 
--

CREATE TABLE [dbo].[Out_db_Clock] (
  [ClockID]int NOT NULL,
  [ClockAddr]varchar(30) COLLATE Chinese_PRC_CI_AS NULL,
  [DoorState]bit NOT NULL
)
ON [PRIMARY]
GO

--
-- Definition for table Out_db_Company : 
--

CREATE TABLE [dbo].[Out_db_Company] (
  [CompanyNum]int IDENTITY(1, 1) NOT NULL,
  [CompanyCode]varchar(10) COLLATE Chinese_PRC_CI_AS NULL,
  [CompanyName]varchar(30) COLLATE Chinese_PRC_CI_AS NULL
)
ON [PRIMARY]
GO

--
-- Definition for table Out_db_CompanyTelephone : 
--

CREATE TABLE [dbo].[Out_db_CompanyTelephone] (
  [SEQ]int IDENTITY(1, 1) NOT NULL,
  [CompanyCode]varchar(10) COLLATE Chinese_PRC_CI_AS NOT NULL,
  [DeptName]varchar(30) COLLATE Chinese_PRC_CI_AS NOT NULL,
  [PSNName]varchar(30) COLLATE Chinese_PRC_CI_AS NULL,
  [CompanyTel]varchar(30) COLLATE Chinese_PRC_CI_AS NOT NULL,
  [PSNTel]varchar(40) COLLATE Chinese_PRC_CI_AS NULL
)
ON [PRIMARY]
GO

--
-- Definition for table Out_db_Day_Input : 
--

CREATE TABLE [dbo].[Out_db_Day_Input] (
  [CompanyCode]varchar(10) COLLATE Chinese_PRC_CI_AS NOT NULL,
  [AbnDate]smalldatetime NOT NULL,
  [gno]int NOT NULL,
  [T1]int NOT NULL,
  [T2]int NULL,
  [CardNo]varchar(20) COLLATE Chinese_PRC_CI_AS NULL,
  [StateName]varchar(10) COLLATE Chinese_PRC_CI_AS NULL,
  [Remark]varchar(50) COLLATE Chinese_PRC_CI_AS NULL,
  [CreateDate]smalldatetime NULL,
  [CreateUser]varchar(20) COLLATE Chinese_PRC_CI_AS NULL,
  [IsDel]bit CONSTRAINT [DF__Out_db_Da__IsDel__656C112C] DEFAULT 0 NOT NULL
)
ON [PRIMARY]
GO

--
-- Definition for table Out_db_Day_Read : 
--

CREATE TABLE [dbo].[Out_db_Day_Read] (
  [ClockData]varchar(50) COLLATE Chinese_PRC_CI_AS NOT NULL,
  [CompanyCode]varchar(10) COLLATE Chinese_PRC_CI_AS NULL,
  [Gno]int NULL,
  [ClockID]int NULL,
  [AbnDate]smalldatetime NULL,
  [AbnTime]int NULL,
  [CreateDate]smalldatetime CONSTRAINT [DF__Out_db_Da__Creat__68487DD7] DEFAULT getdate() NULL
)
ON [PRIMARY]
GO

--
-- Definition for table Out_db_Dept : 
--

CREATE TABLE [dbo].[Out_db_Dept] (
  [DeptNum]int IDENTITY(1, 1) NOT NULL,
  [DeptName]varchar(30) COLLATE Chinese_PRC_CI_AS NULL
)
ON [PRIMARY]
GO

--
-- Definition for table Out_db_Menu : 
--

CREATE TABLE [dbo].[Out_db_Menu] (
  [ItemNo]varchar(10) COLLATE Chinese_PRC_CI_AS NOT NULL,
  [MenuCode]varchar(20) COLLATE Chinese_PRC_CI_AS NULL,
  [MenuName]varchar(30) COLLATE Chinese_PRC_CI_AS NULL
)
ON [PRIMARY]
GO

--
-- Definition for table Out_db_Personal : 
--

CREATE TABLE [dbo].[Out_db_Personal] (
  [CompanyCode]varchar(10) COLLATE Chinese_PRC_CI_AS NOT NULL,
  [gno]int NOT NULL,
  [PsnName]varchar(20) COLLATE Chinese_PRC_CI_AS NULL,
  [PsnDept]varchar(20) COLLATE Chinese_PRC_CI_AS NULL,
  [PsnPosi]varchar(30) COLLATE Chinese_PRC_CI_AS NULL,
  [PsnSex]varchar(4) COLLATE Chinese_PRC_CI_AS NULL,
  [CardNo]varchar(20) COLLATE Chinese_PRC_CI_AS NULL
)
ON [PRIMARY]
GO

--
-- Definition for table Out_db_State : 
--

CREATE TABLE [dbo].[Out_db_State] (
  [StateCode]int NOT NULL,
  [StateName]varchar(10) COLLATE Chinese_PRC_CI_AS NULL
)
ON [PRIMARY]
GO

--
-- Definition for table Out_db_User : 
--

CREATE TABLE [dbo].[Out_db_User] (
  [UserID]varchar(20) COLLATE Chinese_PRC_CI_AS NOT NULL,
  [UserName]varchar(20) COLLATE Chinese_PRC_CI_AS NULL,
  [Psw]varchar(20) COLLATE Chinese_PRC_CI_AS NULL,
  [UserType]varchar(10) COLLATE Chinese_PRC_CI_AS NULL
)
ON [PRIMARY]
GO

--
-- Definition for table Out_db_UserGrant_Company : 
--

CREATE TABLE [dbo].[Out_db_UserGrant_Company] (
  [UserID]varchar(20) COLLATE Chinese_PRC_CI_AS NOT NULL,
  [Company]varchar(30) COLLATE Chinese_PRC_CI_AS NOT NULL
)
ON [PRIMARY]
GO

--
-- Definition for table Out_db_UserGrant_Dept : 
--

CREATE TABLE [dbo].[Out_db_UserGrant_Dept] (
  [UserID]varchar(20) COLLATE Chinese_PRC_CI_AS NOT NULL,
  [DeptName]varchar(30) COLLATE Chinese_PRC_CI_AS NOT NULL
)
ON [PRIMARY]
GO

--
-- Definition for table Out_db_UserGrant_Menu : 
--

CREATE TABLE [dbo].[Out_db_UserGrant_Menu] (
  [UserID]varchar(10) COLLATE Chinese_PRC_CI_AS NOT NULL,
  [MenuCode]varchar(20) COLLATE Chinese_PRC_CI_AS NOT NULL
)
ON [PRIMARY]
GO

--
-- Definition for table Pic_db_Folder : 
--

CREATE TABLE [dbo].[Pic_db_Folder] (
  [UserID]varchar(20) COLLATE Chinese_PRC_CI_AS NOT NULL,
  [ParentDir]varchar(500) COLLATE Chinese_PRC_CI_AS NOT NULL,
  [FileName]varchar(100) COLLATE Chinese_PRC_CI_AS NOT NULL,
  [FileType]varchar(20) COLLATE Chinese_PRC_CI_AS NOT NULL,
  [ReMark]varchar(100) COLLATE Chinese_PRC_CI_AS NULL,
  [FileID]int IDENTITY(1, 1) NOT NULL,
  [FilePath] AS ([UserID] + '_' + convert(varchar(10),[FileID]) + reverse(substring(reverse([filename]),1,charindex('.',reverse([filename]))))),
  [CreateUser]varchar(20) COLLATE Chinese_PRC_CI_AS NULL,
  [CreateDate]smalldatetime NULL,
  [UpdateUser]varchar(20) COLLATE Chinese_PRC_CI_AS NULL,
  [UpdateDate]smalldatetime NULL
)
ON [PRIMARY]
GO

--
-- Definition for table Pic_db_Folder_Backup : 
--

CREATE TABLE [dbo].[Pic_db_Folder_Backup] (
  [UserID]varchar(20) COLLATE Chinese_PRC_CI_AS NULL,
  [ParentDir]varchar(500) COLLATE Chinese_PRC_CI_AS NULL,
  [FileName]varchar(100) COLLATE Chinese_PRC_CI_AS NULL,
  [FileType]varchar(20) COLLATE Chinese_PRC_CI_AS NULL,
  [ReMark]varchar(100) COLLATE Chinese_PRC_CI_AS NULL,
  [FileID]int NULL,
  [FilePath]varchar(131) COLLATE Chinese_PRC_CI_AS NULL,
  [CreateUser]varchar(20) COLLATE Chinese_PRC_CI_AS NULL,
  [CreateDate]smalldatetime NULL,
  [UpdateUser]varchar(20) COLLATE Chinese_PRC_CI_AS NULL,
  [UpdateDate]smalldatetime NULL,
  [DelDate]smalldatetime NULL
)
ON [PRIMARY]
GO

--
-- Definition for table Pic_db_Folder_Body : 
--

CREATE TABLE [dbo].[Pic_db_Folder_Body] (
  [FileID]int NOT NULL,
  [item]int NOT NULL,
  [PicType]varchar(20) COLLATE Chinese_PRC_CI_AS NULL,
  [PicTop]float NULL,
  [PicLeft]float NULL,
  [PicWidth]float NULL,
  [PicHeight]float NULL,
  [Remark]varchar(400) COLLATE Chinese_PRC_CI_AS NULL
)