| 1 | insert into project.users_table (username,email,name_user,password_user) values
|
|---|
| 2 | ('andrejtodorovski5','andrejtodorovski5@gmail.com','Andrej Todorovski','andrejpassword'),
|
|---|
| 3 | ('stefanmileski4','stefanmileski4@gmail.com','Stefan Mileski','stefanpassword'),
|
|---|
| 4 | ('hristijansazdovski3','hristijansazdovski3@gmail.com','Hristijan Sazdovski','hristijanpassword'),
|
|---|
| 5 | ('client1','bojantrpeski2@gmail.com','Bojan Trpeski','bojanpassword'),
|
|---|
| 6 | ('client2','darkosasanski1@gmail.com','Darko Sasanski','darkopassword'),
|
|---|
| 7 | ('client3','denicakjorvezir@gmail.com','Denica Kjorvezir','denicapassword'),
|
|---|
| 8 | ('deliveryman1','deliveryman1@gmail.com','Petko Petkovski','petko123'),
|
|---|
| 9 | ('deliveryman2','deliveryman2@gmail.com','Marko Markovski','marko123'),
|
|---|
| 10 | ('deliveryman3','deliveryman3@gmail.com','Petar Petrovski','petar123'),
|
|---|
| 11 | ('warehouseman1','warehouseman1@gmail.com','Viktor Petrovski','viktor123'),
|
|---|
| 12 | ('warehouseman2','warehouseman2@gmail.com','Vlatko Petrovski','vlatko123'),
|
|---|
| 13 | ('warehouseman3','warehouseman3@gmail.com','Vido Petrovski','vido123'),
|
|---|
| 14 | ('client4','client4@gmail.com','Client Four','client4'),
|
|---|
| 15 | ('client5','client5@gmail.com','Client Five','client5'),
|
|---|
| 16 | ('client6','client6@gmail.com','Client Six','client6'),
|
|---|
| 17 | ('client7','client7@gmail.com','Client Seven','client7'),
|
|---|
| 18 | ('client8','client8@gmail.com','Client Eight','client8'),
|
|---|
| 19 | ('client9','client9@gmail.com','Client Nine','client9'),
|
|---|
| 20 | ('client10','client10@gmail.com','Client Ten','client10'),
|
|---|
| 21 | ('client11','client11@gmail.com','Client Eleven','client11'),
|
|---|
| 22 | ('client12','client12@gmail.com','Client Twelve','client12'),
|
|---|
| 23 | ('client13','client13@gmail.com','Client Thirteen','client13'),
|
|---|
| 24 | ('client14','client14@gmail.com','Client Fourteen','client14'),
|
|---|
| 25 | ('client15','client15@gmail.com','Client Fifteen','client15');
|
|---|
| 26 |
|
|---|
| 27 | --insert into project.users_table (username,email,name_user,password_user,phone_number) values
|
|---|
| 28 | --('stefanmileski5','stefanmileski5@gmail.com','Stefan Mileski','stefanklient','070 123 456');
|
|---|
| 29 |
|
|---|
| 30 | 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');
|
|---|
| 31 |
|
|---|
| 32 | insert into project.client (id_user) values (4),(5),(6),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24);
|
|---|
| 33 |
|
|---|
| 34 | insert into project.delivery_man (id_user,employed_from) values (7,now()),(8,now()),(9,now());
|
|---|
| 35 |
|
|---|
| 36 | insert into project.warehouse (warehouse_location) values ('Radishani'),('Kozle'),('Vizbegovo');
|
|---|
| 37 |
|
|---|
| 38 | insert into project.warehouseman (id_user,employed_from,id_warehouse) values (10,now(),1),(11,now(),2),(12,now(),3);
|
|---|
| 39 |
|
|---|
| 40 | insert into project.part_manufacturer (pm_name,pm_location) values
|
|---|
| 41 | ('Bosch','Germany'),
|
|---|
| 42 | ('Continental','Belgium'),
|
|---|
| 43 | ('Mahle','Netherlands'),
|
|---|
| 44 | ('Sachs','Germany'),
|
|---|
| 45 | ('Brembo','Italy');
|
|---|
| 46 |
|
|---|
| 47 | insert into project.car_manufacturer (cm_name,cm_country) values
|
|---|
| 48 | ('Volkswagen','Germany'),
|
|---|
| 49 | ('Fiat','Italy'),
|
|---|
| 50 | ('Ford','USA'),
|
|---|
| 51 | ('Toyota','Japan'),
|
|---|
| 52 | ('Hyndai','Korea');
|
|---|
| 53 |
|
|---|
| 54 | insert into project.repair_shop (rs_name,rs_location,rs_phone_number) values
|
|---|
| 55 | ('Volkswagen Service','Ilinenska','075 876 543'),
|
|---|
| 56 | ('Fiat Service','Vodnjanska','078 555 666'),
|
|---|
| 57 | ('Ford Service','Teodosij Gologanov','071 333 444'),
|
|---|
| 58 | ('Toyota Service','Partizanska','070 123 456'),
|
|---|
| 59 | ('Hyndai Service','Boris Trajkovski','075 500 000');
|
|---|
| 60 |
|
|---|
| 61 | insert into project.car(in_production_since,in_production_till,car_type,id_car_manufacturer) values
|
|---|
| 62 | (2001,2004,'Golf 4',1),
|
|---|
| 63 | (2004,2006,'Golf 5',1),
|
|---|
| 64 | (2001,2003,'Punto',2),
|
|---|
| 65 | (2004,2008,'Punto Evo',2),
|
|---|
| 66 | (2009,2012,'Grande Punto',2),
|
|---|
| 67 | (2007,2014,'Doblo',2),
|
|---|
| 68 | (2000,2008,'Fiesta',3),
|
|---|
| 69 | (2005,2015,'Focus',3),
|
|---|
| 70 | (2003,2006,'Aygo',4),
|
|---|
| 71 | (2007,2015,'Yaris',4),
|
|---|
| 72 | (2015,2022,'Corollaa',4),
|
|---|
| 73 | (1995,2005,'Coupe',5);
|
|---|
| 74 |
|
|---|
| 75 | insert into project.car_sample(vin,year_of_production, engine_power, displacement, fuel_type, km_driven, ID_user, ID_car) values
|
|---|
| 76 | (4444,2003,65,1200,'Diesel',120000,4,1),
|
|---|
| 77 | (5555,2005,80,1400,'Diesel',150000,5,2),
|
|---|
| 78 | (6666,2002,65,1200,'Petrol/Gas',65000,6,3),
|
|---|
| 79 | (1313,2006,140,2000,'Diesel',185000,13,4),
|
|---|
| 80 | (1414,2006,120,1900,'Diesel',240000,14,5),
|
|---|
| 81 | (1515,2006,95,1600,'Petrol',85000,15,6),
|
|---|
| 82 | (1616,2006,80,1400,'Petrol/Gas',125000,16,7),
|
|---|
| 83 | (1717,2006,69,1200,'Petrol',113000,17,8),
|
|---|
| 84 | (1818,2006,65,1200,'Petrol/Gas',150000,18,9),
|
|---|
| 85 | (1919,2006,140,1600,'Diesel',140000,19,10),
|
|---|
| 86 | (2020,2017,200,2400,'Diesel',20000,20,11),
|
|---|
| 87 | (2121,1996,200,2000,'Petrol/Gas',320000,21,12),
|
|---|
| 88 | (2222,2004,120,1600,'Diesel',115000,22,3),
|
|---|
| 89 | (2323,2003,140,2000,'Petrol/Gas',105000,23,3),
|
|---|
| 90 | (2424,2000,120,1900,'Diesel',150000,24,1);
|
|---|
| 91 |
|
|---|
| 92 | insert into project.part(part_name, id_part_manufacturer) values
|
|---|
| 93 | ('Engine G4GF',1),
|
|---|
| 94 | ('Wheel 20CM',2),
|
|---|
| 95 | ('Shift 6GEARS',3),
|
|---|
| 96 | ('Radio with AUX',1),
|
|---|
| 97 | ('Rear Bumper FOR VW GOLF 4',2),
|
|---|
| 98 | ('Headlight FOR PUNTO',3),
|
|---|
| 99 | ('Air Conditioning',4),
|
|---|
| 100 | ('Hand Brake',5),
|
|---|
| 101 | ('Siren',4),
|
|---|
| 102 | ('Electrical window buttons',5),
|
|---|
| 103 | ('Sport filter',4),
|
|---|
| 104 | ('Air filter',5),
|
|---|
| 105 | ('Fuel filter',4),
|
|---|
| 106 | ('Oil filter',5);
|
|---|
| 107 |
|
|---|
| 108 | insert into project.category(category_name) values ('Brakes'),('Cooling System'),('Electrical'),('Engine components'),('Exterior'),('Filters'),('Interior'),('Shift');
|
|---|
| 109 |
|
|---|
| 110 | insert into project.order_table(order_status,order_date,id_user) values
|
|---|
| 111 | ('created',now()-interval'12months',4),
|
|---|
| 112 | ('created',now()-interval'6months',4),
|
|---|
| 113 | ('created',now()-interval'7months',4),
|
|---|
| 114 | ('created',now()-interval'1month',4),
|
|---|
| 115 | ('created',now()-interval'11months',5),
|
|---|
| 116 | ('created',now()-interval'10months',6),
|
|---|
| 117 | ('created',now()-interval'2months',6),
|
|---|
| 118 | ('created',now()-interval'9months',13),
|
|---|
| 119 | ('created',now()-interval'8months',14),
|
|---|
| 120 | ('created',now()-interval'7months',15),
|
|---|
| 121 | ('created',now()-interval'6months',16),
|
|---|
| 122 | ('created',now()-interval'9months',17),
|
|---|
| 123 | ('created',now()-interval'5months',17),
|
|---|
| 124 | ('created',now()-interval'4months',18),
|
|---|
| 125 | ('created',now()-interval'3months',19),
|
|---|
| 126 | ('created',now()-interval'2months',20),
|
|---|
| 127 | ('created',now()-interval'1month',21),
|
|---|
| 128 | ('created',now()-interval'12months',22),
|
|---|
| 129 | ('created',now(),23),
|
|---|
| 130 | ('created',now()-interval'3months',24),
|
|---|
| 131 | ('created',now(),24);
|
|---|
| 132 |
|
|---|
| 133 |
|
|---|
| 134 | insert into project.service_book (vin) values (4444),(5555),(6666),(1313),(1414),(1515),(1616),(1717),(1818),(1919),(2020),(2121),(2222),(2323),(2424);
|
|---|
| 135 |
|
|---|
| 136 | insert into project.delivery (delivery_status, delivery_address,id_user,id_order) values
|
|---|
| 137 | ('finished','Pintija',8,1),
|
|---|
| 138 | ('finished','Kisela Voda',9,2),
|
|---|
| 139 | ('finished','Aerodrom',7,3),
|
|---|
| 140 | ('in progress','Kozle',8,4),
|
|---|
| 141 | ('finished', 'Radisani',7,5),
|
|---|
| 142 | ('finished','Dracevo',8,6),
|
|---|
| 143 | ('in progress','Gjorce',9,7),
|
|---|
| 144 | ('finished','Novo lisice',7,8),
|
|---|
| 145 | ('finished','Lisice',8,9),
|
|---|
| 146 | ('finished', 'Butel',7,10),
|
|---|
| 147 | ('finished','Pintija',8,11),
|
|---|
| 148 | ('finished','Kisela Voda',9,12),
|
|---|
| 149 | ('finished','Radisani',7,13),
|
|---|
| 150 | ('finished','Nerezi',8,14),
|
|---|
| 151 | ('finished', 'Aerodrom',7,15),
|
|---|
| 152 | ('finished', 'Aerodrom',9,16),
|
|---|
| 153 | ('in progress', 'Novo Lisice',9,17),
|
|---|
| 154 | ('finished', 'Butel',7,18),
|
|---|
| 155 | ('in progress', 'Kozle',8,19),
|
|---|
| 156 | ('finished', 'Aerodrom',9,20),
|
|---|
| 157 | ('in progress', 'Gjorce',8,21);
|
|---|
| 158 |
|
|---|
| 159 |
|
|---|
| 160 |
|
|---|
| 161 | insert into project.price (amount, price_from, id_part) values
|
|---|
| 162 | (3000,now(),1),
|
|---|
| 163 | (1000,now(),2),
|
|---|
| 164 | (2000,now(),3),
|
|---|
| 165 | (1500,now(),4),
|
|---|
| 166 | (3500,now(),5),
|
|---|
| 167 | (300,now(),6),
|
|---|
| 168 | (4300,now(),7),
|
|---|
| 169 | (900,now(),8),
|
|---|
| 170 | (500,now(),9),
|
|---|
| 171 | (1300,now(),10),
|
|---|
| 172 | (3500,now(),11),
|
|---|
| 173 | (3600,now(),12),
|
|---|
| 174 | (4500,now(),13),
|
|---|
| 175 | (4100,now(),14);
|
|---|
| 176 |
|
|---|
| 177 |
|
|---|
| 178 | insert into project.repair (vin, id_repair_shop, id_service_book) values
|
|---|
| 179 | (4444,1,1),
|
|---|
| 180 | (5555,1,2),
|
|---|
| 181 | (6666,2,3),
|
|---|
| 182 | (1313,2,4),
|
|---|
| 183 | (1414,2,5),
|
|---|
| 184 | (1515,2,6),
|
|---|
| 185 | (1616,3,7),
|
|---|
| 186 | (1717,3,8),
|
|---|
| 187 | (1818,4,9),
|
|---|
| 188 | (1919,4,10),
|
|---|
| 189 | (2020,4,11),
|
|---|
| 190 | (2121,5,12),
|
|---|
| 191 | (2222,2,13),
|
|---|
| 192 | (2323,2,14),
|
|---|
| 193 | (2424,1,15);
|
|---|
| 194 |
|
|---|
| 195 |
|
|---|
| 196 | 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);
|
|---|
| 197 |
|
|---|
| 198 | 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);
|
|---|
| 199 |
|
|---|
| 200 |
|
|---|
| 201 | insert into project.review (review_rating, id_repair, id_user) values (10,1,4),(6,2,5),(7,3,6),
|
|---|
| 202 | (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);
|
|---|
| 203 |
|
|---|
| 204 |
|
|---|
| 205 | 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),
|
|---|
| 206 | (7,3,10),(8,2,20),(9,3,50),(10,3,25),(11,1,16),(12,2,24),(13,1,20),(14,2,70);
|
|---|
| 207 |
|
|---|
| 208 |
|
|---|
| 209 | insert into project.part_is_appropriate_for_car (id_part, id_car) values
|
|---|
| 210 | (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),
|
|---|
| 211 | (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);
|
|---|
| 212 |
|
|---|
| 213 |
|
|---|
| 214 |
|
|---|
| 215 | insert into project.order_contains_part (id_order, id_part, quantity_order) values
|
|---|
| 216 | (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),
|
|---|
| 217 | (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),
|
|---|
| 218 | (18,2,1),(19,4,1),(19,6,2),(19,11,2),(20,1,2),(21,9,1);
|
|---|
| 219 |
|
|---|
| 220 |
|
|---|
| 221 |
|
|---|
| 222 | -- Important Use Cases
|
|---|
| 223 | select p.part_name, project.repair.vin,id_repair_shop from project.repair
|
|---|
| 224 | join project.car_sample cs on cs.vin = project.repair.vin
|
|---|
| 225 | join project.order_table ot on ot.id_user = cs.id_user
|
|---|
| 226 | join project.order_contains_part ocp on ocp.id_order = ot.id_order
|
|---|
| 227 | join project.part p on p.id_part = ocp.id_part
|
|---|
| 228 | where cs.id_user = 4
|
|---|
| 229 |
|
|---|
| 230 | select p.part_name, c.car_type from project.part p
|
|---|
| 231 | join project.part_is_appropriate_for_car piafc on piafc.id_part = p.id_part
|
|---|
| 232 | join project.car c on c.id_car = piafc.id_car
|
|---|
| 233 | join project.part_is_from_category pifc on pifc.id_part = p.id_part
|
|---|
| 234 | join project.category c2 on c2.id_category = pifc.id_category
|
|---|
| 235 | where c.car_type = 'Punto'
|
|---|
| 236 | and
|
|---|
| 237 | c2.category_name = 'Exterior'
|
|---|
| 238 |
|
|---|
| 239 | select ot.order_date , p.part_name , p2.amount , ocp.quantity_order , ot.order_status from project.order_table ot
|
|---|
| 240 | join project.order_contains_part ocp on ocp.id_order = ot.id_order
|
|---|
| 241 | join project.part p on p.id_part = ocp.id_part
|
|---|
| 242 | join project.price p2 on p2.id_part = p.id_part
|
|---|
| 243 | where ot.id_user = 4
|
|---|
| 244 |
|
|---|
| 245 | select r.review_rating, r.review_comment, rs.rs_name, rs.rs_location from project.review r
|
|---|
| 246 | join project.repair r2 on r2.id_repair = r.id_repair
|
|---|
| 247 | join project.repair_shop rs on rs.id_repair_shop = r2.id_repair_shop
|
|---|
| 248 | where r.id_user = 4
|
|---|
| 249 |
|
|---|
| 250 | select part_name from project.part
|
|---|
| 251 |
|
|---|
| 252 | -- извештај за сите сервиси и за сите број на reviews и avg рејтинг
|
|---|
| 253 | drop view project.repair_shop_reviews_summary;
|
|---|
| 254 |
|
|---|
| 255 | create or replace view project.repair_shop_reviews_summary as
|
|---|
| 256 | 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
|
|---|
| 257 | from project.repair_shop rs
|
|---|
| 258 | left join project.repair r on r.id_repair_shop = rs.id_repair_shop
|
|---|
| 259 | left join project.review r2 on r2.id_repair = r.id_repair
|
|---|
| 260 | group by rs.id_repair_shop
|
|---|
| 261 |
|
|---|
| 262 | select * from project.repair_shop_reviews_summary
|
|---|
| 263 |
|
|---|
| 264 | --извештај за сите доставувачи, која е најдобра нарачка што ја направил и кој производ е најскап во таа нарачка
|
|---|
| 265 | drop view project.deliveryman_summary
|
|---|
| 266 |
|
|---|
| 267 | create or replace view project.deliveryman_summary as
|
|---|
| 268 | select nar.deliverer, nar.deliverer_username,
|
|---|
| 269 | max(nar.nar_suma) as najdobra_naracka, max(par.par_suma) as najskap_proizvod
|
|---|
| 270 | from project.delivery_man d
|
|---|
| 271 | left join
|
|---|
| 272 | (
|
|---|
| 273 | select d.id_user as deliverer, ut.username as deliverer_username, d2.id_delivery as delivery_num
|
|---|
| 274 | ,sum(p2.amount*ocp.quantity_order) as nar_suma
|
|---|
| 275 | from project.delivery_man d
|
|---|
| 276 | left join project.users_table ut on ut.id_user = d.id_user
|
|---|
| 277 | left join project.delivery d2 on d2.id_user = d.id_user
|
|---|
| 278 | left join project.order_contains_part ocp on ocp.id_order = d2.id_order
|
|---|
| 279 | left join project.part p on p.id_part = ocp.id_part
|
|---|
| 280 | left join project.price p2 on p2.id_part = p.id_part
|
|---|
| 281 | group by (d.id_user,ut.username,delivery_num)
|
|---|
| 282 | ) as nar on d.id_user = nar.deliverer
|
|---|
| 283 | left join
|
|---|
| 284 | (
|
|---|
| 285 | select d.id_user as deliverer, ocp.id_order as order_num,d2.id_delivery as delivery_num, p.part_name as part_name,
|
|---|
| 286 | sum(p2.amount*ocp.quantity_order) as par_suma
|
|---|
| 287 | from project.delivery_man d
|
|---|
| 288 | left join project.users_table ut on ut.id_user = d.id_user
|
|---|
| 289 | left join project.delivery d2 on d2.id_user = d.id_user
|
|---|
| 290 | left join project.order_contains_part ocp on ocp.id_order = d2.id_order
|
|---|
| 291 | left join project.part p on p.id_part = ocp.id_part
|
|---|
| 292 | left join project.price p2 on p2.id_part = p.id_part
|
|---|
| 293 | group by (d.id_user,ocp.id_order,d2.id_delivery,p.part_name)
|
|---|
| 294 | ) as par on nar.deliverer = par.deliverer and nar.delivery_num = par.delivery_num
|
|---|
| 295 | group by nar.deliverer,nar.deliverer_username
|
|---|
| 296 | order by najdobra_naracka desc
|
|---|
| 297 |
|
|---|
| 298 | select * from project.deliveryman_summary
|
|---|
| 299 |
|
|---|
| 300 | --извештај за сите доставувачи, број на нарачки, вкупна вредност на сите нарачки што ги направил
|
|---|
| 301 | drop view project.deliveryman_count_sum
|
|---|
| 302 |
|
|---|
| 303 | create or replace view project.deliveryman_count_sum as
|
|---|
| 304 | select nar.deliverer, nar.deliverer_username, count(distinct(nar.delivery_num)) as vkupno_dostavi,
|
|---|
| 305 | sum(nar.nar_suma) as vkupna_suma
|
|---|
| 306 | from project.delivery_man d
|
|---|
| 307 | left join
|
|---|
| 308 | (
|
|---|
| 309 | select d.id_user as deliverer, ut.username as deliverer_username, d2.id_delivery as delivery_num
|
|---|
| 310 | ,sum(p2.amount*ocp.quantity_order) as nar_suma
|
|---|
| 311 | from project.delivery_man d
|
|---|
| 312 | left join project.users_table ut on ut.id_user = d.id_user
|
|---|
| 313 | left join project.delivery d2 on d2.id_user = d.id_user
|
|---|
| 314 | left join project.order_contains_part ocp on ocp.id_order = d2.id_order
|
|---|
| 315 | left join project.part p on p.id_part = ocp.id_part
|
|---|
| 316 | left join project.price p2 on p2.id_part = p.id_part
|
|---|
| 317 | group by (d.id_user,ut.username,delivery_num)
|
|---|
| 318 | ) as nar on d.id_user = nar.deliverer
|
|---|
| 319 | group by nar.deliverer,nar.deliverer_username
|
|---|
| 320 | order by vkupna_suma desc
|
|---|
| 321 |
|
|---|
| 322 | select * from project.deliveryman_count_sum
|
|---|
| 323 |
|
|---|
| 324 | -- извештај за сите категории и за кој прозиводител на коли биле купени највеќе делови од таа категорија
|
|---|
| 325 | select c.id_category, c.category_name, cm.id_car_manufacturer , cm.cm_name ,
|
|---|
| 326 | count(ocp.id_part) as category_by_car
|
|---|
| 327 | from project.category as c
|
|---|
| 328 | left join project.part_is_from_category pifc on pifc.id_category = c.id_category
|
|---|
| 329 | left join project.part p on p.id_part = pifc.id_part
|
|---|
| 330 | left join project.order_contains_part ocp on ocp.id_part = p.id_part
|
|---|
| 331 | left join project.order_table ot on ot.id_order = ocp.id_order
|
|---|
| 332 | left join project.users_table ut on ut.id_user = ot.id_user
|
|---|
| 333 | left join project.car_sample cs on cs.id_user = ut.id_user
|
|---|
| 334 | left join project.car c2 on c2.id_car = cs.id_car
|
|---|
| 335 | left join project.car_manufacturer cm on cm.id_car_manufacturer = c2.id_car_manufacturer
|
|---|
| 336 | group by c.id_category,cm.id_car_manufacturer
|
|---|
| 337 | order by category_by_car desc
|
|---|
| 338 |
|
|---|
| 339 |
|
|---|
| 340 | -- Извештај за најкупуван артикл, во која количина и од која категорија е истиот
|
|---|
| 341 | select c.category_name , p.part_name , coalesce(sum(ocp2.quantity_order),0) as maxkol from project.part p
|
|---|
| 342 | left join project.part_is_from_category pifc on pifc.id_part = p.id_part
|
|---|
| 343 | left join project.category c on c.id_category = pifc.id_category
|
|---|
| 344 | left join project.order_contains_part ocp2 on ocp2.id_part = p.id_part
|
|---|
| 345 | group by c.id_category , p.id_part
|
|---|
| 346 | having coalesce(sum(ocp2.quantity_order), 0)=(
|
|---|
| 347 | select max(maxkol) from
|
|---|
| 348 | (
|
|---|
| 349 | select coalesce(sum(ocp2.quantity_order),0) as maxkol from project.part p
|
|---|
| 350 | left join project.part_is_from_category pifc on pifc.id_part = p.id_part
|
|---|
| 351 | left join project.category c on c.id_category = pifc.id_category
|
|---|
| 352 | left join project.order_contains_part ocp2 on ocp2.id_part = p.id_part
|
|---|
| 353 | group by c.id_category , p.id_part
|
|---|
| 354 | ) q1
|
|---|
| 355 | )
|
|---|
| 356 |
|
|---|
| 357 |
|
|---|
| 358 | --Извештај за сите делови, со нивни категории и производители, и количината која била нарачана од овие делови во секој од последните 3 месеци.
|
|---|
| 359 | 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
|
|---|
| 360 | from project.part as p
|
|---|
| 361 | join project.part_manufacturer as pm on p.id_part_manufacturer = pm.id_part_manufacturer
|
|---|
| 362 | join project.part_is_from_category as pifc on p.id_part = pifc.id_part
|
|---|
| 363 | join project.category as cat on pifc.id_category = cat.id_category
|
|---|
| 364 | left join project.order_contains_part as ocp on p.id_part = ocp.id_part
|
|---|
| 365 | left join project.order_table as o on ocp.id_order = o.id_order
|
|---|
| 366 | left join (
|
|---|
| 367 | select p.id_part, p.part_name, cat.category_name, pm.pm_name,
|
|---|
| 368 | count(o.id_order) as narachani_vo_ovoj_mesec
|
|---|
| 369 | from project.part as p
|
|---|
| 370 | join project.part_manufacturer as pm on p.id_part_manufacturer = pm.id_part_manufacturer
|
|---|
| 371 | join project.part_is_from_category as pifc on p.id_part = pifc.id_part
|
|---|
| 372 | join project.category as cat on pifc.id_category = cat.id_category
|
|---|
| 373 | left join project.order_contains_part as ocp on p.id_part = ocp.id_part
|
|---|
| 374 | left join project.order_table as o on ocp.id_order = o.id_order
|
|---|
| 375 | and
|
|---|
| 376 | extract(month from o.order_date) = extract(month from now()) and extract(year from o.order_date) = extract(year from now())
|
|---|
| 377 | group by p.id_part, p.part_name, cat.category_name, pm.pm_name
|
|---|
| 378 | ) as f on p.id_part = f.id_part and pm.pm_name = f.pm_name
|
|---|
| 379 | left join (
|
|---|
| 380 | select p.id_part, p.part_name, cat.category_name, pm.pm_name,
|
|---|
| 381 | count(o.id_order) as narachani_vo_prethoden_mesec
|
|---|
| 382 | from project.part as p
|
|---|
| 383 | join project.part_manufacturer as pm on p.id_part_manufacturer = pm.id_part_manufacturer
|
|---|
| 384 | join project.part_is_from_category as pifc on p.id_part = pifc.id_part
|
|---|
| 385 | join project.category as cat on pifc.id_category = cat.id_category
|
|---|
| 386 | left join project.order_contains_part as ocp on p.id_part = ocp.id_part
|
|---|
| 387 | left join project.order_table as o on ocp.id_order = o.id_order
|
|---|
| 388 | and
|
|---|
| 389 | 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')
|
|---|
| 390 | group by p.id_part, p.part_name, cat.category_name, pm.pm_name
|
|---|
| 391 | ) as s on p.id_part = s.id_part and pm.pm_name = s.pm_name
|
|---|
| 392 | left join (
|
|---|
| 393 | select p.id_part, p.part_name, cat.category_name, pm.pm_name,
|
|---|
| 394 | count(o.id_order) as narachani_vo_predprethoden_mesec
|
|---|
| 395 | from project.part as p
|
|---|
| 396 | join project.part_manufacturer as pm on p.id_part_manufacturer = pm.id_part_manufacturer
|
|---|
| 397 | join project.part_is_from_category as pifc on p.id_part = pifc.id_part
|
|---|
| 398 | join project.category as cat on pifc.id_category = cat.id_category
|
|---|
| 399 | left join project.order_contains_part as ocp on p.id_part = ocp.id_part
|
|---|
| 400 | left join project.order_table as o on ocp.id_order = o.id_order
|
|---|
| 401 | and
|
|---|
| 402 | 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')
|
|---|
| 403 | group by p.id_part, p.part_name, cat.category_name, pm.pm_name
|
|---|
| 404 | ) as t on p.id_part = t.id_part and pm.pm_name = t.pm_name
|
|---|
| 405 | 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
|
|---|
| 406 |
|
|---|
| 407 |
|
|---|
