📄 oa.sql
字号:
--
-- insert DoucumentFolder values('00050001','生产部规章制度说明11',0001,0,'2006-01-01',0,0001)
-- insert DoucumentFolder values('00050002','生产部章制度说明111',0001,0,'2006-01-01',0,0001)
--
--
-- insert DoucumentFolder values('0006','行政部文档',0001,1,'2006-01-01',0,0001)
--
-- insert DoucumentFolder values('00060001','行政部规章制度说明11',0001,0,'2006-01-01',0,0001)
-- insert DoucumentFolder values('00060002','行政部章制度说明111',0001,0,'2006-01-01',0,0001)
--
-- select * from DoucumentFolder where len([id])=4
-- select * from DoucumentFolder where id like '____'
-- select * from DoucumentFolder where id like '0001%'
-- select * from DoucumentFolder where ownerFolder=0001
-- select * from DoucumentFolder where ownerFolder=00010001
-- update DoucumentFolder set state=0,editeId=2 where id=0001
-- update DoucumentFolder set state=0 where id=000100010001
-- update DoucumentFolder set state=0 where id=00020001
--delete from DoucumentFolder
--drop table DoucumentEdition
--文档历史版本表
create table DoucumentEdition
(
[id] int identity(1000,1) not null constraint pk_DoucumentEdition_id primary key, --主键 文档版本编号
doucumentFolder varchar(40) constraint fk_DoucumentEdition_DoucumentFolder_id foreign key references DoucumentFolder([id]),----文档的编号 引用文件夹文档表的编号（外键）
editDate Smalldatetime, --文档编辑时间
editeId int, --外键,此次文档的编辑人编号
context text,--文档正文
accessories Varchar(500),--附件的编号（用“，”号将多个附件编号拼起来）
newly bit --是否最新版本
) on G_change textimage_on G_Test
--建立文档编辑人与员工表的关联
alter table DoucumentEdition
add constraint fk_DoucumentEdition_Employee_editeId foreign key(editeId) references Employee(empId)
go
-- select * from DoucumentEdition where doucumentFolder='000100010001' order by editDate desc
--select * from DoucumentEdition where editDate='2006-01-05'
-- delete from DoucumentEdition
-- insert DoucumentEdition values('0001','2006-01-01',2,'','',1)
-- insert DoucumentEdition values('0002','2006-01-01',2,'','',1)
--insert into DoucumentEdition (editDate, accessories, context, newly, doucumentFolder) values ('2006-01-01','','',1,'0001')
-- insert DoucumentEdition values('00010001','2006-01-01',2,'','',1)
-- insert DoucumentEdition values('00010002','2006-01-01',2,'','',1)
--
-- insert DoucumentEdition values('00020001','2006-01-01',2,'','',1)
-- insert DoucumentEdition values('00020002','2006-01-01',2,'','',1)
-- 测试一
--insert DoucumentEdition values('000100010001',getdate(),2,'oidfugosgldgojgldlgdlgl','1,2',1)
-- insert DoucumentEdition values('000100020001','2006-01-06',2,'saljhgsudhgkldhfgifjhdifgh','1,2',1)
--insert DoucumentEdition values('000100010001',getdate(),2,'门好啊你','1,2',0)
-- insert DoucumentEdition values('000100020001','2006-01-02',2,'saljhgsudhgkldhfgifjhdifgh','1,2',0)
-- insert DoucumentEdition values('000100010001','2006-01-03',2,'oidfugosgldgojgldlgdlgl','1,2',0)
-- insert DoucumentEdition values('000100020001','2006-01-02',2,'saljhgsudhgkldhfgifjhdifgh','1,2',0)
-- --
-- insert DoucumentEdition values('00050001','2006-01-01',2,'lksfjgosjgkldfoglkgo','',1)
-- insert DoucumentEdition values('00050002','2006-01-01',2,'oifgofglfgojfdljdlfjl','',1)
--
-- insert DoucumentEdition values('0006','2006-01-01',2,'oidfugosgldgojgldlgdlgl','',1)
-- insert DoucumentEdition values('0006','2006-01-01',2,'saljhgsudhgkldhfgifjhdifgh','',1)
--
-- insert DoucumentEdition values('00060001','2006-01-01',2,'lksfjgaaosjgkldfoglkgo','',1)
-- insert DoucumentEdition values('00060002','2006-01-01',2,'fasdfsdafs','',1)
-- insert DoucumentEdition values('000600010001','2006-01-01',2,'fasdfsdafs','',1)
-- insert DoucumentEdition values('000600010002','2006-01-01',2,'fasdfsdafs','',1)
-- insert DoucumentEdition values('00030001','2006-01-01',0001,'sd;fusodifjiof vodjgoid ','',1)
-- insert DoucumentEdition values('00030002','2006-01-01',0001,'dlfigudoifugo9dufoidjfodog','',1)
--
-- insert DoucumentEdition values('00040001','2006-01-01',0001,'df;godpfgo9d odjiofhoidfjohiftiohu','',1)
-- insert DoucumentEdition values('00040002','2006-01-01',0001,'sdflgsodfgodfugiosdfgoidsfiugihbvhbihbihib','',1)
--
--
-- insert DoucumentEdition values('00050001','2006-01-01',0001,'dfpgoudofgjo fguhoidfjhoigfuhojfoh','',1)
-- insert DoucumentEdition values('00050002','2006-01-01',0001,'fgihuohpofhdfjhojfhojfgohjfoghjofjhf','',1)
--
--
-- insert DoucumentEdition values('00060001','2006-01-01',0001,'fgohiufoghjfoghjofjhofjhofjh','',1)
-- insert DoucumentEdition values('00060002','2006-01-01',0001,'dfihsdklfglkdghksdfhgkhdgkjdhjkghd','',1)
-- delete DoucumentEdition where doucumentFolder like '0001%'
--
-- select * from DoucumentEdition where doucumentFolder like '000100020007'
--000600010002
-- select * from DoucumentEdition where doucumentFolder=0001 order by substring(doucumentFolder,)editDate desc
create table Accessories --附件表
(
[id] Varchar(32) not null constraint pk_Accessories_id primary key ,-- 主键 附件的编号(GUID)
[size] int, --大小
[name] Varchar(250),--附件名
createDate smalldatetime --附件的上传时间
) on G_change
go
--select * from Accessories
-- insert into Accessories values('1',10,'abc.txt',getdate())
-- insert into Accessories values('2',10,'abc.txt',getdate())
-- insert into Accessories values('3',10,'abc.txt',getdate())
create table Floadpurview --权限表
(
[id] int identity(1000,1) not null primary key,--主键 权限编号
doucumentFile varchar(40) constraint fk_Floadpurview_DoucumentFolder_id foreign key references DoucumentFolder([id]),--文件的编号 引用文件夹表的编号 外键
userId int, --外键 用户编号 引用员工表
departmentId varchar(100),--外键 部门编号 引用部门表
type tinyint --权限的类型(如：只读3，修改2，所有者1)
) on G_owner
alter table Floadpurview
add constraint fk_Floadpurview_Employee_userId foreign key(userId) references Employee(empId)
--建立用户与员工表的关联
alter table Floadpurview
add constraint fk_Floadpurview_Department_departmentId foreign key(departmentId) references Department(depId)
--建立部门与部门表的关联
--distinct(f.id)
--delete from Floadpurview
-- select * from Floadpurview f where
-- inner join DoucumentFolder d on f.doucumentFile=d.id
-- where (userid=1 or departmentId in ('0001') ) and d.typeDocument=1
--
-- -------------------
-- select * from Floadpurview
-- where (userid=2 or departmentId in ('0001','0002'))
-- and doucumentFile like'0001____'
--
--
-- ----------------------------
-- select * from Floadpurview where departmentId in ('0001') and type= 1
---注意。。有部门编号就没员工编号，反之也一样（用一个没用的部门字段替代，员工随便起一个）
--用户2 拥有权
--
--
-- insert Floadpurview values('0001',1,null,1)--文件的编号,用户编号 ,部门编号,权限的类型
-- insert Floadpurview values('00010001',1,null,1)
-- insert Floadpurview values('000100010001',1,null,1)
-- insert Floadpurview values('000100010002',1,null,1)
--
-- insert Floadpurview values('00010002',1,null,1)
-- insert Floadpurview values('000100020001',1,null,1)
-- insert Floadpurview values('000100020002',1,null,1)
--
-- insert Floadpurview values('0002',2,null,1)
-- insert Floadpurview values('00020001',2,null,1)
-- insert Floadpurview values('00020002',2,null,1)
--
--
-- insert Floadpurview values('0003',1,null,1)
-- insert Floadpurview values('00030001',1,null,1)
-- insert Floadpurview values('00030002',1,null,1)
--
-- insert Floadpurview values('0004',1,null,1)
--
-- insert Floadpurview values('00040001',1,null,1)
-- insert Floadpurview values('00040002',1,null,1)
--
-- insert Floadpurview values('0005',1,null,1)
--
-- insert Floadpurview values('00050001',1,null,1)
-- insert Floadpurview values('00050002',1,null,1)
-- --用户3 拥有权
-- insert Floadpurview values('0002',3,null,1)
-- insert Floadpurview values('00020001',3,null,1)
-- insert Floadpurview values('00020002',3,null,1)
--
-- --用户4 编辑权利
-- --delete Floadpurview where type=2
-- insert Floadpurview values('0004',4,null,2)
--
-- insert Floadpurview values('00040001',4,null,2)
-- insert Floadpurview values('00040002',4,null,2)
--
-- --用户4 阅读权利
-- --select * from Floadpurview where userId=3 or departmentId in (0003)
-- insert Floadpurview values('0004',2,null,3)
--
-- insert Floadpurview values('00040001',2,null,3)
-- insert Floadpurview values('00040002',2,null,3)
--
-- -----部门情况
-- --部门一
-- insert Floadpurview values('0002',null,'0001',1)
-- insert Floadpurview values('00020001',null,'0001',1)
-- insert Floadpurview values('00020002',null,'0001',1)
--
-- --部门二
-- --delete Floadpurview where type=2
-- insert Floadpurview values('0004',null,'0002',2)
--
-- insert Floadpurview values('00040001',null,'0002',2)
-- insert Floadpurview values('00040002',null,'0002',2)
--
-- --用户4 阅读权利
-- --select * from Floadpurview where userId=null
insert Floadpurview values('0004',null,'0003',3)
insert Floadpurview values('00040001',null,'0003',3)
insert Floadpurview values('00040002',null,'0003',3)
insert Floadpurview values('0004',null,'0003',2)
insert Floadpurview values('00040001',null,'0003',2)
insert Floadpurview values('00040002',null,'0003',2)
go
create table Manage --公文
(
offID int identity(1,1) not null primary key, --公文编号
OffModify varchar(16) not null, --公文主题
offType varchar(10) not null, --公文类型
grade bit, --紧急程度
tate bit, --状态
sendID int , --发送人名字
addreseID int , --收件人名字
copynameID int , --抄送人名字
offDate datetime, --发送日期
finishDate datetime, --归档日期
content text --内容
)on G_owner textimage_on G_Test
go
alter table Manage
add constraint fk_Manage_Employee_sendID foreign key(sendID) references Employee(empId)
--建立发送人与员工表的关联
alter table Manage
add constraint fk_Manage_Employee_addreseID foreign key(addreseID) references Employee(empId)
--建立收件人与员工表的关联
alter table Manage
add constraint fk_Manage_Employee_copynameID foreign key(copynameID) references Employee(empId)
--建立抄送人与员工表的关联
create table OffMove --公文流转
(
MoveID int identity(1,1) not null primary key, --流转编号
manage int not null references manage(offID), --公文编号
accessories Varchar(32), --references Accessories([id]), -- 关联到公文管理表的附件编号
moveName varchar(10), --公文上个发起名字
spID int , --处理人
content text, --内容
receiveDate datetime, --收到日期
finishdate datetime, --处理日期
tate bit --处理状态
) on G_change textimage_on G_Test
--建立抄送人与员工表的关联
alter table OffMove
add constraint fk_OffMove_Employee_spID foreign key(spID) references Employee(empId)
go
create table Copy --抄送表
(
copyID int identity(1,1) not null primary key, --抄送编号
manage int not null references manage(offID), --公文编号
offmove int not null references offmove(moveID),--流转编号
copynameID int --抄送人名字ID
)on G_change
alter table Copy
add constraint fk_Copy_Employee_copynameID foreign key(copynameID) references Employee(empId)
--建立抄送人与员工表的关联
--办工用品申请
--审请物品表
CREATE TABLE [dbo].[AppleStore] (
[id] [int] IDENTITY (1, 1) NOT NULL ,--审请物品编号 主键
[storeName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL , --用品名
[storeType] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,--用品编号类型
[unit] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,--数量单位
[appleNum] [int] NULL ,--申请数量
[actuaNum] [int] NULL , --实际领取数量
[officeRes] [int] NOT NULL --办公用品单号 与 officeRes的id号外键关联
) on G_change
GO
--办公用品申请表
CREATE TABLE [dbo].[OfficeRes] (
[id] [int] IDENTITY (1, 1) NOT NULL , --办公用品申请表编号 主键
[appleMan] [int] NULL , --申请人 员工表外键
[auditMan] [int] NULL , --部门审核人 员工表外键
[storeMan] [int] NULL ,--仓库经办人 员工表外键
[receiveMan] [int] NULL ,--领用人 员工表外键
[state] [bit] NULL ,--申请单的状态
[appleTime] [datetime] NULL ,--审请时间
[remark] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL , --备注
[version] [int] NOT NULL --版本号
)on G_change
GO
ALTER TABLE [dbo].[AppleStore] ADD
CONSTRAINT [PK_AppleStore] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[OfficeRes] ADD
CONSTRAINT [PK_OfficeRes] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[AppleStore] ADD
CONSTRAINT [FK_AppleStore_OfficeRes] FOREIGN KEY
(
[officeRes]
) REFERENCES [dbo].[OfficeRes] (
[id]
)
GO
alter table OfficeRes
add constraint fk_OfficeRes_Employee_appleMan foreign key(appleMan) references Employee(empId)
--建立申请人与员工表的关联
alter table OfficeRes
add constraint fk_OfficeRes_Employee_auditMan foreign key(auditMan) references Employee(empId)
--建立部门审核人与员工表的关联
alter table OfficeRes
add constraint fk_OfficeRes_Employee_storeMan foreign key(storeMan) references Employee(empId)
--建立仓库经办人与员工表的关联
alter table OfficeRes
add constraint fk_OfficeRes_Employee_receiveMan foreign key(receiveMan) references Employee(empId)
--建立领用人与员工表的关联
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -