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 |
|
---|