Zmiany pomiędzy wersją 60 and wersją 61 dla DeployerGuide/Customization/ProcessAutomation/Examples

Pokaż
Ignoruj:
Data i czas:
11/15/16 00:27:59 (8 years temu)
Autor:
JP (IP: 95.160.158.108)
Komentarz:

--

Legend:

Bez zmian
Dodane
Usunięte
Zmienione
  • DeployerGuide/Customization/ProcessAutomation/Examples

    v60 v61  
    156156 
    157157-- 
    158 -- P8. Przypisanie orunid w zależności od accdid (jednostki org. w zależności od typu dokumentu księgowego) 
     158-- P8. Przypisanie osoby wybranej w liście wyboru (lista typu select z wartościami orunid, przypisana do zmiennej typu Integer) 
     159SELECT array[orunid] FROM organization_units WHERE orunid = {$DYREKTOR_HANDLOWY} 
     160 
     161-- 
     162-- P9. Przypisanie orunid w zależności od accdid (jednostki org. w zależności od typu dokumentu księgowego) 
    159163SELECT  
    160164CASE WHEN v.accdid = 1 THEN [62]  
     
    166170FROM vatnote v  WHERE v.doc_id = {DOC_ID} 
    167171 
    168 -- P9. Przypisanie osoby, która załatwiła poprzedni etap 
     172-- P10. Przypisanie osoby, która załatwiła poprzedni etap 
    169173SELECT ARRAY[s.orunid]  
    170174FROM stages s  
     
    172176WHERE s.is_fix IS TRUE AND s.procid = {procedures.procid} AND wl.chloid = (SELECT max(chloid) FROM workflow_log WHERE procid = s.procid) 
    173177 
    174 -- P10 
    175 Przypisanie osób do etapu z tablicy VAR_OSOBY_OPISUJACE, które nie są w tablicy VAR_OSOBY_FIXED. 
     178-- P11 
     179-- Przypisanie osób do etapu z tablicy VAR_OSOBY_OPISUJACE, które nie są w tablicy VAR_OSOBY_FIXED. 
    176180 
    177181SELECT array( 
     
    182186) 
    183187 
    184 -- P11 
    185 Przypisanie osób z określonej grupy (grp_id = 9) 
     188-- P12 
     189-- Przypisanie osób z określonej grupy (grp_id = 9) 
    186190SELECT ARRAY(SELECT o.orunid FROM orgtree_view o WHERE o.orunid IN (SELECT o.orunid FROM users_link_group ulg INNER JOIN orgtree_view o ON o.usr_id = ulg.usr_id AND ulg.grp_id = 2)) 
    187191 
    188192-- 
    189 P12 
    190 Przypisanie wartości cechy - pracownik 
     193-- P13 
     194-- Przypisanie wartości cechy - pracownik 
    191195 
    192196SELECT ARRAY[o.orunid] 
     
    384388}}} 
    385389== Zapytanie dla "moich zadań workflow" == 
    386 === dla wersji < 5.0 === 
    387 {{{ 
    388 #!sql 
    389 SELECT keyval, 
    390        dscrpt, 
    391        clsnam, 
    392        ptstnm, 
    393        dctpid, 
    394        prtpnm, 
    395        end___, 
    396        ptstid 
    397 FROM procedures_def 
    398 RIGHT JOIN ( 
    399     SELECT (doc_id) AS keyval, 
    400             p2.prtpid, 
    401             substr(d.dscrpt, 0, 100) AS dscrpt, 
    402             'DOCUMENT' AS clsnam, 
    403             end___, 
    404             s.ptstnm, 
    405             d.dctpid, 
    406             ptstid, 
    407             orgarr 
    408      FROM procedures pd 
    409      LEFT JOIN stages s USING(procid) 
    410      LEFT JOIN procedures p2 ON (p2.procid = pd.rootpr) 
    411      LEFT JOIN bpm_loops_def bld USING(ptstid) 
    412      RIGHT JOIN documents d ON (d.procid = p2.procid) 
    413      LEFT JOIN ( 
    414         SELECT doc_id, text_sum(attrib::text) AS attrib 
    415         FROM ( 
    416             SELECT * 
    417             FROM ( 
    418                 SELECT doc_id, coalesce(attrib, '-'::text) AS attrib, CASE WHEN grp_id IS NULL THEN -1 ELSE ul.prior_ END AS prior_, CASE WHEN dlu.usr_id IS NULL THEN ul.usr_id ELSE dlu.usr_id END AS usr_id 
    419                 FROM doc_link_users dlu 
    420                 LEFT JOIN users_link_group ul USING(grp_id) 
    421                 WHERE TRUE AND (dlu.usr_id = 2 OR ul.usr_id = 2) 
    422             ) AS foo 
    423             ORDER BY usr_id, prior_ ASC 
    424         ) AS foo2 
    425         GROUP BY doc_id 
    426     ) AS dolu USING (doc_id) 
    427      WHERE (bld.multii IS NOT TRUE OR s.prn_id IS NOT NULL) 
    428          AND s.ptsttp != 'SUBPROCESS' 
    429          AND ((dolu.attrib ~ '^r') OR (dolu.attrib IS NULL AND (prionl IS NOT TRUE OR d.adduid = 2))) 
    430          AND ((ARRAY[1] && s.orgarr) OR (s.orgarr IS NULL AND d.target = ANY(ARRAY[1]))) AND d.is_del IS NOT TRUE AND gostof IS NULL AND sop_id IS NOT NULL AND s.is_act IS TRUE AND pd.comple IS NOT TRUE AND ptsttp != 'START'  
    431           
    432 UNION  
    433     SELECT (prc_id) AS keyval, 
    434            p2.prtpid, 
    435            substr(p.dscrpt, 0, 100) AS dscrpt, 
    436            'PROCESS' AS clsnam, 
    437            end___, 
    438            s.ptstnm, 
    439            0 AS dctpid, 
    440            ptstid, 
    441            orgarr 
    442     FROM procedures pd 
    443     LEFT JOIN stages s USING(procid) 
    444     LEFT JOIN procedures p2 ON (p2.procid = pd.rootpr) 
    445     LEFT JOIN bpm_loops_def bld USING(ptstid) 
    446     RIGHT JOIN processes p ON (p.procid = p2.procid) 
    447     WHERE (bld.multii IS NOT TRUE 
    448            OR s.prn_id IS NOT NULL) 
    449         AND s.ptsttp != 'SUBPROCESS' 
    450         AND p.is_fix IS FALSE 
    451         AND p.is_del IS FALSE 
    452         AND (ARRAY[1] && s.orgarr) 
    453         AND sop_id IS NOT NULL 
    454         AND s.is_act IS TRUE 
    455         AND pd.comple IS NOT TRUE 
    456         AND ptsttp != 'START' 
    457 UNION  
    458     SELECT (rcp_id) AS keyval, 
    459            p2.prtpid, 
    460            substr(p.dscrpt, 0, 100) AS dscrpt, 
    461            'RCP' AS clsnam, 
    462            end___, 
    463            s.ptstnm, 
    464            0 AS dctpid, 
    465            ptstid, 
    466            orgarr 
    467     FROM procedures pd 
    468     LEFT JOIN stages s USING(procid) 
    469     LEFT JOIN procedures p2 ON (p2.procid = pd.rootpr) 
    470     LEFT JOIN bpm_loops_def bld USING(ptstid) 
    471     RIGHT JOIN rcp_cards p ON (p.procid = p2.procid) 
    472     WHERE (bld.multii IS NOT TRUE 
    473            OR s.prn_id IS NOT NULL) 
    474         AND s.ptsttp != 'SUBPROCESS' 
    475         AND p.is_fix IS FALSE 
    476         AND p.is_del IS FALSE 
    477         AND ((ARRAY[1] && s.orgarr) 
    478              OR (s.orgarr IS NULL 
    479                  AND p.emp_id = 2)) 
    480         AND sop_id IS NOT NULL 
    481         AND s.is_act IS TRUE 
    482         AND pd.comple IS NOT TRUE 
    483         AND ptsttp != 'START' 
    484 ) AS aa USING(prtpid) 
    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 
    486 }}} 
    487  
    488 zapytanie dla wszystkich zadań workflow z modułu Dokumenty: 
    489 {{{ 
    490 #!sql 
    491 SELECT doc.*, reg.regtyp,reg.ndenam, type.dctptp,type.dctpnm,type.dctpic,  
    492     (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,  
    493     (EXISTS (SELECT 1 FROM documents_history WHERE doc_id=doc.doc_id AND orunid = 1 AND strpos(dscrpt, 'Otwarcie')=1)) as is_ack  
    494 FROM documents doc  
    495 INNER JOIN ( 
    496     SELECT doc_id, (array_agg(attrib))[1] as attrib  
    497     FROM ( 
    498         SELECT documents_view.doc_id, (CASE WHEN dlu_doc_id IS NULL THEN NULL ELSE coalesce(dlu.attrib, '') END) as attrib  
    499         FROM documents documents_view  
    500         LEFT JOIN storage_places stp using(strpid)  
    501         LEFT JOIN ( 
    502             SELECT dlu.doc_id as dlu_doc_id, dlu.attrib, dlu.usr_id, ul.prior_  
    503             FROM doc_link_users dlu  
    504             LEFT JOIN users_link_group ul on(ul.grp_id = dlu.grp_id AND ul.usr_id = 2) 
    505             WHERE dlu.usr_id = 2 OR ul.usr_id = 2 
    506         ) dlu ON (dlu_doc_id = documents_view.doc_id) 
    507         WHERE is_del IS NOT TRUE AND gostof IS NULL  
    508             AND procid IN ( 
    509                 SELECT p.rootpr  
    510                 FROM procedures p  
    511                 LEFT JOIN stages s USING(procid)  
    512                 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 
    513             )  
    514             AND is_iso IS FALSE AND partof IS NULL ORDER BY dlu_doc_id, dlu.usr_id IS NOT NULL DESC, dlu.prior_ ASC  
    515     ) foo2 
    516     GROUP BY doc_id 
    517 ) dolu USING (doc_id)  
    518 LEFT JOIN types_of_documents type USING(dctpid)  
    519 LEFT JOIN registers reg USING(reg_id)  
    520 WHERE (substr(dolu.attrib, 1, 1) = 'r') OR (dolu.attrib IS NULL AND (prionl IS NOT TRUE OR doc.adduid = 2)) 
    521 ORDER BY timest DESC,doc_id DESC 
    522 }}} 
    523  
    524  
    525 === dla wersji >= 5.0 === 
     390 
     391 
     392=== Zapytanie dla moich zadań workflow (v>5) === 
    526393 
    527394{{{ 
     
    681548[[BR]] 
    682549 
     550=== Zapytanie dla wszystkich zadań workflow z modułu Dokumenty: === 
     551{{{ 
     552#!sql 
     553SELECT doc.*, reg.regtyp,reg.ndenam, type.dctptp,type.dctpnm,type.dctpic,  
     554    (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,  
     555    (EXISTS (SELECT 1 FROM documents_history WHERE doc_id=doc.doc_id AND orunid = 1 AND strpos(dscrpt, 'Otwarcie')=1)) as is_ack  
     556FROM documents doc  
     557INNER JOIN ( 
     558    SELECT doc_id, (array_agg(attrib))[1] as attrib  
     559    FROM ( 
     560        SELECT documents_view.doc_id, (CASE WHEN dlu_doc_id IS NULL THEN NULL ELSE coalesce(dlu.attrib, '') END) as attrib  
     561        FROM documents documents_view  
     562        LEFT JOIN storage_places stp using(strpid)  
     563        LEFT JOIN ( 
     564            SELECT dlu.doc_id as dlu_doc_id, dlu.attrib, dlu.usr_id, ul.prior_  
     565            FROM doc_link_users dlu  
     566            LEFT JOIN users_link_group ul on(ul.grp_id = dlu.grp_id AND ul.usr_id = 2) 
     567            WHERE dlu.usr_id = 2 OR ul.usr_id = 2 
     568        ) dlu ON (dlu_doc_id = documents_view.doc_id) 
     569        WHERE is_del IS NOT TRUE AND gostof IS NULL  
     570            AND procid IN ( 
     571                SELECT p.rootpr  
     572                FROM procedures p  
     573                LEFT JOIN stages s USING(procid)  
     574                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 
     575            )  
     576            AND is_iso IS FALSE AND partof IS NULL ORDER BY dlu_doc_id, dlu.usr_id IS NOT NULL DESC, dlu.prior_ ASC  
     577    ) foo2 
     578    GROUP BY doc_id 
     579) dolu USING (doc_id)  
     580LEFT JOIN types_of_documents type USING(dctpid)  
     581LEFT JOIN registers reg USING(reg_id)  
     582WHERE (substr(dolu.attrib, 1, 1) = 'r') OR (dolu.attrib IS NULL AND (prionl IS NOT TRUE OR doc.adduid = 2)) 
     583ORDER BY timest DESC,doc_id DESC 
     584}}} 
    683585 
    684586