CREATE OR REPLACE FUNCTION food600.kalkuluj(
	mmeno character varying,
	mcstredisk integer,
	mmenaakt integer,
	cdata integer,
	mchl character varying,
	midkas character varying,
	muzaverka character varying,
	ajinventura smallint)
    RETURNS integer
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE
AS $BODY$
DECLARE mhloubka INTEGER DEFAULT 0;
	pokracovat INTEGER DEFAULT 1;
	pocetriadkov INTEGER DEFAULT 0;
	poradie INTEGER;
	mcpartner INTEGER;
	mdatdokl DATE;
	sumaodcitane numeric(20,10) DEFAULT 0;
BEGIN
--Odpocet nultej urovne
INSERT INTO food600.odpocet_askl (menobuf, c_karty, c_data, c_stredisk) SELECT menobuf, c_karty, c_data, mcstredisk as c_stredisk FROM food600.sk1 WHERE sk1.menobuf=mmeno AND sk1.c_data=cdata EXCEPT SELECT menobuf, c_karty, c_data, c_stredisk FROM food600.odpocet_askl WHERE menobuf=mmeno AND c_data=cdata AND c_stredisk=mcstredisk;
PERFORM food600.prep_odpocet(mmeno, cdata, mmenaakt, mcstredisk, ajinventura::smallint);
UPDATE food600.sk1 SET odecteno=LEAST(sk1.mnozstvi, GREATEST(odpocet_askl.k_odpoctu, 0)) FROM food600.odpocet_askl WHERE sk1.c_karty=odpocet_askl.c_karty AND sk1.c_data=odpocet_askl.c_data AND sk1.menobuf=odpocet_askl.menobuf AND sk1.menobuf=mmeno AND sk1.c_data=cdata AND odpocet_askl.c_stredisk=mcstredisk;
UPDATE food600.sk1 SET mnozstvi=sk1.mnozstvi-sk1.odecteno WHERE sk1.menobuf=mmeno AND sk1.c_data=cdata AND sk1.hloubka=mhloubka;
--Uprava stavov v odpocet_askl o odpocitane mnozstvo z nultej urovne
UPDATE food600.odpocet_askl SET k_odpoctu=k_odpoctu-sk1.odecteno FROM food600.sk1 WHERE odpocet_askl.c_karty=sk1.c_karty AND odpocet_askl.menobuf=sk1.menobuf AND odpocet_askl.c_data=sk1.c_data AND odpocet_askl.menobuf=mmeno AND odpocet_askl.c_data=cdata AND odpocet_askl.c_stredisk=mcstredisk;
-- Zapis odpoctu z nultej urovne do logu
INSERT INTO food600.odp_log (c_karty, hruba, hloubka, o_nazev, menobuf, c_data) SELECT c_karty, odecteno AS hruba, mhloubka+1 as hloubka, '>>>  ODECTENO  <<<' as o_nazev, mmeno as menobuf, cdata as c_data FROM food600.sk1 WHERE sk1.menobuf=mmeno AND sk1.c_data=cdata AND sk1.odecteno>0 AND sk1.hloubka=0;
--Oznacenie vycislenych poloziek na odpocet, aby sa na dalsej urovni nerozbili na polozky
UPDATE food600.sk1 SET vycisleni=1 FROM food600.sklad WHERE sk1.c_karty=sklad.c_karty AND sk1.c_data=sklad.c_data AND sk1.menobuf=mmeno AND sk1.c_data=cdata AND sklad.c_stredisk=mcstredisk AND sklad.vycisleni<>'';
WHILE pokracovat =1 LOOP
	mhloubka:=mhloubka+1;
	IF mhloubka<10 THEN
		DELETE FROM food600.odpocet_kalk WHERE menobuf=mmeno AND c_data=cdata;
--Rozbitie neodpocitanych mnozstiev z minulej urovne cez kalkulacie
		INSERT INTO food600.odpocet_kalk (mnozstvi, hruba, c_karty, c_poloz, menobuf, c_data) SELECT sk1.mnozstvi, ROUND(CAST(polozky.hruba*sk1.mnozstvi/karty.pocet AS NUMERIC),4) AS hruba, sk1.c_karty, polozky.c_poloz, sk1.menobuf, sk1.c_data FROM food600.sk1 INNER JOIN food600.karty ON sk1.c_karty=karty.c_karty AND sk1.c_Data=karty.c_data INNER JOIN food600.polozky ON sk1.c_karty=polozky.c_karty AND sk1.c_data=polozky.c_data WHERE sk1.menobuf=mmeno AND sk1.c_data=cdata AND polozky.c_poloz>0 AND polozky.hruba<>0 AND karty.pocet<>0 AND sk1.vycisleni=0 AND sk1.mnozstvi<>0 AND sk1.hloubka=mhloubka-1;
--Zapis poloziek na odpocet na tejto urovni do logu
		INSERT INTO food600.odp_log ( o_c_karty, hloubka, o_mnozstvi, menobuf, c_data) SELECT sk1.c_karty, mhloubka as hloubka, sk1.mnozstvi as o_mnozstvi, menobuf, c_data FROM food600.sk1 WHERE sk1.menobuf=mmeno AND sk1.c_data=cdata AND sk1.vycisleni=0 AND sk1.mnozstvi<>0 AND sk1.hloubka=mhloubka-1;
		SELECT COUNT(*) INTO pocetriadkov FROM food600.odpocet_kalk;
		IF pocetriadkov>0 THEN
--Zapis poloziek na odpocet na tejto urovni do SK1
			INSERT INTO food600.sk1 (c_karty, mnozstvi, menobuf, c_data, hloubka) SELECT c_poloz as c_karty, sum(all hruba) AS mnozstvi, mmeno as menobuf, cdata as c_data, mhloubka AS hloubka FROM food600.odpocet_kalk WHERE menobuf=mmeno AND c_data=cdata GROUP BY c_poloz, menobuf, c_data, hloubka;
--Zapis hlaviciek na odpocet na tejto urovni do logu
			INSERT INTO food600.odp_log (c_karty, hruba, o_c_karty, hloubka, menobuf, c_data) SELECT polozky.c_poloz, ROUND(CAST(polozky.hruba*sk1.mnozstvi/karty.pocet AS NUMERIC),4) AS hruba, sk1.c_karty as o_c_karty, mhloubka as hloubka, sk1.menobuf, sk1.c_data FROM food600.sk1 INNER JOIN food600.karty ON sk1.c_karty=karty.c_karty AND sk1.c_Data=karty.c_data INNER JOIN food600.polozky ON sk1.c_karty=polozky.c_karty AND sk1.c_data=polozky.c_data WHERE sk1.menobuf=mmeno AND sk1.c_data=cdata AND polozky.c_poloz>0 AND polozky.hruba<>0 AND karty.pocet<>0 AND sk1.vycisleni=0 AND sk1.mnozstvi<>0 AND sk1.hloubka=mhloubka-1;
--Vynulovanie neodpocitanch mnozstiev z minulej urovne (uz su rozbite na polozky)
			UPDATE food600.sk1 SET mnozstvi=0 FROM food600.odpocet_kalk WHERE sk1.c_karty=odpocet_kalk.c_karty AND sk1.menobuf=mmeno AND sk1.c_data=cdata AND sk1.hloubka=mhloubka-1;
--Odpocet aktualnej urovne
			INSERT INTO food600.odpocet_askl (menobuf, c_karty, c_data, c_stredisk) SELECT menobuf, c_karty, c_data, mcstredisk as c_stredisk FROM food600.sk1 WHERE sk1.menobuf=mmeno AND sk1.c_data=cdata AND sk1.hloubka=mhloubka EXCEPT SELECT menobuf, c_karty, c_data, c_stredisk FROM food600.odpocet_askl WHERE menobuf=mmeno AND c_data=cdata AND c_stredisk=mcstredisk;
			PERFORM food600.prep_odpocet(mmeno, cdata, mmenaakt, mcstredisk, ajinventura::smallint);
			UPDATE food600.sk1 SET odecteno=LEAST(sk1.mnozstvi, GREATEST(odpocet_askl.k_odpoctu, 0)) FROM food600.odpocet_askl WHERE sk1.c_karty=odpocet_askl.c_karty AND sk1.c_data=odpocet_askl.c_data AND sk1.menobuf=odpocet_askl.menobuf AND sk1.menobuf=mmeno AND sk1.c_data=cdata AND sk1.hloubka=mhloubka AND odpocet_askl.c_stredisk=mcstredisk;
			UPDATE food600.sk1 SET mnozstvi=sk1.mnozstvi-sk1.odecteno WHERE sk1.menobuf=mmeno AND sk1.c_data=cdata AND sk1.hloubka=mhloubka;
--Spocitanie odpocitaneho mnozstva na tejto urovni po kartach
			DELETE FROM food600.odpocet_kalk WHERE menobuf=mmeno AND c_data=cdata;
--Uprava stavov v odpocet_askl o odpocitane mnozstvo z tejto urovne
			INSERT INTO food600.odpocet_kalk (mnozstvi, c_karty, menobuf, c_data) SELECT sum(odecteno) as mnozstvi, sk1.c_karty, sk1.menobuf, sk1.c_data FROM food600.sk1 WHERE sk1.menobuf=mmeno AND sk1.c_data=cdata AND sk1.hloubka=mhloubka GROUP BY c_karty, menobuf,c_data;
			UPDATE food600.odpocet_askl SET k_odpoctu=k_odpoctu-odpocet_kalk.mnozstvi FROM food600.odpocet_kalk WHERE odpocet_askl.c_karty=odpocet_kalk.c_karty AND odpocet_askl.menobuf=odpocet_kalk.menobuf AND odpocet_askl.c_data=odpocet_kalk.c_data AND odpocet_askl.menobuf=mmeno AND odpocet_askl.c_data=cdata;
			DELETE FROM food600.odpocet_kalk WHERE menobuf=mmeno AND c_data=cdata;
--Zapis odpoctu z tejto urovne do logu
			INSERT INTO food600.odp_log (c_karty, hruba, hloubka, o_nazev, menobuf, c_data) SELECT c_karty, odecteno AS hruba, mhloubka+1 as hloubka, '>>>  ODECTENO  <<<' as o_nazev, mmeno as menobuf, cdata as c_data FROM food600.sk1 WHERE sk1.menobuf=mmeno AND sk1.c_data=cdata AND sk1.odecteno>0 AND sk1.hloubka=mhloubka;
--Oznacenie vycislenych poloziek na odpocet, aby sa na dalsej urovni nerozbili na polozky
			UPDATE food600.sk1 SET vycisleni=1 FROM food600.sklad WHERE sk1.c_karty=sklad.c_karty AND sk1.c_data=sklad.c_data AND sk1.menobuf=mmeno AND sk1.c_data=cdata AND sklad.c_stredisk=mcstredisk AND sklad.vycisleni<>'';
		ELSE
			pokracovat:=0;
		END IF;
	ELSE
		pokracovat:=0;
	END IF;
END LOOP;
DELETE FROM food600.odpocet_kalk WHERE menobuf=mmeno AND c_data=cdata;
--Vyhladanie posledneho odpoctu v bufferd
SELECT MAX(bufferd.bufdidr) INTO poradie FROM food600.bufferd WHERE menobuf=mmeno AND c_data=cdata;
SELECT SUM(mnozstvi+odecteno) INTO sumaodcitane FROM food600.sk1 WHERE menobuf=mmeno AND c_data=cdata;
IF sumaodcitane<>0 THEN
	IF poradie IS NULL THEN
		poradie:=1;
	ELSE
		poradie:=poradie+1;
	END IF;
	SELECT c_stredisk INTO mcpartner FROM food600.food_dat WHERE id=midkas AND c_data=cdata;
	IF mcpartner IS NULL THEN
		mcpartner:=mcstredisk;
	END IF;
	SELECT uct_den INTO mdatdokl FROM food600.sk__sez WHERE uzaverka=muzaverka AND c_data=cdata;
	IF mdatdokl IS NULL THEN
		mdatdokl:=to_date(substring(muzaverka,7,2)||substring(muzaverka,5,2)||substring(muzaverka,1,4), 'DDMMYYYY');
	END IF;
--Zapis do buffer a bufferd
	INSERT INTO food600.bufferd (bufdidr, operace, c_mena, pz1, pz2, datum_dokl, cen_hlad, kdy, menobuf, c_data, c_stredisk, c_partner) VALUES (poradie, 'VK', mmenaakt, 'K'||midkas, SUBSTR(muzaverka,3,12)||'/'|| midkas, mdatdokl, mchl, current_timestamp, mmeno, cdata, mcstredisk, mcpartner);
	INSERT INTO food600.buffer (bufdidr, c_karty, mnozstvi, dluh_mnoz, menobuf, c_data, c_stredisk) SELECT poradie as bufdidr, c_karty, SUM(ALL ROUND(CAST(odecteno AS NUMERIC),4)) as mnozstvi, SUM(ALL ROUND(CAST(mnozstvi AS NUMERIC),4)) as dluh_mnoz, menobuf, c_data, mcstredisk as c_stredisk FROM food600.sk1 WHERE menobuf=mmeno AND c_data=cdata GROUP BY bufdidr, c_karty, menobuf, c_data;
	DELETE FROM food600.buffer WHERE menobuf=mmeno AND c_data=cdata AND mnozstvi=0 AND dluh_mnoz=0;
	UPDATE food600.buffer SET cena=odpocet_askl.cena_prum , operace='VK', label='Q' FROM food600.odpocet_askl WHERE buffer.menobuf=odpocet_askl.menobuf AND buffer.c_data=odpocet_askl.c_data AND buffer.c_karty=odpocet_askl.c_karty AND buffer.menobuf=mmeno AND buffer.c_data=cdata AND odpocet_askl.c_stredisk=mcstredisk AND buffer.operace<>'VK';
END IF;
RETURN 1;
END
$BODY$;
