
m.fn_sk_o_plt = gccen_path + "sk_plt"
m.fn_sk_o_pol = gccen_path + "sk_pol"
m.fn_sk_o_fin = gccen_path + "sk_fin"
m.fn_sk_o_sez = gccen_path + "sk_sez"
m.fn_sk_o_txt = gccen_path + "sk_txt"

m.fn_sk_n_plt = gccen_path + "sk__plt"
m.fn_sk_n_pol = gccen_path + "sk__pol"
m.fn_sk_n_fin = gccen_path + "sk__fin"
m.fn_sk_n_sez = gccen_path + "sk__sez"
m.fn_sk_n_txt = gccen_path + "sk__txt"

DO sk_new2

#DEFINE UZAV_SPACEHOLDER 000000000
#DEFINE STRED_SPACEHOLDER  0000000


*********************************************************************************************
**     sk_new1     sk_new1     sk_new1     sk_new1     sk_new1     sk_new1     sk_new1     **
**                                                                                         **
**  spoji aktualni a archivni databaze vybranych uzaverek do (m.fn_sk_o_XXX                **
**                                                                                         **
*********************************************************************************************
PROCEDURE sk_new1
PRIVATE m.hladajuzaverkaod, m.hladajuzaverkado
had = createobject("had", "sk_new1")
had.show()
had.set_max(15)
had.increment("sk_sez")

SELECT MIN(uzaverka) as uzaverka FROM sk_sez INTO CURSOR minuz
IF !USED("sk_sez_a")
	m.ajarchiv=.f.
ELSE 
	IF RECCOUNT("minuz")>0
		IF minuz.uzaverka>STRTRAN(m.minuz,"O","0")
			m.ajarchiv=.t.
		ELSE
			m.ajarchiv=.f.
		ENDIF 
	ELSE
		m.ajarchiv=.t.
	ENDIF 
ENDIF 
USE IN minuz
SELECT * WHERE uzaverka + id_kas IN (SELECT uzaverka + id_kas FROM uzaverky WHERE ukazat .and. lbl) ;
       FROM sk_sez INTO CURSOR sk_sez_ READWRITE 
IF m.ajarchiv
	INSERT INTO sk_sez_ (id_kas, id_man, uzaverka, autor, uz_cislo, c_mena, uct_den, lastuzav) ;
           SELECT        id_kas, id_man, uzaverka, autor, uz_cislo, c_mena, uct_den, lastuzav ;
                  WHERE uzaverka + id_kas IN (SELECT uzaverka + id_kas FROM uzaverky WHERE ukazat .and. lbl) ;
                  FROM sk_sez_a 
ENDIF 
SELECT sk_sez_
INDEX ON uzaverka + id_kas TAG uz_idkas
REPLACE c_mena WITH m.giakt_mena FOR EMPTY(c_mena)
REPLACE ALL priznak WITH ""

IF m.issql
    * zatim nic
ELSE 
	IF m.ajarchiv

       IF FILE(m.gccen_path + "sk_pol_a2.dbf") .and. FILE(m.gccen_path + "sk_plt_a2.dbf") .and. FILE(m.gccen_path + "sk_fin_a2.dbf") && volba, zda se pouziji velka archivni eskacka, nebo redukovana
           USE (m.gccen_path + "sk_pol_a2") ALIAS sk_pol_a IN 0 
           USE (m.gccen_path + "sk_plt_a2") ALIAS sk_plt_a IN 0 
           USE (m.gccen_path + "sk_fin_a2") ALIAS sk_fin_a IN 0 
           USE (m.gccen_path + "sk_txt_a2") ALIAS sk_txt_a IN 0 
           IF FILE(m.gccen_path + "sk_vklvy_a2.dbf")
           		USE (m.gccen_path + "sk_vklvy_a2") ALIAS sk_vklvy_a IN 0 
           	ENDIF 
       ELSE
           IF !USED("sk_pol_a")
               USE (m.fn_sk_pol_a) ALIAS sk_pol_a IN 0 
           ENDIF 
           IF !USED("sk_plt_a")
               USE (m.fn_sk_plt_a) ALIAS sk_plt_a IN 0 
           ENDIF 
           IF !USED("sk_fin_a")              
               USE (m.fn_sk_fin_a) ALIAS sk_fin_a IN 0 
           ENDIF 
           IF !USED("sk_txt_a")
               USE (m.fn_sk_txt_a) ALIAS sk_txt_a IN 0 
           ENDIF 
           IF !USED("sk_vklvy_a")
               USE (m.fn_sk_vklvy_a) ALIAS sk_vklvy_a IN 0 
           ENDIF 
       ENDIF

	ENDIF 	
	IF !USED("sk_pol")
		USE (m.fn_sk_pol) ALIAS sk_pol IN 0 
	ENDIF 
	IF !USED("sk_plt")
		USE (m.fn_sk_plt) ALIAS sk_plt IN 0 
	ENDIF 
	IF !USED("sk_fin")
		USE (m.fn_sk_fin) ALIAS sk_fin IN 0 
	ENDIF 
	IF !USED("sk_txt")
		USE (m.fn_sk_txt) ALIAS sk_txt IN 0 
	ENDIF 
	IF FILE(m.gccen_path+"sk_vklvy.dbf") AND !USED("sk_vklvy") 
		USE (m.gccen_path+"sk_vklvy") ALIAS sk_vklvy IN 0 
	ENDIF 
ENDIF  

*  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *
had.increment("sk_pol")
DO pozavirej WITH "sk_pol_"
IF m.slovenskaverzia
	SELECT id_kas, id_man, c_karty, $0+prachy as prachy, dan, $0+mnozstvi as mnozstvi, u_cis, uzaverka, k_odpoctu, cen_hlad, dotacia, id_fmenu, u_time, 1 as dil_porce, kompez, procentosl ;
	       WHERE !kompez AND uzaverka+id_kas IN (SELECT uzaverka+id_kas FROM sk_sez_) FROM sk_pol INTO CURSOR sk_pol_ readwrite 
	ALTER TABLE sk_pol_ ALTER c_karty INTEGER

	IF m.ajarchiv
		had.increment("append sk_pol_a")
		INSERT INTO sk_pol_ (id_kas, id_man, c_karty, prachy, dan, mnozstvi, u_cis, uzaverka, k_odpoctu, cen_hlad, dotacia, id_fmenu, u_time, dil_porce, kompez, procentosl);
			SELECT           id_kas, id_man, c_karty, prachy, dan, mnozstvi, u_cis, uzaverka, k_odpoctu, cen_hlad, dotacia, id_fmenu, u_time, 1, kompez, procentosl FROM sk_pol_a;
			WHERE  !kompez AND sk_pol_a.uzaverka+sk_pol_a.id_kas IN (SELECT uzaverka+id_kas FROM sk_sez_)
	ENDIF 
	SELECT sk_pol_
	REPLACE FOR dan = 0 dan WITH 1.0    && aby nebyly chyby deleni nulovou dani
	REPLACE FOR LEFT(u_time, 1) = "/" .and. BETWEEN(SUBSTR(u_time,2,1), "2", "9") dil_porce WITH VAL(SUBSTR(u_time,2,1))    && delene porce

	SELECT id_kas, id_man, c_karty, $0+prachy as prachy, dan, $0+mnozstvi as mnozstvi, u_cis, uzaverka, k_odpoctu, cen_hlad, dotacia, id_fmenu, u_time, 1 as dil_porce, kompez, procentosl ;
	       WHERE kompez AND uzaverka+id_kas IN (SELECT uzaverka+id_kas FROM sk_sez_) FROM sk_pol INTO CURSOR sk_pol_kompez READWRITE 
	IF m.ajarchiv
		had.increment("append sk_pol_a")
		INSERT INTO sk_pol_kompez (id_kas, id_man, c_karty, prachy, dan, mnozstvi, u_cis, uzaverka, k_odpoctu, cen_hlad, dotacia, id_fmenu, u_time, dil_porce, kompez, procentosl);
			SELECT                 id_kas, id_man, c_karty, prachy, dan, mnozstvi, u_cis, uzaverka, k_odpoctu, cen_hlad, dotacia, id_fmenu, u_time, 1, kompez, procentosl FROM sk_pol_a;
			WHERE  kompez AND sk_pol_a.uzaverka+sk_pol_a.id_kas IN (SELECT uzaverka+id_kas FROM sk_sez_)
	ENDIF 
	SELECT sk_pol_kompez
	REPLACE FOR dan = 0 dan WITH 1.0    && aby nebyly chyby deleni nulovou dani
	REPLACE FOR LEFT(u_time, 1) = "/" .and. BETWEEN(SUBSTR(u_time,2,1), "2", "9") dil_porce WITH VAL(SUBSTR(u_time,2,1))    && delene porce
ELSE

*      sloupec KOMPEZ je v cesku pouzit pro oznaceni NEZLEVNENYCH polozek uctu
*      vyrobim i prazdnou databazi sk_pol_kompez, takze zbytek programu muze zustat stejny
*      2018-06 zaveden sloupec sk_pol.procslev s procentem slevy polozky

    SELECT id_kas, id_man, c_karty, $0+prachy as prachy, dan, $0+mnozstvi as mnozstvi, u_cis, uzaverka, k_odpoctu, cen_hlad, dotacia, id_fmenu, u_time, 1 as dil_porce, kompez, procentosl ;
           WHERE uzaverka+id_kas IN (SELECT uzaverka+id_kas FROM sk_sez_) FROM sk_pol INTO CURSOR sk_pol_ readwrite 
    ALTER TABLE sk_pol_ ALTER c_karty INTEGER

    IF m.ajarchiv
    	had.increment("append sk_pol_a")
    	INSERT INTO sk_pol_ (id_kas, id_man, c_karty, prachy, dan, mnozstvi, u_cis, uzaverka, k_odpoctu, cen_hlad, dotacia, id_fmenu, u_time, dil_porce, kompez, procentosl);
    		SELECT           id_kas, id_man, c_karty, prachy, dan, mnozstvi, u_cis, uzaverka, k_odpoctu, cen_hlad, dotacia, id_fmenu, u_time,     1    , kompez, procentosl FROM sk_pol_a;
    		WHERE  sk_pol_a.uzaverka+sk_pol_a.id_kas IN (SELECT uzaverka+id_kas FROM sk_sez_)
    ENDIF 
    SELECT sk_pol_
    REPLACE FOR dan = 0 dan WITH 1.0    && aby nebyly chyby deleni nulovou dani
    REPLACE FOR LEFT(u_time, 1) = "/" .and. BETWEEN(SUBSTR(u_time,2,1), "2", "9") dil_porce WITH VAL(SUBSTR(u_time,2,1))    && delene porce

    SELECT id_kas, id_man, c_karty, $0+prachy as prachy, dan, $0+mnozstvi as mnozstvi, u_cis, uzaverka, k_odpoctu, cen_hlad, dotacia, id_fmenu, u_time, 1 as dil_porce ;
           WHERE .F. AND uzaverka+id_kas IN (SELECT uzaverka+id_kas FROM sk_sez_) FROM sk_pol INTO CURSOR sk_pol_kompez READWRITE 
ENDIF

DO pozavirej WITH "sk_pol, sk_pol_a"


****************************    kvuli menu ve frontiere
IF .not. m.slovenskaverzia
    SELECT sk_pol_ 
    REPLACE FOR EMPTY(id_man) k_odpoctu WITH ""
ENDIF 
****************************    kvuli menu ve frontiere



*  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *
had.increment("sk_vklvy")
SELECT u_cis, uzaverka, nazev, popis, datum, operacia, id_kas, autor, mena, ciastka ;
       WHERE uzaverka+id_kas IN (SELECT uzaverka+id_kas FROM sk_sez_) ;
       FROM sk_vklvy INTO CURSOR sk_vklvy_ READWRITE 
IF m.ajarchiv
	had.increment("append sk_vklvy_a")
	INSERT INTO sk_vklvy_ (u_cis, uzaverka, nazev, popis, datum, operacia, id_kas, autor, mena, ciastka);
		SELECT u_cis, uzaverka, nazev, popis, datum, operacia, id_kas, autor, mena, ciastka FROM sk_vklvy_a ; 
		WHERE sk_vklvy_a.uzaverka+sk_vklvy_a.id_kas IN (SELECT uzaverka+id_kas FROM sk_sez_)
ENDIF 
DO pozavirej WITH "sk_vklvy, sk_vklvy_a"


*  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *
had.increment("sk_plt")
SELECT u_cis, uzaverka, druh_pl, mena, SUM(cena_pl) as cena_pl, SUM(tip) as tip, SUM(cena_mena) as cena_mena, kompez ;
       WHERE EMPTY(kompez) AND uzaverka+LEFT(u_cis,2) IN (SELECT uzaverka+id_kas FROM sk_sez_) ;
       GROUP BY 1,2,3,4 FROM sk_plt INTO CURSOR sk_plt_ READWRITE 
IF m.ajarchiv
	had.increment("append sk_plt_a")
	INSERT INTO sk_plt_ (u_cis, uzaverka, druh_pl, mena, cena_pl, tip, cena_mena);
		SELECT u_cis, uzaverka, druh_pl, mena, SUM(cena_pl) as cena_pl, SUM(tip) as tip, SUM(cena_mena) as cena_mena FROM sk_plt_a group by 1,2,3,4 ;
		WHERE  EMPTY(kompez) AND sk_plt_a.uzaverka+LEFT(sk_plt_a.u_cis,2) IN (SELECT uzaverka+id_kas FROM sk_sez_)
ENDIF 
SELECT u_cis, uzaverka, cena_pl, druh_pl, tip, mena, cena_mena, kompez WHERE !EMPTY(kompez) AND uzaverka+LEFT(u_cis,2) IN (SELECT uzaverka+id_kas FROM sk_sez_) ;
	FROM sk_plt INTO CURSOR sk_plt_kompez READWRITE 
IF m.ajarchiv
	had.increment("append sk_plt_a")
	INSERT INTO sk_plt_kompez (u_cis, uzaverka, cena_pl, druh_pl, tip, mena, cena_mena, kompez);
		SELECT u_cis, uzaverka, cena_pl, druh_pl, tip, mena, cena_mena, kompez FROM sk_plt_a;
		WHERE  !EMPTY(kompez) AND sk_plt_a.uzaverka+LEFT(sk_plt_a.u_cis,2) IN (SELECT uzaverka+id_kas FROM sk_sez_)
ENDIF 
IF ATC( "D_GREENFACT", m.gchom_path) > 0
    SELECT sk_plt_
    REPLACE FOR ATC( "hotove", druh_pl) > 0 druh_pl WITH "Hotove  "
ENDIF 

DO pozavirej WITH "sk_plt, sk_plt_a"

*  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *
had.increment("sk_fin")
IF m.slovenskaverzia
	SELECT id_kas, u_cis, autor, text_sleva, text_link, uzaverka, $0+cena_celk as cena_celk, $0+sleva_abs as sleva_abs, $0+cena_slev as cena_slev, ;
		u_cas, pocet_h, stul, cas_atrib, mutace, u_open, uzcis, ducis, id_limit, lim_datum, pohladavka, round50, id_slave WHERE uzaverka+id_kas IN (SELECT uzaverka+id_kas FROM sk_sez_) ;
		FROM sk_fin INTO CURSOR sk_fin_ READWRITE 
ELSE
	SELECT id_kas, u_cis, autor, text_sleva, text_link, uzaverka, $0+cena_celk as cena_celk, $0+sleva_abs as sleva_abs, $0+cena_slev as cena_slev, ;
		u_cas, pocet_h, stul, cas_atrib, mutace, u_open, uzcis, "XXXX" as ducis, id_limit, lim_datum, .f. as pohladavka, 00.00 as round50, id_slave WHERE uzaverka+id_kas IN (SELECT uzaverka+id_kas FROM sk_sez_) ;
		FROM sk_fin INTO CURSOR sk_fin_ READWRITE 
ENDIF

IF m.ajarchiv
	had.increment("append sk_fin_a")
	IF m.slovenskaverzia
		INSERT INTO sk_fin_ (id_kas, u_cis, autor, text_sleva, text_link, uzaverka, cena_celk, sleva_abs, cena_slev, ;
			u_cas, pocet_h, stul, cas_atrib, mutace, u_open, uzcis, ducis, id_limit, lim_datum, pohladavka, round50, id_slave) ;
			SELECT id_kas, u_cis, autor, text_sleva, text_link, uzaverka, cena_celk, sleva_abs, cena_slev, ;
				u_cas, pocet_h, stul, cas_atrib, mutace, u_open, SPACE(14) AS uzcis, ducis, id_limit, lim_datum, pohladavka, round50, id_slave WHERE uzaverka+id_kas IN (SELECT uzaverka+id_kas FROM sk_sez_) ;
				FROM sk_fin_a 
	ELSE
		INSERT INTO sk_fin_ (id_kas, u_cis, autor, text_sleva, text_link, uzaverka, cena_celk, sleva_abs, cena_slev, ;
			u_cas, pocet_h, stul, cas_atrib, mutace, u_open, uzcis, ducis, id_limit, lim_datum, pohladavka, round50, id_slave) ;
			SELECT id_kas, u_cis, autor, text_sleva, text_link, uzaverka, cena_celk, sleva_abs, cena_slev, ;
				u_cas, pocet_h, stul, cas_atrib, mutace, u_open, SPACE(14) AS uzcis, "XXXX", id_limit, lim_datum, .f. as pohladavka, 00.00 as round50, id_slave WHERE uzaverka+id_kas IN (SELECT uzaverka+id_kas FROM sk_sez_) ;
				FROM sk_fin_a 
	ENDIF
ENDIF 
SELECT sk_fin_
INDEX on uzaverka+u_cis TAG uzuc
IF m.slovenskaverzia
	IF m.hotel_krajina="CZE" AND m.issql
		UPDATE sk_fin_ SET round50=sleva_abs, cena_slev=cena_slev-sleva_abs WHERE ABS(sleva_abs)<.5 AND sleva_abs<>0
		UPDATE sk_fin_ SET sleva_abs=0 WHERE ABS(sleva_abs)<.5 AND sleva_abs<>0
	ENDIF 
ENDIF

SELECT sk_plt_
INDEX on uzaverka+u_cis TAG uzuc

SELECT sk_pol_kompez
IF RECCOUNT()>0
	SELECT * FROM sk_fin_ INTO CURSOR sk_fin_kompez WHERE ((uzaverka+u_cis) IN (SELECT DISTINCT uzaverka+u_cis as ucet FROM sk_pol_kompez)) READWRITE 
	INDEX on uzaverka+u_cis TAG uzuc
	REPLACE ALL cena_celk WITH 0, cena_slev WITH 0, sleva_abs WITH 0
	SELECT sk_pol_kompez
	SCAN ALL 
		m.uzaverka=sk_pol_kompez.uzaverka
		m.u_cis=sk_pol_kompez.u_cis
		m.dan=sk_pol_kompez.dan
		IF sk_pol_kompez.prachy<>0
			SELECT sk_fin_kompez
			IF SEEK(m.uzaverka+m.u_cis)
				REPLACE cena_celk WITH cena_celk + sk_pol_kompez.prachy, cena_slev WITH cena_slev + sk_pol_kompez.prachy
			ENDIF 
		ENDIF
		SELECT sk_pol_kompez 
	ENDSCAN 
	SELECT sk_pol_kompez
	REPLACE ALL u_cis WITH LEFT(u_cis,2)+PADL(ALLTRIM(STR(99999-VAL(RIGHT(u_cis,5)),5,0)),5,"0")
	SELECT sk_pol_
	APPEND FROM DBF("sk_pol_kompez")
ENDIF 

SELECT sk_plt_kompez
IF RECCOUNT()>0
	*** Pospocitavam kompenzacie po uctoch. Ak ide o kompenzaciu zlavou, musi byt suma kompenzacii v sk_plt=0. Pri kompenzacii polozkou to bude nenulova suma. Nenulove musia byt osetrene v sk_pol, preto ich ignorujem.
	SELECT uzaverka, u_cis, SUM(cena_pl) as cena_pl FROM sk_plt_kompez GROUP BY uzaverka, u_cis INTO CURSOR plt_kompez READWRITE 
	SCAN FOR cena_pl=0
		m.uzaverka=plt_kompez.uzaverka
		m.u_cis=plt_kompez.u_cis
		m.cena_pl=plt_kompez.cena_pl
		INSERT INTO sk_plt_ (u_cis, uzaverka, druh_pl, mena, cena_pl, tip, cena_mena) select u_cis, uzaverka, druh_pl, mena, cena_pl, tip, cena_mena FROM sk_plt_kompez WHERE uzaverka=m.uzaverka AND u_cis=m.u_cis
		IF m.issql
			UPDATE sk_plt_ set kompez=.f. WHERE uzaverka=m.uzaverka AND u_cis=m.u_cis
		ENDIF 
		DELETE FROM sk_plt_kompez WHERE uzaverka=m.uzaverka AND u_cis=m.u_cis
	ENDSCAN 
	USE IN plt_kompez
	SELECT sk_plt_kompez
	REPLACE ALL u_cis WITH LEFT(u_cis,2)+PADL(ALLTRIM(STR(99999-VAL(RIGHT(u_cis,5)),5,0)),5,"0")
	SELECT sk_plt_
	APPEND FROM DBF("sk_plt_kompez")
ENDIF 
IF USED("sk_fin_kompez")
	SELECT sk_fin_kompez
	REPLACE ALL u_cis WITH LEFT(u_cis,2)+PADL(ALLTRIM(STR(99999-VAL(RIGHT(u_cis,5)),5,0)),5,"0")
	SELECT sk_fin_
	APPEND FROM DBF("sk_fin_kompez")
ENDIF 
DO pozavirej WITH "sk_fin, sk_fin_a"
DO pozavirej WITH "sk_fin_kompez,sk_pol_kompez,sk_plt_kompez"
	
*  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *
had.increment("sk_txt")
SELECT u_cis, txt, jmeno, adresa1, adresa2, adresa3, schvalil, akce, dic, cislo, hucet, hjmeno, hpokoj, hporad, hpref, id_kas, uzaverka, hid, icdph, ico, poradi, "" as karta WHERE uzaverka+id_kas IN (SELECT uzaverka+id_kas FROM sk_sez_) ;
	FROM sk_txt INTO CURSOR sk_txt_ READWRITE
IF m.ajarchiv
	had.increment("append sk_txt_a")
	INSERT INTO sk_txt_ (u_cis, txt, jmeno, adresa1, adresa2, adresa3, schvalil, akce, dic, cislo, hucet, hjmeno, hpokoj, hporad, hpref, id_kas, uzaverka, hid, icdph, ico, poradi, karta);
		SELECT u_cis, txt, jmeno, adresa1, adresa2, adresa3, schvalil, akce, dic, cislo, hucet, hjmeno, hpokoj, hporad, hpref, id_kas, uzaverka, hid, icdph, ico, poradi,"" FROM sk_txt_a;
		WHERE uzaverka+id_kas IN (SELECT uzaverka+id_kas FROM sk_sez_)
ENDIF 
*  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *
DO pozavirej WITH "sk_txt, sk_txt_a"
RELEASE had


*********************************************************************************************
**     sk_new2     sk_new2     sk_new2     sk_new2     sk_new2     sk_new2     sk_new2     **
*********************************************************************************************
PROCEDURE sk_new2

had = CREATEOBJECT("had", "sk_new2" )
had.SHOW()
had.set_max(33)

*     *     *     *     *     *     *     *     *     *     *     *     *     *     *     *     *
CREATE CURSOR  sk__sez (c_kas I, id_kas C(2), c_stredisk I, id_man C(2), zkr_kas C(30), zkr_man C(30), ;
       uzaverka C(17), c_uzaverka I, c_uzav_kas I, autor C(3), odecteno L, uz_cislo N(4), uctexport L, uct_den D, c_mena N(1), priznak C(20), lastuzav c(17), id_prev c(2), prevadzka c(30))

* uct_den       datum, k nemuz ma byt uzaverka uctovana (typicky pri uzavreni kasy po pulnoci to je vcerejsi datum)
* uzaverka      oznaceni uzaverky ve formatu RRRRMMDD<znak>HHMM
*               <znak> rozlisuje, zda uzaverka vznikla na kase (mezera), odpoctem davky (hvezdicka - "*"), z Ondrova hotelaku (kriminal - "#")
* c_uzaverka a c_uzav_kas: klic do ostatnich SK. dva sloupce jsou pro vzdalene kasy - kasa vyplni svoje cislo, 
*               program sk_in to do centralnich dat precisluje, aby na centrale byla zajistena unikatnost

*CREATE TABLE (m.gcpri_path + "sk__pol") ( 
CREATE CURSOR sk__pol ( ;
       c_uzaverka I, ucet I, c_stredisk I, c_karty I, prachy Y, prachy_puv Y, dan Y, dan_sazba N(6,3), ;
       mnozstvi Y, cen_hlad C(2), k_odpoctu C(1), poradi N(3), dotacia C(1), dil_porce N(1), id_fmenu c(7), id_kas C(2), id_man C(2), sleva_pol N(1), pslev c(1) )

* prachy        trzba (vc. DPH) za polozku po sleve
* prachy_puv    trzba (vc. DPH) za polozku pred slevou
* mnozstvi      prodane mnozstvi
* dan_sazba     procentni sazba dph; 9% = 9.0, 19% = 19.0           TAK TOHLE TED NEPLATI, ABYCHOM SE PRI KONVERZICH VESLI DO LIMITU 2GB SOUBORU - !!DAN_SAZBA=KOEF!!
* cen_hlad      cenova hladina
* k_odpoctu 
* poradi        poradi radku pro opakovany tisk uctu
* dil_porce     pro rozliseni polovicni, tretinove, ctvrtinove porce
* sleva_pol a pslev prikladam, aby som mal rovnaku strukturu s sql datami
CREATE CURSOR sk__plt ( ;
       c_uzaverka I, ucet I, druh_pl C(10), cena_pl N(12,2), tip N(10,2), mena C(3), cena_mena N(12,2), poradi N(2), text C(30))
* poradi se vyplnuje pri vice platbach za 1 ucet; podle toho jsou pak svazany radky v sktxt

CREATE CURSOR sk__vklvy (;
	ucet I, c_uzaverka I, nazev c(8), popis c(50), datum T, operacia I, id_kas c(2), autor c(3), mena c(3), ciastka N(12,2))


CREATE CURSOR sk__fin ( ;
       c_uzaverka I, ucet I, autor C(3), ;
       u_cas C(8), u_minut N(4), mutace C(1), cas_atrib C(1), pocet_h I, stul N(6,1), ;
       text_sleva C(16), text_link C(25), ducis C(4), lim_datum D, id_limit I, round50 N(4,2), pohladavka L, uzcis C(13), id_slave C(2))

* u_cas         cas vystaveni uctu, DD HH:MM
* u_minut       stari uctu v minutach - doba od objednani 1. polozky po uzavreni

CREATE CURSOR sk__txt ( ;
       c_uzaverka I, ucet I, poradi N(2), ;
       txt         c(35)  NOCP,;
       jmeno       c(35)  NOCP, adresa1 c(35), adresa2 c(35), adresa3 c(35), ;
       schvalil    c(35), akce c(35), dic c(15), cislo c(15), ;
       hucet       c(15), hpokoj c(6), hjmeno c(15), hporad c(2), hpref c(15), hid c(12), icdph c(20), ico c(15), karta c(250))


* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * 
SELECT disti uzaverka as uzav_old, id_kas, id_man, autor, uz_cislo, c_mena, uct_den, odecteno as odect_char, lastuzav, ;
       STRED_SPACEHOLDER as c_kas, STRED_SPACEHOLDER as c_stredisk, SPACE(30) AS zkr_kas, SPACE(30) AS zkr_man, ;
       UZAV_SPACEHOLDER as c_uzaverka, PADR(uzaverka, 19) as uzaverka, .F. as odecteno, priznak, SPACE(2) as id_prev, SPACE(30) as prevadzka ;
       FROM sk_sez_ INTO CURSOR sez READWRITE 

UPDATE sez SET c_kas      = fd.c_stredisk, zkr_kas = fd.id_zkratka, id_prev=fd.id_prev, prevadzka=fd.prevadzka from fd WHERE sez.id_kas=fd.id
UPDATE sez SET c_stredisk = fd.c_stredisk, zkr_man = fd.id_zkratka from fd WHERE sez.id_man=fd.id
SELECT sez
REPLACE ALL odecteno WITH .not. EMPTY(odect_char), uzaverka WITH uzav_old2new (uzav_old)

INDEX on uzav_old + id_kas TAG uz_kas   && generovani cisel uzaverek
uz_kas = "%^&$#@"
c_uzaverka = 1
SCAN
    IF sez.uzav_old + sez.id_kas # m.uz_kas
        uz_kas = sez.uzav_old + sez.id_kas
        c_uzaverka = m.c_uzaverka + 1
    ENDIF 
    GATHER FIELDS c_uzaverka MEMVAR 
ENDSCAN 

*     *     *     *     *     *     *     *     *     *     *     *     *     *     *     *     *
had.INCREMENT("plt")

SELECT uzaverka, u_cis, druh_pl, cena_pl, tip, mena, cena_mena, LEFT(u_cis, 2) as id_kas, ;
       SPACE(3) as autor , 0000 as poradi, SPACE(30) as text ;
       FROM sk_plt_ INTO CURSOR plt READWRITE 

*     *     *     *     *     *     *     *     *     *     *     *     *     *     *     *     *
had.INCREMENT("pol")

SELECT id_kas, id_man, uzaverka, u_cis, c_karty, prachy as prachy_puv, ;
       $0 as prachy, $0 as dan, mnozstvi, cen_hlad, k_odpoctu, dan as dan_sazba, dil_porce, ;
       STRED_SPACEHOLDER as c_stredisk, 000 as poradi, dotacia, id_fmenu, kompez, procentosl ;
       FROM sk_pol_ INTO CURSOR pol READWRITE 
REPLACE FOR cen_hlad = "0" cen_hlad WITH "1"    && kasy eurowin davaji hladinu 0

IF RECCOUNT("pol") > 0

    IF m.slovenskaverzia
        SELECT pol
        REPLACE ALL prachy WITH prachy_puv - procentosl * .01 * prachy_puv
        SELECT uzaverka, u_cis, SUM(prachy) as prachy_pol, $0 as prachy_plt, $0 as prachy_bez, $0 as round50 ;
               WHERE NOT EMPTY(k_odpoctu) GROUP BY uzaverka, u_cis ;
               FROM pol INTO CURSOR pol_sum READWRITE 
        UPDATE pol_sum SET round50=sk_fin_.round50 from sk_fin_ WHERE  pol_sum.uzaverka=sk_fin_.uzaverka AND pol_sum.u_cis=sk_fin_.u_cis
               
        INSERT INTO pol_sum (uzaverka, u_cis, prachy_plt) SELECT uzaverka, u_cis, SUM(cena_pl) as prachy_plt GROUP BY uzaverka, u_cis FROM plt 

        SELECT uzaverka, u_cis, SUM(prachy_pol+round50) as prachy_pol, SUM(prachy_plt) as prachy_plt, SUM(prachy_bez) as prachy_bez, 001.000000000 as slev_koef ;
               GROUP BY uzaverka, u_cis FROM pol_sum INTO CURSOR pol_plt READWRITE 

        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 RELATION TO uzaverka + u_cis INTO pol_plt
        REPLACE ALL prachy WITH ROUND(prachy_puv * pol_plt.slev_koef, 2), dan WITH ROUND(prachy * (dan_sazba-1) / dan_sazba, 2)

    ELSE

        SELECT pol
        REPLACE ALL prachy WITH prachy_puv - procentosl * .01 * prachy_puv

    	SELECT uzaverka, u_cis, SUM(prachy) as prachy_pol, $0 as prachy_plt ;
    	       WHERE NOT EMPTY(k_odpoctu) GROUP BY uzaverka, u_cis ;
    	       FROM pol INTO CURSOR pol_sum READWRITE 
    	       
    	INSERT INTO pol_sum (uzaverka, u_cis, prachy_plt) SELECT uzaverka, u_cis, SUM(cena_pl) GROUP BY uzaverka, u_cis FROM plt 

    	SELECT uzaverka, u_cis, SUM(prachy_pol) as prachy_pol, SUM(prachy_plt) as prachy_plt, 0 as prachy_bez, 001.000000000 as slev_koef ;
    	       GROUP BY uzaverka, u_cis FROM pol_sum INTO CURSOR pol_plt READWRITE 

    	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 RELATION TO uzaverka + u_cis INTO pol_plt
    	REPLACE ALL prachy WITH ROUND(prachy * pol_plt.slev_koef, 2), dan WITH ROUND(prachy * (dan_sazba-1) / dan_sazba, 2)

    ENDIF


	SELECT pol
	UPDATE pol SET c_stredisk=fd.c_stredisk from fd WHERE pol.id_man=fd.id
	REPLACE FOR .not. EMPTY(k_odpoctu) poradi WITH ASC(k_odpoctu) - 40, k_odpoctu WITH "*"

	****  korekce zaokrouhlovacich chyb pri vypoctu ceny po sleve  ****
	had.INCREMENT("pol zaokr")
	SELECT pol
	DELETE TAG ALL 

	INDEX on uzaverka + u_cis TAG ouz_ucet FOR (.NOT. EMPTY(k_odpoctu)) .and. (.NOT. EMPTY(prachy)) .and. EMPTY(kompez)

	SELECT uzaverka, u_cis, SUM(prachy) as prachy_pol, 000.00 as rozdil ;
	       WHERE NOT EMPTY(k_odpoctu) GROUP BY 1,2 FROM pol INTO CURSOR sum_pol READWRITE 
    UPDATE sum_pol SET prachy_pol=prachy_pol+sk_fin_.round50 from sk_fin_ WHERE  sum_pol.uzaverka=sk_fin_.uzaverka AND sum_pol.u_cis=sk_fin_.u_cis
	SET RELATION TO uzaverka + u_cis INTO pol_plt
	
	REPLACE ALL rozdil WITH pol_plt.prachy_plt  + pol_plt.prachy_bez - sum_pol.prachy_pol

	DELETE FOR rozdil = 0
	SET RELATION TO uzaverka + u_cis INTO pol
	REPLACE ALL pol.prachy WITH pol.prachy + sum_pol.rozdil

	****  korekce zaokrouhlovacich chyb DPH  ****
	had.INCREMENT("pol zaokr dph")
	SELECT pol
	DELETE TAG ALL 
	INDEX on uzaverka + u_cis + STR(dan_sazba, 6,3) TAG ouz_uc_dan FOR (.NOT. EMPTY(k_odpoctu)) .and. (.NOT. EMPTY(prachy))

	SELECT uzaverka, u_cis, dan_sazba, SUM(prachy) as prachy, SUM(dan) as dan_pol, 000000000.00 as dan, 000.00 as rozdil ;
	       WHERE NOT EMPTY(k_odpoctu) GROUP BY 1,2,3 FROM pol INTO CURSOR sum_dph READWRITE 

    REPLACE ALL dan WITH ROUND(prachy * (dan_sazba-1) / dan_sazba, 2), rozdil WITH dan - dan_pol
	SELECT sum_dph
	DELETE FOR rozdil = 0
	SET RELATION TO uzaverka + u_cis + STR(dan_sazba, 6,3) INTO pol
	REPLACE FOR .not. EMPTY(pol.u_cis) pol.dan WITH pol.dan + sum_dph.rozdil, u_cis WITH "ZZZZZZZ"

	DO pozavirej WITH "pol_plt, pol_sum, sum_pol, sum_dph"

	SELECT pol
	SET ORDER TO 

ENDIF 

*    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    
had.INCREMENT("fin")
SELECT id_kas, uzaverka, u_cis, mutace, cas_atrib, autor, ;
       pocet_h, text_sleva, text_link, stul, RIGHT(u_cas, 8) as u_cas, u_open, ;
       uzcis, SPACE(4) as ducis, 0000 as u_minut, id_limit, lim_datum, round50, pohladavka, id_slave ;
       FROM sk_fin_ INTO CURSOR fin READWRITE 

REPLACE FOR AT(" ", u_cas) + AT(":", u_cas) = 0 u_cas WITH LEFT(u_cas, 2) + " " + SUBSTR(u_cas, 3,2) + ":" + SUBSTR(u_cas, 5,2) && oprava ceskeho formatu casu vcetne vterin

IF RECCOUNT("fin")>0
	REPLACE FOR EMPTY(u_open) u_open WITH RIGHT(u_cas, 5)
	REPLACE ALL u_minut WITH MOD( (VAL(SUBST(u_cas,4,2)) * 60 + VAL(SUBST(u_cas,7,2))) - (VAL(LEFT(u_open,2)) * 60 + VAL(RIGHT(u_open,2))) , 24*60)
	REPLACE FOR u_open = "DAVKA" text_link WITH text_sleva, u_minut WITH 0
ENDIF 

*    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    
had.INCREMENT("txt")
SELECT id_kas, uzaverka, u_cis, ;
	poradi, txt, jmeno, adresa1, adresa2, adresa3, schvalil, akce, dic, cislo, ;
	hucet, hpokoj, hjmeno, hporad, hpref, hid, icdph, ico, karta ;
	FROM sk_txt_ INTO CURSOR txt READWRITE 
INDEX on uzaverka + u_cis TAG uz_ucet

*    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    
had.INCREMENT("plt")
SELECT plt
IF RECCOUNT("plt")>0
	INDEX on uzaverka + u_cis + STR(9999999999 - ABS(cena_pl),12) TAG uz_ucet_p
	o_uz_ucet = "(*&*&^#$%"
	SCAN
	    IF uzaverka + u_cis # m.o_uz_ucet
	         m.o_uz_ucet = uzaverka + u_cis
	         m.poradi = 1
	    ELSE
	         m.poradi = m.poradi + 1
	    ENDIF 
		REPLACE poradi WITH m.poradi
	ENDSCAN 
	SET RELATION TO uzaverka + u_cis INTO txt
	*****************************************************************************
	*****    tady by se musime dohodnout, jak prevzit informace z sk_txt    *****
	*****************************************************************************
	REPLACE ALL plt.text WITH IIF(EMPTY(txt.hpokoj), ALLTRIM(txt.txt), ALLTRIM(txt.hpokoj) + " / ") + ALLTRIM(txt.hjmeno)
ENDIF 

had.INCREMENT("append SEZ")
INSERT INTO sk__sez (c_kas, id_kas, c_stredisk, id_man,  uzaverka, c_uzaverka, autor, odecteno, uz_cislo, uct_den, c_mena, zkr_kas, zkr_man, priznak, lastuzav);
              SELECT c_kas, id_kas, c_stredisk, id_man,  uzaverka, c_uzaverka, autor, odecteno, uz_cislo, uct_den, c_mena, zkr_kas, zkr_man, priznak, lastuzav FROM sez
*USE IN sez

SELECT DISTINCT id_kas, c_kas, c_uzaverka, uzav_old, uzaverka, uz_cislo ;
       FROM sez INTO CURSOR sez_1radek READWRITE 
INDEX on uzav_old + id_kas TAG uzo_kas

had.INCREMENT("append POL")
*!*    INSERT INTO sk__pol (c_uzaverka, ucet, c_stredisk, c_karty, prachy, prachy_puv, dan, dan_sazba, mnozstvi, cen_hlad, k_odpoctu, poradi, dotacia, dil_porce, id_fmenu);
*!*        SELECT c_uzaverka, ucet, c_stredisk, c_karty, prachy, prachy_puv, dan, dan_sazba, mnozstvi, cen_hlad, k_odpoctu, poradi, dotacia, dil_porce, id_fmenu FROM pol
INSERT INTO sk__pol (ucet,        c_stredisk, c_karty, prachy, prachy_puv, dan, dan_sazba, mnozstvi, cen_hlad, k_odpoctu, poradi, dotacia, dil_porce, id_fmenu,     c_uzaverka,     id_kas,     id_man ) ;
       SELECT precisluj_ucty(u_cis), pol.c_stredisk, c_karty, prachy, prachy_puv, dan, dan_sazba, mnozstvi, cen_hlad, k_odpoctu, poradi, dotacia, dil_porce, id_fmenu, sez.c_uzaverka, pol.id_kas, pol.id_man ;
           FROM pol INNER JOIN sez_1radek sez ON (sez.uzav_old + sez.id_kas)==(pol.uzaverka + pol.id_kas)
USE IN pol

had.INCREMENT("append PLT")
INSERT INTO sk__plt (ucet,        druh_pl, cena_pl, tip, mena, cena_mena, poradi, text,     c_uzaverka ) ;
	SELECT precisluj_ucty(u_cis), druh_pl, cena_pl, tip, mena, cena_mena, poradi, text, sez.c_uzaverka ;
    FROM plt INNER JOIN sez_1radek sez ON (sez.uzav_old + sez.id_kas)==(plt.uzaverka + plt.id_kas)
USE IN plt

had.INCREMENT("append VKLVY")
INSERT INTO sk__vklvy (	ucet, c_uzaverka, nazev, popis, datum, operacia, id_kas, autor, mena, ciastka) ;
	SELECT precisluj_ucty(u_cis), sez.c_uzaverka, nazev, popis, datum, operacia, sk_vklvy_.id_kas, autor, mena, ciastka;
    FROM sk_vklvy_ INNER JOIN sez_1radek sez ON (sez.uzav_old + sez.id_kas)==(sk_vklvy_.uzaverka + sk_vklvy_.id_kas)
USE IN sk_vklvy_

had.INCREMENT("append FIN")
INSERT INTO sk__fin (ucet,            autor, u_cas, u_minut, mutace, cas_atrib, pocet_h, stul, text_sleva, text_link, uzcis, ducis, lim_datum, id_limit, round50, pohladavka, c_uzaverka, id_slave);
	SELECT precisluj_ucty(u_cis), fin.autor, u_cas, u_minut, mutace, cas_atrib, pocet_h, stul, text_sleva, text_link, uzcis, "XXXX", lim_datum, id_limit, round50, pohladavka, sez.c_uzaverka, id_slave ;
           WHERE !ISNULL(fin.lim_datum) FROM fin INNER JOIN sez_1radek sez ON (sez.uzav_old + sez.id_kas)==(fin.uzaverka + fin.id_kas)

INSERT INTO sk__fin (ucet,            autor, u_cas, u_minut, mutace, cas_atrib, pocet_h, stul, text_sleva, text_link, uzcis, ducis, id_limit, round50, pohladavka, c_uzaverka, id_slave);
	SELECT precisluj_ucty(u_cis), fin.autor, u_cas, u_minut, mutace, cas_atrib, pocet_h, stul, text_sleva, text_link, uzcis, "XXXX", id_limit, round50, pohladavka, sez.c_uzaverka, id_slave ;
           WHERE ISNULL(fin.lim_datum) FROM fin INNER JOIN sez_1radek sez ON (sez.uzav_old + sez.id_kas)==(fin.uzaverka + fin.id_kas)
USE IN fin

had.INCREMENT("append TXT")
INSERT INTO sk__txt (ucet,        poradi, txt, jmeno, adresa1, adresa2, adresa3, schvalil, akce, dic, cislo, hucet, hpokoj, hjmeno, hporad, hpref, hid, icdph, ico,     c_uzaverka, karta );
	SELECT precisluj_ucty(u_cis), poradi, txt, jmeno, adresa1, adresa2, adresa3, schvalil, akce, dic, cislo, hucet, hpokoj, hjmeno, hporad, hpref, hid, icdph, ico, sez.c_uzaverka, karta ;
           FROM txt INNER JOIN sez_1radek sez ON (sez.uzav_old + sez.id_kas)==(txt.uzaverka + txt.id_kas)
USE IN txt
RELEASE had



*************************************************************************************************
FUNCTION uzav_old2new (o_uzav)

*** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** 
*** tohle je asi jediny misto, kde se algoritmus lisi pro ceska a slovenska data ZACATEK
IF m.slovenskaverzia OR m.issql
	IF LEN(m.o_uzav)=12
		m.new_uzav="20"+m.o_uzav
	ELSE 
		new_uzav = LEFT(m.o_uzav, 14)
	ENDIF 
ELSE
	DO CASE 
		CASE LEFT(m.o_uzav,1) = "9"
			new_uzav = "199" + SUBSTR(m.o_uzav, 2,8) + ":" + SUBSTR(m.o_uzav, 11,2)
		CASE LEFT(m.o_uzav,1) = "O"
			new_uzav = "200" + SUBSTR(m.o_uzav, 2,8) + ":" + SUBSTR(m.o_uzav, 11,2)
		OTHERWISE 
			new_uzav = "20"  + SUBSTR(m.o_uzav, 1,9) + ":" + SUBSTR(m.o_uzav, 11,2)
	ENDCASE 
ENDIF 
*** tohle je asi jediny misto, kde se algoritmus lisi pro ceska a slovenska data  KONEC
*** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** 

DO CASE 
	CASE "*" $ m.o_uzav AND LEN(m.o_uzav)>=12
*		RETURN SUBSTR(m.new_uzav, 1,9) + "DD*" + SUBSTR(m.new_uzav, 13,2)    zruseno, aby fungovalo rozpeti (vazba na doklady.pz2)
		RETURN SUBSTR(m.new_uzav, 1,11) + "*" + SUBSTR(m.new_uzav, 13,2)
	CASE "#" $ m.o_uzav AND LEN(m.o_uzav)>=12 
		RETURN SUBSTR(m.new_uzav, 1,11) + "#" + SUBSTR(m.new_uzav, 13,2)
	OTHERWISE
		RETURN m.new_uzav
ENDCASE 


*************************************************************************************************
PROCEDURE sk_new3
*************************************************************************************************
PRIVATE m.hladajuzaverkaod, m.hladajuzaverkado
had = createobject("had", "sk_new1")
had.show()

**** puvodne byla podminka "WHERE ukazat", tim byly zapocitany i odfiltrovane uzaverky
SELECT MIN(uzaverka) as uzaverka FROM sk_sez INTO CURSOR minuz
IF !USED("sk_sez_a")
	ajarchiv=.f.
ELSE 
	IF RECCOUNT("minuz")>0
		IF minuz.uzaverka>STRTRAN(m.minuz,"O","0")
			ajarchiv=.t.
		ELSE
			ajarchiv=.f.
		ENDIF 
	ELSE
		ajarchiv=.t.
	ENDIF 
ENDIF 
USE IN minuz
SELECT *, .f. as archiv, SPACE(30) as zkr_kas, SPACE(2) as id_prev, SPACE(30) as prevadzka WHERE uzaverka + id_kas IN (SELECT uzaverka + id_kas FROM uzaverky WHERE ukazat .and. lbl) ;
       FROM sk_sez INTO CURSOR sk__sez READWRITE ORDER BY  uzaverka
IF m.ajarchiv
	INSERT INTO sk__sez (id_kas, id_man, uzaverka, autor, uz_cislo, priznak, c_mena, uct_den, lastuzav, c_stredisk, c_kas, c_uzaverka, archiv) ;
		SELECT id_kas, id_man, uzaverka, autor, uz_cislo, priznak, c_mena, uct_den, lastuzav, c_stredisk, c_kas, c_uzaverka,.t. as archiv WHERE uzaverka + id_kas IN (SELECT uzaverka + id_kas FROM uzaverky WHERE ukazat .and. lbl) ;
		FROM sk_sez_a order by uzaverka 
ENDIF 
REPLACE c_mena WITH m.giakt_mena FOR EMPTY(c_mena)
REPLACE ALL priznak WITH ""

UPDATE sk__sez SET priznak = "NENENENENE" ;
	WHERE uzaverka + id_kas IN (SELECT uzaverka + id_kas FROM uzaverky WHERE lbl = .F.)

SELECT sk__sez
DO pozavirej WITH "fin, pol, plt, txt, fina, pola, plta, txta"

	had.set_max(7)
lsql="SELECT c_uzaverka, ucet, autor, u_cas, u_minut, mutace, cas_atrib, pocet_h, stul, text_sleva, text_link, ducis, lim_datum, id_limit, round50, pohladavka, id_slave, u_datetime FROM food600.sk__fin WHERE c_data=?m.cdata and c_uzaverka IN "
lsql=m.lsql+"(SELECT DISTINCT c_uzaverka from food600.sk__sez WHERE uct_den>=?m.uctden1 and uct_den<=?m.uctden2 and c_data=?m.cdata)"
sql_select(lsql,'fin',"c_uzaverka I, ucet I, autor c(3), u_cas c(8), u_minut N(6), mutace c(1), cas_atrib c(1), pocet_h N(6), stul N(8,1), text_sleva c(16), text_link c(25), ducis C(4), lim_datum D, id_limit I, round50 N(6,2), pohladavka L, id_slave C(2), u_datetime T")
IF USED("fin")
	SELECT c_uzaverka, ucet, autor, u_cas, u_minut, mutace, cas_atrib, pocet_h, stul, text_sleva, text_link, ducis, lim_datum, id_limit, round50, pohladavka, id_slave, SPACE(14) as uzcis, u_datetime FROM fin INTO CURSOR sk_fin_ READWRITE
	had.increment("Financie")

	lsql="SELECT c_uzaverka, ucet, c_stredisk, c_karty, prachy, prachy_puv, dan, dan_sazba, mnozstvi, cen_hlad, k_odpoctu, poradi, dotacia, dil_porce, id_fmenu, kompez, sleva_pol, sleva_txt FROM food600.sk__pol WHERE c_data=?m.cdata and c_uzaverka IN "
	lsql=m.lsql+"(SELECT DISTINCT c_uzaverka from food600.sk__sez WHERE uct_den>=?m.uctden1 and uct_den<=?m.uctden2 and c_data=?m.cdata)"
	sql_select(lsql,"pol","c_uzaverka I, ucet I, c_stredisk I, c_karty I, prachy N(12,2), prachy_puv N(12,2), dan N(11,2), dan_sazba N(6,2), mnozstvi N(12,3), cen_hlad c(2), k_odpoctu c(1), poradi I, dotacia C(1), dil_porce N(3), id_fmenu C(7), kompez L, sleva_pol Y, pslev C(20)")
	IF USED("pol")
		IF m.slovenskaverzia
			SELECT c_uzaverka, ucet, c_stredisk, c_karty, prachy, prachy_puv, dan, dan_sazba, mnozstvi, cen_hlad, k_odpoctu, poradi, dotacia, dil_porce, id_fmenu, SPACE(2) as id_kas, SPACE(2) as id_man, sleva_pol, pslev FROM pol INTO CURSOR sk_pol_ READWRITE WHERE EMPTY(kompez)
		ELSE
			SELECT c_uzaverka, ucet, c_stredisk, c_karty, prachy, prachy_puv, dan, dan_sazba, mnozstvi, cen_hlad, k_odpoctu, poradi, dotacia, dil_porce, id_fmenu, SPACE(2) as id_kas, SPACE(2) as id_man, sleva_pol, pslev FROM pol INTO CURSOR sk_pol_ READWRITE WHERE EMPTY(kompez) AND k_odpoctu # "F"
		ENDIF 
		SELECT c_uzaverka, ucet, c_stredisk, c_karty, prachy, prachy_puv, dan, dan_sazba, mnozstvi, cen_hlad, k_odpoctu, poradi, dotacia, dil_porce, id_fmenu, SPACE(2) as id_kas, SPACE(2) as id_man, sleva_pol, pslev FROM pol INTO CURSOR sk_pol_kompez READWRITE WHERE !EMPTY(kompez)
		had.increment("Položky")
		m.lsql="SELECT c_uzaverka, ucet, druh_pl, cena_pl, tip, mena, cena_mena, poradi, text, kompez FROM food600.sk__plt WHERE c_data=?m.cdata and c_uzaverka IN "
		m.lsql=m.lsql+"(SELECT DISTINCT c_uzaverka from food600.sk__sez WHERE uct_den>=?m.uctden1 and uct_den<=?m.uctden2 and c_data=?m.cdata)"
		sql_select(lsql,"plt","c_uzaverka I, ucet I, druh_pl c(10), cena_pl N(14,2), tip N(12,2), mena C(3), cena_mena N(14,2), poradi N(4), text C(30), kompez L")
		IF USED("plt")
			SELECT c_uzaverka, ucet, druh_pl, cena_pl, tip, mena, cena_mena, poradi, text FROM plt INTO CURSOR sk_plt_ READWRITE WHERE EMPTY(kompez)
			SELECT c_uzaverka, ucet, druh_pl, cena_pl, tip, mena, cena_mena, poradi, text FROM plt INTO CURSOR sk_plt_kompez READWRITE WHERE !EMPTY(kompez)
			had.increment("Platby")
			m.lsql="SELECT c_uzaverka, ucet, poradi, txt, jmeno, adresa1, adresa2, adresa3, schvalil, akce, dic, cislo, hucet, hpokoj, hjmeno, hporad, hpref, hid, icdph, ico, transaction_metadata FROM food600.sk__txt WHERE c_data=?m.cdata and c_uzaverka IN "
			m.lsql=m.lsql+"(SELECT DISTINCT c_uzaverka from food600.sk__sez WHERE uct_den>=?m.uctden1 and uct_den<=?m.uctden2 and c_data=?m.cdata)"
			sql_select(lsql,"txt","c_uzaverka I,ucet I,poradi N(6),txt C(35),jmeno C(35),adresa1 C(35),adresa2 C(35),adresa3 C(35),schvalil C(35),akce C(35),dic C(15),cislo C(15),hucet C(15),hpokoj C(6),hjmeno C(15),hporad C(2),hpref C(15),hid C(12),icdph C(20),ico C(20),karta m")
			IF USED("txt")
				SELECT c_uzaverka, ucet, poradi, txt, jmeno, adresa1, adresa2, adresa3, schvalil, akce, dic, cislo, hucet, hpokoj, hjmeno, hporad, hpref, hid, icdph, ico, karta FROM txt INTO CURSOR sk_txt_ READWRITE
				had.increment("Texty")
				m.lsql="SELECT ucet, c_uzaverka, nazev, popis, datum, operacia, autor, mena, ciastka FROM food600.sk__vklvy WHERE c_data=?m.cdata and c_uzaverka IN "
				m.lsql=m.lsql+"(SELECT DISTINCT c_uzaverka from food600.sk__sez WHERE uct_den>=?m.uctden1 and uct_den<=?m.uctden2 and c_data=?m.cdata)"
				sql_select(lsql,"vklvy","ucet I, c_uzaverka I, nazev C(8), popis C(50), datum T, operacia I, autor C(3), mena C(3), ciastka N(12,2)")
				IF USED("vklvy")
					SELECT ucet, c_uzaverka, nazev, popis, datum, operacia, autor, mena, ciastka, SPACE(2) as id_kas FROM vklvy INTO CURSOR sk_vklvy_ READWRITE
					had.increment("Vklady/výbery")
					IF m.ajarchiv
						m.lsql="SELECT c_uzaverka, ucet, autor, u_cas, u_minut, mutace, cas_atrib, pocet_h, stul, text_sleva, text_link, ducis, lim_datum, id_limit, round50, pohladavka, id_slave, u_datetime FROM food600.sk__fin_a WHERE c_data=?m.cdata and c_uzaverka IN "
						m.lsql=m.lsql+"(SELECT DISTINCT c_uzaverka from food600.sk__sez_a WHERE uct_den>=?m.uctden1 and uct_den<=?m.uctden2 and c_data=?m.cdata)"
						sql_select(lsql,'fina',"c_uzaverka I, ucet I, autor c(3), u_cas c(8), u_minut N(6), mutace c(1), cas_atrib c(1), pocet_h N(6), stul N(8,1), text_sleva c(16), text_link c(25), ducis C(4), lim_datum D, id_limit I, round50 N(6,2), pohladavka L, id_slave c(2), u_datetime T")
						IF USED("fina")
							INSERT INTO sk_fin_ (c_uzaverka, ucet, autor, u_cas, u_minut, mutace, cas_atrib, pocet_h, stul, text_sleva, text_link, ducis, lim_datum, id_limit, round50, pohladavka, id_slave, u_datetime) SELECT c_uzaverka, ucet, autor, u_cas, u_minut, mutace, cas_atrib, pocet_h, stul, text_sleva, text_link, ducis, lim_datum, id_limit, round50, pohladavka, id_slave, u_datetime FROM fina
							had.increment("Financie - archív")
							m.lsql="SELECT c_uzaverka, ucet, c_stredisk, c_karty, prachy, prachy_puv, dan, dan_sazba, mnozstvi, cen_hlad, k_odpoctu, poradi, dotacia, dil_porce, id_fmenu, kompez, sleva_pol, sleva_txt FROM food600.sk__pol_a WHERE c_data=?m.cdata and c_uzaverka IN "
							m.lsql=m.lsql+"(SELECT DISTINCT c_uzaverka from food600.sk__sez_a WHERE uct_den>=?m.uctden1 and uct_den<=?m.uctden2 and c_data=?m.cdata)"
							sql_select(lsql,"pola","c_uzaverka I, ucet I, c_stredisk I, c_karty I, prachy N(12,2), prachy_puv N(12,2), dan N(11,2), dan_sazba N(6,2), mnozstvi N(12,3), cen_hlad c(2), k_odpoctu c(1), poradi I, dotacia C(1), dil_porce N(3), id_fmenu C(7), kompez L, sleva_pol Y, pslev C(20)")
							IF USED("pola")
								INSERT INTO sk_pol_ (c_uzaverka, ucet, c_stredisk, c_karty, prachy, prachy_puv, dan, dan_sazba, mnozstvi, cen_hlad, k_odpoctu, poradi, dotacia, dil_porce, id_fmenu, sleva_pol, pslev) SELECT c_uzaverka, ucet, c_stredisk, c_karty, prachy, prachy_puv, dan, dan_sazba, mnozstvi, cen_hlad, k_odpoctu, poradi, dotacia, dil_porce, id_fmenu, sleva_pol, pslev FROM pola WHERE EMPTY(kompez)
								INSERT INTO sk_pol_kompez (c_uzaverka, ucet, c_stredisk, c_karty, prachy, prachy_puv, dan, dan_sazba, mnozstvi, cen_hlad, k_odpoctu, poradi, dotacia, dil_porce, id_fmenu, sleva_pol, pslev) SELECT c_uzaverka, ucet, c_stredisk, c_karty, prachy, prachy_puv, dan, dan_sazba, mnozstvi, cen_hlad, k_odpoctu, poradi, dotacia, dil_porce, id_fmenu, sleva_pol, pslev FROM pola WHERE !EMPTY(kompez)
								had.increment("Položky - archív")
								m.lsql="SELECT c_uzaverka, ucet, druh_pl, cena_pl, tip, mena, cena_mena, poradi, text, kompez FROM food600.sk__plt_a WHERE c_data=?m.cdata and c_uzaverka IN "
								m.lsql=m.lsql+"(SELECT DISTINCT c_uzaverka from food600.sk__sez_a WHERE uct_den>=?m.uctden1 and uct_den<=?m.uctden2 and c_data=?m.cdata)"
								sql_select(lsql,"plta","c_uzaverka I, ucet I, druh_pl c(10), cena_pl N(14,2), tip N(12,2), mena C(3), cena_mena N(14,2), poradi N(4), text C(30), kompez L")
								IF USED("plta")
									INSERT INTO sk_plt_ (c_uzaverka, ucet, druh_pl, cena_pl, tip, mena, cena_mena, poradi, text) SELECT c_uzaverka, ucet, druh_pl, cena_pl, tip, mena, cena_mena, poradi, text FROM plta WHERE EMPTY(kompez)
									INSERT INTO sk_plt_kompez (c_uzaverka, ucet, druh_pl, cena_pl, tip, mena, cena_mena, poradi, text) SELECT c_uzaverka, ucet, druh_pl, cena_pl, tip, mena, cena_mena, poradi, text FROM plta WHERE !EMPTY(kompez)
									had.increment("Platby - archív")
									m.lsql="SELECT c_uzaverka, ucet, poradi, txt, jmeno, adresa1, adresa2, adresa3, schvalil, akce, dic, cislo, hucet, hpokoj, hjmeno, hporad, hpref, hid, icdph, ico, transaction_metadata as karta FROM food600.sk__txt_a WHERE c_data=?m.cdata and c_uzaverka IN "
									m.lsql=m.lsql+"(SELECT DISTINCT c_uzaverka from food600.sk__sez_a WHERE uct_den>=?m.uctden1 and uct_den<=?m.uctden2 and c_data=?m.cdata)"
									sql_select(lsql,"txta","c_uzaverka I,ucet I,poradi N(6),txt C(35),jmeno C(35),adresa1 C(35),adresa2 C(35),adresa3 C(35),schvalil C(35),akce C(35),dic C(15),cislo C(15),hucet C(15),hpokoj C(6),hjmeno C(15),hporad C(2),hpref C(15),hid C(12),icdph C(20),ico C(20),karta m")
									IF USED("txta")
										INSERT INTO sk_txt_ (c_uzaverka, ucet, poradi, txt, jmeno, adresa1, adresa2, adresa3, schvalil, akce, dic, cislo, hucet, hpokoj, hjmeno, hporad, hpref, hid, icdph, ico, karta) SELECT c_uzaverka, ucet, poradi, txt, jmeno, adresa1, adresa2, adresa3, schvalil, akce, dic, cislo, hucet, hpokoj, hjmeno, hporad, hpref, hid, icdph, ico, karta FROM txta
										had.increment("Texty - archív")
										m.lsql="SELECT ucet, c_uzaverka, nazev, popis, datum, operacia, autor, mena, ciastka FROM food600.sk__vklvy_a WHERE c_data=?m.cdata and c_uzaverka IN "
										m.lsql=m.lsql+"(SELECT DISTINCT c_uzaverka from food600.sk__sez WHERE uct_den>=?m.uctden1 and uct_den<=?m.uctden2 and c_data=?m.cdata)"
										sql_select(lsql,"vklvya","ucet I, c_uzaverka I, nazev C(8), popis C(50), datum T, operacia I, autor C(3), mena C(3), ciastka N(12,2)")
										IF USED("vklvya")
											INSERT INTO sk_vklvy_ (ucet, c_uzaverka, nazev, popis, datum, operacia, autor, mena, ciastka);
												SELECT ucet, c_uzaverka, nazev, popis, datum, operacia, autor, mena, ciastka FROM vklvya 
												had.increment("Vklady/výbery - archív")
										ELSE
											=MESSAGEBOX(jaz("prg_nepodarilo_sa_otvorit")+" "+"vklvya",16,"FoodMan")
										ENDIF 
									ELSE
										=MESSAGEBOX(jaz("prg_nepodarilo_sa_otvorit")+" "+"txta",16,"FoodMan")
									ENDIF 
								ELSE
									=MESSAGEBOX(jaz("prg_nepodarilo_sa_otvorit")+" "+"plta",16,"FoodMan")
								ENDIF 
							ELSE
								=MESSAGEBOX(jaz("prg_nepodarilo_sa_otvorit")+" "+"pola",16,"FoodMan")
							ENDIF 
						ELSE
							=MESSAGEBOX(jaz("prg_nepodarilo_sa_otvorit")+" "+"fina",16,"FoodMan")
						ENDIF 
					ENDIF 
				ELSE
					=MESSAGEBOX(jaz("prg_nepodarilo_sa_otvorit")+" "+"vklvy",16,"FoodMan")
				ENDIF 
			ELSE
				=MESSAGEBOX(jaz("prg_nepodarilo_sa_otvorit")+" "+"txt",16,"FoodMan")
			ENDIF 
		ELSE
			=MESSAGEBOX(jaz("prg_nepodarilo_sa_otvorit")+" "+"plt",16,"FoodMan")
		ENDIF 
	ELSE
		=MESSAGEBOX(jaz("prg_nepodarilo_sa_otvorit")+" "+"pol",16,"FoodMan")
	ENDIF 
ELSE
	=MESSAGEBOX(jaz("prg_nepodarilo_sa_otvorit")+" "+"fin",16,"FoodMan")
ENDIF 
IF USED("sk_fin_") AND USED("sk_plt_") AND USED("sk_pol_") AND USED("sk_txt_") AND USED("sk_vklvy_")
	UPDATE sk_pol_ set dil_porce=1 WHERE dil_porce=0 OR ISNULL(dil_porce)
***	UPDATE sk_pol_ set mnozstvi=mnozstvi/dil_porce WHERE dil_porce>1                   tohle zruseno, v mnozstvi je uz polovina nebo ctvrtina/tretina

	ajarchiv=.f.
	DO pozavirej WITH "fin, pol, plt, txt, fina, pola, plta, txta, vklvy, vklvya"
	IF USED("sk_pol_kompez")
		UPDATE sk_pol_kompez set dil_porce=1 WHERE dil_porce=0 OR ISNULL(dil_porce)
***		UPDATE sk_pol_kompez set mnozstvi=mnozstvi/dil_porce WHERE dil_porce>1                   tohle zruseno, v mnozstvi je uz polovina nebo ctvrtina/tretina
		SELECT sk_pol_kompez
		IF RECCOUNT()>0
			SELECT * FROM sk_fin_ INTO CURSOR sk_fin_kompez WHERE ((STR(c_uzaverka,10)+STR(ucet,10)) in (SELECT DISTINCT (STR(c_uzaverka,10)+STR(ucet,10)) as ucet FROM sk_pol_kompez)) READWRITE 
			SELECT sk_pol_kompez
			REPLACE ALL ucet WITH ucet+100000
			SELECT sk_pol_
			APPEND FROM DBF("sk_pol_kompez")
		ENDIF 
	ENDIF 
	IF USED("sk_plt_kompez")
		SELECT sk_plt_kompez
		IF RECCOUNT()>0
			*** Pospocitavam kompenzacie po uctoch. Ak ide o kompenzaciu zlavou, musi byt suma kompenzacii v sk_plt=0. Pri kompenzacii polozkou to bude nenulova suma. Nenulove musia byt osetrene v sk_pol, preto ich ignorujem.
			SELECT c_uzaverka, ucet, SUM(cena_pl) as cena_pl FROM sk_plt_kompez GROUP BY c_uzaverka, ucet INTO CURSOR plt_kompez READWRITE 
			SCAN FOR cena_pl=0
				m.c_uzaverka=plt_kompez.c_uzaverka
				m.ucet=plt_kompez.ucet
				m.cena_pl=plt_kompez.cena_pl
				INSERT INTO sk_plt_ select * FROM sk_plt_kompez WHERE c_uzaverka=m.c_uzaverka AND ucet=m.ucet
				DELETE FROM sk_plt_kompez WHERE c_uzaverka=m.c_uzaverka AND ucet=m.ucet
			ENDSCAN 
			USE IN plt_kompez

			IF !USED("sk_fin_kompez")
				SELECT * FROM sk_fin_ INTO CURSOR sk_fin_kompez WHERE ((STR(c_uzaverka,10)+STR(ucet,10)) in (SELECT DISTINCT (STR(c_uzaverka,10)+STR(ucet,10)) as ucet FROM sk_plt_kompez)) READWRITE 
			ELSE 
				SELECT * FROM sk_plt_kompez WHERE !((STR(c_uzaverka,10)+STR(ucet,10)) in (SELECT DISTINCT (STR(c_uzaverka,10)+STR(ucet,10)) as ucet FROM sk_fin_kompez)) INTO CURSOR plt_kompez
				INSERT INTO sk_fin_kompez SELECT * FROM sk_fin_ WHERE ((STR(c_uzaverka,10)+STR(ucet,10)) in (SELECT DISTINCT (STR(c_uzaverka,10)+STR(ucet,10)) as ucet FROM plt_kompez)) AND !((STR(c_uzaverka,10)+STR(ucet,10)) in (SELECT DISTINCT (STR(c_uzaverka,10)+STR(ucet,10)) as ucet FROM sk_fin_kompez))
				USE IN plt_kompez
			ENDIF 
			SELECT sk_plt_kompez
			REPLACE ALL ucet WITH ucet+100000
			SELECT sk_plt_
			APPEND FROM DBF("sk_plt_kompez")
		ENDIF 
	ENDIF 
	IF USED("sk_fin_kompez")
		SELECT sk_fin_kompez
		REPLACE ALL ucet WITH ucet+100000
		SELECT sk_fin_
		APPEND FROM DBF("sk_fin_kompez")
	ENDIF 
	DO pozavirej WITH "sk_fin_kompez,sk_pol_kompez,sk_plt_kompez"
	*  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *
	had.increment("sk_pol")
	IF USED("sk_pol_")
		SELECT * FROM sk_pol_ WHERE c_uzaverka IN (SELECT c_uzaverka FROM sk__sez) INTO CURSOR sk__pol READWRITE 
	ENDIF
	*  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *
	had.increment("sk_plt")
	IF USED("sk_plt_")
		SELECT * FROM sk_plt_ WHERE c_uzaverka IN (SELECT c_uzaverka FROM sk__sez) INTO CURSOR sk__plt READWRITE 
	ENDIF 
	*  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *
	had.increment("sk_vklvy")
	IF USED("sk_vklvy_")
		SELECT * FROM sk_vklvy_ WHERE c_uzaverka IN (SELECT c_uzaverka FROM sk__sez) INTO CURSOR sk__vklvy READWRITE 
	ENDIF 
	*  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *
	had.increment("sk_fin")
	IF USED("sk_fin_")
		SELECT * FROM sk_fin_ WHERE c_uzaverka IN (SELECT c_uzaverka FROM sk__sez) INTO CURSOR sk__fin READWRITE 
	ENDIF 
	*  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *
	had.increment("sk_txt")
	IF USED("sk_txt_")
		SELECT * FROM sk_txt_ WHERE c_uzaverka IN (SELECT c_uzaverka FROM sk__sez) INTO CURSOR sk__txt READWRITE 
	ENDIF 
	*  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *
	UPDATE sk__sez SET zkr_kas=fd.id_zkratka from fd WHERE sk__sez.c_kas=fd.c_stredisk
	TRY 
		UPDATE sk__sez SET id_prev=fd.id_prev, prevadzka=fd.prevadzka from fd WHERE sk__sez.c_kas=fd.c_stredisk
	CATCH
	ENDTRY 
	UPDATE sk__pol SET id_man=fd.id from fd WHERE sk__pol.c_stredisk=fd.c_stredisk
	UPDATE sk__pol SET id_kas=sk__sez.id_kas from sk__sez WHERE sk__pol.c_uzaverka=sk__sez.c_uzaverka
	UPDATE sk__pol SET dan_sazba=(1+dan_sazba/100) WHERE .t.
ENDIF 
RELEASE had


*************************************************************************************************
FUNCTION precisluj_ucty (cislo_uctu)
posledni_znak = RIGHT(cislo_uctu, 1)
IF m.posledni_znak > "9"
    RETURN VAL(STR((VAL(SUBSTR(m.cislo_uctu,3,1))+5)%10, 1) + SUBSTR(m.cislo_uctu,4,3) + CHR(ASC(m.posledni_znak)-17))
ELSE
    RETURN VAL(SUBSTR(m.cislo_uctu, 3))
ENDIF 

