Index: database_scripts/kreiranje_v2.sql =================================================================== --- database_scripts/kreiranje_v2.sql (revision f094f6c409345b74661afd84bd147a6a076a8967) +++ database_scripts/kreiranje_v2.sql (revision 101cbe2e9b2ba230315e8bd061810f42c4781f3c) @@ -1,28 +1,28 @@ -drop table if exists users_table cascade; -drop table if exists client cascade; -drop table if exists delivery_man cascade; -drop table if exists warehouseman cascade; -drop table if exists administrator cascade; -drop table if exists review cascade; -drop table if exists repair cascade; -drop table if exists repair_shop cascade; -drop table if exists car_manufacturer cascade; -drop table if exists car cascade; -drop table if exists car_sample cascade; -drop table if exists service_book cascade; -drop table if exists order_table cascade; -drop table if exists part_manufacturer cascade; -drop table if exists part cascade; -drop table if exists delivery cascade; -drop table if exists warehouse cascade; -drop table if exists price cascade; -drop table if exists category cascade; -drop table if exists part_is_from_category cascade; -drop table if exists repair_shop_is_authorized_for_car_make cascade; -drop table if exists order_contains_part cascade; -drop table if exists part_is_in_stock_in_warehouse cascade; -drop table if exists part_is_appropriate_for_car cascade; - -create table users_table( +drop table if exists project.users_table cascade; +drop table if exists project.client cascade; +drop table if exists project.delivery_man cascade; +drop table if exists project.warehouseman cascade; +drop table if exists project.administrator cascade; +drop table if exists project.review cascade; +drop table if exists project.repair cascade; +drop table if exists project.repair_shop cascade; +drop table if exists project.car_manufacturer cascade; +drop table if exists project.car cascade; +drop table if exists project.car_sample cascade; +drop table if exists project.service_book cascade; +drop table if exists project.order_table cascade; +drop table if exists project.part_manufacturer cascade; +drop table if exists project.part cascade; +drop table if exists project.delivery cascade; +drop table if exists project.warehouse cascade; +drop table if exists project.price cascade; +drop table if exists project.category cascade; +drop table if exists project.part_is_from_category cascade; +drop table if exists project.repair_shop_is_authorized_for_car_make cascade; +drop table if exists project.order_contains_part cascade; +drop table if exists project.part_is_in_stock_in_warehouse cascade; +drop table if exists project.part_is_appropriate_for_car cascade; + +create table project.users_table( ID_user serial primary key, username varchar(100) unique not null, @@ -34,45 +34,44 @@ ID_administrator integer -- constraint for fk_user_by_administrator added later - -); - -create table client( - ID_user integer primary key, - constraint fk_client_is_user foreign key (ID_user) references users_table(ID_user) -); - -create table delivery_man( +); + +create table project.client( + ID_user integer primary key, + constraint fk_client_is_user foreign key (ID_user) references project.users_table(ID_user) +); + +create table project.delivery_man( ID_user integer primary key, employed_from date not null, - constraint fk_delivery_man_is_user foreign key (ID_user) references users_table(ID_user) -); - -create table warehouseman( + constraint fk_delivery_man_is_user foreign key (ID_user) references project.users_table(ID_user) +); + +create table project.warehouseman( ID_user integer primary key, employed_from date not null, ID_warehouse integer not null, - constraint fk_warehouseman_is_user foreign key (ID_user) references users_table(ID_user) + constraint fk_warehouseman_is_user foreign key (ID_user) references project.users_table(ID_user) -- contraint for fk_warehouseman_warehouse added later ); -create table administrator( +create table project.administrator( ID_user integer primary key, authorized_from date not null, authorized_till date not null, - constraint fk_administrator_is_user foreign key (ID_user) references users_table(ID_user) -); - -alter table users_table add constraint fk_user_by_administrator foreign key (ID_administrator) - references administrator(ID_user); + constraint fk_administrator_is_user foreign key (ID_user) references project.users_table(ID_user) +); + +alter table project.users_table add constraint fk_user_by_administrator foreign key (ID_administrator) + references project.administrator(ID_user); -create table warehouse( +create table project.warehouse( ID_warehouse serial primary key, warehouse_location varchar(100) not null ); -alter table warehouseman add constraint fk_warehouseman_warehouse foreign key (ID_warehouse) - references warehouse(ID_warehouse); +alter table project.warehouseman add constraint fk_warehouseman_warehouse foreign key (ID_warehouse) + references project.warehouse(ID_warehouse); -create table car_manufacturer( +create table project.car_manufacturer( ID_car_manufacturer serial primary key, cm_name varchar(100) not null, @@ -80,5 +79,5 @@ ); -create table car( +create table project.car( ID_car serial primary key, in_production_since integer not null, @@ -86,8 +85,8 @@ car_type varchar(100) not null, ID_car_manufacturer integer not null, - constraint fk_car_car_manufacturer foreign key (ID_car_manufacturer) references car_manufacturer(ID_car_manufacturer) -); - -create table car_sample( + constraint fk_car_car_manufacturer foreign key (ID_car_manufacturer) references project.car_manufacturer(ID_car_manufacturer) +); + +create table project.car_sample( vin integer primary key, year_of_production integer not null, @@ -98,16 +97,16 @@ ID_user integer not null, ID_car integer not null, - constraint fk_car_sample_user foreign key (ID_user) references client(ID_user), - constraint fk_car_sample_car foreign key (ID_car) references car(ID_car) -); - -create table service_book( + constraint fk_car_sample_user foreign key (ID_user) references project.client(ID_user), + constraint fk_car_sample_car foreign key (ID_car) references project.car(ID_car) +); + +create table project.service_book( ID_service_book serial primary key, sb_created_on timestamp default now(), vin integer not null, - constraint fk_service_book_car_sample foreign key (vin) references car_sample(vin) -); - -create table repair_shop( + constraint fk_service_book_car_sample foreign key (vin) references project.car_sample(vin) +); + +create table project.repair_shop( ID_repair_shop serial primary key, rs_name varchar(100) not null, @@ -116,15 +115,15 @@ ); -create table repair( +create table project.repair( ID_repair serial primary key, vin integer not null, ID_repair_shop integer not null, ID_service_book integer, - constraint fk_repair_car_sample foreign key (vin) references car_sample(vin), - constraint fk_repair_repair_shop foreign key (ID_repair_shop) references repair_shop(ID_repair_shop), - constraint fk_repair_service_book foreign key (ID_service_book) references service_book(ID_service_book) -); - -create table review( + constraint fk_repair_car_sample foreign key (vin) references project.car_sample(vin), + constraint fk_repair_repair_shop foreign key (ID_repair_shop) references project.repair_shop(ID_repair_shop), + constraint fk_repair_service_book foreign key (ID_service_book) references project.service_book(ID_service_book) +); + +create table project.review( ID_review serial primary key, review_rating integer not null, @@ -132,17 +131,17 @@ ID_repair integer not null, ID_user integer not null, - constraint fk_review_repair foreign key (ID_repair) references repair(ID_repair), - constraint fk_review_user foreign key (ID_user) references client(ID_user) -); - -create table order_table( + constraint fk_review_repair foreign key (ID_repair) references project.repair(ID_repair), + constraint fk_review_user foreign key (ID_user) references project.client(ID_user) +); + +create table project.order_table( ID_order serial primary key, order_status varchar(100) not null, order_date timestamp default now(), ID_user integer not null, - constraint fk_order_user foreign key (ID_user) references client(ID_user) -); - -create table part_manufacturer( + constraint fk_order_user foreign key (ID_user) references project.client(ID_user) +); + +create table project.part_manufacturer( ID_part_manufacturer serial primary key, pm_name varchar(100) not null, @@ -150,13 +149,13 @@ ); -create table part( +create table project.part( ID_part serial primary key, part_name varchar(100) not null, part_description varchar(300), ID_part_manufacturer integer not null, - constraint fk_part_part_manufacturer foreign key(ID_part_manufacturer) references part_manufacturer(ID_part_manufacturer) -); - -create table delivery( + constraint fk_part_part_manufacturer foreign key(ID_part_manufacturer) references project.part_manufacturer(ID_part_manufacturer) +); + +create table project.delivery( ID_delivery serial primary key, delivery_status varchar(100) not null, @@ -164,9 +163,9 @@ ID_user integer not null, ID_order integer not null, - constraint fk_delivered_by_deliver_man foreign key(ID_user) references delivery_man(ID_user), - constraint fk_delivery_for_order foreign key(ID_order) references order_table(ID_order) -); - -create table price( + constraint fk_delivered_by_deliver_man foreign key(ID_user) references project.delivery_man(ID_user), + constraint fk_delivery_for_order foreign key(ID_order) references project.order_table(ID_order) +); + +create table project.price( ID_price serial primary key, amount integer not null, @@ -174,52 +173,52 @@ price_till date, ID_part integer not null, - constraint fk_price_for_part foreign key(ID_part) references part(ID_part), + constraint fk_price_for_part foreign key(ID_part) references project.part(ID_part), constraint ck_amount_gt_0 check (amount>0) ); -create table category( +create table project.category( ID_category serial primary key, category_name varchar(100) not null, ID_parent_category integer, - constraint fk_category_parentcategory foreign key (ID_parent_category) references category(ID_category) -); - -create table part_is_from_category( + constraint fk_category_parentcategory foreign key (ID_parent_category) references project.category(ID_category) +); + +create table project.part_is_from_category( ID_part integer not null, ID_category integer not null, - constraint fk_part_is_from_category_part foreign key (ID_part) references part(ID_part), - constraint fk_part_is_from_category_category foreign key (ID_category) references category(ID_category) - -); - -create table repair_shop_is_authorized_for_car_make( + constraint fk_part_is_from_category_part foreign key (ID_part) references project.part(ID_part), + constraint fk_part_is_from_category_category foreign key (ID_category) references project.category(ID_category) + +); + +create table project.repair_shop_is_authorized_for_car_make( ID_repair_shop integer not null, ID_car_manufacturer integer not null, - constraint fk_repair_shop_is_authorized_for_car_make_repair_shop foreign key (ID_repair_shop) references repair_shop(ID_repair_shop), - constraint fk_repair_shop_is_authorized_for_car_make_car_manufacturer foreign key (ID_car_manufacturer) references car_manufacturer(ID_car_manufacturer) -); - -create table order_contains_part( + constraint fk_repair_shop_is_authorized_for_car_make_repair_shop foreign key (ID_repair_shop) references project.repair_shop(ID_repair_shop), + constraint fk_repair_shop_is_authorized_for_car_make_car_manufacturer foreign key (ID_car_manufacturer) references project.car_manufacturer(ID_car_manufacturer) +); + +create table project.order_contains_part( ID_part integer not null, ID_order integer not null, quantity_order integer not null, - constraint fk_order_contains_part_part foreign key (ID_part) references part(ID_part), - constraint fk_order_contains_part_order foreign key (ID_order) references order_table(ID_order), + constraint fk_order_contains_part_part foreign key (ID_part) references project.part(ID_part), + constraint fk_order_contains_part_order foreign key (ID_order) references project.order_table(ID_order), constraint ck_quantity_order check (quantity_order>0) ); -create table part_is_in_stock_in_warehouse( +create table project.part_is_in_stock_in_warehouse( ID_part integer not null, ID_warehouse integer not null, quantity_warehouse integer not null, - constraint fk_part_is_in_stock_in_warehouse_part foreign key (ID_part) references part(ID_part), - constraint fk_part_is_in_stock_in_warehouse_warehouse foreign key (ID_warehouse) references warehouse(ID_warehouse), + constraint fk_part_is_in_stock_in_warehouse_part foreign key (ID_part) references project.part(ID_part), + constraint fk_part_is_in_stock_in_warehouse_warehouse foreign key (ID_warehouse) references project.warehouse(ID_warehouse), constraint ck_quantity_warehouse check (quantity_warehouse>0) ); -create table part_is_appropriate_for_car( +create table project.part_is_appropriate_for_car( ID_part integer not null, ID_car integer not null, - constraint fk_part_is_appropriate_for_car_part foreign key (ID_part) references part(ID_part), - constraint fk_part_is_appropriate_for_car_car foreign key (ID_car) references car(ID_car) -); + constraint fk_part_is_appropriate_for_car_part foreign key (ID_part) references project.part(ID_part), + constraint fk_part_is_appropriate_for_car_car foreign key (ID_car) references project.car(ID_car) +); Index: database_scripts/polnenje_v2.sql =================================================================== --- database_scripts/polnenje_v2.sql (revision f094f6c409345b74661afd84bd147a6a076a8967) +++ database_scripts/polnenje_v2.sql (revision 101cbe2e9b2ba230315e8bd061810f42c4781f3c) @@ -3,7 +3,7 @@ ('stefanmileski4','stefanmileski4@gmail.com','Stefan Mileski','stefanpassword'), ('hristijansazdovski3','hristijansazdovski3@gmail.com','Hristijan Sazdovski','hristijanpassword'), -('bojantrpeski2','bojantrpeski2@gmail.com','Bojan Trpeski','bojanpassword'), -('darkosasanski1','darkosasanski1@gmail.com','Darko Sasanski','darkopassword'), -('denicakjorvezir','denicakjorvezir@gmail.com','Denica Kjorvezir','denicapassword'), +('client1','bojantrpeski2@gmail.com','Bojan Trpeski','bojanpassword'), +('client2','darkosasanski1@gmail.com','Darko Sasanski','darkopassword'), +('client3','denicakjorvezir@gmail.com','Denica Kjorvezir','denicapassword'), ('deliveryman1','deliveryman1@gmail.com','Petko Petkovski','petko123'), ('deliveryman2','deliveryman2@gmail.com','Marko Markovski','marko123'), @@ -11,12 +11,24 @@ ('warehouseman1','warehouseman1@gmail.com','Viktor Petrovski','viktor123'), ('warehouseman2','warehouseman2@gmail.com','Vlatko Petrovski','vlatko123'), -('warehouseman3','warehouseman3@gmail.com','Vido Petrovski','vido123'); - -insert into project.users_table (username,email,name_user,password_user,phone_number) values -('stefanmileski5','stefanmileski5@gmail.com','Stefan Mileski','stefanklient','070 123 456'); +('warehouseman3','warehouseman3@gmail.com','Vido Petrovski','vido123'), +('client4','client4@gmail.com','Client Four','client4'), +('client5','client5@gmail.com','Client Five','client5'), +('client6','client6@gmail.com','Client Six','client6'), +('client7','client7@gmail.com','Client Seven','client7'), +('client8','client8@gmail.com','Client Eight','client8'), +('client9','client9@gmail.com','Client Nine','client9'), +('client10','client10@gmail.com','Client Ten','client10'), +('client11','client11@gmail.com','Client Eleven','client11'), +('client12','client12@gmail.com','Client Twelve','client12'), +('client13','client13@gmail.com','Client Thirteen','client13'), +('client14','client14@gmail.com','Client Fourteen','client14'), +('client15','client15@gmail.com','Client Fifteen','client15'); + +--insert into project.users_table (username,email,name_user,password_user,phone_number) values +--('stefanmileski5','stefanmileski5@gmail.com','Stefan Mileski','stefanklient','070 123 456'); insert into project.administrator (id_user,authorized_from, authorized_till) values (1,'2022-01-01','2024-01-01'),(2,'2022-01-01','2025-01-01'),(3,'2022-01-01','2026-01-01'); -insert into project.client (id_user) values (13),(4),(5),(6); +insert into project.client (id_user) values (4),(5),(6),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24); insert into project.delivery_man (id_user,employed_from) values (7,now()),(8,now()),(9,now()); @@ -29,5 +41,7 @@ ('Bosch','Germany'), ('Continental','Belgium'), -('Mahle','Netherlands'); +('Mahle','Netherlands'), +('Sachs','Germany'), +('Brembo','Italy'); insert into project.car_manufacturer (cm_name,cm_country) values @@ -39,21 +53,40 @@ insert into project.repair_shop (rs_name,rs_location,rs_phone_number) values -('Toyota Service','Partizanska','070 123 456'), ('Volkswagen Service','Ilinenska','075 876 543'), ('Fiat Service','Vodnjanska','078 555 666'), +('Ford Service','Teodosij Gologanov','071 333 444'), +('Toyota Service','Partizanska','070 123 456'), ('Hyndai Service','Boris Trajkovski','075 500 000'); insert into project.car(in_production_since,in_production_till,car_type,id_car_manufacturer) values (2001,2004,'Golf 4',1), -(2006,2009,'Punto',2), (2004,2006,'Golf 5',1), +(2001,2003,'Punto',2), +(2004,2008,'Punto Evo',2), +(2009,2012,'Grande Punto',2), +(2007,2014,'Doblo',2), (2000,2008,'Fiesta',3), +(2005,2015,'Focus',3), +(2003,2006,'Aygo',4), +(2007,2015,'Yaris',4), +(2015,2022,'Corollaa',4), (1995,2005,'Coupe',5); insert into project.car_sample(vin,year_of_production, engine_power, displacement, fuel_type, km_driven, ID_user, ID_car) values -(1111,2008,65,1200,'Diesel',120000,4,4), -(2222,2010,80,1400,'Diesel',150000,5,4), -(3333,2016,65,1200,'Petrol/Gas',65000,6,2), -(4444,1997,140,2000,'Petrol/Gas',185000,13,5); +(4444,2003,65,1200,'Diesel',120000,4,1), +(5555,2005,80,1400,'Diesel',150000,5,2), +(6666,2002,65,1200,'Petrol/Gas',65000,6,3), +(1313,2006,140,2000,'Diesel',185000,13,4), +(1414,2006,120,1900,'Diesel',240000,14,5), +(1515,2006,95,1600,'Petrol',85000,15,6), +(1616,2006,80,1400,'Petrol/Gas',125000,16,7), +(1717,2006,69,1200,'Petrol',113000,17,8), +(1818,2006,65,1200,'Petrol/Gas',150000,18,9), +(1919,2006,140,1600,'Diesel',140000,19,10), +(2020,2017,200,2400,'Diesel',20000,20,11), +(2121,1996,200,2000,'Petrol/Gas',320000,21,12), +(2222,2004,120,1600,'Diesel',115000,22,3), +(2323,2003,140,2000,'Petrol/Gas',105000,23,3), +(2424,2000,120,1900,'Diesel',150000,24,1); insert into project.part(part_name, id_part_manufacturer) values @@ -61,50 +94,131 @@ ('Wheel 20CM',2), ('Shift 6GEARS',3), -('Radio WITH AUX',1), +('Radio with AUX',1), ('Rear Bumper FOR VW GOLF 4',2), -('Headlight FOR PUNTO',3); - -insert into project.category(category_name) values ('Brakes'),('Cooling System'),('Electrical'),('Engine components'),('Exterior'),('Filters'); +('Headlight FOR PUNTO',3), +('Air Conditioning',4), +('Hand Brake',5), +('Siren',4), +('Electrical window buttons',5), +('Sport filter',4), +('Air filter',5), +('Fuel filter',4), +('Oil filter',5); + +insert into project.category(category_name) values ('Brakes'),('Cooling System'),('Electrical'),('Engine components'),('Exterior'),('Filters'),('Interior'),('Shift'); insert into project.order_table(order_status,order_date,id_user) values -('created',now(),4), -('created',now(),5), -('created',now(),6), -('created',now(),13); - -insert into project.service_book (vin) values (1111),(2222),(3333),(4444); +('created',now()-interval'12months',4), +('created',now()-interval'6months',4), +('created',now()-interval'7months',4), +('created',now()-interval'1month',4), +('created',now()-interval'11months',5), +('created',now()-interval'10months',6), +('created',now()-interval'2months',6), +('created',now()-interval'9months',13), +('created',now()-interval'8months',14), +('created',now()-interval'7months',15), +('created',now()-interval'6months',16), +('created',now()-interval'9months',17), +('created',now()-interval'5months',17), +('created',now()-interval'4months',18), +('created',now()-interval'3months',19), +('created',now()-interval'2months',20), +('created',now()-interval'1month',21), +('created',now()-interval'12months',22), +('created',now(),23), +('created',now()-interval'3months',24), +('created',now(),24); + + +insert into project.service_book (vin) values (4444),(5555),(6666),(1313),(1414),(1515),(1616),(1717),(1818),(1919),(2020),(2121),(2222),(2323),(2424); insert into project.delivery (delivery_status, delivery_address,id_user,id_order) values -('in progress','Aerodrom',7,3), ('finished','Pintija',8,1), -('in progress','Kisela Voda',9,2), -('in progress','Kozle',8,4); +('finished','Kisela Voda',9,2), +('finished','Aerodrom',7,3), +('in progress','Kozle',8,4), +('finished', 'Radisani',7,5), +('finished','Dracevo',8,6), +('in progress','Gjorce',9,7), +('finished','Novo lisice',7,8), +('finished','Lisice',8,9), +('finished', 'Butel',7,10), +('finished','Pintija',8,11), +('finished','Kisela Voda',9,12), +('finished','Radisani',7,13), +('finished','Nerezi',8,14), +('finished', 'Aerodrom',7,15), +('finished', 'Aerodrom',9,16), +('in progress', 'Novo Lisice',9,17), +('finished', 'Butel',7,18), +('in progress', 'Kozle',8,19), +('finished', 'Aerodrom',9,20), +('in progress', 'Gjorce',8,21); + + insert into project.price (amount, price_from, id_part) values +(3000,now(),1), (1000,now(),2), (2000,now(),3), -(3000,now(),1), (1500,now(),4), (3500,now(),5), -(300,now(),6); - -insert into project.repair (vin, id_repair_shop, id_service_book) values (1111,3,1),(2222,2,2),(3333,1,3),(4444, 2, 4); -insert into project.repair (vin, id_repair_shop, id_service_book) values (1111,2,1) - -insert into project.review (review_rating, id_repair, id_user) values (10, 1, 4), (6, 2, 5), (8, 3, 6), (9, 4, 13); -insert into project.review (review_rating, id_repair, id_user) values (7, 5, 4) - -insert into project.part_is_from_category (id_part, id_category) values (5,5), (1,4), (6,5); - -insert into project.repair_shop_is_authorized_for_car_make (id_repair_shop, id_car_manufacturer) values (1,4),(2,1),(3,2),(4,5); - -insert into project.order_contains_part (id_order, id_part, quantity_order) values (1,3,1),(2,4,2),(3,1,2),(4,6,1); - -insert into project.part_is_in_stock_in_warehouse (id_part,id_warehouse,quantity_warehouse) values (1,2,100), (2,1,150), (3,1,50), (4,3,75),(5,1,30),(6,2,70); - -insert into project.part_is_appropriate_for_car (id_part, id_car) values (1,1),(1,2),(1,3),(2,1),(2,4),(2,5),(5,1),(4,1),(4,2),(4,3),(4,4),(4,5),(3,5),(6,2); - - - +(300,now(),6), +(4300,now(),7), +(900,now(),8), +(500,now(),9), +(1300,now(),10), +(3500,now(),11), +(3600,now(),12), +(4500,now(),13), +(4100,now(),14); + + +insert into project.repair (vin, id_repair_shop, id_service_book) values +(4444,1,1), +(5555,1,2), +(6666,2,3), +(1313,2,4), +(1414,2,5), +(1515,2,6), +(1616,3,7), +(1717,3,8), +(1818,4,9), +(1919,4,10), +(2020,4,11), +(2121,5,12), +(2222,2,13), +(2323,2,14), +(2424,1,15); + + +insert into project.part_is_from_category (id_part, id_category) values (1,4),(2,7),(3,8),(4,3),(5,5),(6,5),(7,2),(8,1),(9,3),(10,3),(11,6),(12,6),(13,6),(14,6); + +insert into project.repair_shop_is_authorized_for_car_make (id_repair_shop, id_car_manufacturer) values (1,1),(2,2),(3,3),(4,4),(5,5); + + +insert into project.review (review_rating, id_repair, id_user) values (10,1,4),(6,2,5),(7,3,6), +(8,4,13),(9,5,14),(10,6,15),(5,7,16),(3,8,17),(4,9,18),(10,10,19),(9,11,20),(7,12,21),(8,13,22),(9,14,23),(10,15,24); + + +insert into project.part_is_in_stock_in_warehouse (id_part,id_warehouse,quantity_warehouse) values (1,2,100), (2,1,150), (3,1,50), (4,3,75),(5,1,30),(6,2,70), +(7,3,10),(8,2,20),(9,3,50),(10,3,25),(11,1,16),(12,2,24),(13,1,20),(14,2,70); + + +insert into project.part_is_appropriate_for_car (id_part, id_car) values +(1,1),(1,2),(2,3),(2,4),(2,5),(2,6),(3,8),(3,11),(3,12),(4,1),(4,2),(4,3),(4,4),(4,5),(4,6),(4,7),(4,8),(5,1),(6,3),(6,4),(7,9),(7,10), +(7,11),(8,12),(9,1),(9,2),(9,7),(9,8),(10,1),(10,2),(10,12),(11,3),(11,4),(11,5),(12,7),(12,8),(13,6),(14,1),(14,2); + + + +insert into project.order_contains_part (id_order, id_part, quantity_order) values +(1,1,2),(1,4,1),(2,14,1),(3,10,2),(3,5,2),(4,4,3),(4,1,1),(5,1,2),(5,4,1),(5,14,1),(6,11,2),(7,2,2),(7,6,1),(7,11,1), +(8,2,1),(9,11,2),(10,13,1),(11,9,1),(11,12,1),(12,3,1),(12,4,1),(13,9,2),(14,7,1),(15,7,1),(16,7,1),(17,10,1),(17,8,1), +(18,2,1),(19,4,1),(19,6,2),(19,11,2),(20,1,2),(21,9,1); + + + +-- Important Use Cases select p.part_name, project.repair.vin,id_repair_shop from project.repair join project.car_sample cs on cs.vin = project.repair.vin @@ -136,8 +250,9 @@ select part_name from project.part -drop view project.repair_shop_reviews_summary +-- извештај за сите сервиси и за сите број на reviews и avg рејтинг +drop view project.repair_shop_reviews_summary; create or replace view project.repair_shop_reviews_summary as -select rs.id_repair_shop as rsid , rs.rs_name as rsname , count(r2.review_rating) as reviewcount ,avg(r2.review_rating)::float as reviewaverage +select rs.id_repair_shop as rs_id , rs.rs_name as rs_name , count(r2.review_rating) as review_count ,avg(r2.review_rating)::float as review_average from project.repair_shop rs left join project.repair r on r.id_repair_shop = rs.id_repair_shop @@ -147,2 +262,146 @@ select * from project.repair_shop_reviews_summary +--извештај за сите доставувачи, која е најдобра нарачка што ја направил и кој производ е најскап во таа нарачка +drop view project.deliveryman_summary + +create or replace view project.deliveryman_summary as + select nar.deliverer, nar.deliverer_username, + max(nar.nar_suma) as najdobra_naracka, max(par.par_suma) as najskap_proizvod + from project.delivery_man d + left join + ( + select d.id_user as deliverer, ut.username as deliverer_username, d2.id_delivery as delivery_num + ,sum(p2.amount*ocp.quantity_order) as nar_suma + from project.delivery_man d + left join project.users_table ut on ut.id_user = d.id_user + left join project.delivery d2 on d2.id_user = d.id_user + left join project.order_contains_part ocp on ocp.id_order = d2.id_order + left join project.part p on p.id_part = ocp.id_part + left join project.price p2 on p2.id_part = p.id_part + group by (d.id_user,ut.username,delivery_num) + ) as nar on d.id_user = nar.deliverer + left join + ( + select d.id_user as deliverer, ocp.id_order as order_num,d2.id_delivery as delivery_num, p.part_name as part_name, + sum(p2.amount*ocp.quantity_order) as par_suma + from project.delivery_man d + left join project.users_table ut on ut.id_user = d.id_user + left join project.delivery d2 on d2.id_user = d.id_user + left join project.order_contains_part ocp on ocp.id_order = d2.id_order + left join project.part p on p.id_part = ocp.id_part + left join project.price p2 on p2.id_part = p.id_part + group by (d.id_user,ocp.id_order,d2.id_delivery,p.part_name) + ) as par on nar.deliverer = par.deliverer and nar.delivery_num = par.delivery_num + group by nar.deliverer,nar.deliverer_username + order by najdobra_naracka desc + +select * from project.deliveryman_summary + +--извештај за сите доставувачи, број на нарачки, вкупна вредност на сите нарачки што ги направил +drop view project.deliveryman_count_sum + +create or replace view project.deliveryman_count_sum as + select nar.deliverer, nar.deliverer_username, count(distinct(nar.delivery_num)) as vkupno_dostavi, + sum(nar.nar_suma) as vkupna_suma + from project.delivery_man d + left join + ( + select d.id_user as deliverer, ut.username as deliverer_username, d2.id_delivery as delivery_num + ,sum(p2.amount*ocp.quantity_order) as nar_suma + from project.delivery_man d + left join project.users_table ut on ut.id_user = d.id_user + left join project.delivery d2 on d2.id_user = d.id_user + left join project.order_contains_part ocp on ocp.id_order = d2.id_order + left join project.part p on p.id_part = ocp.id_part + left join project.price p2 on p2.id_part = p.id_part + group by (d.id_user,ut.username,delivery_num) + ) as nar on d.id_user = nar.deliverer + group by nar.deliverer,nar.deliverer_username + order by vkupna_suma desc + +select * from project.deliveryman_count_sum + +-- извештај за сите категории и за кој прозиводител на коли биле купени највеќе делови од таа категорија +select c.id_category, c.category_name, cm.id_car_manufacturer , cm.cm_name , +count(ocp.id_part) as category_by_car +from project.category as c +left join project.part_is_from_category pifc on pifc.id_category = c.id_category +left join project.part p on p.id_part = pifc.id_part +left join project.order_contains_part ocp on ocp.id_part = p.id_part +left join project.order_table ot on ot.id_order = ocp.id_order +left join project.users_table ut on ut.id_user = ot.id_user +left join project.car_sample cs on cs.id_user = ut.id_user +left join project.car c2 on c2.id_car = cs.id_car +left join project.car_manufacturer cm on cm.id_car_manufacturer = c2.id_car_manufacturer +group by c.id_category,cm.id_car_manufacturer +order by category_by_car desc + + +-- Извештај за најкупуван артикл, во која количина и од која категорија е истиот +select c.category_name , p.part_name , coalesce(sum(ocp2.quantity_order),0) as maxkol from project.part p + left join project.part_is_from_category pifc on pifc.id_part = p.id_part + left join project.category c on c.id_category = pifc.id_category + left join project.order_contains_part ocp2 on ocp2.id_part = p.id_part + group by c.id_category , p.id_part + having coalesce(sum(ocp2.quantity_order), 0)=( + select max(maxkol) from + ( + select coalesce(sum(ocp2.quantity_order),0) as maxkol from project.part p + left join project.part_is_from_category pifc on pifc.id_part = p.id_part + left join project.category c on c.id_category = pifc.id_category + left join project.order_contains_part ocp2 on ocp2.id_part = p.id_part + group by c.id_category , p.id_part + ) q1 + ) + + +--Извештај за сите делови, со нивни категории и производители, и количината која била нарачана од овие делови во секој од последните 3 месеци. +select p.id_part, p.part_name, cat.category_name, pm.pm_name, f.narachani_vo_ovoj_mesec as narachani_ovoj_mesec, s.narachani_vo_prethoden_mesec as narachani_prethoden_mesec, t.narachani_vo_predprethoden_mesec as narachani_predprethoden_mesec +from project.part as p +join project.part_manufacturer as pm on p.id_part_manufacturer = pm.id_part_manufacturer +join project.part_is_from_category as pifc on p.id_part = pifc.id_part +join project.category as cat on pifc.id_category = cat.id_category +left join project.order_contains_part as ocp on p.id_part = ocp.id_part +left join project.order_table as o on ocp.id_order = o.id_order +left join ( +select p.id_part, p.part_name, cat.category_name, pm.pm_name, + count(o.id_order) as narachani_vo_ovoj_mesec +from project.part as p +join project.part_manufacturer as pm on p.id_part_manufacturer = pm.id_part_manufacturer +join project.part_is_from_category as pifc on p.id_part = pifc.id_part +join project.category as cat on pifc.id_category = cat.id_category +left join project.order_contains_part as ocp on p.id_part = ocp.id_part +left join project.order_table as o on ocp.id_order = o.id_order + and + extract(month from o.order_date) = extract(month from now()) and extract(year from o.order_date) = extract(year from now()) +group by p.id_part, p.part_name, cat.category_name, pm.pm_name +) as f on p.id_part = f.id_part and pm.pm_name = f.pm_name +left join ( +select p.id_part, p.part_name, cat.category_name, pm.pm_name, + count(o.id_order) as narachani_vo_prethoden_mesec +from project.part as p +join project.part_manufacturer as pm on p.id_part_manufacturer = pm.id_part_manufacturer +join project.part_is_from_category as pifc on p.id_part = pifc.id_part +join project.category as cat on pifc.id_category = cat.id_category +left join project.order_contains_part as ocp on p.id_part = ocp.id_part +left join project.order_table as o on ocp.id_order = o.id_order + and + extract(month from o.order_date) = extract(month from now()-interval'1 month') and extract(year from o.order_date) = extract(year from now()-interval'1 month') +group by p.id_part, p.part_name, cat.category_name, pm.pm_name +) as s on p.id_part = s.id_part and pm.pm_name = s.pm_name +left join ( +select p.id_part, p.part_name, cat.category_name, pm.pm_name, + count(o.id_order) as narachani_vo_predprethoden_mesec +from project.part as p +join project.part_manufacturer as pm on p.id_part_manufacturer = pm.id_part_manufacturer +join project.part_is_from_category as pifc on p.id_part = pifc.id_part +join project.category as cat on pifc.id_category = cat.id_category +left join project.order_contains_part as ocp on p.id_part = ocp.id_part +left join project.order_table as o on ocp.id_order = o.id_order + and + extract(month from o.order_date) = extract(month from now()-interval'2 month') and extract(year from o.order_date) = extract(year from now()-interval'2 month') +group by p.id_part, p.part_name, cat.category_name, pm.pm_name +) as t on p.id_part = t.id_part and pm.pm_name = t.pm_name +group by p.id_part, p.part_name, cat.category_name, pm.pm_name, f.narachani_vo_ovoj_mesec, s.narachani_vo_prethoden_mesec, t.narachani_vo_predprethoden_mesec + +