*!*****************************************************************************
*!      Procedure: priprav_tm_sk
*!*****************************************************************************

had = CREATEOBJECT("had", "Priprav TM_SK")
had.set_max(11)
had.SHOW()

***********************  nahrada cisla pokladny cislem baraku  ***********************  zacatek
IF "sum" $ LOWER(gcbaraky_summary)
    had.INCREMENT("cislo baraku")
	SELECT ID, c_stredisk, id_zkratka, LEFT(stredskup,2) AS stredskup, c_stredisk AS o_c_stred, ID AS o_id ;
		FROM fd INTO CURSOR skupiny_stredisek READWRITE

	SELECT ID, c_stredisk, id_zkratka, LEFT(stredskup,2) AS stredskup ;
		WHERE ID = LEFT(stredskup,2) ;
		FROM skupiny_stredisek INTO CURSOR stredskup_uniq &&READWRITE
	SCAN FOR .NOT. EMPTY(stredskup)
		SCATTER MEMVAR
		SELECT skupiny_stredisek
		REPLACE FOR stredskup = m.stredskup ID WITH m.id, c_stredisk WITH m.c_stredisk, id_zkratka WITH m.id_zkratka
		SELECT stredskup_uniq
	ENDSCAN

	SELECT skupiny_stredisek
	INDEX ON o_c_stred TAG o_c_stred

	SELECT sk__sez
	SET RELATION TO c_kas INTO skupiny_stredisek
	REPLACE ALL c_kas WITH skupiny_stredisek.c_stredisk, id_kas WITH skupiny_stredisek.id, zkr_kas WITH skupiny_stredisek.id_zkratka

	DO pozavirej WITH "skupiny_stredisek, stredskup_uniq"
ENDIF
***********************  nahrada cisla pokladny cislem baraku  ************************   konec


SELECT c_stredisk, ID, id_zkratka WHERE UPPER(pgm_param) NOT LIKE "SLAVE%" FROM fd INTO CURSOR fp_str READWRITE
INSERT INTO fp_str (c_stredisk, ID, id_zkratka) VALUES (999999, "ZZ", "ZZ nechci ZZ")
INDEX ON id         TAG id         UNIQUE
INDEX ON c_stredisk TAG c_stredisk UNIQUE


***     ****     ****     ****   SEZ  SEZ  SEZ  SEZ  SEZ  SEZ  SEZ    ****     ****     ****     ****
had.INCREMENT("SEZ")
SELECT * FROM sk__sez INTO CURSOR tm_sk_sez READWRITE
INDEX ON c_uzaverka TAG c_uzaverka

SELECT DISTINCT c_kas, id_kas, zkr_kas, c_uzaverka, uzaverka, uct_den, c_mena, uz_cislo ;
       FROM tm_sk_sez INTO CURSOR tm_sk_sez_kas
INDEX ON uzaverka + id_kas TAG uz_kas


***     ****     ****     ****   FIN  FIN  FIN  FIN  FIN  FIN  FIN   ****     ****     ****     ****
had.INCREMENT("FIN")
IF FSIZE("u_datetime","sk__fin")>0
	SELECT sk__fin.c_uzaverka, TRANS(ucet, "@L 99999") AS u_cis, stul, cas_atrib, mutace, pocet_h, sk__fin.autor, u_cas, u_minut, round50, text_sleva, text_link, id_limit, uzcis, u_datetime, ;
		tm_sk_sez_kas.c_kas, tm_sk_sez_kas.id_kas, tm_sk_sez_kas.uzaverka, tm_sk_sez_kas.c_mena, ;
		$0 AS cena_celk, $0 AS sleva_abs, $0 AS trzba1, $0 AS trzba2, $0 AS trzba3, $0 AS trzba4, $0 AS dan1, $0 AS dan2, $0 AS dan3, $0 AS dan4, sk__fin.pohladavka, sk__fin.id_slave ;
		GROUP BY sk__fin.c_uzaverka, ucet ;
	    FROM sk__fin LEFT JOIN tm_sk_sez_kas ON sk__fin.c_uzaverka = tm_sk_sez_kas.c_uzaverka INTO CURSOR tm_sk_fin READWRITE
ELSE 
	SELECT sk__fin.c_uzaverka, TRANS(ucet, "@L 99999") AS u_cis, stul, cas_atrib, mutace, pocet_h, sk__fin.autor, u_cas, u_minut, round50, text_sleva, text_link, id_limit, uzcis, ;
		tm_sk_sez_kas.c_kas, tm_sk_sez_kas.id_kas, tm_sk_sez_kas.uzaverka, tm_sk_sez_kas.c_mena, ;
		$0 AS cena_celk, $0 AS sleva_abs, $0 AS trzba1, $0 AS trzba2, $0 AS trzba3, $0 AS trzba4, $0 AS dan1, $0 AS dan2, $0 AS dan3, $0 AS dan4, sk__fin.pohladavka, sk__fin.id_slave ;
		GROUP BY sk__fin.c_uzaverka, ucet ;
	    FROM sk__fin LEFT JOIN tm_sk_sez_kas ON sk__fin.c_uzaverka = tm_sk_sez_kas.c_uzaverka INTO CURSOR tm_sk_fin READWRITE
ENDIF 
REPLACE FOR ISNULL(id_kas) c_kas WITH 0, id_kas WITH "XX"
REPLACE FOR EMPTY(cas_atrib) cas_atrib WITH "2"
*!*	IF m.girep_mena=2
*!*		REPLACE ALL round50 WITH 0
*!*	ENDIF
INDEX ON uzaverka + u_cis TAG uz_ucet
INDEX ON uzaverka + u_cis + id_kas TAG uzucetkas


***     ****     ****     ****   PLT  PLT  PLT  PLT  PLT  PLT  PLT   ****     ****     ****     ****
had.INCREMENT("PLT")
SELECT sk__plt.c_uzaverka, sk__plt.druh_pl, sk__plt.cena_pl, sk__plt.tip, sk__plt.mena, sk__plt.cena_mena, sk__plt.poradi, sk__plt.TEXT, TRANS(ucet, "@L 99999") AS u_cis, "   " AS AUTOR, ;
	tm_sk_sez_kas.c_kas, tm_sk_sez_kas.id_kas, tm_sk_sez_kas.uzaverka, tm_sk_sez_kas.c_mena ;
    FROM sk__plt LEFT JOIN tm_sk_sez_kas ON sk__plt.c_uzaverka = tm_sk_sez_kas.c_uzaverka INTO CURSOR tm_sk_plt READWRITE 

REPLACE FOR ISNULL(id_kas) c_kas WITH 0, id_kas WITH "XX", c_mena WITH 1
REPLACE ALL   tip WITH NTOM(MTON(tip)    * en__meny(c_mena,m.girep_mena)), ;
	cena_pl WITH NTOM(MTON(cena_pl)* en__meny(c_mena,m.girep_mena))
SET RELATION TO uzaverka + u_cis + id_kas INTO tm_sk_fin
REPLACE ALL autor WITH tm_sk_fin.autor
DELETE FOR tm_sk_fin.pohladavka
SET RELATION TO

***     ****     ****     ****   prodane-karty  prodane-karty  prodane-karty  prodane-karty      ****     ****     ****     ****
had.INCREMENT("prodane-karty")

***   pomocna databaze z karet a skupin; prace s ni je rychlejsi, nez z velke tm_sk_pol postupne relace do karet a skupin
nazev_x = SUBSTR(m.s_trz_mutace, AT(".", m.s_trz_mutace)+1)
IF m.issql
	SELECT c_karty, nazev, SPACE(m.sirkastlpcadruh) as druh, SPACE(m.sirkastlpcadruh) as druh_skl, SPACE(m.sirkastlpcadruh) as druh_kalk, c_druh, c_druhs, c_druhk, cena_prum, SPACE(m.sirkastlpcadruh) as druh_xx, c_druhk as c_druhxx, ;
		SPACE(3) as spart, c_karty as c_kart_new, &nazev_x as nazev_xx, SPACE(3) as spart_kas ;
		FROM karty INTO CURSOR prodane_karty READWRITE 
ELSE
	SELECT c_karty, nazev, druh, druh_skl, druh_kalk, c_druh, c_druhs, c_druhk, cena_prum, druh_kalk as druh_xx, c_druhk as c_druhxx, ;
		SPACE(3) as spart, c_karty as c_kart_new, &nazev_x as nazev_xx, SPACE(3) as spart_kas ;
		FROM karty INTO CURSOR prodane_karty READWRITE 
ENDIF 
REPLACE FOR EMPTY(c_druhxx) c_druhxx WITH c_druh
REPLACE FOR EMPTY(c_druhxx) c_druhxx WITH c_druhs
UPDATE prodane_karty SET druh_xx=skupiny.druh from skupiny WHERE prodane_karty.c_druhxx = skupiny.c_druh

REPLACE FOR "SMAZANO" $ nazev_xx nazev_xx WITH nazev
INSERT INTO prodane_karty (c_karty, nazev, nazev_xx) VALUES (-99,           m.nechci,                      m.nechci)
INSERT INTO prodane_karty (c_karty, nazev, nazev_xx) VALUES (-654321, "---- nic se neprodalo ---", "---- nic se neprodalo ---")

SET ORDER TO druh IN seldruhy
SET RELATION TO druh_xx INTO seldruhy
REPLACE FOR .not. seldruhy.lbl druh_xx WITH m.nechci, c_druhxx WITH -99, c_kart_new WITH -99
SET RELATION TO
IF !EMPTY(m.filter_priznakov)
	DO pozavirej WITH "ktriedfilt"
	IF m.issql
		TRY 
			sql_select('select distinct c_karty FROM  food600.ktried where c_data = ?m.cdata and '+m.filter_priznakov,'ktriedfilt','c_karty i')
		CATCH
			DO pozavirej WITH "ktriedfilt"
		ENDTRY 
	ELSE 
		IF !USED("ktried")
			m.bolktried=.f.
			USE (m.fn_ktried) IN 0 ALIAS ktried
		ELSE
			m.bolktried=.t.
		ENDIF 					
		TRY 
			SELECT * FROM ktried WHERE &m.filter_priznakov INTO CURSOR ktriedfilt
		CATCH
			DO pozavirej WITH "ktriedfilt"
		ENDTRY
		IF !m.bolktried 
			DO pozavirej WITH "ktried"
		ENDIF  
	ENDIF 
ELSE 
	DO pozavirej WITH "ktriedfilt"
ENDIF 
IF USED("ktriedfilt")
	UPDATE prodane_karty SET druh_xx=m.nechci, c_druhxx=-99, c_kart_new=-99 WHERE !(c_karty in (select c_karty from ktriedfilt))
	USE IN ktriedfilt
ENDIF 
SELECT prodane_karty

UPDATE prodane_karty SET spart=skupiny.spart, spart_kas=skupiny.spart_kas from skupiny WHERE prodane_karty.c_druhxx = skupiny.c_druh
UPDATE prodane_karty SET druh=skupiny.druh from skupiny WHERE prodane_karty.c_druh = skupiny.c_druh
UPDATE prodane_karty SET druh_skl=skupiny.druh from skupiny WHERE prodane_karty.c_druhs = skupiny.c_druh
UPDATE prodane_karty SET druh_kalk=skupiny.druh from skupiny WHERE prodane_karty.c_druhk = skupiny.c_druh
UPDATE prodane_karty SET druh_xx=skupiny.druh from skupiny WHERE prodane_karty.c_druhxx = skupiny.c_druh
REPLACE FOR EMPTY(spart) spart WITH "___"
REPLACE cena_prum WITH 0 FOR INLIST(spart,"FST","VAR") OR INLIST(spart_kas,"FST","VAR")
INDEX on c_karty TAG c_karty

***     ****     ****     ****   POL  POL  POL  POL  POL  POL  POL   ****     ****     ****     ****
had.INCREMENT("POL")
SELECT sk__pol.c_uzaverka, TRANS(sk__pol.ucet, "@L 99999") AS u_cis, c_stredisk, c_karty, prachy, prachy_puv, dan, mnozstvi, dan_sazba, ;
	cen_hlad, k_odpoctu, poradi, dotacia, dil_porce, sk__pol.id_kas, sk__pol.id_man, 000000000000 as c_druh, ;
	tm_sk_sez_kas.c_kas, tm_sk_sez_kas.uzaverka, id_fmenu, tm_sk_sez_kas.c_mena ;
	FROM sk__pol LEFT JOIN tm_sk_sez_kas ON sk__pol.c_uzaverka = tm_sk_sez_kas.c_uzaverka INTO CURSOR tm_sk_pol READWRITE

REPLACE FOR EMPTY(cen_hlad) cen_hlad WITH "1"
REPLACE FOR VAL(cen_hlad) # 0 cen_hlad WITH ALLTRIM(STR(VAL(cen_hlad)))

REPLACE FOR EMPTY(k_odpoctu) dan WITH 0, prachy_puv WITH 0, prachy WITH 0

SET RELATION TO uzaverka + u_cis + id_kas INTO tm_sk_fin
DELETE FOR tm_sk_fin.pohladavka
*REPLACE FOR EMPTY(c_mena) c_mena WITH 1

had.INCREMENT("POL 2")

SET RELATION TO c_karty INTO prodane_karty
REPLACE ALL c_druh WITH prodane_karty.c_druhxx, c_karty WITH prodane_karty.c_kart_new
SET RELATION TO

c_mena = 1
REPLACE ALL prachy WITH NTOM(MTON(prachy)    * en__meny(c_mena,m.girep_mena)), ;
        prachy_puv WITH NTOM(MTON(prachy_puv)* en__meny(c_mena,m.girep_mena)), ;
        dan        WITH NTOM(MTON(dan)       * en__meny(c_mena,m.girep_mena))


*!*******************      byvala procedura TR_BUFFPREP       *******************
had.INCREMENT("dane")

SELECT DISTINCT dan_sazba AS dan WHERE prachy # 0 FROM tm_sk_pol INTO CURSOR dane READWRITE 
iii = 1
STORE 1.000 TO s_dph1
STORE 9.999 TO s_dph2, s_dph3, s_dph4, s_dph5
SCAN FOR dan > 1
	iii = m.iii + 1
	dph_xx = "s_dph" + STR(m.iii, 1)
	&dph_xx = dane.dan
ENDSCAN

***     ****     ****     ****        trzby po DPH z POL do FIN        ****     ****     ****     ****
had.INCREMENT("scan dane")
SELECT tm_sk_pol
SCAN FOR .NOT. EMPTY(k_odpoctu)
	SCATTER MEMVAR
    had.set_txt2(m.uzaverka)
	SELECT tm_sk_fin
    SET ORDER TO uzucetkas
	SEEK m.uzaverka + m.u_cis + m.id_kas
	IF .NOT. FOUND()
		INSERT INTO tm_sk_fin (uzaverka, u_cis, id_kas, text_link) VALUES (m.uzaverka, m.u_cis, m.id_kas, "pridan z POL")
	ENDIF
	DO CASE
		CASE dan_sazba = m.s_dph1
			REPL trzba1 WITH trzba1 + m.prachy, cena_celk WITH cena_celk + m.prachy_puv, dan1 WITH dan1 + dan, sleva_abs WITH sleva_abs+m.prachy_puv-m.prachy
		CASE dan_sazba = m.s_dph2
			REPL trzba2 WITH trzba2 + m.prachy, cena_celk WITH cena_celk + m.prachy_puv, dan2 WITH dan2 + dan, sleva_abs WITH sleva_abs+m.prachy_puv-m.prachy
		CASE dan_sazba = m.s_dph3
			REPL trzba3 WITH trzba3 + m.prachy, cena_celk WITH cena_celk + m.prachy_puv, dan3 WITH dan3 + dan, sleva_abs WITH sleva_abs+m.prachy_puv-m.prachy
		CASE dan_sazba = m.s_dph4
			REPL trzba4 WITH trzba4 + m.prachy, cena_celk WITH cena_celk + m.prachy_puv, dan4 WITH dan4 + dan, sleva_abs WITH sleva_abs+m.prachy_puv-m.prachy
	ENDCASE
	SELECT tm_sk_pol
ENDSCAN

USE IN dane

SELECT tm_sk_fin
IF m.pocitat_nulove_hosty
	REPLACE FOR EMPTY(pocet_h) pocet_h WITH IIF(INLIST(LEFT(text_sleva,1),"^","@"), -1, 1)
ENDIF 
DELETE FOR pohladavka

****  ****  ****  ****  ****  ****  ****  ****  ****  ****  ****  ****  ****  ****  ****  ****  ****
****    nastaveni, jestli ucty se zapornou castkou zapocitaji pocet hostu nulou nebo zaporne    ****
* replace for cena_celk < 0 pocet_h with - ABS(pocet_h)
* replace for cena_celk < 0 pocet_h with 0
****  ****  ****  ****  ****  ****  ****  ****  ****  ****  ****  ****  ****  ****  ****  ****  ****


***     ****     ****     ****   TXT  TXT  TXT  TXT  TXT  TXT  TXT  ****     ****     ****     ****
had.INCREMENT("TXT")
SELECT sk__txt.*, TRANS(sk__txt.ucet, "@L 99999") AS u_cis, tm_sk_sez_kas.uzaverka, tm_sk_sez_kas.id_kas ;
	FROM sk__txt LEFT JOIN tm_sk_sez_kas ON sk__txt.c_uzaverka = tm_sk_sez_kas.c_uzaverka INTO CURSOR tm_sk_txt READWRITE
INDEX on uzaverka + u_cis + STR(poradi,2) TAG tm_skxx

had.INCREMENT("TXT 2")
REPLACE FOR ISNULL(id_kas) id_kas WITH "XX"
REPLACE FOR  EMPTY(id_kas) id_kas WITH "XX"

SELECT tm_sk_fin
SET ORDER TO uz_ucet
RELEASE had

IF s_dph5 < 9
    = MESSAGEBOX( jaz("rep_sest_ctyri_stlp") + CHR(13) + jaz("rep_zvol_krat_obd"), 48, jaz("rep_viac_ctyri_dph"), 7777)
ENDIF

