*:*********************************************************************************************************
*:     Procedure file: SK_IN.PRG
*:
*:             append databazi zacina od SK_SEZ, kde se nejprve vyplni field odecteno = "SK_IN"
*:             pak se appenduji ostatni databaze a na konci se SK_SEZ.odecteno opet smaze. 
*:             pred vlastnim appendem se kontroluje, jestli z minula nejsou v SK_SEZ radky se "SK_IN" v odecteno,
*:             pokud ano, takova uzaverka se ze vsech eskacek smaze, takze bude appendnuta znovu.
*: 2012-11-08: po importu se data kontroluji proti sk_chk.dbf
*: 2012-11-13: do vysledku se zapisuji kody pro romankuv html kontrolor
*: 2012-11-15: v testu oteviratelnosti nahrazeno on error frazi try-catch
*: 2012-11-15: v try-catch se do prom. SK_OTEVRENO (a pak do VYSLEDEK) pise i pricina chyby
*: 2012-12-05: sk_in_kontrola hlasi do vysledku neexistenci kontrolniho souboru sk_chk*
*: 2013-06-21: sk_in_kontrola do vsech radku s nesrovnalostmi zapisuje kod cervene barvy
*: 2014-01-06: pokud jsou velka SK > 2GB, neimportuje se
*: 2014-03-17: pokud jsou velka SK > 2GB, neimportuje se a do logu je zapsana velikost nadmerneho souboru
*: 2014-06-10: neimportuji se uzaverky, zacinajici pismenem "O"
*: 2015-11-16: hlida prazdny id_man ve vstupnich datech
*:             do sk_sez nahrazen append prikazem insert into
*:             kdyz je nalezen radek s prazdnym sk_sez.id_man, je smazan, pokud pro tuto uzaverku nejsou zaznamy v ostatnich dbf
*: 2017-01-15: jine hledani drive importovanych uzaverek kvuli chybe v kase, ktera uzaverku misto RRMMDD HH:MM zapsala RRMMDDHHMMSS
*:             tyto fieldy uzaverka, pokud maji prazdny field ODECTENO, jsou opraveny
*: 2017-01-21: pridan import zurobj.dbf
*: 2017-03-03: osetrena situace, kdy existuje zurobj.dbf, ale z kas neni poslan sk_obj
*:             pridan import vahimp.dbf
*:
*:*********************************************************************************************************
param sk_path, pripona, imp_path

if empt(m.imp_path)
    imp_path = "c:\ftp\data\"
endif

imp_path = imp_path + IIF(RIGHT(m.imp_path,1)="\", "", "\")
sk_path  = sk_path  + IIF(RIGHT(m.sk_path,1) ="\", "", "\")
vysledek = " . . v.2017-0303: " + m.crlf

CLOSE DATABASES 

if file(m.imp_path + "sk_sez" + m.pripona + ".dbf")

    COPY FILE (m.imp_path + "sk_???" + m.pripona + ".*") to (m.pri_path + "*.*")
    sk_otevreno = ""

    DO kontrola_velikosti_eskacek    && pripadne prekroceni hlaseno v m.sk_otevreno

    LOCAL skin_oErr AS Exception
    TRY 
       use (m.pri_path + "SK_PLT" + m.pripona) excl
       use (m.pri_path + "SK_POL" + m.pripona) excl
       use (m.pri_path + "SK_FIN" + m.pripona) excl
       use (m.pri_path + "SK_TXT" + m.pripona) excl
       use (m.pri_path + "SK_SEZ" + m.pripona) excl
    CATCH TO skin_oErr 
       sk_otevreno = m.sk_otevreno + " nepristupna importovana SK " + m.pripona + " " + ALLTRIM(STR(skin_oErr.ErrorNo)) + " " + skin_oErr.Message + " Line:" + ALLTRIM(STR(skin_oErr.LineNo))
    FINALLY
       USE 
    ENDTRY 

    TRY
       IF FILE(m.sk_path + "zurobj.dbf") .and. FILE(m.pri_path + "SK_OBJ" + m.pripona)
          USE (m.sk_path + "ZUROBJ ") in 0 excl ALIAS sk_obj
       ENDIF 
       IF FILE(m.sk_path + "vahinv.dbf") .and. FILE(m.pri_path + "SK_VAH" + m.pripona)
          USE (m.sk_path + "vahinv ") in 0 excl ALIAS sk_vah
       ENDIF 
       USE (m.sk_path + "SK_PLT ")    in 0 excl
       USE (m.sk_path + "SK_POL ")    in 0 excl
       USE (m.sk_path + "SK_FIN ")    in 0 excl
       USE (m.sk_path + "SK_TXT ")    in 0 excl
       USE (m.sk_path + "SK_SEZ ")    in 0 excl
       USE (m.sk_path + "SK_SEZ_A ")  in 0 excl
    CATCH TO skin_oErr 
       sk_otevreno = m.sk_otevreno + " nelze otevrit velka SK " + ALLTRIM(STR(skin_oErr.ErrorNo)) + " " + skin_oErr.Message + " Line:" + ALLTRIM(STR(skin_oErr.LineNo))
    FINALLY
    ENDTRY 

    IF EMPTY(m.sk_otevreno)    
        DO kontrola_nedokoncenych_prenosu

        SELECT 0
        USE (m.pri_path + "sk_sez" + m.pripona) ALIAS sk_sez_x EXCL 
        DELETE FOR LEFT(uzaverka,1) = "O"
        PACK
        REPLACE ALL label WITH "X", odecteno WITH "SK_IN"

        SCAN 		&& nalezeni uzaverek, ktere jiz byly importovany
            SELECT sk_sez
            LOCATE FOR (LEFT(         uzaverka,6) +          id_kas +          autor +          lastuzav + STR(         uz_cislo,6)) = ;
                       (LEFT(sk_sez_x.uzaverka,6) + sk_sez_x.id_kas + sk_sez_x.autor + sk_sez_x.lastuzav + STR(sk_sez_x.uz_cislo,6)) nooptimize
            if found()
               sele sk_sez_x
               repl label with ""
               LOOP 
            ELSE
               sele sk_sez_x
               REPLACE priznak WITH  TTOC(DATETIME(),3)
            endif

            SELECT sk_sez_a
            LOCATE FOR (LEFT(         uzaverka,6) +          id_kas +          autor +          lastuzav + STR(         uz_cislo,6)) = ;
                       (LEFT(sk_sez_x.uzaverka,6) + sk_sez_x.id_kas + sk_sez_x.autor + sk_sez_x.lastuzav + STR(sk_sez_x.uz_cislo,6)) nooptimize
            if found()
               sele sk_sez_x
               repl label with ""
            endif

        ENDSCAN 
        DELETE FOR EMPTY(label)    && sk_sez_x selected
        PACK
        IF RECCOUNT() > 0

            inde on id_kas + uzaverka tag kas_uz
            o_uz = "UUII"
            SCAN
                IF o_uz # id_kas + uzaverka
                    IF o_uz # "UUII"
                        vysledek = m.vysledek + " )" + crlf
                    ENDIF 
                    vysledek = m.vysledek + PADL(id_kas, 11) + "  " + uzaverka + " ("
                    o_uz = id_kas + uzaverka
                ENDIF 
                vysledek = m.vysledek + " " + IIF(EMPTY(id_man), "CHYBI ID_MAN", id_man)       && aby bylo videt, kdyby byl id_man prazdny
            ENDSCAN        
            vysledek = m.vysledek + " )" + m.crlf

            INSERT INTO sk_sez (id_kas, id_man, uzaverka, autor, odecteno, uz_cislo, uctexport, c_mena, uct_den, lastuzav) ;
                        SELECT  id_kas, id_man, uzaverka, autor, odecteno, uz_cislo, uctexport, c_mena, uct_den, lastuzav FROM (m.pri_path + "sk_sez" + m.pripona)

            vysledek = m.vysledek + "    appendovano " + "SK_SEZ"

            DO jedensoubor with "sk_plt"
            DO jedensoubor with "sk_pol"
            DO jedensoubor with "sk_fin"
            DO jedensoubor with "sk_txt"
            IF USED("sk_obj")
                DO jedensoubor with "sk_obj"
            ENDIF 
            IF USED("sk_vah")
                DO jedensoubor with "sk_vah"
            ENDIF 
            
            SELECT sk_sez
            REPLACE  for odecteno = "SK_IN" odecteno WITH ""
            vysledek = m.vysledek + " ... OK"
            
            DO sk_in_kontrola
        ELSE
            vysledek = m.vysledek + "[1B]  " +  "   . . . . . . nejsou zadne nove uzaverky "
        ENDIF 
        DO kontrola_prazdneho_id_man
        vysledek = m.vysledek + "   ... "

    ELSE
        vysledek = m.vysledek + "[2#R]  " + "       ... " + m.sk_otevreno + "  "
    ENDIF
else

   vysledek = m.vysledek + "[2#R]  " + "       ... " + "neexistuje " + upper(m.imp_path + "sk_sez" + m.pripona) + "  ... "
endif
clos data
ERASE (m.pri_path + "sk_???" + m.pripona + ".*")


************************************************
PROCEDURE jedensoubor
PARAMETERS soubor

SELECT 0
USE (m.pri_path + m.soubor + m.pripona) excl 
IF FSIZE("id_kas") > 0
    SET RELATION TO id_kas + uzaverka into sk_sez_x
ELSE 
    SET RELATION TO LEFT(u_cis,2) + uzaverka into sk_sez_x
ENDIF 
DELETE FOR EMPTY(sk_sez_x.uzaverka)
PACK 

SELECT (m.soubor)
IF FSIZE("id_kas") > 0
    SET RELATION TO id_kas + uzaverka into sk_sez_x
ELSE 
    SET RELATION TO LEFT(u_cis,2) + uzaverka into sk_sez_x
ENDIF 
dele for !empt(sk_sez_x.uzaverka)
appe from (m.pri_path + m.soubor + m.pripona)

vysledek = m.vysledek + "  " + UPPER(m.soubor)


************************************************
PROCEDURE kontrola_nedokoncenych_prenosu
SELECT sk_sez
LOCATE FOR odecteno = "SK_IN"
IF FOUND()
   SCAN FOR odecteno = "SK_IN"
   SCATTER MEMVAR
 
       vysledek = m.vysledek + "[1B]          smazano z minula: " + m.uzaverka + "/" + m.id_kas + crlf
       DELETE FOR uzaverka = m.uzaverka .and. LEFT(u_cis,2) = m.id_kas IN sk_plt
       DELETE FOR uzaverka = m.uzaverka .and. id_kas = m.id_kas        IN sk_pol
       DELETE FOR uzaverka = m.uzaverka .and. id_kas = m.id_kas        IN sk_fin
       DELETE FOR uzaverka = m.uzaverka .and. id_kas = m.id_kas        IN sk_txt
       
       SELECT sk_sez
   ENDSCAN
   DELETE FOR odecteno = "SK_IN"
ENDIF

************************************************
PROCEDURE kontrola_prazdneho_id_man
  SELECT sk_sez
  SCAN FOR EMPTY(id_man) .and. EMPTY(odecteno)
      lze_smazat = ""
      SCATTER MEMVAR 

      SELECT sk_fin
      LOCATE FOR id_kas + uzaverka = m.id_kas + m.uzaverka
      lze_smazat = m.lze_smazat + IIF(FOUND(), " FIN", "")
      
      SELECT sk_plt
      LOCATE FOR LEFT(u_cis,2) + uzaverka = m.id_kas + m.uzaverka
      lze_smazat = m.lze_smazat + IIF(FOUND(), " PLT", "")
      
      SELECT sk_pol
      LOCATE FOR id_kas + uzaverka = m.id_kas + m.uzaverka
      lze_smazat = m.lze_smazat + IIF(FOUND(), " POL", "")
      
      SELECT sk_txt
      LOCATE FOR id_kas + uzaverka = m.id_kas + m.uzaverka
      lze_smazat = m.lze_smazat + IIF(FOUND(), " TXT", "")

      SELECT sk_sez
      IF EMPTY(m.lze_smazat)
         DELETE 
         vysledek = m.vysledek + crlf + "[2#R]  " + " prazdny id_man v SK_SEZ " + m.id_kas + " " + m.uzaverka
      ELSE
         vysledek = m.vysledek + crlf + "[2#R]  " + " prazdny id_man v SK_SEZ " + m.id_kas + " " + m.uzaverka + ", NELZE SMAZAT:" + m.lze_smazat
      ENDIF 
  ENDSCAN 


********************************************************************************************
PROCEDURE sk_in_kontrola
*    jako aliasy sk_??? jsou otevreny soubory v sk_path, cili data, DO kterych se importuje
*    jako aliasy sk_??? + <pripona> jsou otevreny importovane soubory v pri_path

IF FILE(m.pri_path + "sk_chk" + m.pripona + ".dbf")
    DELETE FILE (m.pri_path + "sk_???_kontrola")

    SELECT * FROM sk_fin INTO TABLE (m.pri_path + "sk_fin_kontrola") ;
           WHERE uzaverka + LEFT(u_cis,2) in (SELECT uzaverka + id_kas FROM (m.pri_path + "sk_chk" + m.pripona))

    SELECT * FROM sk_plt INTO TABLE (m.pri_path + "sk_plt_kontrola") ;
           WHERE uzaverka + LEFT(u_cis,2) in (SELECT uzaverka + id_kas FROM (m.pri_path + "sk_chk" + m.pripona))

    SELECT * FROM sk_pol INTO TABLE (m.pri_path + "sk_pol_kontrola") ;
           WHERE uzaverka + LEFT(u_cis,2) in (SELECT uzaverka + id_kas FROM (m.pri_path + "sk_chk" + m.pripona))

*    *    *    *    *    *    *    *    KONTROLA    *    *    *    *    *    *     zacatek
    SELECT uzaverka, u_cis, SUM(cena_slev) AS cena_fin, SUM(cena_celk) AS cena_celk, 1 as je_v_fin ;
           GROUP BY 1,2 FROM (m.pri_path + "sk_fin_kontrola") INTO TABLE (m.pri_path + "fin")

    SELECT uzaverka, u_cis, SUM(cena_pl) AS cena_plt, 1 as je_v_plt ;
           GROUP BY 1,2 FROM (m.pri_path + "sk_plt_kontrola") INTO TABLE (m.pri_path + "plt")

    SELECT uzaverka, u_cis, SUM(prachy) AS cena_pol, ;
           00000000.00 AS cena_plt, 00000000.00 AS cena_fin, 00000000.00 AS cena_celk, 01 as je_v_pol, 00 as je_v_fin, 00 as je_v_plt ;
           WHERE ! EMPT(k_odpoctu) ;
           GROUP BY 1,2 FROM (m.pri_path + "sk_pol_kontrola") INTO TABLE (m.pri_path + "pol")

    APPE FROM (m.pri_path + "fin")
    APPE FROM (m.pri_path + "plt")

    SELECT uzaverka, LEFT(u_cis,2) as id_kas, SUM(cena_pol) AS cena_pol2, ;
           SUM(cena_plt) AS cena_plt2, SUM(cena_fin) AS cena_fin2, SUM(cena_celk) AS cena_celk2, ;
           SUM(je_v_fin) AS je_v_fin2, SUM(je_v_pol) AS je_v_pol2, SUM(je_v_plt)  AS je_v_plt2, ;
           $0 AS cena_pol, $0 AS cena_plt, $0 AS cena_fin, $0 AS cena_celk, 000000 as je_v_pol, 000000 as je_v_fin, 000000 as je_v_plt ;
           GROUP BY 1,2 FROM (m.pri_path + "pol") INTO TABLE (m.pri_path + "sk_chk_kontrola")

******           sk_chk_kontrola.dbf se porovna s prijimanou sk_chk.dbf
    APPE FROM (m.pri_path + "sk_chk" + m.pripona)
    SELECT uzaverka, id_kas, ;
           SUM(cena_pol)  AS cena_pol,  SUM(cena_pol2)  AS cena_pol2, SUM(je_v_pol) AS je_v_pol, SUM(je_v_pol2) AS je_v_pol2, ;
           SUM(cena_plt)  AS cena_plt,  SUM(cena_plt2)  AS cena_plt2, SUM(je_v_plt) AS je_v_plt, SUM(je_v_plt2) AS je_v_plt2, ;
           SUM(cena_fin)  AS cena_fin,  SUM(cena_fin2)  AS cena_fin2, SUM(je_v_fin) AS je_v_fin, SUM(je_v_fin2) AS je_v_fin2, ;
           SUM(cena_celk) AS cena_celk, SUM(cena_celk2) AS cena_celk2 ;
           GROUP BY 1,2 FROM (m.pri_path + "sk_chk_kontrola") INTO TABLE (m.pri_path + "sk_chk_kontrola" + m.pripona)        &&&(m.sk_path + "sk_chk_kontrola" + m.pripona)

    SET FILTER TO (ABS(cena_pol - cena_pol2) > 1) .or. (je_v_pol # je_v_pol2) .or. ;
                  (ABS(cena_plt - cena_plt2) > 1) .or. (je_v_plt # je_v_plt2) .or. ;
                  (ABS(cena_fin - cena_fin2) > 1) .or. (je_v_fin # je_v_fin2) .or. ;
                  (ABS(cena_celk- cena_celk2) > 1)
    COUNT TO pocet_chyb
    IF m.pocet_chyb = 0
        vysledek = m.vysledek + crlf + "     SK_IN_KONTROLA... OK"
    ELSE
        SCAN
            vysledek = m.vysledek + crlf + "[2B#R]     SK_IN_KONTROLA: nesrovnalost v uzaverce " + uzaverka +  "/" + id_kas
            IF (ABS(cena_pol - cena_pol2) > 1) .or. (je_v_pol # je_v_pol2)
                vysledek = m.vysledek + " SK_POL"
            ENDIF 
            IF (ABS(cena_plt - cena_plt2) > 1) .or. (je_v_plt # je_v_plt2)
                vysledek = m.vysledek + " SK_PLT"
            ENDIF 
            IF (ABS(cena_fin - cena_fin2) > 1) .or. (je_v_fin # je_v_fin2)
                vysledek = m.vysledek + " SK_FIN"
            ENDIF 
            IF (ABS(cena_celk- cena_celk2) > 1)
                vysledek = m.vysledek + " CENA_CELK"
            ENDIF 
        ENDSCAN 
    ENDIF 
ELSE
   vysledek = m.vysledek + crlf + "[2#r]      SK_IN_KONTROLA: kontrolni soubor sk_chk" + UPPER(m.pripona) + " neexistuje"
ENDIF 

********************************************************************************************
PROCEDURE kontrola_velikosti_eskacek
  pocet = ADIR(qqq, m.sk_path + "sk_???.dbf")
  FOR iii = 1 TO m.pocet
     IF qqq(iii,2) > 2000000000
        sk_otevreno = sk_otevreno + " " + UPPER(m.sk_path + qqq(iii,1)) + " je prilis velky (" + ALLT(STR(qqq(iii,2),12)) + ") " + m.crlf
     ENDIF 
  ENDFOR 

