簽核流程設置

2024-03-21 02:08
文章标签 流程 設置 簽核

本文主要是介绍簽核流程設置，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

/*
---游標處理批量流程---------
---步驟1,建立表--檢查ProjectCode是否帶有特殊字符
---步驟2---檢查所有簽核人帳號是否存在---
--檢查導入簽核人是否正確（主要判斷數據是否為NULL）
select distinct [級數] from dbo.[zlq_PCO1107]
select *  from dbo.[zlq_PCO1107] where [級數]=1 and ([一級簽核] is NULL)
select *  from dbo.[zlq_PCO1107] where [級數]=2 and ([二級簽核] is NULL or [一級簽核] is NULL)
select *  from dbo.[zlq_PCO1107] where [級數]=3 and ([三級簽核] is NULL or [二級簽核] is NULL or [一級簽核] is NULL)
select *  from dbo.[zlq_PCO1107] where [級數]=4 and ([四級簽核] is NULL or [三級簽核] is NULL or [二級簽核] is NULL or [一級簽核] is NULL)
select *  from dbo.[zlq_PCO1107] where [級數]=5 and ([五級簽核] is NULL or [四級簽核] is NULL or [三級簽核] is NULL or [二級簽核] is NULL or [一級簽核] is NULL)
select *  from dbo.[zlq_PCO1107] where [級數]=6 and ([六級簽核] is NULL or [五級簽核] is NULL or [四級簽核] is NULL or [三級簽核] is NULL or [二級簽核] is NULL or [一級簽核] is NULL)
select *  from dbo.[zlq_PCO1107] where [級數]=7 and ([七級簽核] is NULL or [六級簽核] is NULL or [五級簽核] is NULL or [四級簽核] is NULL or [三級簽核] is NULL or [二級簽核] is NULL or [一級簽核] is NULL)
select *  from dbo.[zlq_PCO1107] where [級數]=8 and ([八級簽核] is NULL or [七級簽核] is NULL or [六級簽核] is NULL or [五級簽核] is NULL or [四級簽核] is NULL or [三級簽核] is NULL or [二級簽核] is NULL or [一級簽核] is NULL)

---檢查是否存在于表dbo.PCO_CheckMan中-----      

----首先帳號是否存在于表dbo.PCO_CheckMan中---
----其次真實帳號是否存在于表dbo.Ac_BRM_User中---
select a.[一級簽核],b.Username,c.CheckManID from (select distinct [一級簽核] from dbo.[zlq_PCO1107]) a left join dbo.Ac_BRM_User  b on b.realname=a.[一級簽核] left join dbo.PCO_CheckMan c on c.UserName=b.Username
select a.[二級簽核],b.Username,c.CheckManID from (select distinct [二級簽核] from dbo.[zlq_PCO1107]) a left join dbo.Ac_BRM_User  b on b.realname=a.[二級簽核] left join dbo.PCO_CheckMan c on c.UserName=b.Username
select a.[三級簽核],b.Username,c.CheckManID from (select distinct [三級簽核] from dbo.[zlq_PCO1107]) a left join dbo.Ac_BRM_User  b on b.realname=a.[三級簽核] left join dbo.PCO_CheckMan c on c.UserName=b.Username
select a.[四級簽核],b.Username,c.CheckManID from (select distinct [四級簽核] from dbo.[zlq_PCO1107]) a left join dbo.Ac_BRM_User  b on b.realname=a.[四級簽核] left join dbo.PCO_CheckMan c on c.UserName=b.Username
select a.[五級簽核],b.Username,c.CheckManID from (select distinct [五級簽核] from dbo.[zlq_PCO1107]) a left join dbo.Ac_BRM_User  b on b.realname=a.[五級簽核] left join dbo.PCO_CheckMan c on c.UserName=b.Username
select a.[六級簽核],b.Username,c.CheckManID from (select distinct [六級簽核] from dbo.[zlq_PCO1107]) a left join dbo.Ac_BRM_User  b on b.realname=a.[六級簽核] left join dbo.PCO_CheckMan c on c.UserName=b.Username
select a.[七級簽核],b.Username,c.CheckManID from (select distinct [七級簽核] from dbo.[zlq_PCO1107]) a left join dbo.Ac_BRM_User  b on b.realname=a.[七級簽核] left join dbo.PCO_CheckMan c on c.UserName=b.Username
select a.[八級簽核],b.Username,c.CheckManID from (select distinct [八級簽核] from dbo.[zlq_PCO1107]) a left join dbo.Ac_BRM_User  b on b.realname=a.[八級簽核] left join dbo.PCO_CheckMan c on c.UserName=b.Username
以上語句執行結果中，在簽核人，UserName，CheckManID不同時為空的情況下，有以下兩種情況：
如果UserName欄位為空，說明對應的簽核人在系統中不存在帳號，則暫停設置流程，請相關簽核人申請帳號后再做處理
如果UserName不為空，而CheckManID欄位為空，則說明對應的簽核人在系統中存在帳號，但是在表PCO_CheckMan中不存在帳號，則需要將帳號放入PCO_CheckMan中


--步驟3---建立游標----------
declare Step_cur scroll cursor for
select [流程號],[級數] from [zlq_PCO1107]
open Step_cur
declare @StartID int,@Step int
fetch first from Step_cur into @StartID,@Step
while @@fetch_status=0
begin
 print convert(varchar(30),@StartID)+','+convert(varchar(30),@Step)
 exec dbo.ljj_SetWorkFlow @StartID,@Step
 fetch next from Step_cur into @StartID,@Step
end
close Step_cur
deallocate Step_cur
------------------------------------------
--delete dbo.PCO_WorkFlowStep where stepid in(select distinct StepID from dbo.PCO_WorkFlowStep where StepID>=219320 and StepID<=229543)
select a.StepID,b.[流程號]
from dbo.PCO_WorkFlowStep a
left join dbo.[zlq_PCO1107] b on a.StepID=b.[流程號]
where StepID>=229540 and StepID<=229549
exec dbo.ljj_SetWorkFlow 229540,4
以上這些語句是在導入數據且執行建立游標后，在設置簽核條件這一步驟時出現錯誤的補救方法
--步驟4---設置簽核條件----------
select * from dbo.zlq_PCO1107 where uprange='+∞'

update [zlq_PCO1107]
set [UpRange]=8888 where uprange='+∞'

select * from dbo.zlq_PCO1107 where downrange='-∞'

update [zlq_PCO1107]
set [DownRange]=-8888 where downrange='-∞'


INSERT INTO [QBRClient].[dbo].[PCO_CheckCondition]
   ([ProjectCode], [CommodityCode], [UpRange], [DownRange], [SupplierFlag], [StepID], [Remark])
SELECT [ProjectCode], [Commoditycode],convert(decimal(38,6),[UpRange]),convert(decimal(38,6),[DownRange]), [內外交或其它],[流程號], [級數]
FROM [QBRClient].[dbo].[zlq_PCO1107]

select * from dbo.PCO_Checkcondition where UpRange=8888
update dbo.PCO_Checkcondition
set UpRange=99999999999999999999999999999999
where UpRange=8888

select * from dbo.PCO_Checkcondition where DownRange=-8888
update dbo.PCO_Checkcondition
set DownRange=-99999999999999999999999999999999
where DownRange=-8888

--步驟5---設置簽核帳號----------
INSERT INTO [QBRClient].[dbo].[PCO_WorkFlowChecker]([StepID]  , [CheckManID])
 select [流程號],(select CheckmanID from dbo.PCO_CheckMan
                     where UserName=(select UserName from dbo.member where  Isvalid=1 and Realname=[一級簽核]))
  from dbo.[zlq_PCO1107] where  [一級簽核] is not null

INSERT INTO [QBRClient].[dbo].[PCO_WorkFlowChecker]([StepID]  , [CheckManID])
 select [流程號]+1,(select CheckmanID from dbo.PCO_CheckMan
                     where UserName=(select UserName from dbo.member where  Isvalid=1 and Realname=[二級簽核]))
  from dbo.[zlq_PCO1107] where  [二級簽核] is not null


INSERT INTO [QBRClient].[dbo].[PCO_WorkFlowChecker]([StepID]  , [CheckManID])
 select [流程號]+2,(select CheckmanID from dbo.PCO_CheckMan
                     where UserName=(select UserName from dbo.member where  Isvalid=1 and Realname=[三級簽核]))
  from dbo.[zlq_PCO1107] where  [三級簽核] is not null

INSERT INTO [QBRClient].[dbo].[PCO_WorkFlowChecker]([StepID]  , [CheckManID])
 select [流程號]+3,(select CheckmanID from dbo.PCO_CheckMan
                     where UserName=(select UserName from dbo.member where  Isvalid=1 and Realname=[四級簽核]))
  from dbo.[zlq_PCO1107] where  [四級簽核] is not null

INSERT INTO [QBRClient].[dbo].[PCO_WorkFlowChecker]([StepID]  , [CheckManID])
 select [流程號]+4,(select CheckmanID from dbo.PCO_CheckMan
                     where UserName=(select UserName from dbo.member where  Isvalid=1 and Realname=[五級簽核]))
  from dbo.[zlq_PCO1107] where  [五級簽核] is not null

INSERT INTO [QBRClient].[dbo].[PCO_WorkFlowChecker]([StepID]  , [CheckManID])
 select [流程號]+5,(select CheckmanID from dbo.PCO_CheckMan
                     where UserName=(select UserName from dbo.member where  Isvalid=1 and Realname=[六級簽核]))
  from dbo.[zlq_PCO1107] where  [六級簽核] is not null

INSERT INTO [QBRClient].[dbo].[PCO_WorkFlowChecker]([StepID]  , [CheckManID])
 select [流程號]+6,(select CheckmanID from dbo.PCO_CheckMan
                     where UserName=(select UserName from dbo.member where  Isvalid=1 and Realname=[七級簽核]))
  from dbo.[zlq_PCO1107] where  [七級簽核] is not null

INSERT INTO [QBRClient].[dbo].[PCO_WorkFlowChecker]([StepID]  , [CheckManID])
 select [流程號]+7,(select CheckmanID from dbo.PCO_CheckMan
                     where UserName=(select UserName from dbo.member where  Isvalid=1 and Realname=[八級簽核]))
  from dbo.[zlq_PCO1107] where  [八級簽核] is not null

--步驟6----檢查同一種條件下是否存在一人簽多次的流程-------------
select * from dbo.PCO_WorkFlowChecker
where id in(
 select a.id from dbo.PCO_WorkFlowChecker a (nolock)
 inner join dbo.PCO_WorkFlowChecker b (nolock) on a.checkmanid=b.checkmanid
 and a.stepid+1=b.stepid
 )
---步驟7--------------檢查PCO流程設置是否斷點-----------------------

根據PCO簽核條件表中STEPID不為0的記錄數﹐去跟簽核流程表(PCO_WorkFlowStep)
中的最后一級簽核步驟數(nextID為0的步驟)比較﹐如果相同﹐則說明無斷點﹐可
簽核流程可走通。

select count(*) from dbo.PCO_WorkFlowStep
where stepid in
(
select a.stepid+c.Remark-1 from dbo.PCO_WorkFlowChecker a (nolock)
--inner join dbo.PCO_WorkFlowStep b (nolock) on a.stepid=b.stepid
inner join dbo.PCO_CheckCondition c (nolock) on  a.stepid=c.stepid
)
and nextid=0

---------步驟8-------簽核人在簽核流程里是否正確---------
select * from dbo.PCO_WorkFlowStep (nolock)
where StepID>=229550 and StepID<=229550
*/
CREATE                     proc dbo.ljj_SetWorkFlow
@SetpID int,
@Level  int
as
declare @err varchar(200)
declare @i   int

if exists(select * from dbo.PCO_WorkFlowStep where StepID>=@SetpID and StepID<=(@SetpID+@Level))
begin
 select @err='您設置的流程SetpID='+convert(varchar(30),@SetpID)+',簽核層級='+convert(varchar(30),@Level)+' 在系統中已經存在,無法繼續設置簽核流程!'
 ----print @err
 raiserror(@err,16,1)
 return 111
end
--第一級---------------------------
--print '--第一級---------------------------'
--print @SetpID
if @Level=1
begin
 insert into dbo.PCO_WorkFlowStep
    ( StepID,PreID,NextID   )
 values(@SetpID,  0  ,0       )
end
if @Level>1
begin
 insert into dbo.PCO_WorkFlowStep
    ( StepID,PreID,NextID   )
 values(@SetpID,  0  ,@SetpID+1)
end

--第二級到倒數第二級---------------
--print '--第二級到倒數第二級----------------------'
select @i=(@SetpID+1)
while @i<(@SetpID+@Level-1)
begin
 --print @i
 insert into dbo.PCO_WorkFlowStep
    (StepID,PreID,NextID)
 values( @i   ,@i-1 ,@i+1  )
 select @i=@i+1
end
--最後一級-------------------------
--print '--最後一級---------------------------'
--print @SetpID+@Level-1
if @Level>1
begin
 insert into dbo.PCO_WorkFlowStep
    (  StepID  ,  PreID   ,NextID)
 values(@SetpID+@Level-1,@SetpID+@Level-2,0     )
end

这篇关于簽核流程設置的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

原文地址:
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.chinasem.cn/article/831459。

相关文章

Java对接Dify API接口的完整流程

Java对接Dify API接口的完整流程

《Java对接DifyAPI接口的完整流程》Dify是一款AI应用开发平台,提供多种自然语言处理能力,通过调用Dify开放API,开发者可以快速集成智能对话、文本生成等功能到自己的Java应用中,本... 目录Java对接Dify API接口完整指南一、Dify API简介二、准备工作三、基础对接实现1.
阅读更多...
将Java项目提交到云服务器的流程步骤

将Java项目提交到云服务器的流程步骤

《将Java项目提交到云服务器的流程步骤》所谓将项目提交到云服务器即将你的项目打成一个jar包然后提交到云服务器即可,因此我们需要准备服务器环境为：Linux+JDK+MariDB（MySQL）+Gi... 目录1. 安装 jdk1.1 查看 jdk 版本1.2 下载 jdk2. 安装 mariadb(my
阅读更多...
Spring AI ectorStore的使用流程

Spring AI ectorStore的使用流程

《SpringAIectorStore的使用流程》SpringAI中的VectorStore是一种用于存储和检索高维向量数据的数据库或存储解决方案,它在AI应用中发挥着至关重要的作用,本文给大家介... 目录一、VectorStore的基本概念二、VectorStore的核心接口三、VectorStore的
阅读更多...
python之流程控制语句match-case详解

python之流程控制语句match-case详解

《python之流程控制语句match-case详解》：本文主要介绍python之流程控制语句match-case使用,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐... 目录match-case 语法详解与实战一、基础值匹配（类似 switch-case）二、数据结构解构匹
阅读更多...
在VSCode中本地运行DeepSeek的流程步骤

在VSCode中本地运行DeepSeek的流程步骤

《在VSCode中本地运行DeepSeek的流程步骤》本文详细介绍了如何在本地VSCode中安装和配置Ollama和CodeGPT,以使用DeepSeek进行AI编码辅助,无需依赖云服务,需要的朋友可... 目录步骤 1：在 VSCode 中安装 Ollama 和 CodeGPT安装Ollama下载Olla
阅读更多...
linux环境openssl、openssh升级流程

linux环境openssl、openssh升级流程

《linux环境openssl、openssh升级流程》该文章详细介绍了在Ubuntu22.04系统上升级OpenSSL和OpenSSH的方法,首先,升级OpenSSL的步骤包括下载最新版本、安装编译... 目录一.升级openssl1.官网下载最新版openssl2.安装编译环境3.下载后解压安装4.备份
阅读更多...
C#集成DeepSeek模型实现AI私有化的流程步骤(本地部署与API调用教程)

C#集成DeepSeek模型实现AI私有化的流程步骤(本地部署与API调用教程)

《C#集成DeepSeek模型实现AI私有化的流程步骤(本地部署与API调用教程)》本文主要介绍了C#集成DeepSeek模型实现AI私有化的方法,包括搭建基础环境,如安装Ollama和下载DeepS... 目录前言搭建基础环境1、安装 Ollama2、下载 DeepSeek R1 模型客户端 ChatBo
阅读更多...
Linux流媒体服务器部署流程

Linux流媒体服务器部署流程

《Linux流媒体服务器部署流程》文章详细介绍了流媒体服务器的部署步骤,包括更新系统、安装依赖组件、编译安装Nginx和RTMP模块、配置Nginx和FFmpeg,以及测试流媒体服务器的搭建... 目录流媒体服务器部署部署安装1.更新系统2.安装依赖组件3.解压4.编译安装（添加RTMP和openssl模块
阅读更多...
0基础租个硬件玩deepseek,蓝耘元生代智算云|本地部署DeepSeek R1模型的操作流程

0基础租个硬件玩deepseek,蓝耘元生代智算云|本地部署DeepSeek R1模型的操作流程

《0基础租个硬件玩deepseek,蓝耘元生代智算云|本地部署DeepSeekR1模型的操作流程》DeepSeekR1模型凭借其强大的自然语言处理能力,在未来具有广阔的应用前景,有望在多个领域发... 目录0基础租个硬件玩deepseek,蓝耘元生代智算云|本地部署DeepSeek R1模型，3步搞定一个应
阅读更多...
springboot启动流程过程

springboot启动流程过程

《springboot启动流程过程》SpringBoot简化了Spring框架的使用,通过创建`SpringApplication`对象,判断应用类型并设置初始化器和监听器,在`run`方法中,读取配... 目录springboot启动流程springboot程序启动入口1.创建SpringApplicat
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2