CREATE OR REPLACE FUNCTION food600.gen_stav_a(
	cstredisk integer,
	datinv bigint,
	cmena integer,
	meno character varying,
	cdata integer)
    RETURNS void
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE
AS $BODY$
DECLARE 
	karty CURSOR FOR SELECT c_data, c_stredisk, c_dokl, c_karty, operace, idr FROM food600.genstav WHERE menostlp=meno AND c_data=cdata;
	mcdata INTEGER;
	mcstredisk INTEGER;
	mcdokl INTEGER;
	mckarty INTEGER;
	mporadi INTEGER;
	moperace CHARACTER(2);
	pocet BIGINT DEFAULT 0;
	midr BIGINT;
	midriadok BIGINT;
BEGIN
/*Vymazanie pomocnej databazy*/
DELETE FROM food600.genstav WHERE menostlp=meno;
/*Do genstav naplnim zoznam kariet a maximalne cisla dokladov, na ktorych bol pohyb*/
IF cstredisk=0 THEN
	INSERT INTO food600.genstav (c_stredisk, c_karty, c_dokl, menostlp, c_data) SELECT obraty_a.c_stredisk,obraty_a.c_karty, MAX(obraty_a.c_dokl) AS c_dokl, meno as menostlp, c_data FROM food600.obraty_a WHERE obraty_a.c_dokl<datinv AND (SUBSTRING(obraty_a.operace FROM 1 FOR 1) in('P','V')) AND obraty_a.c_data=cdata GROUP BY obraty_a.c_stredisk, obraty_a.c_karty, obraty_a.c_data ;
ELSE
	INSERT INTO food600.genstav (c_stredisk, c_karty, c_dokl, menostlp, c_data) SELECT obraty_a.c_stredisk,obraty_a.c_karty, MAX(obraty_a.c_dokl) AS c_dokl, meno AS menostlp, c_data FROM food600.obraty_a WHERE obraty_a.c_stredisk=cstredisk  AND obraty_a.c_dokl<datinv AND (SUBSTRING(obraty_a.operace FROM 1 FOR 1) IN('P','V')) AND obraty_a.c_data=cdata GROUP BY obraty_a.c_stredisk, obraty_a.c_karty, obraty_a.c_data;
END IF;
/*Z dokladov dotiahnem operaciu a menu*/
UPDATE food600.genstav SET operace=doklady_a.operace, c_mena=doklady_a.c_mena from food600.doklady_a WHERE genstav.c_dokl=doklady_a.c_dokl and genstav.menostlp=meno AND doklady_a.c_data=genstav.c_data;
/*Zlucim VP a PP*/
UPDATE food600.genstav SET operace='PP' WHERE operace='VP' AND menostlp=meno;
/*Vyplnim idriadok kazdej karty na prislusnom doklade*/
OPEN karty;
LOOP
	FETCH karty INTO mcdata, mcstredisk, mcdokl, mckarty, moperace, midr;
	EXIT WHEN found = false;
	SELECT COUNT(c_karty) INTO pocet FROM food600.obraty_a WHERE c_data=mcdata AND c_stredisk=mcstredisk AND c_dokl=mcdokl AND c_karty=mckarty;
	IF pocet=1 THEN
		SELECT idriadok INTO midriadok FROM food600.obraty_a WHERE c_data=mcdata AND c_stredisk=mcstredisk AND c_dokl=mcdokl AND c_karty=mckarty;
	ELSE
		SELECT MAX(poradi) INTO mporadi FROM food600.obraty_a WHERE c_data=mcdata AND c_stredisk=mcstredisk AND c_dokl=mcdokl AND c_karty=mckarty;
		CASE  
			WHEN moperace='VK' THEN
				SELECT idriadok INTO midriadok FROM food600.obraty_a WHERE c_data=mcdata AND c_stredisk=mcstredisk AND c_dokl=mcdokl AND c_karty=mckarty AND poradi=mporadi;
			WHEN moperace<>'VK' THEN
				SELECT idriadok INTO midriadok FROM food600.obraty_a WHERE c_data=mcdata AND c_stredisk=mcstredisk AND c_dokl=mcdokl AND c_karty=mckarty AND poradi=mporadi AND mnozstvi>0;
				IF midriadok IS NULL THEN 
					SELECT idriadok INTO midriadok FROM food600.obraty_a WHERE c_data=mcdata AND c_stredisk=mcstredisk AND c_dokl=mcdokl AND c_karty=mckarty AND poradi=mporadi;
				END IF;

		END CASE;
	END IF;
	UPDATE food600.genstav SET idriadok=midriadok,min_zaso=pocet WHERE idr=midr;
END LOOP;
CLOSE karty;
/*Dotiahnem z obratov potrebne data - mnozstva, ceny, zostatky, ...*/
UPDATE food600.genstav SET zustatek= obraty_a.zustatek, cena_prum=obraty_a.cena_prum, cena=obraty_a.cena, mnozstvi=obraty_a.mnozstvi FROM food600.obraty_a WHERE genstav.idriadok=obraty_a.idriadok AND genstav.menostlp=meno;
/*Osetrenie prazdnych c_mena*/
UPDATE food600.genstav SET c_mena=cmena WHERE c_mena=NULL OR c_mena=0 AND menostlp=meno;
/*Prepocet cien na pozadovanu vystupnu menu*/
UPDATE food600.genstav SET cena_prum=food600.prep_meny(c_mena, cmena ,cena_prum) WHERE c_mena<> cmena AND menostlp=meno;
/*Prepisanie meny na pozadovanu vystupnu menu*/
UPDATE food600.genstav SET c_mena=cmena WHERE c_mena<>cmena AND menostlp=meno;
END
$BODY$;
