*    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *
*
* program exportuje utraty zamestnancu z transakci v datastore
*
*  2022-12-07 - chybel zacatek ledna, tak se muselo vzit z pepinova zurnalo, to za unor musim zrusit
*
*    *    *    *    *    *    *    *    *    *    *    *      #> ver. 2023-01-30 <#       *    *    *    *    *    *    *    *    *    *    *    *    *
#DEFINE PGM_VERZE                                               "ver. 2023-01-30"

verze = "2023-01-01"
WAIT PROGRAM() WINDOW AT 22,22 NOWAIT 

DO clos_data
ACTIVATE SCREEN 
CLEAR 

adresar = m.gchom_path
STORE "" TO datsk1, datsk2, mesicek, s_trz_dat1, s_trz_dat2
nastav_obdobi()

SUSPEND

importuj_zurnal()




DO ppgm_http 
SET PROCEDURE TO ppgm_stravnici ADDITIVE 

DO get_courses      IN stravovani_ppgm
DO get_clients      IN stravovani_ppgm

DO get_meals        IN stravovani_ppgm
DO get_orders       IN stravovani_ppgm

DO get_transactions IN stravovani_ppgm

oznam_hotovo()



SELECT id, amount, u_cis, descript, client, client_id, clientname, firma, trans_at FROM transactions INTO TABLE (m.adresar + "elfetex")
dopln_clienty()
dbf2csv()

SELECT client_id, clientname, SUM(amount) as utrata, firma GROUP BY 1 FROM elfetex INTO TABLE (m.adresar + "elfetex_sum")
dbf2csv()


DO clos_data
WAIT CLEAR 



*    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *
PROCEDURE elfetex2

*adresar = "i:\food_sql\elfetex\"
adresar = m.gchom_path

CLOSE DATABASES 
CLOSE DATABASES 
USE (m.gcpri_path + "clients")      IN 0
USE (m.gcpri_path + "meals")        IN 0
USE (m.gcpri_path + "orders")       IN 0
USE (m.gcpri_path + "transactions") IN 0

SELECT id, amount, u_cis, descript, client, client_id, clientname, firma, trans_at FROM transactions INTO TABLE (m.adresar + "elfetex")

dopln_clienty()

importuj_zurnal()

SELECT elfetex
DELETE FOR descript = "ZE ZURNALU  "

SELECT radky
SCAN FOR cena # 0 .and. .not. EMPTY(client_id)
    SCATTER MEMVAR 
    SELECT elfetex
    LOCATE FOR u_cis = m.u_cis && .and. client_id = m.client_id
    IF FOUND()
        REPLACE descript WITH ALLTRIM(descript) + "   **"
        SELECT radky
        REPLACE radek WITH ALLTRIM(radek) + " ********"
    ELSE
        descript = "ZE ZURNALU"
        trans_at = m.date
        amount = m.cena
        INSERT INTO elfetex FROM MEMVAR 
        SELECT radky
        REPLACE radek WITH ALLTRIM(radek) + " IIIIIIIII"
    ENDIF 
ENDSCAN 

dopln_clienty()

SELECT elfetex
dbf2csv()

SELECT client_id, clientname, SUM(amount) as utrata, firma GROUP BY 1 FROM elfetex INTO TABLE (m.adresar + "elfetex_sum")
dbf2csv()


*    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *
PROCEDURE dopln_clienty
SELECT elfetex
SCAN FOR EMPTY(client_id)
    SELECT clients
    LOCATE FOR id = elfetex.client
    IF FOUND()
        SELECT elfetex
        REPLACE client_id WITH clients.client_id, firma WITH clients.firma, clientname WITH ALLTRIM(clients.surname) + " " + clients.firstname
    ENDIF 
ENDSCAN 

SCAN FOR EMPTY(client)
    SELECT clients
    LOCATE FOR VAL(client_id) = VAL(elfetex.client_id)
    IF FOUND()
        SELECT elfetex
        REPLACE client WITH clients.id, firma WITH clients.firma, clientname WITH ALLTRIM(clients.surname) + " " + clients.firstname
    ENDIF 
ENDSCAN 


*    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *
PROCEDURE  importuj_zurnal

CREATE TABLE radky (radek C(88), client_id C(15), u_cis C(7), cena N(7,1), date C(12))

#DEFINE IMPORT_FILE "i:\zu2302.TXT"
SET POINT TO "."
fh = FOPEN(IMPORT_FILE)
IF m.fh > 0 
    DO while ! feof(m.fh)

        radek     = fgets(m.fh,333)
        cena = 0

        DO CASE 
        CASE ATC('"Hotove  "', m.radek)  > 0
            *nic
        CASE ATC('PRIMA:,0,0', m.radek)  > 0
            *nic
        CASE ATC('"VAZBA"', m.radek)  > 0
            STORE "" TO u_cis, client_id
            pozice = ATC('PRIMA:', m.radek)
            IF pozice > 7
                u_cis = ""
                client_id = SUBSTR(m.radek, m.pozice + 6)
                pozice = ATC(',', m.client_id)
                IF pozice > 1
                    client_id = LEFT(m.client_id, m.pozice -1)
                ENDIF 
                date = SUBSTR(m.radek, 10)
                date = LEFT(m.date, 4) + "-" + SUBSTR(m.date, 5,2) + "-" + SUBSTR(m.date, 7,2)
                INSERT INTO radky FROM MEMVAR 
            ENDIF 
        CASE ATC('"VYBHK"', m.radek)  > 0
                STORE "" TO u_cis, client_id
                client_id = SUBSTR(m.radek, 39)
                pozice = ATC(',', m.client_id)
                IF pozice > 1
                    client_id = LEFT(m.client_id, m.pozice -1)
                ENDIF 
                date = SUBSTR(m.radek, 10)
                date = LEFT(m.date, 4) + "-" + SUBSTR(m.date, 5,2) + "-" + SUBSTR(m.date, 7,2)
                INSERT INTO radky FROM MEMVAR 
        CASE ATC('"UCPLT"', m.radek) > 0
            u_cis = SUBSTR(m.radek, 10)
            pozice = ATC('CZK', m.radek)
            IF pozice > 7
                cena = VAL(SUBSTR(m.radek, m.pozice + 9))
            ENDIF 
            INSERT INTO radky FROM MEMVAR 
        ENDCASE
        
    ENDDO 
    FCLOSE(m.fh)
ELSE
    WAIT "nelze otevrit soubor " + IMPORT_FILE WINDOW TIMEOUT 3 AT 22,22
ENDIF 
RETURN m.fh







*     *     *     *     *     *     *     *     *     *     *     *     *     *     *     *     *     *     *     *     *     *     
PROCEDURE nastav_obdobi
IF DAY(DATE()) < 20
    s_trz_dat2 = DATE(YEAR(DATE()   ), MONTH(DATE()   ), 1) - 1
ELSE
    s_trz_dat2 = DATE(YEAR(DATE()+27), MONTH(DATE()+27), 1) - 1
ENDIF
s_trz_dat1     = DATE(YEAR(m.s_trz_dat2), MONTH(m.s_trz_dat2), 1)

datsk1 = LEFT(TTOC(m.s_trz_dat1, 3), 10)
datsk2 = LEFT(TTOC(m.s_trz_dat2, 3), 10)

mesicek = SUBSTR(datsk1, 3, 2) + SUBSTR(datsk1, 6, 2)                     && "2108", cili ctyrmistne RRMM

?    "exportuje se obdobi: " + m.datsk1 + " - " + m.datsk2 + "   ( " + m.mesicek + " )"



*  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  
FUNCTION formatuj (penizky)
IF m.v_tisicich
    RETURN TRANSFORM(ROUND(penizky/1000, 1), "99 999.9")
ELSE 
    RETURN TRANSFORM(ROUND(penizky, 0)    , "9 999 999")
ENDIF

