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

Pokaż
Ignoruj:
Data i czas:
01/17/14 23:51:26 (11 years temu)
Autor:
JP (IP: 89.78.176.104)
Komentarz:

--

Legend:

Bez zmian
Dodane
Usunięte
Zmienione
  • DeployerGuide/Custumization/AdvancedReporting/SQLExamples

    v9 v10  
    2727-- wybiera rekordy nie starsze niż miesiąc 
    2828AND dcr.adddat > CURRENT_DATE - interval '1 month' 
     29 
    2930 
    3031}}} 
     
    164165SELECT COALESCE(c.name_2, c.name_1) FROM contacts c; 
    165166 
    166  
    167  
    168  
    169167-- Wybiera dane do trendu - do wykresu 
    170168 
     
    177175extract(month from rlstrt)  
    178176ORDER BY extract(year from rlstrt), extract(month from rlstrt)  
     177 
     178-- Formatuje status z obrazkiem czy zakończony 
     179-- Pokazuje wiele osób na równoegłych etapach w obiegu faktury 
     180SELECT 'DOCUMENT'::text AS clsnam, d.doc_id AS keyval, d.doc_id, v.amount, v.vat___, v.number, reg.evdnum, 
     181regexp_replace(d.dscrpt, 'Od:[ 0-9a-zA-ZąęłóśćńźżĄĘŚÓĆŃŁŹŻ\";\./.)(,-]*$', '')  AS dscrpt, d.trgtxt, v.payed_,  v.seldat::date, d.adddat::date AS dwplywu, 
     182CASE WHEN tps.status IN ('FINAL', 'CANCEL') THEN '<img src=../../framework/img/MessageBox/checked.png width=18 title=Status>' ELSE '' END AS icon, 
     183v.paytrm::date AS paytrm, 
     184COALESCE(name_1, name_2) AS contrah, 
     185ftv.ftopnm AS rodzaj, 
     186tps.dscrpt AS status, 
     187tps.tpstid, 
     188toa.code__, 
     189(SELECT text_sum(osoby) FROM ( 
     190SELECT DISTINCT (firnam||' '||lasnam) AS osoby 
     191FROM stages s  
     192INNER JOIN procedures pr2 USING(procid)  
     193LEFT JOIN orgtree_view o3 ON o3.orunid = any(s.orgarr)  
     194WHERE  
     195   ((s.is_act IS TRUE AND s.is_fix IS FALSE)  
     196    OR (s.ptsttp = 'END' AND s.is_fix IS TRUE)) 
     197    AND (s.ptsttp != 'SUBPROCESS') AND (pr2.rootpr = d.procid) 
     198 ) x) AS osoby, 
     199d.procid, 
     200pr.prtpnm 
     201FROM documents d  
     202INNER JOIN vatnote v USING(doc_id) 
     203INNER JOIN contacts c ON (c.contid = v.frctid) 
     204LEFT JOIN regofvatnotes reg USING (doc_id) 
     205LEFT JOIN features_opt_view ftv ON d.doc_id = ftv.tbl_id AND ftv.featid = 2 
     206LEFT JOIN types_of_accountants_doc toa USING(accdid) 
     207LEFT JOIN types_of_processes_states AS tps ON d.tpstid = tps.tpstid 
     208LEFT JOIN procedures pr USING(procid) 
     209 
     210WHERE d.gostof IS NULL AND d.copyof IS NULL AND d.state_ = 2 AND d.is_del IS FALSE 
     211AND d.adddat BETWEEN '{DATE_FROM}'::date AND '{DATE_TO}'::date 
     212AND {FILTER_STRING} 
     213ORDER BY d.doc_id 
     214 
     215 
    179216 
    180217}}}