******************************************************************************
*
* 2015-04-03 - pro helios se exportuji cisla stredisek a data trzeb se castecne totaluji kvuli rychlosti heloisoveho importeru
* 2015-05-27 - pro helios se v #IF da nastavit, jak moc se datata totaluji
*            - vyhozen export pro prazdroj
* 2015-06-02 - do pripraveneho plt se z sk_txt dotahne prefix hotelu HPREF, tyz sl. je pridan do U_EX_PLT a ucastni se locate 
* 2015-10-07 - u_ex se kopiruje jeste do u_ex_fox2 jako TYPE FOX2X
* 2015-10-08 - i volba [Novy export] generuje (prazdny) u_ex_fox2
* 2015-10-12 - u_ex_fox2 je generovan s presne definovanymi sloupci
* 2020-03-22 - zvladne i sql verzi, ale soubory u_ex a u_ex_sum se generuji jako dbf
* 2020-03-26 - prestehovana sem definice menu z menu_def.prg
* 2020-07-29 - novy export vytvori i adresar
* 2020-08-07 - i pro krumlovsky dvur se vola budvarska generace souboru
* 2021-01-17 - i pro SQL se generuje na zacatku u_ex.dbf, pokud neexistuje
*
******************************************************************************

DO ini_files


*!*********************************************************************
PROCEDURE set_u_ex
PARAMETERS soubor
DO clos_data

IF m.issql
    DO open_database
    USE ("ps_r" + m.soubor)
ELSE
    USE (m.gcucto_path + m.soubor) excl
ENDIF 

DEFINE WINDOW brw FROM 16,0 TO SROWS()-1, SCOLS()-1
PUSH KEY CLEAR
ON KEY LABEL f10 KEYBOARD "{Ctrl+W}"
ON KEY LABEL esc KEYBOARD "{Ctrl+W}"
ON KEY LABEL f4  KEYBOARD "{Ctrl+T}"
ON KEY LABEL f5  KEYBOARD "{Ctrl+N}"
keyboard "{Ctrl+F10}"
set carry on
do case
case "POL" $ upper(m.soubor)
    index on id_kas + id_man + cen_hlad + druh + str(dan,5,3) + LEFT(co, 33) to (m.gcpri_path + "iiiiiiii")
    BROWSE WINDOW brw NORMAL title uppe(m.soubor) + "      Ctrl+Y prida radek, Ctrl+T smaze, Ctrl+W ukonci"
    
case "PLT" $ upper(m.soubor)
    index on id_kas + druh_pl to (m.gcpri_path + "iiiiiiii")
    BROWSE FIELDS id_kas:H="Kas", druh_pl, md, dal, hs_md:17, hs_dal:17, md2, dal2, co, pz, poznamka ;
           WINDOW brw NORMAL title uppe(m.soubor) + "      Ctrl+Y prida radek, Ctrl+T smaze, Ctrl+W ukonci"

OTHERWISE && "obr"
    index on id_man + operace + cen_hlad + partner to (m.gcpri_path + "iiiiiiii")
    BROWSE WINDOW brw NORMAL title uppe(m.soubor) + "      Ctrl+Y prida radek, Ctrl+T smaze, Ctrl+W ukonci"
endcase

POP KEY
RELEASE WINDOW brw
SET CARRY OFF 
DO clos_data


*!*********************************************************************
PROCEDURE gen_exp

IF LEN(m.gcuexp_path) < 2
     m.gcuexp_path = m.gcucto_path
ENDIF 
DO clos_data

gen_exp_had = createobject("had", "Generování exportních souborů")

DO CASE 
CASE ATC("BUDVAR", m.wi_zakaz) > 0
    DO gen_exp_budvar

CASE ATC("_SENAT", m.gchom_path) > 0
    DO gen_exp_senat

CASE ATC("D_PRESS", m.gchom_path) > 0
    DO gen_exp_itvv

CASE ATC("D_HATE", m.gchom_path) > 0
    DO gen_exp_itvv

CASE ATC("D_RESTLIPA", m.gchom_path) > 0
    DO gen_exp_helios

CASE ATC("D_LINDR", m.gchom_path) > 0
    DO gen_exp_helios

OTHERWISE 
    MESSAGEBOX("Pro datový adresář " + UPPER(m.gchom_path) + " se nic negeneruje", 0+16, "Generovani exportniho souboru", 4444)
ENDCASE 

RELEASE gen_exp_had
DO clos_data



*!*********************************************************************
*!*********************************************************************
proc gen_exp_helios
gen_exp_had.show()
SELECT * FROM (m.gcucto_path + "u_ex") INTO TABLE (m.gcucto_path + "u_ex_hel")
soubor = STRTR(LOWER(DBF()),".dbf", ".csv")

#IF .F. 

* ucty s vybranymi druhy plateb se uctuji po jednom (pol i plt), ostatni souctem za uzaverku

    SELECT datum, faktura WHERE (pz3 = "TRZ-PLT") AND (faktura LIKE "N_HOTEL%") ;
           FROM (m.gcucto_path + "u_ex_hel") INTO TABLE (m.gcpri_path + "vybrane_ucty")
    INDEX on datum TAG datum

    SELECT u_ex_hel
    SET RELATION TO datum INTO vybrane_ucty
    SCAN FOR LEFT(pz3, 5) = "TRZ-P"
        IF datum = vybrane_ucty.datum
           REPLACE datum WITH LEFT(ALLTRIM(pz), 6) + "--" + ALLTRIM(datum)
        ELSE
           REPLACE datum WITH ALLTRIM(pz) + "/" + partner, poznamka WITH LEFT(poznamka,4) + " sum"
        ENDIF 
    ENDSCAN 
#ELSE

*   polozky se uctuji souctem za celou uzaverku, platby hotelove ucty zvlast, ostatni platby souctem za uzaverku

    REPLACE FOR LEFT(pz3, 5) = "TRZ-P" datum WITH ALLTRIM(pz) + "/" + partner        && v pz je uzaverka
    REPLACE FOR (pz3 = "TRZ-PLT") .and. LEFT(faktura, 7) = "N_HOTEL" pz WITH LEFT(ALLTRIM(pz), 6) + "--" + ALLTRIM(doklad)
#ENDIF


SELECT           datum, datum_dokl, md, dal, hs_md, hs_dal, md2, dal2, pz, pz3, poznamka, SUM(castka) as castka ;
       GROUP BY  datum, datum_dokl, md, dal, hs_md, hs_dal, md2, dal2, pz, pz3, poznamka ;  
       FROM (m.gcucto_path + "u_ex_hel") INTO TABLE (m.gcucto_path + "u_ex_polosum")
fh = FCREATE(m.soubor)
odd = ";"
FPUTS (fh, "ExtDoklad; Sbornik; DatumPripad; DatumPorizeno; DatumDUZP; DruhData; Strana; CisloUcet; Castka; CisloNakladovyOkruh; Utvar; UcetDPH; Popis; Zaknihovano; Stav; CisloOrg; NazevOrganizace; ParovaciZnak")
SCAN FOR .not. "EXPORT" $ md
   gen_exp_had.set_txt2(STR(RECNO()))
   druhdata  = IIF(LEFT(pz3, 6) = "OBR-PE", 3,      0)                && 3 pro DAL/PE
   pz_dal    = IIF(LEFT(pz3, 6) = "OBR-PE", datum, "")                && c_dokl pro DAL/PE
   cis_org   = IIF(LEFT(pz3, 6) = "OBR-PE", VAL(SUBSTR(pz3, 7)), 0)   && c_partner pro DAL/PE
   naz_org   = IIF(LEFT(pz3, 6) = "OBR-PE", SUBSTR(poznamka,11), "")  && nazev partnera pro DAL/PE
   datum_uct = VAL(DTOS(datum_dokl))

*          ExtDoklad   Sbornik      DatumPripad       DatumPorizeno     DatumDUZP         DruhData
   r_md  = xx(datum) + xx(md2) + nx(m.datum_uct) + nx(m.datum_uct) + nx(m.datum_uct) + nx(m.druhdata, 0)    && spolecny zacatek pro radek MD i DAL
   r_dal = m.r_md

*                             Ucet        Castka      Nakl.okruh  Utvar=stredisko
   r_md  = m.r_md  + nx(0) + xx(md)  + nx(castka, 2) + xx("200")  +  xx(hs_md)
   r_dal = m.r_dal + nx(1) + xx(dal) + nx(castka, 2) + xx("200")  +  xx(hs_dal)

*                    UcetDPH   Popis  Zaknihovan  Stav     CisloOrg     NazevOrganizace ParovaciZnakDAL
   r_md  = m.r_md  + xx(dal2) + tx2(pz) + nx(1) + nx(0) + nx(m.cis_org) + tx2(m.naz_org) + xx("0")
   r_dal = m.r_dal + xx(dal2) + tx2(pz) + nx(1) + nx(0) + nx(m.cis_org) + tx2(m.naz_org) + xx(m.pz_dal)

   IF .not. EMPTY(md)
      FPUTS (fh, m.r_md )    && radek strany MD
   ENDIF 
   IF .not. EMPTY(dal)
      FPUTS (fh, m.r_dal)    && radek strany DAL
   ENDIF  
ENDSCAN
FCLOSE(fh)

TRY
    COPY FILE (m.soubor) TO p:\alto\ucto_z_alta.csv
CATCH
	WAIT "nepodarilo se nakopirovat soubor do adresare p:\alto" WINDOW TIMEOUT .3
ENDTRY

* jako sbornik pouzito md2
* jako UcetDPH pouzito dal2


********** vraci string + uvozovky + oddelovac **********
FUNCTION tx2 (retez)
RETURN '"' + ALLTRIM(STRTRAN(retez, '"', "'")) + '"' + m.odd

********** vraci string bez mezer + uvozovky + oddelovac **********
FUNCTION tx (retez)
retval = STRTRAN(m.retez, " ", "")
RETURN '"' + ALLTRIM(STRTRAN(retval, '"', "'")) + '"' + m.odd

********** vraci string + oddelovac **********
FUNCTION xx (retez)

RETURN ALLTRIM(STRTRAN(retez, '"', "'")) + m.odd

********** vraci formatovane cislo + oddelovac **********
FUNCTION nx (cislo, des_mist)
IF EMPTY(m.des_mist)
    des_mist = 0
ENDIF 
RETURN ALLTRIM(STR(cislo, 12, m.des_mist)) + m.odd


*!*********************************************************************
*!*********************************************************************
proc gen_exp_itvv
gen_exp_had.show()
use (m.gcucto_path + "u_ex") excl
soubor = STRTR(LOWER(DBF()),".dbf", ".txt")
fh = FCREATE(m.soubor)
odd = "; "
SCAN FOR .not. "EXPORT" $ md
   gen_exp_had.set_txt2(STR(RECNO()))
   radek = tx(datum) + tx(id_man) + tx(partner) + tx(doklad) + tx(DTOS(datum_dokl)) + tx(md) + tx(dal) + tx(hs_md) + tx(hs_dal) ;
         + tx(md2) + tx(dal2) + STR(castka,12,2) + odd + tx2(pz) + tx2(poznamka) + tx2(faktura) + tx2(SUBSTR(pz3, 7))
   FPUTS (fh, m.radek)
ENDSCAN
FCLOSE(fh)

*!*********************************************************************
*!*********************************************************************
proc gen_exp_senat
soubor = "c:\tmp\exp1_.txt"
soubor = INPUTBOX("Jmeno exportniho souboru:", "Export do účetnictví", m.soubor )
IF EMPTY(JUSTEXT(soubor))
    soubor = STRTRAN(ALLTRIM(soubor), ".", "") + ".txt"
ENDIF 

gen_exp_had.show()
fh = fcreate(m.soubor)
= fwrite(m.fh, "21/" + trans(MONTH(DATE()-22), "@L 99") + trans(year(DATE()-22), "@L 9999") + "001" + chr(13) + chr(10))
= fwrite(m.fh, "22/006383940700000000311200000" + chr(13) + chr(10))

use (m.gcucto_path + "u_ex_sum") excl
scan
    = fwrite(m.fh, "24/" + padr(md, 8) + padr(hs_md, 15) + padr(dal, 8) + padr(hs_dal, 15) )
    = fwrite(m.fh, "0000000000000000000000000000000000000000000000000000")

    IF "MD" $ md2
        = fwrite(m.fh,                     trans(ABS(castka*100), "99999999999999") + IIF(castka < 0, "-", "+" ) + "00000000000000+" )
    ELSE 
        = fwrite(m.fh, "00000000000000+" + trans(ABS(castka*100), "99999999999999") + IIF(castka < 0, "-", "+" )                     )
    ENDIF 

    = fwrite(m.fh, padr(pz, 30) + chr(13) + chr(10) )
endscan
= fclose(m.fh)


*!*********************************************************************
*!*********************************************************************
proc gen_exp_budvar

soubor = "Q:\expot\sp1_.txt"
soubor = INPUTBOX("Jmeno exportniho souboru:", "Export do účetnictví", m.soubor )

IF EMPTY(JUSTEXT(soubor))
    soubor = STRTRAN(ALLTRIM(soubor), ".", "") + ".txt"
ENDIF 

DO mk_dir WITH JUSTPATH(m.soubor)
fh = fcreate(m.soubor)

IF m.fh > 0
    use (m.gcucto_path + "u_ex_sum") excl
    scan
        fwrite(m.fh, SPACE(32) )
        fwrite(m.fh, padr(md, 8) + padr(hs_md, 15) + padr(dal, 8) + padr(hs_dal, 15) )
        fwrite(m.fh, trans(castka*100, "999999999999999") + padr(pz, 30) )
        fwrite(m.fh, chr(13) + chr(10) )
    endscan
    fclose(m.fh)
ELSE
    MESSAGEBOX("Soubor " + UPPER(m.soubor) + " se nepodarilo zapsat", 0+16, "Generovani exportniho souboru")
ENDIF 

soubor = "Q:\expot\sp2_.txt"
soubor = INPUTBOX("Jmeno exportniho souboru:", "Export do účetnictví", m.soubor )
IF EMPTY(JUSTEXT(soubor))
    soubor = STRTRAN(ALLTRIM(soubor), ".", "") + ".txt"
ENDIF 

DO mk_dir WITH JUSTPATH(m.soubor)
fh = fcreate(m.soubor)
IF m.fh > 0
    use (m.gcucto_path + "u_ex") excl
    scan
        fwrite(m.fh, SPACE(4) )
        fwrite(m.fh, PADR(ALLTRIM(doklad), 28) )
        fwrite(m.fh, padr(md, 8) + padr(hs_md, 15) + padr(dal, 8) + padr(hs_dal, 15) )
        fwrite(m.fh, trans(castka*100, "999999999999999") + padr(pz, 30) )
        fwrite(m.fh, chr(13) + chr(10) )
    endscan
    fclose(m.fh)
ELSE
    MESSAGEBOX("Soubor " + UPPER(m.soubor) + " se nepodarilo zapsat", 0+16, "Generovani exportniho souboru")
ENDIF 
DO clos_data


*!*********************************************************************
PROCEDURE dbf2xml

= CURSORTOXML(0, LOWER( JUSTPATH(DBF()) + "\" + JUSTSTEM(DBF()) ) + ".xml8", 1, 512)
fh =      fopen( LOWER( JUSTPATH(DBF()) + "\" + JUSTSTEM(DBF()) ) + ".xml8" )
IF m.fh > 0
    fh16 = FCREA(LOWER( JUSTPATH(DBF()) + "\" + JUSTSTEM(DBF()) ) + ".xml16")
    IF m.fh16 > 0
        FWRITE(m.fh16, CHR(255) + CHR(254))
        DO WHILE ! feof(m.fh)
           radek = ALLTRIM(fgets(m.fh, 333))
           radek_unicode = ""
           FOR iii = 1 TO LEN(m.radek)
               radek_unicode = m.radek_unicode + SUBSTR(m.radek, m.iii, 1) + CHR(0)
           ENDFOR 
           FWRITE(m.fh16, m.radek_unicode + CHR(13) + CHR(0) + CHR(10) + CHR(0))
        ENDDO
        = fclose(m.fh16)
    ELSE 
         WAIT "XML16 nelze vytvorit" WINDOW AT 22,22 TIMEOUT 3
    ENDIF 
    = fclose(m.fh)
ELSE 
     WAIT "XML8 nebyl vytvoren" WINDOW AT 22,22 TIMEOUT 3
ENDIF 


*!*********************************************************************
PROCEDURE  generuj_souctovy_soubor
SELECT md, dal, hs_md, hs_dal, md2, dal2, pz, round(sum(castka), 2) as castka, poznamka ;
       GROUP BY 1,2,3,4,5,6,7 from (m.gcucto_path + "u_ex") INTO TABLE (m.gcucto_path + "u_ex_sum")

small_log ("generuj_souctovy_soubor:" + UPPER(m.gcucto_path), "ucto_exp")
IF ATC("BUDVAR", m.wi_zakaz) > 0
    DO budvar_u_ex2sap WITH m.gcucto_path + "u_ex"
ELSE 
    SELECT doklad, datum_dokl, md, dal, hs_md, hs_dal, md2, dal2, castka, pz, poznamka, faktura as pz2, pz3 ;
           FROM (m.gcucto_path + "u_ex") into table (m.gcpri_path + "uuu_ex")
    COPY ALL TO (m.gcucto_path + "u_ex_fox2") TYPE fox2x
    SELECT u_ex
    COPY ALL TO (m.gcucto_path + "u_ex_fox22") TYPE fox2x
ENDIF 
small_log ("generuj_souctovy_soubor: ==== hotovo ====", "ucto_exp")


*!*********************************************************************
PROCEDURE exp_new

DO mk_dir WITH m.gcucto_path
IF DIRECTORY(m.gcucto_path)

    bytes_zapsano = STRTOFILE("zkouskazapisu", m.gcucto_path + "zkouskazapisu.txt")
    IF bytes_zapsano = 0
        small_log ("exp_new:" + UPPER(m.gcucto_path) + "nelze zapisovat!", "ucto_exp")
        MESSAGEBOX("do adresáře " + UPPER(m.gcucto_path) + CHR(13) + "nelze zapisovat!", 1+16)
    ELSE
        DELETE FILE (m.gcucto_path + "zkouskazapisu.txt")
    ENDIF 

    WAIT "Generování prázdných souborů v adresáři" + UPPER(m.gcucto_path) WINDOW AT 22,22 NOWAIT 
    DO clos_data

    chybicka = "mazani " + m.gcucto_path + "u_ex    "
    TRY 
        DELETE FILE (m.gcucto_path + "u_ex_sum.dbf")
        DELETE FILE (m.gcucto_path + "u_ex.dbf"    )
    CATCH TO enErr 
        chybicka = m.chybicka + nx(enErr.ErrorNo) + " " + enErr.Message + " Line:" + nx(enErr.LineNo)
    ENDTRY
    small_log ("exp_new:" + m.chybicka, "ucto_exp")

    creafile (m.gcucto_path + "u_ex")

    DO generuj_souctovy_soubor

    WAIT CLEAR 
    messagebox("Exportní soubor " + m.gcucto_path + "U_EX" + " je prázdný",0+64,"",1111)
    DO clos_data
ELSE
    small_log ("exp_new:" + UPPER(m.gcucto_path) + "NEEXISTUJE A NEJDE VYTVORIT", "ucto_exp")
    MESSAGEBOX("Exportní adresář " + CHR(13) + UPPER(m.gcucto_path) + CHR(13) + "NEEXISTUJE A NEJDE VYTVORIT ", 1+16)
ENDIF 


*!*********************************************************************
PROCEDURE ini_files
DO mk_dir WITH m.gcucto_path
IF DIRECTORY(m.gcucto_path)
   DO clos_data
   IF m.issql
   ELSE 
     if ! file (m.gcucto_path + "u_ex_plt.dbf")
    	DO creafile WITH (m.gcucto_path + "u_ex_plt.dbf")
     endif

     if ! file (m.gcucto_path + "u_ex_pol.dbf")
    	DO creafile WITH (m.gcucto_path + "u_ex_pol.dbf")
     endif

     if ! file (m.gcucto_path + "u_ex_obr.dbf")
    	DO creafile WITH (m.gcucto_path + "u_ex_obr.dbf")
     endif

   ENDIF 
   IF ! file (m.gcucto_path + "u_ex.dbf")
     DO exp_new
   ENDIF
   DO clos_data
ELSE
    MESSAGEBOX("Exportní adresář " + CHR(13) + UPPER(m.gcucto_path) + CHR(13) + "NEEXISTUJE!", 1+16)
ENDIF 


*!*********************************************************************
PROCEDURE ucto_menu_def

DEFINE POPUP          uc_nastaveni MARGIN RELATIVE SHADOW COLOR SCHEME 4
DEFINE BAR       1 OF uc_nastaveni PROMPT "Nastavení vazev plateb"
DEFINE BAR       2 OF uc_nastaveni PROMPT "Nastavení vazev polozek"
DEFINE BAR       3 OF uc_nastaveni PROMPT "Nastavení vazev obratů"
ON SELECTION BAR 1 OF uc_nastaveni DO set_u_ex IN ucto_exp WITH "u_ex_plt"
ON SELECTION BAR 2 OF uc_nastaveni DO set_u_ex IN ucto_exp WITH "u_ex_pol"
ON SELECTION BAR 3 OF uc_nastaveni DO set_u_ex IN ucto_exp WITH "u_ex_obr"

*- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
DEFINE POPUP          uc_sestavy MARGIN RELATIVE SHADOW COLOR SCHEME 4
DEFINE BAR       1 OF uc_sestavy PROMPT "Součty účtů"
DEFINE BAR       2 OF uc_sestavy PROMPT "Exportní soubor"
DEFINE BAR       3 OF uc_sestavy PROMPT "Detaily exportu"
DEFINE BAR       4 OF uc_sestavy PROMPT "\-"
DEFINE BAR       5 OF uc_sestavy PROMPT "Vazby plateb"
DEFINE BAR       6 OF uc_sestavy PROMPT "Vazby položek"
DEFINE BAR       7 OF uc_sestavy PROMPT "Vazby obratů"

ON SELECTION BAR 1 OF uc_sestavy DO ucty_sum IN uc_sest
ON SELECTION BAR 2 OF uc_sestavy DO ucty     IN uc_sest
ON SELECTION BAR 3 OF uc_sestavy DO ucty_det IN uc_sest

ON SELECTION BAR 5 OF uc_sestavy DO vazby IN uc_sest WITH "u_ex_plt"
ON SELECTION BAR 6 OF uc_sestavy DO vazby IN uc_sest WITH "u_ex_pol"
ON SELECTION BAR 7 OF uc_sestavy DO vazby IN uc_sest WITH "u_ex_obr"

*- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
DEFINE POPUP           uc_exp MARGIN RELATIVE SHADOW COLOR SCHEME 4
DEFINE BAR        1 OF uc_exp PROMPT "Nastavení"       MESS "Nastaveni ucetnich vazeb"
DEFINE BAR        2 OF uc_exp PROMPT "Sestavy"         MESS "Sestavy"
DEFINE BAR       30 OF uc_exp PROMPT "\-"
DEFINE BAR       33 OF uc_exp PROMPT "Export skladů"   MESS "Export dat z jednotlivych stredisek"
DEFINE BAR       34 OF uc_exp PROMPT "Export tržeb"    MESS "Export tržeb pokladen"
DEFINE BAR       37 OF uc_exp PROMPT "Nový export"     MESS "Zacatek noveho exportu"
DEFINE BAR       38 OF uc_exp PROMPT "Generování exp. souborů" MESS "Generování exp. souborů"
DEFINE BAR       60 OF uc_exp PROMPT "\-"
DEFINE BAR       66 OF uc_exp PROMPT "Formulár" MESS "Nový formulár"
DEFINE BAR       99 OF uc_exp PROMPT "\-"

ON           BAR  1 OF uc_exp ACTIVATE POPUP uc_nastaveni
ON           BAR  2 OF uc_exp ACTIVATE POPUP uc_sestavy
ON SELECTION BAR 33 OF uc_exp DO u_ex_obr
ON SELECTION BAR 34 OF uc_exp DO FORM uct_exp_trzby
ON SELECTION BAR 37 OF uc_exp DO exp_new IN ucto_exp
ON SELECTION BAR 38 OF uc_exp DO gen_exp IN ucto_exp
ON SELECTION BAR 66 OF uc_exp DO FORM ucto

