*    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *
*
* program z sql tabulek udela dbf, ktere mohou byt zpracovana pro html stranky trzeb
*
*  2024-01-29 - 
*
*    *    *    *    *    *    *    *    *    *    *    *      #> ver. v.2024-01-31 <#       *    *    *    *    *    *    *    *    *    *    *    *    *

LPARAMETERS pocet_dni 
IF EMPTY( m.pocet_dni )
    pocet_dni = 22
ENDIF 

#DEFINE VERZE "v.2024-01-31"


min_uct_den = DATE() - m.pocet_dni 
vysledek = m.vysledek + crlf + "min_uct_den: " + DTOS(m.min_uct_den)

cdata = 1
crlf = CHR(13)+CHR(10)
vysledek = crlf  + VERZE
STORE "" TO sqldatabaza, sqladresa
issql = .T.

SET REPROCESS TO AUTOMATIC
CLOSE DATABASES 
CLOSE DATABASES 
o_set_point = SET("POINT")
SET POINT TO "."
ACTIVATE SCREEN 

ftp_sql()

vezmi_food_dat_a_karty()
vezmi_ucpol()
vezmi_sk()
vezmi_tmsk()

SET POINT TO (m.o_set_point)
? "==========    HOTOVO    =========="


*  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  
PROCEDURE vezmi_food_dat_a_karty
small_log(PROGRAM())

lsql =                  "SELECT c_stredisk  , id     , id_zkratka      , stredskup FROM food600.food_dat WHERE c_data=?m.cdata "
sql_select(lsql,"sql_cursor","c_stredisk I, id C(2), id_zkratka C(30), stredskup C(4)")
SELECT * FROM sql_cursor INTO TABLE (m.pri_path + "food_dat")
*REPL ALL id_zkratka WITH strtr(id_zkratka, "POKLADNA", "" )
REPL ALL id_zkratka WITH strtr(id_zkratka, "KASA"    , "" )
REPL ALL id_zkratka WITH strtr(id_zkratka, "MASTER"  , "" )
REPL ALL id_zkratka WITH strtr(id_zkratka, " - "     , " ")

INDEX on id         TAG id
INDEX on c_stredisk TAG c_stredisk

lsql =                 "SELECT c_karty  , nazev      , s.druh    , s.spart FROM food600.karty k LEFT JOIN food600.skupiny s ON s.c_druh = k.c_druhk "
sql_select(lsql,"sql_cursor", "c_karty I, nazev C(30),   druh C(30), spart C(3)")
SELECT * FROM sql_cursor INTO TABLE (m.pri_path + "karty")
INDEX on c_karty TAG c_karty



*  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  
PROCEDURE vezmi_ucpol
small_log(PROGRAM())

lsql =                 "SELECT kolik,        delitel     , nazev      , druh      , spart     , pc         , u_cis     , stul       , cidmaster FROM food600.uc_pol "
sql_select(lsql, "sql_cursor", "kolik N(9,2), delitel N(2), nazev C(30), druh C(30), spart C(3), cena N(9,1), u_cis C(7), stul N(6,1), kas N(5)")
DELETE FOR .not. EMPTY(u_cis)
REPLACE FOR EMPTY(delitel) delitel WITH 1
SELECT * FROM sql_cursor INTO TABLE (m.pri_path + "ucpol")


*  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  
PROCEDURE vezmi_sk
small_log(PROGRAM())

lsql =             "SELECT c_uzaverka  , uzaverka      , c_kas  , id_kas     , uct_den FROM food600.sk__sez WHERE c_data=?m.cdata AND uct_den > ?min_uct_den "
sql_select(lsql,"sql_cursor","c_uzaverka I, uzaverka C(22), c_kas I, id_kas C(2), uct_den D")
SELECT * FROM sql_cursor INTO TABLE (m.pri_path + "sk_sez")
DELETE FOR .not. ":" $ uzaverka

REPLACE ALL uzaverka WITH LEFT(TTOC(uct_den, 3), 10)
REPLACE ALL uzaverka WITH LEFT(uzaverka, 12) + dat_den_tydne(uzaverka)

dopln_nazev_kasy()
INDEX on c_uzaverka TAG c_uzaverka

SELECT MIN(c_uzaverka) as min_c_uzav FROM sk_sez INTO CURSOR min_c_uzav
SCATTER MEMVAR 
USE IN min_c_uzav

*** ---------------- 
lsql = "SELECT             c_uzaverka,   ucet,   druh_pl,       cena_pl,   tip,   mena,      cena_mena, poradi FROM food600.sk__plt WHERE c_data=?m.cdata AND c_uzaverka > ?min_c_uzav"
sql_select(lsql,"sql_cursor","c_uzaverka I, ucet I, druh_pl C(22), cena_pl N(9,2), tip N(9,2), mena C(3), cena_mena N(9,2), poradi I")

SELECT *, SPACE(22) as uzaverka, {} as uct_den, SPACE(30) as id_zkratka, "    " as stredskup ;
       FROM sql_cursor INTO TABLE (m.pri_path + "sk_plt") 
dopln_z_sk_sez()
dopln_nazev_platby()


*** ---------------- 
lsql = "SELECT c_uzaverka, ucet, c_stredisk, c_karty, cen_hlad, k_odpoctu, prachy, prachy_puv, dan, dan_sazba, mnozstvi FROM food600.sk__pol WHERE c_data=?m.cdata AND c_uzaverka > ?min_c_uzav"
sql_select(lsql,"sql_cursor", "c_uzaverka I, ucet I, c_stredisk I, c_karty I, cen_hlad C(2), k_odpoctu C(1), prachy N(9,2), prachy_puv N(9,2), dan N(9,2), dan_sazba N(6,2), mnozstvi N(9,2)")

SELECT *, SPACE(22) as uzaverka, {} as uct_den, SPACE(30) as id_zkratka, "    " as stredskup, SPACE(10) as druh, "   " as spart ;
       FROM sql_cursor INTO TABLE (m.pri_path + "sk_pol")
dopln_z_sk_sez()

SET RELATION TO c_karty INTO karty
REPLACE FOR .not. ISNULL(karty.druh) druh WITH karty.druh, spart WITH karty.spart



*  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  
PROCEDURE vezmi_tmsk
small_log(PROGRAM())

lsql =                 "SELECT u_cis     ,  druh_pl,      cena_pl      , tip        , mena     , cena_mena       , poradi FROM food600._tmskplt WHERE c_data=?m.cdata "
sql_select(lsql,"sql_cursor","u_cis C(7), druh_pl C(22), cena_pl N(9,2), tip N(9,2), mena C(3), cena_mena N(9,2), poradi I")

SELECT *, "  " as id_kas, SPACE(22) as uzaverka, {} as uct_den ;
       FROM sql_cursor INTO TABLE (m.pri_path + "_tmskplt") 
REPLACE ALL id_kas WITH u_cis
dopln_nazev_kasy()
dopln_nazev_platby()

*** ---------------- 
lsql =                   "SELECT id_kas     , cena_slev  , 1 as pocet, u_cas FROM food600._tmskfin"
sql_select(lsql, "sql_cursor", "id_kas C(2), cena N(9,1), pocet N(4), u_cas C(14)")
SELECT *, SPACE(22) as uzaverka, {} as uct_den ;
       FROM sql_cursor INTO TABLE (m.pri_path + "_tmskfin") 
dopln_nazev_kasy()

*** ---------------- 
lsql =                  "SELECT u_cis     , id_man     , id_kas     , c_karty  , cen_hlad     , k_odpoctu     , prachy       , dan       , mnozstvi FROM food600._tmskpol WHERE c_data=?m.cdata "
sql_select(lsql,"sql_cursor", "u_cis C(7), id_man C(2), id_kas C(2), c_karty I, cen_hlad C(2), k_odpoctu C(1), prachy N(9,2), dan N(9,2), mnozstvi N(9,2)")

SELECT *, prachy as prachy_puv, SPACE(22) as uzaverka, {} as uct_den, SPACE(10) as druh, "   " as spart ;
       FROM sql_cursor INTO TABLE (m.pri_path + "_tmskpol")
dopln_nazev_kasy()

SET RELATION TO c_karty INTO karty
REPLACE FOR .not. ISNULL(karty.druh) druh WITH karty.druh, spart WITH karty.spart




***********************************************************
* Vytvori kurzor z SQL selectu pomocou Cursoradapteru
***********************************************************
FUNCTION sql_select
PARAMETERS ip_select,ip_alias,ip_schema,ip_bez_hada, m.boladb
boladb = .f.
LOCAL l_prip, vysledok

WAIT LEFT('dotahujem ' + m.ip_alias, 254) WINDOW AT 22,22 NOWAIT 

IF NOT USED("teraz")
    USE 'ps_rteraz' ALIAS 'teraz' IN 0
ENDIF
l_prip =CURSORGETPROP("ConnectHandle","teraz")

lobj=IIF(TYPE('THISFORM')='O',THISFORM,_SCREEN)
lalmeno='aca_'+ip_alias
IF TYPE('lobj.'+lalmeno)<>'O'
    lobj.ADDPROPERTY(lalmeno)
ELSE
    lobj.&lalmeno=.NULL.
ENDIF
lobj.&lalmeno= CREATEOBJECT('cursoradapter')
lobj= lobj.&lalmeno

lobj.SELECTCMD=ip_select
lobj.ALIAS=ip_alias
lobj.DATASOURCE=l_prip
lobj.DATASOURCETYPE='ODBC'
lobj.FETCHMEMO=.T.
lobj.FETCHASNEEDED=.F.
lobj.FETCHSIZE=-1
lobj.MAXRECORDS=-1
IF TYPE('ip_schema')='C'
    lobj.USECURSORSCHEMA=.T.
    lobj.CURSORSCHEMA=ip_schema
ENDIF

vysledok = lobj.CURSORFILL()
IF m.vysledok
    IF USED('teraz')
        USE IN 'teraz'
    ENDIF
    SELECT(ip_alias)
ELSE
    AERROR(chybapole)
    MESSAGEBOX("SQL Chyba" + CHR(10) + chybapole(2),64)
ENDIF
WAIT CLEAR 
RETURN m.vysledok


*  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  
PROCEDURE small_log (texticek)
vysledek = vysledek  + crlf + m.texticek


*  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  
PROCEDURE dopln_nazev_kasy

ALTER TABLE (DBF()) ADD id_zkratka C(30)
ALTER TABLE (DBF()) ADD stredskup  C(34)

IF FSIZE("id_kas") > 0
    SET ORDER TO id         IN   food_dat
    SET RELATION TO id_kas  INTO food_dat
ELSE
    SET ORDER TO c_stredisk IN food_dat
    SET RELATION TO c_kas INTO food_dat
ENDIF 

REPLACE FOR .NOT. EMPT(food_dat.id_zkratka) id_zkratka WITH food_dat.id_zkratka
REPLACE ALL stredskup WITH food_dat.stredskup
    

*  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  
PROCEDURE dopln_nazev_platby
o_sel_dnp = SELE()

SELECT PADR(LOWER(kluc), 22) as klic, hodnota FROM ps_rtexty INTO cursor texty WHERE modul = "FOOD"
INDEX on klic TAG klic

SELE (m.o_sel_dnp)
SET RELA TO lower(LEFT(druh_pl,22)) INTO texty
REPL FOR .NOT. EMPT(texty.klic) druh_pl WITH LOWER(texty.hodnota)
USE IN texty


*  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  
FUNCTION dopln_z_sk_sez()
SET RELATION TO c_uzaverka INTO sk_sez
REPLACE ALL uzaverka WITH sk_sez.uzaverka, uct_den WITH sk_sez.uct_den, id_zkratka WITH sk_sez.id_zkratka, stredskup WITH sk_sez.stredskup
DELETE FOR EMPTY(uzaverka)
SET RELATION TO 


*  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  
FUNCTION dat_den_tydne(ddd)

TRY 
    IF VARTYPE(ddd) = "D"
        downum = DOW(m.ddd, 2)
    ELSE
        downum = DOW(DATE(VAL(LEFT(m.ddd,4)), VAL(SUBST(m.ddd,6,2)), VAL(SUBST(m.ddd,9,2))), 2)
    ENDIF 
CATCH
    downum = 9
ENDTRY 
*                  1234567890123456789
#DEFINE DNY_TYDNE "-PoUtStCtPaSoNe--------"
RETURN SUBSTR(DNY_TYDNE, m.downum * 2, 2)
