CREATE OR REPLACE FUNCTION food600.kalkuluj_vyroba(
	mmeno character varying,
	mmenaakt integer,
	cdata integer,
	cstredisk integer)
    RETURNS integer
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE
AS $BODY$
DECLARE mhloubka INTEGER DEFAULT 0;
	pokracovat INTEGER DEFAULT 1;
	pocetriadkov INTEGER DEFAULT 0;
BEGIN
--INSERT INTO food600.odpocet_askl (c_karty, k_odpoctu, cena_prum, c_mena, c_data, menobuf, zustatek, prepocet) SELECT c_karty, zustatek as k_odpoctu, food600.prep_meny(sklad.c_mena, mmenaakt, sklad.cena_prum) as cena_prum, c_mena, cdata as c_data, mmeno as menobuf, cena_prum, 1 as prepocet FROM food600.sklad WHERE c_stredisk=cstredisk AND c_data=cdata;
--Oznacenie vycislenych poloziek, 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=cstredisk AND sklad.vycisleni<>'';
UPDATE food600.odp_log SET kalk='K' FROM food600.polozky WHERE odp_log.c_karty=polozky.c_karty AND odp_log.c_data=polozky.c_data AND odp_log.menobuf=mmeno AND odp_log.c_data=cdata AND odp_log.hloubka=0;
--Zapis poloziek na odpocet na tejto urovni do SK1
INSERT INTO food600.sk1 (c_karty, mnozstvi, odecteno, menobuf, c_data, hloubka, o_c_karty, o_mnozstvi) SELECT c_karty, mnozstvi, mnozstvi as odecteno, menobuf, c_data, hloubka+1 AS hloubka, c_karty as o_c_karty, mnozstvi as o_mnozstvi FROM food600.sk1 WHERE menobuf=mmeno AND c_data=cdata AND hloubka=0 AND vycisleni=1;
INSERT INTO food600.sk1 (c_karty, mnozstvi, odecteno, menobuf, c_data, hloubka, o_c_karty, o_mnozstvi) SELECT c_karty, mnozstvi, mnozstvi as odecteno, menobuf, c_data, hloubka+1 AS hloubka, c_karty as o_c_karty, mnozstvi as o_mnozstvi FROM food600.sk1 WHERE menobuf=mmeno AND c_data=cdata AND hloubka=0 AND vycisleni=0 AND NOT (c_karty IN (SELECT DISTINCT c_karty FROM food600.polozky WHERE c_data=cdata));
WHILE pokracovat =1 LOOP
	mhloubka:=mhloubka+1;
	IF mhloubka<10 THEN
		DELETE FROM food600.odpocet_kalk WHERE menobuf=mmeno AND c_data=cdata;
--Rozbitie mnozstiev z minulej urovne cez kalkulacie
		INSERT INTO food600.odpocet_kalk (mnozstvi, hruba, c_karty, c_poloz, menobuf, c_data, o_c_karty, cista) SELECT sk1.mnozstvi, ROUND(CAST(polozky.hruba*sk1.mnozstvi/karty.pocet AS NUMERIC),4) AS hruba, sk1.c_karty AS c_karty, polozky.c_poloz, sk1.menobuf, sk1.c_data, sk1.c_karty AS o_c_karty, ROUND(CAST(polozky.cista*sk1.mnozstvi/karty.pocet AS NUMERIC),4) AS cista 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;
--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;
		IF pocetriadkov>0 THEN
--Zapis poloziek na odpocet na tejto urovni do SK1
			INSERT INTO food600.sk1 (c_karty, mnozstvi, odecteno, menobuf, c_data, hloubka, o_c_karty, o_mnozstvi) SELECT c_poloz as c_karty, sum(all hruba) AS mnozstvi, sum(all cista) as odecteno, mmeno as menobuf, cdata as c_data, mhloubka AS hloubka, c_karty as o_c_karty, mnozstvi as o_mnozstvi FROM food600.odpocet_kalk WHERE menobuf=mmeno AND c_data=cdata GROUP BY c_poloz, menobuf, c_data, hloubka, c_karty, mnozstvi;
--Zapis hlaviciek na odpocet na tejto urovni do logu
			INSERT INTO food600.odp_log (c_karty, hruba, o_c_karty, hloubka, menobuf, c_data, cista, o_mnozstvi) 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, ROUND(CAST(polozky.cista*sk1.mnozstvi/karty.pocet AS NUMERIC),4) AS cista, sk1.mnozstvi as o_mnozstvi 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;
--Spocitanie odpocitaneho mnozstva na tejto urovni po kartach
			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=cstredisk AND sklad.vycisleni<>'' AND sk1.hloubka=mhloubka;
			UPDATE food600.odp_log SET kalk='K' FROM food600.polozky WHERE odp_log.c_karty=polozky.c_karty AND odp_log.c_data=polozky.c_data AND odp_log.menobuf=mmeno AND odp_log.c_data=cdata AND odp_log.hloubka=mhloubka;
		ELSE
			pokracovat:=0;
		END IF;
	ELSE
		pokracovat:=0;
	END IF;
END LOOP;
DELETE FROM food600.odpocet_kalk WHERE menobuf=mmeno AND c_data=cdata;
DELETE FROM food600.sk1 WHERE menobuf=mmeno AND c_data=cdata AND hloubka>1;
RETURN 1;
END
$BODY$;
