source: view-database.js@ 79fff4f

finki-main main
Last change on this file since 79fff4f was 69f2a41, checked in by Klimentina Efremova <klimentina08642@ā€¦>, 7 months ago

Initial commit
  • Property mode set to 100644
File size: 19.3 KB
RevLineĀ 
[69f2a41]1const sqlite3 = require('sqlite3').verbose();
2const path = require('path');
3
4const dbPath = path.join(__dirname, 'database', 'handcraft.db');
5const db = new sqlite3.Database(dbPath);
6
7console.log('\nšŸŽØ HANDCRAFT MARKETPLACE - DATABASE CONTENTS\n');
8
9// Helper function to format date
10function formatDate(dateString) {
11 if (!dateString) return 'N/A';
12 const date = new Date(dateString);
13 return date instanceof Date && !isNaN(date) ? date.toString() : dateString;
14}
15
16// Helper function to check if table exists
17function tableExists(tableName, callback) {
18 db.get(
19 "SELECT name FROM sqlite_master WHERE type='table' AND name = ?",
20 [tableName],
21 (err, row) => {
22 callback(!err && row);
23 }
24 );
25}
26
27// Display CLIENTS table
28console.log('\nšŸ‘¤ CLIENTS TABLE:');
29console.log('================================================================================');
30tableExists('client', (exists) => {
31 if (!exists) {
32 console.log('Table does not exist');
33 checkNext();
34 return;
35 }
36
37 db.all('SELECT * FROM client', [], (err, rows) => {
38 if (err) {
39 console.log(`Error: ${err.message}`);
40 checkNext();
41 return;
42 }
43
44 if (!rows || rows.length === 0) {
45 console.log('No clients found');
46 } else {
47 console.log(`Total: ${rows.length} clients\n`);
48 rows.forEach((client, index) => {
49 console.log(`ID: ${client.client_id} | Name: ${client.first_name || ''} ${client.last_name || ''}`);
50 console.log(`Email: ${client.email || 'N/A'}`);
51 if (index < rows.length - 1) console.log('-'.repeat(80));
52 });
53 }
54 checkNext();
55 });
56});
57
58// Display PERSONAL table
59function checkPersonal() {
60 console.log('\nšŸ‘” PERSONAL TABLE (STORE OWNERS/EMPLOYEES):');
61 console.log('================================================================================');
62 tableExists('personal', (exists) => {
63 if (!exists) {
64 console.log('Table does not exist');
65 checkStore();
66 return;
67 }
68
69 db.all(`
70 SELECT p.*,
71 CASE WHEN b.boss_id IS NOT NULL THEN 'BOSS'
72 WHEN e.employee_id IS NOT NULL THEN 'EMPLOYEE'
73 ELSE 'PERSONAL' END as role
74 FROM personal p
75 LEFT JOIN boss b ON p.id = b.boss_id
76 LEFT JOIN employees e ON p.id = e.employee_id
77 `, [], (err, rows) => {
78 if (err) {
79 console.log(`Error: ${err.message}`);
80 checkStore();
81 return;
82 }
83
84 if (!rows || rows.length === 0) {
85 console.log('No personal records found');
86 } else {
87 console.log(`Total: ${rows.length} personal records\n`);
88 rows.forEach((person, index) => {
89 console.log(`ID: ${person.id || 'N/A'} | Name: ${person.first_name || ''} ${person.last_name || ''} | Role: ${person.role || 'N/A'}`);
90 console.log(`Email: ${person.email || 'N/A'} | SSN: ${person.ssn || 'N/A'}`);
91 if (index < rows.length - 1) console.log('-'.repeat(80));
92 });
93 }
94 checkStore();
95 });
96 });
97}
98
99// Display STORE table
100function checkStore() {
101 console.log('\nšŸŖ STORE TABLE:');
102 console.log('================================================================================');
103 tableExists('store', (exists) => {
104 if (!exists) {
105 console.log('Table does not exist');
106 checkProduct();
107 return;
108 }
109
110 db.all('SELECT * FROM store', [], (err, rows) => {
111 if (err) {
112 console.log(`Error: ${err.message}`);
113 checkProduct();
114 return;
115 }
116
117 if (!rows || rows.length === 0) {
118 console.log('No stores found');
119 } else {
120 console.log(`Total: ${rows.length} stores\n`);
121 rows.forEach((store, index) => {
122 console.log(`ID: ${store.store_id || 'N/A'} | Name: ${store.name || 'N/A'}`);
123 console.log(`Email: ${store.store_email || 'N/A'} | Rating: ${store.rating || '0.0'}`);
124 console.log(`Address: ${store.physical_address || 'N/A'}`);
125 console.log(`Founded: ${formatDate(store.date_of_founding)}`);
126 if (index < rows.length - 1) console.log('-'.repeat(80));
127 });
128 }
129 checkProduct();
130 });
131 });
132}
133
134// Display PRODUCT table
135function checkProduct() {
136 console.log('\nšŸ›ļø PRODUCT TABLE:');
137 console.log('================================================================================');
138 tableExists('product', (exists) => {
139 if (!exists) {
140 console.log('Table does not exist');
141 checkCategory();
142 return;
143 }
144
145 db.all(`
146 SELECT p.*, c.name as category_name
147 FROM product p
148 LEFT JOIN category c ON p.category_id = c.category_id
149 `, [], (err, rows) => {
150 if (err) {
151 console.log(`Error: ${err.message}`);
152 checkCategory();
153 return;
154 }
155
156 if (!rows || rows.length === 0) {
157 console.log('No products found');
158 } else {
159 console.log(`Total: ${rows.length} products\n`);
160 rows.forEach((product, index) => {
161 console.log(`Code: ${product.code || 'N/A'} | Price: $${product.price || '0.00'}`);
162 console.log(`Description: ${product.description ? product.description.substring(0, 50) + (product.description.length > 50 ? '...' : '') : 'N/A'}`);
163 console.log(`Store ID: ${product.store_id || 'N/A'} | Category: ${product.category_name || 'N/A'}`);
164 console.log(`Available: ${product.availability || '0'} | Weight: ${product.weight || '0'}kg`);
165 if (index < rows.length - 1) console.log('-'.repeat(80));
166 });
167 }
168 checkCategory();
169 });
170 });
171}
172
173// Display CATEGORY table
174function checkCategory() {
175 console.log('\nšŸ“ CATEGORY TABLE:');
176 console.log('================================================================================');
177 tableExists('category', (exists) => {
178 if (!exists) {
179 console.log('Table does not exist');
180 checkWorksInStore();
181 return;
182 }
183
184 db.all(`
185 SELECT c1.*, c2.name as parent_name
186 FROM category c1
187 LEFT JOIN category c2 ON c1.parent_category_id = c2.category_id
188 `, [], (err, rows) => {
189 if (err) {
190 console.log(`Error: ${err.message}`);
191 checkWorksInStore();
192 return;
193 }
194
195 if (!rows || rows.length === 0) {
196 console.log('No categories found');
197 } else {
198 console.log(`Total: ${rows.length} categories\n`);
199 rows.forEach((cat, index) => {
200 console.log(`ID: ${cat.category_id || 'N/A'} | Name: ${cat.name || 'N/A'}`);
201 console.log(`Parent ID: ${cat.parent_category_id || 'None'} | Parent Name: ${cat.parent_name || 'None'}`);
202 console.log(`Description: ${cat.description || 'No description'}`);
203 if (index < rows.length - 1) console.log('-'.repeat(80));
204 });
205 }
206 checkWorksInStore();
207 });
208 });
209}
210
211// Display WORKS_IN_STORE table
212function checkWorksInStore() {
213 console.log('\nšŸ”— WORKS_IN_STORE TABLE:');
214 console.log('================================================================================');
215 tableExists('works_in_store', (exists) => {
216 if (!exists) {
217 console.log('Table does not exist');
218 checkPermissions();
219 return;
220 }
221
222 db.all(`
223 SELECT w.*, p.first_name, p.last_name, s.name as store_name
224 FROM works_in_store w
225 LEFT JOIN personal p ON w.personal_id = p.id
226 LEFT JOIN store s ON w.store_id = s.store_id
227 `, [], (err, rows) => {
228 if (err) {
229 console.log(`Error: ${err.message}`);
230 checkPermissions();
231 return;
232 }
233
234 if (!rows || rows.length === 0) {
235 console.log('No assignments found');
236 } else {
237 console.log(`Total: ${rows.length} assignments\n`);
238 rows.forEach((assign, index) => {
239 console.log(`Personal ID: ${assign.personal_id || 'N/A'} | Store ID: ${assign.store_id || 'N/A'}`);
240 console.log(`Name: ${assign.first_name || ''} ${assign.last_name || ''} | Store: ${assign.store_name || 'N/A'}`);
241 if (index < rows.length - 1) console.log('-'.repeat(80));
242 });
243 }
244 checkPermissions();
245 });
246 });
247}
248
249// Display PERMISSIONS table
250function checkPermissions() {
251 console.log('\nšŸ” PERMISSIONS TABLE:');
252 console.log('================================================================================');
253 tableExists('permissions', (exists) => {
254 if (!exists) {
255 console.log('Table does not exist');
256 checkEmployees();
257 return;
258 }
259
260 db.all(`
261 SELECT perm.*, p.first_name, p.last_name
262 FROM permissions perm
263 LEFT JOIN personal p ON perm.personal_id = p.id
264 `, [], (err, rows) => {
265 if (err) {
266 console.log(`Error: ${err.message}`);
267 checkEmployees();
268 return;
269 }
270
271 if (!rows || rows.length === 0) {
272 console.log('No permissions found');
273 } else {
274 console.log(`Total: ${rows.length} permissions\n`);
275 rows.forEach((perm, index) => {
276 console.log(`Personal ID: ${perm.personal_id || 'N/A'} | Name: ${perm.first_name || ''} ${perm.last_name || ''}`);
277 console.log(`Type: ${perm.type || 'N/A'} | Authorization: ${perm.authorisation || 'N/A'}`);
278 if (index < rows.length - 1) console.log('-'.repeat(80));
279 });
280 }
281 checkEmployees();
282 });
283 });
284}
285
286// Display EMPLOYEES table
287function checkEmployees() {
288 console.log('\nšŸ‘· EMPLOYEES TABLE:');
289 console.log('================================================================================');
290 tableExists('employees', (exists) => {
291 if (!exists) {
292 console.log('Table does not exist');
293 checkBoss();
294 return;
295 }
296
297 db.all(`
298 SELECT e.*, p.first_name, p.last_name, p.email
299 FROM employees e
300 LEFT JOIN personal p ON e.employee_id = p.id
301 `, [], (err, rows) => {
302 if (err) {
303 console.log(`Error: ${err.message}`);
304 checkBoss();
305 return;
306 }
307
308 if (!rows || rows.length === 0) {
309 console.log('No employees found');
310 } else {
311 console.log(`Total: ${rows.length} employees\n`);
312 rows.forEach((emp, index) => {
313 console.log(`Employee ID: ${emp.employee_id || 'N/A'} | Name: ${emp.first_name || ''} ${emp.last_name || ''}`);
314 console.log(`Email: ${emp.email || 'N/A'} | Date Hired: ${formatDate(emp.date_of_hire)}`);
315 if (index < rows.length - 1) console.log('-'.repeat(80));
316 });
317 }
318 checkBoss();
319 });
320 });
321}
322
323// Display BOSS table
324function checkBoss() {
325 console.log('\nšŸ‘‘ BOSS TABLE:');
326 console.log('================================================================================');
327 tableExists('boss', (exists) => {
328 if (!exists) {
329 console.log('Table does not exist');
330 checkOrder();
331 return;
332 }
333
334 db.all(`
335 SELECT b.*, p.first_name, p.last_name, p.email
336 FROM boss b
337 LEFT JOIN personal p ON b.boss_id = p.id
338 `, [], (err, rows) => {
339 if (err) {
340 console.log(`Error: ${err.message}`);
341 checkOrder();
342 return;
343 }
344
345 if (!rows || rows.length === 0) {
346 console.log('No bosses found');
347 } else {
348 console.log(`Total: ${rows.length} bosses\n`);
349 rows.forEach((boss, index) => {
350 console.log(`Boss ID: ${boss.boss_id || 'N/A'} | Name: ${boss.first_name || ''} ${boss.last_name || ''}`);
351 console.log(`Email: ${boss.email || 'N/A'} | Signature: ${boss.signature || 'N/A'}`);
352 if (index < rows.length - 1) console.log('-'.repeat(80));
353 });
354 }
355 checkOrder();
356 });
357 });
358}
359
360// Display ORDER table
361function checkOrder() {
362 console.log('\nšŸ“¦ ORDERS TABLE:');
363 console.log('================================================================================');
364 tableExists('order', (exists) => {
365 if (!exists) {
366 console.log('Table does not exist');
367 checkReport();
368 return;
369 }
370
371 db.all(`
372 SELECT o.*, c.first_name, c.last_name, s.name as store_name
373 FROM "order" o
374 LEFT JOIN client c ON o.client_id = c.client_id
375 LEFT JOIN store s ON o.store_id = s.store_id
376 `, [], (err, rows) => {
377 if (err) {
378 console.log(`Error: ${err.message}`);
379 checkReport();
380 return;
381 }
382
383 if (!rows || rows.length === 0) {
384 console.log('No orders found');
385 } else {
386 console.log(`Total: ${rows.length} orders\n`);
387 rows.forEach((order, index) => {
388 console.log(`Order #: ${order.order_num || 'N/A'} | Client: ${order.first_name || ''} ${order.last_name || ''}`);
389 console.log(`Store: ${order.store_name || 'N/A'} | Date: ${formatDate(order.order_date)}`);
390 console.log(`Status: ${order.status || 'N/A'} | Payment: ${order.payment_method || 'N/A'}`);
391 console.log(`Delivery: ${order.delivery_address ? order.delivery_address.substring(0, 30) + '...' : 'N/A'}`);
392 if (index < rows.length - 1) console.log('-'.repeat(80));
393 });
394 }
395 checkReport();
396 });
397 });
398}
399
400// Display REPORT table
401function checkReport() {
402 console.log('\nšŸ“Š REPORTS TABLE:');
403 console.log('================================================================================');
404 tableExists('report', (exists) => {
405 if (!exists) {
406 console.log('Table does not exist');
407 checkRefund();
408 return;
409 }
410
411 db.all('SELECT * FROM report', [], (err, rows) => {
412 if (err) {
413 console.log(`Error: ${err.message}`);
414 checkRefund();
415 return;
416 }
417
418 if (!rows || rows.length === 0) {
419 console.log('No reports found');
420 } else {
421 console.log(`Total: ${rows.length} reports\n`);
422 rows.forEach((report, index) => {
423 console.log(`Report Date: ${formatDate(report.date)} | Store ID: ${report.store_id || 'N/A'}`);
424 console.log(`Profit: $${report.overall_profit || '0.00'} | Signature: ${report.owner_signature || 'N/A'}`);
425 if (index < rows.length - 1) console.log('-'.repeat(80));
426 });
427 }
428 checkRefund();
429 });
430 });
431}
432
433// Display REFUND table
434function checkRefund() {
435 console.log('\nšŸ’° REFUND TABLE:');
436 console.log('================================================================================');
437 tableExists('refund', (exists) => {
438 if (!exists) {
439 console.log('Table does not exist');
440 checkImage();
441 return;
442 }
443
444 db.all('SELECT * FROM refund', [], (err, rows) => {
445 if (err) {
446 console.log(`Error: ${err.message}`);
447 checkImage();
448 return;
449 }
450
451 if (!rows || rows.length === 0) {
452 console.log('No refunds found');
453 } else {
454 console.log(`Total: ${rows.length} refunds\n`);
455 rows.forEach((refund, index) => {
456 console.log(`Refund ID: ${refund.refund_id || 'N/A'} | Order: ${refund.order_num || 'N/A'}`);
457 console.log(`Amount: $${refund.amount || '0.00'} | Status: ${refund.status || 'N/A'}`);
458 console.log(`Reason: ${refund.reason || 'N/A'}`);
459 if (index < rows.length - 1) console.log('-'.repeat(80));
460 });
461 }
462 checkImage();
463 });
464 });
465}
466
467// Display IMAGE table
468function checkImage() {
469 console.log('\nšŸ–¼ļø IMAGE TABLE:');
470 console.log('================================================================================');
471 tableExists('image', (exists) => {
472 if (!exists) {
473 console.log('Table does not exist');
474 checkColor();
475 return;
476 }
477
478 db.all('SELECT * FROM image', [], (err, rows) => {
479 if (err) {
480 console.log(`Error: ${err.message}`);
481 checkColor();
482 return;
483 }
484
485 if (!rows || rows.length === 0) {
486 console.log('No images found');
487 } else {
488 console.log(`Total: ${rows.length} images\n`);
489 rows.forEach((image, index) => {
490 console.log(`Product Code: ${image.product_code || 'N/A'} | Image: ${image.image || 'N/A'}`);
491 if (index < rows.length - 1) console.log('-'.repeat(80));
492 });
493 }
494 checkColor();
495 });
496 });
497}
498
499// Display COLOR table
500function checkColor() {
501 console.log('\nšŸŽØ COLOR TABLE:');
502 console.log('================================================================================');
503 tableExists('color', (exists) => {
504 if (!exists) {
505 console.log('Table does not exist');
506 finish();
507 return;
508 }
509
510 db.all('SELECT * FROM color', [], (err, rows) => {
511 if (err) {
512 console.log(`Error: ${err.message}`);
513 finish();
514 return;
515 }
516
517 if (!rows || rows.length === 0) {
518 console.log('No colors found');
519 } else {
520 console.log(`Total: ${rows.length} colors\n`);
521 rows.forEach((color, index) => {
522 console.log(`Product Code: ${color.product_code || 'N/A'} | Color: ${color.color || 'N/A'}`);
523 if (index < rows.length - 1) console.log('-'.repeat(80));
524 });
525 }
526 finish();
527 });
528 });
529}
530
531// Start the chain of checks
532function checkNext() {
533 checkPersonal();
534}
535
536function finish() {
537 console.log('\n' + '='.repeat(80));
538 console.log('āœ… Database inspection complete');
539 console.log('='.repeat(80) + '\n');
540
541 db.close();
542}
543
544// Start with CLIENTS
545checkNext();
Note: See TracBrowser for help on using the repository browser.