*    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *
*
* program cte CSV soubory z automatu IMA, zapise je do SK a hkredobr
*
*  2021-02-28 - 
*  2021-03-02 - co stoji 57Kc, bere se jako krabicka a zvlast se zapisuje do sk_pol
*  2021-03-26 - obsluha cis_kred i idklienta 
*             - z imy se bere vice souboru
*
*             - kousek programu, zakladajici skladove karty, dam casem do hajzlu
*  2021-04-01 - pro starsi uzaverky se z imy bere cis_kred, pro novejsi idklienta
*  2021-04-17 - pro VSECHNY uzaverky se z imy bere idklienta
*  2022-02-17 - u_cis se bere z POSLEDNICH 5 znaku z cisla transakce
*
*    *    *    *    *    *    *    *    *      #> ver. 2022-02-17 <#       *    *    *    *    *    *    *    *    *    *
#DEFINE PGM_VERZE                                "ver. 2022-02-17"

#DEFINE ODD             ";"
#DEFINE KASA_AUTOMAT    "AA"
#DEFINE C_KARTY_AUTOMAT  77
#DEFINE C_KARTY_KRABICKA 88

IF DAY(DATE()) > 7
    minuly_mesic = LEFT(TTOC(        DATE()     , 3), 8)
ELSE 
    minuly_mesic = LEFT(TTOC(GOMONTH(DATE(), -1), 3), 8)
ENDIF 

tenhle_mesic     = LEFT(TTOC(        DATE()     , 3), 8)
WAIT "importuje se obdobi:" + m.minuly_mesic WINDOW TIMEOUT 3 AT 22,22

import_path     = m.gccen_path
import_filename = "ima_"

IF VARTYPE(vysledek) # "C"
    vysledek = " "
ENDIF 
vysledek = m.vysledek + PGM_VERZE

o_set_point = SET("POINT")

ACTIVATE SCREEN 
_screen.Cls

*dopln_karty_pokud_chybi()

IF .not. FILE(m.gccen_path + "automaty.dbf")
    CREATE TABLE (m.gccen_path + "automaty") FREE ;
                 (cis_kred C(22), prachy N(9,2), cas C(19), nazev C(30), c_karty I, u_cis C(7), id_kas C(2), id_man C(2), uzaverka C(12), idklienta I)
    INDEX on u_cis TAG u_cis
ELSE 
    USE (m.gccen_path + "automaty")
ENDIF 

importuj_csv()
priprav_sk()
zapis_do_sk()

SET POINT TO (m.o_set_point)


*     *     *     *     *     *     *     *     *     *     *     *     *     *     *     *     
PROCEDURE priprav_sk

SELECT uzaverka, u_cis, prachy, id_kas, id_man, c_karty, 1 as mnozstvi, 1.15 as dan, "1" as cen_hlad, "*" as k_odpoctu, nazev ;
       WHERE LEFT(cas, 8) = m.minuly_mesic FROM automaty INTO TABLE (m.gcpri_path + "automaty_do_sk_pol")

SELECT uzaverka, u_cis, id_kas, ;
       "CZK" as mena, "K_HOTEL2  " as druh_pl, prachy as cena_pl, prachy as cena_mena, prachy as cena_celk, prachy as cena_slev, CHRTRAN(cas, "-:tT", "") as u_cas, ;
       9977.7 as stul, "*K*" as autor, "_PLATBA_NEFISKALNI_" as fik, "_PLATBA_NEFISKALNI_" as bkp ;
       WHERE LEFT(cas, 8) = m.minuly_mesic FROM automaty INTO TABLE (m.gcpri_path + "automaty_do_sk_plt")

SELECT DISTI uzaverka, id_kas, id_man, "1" as c_mena, "*K*" as autor, {} as uct_den ;
       GROUP BY 1,2,3 WHERE LEFT(cas, 8) = m.minuly_mesic FROM automaty INTO TABLE (m.gcpri_path + "automaty_do_sk_sez")
REPLACE ALL uct_den WITH DATE( 2000+VAL(LEFT(uzaverka,2)), VAL(SUBSTR(uzaverka,3,2)), VAL(SUBSTR(uzaverka,5,2)) )

SELECT STRTRAN(LEFT(cas, 10), "-", "") as datum, u_cis, - prachy as castka, nazev as poznamka, cis_kred, RIGHT(cas, 8) as cas, .T. as zapsano, idklienta, "CZK" as hk_mena ;
       WHERE LEFT(cas, 8) = m.minuly_mesic FROM automaty INTO TABLE (m.gcpri_path + "automaty_do_hkredobr")

UPDATE automaty_do_hkredobr SET cis_kred = hot_kred.cis_kred FROM (m.gchom_path + "hot_kred") ;
       WHERE empt(automaty_do_hkredobr.cis_kred) AND automaty_do_hkredobr.idklienta = hot_kred.idklienta


*     *     *     *     *     *     *     *     *     *     *     *     *     *     *     *     
PROCEDURE zapis_do_sk

uzaverka_zacatek = SUBSTR(STRTRAN(m.minuly_mesic, "-", ""), 3)

USE (m.gccen_path + "sk_sez")  AGAIN
DELETE FOR id_kas        = KASA_AUTOMAT .and. LEFT(uzaverka, 4) = m.uzaverka_zacatek
APPEND FROM (m.gcpri_path + "automaty_do_sk_sez")

USE (m.gccen_path + "sk_pol")  AGAIN 
DELETE FOR id_kas        = KASA_AUTOMAT .and. LEFT(uzaverka, 4) = m.uzaverka_zacatek
APPEND FROM (m.gcpri_path + "automaty_do_sk_pol")

USE (m.gccen_path + "sk_fin")  AGAIN
DELETE FOR id_kas        = KASA_AUTOMAT .and. LEFT(uzaverka, 4) = m.uzaverka_zacatek
APPEND FROM (m.gcpri_path + "automaty_do_sk_plt")

USE (m.gccen_path + "sk_plt")  AGAIN
DELETE FOR LEFT(u_cis,2) = KASA_AUTOMAT .and. LEFT(uzaverka, 4) = m.uzaverka_zacatek
APPEND FROM (m.gcpri_path + "automaty_do_sk_plt")

USE (m.gchom_path + "hkredobr") AGAIN 
DELETE FOR LEFT(u_cis,2) = KASA_AUTOMAT .and. LEFT(datum, 6) = "20" + m.uzaverka_zacatek
APPEND FROM (m.gcpri_path + "automaty_do_hkredobr")


*    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *
PROCEDURE  importuj_csv

STORE KASA_AUTOMAT to id_kas, id_man

pocet_souboru = ADIR(qqq, m.import_path + m.import_filename + "*.csv")
FOR iii = 1 TO m.pocet_souboru
    jmeno_soub = m.import_path + qqq(iii, 1)
    IF ATC("_" + m.tenhle_mesic, m.jmeno_soub) + ATC("_" + m.minuly_mesic, m.jmeno_soub) = 0
        WAIT m.jmeno_soub + " vynechavam" AT 22,22 WINDOW TIMEOUT .5
        LOOP 
    ENDIF 
    WAIT m.jmeno_soub + "           " AT 22,22 WINDOW TIMEOUT .5
    WAIT m.jmeno_soub + "           " AT 22,22 WINDOW NOWAIT 
    fh = FOPEN(m.jmeno_soub )
    IF m.fh > 0 
        DO while ! feof(m.fh)

*    os. è.;konc.cena;datum;automat;volba;id.odb. - bacha, os.c. je pepinovo idklienta
*    1631;4.00;2021-03-25T22:58:02;3;16;18308
*    1923;4.00;2021-03-25T23:15:24;5;1;18310
*    1787;  4.00;  2021-03-25T23:16:24;  5;  1;  18311

            cis_kred = ""
            radek    = fgets(m.fh,333) + REPLICATE(ODD, 11)
            idklienta=       VAL( vrat_item() )
            prachy   =       VAL( vrat_item() )
            cas      =            vrat_item()
            nazev    = "Automat" + PADL(vrat_item(), 3, "_") + PADL(vrat_item(), 3, "_")
            u_cis    =            vrat_item()
            u_cis    = KASA_AUTOMAT + RIGHT("00000" + m.u_cis, 5)
            uzaverka = SUBSTR(STRTRAN(m.cas, "-", ""), 3, 6) + " 23:99"
            IF m.prachy = 57.00
                nazev = m.nazev + "  KRABICKA"
                c_karty = C_KARTY_KRABICKA
            ELSE 
                c_karty = C_KARTY_AUTOMAT
            ENDIF 

            IF m.prachy # 0
                SELECT automaty
                LOCATE FOR uzaverka = m.uzaverka .and. u_cis = m.u_cis
                IF .not. FOUND()
                    INSERT INTO automaty FROM MEMVAR 
                ENDIF 
            ENDIF                 
        ENDDO 
        FCLOSE(m.fh)

    ELSE
        WAIT             "nelze otevrit soubor " + m.jmeno_soub WINDOW TIMEOUT 3 AT 22,22
        vysledek = m.vysledek + "nelze otevrit " + m.jmeno_soub
    ENDIF 
ENDFOR 
WAIT CLEAR 


*    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *
FUNCTION vrat_item
PRIVATE at1, retval
at1     = at(ODD, m.radek, 1)
retval  = LEFT(m.radek, m.at1-1)
radek   = SUBSTR(m.radek, m.at1+1)
RETURN ALLTRIM(STRTRAN(m.retval, '"', ''))


*    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *
PROCEDURE dopln_karty_pokud_chybi
USE (m.gccen_path + "karty")
LOCATE FOR c_karty = C_KARTY_AUTOMAT
IF .not. FOUND()
    INSERT INTO karty (c_karty, nazev) VALUES (C_KARTY_AUTOMAT, "Automat")
ENDIF 
LOCATE FOR c_karty = C_KARTY_KRABICKA
IF .not. FOUND()
    INSERT INTO karty (c_karty, nazev) VALUES (C_KARTY_KRABICKA, "Automat - krabicka")
ENDIF 
USE 
