1 | --- view za pregled na sostojbata na site artikli na bilo koja lokacija
|
---|
2 |
|
---|
3 | create or replace view articles_report as
|
---|
4 | select a.articleid, a.articlename, a.description, a.imageurl, s.quantity, l.locationname, l.locationid, s.sarticleid from articles a
|
---|
5 | left join storedarticles s on s.articleid=a.articleid
|
---|
6 | left join locations l on l.locationid=s.locationid
|
---|
7 |
|
---|
8 | --- view za pregled na koi kategorii pripagja sekoj artikl
|
---|
9 |
|
---|
10 | create or replace view articles_and_cats_report as
|
---|
11 | select c.categoryname, c.categoryid, a.articleid, a.articlename from categories c
|
---|
12 | left join article_belongs_to_category abtc on abtc.categoryid=c.categoryid
|
---|
13 | left join articles a on a.articleid=abtc.articleid
|
---|
14 |
|
---|
15 | -- view za pregled na artiklite koi moze da gi dostavi sekoj od dobavuvacite
|
---|
16 |
|
---|
17 | create or replace view supplier_supplies_article as
|
---|
18 | select articlename, s.userid, a.articleid from articles a
|
---|
19 | left join article_belongs_to_category abtc on abtc.articleid=a.articleid
|
---|
20 | left join supplier_supplies_category ssc on ssc.categoryid=abtc.categoryid
|
---|
21 | left join suppliers s on s.userid=ssc.userid
|
---|
22 |
|
---|
23 | -- view za pregled na artiklite i nivnata dostapnosta na site lokacii
|
---|
24 |
|
---|
25 | create or replace view articles_at_location as
|
---|
26 | select a.articleid, a.description, a.articlename, a.imageurl, a.maxquantityperlocation, s.sarticleid, s.quantity, s.locationid, l.locationname from articles a
|
---|
27 | left join storedarticles s on s.articleid=a.articleid
|
---|
28 | left join locations l on l.locationid=s.locationid
|
---|
29 |
|
---|
30 | -- view za pregled na artiklite vo sekoja narachka
|
---|
31 |
|
---|
32 | create or replace view ordered_articles_report as
|
---|
33 | select a.articleid, a.description, a.articlename, a.imageurl, a.maxquantityperlocation, o.oarticleid, o.quantity, o.locationid, o.price, o.articlestatus, o.orderid, s.quantity as storedQuantity from orderedarticles o
|
---|
34 | left join articles a on o.articleid=a.articleid
|
---|
35 | left join storedarticles s on s.locationid=o.locationid and s.articleid=a.articleid
|
---|
36 |
|
---|
37 | -- view za pregled na artiklite vo sekoja faktura
|
---|
38 |
|
---|
39 | create or replace view invoiced_articles_report as
|
---|
40 | select i.iarticleid, i.invoiceid, i.articleid, i.price, i.quantity, a.description, a.articlename, a.imageurl, a.maxquantityperlocation from invoicedarticles i
|
---|
41 | left join articles a on a.articleid=i.articleid
|
---|
42 |
|
---|
43 | -- view za pregled na detalni informacii za narachkite
|
---|
44 |
|
---|
45 | create or replace view orders_report as
|
---|
46 | select o.orderid, o.supplierremark, o.managerremark, o.status, o.datecreated, o.priority, o.supplieruserid, s.supplierinfo, o.manageruserid from orders o
|
---|
47 | left join suppliers s on s.userid=o.supplieruserid
|
---|
48 |
|
---|
49 | -- view za pregled na detalni informacii za dobavuvacite
|
---|
50 |
|
---|
51 | create or replace view suppliers_report as select u.userid,
|
---|
52 | u.username,
|
---|
53 | u.firstname,
|
---|
54 | u.lastname,
|
---|
55 | s.street,
|
---|
56 | s.streetnumber,
|
---|
57 | s.city,
|
---|
58 | s.phone,
|
---|
59 | s.supplierinfo
|
---|
60 | from users u
|
---|
61 | join suppliers s on s.userid = u.userid;
|
---|
62 |
|
---|
63 | -- funkcija za popolnuvanje na razlicnite magacini
|
---|
64 |
|
---|
65 | CREATE OR REPLACE FUNCTION project.populatestorage()
|
---|
66 | RETURNS trigger
|
---|
67 | LANGUAGE plpgsql
|
---|
68 | AS $function$
|
---|
69 | BEGIN
|
---|
70 | insert into project.storedarticles(quantity,locationid,articleid) select 0 as quantity, l.locationid, new.articleid as articleid from project.locations l;
|
---|
71 | return new;
|
---|
72 | END;
|
---|
73 | $function$
|
---|
74 | ;
|
---|
75 |
|
---|
76 | -- triger za avtomatsko popolnuvanje na razlicnite magacini
|
---|
77 |
|
---|
78 | CREATE TRIGGER populateStorage
|
---|
79 | after insert on public.articles
|
---|
80 | for each row
|
---|
81 | execute procedure public.populatestoragefunc();
|
---|
82 |
|
---|
83 | |
---|