******************************************************************************
*
* 2016-06-15 - volba uctovani po skupinach nebo spartech neni natvrdo, ale podle hom_path
*            - pokud se z eskacek pridava novy radek do u_ex_pol nebo u_ex_plt, do hs_dal se pise timestamp, aby se to dobre hledalo
* 2024-04-09 - pro budvar se pred exportem trzeb vytvori prazdny exportni soubor
*
******************************************************************************

PARAMETERS datum_uz1, datum_uz2
clos data

u_ex_sk_had = createobject("had", program())
u_ex_sk_had.show()

DO CASE 
CASE ATC("d_galant", m.gchom_path) > 0
    DO u_ex_sk_galant

CASE ATC("d_myhotel", m.gchom_path) > 0
    DO u_ex_sk_myhotel

CASE ATC("BUDVAR", m.wi_zakaz) > 0
    DO exp_new IN ucto_exp
    DO uexsk WITH datum_uz1, datum_uz2, m.gccen_path, .F.

CASE m.issql
    DO uexsk WITH datum_uz1, datum_uz2, m.gccen_path, .F.

OTHERWISE 
    use (m.gcucto_path + "u_ex") excl IN 0
    insert into (m.gcucto_path + "u_ex") (md,     dal,     hs_md,     hs_dal,     md2,     pz,     datum_dokl, pz3) values ;
                 ("EXPORT", "EXPORT", right(gccen_path, 8), right(gccen_path, 8), str(seco()*50, 7), DTOS(m.datum_uz1) + "   " + DTOS(m.datum_uz2), DATE(), "TRZ-")

    do priprava

    u_ex_sk_had.set_had(44)
    do exp_pol

    u_ex_sk_had.set_had(55)
    do exp_plt

    u_ex_sk_had.set_had(77)
    DO generuj_souctovy_soubor IN ucto_exp

    if file(m.gcucto_path + "doklpokp.dbf") .and. file(m.gcucto_path + "doklpok.dbf")
        DO u_ex_sk_bakare                        &&    BAKARE    * * * * * 
    endif
ENDCASE
release u_ex_sk_had
DO clos_data 


*************************************************************
PROCEDURE exp_pol
use (m.gcucto_path + "u_ex_pol") in 0

sele pol
scan
  scatt memvar
  sele u_ex_pol
  locate for (id_kas = m.id_kas) .and. (dan = m.dan) .and. (id_man = m.id_man .or. id_man = "*") .and. (druh = m.druh .or. druh = "*") .and. (cen_hlad = m.cen_hlad .or. cen_hlad = "*")
  if ! found()
     insert into (m.gcucto_path + "u_ex_pol") ;
            (  id_kas,   id_man,   druh,   dan,   cen_hlad,     co,         pz,    poznamka, hs_md ) value ;
            (m.id_kas, m.id_man, m.druh, m.dan, m.cen_hlad, "dan_zakl", "uzaverka", "POL "+ m.id_kas + " " + m.id_man + " " + m.druh + str(m.dan*100-100, 3) + "  " + m.cen_hlad, STRTRAN(SUBSTR(TTOC(DATETIME(), 3), 1, 16), "-", ""))
  endif

  scan for (id_kas = m.id_kas) .and. (dan = m.dan) .and. (id_man = m.id_man .or. id_man = "*") .and. (druh = m.druh .or. druh = "*") .and. (cen_hlad = m.cen_hlad .or. cen_hlad = "*")
      scatt field md, dal, hs_md, hs_dal, md2, dal2, co, poznamka memvar
      partner = m.id_kas
      doklad  = m.u_cis
      datum   = m.u_cis
      faktura = str(m.dan_zakl,14,4)
      pz3 = "TRZ-POL"

      TRY 
          pz = EVALUATE(pz)
      CATCH 
          pz = "ERR:" + m.pz
      ENDTRY 

      TRY 
*          castka = &co
          castka = EVALUATE(co)
      CATCH 
          castka = 99999.99
          pz = "ERR:" + m.co
      ENDTRY 

      if m.castka # 0
         insert into (m.gcucto_path + "u_ex") from memvar
      endif
      sele u_ex_pol
  endscan
  sele pol
endscan

*************************************************************
PROCEDURE exp_plt

SELECT 0
use (m.gcucto_path + "u_ex_plt") excl
IF FSIZE("hpref") = 0
    ALTER TABLE (m.gcucto_path + "u_ex_plt") ADD hpref C(1)
ENDIF 

sele plt
scan
  scatt memvar
  sele u_ex_plt

  locate for id_kas = m.id_kas .and. druh_pl = m.druh_pl .and. hpref = m.hpref
  if ! found()
    insert into (m.gcucto_path + "u_ex_plt") ;
           (  id_kas,   druh_pl,   hpref,   co     ,                   pz      ,              poznamka                   , hs_md)         value ;
           (m.id_kas, m.druh_pl, m.hpref, "cena_pl", 'm.uzaverka +" "+ m.u_cis', "PLT " + m.druh_pl + " kasa " + m.id_kas, STRTRAN(SUBSTR(TTOC(DATETIME(), 3), 1, 16), "-", ""))
  endif

  scan for id_kas = m.id_kas .and. druh_pl = m.druh_pl .and. hpref = m.hpref
      scatt field md, dal, hs_md, hs_dal, md2, dal2, co, poznamka memvar
      partner = m.id_kas
      id_man = ""
      doklad = u_cis
      datum  = u_cis
      faktura = m.druh_pl + m.hpref
      pz3 = "TRZ-PLT"

      TRY 
          pz = EVALUATE(pz)
      CATCH 
          pz = "ERR:" + m.pz
      ENDTRY 

      TRY 
          castka = EVALUATE(co)
      CATCH 
          castka = 99999.99
          pz = "ERR:" + m.co
      ENDTRY 

      if m.castka # 0
         insert into (m.gcucto_path + "u_ex") from memvar
      endif
      sele u_ex_plt
  endscan
  sele plt
endscan

*************************************************************
PROCEDURE priprava

SELECT disti id_kas, LEFT(uzaverka, 12) as uzaverka, uz_cislo, uct_den WHERE BETWEEN(uct_den, m.datum_uz1, m.datum_uz2) ;
       FROM (m.fn_sk_sez) INTO TABLE (m.gcpri_path + "uzaverky")
index on uzaverka + id_kas TAG uzuniq uniq


* * * * * * * * * * * *    P L T    * * * * * * * * * * * * *

u_ex_sk_had.set_had(11)
SELECT uzaverka, u_cis, hpref ;
       WHERE uzaverka + id_kas in (sele uzaverka + id_kas from (m.gcpri_path + "uzaverky")) ;
       FROM (m.fn_sk_txt) INTO TABLE (m.gcpri_path + "txt")
index on uzaverka + u_cis TAG uzuniq uniq

SELECT uzaverka, u_cis, druh_pl, cena_pl, tip, mena, cena_mena, left(u_cis, 2) as id_kas, date() as datum_dokl, " " as hpref, ;
       00000000.00 AS trzba_dph1, 00000000.00 AS trzba_dph2, 00000000.00 AS trzba_dph3, 00000000.00 AS trzba_dph4 ;
       WHERE uzaverka + LEFT(u_cis,2) in (sele uzaverka + id_kas from (m.gcpri_path + "uzaverky")) ;
       FROM (m.fn_sk_plt) INTO TABLE (m.gcpri_path + "plt")

SET RELA TO uzaverka + id_kas INTO uzaverky
replace all datum_dokl with uzaverky.uct_den

SET RELA TO uzaverka + u_cis INTO txt
replace all hpref with txt.hpref


* * * * * * * * * * * *    P O L    * * * * * * * * * * * * *

u_ex_sk_had.set_had(22)
use (m.gccen_path + "skupiny") order druh in 0 Alias skupiny
sele 0
use (m.gccen_path + "karty") order nazev Alias mkahlc
set Relation To druh_kalk into skupiny

SELECT id_kas, id_man, uzaverka, u_cis, nazev, dan, cen_hlad, SPACE(10) AS druh, prachy ;
       WHERE uzaverka + id_kas in (sele uzaverka + id_kas from (m.gcpri_path + "uzaverky")) AND ! EMPT(k_odpoctu);
       FROM (m.fn_sk_pol) INTO TABLE (m.gcpri_path + "pol1")
SET RELA TO nazev INTO mkahlc

IF ATC("D_GAST", m.gchom_path) + ATC("D_XYZ", m.gchom_path) > 0   &&& uctovani po gastroskupinach nebo po spartech
    WAIT " po DRUZICH" WINDOW TIMEOUT 2
    REPL ALL druh WITH mkahlc.druh_kalk
ELSE 
    WAIT " po SPARTECH" WINDOW TIMEOUT 2
    REPL ALL druh WITH PADR(skupiny.spart, 10)
ENDIF 

REPL FOR EMPTY(druh) druh WITH "____________"

SELECT id_kas, id_man, uzaverka, u_cis, druh, dan, cen_hlad, date() as datum_dokl, ;
       sum(prachy) AS trzba_puv, 00000000.00 AS trzba, 00000000.0000 AS dan_zakl, 00000000.0000 AS dph, space(8) as druh_pl, 0000 as uz_cislo ;
       group by uzaverka, u_cis, id_man, druh, dan, cen_hlad ;
       FROM (m.gcpri_path + "pol1") INTO TABLE (m.gcpri_path + "pol")

SET RELA TO uzaverka + id_kas INTO uzaverky
replace all uz_cislo with uzaverky.uz_cislo, datum_dokl with uzaverky.uct_den


***************    vypocet koeficientu pro prodejni cenu polozek    ***************  
SELECT uzaverka, u_cis, SUM(cena_pl) as prachy_plt GROUP BY uzaverka, u_cis ;
       FROM (gcpri_path + "plt") INTO TABLE (m.gcpri_path + "plt_sum")

SELECT uzaverka, u_cis, SUM(trzba_puv) as prachy_pol, $0 as prachy_plt ;
       GROUP BY uzaverka, u_cis ;
       FROM (gcpri_path + "pol") INTO TABLE (m.gcpri_path + "pol_sum")
APPEND FROM (m.gcpri_path + "plt_sum")

SELECT uzaverka, u_cis, SUM(prachy_pol) as prachy_pol, SUM(prachy_plt) as prachy_plt, 001.000000000 as slev_koef ;
       GROUP BY uzaverka, u_cis ;
       FROM (gcpri_path + "pol_sum") INTO TABLE (m.gcpri_path + "pol_plt")
REPLACE FOR prachy_pol # 0 slev_koef WITH ROUND(prachy_plt / prachy_pol, 9)
INDEX ON uzaverka + u_cis TAG uz_ucet

SELECT pol
SET RELA TO uzaverka + u_cis INTO pol_plt
REPL ALL trzba WITH round(trzba_puv * pol_plt.slev_koef, 2)
SET RELA TO
DELETE FOR trzba = 0
INDEX ON uzaverka + u_cis tag uz_uc


* * * * *    oprava pripadnych zaokrouhlovacich rozdilu trzby    * * * * *
SELECT uzaverka, u_cis, sum(trzba) AS opr_cen group by uzaverka, u_cis ;
       FROM (m.gcpri_path + "pol") INTO TABLE (m.gcpri_path + "opr_cen")
SET RELA TO uzaverka + u_cis INTO pol_plt
REPL ALL opr_cen WITH pol_plt.prachy_plt - opr_cen
SET RELA OFF INTO pol_plt
scan for opr_cen # 0
   sele pol
   locate for uzaverka + u_cis = opr_cen.uzaverka + opr_cen.u_cis
   repl trzba with trzba + opr_cen.opr_cen
   sele opr_cen
endscan

SELECT pol
SET ORDER TO

REPLACE ALL dph WITH round(trzba * (dan - 1) / dan, 2), dan_zakl WITH trzba - dph

do pozavirej with "mkahlc, opr_cen, pol1, sk_pol, sk_plt, fin1, sk_fin"


* * * * * * * * * * * *    BAKARE    * * * * * * * * * * * * *    zacatek
IF "d_strah" $ LOWER(m.gchom_path)  &&&&&&   vynechani plateb, ktere smidova nechce
    SELECT plt
    index on uzaverka + u_cis to (m.gcpri_path + "plt_plt")
    sele pol
    set Relation To uzaverka + u_cis into plt
    repl all druh_pl with plt.druh_pl
    set Relation To
    dele for UPPER(druh_pl) $ "K_HOTEL N_UVER   "
    sele plt
    dele for UPPER(druh_pl) $ "K_HOTEL N_UVER   "
ENDIF 
* * * * * * * * * * * *    BAKARE    * * * * * * * * * * * * *     konec


*************************************************************
PROCEDURE u_ex_sk_bakare
  u_ex_sk_had.set_had(88)
  select left(pz, 16) + left(poznamka, 15) as filler1, ;
         "TRŽBA " + left(pz, 8) as txtdoklpok, substr(pz, 3,7) + partner as idedoklpok, md as idepokladn, dal as ideorgvlst, "CZ" + hs_dal as idedicvlst, ;
         hs_md as idepol, sum(castka) as dphmencelk, sum(val(faktura)) as cenmencelk, val(md2) as nkodudph, datum_dokl ;
         group by 1 from (m.gcucto_path + "u_ex") into table (m.gcpri_path + "ex_poloz")
  delete for idepokladn = "EXPORT"
  select 0
  use (m.gcucto_path + "doklpokp") excl
  zap
  append from (m.gcpri_path + "ex_poloz")
  replace all cencelkb with cenmencelk, cenmenpolp with cenmencelk, ;
              dphcelk  with dphmencelk, dphmenpolp with dphmencelk, ;
              mnopol with 1, procslevy with 0, cenmenkore with 0, zablokuct with "N", cenkorekce with 0, cenmenkore with 0

  u_ex_sk_had.set_had(97)
  select disti idedoklpok, idepokladn, ideorgvlst, ideorgvlst as ideorg, idedicvlst, idedicvlst as idedic, ;
         {^2005-01-01} as datkurzume, datum_dokl as datvystdok, datum_dokl as datuzpdokl, ;
         "CZK" as idemeny, 1 as kurzmeny, 1 as mnojednmen, "N" as jestorno, "N" as jedoklok, 1210 as ntypudoklp ;
         from (m.gcpri_path + "ex_poloz") into table (m.gcpri_path + "ex_hlav")

  select 0
  use (m.gcucto_path + "doklpok") excl
  zap
  append from (m.gcpri_path + "ex_hlav")
