[708a5bc] | 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 | |
---|