PARAMETERS uzaverka1, uzaverka2, src_path, zkontroly

old_errh = ON("ERROR")

DO open_database
IF !USED("karty")
		USE (m.sqlprefix+"rkartyucto") ALIAS karty IN 0
		SELECT karty
		INDEX on c_karty TAG c_karty
ENDIF 
IF !USED("skupiny")
		USE (m.sqlprefix+"rskupiny") ALIAS skupiny IN 0
ENDIF 
IF !USED("food_dat")
		USE (m.sqlprefix+"rfooddat") ALIAS food_dat IN 0
ENDIF 

u_ex_sk_had = CREATEOBJECT("had", "Export tržeb")
u_ex_sk_had.SHOW()
u_ex_sk_had.set_txt2("priprava")

insert into (m.gcucto_path + "u_ex") (md,     dal,     hs_md,     hs_dal,     md2,     pz,     datum_dokl, pz3) values ;
       ("EXPORT", "EXPORT", right(m.src_path, 8), right(m.src_path, 8), str(seco()*50, 7), DTOS(m.uzaverka1) + "   " + DTOS(m.uzaverka2), DATE(), "TRZ-")

DO priprava

u_ex_sk_had.set_had(44)
u_ex_sk_had.set_txt2("polozky")
DO exp_pol

u_ex_sk_had.set_had(66)
u_ex_sk_had.set_txt2("platby")
DO exp_plt

u_ex_sk_had.set_had(88)
DO generuj_souctovy_soubor IN ucto_exp

RELEASE u_ex_sk_had
DO pozavirej WITH "pol, plt, fin, u_ex, u_ex_sum"


*************************************************************
PROCEDURE exp_pol
SELECT pol 
INDEX on id_kas + uzaverka + u_cis TAG uz_u_cis
o_uz = "(*&^#$%"
SCAN ALL 
    SCATTER MEMVAR 

    IF o_uz # m.id_kas + " " + m.uzaverka 
       o_uz = m.id_kas + " " + m.uzaverka 
       u_ex_sk_had.set_txt2("polozky " + LEFT(m.o_uz, 11))
    ENDIF 

	dan = ROUND(m.dan, 2)

	IF testuexpol()
		SELECT uexpol
        SCAN
          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)
          on error pz = pz
          pz = eval(pz)
          pz3 = "TRZ-POL"
          on error castka = 9999.99
          castka = &co    &&&& EVALUATE(co)
          ON ERROR &old_errh
          if m.castka # 0
             insert into (m.gcucto_path + "u_ex") from memvar
          endif
        ENDSCAN 
	ENDIF 
ENDSCAN 


*************************************************************
PROCEDURE exp_plt
SELECT plt
INDEX on id_kas + uzaverka + u_cis TAG uz_u_cis
o_uz = "(*&^#$%"
SCAN ALL 
    SCATTER MEMVAR 

    IF o_uz # m.id_kas + " " + m.uzaverka 
       o_uz = m.id_kas + " " + m.uzaverka 
       u_ex_sk_had.set_txt2("polozky " + LEFT(m.o_uz, 11))
    ENDIF 

	hladajidkas  = m.id_kas
	hladajdruhpl = m.druh_pl
	IF testuexplt()
		SELECT uexplt1

        SCAN
          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 = m.druh_pl
          on error pz = pz
          pz = eval(pz)
          pz3 = "TRZ-PLT"
          on error castka = 9999.99
          castka = &co
          ON ERROR &old_errh
          if m.castka # 0
             insert into (m.gcucto_path + "u_ex") from memvar
          endif
        ENDSCAN 
	ENDIF 
ENDSCAN 


*************************************************************
PROCEDURE priprava
* * * * * * * * * * * *    F I N    * * * * * * * * * * * * *

lsql  = "SELECT DISTINCT uzaverka, c_uzaverka, id_kas, uct_den FROM food600.sk__sez WHERE c_data=?m.cdata AND uct_den>=?m.uzaverka1 and uct_den<=?m.uzaverka2"
lsql2 = "SELECT DISTINCT c_uzaverka                            FROM food600.sk__sez WHERE c_data=?m.cdata AND uct_den>=?m.uzaverka1 and uct_den<=?m.uzaverka2"

sql_select(lsql,'sk_sez',"uzaverka c(17), c_uzaverka I, id_kas c(2), uct_den D")
lsql1="SELECT c_uzaverka, ucet, c_stredisk, c_karty, prachy, prachy_puv, dan, dan_sazba, cen_hlad, mnozstvi FROM food600.sk__pol where c_data=?m.cdata AND kompez=0 AND k_odpoctu<>'' AND c_uzaverka in ("+m.lsql2+")"
sql_select(lsql1,'sk_pol',"c_uzaverka I, ucet I, c_stredisk I, c_karty I, prachy Y, prachy_puv Y, dan Y, dan_sazba N(5,2), cen_hlad c(2), mnozstvi Y")
lsql1="SELECT c_uzaverka, ucet, druh_pl, cena_pl, mena, cena_mena, tip FROM food600.sk__plt where c_data=?m.cdata AND kompez=0 AND c_uzaverka in ("+m.lsql2+")"
sql_select(lsql1,'sk_plt',"c_uzaverka I, ucet I, druh_pl C(8), cena_pl Y, mena C(3), cena_mena Y, tip Y")
	
u_ex_sk_had.set_had(10)

SELECT sk_sez.uzaverka, sk_sez.id_kas, sk_pol.ucet, sk_pol.c_stredisk, sk_pol.c_karty, sk_pol.prachy as trzba, sk_pol.prachy_puv as trzba_puv, sk_pol.dan, ;
	$0 as dan_zakl, SPACE(7) as u_cis, sk_sez.uct_den as datum_dokl, sk_pol.dan_sazba/100+1 as dan_sazba, sk_pol.cen_hlad, karty.c_druh, karty.c_druhs, ;
	karty.c_druhk, SPACE(10) as druh, SPACE(10) as spart, SPACE(2) as id_man, 00000 as spstupne, m.spotrebnadan as spdan, karty.objem, sk_pol.mnozstvi ;
	FROM sk_pol LEFT JOIN sk_sez ON sk_pol.c_uzaverka=sk_sez.c_uzaverka LEFT JOIN karty ON sk_pol.c_karty=karty.c_karty INTO CURSOR pol1 READWRITE 

UPDATE pol1 SET druh=skupiny.druh, spart=skupiny.spart from skupiny WHERE pol1.c_druhk=skupiny.c_druh
UPDATE pol1 SET druh=skupiny.druh, spart=skupiny.spart from skupiny WHERE pol1.c_druh=skupiny.c_druh  AND (EMPTY(pol1.druh) OR ISNULL(pol1.druh))
UPDATE pol1 SET druh=skupiny.druh, spart=skupiny.spart from skupiny WHERE pol1.c_druhs=skupiny.c_druh AND (EMPTY(pol1.druh) OR ISNULL(pol1.druh))
UPDATE pol1 SET id_man=food_dat.id from food_dat WHERE pol1.c_stredisk=food_dat.c_stredisk
SELECT pol1
REPLACE ALL  u_cis WITH id_kas+PADL(ALLTRIM(STR(ucet,5,0)),5,"0"), dan_zakl WITH trzba-dan


*	IF LOWER(m.gcuct_pole)="spart"
		REPLACE ALL druh WITH spart

        REPLACE FOR EMPTY(druh ) druh WITH "____________"
        REPLACE FOR ISNULL(druh) druh WITH "____________"
*	ENDIF 

SELECT id_kas, id_man, uzaverka, u_cis, druh, dan_sazba as dan, cen_hlad, datum_dokl, SUM(mnozstvi*spdan*objem*spstupne/100*dan_sazba) as spdan, ;
		SUM(trzba_puv) AS trzba_puv, SUM(trzba) AS trzba, SUM(dan_zakl) AS dan_zakl, 0000 AS uz_cislo, ;
		$0 as castka, SPACE(20) as faktura, .f. as zaloha, SPACE(30) as partner, SPACE(15) as doklad ;
		GROUP BY uzaverka, u_cis, id_kas, id_man, druh, dan, cen_hlad ;
		FROM pol1 INTO CURSOR pol READWRITE 

REPLACE FOR ISNULL(trzba) trzba WITH 0
REPLACE FOR ISNULL(spdan) spdan WITH 0
REPLACE ALL spdan WITH ground(spdan,2)
REPLACE ALL trzba WITH trzba-spdan

REPLACE dan_zakl WITH dan_zakl-ground(spdan/dan,2) FOR dan<>0 AND spdan<>0
u_ex_sk_had.set_had(20)
SELECT c_uzaverka, ucet, SUM(prachy_puv) as cena_celk, $0 as sleva_abs, SUM(prachy) as cena_slev, SUM(dan) as dph_celk, 0.000 as dan1, 0.000 as dan2, 0.000 as dan3, 0.000 as dan4, ;
	00000000.00 AS trzba_dph1, 00000000.00 AS trzba_dph2, 00000000.00 AS trzba_dph3, 00000000.00 AS trzba_dph4, "   " AS mena;
	FROM sk_pol INTO CURSOR fin READWRITE GROUP BY c_uzaverka, ucet
SELECT fin
REPLACE ALL sleva_abs WITH cena_celk-cena_slev
*** Kontrola jedinecnosti nastavenia povolenych sadzieb dph a vyplnenie trzba_dph1-4
j=1
sj="1"
LOCAL m.uzbolo
FOR m.i=1 TO 7
	m.uzbolo=.f.
	m.si=STR(m.i,1,0)
	IF m.i>1
		FOR m.k=1 TO m.i-1
			sk=STR(m.k,1,0)
			IF m.dph&si=m.dph&sk
				m.uzbolo=.t.
				EXIT
			ENDIF 
		ENDFOR 
	ENDIF 
	IF !m.uzbolo
		SELECT c_uzaverka, ucet, SUM(prachy) as suma FROM sk_pol INTO CURSOR podaniach WHERE dan_sazba=(m.dph&si-1)*100 GROUP BY c_uzaverka, ucet
		IF RECCOUNT("podaniach")>0
			SELECT fin
			REPLACE ALL dan&sj WITH m.dph&si
			UPDATE fin SET trzba_dph&sj=podaniach.suma from podaniach WHERE fin.c_uzaverka=podaniach.c_uzaverka AND fin.ucet=podaniach.ucet
			m.j=m.j+1
			m.sj=STR(m.j,1,0)
		ENDIF 
		USE IN podaniach
	ENDIF 
ENDFOR 
u_ex_sk_had.set_had(30)

SELECT sk_sez.uzaverka, sk_sez.id_kas, SPACE(7) as u_cis, sk_plt.druh_pl, sk_plt.cena_pl, sk_plt.tip, sk_plt.mena, sk_plt.cena_mena, sk_sez.uct_den AS datum_dokl, sk_plt.c_uzaverka, sk_plt.ucet,;
		SPACE(8) as md, SPACE(8) as dal, SPACE(15) as hs_md, SPACE(15) as hs_dal, .f. as  exportuj, SPACE(7) as md2, SPACE(7) as dal2, ;
		SPACE(30) as co, SPACE(50) as pz, SPACE(30) as poznamka,$0 as castka, SPACE(20) as faktura, .f. as zaloha, SPACE(30) as partner, SPACE(15) as doklad, ; 
		00000000.00 AS trzba_dph1, 00000000.00 AS trzba_dph2, 00000000.00 AS trzba_dph3, 00000000.00 AS trzba_dph4 FROM sk_plt LEFT JOIN sk_sez ON sk_plt.c_uzaverka=sk_sez.c_uzaverka;
		INTO CURSOR plt READWRITE 
REPLACE ALL u_cis WITH id_kas+PADL(ALLTRIM(STR(ucet,5,0)),5,"0")
UPDATE plt SET trzba_dph1=fin.trzba_dph1*plt.cena_pl/fin.cena_slev, trzba_dph2=fin.trzba_dph2*plt.cena_pl/fin.cena_slev, trzba_dph3=fin.trzba_dph3*plt.cena_pl/fin.cena_slev, trzba_dph4=fin.trzba_dph4*plt.cena_pl/fin.cena_slev from fin;
		WHERE plt.c_uzaverka=fin.c_uzaverka AND plt.ucet=fin.ucet AND fin.cena_slev<>0


*************************************************************
FUNCTION testuexplt
IF USED("uexplt1")
	=REQUERY("uexplt1")
ELSE
	USE (m.sqlprefix+"ruexplt1") ALIAS uexplt1 IN 0
	=CURSORSETPROP("Buffering",5,"uexplt1")
ENDIF

IF RECCOUNT("uexplt1")=0                   	&& konkretny druh_pl a kasa nenalezen, zakladam riadok do u_ex_plt 
    INSERT INTO uexplt1 (id_kas, druh_pl  ,    co    ,       pz              ,               poznamka                                  , c_data,     md2,                   dal2,                    hs_md      ) ;
     VALUES (m.hladajidkas, m.hladajdruhpl, "cena_pl", 'uzaverka +" "+ u_cis', "Platba " + m.hladajdruhpl + " na kase " + m.hladajidkas, m.cdata, SUBSTR(DTOS(DATE()), 3), LEFT(TIME(), 5), RIGHT(m.machine, 15))
	=TABLEUPDATE(.t.,.t.,"uexplt1")	
	=REQUERY("uexplt1")
ENDIF 


*************************************************************
FUNCTION testuexpol
IF USED("uexpol")
	REQUERY("uexpol")
ELSE
	USE (m.sqlprefix+"ruexpol") ALIAS uexpol IN 0
	CURSORSETPROP("Buffering",5,"uexpol")
ENDIF

SELECT uexpol
SET FILTER TO id_kas = m.id_kas .and. id_man = m.id_man .and. druh = m.druh .and. cen_hlad = m.cen_hlad .and. ROUND(dan,2) = m.dan    &&  konkretny man, konkretny druh a konkretna cen_hlad
COUNT TO pocet_radku
IF m.pocet_radku > 0
    RETURN 
ENDIF 

SET FILTER TO id_kas = m.id_kas .and. id_man = m.id_man .and. druh =  "*"   .and. cen_hlad = m.cen_hlad .and. ROUND(dan,2) = m.dan    &&  konkretny man, VSETKY DRUHY, konkretna cen_hlad
COUNT TO pocet_radku
IF m.pocet_radku > 0
    RETURN 
ENDIF 

SET FILTER TO id_kas = m.id_kas .and. id_man = m.id_man .and. druh = m.druh .and. cen_hlad =    "*"     .and. ROUND(dan,2) = m.dan    &&  konkretny man, konkretny druh a VSETKY CEN_HLAD
COUNT TO pocet_radku
IF m.pocet_radku > 0
    RETURN 
ENDIF 

SET FILTER TO id_kas = m.id_kas .and. id_man =   "*"    .and. druh = m.druh .and. cen_hlad = m.cen_hlad .and. ROUND(dan,2) = m.dan      && VSETKY MAN, konkretny druh a konkretna cen_hlad
COUNT TO pocet_radku
IF m.pocet_radku > 0
    RETURN 
ENDIF 

SET FILTER TO id_kas = m.id_kas .and. id_man = m.id_man .and. druh =  "*"   .and. cen_hlad =    "*"     .and. ROUND(dan,2) = m.dan    &&  konkretny man, VSETKY DRUHY a VSETKY CEN_HLAD
COUNT TO pocet_radku
IF m.pocet_radku > 0
    RETURN 
ENDIF 

SET FILTER TO id_kas = m.id_kas .and. id_man =   "*"    .and. druh =  "*"   .and. cen_hlad = m.cen_hlad .and. ROUND(dan,2) = m.dan    &&  VSETKY MAN, VSETKY DRUHY a konkretna cen_hlad
COUNT TO pocet_radku
IF m.pocet_radku > 0
    RETURN 
ENDIF 

SET FILTER TO id_kas = m.id_kas .and. id_man =    "*"   .and. druh = m.druh .and. cen_hlad =     "*"    .and. ROUND(dan,2) = m.dan    &&  VSETKY MAN, konkretny druh a VSETKY CEN_HLAD
COUNT TO pocet_radku
IF m.pocet_radku > 0
    RETURN 
ENDIF 

**	nic sa nenaslo, musim zalozit novy riadok do u_ex_pol
INSERT INTO uexpol (id_kas,   id_man,   druh,   cen_hlad,   dan,   co   ,     pz               ,           poznamka                                                                                  ,  c_data,     md2,     dal2,                                 hs_md      ) ;
          VALUES (m.id_kas, m.id_man, m.druh, m.cen_hlad, m.dan, "trzba",  'uzaverka+" "+u_cis', "POL "+ m.id_kas + " " + m.id_man + " " + ALLTRIM(m.druh) + STR(m.dan*100-100, 4) + " " + m.cen_hlad, m.cdata, SUBSTR(DTOS(DATE()), 3), LEFT(TIME(), 5), RIGHT(m.machine, 15))
TABLEUPDATE(.t.,.t.,"uexpol")	
REQUERY("uexpol")
