| 36 | | -- Pobranie danych z formularzy dynamicznych |
| | 36 | -- POBIERANIE DANYCH Z CECH |
| | 37 | -- |
| | 38 | -- Przykład linkowania cech z dokumentu kastomowego o dctpid = 22 |
| | 39 | -- f18, f19, f20 to wartości pól tekstowe a f21 to lista JEDNOKROTNEGO wyboru |
| | 40 | -- |
| | 41 | SELECT |
| | 42 | doc.doc_id AS keyval, |
| | 43 | 'DOCUMENT'::text AS clsnam, |
| | 44 | r.evdnum AS nr, |
| | 45 | u.firnam || ' ' || u.lasnam AS imienazwisko, |
| | 46 | CAST(doc.adddat AS date) AS datawniosku, |
| | 47 | CAST(f18.data__ AS date) AS poczurlop, |
| | 48 | CAST(f20.data__ AS date) AS konurlop, |
| | 49 | CAST(COALESCE(f19.data__,'0') AS int) AS dniurlopu, |
| | 50 | f21.ftopnm AS powod, |
| | 51 | COALESCE(tops.dscrpt, '-') AS status |
| | 52 | FROM documents doc |
| | 53 | LEFT JOIN users u ON u.usr_id = doc.adduid |
| | 54 | LEFT JOIN regofpapers r ON r.doc_id=doc.doc_id |
| | 55 | LEFT JOIN features_text_view f18 ON doc.doc_id = f18.tbl_id AND f1.featid = 18 |
| | 56 | LEFT JOIN features_text_view f19 ON doc.doc_id = f19.tbl_id AND f19.featid = 19 |
| | 57 | LEFT JOIN features_text_view f20 ON doc.doc_id = f20.tbl_id AND f20.featid = 20 |
| | 58 | LEFT JOIN features_opt_view f21 ON doc.doc_id = f21.tbl_id AND f21.featid = 21 |
| | 59 | LEFT JOIN types_of_processes_states tops ON doc.tpstid = tops.tpstid |
| | 60 | WHERE dctpid = 22 AND gostof IS NULL AND doc.is_del IS FALSE |
| | 62 | -- |
| | 63 | -- Przykład sprawy z wartościami cech (tekstowe i opcje) |
| | 64 | -- |
| | 65 | SELECT p.dscrpt, p.symbol, 'PROCESS'::text AS clsnam, prc_id AS keyval, |
| | 66 | f1.data__ AS opinia, f2.ftopnm AS reklamacja, f3.data__ AS dzialanie_korygujace, |
| | 67 | f4.data__ AS dzialanie_Klient, f5.data__ AS przyczyna |
| | 68 | FROM processes_view p |
| | 69 | LEFT JOIN features_text_view f1 ON p.prc_id = f1.tbl_id AND f1.featid = 14 |
| | 70 | LEFT JOIN features_opt_view f2 ON p.prc_id = f2.tbl_id AND f2.featid = 15 |
| | 71 | LEFT JOIN features_text_view f3 ON p.prc_id = f3.tbl_id AND f3.featid = 16 |
| | 72 | LEFT JOIN features_text_view f4 ON p.prc_id = f4.tbl_id AND f4.featid = 17 |
| | 73 | LEFT JOIN features_text_view f5 ON p.prc_id = f5.tbl_id AND f5.featid = 18 |
| | 74 | WHERE p.prtpid = 1 |
| | 75 | |
| | 76 | -- |
| | 77 | -- Przykład pobrania cechy kontrahenta - listy WIELOKROTNEGO wyboru |
| | 78 | -- konieczne jest użycie funkcji agregującej text_sum aby nie otrzymywać podwójnych rekordów |
| | 79 | -- |
| | 80 | SELECT c.name_1, text_sum(f2.ftopnm) |
| | 81 | FROM contacts c |
| | 82 | LEFT JOIN features_opt_view f2 ON c.contid = f2.tbl_id AND f2.featid = 20 |
| | 83 | WHERE contid = {CONTID} |
| | 84 | GROUP BY c.name_1 |
| | 85 | |
| | 86 | -- |
| | 87 | -- Przykład linkowania cech zdarzeń |
| | 88 | -- |
| 67 | | Wybiera sprawy z wartościami cech (tekstowe i opcje) |
| 68 | | |
| 69 | | SELECT p.dscrpt, p.symbol, 'PROCESS'::text AS clsnam, prc_id AS keyval, |
| 70 | | f1.data__ AS opinia, f2.ftopnm AS reklamacja, f3.data__ AS dzialanie_korygujace, |
| 71 | | f4.data__ AS dzialanie_Klient, f5.data__ AS przyczyna |
| 72 | | FROM processes_view p |
| 73 | | LEFT JOIN features_text_view f1 ON p.prc_id = f1.tbl_id AND f1.featid = 14 |
| 74 | | LEFT JOIN features_opt_view f2 ON p.prc_id = f2.tbl_id AND f2.featid = 15 |
| 75 | | LEFT JOIN features_text_view f3 ON p.prc_id = f3.tbl_id AND f3.featid = 16 |
| 76 | | LEFT JOIN features_text_view f4 ON p.prc_id = f4.tbl_id AND f4.featid = 17 |
| 77 | | LEFT JOIN features_text_view f5 ON p.prc_id = f5.tbl_id AND f5.featid = 18 |
| 78 | | WHERE p.prtpid = 1 |
