CREATE OR REPLACE FUNCTION food600.sklad_ceny()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$
DECLARE mc_mena_na INTEGER;
	mc_mena INTEGER;
	mcena DOUBLE PRECISION;
	mmozstvi DOUBLE PRECISION;
	mhodnota DOUBLE PRECISION;
	mdone INTEGER DEFAULT 0;
	mcena_prum DOUBLE PRECISION;
	mmnozstvi DOUBLE PRECISION;
	mnovacena DOUBLE PRECISION;
	mzustatek DOUBLE PRECISION;
	zaokruhlenie INTEGER DEFAULT 10;
    zapisuj INTEGER DEFAULT 0;    
	skl1 CURSOR FOR SELECT SUM(zustatek) AS mnozstvi, SUM(zustatek * cena_prum) AS hodnota, AVG(cena_prum) AS cena_prum, c_mena FROM food600.sklad WHERE c_karty=NEW.c_karty AND c_data=NEW.c_data GROUP BY c_mena;
	sklx CURSOR FOR SELECT SUM(zustatek) AS mnozstvi, SUM(zustatek * cena_prum) AS hodnota, AVG(cena_prum) AS cena_prum, c_mena FROM food600.sklad WHERE c_karty=NEW.c_karty AND c_data=NEW.c_data AND (zustatek<>0 or cena_prum<>0) GROUP BY c_mena;
	skl2 CURSOR FOR SELECT SUM(zustatek) AS mnozstvi, SUM(zustatek * cena_prum) AS hodnota, AVG(cena_prum) AS cena_prum, c_mena FROM food600.sklad WHERE c_karty=OLD.c_karty AND c_data=OLD.c_data GROUP BY c_mena;
BEGIN
IF (TG_OP = 'UPDATE') OR (TG_OP = 'INSERT') THEN
	IF zapisuj THEN
		insert into food600.test (cas,text) values (clock_timestamp(),'SKLAD_CENY:'||new.c_karty);
        raise notice 'c_karty: %',NEW.c_karty;
		insert into food600.test (cas,text) values (clock_timestamp(),'SKLAD_CENY:'||new.c_stredisk);
        raise notice 'c_stredisk: %',NEW.c_stredisk;
    END IF;
    SELECT COUNT(c_karty) INTO mdone FROM food600.polozky WHERE c_karty=NEW.c_karty AND c_data=NEW.c_data;
	IF zapisuj THEN
		insert into food600.test (cas,text) values (clock_timestamp(),'MDONE:'||mdone);
        raise notice 'mdone: %',mdone;
    END IF;
	IF mdone=0 THEN
		mc_mena_na:=NEW.c_mena;
		mnovacena:=0;
		mzustatek:=0;
		OPEN sklx;
		WHILE mdone=0 LOOP
			FETCH sklx INTO mmnozstvi, mhodnota, mcena_prum, mc_mena;
           	IF zapisuj THEN
	            insert into food600.test (cas,text) values (clock_timestamp(),'SKLAD_CENY MMNOZSTVI:'||mmnozstvi);
                   raise notice 'x mmnozstvi: %',mmnozstvi;
	            insert into food600.test (cas,text) values (clock_timestamp(),'SKLAD_CENY MHODNOTA:'||mhodnota);
                   raise notice 'x mhodnota: %',mhodnota;
	            insert into food600.test (cas,text) values (clock_timestamp(),'SKLAD_CENY MCENA_PRUM:'||mcena_prum);
                   raise notice 'x mcena_prum: %',mcena_prum;
	            insert into food600.test (cas,text) values (clock_timestamp(),'SKLAD_CENY MC_MENA:'||mc_mena);
                   raise notice 'x mc_mena: %',mc_mena;
            END IF;
			IF mmnozstvi IS NULL THEN
				mdone:=1;
			ELSE
				IF mc_mena<>mc_mena_na THEN
					mhodnota:=ROUND(CAST(food600.prep_meny(mc_mena,mc_mena_na,mhodnota) AS NUMERIC),zaokruhlenie);
					mcena_prum:=ROUND(CAST(food600.prep_meny(mc_mena,mc_mena_na,mcena_prum) AS NUMERIC),zaokruhlenie);
            	    IF zapisuj THEN
		                insert into food600.test (cas,text) values (clock_timestamp(),'SKLAD_CENY MHODNOTA:'||mhodnota);
                        raise notice 'x mhodnota: %',mhodnota;
		                insert into food600.test (cas,text) values (clock_timestamp(),'SKLAD_CENY MCENA_PRUM:'||mcena_prum);
                        raise notice 'x mcena_prum: %',mcena_prum;
                    END IF;
				END IF;
				IF (mzustatek+mmnozstvi)=0 THEN
					mnovacena:=mcena_prum;
				ELSE 
					mnovacena:=ROUND(CAST((mnovacena*mzustatek+mhodnota)/(mzustatek+mmnozstvi) AS NUMERIC),zaokruhlenie);
				END IF;
				mzustatek:=mzustatek+mmnozstvi;
               	IF zapisuj THEN
	                insert into food600.test (cas,text) values (clock_timestamp(),'SKLAD_CENY MZUSTATEK:'||mzustatek);
                    raise notice 'x mzustatek: %',mzustatek;
	                insert into food600.test (cas,text) values (clock_timestamp(),'SKLAD_CENY MNOVACENA:'||mnovacena);
                   raise notice 'x mnovacena: %',mnovacena;
                END IF;
			END IF;
		END LOOP;
		CLOSE sklx;
		IF mnovacena=0 AND mzustatek=0 THEN
			OPEN skl1;
               	IF zapisuj THEN
	                insert into food600.test (cas,text) values (clock_timestamp(),'SKLAD_CENY OTVORENE SKL1');
                    raise notice 'otvorene skl1';
                END IF;
			WHILE mdone=0 LOOP
				FETCH skl1 INTO mmnozstvi, mhodnota, mcena_prum, mc_mena;
           	    IF zapisuj THEN
	                insert into food600.test (cas,text) values (clock_timestamp(),'SKLAD_CENY SKL1 MMNOZSTVI:'||mmnozstvi);
                    raise notice '1 mmnozstvi: %',mmnozstvi;
	                insert into food600.test (cas,text) values (clock_timestamp(),'SKLAD_CENY SKL1 MHODNOTA:'||mhodnota);
                    raise notice '1 mhodnota: %',mhodnota;
	                insert into food600.test (cas,text) values (clock_timestamp(),'SKLAD_CENY SKL1 MCENA_PRUM:'||mcena_prum);
                    raise notice '1 mcena_prum: %',mcena_prum;
	                insert into food600.test (cas,text) values (clock_timestamp(),'SKLAD_CENY SKL1 MC_MENA:'||mc_mena);
                    raise notice '1 mc_mena: %',mc_mena;
                END IF;
				IF mmnozstvi IS NULL THEN
					mdone:=1;
				ELSE
					IF mc_mena<>mc_mena_na THEN
						mhodnota:=ROUND(CAST(food600.prep_meny(mc_mena,mc_mena_na,mhodnota) AS NUMERIC),zaokruhlenie);
						mcena_prum:=ROUND(CAST(food600.prep_meny(mc_mena,mc_mena_na,mcena_prum) AS NUMERIC),zaokruhlenie);
					END IF;
					IF (mzustatek+mmnozstvi)=0 THEN
						mnovacena:=mcena_prum;
					ELSE 
						mnovacena:=ROUND(CAST((mnovacena*mzustatek+mhodnota)/(mzustatek+mmnozstvi) AS NUMERIC),zaokruhlenie);
					END IF;
					mzustatek:=mzustatek+mmnozstvi;
                   	IF zapisuj THEN
	                    insert into food600.test (cas,text) values (clock_timestamp(),'SKLAD_CENY SKL1 MZUSTATEK:'||mzustatek);
                           raise notice '1 mzustatek: %',mzustatek;
	                    insert into food600.test (cas,text) values (clock_timestamp(),'SKLAD_CENY SKL1 MNOVACENA:'||mnovacena);
                           raise notice '1 mnovacena: %',mnovacena;
                    END IF;
				END IF;
			END LOOP;
			CLOSE skl1;
		END IF;
		UPDATE food600.karty SET cena_prum=mnovacena, c_mena_n=mc_mena_na WHERE c_karty=NEW.c_karty AND c_data=NEW.c_data;
        IF zapisuj THEN
            insert into food600.test (cas,text) values (clock_timestamp(),'SKLAD_CENY UPDATE KARTY');
            raise notice '1 update karty';
        END IF;
	END IF;
	RETURN NEW;
ELSE
	SELECT COUNT(c_karty) INTO mdone FROM food600.polozky WHERE c_karty=OLD.c_karty AND c_data=OLD.c_data;
	IF mdone=0 THEN
		mc_mena_na:=OLD.c_mena;
		mnovacena:=0;
		mzustatek:=0;
		OPEN skl2;
		WHILE mdone=0 LOOP
			FETCH skl2 INTO mmnozstvi, mhodnota, mcena_prum, mc_mena;
           	    IF zapisuj THEN
	                insert into food600.test (cas,text) values (clock_timestamp(),'SKLAD_CENY SKL2 MMNOZSTVI:'||mmnozstvi);
                       raise notice '2 mmnozstvi: %',mmnozstvi;
	                insert into food600.test (cas,text) values (clock_timestamp(),'SKLAD_CENY SKL2 MHODNOTA:'||mhodnota);
                       raise notice '2 mhodnota: %',mhodnota;
	                insert into food600.test (cas,text) values (clock_timestamp(),'SKLAD_CENY SKL2 MCENA_PRUM:'||mcena_prum);
                       raise notice '2 mcena_prum: %',mcena_prum;
	                insert into food600.test (cas,text) values (clock_timestamp(),'SKLAD_CENY SKL2 MC_MENA:'||mc_mena);
                       raise notice '2 mc_mena: %',mc_mena;
                END IF;
			IF mmnozstvi IS NULL THEN
				mdone:=1;
			ELSE
				IF mc_mena<>mc_mena_na THEN
					mhodnota:=ROUND(CAST(food600.prep_meny(mc_mena,mc_mena_na,mhodnota) AS NUMERIC),zaokruhlenie);
					mcena_prum:=ROUND(CAST(food600.prep_meny(mc_mena,mc_mena_na,mcena_prum) AS NUMERIC),zaokruhlenie);
				END IF;
				IF (mzustatek+mmnozstvi)=0 THEN
					mnovacena:=mcena_prum;
				ELSE 
					mnovacena:=(mnovacena*mzustatek+mhodnota)/(mzustatek+mmnozstvi);
				END IF;
				mzustatek:=mzustatek+mmnozstvi;
                   	IF zapisuj THEN
	                    insert into food600.test (cas,text) values (clock_timestamp(),'SKLAD_CENY SKL2 MZUSTATEK:'||mzustatek);
                           raise notice '2 mzustatek: %',mzustatek;
	                    insert into food600.test (cas,text) values (clock_timestamp(),'SKLAD_CENY SKL2 MNOVACENA:'||mnovacena);
                           raise notice '2 mnovacena: %',mnovacena;
                    END IF; 
			END IF;
		END LOOP;
		CLOSE skl2;
		UPDATE food600.karty SET cena_prum=mnovacena, c_mena_n=mc_mena_na WHERE c_karty=OLD.c_karty AND c_data=OLD.c_data;
        IF zapisuj THEN
            insert into food600.test (cas,text) values (clock_timestamp(),'SKLAD_CENY UPDATE KARTY');
            raise notice '2 update karty';
        END IF;
	END IF;
	RETURN OLD;
END IF;
END;
$BODY$;
