| 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) === |
| | 550 | === Zapytanie dla wszystkich zadań workflow z modułu Dokumenty: === |
| | 551 | {{{ |
| | 552 | #!sql |
| | 553 | SELECT 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 |
| | 556 | FROM documents doc |
| | 557 | INNER 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) |
| | 580 | LEFT JOIN types_of_documents type USING(dctpid) |
| | 581 | LEFT JOIN registers reg USING(reg_id) |
| | 582 | WHERE (substr(dolu.attrib, 1, 1) = 'r') OR (dolu.attrib IS NULL AND (prionl IS NOT TRUE OR doc.adduid = 2)) |
| | 583 | ORDER BY timest DESC,doc_id DESC |
| | 584 | }}} |
