| | 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 | |
| 646 | | zapytanie dla wszystkich zadań workflow z modułu Dokumenty: |
| 647 | | {{{ |
| 648 | | #!sql |
| 649 | | SELECT doc.*, reg.regtyp,reg.ndenam, type.dctptp,type.dctpnm,type.dctpic, |
| 650 | | (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, |
| 651 | | (EXISTS (SELECT 1 FROM documents_history WHERE doc_id=doc.doc_id AND orunid = 1 AND strpos(dscrpt, 'Otwarcie')=1)) as is_ack |
| 652 | | FROM documents doc |
| 653 | | INNER JOIN ( |
| 654 | | SELECT doc_id, (array_agg(attrib))[1] as attrib |
| 655 | | FROM ( |
| 656 | | SELECT documents_view.doc_id, (CASE WHEN dlu_doc_id IS NULL THEN NULL ELSE coalesce(dlu.attrib, '') END) as attrib |
| 657 | | FROM documents documents_view |
| 658 | | LEFT JOIN storage_places stp using(strpid) |
| 659 | | LEFT JOIN ( |
| 660 | | SELECT dlu.doc_id as dlu_doc_id, dlu.attrib, dlu.usr_id, ul.prior_ |
| 661 | | FROM doc_link_users dlu |
| 662 | | LEFT JOIN users_link_group ul on(ul.grp_id = dlu.grp_id AND ul.usr_id = 2) |
| 663 | | WHERE dlu.usr_id = 2 OR ul.usr_id = 2 |
| 664 | | ) dlu ON (dlu_doc_id = documents_view.doc_id) |
| 665 | | WHERE is_del IS NOT TRUE AND gostof IS NULL |
| 666 | | AND procid IN ( |
| 667 | | SELECT p.rootpr |
| 668 | | FROM procedures p |
| 669 | | LEFT JOIN stages s USING(procid) |
| 670 | | 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 |
| 671 | | ) |
| 672 | | AND is_iso IS FALSE AND partof IS NULL ORDER BY dlu_doc_id, dlu.usr_id IS NOT NULL DESC, dlu.prior_ ASC |
| 673 | | ) foo2 |
| 674 | | GROUP BY doc_id |
| 675 | | ) dolu USING (doc_id) |
| 676 | | LEFT JOIN types_of_documents type USING(dctpid) |
| 677 | | LEFT JOIN registers reg USING(reg_id) |
| 678 | | WHERE (substr(dolu.attrib, 1, 1) = 'r') OR (dolu.attrib IS NULL AND (prionl IS NOT TRUE OR doc.adduid = 2)) |
| 679 | | ORDER BY timest DESC,doc_id DESC |
| 680 | | }}} |
