*   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   *   * 
*
* 2023-12-19 - opraveno v sql oprava konkretnich skupin
* 2023-12-28 - vlozena funkce nx(), protoze v hodne starych verzich foodu chybi
* 2024-01-02 - pro sql se doplni sazby i do tabulky depehacka
* 2024-01-03 - messagebox informuje o verzi
*
*
*   *   *   *   *   *     #> ver. 2024-01-03 <#     *   *   *BEZ HADA*  *   *   *   *   *   *   *   *   *   *   *   *

#DEFINE CRLF          CHR(13)+CHR(10)
#DEFINE VYSSI_SAZBA   1.21

otazka  = "Chcete nastavit sazbu DPH 12% ?"
cisilko = 3+32+256    && otaznik

IF DATETIME() > {^2024-01-22}
    otazka  = m.otazka + CHR(13) + "I KDYZ JE TAK POZDE? " + DTOS(DATE())
    cisilko = 3+48+256    && vykricnik
ENDIF 

MESSAGEBOX("TOHLE JE VERZE, KTERA VYPLNI I " + CHR(13) + "PEPINOVU TABULKU DEPEHACKA",1+64, "DPH20024" + " ver. 2024-01-03", 6666)

IF MESSAGEBOX(otazka, m.cisilko, "Nové sazby DPH     v adresari " + UPPER(m.gchom_path)) = 6
    CLOSE data
    DO open_database

    nastav_sazby_v_food_set()
    nastav_sazby_v_skupiny()
    nastav_skupiny_vyssi_sazby()
    nastav_sazby_v_depehacka()
    CLOSE data
ENDIF 


*    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *   
PROCEDURE nastav_sazby_v_depehacka
IF m.issql

    sql_select("SELECT dzmeny, koefdph FROM food600.depehacka ", "sqlresult") 
    SELECT sqlresult
    LOCATE FOR dzmeny > {^2023-12-30}
    IF .not. FOUND()

        proved("INSERT INTO food600.depehacka (dzmeny, koefdph, popis) VALUES ('2023-12-31', 1.00, 'nulova'  )")
        proved("INSERT INTO food600.depehacka (dzmeny, koefdph, popis) VALUES ('2023-12-31', 1.10, 'nizka_10')")
        proved("INSERT INTO food600.depehacka (dzmeny, koefdph, popis) VALUES ('2023-12-31', 1.12, 'snizena' )")
        proved("INSERT INTO food600.depehacka (dzmeny, koefdph, popis) VALUES ('2023-12-31', 1.15, 'nizka_15')")
        proved("INSERT INTO food600.depehacka (dzmeny, koefdph, popis) VALUES ('2023-12-31', 1.21, 'zakladni')")

    ENDIF 
ENDIF 



*    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *   
PROCEDURE nastav_skupiny_vyssi_sazby

IF m.issql
    cmd = "SELECT druh, typ_kalk  FROM food600.skupiny "
    sql_select(m.cmd, "kalkulace", "druh C(10), typ_kalk C(1)")
    SELECT kalkulace
ELSE 
    SELECT druh, typ_kalk FROM skupiny INTO TABLE kalkulace
ENDIF 
SET FILTER TO typ_kalk = "K"

DEFINE POPUP nabidka PROMPT FIELD druh FROM 1,22 TITLE "Vyber skupinu pro " + nx(VYSSI_SAZBA*100-100) + "%, pro konec [Esc]" FONT 'Courier NEW' FOOTER "pro konec [Esc]"
ON SELECTION POPUP nabidka deacti popup
ACTIVATE SCREEN
ACTIVATE POPUP nabidka
DO WHILE !EMPTY(PROMPT())
    vybranej_druh = PROMPT()
    IF MESSAGEBOX("Opravdu nastavit 21% pro skupinu " + UPPER(m.vybranej_druh) + "?",3+32,"DPH pro " + UPPER(m.vybranej_druh)) = 6
        prepis_skupinu_vyssi_sazby()
    ENDIF 

    ACTIVATE SCREEN
    SELECT kalkulace
    ACTIVATE POPUP nabidka
ENDDO
RELEASE POPUP nabidka


*    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *   
PROCEDURE prepis_skupinu_vyssi_sazby
IF m.issql
    cmd = "SELECT table_schema, table_name, column_name, data_type, udt_name FROM information_schema.columns "
    sql_select(m.cmd, "list_columns", "schema C(33), table_name C(33), clm_name C(33), data_type C(33), udt_name C(33)")
    SELECT list_columns
    SCAN FOR schema = "food600" .and. table_name = "skupiny"  .and. LEFT(clm_name, 3) = "dan"
        sql_cmd = "UPDATE food600.skupiny SET " + ALLTRIM(clm_name) + " = " + nx(VYSSI_SAZBA,3) + " WHERE druh = '" + m.vybranej_druh + "' ;"
        proved(m.sql_cmd )
    ENDSCAN 
ELSE

    SELECT skupiny 
    FOR gnCount = 1 TO FCOUNT()
        policko = FIELD(gnCount)
        IF LOWER(LEFT(m.policko, 3)) = "dan"
            WAIT  m.policko WINDOW NOWAIT 
            REPLACE FOR druh = m.vybranej_druh &policko WITH VYSSI_SAZBA
        ENDIF 
    ENDFOR 
ENDIF 
WAIT CLEAR 


*    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *   
PROCEDURE nastav_sazby_v_skupiny
IF m.issql
    cmd = "SELECT table_schema, table_name, column_name, data_type, udt_name FROM information_schema.columns "
    sql_select(m.cmd, "list_columns", "schema C(33), table_name C(33), clm_name C(33), data_type C(33), udt_name C(33)")
    SELECT list_columns
    SCAN FOR schema = "food600" .and. table_name = "skupiny"  .and. LEFT(clm_name, 3) = "dan"
        sql_cmd ="UPDATE food600.skupiny SET " + ALLTRIM(clm_name) + " = 1.12 WHERE " + ALLTRIM(clm_name) + " BETWEEN 1.10 AND 1.15 ;"
        proved(m.sql_cmd )
    ENDSCAN 

ELSE
    IF FILE(m.gchom_path + "data\skupiny.dbf")
        USE (m.gchom_path + "data\skupiny") ORDER druh

        text_waitu = ""
        FOR gnCount = 1 TO FCOUNT()
             policko = FIELD(gnCount)
             IF ATC("dan", LEFT(m.policko, 3)) > 0
                 text_waitu = IIF(LEN(m.text_waitu) < 99, m.text_waitu + " " + m.policko, m.policko)
                 WAIT  m.text_waitu WINDOW NOWAIT 
                 REPLACE FOR BETWEEN(&policko, 1.10, 1.15) &policko WITH 1.12
             ENDIF 
        ENDFOR 
        WAIT CLEAR 
    ENDIF 
ENDIF 


*    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *   
PROCEDURE nastav_sazby_v_food_set
IF m.issql
    proved("UPDATE food600.defsubor SET var_value = 1.00 WHERE var_name = 'dph1' AND modul = 'FOOD_SET' ;")
    proved("UPDATE food600.defsubor SET var_value = 1.10 WHERE var_name = 'dph2' AND modul = 'FOOD_SET' ;")
    proved("UPDATE food600.defsubor SET var_value = 1.12 WHERE var_name = 'dph3' AND modul = 'FOOD_SET' ;")
    proved("UPDATE food600.defsubor SET var_value = 1.15 WHERE var_name = 'dph4' AND modul = 'FOOD_SET' ;")
    proved("UPDATE food600.defsubor SET var_value = 1.21 WHERE var_name = 'dph5' AND modul = 'FOOD_SET' ;")
    proved("UPDATE food600.defsubor SET var_value = 1.99 WHERE var_name = 'dph6' AND modul = 'FOOD_SET' ;")
    proved("UPDATE food600.defsubor SET var_value = 1.99 WHERE var_name = 'dph7' AND modul = 'FOOD_SET' ;")

ELSE 
    USE (m.gchom_path + "food_set")
    DO dopln_sazby WITH "dph1", "1.00"
    DO dopln_sazby WITH "dph2", "1.10"
    DO dopln_sazby WITH "dph3", "1.12"
    DO dopln_sazby WITH "dph4", "1.15"
    DO dopln_sazby WITH "dph5", "1.21"
    DO dopln_sazby WITH "dph6", "1.99"
    DO dopln_sazby WITH "dph7", "1.99"
ENDIF 


*    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *   
PROCEDURE dopln_sazby
PARAMETERS v_name, v_value
LOCATE FOR var_name = m.v_name
IF FOUND()
   REPLACE FOR var_name = m.v_name var_value WITH m.v_value
ELSE
   INSERT INTO (m.gchom_path + "food_set") (var_name, var_value) VALUES (m.v_name, m.v_value)
ENDIF 


*    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *    
FUNCTION proved (co_udelat)
WAIT m.co_udelat WINDOW NOWAIT 
vysledek = sql_exec( m.co_udelat )
zprava_do_logu = m.co_udelat + STR(m.vysledek, 5)
IF m.vysledek < 0
    DIMENSION chybaexe(9)
    chybaexe(2) = "vsechno v poradku"
    AERROR(chybaexe)
    MESSAGEBOX("SQL Chyba" + CHR(10) + chybaexe(2),64)
    zprava_do_logu = m.zprava_do_logu + " " + chybaexe(2)
ENDIF
small_log (m.zprava_do_logu, "dane2024")
WAIT CLEAR 



********** vraci formatovane cislo  ********** delka se uplatni pouze je-li vetsi nez 
FUNCTION nx (cislo, des_mist, delka)

IF EMPTY(m.des_mist)
    des_mist = 0
ENDIF 

IF EMPTY(m.delka)
    delka = 0
ENDIF 

IF ISNULL(m.cislo)
    cislo = 0
ENDIF 
nxpolotovar = STRTRAN(ALLTRIM(STR(m.cislo, 15, m.des_mist)), ",", ".")    && tohle je alltrimovany

IF m.delka > LEN(m.nxpolotovar)
    nxpolotovar = PADL(m.nxpolotovar, m.delka)
ENDIF 
RETURN nxpolotovar
