***********************   N A S T A V I T   ***********************
***********************   N A S T A V I T   ***********************
*	cesty k cenarum (m.p_c0 a m.p_c1)
*	zacatek cesty ke skladum (m.p_skl)
*	cestu k datum food600 - musi tam existovat nove databaze, na obsahu nezalezi. na velikosti taky ne :-)
*	volani procedury SKL1 pro vsechny sklady
*	v prevodu mkaumc nastavit, jaxe prevadeji ceny do karty.dbf
***********************   N A S T A V I T   ***********************
***********************   N A S T A V I T   ***********************

set safe off
set dele on
set excl on
set talk on
set date to german
clos data

***********************   N A S T A V I T   ***********************
m.p_skl = "J:\f5grand\d_man\"
m.p_c0  = "J:\f5grand\d_cen\a0\"
m.p_c1  = "J:\F5grand\D_CEN\A1\"
*m.p_c2  = "J:\F5grand\D_CEN\A2\"
*m.p_c3  = "J:\F5grand\D_CEN\A3\"

m.p_600 = "J:\d_grand\data\"

DO CASE
CASE ! EMPT(GETENV("TMP"))
  pri_path = allt(GETENV("TMP"))
CASE ! EMPT(GETENV("TEMP"))
  pri_path = allt(GETENV("TEMP"))
OTHER
  pri_path = "\tmp\"
ENDCASE
pri_path = pri_path + IIF(RIGHT(m.pri_path,1)="\", "", "\")


* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

copy file (m.p_c0 + "sk_???.*") to (m.p_600 + "*.*")

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

clos data
sele *, "a" as typ_skup, " " AS typ_kalk, " " as typ_skl, dan_1 as dan01 from (m.p_c0 + "mjidec") into table (m.pri_path + "ddd")
repl for ! empt(kalk) typ_skup with "K",typ_kalk with "K"
use (m.p_600 + "skupiny")
zap
appe from (m.pri_path + "ddd")
appe from (m.p_c1 + "mjidec")
*appe from (m.p_c2 + "mjidec")
*appe from (m.p_c3 + "mjidec")
repl for empt(typ_skup) typ_skl with "1"
REPLACE FOR typ_kalk="K" typ_skup WITH ""
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

clos data

sele nazev, druh, druh as druh_kalk, kod, kalk, pocet, obal as bar_kod1, 00000000 as c_karty ;
    from (m.p_c0 + "mkahlc") into table (m.pri_path + "ddd")
repl for empt(kalk) druh_kalk with ""
repl for !empt(kalk) druh with ""
use (m.p_600 + "karty")
zap
appe from (m.pri_path + "ddd")
sele 0
use (m.p_c1 + "mkahlc") alias hl1
scan
   scatt field nazev, druh, kod memvar
   sele karty
   locate for nazev = m.nazev
   if found()
      repl druh_skl with m.druh
   else
      insert into (m.p_600 + "karty") (nazev, druh_skl, kod) values (m.nazev, m.druh, allt(str(m.kod)))
   endif
   sele hl1
endscan
USE IN hl1

*!*	sele 0
*!*	use (m.p_c2 + "mkahlc") alias hl1
*!*	scan
*!*	   scatt field nazev, druh, kod memvar
*!*	   sele karty
*!*	   locate for nazev = m.nazev
*!*	   if found()
*!*	      repl druh_skl with m.druh
*!*	   else
*!*	      insert into (m.p_600 + "karty") (nazev, druh_skl, kod) values (m.nazev, m.druh, allt(str(m.kod)))
*!*	   endif
*!*	   sele hl1
*!*	endscan
*!*	USE IN hl1

*!*	sele 0
*!*	use (m.p_c3 + "mkahlc") alias hl1
*!*	scan
*!*	   scatt field nazev, druh, kod memvar
*!*	   sele karty
*!*	   locate for nazev = m.nazev
*!*	   if found()
*!*	      repl druh_skl with m.druh
*!*	   else
*!*	      insert into (m.p_600 + "karty") (nazev, druh_skl, kod) values (m.nazev, m.druh, allt(str(m.kod)))
*!*	   endif
*!*	   sele hl1
*!*	endscan
*!*	USE IN hl1

sele karty
repl all c_karty with recn()

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

clos data
use (m.p_600 + "partneri")
zap
appe from (m.p_c0 + "partneri")
appe from (m.p_c1 + "partneri")
*!*	appe from (m.p_c2 + "partneri")
*!*	appe from (m.p_c3 + "partneri")
repl all c_partner with recn()

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

clos data
use (m.p_600 + "karty")
set order to nazev
sele *, 000000 as c_karty, 000000 as c_poloz from (m.p_c0 + "mkapoc") into table (m.pri_path + "ddd")
set rela to nazev into karty
repl all c_karty with karty.c_karty
set rela to poloz into karty
repl all c_poloz with karty.c_karty

use (m.p_600 + "polozky")
zap
appe from (m.pri_path + "ddd")

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

clos data
use (m.p_600 + "karty")
set order to nazev
sele nazev, id_kas, id_man, prn_no, str(kod) as kod, rkod, druh, spart, 000000 as c_karty, ;
    cena1, cena2, cena3, cena4, cena5, cenb1, cenb2, cenb3, cenb4, cenb5, cenc1, cenc2, cenc3, cenc4, cenc5 ;
     from (m.p_c0 + "mkaumc") into table (m.pri_path + "ddd")
*!*	sele nazev, "02" AS id_kas, "07" aS Id_man,  str(kod) as kod, druh, spart, 000000 as c_karty, ;
*!*	    cena1, cena2, cena3, cena4, cena5 ;
*!*	     from (m.p_c1 + "mkaHLc") into table (m.pri_path + "ddd1")
SELECT DDD
*!*	APPEND FROM (m.pri_path + "ddd1")
*!*	USE IN DDD1

set rela to nazev into karty
repl all c_karty with karty.c_karty

***********************   N A S T A V I T   ***********************
repl for id_kas = "06" karty.cena01 with cenb1, karty.cena02 with cenb2, karty.cena03 with cenb3, karty.cena04 with cenb4, karty.cena05 with cenb5
repl for id_kas = "05" karty.cena01 with cenb1, karty.cena02 with cenb2, karty.cena03 with cenb3, karty.cena04 with cenb4, karty.cena05 with cenb5
repl for id_kas = "04" karty.cena01 with cenb1, karty.cena02 with cenb2, karty.cena03 with cenb3, karty.cena04 with cenb4, karty.cena05 with cenb5
repl for id_kas = "03" karty.cena01 with cenb1, karty.cena02 with cenb2, karty.cena03 with cenb3, karty.cena04 with cenb4, karty.cena05 with cenb5
repl for id_kas = "02" karty.cena01 with cenb1, karty.cena02 with cenb2, karty.cena03 with cenb3, karty.cena04 with cenb4, karty.cena05 with cenb5
repl for id_kas = "01" karty.cena01 with cenb1, karty.cena02 with cenb2, karty.cena03 with cenb3, karty.cena04 with cenb4, karty.cena05 with cenb5

repl for id_kas = "37" karty.cena11 with cenb1, karty.cena12 with cenb2, karty.cena13 with cenb3, karty.cena14 with cenb4, karty.cena15 with cenb5
repl for id_kas = "36" karty.cena11 with cenb1, karty.cena12 with cenb2, karty.cena13 with cenb3, karty.cena14 with cenb4, karty.cena15 with cenb5
repl for id_kas = "35" karty.cena11 with cenb1, karty.cena12 with cenb2, karty.cena13 with cenb3, karty.cena14 with cenb4, karty.cena15 with cenb5
repl for id_kas = "34" karty.cena11 with cenb1, karty.cena12 with cenb2, karty.cena13 with cenb3, karty.cena14 with cenb4, karty.cena15 with cenb5
repl for id_kas = "33" karty.cena11 with cenb1, karty.cena12 with cenb2, karty.cena13 with cenb3, karty.cena14 with cenb4, karty.cena15 with cenb5
repl for id_kas = "32" karty.cena11 with cenb1, karty.cena12 with cenb2, karty.cena13 with cenb3, karty.cena14 with cenb4, karty.cena15 with cenb5
repl for id_kas = "31" karty.cena11 with cenb1, karty.cena12 with cenb2, karty.cena13 with cenb3, karty.cena14 with cenb4, karty.cena15 with cenb5

use (m.p_600 + "umisteni")
zap
appe from (m.pri_path + "ddd")

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

clos data
use (m.p_600 + "bourak")
zap
sele 0
use (m.p_600 + "karty") order nazev

iii = adir(bbb, m.p_c0 + "bourak*.dbf")
for ii = 1 to m.iii
  fn_bour = strtr(bbb(m.ii, 1), ".DBF", "")
  sele *, 000000 as c_karty, 000000 as c_karty2, "  " as bour_skup ;
       from (m.p_c0 + m.fn_bour) into table (m.pri_path + "bour1")
  set rela to nazev into karty
  repl all c_karty with karty.c_karty
  set rela to nazev2 into karty
  repl all c_karty2 with karty.c_karty
  repl all bour_skup with subst(fn_bour, 8)
  sele bourak
  appe from (m.pri_path + "bour1")
endfor

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

clos data
use (m.p_600 + "karty") order nazev noupdate
sele 0
use (m.p_600 + "partneri") order partner noupdate

sele 0
use (m.p_600 + "sklad")
zap
sele 0
use (m.p_600 + "doklady") alias d_600
zap
sele 0
use (m.p_600 + "obraty") alias o_600
zap
sele 0
use (m.p_600 + "davky") alias davk_600
zap
sele 0
use (m.p_600 + "uzaverka") alias uzav_600 exclu
zap
sele *, space(17) as datum, space(25) as partner from (m.p_600 + "doklady") into table (m.pri_path + "ddd")
sele *, space(17) as datum                       from (m.p_600 + "obraty")  into table (m.pri_path + "ooo")
sele *, space(17) as d1,SPACE(17) as d2          from uzav_600  into table (m.pri_path + "uuu")

***********************   N A S T A V I T   ***********************
do skl1 with -41, m.p_skl + "41\"		&&&&	c_stredisk, cesta k sklad.dbf
do skl1 with -42, m.p_skl + "42\"		&&&&	c_stredisk, cesta k sklad.dbf
*do skl1 with -43, m.p_skl + "43\"		&&&&	c_stredisk, cesta k sklad.dbf
do skl1 with -44, m.p_skl + "44\"		&&&&	c_stredisk, cesta k sklad.dbf
*do skl1 with -45, m.p_skl + "45\"		&&&&	c_stredisk, cesta k sklad.dbf
*do skl1 with -46, m.p_skl + "46\"		&&&&	c_stredisk, cesta k sklad.dbf
do skl1 with -51, m.p_skl + "51\"		&&&&	c_stredisk, cesta k sklad.dbf
do skl1 with -52, m.p_skl + "52\"        &&&&    c_stredisk, cesta k sklad.dbf
do skl1 with -53, m.p_skl + "53\"        &&&&    c_stredisk, cesta k sklad.dbf
do skl1 with -54, m.p_skl + "54\"		&&&&	c_stredisk, cesta k sklad.dbf
do skl1 with -55, m.p_skl + "55\"		&&&&	c_stredisk, cesta k sklad.dbf
do skl1 with -61, m.p_skl + "61\"		&&&&	c_stredisk, cesta k sklad.dbf
do skl1 with -62, m.p_skl + "62\"		&&&&	c_stredisk, cesta k sklad.dbf
do skl1 with -63, m.p_skl + "63\"		&&&&	c_stredisk, cesta k sklad.dbf
do skl1 with -65, m.p_skl + "65\"		&&&&	c_stredisk, cesta k sklad.dbf
do skl1 with -66, m.p_skl + "66\"		&&&&	c_stredisk, cesta k sklad.dbf
do skl1 with -67, m.p_skl + "67\"		&&&&	c_stredisk, cesta k sklad.dbf
do skl1 with -68, m.p_skl + "68\"		&&&&	c_stredisk, cesta k sklad.dbf
do skl1 with -69, m.p_skl + "69\"		&&&&	c_stredisk, cesta k sklad.dbf
do skl1 with -70, m.p_skl + "70\"		&&&&	c_stredisk, cesta k sklad.dbf

sele ddd
set rela to partner into partneri
repl all c_partner with partneri.c_partner
set rela to
repl for "BOURAK" $ partner .or. "INVENTUR" $ partner .or. "PRECENEN" $ partner ;
     c_partner with c_stredisk 
repl for operace $ "PV VV" c_partner with - val(partner)
repl for operace = "VK" c_partner with - val (subst(partner, 5))
inde on datum tag datum
c_dokl = 0
scan
   c_dokl = m.c_dokl + 10
   gath field c_dokl memvar
endscan
sele ooo
set rela to datum into ddd
repl all c_dokl with ddd.c_dokl
SELECT uuu
SCAN ALL
	SELECT ddd
	LOCATE FOR datum = uuu.d1 .and. c_stredisk=uuu.c_stredisk
	IF FOUND()
		SELECT uuu
		REPLACE c_doklod WITH ddd.c_dokl
	ENDIF 
	SELECT ddd
	LOCATE FOR datum = uuu.d2 .and. c_stredisk=uuu.c_stredisk
	IF FOUND()
		SELECT uuu
		REPLACE c_dokldo WITH ddd.c_dokl
	ENDIF 
	SELECT ddd
	LOCATE FOR operace="KK" .and. SUBSTR(pz2,11,2)=PADL(ALLTRIM(uuu.mesiac),2,"0") .and. SUBSTR(pz2,14,2)=RIGHT(uuu.rok,2) .and. c_stredisk=uuu.c_stredisk
	IF FOUND()
		SELECT uuu
		replace c_dokl WITH ddd.c_dokl
	ENDIF
	SELECT uuu
ENDSCAN 



sele d_600
appe from (m.pri_path + "ddd")
sele o_600
appe from (m.pri_path + "ooo")
sele uzav_600
appe from (m.pri_path + "uuu")

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

sele * from (m.p_600 + "doklady") where operace = "PV" into table (m.pri_path + "pom")
inde on doklad*100000 + c_partner tag dp
sele d_600
set rela to doklad*100000 + c_stredisk into pom
repl for pom.doklad # 0 pz2 with str(pom.c_dokl ,15)

sele * from (m.p_600 + "doklady") where operace = "VV" into table (m.pri_path + "pom")
inde on doklad*100000 + c_partner tag dp
sele d_600
set rela to doklad*100000 + c_stredisk into pom
repl for pom.doklad # 0 pz2 with str(pom.c_dokl ,15)

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

sele d_600
set order to c_dokl

sele c_dokl, poradi, cena, 00000000 as c_prij from (m.p_600 + "obraty") where operace = "VV" into table (m.pri_path + "pom")
set rela to c_dokl into d_600
repl all c_prij with val(d_600.pz2)
set rela to
index on c_prij * 100000 + poradi tag cp

sele o_600
set rela to c_dokl * 100000 + poradi into pom
repl for ! empt(pom.cena) cena_part with pom.cena

repl for operace = "VV" cena_part with cena

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

sele c_dokl, poradi, cena where inlist(operace, "VB", "PB") and mnozstvi < 0 ;
     from (m.p_600 + "obraty") into table (m.pri_path + "pom")
index on c_dokl * 10000 + poradi tag cp
sele o_600
set filt to inlist(operace, "VB", "PB") and mnozstvi > 0
set rela to c_dokl * 10000 + poradi into pom
repl for ! empt(pom.cena) cena_part with pom.cena


* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
proc skl1
param c_stredisk, adresar
sele *, m.c_stredisk as c_stredisk, 000000 as c_karty from (m.adresar + "sklad") into table (m.pri_path + "skl")
set rela to nazev into karty
repl all c_karty with karty.c_karty
sele sklad
appe from (m.pri_path + "skl")

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

sele *, m.c_stredisk as c_stredisk, 000000 as c_karty ;
     where stornovano = .f. from (m.adresar + "obraty") into table (m.pri_path + "obr")
set rela to nazev into karty
repl all c_karty with karty.c_karty
repl all operace with left(operace, 1) + right(operace, 1)
repl for operace = "PL" operace with "PV"
repl for operace = "PP" operace with "PC"    && preceneni
repl for operace = "VP" operace with "VC"    && preceneni
repl for operace = "PO" operace with "PP"    && polotovary
repl for operace = "VO" operace with "VP"    && polotovary
repl for operace = "VL" operace with "VV"
repl for operace = "VJ" operace with "VE"
sele ooo
appe from (m.pri_path + "obr")

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

sele *, m.c_stredisk as c_stredisk, fakturovan as pz1, SPACE(15) as pz2 from (m.adresar + "doklady") into table (m.pri_path + "dok")
repl all operace with left(operace, 1) + right(operace, 1), ;
         datum_dokl with ctod(subst(datum, 7,2) + "." + subst(datum, 5,2) + "." + subst(datum, 1,4))
repl for operace = "PL" operace with "PV"
repl for operace = "PP" operace with "PC"    && preceneni
repl for operace = "VP" operace with "VC"    && preceneni
repl for operace = "PO" operace with "PP"    && polotovary
repl for operace = "VO" operace with "VP"    && polotovary
repl for operace = "VL" operace with "VV"
repl for operace = "VJ" operace with "VE"
repl for operace = "KC" operace with "KK"
REPLACE pz2 WITH doklad FOR operace="KK"
sele ddd
appe from (m.pri_path + "dok")

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

SELECT poc_stav as ps, pripr as pr,prije as pe,vydej as ve,privl as pv, vydvl as vv,pri_i as pi,pri_p as pp,vyd_p as vp,pri_b as pb, vyd_b as vb,pri__ as pc,vyd__ as vc,vyd_k as vk,pri_o as po, vyd_o as vo, vydvr as vr, err_zaokru as err, kon_stav as ks,d1,d2,padl(alltrim(mesiac),2,"0") as mesiac,rok, datum as datum_dokl,m.c_stredisk as c_stredisk, 0000000000 as c_doklod, 0000000000 as c_dokldo,0000000 as c_dokl FROM (m.adresar + "uzaver") INTO TABLE (m.pri_path+"uzaverka")
SELECT uuu
APPEND FROM (m.pri_path+"uzaverka")

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

sele *, m.c_stredisk as c_stredisk, 000000 as c_karty from (m.adresar + "davky") into table (m.pri_path + "davk")
set rela to nazev into karty
repl all c_karty with karty.c_karty
sele davk_600
appe from (m.pri_path + "davk")
