Zmiany pomiędzy wersją 57 and wersją 58 dla DeployerGuide/Customization/ProcessAutomation/Examples

Data i czas:

10/13/16 13:56:52 (8 years temu)

Autor:

MK (IP: 213.5.203.190)

Komentarz:

--

DeployerGuide/Customization/ProcessAutomation/Examples

@@ -384 +384 @@
 }}}
 == Zapytanie dla "moich zadań workflow" ==
-
-{{{
+=== dla wersji < 5.0 ===
+{{{
+#!sql
 SELECT keyval,
        dscrpt,
@@ -484 +485 @@
 ORDER BY prtpid, ptstid, (end___ IS NOT NULL AND orgarr IS NOT NULL) DESC, end___ IS NOT NULL DESC, end___ ASC, orgarr IS NOT NULL DESC, dscrpt ASC
 }}}
+
+=== dla wersji >= 5.0 ===
+
+{{{
+#!sql
+WITH RECURSIVE user_all_replacements (who___, bywhom, path)
+AS (
+    SELECT r.who___,
+        r.bywhom,
+        array [r.who___]::INT [] AS path
+    FROM replacements r
+    WHERE r.bywhom IN (29)
+        AND (now() BETWEEN r.from__ AND r.to____)
+        AND NOT r.suspen
+    
+    UNION ALL
+    
+    SELECT r.who___,
+        r.bywhom,
+        uar.path || r.who___ AS path
+    FROM replacements r,
+        user_all_replacements uar
+    WHERE NOT (r.who___ = ANY (path))
+        AND r.bywhom = uar.who___
+        AND (now() BETWEEN r.from__ AND r.to____)
+        AND NOT r.suspen
+),
+all_orunids as (
+        SELECT array_agg(who___)||29 as val
+        FROM (
+                SELECT DISTINCT r.who___ FROM user_all_replacements r
+        ) foo
+)
+SELECT keyval,
+    dscrpt,
+    clsnam,
+    ptstnm,
+    dctpid,
+    prtpnm,
+    end___,
+    ptstid,
+    aa.prior_
+FROM procedures_def
+RIGHT JOIN (
+    SELECT (doc_id) AS keyval,
+        p2.prtpid,
+        substr(d.dscrpt, 0, 100) AS dscrpt,
+        'DOCUMENT' AS clsnam,
+        end___,
+        s.ptstnm,
+        d.dctpid,
+        ptstid,
+        orgarr,
+        s.actdat,
+        d.prior_
+    FROM procedures pd
+    LEFT JOIN stages s USING (PROCID)
+    LEFT JOIN procedures p2 ON (p2.PROCID = pd.rootpr)
+    LEFT JOIN bpm_loops_def bld USING (ptstid)
+    RIGHT JOIN documents d ON (d.PROCID = p2.PROCID)
+    WHERE (
+            bld.multii IS NOT TRUE
+            OR s.prn_id IS NOT NULL
+            )
+        AND (
+            ((SELECT val FROM all_orunids) && s.orgarr)
+            OR (
+                s.orgarr IS NULL
+                AND ARRAY[d.target] <@ (SELECT val FROM all_orunids)
+                )
+            )
+        AND NOT d.is_del
+        AND gostof IS NULL
+        AND sop_id IS NOT NULL
+        AND s.is_act
+        AND NOT p2.comple
+        AND ptsttp NOT IN ('SUBPROCESS', 'START', 'END')
+    
+    UNION
+    
+    SELECT (prc_id) AS keyval,
+        p2.prtpid,
+        substr(p.dscrpt, 0, 100) AS dscrpt,
+        'PROCESS' AS clsnam,
+        end___,
+        s.ptstnm,
+        0 AS dctpid,
+        ptstid,
+        orgarr,
+        s.actdat,
+        NULL AS prior_
+    FROM procedures pd
+    LEFT JOIN stages s USING (PROCID)
+    LEFT JOIN procedures p2 ON (p2.PROCID = pd.rootpr)
+    LEFT JOIN bpm_loops_def bld USING (ptstid)
+    RIGHT JOIN processes p ON (p.PROCID = p2.PROCID)
+    WHERE (
+            bld.multii IS NOT TRUE
+            OR s.prn_id IS NOT NULL
+            )
+        AND p.is_fix IS FALSE
+        AND p.is_del IS FALSE
+        AND ((SELECT val FROM all_orunids) && s.orgarr)
+        AND sop_id IS NOT NULL
+        AND s.is_act IS TRUE
+        AND p2.comple IS NOT TRUE
+        AND ptsttp NOT IN ('SUBPROCESS', 'START', 'END')
+    
+    UNION
+    
+    SELECT (rcp_id) AS keyval,
+        p2.prtpid,
+        substr(p.dscrpt, 0, 100) AS dscrpt,
+        'RCP' AS clsnam,
+        end___,
+        s.ptstnm,
+        0 AS dctpid,
+        ptstid,
+        orgarr,
+        s.actdat,
+        NULL AS prior_
+    FROM procedures pd
+    LEFT JOIN stages s USING (PROCID)
+    LEFT JOIN procedures p2 ON (p2.PROCID = pd.rootpr)
+    LEFT JOIN bpm_loops_def bld USING (ptstid)
+    RIGHT JOIN rcp_cards p ON (p.PROCID = p2.PROCID)
+    WHERE (
+            bld.multii IS NOT TRUE
+            OR s.prn_id IS NOT NULL
+            )
+        AND p.is_fix IS FALSE
+        AND p.is_del IS FALSE
+        AND (
+            ((SELECT val FROM all_orunids) && s.orgarr)
+            OR (
+                s.orgarr IS NULL
+                AND p.emp_id = 14
+                )
+            )
+        AND sop_id IS NOT NULL
+        AND s.is_act IS TRUE
+        AND p2.comple IS NOT TRUE
+        AND ptsttp NOT IN ('SUBPROCESS', 'START', 'END')
+    ) AS aa USING (prtpid)
+ORDER BY prtpid,
+    ptstid,
+    (
+        end___ IS NOT NULL
+        AND orgarr IS NOT NULL
+        ) DESC,
+    end___ IS NOT NULL DESC,
+    end___ ASC,
+    actdat ASC,
+    orgarr IS NOT NULL DESC,
+    dscrpt ASC
+}}}
+
 [[BR]]
 
 zapytanie dla wszystkich zadań workflow z modułu Dokumenty:
 {{{
+#!sql
 SELECT doc.*, reg.regtyp,reg.ndenam, type.dctptp,type.dctpnm,type.dctpic, 
     (SELECT ARRAY[count(f.fileid),min(f.fileid)] FROM attachments LEFT JOIN files f USING(fileid) WHERE is_del IS NOT TRUE AND doc_id = doc.doc_id) as files,