33 | | DECLARE @Kolicina INT; |
| 15 | CREATE OR REPLACE FUNCTION create_full_naracka( |
| 16 | p_kolicina INT, |
| 17 | p_datum DATE, |
| 18 | p_opis TEXT, |
| 19 | p_firma_naziv VARCHAR(100), |
| 20 | p_artikl_naziv VARCHAR(100), |
| 21 | p_datum_start DATE, |
| 22 | p_datum_end DATE |
| 23 | ) RETURNS VOID AS $$ |
| 24 | DECLARE |
| 25 | v_naracka_id INT; |
| 26 | v_firma_id INT; |
| 27 | v_artikl_id INT; |
| 28 | v_cena DECIMAL(10,2); |
| 29 | v_izrabotka_plan_id INT; |
| 30 | BEGIN |
| 31 | -- Get FirmaID |
| 32 | SELECT FirmaID INTO v_firma_id |
| 33 | FROM Firma |
| 34 | WHERE Naziv = p_firma_naziv; |
| 35 | |
| 36 | -- Get ArtiklID and Cena |
| 37 | SELECT ArtiklID, Cena INTO v_artikl_id, v_cena |
| 38 | FROM Artikl |
| 39 | WHERE Naziv = p_artikl_naziv; |
| 40 | |
| 41 | -- Insert into Naracka |
| 42 | INSERT INTO Naracka (FirmaID, Datum, Opis) |
| 43 | VALUES (v_firma_id, p_datum, p_opis) |
| 44 | RETURNING NarackaID INTO v_naracka_id; |
| 45 | |
| 46 | -- Insert into Naracka_Artikl |
| 47 | INSERT INTO Naracka_Artikl (NarackaID, ArtiklID, Kolicina, Cena) |
| 48 | VALUES (v_naracka_id, v_artikl_id, p_kolicina, v_cena); |
| 49 | |
| 50 | -- Get IzrabotkaPlanID |
| 51 | SELECT IzrabotkaPlanID INTO v_izrabotka_plan_id |
| 52 | FROM IzrabotkaPlan |
| 53 | WHERE DatumStart = p_datum_start AND DatumEnd = p_datum_end; |
| 54 | |
| 55 | -- Insert into Naracka_IzrabotkaPlan |
| 56 | INSERT INTO Naracka_IzrabotkaPlan (NarackaID, IzrabotkaPlanID) |
| 57 | VALUES (v_naracka_id, v_izrabotka_plan_id); |
| 58 | END; |
| 59 | $$ LANGUAGE plpgsql; |
35 | | DECLARE @DatumStart DATE; |
36 | | |
37 | | DECLARE @DatumEnd DATE; |
38 | | |
39 | | |
40 | | INSERT INTO Naracka (Kolicina, Datum, Opis) |
41 | | |
42 | | VALUES (@Kolicina, GETDATE(), 'YourOpis'); |
43 | | |
44 | | |
45 | | DECLARE @NarackaID INT; |
46 | | |
47 | | SET @NarackaID = SCOPE_IDENTITY(); |
48 | | |
49 | | |
50 | | DECLARE @FirmaID INT; |
51 | | |
52 | | SET @FirmaID = (SELECT FirmaID FROM Firma WHERE Naziv = @FirmaNaziv); |
53 | | |
54 | | |
55 | | DECLARE @ArtiklID INT; |
56 | | |
57 | | SET @ArtiklID = (SELECT ArtiklID FROM Artikl WHERE Naziv = @ArtiklNaziv); |
58 | | |
59 | | |
60 | | INSERT INTO Naracka_Artikl (NarackaID, ArtiklID) |
61 | | |
62 | | VALUES (@NarackaID, @ArtiklID); |
63 | | |
64 | | |
65 | | INSERT INTO Naracka_Firma (NarackaID, FirmaID) |
66 | | |
67 | | VALUES (@NarackaID, @FirmaID); |
68 | | |
69 | | |
70 | | INSERT INTO Naracka_IzrabotkaPlan (NarackaID, DatumStart, DatumEnd) |
71 | | |
72 | | VALUES (@NarackaID, @DatumStart, @DatumEnd); |
86 | | DECLARE @NewStartDate DATE; |
87 | | |
88 | | DECLARE @NewEndDate DATE; |
89 | | |
90 | | |
91 | | UPDATE Naracka_IzrabotkaPlan |
92 | | |
93 | | SET DatumStart = @NewStartDate |
94 | | |
95 | | WHERE NarackaID = @NarackaID; |
96 | | |
97 | | |
98 | | UPDATE Naracka_IzrabotkaPlan |
99 | | |
100 | | SET DatumEnd = @NewEndDate |
101 | | |
102 | | WHERE NarackaID = @NarackaID; |
| 83 | -- Update DatumEnd |
| 84 | UPDATE Naracka_IzrabotkaPlan |
| 85 | SET DatumEnd = p_new_end_date |
| 86 | WHERE NarackaID = p_naracka_id; |
| 87 | END; |
| 88 | $$ LANGUAGE plpgsql; |