*    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *
*
* program cte vsechny soubory z ivaru (kantyna aquaparku) a zapise je do SK
* jmeno souboru ma format "export_20171213094300.csv"
*
* zaroven generuje CSV cenik podle umisteni na kasu 04, cenu bere z karty.cena01, sazbu dph ze skupiny.dan01
*
* 2017-12-13 
* 2017-12-21 - do sk_pol i sk_sez se zapisuje id_man podle umisteni
* 2018-01-12 - do ceniku se zapisuje i id_man
* 2018-01-14 - import se vyrovna s desetinnou tectou i carkou v ceesveckach
* 2020-07-22 - hledani, zda uzaverka uz byla naimportovana, filtruje krom fieldu uzaverka i id_kas
*            - do sk_sez se vyplni i uct_den
*
*    *    *    *    *    *    *    *    *    *    *    *      #> ver. 2020-07-22 <#       *    *    *    *    *    *    *    *    *    *    *    *    *

#DEFINE ODD          ";"
#DEFINE IVAR_PATH    "i:\ivar\"
#DEFINE ID_KAS_IVAR  "04"
#DEFINE U_CIS_IVAR   ID_KAS_IVAR + "00001"
#DEFINE AUTOR_IVAR   "IVR"
#DEFINE DRUH_PL_IVAR "I_Ivar"
#DEFINE CRLF         CHR(13)+CHR(10)
#DEFINE DEFAULT_DPH  1.15
#DEFINE EXPORTFILE   IVAR_PATH + "cenik_kantyna.csv"
SET REPROCESS TO AUTOMATIC

SELECT c_karty, nazev, id_man, id_kas WHERE id_kas = ID_KAS_IVAR ;
       FROM (m.fn_umisteni) INTO CURSOR umisteni_aqua READWRITE
INDEX on c_karty TAG c_karty

generuj_cenik()

pocet_souboru = ADIR(soubory, IVAR_PATH + "export_20*.csv")
FOR iii = 1 TO pocet_souboru
    soubor_ivar = IVAR_PATH + soubory(iii, 1)
    uzaverka = SUBSTR(soubory(iii, 1), 10, 6) + " " + SUBSTR(soubory(iii, 1), 16, 2) + ":" + SUBSTR(soubory(iii, 1), 18, 2)
    hodina_uzaverky = SUBSTR(m.uzaverka, 8,2)
    odecist_dni = IIF(m.hodina_uzaverky < "10", 1, 0)
    uct_den = {}
    TRY 
        uct_den = DATE(2000 + VAL(SUBSTR(m.uzaverka, 1,2)), VAL(SUBSTR(m.uzaverka, 3,2)), VAL(SUBSTR(m.uzaverka, 5,2))) - m.odecist_dni
    CATCH 
    ENDTRY 

    u_cas = SUBSTR(soubory(iii, 1), 8)
    SELECT uzaverka FROM (m.fn_sk_sez) WHERE uzaverka = m.uzaverka AND id_kas = ID_KAS_IVAR INTO CURSOR uzuz
    IF RECCOUNT() = 0
        celkovy_prachy = 0
        dopln_polozky()
        INSERT INTO (m.fn_sk_fin) (id_kas, uzaverka,  u_cis    , autor     ,   cena_celk     ,   cena_slev     ,   u_cas ) ;
                    VALUES (ID_KAS_IVAR, m.uzaverka, U_CIS_IVAR, AUTOR_IVAR, m.celkovy_prachy, m.celkovy_prachy, m.u_cas )

        INSERT INTO (m.fn_sk_plt) (uzaverka,  u_cis, druh_pl,     fik         ,   cena_pl       , mena ,   cena_mena      ) ;
               VALUES (m.uzaverka, U_CIS_IVAR, DRUH_PL_IVAR, "NEFISKALNI IVAR", m.celkovy_prachy, "CZK", m.celkovy_prachy )

        RENAME (m.soubor_ivar) TO (m.soubor_ivar + "_importovano")
    ELSE 
        WAIT "uzaverka " + m.uzaverka + " uz v eskackach je" WINDOW TIMEOUT 2
        RENAME (m.soubor_ivar) TO (m.soubor_ivar + "_importovano_drive")
    ENDIF 
ENDFOR


* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * 
PROCEDURE dopln_polozky
fh = FOPEN(m.soubor_ivar)
IF m.fh > 0 
    seznam_id_man = ""
    DO while ! feof(m.fh)
        radek      = fgets(m.fh,333)

        IF SET("point") = "."
            radek = STRTRAN(m.radek, ",", ".")
        ELSE
            radek = STRTRAN(m.radek, ".", ",")
        ENDIF 

        at1        = at(ODD, m.radek, 2)
        c_karty    = INT(VAL(substr(m.radek, m.at1+1)))

        at1        = at(ODD, m.radek, 3)
        mnozstvi   = VAL(substr(m.radek, m.at1+1))

        at1        = at(ODD, m.radek, 4)
        prachy     = VAL(substr(m.radek, m.at1+1))
        celkovy_prachy = m.celkovy_prachy + prachy

        at1        = at(ODD, m.radek, 5)
        dan        = VAL(substr(m.radek, m.at1+1))

        IF m.c_karty # 0 .and. m.mnozstvi # 0

            SELECT umisteni_aqua 
            LOCATE FOR c_karty = m.c_karty
            SCATTER FIELDS nazev, id_man, id_kas MEMVAR 
            IF ATC(m.id_man, seznam_id_man) = 0        && do sk_sez se musi zapsat vsechny pouzite id_man
                seznam_id_man = seznam_id_man + " _ " + m.id_man
                INSERT INTO (m.fn_sk_sez) (id_kas,   id_man,   uzaverka,   uct_den, autor     , lastuzav ) ;
                                 VALUES (m.id_kas, m.id_man, m.uzaverka, m.uct_den, AUTOR_IVAR, SUBSTR(DTOS(DATE()), 3) + " " + TIME())
            ENDIF 

            INSERT INTO (m.fn_sk_pol) (id_kas,   id_man,   uzaverka,  u_cis, k_odpoctu, cen_hlad,  c_karty,   nazev,   mnozstvi,   prachy,   dan) ;
                             VALUES (m.id_kas, m.id_man, m.uzaverka, U_CIS_IVAR, "*",     "1",   m.c_karty, m.nazev, m.mnozstvi, m.prachy, m.dan)
        ENDIF 
    ENDDO 
    FCLOSE(m.fh)
ELSE
    WAIT "nelze otevrit soubor " + m.soubor_ivar WINDOW TIMEOUT 3 AT 22,22
ENDIF 


* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * 
PROCEDURE generuj_cenik
SELECT c_karty, nazev, druh_kalk, cena01, kod, skupiny.dan01, "  " as id_man ;
       WHERE c_karty in (SELECT c_karty FROM (m.fn_umisteni) WHERE id_kas = ID_KAS_IVAR) ;
       ORDER BY c_karty ;
       FROM (m.fn_karty) LEFT JOIN (m.fn_skupiny) ON skupiny.c_druh == karty.c_druhk INTO CURSOR cenik_aqua READWRITE
SET RELATION TO c_karty INTO umisteni_aqua 
REPLACE ALL id_man WITH umisteni_aqua.id_man

REPLACE FOR ISNULL(dan01) dan01 WITH DEFAULT_DPH
REPLACE ALL nazev WITH STRTRAN(nazev, "   ", " ")
REPLACE ALL nazev WITH STRTRAN(nazev, "   ", " ")
REPLACE ALL nazev WITH STRTRAN(nazev, "   ", " ")
REPLACE ALL nazev WITH STRTRAN(nazev, "  ", " ")
REPLACE ALL nazev WITH STRTRAN(nazev, "  ", " ")
STRTOFILE( '"c_karty"' + ODD + '"nazev"' + ODD + '"skupina"' + ODD + '"cena"' + ODD + '"dph"' + ODD + '"ean"' + ODD + '"sklad"' + CRLF, EXPORTFILE)
SCAN
    STRTOFILE( nx(c_karty) + ODD + tx2(nazev) + ODD + tx2(druh_kalk) + ODD + nx(cena01,2) + ODD + nx(dan01,2) + ODD + tx2(kod) + ODD + tx2(id_man) + CRLF, EXPORTFILE, 1)
ENDSCAN 


********** vraci string + uvozovky  **********
FUNCTION tx2 (retez)
RETURN '"' + ALLTRIM(STRTRAN(IIF(ISNULL(m.retez), "", m.retez), '"', "'")) + '"'

********** vraci string bez mezer + uvozovky  **********
FUNCTION tx (retez)
retval = STRTRAN(m.retez, " ", "")
RETURN '"' + ALLTRIM(STRTRAN(retval, '"', "'")) + '"'

********** vraci string  **********
FUNCTION xx (retez)
RETURN ALLTRIM(STRTRAN(retez, '"', "'"))

