CREATE OR REPLACE FUNCTION food600.prep1po_buf(
	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 0;
pocetriadkov BIGINT DEFAULT 0;
pocetkariet BIGINT DEFAULT 0;
max_doklad BIGINT DEFAULT 0;
max_poradie INTEGER DEFAULT 0;
max_operace CHAR(2);
mdone BIGINT DEFAULT 0;
mdone2 BIGINT DEFAULT 0;
ckarty BIGINT DEFAULT 0;
mmnozstvi numeric(20,10);
mcena numeric(20,10);
mzustatek numeric(20,10);
mcena_prum numeric(20,10);
mc_mena INTEGER;
mc_mena_z INTEGER;
mc_mena_na INTEGER;
midriadok BIGINT;
mc_dokl BIGINT;
md_dokl BIGINT DEFAULT 0;
max_c_dokl BIGINT DEFAULT podokl;
mporadi INTEGER;
moperace CHAR(2);
mzostatok numeric(20,10);
mnovacena numeric(20,10);
prep1 CURSOR FOR SELECT obra_buf.idriadok,obra_buf.c_dokl,obra_buf.operace,obra_buf.cena,obra_buf.mnozstvi,obra_buf.cena_prum,obra_buf.zustatek,obra_buf.poradi,dokl_buf.c_mena FROM food600.obra_buf LEFT JOIN food600.dokl_buf ON obra_buf.c_dokl=dokl_buf.c_dokl WHERE obra_buf.c_data=cdata AND obra_buf.c_stredisk=cstredisk AND (food600.LEFT(food600.obra_buf.operace,1) ='P' OR food600.LEFT(food600.obra_buf.operace,1) = 'V') AND obra_buf.c_data=cdata AND obra_buf.c_karty=ckarty AND obra_buf.c_dokl>moddokladu AND obra_buf.c_dokl<=max_c_dokl ORDER BY obra_buf.c_dokl, obra_buf.poradi, SIGN(mnozstvi);
prep2 CURSOR FOR SELECT DISTINCT c_karty FROM food600.buffer WHERE menobuf=mmenobuf AND c_data=cdata;
BEGIN
moddokladu:=0;

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.genstavqq WHERE menostlp=mmenobuf;
	DELETE FROM food600.genstavqqq WHERE menostlp=mmenobuf;
/*Vytvorenie prvej pomocnej databazy so zoznamom dokladov, na ktorych bol na jednotlivych kartach posledny pohyb*/
	IF max_c_dokl<moddokladu THEN
		INSERT INTO food600.genstavqq SELECT obra_buf.c_stredisk,obra_buf.c_karty, MAX(obra_buf.c_dokl) AS c_dokl, mmenobuf as menostlp FROM food600.obra_buf INNER JOIN food600.buffer ON obra_buf.c_karty=buffer.c_karty WHERE obra_buf.c_dokl<=max_c_dokl AND (SUBSTRING(obra_buf.operace FROM 1 FOR 1) in('P','V')) AND obra_buf.c_data=cdata AND obra_buf.c_stredisk=cstredisk AND buffer.menobuf=mmenobuf GROUP BY obra_buf.c_stredisk, obra_buf.c_karty ;
	ELSE
		INSERT INTO food600.genstavqq SELECT obra_buf.c_stredisk,obra_buf.c_karty, MAX(obra_buf.c_dokl) AS c_dokl, mmenobuf as menostlp FROM food600.obra_buf INNER JOIN food600.buffer ON obra_buf.c_karty=buffer.c_karty WHERE obra_buf.c_dokl<=moddokladu AND (SUBSTRING(obra_buf.operace FROM 1 FOR 1) in('P','V')) AND obra_buf.c_data=cdata AND obra_buf.c_stredisk=cstredisk AND buffer.menobuf=mmenobuf GROUP BY obra_buf.c_stredisk, obra_buf.c_karty ;
	END IF;
/*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 obra_buf.c_stredisk,obra_buf.c_karty, obra_buf.c_dokl, MAX(obra_buf.poradi) as poradi, genstavqq.menostlp FROM food600.genstavqq LEFT JOIN food600.obra_buf ON obra_buf.c_stredisk=genstavqq.c_stredisk AND obra_buf.c_dokl=genstavqq.c_dokl AND obra_buf.c_karty=genstavqq.c_karty WHERE genstavqq.menostlp=mmenobuf GROUP BY obra_buf.c_stredisk, obra_buf.c_dokl,obra_buf.c_karty, genstavqq.menostlp;
/*Vymazanie pomocnej databazy*/
	DELETE FROM food600.genstavqq WHERE menostlp=mmenobuf;
	INSERT INTO food600.genstav SELECT obra_buf.c_karty, obra_buf.c_dokl, obra_buf.zustatek, obra_buf.cena_prum, obra_buf.cena , obra_buf.c_stredisk, dokl_buf.c_mena as c_mena, obra_buf.operace, obra_buf.poradi, obra_buf.mnozstvi, 0 as min_zaso, cdata AS c_data, genstavq.menostlp FROM food600.genstavq LEFT JOIN food600.obra_buf  ON genstavq.c_stredisk=obra_buf.c_stredisk  AND genstavq.c_dokl=obra_buf.c_dokl AND genstavq.c_karty=obra_buf.c_karty AND genstavq.poradi=obra_buf.poradi LEFT JOIN food600.dokl_buf on genstavq.c_dokl=dokl_buf.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;
/*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 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.genstavqq WHERE menostlp=mmenobuf;
	DELETE FROM food600.genstavqqq WHERE menostlp=mmenobuf;
END IF;
IF max_c_dokl>moddokladu THEN
	SELECT MAX(c_dokl) INTO max_c_dokl FROM food600.dokl_buf WHERE (food600.left(operace,1) ='P' OR food600.left(operace,1) = 'V') AND c_data=cdata AND c_dokl<=max_c_dokl;
END IF;
OPEN prep2;
WHILE mdone2<pocetkariet LOOP
	mdone2:=mdone2+1;
	FETCH prep2 INTO ckarty;
	IF max_c_dokl<=moddokladu THEN
		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;
		mcena:=mnovacena;
		mcena_prum:=mnovacena;
		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;
	ELSE
		IF moddokladu=0 THEN
			mzostatok:=0.0000;
			mnovacena:=0.0000;
			mcena:=0.0000;
			mcena_prum:=0.0000;
			mc_mena:=mmenaakt;
			mc_mena_na:=mmenaakt;
		ELSE
			SELECT cena_prum, zustatek, c_mena INTO mnovacena, mzostatok, mc_mena 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;
			mcena:=mnovacena;
			mcena_prum:=mnovacena;
		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;
		OPEN prep1;
		SELECT COUNT(*) INTO pocetriadkov FROM food600.obra_buf WHERE c_stredisk=cstredisk AND (food600.LEFT(operace,1) ='P' OR food600.LEFT(operace,1) = 'V') AND c_data=cdata AND c_karty=ckarty AND c_dokl>moddokladu AND c_dokl<=max_c_dokl;
		IF zapisuj=1 THEN 
			insert into food600.test (cas,text) values (clock_timestamp(),'Pocet riadkov: ' || pocetriadkov);
		END IF;
		mdone:=0;
		WHILE (mdone<pocetriadkov) LOOP 
			mdone:=mdone+1;
			FETCH prep1 INTO midriadok, mc_dokl, moperace, mcena, mmnozstvi, mcena_prum, mzustatek, mporadi, mc_mena;
			IF zapisuj=1 THEN 
				insert into food600.test (cas,text) values (clock_timestamp(),'Mdone: ' || mdone);
			END IF;
			IF mdone<=pocetriadkov THEN
				CASE  
					WHEN md_dokl=mc_dokl THEN mc_mena_z:=mc_mena;mc_mena_na=mc_mena;
					WHEN md_dokl=0 THEN md_dokl:=mc_dokl; mc_mena_z:=mc_mena; mc_mena_na:=mc_mena;
					ELSE mc_mena_z:=mc_mena_na; mc_mena_na:=mc_mena; md_dokl:=mc_dokl;
				END CASE ;
				IF zapisuj=1 THEN 
					insert into food600.test (idriadok,novacena,cena_prum,zustatek, zostatok, mnozstvi,cena, cas,text,c_karty,c_stredisk,c_dokl,poradie,sidriadok, operace,c_mena_z,c_mena_na,c_mena) values (midriadok,mnovacena,mcena_prum,mzustatek,mzostatok, mmnozstvi,mcena,clock_timestamp(),moperace,ckarty,cstredisk,mc_dokl,mporadi,0, moperace,mc_mena_z,mc_mena_na,mc_mena);
				END IF;
				CASE
					WHEN moperace = 'PR' THEN
						mnovacena:=mcena;
						mzostatok:=mmnozstvi;
						UPDATE food600.obra_buf SET cena_prum=mcena, zustatek=mmnozstvi WHERE obra_buf.idriadok=midriadok;
					WHEN moperace = 'PI' THEN
						mmnozstvi:=mzustatek-mzostatok;
						mnovacena:=ROUND(CAST(food600.prep_meny(mc_mena_z,mc_mena_na,mnovacena) AS NUMERIC),4);
						UPDATE food600.obra_buf SET mnozstvi=mmnozstvi, cena=mnovacena, cena_prum=mnovacena WHERE idriadok=midriadok;
						mzostatok:=mzustatek;
					WHEN mmnozstvi<0 THEN
						mnovacena:=ROUND(CAST(food600.prep_meny(mc_mena_z,mc_mena_na,mnovacena) AS NUMERIC),4); 
						mzostatok:=mzostatok+mmnozstvi;
						UPDATE food600.obra_buf SET zustatek=mzostatok, cena=mnovacena, cena_prum=mnovacena WHERE idriadok=midriadok;	
					WHEN (mmnozstvi + mzostatok)<=0 THEN
						mnovacena:= mcena;
						mzostatok:=mzostatok+mmnozstvi;
						UPDATE food600.obra_buf SET zustatek=mzostatok, cena=mnovacena, cena_prum=mnovacena WHERE idriadok=midriadok;	
					WHEN  mzostatok<=0 THEN
						mnovacena := mcena;
						mzostatok:=mzostatok+mmnozstvi;
						UPDATE food600.obra_buf SET zustatek=mzostatok, cena=mnovacena, cena_prum=mnovacena WHERE idriadok=midriadok;	
					ELSE
						mnovacena:=ROUND(CAST((mzostatok*food600.prep_meny(mc_mena_z,mc_mena_na,mnovacena)+mmnozstvi*mcena)/(mmnozstvi+mzostatok) AS NUMERIC),4);
						mzostatok:=mzostatok+mmnozstvi;
						UPDATE food600.obra_buf SET zustatek=mzostatok, cena_prum=mnovacena WHERE idriadok=midriadok;	
						IF mnovacena > 99999999 THEN
							mnovacena := 99999999.9999;
						END IF;
				END CASE;
				IF zapisuj=1 THEN 
					insert into food600.test (idriadok,novacena,cena_prum,zustatek, zostatok, mnozstvi,cena, cas,text,c_karty,c_stredisk,c_dokl,poradie,sidriadok, operace,c_mena_z,c_mena_na,c_mena) values (midriadok,mnovacena,mcena_prum,mzustatek,mzostatok, mmnozstvi,mcena,clock_timestamp(),moperace,ckarty,cstredisk,mc_dokl,mporadi,0, moperace,mc_mena_z,mc_mena_na,mc_mena);
				END IF;
			END IF; 
		END LOOP; 
		CLOSE prep1;
	END IF;
	mnovacena:=ROUND(CAST(food600.prep_meny(mc_mena_na,mmenaakt,mnovacena) AS NUMERIC),4);
	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.genstavqq WHERE menostlp=mmenobuf;
DELETE FROM food600.genstavqqq WHERE menostlp=mmenobuf;
END
$BODY$;
