source: database.js@ 69f2a41

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

Initial commit
  • Property mode set to 100644
File size: 37.1 KB
Line 
1const sqlite3 = require('sqlite3').verbose();
2const path = require('path');
3const bcrypt = require('bcryptjs');
4const crypto = require('crypto');
5
6const dbPath = path.join(__dirname, 'database', 'handcraft.db');
7const dbDir = path.dirname(dbPath);
8
9// Create database directory if it doesn't exist
10const fs = require('fs');
11if (!fs.existsSync(dbDir)) {
12 fs.mkdirSync(dbDir, { recursive: true });
13}
14
15const database = new sqlite3.Database(dbPath, (err) => {
16 if (err) {
17 console.error('Error opening database:', err.message);
18 } else {
19 console.log('✅ Connected to SQLite database');
20 }
21});
22
23// Enable foreign keys
24database.run('PRAGMA foreign_keys = ON');
25
26// Helper function to ensure general category exists
27function ensureGeneralCategory(callback) {
28 database.get('SELECT category_id FROM category WHERE name = ?', ['General'], (err, row) => {
29 if (err) {
30 callback(err);
31 } else if (!row) {
32 database.run(
33 'INSERT INTO category (name, description) VALUES (?, ?)',
34 ['General', 'General products category'],
35 function(err) {
36 callback(err);
37 }
38 );
39 } else {
40 callback(null);
41 }
42 });
43}
44
45// Helper function to get the General category ID
46function getGeneralCategoryId(callback) {
47 database.get('SELECT category_id FROM category WHERE name = ?', ['General'], (err, row) => {
48 if (err) {
49 callback(err, null);
50 } else if (row) {
51 callback(null, row.category_id);
52 } else {
53 // Create General category if it doesn't exist
54 database.run(
55 'INSERT INTO category (name, description) VALUES (?, ?)',
56 ['General', 'General products category'],
57 function(err) {
58 if (err) {
59 callback(err, null);
60 } else {
61 callback(null, this.lastID);
62 }
63 }
64 );
65 }
66 });
67}
68
69// User functions
70function getUserByUsername(username, callback) {
71 database.get('SELECT * FROM users WHERE username = ?', [username], (err, row) => {
72 callback(err, row);
73 });
74}
75
76function getUserById(id, callback) {
77 database.get('SELECT * FROM users WHERE id = ?', [id], (err, row) => {
78 if (err || !row) {
79 callback(err, null);
80 return;
81 }
82
83 // Get user roles
84 database.all(
85 `SELECT r.* FROM roles r
86 JOIN user_roles ur ON r.role_id = ur.role_id
87 WHERE ur.user_id = ?`,
88 [id],
89 (err, roles) => {
90 if (err) {
91 callback(err, null);
92 } else {
93 row.roles = roles || [];
94 callback(null, row);
95 }
96 }
97 );
98 });
99}
100
101function createUser(id, username, email, password, userType, callback) {
102 const hashedPassword = bcrypt.hashSync(password, 10);
103 database.run(
104 'INSERT INTO users (id, username, email, password, user_type) VALUES (?, ?, ?, ?, ?)',
105 [id, username, email, hashedPassword, userType],
106 function(err) {
107 if (err) {
108 callback(err, null);
109 } else {
110 callback(null, id);
111 }
112 }
113 );
114}
115
116// Client functions
117function getClientByEmail(email, callback) {
118 database.get('SELECT * FROM client WHERE email = ?', [email], (err, row) => {
119 callback(err, row);
120 });
121}
122
123function getClientById(id, callback) {
124 database.get('SELECT * FROM client WHERE client_id = ?', [id], (err, row) => {
125 callback(err, row);
126 });
127}
128
129function createClient(clientData, callback) {
130 const hashedPassword = bcrypt.hashSync(clientData.password, 10);
131 database.run(
132 'INSERT INTO client (first_name, last_name, email, password) VALUES (?, ?, ?, ?)',
133 [clientData.first_name, clientData.last_name, clientData.email, hashedPassword],
134 function(err) {
135 if (err) {
136 callback(err, null);
137 } else {
138 callback(null, this.lastID);
139 }
140 }
141 );
142}
143
144function verifyClientPassword(password, hashedPassword, callback) {
145 try {
146 const isValid = bcrypt.compareSync(password, hashedPassword);
147 callback(null, isValid);
148 } catch (err) {
149 callback(err, false);
150 }
151}
152
153// Personal functions
154function getPersonalByEmail(email, callback) {
155 database.get('SELECT * FROM personal WHERE email = ?', [email], (err, row) => {
156 callback(err, row);
157 });
158}
159
160function getPersonalById(id, callback) {
161 database.get('SELECT * FROM personal WHERE id = ?', [id], (err, row) => {
162 callback(err, row);
163 });
164}
165
166// Password verification for regular users
167function verifyPassword(password, hashedPassword) {
168 return bcrypt.compareSync(password, hashedPassword);
169}
170
171// Password update
172function updatePasswordAndClearForce(userId, newPassword, callback) {
173 const hashedPassword = bcrypt.hashSync(newPassword, 10);
174 database.run(
175 'UPDATE users SET password = ?, force_password_change = 0 WHERE id = ?',
176 [hashedPassword, userId],
177 function(err) {
178 callback(err);
179 }
180 );
181}
182
183// Product functions
184function getProducts(categoryId, searchTerm, callback) {
185 let query = `
186 SELECT p.*, c.name as category_name, s.name as store_name
187 FROM product p
188 JOIN category c ON p.category_id = c.category_id
189 JOIN store s ON p.store_id = s.store_id
190 WHERE 1=1
191 `;
192 const params = [];
193
194 if (categoryId && categoryId !== 'all') {
195 query += ' AND p.category_id = ?';
196 params.push(categoryId);
197 }
198
199 if (searchTerm) {
200 query += ' AND (p.description LIKE ? OR p.code LIKE ?)';
201 params.push(`%${searchTerm}%`, `%${searchTerm}%`);
202 }
203
204 database.all(query, params, (err, rows) => {
205 if (err) {
206 callback(err, null);
207 } else {
208 callback(null, rows || []);
209 }
210 });
211}
212
213function getProductById(id, callback) {
214 database.get(
215 `SELECT p.*, c.name as category_name, s.name as store_name
216 FROM product p
217 JOIN category c ON p.category_id = c.category_id
218 JOIN store s ON p.store_id = s.store_id
219 WHERE p.id = ?`,
220 [id],
221 (err, row) => {
222 if (err || !row) {
223 callback(err, null);
224 } else {
225 // Get images for product
226 database.all(
227 'SELECT * FROM image WHERE product_code = ?',
228 [row.code],
229 (err, images) => {
230 if (err) {
231 callback(err, null);
232 } else {
233 row.images = images || [];
234 // Get colors for product
235 database.all(
236 'SELECT * FROM color WHERE product_code = ?',
237 [row.code],
238 (err, colors) => {
239 if (err) {
240 callback(err, null);
241 } else {
242 row.colors = colors || [];
243 callback(null, row);
244 }
245 }
246 );
247 }
248 }
249 );
250 }
251 }
252 );
253}
254
255function getProductByCode(code, callback) {
256 database.get(
257 `SELECT p.*, c.name as category_name, s.name as store_name
258 FROM product p
259 JOIN category c ON p.category_id = c.category_id
260 JOIN store s ON p.store_id = s.store_id
261 WHERE p.code = ?`,
262 [code],
263 (err, row) => {
264 if (err || !row) {
265 callback(err, null);
266 } else {
267 // Get images for product
268 database.all(
269 'SELECT * FROM image WHERE product_code = ?',
270 [code],
271 (err, images) => {
272 if (err) {
273 callback(err, null);
274 } else {
275 row.images = images || [];
276 // Get colors for product
277 database.all(
278 'SELECT * FROM color WHERE product_code = ?',
279 [code],
280 (err, colors) => {
281 if (err) {
282 callback(err, null);
283 } else {
284 row.colors = colors || [];
285 callback(null, row);
286 }
287 }
288 );
289 }
290 }
291 );
292 }
293 }
294 );
295}
296
297function addProduct(personalId, productData, callback) {
298 getGeneralCategoryId((err, generalCategoryId) => {
299 if (err) {
300 callback(err, null);
301 return;
302 }
303
304 const categoryId = productData.category_id || generalCategoryId;
305
306 // FIXED: Added validation for required fields
307 if (!productData.code) {
308 callback(new Error('Product code is required'), null);
309 return;
310 }
311
312 if (!productData.store_id) {
313 callback(new Error('Store ID is required'), null);
314 return;
315 }
316
317 database.run(
318 `INSERT INTO product (
319 id, code, description, price, availability, weight, dimensions,
320 production_time, category_id, store_id, created_at
321 ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, datetime('now'))`,
322 [
323 'PROD_' + Date.now().toString().slice(-8), // Generate a unique ID
324 productData.code,
325 productData.description || 'No description',
326 productData.price || 0,
327 productData.availability || 0,
328 productData.weight || 0,
329 productData.dimensions || '0x0x0',
330 productData.production_time || 1,
331 categoryId,
332 productData.store_id
333 ],
334 function(err) {
335 if (err) {
336 callback(err, null);
337 } else {
338 const productId = this.lastID;
339
340 // Log the change
341 database.run(
342 `INSERT INTO "change" (date_and_time, product_code, changes)
343 VALUES (datetime('now'), ?, ?)`,
344 [productData.code, 'Product created'],
345 function(err) {
346 if (err) {
347 console.error('Error logging product creation:', err);
348 }
349 }
350 );
351
352 // Log who made the change
353 database.run(
354 `INSERT INTO makes_change (personal_id, change_date_time, product_code)
355 VALUES (?, datetime('now'), ?)`,
356 [personalId, productData.code],
357 function(err) {
358 if (err) {
359 console.error('Error logging change maker:', err);
360 }
361 }
362 );
363
364 // Insert images if provided
365 if (productData.images && Array.isArray(productData.images) && productData.images.length > 0) {
366 productData.images.forEach((imageUrl, index) => {
367 database.run(
368 `INSERT INTO image (product_code, image_url, is_primary)
369 VALUES (?, ?, ?)`,
370 [productData.code, imageUrl, index === 0 ? 1 : 0],
371 function(err) {
372 if (err) {
373 console.error('Error inserting image:', err);
374 }
375 }
376 );
377 });
378 }
379
380 // Insert colors if provided
381 if (productData.colors && Array.isArray(productData.colors) && productData.colors.length > 0) {
382 productData.colors.forEach(color => {
383 database.run(
384 `INSERT INTO color (product_code, name)
385 VALUES (?, ?)`,
386 [productData.code, color],
387 function(err) {
388 if (err) {
389 console.error('Error inserting color:', err);
390 }
391 }
392 );
393 });
394 }
395
396 callback(null, productId);
397 }
398 }
399 );
400 });
401}
402
403function updateProduct(personalId, productData, callback) {
404 const updates = [];
405 const params = [];
406
407 if (productData.description !== undefined) {
408 updates.push('description = ?');
409 params.push(productData.description);
410 }
411 if (productData.price !== undefined) {
412 updates.push('price = ?');
413 params.push(productData.price);
414 }
415 if (productData.availability !== undefined) {
416 updates.push('availability = ?');
417 params.push(productData.availability);
418 }
419 if (productData.weight !== undefined) {
420 updates.push('weight = ?');
421 params.push(productData.weight);
422 }
423 if (productData.dimensions !== undefined) {
424 updates.push('dimensions = ?');
425 params.push(productData.dimensions);
426 }
427 if (productData.production_time !== undefined) {
428 updates.push('production_time = ?');
429 params.push(productData.production_time);
430 }
431 if (productData.category_id !== undefined) {
432 updates.push('category_id = ?');
433 params.push(productData.category_id);
434 }
435
436 if (updates.length === 0) {
437 callback(null, 0);
438 return;
439 }
440
441 params.push(productData.code);
442
443 database.run(
444 `UPDATE product SET ${updates.join(', ')} WHERE code = ?`,
445 params,
446 function(err) {
447 if (err) {
448 callback(err, null);
449 } else {
450 // Log the change
451 const changesDesc = `Product updated: ${updates.join(', ')}`;
452 database.run(
453 `INSERT INTO "change" (date_and_time, product_code, changes)
454 VALUES (datetime('now'), ?, ?)`,
455 [productData.code, changesDesc],
456 function(err) {
457 if (err) {
458 console.error('Error logging product update:', err);
459 }
460 // Log who made the change
461 database.run(
462 `INSERT INTO makes_change (personal_id, change_date_time, product_code)
463 VALUES (?, datetime('now'), ?)`,
464 [personalId, productData.code],
465 function(err) {
466 if (err) {
467 console.error('Error logging change maker:', err);
468 }
469 }
470 );
471 }
472 );
473
474 // Handle images if provided
475 if (productData.images && Array.isArray(productData.images)) {
476 // Delete old images first
477 database.run('DELETE FROM image WHERE product_code = ?', [productData.code], (err) => {
478 if (!err) {
479 // Insert new images
480 productData.images.forEach(image => {
481 database.run(
482 'INSERT INTO image (product_code, image) VALUES (?, ?)',
483 [productData.code, image]
484 );
485 });
486 }
487 });
488 }
489
490 // Handle colors if provided
491 if (productData.colors && Array.isArray(productData.colors)) {
492 // Delete old colors first
493 database.run('DELETE FROM color WHERE product_code = ?', [productData.code], (err) => {
494 if (!err) {
495 // Insert new colors
496 productData.colors.forEach(color => {
497 database.run(
498 'INSERT INTO color (product_code, color) VALUES (?, ?)',
499 [productData.code, color]
500 );
501 });
502 }
503 });
504 }
505
506 callback(null, this.changes);
507 }
508 }
509 );
510}
511
512function deleteProduct(productCode, storeId, personalId, callback) {
513 database.run('BEGIN TRANSACTION', (err) => {
514 if (err) {
515 callback(err);
516 return;
517 }
518
519 // Log the deletion
520 database.run(
521 `INSERT INTO "change" (date_and_time, product_code, changes)
522 VALUES (datetime('now'), ?, ?)`,
523 [productCode, 'Product deleted'],
524 function(err) {
525 if (err) {
526 database.run('ROLLBACK');
527 callback(err);
528 return;
529 }
530
531 // Log who deleted it
532 database.run(
533 `INSERT INTO makes_change (personal_id, change_date_time, product_code)
534 VALUES (?, datetime('now'), ?)`,
535 [personalId, productCode],
536 function(err) {
537 if (err) {
538 database.run('ROLLBACK');
539 callback(err);
540 return;
541 }
542
543 // Delete the product (cascades to image, color)
544 database.run(
545 'DELETE FROM product WHERE code = ? AND store_id = ?',
546 [productCode, storeId],
547 function(err) {
548 if (err) {
549 database.run('ROLLBACK');
550 callback(err);
551 } else {
552 database.run('COMMIT', callback);
553 }
554 }
555 );
556 }
557 );
558 }
559 );
560 });
561}
562
563// Category functions
564function getCategories(callback) {
565 database.all('SELECT * FROM category ORDER BY name', [], (err, rows) => {
566 callback(err, rows || []);
567 });
568}
569
570function getCategoriesWithParents(callback) {
571 database.all(
572 `SELECT c1.*, c2.name as parent_name
573 FROM category c1
574 LEFT JOIN category c2 ON c1.parent_category_id = c2.category_id
575 ORDER BY c1.name`,
576 [],
577 (err, rows) => {
578 callback(err, rows || []);
579 }
580 );
581}
582
583function createCategory(categoryData, callback) {
584 database.run(
585 'INSERT INTO category (name, description, parent_category_id) VALUES (?, ?, ?)',
586 [categoryData.name, categoryData.description || null, categoryData.parent_id || null],
587 function(err) {
588 if (err) {
589 callback(err, null);
590 } else {
591 callback(null, {
592 id: this.lastID,
593 name: categoryData.name,
594 parent_id: categoryData.parent_id,
595 description: categoryData.description
596 });
597 }
598 }
599 );
600}
601
602// Store functions
603function getStores(callback) {
604 database.all('SELECT * FROM store ORDER BY name', [], (err, rows) => {
605 callback(err, rows || []);
606 });
607}
608
609function getStoreProducts(storeId, callback) {
610 database.all(
611 `SELECT p.*, c.name as category_name
612 FROM product p
613 JOIN category c ON p.category_id = c.category_id
614 WHERE p.store_id = ?
615 ORDER BY p.code`,
616 [storeId],
617 (err, rows) => {
618 if (err) {
619 callback(err, null);
620 } else {
621 callback(null, rows || []);
622 }
623 }
624 );
625}
626
627function getStoreOrders(storeId, callback) {
628 database.all(
629 `SELECT o.*, c.first_name, c.last_name
630 FROM "order" o
631 JOIN client c ON o.client_id = c.client_id
632 WHERE o.store_id = ?
633 ORDER BY o.order_date DESC`,
634 [storeId],
635 (err, rows) => {
636 if (err) {
637 callback(err, null);
638 } else {
639 // Get order items for each order
640 let completed = 0;
641 const orders = rows || [];
642
643 if (orders.length === 0) {
644 callback(null, []);
645 return;
646 }
647
648 orders.forEach(order => {
649 database.all(
650 `SELECT oi.*, p.description
651 FROM order_items oi
652 JOIN product p ON oi.product_code = p.code
653 WHERE oi.order_num = ?`,
654 [order.order_num],
655 (err, items) => {
656 if (!err) {
657 order.items = items || [];
658 } else {
659 order.items = [];
660 }
661
662 completed++;
663 if (completed === orders.length) {
664 callback(null, orders);
665 }
666 }
667 );
668 });
669 }
670 }
671 );
672}
673
674function getStoreEmployees(storeId, callback) {
675 database.all(
676 `SELECT p.*, e.date_of_hire, perm.type as permission_type, perm.authorisation
677 FROM personal p
678 JOIN works_in_store w ON p.id = w.personal_id
679 LEFT JOIN employees e ON p.id = e.employee_id
680 LEFT JOIN permissions perm ON p.id = perm.personal_id
681 WHERE w.store_id = ?`,
682 [storeId],
683 (err, rows) => {
684 callback(err, rows || []);
685 }
686 );
687}
688
689function getStoreReports(storeId, callback) {
690 database.all(
691 `SELECT * FROM report
692 WHERE store_id = ?
693 ORDER BY generated_at DESC`,
694 [storeId],
695 (err, rows) => {
696 callback(err, rows || []);
697 }
698 );
699}
700
701function getStoreStats(storeId, callback) {
702 const stats = {};
703
704 // Get total products
705 database.get(
706 'SELECT COUNT(*) as total_products FROM product WHERE store_id = ?',
707 [storeId],
708 (err, row) => {
709 stats.total_products = row ? row.total_products : 0;
710
711 // Get total orders
712 database.get(
713 'SELECT COUNT(*) as total_orders FROM "order" WHERE store_id = ?',
714 [storeId],
715 (err, row) => {
716 stats.total_orders = row ? row.total_orders : 0;
717
718 // Get total revenue
719 database.get(
720 `SELECT SUM(oi.price * oi.quantity) as total_revenue
721 FROM order_items oi
722 JOIN "order" o ON oi.order_num = o.order_num
723 WHERE o.store_id = ?`,
724 [storeId],
725 (err, row) => {
726 stats.total_revenue = row && row.total_revenue ? row.total_revenue : 0;
727
728 // Get average rating
729 database.get(
730 `SELECT AVG(rating) as avg_rating
731 FROM review r
732 JOIN product p ON r.product_code = p.code
733 WHERE p.store_id = ?`,
734 [storeId],
735 (err, row) => {
736 stats.avg_rating = row && row.avg_rating ? row.avg_rating : 0;
737
738 callback(null, stats);
739 }
740 );
741 }
742 );
743 }
744 );
745 }
746 );
747}
748
749// Order functions
750function createOrderNew(orderData, callback) {
751 database.run('BEGIN TRANSACTION', (err) => {
752 if (err) {
753 callback(err, null);
754 return;
755 }
756
757 database.run(
758 `INSERT INTO "order" (order_num, client_id, order_date, quantity, payment_method,
759 discount, delivery_address, store_id)
760 VALUES (?, ?, datetime('now'), ?, ?, ?, ?, ?)`,
761 [
762 orderData.order_num,
763 orderData.client_id,
764 orderData.quantity,
765 orderData.payment_method,
766 orderData.discount,
767 orderData.delivery_address,
768 orderData.store_id
769 ],
770 function(err) {
771 if (err) {
772 database.run('ROLLBACK');
773 callback(err, null);
774 return;
775 }
776
777 let itemsInserted = 0;
778 const items = orderData.items || [];
779
780 if (items.length === 0) {
781 database.run('COMMIT');
782 callback(null, orderData.order_num);
783 return;
784 }
785
786 items.forEach(item => {
787 database.run(
788 `INSERT INTO order_items (order_num, product_code, quantity, price)
789 VALUES (?, ?, ?, ?)`,
790 [orderData.order_num, item.product_code, item.quantity, item.price],
791 function(err) {
792 if (err) {
793 database.run('ROLLBACK');
794 callback(err, null);
795 return;
796 }
797
798 itemsInserted++;
799 if (itemsInserted === items.length) {
800 database.run('COMMIT', (err) => {
801 if (err) {
802 callback(err, null);
803 } else {
804 callback(null, orderData.order_num);
805 }
806 });
807 }
808 }
809 );
810 });
811 }
812 );
813 });
814}
815
816function getOrdersByClient(clientId, callback) {
817 database.all(
818 `SELECT o.*, s.name as store_name
819 FROM "order" o
820 JOIN store s ON o.store_id = s.store_id
821 WHERE o.client_id = ?
822 ORDER BY o.order_date DESC`,
823 [clientId],
824 (err, rows) => {
825 if (err) {
826 callback(err, null);
827 } else {
828 // Get order items for each order
829 let completed = 0;
830 const orders = rows || [];
831
832 if (orders.length === 0) {
833 callback(null, []);
834 return;
835 }
836
837 orders.forEach(order => {
838 database.all(
839 `SELECT oi.*, p.description
840 FROM order_items oi
841 JOIN product p ON oi.product_code = p.code
842 WHERE oi.order_num = ?`,
843 [order.order_num],
844 (err, items) => {
845 if (!err) {
846 order.items = items || [];
847 } else {
848 order.items = [];
849 }
850
851 completed++;
852 if (completed === orders.length) {
853 callback(null, orders);
854 }
855 }
856 );
857 });
858 }
859 }
860 );
861}
862
863function getAllOrders(callback) {
864 database.all(
865 `SELECT o.*, c.first_name, c.last_name, s.name as store_name
866 FROM "order" o
867 JOIN client c ON o.client_id = c.client_id
868 JOIN store s ON o.store_id = s.store_id
869 ORDER BY o.order_date DESC`,
870 [],
871 (err, rows) => {
872 callback(err, rows || []);
873 }
874 );
875}
876
877// Review functions
878function createReviewNew(reviewData, callback) {
879 database.run(
880 `INSERT INTO review (review_id, client_id, product_code, rating, comment, review_date)
881 VALUES (?, ?, ?, ?, ?, datetime('now'))`,
882 [
883 'REV' + Date.now().toString().slice(-8),
884 reviewData.client_id,
885 reviewData.product_code,
886 reviewData.rating,
887 reviewData.comment || ''
888 ],
889 function(err) {
890 if (err) {
891 callback(err, null);
892 } else {
893 callback(null, this.lastID);
894 }
895 }
896 );
897}
898
899// Request functions
900function createRequest(requestData, callback) {
901 database.run(
902 `INSERT INTO request (request_num, date_and_time, problem, client_id, store_id)
903 VALUES (?, ?, ?, ?, ?)`,
904 [
905 requestData.request_num,
906 requestData.date_and_time,
907 requestData.problem,
908 requestData.client_id,
909 requestData.store_id
910 ],
911 function(err) {
912 if (err) {
913 callback(err, null);
914 } else {
915 callback(null, requestData.request_num);
916 }
917 }
918 );
919}
920
921// Refund functions
922function createRefund(refundData, callback) {
923 database.run(
924 `INSERT INTO refund (refund_id, order_num, amount, reason, request_date)
925 VALUES (?, ?, ?, ?, datetime('now'))`,
926 [
927 refundData.refund_id,
928 refundData.order_num,
929 refundData.amount,
930 refundData.reason
931 ],
932 function(err) {
933 if (err) {
934 callback(err, null);
935 } else {
936 callback(null, refundData.refund_id);
937 }
938 }
939 );
940}
941
942// Employee task functions
943function getEmployeeTasks(personalId, storeId, callback) {
944 const tasks = {
945 pending_orders: [],
946 pending_requests: [],
947 pending_refunds: []
948 };
949
950 // Get pending orders
951 database.all(
952 `SELECT o.*, c.first_name, c.last_name
953 FROM "order" o
954 JOIN client c ON o.client_id = c.client_id
955 WHERE o.store_id = ? AND o.status = 'pending'
956 ORDER BY o.order_date ASC`,
957 [storeId],
958 (err, rows) => {
959 if (!err) {
960 tasks.pending_orders = rows || [];
961 }
962
963 // Get pending requests
964 database.all(
965 `SELECT r.*, c.first_name, c.last_name
966 FROM request r
967 JOIN client c ON r.client_id = c.client_id
968 WHERE r.store_id = ? AND r.status = 'pending'
969 ORDER BY r.date_and_time ASC`,
970 [storeId],
971 (err, rows) => {
972 if (!err) {
973 tasks.pending_requests = rows || [];
974 }
975
976 // Get pending refunds
977 database.all(
978 `SELECT rf.*, o.client_id, c.first_name, c.last_name
979 FROM refund rf
980 JOIN "order" o ON rf.order_num = o.order_num
981 JOIN client c ON o.client_id = c.client_id
982 WHERE o.store_id = ? AND rf.status = 'pending'
983 ORDER BY rf.request_date ASC`,
984 [storeId],
985 (err, rows) => {
986 if (!err) {
987 tasks.pending_refunds = rows || [];
988 }
989 callback(null, tasks);
990 }
991 );
992 }
993 );
994 }
995 );
996}
997
998// Client stats functions
999function getClientStats(clientId, callback) {
1000 const stats = {};
1001
1002 // Get total orders
1003 database.get(
1004 'SELECT COUNT(*) as total_orders FROM "order" WHERE client_id = ?',
1005 [clientId],
1006 (err, row) => {
1007 stats.total_orders = row ? row.total_orders : 0;
1008
1009 // Get total spent
1010 database.get(
1011 `SELECT SUM(oi.price * oi.quantity) as total_spent
1012 FROM order_items oi
1013 JOIN "order" o ON oi.order_num = o.order_num
1014 WHERE o.client_id = ?`,
1015 [clientId],
1016 (err, row) => {
1017 stats.total_spent = row && row.total_spent ? row.total_spent : 0;
1018
1019 // Get pending orders
1020 database.get(
1021 'SELECT COUNT(*) as pending_orders FROM "order" WHERE client_id = ? AND status = "pending"',
1022 [clientId],
1023 (err, row) => {
1024 stats.pending_orders = row ? row.pending_orders : 0;
1025
1026 // Get delivered orders
1027 database.get(
1028 'SELECT COUNT(*) as delivered_orders FROM "order" WHERE client_id = ? AND status = "delivered"',
1029 [clientId],
1030 (err, row) => {
1031 stats.delivered_orders = row ? row.delivered_orders : 0;
1032
1033 callback(null, stats);
1034 }
1035 );
1036 }
1037 );
1038 }
1039 );
1040 }
1041 );
1042}
1043
1044// User functions for admin
1045function getAllUsers(callback) {
1046 const users = [];
1047
1048 // Get client users
1049 database.all(
1050 `SELECT client_id as id, first_name, last_name, email, 'client' as user_type
1051 FROM client
1052 ORDER BY client_id`,
1053 [],
1054 (err, rows) => {
1055 if (!err) {
1056 users.push(...(rows || []));
1057 }
1058
1059 // Get personal users
1060 database.all(
1061 `SELECT p.id, p.first_name, p.last_name, p.email,
1062 CASE WHEN b.boss_id IS NOT NULL THEN 'store_owner'
1063 WHEN e.employee_id IS NOT NULL THEN 'store_employee'
1064 ELSE 'personal' END as user_type
1065 FROM personal p
1066 LEFT JOIN boss b ON p.id = b.boss_id
1067 LEFT JOIN employees e ON p.id = e.employee_id
1068 ORDER BY p.id`,
1069 [],
1070 (err, rows) => {
1071 if (!err) {
1072 users.push(...(rows || []));
1073 }
1074
1075 // Get system users
1076 database.all(
1077 `SELECT id, username, email, user_type
1078 FROM users
1079 ORDER BY id`,
1080 [],
1081 (err, rows) => {
1082 if (!err) {
1083 users.push(...(rows || []));
1084 }
1085 callback(null, users);
1086 }
1087 );
1088 }
1089 );
1090 }
1091 );
1092}
1093
1094// Audit log function
1095function logAudit(userId, action, resourceType, resourceId, details, ipAddress) {
1096 database.run(
1097 `INSERT INTO audit_log (user_id, action, resource_type, resource_id, details, ip_address)
1098 VALUES (?, ?, ?, ?, ?, ?)`,
1099 [userId, action, resourceType, resourceId, details, ipAddress],
1100 (err) => {
1101 if (err) {
1102 console.error('Error logging audit:', err);
1103 }
1104 }
1105 );
1106}
1107
1108module.exports = {
1109 database,
1110 ensureGeneralCategory,
1111 getGeneralCategoryId,
1112 getUserByUsername,
1113 getUserById,
1114 createUser,
1115 getClientByEmail,
1116 getClientById,
1117 createClient,
1118 verifyClientPassword,
1119 getPersonalByEmail,
1120 getPersonalById,
1121 verifyPassword,
1122 updatePasswordAndClearForce,
1123 getProducts,
1124 getProductById,
1125 getProductByCode,
1126 addProduct,
1127 updateProduct,
1128 deleteProduct,
1129 getCategories,
1130 getCategoriesWithParents,
1131 createCategory,
1132 getStores,
1133 getStoreProducts,
1134 getStoreOrders,
1135 getStoreEmployees,
1136 getStoreReports,
1137 getStoreStats,
1138 createOrderNew,
1139 getOrdersByClient,
1140 getAllOrders,
1141 createReviewNew,
1142 createRequest,
1143 createRefund,
1144 getEmployeeTasks,
1145 getClientStats,
1146 getAllUsers,
1147 logAudit
1148};
Note: See TracBrowser for help on using the repository browser.