source: database.js

main
Last change on this file was 6c7cfa6, checked in by Klimentina Efremova <klimentina08642@…>, 8 days ago

Implemented Advaced database reports
  • Property mode set to 100644
File size: 87.3 KB
RevLine 
[62b2964]1const { Pool } = require('pg');
[69f2a41]2const bcrypt = require('bcryptjs');
[62b2964]3require('dotenv').config();
4
5/*
6 * ============================================================
7 * PostgreSQL CONNECTION
8 * ============================================================
9 *
10 * Put your actual FINKI connection information in .env
11 *
12 * Example:
13 *
14 * PGHOST=localhost
15 * PGPORT=5432
16 * PGDATABASE=handcraft
17 * PGUSER=your_username
18 * PGPASSWORD=your_password
19 *
20 * If you use the SSH tunnel, PGHOST/PGPORT will normally
21 * point to the LOCAL end of the SSH tunnel.
22 */
23
24const pool = new Pool({
25 host: process.env.PGHOST || 'localhost',
26 port: parseInt(process.env.PGPORT || '5432', 10),
[33517cc]27 database: process.env.PGDATABASE || 'db_202526z_va_prj_handcraft_store',
28 user: process.env.PGUSER || 'db_202526z_va_prj_handcraft_store_owner',
[62b2964]29 password: process.env.PGPASSWORD || '',
30 max: 10,
31 idleTimeoutMillis: 30000,
32 connectionTimeoutMillis: 10000
33});
34
35pool.on('connect', () => {
36 console.log('✅ Connected to PostgreSQL database');
37});
[69f2a41]38
[62b2964]39pool.on('error', (err) => {
40 console.error('❌ Unexpected PostgreSQL pool error:', err);
41});
[69f2a41]42
[62b2964]43/*
44 * ============================================================
45 * HELPER
46 * ============================================================
47 */
48
49function query(text, params = [], callback) {
50 pool.query(text, params)
51 .then(result => {
52 callback(null, result);
53 })
54 .catch(err => {
55 callback(err, null);
56 });
[69f2a41]57}
58
59
[62b2964]60/*
61 * ============================================================
62 * GENERAL CATEGORY
63 * ============================================================
64 */
[69f2a41]65
66function ensureGeneralCategory(callback) {
[62b2964]67
68 query(
69 `SELECT category_id, name
70 FROM category
71 WHERE category_id = 1`,
72 [],
73 (err, result) => {
74
75 if (err) {
76 callback(err);
77 return;
78 }
79
80 const row = result.rows[0];
81
82 if (row && row.name === 'General') {
83
84 console.log('✅ General category exists with ID: 1');
85 callback(null);
86
87 } else if (row && row.name !== 'General') {
88
89 query(
90 `UPDATE category
91 SET name = $1,
92 description = $2
93 WHERE category_id = 1`,
94 ['General', 'General products category'],
95 (err) => {
96
97 if (err) {
98 callback(err);
99 } else {
100 console.log('✅ Updated category ID 1 to General');
101 callback(null);
102 }
[591278c]103 }
[62b2964]104 );
105
106 } else {
107
108 query(
109 `INSERT INTO category
110 (category_id, name, description)
111 VALUES
112 (1, $1, $2)
113 ON CONFLICT (category_id) DO NOTHING`,
114 ['General', 'General products category'],
115 (err) => {
116
117 if (err) {
118 callback(err);
119 } else {
120 console.log('✅ Created General category with ID: 1');
121 callback(null);
122 }
[591278c]123 }
[62b2964]124 );
125 }
[69f2a41]126 }
[62b2964]127 );
[69f2a41]128}
129
[62b2964]130
[69f2a41]131function getGeneralCategoryId(callback) {
[62b2964]132
133 query(
134 `SELECT category_id
135 FROM category
136 WHERE category_id = 1
137 AND name = $1`,
138 ['General'],
139 (err, result) => {
140
141 if (err) {
142 callback(err, null);
143 return;
144 }
145
146 if (result.rows.length > 0) {
147 callback(null, result.rows[0].category_id);
148 return;
149 }
150
151 query(
152 `SELECT category_id
153 FROM category
154 WHERE name = $1`,
155 ['General'],
156 (err, result) => {
157
158 if (err) {
159 callback(err, null);
160 return;
161 }
162
163 if (result.rows.length > 0) {
164 callback(null, result.rows[0].category_id);
165 return;
166 }
167
168 query(
169 `INSERT INTO category
170 (name, description)
171 VALUES
172 ($1, $2)
173 RETURNING category_id`,
[591278c]174 ['General', 'General products category'],
[62b2964]175 (err, result) => {
176
[591278c]177 if (err) {
178 callback(err, null);
179 } else {
[62b2964]180 const newId = result.rows[0].category_id;
181
182 console.log(
183 `✅ Created new General category with ID: ${newId}`
184 );
185
[591278c]186 callback(null, newId);
187 }
188 }
189 );
[69f2a41]190 }
[62b2964]191 );
[69f2a41]192 }
[62b2964]193 );
[69f2a41]194}
195
[62b2964]196
197/*
198 * ============================================================
199 * USER FUNCTIONS
200 * ============================================================
201 */
202
[69f2a41]203function getUserByUsername(username, callback) {
[62b2964]204
205 query(
206 `SELECT *
207 FROM users
208 WHERE username = $1`,
209 [username],
210 (err, result) => {
211
212 callback(
213 err,
214 result ? result.rows[0] : null
215 );
216 }
217 );
[69f2a41]218}
219
[62b2964]220
[69f2a41]221function getUserById(id, callback) {
222
[62b2964]223 query(
224 `SELECT *
225 FROM users
226 WHERE id = $1`,
227 [id],
228 (err, result) => {
229
230 if (err || !result || result.rows.length === 0) {
231 callback(err, null);
232 return;
[69f2a41]233 }
[62b2964]234
235 const row = result.rows[0];
236
237 query(
238 `SELECT r.*
239 FROM roles r
240 JOIN user_roles ur
241 ON r.role_id = ur.role_id
242 WHERE ur.user_id = $1`,
243 [id],
244 (err, result) => {
245
246 if (err) {
247 callback(err, null);
248 } else {
249 row.roles = result.rows || [];
250 callback(null, row);
251 }
252 }
253 );
254 }
255 );
[69f2a41]256}
257
[62b2964]258
[69f2a41]259function createUser(id, username, email, password, userType, callback) {
[62b2964]260
[69f2a41]261 const hashedPassword = bcrypt.hashSync(password, 10);
[62b2964]262
263 query(
264 `INSERT INTO users
265 (id, username, email, password, user_type)
266 VALUES
267 ($1, $2, $3, $4, $5)`,
[69f2a41]268 [id, username, email, hashedPassword, userType],
[62b2964]269 (err) => {
270
[69f2a41]271 if (err) {
272 callback(err, null);
273 } else {
274 callback(null, id);
275 }
276 }
277 );
278}
279
[62b2964]280
281/*
282 * ============================================================
283 * CLIENT FUNCTIONS
284 * ============================================================
285 */
286
[69f2a41]287function getClientByEmail(email, callback) {
[62b2964]288
289 query(
290 `SELECT *
291 FROM client
292 WHERE email = $1`,
293 [email],
294 (err, result) => {
295
296 callback(
297 err,
298 result ? result.rows[0] : null
299 );
300 }
301 );
[69f2a41]302}
303
[62b2964]304
[69f2a41]305function getClientById(id, callback) {
[62b2964]306
307 query(
308 `SELECT *
309 FROM client
310 WHERE client_id = $1`,
311 [id],
312 (err, result) => {
313
314 callback(
315 err,
316 result ? result.rows[0] : null
317 );
318 }
319 );
[69f2a41]320}
321
[62b2964]322
[69f2a41]323function createClient(clientData, callback) {
[62b2964]324
325 const hashedPassword =
326 bcrypt.hashSync(clientData.password, 10);
327
328 query(
329 `INSERT INTO client
330 (first_name, last_name, email, password)
331 VALUES
332 ($1, $2, $3, $4)
333 RETURNING client_id`,
334 [
335 clientData.first_name,
336 clientData.last_name,
337 clientData.email,
338 hashedPassword
339 ],
340 (err, result) => {
341
[69f2a41]342 if (err) {
343 callback(err, null);
344 } else {
[62b2964]345 callback(
346 null,
347 result.rows[0].client_id
348 );
[69f2a41]349 }
350 }
351 );
352}
353
[62b2964]354
355function verifyClientPassword(
356 password,
357 hashedPassword,
358 callback
359) {
360
[69f2a41]361 try {
[62b2964]362
363 const isValid =
364 bcrypt.compareSync(password, hashedPassword);
365
[69f2a41]366 callback(null, isValid);
[62b2964]367
[69f2a41]368 } catch (err) {
[62b2964]369
[69f2a41]370 callback(err, false);
371 }
372}
373
[62b2964]374
375/*
376 * ============================================================
377 * PERSONAL / EMPLOYEE FUNCTIONS
378 * ============================================================
379 */
380
[69f2a41]381function getPersonalByEmail(email, callback) {
[62b2964]382
383 query(
384 `SELECT *
385 FROM personal
386 WHERE email = $1`,
387 [email],
388 (err, result) => {
389
390 callback(
391 err,
392 result ? result.rows[0] : null
393 );
394 }
395 );
[69f2a41]396}
397
[62b2964]398
[69f2a41]399function getPersonalById(id, callback) {
[62b2964]400
401 query(
402 `SELECT *
403 FROM personal
404 WHERE id = $1`,
405 [id],
406 (err, result) => {
407
408 callback(
409 err,
410 result ? result.rows[0] : null
411 );
412 }
413 );
[69f2a41]414}
415
[62b2964]416
[69f2a41]417function verifyPassword(password, hashedPassword) {
418 return bcrypt.compareSync(password, hashedPassword);
419}
420
[62b2964]421
422function updatePasswordAndClearForce(
423 userId,
424 newPassword,
425 callback
426) {
427
428 const hashedPassword =
429 bcrypt.hashSync(newPassword, 10);
430
431 query(
432 `UPDATE users
433 SET password = $1,
434 force_password_change = 0
435 WHERE id = $2`,
[69f2a41]436 [hashedPassword, userId],
[62b2964]437 (err) => {
438
[69f2a41]439 callback(err);
440 }
441 );
442}
443
[62b2964]444
445/*
446 * ============================================================
447 * PRODUCT FUNCTIONS
448 * ============================================================
449 */
450
[69f2a41]451function getProducts(categoryId, searchTerm, callback) {
[62b2964]452
453 let queryText = `
454 SELECT
455 p.*,
456 c.name AS category_name,
457 s.name AS store_name
458 FROM product p
459 JOIN category c
460 ON p.category_id = c.category_id
461 JOIN store s
462 ON p.store_id = s.store_id
463 WHERE 1 = 1
464 `;
465
[69f2a41]466 const params = [];
[62b2964]467 let paramIndex = 1;
[69f2a41]468
469 if (categoryId && categoryId !== 'all') {
[62b2964]470
471 queryText +=
472 ` AND p.category_id = $${paramIndex}`;
473
[69f2a41]474 params.push(categoryId);
[62b2964]475 paramIndex++;
[69f2a41]476 }
477
478 if (searchTerm) {
[62b2964]479
480 queryText +=
481 ` AND (
482 p.description ILIKE $${paramIndex}
483 OR p.code ILIKE $${paramIndex + 1}
484 )`;
485
486 params.push(`%${searchTerm}%`);
487 params.push(`%${searchTerm}%`);
488
489 paramIndex += 2;
[69f2a41]490 }
491
[62b2964]492 queryText += ` ORDER BY p.code`;
493
494 query(
495 queryText,
496 params,
497 (err, result) => {
498
499 if (err) {
500 callback(err, null);
501 } else {
502 callback(null, result.rows || []);
503 }
[69f2a41]504 }
[62b2964]505 );
[69f2a41]506}
507
[62b2964]508
[69f2a41]509function getProductById(id, callback) {
[62b2964]510
511 query(
512 `SELECT
513 p.*,
514 c.name AS category_name,
515 s.name AS store_name
516 FROM product p
517 JOIN category c
518 ON p.category_id = c.category_id
519 JOIN store s
520 ON p.store_id = s.store_id
521 WHERE p.id = $1`,
[69f2a41]522 [id],
[62b2964]523 (err, result) => {
524
525 if (err || result.rows.length === 0) {
[69f2a41]526 callback(err, null);
[62b2964]527 return;
[69f2a41]528 }
[62b2964]529
530 const row = result.rows[0];
531
532 query(
533 `SELECT *
534 FROM image
535 WHERE product_code = $1`,
536 [row.code],
537 (err, result) => {
538
539 if (err) {
540 callback(err, null);
541 return;
542 }
543
544 row.images = result.rows || [];
545
546 query(
547 `SELECT *
548 FROM color
549 WHERE product_code = $1`,
550 [row.code],
551 (err, result) => {
552
553 if (err) {
554 callback(err, null);
555 } else {
556 row.colors =
557 result.rows || [];
558
559 callback(null, row);
560 }
561 }
562 );
563 }
564 );
[69f2a41]565 }
566 );
567}
568
[62b2964]569
[69f2a41]570function getProductByCode(code, callback) {
[62b2964]571
572 query(
573 `SELECT
574 p.*,
575 c.name AS category_name,
576 s.name AS store_name
577 FROM product p
578 JOIN category c
579 ON p.category_id = c.category_id
580 JOIN store s
581 ON p.store_id = s.store_id
582 WHERE p.code = $1`,
[69f2a41]583 [code],
[62b2964]584 (err, result) => {
585
586 if (err || result.rows.length === 0) {
[69f2a41]587 callback(err, null);
[62b2964]588 return;
[69f2a41]589 }
[62b2964]590
591 const row = result.rows[0];
592
593 query(
594 `SELECT *
595 FROM image
596 WHERE product_code = $1`,
597 [code],
598 (err, result) => {
599
600 if (err) {
601 callback(err, null);
602 return;
603 }
604
605 row.images = result.rows || [];
606
607 query(
608 `SELECT *
609 FROM color
610 WHERE product_code = $1`,
611 [code],
612 (err, result) => {
613
614 if (err) {
615 callback(err, null);
616 } else {
617
618 row.colors =
619 result.rows || [];
620
621 callback(null, row);
622 }
623 }
624 );
625 }
626 );
[69f2a41]627 }
628 );
629}
630
[62b2964]631
[69f2a41]632function addProduct(personalId, productData, callback) {
[62b2964]633
[69f2a41]634 getGeneralCategoryId((err, generalCategoryId) => {
[62b2964]635
[69f2a41]636 if (err) {
637 callback(err, null);
638 return;
639 }
640
[62b2964]641 const categoryId =
642 productData.category_id || generalCategoryId;
[69f2a41]643
644 if (!productData.code) {
[62b2964]645 callback(
646 new Error('Product code is required'),
647 null
648 );
[69f2a41]649 return;
650 }
651
652 if (!productData.store_id) {
[62b2964]653 callback(
654 new Error('Store ID is required'),
655 null
656 );
[69f2a41]657 return;
658 }
659
[62b2964]660 const productId =
661 'PROD_' +
662 Date.now().toString().slice(-8);
663
664 query(
665 `INSERT INTO product
666 (
667 id,
668 code,
669 description,
670 price,
671 availability,
672 weight,
673 dimensions,
674 production_time,
675 category_id,
676 store_id,
677 created_at
678 )
679 VALUES
680 (
681 $1,
682 $2,
683 $3,
684 $4,
685 $5,
686 $6,
687 $7,
688 $8,
689 $9,
690 $10,
691 NOW()
692 )
693 RETURNING id`,
[69f2a41]694 [
[62b2964]695 productId,
[69f2a41]696 productData.code,
697 productData.description || 'No description',
698 productData.price || 0,
699 productData.availability || 0,
700 productData.weight || 0,
701 productData.dimensions || '0x0x0',
702 productData.production_time || 1,
703 categoryId,
704 productData.store_id
705 ],
[62b2964]706 (err, result) => {
707
[69f2a41]708 if (err) {
709 callback(err, null);
[62b2964]710 return;
711 }
[69f2a41]712
[62b2964]713 const returnedId = result.rows[0].id;
714
715 /*
716 * Log product creation
717 */
718 query(
719 `INSERT INTO "change"
720 (date_and_time, product_code, changes)
721 VALUES
722 (NOW(), $1, $2)`,
723 [
724 productData.code,
725 'Product created'
726 ],
727 (err) => {
728
729 if (err) {
730 console.error(
731 'Error logging product creation:',
732 err
733 );
[69f2a41]734 }
[62b2964]735 }
736 );
[69f2a41]737
[62b2964]738 /*
739 * Log who made the change
740 */
741 query(
742 `INSERT INTO makes_change
743 (
744 personal_id,
745 change_date_time,
746 product_code
747 )
748 VALUES
749 ($1, NOW(), $2)`,
750 [
751 personalId,
752 productData.code
753 ],
754 (err) => {
755
756 if (err) {
757 console.error(
758 'Error logging change maker:',
759 err
[69f2a41]760 );
[62b2964]761 }
[69f2a41]762 }
[62b2964]763 );
764
765 /*
766 * Insert images
767 */
768 if (
769 productData.images &&
770 Array.isArray(productData.images) &&
771 productData.images.length > 0
772 ) {
773
774 productData.images.forEach(
775 (imageUrl, index) => {
776
777 query(
778 `INSERT INTO image
779 (
780 product_code,
781 image_url,
782 is_primary
783 )
784 VALUES
785 ($1, $2, $3)`,
786 [
787 productData.code,
788 imageUrl,
789 index === 0
790 ],
791 (err) => {
[69f2a41]792
793 if (err) {
[62b2964]794 console.error(
795 'Error inserting image:',
796 err
797 );
[69f2a41]798 }
799 }
800 );
[62b2964]801 }
802 );
803 }
804
805 /*
806 * Insert colors
807 */
808 if (
809 productData.colors &&
810 Array.isArray(productData.colors) &&
811 productData.colors.length > 0
812 ) {
813
814 productData.colors.forEach(color => {
815
816 query(
817 `INSERT INTO color
818 (product_code, name)
819 VALUES
820 ($1, $2)`,
821 [
822 productData.code,
823 color
824 ],
825 (err) => {
[69f2a41]826
[62b2964]827 if (err) {
828 console.error(
829 'Error inserting color:',
830 err
831 );
832 }
833 }
834 );
835 });
[69f2a41]836 }
[62b2964]837
838 callback(null, returnedId);
[69f2a41]839 }
840 );
841 });
842}
843
[62b2964]844
845function updateProduct(
846 personalId,
847 productData,
848 callback
849) {
850
[69f2a41]851 const updates = [];
852 const params = [];
853
854 if (productData.description !== undefined) {
[62b2964]855 updates.push(`description = $${params.length + 1}`);
[69f2a41]856 params.push(productData.description);
857 }
[591278c]858
[69f2a41]859 if (productData.price !== undefined) {
[62b2964]860 updates.push(`price = $${params.length + 1}`);
[69f2a41]861 params.push(productData.price);
862 }
[591278c]863
[69f2a41]864 if (productData.availability !== undefined) {
[62b2964]865 updates.push(`availability = $${params.length + 1}`);
[69f2a41]866 params.push(productData.availability);
867 }
[591278c]868
[69f2a41]869 if (productData.weight !== undefined) {
[62b2964]870 updates.push(`weight = $${params.length + 1}`);
[69f2a41]871 params.push(productData.weight);
872 }
[591278c]873
[69f2a41]874 if (productData.dimensions !== undefined) {
[62b2964]875 updates.push(`dimensions = $${params.length + 1}`);
[69f2a41]876 params.push(productData.dimensions);
877 }
[591278c]878
[69f2a41]879 if (productData.production_time !== undefined) {
[62b2964]880 updates.push(`production_time = $${params.length + 1}`);
[69f2a41]881 params.push(productData.production_time);
882 }
[591278c]883
[69f2a41]884 if (productData.category_id !== undefined) {
[62b2964]885 updates.push(`category_id = $${params.length + 1}`);
[69f2a41]886 params.push(productData.category_id);
887 }
888
889 if (updates.length === 0) {
890 callback(null, 0);
891 return;
892 }
893
894 params.push(productData.code);
895
[62b2964]896 const codeParameter = params.length;
897
898 query(
899 `UPDATE product
900 SET ${updates.join(', ')}
901 WHERE code = $${codeParameter}`,
[69f2a41]902 params,
[62b2964]903 (err, result) => {
904
[69f2a41]905 if (err) {
906 callback(err, null);
[62b2964]907 return;
908 }
909
910 const changesDesc =
911 `Product updated: ${updates.join(', ')}`;
912
913 /*
914 * Log product change
915 */
916 query(
917 `INSERT INTO "change"
918 (
919 date_and_time,
920 product_code,
921 changes
922 )
923 VALUES
924 (NOW(), $1, $2)`,
925 [
926 productData.code,
927 changesDesc
928 ],
929 (err) => {
930
931 if (err) {
932 console.error(
933 'Error logging product update:',
934 err
[69f2a41]935 );
936 }
[62b2964]937 }
938 );
[69f2a41]939
[62b2964]940 /*
941 * Log who made the change
942 */
943 query(
944 `INSERT INTO makes_change
945 (
946 personal_id,
947 change_date_time,
948 product_code
949 )
950 VALUES
951 ($1, NOW(), $2)`,
952 [
953 personalId,
954 productData.code
955 ],
956 (err) => {
957
958 if (err) {
959 console.error(
960 'Error logging change maker:',
961 err
962 );
963 }
[69f2a41]964 }
[62b2964]965 );
[69f2a41]966
[62b2964]967 /*
968 * Images
969 */
970 if (
971 productData.images &&
972 Array.isArray(productData.images)
973 ) {
974
975 query(
976 `DELETE FROM image
977 WHERE product_code = $1`,
978 [productData.code],
979 (err) => {
980
981 if (err) {
982 console.error(
983 'Error deleting old images:',
984 err
985 );
986 return;
[69f2a41]987 }
988
[62b2964]989 productData.images.forEach(
990 (image, index) => {
991
992 query(
993 `INSERT INTO image
994 (
995 product_code,
996 image_url,
997 is_primary
998 )
999 VALUES
1000 ($1, $2, $3)`,
1001 [
1002 productData.code,
1003 image,
1004 index === 0
1005 ],
1006 (err) => {
1007
1008 if (err) {
1009 console.error(
1010 'Error inserting image:',
1011 err
1012 );
1013 }
1014 }
1015 );
1016 }
1017 );
1018 }
1019 );
[69f2a41]1020 }
1021
[62b2964]1022 /*
1023 * Colors
1024 */
1025 if (
1026 productData.colors &&
1027 Array.isArray(productData.colors)
1028 ) {
[69f2a41]1029
[62b2964]1030 query(
1031 `DELETE FROM color
1032 WHERE product_code = $1`,
1033 [productData.code],
1034 (err) => {
[69f2a41]1035
1036 if (err) {
[62b2964]1037 console.error(
1038 'Error deleting old colors:',
1039 err
1040 );
[69f2a41]1041 return;
1042 }
1043
[62b2964]1044 productData.colors.forEach(color => {
1045
1046 query(
1047 `INSERT INTO color
1048 (product_code, name)
1049 VALUES
1050 ($1, $2)`,
1051 [
1052 productData.code,
1053 color
1054 ],
1055 (err) => {
1056
1057 if (err) {
1058 console.error(
1059 'Error inserting color:',
1060 err
1061 );
1062 }
[69f2a41]1063 }
[62b2964]1064 );
1065 });
[69f2a41]1066 }
1067 );
1068 }
[62b2964]1069
1070 callback(null, result.rowCount);
1071 }
1072 );
1073}
1074
1075
1076function deleteProduct(
1077 productCode,
1078 storeId,
1079 personalId,
1080 callback
1081) {
1082
1083 pool.connect()
1084 .then(client => {
1085
1086 return client.query('BEGIN')
1087 .then(() => {
1088
1089 return client.query(
1090 `INSERT INTO "change"
1091 (
1092 date_and_time,
1093 product_code,
1094 changes
1095 )
1096 VALUES
1097 (NOW(), $1, $2)`,
1098 [
1099 productCode,
1100 'Product deleted'
1101 ]
1102 );
1103 })
1104 .then(() => {
1105
1106 return client.query(
1107 `INSERT INTO makes_change
1108 (
1109 personal_id,
1110 change_date_time,
1111 product_code
1112 )
1113 VALUES
1114 ($1, NOW(), $2)`,
1115 [
1116 personalId,
1117 productCode
1118 ]
1119 );
1120 })
1121 .then(() => {
1122
1123 return client.query(
1124 `DELETE FROM product
1125 WHERE code = $1
1126 AND store_id = $2`,
1127 [
1128 productCode,
1129 storeId
1130 ]
1131 );
1132 })
1133 .then(result => {
1134
1135 return client.query('COMMIT')
1136 .then(() => {
1137
1138 client.release();
1139
1140 callback(
1141 null,
1142 result.rowCount
1143 );
1144 });
1145 })
1146 .catch(err => {
1147
1148 return client.query('ROLLBACK')
1149 .catch(() => {})
1150 .then(() => {
1151
1152 client.release();
1153 callback(err);
1154 });
1155 });
1156 })
1157 .catch(err => {
1158 callback(err);
1159 });
[69f2a41]1160}
1161
[62b2964]1162
1163/*
1164 * ============================================================
1165 * CATEGORY FUNCTIONS
1166 * ============================================================
1167 */
1168
[69f2a41]1169function getCategories(callback) {
[62b2964]1170
1171 query(
1172 `SELECT *
1173 FROM category
1174 ORDER BY name`,
1175 [],
1176 (err, result) => {
1177
1178 callback(
1179 err,
1180 result ? result.rows : []
1181 );
1182 }
1183 );
[69f2a41]1184}
1185
[62b2964]1186
[69f2a41]1187function getCategoriesWithParents(callback) {
[62b2964]1188
1189 query(
1190 `SELECT
1191 c1.*,
1192 c2.name AS parent_name
1193 FROM category c1
1194 LEFT JOIN category c2
1195 ON c1.parent_category_id =
1196 c2.category_id
1197 ORDER BY c1.name`,
[69f2a41]1198 [],
[62b2964]1199 (err, result) => {
1200
1201 callback(
1202 err,
1203 result ? result.rows : []
1204 );
[69f2a41]1205 }
1206 );
1207}
1208
[62b2964]1209
[69f2a41]1210function createCategory(categoryData, callback) {
[62b2964]1211
1212 query(
1213 `INSERT INTO category
1214 (
1215 name,
1216 description,
1217 parent_category_id
1218 )
1219 VALUES
1220 ($1, $2, $3)
1221 RETURNING category_id`,
1222 [
1223 categoryData.name,
1224 categoryData.description || null,
1225 categoryData.parent_id || null
1226 ],
1227 (err, result) => {
1228
[69f2a41]1229 if (err) {
1230 callback(err, null);
1231 } else {
[62b2964]1232
[69f2a41]1233 callback(null, {
[62b2964]1234 id: result.rows[0].category_id,
[69f2a41]1235 name: categoryData.name,
1236 parent_id: categoryData.parent_id,
1237 description: categoryData.description
1238 });
1239 }
1240 }
1241 );
1242}
1243
[62b2964]1244
1245/*
1246 * ============================================================
1247 * STORE FUNCTIONS
1248 * ============================================================
1249 */
1250
[69f2a41]1251function getStores(callback) {
[62b2964]1252
1253 query(
1254 `SELECT *
1255 FROM store
1256 ORDER BY name`,
1257 [],
1258 (err, result) => {
1259
1260 callback(
1261 err,
1262 result ? result.rows : []
1263 );
1264 }
1265 );
[69f2a41]1266}
1267
[62b2964]1268
[69f2a41]1269function getStoreProducts(storeId, callback) {
[62b2964]1270
1271 query(
1272 `SELECT
1273 p.*,
1274 c.name AS category_name
1275 FROM product p
1276 JOIN category c
1277 ON p.category_id = c.category_id
1278 WHERE p.store_id = $1
1279 ORDER BY p.code`,
[69f2a41]1280 [storeId],
[62b2964]1281 (err, result) => {
1282
1283 callback(
1284 err,
1285 result ? result.rows : []
1286 );
[69f2a41]1287 }
1288 );
1289}
1290
[62b2964]1291
[69f2a41]1292function getStoreOrders(storeId, callback) {
[62b2964]1293
1294 query(
1295 `SELECT
1296 o.*,
1297 c.first_name,
1298 c.last_name
1299 FROM "order" o
1300 JOIN client c
1301 ON o.client_id = c.client_id
1302 WHERE o.store_id = $1
1303 ORDER BY o.order_date DESC`,
[69f2a41]1304 [storeId],
[62b2964]1305 (err, result) => {
1306
[69f2a41]1307 if (err) {
1308 callback(err, null);
[62b2964]1309 return;
1310 }
[69f2a41]1311
[62b2964]1312 const orders = result.rows || [];
[69f2a41]1313
[62b2964]1314 if (orders.length === 0) {
1315 callback(null, []);
1316 return;
[69f2a41]1317 }
[62b2964]1318
1319 let completed = 0;
1320
1321 orders.forEach(order => {
1322
1323 query(
1324 `SELECT
1325 oi.*,
1326 p.description
1327 FROM order_items oi
1328 JOIN product p
1329 ON oi.product_code = p.code
1330 WHERE oi.order_num = $1`,
1331 [order.order_num],
1332 (err, result) => {
1333
1334 if (!err) {
1335 order.items =
1336 result.rows || [];
1337 } else {
1338 order.items = [];
1339 }
1340
1341 completed++;
1342
1343 if (completed === orders.length) {
1344 callback(null, orders);
1345 }
1346 }
1347 );
1348 });
[69f2a41]1349 }
1350 );
1351}
1352
[62b2964]1353
[69f2a41]1354function getStoreEmployees(storeId, callback) {
[62b2964]1355
1356 query(
1357 `SELECT
1358 p.*,
1359 e.date_of_hire,
1360 perm.type AS permission_type,
1361 perm.authorisation
1362 FROM personal p
1363 JOIN works_in_store w
1364 ON p.id = w.personal_id
1365 LEFT JOIN employees e
1366 ON p.id = e.employee_id
1367 LEFT JOIN permissions perm
1368 ON p.id = perm.personal_id
1369 WHERE w.store_id = $1`,
[69f2a41]1370 [storeId],
[62b2964]1371 (err, result) => {
1372
1373 callback(
1374 err,
1375 result ? result.rows : []
1376 );
[69f2a41]1377 }
1378 );
1379}
1380
[62b2964]1381
[69f2a41]1382function getStoreReports(storeId, callback) {
[62b2964]1383
1384 query(
[6c7cfa6]1385 `SELECT
1386 r.date,
1387 r.store_id,
1388 r.overall_profit,
1389 r.sales_trend,
1390 r.marketing_growth,
1391 r.owner_signature
1392 FROM report r
1393 WHERE r.store_id = $1
1394 ORDER BY r.date DESC`,
[69f2a41]1395 [storeId],
[62b2964]1396 (err, result) => {
1397 callback(
1398 err,
1399 result ? result.rows : []
1400 );
[69f2a41]1401 }
1402 );
1403}
1404
[62b2964]1405
[69f2a41]1406function getStoreStats(storeId, callback) {
[62b2964]1407
[69f2a41]1408 const stats = {};
1409
[62b2964]1410 query(
[6c7cfa6]1411 `SELECT COUNT(DISTINCT se.product_code) AS total_products
1412 FROM sells se
1413 WHERE se.store_id = $1`,
[69f2a41]1414 [storeId],
[62b2964]1415 (err, result) => {
1416
1417 if (err) {
1418 callback(err, null);
1419 return;
1420 }
[69f2a41]1421
[6c7cfa6]1422 stats.total_products = Number(
1423 result.rows[0]?.total_products || 0
1424 );
[62b2964]1425
1426 query(
[6c7cfa6]1427 `SELECT COUNT(DISTINCT o.order_num) AS total_orders
1428 FROM sells se
1429 JOIN includes i
1430 ON i.product_code = se.product_code
1431 JOIN "order" o
1432 ON o.order_num = i.order_num
1433 WHERE se.store_id = $1`,
[69f2a41]1434 [storeId],
[62b2964]1435 (err, result) => {
1436
1437 if (err) {
1438 callback(err, null);
1439 return;
1440 }
1441
[6c7cfa6]1442 stats.total_orders = Number(
1443 result.rows[0]?.total_orders || 0
1444 );
[62b2964]1445
1446 query(
1447 `SELECT
1448 COALESCE(
1449 SUM(
[6c7cfa6]1450 p.price * i.quantity
1451 * (1 - COALESCE(o.discount, 0) / 100.0)
[62b2964]1452 ),
1453 0
1454 ) AS total_revenue
[6c7cfa6]1455 FROM sells se
1456 JOIN product p
1457 ON p.code = se.product_code
1458 JOIN includes i
1459 ON i.product_code = se.product_code
[62b2964]1460 JOIN "order" o
[6c7cfa6]1461 ON o.order_num = i.order_num
1462 WHERE se.store_id = $1`,
[69f2a41]1463 [storeId],
[62b2964]1464 (err, result) => {
1465
1466 if (err) {
1467 callback(err, null);
1468 return;
1469 }
1470
[6c7cfa6]1471 stats.total_revenue = Number(
1472 result.rows[0]?.total_revenue || 0
1473 );
[62b2964]1474
1475 query(
[6c7cfa6]1476 `WITH store_reviews AS (
1477 SELECT DISTINCT
1478 r.order_num,
1479 r.rating
1480 FROM review r
1481 JOIN includes i
1482 ON i.order_num = r.order_num
1483 JOIN sells se
1484 ON se.product_code = i.product_code
1485 WHERE se.store_id = $1
1486 )
1487 SELECT COALESCE(AVG(rating), 0) AS avg_rating
1488 FROM store_reviews`,
[69f2a41]1489 [storeId],
[62b2964]1490 (err, result) => {
1491
1492 if (err) {
1493 callback(err, null);
1494 return;
1495 }
1496
[6c7cfa6]1497 stats.avg_rating = Number(
1498 result.rows[0]?.avg_rating || 0
[62b2964]1499 );
[6c7cfa6]1500
1501 callback(null, stats);
[69f2a41]1502 }
1503 );
1504 }
1505 );
1506 }
1507 );
1508 }
1509 );
1510}
1511
[62b2964]1512
1513/*
1514 * ============================================================
1515 * ORDER FUNCTIONS
1516 * ============================================================
1517 */
1518
[69f2a41]1519function createOrderNew(orderData, callback) {
1520
[62b2964]1521 pool.connect()
1522 .then(client => {
1523
1524 return client.query('BEGIN')
1525 .then(() => {
1526
1527 return client.query(
1528 `INSERT INTO "order"
1529 (
1530 order_num,
1531 client_id,
1532 order_date,
1533 quantity,
1534 payment_method,
1535 discount,
1536 delivery_address,
1537 store_id
1538 )
1539 VALUES
1540 (
1541 $1,
1542 $2,
1543 NOW(),
1544 $3,
1545 $4,
1546 $5,
1547 $6,
1548 $7
1549 )`,
1550 [
1551 orderData.order_num,
1552 orderData.client_id,
1553 orderData.quantity,
1554 orderData.payment_method,
1555 orderData.discount,
1556 orderData.delivery_address,
1557 orderData.store_id
1558 ]
1559 );
1560 })
1561 .then(() => {
[69f2a41]1562
[62b2964]1563 const items =
1564 orderData.items || [];
[69f2a41]1565
[62b2964]1566 if (items.length === 0) {
1567 return client.query('COMMIT')
1568 .then(() => {
[69f2a41]1569
[62b2964]1570 client.release();
[69f2a41]1571
[62b2964]1572 callback(
1573 null,
1574 orderData.order_num
1575 );
1576 });
1577 }
1578
1579 return Promise.all(
1580 items.map(item => {
1581
1582 return client.query(
1583 `INSERT INTO order_items
1584 (
1585 order_num,
1586 product_code,
1587 quantity,
1588 price
1589 )
1590 VALUES
1591 ($1, $2, $3, $4)`,
1592 [
1593 orderData.order_num,
1594 item.product_code,
1595 item.quantity,
1596 item.price
1597 ]
1598 );
1599 })
1600 )
1601 .then(() => client.query('COMMIT'))
1602 .then(() => {
1603
1604 client.release();
1605
1606 callback(
1607 null,
1608 orderData.order_num
1609 );
1610 });
1611 })
1612 .catch(err => {
1613
1614 return client.query('ROLLBACK')
1615 .catch(() => {})
1616 .then(() => {
1617
1618 client.release();
1619 callback(err, null);
1620 });
[69f2a41]1621 });
[62b2964]1622 })
1623 .catch(err => {
1624 callback(err, null);
1625 });
[69f2a41]1626}
1627
[62b2964]1628
[69f2a41]1629function getOrdersByClient(clientId, callback) {
[62b2964]1630
1631 query(
1632 `SELECT
1633 o.*,
1634 s.name AS store_name
1635 FROM "order" o
1636 JOIN store s
1637 ON o.store_id = s.store_id
1638 WHERE o.client_id = $1
1639 ORDER BY o.order_date DESC`,
[69f2a41]1640 [clientId],
[62b2964]1641 (err, result) => {
1642
[69f2a41]1643 if (err) {
1644 callback(err, null);
[62b2964]1645 return;
1646 }
[69f2a41]1647
[62b2964]1648 const orders = result.rows || [];
[69f2a41]1649
[62b2964]1650 if (orders.length === 0) {
1651 callback(null, []);
1652 return;
[69f2a41]1653 }
[62b2964]1654
1655 let completed = 0;
1656
1657 orders.forEach(order => {
1658
1659 query(
1660 `SELECT
1661 oi.*,
1662 p.description
1663 FROM order_items oi
1664 JOIN product p
1665 ON oi.product_code = p.code
1666 WHERE oi.order_num = $1`,
1667 [order.order_num],
1668 (err, result) => {
1669
1670 if (!err) {
1671 order.items =
1672 result.rows || [];
1673 } else {
1674 order.items = [];
1675 }
1676
1677 completed++;
1678
1679 if (completed === orders.length) {
1680 callback(null, orders);
1681 }
1682 }
1683 );
1684 });
[69f2a41]1685 }
1686 );
1687}
1688
[62b2964]1689
[69f2a41]1690function getAllOrders(callback) {
[62b2964]1691
1692 query(
1693 `SELECT
1694 o.*,
1695 c.first_name,
1696 c.last_name,
1697 s.name AS store_name
1698 FROM "order" o
1699 JOIN client c
1700 ON o.client_id = c.client_id
1701 JOIN store s
1702 ON o.store_id = s.store_id
1703 ORDER BY o.order_date DESC`,
[69f2a41]1704 [],
[62b2964]1705 (err, result) => {
1706
1707 callback(
1708 err,
1709 result ? result.rows : []
1710 );
[69f2a41]1711 }
1712 );
1713}
1714
[62b2964]1715
1716/*
1717 * ============================================================
1718 * REVIEW FUNCTIONS
1719 * ============================================================
1720 */
1721
[69f2a41]1722function createReviewNew(reviewData, callback) {
[62b2964]1723
1724 const reviewId =
1725 'REV' +
1726 Date.now().toString().slice(-8);
1727
1728 query(
1729 `INSERT INTO review
1730 (
1731 review_id,
1732 client_id,
1733 product_code,
1734 rating,
1735 comment,
1736 review_date
1737 )
1738 VALUES
1739 ($1, $2, $3, $4, $5, NOW())
1740 RETURNING review_id`,
[69f2a41]1741 [
[62b2964]1742 reviewId,
[69f2a41]1743 reviewData.client_id,
1744 reviewData.product_code,
1745 reviewData.rating,
1746 reviewData.comment || ''
1747 ],
[62b2964]1748 (err, result) => {
1749
[69f2a41]1750 if (err) {
1751 callback(err, null);
1752 } else {
[62b2964]1753 callback(
1754 null,
1755 result.rows[0].review_id
1756 );
[69f2a41]1757 }
1758 }
1759 );
1760}
1761
[62b2964]1762
1763/*
1764 * ============================================================
1765 * REQUEST FUNCTIONS
1766 * ============================================================
1767 */
1768
[69f2a41]1769function createRequest(requestData, callback) {
[62b2964]1770
1771 query(
1772 `INSERT INTO request
1773 (
1774 request_num,
1775 date_and_time,
1776 problem,
1777 client_id,
1778 store_id
1779 )
1780 VALUES
1781 ($1, $2, $3, $4, $5)`,
[69f2a41]1782 [
1783 requestData.request_num,
1784 requestData.date_and_time,
1785 requestData.problem,
1786 requestData.client_id,
1787 requestData.store_id
1788 ],
[62b2964]1789 (err) => {
1790
[69f2a41]1791 if (err) {
1792 callback(err, null);
1793 } else {
[62b2964]1794 callback(
1795 null,
1796 requestData.request_num
1797 );
[69f2a41]1798 }
1799 }
1800 );
1801}
1802
[62b2964]1803
1804/*
1805 * ============================================================
1806 * REFUND FUNCTIONS
1807 * ============================================================
1808 */
1809
[69f2a41]1810function createRefund(refundData, callback) {
[62b2964]1811
1812 query(
1813 `INSERT INTO refund
1814 (
1815 refund_id,
1816 order_num,
1817 amount,
1818 reason,
1819 request_date
1820 )
1821 VALUES
1822 ($1, $2, $3, $4, NOW())`,
[69f2a41]1823 [
1824 refundData.refund_id,
1825 refundData.order_num,
1826 refundData.amount,
1827 refundData.reason
1828 ],
[62b2964]1829 (err) => {
1830
[69f2a41]1831 if (err) {
1832 callback(err, null);
1833 } else {
[62b2964]1834 callback(
1835 null,
1836 refundData.refund_id
1837 );
[69f2a41]1838 }
1839 }
1840 );
1841}
1842
[62b2964]1843
1844/*
1845 * ============================================================
1846 * EMPLOYEE TASKS
1847 * ============================================================
1848 */
1849
1850function getEmployeeTasks(
1851 personalId,
1852 storeId,
1853 callback
1854) {
1855
[69f2a41]1856 const tasks = {
1857 pending_orders: [],
1858 pending_requests: [],
1859 pending_refunds: []
1860 };
1861
[62b2964]1862 /*
1863 * Pending orders
1864 */
1865 query(
1866 `SELECT
1867 o.*,
1868 c.first_name,
1869 c.last_name
1870 FROM "order" o
1871 JOIN client c
1872 ON o.client_id = c.client_id
1873 WHERE o.store_id = $1
1874 AND o.status = $2
1875 ORDER BY o.order_date ASC`,
1876 [
1877 storeId,
1878 'pending'
1879 ],
1880 (err, result) => {
1881
[69f2a41]1882 if (!err) {
[62b2964]1883 tasks.pending_orders =
1884 result.rows || [];
[69f2a41]1885 }
1886
[62b2964]1887 /*
1888 * Pending requests
1889 */
1890 query(
1891 `SELECT
1892 r.*,
1893 c.first_name,
1894 c.last_name
1895 FROM request r
1896 JOIN client c
1897 ON r.client_id = c.client_id
1898 WHERE r.store_id = $1
1899 AND r.status = $2
1900 ORDER BY r.date_and_time ASC`,
1901 [
1902 storeId,
1903 'pending'
1904 ],
1905 (err, result) => {
1906
[69f2a41]1907 if (!err) {
[62b2964]1908 tasks.pending_requests =
1909 result.rows || [];
[69f2a41]1910 }
1911
[62b2964]1912 /*
1913 * Pending refunds
1914 */
1915 query(
1916 `SELECT
1917 rf.*,
1918 o.client_id,
1919 c.first_name,
1920 c.last_name
1921 FROM refund rf
1922 JOIN "order" o
1923 ON rf.order_num =
1924 o.order_num
1925 JOIN client c
1926 ON o.client_id =
1927 c.client_id
1928 WHERE o.store_id = $1
1929 AND rf.status = $2
1930 ORDER BY rf.request_date ASC`,
1931 [
1932 storeId,
1933 'pending'
1934 ],
1935 (err, result) => {
1936
[69f2a41]1937 if (!err) {
[62b2964]1938 tasks.pending_refunds =
1939 result.rows || [];
[69f2a41]1940 }
[62b2964]1941
1942 callback(
1943 null,
1944 tasks
1945 );
[69f2a41]1946 }
1947 );
1948 }
1949 );
1950 }
1951 );
1952}
1953
[62b2964]1954
1955/*
1956 * ============================================================
1957 * CLIENT STATISTICS
1958 * ============================================================
1959 */
1960
[69f2a41]1961function getClientStats(clientId, callback) {
[62b2964]1962
[69f2a41]1963 const stats = {};
1964
[62b2964]1965 query(
1966 `SELECT COUNT(*) AS total_orders
1967 FROM "order"
1968 WHERE client_id = $1`,
[69f2a41]1969 [clientId],
[62b2964]1970 (err, result) => {
1971
1972 if (err) {
1973 callback(err, null);
1974 return;
1975 }
1976
1977 stats.total_orders =
1978 Number(
1979 result.rows[0]
1980 ? result.rows[0].total_orders
1981 : 0
1982 );
1983
1984 query(
1985 `SELECT
1986 COALESCE(
1987 SUM(
1988 oi.price * oi.quantity
1989 ),
1990 0
1991 ) AS total_spent
1992 FROM order_items oi
1993 JOIN "order" o
1994 ON oi.order_num = o.order_num
1995 WHERE o.client_id = $1`,
[69f2a41]1996 [clientId],
[62b2964]1997 (err, result) => {
1998
1999 if (err) {
2000 callback(err, null);
2001 return;
2002 }
2003
2004 stats.total_spent =
2005 Number(
2006 result.rows[0]
2007 ? result.rows[0].total_spent
2008 : 0
2009 );
2010
2011 query(
2012 `SELECT COUNT(*) AS pending_orders
2013 FROM "order"
2014 WHERE client_id = $1
2015 AND status = $2`,
2016 [
2017 clientId,
2018 'pending'
2019 ],
2020 (err, result) => {
2021
2022 if (err) {
2023 callback(err, null);
2024 return;
2025 }
2026
2027 stats.pending_orders =
2028 Number(
2029 result.rows[0]
2030 ? result.rows[0]
2031 .pending_orders
2032 : 0
2033 );
2034
2035 query(
2036 `SELECT
2037 COUNT(*) AS delivered_orders
2038 FROM "order"
2039 WHERE client_id = $1
2040 AND status = $2`,
2041 [
2042 clientId,
2043 'delivered'
2044 ],
2045 (err, result) => {
2046
2047 if (err) {
2048 callback(err, null);
2049 return;
2050 }
2051
2052 stats.delivered_orders =
2053 Number(
2054 result.rows[0]
2055 ? result.rows[0]
2056 .delivered_orders
2057 : 0
2058 );
2059
2060 callback(
2061 null,
2062 stats
2063 );
[69f2a41]2064 }
2065 );
2066 }
2067 );
2068 }
2069 );
2070 }
2071 );
2072}
2073
[62b2964]2074
2075/*
2076 * ============================================================
2077 * ADMIN USER FUNCTIONS
2078 * ============================================================
2079 */
2080
[69f2a41]2081function getAllUsers(callback) {
2082
[62b2964]2083 query(
2084 `SELECT
2085 client_id AS id,
2086 first_name,
2087 last_name,
2088 email,
2089 'client' AS user_type,
2090 NULL AS username,
2091 5 AS role_priority
[4dff800]2092 FROM client
2093 ORDER BY client_id`,
[69f2a41]2094 [],
[62b2964]2095 (err, result) => {
2096
2097 if (err) {
2098 callback(err, null);
2099 return;
[69f2a41]2100 }
2101
[62b2964]2102 const usersMap = new Map();
2103
2104 result.rows.forEach(row => {
2105 usersMap.set(row.id, row);
2106 });
2107
2108 /*
2109 * Personal users
2110 */
2111 query(
2112 `SELECT
2113 p.id,
2114 p.first_name,
2115 p.last_name,
2116 p.email,
2117 CASE
2118 WHEN b.boss_id IS NOT NULL
2119 THEN 'store_owner'
2120 ELSE 'store_employee'
2121 END AS user_type,
2122 NULL AS username,
2123 CASE
2124 WHEN b.boss_id IS NOT NULL
2125 THEN 2
2126 ELSE 4
2127 END AS role_priority
[4dff800]2128 FROM personal p
[62b2964]2129 LEFT JOIN boss b
2130 ON p.id = b.boss_id
2131 LEFT JOIN employees e
2132 ON p.id = e.employee_id
2133 WHERE b.boss_id IS NOT NULL
2134 OR e.employee_id IS NOT NULL
[4dff800]2135 ORDER BY p.id`,
[69f2a41]2136 [],
[62b2964]2137 (err, result) => {
2138
2139 if (err) {
2140 callback(err, null);
2141 return;
[69f2a41]2142 }
2143
[62b2964]2144 result.rows.forEach(row => {
2145
2146 const existing =
2147 usersMap.get(row.id);
2148
2149 if (
2150 !existing ||
2151 (
2152 existing.role_priority &&
2153 row.role_priority <
2154 existing.role_priority
2155 )
2156 ) {
2157 usersMap.set(
2158 row.id,
2159 row
2160 );
2161 }
2162 });
2163
2164 /*
2165 * System users
2166 */
2167 query(
2168 `SELECT
2169 id,
2170 username,
2171 email,
2172 user_type,
2173 CASE
2174 WHEN user_type = 'admin'
2175 THEN 1
2176 ELSE 3
2177 END AS role_priority
[4dff800]2178 FROM users
2179 ORDER BY id`,
[69f2a41]2180 [],
[62b2964]2181 (err, result) => {
2182
2183 if (err) {
2184 callback(err, null);
2185 return;
[69f2a41]2186 }
[4dff800]2187
[62b2964]2188 result.rows.forEach(row => {
2189
2190 const existing =
2191 usersMap.get(row.id);
2192
2193 if (
2194 !existing ||
2195 (
2196 existing.role_priority &&
2197 row.role_priority <
2198 existing.role_priority
2199 )
2200 ) {
2201
2202 const userData = {
2203 id: row.id,
2204 username: row.username,
2205 email: row.email,
2206 user_type: row.user_type,
2207 role_priority: row.role_priority
2208 };
2209
2210 if (
2211 row.user_type ===
2212 'admin'
2213 ) {
2214 userData.first_name =
2215 'Admin';
2216
2217 userData.last_name =
2218 'User';
2219 }
2220
2221 usersMap.set(
2222 row.id,
2223 userData
2224 );
2225 }
[4dff800]2226 });
2227
[62b2964]2228 const users =
2229 Array.from(
2230 usersMap.values()
2231 ).map(user => {
2232
2233 const {
2234 role_priority,
2235 ...userWithoutPriority
2236 } = user;
2237
2238 return userWithoutPriority;
2239 });
2240
2241 callback(
2242 null,
2243 users
2244 );
[69f2a41]2245 }
2246 );
2247 }
2248 );
2249 }
2250 );
2251}
2252
[62b2964]2253
2254/*
2255 * ============================================================
2256 * AUDIT LOG
2257 * ============================================================
2258 */
2259
2260function logAudit(
2261 userId,
2262 action,
2263 resourceType,
2264 resourceId,
2265 details,
2266 ipAddress
2267) {
2268
2269 query(
2270 `INSERT INTO audit_log
2271 (
2272 user_id,
2273 action,
2274 resource_type,
2275 resource_id,
2276 details,
2277 ip_address
2278 )
2279 VALUES
2280 ($1, $2, $3, $4, $5, $6)`,
2281 [
2282 userId,
2283 action,
2284 resourceType,
2285 resourceId,
2286 details,
2287 ipAddress
2288 ],
[69f2a41]2289 (err) => {
[62b2964]2290
[69f2a41]2291 if (err) {
[62b2964]2292 console.error(
2293 'Error logging audit:',
2294 err
2295 );
[69f2a41]2296 }
2297 }
2298 );
2299}
2300
[62b2964]2301
[6c7cfa6]2302
2303
2304/*
2305 * ============================================================
2306 * ADVANCED REPORTS
2307 * ============================================================
2308 */
2309
2310
2311const REPORT_FUNCTIONS_SQL = String.raw`
2312-- ============================================================
2313-- HANDCRAFT MARKETPLACE REPORT FUNCTIONS
2314-- PostgreSQL / exact project schema
2315-- ============================================================
2316
2317CREATE OR REPLACE FUNCTION get_orders_by_total()
2318RETURNS TABLE (
2319 order_num VARCHAR(11),
2320 client_id INTEGER,
2321 client_name TEXT,
2322 order_quantity BIGINT,
2323 order_status VARCHAR(20),
2324 payment_method VARCHAR(250),
2325 discount NUMERIC,
2326 order_total NUMERIC
2327)
2328LANGUAGE sql
2329AS $$
2330 SELECT
2331 o.order_num,
2332 o.client_id,
2333 CONCAT_WS(' ', c.first_name, c.last_name) AS client_name,
2334 COALESCE(SUM(i.quantity), 0)::BIGINT AS order_quantity,
2335 o.status,
2336 o.payment_method,
2337 COALESCE(o.discount, 0)::NUMERIC AS discount,
2338 ROUND(
2339 COALESCE(SUM(p.price * i.quantity), 0)
2340 * (1 - COALESCE(o.discount, 0) / 100.0),
2341 2
2342 ) AS order_total
2343 FROM "order" o
2344 LEFT JOIN client c ON c.client_id = o.client_id
2345 LEFT JOIN includes i ON i.order_num = o.order_num
2346 LEFT JOIN product p ON p.code = i.product_code
2347 GROUP BY
2348 o.order_num, o.client_id, c.first_name, c.last_name,
2349 o.status, o.payment_method, o.discount
2350 ORDER BY order_total DESC, o.order_num;
2351$$;
2352
2353CREATE OR REPLACE FUNCTION get_products_by_total_sales()
2354RETURNS TABLE (
2355 product_code VARCHAR(8),
2356 product_description VARCHAR(500),
2357 product_price NUMERIC,
2358 number_of_orders BIGINT,
2359 total_quantity_sold BIGINT,
2360 total_revenue NUMERIC
2361)
2362LANGUAGE sql
2363AS $$
2364 SELECT
2365 p.code,
2366 p.description,
2367 p.price::NUMERIC,
2368 COUNT(DISTINCT i.order_num) AS number_of_orders,
2369 COALESCE(SUM(i.quantity), 0)::BIGINT AS total_quantity_sold,
2370 ROUND(
2371 COALESCE(
2372 SUM(
2373 p.price * i.quantity
2374 * (1 - COALESCE(o.discount, 0) / 100.0)
2375 ),
2376 0
2377 ),
2378 2
2379 ) AS total_revenue
2380 FROM product p
2381 LEFT JOIN includes i ON i.product_code = p.code
2382 LEFT JOIN "order" o ON o.order_num = i.order_num
2383 GROUP BY p.code, p.description, p.price
2384 ORDER BY number_of_orders DESC, total_quantity_sold DESC, total_revenue DESC;
2385$$;
2386
2387CREATE OR REPLACE FUNCTION get_low_stock_high_demand_products(
2388 p_stock_threshold INTEGER,
2389 p_demand_threshold INTEGER
2390)
2391RETURNS TABLE (
2392 product_code VARCHAR(8),
2393 product_description VARCHAR(500),
2394 current_stock INTEGER,
2395 number_of_orders BIGINT,
2396 total_quantity_sold BIGINT
2397)
2398LANGUAGE sql
2399AS $$
2400 SELECT
2401 p.code,
2402 p.description,
2403 p.availability,
2404 COUNT(DISTINCT i.order_num),
2405 COALESCE(SUM(i.quantity), 0)::BIGINT
2406 FROM product p
2407 JOIN includes i ON i.product_code = p.code
2408 GROUP BY p.code, p.description, p.availability
2409 HAVING
2410 p.availability < p_stock_threshold
2411 AND COUNT(DISTINCT i.order_num) >= p_demand_threshold
2412 ORDER BY number_of_orders DESC, total_quantity_sold DESC, current_stock ASC;
2413$$;
2414
2415CREATE OR REPLACE FUNCTION get_products_monthly_sales()
2416RETURNS TABLE (
2417 product_code VARCHAR(8),
2418 product_description VARCHAR(500),
2419 year INTEGER,
2420 month INTEGER,
2421 number_of_orders BIGINT,
2422 total_quantity_sold BIGINT,
2423 total_revenue NUMERIC
2424)
2425LANGUAGE sql
2426AS $$
2427 SELECT
2428 p.code,
2429 p.description,
2430 EXTRACT(YEAR FROM o.last_date_mod)::INTEGER,
2431 EXTRACT(MONTH FROM o.last_date_mod)::INTEGER,
2432 COUNT(DISTINCT o.order_num),
2433 COALESCE(SUM(i.quantity), 0)::BIGINT,
2434 ROUND(
2435 COALESCE(
2436 SUM(
2437 p.price * i.quantity
2438 * (1 - COALESCE(o.discount, 0) / 100.0)
2439 ),
2440 0
2441 ),
2442 2
2443 )
2444 FROM product p
2445 JOIN includes i ON i.product_code = p.code
2446 JOIN "order" o ON o.order_num = i.order_num
2447 GROUP BY
2448 p.code, p.description,
2449 EXTRACT(YEAR FROM o.last_date_mod),
2450 EXTRACT(MONTH FROM o.last_date_mod)
2451 ORDER BY year DESC, month DESC, total_revenue DESC;
2452$$;
2453
2454CREATE OR REPLACE FUNCTION get_stores_by_last_calendar_year_revenue()
2455RETURNS TABLE (
2456 store_id VARCHAR(3),
2457 store_name VARCHAR(50),
2458 number_of_orders BIGINT,
2459 total_quantity_sold BIGINT,
2460 total_revenue NUMERIC
2461)
2462LANGUAGE sql
2463AS $$
2464 SELECT
2465 s.store_id,
2466 s.name,
2467 COUNT(DISTINCT o.order_num),
2468 COALESCE(SUM(i.quantity), 0)::BIGINT,
2469 ROUND(
2470 COALESCE(
2471 SUM(
2472 p.price * i.quantity
2473 * (1 - COALESCE(o.discount, 0) / 100.0)
2474 ),
2475 0
2476 ),
2477 2
2478 )
2479 FROM store s
2480 LEFT JOIN sells se ON se.store_id = s.store_id
2481 LEFT JOIN product p ON p.code = se.product_code
2482 LEFT JOIN includes i ON i.product_code = p.code
2483 LEFT JOIN "order" o
2484 ON o.order_num = i.order_num
2485 AND o.last_date_mod >= DATE_TRUNC('year', CURRENT_DATE) - INTERVAL '1 year'
2486 AND o.last_date_mod < DATE_TRUNC('year', CURRENT_DATE)
2487 GROUP BY s.store_id, s.name
2488 ORDER BY total_revenue DESC, s.store_id;
2489$$;
2490
2491CREATE OR REPLACE FUNCTION get_products_never_ordered()
2492RETURNS TABLE (
2493 product_code VARCHAR(8),
2494 product_description VARCHAR(500),
2495 product_price NUMERIC,
2496 current_stock INTEGER
2497)
2498LANGUAGE sql
2499AS $$
2500 SELECT p.code, p.description, p.price::NUMERIC, p.availability
2501 FROM product p
2502 WHERE NOT EXISTS (
2503 SELECT 1
2504 FROM includes i
2505 WHERE i.product_code = p.code
2506 )
2507 ORDER BY p.code;
2508$$;
2509
2510CREATE OR REPLACE FUNCTION get_products_by_number_of_orders()
2511RETURNS TABLE (
2512 product_code VARCHAR(8),
2513 product_description VARCHAR(500),
2514 product_price NUMERIC,
2515 number_of_orders BIGINT
2516)
2517LANGUAGE sql
2518AS $$
2519 SELECT
2520 p.code,
2521 p.description,
2522 p.price::NUMERIC,
2523 COUNT(DISTINCT i.order_num)
2524 FROM product p
2525 JOIN includes i ON i.product_code = p.code
2526 GROUP BY p.code, p.description, p.price
2527 ORDER BY number_of_orders DESC, p.code;
2528$$;
2529
2530CREATE OR REPLACE FUNCTION get_stores_by_average_review()
2531RETURNS TABLE (
2532 store_id VARCHAR(3),
2533 store_name VARCHAR(50),
2534 average_review NUMERIC,
2535 number_of_reviews BIGINT
2536)
2537LANGUAGE sql
2538AS $$
2539 WITH store_reviews AS (
2540 SELECT DISTINCT
2541 s.store_id,
2542 s.name AS store_name,
2543 r.order_num,
2544 r.rating
2545 FROM store s
2546 JOIN sells se ON se.store_id = s.store_id
2547 JOIN includes i ON i.product_code = se.product_code
2548 JOIN review r ON r.order_num = i.order_num
2549 )
2550 SELECT
2551 s.store_id,
2552 s.name,
2553 COALESCE(ROUND(AVG(sr.rating), 2), 0)::NUMERIC,
2554 COUNT(sr.order_num)
2555 FROM store s
2556 LEFT JOIN store_reviews sr ON sr.store_id = s.store_id
2557 GROUP BY s.store_id, s.name
2558 ORDER BY average_review DESC, s.store_id;
2559$$;
2560
2561CREATE OR REPLACE FUNCTION get_store_with_highest_revenue_growth()
2562RETURNS TABLE (
2563 store_id VARCHAR(3),
2564 store_name VARCHAR(50),
2565 previous_year_revenue NUMERIC,
2566 last_year_revenue NUMERIC,
2567 revenue_growth NUMERIC
2568)
2569LANGUAGE sql
2570AS $$
2571 WITH store_years AS (
2572 SELECT
2573 s.store_id,
2574 s.name AS store_name,
2575 EXTRACT(YEAR FROM o.last_date_mod)::INTEGER AS sales_year,
2576 SUM(
2577 p.price * i.quantity
2578 * (1 - COALESCE(o.discount, 0) / 100.0)
2579 ) AS revenue
2580 FROM store s
2581 JOIN sells se ON se.store_id = s.store_id
2582 JOIN includes i ON i.product_code = se.product_code
2583 JOIN product p ON p.code = i.product_code
2584 JOIN "order" o ON o.order_num = i.order_num
2585 WHERE o.last_date_mod >= DATE_TRUNC('year', CURRENT_DATE) - INTERVAL '2 years'
2586 AND o.last_date_mod < DATE_TRUNC('year', CURRENT_DATE)
2587 GROUP BY s.store_id, s.name, EXTRACT(YEAR FROM o.last_date_mod)
2588 ),
2589 comparison AS (
2590 SELECT
2591 s.store_id,
2592 s.name AS store_name,
2593 COALESCE(MAX(CASE
2594 WHEN sy.sales_year = EXTRACT(YEAR FROM CURRENT_DATE)::INTEGER - 2
2595 THEN sy.revenue ELSE 0 END), 0) AS previous_year_revenue,
2596 COALESCE(MAX(CASE
2597 WHEN sy.sales_year = EXTRACT(YEAR FROM CURRENT_DATE)::INTEGER - 1
2598 THEN sy.revenue ELSE 0 END), 0) AS last_year_revenue
2599 FROM store s
2600 LEFT JOIN store_years sy ON sy.store_id = s.store_id
2601 GROUP BY s.store_id, s.name
2602 )
2603 SELECT
2604 store_id,
2605 store_name,
2606 ROUND(previous_year_revenue, 2),
2607 ROUND(last_year_revenue, 2),
2608 ROUND(last_year_revenue - previous_year_revenue, 2)
2609 FROM comparison
2610 ORDER BY revenue_growth DESC, store_id
2611 LIMIT 1;
2612$$;
2613
2614CREATE OR REPLACE FUNCTION get_clients_by_number_of_orders()
2615RETURNS TABLE (
2616 client_id INTEGER,
2617 client_name TEXT,
2618 number_of_orders BIGINT
2619)
2620LANGUAGE sql
2621AS $$
2622 SELECT
2623 c.client_id,
2624 CONCAT_WS(' ', c.first_name, c.last_name),
2625 COUNT(o.order_num)
2626 FROM client c
2627 JOIN "order" o ON o.client_id = c.client_id
2628 GROUP BY c.client_id, c.first_name, c.last_name
2629 ORDER BY number_of_orders DESC, c.client_id;
2630$$;
2631
2632CREATE OR REPLACE FUNCTION get_approximate_orders_per_client()
2633RETURNS TABLE (
2634 total_clients BIGINT,
2635 total_orders BIGINT,
2636 approximate_orders_per_client NUMERIC
2637)
2638LANGUAGE sql
2639AS $$
2640 SELECT
2641 (SELECT COUNT(*) FROM client),
2642 (SELECT COUNT(*) FROM "order"),
2643 ROUND(
2644 (SELECT COUNT(*)::NUMERIC FROM "order")
2645 / NULLIF((SELECT COUNT(*) FROM client), 0),
2646 2
2647 );
2648$$;
2649
2650CREATE OR REPLACE FUNCTION get_clients_without_orders()
2651RETURNS TABLE (
2652 client_id INTEGER,
2653 client_name TEXT,
2654 email VARCHAR(50)
2655)
2656LANGUAGE sql
2657AS $$
2658 SELECT
2659 c.client_id,
2660 CONCAT_WS(' ', c.first_name, c.last_name),
2661 c.email
2662 FROM client c
2663 WHERE NOT EXISTS (
2664 SELECT 1 FROM "order" o WHERE o.client_id = c.client_id
2665 )
2666 ORDER BY c.client_id;
2667$$;
2668
2669CREATE OR REPLACE FUNCTION get_store_request_statistics()
2670RETURNS TABLE (
2671 store_id VARCHAR(3),
2672 store_name VARCHAR(50),
2673 total_requests BIGINT,
2674 solved_requests BIGINT,
2675 requests_in_progress BIGINT
2676)
2677LANGUAGE sql
2678AS $$
2679 SELECT
2680 s.store_id,
2681 s.name,
2682 COUNT(r.request_num),
2683 COUNT(r.request_num) FILTER (WHERE r.customer_satisfaction > 0),
2684 COUNT(r.request_num) FILTER (WHERE r.customer_satisfaction <= 0)
2685 FROM store s
2686 LEFT JOIN for_store fs ON fs.store_id = s.store_id
2687 LEFT JOIN request r ON r.request_num = fs.request_num
2688 GROUP BY s.store_id, s.name
2689 ORDER BY total_requests DESC, s.store_id;
2690$$;
2691
2692CREATE OR REPLACE FUNCTION get_top_10_employees_by_requests_last_month()
2693RETURNS TABLE (
2694 employee_id VARCHAR(10),
2695 employee_name TEXT,
2696 number_of_requests BIGINT
2697)
2698LANGUAGE sql
2699AS $$
2700 SELECT
2701 e.employee_id,
2702 CONCAT_WS(' ', p.first_name, p.last_name),
2703 COUNT(DISTINCT a.request_num)
2704 FROM employees e
2705 JOIN personal p ON p.id = e.employee_id
2706 JOIN answers a ON a.personal_id = e.employee_id
2707 JOIN request r ON r.request_num = a.request_num
2708 WHERE r.date_and_time >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 month'
2709 AND r.date_and_time < DATE_TRUNC('month', CURRENT_DATE)
2710 GROUP BY e.employee_id, p.first_name, p.last_name
2711 ORDER BY number_of_requests DESC, e.employee_id
2712 LIMIT 10;
2713$$;
2714
2715CREATE OR REPLACE FUNCTION get_employees_by_hours_and_pay()
2716RETURNS TABLE (
2717 employee_id VARCHAR(10),
2718 employee_name TEXT,
2719 total_hours_worked NUMERIC,
2720 total_pay NUMERIC
2721)
2722LANGUAGE sql
2723AS $$
2724 SELECT
2725 e.employee_id,
2726 CONCAT_WS(' ', p.first_name, p.last_name),
2727 COALESCE(SUM(w.total_hours), 0),
2728 COALESCE(SUM(w.wage * w.total_hours), 0)
2729 FROM employees e
2730 JOIN personal p ON p.id = e.employee_id
2731 LEFT JOIN worked w ON w.personal_id = e.employee_id
2732 GROUP BY e.employee_id, p.first_name, p.last_name
2733 ORDER BY total_hours_worked DESC, total_pay DESC, e.employee_id;
2734$$;
2735
2736CREATE OR REPLACE FUNCTION get_stores_average_pay()
2737RETURNS TABLE (
2738 store_id VARCHAR(3),
2739 store_name VARCHAR(50),
2740 average_pay NUMERIC
2741)
2742LANGUAGE sql
2743AS $$
2744 SELECT
2745 s.store_id,
2746 s.name,
2747 COALESCE(ROUND(AVG(w.wage), 2), 0)
2748 FROM store s
2749 LEFT JOIN worked w ON w.store_id = s.store_id
2750 GROUP BY s.store_id, s.name
2751 ORDER BY average_pay DESC, s.store_id;
2752$$;
2753
2754CREATE OR REPLACE FUNCTION get_employee_product_changes_last_month()
2755RETURNS TABLE (
2756 employee_id VARCHAR(10),
2757 employee_name TEXT,
2758 number_of_product_changes BIGINT
2759)
2760LANGUAGE sql
2761AS $$
2762 SELECT
2763 e.employee_id,
2764 CONCAT_WS(' ', p.first_name, p.last_name),
2765 COUNT(mc.change_date_time)
2766 FROM employees e
2767 JOIN personal p ON p.id = e.employee_id
2768 LEFT JOIN makes_change mc
2769 ON mc.personal_id = e.employee_id
2770 AND mc.change_date_time >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 month'
2771 AND mc.change_date_time < DATE_TRUNC('month', CURRENT_DATE)
2772 GROUP BY e.employee_id, p.first_name, p.last_name
2773 ORDER BY number_of_product_changes DESC, e.employee_id;
2774$$;
2775
2776CREATE OR REPLACE FUNCTION get_stores_by_monthly_profit_and_revenue_growth()
2777RETURNS TABLE (
2778 store_id VARCHAR(3),
2779 store_name VARCHAR(50),
2780 month_and_year TEXT,
2781 monthly_profit NUMERIC,
2782 previous_month_revenue NUMERIC,
2783 current_month_revenue NUMERIC,
2784 revenue_growth NUMERIC
2785)
2786LANGUAGE sql
2787AS $$
2788 WITH monthly_revenue AS (
2789 SELECT
2790 s.store_id,
2791 s.name AS store_name,
2792 DATE_TRUNC('month', o.last_date_mod)::DATE AS month_date,
2793 SUM(
2794 p.price * i.quantity
2795 * (1 - COALESCE(o.discount, 0) / 100.0)
2796 ) AS revenue
2797 FROM store s
2798 JOIN sells se ON se.store_id = s.store_id
2799 JOIN includes i ON i.product_code = se.product_code
2800 JOIN product p ON p.code = i.product_code
2801 JOIN "order" o ON o.order_num = i.order_num
2802 GROUP BY s.store_id, s.name, DATE_TRUNC('month', o.last_date_mod)
2803 ),
2804 with_previous AS (
2805 SELECT
2806 store_id,
2807 store_name,
2808 month_date,
2809 revenue,
2810 LAG(revenue) OVER (
2811 PARTITION BY store_id
2812 ORDER BY month_date
2813 ) AS previous_revenue
2814 FROM monthly_revenue
2815 )
2816 SELECT
2817 store_id,
2818 store_name,
2819 TO_CHAR(month_date, 'YYYY-MM'),
2820 ROUND(revenue, 2),
2821 ROUND(COALESCE(previous_revenue, 0), 2),
2822 ROUND(revenue, 2),
2823 ROUND(revenue - COALESCE(previous_revenue, 0), 2)
2824 FROM with_previous
2825 ORDER BY month_date DESC, monthly_profit DESC, store_id;
2826$$;
2827
2828CREATE OR REPLACE FUNCTION get_unapproved_reports()
2829RETURNS TABLE (
2830 report_date TIMESTAMP,
2831 store_id VARCHAR(3),
2832 overall_profit NUMERIC,
2833 sales_trend VARCHAR(100),
2834 marketing_growth VARCHAR(100),
2835 owner_signature VARCHAR(50)
2836)
2837LANGUAGE sql
2838AS $$
2839 SELECT
2840 r.date,
2841 r.store_id,
2842 r.overall_profit,
2843 r.sales_trend,
2844 r.marketing_growth,
2845 r.owner_signature
2846 FROM report r
2847 LEFT JOIN approves a
2848 ON a.report_date = r.date
2849 AND a.store_id = r.store_id
2850 WHERE a.report_date IS NULL
2851 ORDER BY r.date DESC, r.store_id;
2852$$;
2853`;
2854
2855function installReportFunctions(callback) {
2856
2857 pool.query(REPORT_FUNCTIONS_SQL)
2858 .then(() => {
2859 console.log('✅ PostgreSQL report functions installed');
2860 if (callback) callback(null);
2861 })
2862 .catch(err => {
2863 console.error('❌ Failed to install PostgreSQL report functions:', err);
2864 if (callback) callback(err);
2865 });
2866}
2867
2868const REPORT_FUNCTION_NAMES = new Set([
2869 'get_orders_by_total',
2870 'get_products_by_total_sales',
2871 'get_low_stock_high_demand_products',
2872 'get_products_monthly_sales',
2873 'get_stores_by_last_calendar_year_revenue',
2874 'get_products_never_ordered',
2875 'get_products_by_number_of_orders',
2876 'get_stores_by_average_review',
2877 'get_store_with_highest_revenue_growth',
2878 'get_clients_by_number_of_orders',
2879 'get_approximate_orders_per_client',
2880 'get_clients_without_orders',
2881 'get_store_request_statistics',
2882 'get_top_10_employees_by_requests_last_month',
2883 'get_employees_by_hours_and_pay',
2884 'get_stores_average_pay',
2885 'get_employee_product_changes_last_month',
2886 'get_stores_by_monthly_profit_and_revenue_growth',
2887 'get_unapproved_reports'
2888]);
2889
2890function runReport(reportName, params, callback) {
2891
2892 if (!REPORT_FUNCTION_NAMES.has(reportName)) {
2893 callback(new Error('Unknown report: ' + reportName), null);
2894 return;
2895 }
2896
2897 const values = Array.isArray(params) ? params : [];
2898
2899 const placeholders = values.map(
2900 (_, index) => '$' + (index + 1)
2901 ).join(', ');
2902
2903 query(
2904 `SELECT * FROM ${reportName}(${placeholders})`,
2905 values,
2906 (err, result) => {
2907 callback(
2908 err,
2909 result ? result.rows : []
2910 );
2911 }
2912 );
2913}
2914
2915function generateStoreReport(
2916 storeId,
2917 startDate,
2918 endDate,
2919 type,
2920 period,
2921 ownerSignature,
2922 callback
2923) {
2924
2925 query(
2926 `WITH sales AS (
2927 SELECT
2928 COALESCE(
2929 SUM(
2930 p.price * i.quantity
2931 * (1 - COALESCE(o.discount, 0) / 100.0)
2932 ),
2933 0
2934 ) AS revenue
2935 FROM sells se
2936 JOIN product p
2937 ON p.code = se.product_code
2938 JOIN includes i
2939 ON i.product_code = se.product_code
2940 JOIN "order" o
2941 ON o.order_num = i.order_num
2942 WHERE se.store_id = $1
2943 AND o.last_date_mod >= $2::timestamp
2944 AND o.last_date_mod < ($3::timestamp + INTERVAL '1 day')
2945 ),
2946 refunds AS (
2947 SELECT
2948 COALESCE(SUM(rf.amount), 0) AS refund_total
2949 FROM refund rf
2950 JOIN "order" o
2951 ON o.order_num = rf.order_num
2952 WHERE LEFT(o.order_num, 3) = $1
2953 AND o.last_date_mod >= $2::timestamp
2954 AND o.last_date_mod < ($3::timestamp + INTERVAL '1 day')
2955 AND rf.status IN ('approved', 'processed')
2956 )
2957 SELECT
2958 sales.revenue,
2959 refunds.refund_total,
2960 sales.revenue - refunds.refund_total AS net_profit
2961 FROM sales CROSS JOIN refunds`,
2962 [storeId, startDate, endDate],
2963 (err, result) => {
2964
2965 if (err) {
2966 callback(err, null);
2967 return;
2968 }
2969
2970 const row = result.rows[0] || {};
2971 const revenue = Number(row.revenue || 0);
2972 const refundTotal = Number(row.refund_total || 0);
2973 const netProfit = Number(row.net_profit || 0);
2974
2975 const salesTrend =
2976 `Revenue ${revenue.toFixed(2)}; Refunds ${refundTotal.toFixed(2)}`;
2977
2978 query(
2979 `SELECT
2980 COALESCE(SUM(
2981 p.price * i.quantity
2982 * (1 - COALESCE(o.discount, 0) / 100.0)
2983 ), 0) AS revenue
2984 FROM sells se
2985 JOIN product p
2986 ON p.code = se.product_code
2987 JOIN includes i
2988 ON i.product_code = se.product_code
2989 JOIN "order" o
2990 ON o.order_num = i.order_num
2991 WHERE se.store_id = $1
2992 AND o.last_date_mod >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 month'
2993 AND o.last_date_mod < DATE_TRUNC('month', CURRENT_DATE)`,
2994 [storeId],
2995 (previousErr, previousResult) => {
2996
2997 if (previousErr) {
2998 callback(previousErr, null);
2999 return;
3000 }
3001
3002 const previousRevenue =
3003 Number(previousResult.rows[0]?.revenue || 0);
3004
3005 const growth =
3006 previousRevenue === 0
3007 ? (revenue > 0 ? 100 : 0)
3008 : ((revenue - previousRevenue) / previousRevenue) * 100;
3009
3010 const marketingGrowth =
3011 `${growth.toFixed(2)}%`;
3012
3013 query(
3014 `INSERT INTO report
3015 (
3016 date,
3017 store_id,
3018 overall_profit,
3019 sales_trend,
3020 marketing_growth,
3021 owner_signature
3022 )
3023 VALUES
3024 (
3025 CURRENT_TIMESTAMP,
3026 $1,
3027 $2,
3028 $3,
3029 $4,
3030 $5
3031 )
3032 RETURNING
3033 date,
3034 store_id,
3035 overall_profit,
3036 sales_trend,
3037 marketing_growth,
3038 owner_signature`,
3039 [
3040 storeId,
3041 Math.max(0, netProfit),
3042 salesTrend.slice(0, 100),
3043 marketingGrowth.slice(0, 100),
3044 ownerSignature || 'Not signed yet'
3045 ],
3046 (insertErr, insertResult) => {
3047
3048 if (insertErr) {
3049 callback(insertErr, null);
3050 return;
3051 }
3052
3053 const report = insertResult.rows[0];
3054
3055 /*
3056 * monthly_profit has a composite primary key of
3057 * (report_date, store_id), so it can contain one
3058 * summary row per generated report without changing
3059 * the project database structure.
3060 */
3061 query(
3062 `INSERT INTO monthly_profit
3063 (
3064 report_date,
3065 store_id,
3066 month_and_year,
3067 profit
3068 )
3069 VALUES
3070 (
3071 $1,
3072 $2,
3073 DATE_TRUNC('month', $3::timestamp)::DATE,
3074 $4
3075 )
3076 ON CONFLICT (report_date, store_id)
3077 DO UPDATE SET
3078 month_and_year = EXCLUDED.month_and_year,
3079 profit = EXCLUDED.profit`,
3080 [
3081 report.date,
3082 storeId,
3083 endDate,
3084 Math.max(0, netProfit)
3085 ],
3086 (monthlyErr) => {
3087
3088 if (monthlyErr) {
3089 console.error(
3090 'Warning inserting monthly profit:',
3091 monthlyErr
3092 );
3093 }
3094
3095 query(
3096 `INSERT INTO exchanges_data
3097 (
3098 report_date,
3099 store_id,
3100 monthly_profit,
3101 date,
3102 sales,
3103 damages
3104 )
3105 VALUES
3106 (
3107 $1,
3108 $2,
3109 $3,
3110 CURRENT_TIMESTAMP,
3111 $4,
3112 $5
3113 )
3114 ON CONFLICT (report_date, store_id)
3115 DO UPDATE SET
3116 monthly_profit = EXCLUDED.monthly_profit,
3117 date = EXCLUDED.date,
3118 sales = EXCLUDED.sales,
3119 damages = EXCLUDED.damages`,
3120 [
3121 report.date,
3122 storeId,
3123 Math.max(0, netProfit),
3124 revenue,
3125 -refundTotal
3126 ],
3127 (exchangeErr) => {
3128
3129 if (exchangeErr) {
3130 console.error(
3131 'Warning inserting exchange data:',
3132 exchangeErr
3133 );
3134 }
3135
3136 callback(
3137 null,
3138 report
3139 );
3140 }
3141 );
3142 }
3143 );
3144 }
3145 );
3146 }
3147 );
3148 }
3149 );
3150}
[62b2964]3151/*
3152 * ============================================================
3153 * EXPORTS
3154 * ============================================================
3155 */
3156
[69f2a41]3157module.exports = {
[62b2964]3158
3159 pool,
3160
3161 query,
3162
[69f2a41]3163 ensureGeneralCategory,
3164 getGeneralCategoryId,
[62b2964]3165
[69f2a41]3166 getUserByUsername,
3167 getUserById,
3168 createUser,
[62b2964]3169
[69f2a41]3170 getClientByEmail,
3171 getClientById,
3172 createClient,
3173 verifyClientPassword,
[62b2964]3174
[69f2a41]3175 getPersonalByEmail,
3176 getPersonalById,
3177 verifyPassword,
3178 updatePasswordAndClearForce,
[62b2964]3179
[69f2a41]3180 getProducts,
3181 getProductById,
3182 getProductByCode,
3183 addProduct,
3184 updateProduct,
3185 deleteProduct,
[62b2964]3186
[69f2a41]3187 getCategories,
3188 getCategoriesWithParents,
3189 createCategory,
[62b2964]3190
[69f2a41]3191 getStores,
3192 getStoreProducts,
3193 getStoreOrders,
3194 getStoreEmployees,
3195 getStoreReports,
3196 getStoreStats,
[62b2964]3197
[6c7cfa6]3198 installReportFunctions,
3199 runReport,
3200 generateStoreReport,
3201
[69f2a41]3202 createOrderNew,
3203 getOrdersByClient,
3204 getAllOrders,
[62b2964]3205
[69f2a41]3206 createReviewNew,
[62b2964]3207
[69f2a41]3208 createRequest,
[62b2964]3209
[69f2a41]3210 createRefund,
[62b2964]3211
[69f2a41]3212 getEmployeeTasks,
[62b2964]3213
[69f2a41]3214 getClientStats,
[62b2964]3215
[69f2a41]3216 getAllUsers,
[62b2964]3217
[69f2a41]3218 logAudit
3219};
Note: See TracBrowser for help on using the repository browser.