had = CREATEOBJECT("had")
had.SHOW()


*  bacha, tenhle kus je fakt vzat ze stravovani_stravnici_parlament.prg, ale SELE DISTI zahrnuje i canteenu a ve scanu se par replacuje podle date+client+canteen

********  ze stravovani_stravnici_parlament  ********  zacatek
SELECT orders 
REPLACE FOR LEFT(updated_at, 10) = LEFT(created_at, 10) .and. SUBSTR(created_at, 12) >= "15" .and. ATC("lunch", course) > 0 course WITH "Dinner>15"

SELECT disti clientname, client, SPACE(22) as client_id, canteen, cant_name, date, den_tydne, firma, 0000.00 as cena, 00000.00 as doplnkovy, ;
       00 as  no_pre, 00 as  no_main, 00 as  no_side, 00 as  no_dinner, ;
       00 as ord_pre, 00 as ord_main, 00 as ord_side, 00 as ord_dinner, 000 as ord_lunch, ;
       00 as eat_pre, 00 as eat_main, 00 as eat_side, 00 as eat_dinner, 000 as eat_lunch  ;
       FROM orders INTO CURSOR orders_sum READWRITE 

had.set_had( 44 )
SELECT orders
SCAN
    SCATTER MEMVAR 
    SELECT orders_sum
    DO CASE 
    CASE ATC("lunch", m.course) > 0
        REPLACE FOR date = m.date .and. client = m.client .and. canteen = m.canteen ord_lunch  WITH ord_lunch  + m.ordered, eat_lunch  WITH eat_lunch  + m.eaten
    OTHERWISE
        REPLACE FOR date = m.date .and. client = m.client .and. canteen = m.canteen ord_dinner WITH ord_dinner + m.ordered, eat_dinner WITH eat_dinner + m.eaten
    ENDCASE 
ENDSCAN 

SELECT orders_sum
REPLACE FOR ord_lunch  > 0 ord_lunch  WITH 1
REPLACE FOR ord_dinner > 0 ord_dinner WITH 1
REPLACE FOR eat_lunch  > 0 eat_lunch  WITH 1
REPLACE FOR eat_dinner > 0 eat_dinner WITH 1
********  ze stravovani_stravnici_parlament  ********  konec

had.set_had( 66 )
DO spocti_cenu_surovin

SELECT cant_name, date, den_tydne, canteen, 0000000 as c_stredisk, SUM(eat_dinner) as eat_dinner, SUM(eat_lunch) as eat_lunch, $0 as naklady ;
       GROUP BY cant_name, date FROM orders_sum INTO CURSOR orders_sumsum READWRITE 
REPLACE FOR canteen = 1 c_stredisk WITH -46
REPLACE FOR canteen = 2 c_stredisk WITH -41

SET RELATION TO date + STR(c_stredisk) INTO naklady
REPLACE ALL naklady WITH naklady.prachy

RELEASE had 
DO pr_report WITH "stravovani_naklady_parlament"


*  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  
PROCEDURE spocti_cenu_surovin

IF m.issql

    DO open_database

    CREATE TABLE (m.gcpri_path + "odpocty_obraty") FREE (c_dokl I, c_stredisk I, date C(10), prachy Y, pz1 C(15), pz2 C(15))

    rv("doklady","doklady")

    SELECT c_dokl, c_stredisk, datum_dokl, pz1, pz2 WHERE operace = "VK" AND cen_hlad = "3" ;
           FROM doklady INTO cursor odpocty_dokl READWRITE 
    INDEX on c_dokl TAG c_dokl

    SELECT MIN(c_dokl) as c_dokl FROM odpocty_dokl INTO cursor min_cdokl
    hladajcdoklod = min_cdokl.c_dokl
    SELECT MAX(c_dokl) as c_dokl FROM odpocty_dokl INTO cursor max_cdokl
    hladajcdokldo = max_cdokl.c_dokl

    hladajcstredisk = 987789
    rv("zostobr_obraty","obraty")
    SELECT DISTI c_stredisk FROM odpocty_dokl INTO cursor c_stredisk
    SCAN
        hladajcstredisk = c_stredisk.c_stredisk
        REQUERY("obraty")

        SELECT c_dokl, SUM(mnozstvi*cena) as prachy GROUP BY 1 WHERE c_dokl IN (SELECT c_dokl FROM odpocty_dokl);
               FROM obraty INTO TABLE (m.gcpri_path + "obraty_1")

        SELECT odpocty_obraty 
        APPEND FROM (m.gcpri_path + "obraty_1")

    ENDSCAN 

    SELECT odpocty_obraty 
    SET RELATION TO c_dokl INTO odpocty_dokl
    REPLACE ALL date WITH LEFT(TTOC(odpocty_dokl.datum_dokl, 3), 10), pz1 WITH odpocty_dokl.pz1, pz2 WITH odpocty_dokl.pz2, c_stredisk WITH odpocty_dokl.c_stredisk

ELSE 
    SELECT c_dokl, c_stredisk, datum_dokl, pz1, pz2 WHERE operace = "VK" AND cen_hlad = "3" ;
           FROM (m.fn_doklady) INTO cursor odpocty_dokl READWRITE 
    INDEX on c_dokl TAG c_dokl

    SELECT c_dokl, c_stredisk, SPACE(10) as date, SUM(mnozstvi*cena) as prachy, SPACE(15) as pz1, SPACE(15) as pz2  GROUP BY 1 WHERE c_dokl IN (SELECT c_dokl FROM odpocty_dokl);
           FROM (m.fn_obraty) INTO cursor odpocty_obraty READWRITE 
    SET RELATION TO c_dokl INTO odpocty_dokl
    REPLACE ALL date WITH LEFT(TTOC(odpocty_dokl.datum_dokl, 3), 10), pz1 WITH odpocty_dokl.pz1, pz2 WITH odpocty_dokl.pz2

ENDIF 

SELECT date, c_stredisk, -SUM(prachy) as prachy GROUP BY 1,2 ;
       FROM odpocty_obraty INTO CURSOR naklady READWRITE 
INDEX ON date + STR(c_stredisk) TAG date_stred
