*    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    
*  export dat do mezd pro faurecia bakov
*  ze sk_plt vezme radky s platbami k_hotel2 a p_dotace, k nim dotahne info z hkredobr a hot_kred
*  scanuje sk_pol a vyplni castky za menu, minutky a ostatni
*  vygeneruje samostatna CSV po firmach
*
*  2020-11-02 - opravena relace podle cis_kred misto idklienta
*  2020-11-26 - bere se i gastroskupina
*             - do exportu pridan sloupec automaty, dava se do nej z id_kas = "AA"
*             - z automatu dalsi 2 sloupce s poctem a prachama za krabicky (konstantni cena 57.00 na kas AA a text KRABIC v sk_pol.nazev)
*  2021-04-17 - vydeje automatovych krabicek se dotuji, pokut den den zadny jiny dotovany prodej neni
*             - do exportu pridan sloupec dotace
*  2021-08-09 - hkredobr je filtrovan na casove obdobi kvuli duplicite cisel uctu
*
*    *    *    *    *    *    *    *    *      #> ver. 2022-03-31 <#       *    *    *    *    *    *    *    *    *    *

#DEFINE DOTACE_KRABIC 30
#DEFINE ODD           " ;"
#DEFINE CRLF          CHR(13)+CHR(10)


SELECT uzaverka, u_cis, SUM(cena_pl) AS dotace, 0001 as poc_dotaci ;
       GROUP BY 1,2 WHERE druh_pl = "P_DOTACE" AND LEFT(uzaverka, 4) = m.mesicek FROM sk_plt INTO TABLE prodotovano
 REPLACE ALL poc_dotaci WITH SIGN(dotace)
INDEX on uzaverka + u_cis TAG uzu

SELECT uzaverka, u_cis, SUM(cena_pl) as cena_pl, SPACE(12) as cis_kred, 0000000000 as idklienta, SPACE(25) as firma, SPACE(44) as jmeno, ;
       $0 as castka, $0 as menicka, $0 as minutky, $0 as bufety, $0 as doplnky, $0 as automaty, $0 as auto_krab, ;
       0000 as pocet_krab, 0000 as pocet_menu, 0000 as pocet_min, 0000 as pocet_buf, $0 as dotace, "     " as priznak ;
       GROUP BY 1,2 WHERE INLIST(druh_pl, "K_HOTEL2", "P_DOTACE") AND LEFT(uzaverka, 4) = m.mesicek FROM sk_plt INTO TABLE prozrano
INDEX on u_cis TAG u_cis 

SET RELATION TO u_cis INTO hkredobr
REPLACE ALL cis_kred WITH hkredobr.cis_kred, idklienta WITH hkredobr.idklienta, castka WITH -hkredobr.castka

SET RELATION TO cis_kred INTO hot_kred
REPLACE ALL firma WITH hot_kred.firma, jmeno WITH hot_kred.jmeno

SET RELATION TO uzaverka + u_cis INTO prodotovano
REPLACE ALL dotace WITH prodotovano.dotace

SELECT uzaverka, u_cis, pol.c_karty, pol.nazev, mnozstvi, prachy, dan, cen_hlad, k.druh_kalk, k.cena01 AS cena ;
       WHERE LEFT(uzaverka, 4) = m.mesicek ;
       FROM sk_pol pol LEFT JOIN karty k ON k.c_karty == pol.c_karty INTO TABLE pol
REPLACE FOR ISNULL(druh_kalk)             druh_kalk WITH "<ostatni>"
REPLACE FOR   EMPT(druh_kalk)             druh_kalk WITH "<ostatni>"
REPLACE FOR   druh_kalk # m.druh_hl_jidla druh_kalk WITH "<ostatni>"

REPLACE FOR mnozstvi # 0 cena WITH prachy / mnozstvi

SCAN 
    SCATTER MEMVAR 
    SELECT prozrano
    LOCATE FOR u_cis = m.u_cis
    IF FOUND()
        DO CASE 
        CASE LEFT(m.u_cis, 2) = "AA" .and. "KRABIC" $ m.nazev .AND. m.prachy = 57.00
            REPLACE auto_krab WITH auto_krab + m.prachy, pocet_krab WITH pocet_krab + m.mnozstvi

        CASE LEFT(m.u_cis, 2) = "AA"
            REPLACE automaty WITH automaty + m.prachy

        CASE druh_kalk # m.druh_hl_jidla
            REPLACE doplnky WITH doplnky + m.prachy

        CASE LEFT(m.nazev, 5) = "menu"
            REPLACE menicka   WITH menicka + m.prachy, pocet_menu WITH pocet_menu + m.mnozstvi

        CASE LEFT(m.nazev, 8) = "minutka "
            REPLACE minutky   WITH minutky + m.prachy, pocet_min  WITH pocet_min  + m.mnozstvi

        CASE LEFT(m.nazev, 6) = "bufet "
            REPLACE bufety   WITH bufety   + m.prachy, pocet_buf  WITH pocet_buf  + m.mnozstvi

        OTHERWISE 
            REPLACE doplnky WITH doplnky + m.prachy

        ENDCASE 
    ELSE 
        SELECT pol
        REPLACE druh_kalk WITH "XXXXXXXXXX"        
    ENDIF 
ENDSCAN 

dotace_automatu()

SELECT prozrano
INDEX on cis_kred + u_cis TAG cis_ucis

REPLACE FOR priznak = "jinde" dotace WITH -0.001

SELECT cis_kred, idklienta, firma, jmeno, SUM(dotace) as dotace, SUM(menicka)    as menicka   , SUM(minutky)   as minutky  , SUM(bufety)    as bufety   , SUM(auto_krab)  as auto_krab, ;
       SUM(automaty) as automaty, SUM(doplnky) as doplnky      , SUM(pocet_menu) as pocet_menu, SUM(pocet_min) as pocet_min, SUM(pocet_buf) as pocet_buf, SUM(pocet_krab) as pocet_krab ;
       GROUP BY 1 FROM prozrano INTO TABLE prozrano_sum

SELECT disti firma FROM prozrano_sum INTO CURSOR prozrano_firmy
SCAN
    DO exportuj_firmu WITH prozrano_firmy.firma
ENDSCAN


*    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    
PROCEDURE exportuj_firmu
PARAMETERS nazev_firmy
exportni_soubor = m.zacatek_nazvu_souboru + "strava_" + ALLTRIM(m.nazev_firmy) + ".csv"
WAIT exportni_soubor WINDOW NOWAIT 
STRTOFILE("os.cislo     ;sraz.celk ;sraz.jidla; sraz.min.;sraz.bufet; sraz.ost.;automaty-krabicky;automaty;porce menu;porce minut;porce bufet;pocet krabicek;dotace" + CRLF, m.exportni_soubor)
SELECT prozrano_sum
SCAN FOR firma = m.nazev_firmy
    retez = cis_kred + ODD + nn(menicka+minutky+doplnky+automaty+auto_krab) + ODD ;
          + nn(menicka) + ODD + nn(minutky) + ODD + nn(bufety) + ODD + nn(doplnky) + ODD + nn(auto_krab) + ODD + nn(automaty) + ODD + nn0(pocet_menu) + ODD + nn0(pocet_min) + ODD + nn0(pocet_buf) + ODD + nn0(pocet_krab) + ODD + nn(dotace)
    STRTOFILE(m.retez + CRLF, m.exportni_soubor, 1)
ENDSCAN 

exportni_soubor = zacatek_nazvu_souboru + "kontrola_" + ALLTRIM(m.nazev_firmy) + ".csv"
STRTOFILE("   os.cislo  ;    jmeno                         ;  uzaverka   ; ucet   ; celkem ; sraz.jidla ; sraz.min.; sraz.bufet; sraz.ost.; krabicky; automaty ;porce menu;porce minutky;porce bufet;pocet krabicek;dotace" + CRLF, m.exportni_soubor)
SELECT prozrano
SCAN FOR firma = m.nazev_firmy
    retez = cis_kred + ODD + LEFT(jmeno, 33) + ODD + uzaverka + ODD + u_cis + ODD + nn(menicka+minutky+doplnky+automaty+auto_krab) + ODD + ;
          + nn(menicka) + ODD + nn(minutky) + ODD + nn(bufety) + ODD + nn(doplnky) + ODD + nn(auto_krab) + ODD + nn(automaty) + ODD + nn0(pocet_menu) + ODD + nn0(pocet_min) + ODD + nn0(pocet_buf) + ODD + nn0(pocet_krab) + ODD + nn(dotace)
    STRTOFILE(m.retez + CRLF, m.exportni_soubor, 1)
ENDSCAN 


*    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    
PROCEDURE dotace_automatu
WAIT "dotace automatu" WINDOW NOWAIT 
sele prozrano
locate for auto_krab # 0 .and. empt(priznak)
do while found()
    radek = recn()
    scatt field uzaverka, u_cis, idklienta memvar 
    locate for idklienta = m.idklienta .and. left(uzaverka, 6) = left(m.uzaverka, 6) .and. dotace # 0
    if found()
        go (m.radek)
        repl priznak with "jinde"
    else
        go (m.radek)
        repl priznak with "DOTAC", dotace with DOTACE_KRABIC
    endif 
    locate for auto_krab # 0 .and. empt(priznak)
ENDDO 

