执行审核上则是每处理一个环节,就插入一个环节的信息.
我个人认为实现审核的代码使用存储过程去实现要比用C#代码去实现要好,至于用事务处理感觉还是差点,因为如果一个系统的事务处理过多,会造成处理缓慢,而用C#代码去实现,则需要进行多次的查询数据库,操作过程也比较复杂..所以我觉得还是用存储过程去实现是最理想的.
1
Create
procedure
ExecFlowAuditPass
2 (
3 @AuditMainID int ,
4 @UserID varchar ( 100 ),
5 @AuditOpinion ntext ,
6 @Auditstatus int
7 )
8 As
9 -- 获取最后审核的节点
10 declare @posid int
11 select @posid = max (Sequence) from 审核子表 where MainID = @AuditMainID
12 -- 获取最大审核的节点
13 declare @maxposid int
14 select @maxposid = max (Sequence) from 流程子表 where MainID = @AuditMainID
15 -- 获取将审核节点角色资料
16 declare @UserGroupID int
17 select @UserGroupID = UserGroupID from 审核子表 where MainID = @AuditMainID and Sequence = @posid + 1
18 -- 角色不符
19 if ( select count ( * ) from 员工表 where 员工ID = @UserID and 角色ID = @UserGroupID ) = 0
20 begin
21 select * from 审核子表 where MainID = @AuditMainID
22 print - 1 ;
23 return - 1 ;
24 end
25 -- 角色符合执行
26 if @Auditstatus = 1
27 begin
28 insert into 流程子表(MainID,AuditOpinion,Auditstatus,Sequence,AuditID,CreateDate,角色ID) values ( @AuditMainID , @AuditOpinion , 1 , @posid + 1 , @UserID , getdate (), @UserGroupID )
29 if @posid + 1 = @maxposid
30 begin
31 update 审核主表 set Auditstatus = 3 where [ ID ] = @AuditMainID
32 end
33 end
34 else
35 begin
36 insert into 流程子表(MainID,AuditOpinion,Auditstatus,Sequence,AuditID,CreateDate,角色ID) values ( @AuditMainID , @AuditOpinion , 0 , @posid + 1 , @UserID , getdate (), @UserGroupID )
37 update 审核主表 set Auditstatus = 2 where [ ID ] = @AuditMainID
38 end
2 (
3 @AuditMainID int ,
4 @UserID varchar ( 100 ),
5 @AuditOpinion ntext ,
6 @Auditstatus int
7 )
8 As
9 -- 获取最后审核的节点
10 declare @posid int
11 select @posid = max (Sequence) from 审核子表 where MainID = @AuditMainID
12 -- 获取最大审核的节点
13 declare @maxposid int
14 select @maxposid = max (Sequence) from 流程子表 where MainID = @AuditMainID
15 -- 获取将审核节点角色资料
16 declare @UserGroupID int
17 select @UserGroupID = UserGroupID from 审核子表 where MainID = @AuditMainID and Sequence = @posid + 1
18 -- 角色不符
19 if ( select count ( * ) from 员工表 where 员工ID = @UserID and 角色ID = @UserGroupID ) = 0
20 begin
21 select * from 审核子表 where MainID = @AuditMainID
22 print - 1 ;
23 return - 1 ;
24 end
25 -- 角色符合执行
26 if @Auditstatus = 1
27 begin
28 insert into 流程子表(MainID,AuditOpinion,Auditstatus,Sequence,AuditID,CreateDate,角色ID) values ( @AuditMainID , @AuditOpinion , 1 , @posid + 1 , @UserID , getdate (), @UserGroupID )
29 if @posid + 1 = @maxposid
30 begin
31 update 审核主表 set Auditstatus = 3 where [ ID ] = @AuditMainID
32 end
33 end
34 else
35 begin
36 insert into 流程子表(MainID,AuditOpinion,Auditstatus,Sequence,AuditID,CreateDate,角色ID) values ( @AuditMainID , @AuditOpinion , 0 , @posid + 1 , @UserID , getdate (), @UserGroupID )
37 update 审核主表 set Auditstatus = 2 where [ ID ] = @AuditMainID
38 end
这个就是执行审核的存储过程,
现在主任C1打开审核系统,根据需要,他只能看到它能看的审核.
1
Create
procedure
GetListAudit
2 (
3 @userid varchar ( 100 ),
4 @statusid int -- 0待审核,1已审核,2历史记录
5 )
6
7 AS
8 if @statusid = 1
9 begin
10 select *
11 from 审核主表
12 where [ ID ] in
13 (
14 Select MainID from 审核子表
15 where AuditID = @userid
16 )
17 end
18 if @statusid = 0
19 begin
20 select *
21 from 审核主表
22 where [ ID ] not in
23 (
24 select MainID from 审核子表
25 where AuditID = @userid
26 )
27 and ProcessID in
28 (
29 Select MainID from 流程子表
30 where UserGroupID in
31 (
32 select 角色ID from 员工表
33 where 员工ID = @userid
34 )
35 )
36 end
37 if @statusid = 2
38 begin
39 select *
40 from 审核主表
41 where Auditstatus in ( 2 , 3 )
42 and ProcessID in
43 (
44 Select MainID from 流程子表
45 where UserGroupID in
46 (
47 select 角色ID from 员工表
48 where 员工ID = @userid
49 )
50 )
51 end
2 (
3 @userid varchar ( 100 ),
4 @statusid int -- 0待审核,1已审核,2历史记录
5 )
6
7 AS
8 if @statusid = 1
9 begin
10 select *
11 from 审核主表
12 where [ ID ] in
13 (
14 Select MainID from 审核子表
15 where AuditID = @userid
16 )
17 end
18 if @statusid = 0
19 begin
20 select *
21 from 审核主表
22 where [ ID ] not in
23 (
24 select MainID from 审核子表
25 where AuditID = @userid
26 )
27 and ProcessID in
28 (
29 Select MainID from 流程子表
30 where UserGroupID in
31 (
32 select 角色ID from 员工表
33 where 员工ID = @userid
34 )
35 )
36 end
37 if @statusid = 2
38 begin
39 select *
40 from 审核主表
41 where Auditstatus in ( 2 , 3 )
42 and ProcessID in
43 (
44 Select MainID from 流程子表
45 where UserGroupID in
46 (
47 select 角色ID from 员工表
48 where 员工ID = @userid
49 )
50 )
51 end
上面的存储过程还可以增加未提交的列表
只需要一个存储过程就实现了,未提交,已经审核,未审核,和历史审核记录,
并将这些不同的状态定义为一个枚举类型.
我们实现了,审核操作,和查看审核信息,还缺少什么呢?还缺少一个文件跟踪功能,就是审核流程走到哪个角色的功能
1
Create
procedure
GetListAuditTrail
2 (
3 @AuditMainID int -- 审核单号
4 )
5 AS
6
7 Select a. * b.AuditID,b.CreateDate Dates
8 from 流程子表 a left join 审核子表 b
9 on a.Sequence = b.Sequence
10 where b.MainID = @AuditMainID and
11 a.MainID in ( select ProcessID from 审核主表 where [ ID ] = @AuditMainID )
2 (
3 @AuditMainID int -- 审核单号
4 )
5 AS
6
7 Select a. * b.AuditID,b.CreateDate Dates
8 from 流程子表 a left join 审核子表 b
9 on a.Sequence = b.Sequence
10 where b.MainID = @AuditMainID and
11 a.MainID in ( select ProcessID from 审核主表 where [ ID ] = @AuditMainID )
从安全性来说,我觉得应该进行下用户权限检验的.
其实我个人写存储过程有个习惯的,如果这个存储过程是返回一个列表的,我喜欢用GetList为开头,如果执行性存储过程则喜欢Exec,如果只是返回一个值的用则是GetOnly开头,