*************************************************************************************
* spocte prumerne ceny nakupu celkem, za posledni mesic a posledni kvartal
* do tabulky "prumerne_ceny" ulozi posledni z nich 
* pocita jednak absloutni prumer (cena_prum), jednak vazeny (cena_vazen)
*
*************************************************************************************

IF m.issql
    RETURN
ENDIF 


SELECT min(c_dokl) as min_kvartal FROM (m.gccen_path + "doklady") WHERE datum_dokl > DATE() - 99 INTO CURSOR qqq
SCATTER MEMVAR 
SELECT min(c_dokl) as min_mesic   FROM (m.gccen_path + "doklady") WHERE datum_dokl > DATE() - 33 INTO CURSOR qqq
SCATTER MEMVAR 


SELECT c_karty, SUM(mnozstvi) as mnozstvi, SUM(mnozstvi * cena) as prachy, ROUND(AVG(cena), 1) as cena_prum ;
       WHERE operace = "PE" AND c_dokl > m.min_kvartal ;
       GROUP BY 1 FROM (m.gccen_path + "obraty") INTO TABLE (m.gcpri_path + "prumery_q")
REPLACE FOR mnozstvi # 0 prachy WITH ROUND(prachy / mnozstvi, 1)
INDEX on c_karty TAG c_karty

SELECT c_karty, SUM(mnozstvi) as mnozstvi, SUM(mnozstvi * cena) as prachy, ROUND(AVG(cena), 1) as cena_prum ;
       WHERE operace = "PE" AND c_dokl > m.min_mesic;
       GROUP BY 1 FROM (m.gccen_path + "obraty") INTO TABLE (m.gcpri_path + "prumery_m")
REPLACE FOR mnozstvi # 0 prachy WITH ROUND(prachy / mnozstvi, 1)
INDEX on c_karty TAG c_karty


SELECT c_karty, SUM(mnozstvi) as mnozstvi, SUM(mnozstvi * cena) as prachy, ROUND(AVG(cena), 1) as cena_prum, ;
       " " as obdobi WHERE operace = "PE" ;
       GROUP BY 1 FROM (m.gccen_path + "obraty") INTO TABLE (m.gccen_path + "prumerne_ceny")
REPLACE FOR mnozstvi # 0 prachy WITH ROUND(prachy / mnozstvi, 1)


SET RELATION TO c_karty INTO prumery_q
REPLACE FOR prumery_q.c_karty # 0 cena_prum WITH prumery_q.cena_prum, prachy WITH prumery_q.prachy, obdobi WITH "Q"

SET RELATION TO c_karty INTO prumery_m
REPLACE FOR prumery_m.c_karty # 0 cena_prum WITH prumery_m.cena_prum, prachy WITH prumery_m.prachy, obdobi WITH "M"
       
REPLACE FOR cena_prum >  50 cena_prum WITH ROUND(cena_prum,  0)
REPLACE FOR cena_prum > 500 cena_prum WITH ROUND(cena_prum, -1)
REPLACE FOR prachy    >  50 prachy    WITH ROUND(prachy   ,  0)
REPLACE FOR prachy    > 500 prachy    WITH ROUND(prachy   , -1)

INDEX on c_karty TAG c_karty
ALTER table (m.gccen_path + "prumerne_ceny") rename COLUMN prachy to cena_vazen
