簡易OA漫談之工作流設計（DB實作）

好久沒寫部落格， 沒想到大家讨論還挻熱烈，非常感謝大家關注和提出的一些建議和意見。

大家關注的還是兩點：

1、現實流程會複雜得多;

2、流程平台需要複雜的設計（作圖）工具。

我想說的是我這裡不是做一個“大而全”的工作流平台，隻是從最基本的地方入手，通過這樣一些測試，讓初學者發現工作流的一些原理。

了解這些原理後，你使用各種平台（如K2）開發就會清楚一些。

上篇算是一個概要設計，這篇簡單的做一個資料庫實作，本實作暫不包括“規則處理”，即由調用端決定下一手步驟和處理人。

兄弟剛離職，現在正找工作，關乎一家人是否有飯吃，是以時間不多，寫得很粗糙，大家還請原諒則個！

用的sql server.

1、先建一個資料庫MyData

2、執行一下結構腳本

View Code USE [MyData]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[BaseUser](

[ID] [int] IDENTITY(1,1) NOT NULL,

[UserName] [varchar](50) NOT NULL,

[PassWord] [varchar](50) NULL,

[UserDesc] [nvarchar](200) NULL,

[UnitID] [int] NULL,

[PostID] [int] NULL,

[LocationID] [int] NULL,

[Phone] [varchar](50) NULL,

[Email] [varchar](100) NULL,

[EnableFlag] [bit] NOT NULL,

CONSTRAINT [PK_MyUser] 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

SET ANSI_PADDING OFF

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[FlowStep](

[ID] [int] IDENTITY(1,1) NOT NULL,

[StepID] [int] NOT NULL,

[FlowID] [int] NOT NULL,

[StepCode] [varchar](50) NOT NULL,

[StepName] [nvarchar](50) NOT NULL,

[IsJoin] [bit] NULL,

[Params] [xml] NULL,

[EnabeFlag] [bit] NOT NULL,

CONSTRAINT [PK_FlowStep] 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

SET ANSI_PADDING OFF

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[FlowInstance](

[ID] [int] IDENTITY(1,1) NOT NULL,

[FlowID] [int] NOT NULL,

[FlowName] [varchar](50) NOT NULL,

[UserID] [int] NOT NULL,

[UserName] [varchar](50) NOT NULL,

[ApplyUserName] [varchar](50) NULL,

[ApplyDateTime] [datetime] NULL,

[EndUserName] [varchar](50) NULL,

[EndDateTime] [datetime] NULL,

[StatusID] [int] NULL,

[StatusName] [varchar](50) NULL,

[ApplyStatus] [nvarchar](500) NULL,

CONSTRAINT [PK_FlowMain] 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

SET ANSI_PADDING OFF

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[FlowActionTraceData](

[TraceID] [int] NOT NULL,

[Data] [xml] NULL,

CONSTRAINT [PK_FlowActionTraceData] PRIMARY KEY CLUSTERED 

(

[TraceID] 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

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[FlowActionTrace](

[ID] [int] IDENTITY(1,1) NOT NULL,

[InstanceID] [int] NOT NULL,

[FlowID] [int] NOT NULL,

[FlowName] [nvarchar](50) NOT NULL,

[FlowCode] [varchar](50) NOT NULL,

[StepID] [int] NOT NULL,

[StepName] [nvarchar](50) NOT NULL,

[StepCode] [varchar](50) NOT NULL,

[ActionID] [int] NULL,

[ActionName] [nvarchar](50) NULL,

[ActionCode] [varchar](50) NULL,

[OwnerUserID] [int] NOT NULL,

[OwnerUserName] [varchar](50) NOT NULL,

[OwnerUserDesc] [nvarchar](50) NOT NULL,

[SubmitUserID] [int] NULL,

[SubmitUserName] [varchar](50) NULL,

[SubmitUserDesc] [nvarchar](50) NULL,

[ArriveTime] [datetime] NOT NULL,

[ReadTime] [datetime] NULL,

[SubmitTime] [datetime] NULL,

[PreID] [int] NOT NULL,

[SubmitMemo] [nvarchar](500) NULL,

CONSTRAINT [PK_FlowActionTrace] 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

SET ANSI_PADDING OFF

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[FlowAction](

[ID] [int] IDENTITY(1,1) NOT NULL,

[ActionID] [int] NOT NULL,

[StepID] [int] NOT NULL,

[FlowID] [int] NOT NULL,

[ActionCode] [varchar](50) NOT NULL,

[ActionName] [nvarchar](50) NOT NULL,

[Params] [xml] NULL,

[EnableFlag] [bit] NOT NULL,

CONSTRAINT [PK_FlowAction] 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

SET ANSI_PADDING OFF

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Flow](

[ID] [int] NOT NULL,

[FlowCode] [varchar](50) NOT NULL,

[FlowName] [nvarchar](50) NULL,

[FlowVersion] [varchar](50) NOT NULL,

[Params] [xml] NULL,

[EnableFlag] [bit] NOT NULL,

CONSTRAINT [PK_Flow_1] 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

SET ANSI_PADDING OFF

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE function [dbo].[f_splitstr](@SourceSql varchar(8000),@StrSeprate varchar(100))

returns @temp table(F1 varchar(100))

as 

begin 

declare @ch as varchar(100)

set @[email protected][email protected] 

while(@SourceSql<>'')

begin 

set @ch=left(@SourceSql,charindex(@StrSeprate,@SourceSql,1)-1) 

insert @temp values(@ch)

set @SourceSql=stuff(@SourceSql,1,charindex(@StrSeprate,@SourceSql,1),'')

end 

return 

end

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: <Author,,Name>

-- Create date: <Create Date, ,>

-- Description: <Description, ,>

-- =============================================

CREATE FUNCTION [dbo].[f_getstartstatus]

(

@OwnerUserIDStr varchar(500),

@StepName nvarchar(50)

)

RETURNS nvarchar(500)

AS

BEGIN

declare @ret nvarchar(500) 

set @ret=''

select @[email protected]+e.UserDesc +'('[email protected]+');'

from f_splitstr(@OwnerUserIDStr,',') d 

join dbo.BaseUser e on d.f1=e.ID

return @ret

END

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: <Author,,Name>

-- Create date: <Create Date, ,>

-- Description: <Description, ,>

-- =============================================

create FUNCTION [dbo].[f_getflowstatus]

(

@instanceid int 

)

RETURNS nvarchar(500)

AS

BEGIN

declare @ret nvarchar(500) 

set @ret=''

select @[email protected]+OwnerUserDesc+'('+StepName+');'

from dbo.flowactiontrace

where [email protected]

and SubmitTime is null

return @ret

END

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- exec usp_FlowSubmit 10,3,3,0,'4/2,3',null

-- =============================================

CREATE PROCEDURE [dbo].[usp_FlowSubmit]

(

@TraceID int,--flowactiontrace的id

@AcionID int,--操作編号

@SubmitUserID int,--送出人編号

@IsEnd int,--是否結束步驟

@NextStr varchar(2000),--'1/3,4;3/5,6'

@Data nvarchar(max)

)

AS

BEGIN

SET NOCOUNT ON;

declare @flowid int

declare @stepid int

declare @actioncode varchar(50)

declare @actionname nvarchar(50)

declare @instanceid int

declare @submitusername varchar(50)

declare @submituserdesc nvarchar(50)

declare @isjoin bit

select @submitusername=username,

@submituserdesc=userdesc

from dbo.BaseUser

where [email protected]

select @instanceid=instanceid,

@flowid=flowid,

@stepid=stepid

from dbo.FlowActionTrace

where [email protected]

select @actioncode=actioncode,

@actionname=actionname

from dbo.FlowAction

where [email protected] and [email protected] and [email protected] 

select @isjoin =isjoin 

from dbo.FlowStep

where [email protected] and [email protected]

begin tran

--送出目前待辦

update dbo.FlowActionTrace 

set [email protected],

[email protected],

[email protected],

[email protected],

[email protected],

[email protected],

SubmitTime=GETDATE()

where [email protected] 

if(@Data is not null)

begin

insert into dbo.FlowActionTraceData(TraceID,Data)

values(@TraceID,@Data)

end

--如果不是會審，删除其他待辦

if(@isjoin=0)

begin

delete dbo.FlowActionTrace

where [email protected] and [email protected] and ID<>@TraceID 

end

--更新主表狀态

if(@IsEnd=1 

and (select COUNT(*) from dbo.FlowActionTrace where [email protected] and SubmitTime is null)=0

)

begin 

update dbo.FlowInstance 

set [email protected], 

EndDateTime=GETDATE(),

StatusID=100,

StatusName='正常結束',

ApplyStatus=''

where [email protected] 

end 

else --不是結束時插入待辦，可能多個路線，一個路線可能多人

begin 

declare @next varchar(500) 

DECLARE db_cursor CURSOR FOR 

select * FROM dbo.f_splitstr(@NextStr,';')

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @next

WHILE @@FETCH_STATUS=0

BEGIN

declare @nextstepid int --下一步驟

declare @OwnerUserIDStr varchar(500) --下一步驟處理人

set @nextstepid=convert(int,left(@next,charindex('/',@next,1)-1))

set @OwnerUserIDStr=right(@next,len(@next)-charindex('/',@next,1))

declare @nextstepcode varchar(50)

declare @nextstepname nvarchar(50)

select @nextstepcode=stepcode,

@nextstepname =stepname

from FlowStep where [email protected] and [email protected]

--插入待辦

INSERT INTO [FlowActionTrace]

([InstanceID]

,[FlowID]

,[FlowName]

,[FlowCode]

,[StepID]

,[StepName]

,[StepCode]

,[OwnerUserID]

,[OwnerUserName]

,[OwnerUserDesc] 

,[ArriveTime],[PreID]

) 

SELECT @instanceid,@FlowID,a.FlowName,a.FlowCode,

@nextstepid,@nextstepcode,@nextstepname,

b.ID,b.UserName,b.UserDesc,

getdate(),@TraceID

FROM dbo.Flow a,

(

select e.ID,e.UserName,e.UserDesc 

from dbo.f_splitstr(@OwnerUserIDStr,',') d 

join dbo.BaseUser e on d.f1=e.ID

)b WHERE [email protected]

FETCH NEXT FROM db_cursor INTO @next

END

CLOSE db_cursor

DEALLOCATE db_cursor

--更新主表狀态 

update dbo.FlowInstance 

set ApplyStatus=dbo.f_getflowstatus(@instanceid) 

where [email protected] 

end

commit tran

END

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- exec usp_FlowStart 1,1,1,2,'2,3'

-- =============================================

CREATE PROCEDURE [dbo].[usp_FlowStart]

(

@FlowID int,--流程模闆編号

@UserID int,--流程使用者

@ApplyUserID int,--流程申請使用者

@NextStepID int,--下一步驟

@OwnerUserIDStr varchar(500),--多個下手審批人，以‘,’分隔

@Data nvarchar(max)

)

AS

BEGIN

SET NOCOUNT ON;

declare @stepid int

declare @stepcode varchar(50)

declare @stepname nvarchar(50)

declare @actionid int

declare @actioncode varchar(50)

declare @actionname nvarchar(50)

select @stepid=stepid,

@stepcode=stepcode,

@stepname=stepname

from FlowStep where [email protected] and StepID=1

select @actionid=actionid,

@actioncode=actioncode,

@actionname=actionname

from FlowAction where [email protected] and StepID=1 and ActionID=1 

declare @nextstepcode varchar(50)

declare @nextstepname nvarchar(50)

select @nextstepcode=stepcode,

@nextstepname=stepname

from FlowStep where [email protected] and [email protected] 

begin tran

declare @instanceid int

--插入主表

INSERT INTO [FlowInstance]

([FlowID]

,[FlowName]

,[UserID]

,[UserName]

,[ApplyUserName]

,[ApplyDateTime]

,[StatusID]

,[StatusName]

,[ApplyStatus])--待辦狀态

SELECT @FlowID,a.FlowName,

@UserID,b.UserName,c.UserName,

GETDATE(),1,'流程中',dbo.f_getstartstatus(@OwnerUserIDStr,@nextstepname) 

FROM dbo.Flow a,dbo.BaseUser b,dbo.BaseUser c

WHERE [email protected] and [email protected] and [email protected] 

set @instanceid=@@identity

--插入申請日志

INSERT INTO [MyData].[dbo].[FlowActionTrace]

([InstanceID]

,[FlowID]

,[FlowName]

,[FlowCode]

,[StepID]

,[StepName]

,[StepCode]

,[ActionID]

,[ActionName]

,[ActionCode]

,[OwnerUserID]

,[OwnerUserName]

,[OwnerUserDesc]

,[SubmitUserID]

,[SubmitUserName]

,[SubmitUserDesc]

,[ArriveTime]

,[ReadTime]

,[SubmitTime])

SELECT @instanceid,@FlowID,a.FlowName,a.FlowCode,

@stepid,@stepcode,@stepname,

@actionid,@actioncode,@actionname,

b.ID,b.UserName,b.UserDesc,

b.ID,b.UserName,b.UserDesc,

getdate(),getdate(),getdate()

FROM dbo.Flow a,dbo.BaseUser b

where [email protected] and [email protected]

declare @preid int

set @preid=@@IDENTITY

if(@Data is not null)

begin

insert into dbo.FlowActionTraceData(TraceID,Data)

values(@preid,@Data)

end

--插入待辦

INSERT INTO [FlowActionTrace]

([InstanceID]

,[FlowID]

,[FlowName]

,[FlowCode]

,[StepID]

,[StepName]

,[StepCode]

,[OwnerUserID]

,[OwnerUserName]

,[OwnerUserDesc] 

,[ArriveTime],[PreID]

) 

SELECT @instanceid,@FlowID,a.FlowName,a.FlowCode,

@nextstepid,@nextstepcode,@nextstepname,

b.ID,b.UserName,b.UserDesc,

getdate(),@preid

FROM dbo.Flow a,

(

select e.ID,e.UserName,e.UserDesc 

from dbo.f_splitstr(@OwnerUserIDStr,',') d 

join dbo.BaseUser e on d.f1=e.ID

) b

WHERE [email protected] 

commit tran

END

GO

ALTER TABLE [dbo].[BaseUser] ADD CONSTRAINT [DF_MyUser_EnableFlag] DEFAULT ((1)) FOR [EnableFlag]

GO

ALTER TABLE [dbo].[FlowActionTrace] ADD CONSTRAINT [DF_FlowActionTrace_PreID] DEFAULT ((0)) FOR [PreID]

GO

------------

3、執行一下資料腳本

View Code --插入使用者

insert BaseUser(ID,UserName,PassWord,UserDesc,UnitID,PostID,LocationID,Phone,Email,EnableFlag) values( 1 , 'aaa' , '123' , N'王小二' , NULL , NULL , NULL , NULL , NULL , 1 )

insert BaseUser(ID,UserName,PassWord,UserDesc,UnitID,PostID,LocationID,Phone,Email,EnableFlag) values( 2 , 'bbb' , '234' , N'張老三' , NULL , NULL , NULL , NULL , NULL , 1 )

insert BaseUser(ID,UserName,PassWord,UserDesc,UnitID,PostID,LocationID,Phone,Email,EnableFlag) values( 3 , 'ccc' , '345' , N'李家四' , NULL , NULL , NULL , NULL , NULL , 1 )

insert BaseUser(ID,UserName,PassWord,UserDesc,UnitID,PostID,LocationID,Phone,Email,EnableFlag) values( 4 , 'ddd' , '456' , N'劉大五' , NULL , NULL , NULL , NULL , NULL , 1 )

--插入流程模闆表

insert Flow(ID,FlowCode,FlowName,FlowVersion,Params,EnableFlag) values( 1 , 'qinjia' , N'請假' , '1' , NULL , 1 )

insert FlowStep(ID,StepID,FlowID,StepCode,StepName,IsJoin,Params,EnabeFlag) values( 1 , 1 , 1 , 'apply' , N'請假申請' , 0 , NULL , 1 )

insert FlowStep(ID,StepID,FlowID,StepCode,StepName,IsJoin,Params,EnabeFlag) values( 2 , 2 , 1 , 'parent' , N'上級審批' , 0 , NULL , 1 )

insert FlowStep(ID,StepID,FlowID,StepCode,StepName,IsJoin,Params,EnabeFlag) values( 4 , 3 , 1 , 'manager' , N'經理審批' , 1 , NULL , 1 )

insert FlowStep(ID,StepID,FlowID,StepCode,StepName,IsJoin,Params,EnabeFlag) values( 5 , 4 , 1 , 'hrconfirm' , N'HR确認' , 0 , NULL , 1 )

insert FlowAction(ID,ActionID,StepID,FlowID,ActionCode,ActionName,Params,EnableFlag) values( 1 , 1 , 1 , 1 , 'applysubmit' , N'申請送出' , NULL , 1 )

insert FlowAction(ID,ActionID,StepID,FlowID,ActionCode,ActionName,Params,EnableFlag) values( 2 , 2 , 1 , 1 , 'applycancel' , N'申請取消' , NULL , 1 )

insert FlowAction(ID,ActionID,StepID,FlowID,ActionCode,ActionName,Params,EnableFlag) values( 3 , 3 , 2 , 1 , 'parentsubmit' , N'上級送出' , NULL , 1 )

insert FlowAction(ID,ActionID,StepID,FlowID,ActionCode,ActionName,Params,EnableFlag) values( 4 , 4 , 2 , 1 , 'parentreject' , N'上級駁回' , NULL , 1 )

insert FlowAction(ID,ActionID,StepID,FlowID,ActionCode,ActionName,Params,EnableFlag) values( 5 , 5 , 2 , 1 , 'parentcancel' , N'上級取消' , NULL , 1 )

insert FlowAction(ID,ActionID,StepID,FlowID,ActionCode,ActionName,Params,EnableFlag) values( 6 , 6 , 3 , 1 , 'managersubmit' , N'經理送出' , NULL , 1 )

insert FlowAction(ID,ActionID,StepID,FlowID,ActionCode,ActionName,Params,EnableFlag) values( 7 , 7 , 3 , 1 , 'managercancel' , N'經理取消' , NULL , 1 )

insert FlowAction(ID,ActionID,StepID,FlowID,ActionCode,ActionName,Params,EnableFlag) values( 10 , 8 , 4 , 1 , 'hrconfirmsubmit' , N'HR确認送出' , NULL , 1 )

insert FlowAction(ID,ActionID,StepID,FlowID,ActionCode,ActionName,Params,EnableFlag) values( 11 , 9 , 4 , 1 , 'hrconfirmcancel' , N'HR确認取消' , NULL , 1 )

4、發起一個流程

exec usp_FlowStart 1,1,1,2,'2,3',null

參數解釋：

 @FlowID int,--流程模闆編号

 @UserID int,--流程使用者

 @ApplyUserID int,--流程申請使用者

 @NextStepID int,--下一步驟

 @OwnerUserIDStr varchar(500),--多個下手審批人，以‘,’分隔

 @Data nvarchar(max) --表單資料，xml格式

5、讀一下任務資訊

檢視應該可以看到FlowInstance一條資料，這是流程執行個體主表。

檢視應該可以看到FlowActionTrace三條資料，即申請已辦一條，待辦兩條，為什麼待會是兩條，因為上面的參數

'2,3'就表示下一步處理人是兩個。

select * from dbo.FlowInstance

select * from dbo.FlowActionTrace

6、執行一個待辦送出

exec usp_FlowSubmit 3,3,3,0,'3/2,3',null

參數解釋：

 @TraceID int,--flowactiontrace的id，選擇一個submittime 為null的記錄的id

 @AcionID int,--操作編号，就是做的是哪種操作，一般1代表同意送出

 @SubmitUserID int,--送出人編号

 @IsEnd int,--是否結束步驟

 @NextStr varchar(2000),--'1/3,4;3/5,6'這個結構表示“下一步驟編号/處理人編号串;下一步驟編号/處理人編号串”

 @Data nvarchar(max)--表單資料，xml格式

大家會覺得組裝下一步驟和下一步操作人很麻煩，我們做了元件後會使用更好結構的屬性。

下和篇如果有興趣就做一個簡單dll和website demo,以便跑跑看。