*   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   
*    exportuje trzby po mesicich a tydnech pro globus
*
* 2020-01-28 - nepouziva se sk_plt
*            - generuji se stejne soubory po tydnech i po mesicich
* 2020-03-04 - generuje se jen za posledni obdobi vcetne celeho tydne
* 2020-04-07 - pridano cislo baraku
*            - nova sestava zakazniku
* 2020-05-03 - kousek programu variantni k exportu delych dat nebo jen posledniho mesice
* 2020-11-02 - upraven vybver obdobi
*
*   *   *   *   *   *   *   *   *   *   *   *   *   *   *     #> ver. 2020-11-02 <#     *   *   *   *   *   *   *   *   *   *   *   *   *   *   

CLOSE DATABASES 
set excl off
set safe off
set dele on
SET ESCAPE ON 
SET ENGINEBEHAVIOR 70
CLEAR

export_path = "i:\exporty_sestav\"
cesta = "i:\d_globus\data\"
*cesta = m.gccen_path

USE (JUSTPATH(JUSTPATH(cesta)) + "\food_dat") NOUPDATE IN 0 ORDER id
USE (m.cesta + "karty")                       NOUPDATE IN 0 ORDER c_karty

USE (m.cesta + "sk_sez")   NOUPDATE IN 0
USE (m.cesta + "sk_sez_a") NOUPDATE IN 0
USE (m.cesta + "sk_fin")   NOUPDATE IN 0
USE (m.cesta + "sk_fin_a") NOUPDATE IN 0
USE (m.cesta + "sk_pol")   NOUPDATE IN 0
USE (m.cesta + "sk_pol_a") NOUPDATE IN 0
USE (m.cesta + "sk_plt")   NOUPDATE IN 0
USE (m.cesta + "sk_plt_a") NOUPDATE IN 0

DO vytvor_jmena_tydnu_a_mesicu

SELECT id_kas, uzaverka, uct_den, mesic     as obdobi, actual, archiv WHERE mesic > "1111-11" FROM tydny_mesice INTO TABLE obdobi
obdobi()

SELECT id_kas, uzaverka, uct_den, tyden_dat as obdobi, actual, archiv                         FROM tydny_mesice INTO TABLE obdobi
obdobi()
? "============    hotovo    ============"

*   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   
FUNCTION obdobi
SELECT DISTI obdobi FROM obdobi INTO CURSOR mesic_dist
SCAN
    SCATTER MEMVAR 
    ACTIVATE SCREEN 
    ? m.obdobi
    DO priprav_data_obdobi
    DO zpracuj_obdobi WITH m.obdobi
ENDSCAN 


*   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   
FUNCTION zpracuj_obdobi (mesic)
?? " zpracovani "

SELECT pol
SET RELATION TO uzaverka + u_cis INTO fin
REPLACE ALL prachy_sl WITH ROUND(prachy * fin.slev_koef, 1)

SET RELATION TO c_karty INTO karty
REPLACE ALL nazev WITH karty.nazev, druh WITH karty.druh_kalk, cena_prum WITH karty.cena_prum * mnozstvi, dan_zakl WITH prachy_sl / dan
REPLACE FOR dan_zakl # 0 spanne WITH ROUND(100 * (dan_zakl - cena_prum) / dan_zakl, 1)    &&& Spanne

SELECT LEFT(uzaverka, 6) as datum,        hodina, id_kas, id_man, druh, c_karty, nazev, cen_hlad, u_time as porce, dan, ;
       SUM(prachy) AS prachy, SUM(prachy_sl) as prachy_sl, SUM(mnozstvi) AS mnozstvi, SUM(dan_zakl) as dan_zakl, SUM(cena_prum) as cena_prum, $0 as spanne, SPACE(30) as pokladna, space(4) as barak ;
       GROUP BY datum, hodina, id_kas, id_man, c_karty, cen_hlad, porce FROM pol INTO TABLE pol_sum
REPLACE FOR dan_zakl # 0 spanne WITH ROUND(100 * (dan_zakl - cena_prum) / dan_zakl, 1)    &&& Spanne
DO generuj_csv WITH (m.export_path + "polozky_hodinove_" + m.mesic)

SELECT LEFT(uzaverka, 6) as datum, "  " as hodina, id_kas, id_man, druh, c_karty, nazev, cen_hlad, u_time as porce, dan, ;
       SUM(prachy) AS prachy, SUM(prachy_sl) as prachy_sl, SUM(mnozstvi) AS mnozstvi, SUM(dan_zakl) as dan_zakl, SUM(cena_prum) as cena_prum, $0 as spanne, SPACE(30) as pokladna, space(4) as barak ;
       GROUP BY datum,         id_kas, id_man, c_karty, cen_hlad, porce FROM pol INTO TABLE pol_sum
REPLACE FOR dan_zakl # 0 spanne WITH ROUND(100 * (dan_zakl - cena_prum) / dan_zakl, 1)    &&& Spanne
DO generuj_csv WITH (m.export_path + "polozky_denne_" + m.mesic)

SELECT LEFT(uzaverka, 6) as datum, left(u_cis, 2) as id_kas, SPACE(30) as pokladna, SPACE(4) as barak, SUM(cena_celk) AS cena_celk, SUM(cena_slev) as cena_slev, SUM(pocet_h) AS pocet_h ;
       GROUP BY datum, id_kas FROM fin INTO TABLE fin_sum
DO generuj_csv WITH (m.export_path + "zakaznici_" + m.mesic)


*   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   
PROCEDURE priprav_data_obdobi

SELECT * FROM (m.cesta + "sk_fin_a") INTO TABLE pom ;
       WHERE uzaverka IN (SELECT uzaverka FROM obdobi WHERE obdobi = m.obdobi AND archiv = "*")
SELECT uzaverka, u_cis, cena_celk, cena_slev, pocet_h, stul, u_cas, u_open, 001.000000000 as slev_koef ;
       FROM (m.cesta + "sk_fin")   INTO TABLE fin ;
       WHERE uzaverka IN (SELECT uzaverka FROM obdobi WHERE obdobi = m.obdobi AND actual = "*")
APPEND FROM pom
REPLACE FOR cena_celk # 0 slev_koef WITH ROUND(cena_slev / cena_celk, 9)
REPLACE FOR EMPT(pocet_h) pocet_h WITH 1
INDEX on uzaverka + u_cis TAG uz_u
?? " fin "

SELECT * FROM (m.cesta + "sk_pol_a") INTO TABLE pom ;
       WHERE uzaverka IN (SELECT uzaverka FROM obdobi WHERE obdobi = m.obdobi AND archiv = "*")
SELECT id_kas, id_man, uzaverka, u_cis, c_karty, nazev, prachy, prachy as prachy_sl, $0 as dan_zakl, $0 as cena_prum, dan, mnozstvi, cen_hlad, k_odpoctu, u_time, ;
       $0 as spanne, SPACE(10) as druh, "  " as hodina ;
       FROM (m.cesta + "sk_pol") INTO TABLE pol ;
       WHERE uzaverka IN (SELECT uzaverka FROM obdobi WHERE obdobi = m.obdobi AND actual = "*")
APPEND FROM pom
USE     IN  pom
?? " pol "
SET RELATION TO uzaverka + u_cis INTO fin
REPLACE ALL hodina WITH fin.u_open

REPLACE FOR EMPTY(cen_hlad) cen_hlad WITH "1"
REPLACE FOR EMPTY(k_odpoctu) dan WITH 0, prachy WITH 0, prachy_sl WITH 0
REPLACE FOR dan = 0 dan WITH 1.0    && aby nebyly chyby deleni nulovou dani


*   *   *   *   *   *   
PROCEDURE vytvor_jmena_tydnu_a_mesicu

SELECT id_kas, uzaverka, uct_den, SPACE(7) as mesic, 00 as tyden, SPACE(15) as tyden_dat, "*" as actual ;
       ORDER BY uct_den FROM (m.cesta + "sk_sez") INTO TABLE tydny_mesice_actual

SELECT id_kas, uzaverka, uct_den, SPACE(7) as mesic, 00 as tyden, SPACE(15) as tyden_dat, "  " as den_tydne, " " as actual, "*" as archiv ;
       ORDER BY uct_den FROM (m.cesta + "sk_sez_a") INTO TABLE tydny_mesice

APPEND FROM tydny_mesice_actual
USE     IN  tydny_mesice_actual

SELECT MIN(uct_den) as hranice FROM tydny_mesice INTO CURSOR hranice
SCATTER MEMVAR 

SELECT tydny_mesice
REPLACE ALL mesic WITH LEFT(TTOC(uct_den, 3), 7), tyden WITH WEEK(uct_den, 1,5), den_tydne with dat_den_tydne(uct_den)

FOR iii = -8 TO 444
    datum = m.hranice + m.iii
    IF DOW(m.datum, 2) = 4    && ctvrtek
        REPLACE FOR BETWEEN(uct_den, m.datum, m.datum + 6) tyden WITH WEEK(m.datum, 1,2), tyden_dat WITH "_W" + TRANSFORM(WEEK(m.datum, 1,2), "@L 99") + "_" + LEFT(TTOC(datum, 3), 10)
    ENDIF 
ENDFOR 
INDEX on uzaverka + id_kas TAG uz_kas

*  *  tady je v promenne m.hranice prvni datum z eskacek

*    *    *    *    *    *    *    *    *    *    kdyz se tenhle kousek zakomentuje, generuji se soubory za cela eskacka     ZACATEK
hranice = DATE()
IF DAY(m.hranice) < 11
    hranice = GOMONTH(hranice, -1)
ENDIF 
*    *    *    *    *    *    *    *    *    *    kdyz se tenhle kousek zakomentuje, generuji se soubory za cela eskacka     KONEC
*hranice = {^2020-07-01}

hranice_mesic = LEFT(TTOC(m.hranice, 3), 7)
DELETE FOR uct_den < DATE(year(m.hranice), month(m.hranice), 1) - 8

sele min(tyden_dat) as min_tyden where mesic = m.hranice_mesic from tydny_mesice into cursor min_tyden
scatt memvar
SELECT tydny_mesice
dele for tyden_dat < m.min_tyden
repl for mesic < m.hranice_mesic mesic with "0000-00"
repl for uct_den >= DATE(year(date()), month(date()), 1) mesic with "0000-00"


*   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *
PROCEDURE generuj_csv
LPARAMETERS exportni_soubor

#DEFINE ODD "; "
#DEFINE CRLF CHR(13)+CHR(10)

SET RELATION TO id_kas INTO food_dat
REPLACE ALL pokladna WITH food_dat.id_zkratka, barak with food_dat.stredskup

?? "  generating CSV"

fh = FCREATE(m.exportni_soubor + ".csv")
IF m.fh > 0 
  IF ATC("zakaznici", m.exportni_soubor) > 0
    FPUTS(m.fh, "Datum   ; Barak;       Kasa             ; Trzba_pred_slevou; Trzba_po_sleve; Pocet_uctu; ")
    SCAN 
        radek = tx2(datum) + tx2(barak) + tx2(pokladna) + nx2(cena_celk,12,1) + nx2(cena_slev,12,1) + nx2(pocet_h,7,0)
        FPUTS(m.fh, m.radek )
    ENDSCAN 
  ELSE
    FPUTS(m.fh, "Datum; Hod;  Barak ;       Kasa              ; Stred; Druh      ; C_karty; Nazev                       ;Porce;     Mnozstvi;       Prachy;    Prachy_sl;   Dan_zaklad;       Naklad;     Spanne; ")
    SCAN 
        radek =           tx2(datum) + tx2(hodina) + tx2(barak) + tx2(pokladna) + tx2(id_man) + tx2(druh) + nx2(c_karty,7,0) + tx2(nazev) + tx2(porce) 
        radek = m.radek + nx2(mnozstvi, 12,3) + nx2(prachy, 12,1) + nx2(prachy_sl, 12,1) + nx2(dan_zakl, 12,1) + nx2(cena_prum, 12.1) + nx2(spanne, 10,1)
        FPUTS(m.fh, m.radek )
    ENDSCAN 
  ENDIF
  FCLOSE(m.fh)
ENDIF 


********** vraci formatovane cislo  **********
FUNCTION nx (nxcislo, nxdes_mist)
IF EMPTY(m.nxdes_mist)
    nxdes_mist = 0
ENDIF 
RETURN STRTRAN(ALLTRIM(STR(IIF(ISNULL(m.nxcislo), 0, m.nxcislo), 12, m.nxdes_mist)), ".", ",") + ODD

*    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *
FUNCTION nx2 (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) + ODD

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

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


*  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  
FUNCTION dat_den_tydne(ddatum)
TRY 
    downum = DOW(m.ddatum, 2)
CATCH
    downum = 9
ENDTRY 

DO CASE 
CASE downum = 1
    RETURN "Po"
CASE downum = 2
    RETURN "Út"
CASE downum = 3
    RETURN "St"
CASE downum = 4
    RETURN "Čt"
CASE downum = 5
    RETURN "Pá"
CASE downum = 6
    RETURN "So"
CASE downum = 7
    RETURN "Ne"
OTHERWISE 
    RETURN "--"
ENDCASE 
