How to write sql for document approval process

caoyuezj

2.How to judge that all approvals have been completed
Regarding this, first take out all the departments in '| bmbegin | department | bmend |' in f_spyj to form a total string, and in tbnote_check_order, form all the departments into one line. That means it's all there, how to do it?

fanfan1980

There may be multiple | linebegin |, | lineend | in one line?

caoyuezj

One line here refers to the approval information of a department. There is no limit on the number of characters, that is, the approval information of a department is enclosed by linebegin and lineend, and it should be understood as a pair of ().

caoyuezj

One line is the meaning of a pair of brackets, not a single line of text.A pair of brackets may be one line or multiple lines, and there may be multiple pairs of brackets in a line.

艾丽茜思

Learn

caoyuezj

create table tbnote_check_order
(
f_djlxbm varchar (3) Not Null, --document type encoding
f_xh int Not Null,-
f_bmbm varchar (15) Not Null,-department code
f_must int Not Null, --must (0 no 1 yes)
Constraint tbnote_check_order_key Primary Key (f_djlxbm, f_xh)
)
That is, different documents have different approval business processes. I just requested a business process for a document. Is it so difficult to write?

fanfan1980

Table structure has a problem

toogun

/ *
Design idea: first get the number of all departments that must be reviewed in tbnote_check_order, then decode the approval information, and judge whether the department must be reviewed when it encounters department characters.
        If the conditions are met, it means that one of all the review processes has been met, and at the end of the cycle, it can be known whether the number of required review departments included in the approval information is equal to tbnote_check_order
        Number specified in
Parameter Description:
 @vDjlxbm
  Indicates the type of document to be reviewed
 @vSpInfo
  Incoming approval information string
Return parameters:
 @iIsOver
  0 means no
  1 means all passed
  2 means no approval process
* /
create procedure p_SpIsOver (@vDjlxbm varchar (3), @vSpInfo varchar (8000), @iIsOver int output)
as
begin
     declare @s as varchar (8000)
     declare @i as int
     declare @j as int
     declare @len as int
     declare @bm as varchar (30)
     select @ i = count (f_bmbm) from tbnote_check_order where f_djlxbm = @ vDjlxbm and f_must = 1
     if @i> 0
     begin
       set @ len = len (@vSpInfo)
       if @ len = 0
       begin
         set @ iIsOver = 0
         return
       end
       else
       begin
         while @len> 1
         begin
            if substring (right (@ vSpInfo, @ len), 1, len ('| bmbegin |')) = '| bmbegin |'
            begin
              set @ bm = substring (@ vSpInfo, len (@vSpInfo)-@ len + len ('| bmbegin |') + 1, charindex ('| bmend |', right (@ vSpInfo, @ len))-len (' | bmbegin | ')-1)
              select @ j = count (f_bmbm) from tbnote_check_order where f_djlxbm = @ vDjlxbm and f_must = 1 and f_bmbm = @ bm
              if @ j = 1 set @ i = @ i-1
            end
            set @ len = @ len-1
         end
         if @ i = 0 set @ iIsOver = 1
         else set @ iIsOver = 0
       end
     end
     else
     begin
       set @ iIsOver = 2
       return
     end
end

toogun

--test environment

create table tbnote_check_order
(
f_djlxbm varchar (3) Not Null, --document type encoding
f_xh int Not Null,-
f_bmbm varchar (15) Not Null,-department code
f_must int Not Null, --must (0 no 1 yes)
Constraint tbnote_check_order_key Primary Key (f_djlxbm, f_xh)
)

insert into tbnote_check_order
select 'abc', 1, '001', 1 union all
select 'abc', 2, '002', 0 union all
select 'abc', 3, '004', 1

declare @c int
exec p_SpIsOver 'abc', '| linebegin | haha2007-1-1 | bmbegin | 001 | bmend | hehe123 | ygbmbegin | 001 | ygbmend | sdjklsd | lineend |', @ c out
select @c

--result
0

declare @c int
exec p_SpIsOver 'abc', '| linebegin | haha2007-1-1 | bmbegin | 001 | bmend | hehe123 | ygbmbegin | 001 | ygbmend | sdjklsd | lineend |
| linebegin | haha2007-2-1 | bmbegin | 004 | bmend | hehe123 | ygbmbegin | 001 | ygbmend | sdjkfddfdfssfdlsd | lineend |
', @ c out
select @c
--result
1

declare @c int
exec p_SpIsOver 'abc', '| linebegin | haha2007-1-1 | bmbegin | 001 | bmend | hehe123 | ygbmbegin | 001 | ygbmend | sdjklsd | lineend |
| linebegin | haha2007-2-1 | bmbegin | 002 | bmend | hehe123 | ygbmbegin | 001 | ygbmend | sdjkfddfdfssfdlsd | lineend |
', @ c out
select @c
--result
0

declare @c int
exec p_SpIsOver 'abc', '| linebegin | haha2007-1-1 | bmbegin | 001 | bmend | hehe123 | ygbmbegin | 001 | ygbmend | sdjklsd | lineend |
| linebegin | haha2007-2-1 | bmbegin | 002 | bmend | hehe123 | ygbmbegin | 001 | ygbmend | sdjkfddfdfssfdlsd | lineend |
| linebegin | haha2007-2-1 | bmbegin | 004 | bmend | hehe123 | ygbmbegin | 001 | ygbmend | sdjkfddfdfssfdlsd | lineend |
', @ c out
select @c
--result
1

toogun

declare @c int
exec p_SpIsOver 'aaa', '| linebegin | haha2007-1-1 | bmbegin | 001 | bmend | hehe123 | ygbmbegin | 001 | ygbmend | sdjklsd | lineend |', @ c out
select @c
--result
2