Index: SQLScripts/AdditionalCreations.sql =================================================================== --- SQLScripts/AdditionalCreations.sql (revision 708a5bca6e068452ef930ad490fb859e8ce8868d) +++ SQLScripts/AdditionalCreations.sql (revision 708a5bca6e068452ef930ad490fb859e8ce8868d) @@ -0,0 +1,83 @@ +--- view za pregled na sostojbata na site artikli na bilo koja lokacija + +create or replace view articles_report as + select a.articleid, a.articlename, a.description, a.imageurl, s.quantity, l.locationname, l.locationid, s.sarticleid from articles a + left join storedarticles s on s.articleid=a.articleid + left join locations l on l.locationid=s.locationid + +--- view za pregled na koi kategorii pripagja sekoj artikl + +create or replace view articles_and_cats_report as + select c.categoryname, c.categoryid, a.articleid, a.articlename from categories c + left join article_belongs_to_category abtc on abtc.categoryid=c.categoryid + left join articles a on a.articleid=abtc.articleid + +-- view za pregled na artiklite koi moze da gi dostavi sekoj od dobavuvacite + +create or replace view supplier_supplies_article as + select articlename, s.userid, a.articleid from articles a + left join article_belongs_to_category abtc on abtc.articleid=a.articleid + left join supplier_supplies_category ssc on ssc.categoryid=abtc.categoryid + left join suppliers s on s.userid=ssc.userid + +-- view za pregled na artiklite i nivnata dostapnosta na site lokacii + +create or replace view articles_at_location as + select a.articleid, a.description, a.articlename, a.imageurl, a.maxquantityperlocation, s.sarticleid, s.quantity, s.locationid, l.locationname from articles a + left join storedarticles s on s.articleid=a.articleid + left join locations l on l.locationid=s.locationid + +-- view za pregled na artiklite vo sekoja narachka + +create or replace view ordered_articles_report as + 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 + left join articles a on o.articleid=a.articleid + left join storedarticles s on s.locationid=o.locationid and s.articleid=a.articleid + +-- view za pregled na artiklite vo sekoja faktura + +create or replace view invoiced_articles_report as + select i.iarticleid, i.invoiceid, i.articleid, i.price, i.quantity, a.description, a.articlename, a.imageurl, a.maxquantityperlocation from invoicedarticles i + left join articles a on a.articleid=i.articleid + +-- view za pregled na detalni informacii za narachkite + +create or replace view orders_report as + select o.orderid, o.supplierremark, o.managerremark, o.status, o.datecreated, o.priority, o.supplieruserid, s.supplierinfo, o.manageruserid from orders o + left join suppliers s on s.userid=o.supplieruserid + +-- view za pregled na detalni informacii za dobavuvacite + +create or replace view suppliers_report as select u.userid, + u.username, + u.firstname, + u.lastname, + s.street, + s.streetnumber, + s.city, + s.phone, + s.supplierinfo + from users u + join suppliers s on s.userid = u.userid; + +-- funkcija za popolnuvanje na razlicnite magacini + +CREATE OR REPLACE FUNCTION project.populatestorage() + RETURNS trigger + LANGUAGE plpgsql +AS $function$ + BEGIN + insert into project.storedarticles(quantity,locationid,articleid) select 0 as quantity, l.locationid, new.articleid as articleid from project.locations l; + return new; + END; +$function$ +; + +-- triger za avtomatsko popolnuvanje na razlicnite magacini + +CREATE TRIGGER populateStorage + after insert on public.articles + for each row + execute procedure public.populatestoragefunc(); + + Index: SQLScripts/kreiranje.sql =================================================================== --- SQLScripts/kreiranje.sql (revision 708a5bca6e068452ef930ad490fb859e8ce8868d) +++ SQLScripts/kreiranje.sql (revision 708a5bca6e068452ef930ad490fb859e8ce8868d) @@ -0,0 +1,246 @@ +drop schema if exists project cascade; +create schema project; + +--use project + +drop table if exists users cascade; +drop table if exists workers cascade; +drop table if exists managers cascade; +drop table if exists suppliers cascade; +drop table if exists articles cascade; +drop table if exists invoices cascade; +drop table if exists orders cascade; +drop table if exists categories cascade; +drop table if exists locations cascade; +drop table if exists questions cascade; +drop table if exists storedArticles cascade; +drop table if exists invoicedArticles cascade; +drop table if exists orderedArticles cascade; +drop table if exists answers cascade; +drop table if exists question_availability_for_storedarticle cascade; +drop table if exists article_belongs_to_category cascade; +drop table if exists supplier_supplies_category cascade; + +------------------------- + +create table users( + + userId serial primary key, + firstName varchar(100) not null, + lastName varchar(100) not null, + username varchar(100) unique not null, + email varchar(100) not null, + userPassword varchar(100) not null + +); + +------------------------- + +create table articles( + + articleId serial primary key, + description varchar(400) not null, + articleName varchar(100) not null, + imageURL varchar(200), + maxQuantityPerLocation integer not null + +); + +------------------------- + +create table categories( + + categoryId serial primary key, + categoryName varchar(100) not null, + description varchar(400) not null + +); + +------------------------- + +create table locations( + + locationId serial primary key, + locationName varchar(100) not null, + phone varchar(20) not null, + street varchar(100) not null, + streetNumber integer not null, + city varchar(20) not null + +); + +------------------------- + +create table workers( + + userId integer primary key, + locationId integer, + constraint fk_workers_id foreign key (userId) references users(userId) on delete cascade on update cascade, + constraint fk_workers_loc foreign key (locationId) references locations(locationId) + +); + +------------------------- + +create table managers( + + userId integer primary key, + constraint fk_managers_id foreign key (userId) references users(userId) on delete cascade on update cascade + +); + +------------------------- + +create table suppliers( + + userId integer primary key, + supplierInfo varchar(300) not null, + phone varchar(20) not null, + street varchar(100) not null, + streetNumber integer not null, + city varchar(100) not null, + constraint fk_suppliers_id foreign key (userId) references users(userId) on delete cascade on update cascade + +); + +------------------------- + +create table invoices( + invoiceId serial primary key, + customerName varchar(100), + customerPhone varchar(20), + street varchar(100), + streetNumber integer, + city varchar(100), + dateCreate timestamp default now(), + workerUserId integer not null, + constraint fk_invoice_worker foreign key (workerUserId) references workers(userId) + +); + +------------------------- + +create table orders( + + orderId serial primary key, + status varchar(50) not null, + supplierRemark varchar(400), + managerRemark varchar(400), + dateCreated timestamp default now(), + dateApproved timestamp, + priority varchar(50) not null, + managerUserId integer not null, + supplierUserId integer not null, + constraint fk_order_manager foreign key (managerUserId) references managers(userId), + constraint fk_order_supplier foreign key (supplierUserId) references suppliers(userId) + +); + +------------------------- + +create table storedArticles( + + sArticleId serial primary key, + quantity integer not null, + locationId integer not null, + articleId integer not null, + constraint fk_storedArt_article foreign key (articleId) references articles(articleId), + constraint fk_storedArt_location foreign key (locationId) references locations(locationId), + constraint ck_quantity_gt_0 check (quantity>=0) + +); + +------------------------- + +create table invoicedArticles( + + iArticleId serial primary key, + price integer not null, + quantity integer not null, + invoiceId integer not null, + articleId integer not null, + constraint fk_invoicedArt_article foreign key (articleId) references articles(articleId), + constraint fk_invoicedArt_invoice foreign key (invoiceId) references invoices(invoiceId), + constraint ck_price_gt_0 check (price>0), + constraint ck_quantity_gt_0 check (quantity>0) + +); + +------------------------- + +create table orderedArticles( + + oArticleId serial primary key, + price integer, + quantity integer not null, + articleStatus varchar(50) not null, + orderId integer not null, + locationId integer not null, + articleId integer not null, + constraint fk_orderedArt_article foreign key (articleId) references articles(articleId), + constraint fk_orderedArt_location foreign key (locationId) references locations(locationId), + constraint fk_orderedArt_order foreign key (orderId) references orders(orderId), + constraint ck_price_gt_0 check (price>0), + constraint ck_quantity_gt_0 check (quantity>0) + +); + +------------------------- + +create table questions( + + questionId serial primary key, + questionText varchar(500) not null, + dateCreated timestamp default now(), + workerUserId integer not null, + managerUserId integer not null, + constraint fk_question_worker foreign key (workerUserId) references workers(userId), + constraint fk_question_manager foreign key (managerUserId) references managers(userId) + +); + +------------------------- + +create table answers( + + answerId serial, + questionId integer, + answerText varchar(500) not null, + dateCreated timestamp default now(), + constraint fk_answer_question foreign key (questionId) references questions(questionId) on delete cascade on update cascade, + constraint pk_answer primary key(questionId, answerId) + +); + +------------------------- + +create table question_availability_for_storedarticle( + + questionId integer, + sArticleId integer, + constraint fk_question_sarticle_question foreign key (questionId) references questions(questionId), + constraint fk_question_sarticle_sarticle foreign key (sArticleId) references storedArticles(sArticleId) + +); + +------------------------- + +create table article_belongs_to_category( + + articleId integer, + categoryId integer, + constraint fk_article_category_article foreign key (articleId) references articles(articleId), + constraint fk_article_category_category foreign key (categoryId) references categories(categoryId) + +); + +------------------------- + +create table supplier_supplies_category( + + userId integer, + categoryId integer, + constraint fk_supplier_category_supplier foreign key (userId) references suppliers(userId), + constraint fk_supplier_category_category foreign key (categoryId) references categories(categoryId) + +); Index: SQLScripts/polnenje.sql =================================================================== --- SQLScripts/polnenje.sql (revision 708a5bca6e068452ef930ad490fb859e8ce8868d) +++ SQLScripts/polnenje.sql (revision 708a5bca6e068452ef930ad490fb859e8ce8868d) @@ -0,0 +1,113 @@ +insert into users (firstname,lastname,username,email,userpassword) values +('Bojan','Trpeski','BojanT','bojan@gmail.com','1234'), +('Darko','Sasanski','DarkoS','darko@gmail.com','1111'), +('Trajko','Trajkovski','TrajkoT','trajko@gmail.com','2222'), +('Petko','Petkovski','PetkoP','petko@gmail.com','3333'), +('Andrej','Todorovski','AndrejT','andrej@gmail.com','4444'), +('Hristijan','Hristovski','HristijanH','hristijan@gmail.com','55555'), +('Stefan','Stefanovski','StefanS','stefan@gmail.com','6666'), +('Dimitar','Dimovski','DimitarD','dimitar@gmail.com','7777'), +('David','Davidovski','DavidD','david@gmail.com','8888'); + +insert into managers (userid) values +(2),(3),(6); + +insert into workers (userid) values +(4),(5),(7); + +insert into suppliers (userid,supplierinfo,phone,street,streetnumber,city) values +(8,'DHL','070123456','Ilindenska',123,'Skopje'), +(9,'FedEx','070555111','Partizanski Oderdi',15,'Skopje'), +(1,'Kargo Express','070789456','Jane Sandanski',55,'Skopje'); + + +insert into orders (status,supplierremark,priority,manageruserid,supplieruserid) values +('IN_PROGRESS','To be delivered','Low',2,1); +insert into orders (status,managerremark,priority,manageruserid,supplieruserid) values +('IN_PROGRESS','To be packed','Medium',3,8); +insert into orders (status,datecreated,dateapproved ,priority,manageruserid,supplieruserid) values +('DELIVERED','2022-10-11 11:53:45','2022-10-12 12:13:14','High',6,9); + +insert into articles (description,articlename,maxquantityperlocation) values +('Del za velosiped','Osovina',10), +('Novi televizori','Samsung TV 4K',15), +('Bluetooth audio','JBL GO 2',50), +('Domasen enterier','Agolna garnitura',5), +('Novi laptopi','Apple M1',100), +('Novi velosipedi','Focus',10), +('Domasen enterier','Biro',20), +('Debela Jakna','Addidas jakna',120), +('Jakna za skijanje','Nike jakna',150); + +insert into categories (categoryName,description) values +('Garderoba','Zimski jakni'), +('Tehnologija','Najnovi televizori, kompjuteri i audio uredi'), +('Velosipedizam','Se za velosipedi'), +('Mebel','Mebel za sekoj dom'); + +insert into locations (locationname,phone,street,streetnumber,city) values +('Magacin 1','078894563','Metodija Shatorov Sharlo',53,'Skopje'), +('Magacin 2','075321654','Boris Trajkovski',81,'Skopje'), +('Magacin 3','072125874','1.Maj',45,'Bitola'); + +update workers set locationid =1 where userid =4; +update workers set locationid =2 where userid =5; +update workers set locationid =3 where userid =7; + +insert into invoices (customername,customerphone,street,streetnumber,city,workeruserid) values +('Stojan','0789654123','Bulervar Srbija','155','Skopje',7), +('Marko','070456321','Ruzveltova','3','Skopje',4), +('Kristijan','075897125','ASNOM','47','Veles',5); + +insert into invoicedarticles (price,quantity,invoiceid,articleid) values +(6000,3,1,3), +(50000,1,3,5), +(20000,2,2,7); + +insert into storedarticles (quantity,locationid,articleid) values +(5,1,6), +(10,3,3), +(20,3,8), +(2,2,5), +(8,1,7); + +insert into orderedarticles (price,quantity,orderid,locationid,articleid, articlestatus) values +(100000,2,1,1,4, 'ORDERED'), +(2000,1,2,2,1, 'ORDERED'), +(15000,5,3,3,9, 'DELIVERED'); + +insert into questions (questiontext,datecreated,workeruserid,manageruserid) values +('Dali ke moze da naracate uste Focus velosipedi, nemame na zaliha?','2022-10-10 14:15:23',7,6), +('Dali se dosta 100 jakni?','2022-11-11 11:12:12',5,2), +('Dali ke moze da naracate novi JBL GO2 zvucnici, imame uste 10?','2022-11-15 13:51:23',4,3); + +insert into answers (questionId,answertext,datecreated) values +(1,'Da, ke naracam uste 5.','2022-10-11 08:14:14'), +(2,'Dovolno e 100 jakni.','2022-11-12 09:16:23'), +(3,'Sekako, ke naracam 100 parcinja.','2022-11-17 12:42:24'); + +insert into question_availability_for_storedarticle (questionId,sarticleid) values +(1,1), +(2,3), +(3,2); + +insert into supplier_supplies_category (userid,categoryid) values +(8,1), +(8,2), +(9,3), +(9,4), +(9,2), +(1,1), +(1,3), +(1,4); + +insert into article_belongs_to_category (articleId,categoryid) values +(1,2), +(2,1), +(3,1), +(4,3), +(5,1), +(6,2), +(7,3), +(8,4), +(9,4);