CREATE OR REPLACE FUNCTION food600.prep1po(
	mmenobuf character varying,
	cdata integer,
	mmenaakt integer,
	podokl bigint,
	cstredisk integer)
    RETURNS void
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE
AS $BODY$
DECLARE zapisuj INTEGER DEFAULT 0;
moddokladu BIGINT DEFAULT podokl;
pocetkariet BIGINT DEFAULT 0;
mdone2 BIGINT DEFAULT 0;
ckarty BIGINT DEFAULT 0;
mc_mena INTEGER;
mc_mena_na INTEGER;
mzostatok numeric(20,10);
mnovacena numeric(20,10);
zaokruhlenie INTEGER DEFAULT 10;
prep2 CURSOR FOR SELECT DISTINCT c_karty FROM food600.buffer WHERE menobuf=mmenobuf AND c_data=cdata;
BEGIN

IF zapisuj=1 THEN 
	insert into food600.test (cas,text) values (clock_timestamp(),'MODDOKLADU:' || moddokladu);
	insert into food600.test (cas,text) values (clock_timestamp(),'MMENOBUF:' || mmenobuf);
END IF;

SELECT COUNT(DISTINCT c_karty) INTO pocetkariet FROM food600.buffer WHERE menobuf=mmenobuf AND c_data=cdata;
IF zapisuj=1 THEN 
	insert into food600.test (cas,text) values (clock_timestamp(),'POCETKARIET:' || pocetkariet);
END IF;
/*Vymazanie pomocnej databazy*/
IF moddokladu>0 THEN
	DELETE FROM food600.genstav WHERE menostlp=mmenobuf;
	DELETE FROM food600.genstavq WHERE menostlp=mmenobuf;
	DELETE FROM food600.genstavqqq WHERE menostlp=mmenobuf;
/*Vytvorenie druhej pomocnej databazy, kde sa z dokladu, na ktorom bol posledny pohyb, vyberie posledne poradie kazdej karty (hlavne pre PV a PE, kde moze byt karta na doklade aj viackrat)*/
	INSERT INTO food600.genstavq SELECT obraty.c_stredisk,obraty.c_karty, obraty.c_dokl, MAX(obraty.poradi) as poradi, genstavqq.menostlp FROM (SELECT obraty.c_stredisk,obraty.c_karty, MAX(obraty.c_dokl) AS c_dokl, mmenobuf as menostlp FROM food600.obraty INNER JOIN food600.buffer ON obraty.c_karty=buffer.c_karty WHERE obraty.c_dokl<=moddokladu AND (SUBSTRING(obraty.operace FROM 1 FOR 1) in('P','V')) AND obraty.c_data=cdata AND obraty.c_stredisk=cstredisk AND buffer.menobuf=mmenobuf GROUP BY obraty.c_stredisk, obraty.c_karty) genstavqq LEFT JOIN food600.obraty ON obraty.c_stredisk=genstavqq.c_stredisk AND obraty.c_dokl=genstavqq.c_dokl AND obraty.c_karty=genstavqq.c_karty GROUP BY obraty.c_stredisk, obraty.c_dokl,obraty.c_karty, genstavqq.menostlp;
/*Vymazanie pomocnej databazy*/
	INSERT INTO food600.genstav SELECT obraty.c_karty, obraty.c_dokl, obraty.zustatek, obraty.cena_prum, obraty.cena , obraty.c_stredisk, doklady.c_mena as c_mena, obraty.operace, obraty.poradi, obraty.mnozstvi, 0 as min_zaso, cdata AS c_data, genstavq.menostlp FROM food600.genstavq LEFT JOIN food600.obraty  ON genstavq.c_stredisk=obraty.c_stredisk  AND genstavq.c_dokl=obraty.c_dokl AND genstavq.c_karty=obraty.c_karty AND genstavq.poradi=obraty.poradi LEFT JOIN food600.doklady on genstavq.c_dokl=doklady.c_dokl WHERE genstavq.menostlp=mmenobuf;
/*Osetrenie prazdnych c_mena*/
	UPDATE food600.genstav SET c_mena=mmenaakt WHERE c_mena=NULL OR c_mena=0 AND menostlp=mmenobuf;
/*Osetrenie vyroby polotovarov - na jednom doklade existuju dve operacie a potom insert o par riadkov niysie neprejde*/
	UPDATE food600.genstav SET operace='PP' WHERE operace='VP' AND menostlp=mmenobuf;
/*Pre precenenie zmazanie riadkov s minusovym mnozstvom*/
	DELETE FROM food600.genstav WHERE operace='PC' AND mnozstvi<0;
/*Vymazanie pomocnej databazy*/
	DELETE FROM food600.genstavq WHERE menostlp=mmenobuf;
/*Vytvorenie tretej pomocnej databazy s dokladmi, kde je karta v rovnakom poradi dvakrat (hlavne buranie a polotovary)*/
	INSERT INTO food600.genstavqqq  SELECT c_stredisk,c_dokl,c_karty,poradi,operace,c_mena, mmenobuf as menostlp FROM (SELECT c_stredisk,c_dokl,c_karty, MAX(DISTINCT poradi) as poradi, COUNT(c_karty) AS riadkov,operace,c_mena FROM food600.genstav WHERE menostlp=mmenobuf GROUP BY  c_stredisk, c_dokl,c_karty, operace,c_mena) xx WHERE xx.riadkov>1 AND (xx.operace IN('PB','VP','PP'));
/*Vymazanie riadkov, kde je karta v rovnakom poradi dvakrat (hlavne buranie a polotovary) - z vystupnej databazy*/
	DELETE FROM food600.genstav WHERE idr IN (SELECT idr FROM food600.genstav INNER JOIN food600.genstavqqq ON genstav.c_stredisk=genstavqqq.c_stredisk AND genstav.c_karty=genstavqqq.c_karty AND genstav.c_dokl=genstavqqq.c_dokl AND genstav.poradi =genstavqqq.poradi WHERE genstav.mnozstvi<0 and genstav.menostlp=mmenobuf);
/*Prepocet cien na pozadovanu vystupnu menu*/
	UPDATE food600.genstav SET cena_prum=food600.prep_meny(c_mena, mmenaakt ,cena_prum) WHERE c_mena<> mmenaakt AND menostlp=mmenobuf;
/*Prepisanie meny na pozadovanu vystupnu menu*/
	UPDATE food600.genstav SET c_mena=mmenaakt WHERE c_mena<>mmenaakt AND menostlp=mmenobuf;
/*Vymazanie pomocnej databazy*/
	DELETE FROM food600.genstavq WHERE menostlp=mmenobuf;
	DELETE FROM food600.genstavqqq WHERE menostlp=mmenobuf;
END IF;
OPEN prep2;
WHILE mdone2<pocetkariet LOOP
	mdone2:=mdone2+1;
	FETCH prep2 INTO ckarty;
	SELECT cena_prum, zustatek, c_mena INTO mnovacena, mzostatok, mc_mena_na FROM food600.genstav WHERE c_karty=ckarty AND menostlp=mmenobuf;
	IF mnovacena IS NULL THEN
		mnovacena:=0;
		mc_mena:=mmenaakt;
		mc_mena_na:=mmenaakt;
		mzostatok:=0;
	END IF;
	IF zapisuj=1 THEN 
		insert into food600.test (novacena, zostatok, c_karty,c_stredisk,c_mena) values (mnovacena,mzostatok,ckarty,cstredisk,mc_mena);
	END IF;
	mnovacena:=ROUND(CAST(food600.prep_meny(mc_mena_na,mmenaakt,mnovacena) AS NUMERIC),zaokruhlenie);
	UPDATE food600.buffer SET zustatek=ROUND(CAST(mzostatok as numeric),4), cena=mnovacena WHERE c_karty=ckarty AND c_stredisk=cstredisk AND menobuf=mmenobuf AND c_data=cdata;
END LOOP;
CLOSE prep2;
UPDATE food600.buffer SET na_sklade=zustatek WHERE zustatek<na_sklade AND c_stredisk=cstredisk AND menobuf=mmenobuf AND c_data=cdata;
DELETE FROM food600.genstav WHERE menostlp=mmenobuf;
DELETE FROM food600.genstavq WHERE menostlp=mmenobuf;
DELETE FROM food600.genstavqqq WHERE menostlp=mmenobuf;
END
$BODY$;
