*    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *
*
*     program cte CSV soubor z kas epos/hasam a zapise do SK__*
*     importovane soubory prejmenuje z *.csv na *.OK
*
* 2024-02-21 - 
* 2024-03-20 - oprava dvakrat nasobeneho mnozstvi*prachy
*            - nektere konstantni fieldy ukazuji na ucty z eposu, aby se snaze poznaly v datech
*
*
*    *    *    *    *    *    *    *    *    *    *    *      #> ver. 2024-03-20 <#       *    *    *    *    *    *    *    *    *    *    *    *    *
vysledek = IIF( VARTYPE(vysledek) = "C", m.vysledek, "" )      + "  v.2024-03-20"

#DEFINE IMPORT_DIR "C:\import_epos\"

CLOSE DATABASES 
ACTIVATE SCREEN 
_screen.Cls

DO open_database

importuj_csv()

IF zpracuj_eany()

    DO zapis_do_eskacek

ELSE 
   MESSAGEBOX("Nejsou vyplneny Foodove karty" + CHR(13) + "doplnte ve Vzorniky-Prevodni tab.EDI", 1+16, "Import EPOS")
ENDIF 

DO clos_data


*    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *
PROCEDURE  zpracuj_eany

#DEFINE EANPREFIX "KASAEPOS::"

cmd = "SELECT c_karty, ean, popis, koef, objem FROM food600.eany "
sql_select(m.cmd, "eany_seznam", "c_karty I, ean C(33), popis C(44), koef N(8,4), objem N(8,4)")

vsechno_pri_starem = .T.
SELECT DISTINCT id_karty, popis FROM sk__pom INTO CURSOR eposkarty
SCAN
    SCATTER MEMVAR 
    SELECT eany_seznam
    LOCATE FOR ean = EANPREFIX + TRANSFORM(m.id_karty, "@L 99999")
    IF FOUND() 
        IF c_karty = 0
            vsechno_pri_starem = .F.    && epos karta sice v bouraku je, ale nema vyplneno c_karty
        ELSE 
            SELECT sk__pom
            REPLACE FOR id_karty = m.id_karty c_karty WITH eany_seznam.c_karty
        ENDIF 
    ELSE 
        vsechno_pri_starem = .F.
        sql_cmd ="INSERT INTO food600.eany (ean, popis) VALUES ('" + EANPREFIX + TRANSFORM(m.id_karty, "@L 99999") + "', '" + ALLTRIM(m.popis) + "') "
        proved(m.sql_cmd )
    ENDIF 
ENDSCAN 
RETURN m.vsechno_pri_starem 


*    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    
FUNCTION proved (co_udelat)
WAIT m.co_udelat WINDOW NOWAIT 
vysledek = sql_exec( m.co_udelat )
zprava_do_logu = m.co_udelat + STR(m.vysledek, 5)
IF m.vysledek < 0
    DIMENSION chybaexe(9)
    chybaexe(2) = "vsechno v poradku"
    AERROR(chybaexe)
    MESSAGEBOX("SQL Chyba" + CHR(10) + chybaexe(2),64)
    zprava_do_logu = m.zprava_do_logu + " " + chybaexe(2)
ENDIF
small_log (m.zprava_do_logu, "import_epos")
WAIT CLEAR 







*    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *
PROCEDURE zapis_do_eskacek

PRIVATE hladajcstredisk, prach_prod, nakl_celk, cen_hlad, idriadok, hladajidkas, hladajckasy, hladajuzaverka, hladajcuzaverka

SELECT uct_den, c_skladu, SPACE(17) as uzaverka, 00000000 as c_uzaverka, 000000 as c_kas, 000000 as c_stredisk, "  " as id_kas, "  " as id_man, SUM(prachy) as prachy, file_name GROUP BY 1, 2 ;
       FROM sk__pom INTO TABLE (m.gcpri_path + "sk__uz")

REPLACE FOR c_skladu =  6 c_kas WITH  -6, c_stredisk WITH -46, id_kas WITH "06", id_man WITH "46"
REPLACE FOR c_skladu = 11 c_kas WITH -11, c_stredisk WITH -41, id_kas WITH "11", id_man WITH "41"

hladajidkas =  "KK"
hladajcuzaverka = 1
hladajuzaverka  = "1"

USE (m.sqlprefix+"rsksezuzaverka") IN 0 ALIAS sk_sez NODATA 
CURSORSETPROP("Buffering",5,"sk_sez")

USE (m.sqlprefix+"rskfinuzaverka") IN 0 ALIAS sk_fin 
CURSORSETPROP("Buffering",5,"sk_fin")

USE (m.sqlprefix+"rskpltuzaverka") IN 0 ALIAS sk_plt 
CURSORSETPROP("Buffering",5,"sk_plt")

USE (m.sqlprefix+"rskpoluzaverka") IN 0 ALIAS sk_pol
CURSORSETPROP("Buffering",5,"sk_pol")

u_cas   = "99 99:99"

SELECT sk__uz
SCAN

    SCATTER MEMVAR 
    ucet = VAL(RIGHT(STRTRAN(JUSTSTEM(m.file_name), "-", ""), 5))
    hladajidkas     = m.id_kas
    hladajcstredisk = m.c_stredisk
    najdi_uzaverku(m.uct_den)
    SELECT sk__uz
    REPLACE uzaverka WITH m.hladajuzaverka, c_uzaverka WITH m.hladajcuzaverka 

    REQUERY("sk_sez")
    INSERT INTO sk_sez (id_man,   id_kas  ,   uzaverka    ,   c_stredisk,   c_kas,     c_uzaverka   , c_uzav_kas, autor      , odecteno, uz_cislo, lastuzav, c_mena     ,  c_data, uct_den  , priznak) ;
       VALUE        (m.id_man, m.id_kas, m.hladajuzaverka, m.c_stredisk, m.c_kas, m.hladajcuzaverka,  0        , m.gcuser_id,    0    ,    1    , "D_EPOS",m.giakt_mena, m.cdata, m.uct_den,    "")
    tux(.t.,.t.,"sk_sez")

    REQUERY("sk_fin")
    INSERT INTO sk_fin (c_uzaverka,  ucet, autor,   u_cas, u_minut, mutace, cas_atrib, pocet_h, stul, text_sleva, text_link, ducis, round50, pohladavka, c_data) ;
        VALUES (m.hladajcuzaverka, m.ucet, "Epo", m.u_cas,    1   ,  ""   ,    "S"   ,  222   ,  0  ,    " "    ,    " "   ,"0001", 0, 0, m.cdata)
    tux(.t.,.t.,"sk_fin")

    REQUERY("sk_plt")
    INSERT INTO sk_plt (c_uzaverka, ucet, druh_pl , cena_pl , tip, mena , cena_mena, poradi, text,  c_data, fik, bkp) ;
        VALUE (m.hladajcuzaverka, m.ucet, "K_EPOS", m.prachy, 0  , "CZK",  m.prachy,   1   ,  "" , m.cdata, "EPOS", "EPOS")
    tux(.t.,.t.,"sk_plt")

    SELECT sk__pom
    SCAN FOR uct_den = m.uct_den .and. c_skladu = m.c_skladu
        SCATTER MEMVAR 
        INSERT INTO sk_pol ;
             (  c_uzaverka     ,   ucet,   c_stredisk     ,   c_karty,   prachy, prachy_puv,   dan,   dan_sazba,   mnozstvi, cen_hlad, k_odpoctu, poradi   ,  c_data ) VALUES ;
             (m.hladajcuzaverka, m.ucet, m.hladajcstredisk, m.c_karty, m.prachy, m.prachy  , m.dan, m.dan_sazba, m.mnozstvi,   "99"  , "X"      , m.c_karty, m.cdata )

        tux(.t.,.t.,"sk_pol", "requery")
    ENDSCAN 

    SELECT sk__uz
    TRY 
       RENAME ALLTRIM(m.file_name) TO STRTRAN(LOWER(ALLTRIM(m.file_name)), ".csv", ".OK")
    CATCH
    ENDTRY 
ENDSCAN 

DO pozavirej WITH "sk_fin,sk_pol,sk_plt,sk_sez,sk_pom"



*    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    
FUNCTION najdi_uzaverku(datum_dokl)
hladajuzaverka=dajpoleuzaverka(m.datum_dokl, .F.)  && .F. protoze neni z davky
SELECT 0
USE (m.sqlprefix+"rskuzahladajcuzaverky") ALIAS maxuz 
IF RECCOUNT()=0
     INSERT INTO maxuz (uzaverka, id_kas, c_data) VALUES (m.hladajuzaverka, m.hladajidkas, m.cdata)
     tux(.t.,.t.,"maxuz", "requery")
ENDIF 
GO TOP 
hladajcuzaverka = maxuz.idriadok
hladajuzaverka  = maxuz.uzaverka
USE IN maxuz





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

CREATE TABLE (m.gcpri_path + "sk__pom1") FREE (doklad N(11), uct_den D, id_karty N(11), castkanet N(11,3), castkadph N(11,3), dan_sazba N(6,2), popis C(44), c_skladu N(9), cisdokl N(11), poradi N(11), mnozstvi N(11,3), file_name C(99))
INDEX on id_karty TAG id_karty

o_set_point = SET("POINT")
SET POINT TO "."
#DEFINE ODD          ";"

retv = -99
pocet = ADIR(qqq, IMPORT_DIR + "*.csv")
FOR iii = 1 TO m.pocet

    importuj_csv_pokrac (IMPORT_DIR + qqq(iii, 1))

*ERASE (m.gcpri_path + "import_file*.csv")
ENDFOR 

SELECT uct_den, c_skladu, dan_sazba, id_karty, popis, SUM(castkanet+castkadph) as prachy, SUM(castkadph) as dan, SUM(mnozstvi) as mnozstvi, file_name, 0000000 as c_karty GROUP BY 1,2,3,4 FROM sk__pom1 INTO TABLE (m.gcpri_path + "sk__pom")

SET POINT TO (m.o_set_point)
WAIT "csv hotovo" WINDOW TIMEOUT 3
RETURN m.retv

*    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *
FUNCTION importuj_csv_pokrac (file_name)
WAIT m.file_name WINDOW NOWAIT 
fh = FOPEN(m.file_name)
IF m.fh > 0 

    DO while ! feof(m.fh)

*    ExtDoklad;DatumPripad;Strana;CisloUcet;CastkaNetto;CastkaDPH;SazbaDPH;Popis;CisloNakladovyOkruh;Utvar;CisloDokladu;CisloRadku;DanovyKlic_Kod
*    VD2024/0041511; 2024-02-01; 1; 214;40,18;4,82;12,00;Adelholzener SPORT;;6;1685837;2920950;1.0000
*    VD2024/0041598;2024-02-01;1;31;26,79;3,21;12,00;Natura neperliv†/jemno;;6;1685924;2921053;1.0000

        radek     = fgets(m.fh,555) + REPLICATE(ODD, 22)
        doklad    =       vrat_item()    && ExtDoklad
        uct_den   =       vrat_item()    && DatumPripad
                  =   VAL(vrat_item())   && Strana
        id_karty  =   VAL(vrat_item())   && CisloUcet
        castkanet =   VAL(vrat_item())   && CastkaNetto
        castkadph =   VAL(vrat_item())   && CastkaDPH
        dan_sazba =   VAL(vrat_item())   && SazbaDPH
        popis     =       vrat_item()    && Popis
                  =   VAL(vrat_item())   && CisloNakladovyOkruh
        c_skladu  =   VAL(vrat_item())   && Utvar
        cisdokl   =   VAL(vrat_item())   && Cislodokladu
        poradi    =   VAL(vrat_item())   && CisloRadku
        mnozstvi   =   VAL(vrat_item())   && DanovyKlic_Kod


        at1     = at("/", m.doklad)
        doklad  = VAL(SUBSTR(m.doklad, m.at1+1))

        TRY
            uct_den = DATE(VAL(LEFT(m.uct_den, 4)), VAL(SUBST(m.uct_den, 6,2)), VAL(SUBST(m.uct_den, 9,2)))
        CATCH
            uct_den = {^2011-11-11}
        ENDTRY 

        IF m.id_karty # 0
            INSERT INTO sk__pom1 FROM MEMVAR 
        ENDIF 
        
    ENDDO 
    FCLOSE(m.fh)
ELSE
    WAIT "nelze otevrit soubor " + m.file_name WINDOW TIMEOUT 5 AT 22,22
ENDIF 
WAIT CLEAR 
RETURN m.fh


*    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *
FUNCTION vrat_item
PRIVATE at1, retval
at1     = at(ODD, m.radek)
retval  = CHRTRAN(ALLTRIM(LEFT(m.radek, m.at1-1)), ",µÖØ¦íì¡ìŸü¬ œç¢§ýæ", ".AIeZYyiycRCatsozrS")
radek   = SUBSTR(m.radek, m.at1+1)
RETURN m.retval

