Zmiany pomiędzy wersją 62 and wersją 63 dla DeployerGuide/Customization/ProcessAutomation/Examples

Data i czas:

02/06/17 16:01:44 (8 years temu)

Autor:

MK (IP: 83.144.106.170)

Komentarz:

--

DeployerGuide/Customization/ProcessAutomation/Examples

@@ -426 +426 @@
         ) 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)
+SELECT keyval, dscrpt, clsnam, ptstnm, dctpid, prtpnm, end___, ptstid, prior_, prtpid 
+FROM (
+    SELECT keyval, dscrpt, clsnam, ptstnm, dctpid, prtpnm, end___, ptstid, aa.prior_, pd.prtpid, orgarr IS NOT NULL as orgarr, first_value(actdat) OVER (PARTITION BY ptstid ORDER BY actdat ASC) as actdat
+    FROM procedures_def pd
+    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 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 = {USR_ID}
+            AND (
+                ((SELECT val FROM all_orunids) && s.orgarr)
+                OR (
+                    s.orgarr IS NULL
+                    AND ARRAY[d.target] <@ (SELECT val FROM all_orunids)
+                    )
                 )
-            )
-        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')
+            AND NOT d.is_del
+            AND gostof IS NULL
+            AND sop_id IS NOT NULL
+            AND s.is_act
+            AND NOT (p2.comple OR p2.cancel)
+            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 NOT (p2.comple OR p2.cancel)
+            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 = {USR_ID}
+                    )
+                )
+            AND sop_id IS NOT NULL
+            AND s.is_act IS TRUE
+            AND NOT (p2.comple OR p2.cancel)
+            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
+) res
+GROUP BY prtpid, keyval, dscrpt, clsnam, ptstnm, dctpid, prtpnm, end___, ptstid, prior_, orgarr, actdat
+ORDER BY prtpid, ptstid, (end___ IS NOT NULL AND orgarr) DESC, end___ IS NOT NULL DESC, end___ ASC, actdat ASC, orgarr DESC, dscrpt ASC
 }}}