*      *      *      *      *     *BEZ HADA*     *      *      *      *      *

*kontrola specifickych chyb pro Ovocny Svetozor
*kontroluje podezrele oprace a chybne ceny v SK
*----------------------------------------------
#DEFINE VERZE "2023-12-17"
#DEFINE CRLF CHR(13)+CHR(10)
#DEFINE JMENO_LOGU "HaB_kontrola"

small_log ("CRLF" , JMENO_LOGU)
small_log ("ver." + VERZE , JMENO_LOGU)
CLOSE DATABASES 
IF .f.
  SET ENGINEBEHAVIOR 70
  CLOSE DATABASES 
  SET EXACT ON
  SET ANSI OFF
ENDIF 

LOCAL skin_oErr AS Exception
sk_otevreno = ""
TRY 
   *USE (m.gchom_path + "food_dat ") IN 0 NOUPDATE
   USE (m.gcdat_path + "sk_pol "  ) IN 0 NOUPDATE 
   USE (m.gcdat_path + "sk_plt "  ) IN 0 NOUPDATE 
   USE (m.gcdat_path + "sk_fin "  ) IN 0 NOUPDATE 
   USE (m.gcdat_path + "sk_txt "  ) IN 0 NOUPDATE 
CATCH TO skin_oErr 
   sk_otevreno = "nelze otevrit databazi " + ALLTRIM(STR(skin_oErr.ErrorNo)) + " " + skin_oErr.Message + " Line:" + ALLTRIM(STR(skin_oErr.LineNo))
ENDTRY 

IF EMPTY(m.sk_otevreno)
   m.my_path=m.gcpri_path
   *WAIT 'pri_path '+m.gcpri_path WINDOW  
   *vybere obdobi
   WAIT 'Pripravuji vyber obdobi, muze trvat nekolik minut' WINDOW NOWAIT 
   SELECT sk_fin
   SELECT DISTINCT LEFT(uzaverka,6) as u_datum FROM (m.gcdat_path+'sk_fin') WHERE uzaverka > SUBSTR(TTOC(DATE()-222,1), 3,6) INTO CURSOR seluz GROUP BY u_datum &&WHERE SUBSTR(uzaverka,10,1)=':')
   SELECT seluz
   GO BOTTOM 
   SKIP -MIN(RECCOUNT()-1,350) && jen posledni rok
   m.u_start = u_datum
   GO BOTTOM 
   m.u_konec = u_datum
   WAIT CLEAR 
   INDEX on u_datum TAG u_datum DESCENDING 
   DEFINE POPUP selko FROM 5,20 TO 35,55 TITLE "Vyber konec obdobi" PROMPT FIELD u_datum IN SCREEN 
   ON SELECTION POPUP selko deactivate POPUP selko
   ACTIVATE POPUP selko
   m.u_konec = PROMPT()
   RELEASE POPUPS selko
   DEFINE POPUP selst FROM 5,20 TO 35,55 TITLE "Vyber zacatek obdobi" PROMPT FIELD u_datum IN SCREEN 
   ON SELECTION POPUP selst deactivate POPUP selst
   ACTIVATE POPUP selst
   m.u_start = PROMPT()
   *WAIT 'Start ' + m.u_start wind
   RELEASE POPUPS selst
   SET FILTER TO (u_datum >= m.u_start) 
   GO TOP 
   *WAIT 'Selected start ' + m.u_start + ' konec ' + m.u_konec WINDOW 
   DELETE FILE "seluz.idx"
   IF EMPTY(m.u_konec) .or. EMPTY(m.u_start)
      =MESSAGEBOX('Zrušeno uživatelem <Esc>',0)
      CLOSE DATABASES 
      RETURN 
   ENDIF    
   WAIT 'Selected start ' + m.u_start + ' konec ' + m.u_konec WINDOW TIMEOUT 1
   WAIT 'Kopiruji sk_pol, nekolik minut' WINDOW NOWAIT 
   SELECT sk_pol
   COPY TO (m.my_path+'sk_pol.dbf') FOR (uzaverka >= m.u_start) .and. (uzaverka <= m.u_konec).and. SUBSTR(uzaverka,10,1)=':' 
   WAIT 'Kopiruji sk_fin, nekolik minut' WINDOW NOWAIT 
   SELECT sk_fin
   COPY TO (m.my_path+'sk_fin.dbf') FOR (uzaverka >= m.u_start) .and. (uzaverka <= m.u_konec).and.SUBSTR(uzaverka,10,1)=':'
   WAIT 'Kopiruji sk_plt, nekolik minut' WINDOW NOWAIT 
   SELECT sk_plt
   COPY TO (m.my_path+'sk_plt.dbf') FOR (uzaverka >= m.u_start) .and. (uzaverka <= m.u_konec).and.SUBSTR(uzaverka,10,1)=':'
   WAIT 'Kopiruji sk_txt, nekolik minut' WINDOW NOWAIT 
   SELECT sk_txt
   COPY TO (m.my_path+'sk_txt.dbf') FOR (uzaverka >= m.u_start) .and. (uzaverka <= m.u_konec).and.SUBSTR(uzaverka,10,1)=':'
   CLOSE DATABASES 
ELSE 
   =MESSAGEBOX("Tržbové databáze jsou používany jiným uživatelem, zkuste později "+ALLTRIM(m.sk_otevreno),0)
   RETURN 
ENDIF 

DO WHILE .t.
  DO FORM hajek_kontrola TO m.task

  DO CASE m.task
  CASE m.task = 1
    MESSAGEBOX('Kontrola nulovych cen',0,3000)
    SELECT 0
    USE (m.my_path + "sk_pol") EXCLUSIVE 
    SELECT ALL uzaverka, u_cis, nazev, prachy, mnozstvi, cen_hlad FROM (m.my_path + 'sk_pol');
      INTO TABLE (m.my_path + 'NulovePol');
      WHERE (ALLTRIM(nazev) <> 'krabice dortova            ks') .and.;
      (ALLTRIM(nazev) <> 'coko tabulka svetozor zd   por') .and.;
      (ALLTRIM(nazev) <> 'krabice chlebicky          ks') .and.;
      (ALLTRIM(nazev) <> 'smetana do kavy            por') .and.;
      (ALLTRIM(nazev) <> 'voda z kohoutku            por') .and.;
      !('zdarma' $ nazev) .and. (prachy = 0) .and. (mnozstvi <> 0) 
    BROWSE NOMODIFY TITLE "Položky za 0 kromě krabic, vody apod." IN SCREEN 
    CLOSE DATABASES   

    MESSAGEBOX("Podezřelé účty - stornované poslední položky účtu",0,3000)
    ************************************************
    *kontroluje storna posledních položek z účtu
    ************************************************
    SELECT DISTINCT pol.uzaverka, pol.u_cis, pol.nazev, pol.prachy, fin.autor FROM (m.my_path + "sk_fin") fin, (m.my_path + "sk_pol") pol INTO TABLE (m.my_path+'storna');
      HAVING pol.prachy < 0 .and. ALLTRIM(pol.nazev) <> "predplaceno";
      .and. ALLTRIM(pol.nazev) <> "slehacka -                 por";
      WHERE (pol.uzaverka = fin.uzaverka) .and. (pol.u_cis = fin.u_cis)
    INDEX on autor TAG autor  
    *BROWSE TITLE 'Kontrola poslednich polozek uctu po uctech' NOMODIFY 
    SELECT DISTINCT SUM(prachy) as prachy, SUM(1) as pocet, autor GROUP BY autor FROM (m.my_path+"storna") INTO TABLE (m.my_path+"sumst")
    INDEX on pocet TAG pocet DESCENDING 
    *BROWSE TITLE 'Kontrola poslednich polozek uctu sumy po autorech' NOMODIFY  IN SCREEN 
    SET RELATION TO autor INTO storna
    DO FORM hajek_poslst
    CLOSE DATABASES 

    ************************************************
    *kontroluje nejsou-li markovany krabice bez dortu a podobne
    ************************************************
    *MESSAGEBOX("Podezřelé účty",0,3000)
    SELECT 0
    USE (m.my_path + 'sk_fin') EXCLUSIVE 
    SELECT ALL pol.uzaverka, pol.u_cis, nazev, 000000.00 as cena_dort, 000000.0 as celkem, SPACE(3) as autor,;
      SPACE(1) as is_hot FROM (m.my_path + 'sk_pol') pol, (m.my_path + 'sk_plt') plt;
      INTO TABLE (m.my_path + 'krabice') HAVING ALLTRIM(pol.nazev) = 'krabice dortova            ks' ;
      WHERE (plt.uzaverka = pol.uzaverka) .and. (plt.u_cis = pol.u_cis)
    INDEX on uzaverka + u_cis tag uzcis  
    SELECT sk_plt
    SET RELATION TO uzaverka + u_cis INTO krabice
    SCAN FOR LEFT(druh_pl,6) = "Hotove"
      IF FOUND("krabice")
        REPLACE krabice.is_hot WITH "*"
      ENDIF 
    ENDSCAN 
    SET RELATION TO     
    MESSAGEBOX("Podezřelé účty - nízké sumy s krabicemi",0,3000)
    WAIT 'Cekej' WINDOW NOWAIT 
    SELECT sk_pol
    USE (m.my_path + 'sk_pol') EXCLUSIVE 
    INDEX on uzaverka + u_cis to (m.gcpri_path + "pol_uzcis")
    SELECT sk_plt 
    INDEX on uzaverka + u_cis TO (m.gcpri_path + "plt_uzcis")
    SELECT sk_fin
    INDEX on uzaverka + u_cis to (m.gcpri_path + "fin_uzcis")
    SELECT krabice
    SET RELATION TO uzaverka+u_cis INTO sk_fin
    REPLACE ALL celkem WITH sk_fin.cena_slev, autor WITH sk_fin.autor
    SET RELATION TO uzaverka+u_cis INTO sk_pol ADDITIVE 
    SET RELATION TO uzaverka+u_cis INTO sk_plt ADDITIVE 
    SELECT krabice
    INDEX on cena_dort TAG cena_dort
    SET FILTER TO celkem >= 0 .and. !EMPTY(is_hot)
    WAIT CLEAR 
    DO FORM hajek_krafinpol
    CLOSE DATABASES 

    ************************************************
    *kontroluje VIP karty, kolik kdo utrati
    *kontroluje personalni utraty
    ************************************************
    WAIT "Slevy, polozky" WINDOW NOWAIT 
    * hladina 1 prodej
    * hladina 4 objednavky, 100%
    * hladina 2 Quadrio, plazma 90%
    * hladina 3 vecerni sleva, VIP karty
    * hladina 4 objednavky 
    * hladina 5 personal, super VIP karty
    * empty(text_sleva) večerní sleva
    SELECT ALL FROM (m.my_path + "sk_fin") fin, (m.my_path + "SK_pol") pol;
      pol.uzaverka, pol.u_cis, pol.nazev, pol.prachy, pol.mnozstvi, pol.cen_hlad, fin.text_sleva, ALLTRIM(SUBSTR(text_sleva,7)) as ident, fin.autor;
     HAVING (pol.cen_hlad <> "1") .and. (pol.cen_hlad <> "4") .and. LEFT(fin.text_sleva, 1) = "#";
     WHERE (pol.uzaverka = fin.uzaverka) .and. (pol.u_cis = fin.u_cis);
     INTO TABLE (m.my_path + "slevy") ORDER BY fin.text_sleva 
    WAIT "Slevy, sumy" WINDOW NOWAIT
    INDEX on u_cis TAG u_cis
    SELECT sk_fin
    SET RELATION TO ALLTRIM(SUBSTR(sk_fin.text_sleva,2,10)) INTO slevy
    SCAN ALL FOR LEFT(text_sleva,1)='@' && storna vyhod, pokud je najdes
      IF FOUND('slevy')
        SELECT slevy
        DELETE ALL FOR u_cis=ALLTRIM(SUBSTR(sk_fin.text_sleva,2,10))
        SELECT sk_fin 
      ENDIF 
    ENDSCAN  
    SELECT slevy
    PACK 
    SELECT DISTINCT text_sleva, ident, SUM(prachy) as prachy, SPACE(30) as jmeno, SPACE(30) as firma;
      FROM (m.my_path + "slevy") GROUP BY text_sleva;
      INTO TABLE (m.my_path + "sumy")
    INDEX on prachy TAG prachy  
    SELECT 0 
    SET EXCLUSIVE OFF 
    USE (m.gchom_path + "hot_kred")&&alto
    SET EXCLUSIVE ON 
    SET ORDER To ident
    SELECT sumy
    SET RELATION TO ALLTRIM(SUBSTR(text_sleva,7)) INTO hot_kred
    REPLACE ALL jmeno WITH hot_kred.jmeno, firma WITH hot_kred.firma
    SET RELATION to
    USE IN hot_kred
    SELECT slevy
    INDEX on ident TAG ident
    SET ORDER TO tag ident
    SELECT sumy
    SET RELATION TO ident INTO slevy
    INDEX on firma + TRANSFORM(prachy, "@L 9999999.99") TAG firma
    DO FORM hajek_VIPKarty
    CLOSE DATABASES 
  CASE m.task = 2
    *********************************************************
    * průměrné transakce po střediscích se zmrzlinou i bez
    *********************************************************
    CLOSE DATABASES 
    WAIT "Velikost Transakce" WINDOW NOWAIT 
    SELECT 0
    USE (m.my_path + "sk_pol") EXCLUSIVE 
    * účty se zmrzlinou a počty položek na nich kromě zmrzliny
    SELECT DISTINCT u_cis, 000 as pocet FROM (m.my_path + "sk_pol") INTO TABLE (m.my_path + "zmrzlina") HAVING ATC(left(nazev,6),"kopecektocena")>0
    INDEX on u_cis TAG u_cis 
    SELECT sk_pol
    SET RELATION TO u_cis INTO zmrzlina
    GO TOP 
    m.pocet = 0
    m.u_cis = u_cis
    SCAN ALL 
      IF m.u_cis != u_cis 
        SKIP -1
        REPLACE zmrzlina.pocet WITH m.pocet
        SKIP     
        m.pocet = 0
        m.u_cis = u_cis
        IF ATC(left(nazev,6),"kopecektocena")=0  
          m.pocet = m.pocet + 1
        ENDIF   
      ELSE 
        IF ATC(left(nazev,6),"kopecektocena")=0
          m.pocet = m.pocet +1  
        ENDIF   
      ENDIF       
    ENDSCAN 
    SET RELATION TO 
    SELECT zmrzlina 
    SELECT DISTINCT pol.u_cis, SUM(pol.prachy) as prachy, plt.cena_pl, plt.druh_pl, 000 as pocet;
      FROM (m.my_path + 'sk_pol') pol, (m.my_path + 'sk_plt') plt;
      GROUP BY pol.u_cis, plt.druh_pl;
      INTO TABLE (m.my_path + "transakce");
      WHERE (pol.u_cis = plt.u_cis); 
      HAVING ATC(ALLTRIM(plt.druh_pl),"K_KARTAHotoveK_ESODEN_BODY")>0 
    SET RELATION TO u_cis INTO zmrzlina
    * pocet = -1 -> účet bez zmrzliny
    * pocet =  0 -> účet jen se zmrzlinou
    * pocet >  0 -> počet ostatních položek na účtu se zmrzlinou
    REPLACE ALL pocet WITH iif(FOUND("zmrzlina"), zmrzlina.pocet,-1)
    SET RELATION TO 
    SELECT sk_pol
    INDEX on u_cis TAG u_cis
    SELECT transakce
    SET RELATION TO u_cis INTO sk_pol
    SET RELATION TO 
    SELECT DISTINCT LEFT(u_cis,2) as stredisko,;
      SUM(1) as c_pocuctu, SUM(prachy) as c_prachy, 0000 as c_trans,;
      SUM(IIF(pocet != 0,1,0)) as b_pocuctu, SUM(IIF(pocet != 0,prachy,0)) as b_prachy, 0000 as b_trans,;
      SUM(cena_pl) as cena_pl;
      FROM (m.my_path + "Transakce")   INTO TABLE (m.my_path + "SumTran") GROUP BY stredisko
    REPLACE ALL c_trans WITH ROUND(c_prachy/c_pocuctu, 0), b_trans WITH ROUND(b_prachy/b_pocuctu, 0)
    REPLACE ALL c_prachy WITH ROUND(c_prachy,0), b_prachy WITH ROUND(b_prachy,0), cena_pl WITH ROUND(cena_pl,0)
    GO TOP 
    DO FORM hajek_FinishTran
    CLOSE DATABASES   
  CASE m.task = 3
    DO hajek_FaDotisk
  OTHERWISE 
    RETURN 
  ENDCASE   
ENDDO
