Context Navigation

Back to RelationalDesign

RelationalDesign: polnenje.sql

File polnenje.sql, 19.7 KB (added by 185022, 4 weeks ago)

Line
1	set search_path = "IND0_185022";
2
3	delete
4	from article_unit
5	where true;
6	delete
7	from orders
8	where true;
9	delete
10	from delivery
11	where true;
12	delete
13	from pro_forma
14	where true;
15	delete
16	from price
17	where true;
18	delete
19	from article
20	where true;
21	delete
22	from manufacturer
23	where true;
24	delete
25	from driver
26	where true;
27	delete
28	from vehicle
29	where true;
30	delete
31	from manager
32	where true;
33	delete
34	from warehouse
35	where true;
36	delete
37	from customer
38	where true;
39	delete
40	from users
41	where true;
42	delete
43	from pro_forma_status
44	where true;
45	delete
46	from delivery_status
47	where true;
48	delete
49	from order_status
50	where true;
51	delete
52	from city
53	where true;
54	delete
55	from category
56	where true;
57
58	insert into city(city_name)
59	values ('с. Богородица'),
60	('с. Стојаково'),
61	('с. Ѓавото'),
62	('с. Николиќ'),
63	('с. Моин'),
64	('с. Негорци'),
65	('с. Мрзенци'),
66	('Скопје'),
67	('Тетово'),
68	('Битола'),
69	('Куманово'),
70	('Прилеп'),
71	('Охрид'),
72	('Велес'),
73	('Штип'),
74	('Гостивар'),
75	('Кочани'),
76	('Драчево'),
77	('Струга'),
78	('Дебар'),
79	('Струмица'),
80	('Виница'),
81	('Пробиштип'),
82	('Арачиново'),
83	('Кичево'),
84	('Кавадарци'),
85	('Гевгелија'),
86	('Врапчиште'),
87	('Радовиш'),
88	('Берово'),
89	('Крушево'),
90	('Свети Николе'),
91	('Демир Капија'),
92	('Кучевиште'),
93	('Делчево'),
94	('Богданци'),
95	('Рашче'),
96	('Неготино'),
97	('Вевчани'),
98	('Дебреште'),
99	('Лабуништа'),
100	('Градско'),
101	('Валандово'),
102	('Крива Паланка'),
103	('Кратово'),
104	('Кривогаштани'),
105	('Зрновци'),
106	('Пехчево'),
107	('Пласница'),
108	('Могила'),
109	('Теарце'),
110	('Новаци'),
111	('Росоман'),
112	('Ново Село'),
113	('Босилово'),
114	('Конче'),
115	('Ростуша'),
116	('Јегуновце'),
117	('Ранковце'),
118	('Сопиште'),
119	('Облешево'),
120	('Карбинци'),
121	('Василево'),
122	('Петровец'),
123	('Македонски Брод'),
124	('Старо Нагоричане'),
125	('Демир Хисар'),
126	('Лозово'),
127	('Центар Жупа'),
128	('Брвеница'),
129	('Белчишта'),
130	('Долнени'),
131	('Чучер-Сандево'),
132	('Стар Дојран'),
133	('Студеничани'),
134	('Боговиње'),
135	('Илинден'),
136	('Липково'),
137	('Македонска Каменица'),
138	('Зелениково'),
139	('Желино'),
140	('Ресен');
141
142	insert into category(ctg_name)
143	values ('Газирани Пијалоци'),
144	('Природни сокови'),
145	('Вода и минерална вода'),
146	('Кафе и чај'),
147	('Зачини'),
148	('Сувомеснати производи'),
149	('Житарици и житарки'),
150	('Млечни производи'),
151	('Масла за готвење');
152
153	insert into order_status(ord_status_name, ord_status_desc)
154	values ('Created', 'The order has been created.'),
155	('Pending', 'The order has been placed but is waiting for confirmation or processing.'),
156	('Confirmed', 'The order has been confirmed and is being prepared for shipment.'),
157	('Processing', 'The order is being processed by the warehouse or supplier.'),
158	('Shipped', 'The order has been dispatched and is on its way to the delivery address.'),
159	('Out for Delivery', 'The order is with the delivery personnel and will be delivered soon.'),
160	('Delivered', 'The order has been successfully delivered to the customer.'),
161	('Cancelled', 'The order has been cancelled by the customer or the seller.'),
162	('Refunded', 'The customer has been refunded for the order.'),
163	('On Hold', 'The order is temporarily on hold due to pending information or issues.'),
164	('Failed', 'The order failed to process due to a payment or system error.'),
165	('Returned', 'The order has been returned by the customer.');
166
167	insert into delivery_status(del_status_name, del_status_desc)
168	values ('Pending Pickup', 'The package is ready but has not yet been picked up by the courier.'),
169	('In Transit', 'The package is on its way to the delivery destination.'),
170	('Out for Delivery', 'The package is with the delivery driver and will be delivered soon.'),
171	('Delivered', 'The package has been successfully delivered to the recipient.'),
172	('Delivery Attempted', 'The delivery was attempted but unsuccessful (e.g., recipient not available).'),
173	('Returned to Sender', 'The package could not be delivered and has been returned to the sender.');
174
175	insert into pro_forma_status(pf_status_name, pf_status_desc)
176	values ('Draft', 'The pro forma invoice is in draft form and has not been finalized.'),
177	('Pending Approval', 'The pro forma invoice is awaiting approval from a manager or supervisor.'),
178	('Approved', 'The pro forma invoice has been reviewed and approved for use.'),
179	('Sent to Customer', 'The pro forma invoice has been sent to the customer for review or acceptance.'),
180	('Customer Accepted', 'The customer has reviewed and accepted the pro forma invoice.'),
181	('Customer Rejected', 'The customer has rejected the pro forma invoice.'),
182	('Pending Payment', 'The pro forma invoice is waiting for the customer’s payment.'),
183	('Payment Received', 'Payment for the pro forma invoice has been received.'),
184	('Amended', 'The pro forma invoice has been updated or modified.'),
185	('Cancelled', 'The pro forma invoice has been cancelled and is no longer valid.'),
186	('Expired', 'The pro forma invoice is no longer valid due to expiration of terms.'),
187	('Finalized', 'The pro forma invoice has been finalized and converted into a regular invoice.');
188
189	insert into users(user_name, user_surname, user_pass, user_salt, user_email, user_mobile, user_email_conf, city_id)
190	values ('Борис', 'Џотов', '$2b$12$6KlGHHlwTiZPT5ddyCe1j.x816akgep3Ow7.leXEf5gj6gsHoLhvS',
191	'JDJiJDEyJDZLbEdISGx3VGlaUFQ1ZGR5Q2Uxai4=', 'borisdzotov@gmail.com', '+38977812363', false, 27),
192	('Илија', 'Џотов', '$2b$12$tD4yXvYWKUFdtVRw7zu54u2se.BD8EUqSH7Qvw9Brq9jUAFQCdfDi',
193	'JDJiJDEyJHRENHlYdllXS1VGZHRWUnc3enU1NHU=', 'ilijadzotov@gmail.com', '+38977812364', false, 27),
194	('Владимир', 'Вељаноски', '$2b$12$Gy8SrA3YGNj6C.dcRn/LN.UMABTc4EZbM8ua13ki2iPN3umWM3B2G',
195	'JDJiJDEyJEd5OFNyQTNZR05qNkMuZGNSbi9MTi4=', 'vladimir.veljanoski@gmail.com', '+38978738456', false, 13),
196	('Михаил', 'Џотов', '$2b$12$3ZE1tX6Kngqy8F3JtexzS.8Z7FZs0Rg4kcxEMjID3yioYqTAu7yGa',
197	'JDJiJDEyJDNaRTF0WDZLbmdxeThGM0p0ZXh6Uy4=', 'mihaildz@gmail.com', '+38970320060', false, 27),
198	('Иван', 'Петров', '$2b$12$kFZ/VrHGwrXfQpX8wOdNauQKoLfC9oFq0odPhnIpz7hId.ZvZTnR2', 'c2FsdF9zdHJpbmdfMQ==',
199	'ivan.petrov@example.com', '+38975123456', false, 15),
200	('Елена', 'Трајкова', '$2b$12$eYr8DXiQDQaE8Nm5UpmP0uQ0VtoCkbbV4z9PvHOn6rjbFIp9V0Qji', 'c2FsdF9zdHJpbmdfMg==',
201	'elena.trajkova@example.com', '+38976234567', false, 42),
202	('Ана', 'Јовановска', '$2b$12$USK/NtZkEC6ySiqkjBd8kuMmB7NRfB1R5emTcyMHoXzEhb6Zh2Zmi', 'c2FsdF9zdHJpbmdfMw==',
203	'ana.jovanovska@example.com', '+38977345678', false, 7),
204	('Бојан', 'Стојанов', '$2b$12$Wxx9H9qrTCsT1tngYMe5huQuLFoXmP3YfzOR2eTb3hp9rOnMoAvKa', 'c2FsdF9zdHJpbmdfNA==',
205	'bojan.stojanov@example.com', '+38978456789', false, 28),
206	('Марко', 'Димитров', '$2b$12$5mC1sEyKZ/jqkY4iCDAGheXE49FcDo9J5Ztdu2iwTUXmOIkAmV35C', 'c2FsdF9zdHJpbmdfNQ==',
207	'marko.dimitrov@example.com', '+38978567890', false, 81),
208	('Марија', 'Костова', '$2b$12$L4orKaPxdJ9ck59uIbMkwOtJ25TPnBNvYWfY3XmDeRHvnQ7NZ2UqS', 'c2FsdF9zdHJpbmdfNg==',
209	'marija.kostova@example.com', '+38979678901', false, 3),
210	('Горан', 'Илиев', '$2b$12$uRlgqREzM9ZgoDvcQgpkuucGNtxu5rh.gi7X6oTSuqDF2Ee7oa9uG', 'c2FsdF9zdHJpbmdfNw==',
211	'goran.iliev@example.com', '+38979789012', false, 50),
212	('Софија', 'Панова', '$2b$12$oaAE0IqIgTxHIHy8eNkOWuaBaEcgTzStt1SK9TyTpKMJotO8Ns3g2', 'c2FsdF9zdHJpbmdfOA==',
213	'sofija.panova@example.com', '+38979890123', false, 23),
214	('Димитар', 'Ангелов', '$2b$12$NpS/XHqcAm6KHCDgTxlzeOt8AnStcf9f3dEY8OepQXoMSHL3HbHRq', 'c2FsdF9zdHJpbmdfOQ==',
215	'dimitar.angelov@example.com', '+38975901234', false, 69),
216	('Кристина', 'Николовска', '$2b$12$Z8DlaSyrDxuWTyKqbwQpeO1hLQcruW4w4WmcqukXm8SCjB/Mm.ePi',
217	'c2FsdF9zdHJpbmdfMTA=', 'kristina.nikolovska@example.com', '+38975843210', false, 12);
218
219	insert into customer
220	values (3, '1234752312423', 'Ибо-Трејд', 'ул. Титоградска 3', '08:00:00', '20:00:00', 'blablabla.jpg'),
221	(10, '1234567890123', 'ТехноМакс', 'ул. Брегалница 45', '08:00:00', '17:00:00', 'techmax_representative1.jpg'),
222	(2, '9876543210987', 'ГолдТек', 'ул. Мисирков 12', '09:00:00', '18:00:00', 'goldtek_representative2.jpg'),
223	(6, '1928374650123', 'ЕкоСервис', 'ул. Ново Село 8', '07:30:00', '16:30:00', 'ecoservice_representative3.jpg'),
224	(7, '1231231231231', 'МебелСвет', 'ул. Карпош 10', '10:00:00', '19:00:00', 'mebel_svet_representative4.jpg'),
225	(5, '1112223334444', 'АгроТек', 'ул. Пелагонија 25', '08:30:00', '17:30:00', 'agrotek_representative5.jpg');
226
227	insert into warehouse(wh_address, city_id)
228	values ('ул. Титоградска 3', 27),
229	('ул. Индустриска зона 12', 15),
230	('ул. Транспорт 5', 42);
231
232	insert into manager
233	values (4, 1),
234	(8, 2),
235	(9, 3);
236
237	insert into vehicle(veh_carry_weight, veh_service_interval, veh_kilometers, veh_last_service, veh_last_service_km,
238	veh_plate, veh_vin, veh_reg, wh_id)
239	values (1800, 12000, 200000, '15-11-2024', 199000, 'VE905UZ', 'KNAMB8159NN123456', '2024-12-05', 1),
240	(1500, 15000, 42500, '2024-06-15', 30000, 'SK1234AB', 'KNAMB8159NN123001', '2024-06-13', 2),
241	(2000, 20000, 60500, '2023-12-01', 40000, 'BT5678CD', 'KNAMB8159NN123002', '2024-01-25', 3);
242
243	insert into driver
244	values (1, 1),
245	(11, 2),
246	(12, 3);
247
248	insert into manufacturer(man_name, man_address, man_mobile, man_email)
249	values
250	-- Масло за јадење
251	('Ал-Макс', 'Булевар 12, Струмица', '+38977231983', 'kontakt@almaks.com'),
252	-- Зачини
253	('Витаминка', 'ул. Витаминоза 1, Скопје', '+3897236498', 'kontakt@vitaminka.com'),
254	('Подравка', 'ул. Загребачка 12, Скопје', '+38977888999', 'kontakt@podravka.com'),
255	-- Млечни производи
256	('Бимилк', '123 Млечна улица, Скопје', '+38970123456', 'kontakt@bimilk.com'),
257	('Вардарец', '456 Пат за сирење, Битола', '+38970234567', 'info@vardarec.com'),
258
259	-- Сувомеснати производи
260	('Меин', '789 Пат за месо, Велес', '+38970345678', 'prodaja@mein.com'),
261	('Пекабеско', '321 Улица за кобасици, Охрид', '+38970456789', 'podrska@pekabesko.com'),
262
263	-- Житарици и житарки
264	('Витанова', '101 Улица за житарки, Куманово', '+38970567890', 'kontakt@vitanova.com'),
265	('Жито', '202 Улица за овес, Прилеп', '+38970678901', 'info@zito.com'),
266
267	-- Газирани пијалоци
268	('Прилепска Пивара', '303 Плоштад за сокови, Прилеп', '+38970789012', 'kontakt@kokakola.com'),
269	('Скопска Пивара', '404 Пат за меурчиња, Скопје', '+38970890123', 'podrska@pepsi.com'),
270
271	-- Природни сокови
272	('Нектар', '505 Улица за природни сокови, Крива Паланка', '+38970901234', 'prodaja@nektar.com'),
273	('Вива', '606 Пат за нектар, Тетово', '+38970112345', 'info@viva.com'),
274
275	-- Кафе и чај
276	('Гранд Кафе', '707 Пат за кафе, Скопје', '+38970223456', 'info@grandkafe.com'),
277	('Лојд', '808 Улица за чај, Битола', '+38970334567', 'podrska@loyd.com'),
278
279	-- Вода и минерална вода
280	('Изворска Вода', '909 Пат за вода, Охрид', '+38970445678', 'prodaja@izvorska.com'),
281	('Горска Вода', '1010 Плоштад за чиста вода, Струмица', '+38970556789', 'kontakt@gorskavoda.com');
282
283	insert into article(art_name, art_image, art_weight, ctg_id, man_id)
284	values ('Бисер - 1л', 'biser1l.jpg', 1000, 9, 1),
285	('Бисер - 4.75л', 'biser475l.jpg', 4750, 9, 1),
286	('Бисер - 5л', 'biser1l.jpg', 5000, 9, 1),
287	('Бисер - 10л', 'biser5l.jpg', 10000, 9, 1),
288	-- Млечни производи
289	('Сирење', 'sirene.jpg', 500, 8, 5),
290	('Млеко', 'mleko.jpg', 1000, 8, 4),
291	-- Сувомеснати производи
292	('Кулен', 'kulen.jpg', 300, 6, 6),
293	('Пастрма', 'pastrma.jpg', 400, 6, 7),
294	-- Житарици и житарки
295	('Ориз', 'oriz.jpg', 1000, 7, 8),
296	('Јачмен', 'jacmen.jpg', 500, 7, 9),
297	-- Газирани пијалоци
298	('Кока-Кола', 'coca-cola.jpg', 1500, 1, 10),
299	('Спрајт', 'sprite.jpg', 1500, 1, 11),
300	-- Природни сокови
301	('Јаболков сок', 'jabokov-sok.jpg', 1000, 2, 12),
302	('Портокалов сок', 'portokalov-sok.jpg', 1000, 2, 13),
303	-- Кафе и чај
304	('Кафе', 'kafe.jpg', 200, 4, 14),
305	('Зелен чај', 'zelen-chaj.jpg', 100, 4, 15),
306	-- Вода и минерална вода
307	('Обична вода', 'obicna-voda.jpg', 1500, 3, 16),
308	('Минерална вода', 'mineralna-voda.jpg', 1500, 3, 17),
309	-- Зачини
310	('Лук во прав', 'garlic_powder.jpg', 100, 5, 2),
311	('Босилек', 'basil.jpg', 50, 5, 3);
312
313	insert into price(price, art_id)
314	values (100, 1),
315	(150, 2),
316	(200, 3),
317	(250, 4),
318	(300, 5),
319	(120, 6),
320	(180, 7),
321	(220, 8),
322	(270, 9),
323	(320, 10),
324	(130, 11),
325	(170, 12),
326	(210, 13),
327	(260, 14),
328	(310, 15),
329	(140, 16),
330	(190, 17),
331	(230, 18),
332	(280, 19),
333	(330, 20);
334
335	insert into article_unit(unit_expiration_date, unit_serial_number, unit_batch_number, unit_manufacture_date,
336	unit_cost_price, art_id, wh_id, ord_id)
337	values ('1-05-2025', '1234284372', '2142153', '10-10-2024', 72.0, 1, 1, null),
338	-- Млечни произвои (article_id 1, 2)
339	('2025-12-31', 'SN12345A', 'BN001', '2023-11-01', 150.00, 5, 1, null),
340	('2025-12-31', 'SN12345B', 'BN002', '2023-11-01', 155.00, 5, 1, null),
341	('2024-06-30', 'SN67890A', 'BN003', '2023-05-01', 55.00, 6, 1, null),
342	('2024-06-30', 'SN67890B', 'BN004', '2023-05-01', 58.00, 6, 1, null),
343	-- Сувомеснати производи (article_id 5, 6)
344	('2025-03-10', 'SN34567A', 'BN009', '2023-12-01', 300.00, 7, 2, null),
345	('2025-03-10', 'SN34567B', 'BN010', '2023-12-01', 310.00, 7, 2, null),
346	('2024-12-31', 'SN12378A', 'BN011', '2023-08-01', 400.00, 8, 3, null),
347	('2024-12-31', 'SN12378B', 'BN012', '2023-08-01', 420.00, 8, 3, null),
348	-- Житарици и житарки (article_id 7, 8)
349	('2025-07-01', 'SN11223A', 'BN013', '2023-03-10', 70.00, 9, 1, null),
350	('2025-07-01', 'SN11223B', 'BN014', '2023-03-10', 72.00, 9, 2, null),
351	('2024-05-15', 'SN33445A', 'BN015', '2024-02-01', 35.00, 10, 3, null),
352	('2024-05-15', 'SN33445B', 'BN016', '2024-02-01', 38.00, 10, 2, null),
353	-- Газирани пијалоци (article_id 11, 12)
354	('2025-02-14', 'SN98712A', 'BN021', '2023-12-10', 85.00, 11, 1, null),
355	('2025-02-14', 'SN98712B', 'BN022', '2023-12-10', 90.00, 11, 1, null),
356	('2024-12-30', 'SN65432A', 'BN023', '2023-09-15', 80.00, 12, 2, null),
357	('2024-12-30', 'SN65432B', 'BN024', '2023-09-15', 82.00, 12, 2, null),
358	-- Природни сокови (article_id 13, 14)
359	('2025-05-10', 'SN45678A', 'BN025', '2023-10-10', 110.00, 13, 3, null),
360	('2025-05-10', 'SN45678B', 'BN026', '2023-10-10', 115.00, 13, 3, null),
361	('2024-03-25', 'SN34567A', 'BN027', '2023-01-05', 120.00, 14, 1, null),
362	('2024-03-25', 'SN34567B', 'BN028', '2023-01-05', 125.00, 14, 1, null),
363	-- Кафе и чај (article_id 17, 18)
364	('2025-04-21', 'SN12345C', 'BN033', '2023-03-01', 140.00, 15, 1, null),
365	('2025-04-21', 'SN12345D', 'BN034', '2023-03-01', 145.00, 15, 2, null),
366	('2024-07-10', 'SN54321C', 'BN035', '2024-05-15', 115.00, 16, 2, null),
367	('2024-07-10', 'SN54321D', 'BN036', '2024-05-15', 118.00, 16, 2, null),
368	-- Вода и минерална вода (article_id 19, 20)
369	('2025-09-15', 'SN67890A', 'BN037', '2023-08-10', 18.00, 17, 3, null),
370	('2025-09-15', 'SN67890B', 'BN038', '2023-08-10', 20.00, 17, 3, null),
371	('2024-02-05', 'SN12345E', 'BN039', '2023-01-01', 30.00, 18, 3, null),
372	('2024-02-05', 'SN12345F', 'BN040', '2023-01-01', 32.00, 18, 3, null),
373	-- Зачини
374	('2025-05-01', 'SN12345A', 'BN1001', '2024-01-10', 80.00, 19, 1, null),
375	('2025-05-01', 'SN12345B', 'BN1002', '2024-01-10', 82.00, 19, 2, null),
376	('2025-06-10', 'SN54321A', 'BN2001', '2023-12-15', 50.00, 20, 1, null),
377	('2025-06-10', 'SN54321B', 'BN2002', '2023-12-15', 52.00, 20, 3, null);
378
379	insert into delivery(del_date_created, del_date, del_start_km, del_end_km, del_start_time, del_end_time, del_status_id,
380	veh_id)
381	values ('2024-12-01', '2024-12-05', 100, 200, '08:00:00', '12:00:00', 1, 1),
382	('2024-12-02', '2024-12-06', 150, 250, '09:30:00', '14:30:00', 2, 2),
383	('2024-12-03', '2024-12-07', 120, 180, '07:45:00', '11:45:00', 3, 3),
384	('2024-12-04', '2024-12-08', 90, 170, '10:00:00', '15:00:00', 1, 2),
385	('2024-12-05', '2024-12-09', 80, 160, '11:30:00', '16:30:00', 2, 3),
386	('2024-12-06', '2024-12-10', 130, 210, '06:00:00', '11:00:00', 3, 1);
387
388	insert into orders(ord_date, ord_sum, ord_fulfillment_date, ord_comment, ord_status_id, cust_id, del_id, pf_id)
389	values ('2024-12-05', 0, '2024-12-10 15:30:00', null, 3, 3, null, null),
390	('2024-12-10', 0, '2024-12-15 15:30:00', null, 1, 10, null, null),
391	('2024-12-09', 0, '2024-12-14 15:30:00', null, 2, 2, null, null),
392	('2024-12-08', 0, '2024-12-13 15:30:00', null, 2, 3, null, null),
393	('2024-12-07', 0, '2024-12-12 15:30:00', null, 3, 7, null, null),
394	('2024-12-06', 0, '2024-12-11 15:30:00', null, 4, 5, null, null),
395	('2024-12-04', 0, '2024-12-10 15:30:00', null, 2, 6, null, null);
396
397	insert into pro_forma(pf_deadline, pf_date_created, pf_status_id)
398	values ('01-01-2025', '06-12-2024', 1),
399	('2024-12-31', '2024-12-01', 1),
400	('2025-01-15', '2024-12-05', 2),
401	('2025-02-10', '2024-12-10', 1),
402	('2024-12-25', '2024-12-12', 3),
403	('2025-01-05', '2024-12-15', 2),
404	('2025-03-01', '2024-12-20', 1);

Download in other formats:

Original Format