W tym miejscu publikowane będą przykładowe kwerendy użyteczne przy budowaniu warunków, przekazywaniu parametrów do komend itp.

WARUNKI

W14. -- sprawdzenie czy są uzupełnione cechy na kliencie, do którego jest kierowana oferta SELECT x.potrzeba IS NOT NULL AND x.segment IS NOT NULL AND x.zrodlo IS NOT NULL AND x.branza IS NOT NULL FROM (

W15. -- sprawdzenie czy są uzupełnione cechy na fakturze do którego nabywcy (lub odbiorcy jeśli jest uzupełniony) , albo klienta końcowego oznaczonego na cesze faktury jako klient z bazy - jest kierowana faktura - są uzupełnione cechy typ. segment, źródło, branża SELECT x.typ IS NOT NULL AND x.segment IS NOT NULL AND x.zrodlo IS NOT NULL AND x.branza IS NOT NULL FROM ( SELECT

PRZYPISANIA

KWERENDY DO PARAMETRU SELECT[]

DYNAMICZNE WARTOŚCI PARAMETRÓW KOMEND

Zapytania do parametrów Akcji

Różne

Konstrukcje z ANY

Zapytanie dla "moich zadań workflow"

Zapytanie dla moich zadań workflow (v>5)

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 ({ORUNID})
        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___)||{ORUNID} as val
        FROM (
                SELECT DISTINCT r.who___ FROM user_all_replacements r
        ) foo
)
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 (
                ((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 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)
) 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

Zapytanie dla wszystkich zadań workflow z modułu Dokumenty:

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, 
    (EXISTS (SELECT 1 FROM documents_history WHERE doc_id=doc.doc_id AND orunid = 1 AND strpos(dscrpt, 'Otwarcie')=1)) as is_ack 
FROM documents doc 
INNER JOIN (
    SELECT doc_id, (array_agg(attrib))[1] as attrib 
    FROM (
        SELECT documents_view.doc_id, (CASE WHEN dlu_doc_id IS NULL THEN NULL ELSE coalesce(dlu.attrib, '') END) as attrib 
        FROM documents documents_view 
        LEFT JOIN storage_places stp using(strpid) 
        LEFT JOIN (
            SELECT dlu.doc_id as dlu_doc_id, dlu.attrib, dlu.usr_id, ul.prior_ 
            FROM doc_link_users dlu 
            LEFT JOIN users_link_group ul on(ul.grp_id = dlu.grp_id AND ul.usr_id = 2)
            WHERE dlu.usr_id = 2 OR ul.usr_id = 2
        ) dlu ON (dlu_doc_id = documents_view.doc_id)
        WHERE is_del IS NOT TRUE AND gostof IS NULL 
            AND procid IN (
                SELECT p.rootpr 
                FROM procedures p 
                LEFT JOIN stages s USING(procid) 
                WHERE (ARRAY(SELECT orunid FROM orgtree_view LEFT JOIN users_link_org_units ulo USING(orunid) WHERE orunid = 1 OR (ourpid = 1 AND EXISTS(SELECT 1 FROM (SELECT value_ FROM global_sys_conf WHERE ent_id = 2 AND objnam = 'Replacement_'||ulo.usr_id AND varnam = 'from__') as as1 WHERE value_::date <= now()::date) AND EXISTS(SELECT 1 FROM (SELECT value_ FROM global_sys_conf WHERE ent_id = 2 AND objnam = 'Replacement_'||ulo.usr_id AND varnam = 'to____') as sd2 WHERE value_::date >= now()::date))) && s.orgarr) AND s.ptsttp != 'SUBPROCESS' AND sop_id IS NOT NULL AND is_act IS TRUE
            ) 
            AND is_iso IS FALSE AND partof IS NULL ORDER BY dlu_doc_id, dlu.usr_id IS NOT NULL DESC, dlu.prior_ ASC 
    ) foo2
    GROUP BY doc_id
) dolu USING (doc_id) 
LEFT JOIN types_of_documents type USING(dctpid) 
LEFT JOIN registers reg USING(reg_id) 
WHERE (substr(dolu.attrib, 1, 1) = 'r') OR (dolu.attrib IS NULL AND (prionl IS NOT TRUE OR doc.adduid = 2))
ORDER BY timest DESC,doc_id DESC

Przykłady workflow