Zmiany pomiędzy wersją 9 and wersją 10 dla DeployerGuide/Custumization/AdvancedReporting/SQLExamples

Data i czas:

01/17/14 23:51:26 (11 years temu)

Autor:

JP (IP: 89.78.176.104)

Komentarz:

--

DeployerGuide/Custumization/AdvancedReporting/SQLExamples

@@ -27 +27 @@
 -- wybiera rekordy nie starsze niż miesiąc
 AND dcr.adddat > CURRENT_DATE - interval '1 month'
+
 
 }}}
@@ -164 +165 @@
 SELECT COALESCE(c.name_2, c.name_1) FROM contacts c;
 
-
-
-
 -- Wybiera dane do trendu - do wykresu
 
@@ -177 +175 @@
 extract(month from rlstrt) 
 ORDER BY extract(year from rlstrt), extract(month from rlstrt) 
+
+-- Formatuje status z obrazkiem czy zakończony
+-- Pokazuje wiele osób na równoegłych etapach w obiegu faktury
+SELECT 'DOCUMENT'::text AS clsnam, d.doc_id AS keyval, d.doc_id, v.amount, v.vat___, v.number, reg.evdnum,
+regexp_replace(d.dscrpt, 'Od:[ 0-9a-zA-ZąęłóśćńźżĄĘŚÓĆŃŁŹŻ\";\./.)(,-]*$', '')  AS dscrpt, d.trgtxt, v.payed_,  v.seldat::date, d.adddat::date AS dwplywu,
+CASE WHEN tps.status IN ('FINAL', 'CANCEL') THEN '<img src=../../framework/img/MessageBox/checked.png width=18 title=Status>' ELSE '' END AS icon,
+v.paytrm::date AS paytrm,
+COALESCE(name_1, name_2) AS contrah,
+ftv.ftopnm AS rodzaj,
+tps.dscrpt AS status,
+tps.tpstid,
+toa.code__,
+(SELECT text_sum(osoby) FROM (
+SELECT DISTINCT (firnam||' '||lasnam) AS osoby
+FROM stages s 
+INNER JOIN procedures pr2 USING(procid) 
+LEFT JOIN orgtree_view o3 ON o3.orunid = any(s.orgarr) 
+WHERE 
+   ((s.is_act IS TRUE AND s.is_fix IS FALSE) 
+    OR (s.ptsttp = 'END' AND s.is_fix IS TRUE))
+    AND (s.ptsttp != 'SUBPROCESS') AND (pr2.rootpr = d.procid)
+ ) x) AS osoby,
+d.procid,
+pr.prtpnm
+FROM documents d 
+INNER JOIN vatnote v USING(doc_id)
+INNER JOIN contacts c ON (c.contid = v.frctid)
+LEFT JOIN regofvatnotes reg USING (doc_id)
+LEFT JOIN features_opt_view ftv ON d.doc_id = ftv.tbl_id AND ftv.featid = 2
+LEFT JOIN types_of_accountants_doc toa USING(accdid)
+LEFT JOIN types_of_processes_states AS tps ON d.tpstid = tps.tpstid
+LEFT JOIN procedures pr USING(procid)
+
+WHERE d.gostof IS NULL AND d.copyof IS NULL AND d.state_ = 2 AND d.is_del IS FALSE
+AND d.adddat BETWEEN '{DATE_FROM}'::date AND '{DATE_TO}'::date
+AND {FILTER_STRING}
+ORDER BY d.doc_id
+
+
 
 }}}