*    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *
* program cte transakce a eskacka z ceske televize a vygeneruje exportni soubor pro mzdy a  kontrolni soubor CSV
*
* 2018-10-15 - prvnich par dni v mesici se generuje z minuleho, pak jiz z aktualniho mesice
* 2018-12-03 - testovaci karty (client_id < 99) se neposilaji
*            - celkova castka je BEZ FKSP
* 2019-01-20 - nadefinovany promenne, ktere ve ftp chybi (gchom_path)
*            - hk_dotace se scanuji jen pro radky z daneho mesice
*            - exportni soubor se kopiruje do REMOTE_PATH (u:\)
*            - zaporne castky v cen_zbytek se presunou se do cen_hl_nez 
* 2019-03-04 - prachy ze sk_pol se vynasobi koefincientem slev (cena_slev/cena_celk)
* 2019-03-20 - jako dotovane hlavni je bran i vazeny spart BUF, mnozstvi se bere jako SIGN(mnozstvi)
* 2019-07-04 - jidla, nezarazena v HLJ, ale oznacena jako dotovana, se zapoctou do ceny hlavnich zvyhodnenych
*            - stazene transakce se kopiruji do dbf s postfixem _debug, aby pri se opakovanem pousteni necekalo na opetovny download (alternativni procedure stahni_transakce)
* 2019-10-31 - zapocitaji se i transakce z kavovaru, i kdyz nejsou v eskackach
* 2020-02-06 - pokud chybi radek v hk_dotace, ale v sk_plt je platba FKSP, je zapocitano z sk_plt
* 2020-03-31 - polovicni porce hlavnich jidel v cen_hl=3 se pocitaji do ostatni trzby (nikoli jako hl.j.)
* 2020-06-08 - lepe osetreno kafe pro VIP karty, generuji se dva detailni soubory
* 2020-09-20 - export_path se denne meni, aby zustavaly pracovni soubory pro porovnani, jestli se v prubehu mesice zmenily
* 2020-10-01 - do export_path se ulozi i databaze karty+skupiny
* 2022-02-02 - pro pripad duplikace cisel uctu se pokusi najit uzaverku podle u_cas na trans_at
*            - optimalizovano ne-pridavani recordu do transactions - na zacatku se z fin vymazou radky, ktere nemaji v plt K_HOTEL2 ani P_FKSP
*            - generuje se report.txt s exportovanejma totalama pro kontrolu
*            - kontroluje se fin.cena_slev proti souctu plt.cena_pl, chyba se zaznamena do reportu
*            - generuje se fin_casy.dbf pro kontrolu nesrovnatelnosti casu uzaverky a u_cas (ucet uzavren v jiny den)
* 2022-04-05 - do GET transakci jeste pridan filter description#"VYUCT"
* 2022-06-30 - exportni soubor pro mzdy se kopiruje jen prvniho dne v mesici
* 2022-08-13 - z sk_txt se zkusi ziskat client_id, pokud se insertuje z sk_fin
*            - cislo transakce z kavovaru se pouzije jako u_cis
*
*    *    *    *    *    *    *    *    *    *    *    *      #> ver. 2022-08-31 <#       *    *    *    *    *    *    *    *    *    *    *    *    *
#DEFINE PGM_VERZE                                               "ver. 2022-08-31"

#DEFINE REMOTE_PATH  "u:\"
#DEFINE CRLF         CHR(13)+CHR(10)
#DEFINE ODD          " "
#DEFINE POCET_DNI_K_MINULEMU_MESICI 25

#DEFINE PAGESIZE      "555"
#DEFINE MAX_PAGECOUNT 9999

HTTP_READ_ONLY_MODE = .T.

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

SET REPROCESS TO AUTOMATIC
DO clos_data
o_set_point = SET("POINT")
SET POINT TO "."
ACTIVATE SCREEN 

mesicek = LEFT(TTOC(DATE() - POCET_DNI_K_MINULEMU_MESICI, 3), 8)    && (2018-11-) prvnich nekolik dni v mesici se generuje z minuleho, pak jiz z aktualniho mesice

tv_path = ".\telka" + TRANSFORM(DAY(DATE()), "@L 99") + "\"
TRY
    MKDIR (m.tv_path)
CATCH
ENDTRY 
zurnalujx(PROGRAM() + "  " + PGM_VERZE, CRLF+CRLF)

exportni_soubor = tv_path + SUBST(TTOC(DATE() - POCET_DNI_K_MINULEMU_MESICI,1), 3,4) + "0001"

IF VARTYPE(gchom_path) = "U"    && pouzije se pri spusteni z prenosu FTP
    gchom_path = "c:\alto\d_televize\"
ENDIF

DO (LOWER(JUSTPATH(sys(16))) + "\ppgm_http") WITH "https://alto:1991.Altopraha@ceskatelevize-backend.production-c.asrv.cz/"

DO stahni_transakce
*DO stahni_transakce_debug

DO zpracuj_eskacka

SELECT transactions
DELETE FOR priznak = "z FIN" .and. ATC("K_HOTEL2", platba) + ATC("P_FKSP", platba) = 0
DELETE FOR "HK_D" $ priznak  .and. ATC("K_HOTEL2", platba) + ATC("P_FKSP", platba) = 0
REPLACE FOR  "Z:" $ descript .and. "V:" $ descript ;
        platba WITH "K_HOTEL2", cena_slev WITH amount, cena_celk WITH amount, cen_zbytek WITH amount, u_cas WITH LEFT(trans_at, 13), priznak WITH ALLTRIM(priznak) + " Kafe"

* oprava chybejicich radku v hk_dotace
REPLACE FOR fksp = 0 .and. cen_fksp > 0 fksp WITH cen_fksp, pocdot_hk WITH poc_fksp

DO generuj_csv

SET POINT TO (m.o_set_point)
DO oznam_hotovo
zurnalujx("          --- hotovo ---")


*  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  
PROCEDURE zpracuj_eskacka
CLEAR 
? left(time(), 5) + " zpracuj_eskacka " + PROGRAM(PROGRAM(-1)-1)
USE (m.gchom_path + "\data\karty")   IN 0 NOUPDATE
USE (m.gchom_path + "\data\skupiny") IN 0 NOUPDATE 
USE (m.gchom_path + "\data\sk_fin")  IN 0 NOUPDATE 
USE (m.gchom_path + "\data\sk_plt")  IN 0 NOUPDATE 
USE (m.gchom_path + "\data\sk_pol")  IN 0 NOUPDATE 
USE (m.gchom_path + "\data\sk_txt")  IN 0 NOUPDATE 


*    *    *    *    *    *    *    *    *    *         FIN  FIN  FIN  FIN            *    *    *    *    *    *    *    *    *    *
SELECT uzaverka, u_cis, LEFT(u_cas, 4) + "-" + SUBSTR(u_cas, 5,2) + "-" + SUBSTR(u_cas, 7,2) + "T" + SUBSTR(u_cas, 9,2) as u_cas, m.mesicek as mesicek, SPACE(20) as stav ;
       FROM sk_fin INTO TABLE (tv_path + "fin_casy") READWRITE
REPLACE ALL mesicek WITH SUBSTR(STRTRAN(mesicek, "-", ""), 3)
REPLACE ALL u_cas   WITH SUBSTR(STRTRAN(u_cas  , "-", ""), 3)
DELETE FOR "*" $ uzaverka
DELETE FOR uzaverka < mesicek
DELETE FOR LEFT(uzaverka, 6) = LEFT(u_cas, 6)
USE 


SELECT uzaverka, u_cis, LEFT(u_cas, 4) + "-" + SUBSTR(u_cas, 5,2) + "-" + SUBSTR(u_cas, 7,2) + "T" + SUBSTR(u_cas, 9,2) as u_cas, text_sleva, cena_celk, cena_slev, ;
       001.000000000 as slev_koef, SPACE(44) as platba, 00000000.00 as cena_pl ;
       FROM sk_fin INTO TABLE (tv_path + "fin") READWRITE
DELE FOR LEFT(u_cas, 8) # m.mesicek
PACK
INDEX on uzaverka + u_cis TAG uz_ucet


*    *    *    *    *    *    *    *    *    *         PLT  PLT  PLT  PLT            *    *    *    *    *    *    *    *    *    *
SELECT u_cis, uzaverka, druh_pl, cena_pl ;
       WHERE uzaverka + u_cis IN (SELECT uzaverka + u_cis FROM fin) FROM sk_plt INTO TABLE (tv_path + "plt") READWRITE 
INDEX on uzaverka + u_cis TAG uz_ucet

SCAN 
    SCATTER MEMVAR 
    SELECT fin
    REPLACE FOR uzaverka + u_cis = m.uzaverka + m.u_cis platba WITH ALLTRIM(platba) + " " + ALLTRIM(m.druh_pl), cena_pl WITH cena_pl + m.cena_pl
ENDSCAN 
SELECT fin
DELETE FOR ATC("K_HOTEL2", platba) + ATC("P_FKSP", platba) = 0
PACK 
COUNT fOR cena_pl # cena_slev TO pocet_rozdilu_fin_vs_plt
IF m.pocet_rozdilu_fin_vs_plt > 0
    zurnalujx("pocet_rozdilu_fin_vs_plt: " + STR(m.pocet_rozdilu_fin_vs_plt, 5))
ENDIF 

REPLACE  FOR (cena_celk # 0) slev_koef WITH ROUND(cena_slev / cena_celk, 9)


*    *    *    *    *    *    *    *    *    *         PLT  PLT  PLT  PLT   podruhe         *    *    *    *    *    *    *    *    *    *
SELECT u_cis, uzaverka, druh_pl, cena_pl, 00000000.00 as amount, SPACE(22) as stav ;
       WHERE uzaverka + u_cis IN (SELECT uzaverka + u_cis FROM fin) FROM sk_plt INTO TABLE (tv_path + "plt") READWRITE 
INDEX on uzaverka + u_cis TAG uz_ucet

SELECT druh_pl, SUM(cena_pl) as cena_pl GROUP BY 1 FROM plt INTO CURSOR platby_davidovi


*    *    *    *    *    *    *    *    *    *         TXT  TXT  TXT  TXT            *    *    *    *    *    *    *    *    *    *
SELECT u_cis, uzaverka, txt, SPACE(11) AS client_id ;
       WHERE uzaverka + u_cis IN (SELECT uzaverka + u_cis FROM fin) FROM sk_txt INTO TABLE (tv_path + "txt") READWRITE 
REPLACE FOR "/" $ txt client_id WITH LEFT(txt, AT("/", txt) - 1)
INDEX on uzaverka + u_cis TAG uz_ucet


*    *    *    *    *    *    *    *    *    *         KKK  KKK  KKK  KKK            *    *    *    *    *    *    *    *    *    *
SELECT k.c_karty, k.nazev, k.druh_kalk, k.c_druhk, s.spart_kas FROM karty k LEFT JOIN skupiny s ON s.c_druh == k.c_druhk INTO TABLE (m.tv_path + "kkk")
REPLACE FOR ISNULL(spart_kas) spart_kas WITH "---"
INDEX on c_karty TAG c_karty 

*    *    *    *    *    *    *    *    *    *         POL  POL  POL  POL            *    *    *    *    *    *    *    *    *    *
SELECT u_cis, uzaverka, c_karty, nazev, mnozstvi, prachy, prachy as prachy_old, cen_hlad, u_time, dotacia, SPACE(3) as spart, SPACE(10) as druh ;
       WHERE uzaverka + u_cis IN (SELECT uzaverka + u_cis FROM fin) FROM sk_pol INTO TABLE (tv_path + "pol") READWRITE 
SET RELATION TO c_karty INTO kkk
REPLACE ALL spart WITH kkk.spart_kas, druh WITH kkk.druh_kalk

REPLACE FOR spart = "BUF"       spart WITH "HLJ", mnozstvi WITH SIGN(mnozstvi)    && pocitaji se porce, nikoliv gramaz
REPLACE FOR spart # "HLJ"       spart WITH ""
REPLACE FOR druh # "Polevky   " druh  WITH ""
REPLACE FOR spart = "HLJ" .and. u_time = "/2" .and. cen_hlad = "3" spart WITH ""    && detske porce s sebou pro zamestnance

SET RELATION TO uzaverka + u_cis INTO fin
REPLACE ALL prachy WITH ROUND(prachy * fin.slev_koef, 2)

USE IN sk_fin
USE IN sk_plt
USE IN sk_pol
USE IN sk_txt
USE IN karty
USE IN skupiny

IF FILE(m.gchom_path + "hk_dotace.dbf")
    SELECT 0
    USE (m.gchom_path + "hk_dotace") NOUPDATE 
    hk_mesicek = LEFT(STRTR(m.mesicek, "-", ""), 6)
    ? left(time(), 5) + " scan hk_dotace - obdobi " + m.hk_mesicek + " "
    STORE 0 TO pocet_insertu, pocet_replace
    SCAN FOR LEFT(datum, 6) = m.hk_mesicek
        pocitej(2000)
	    SCATTER MEMVAR 
	    SELECT transactions
	    LOCATE FOR u_cis = m.u_cis 
	    IF FOUND()
	        REPLACE pocdot_hk WITH pocdot_hk + m.pocdotaci, fksp WITH fksp + m.fksp, priznak WITH ALLTRIM(priznak) + "<hk_dot>", uzaverky WITH ALLTRIM(uzaverky) + "<" + datum + ">"
                pocet_replace = m.pocet_replace + 1
	    ELSE 
	        INSERT INTO transactions (client, u_cis, pocdot_hk, fksp, priznak) VALUES (m.idklienta, m.u_cis, m.pocdotaci, m.fksp, "z HK_DOT " + m.datum + " " + m.cas)
                pocet_insertu = m.pocet_insertu + 1
	    ENDIF 
    ENDSCAN
    zurnalujx("from HK_DOTACE replaced TRANSACTIONS:" + TRANSFORM(m.pocet_replace, "999 999") + "    inserted:" + TRANSFORM(m.pocet_insertu, "999 999"))
ENDIF


SELECT transactions
SET RELATION TO client INTO clients
REPLACE ALL client_id WITH clients.client_id, clientname WITH ALLTRIM(clients.surname) + " " + ALLTRIM(clients.firstname) + " (" + ALLTRIM(clients.firma) + ")"
USE IN clients 

? left(time(), 5) + " scan1fin" + str(recc(),7) + " "
SELE fin
SCAN 
    pocitej(3000)
    SCATTER MEMVAR 
    SELECT transactions
    LOCATE FOR u_cis = m.u_cis .and. LEFT(trans_at, 10) = LEFT(m.u_cas, 10)
    IF FOUND()
        GATHER FIELDS u_cas, text_sleva, cena_celk, cena_slev, uzaverka MEMVAR 
        REPLACE priznak WITH ALLTRIM(priznak) + "[fin]", uzaverky WITH ALLTRIM(uzaverky) + "[" + LEFT(m.uzaverka, 6) + "]"
        SELECT fin
        REPLACE u_cas WITH "HOTOVO"
    ENDIF 
ENDSCAN 

? left(time(), 5) + " scan2fin" + str(recc(),7) + " "
SELE fin
SCAN FOR u_cas # "HOTOVO"
    pocitej(3000)
    SCATTER MEMVAR 
    SELECT transactions
    LOCATE FOR u_cis = m.u_cis .and. EMPTY(u_cas)
    IF FOUND()
        GATHER FIELDS u_cas, text_sleva, cena_celk, cena_slev, uzaverka MEMVAR 
        REPLACE priznak WITH ALLTRIM(priznak) + "[fin]", uzaverky WITH ALLTRIM(uzaverky) + "[" + LEFT(m.uzaverka, 6) + "]]"
    ELSE 
        SELECT txt
        LOCATE FOR uzaverka + u_cis = m.uzaverka + m.u_cis
        SCATTER FIELDS client_id MEMVAR 
        description = m.client_id

        priznak = "z FIN"
        INSERT INTO transactions FROM MEMVAR 
    ENDIF 
ENDSCAN 

SELECT u_cis, dotacia, spart, druh, SUM(mnozstvi) AS mnozstvi, SUM(prachy) AS prachy ;
       GROUP BY u_cis, dotacia, spart, druh FROM pol INTO TABLE (tv_path + "pol_sum")
? left(time(), 5) + " scan pol" + str(recc(),7) + " "
SCAN  
    pocitej(3000)
    SCATTER MEMVAR 
    SELECT transactions
    LOCATE FOR u_cis = m.u_cis
    IF FOUND()
        DO CASE 
        CASE m.spart = "HLJ" .AND. m.dotacia = "D"
            REPLACE cen_hl_zvy WITH cen_hl_zvy + m.prachy, poc_hl_zvy WITH poc_hl_zvy + m.mnozstvi, pocdot_sk WITH pocdot_sk + m.mnozstvi

        CASE m.spart = "HLJ" .AND. m.dotacia # "D"
            REPLACE cen_hl_nez WITH cen_hl_nez + m.prachy, poc_hl_nez WITH poc_hl_nez + m.mnozstvi

        CASE m.dotacia = "D"    &&&&&&       pro pripad, ze kasa bude zaznamenavat dotaci i pro jine sparty
            REPLACE cen_hl_zvy WITH cen_hl_zvy + m.prachy, poc_hl_zvy WITH poc_hl_zvy + m.mnozstvi, pocdot_sk WITH pocdot_sk + m.mnozstvi, priznak WITH ALLTRIM(priznak) + " dotacia"

        CASE m.druh = "Polevky   "
            REPLACE cen_polevk WITH cen_polevk + m.prachy, poc_polevk WITH poc_polevk + m.mnozstvi

        OTHERWISE 
            REPLACE cen_zbytek WITH  cen_zbytek + m.prachy
        ENDCASE 
    ELSE
        priznak = "z POL"
        INSERT INTO transactions FROM MEMVAR 
    ENDIF 
ENDSCAN 

SELECT plt
? left(time(), 5) + " scan1plt" + str(recc(),7) + " "
SCAN 
    pocitej(3000)
    SCATTER MEMVAR 
    SELECT transactions
    LOCATE FOR u_cis = m.u_cis .and. uzaverka = m.uzaverka
    IF FOUND()
        REPLACE platba WITH ALLTRIM(platba) + " " + ALLTRIM(m.druh_pl)
        IF m.druh_pl = "P_FKSP  " .and. m.cena_pl # 0
            REPLACE poc_fksp with poc_fksp + sign(m.cena_pl), cen_fksp WITH cen_fksp + m.cena_pl
            REPLACE priznak WITH ALLTRIM(priznak) + "{pl}", uzaverky WITH ALLTRIM(uzaverky) + "{" + LEFT(m.uzaverka, 6) + "}"
            SELECT plt
            REPLACE stav WITH "HOTOVO"
        ELSE
            REPLACE priznak WITH ALLTRIM(priznak) + "{pl}", uzaverky WITH ALLTRIM(uzaverky) + "{" + LEFT(m.uzaverka, 6) + "}"
            SELECT plt
            REPLACE stav WITH "HOTOVO", amount WITH transactions.amount
        ENDIF 
    ENDIF 
ENDSCAN

? left(time(), 5) + " scan2plt" + str(recc(),7) + " "
SCAN FOR stav # "HOTOVO"
    pocitej(3000)
    SCATTER MEMVAR 
    SELECT transactions
    LOCATE FOR u_cis = m.u_cis
    IF FOUND()
        REPLACE platba WITH ALLTRIM(platba) + " " + ALLTRIM(m.druh_pl)
        IF m.druh_pl = "P_FKSP  " .and. m.cena_pl # 0
            REPLACE poc_fksp with poc_fksp + sign(m.cena_pl), cen_fksp WITH cen_fksp + m.cena_pl
            REPLACE priznak WITH ALLTRIM(priznak) + "{pl}}", uzaverky WITH ALLTRIM(uzaverky) + "{" + LEFT(m.uzaverka, 6) + "}}"
        ELSE 
            REPLACE priznak WITH ALLTRIM(priznak) + "{pl}}", uzaverky WITH ALLTRIM(uzaverky) + "{" + LEFT(m.uzaverka, 6) + "}}"
            SELECT plt
            REPLACE stav WITH "HOTOVO napodruhy", amount WITH transactions.amount
        ENDIF 
    ELSE
        priznak = "z PLT"
        platba  = ALLTRIM(m.druh_pl)
        cena_slev = m.cena_pl
        INSERT INTO transactions FROM MEMVAR 
        SELECT plt
        REPLACE stav WITH "NENI V TRANSAKCICH"
    ENDIF 
ENDSCAN


*  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  
PROCEDURE generuj_csv

? left(time(), 5) + " transactions - generating " + UPPER(m.exportni_soubor)

SELECT client_id, clientname, SUM(amount) as amount, ;
       SUM(cena_slev) as cena_slev, SUM(cena_celk) as cena_celk, ;
       SUM(cen_hl_zvy) as cen_hl_zvy, SUM(cen_hl_nez) as cen_hl_nez, SUM(cen_fksp) as cen_fksp, SUM(cen_polevk) as cen_polevk, SUM(cen_zbytek) as cen_zbytek, ;
       SUM(poc_hl_zvy) as poc_hl_zvy, SUM(poc_hl_nez) as poc_hl_nez, SUM(poc_fksp) as poc_fksp, SUM(poc_polevk) as poc_polevk, SUM(pocdot_sk) as pocdot_sk, SUM(pocdot_hk) as pocdot_hk, 000000000.00 as srazka ;
       GROUP BY 1 FROM (tv_path + "transactions") INTO TABLE (tv_path + "trans_sum") READWRITE 


*     *    *     *    *     *    korekce zaporne castky v cen_zbytek - presune se do cen_hl_nez. a kdyz je zapor i tam, presune se to do cen_hl_nez
REPLACE FOR cen_zbytek < 0 cen_hl_nez WITH cen_hl_nez + cen_zbytek, cen_zbytek WITH 0
REPLACE FOR cen_hl_nez < 0 cen_hl_zvy WITH cen_hl_zvy + cen_hl_nez, cen_hl_nez WITH 0

STRTOFILE("", m.exportni_soubor + ".txt")
?? " scan"
SCAN FOR BETWEEN(VAL(client_id), 99, 666000)
    srazka = cen_hl_zvy + cen_hl_nez + cen_zbytek + cen_polevk - cen_fksp
    REPLACE srazka WITH m.srazka
    radek = "0001" + ODD + PADL(ALLT(client_id),6) + ODD + nx2(poc_hl_zvy + poc_hl_nez, 3) + ODD + "000" + ODD + nx2(srazka, 10,2)
    radek = m.radek + ODD + nx2(poc_polevk, 3) + ODD + nx2(cen_polevk, 10,2)    &&   polevky
    radek = m.radek + ODD + nx2(poc_hl_zvy, 3) + ODD + nx2(cen_hl_zvy, 10,2)    &&   zvyhodnena hl. jidla
    radek = m.radek + ODD + nx2(poc_hl_nez, 3) + ODD + nx2(cen_hl_nez, 10,2)    && NEzvyhodnena hl. jidla
    radek = m.radek + ODD + nx2(poc_fksp  , 3) + ODD + nx2(cen_fksp  , 10,2)    &&   FKSP
    radek = m.radek                       + ODD + nx2(cen_zbytek, 10,2)         &&   doplnkovy prodej
    radek = m.radek + ODD + nx2(srazka + cen_fksp, 10,2)                &&   celkem
    STRTOFILE(radek + CRLF, m.exportni_soubor + ".txt", 1)
    pocitej(300)
ENDSCAN 
vysledek = m.vysledek + CRLF + CHR(9) + "vygenerovan soubor " + upper(m.exportni_soubor + ".TXT")

SELECT trans_sum
SUM srazka TO srazka_sum
zurnalujx("Celkem srazka Aljosovi   " + TRANSFORM(m.srazka_sum, "999 999 999.00"))

SELECT platby_davidovi
SCAN FOR cena_pl # 0
    zurnalujx("       za platbu " + druh_pl + TRANSFORM(cena_pl, "999 999 999.00"))
ENDSCAN 


*    EXPORT DETAILNICH DAT
#DEFINE OD "; "

?? "  CSV: transactions"
SELECT transactions
INDEX on client_id + u_cas TAG cl_cas
COPY ALL TO (m.exportni_soubor)
STRTOFILE("osobni cislo; jmeno; cas; transact_at; cislo uctu; text; kind; cen_hl_zvy; poc_hl_zvy; cen_hl_nez; poc_hl_nez; cen_polev; poc_polev; cen_zbytek; cen_fksp; fksp; pocet_fksp; poc_dot_sk; poc_dot_hk" + CRLF, m.exportni_soubor + ".csv")
SCAN FOR BETWEEN(VAL(client_id), 99, 666000)
    radek = PADL(ALLT(client_id),7) + OD + tx2(clientname) + OD + tx(u_cas) + OD + tx(trans_at) + OD + tx(u_cis) + OD + tx(text_sleva) + OD + tx(kind)
    radek = m.radek + OD + nx3(cen_hl_zvy,9,2) + OD + nx3(poc_hl_zvy,2) + OD + nx3(cen_hl_nez,9,2) + OD + nx3(poc_hl_nez,2)
    radek = m.radek + OD + nx3(cen_polevk,9,2) + OD + nx3(poc_polevk,2) + OD + nx3(cen_zbytek,9,2)
    radek = m.radek + OD + nx3(cen_fksp,3) + OD + nx3(fksp,3) + OD + nx3(poc_fksp,2) + OD + nx3(pocdot_sk,2) + OD + nx3(pocdot_hk,2) + CRLF
    STRTOFILE(m.radek, m.exportni_soubor + ".csv", 1)
    pocitej(99999999)
ENDSCAN 

STRTOFILE("osobni cislo; jmeno; cas; transact_at; cislo uctu; text; kind; cen_hl_zvy; poc_hl_zvy; cen_hl_nez; poc_hl_nez; cen_polev; poc_polev; cen_zbytek; cen_fksp; fksp; pocet_fksp; poc_dot_sk; poc_dot_hk" + CRLF, m.exportni_soubor + "ostatni.csv")
SCAN FOR .not. BETWEEN(VAL(client_id), 99, 666000)
    radek = PADL(ALLT(client_id),7) + OD + tx2(clientname) + OD + tx(u_cas) + OD + tx(trans_at) + OD + tx(descript) + OD + tx(text_sleva) + OD + tx(kind)
    radek = m.radek + OD + nx3(cen_hl_zvy,9,2) + OD + nx3(poc_hl_zvy,2) + OD + nx3(cen_hl_nez,9,2) + OD + nx3(poc_hl_nez,2)
    radek = m.radek + OD + nx3(cen_polevk,9,2) + OD + nx3(poc_polevk,2) + OD + nx3(cen_zbytek,9,2)
    radek = m.radek + OD + nx3(cen_fksp,3) + OD + nx3(fksp,3) + OD + nx3(poc_fksp,2) + OD + nx3(pocdot_sk,2) + OD + nx3(pocdot_hk,2) + CRLF
    STRTOFILE(m.radek, m.exportni_soubor + "ostatni.csv", 1)
ENDSCAN 

IF DAY(DATE()) = 1
	TRY
	    COPY FILE (m.exportni_soubor + "*.*") TO (REMOTE_PATH + "*")
	    vysledek = m.vysledek + " a nakopirovan do " + REMOTE_PATH
	CATCH
	    vysledek = m.vysledek + CRLF + "[2T#R] -- NEPODARILO SE JEJ NAKOPIROVAT DO " + REMOTE_PATH
	    MESSAGEBOX("Nepodarilo se nakopirovat exportni soubor " + CRLF + "do " + REMOTE_PATH, 1+48, "Export konzumace", 6666)
	ENDTRY
ELSE
    vysledek = m.vysledek + " dnes se nekopiruje, holenku "
ENDIF 


*  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  
PROCEDURE stahni_transakce_debug
USE (tv_path + "clients") ORDER id IN 0

SELECT * FROM (tv_path + "transactions_debug") INTO TABLE (tv_path + "transactions")
INDEX on u_cis TAG u_cis
INDEX on id    TAG id

SELECT transactions



*  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  
PROCEDURE stahni_transakce
? m.mesicek

CREATE TABLE (tv_path + "clients") (id I, client_id C(11), surname C(22), firstname C(22), firma C(25))
INDEX on id TAG id 

zacatek_prace = SECONDS()
? "clients (" + PAGESIZE + ") "
FOR page_no = 1 TO MAX_PAGECOUNT 
    json = http_get( "kredit/crud/clients?page_size=" + PAGESIZE + "&page=" + ALLTRIM(STR(m.page_no)))
    next_page = get_string_item (m.json, "next")
    ?? nx(m.page_no) + " "
    FOR ii = 1 TO VAL(PAGESIZE)+3
       id        = get_integer_item (m.json, "id"       , ii)
       client_id = get_string_item  (m.json, "client_id", ii)
       card_id   = get_string_item  (m.json, "card_id"  , ii)
       firstname = get_string_item  (m.json, "firstname", ii)
       surname   = get_string_item  (m.json, "surname"  , ii)
       firma     = get_string_item  (m.json, "name"     , ii)
       IF m.id # 0
          INSERT INTO clients FROM MEMVAR 
       ENDIF 
    ENDFOR 

    IF ISNULL(m.next_page)
        EXIT 
    ENDIF 
ENDFOR 
?? "  " + ALLT(STR(SECONDS() - m.zacatek_prace)) + "sec"

*       *       *       *       *       *       *       *      *       *       *       *       *       *       *       *     
CREATE TABLE (tv_path + "transactions") ;
       (id I, client I, kind C(9), amount N(9,2), balance N(9,2), u_cis C(7), descript C(25), trans_at C(13), client_id C(11) NULL , clientname C(44), text_sleva C(22), u_cas C(13), platba C(33), ;
        cena_slev  N(9,2), cena_celk N(9,2), ;
        cen_hl_zvy N(9,2), poc_hl_zvy I, ;
        cen_hl_nez N(9,2), poc_hl_nez I, ;
        cen_zbytek N(9,2), ;
        cen_polevk N(9,2), poc_polevk I, ;
        cen_fksp   N(9,2), poc_fksp   I, fksp N(9,2), pocdot_sk I, pocdot_hk I, priznak C(66), uzaverky C(99), uzaverka C(17) )
INDEX on u_cis TAG u_cis
INDEX on id    TAG id

? "transactions (" + PAGESIZE + ") "
FOR page_no = 1 TO MAX_PAGECOUNT
    json = http_get("kredit/crud/transactions?page_size=" + PAGESIZE + "&page=" + ALLTRIM(STR(m.page_no)) + "&filter{transacted_at.startswith}=" + m.mesicek + "&filter{-description}=VYUCT" )  && { "transacted_at":"2018-09-18T11:27:02.42Z" }
    pocitej(1, m.page_no)
    FOR ii = 1 TO VAL(PAGESIZE)+3
       id       =    get_integer_item (m.json, "id"            , ii)
       client   =    get_integer_item (m.json, "client"        , ii)
       descript =    get_string_item  (m.json, "description"   , ii)
       pozice   = AT(",T:",  m.descript)
       IF m.pozice > 0
           u_cis    = SUBSTR(m.descript, m.pozice+1)
       ELSE
           u_cis    = m.descript
       ENDIF 
       trans_at =    get_string_item  (m.json, "transacted_at" , ii)
       kind     =    get_string_item  (m.json, "kind"          , ii)
       amount   = - VAL(get_string_item (m.json, "amount"      , ii))
       balance  = - VAL(get_string_item (m.json, "balance"     , ii))
       IF m.id # 0 .and. ATC("VYUCT", m.u_cis) = 0 .and. m.kind # "DTC"
          INSERT INTO transactions FROM MEMVAR
       ENDIF 
    ENDFOR 

    next_page = get_string_item (m.json, "next")
    IF ISNULL(m.next_page)
        EXIT 
    ENDIF 
ENDFOR 
?? "  " + ALLT(STR(SECONDS() - m.zacatek_prace)) + "sec"

SELECT * FROM (tv_path + "transactions") INTO TABLE (tv_path + "transactions_debug")


********** vraci formatovane cislo s pevnou delkou **********
FUNCTION nx2 (nxcislo, nxdelka, nxdes_mist)
IF EMPTY(m.nxdes_mist)
    nxdes_mist = 0
ENDIF 
mezivysledek = PADL(STRTRAN(ALLTRIM(STR(IIF(ISNULL(m.nxcislo), 0, ABS(m.nxcislo)), 12, m.nxdes_mist)), ",", "."), nxdelka, "0")
if nxcislo < 0
    mezivysledek = "-" + SUBST(m.mezivysledek, 2)
endif 
RETURN m.mezivysledek 


********** vraci formatovane cislo s pevnou delkou **********
FUNCTION nx3 (nxcislo, nxdelka, nxdes_mist)
IF EMPTY(m.nxdes_mist)
    nxdes_mist = 0
ENDIF 
RETURN PADL(STRTRAN(ALLTRIM(STR(IIF(ISNULL(m.nxcislo), 0, m.nxcislo), 12, m.nxdes_mist)), ",", "."), nxdelka)



*    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *
FUNCTION zurnalujx(co_zapsat, co_dopredu)
IF PARAM() < 2
    co_dopredu = ""
ENDIF 
STRTOFILE(m.co_dopredu + TTOC(DATETIME(), 3) + " " + m.co_zapsat + CRLF, m.tv_path + "report.txt", 1)
