学步园

简单工作流 需要 1.创建事务编码(日期视图) 2.创建事务 3.发送事务处理过程 4.审批事务处理过程 1. (1)日期视图: Create View v_GetDate AS Select dt=Convert(Char(6),GetDate(),12) (2) /* 用途: 自动生成编码 发布时间;2008-11-13 作者:  龚德辉 注释: 其中涉及到一个视图 v_GetDate, 目的是取得年月日 在使用getdate()函数的时候，如果用户义函数中使用getdate函数，将会收到这样的错误信息
"getdate",出现这样的错误原因用户定义函数中部允许使用不确定函数，而getdate恰好是不确定函数。解决这个问题采用视图的方式 */ CREATE procedure UF_Web_GetTaskCode( @Type varchar(2), @TaskCode varchar(11)='' output ) as begin declare @dt CHAR(8),@lsh char(3) Select @dt=@Type+(Select * from v_GetDate) select @lsh=substring(MAX(TaskCode),9,11)
from Task where substring(TaskCode,1,8)=@dt set @lsh=isnull(rtrim(ltrim(@lsh)),0) SET @lsh=right(@lsh,3)+1 while len(@lsh)<3 begin set @lsh='0'+@lsh end set @TaskCode=@dt+@lsh select @TaskCode end GO 2.创建事务 CREATE Procedure UF_Web_CreateNewTask( @TaskCode
as nvarchar(11)=null ,@TaskDescription as nvarchar(30)=null ,@Confident as nchar(1)=null ,@Priority as nchar(1)=null ,@Important as nchar(1)=null ,@CreateBy as int=null ) as declare @Stat as nvarchar(1),@Progress nvarchar(10),@location as nvarchar(10),@CreateDate
as datetime,@Flowid as int set @Stat='P' set @Progress='撰写' set @location='撰写' set @CreateDate=getdate() --取得流ID select @Flowid=f.id from flow f left join flowline fl on f.FlowCode=fl.FlowCode where fl.tranType='W'and f.Type=@TaskDescription and fl.UserID=@CreateBy
begin if not exists(select TaskCode from Task where TaskCode=@TaskCode) begin --新增事务头 insert Task(TaskCode,Description,FlowID,Stat,Progress,location,confident,Priority,important,CreateDate,CreateBy) values(@TaskCode,@TaskDescription,@Flowid,@Stat,@Progress,@location,@Confident,@Priority,@Important,@CreateDate,@CreateBy)
--批量CopyFlowline 到TaskLine insert TaskLine(TaskCode,TaskLine,UserID)select T.TaskCode,FL.FlowLine,FL.UserID from Task T left join Flow F on T.FlowID=F.ID left join FlowLine FL on FL.FlowCode=F.FlowCode where T.TaskCode=@TaskCode order by FlowLine DESC --初始化
update TaskLine set Result='N',TurnTo=1,ReceiptDate=@CreateDate,StartDate=@CreateDate where TaskCode=@TaskCode and TaskLine=1 end end GO 3.发送事务处理过程 CREATE Procedure UF_Web_UpdateTaskSendSp( @SendUserID int ,@TaskCode varchar(11) ) AS declare @temp nvarchar(15),@Progress
varchar(200),@FlowLevel int, @TaskLine int,@UserID int,@FlowCode varchar(15) -- @SendUserID int,@TaskCode varchar(11), --set @SendUserID=12 --set @TaskCode='OT081114001' select top 1 @FlowCode=FL.FlowCode,@FlowLevel=FL.FlowLevel+1 from TaskLine TL left join
Task T on T.TaskCode=TL.TaskCode left join Flow F on T.FlowID=F.ID left join FlowLine FL on FL.FlowCode=F.FlowCode left join users U on FL.UserID=U.ID where TL.TaskCode=@TaskCode and TL.TaskLine='1' and FL.UserID=@SendUserID order by FlowLevel --select @TaskLine=TaskLine
from TaskLine where TaskCode=@TaskCode and UserID=@SendUserID --print @FlowLevel --print @TaskLine --SET @FlowLevel=3 Declare MyCursor1 CURSOR local static FOR select U.name,U.ID from FlowLine FL left join users U on FL.UserID=U.ID WHERE FL.FlowLevel=@FlowLevel
and FL.FlowCode=@FlowCode and FL.tranType='A' Open MyCursor1 while 1=1 begin fetch MyCursor1 into @temp ,@UserID if @@fetch_status<>0 break begin set @Progress=ISNULL(@Progress,'')+isnull(@temp,'')+'' update TaskLine set TurnTo='1',StartDate=getdate() where
TaskCode=@TaskCode and UserID=@UserID end end if @Progress is null set @Progress=@Progress else set @Progress=' 待'+@Progress+'审批' close MyCursor1 deallocate MyCursor1 update Task set Progress=Progress+'->'+@Progress,location=@Progress,Stat='S',SendUserID=@SendUserID,SendDate=getdate()
where TaskCode=@TaskCode GO 4.审批事务处理过程 CREATE Procedure UF_Web_ApproveTask( @ApproveUserID as int=null ,@TaskCode as nvarchar(11)=null ,@Result as nchar(1)=null ,@Ref as nvarchar(50)=null ,@BackLevel as nchar(1)='Y'--最低级 ) as declare @NameVerify varchar(2000),@Name
varchar(20) begin --更新当层TaskLine的信息 Update TaskLine set Result=@Result,ref=@Ref,FinishDate=Getdate() where TaskCode=@TaskCode and UserID=@ApproveUserID if(@Result='Y') begin --更新当层的进度信息和当前进度 select @Name=U.name, @NameVerify=CASE when Result='Y'THEN '已批准' else
'未批准' end from TaskLine TL left join users U on TL.UserID=U.ID where TL.TaskCode=@TaskCode and TL.UserID=@ApproveUserID update Task set Progress=Progress+'->'+@Name+@NameVerify,location=@Name+@NameVerify where TaskCode=@TaskCode --检查单层是否完成--(未完成)--->注明还需要谁批准
--完成是否有下层--(没有下层)-->完成加班单-->(有下层)更新字段:TurnTo declare @temp nvarchar(15),@Progress varchar(2000) --declare @ApproveUserID int,@TaskCode varchar(11) --set @ApproveUserID='13' --set @TaskCode='OT081115004' if exists(select * from Taskline where TaskCode=@TaskCode
and TurnTo='1' and Result is null) begin ---注明当前层还需谁审批 declare MyCursor1 cursor local static for select U.name from Taskline TL left join users U on TL.UserID=U.ID where TL.TaskCode=@TaskCode and TL.TurnTo='1' and Result is null open MyCursor1 while 1=1 begin
fetch MyCursor1 into @temp if @@fetch_status<>0 break begin set @Progress=ISNULL(@Progress,'')+isnull(@temp,'')+'' end end close MyCursor1 deallocate MyCursor1 set @Progress='待'+@Progress+'审批' update Task set Progress=Progress+'->'+@Progress where TaskCode=@TaskCode
end else begin ---处理下层 declare @tmp nvarchar(15),@Pro varchar(200),@FlowLevel int, @TaskLine int,@UserID int,@FlowCode varchar(15) select @FlowCode=FL.FlowCode,@FlowLevel=FL.FlowLevel+1 from ( select Task.FlowID from Task left join taskline tl on Task.TaskCode=TL.TaskCode
where TL.TaskCode=@TaskCode and TL.UserID=@ApproveUserID) T left JOIN Flow f on f.ID=t.FlowID left join FlowLine FL on FL.FlowCode=F.FlowCode where FL.UserID=@ApproveUserID if exists(select U.name,U.ID from FlowLine FL left join users U on FL.UserID=U.ID WHERE
FL.FlowLevel=@FlowLevel and FL.FlowCode=@FlowCode and FL.tranType='A') ---@ApproveUserID为审核人的ID begin --取得下层，更新字段 Declare MyCursor2 CURSOR local static FOR select U.name,U.ID from FlowLine FL left join users U on FL.UserID=U.ID WHERE FL.FlowLevel=@FlowLevel
and FL.FlowCode=@FlowCode and FL.tranType='A' Open MyCursor2 while 1=1 begin fetch MyCursor2 into @tmp ,@UserID if @@fetch_status<>0 break begin set @Pro=isnull(@Pro,'')+isnull(@tmp,'')+' ' update TaskLine set TurnTo='1',StartDate=getdate() where TaskCode=@TaskCode
and UserID=@UserID end end set @Pro='待'+@Pro+'审批' update Task set Progress=Progress+'->'+@Pro,location=@Pro---,SendUserID=@ApproveUserID,SendDate=getdate()// 是否需要发送呢？ where TaskCode=@TaskCode close MyCursor2 deallocate MyCursor2 end else begin --完成事务 还需要做何处理？
如何确定最后完成？ update Task set Stat='A' where TaskCode=@TaskCode end end end else --不同意如何呢? --考虑退回到最低级还是上一级 begin if @BackLevel is not null begin if(@BackLevel='Y')--最低级 begin --头更新 update Task set Stat='P',Progress =Progress+'->'+@Name+'退回',location=location+'->'+@Name+'退回'
where TaskCode=@TaskCode --行更新 update TaskLine set Result=null,ref=null,TurnTo=0,ReceiptDate=null,StartDate=null,FinishDate=null where TaskCode=@TaskCode and TaskLine<>1 update TaskLine set Result='N',ref=null,TurnTo=1,FinishDate=null where TaskCode=@TaskCode
and TaskLine=1 end else begin declare @Lastlevel as int select @Lastlevel=TaskLine-1 from TaskLine where TaskCode=@TaskCode and UserID=@ApproveUserID --考虑是否是最低层 if(@Lastlevel<>1) begin --更新上一层 update TaskLine set Result=null,ref=null,TurnTo=1,ReceiptDate=null,StartDate=null,FinishDate=null
where TaskCode=@TaskCode and TaskLine=@Lastlevel --本层 update TaskLine set Result=null,ref=null,TurnTo=0,ReceiptDate=null,StartDate=null,FinishDate=null where TaskCode=@TaskCode and TaskLine=@Lastlevel+1 end else --更新第一层 begin --头更新 update Task set Stat='P',Progress
=Progress+'->'+@Name+'退回',location=location+'->'+@Name+'退回' where TaskCode=@TaskCode --行更新 update TaskLine set Result=null,ref=null,TurnTo=0,ReceiptDate=null,StartDate=null,FinishDate=null where TaskCode=@TaskCode and TaskLine<>1 update TaskLine set Result='N',ref=null,TurnTo=1,FinishDate=null
where TaskCode=@TaskCode and TaskLine=1 end end end end end GO using System; using System.Data; using System.Configuration; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls; using System.Data.SqlClient; /// /// Workflow 的摘要说明 /// 撰写/ /// 发送/ /// 审批/ /// 退回/ /// 修改权限/ /// public class Workflow { public Workflow() { // // TODO: 在此处添加构造函数逻辑 // } private const string _pro_gettaskcode = "UF_Web_GetTaskCode";
//取得新的新事务编码存储过程 private const string _proc_creatnewtask = "UF_Web_CreateNewTask";//创建新的事务和事务行存储过程 private const string _proc_sendtask = "UF_Web_UpdateTaskSendSp"; //发送事务处理存储过程 private const string _proc_approvetask = "UF_Web_ApproveTask"; //审批事务处理存储过程 private
string _document_type; private string _taskcode; //事务编码 private string _document_description;//单据类型 事务流的Type字段 重要 private string _confident; //机密性 private string _priority; //优先级 private string _important; //重要性 private int _createby; //创建事务userid private
int _approveby; //审批事务userid private string _result; //审批同意否标志(Y/N?) private string _ref; //审批事务备注栏 private string _backLevel; //退回标志(NULL 为 不退回 Y为退回下一级 N为退回到最低级) /// /// 单据类型 /// public string DOCUMENT_TYPE { set { _document_type = value; } get { return _document_type;
} } /// /// 单据号码 /// public string TASKCODE { set { _taskcode = value; } get { return _taskcode; } } /// /// 单据说明务必与工作流的流说明一致 /// public string DOCUMENT_DESCRIPTION { set { _document_description = value; } get { return _document_description; } } /// /// 机密性
/// public string CONFIDENT { set { _confident = value; } get { return _confident; } } /// /// 优先级 /// public string PRIORITY { set { _priority = value; } get { return _priority; } } /// /// 重要性 /// public string IMPORTANT { set { _important = value; } get
{ return _important; } } /// /// 创建人ID /// public int CREATEBY { set { _createby = value; } get { return _createby; } } /// /// 审批人ID /// public int USERID { set { _approveby = value; } get { return _approveby; } } /// /// 审批标志(Y/N?) /// public string RESULT
{ set { _result = value; } get { return _result; } } /// /// 审批内容备注 /// public string REF { set { _ref = value; } get { return _ref; } } /// /// 退回标志(Y/N?:NULL) /// public string BACKLEVEL { set { _backLevel = value; } get { return _backLevel; } } /// ///
产生新单据号码 /// public string GetTaskCode() { string TaskCode; string connectionString = PublicVar.strCon_SHATDB; SqlConnection MyConn = new SqlConnection(connectionString); MyConn.Open(); SqlCommand sc = new SqlCommand(_pro_gettaskcode, MyConn); sc.CommandType
= CommandType.StoredProcedure; sc.Parameters.Add("@Type", SqlDbType.VarChar, 2); SqlParameter C = sc.Parameters.Add("@TaskCode", SqlDbType.VarChar, 11); sc.Parameters[0].Value = DOCUMENT_TYPE; C.Direction = ParameterDirection.Output; sc.ExecuteNonQuery();
TaskCode = C.Value.ToString(); MyConn.Close(); return (TaskCode); } /// /// 生成事务和事务行 /// public bool CreatNewTask() { SqlParameter[] parms = new SqlParameter[6]; parms[0] = new SqlParameter("@TaskCode", SqlDbType.NVarChar, 11); parms[1] = new SqlParameter("@TaskDescription",
SqlDbType.NVarChar, 30); parms[2] = new SqlParameter("@Confident", SqlDbType.NChar); parms[3] = new SqlParameter("@Priority", SqlDbType.NChar, 50); parms[4] = new SqlParameter("@Important", SqlDbType.NChar, 50); parms[5] = new SqlParameter("@CreateBy", SqlDbType.Int);
parms[0].Value = TASKCODE; parms[1].Value = DOCUMENT_DESCRIPTION; parms[2].Value = CONFIDENT; parms[3].Value = PRIORITY; parms[4].Value = IMPORTANT; parms[5].Value = CREATEBY; if (DataAccess.DataAccessSHDB.RunProc(_proc_creatnewtask, parms) != -1) { return
true; } else { return false; } } /// /// 发送事务 /// public bool SendTask() { string SQLs = "select stat from task where taskcode='" +TASKCODE+ "' and stat='W'"; if (!DataAccess.DataAccessSHDB.Login(SQLs)) { DataAccess.MessageObject.ShowLocation("当前" + TASKCODE
+ "之前已被发送,不可再次发送,请注意!"); return false; } else { //更新事务行的第一行 结果改为Y，结束时间为 DateTime FinishDate = DateTime.Now; string strsql = "update TaskLine set Result='Y',FinishDate='" + FinishDate + "' where TaskCode='" + TASKCODE + "'and TaskLine='1'"; DataAccess.DataAccessSHDB.GetNonQuery(strsql);
//变更事务头的相关信息 SqlParameter[] parms = new SqlParameter[2]; parms[0] = new SqlParameter("@SendUserID", SqlDbType.Int); parms[1] = new SqlParameter("@TaskCode", SqlDbType.VarChar, 11); parms[0].Value = CREATEBY; parms[1].Value = TASKCODE; if (DataAccess.DataAccessSHDB.RunProc(_proc_sendtask,
parms) != -1) { DataAccess.MessageObject.ShowLocation("当前" + TASKCODE + "已经发送到上一级待审批"); return true; } else { return false; } } } /// /// 审批事务 /// public bool ApproveTask() { string strupdate = "select * from TaskLine where " + "( TaskCode='" + TASKCODE +
"' and UserID=" + USERID + " and Result is null and TurnTo='1') or" + " (UserID=" + USERID + " and Result='N' and TurnTo='1' and TaskCode='" + USERID + "')"; if (DataAccess.DataAccessSHDB.Login(strupdate)) { SqlParameter[] parms = new SqlParameter[5]; parms[0]
= new SqlParameter("@ApproveUserID", SqlDbType.Int); parms[1] = new SqlParameter("@TaskCode", SqlDbType.VarChar, 11); parms[2] = new SqlParameter("@Result", SqlDbType.VarChar, 11); parms[3] = new SqlParameter("@Ref", SqlDbType.NVarChar, 30); parms[4] = new
SqlParameter("@BackLevel", SqlDbType.NChar, 1); parms[0].Value = USERID; parms[1].Value = TASKCODE; parms[2].Value = RESULT; parms[3].Value = REF; parms[4].Value = BACKLEVEL; if (DataAccess.DataAccessSHDB.RunProc(_proc_approvetask, parms) != -1) { DataAccess.MessageObject.ShowLocation("单据号"
+ TASKCODE + "操作成功"); return true; } else { DataAccess.MessageObject.ShowLocation("单据号" + TASKCODE + "操作成功"); return false; } } else { DataAccess.MessageObject.ShowLocation("单据号" +TASKCODE + "已经发送过/或审批过无权变更"); return false; } } /// /// 发送与变更权限 /// public bool
Approve_authority() { string strupdate = "select * from TaskLine where " + "( TaskCode='" + TASKCODE + "' and UserID=" + USERID + " and Result is null and TurnTo='1') or" + " (UserID=" + USERID + " and Result='N' and TurnTo='1' and TaskCode='" + USERID + "')";
if (DataAccess.DataAccessSHDB.Login(strupdate)) { return true; } else { DataAccess.MessageObject.ShowLocation("单据号" +TASKCODE + "已经发送过/或审批过无权变更"); return false; } } /// /// 审批按钮权限 /// public void Btn_Approve_authority(Button But_Confirm) { if (DataAccess.DataAccessSHDB.Login("select
* from FlowLine where TranType='A' and UserID=" +USERID) && DataAccess.DataAccessSHDB.Login("Select * from TaskLine Where Result is null and UserID=" + USERID + "and TaskCode='" +TASKCODE + "'")) { But_Confirm.Enabled = true; } else { But_Confirm.Enabled =
false; } } /// /// 审批按钮权限 /// public void Btn_Send_authority(Button Btn_Send) { if (DataAccess.DataAccessSHDB.Login("select * from FlowLine where TranType='W' and UserID=" + USERID) && DataAccess.DataAccessSHDB.Login("Select * from TaskLine Where Result ='N'
and UserID=" + USERID + "and TaskCode='" + TASKCODE + "'")) { Btn_Send.Enabled = true; } else { Btn_Send.Enabled = false; } } }