treba da napravim obracun sirovina u magacinu. Imam tabele sirovine(tu su ulazi sirovina u magacin), radni_nalozi(cim se kreira radni nalog skidaju se sirovine) i normativi(sastavnice proizvoda, bira se pri kreiranju radnog naloga). Problem je sto mi stored procedura vraca rezultat tacan samo kad jedan radni nalog unesem, nakon vise unosa uvecava stanje za sum kolicine u tabeli sirovine.
procedura
CREATE PROCEDURE `stanje_sirovina`()
BEGIN
SELECT sirovine.Magacin,sirovine.naziv,(sum(sirovine.Kolicina)-ifnull(sum(radni_nalozi.kolicina*normativi.Kolicina),0)) as 'stanje'
from sirovine left join(radni_nalozi inner join normativi on normativi.IDNormativa=radni_nalozi.normativ) on sirovine.IDSirovine=normativi.Sirovina
and sirovine.Magacin=radni_nalozi.MagacinSirovina
group by Naziv,Magacin;
END
create statements
CREATE TABLE `radni_nalozi` (
`IDRN` int(11) NOT NULL AUTO_INCREMENT,
`BrojRN` varchar(255) NOT NULL,
`sifra_proizvoda` varchar(255) NOT NULL,
`kolicina` decimal(10,2) NOT NULL,
`normativ` varchar(100) NOT NULL,
`DatumRN` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`ZadatiDatum` date DEFAULT NULL,
`Proizvodnja` int(11) NOT NULL,
`Realizovan` bit(1) NOT NULL DEFAULT b'0',
`MagacinSirovina` int(11) NOT NULL,
PRIMARY KEY (`IDRN`),
KEY `BrojRN` (`BrojRN`),
KEY `normativ` (`normativ`),
KEY `Proizvodnja` (`Proizvodnja`),
KEY `sifra_proizvoda` (`sifra_proizvoda`),
KEY `normativ_2` (`normativ`),
KEY `MagacinSirovina` (`MagacinSirovina`),
CONSTRAINT `radni_nalozi_ibfk_4` FOREIGN KEY (`MagacinSirovina`) REFERENCES `magacini_sirovine` (`id_magacina`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `radni_nalozi_ibfk_1` FOREIGN KEY (`normativ`) REFERENCES `normativi` (`IDNormativa`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `radni_nalozi_ibfk_2` FOREIGN KEY (`Proizvodnja`) REFERENCES `proizvodnja` (`IDProizvodnje`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `radni_nalozi_ibfk_3` FOREIGN KEY (`sifra_proizvoda`) REFERENCES `proizvodi` (`SifraProizvoda`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2626 DEFAULT CHARSET=utf8
CREATE TABLE `normativi` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`IDNormativa` varchar(100) NOT NULL,
`SifraProizvoda` varchar(100) NOT NULL,
`Sirovina` varchar(100) NOT NULL,
`Kolicina` decimal(11,2) NOT NULL,
PRIMARY KEY (`ID`),
KEY `SifraProizvoda` (`SifraProizvoda`),
KEY `Sirovina` (`Sirovina`),
KEY `IDNormativa` (`IDNormativa`),
KEY `IDNormativa_2` (`IDNormativa`),
KEY `IDNormativa_3` (`IDNormativa`),
CONSTRAINT `normativi_ibfk_5` FOREIGN KEY (`SifraProizvoda`) REFERENCES `proizvodi` (`SifraProizvoda`) ON UPDATE CASCADE,
CONSTRAINT `normativi_ibfk_6` FOREIGN KEY (`Sirovina`) REFERENCES `sirovine_nazivi` (`Sifra`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=986 DEFAULT CHARSET=utf8
CREATE TABLE `sirovine` (
`IDUF` int(11) NOT NULL AUTO_INCREMENT,
`ID` varchar(255) NOT NULL,
`IDSirovine` varchar(255) NOT NULL,
`Dobavljac` varchar(255) NOT NULL,
`Naziv` varchar(100) NOT NULL,
`Kolicina` decimal(10,2) NOT NULL,
`JM` varchar(100) NOT NULL,
`Cena` decimal(10,2) NOT NULL,
`Rabat` decimal(4,2) NOT NULL,
`PDV` decimal(4,2) NOT NULL,
`CenaBez` decimal(10,2) NOT NULL,
`CenaSa` decimal(10,2) NOT NULL,
`DatumUlaza` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`Magacin` int(11) NOT NULL,
PRIMARY KEY (`IDUF`),
KEY `Dobavljac` (`Dobavljac`),
KEY `IDSirovine` (`IDSirovine`),
KEY `Magacin` (`Magacin`),
KEY `ID` (`ID`),
CONSTRAINT `sirovine_ibfk_3` FOREIGN KEY (`Magacin`) REFERENCES `magacini_sirovine` (`id_magacina`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `sirovine_ibfk_4` FOREIGN KEY (`IDSirovine`) REFERENCES `sirovine_nazivi` (`Sifra`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `sirovine_ibfk_5` FOREIGN KEY (`Dobavljac`) REFERENCES `dobavljaci` (`IDDobavljaca`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8
Hvala!