*    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *
*
* program generuje webovou stranku z uc_pol, cili otevrene stoly
* z poslednich uzaverek v sk_sez najde jmeno kasy a generovaneho souboru
* z tmskfin bere i prachy za uavrene ucty
*
*  2023-02-13 - 
*  2023-03-07 - pridelan soucet za otevrene stoly
*  2023-03-08 - pridelany ucty po hodinach
*  2023-04-24 - pridano desetinne misto do castky na 999 999.9
*
*    *    *    *    *    *    *    *    *    *    *    *      #> ver. 2023-04-24 <#       *    *    *    *    *    *    *    *    *    *    *    *    *

PARAMETERS jmeno_html

verze = "v.2023-04-24"

IF VARTYPE(pri_path) # "C"
*    pri_path = ".\"
ENDIF 

IF EMPTY(m.jmeno_html)
    jmeno_html = "report2"
ENDIF 

cdata = 1
crlf = CHR(13)+CHR(10)
vysledek = crlf  + " ver." + m.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_tmskfin()

vezmi_ucpol()

SELECT ucpol_sum 
APPEND FROM DBF("ucpol_sumsumsum")
APPEND FROM DBF("tmskfin_sum"    )
APPEND FROM DBF("tmskfin_hod"    )
REPLACE ALL kas WITH "Kasa"

INDEX on kas + stul TAG kas_stul

generuj_html()

SET POINT TO (m.o_set_point)


*  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  
PROCEDURE vezmi_tmskfin
small_log(PROGRAM())

lsql =                "SELECT id_kas, cena_slev, 1 as pocet, u_cas FROM food600._tmskfin"
sql_select(lsql, "_tmskfin", "kas C(2), cena N(9,1), pocet N(4), u_cas C(14)")

CREATE TABLE tmskfin_hod FREE (kas C(4), stul C(6), nazev C(30), pocet I, cena N(9,2)) 
FOR ii = 0 TO 24
    INSERT INTO tmskfin_hod (kas, stul, nazev) VALUES ("Kasa", "xx", TRANSFORM(m.ii, "@L 99"))
ENDFOR 
REPLACE ALL stul WITH "x " + nazev

SELECT * FROM _tmskfin INTO TABLE tmskfin
REPLACE ALL kas WITH "Kasa", u_cas WITH SUBSTR(u_cas, 9, 2)        && 20200312164157
SCAN
    SCATTER MEMVAR 
    SELECT tmskfin_hod 
    LOCATE FOR nazev = m.u_cas
    IF FOUND()
        REPLACE pocet WITH pocet + 1, cena WITH cena + m.cena
    ENDIF 
ENDSCAN 
SELECT tmskfin_hod 
GO TOP 
DELETE while pocet = 0 .and. cena = 0

GO BOTTOM 
DELETE while pocet = 0 .and. cena = 0
GO BOTTOM 
DELETE while pocet = 0 .and. cena = 0
GO BOTTOM 
DELETE while pocet = 0 .and. cena = 0
GO BOTTOM 
DELETE while pocet = 0 .and. cena = 0

REPLACE ALL nazev WITH ALLT(nazev) + ". hodina"


INDEX on cena TAG cena 
GO bott
max_cena = cena
REPLACE FOR cena > m.max_cena * .3 nazev WITH ALLT(nazev) + " *"
REPLACE FOR cena > m.max_cena * .4 nazev WITH ALLT(nazev) + "*"
REPLACE FOR cena > m.max_cena * .5 nazev WITH ALLT(nazev) + "*"
REPLACE FOR cena > m.max_cena * .6 nazev WITH ALLT(nazev) + "*"
REPLACE FOR cena > m.max_cena * .7 nazev WITH ALLT(nazev) + "*"
REPLACE FOR cena > m.max_cena * .8 nazev WITH ALLT(nazev) + "*"
REPLACE FOR cena > m.max_cena * .9 nazev WITH ALLT(nazev) + "*"


SELECT kas, "x .." as stul, "uzavřené účty celkem" AS nazev, SUM(pocet) as pocet, SUM(cena) AS cena GROUP BY kas, stul, nazev FROM tmskfin INTO CURSOR tmskfin_sum READWRITE 


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

lsql =             "SELECT kolik,        delitel     , nazev      , pc         , u_cis     , stul       , cidmaster FROM food600.uc_pol "
sql_select(lsql, "ucpol", "kolik N(9,2), delitel N(2), nazev C(30), 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 STR(ABS(kas), 4) as kas, STR(stul, 6,1) as stul, nazev, SUM(kolik/delitel) as pocet, cena GROUP BY kas, stul, nazev FROM ucpol INTO TABLE ucpol_sum READWRITE 
REPLACE ALL stul WITH STRTRAN(stul, ".0", "  ")
REPLACE ALL kas WITH "Kasa"

SELECT kas,             stul,                                       1     as pocet, SUM(cena * pocet) AS cena GROUP BY kas, stul FROM ucpol_sum    INTO CURSOR ucpol_sumsum      && soucty penez po otevrenych stolech
SELECT kas, "    xx" as stul, "OTEVŘENÉ stoly celkem" AS nazev, SUM(pocet) as pocet, SUM(cena * pocet) AS cena GROUP BY kas       FROM ucpol_sumsum INTO CURSOR ucpol_sumsumsum   && dtto  celkem


*  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  
PROCEDURE generuj_html

small_log(PROGRAM() + " generuju " + m.jmeno_html + ".htm")

fh = fcreate(m.jmeno_html + ".htm")
IF m.fh > 0
     DO create_html_head

     DO create_html_stoly

     DO create_html_tail
ELSE
     small_log(" NEPROVEDENO, nelze vytvorit soubor " + upper(m.jmeno_html))
ENDIF 
CLOSE DATABASES 


*  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  
PROCEDURE create_html_stoly

STORE "Z*Z" TO o_stul, o_kas, pozadi

SELECT ucpol_sum
INDEX on kas + stul + nazev TAG k_st_naz
SCAN 
  if kas # m.o_kas
    DO zapis_zacatek_tabulky WITH "d"
    fputs(m.fh, '<TD class="center" width="166px"> ' + UPPER(m.jmeno_html) + ' </TD>')
    fputs(m.fh, '<TD class="center" width="77px"> Cena </TD>')
    fputs(m.fh, '<TD class="center"> Počet </TD></TR>')
  endif

  DO zapis_zacatek_radku_tabulky WITH "d"
  IF ATC("celkem", nazev) > 0          && souctove radky se zobrazi v jine barve
      fputs(m.fh, '<TD class="center" > ' + nazev + '</TD>')
      fputs(m.fh, '<TD class="right" >' + trans(cena, "999 999.9" ) + ' &nbsp; </TD>'     )
      fputs(m.fh, '<TD class="right" >' + trans(pocet, "9999"   )  + ' &nbsp; &nbsp; </TD></TR>')
  ELSE
      fputs(m.fh, '<TD class="smallleft" > ' + nazev + '</TD>')
      fputs(m.fh, '<TD class="right" >' + trans(cena, "999 999.9" ) + ' &nbsp; </TD>'     )
*      fputs(m.fh, '<TD class="right" >' + trans(pocet, "9999.9" )  + ' &nbsp; </TD></TR>')
      IF pocet # 0
          fputs(m.fh, '<TD class="right" >' + trans(pocet, "9999"   )  + ' &nbsp; &nbsp; </TD></TR>')
      ELSE
          fputs(m.fh, '<TD class="right" >' +                          + ' &nbsp; &nbsp; </TD></TR>')
      ENDIF 
  ENDIF
ENDSCAN 


*  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  
PROCEDURE zapis_zacatek_tabulky ( poz1 )
  fputs(m.fh, '</TABLE></CENTER><CENTER><IMG src="./pozadi.jpg"  border="0" height="6" ></CENTER>')
  fputs(m.fh, '<CENTER><TABLE class="a" cellspacing="1" cellpadding="0"> <TR class="' + m.poz1 + 'h">' )
  fputs(m.fh, '<TD class="center" width="50px"> ' + 'Stůl' + ' </TD>')
  STORE "*&#$%" TO o_kas, pozadi
  o_kas = kas


*  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  
PROCEDURE zapis_zacatek_radku_tabulky (poz1)

if stul # m.o_stul
   o_stul = stul
   IF ATC("celkem", nazev) > 0          && souctove radky se zobrazi v jine barve
       pozadi = "b1i"  &&  "a2"
   ELSE
       pozadi = IIF("1" $ m.pozadi, m.poz1 + "2", m.poz1 + "1")     && liche radky se zobrazi inverzne
   ENDIF 

   fputs(m.fh, '<TR class="' + m.pozadi + '"><TD class="center" >' + stul + '</TD>')
else
   fputs(m.fh, '<TR class="' + m.pozadi + '"><TD class="center" >' +  " "  + '</TD>')
endif


*  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  
PROCEDURE create_html_head

*  fputs(m.fh, '<!doctype html>' )
  fputs(m.fh, '<HTML><HEAD>' )
  fputs(m.fh, '<TITLE>' + m.jmeno_html + ' ' + dtoc(date()) + '</TITLE>' )
  fputs(m.fh, '<meta http-equiv="content-type" content="text/html; charset=windows-1250" >' )
  fputs(m.fh, '<meta http-equiv="content-language" content="cs" >' )
  fputs(m.fh, '<meta http-equiv="refresh" content="33">' )
  fputs(m.fh, '<link href="report.css" rel="stylesheet" type="text/css">' )
  fputs(m.fh, '</HEAD><BODY>' )
  fputs(m.fh, '<CENTER><TABLE>' )


*  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  *  
PROCEDURE create_html_tail
  fputs(m.fh, '</TABLE></CENTER>')
  fputs(m.fh, '<h5> generated: ' + LEFT(TTOC(DATETIME(),3),16) +  ' &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ' + m.verze)
  fputs(m.fh, '<br> from ' + GETENV("computername") + " &nbsp; " + m.sqladresa + "\" + UPPER(m.sqldatabaza) + ' </h5>')
  fputs(m.fh, '<CENTER><IMG src="./logo.jpg" ></CENTER>' )
  fputs(m.fh, '</BODY></HTML>' )
  fclose(m.fh)




***********************************************************
* 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)
    IF ".FXP" $ UPPER(SYS(16,0))
        DO FORM ca_error WITH ip_select,ip_alias,ip_schema
    ENDIF
ENDIF
WAIT CLEAR 
RETURN m.vysledok


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

