| 72 | | == Użyteczne konstrukcje i funkcje językowe SQL == |
| 73 | | |
| 74 | | {{{ |
| 75 | | -- Formatuje datę |
| 76 | | SELECT to_char(d.adddat, 'YYYY-MM-DD') FROM documents d; |
| 77 | | |
| 78 | | -- Pobiera rok |
| 79 | | select extract(YEAR from CURRENT_DATE); |
| 80 | | |
| 81 | | -- Oblicza czas który upłynął od daty do daty (np. na realizacji zadania) |
| 82 | | ((extract(EPOCH FROM d.rlend_) - extract(EPOCH FROM d.rlstrt))/3600)::numeric(12,2) AS duration |
| 83 | | |
| 84 | | -- Zamienia liczbę sekund na ciąg: H:M:S - np. 00:30:03 |
| 85 | | SELECT (1803 || 's')::interval |
| 86 | | |
| 87 | | -- Formatuje kwotę z pól tekstowych np. z formularzy customowych |
| 88 | | select cast(regexp_replace(regexp_replace('301 110,43',' ',''),',', '.') as numeric(12,2)) + 12.50; |
| 89 | | |
| 90 | | -- Warunkowo koloruje pole |
| 91 | | CASE WHEN p.pr_sta = 1 THEN '<div style="color: red">Rozpoczęte</div>' WHEN p.pr_sta = 2 THEN 'W trakcie realizacji' WHEN p.pr_sta = 3 THEN 'Zakończone' END AS status, |
| 92 | | |
| 93 | | -- Wybiera te rekordy których data jest w tym tugodniu |
| 94 | | to_char(CURRENT_DATE, 'WW') = to_char (adddat, 'WW') |
| 95 | | |
| 96 | | -- wybiera rekordy nie starsze niż miesiąc |
| 97 | | AND dcr.adddat > CURRENT_DATE - interval '1 month' |
| 98 | | |
| 99 | | }}} |
| 100 | | |
| 101 | | == Przykładowe użyteczne zapytania do bazy edokumenty == |
| 102 | | |
| 103 | | {{{ |
| 104 | | -- |
| 105 | | -- Pobranie danych z formularzy dynamicznych |
| 106 | | -- |
| 107 | | SELECT to_char(d.adddat, 'YY-MM-DD') AS day, |
| 108 | | -- rozmowy |
| 109 | | (SELECT count(*) /10 |
| 110 | | FROM events_view e WHERE e.trmtyp = 'PHONECALL' AND emp_id IN (84,62) AND e.start_ >= d.adddat::date - 7 |
| 111 | | AND e.start_ <= d.adddat) AS rozmowy_handlowe, |
| 112 | | -- spotkania |
| 113 | | (SELECT count(*) |
| 114 | | FROM events_view e WHERE e.trmtyp = 'MEETING' AND emp_id IN (84,62) AND e.start_ >= d.adddat::date - 7 |
| 115 | | AND e.start_ <= d.adddat) AS spotkania, |
| 116 | | -- wartość pola featid 98 |
| 117 | | f3.data__::int AS odwedok, |
| 118 | | f4.ftopnm AS cecha4 |
| 119 | | FROM documents d |
| 120 | | INNER JOIN features_text_view f3 ON d.doc_id = f3.tbl_id AND f3.featid = 98 |
| 121 | | -- cecha z listy wyboru |
| 122 | | INNER JOIN features_opt_view f4 ON d.doc_id = f4.tbl_id AND f4.ftopid = 119 |
| 123 | | WHERE d.is_del IS NOT true AND d.gostof IS NULL |
| 124 | | |
| 125 | | -- |
| 126 | | -- Wybiera symbol teczki z numeru sprawy oo formacie ''2/03/08/UP/AW'' |
| 127 | | -- |
| 128 | | SELECT substring(symbol from '[0-9]*/[0-9]*/[0-9]*/([A-Z]*)') FROm processes; |
| 129 | | |
| 130 | | -- |
| 131 | | -- Wybiera krótką nazwę klienta jeśli jest, a jeśli jest pusta to długą |
| 132 | | -- |
| 133 | | SELECT COALESCE(c.name_2, c.name_1) FROM contacts c; |
| 134 | | |
| 135 | | -- |
| 136 | | -- Wybiera sprawy z wartościami cech (tekstowe i opcje) |
| 137 | | -- |
| 138 | | SELECT p.dscrpt, p.symbol, 'PROCESS'::text AS clsnam, prc_id AS keyval, |
| 139 | | f1.data__ AS opinia, f2.ftopnm AS reklamacja, f3.data__ AS dzialanie_korygujace, |
| 140 | | f4.data__ AS dzialanie_Klient, f5.data__ AS przyczyna |
| 141 | | FROM processes_view p |
| 142 | | LEFT JOIN features_text_view f1 ON p.prc_id = f1.tbl_id AND f1.featid = 14 |
| 143 | | LEFT JOIN features_opt_view f2 ON p.prc_id = f2.tbl_id AND f2.featid = 15 |
| 144 | | LEFT JOIN features_text_view f3 ON p.prc_id = f3.tbl_id AND f3.featid = 16 |
| 145 | | LEFT JOIN features_text_view f4 ON p.prc_id = f4.tbl_id AND f4.featid = 17 |
| 146 | | LEFT JOIN features_text_view f5 ON p.prc_id = f5.tbl_id AND f5.featid = 18 |
| 147 | | WHERE p.prtpid = 1 |
| 148 | | |
| 149 | | -- |
| 150 | | -- Wybiera dane do trendu - do wykresu |
| 151 | | -- |
| 152 | | SELECT extract(month from rlstrt) as m, |
| 153 | | sum((time__::numeric(12,2)/3600)::numeric(12,2)) AS sum |
| 154 | | FROM rcp_cards_view |
| 155 | | WHERE rlstrt IS NOT NULL AND is_fak = TRUE AND is_del = FALSE AND tpstid = 9 |
| 156 | | AND rlstrt + interval '1 year' >= CURRENT_DATE |
| 157 | | GROUP BY extract(year from rlstrt), |
| 158 | | extract(month from rlstrt) |
| 159 | | ORDER BY extract(year from rlstrt), extract(month from rlstrt) |
| 160 | | |
| 161 | | }}} |
| 162 | | |
