CREATE OR REPLACE FUNCTION food600.kalkuluj_polotovary(
	mmeno character varying,
	mcstredisk integer,
	mmenaakt integer,
	cdata integer,
	narast integer,
	mckarty integer)
    RETURNS integer
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE
AS $BODY$
DECLARE mhloubka INTEGER DEFAULT 0;
	pokracovat INTEGER DEFAULT 1;
	pocetriadkov INTEGER DEFAULT 0;
	sumaodcitane numeric(20,10) DEFAULT 0;
BEGIN
--Odpocet nultej urovne
INSERT INTO food600.odpocet_askl (menobuf, c_karty, c_data) SELECT menobuf, c_karty, c_data FROM food600.sk1 WHERE sk1.menobuf=mmeno AND sk1.c_data=cdata EXCEPT SELECT menobuf, c_karty, c_data FROM food600.odpocet_askl WHERE menobuf=mmeno AND c_data=cdata;
PERFORM food600.prep_odpocet(mmeno, cdata, mmenaakt, mcstredisk);
IF narast=1 THEN
	UPDATE food600.odpocet_askl SET k_odpoctu=0 WHERE c_karty=mckarty and menobuf=mmeno and c_data=cdata;
END IF;
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;
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;
--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;
		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;
--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) SELECT menobuf, c_karty, c_data FROM food600.sk1 WHERE sk1.menobuf=mmeno AND sk1.c_data=cdata AND sk1.hloubka=mhloubka EXCEPT SELECT menobuf, c_karty, c_data FROM food600.odpocet_askl WHERE menobuf=mmeno AND c_data=cdata;
			PERFORM food600.prep_odpocet(mmeno, cdata, mmenaakt, mcstredisk);
			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;
			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;
--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;
--Zapis do buffer
INSERT INTO food600.buffer (bufdidr, c_karty, mnozstvi, dluh_mnoz, menobuf, c_data, c_stredisk, poradi) SELECT 0 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, mckarty as poradi FROM food600.sk1 WHERE menobuf=mmeno AND c_data=cdata GROUP BY bufdidr, c_karty, menobuf, c_data, poradi;
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='VP', 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;
RETURN 1;
END
$BODY$;
