Changes between Version 1 and Version 2 of AdvancedReports


Ignore:
Timestamp:
01/24/24 22:05:12 (2 years ago)
Author:
201136
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • AdvancedReports

    v1 v2  
    11= **Напредни извештаи од базата (SQL и складирани процедури)**
    22
    3 === **Извештаи за клиент според тромесечие, број на купени производи и потрошена сума**
     3=== **Извештаи за корисник според тромесечие, просечен број на калории, масти, протеини и јаглени хидрати**
    44{{{#!sql
    5 SELECT DISTINCT u.korisnicko_ime,
    6 CASE WHEN tabela.quarter=1 THEN tabela.kupeni_proizvodi ELSE 0 END AS prv_kvartal,
    7 CASE WHEN tabela.quarter=2 THEN tabela.kupeni_proizvodi ELSE 0 END AS vtor_kvartal,
    8 CASE WHEN tabela.quarter=3 THEN tabela.kupeni_proizvodi ELSE 0 END AS tret_kvartal,
    9 CASE WHEN tabela.quarter=4 THEN tabela.kupeni_proizvodi ELSE 0 END AS cetvrt_kvartal,
     5SELECT
     6  U.name AS UserName,
     7  DATE_TRUNC('quarter', D.date) AS QuarterStart,
     8  AVG(COALESCE(I.calories, 0)) AS AvgCalories,
     9  AVG(COALESCE(I.fats, 0)) AS AvgFats,
     10  AVG(COALESCE(I.protein, 0)) AS AvgProteins,
     11  AVG(COALESCE(I.carbs, 0)) AS AvgCarbs
     12FROM
     13  _User U
     14JOIN
     15  _UserHas_Day UHD ON U.uID = UHD.uID
     16JOIN
     17  _Day D ON UHD.dID = D.dID
     18LEFT JOIN
     19  _DayHasMeal DHM ON D.dID = DHM.dID
     20LEFT JOIN
     21  Meal M ON DHM.mID = M.mID
     22LEFT JOIN
     23  MealHasIngredient MHI ON M.mID = MHI.mID
     24LEFT JOIN
     25  Ingredient I ON MHI.iID = I.iID
     26GROUP BY
     27  U.name, DATE_TRUNC('quarter', D.date)
     28ORDER BY
     29  U.name, QuarterStart;
    1030
    11 CASE WHEN tabela.quarter=1 THEN tabela.suma ELSE 0 END AS prv_kvartal_suma,
    12 CASE WHEN tabela.quarter=2 THEN tabela.suma ELSE 0 END AS vtor_kvartal_suma,
    13 CASE WHEN tabela.quarter=3 THEN tabela.suma ELSE 0 END AS tret_kvartal_suma,
    14 CASE WHEN tabela.quarter=4 THEN tabela.suma ELSE 0 END AS cetvrt_kvartal_suma
    15 
    16 FROM naracki as n
    17  JOIN kupuvaci as k on k.korisnicko_ime=n.korisnicko_ime
    18  JOIN korisnici AS u ON k.korisnicko_ime = u.korisnicko_ime
    19 JOIN parce_e_del_od_naracki as pdm on pdm.id_naracka=n.id_naracka
    20  join parce as p on pdm.id_parce =p.id_parce
    21 join proizvodi as pro on p.id_proizvod=pro.id_proizvod
    22  JOIN cena AS c ON c.id_proizvod=pro.id_proizvod
    23  JOIN (
    24 
    25 SELECT DISTINCT c2.korisnicko_ime, extract(quarter FROM n.data_na_naracka) AS quarter, sum(n.suma) AS suma,
    26 count(n.id_naracka) AS kupeni_proizvodi
    27 FROM naracki AS n
    28 JOIN parce_e_del_od_naracki as pdm on pdm.id_naracka=n.id_naracka
    29  join parce as p on pdm.id_parce =p.id_parce
    30  join proizvodi as pro on p.id_proizvod=pro.id_proizvod
    31  JOIN cena AS c ON c.id_proizvod=pro.id_proizvod
    32 JOIN kupuvaci AS c2 ON c2.korisnicko_ime = n.korisnicko_ime
    33 GROUP BY 1,2
    34 ) AS tabela ON u.korisnicko_ime = tabela.korisnicko_ime
    35 }}}
    36 === **Извештај за вработените,нивната најдобра нарачка,бројот на нарачки што ги направиле и нарачката која што им има најголема гаранција **
    37 {{{#!sql
    38 select d.korisnicko_ime,
    39         max(na.suma) as najdobra_naracka, broj.broj_naracki as broj_naracki,max(najgolema_garancija.garan) as najgolema_garancija
    40         from dostavuvaci d
    41         left join
    42         (
    43                 select  distinct d.korisnicko_ime as dostavuvac,k.e_posta as email, d2.id_dostavuvanja as broj_dostava
    44                 ,n.suma 
    45                 from dostavuvaci  d
    46                 left join korisnici k on k.korisnicko_ime=d.korisnicko_ime
    47                 left join dostavuvanja  d2 on d2.korisnicko_ime = d.korisnicko_ime
    48                 left join naracki n on n.id_naracka =d2.id_naracka
    49                 left join parce_e_del_od_naracki pedon on pedon.id_naracka =n.id_naracka
    50                 left join parce p3 on p3.id_parce=pedon.id_parce
    51                 left join proizvodi p4 on p4.id_proizvod=p3.id_proizvod
    52                 left join cena c on c.id_proizvod=p4.id_proizvod
    53                
    54         ) as na on d.korisnicko_ime = na.dostavuvac
    55         left join
    56         (
    57            select d.korisnicko_ime,n.e_posta,count(dost.id_dostavuvanja) as broj_naracki
    58            from dostavuvaci as d
    59            join korisnici as n on d.korisnicko_ime=n.korisnicko_ime
    60            join dostavuvanja as dost on d.korisnicko_ime=dost.korisnicko_ime
    61            group by d.korisnicko_ime,n.korisnicko_ime
    62         )
    63         as broj on d.korisnicko_ime = broj.korisnicko_ime
    64        
    65        left join
    66        (
    67           select d.korisnicko_ime as dostavuvac,(g.datum_do-g.datum_od) as garan
    68           from dostavuvaci d
    69           left join dostavuvanja  d2 on d2.korisnicko_ime = d.korisnicko_ime
    70           left join naracki n on n.id_naracka =d2.id_naracka
    71           left join parce_e_del_od_naracki pedon on pedon.id_naracka =n.id_naracka
    72           left join parce p3 on p3.id_parce=pedon.id_parce
    73           left join proizvodi p4 on p4.id_proizvod=p3.id_proizvod
    74           left join garancija g on g.id_proizvod =p4.id_proizvod
    75        ) as najgolema_garancija on najgolema_garancija.dostavuvac=d.korisnicko_ime
    76        
    77        
    78         group by d.korisnicko_ime,broj.broj_naracki
    79         order by najdobra_naracka desc
    8031}}}
    8132
    82 === **Извештај за категориите,број на производи по категорија според времетраењето на гаранцијата **
    83 {{{#!sql
    84 select k.ime ,
    85 coalesce(prv.zbir_1,0) as pomalku_od_godina, coalesce(vtor.zbir_2,0) as godina,
    86 coalesce(tret.zbir_3,0) as poveke_od_godina
    87 from kategorii  k
    88 join proizvodi p on p.id_proizvod=k.id_proizvod
    89 join garancija g on g.id_proizvod=p.id_proizvod
    90 left join (
    91 select k.ime ,p.ime_proizvod , count(p.id_proizvod)*p.kolicina  as zbir_1
    92 from kategorii k
    93 join proizvodi p on p.id_proizvod=k.id_proizvod
    94 join garancija g on g.id_proizvod=p.id_proizvod  and (g.datum_do-g.datum_od) < interval'365 days'
    95 group by k.ime,p.ime_proizvod,p.kolicina
    96 ) as prv on  k.ime = prv.ime
    97 left join (
    98 select k.ime ,p.ime_proizvod ,count(p.id_proizvod)*p.kolicina  as zbir_2
    99 from kategorii k
    100 join proizvodi p on p.id_proizvod=k.id_proizvod
    101 join garancija g on g.id_proizvod=p.id_proizvod  and (g.datum_do-g.datum_od) between  interval'365 days' and 2*interval'365 days'
    102 group by k.ime,p.ime_proizvod,p.kolicina
    103 ) as vtor on k.ime = vtor.ime
    104 left join (
    105 select k.ime ,p.ime_proizvod , count(p.id_proizvod)*p.kolicina  as zbir_3
    106 from kategorii k
    107 join proizvodi p on p.id_proizvod=k.id_proizvod
    108 join garancija g on g.id_proizvod=p.id_proizvod  and (g.datum_do-g.datum_od) > interval' 2 years'
    109 group by k.ime,p.ime_proizvod,p.kolicina
    110 
    111 ) as tret on k.ime=tret.ime
    112 
    113 group by k.ime, prv.zbir_1, vtor.zbir_2, tret.zbir_3
    114 }}}
    115 
    116 === **Извештај за компаниите,број на купени производи од секоја компанија според тромесечие **
     33=== **Извештаи за корисник според тромесечие, просечно времетраење на тренинг**
    11734{{{#!sql
    11835
    119 select k.ime ,
    120 coalesce(prv.zbir_1,0) as prv_kvartal, coalesce(vtor.zbir_2,0) as vtor_kvartal,
    121 coalesce(tret.zbir_3,0) as tret_kvartal, coalesce(cetvrt.zbir_4,0) as cetvrt_kvartal
    122 from kompanija k
    123 join proizvodi p on p.ime = k.ime
    124 join parce as par on par.id_proizvod =p.id_proizvod
    125 join parce_e_del_od_naracki pedon on pedon.id_parce =par.id_parce
    126 join naracki as n on n.id_naracka =pedon.id_parce
    127 left join (
    128 select k.ime ,p.ime_proizvod , count(pedon.id_parce)  as zbir_1
    129 from kompanija k
    130 join proizvodi p on p.ime = k.ime
    131 join parce as par on par.id_proizvod =p.id_proizvod
    132 join parce_e_del_od_naracki pedon on pedon.id_parce =par.id_parce
    133 join naracki as n on n.id_naracka =pedon.id_naracka and n.data_na_naracka between to_date(concat('01-01-',extract(year from now())),'dd-mm-yyyy') and to_date(concat('31-03-',extract(year from now())),'dd-mm-yyyy')
    134 group by k.ime,p.ime_proizvod
    135 ) as prv on  k.ime = prv.ime
    136 left join (
    137 select k.ime ,p.ime_proizvod ,count(pedon.id_parce)   as zbir_2
    138 from kompanija k
    139 join proizvodi p on p.ime = k.ime
    140 join parce as par on par.id_proizvod =p.id_proizvod
    141 join parce_e_del_od_naracki pedon on pedon.id_parce =par.id_parce
    142 join naracki as n on n.id_naracka =pedon.id_naracka and n.data_na_naracka between to_date(concat('01-04-',extract(year from now())),'dd-mm-yyyy') and to_date(concat('30-06-',extract(year from now())),'dd-mm-yyyy')
    143 group by k.ime,p.ime_proizvod
    144 ) as vtor on k.ime = vtor.ime
    145 left join (
    146 select k.ime ,p.ime_proizvod , count(pedon.id_parce)  as zbir_3
    147 from kompanija k
    148 join proizvodi p on p.ime = k.ime
    149 join parce as par on par.id_proizvod =p.id_proizvod
    150 join parce_e_del_od_naracki pedon on pedon.id_parce =par.id_parce
    151 join naracki as n on n.id_naracka =pedon.id_naracka  and  n.data_na_naracka between to_date(concat('01-07-',extract(year from now())),'dd-mm-yyyy') and to_date(concat('30-09-',extract(year from now())),'dd-mm-yyyy')
    152 group by k.ime,p.ime_proizvod
    153 ) as tret on k.ime=tret.ime
    154 left join (
    155 select k.ime ,p.ime_proizvod, count(pedon.id_parce)   as zbir_4
    156 from kompanija k
    157 join proizvodi p on p.ime = k.ime
    158 join parce as par on par.id_proizvod =p.id_proizvod
    159 join parce_e_del_od_naracki pedon on pedon.id_parce =par.id_parce
    160 join naracki as n on n.id_naracka =pedon.id_naracka and  n.data_na_naracka between to_date(concat('01-10-',extract(year from now())),'dd-mm-yyyy') and to_date(concat('31-12-',extract(year from now())),'dd-mm-yyyy')
    161 group by k.ime,p.ime_proizvod
    162  
    163 ) as cetvrt on k.ime=cetvrt.ime
    164 group by k.ime, prv.zbir_1, vtor.zbir_2, tret.zbir_3, cetvrt.zbir_4
    165 
     36SELECT
     37  U.name AS UserName,
     38  DATE_TRUNC('quarter', TO_TIMESTAMP(UHD.dID)) AS QuarterStart,
     39  AVG(W.duration) AS AvgWorkoutDurationMins
     40FROM
     41  _User U
     42JOIN
     43  _UserHas_Day UHD ON U.uID = UHD.uID
     44JOIN
     45  _DayHasWorkout DHW ON UHD.dID = DHW.dID
     46JOIN
     47  Workout W ON DHW.wID = W.wID
     48WHERE
     49  DHW.wID IS NOT NULL
     50  AND W.duration IS NOT NULL
     51GROUP BY
     52  U.name, DATE_TRUNC('quarter', TO_TIMESTAMP(UHD.dID))
     53ORDER BY
     54  U.name, QuarterStart;
    16655
    16756}}}