source: database.js@ 6c7cfa6

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

Implemented Advaced database reports
  • Property mode set to 100644
File size: 87.3 KB
Line 
1const { Pool } = require('pg');
2const bcrypt = require('bcryptjs');
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),
27 database: process.env.PGDATABASE || 'db_202526z_va_prj_handcraft_store',
28 user: process.env.PGUSER || 'db_202526z_va_prj_handcraft_store_owner',
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});
38
39pool.on('error', (err) => {
40 console.error('❌ Unexpected PostgreSQL pool error:', err);
41});
42
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 });
57}
58
59
60/*
61 * ============================================================
62 * GENERAL CATEGORY
63 * ============================================================
64 */
65
66function ensureGeneralCategory(callback) {
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 }
103 }
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 }
123 }
124 );
125 }
126 }
127 );
128}
129
130
131function getGeneralCategoryId(callback) {
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`,
174 ['General', 'General products category'],
175 (err, result) => {
176
177 if (err) {
178 callback(err, null);
179 } else {
180 const newId = result.rows[0].category_id;
181
182 console.log(
183 `✅ Created new General category with ID: ${newId}`
184 );
185
186 callback(null, newId);
187 }
188 }
189 );
190 }
191 );
192 }
193 );
194}
195
196
197/*
198 * ============================================================
199 * USER FUNCTIONS
200 * ============================================================
201 */
202
203function getUserByUsername(username, callback) {
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 );
218}
219
220
221function getUserById(id, callback) {
222
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;
233 }
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 );
256}
257
258
259function createUser(id, username, email, password, userType, callback) {
260
261 const hashedPassword = bcrypt.hashSync(password, 10);
262
263 query(
264 `INSERT INTO users
265 (id, username, email, password, user_type)
266 VALUES
267 ($1, $2, $3, $4, $5)`,
268 [id, username, email, hashedPassword, userType],
269 (err) => {
270
271 if (err) {
272 callback(err, null);
273 } else {
274 callback(null, id);
275 }
276 }
277 );
278}
279
280
281/*
282 * ============================================================
283 * CLIENT FUNCTIONS
284 * ============================================================
285 */
286
287function getClientByEmail(email, callback) {
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 );
302}
303
304
305function getClientById(id, callback) {
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 );
320}
321
322
323function createClient(clientData, callback) {
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
342 if (err) {
343 callback(err, null);
344 } else {
345 callback(
346 null,
347 result.rows[0].client_id
348 );
349 }
350 }
351 );
352}
353
354
355function verifyClientPassword(
356 password,
357 hashedPassword,
358 callback
359) {
360
361 try {
362
363 const isValid =
364 bcrypt.compareSync(password, hashedPassword);
365
366 callback(null, isValid);
367
368 } catch (err) {
369
370 callback(err, false);
371 }
372}
373
374
375/*
376 * ============================================================
377 * PERSONAL / EMPLOYEE FUNCTIONS
378 * ============================================================
379 */
380
381function getPersonalByEmail(email, callback) {
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 );
396}
397
398
399function getPersonalById(id, callback) {
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 );
414}
415
416
417function verifyPassword(password, hashedPassword) {
418 return bcrypt.compareSync(password, hashedPassword);
419}
420
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`,
436 [hashedPassword, userId],
437 (err) => {
438
439 callback(err);
440 }
441 );
442}
443
444
445/*
446 * ============================================================
447 * PRODUCT FUNCTIONS
448 * ============================================================
449 */
450
451function getProducts(categoryId, searchTerm, callback) {
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
466 const params = [];
467 let paramIndex = 1;
468
469 if (categoryId && categoryId !== 'all') {
470
471 queryText +=
472 ` AND p.category_id = $${paramIndex}`;
473
474 params.push(categoryId);
475 paramIndex++;
476 }
477
478 if (searchTerm) {
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;
490 }
491
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 }
504 }
505 );
506}
507
508
509function getProductById(id, callback) {
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`,
522 [id],
523 (err, result) => {
524
525 if (err || result.rows.length === 0) {
526 callback(err, null);
527 return;
528 }
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 );
565 }
566 );
567}
568
569
570function getProductByCode(code, callback) {
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`,
583 [code],
584 (err, result) => {
585
586 if (err || result.rows.length === 0) {
587 callback(err, null);
588 return;
589 }
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 );
627 }
628 );
629}
630
631
632function addProduct(personalId, productData, callback) {
633
634 getGeneralCategoryId((err, generalCategoryId) => {
635
636 if (err) {
637 callback(err, null);
638 return;
639 }
640
641 const categoryId =
642 productData.category_id || generalCategoryId;
643
644 if (!productData.code) {
645 callback(
646 new Error('Product code is required'),
647 null
648 );
649 return;
650 }
651
652 if (!productData.store_id) {
653 callback(
654 new Error('Store ID is required'),
655 null
656 );
657 return;
658 }
659
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`,
694 [
695 productId,
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 ],
706 (err, result) => {
707
708 if (err) {
709 callback(err, null);
710 return;
711 }
712
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 );
734 }
735 }
736 );
737
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
760 );
761 }
762 }
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) => {
792
793 if (err) {
794 console.error(
795 'Error inserting image:',
796 err
797 );
798 }
799 }
800 );
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) => {
826
827 if (err) {
828 console.error(
829 'Error inserting color:',
830 err
831 );
832 }
833 }
834 );
835 });
836 }
837
838 callback(null, returnedId);
839 }
840 );
841 });
842}
843
844
845function updateProduct(
846 personalId,
847 productData,
848 callback
849) {
850
851 const updates = [];
852 const params = [];
853
854 if (productData.description !== undefined) {
855 updates.push(`description = $${params.length + 1}`);
856 params.push(productData.description);
857 }
858
859 if (productData.price !== undefined) {
860 updates.push(`price = $${params.length + 1}`);
861 params.push(productData.price);
862 }
863
864 if (productData.availability !== undefined) {
865 updates.push(`availability = $${params.length + 1}`);
866 params.push(productData.availability);
867 }
868
869 if (productData.weight !== undefined) {
870 updates.push(`weight = $${params.length + 1}`);
871 params.push(productData.weight);
872 }
873
874 if (productData.dimensions !== undefined) {
875 updates.push(`dimensions = $${params.length + 1}`);
876 params.push(productData.dimensions);
877 }
878
879 if (productData.production_time !== undefined) {
880 updates.push(`production_time = $${params.length + 1}`);
881 params.push(productData.production_time);
882 }
883
884 if (productData.category_id !== undefined) {
885 updates.push(`category_id = $${params.length + 1}`);
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
896 const codeParameter = params.length;
897
898 query(
899 `UPDATE product
900 SET ${updates.join(', ')}
901 WHERE code = $${codeParameter}`,
902 params,
903 (err, result) => {
904
905 if (err) {
906 callback(err, null);
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
935 );
936 }
937 }
938 );
939
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 }
964 }
965 );
966
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;
987 }
988
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 );
1020 }
1021
1022 /*
1023 * Colors
1024 */
1025 if (
1026 productData.colors &&
1027 Array.isArray(productData.colors)
1028 ) {
1029
1030 query(
1031 `DELETE FROM color
1032 WHERE product_code = $1`,
1033 [productData.code],
1034 (err) => {
1035
1036 if (err) {
1037 console.error(
1038 'Error deleting old colors:',
1039 err
1040 );
1041 return;
1042 }
1043
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 }
1063 }
1064 );
1065 });
1066 }
1067 );
1068 }
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 });
1160}
1161
1162
1163/*
1164 * ============================================================
1165 * CATEGORY FUNCTIONS
1166 * ============================================================
1167 */
1168
1169function getCategories(callback) {
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 );
1184}
1185
1186
1187function getCategoriesWithParents(callback) {
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`,
1198 [],
1199 (err, result) => {
1200
1201 callback(
1202 err,
1203 result ? result.rows : []
1204 );
1205 }
1206 );
1207}
1208
1209
1210function createCategory(categoryData, callback) {
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
1229 if (err) {
1230 callback(err, null);
1231 } else {
1232
1233 callback(null, {
1234 id: result.rows[0].category_id,
1235 name: categoryData.name,
1236 parent_id: categoryData.parent_id,
1237 description: categoryData.description
1238 });
1239 }
1240 }
1241 );
1242}
1243
1244
1245/*
1246 * ============================================================
1247 * STORE FUNCTIONS
1248 * ============================================================
1249 */
1250
1251function getStores(callback) {
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 );
1266}
1267
1268
1269function getStoreProducts(storeId, callback) {
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`,
1280 [storeId],
1281 (err, result) => {
1282
1283 callback(
1284 err,
1285 result ? result.rows : []
1286 );
1287 }
1288 );
1289}
1290
1291
1292function getStoreOrders(storeId, callback) {
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`,
1304 [storeId],
1305 (err, result) => {
1306
1307 if (err) {
1308 callback(err, null);
1309 return;
1310 }
1311
1312 const orders = result.rows || [];
1313
1314 if (orders.length === 0) {
1315 callback(null, []);
1316 return;
1317 }
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 });
1349 }
1350 );
1351}
1352
1353
1354function getStoreEmployees(storeId, callback) {
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`,
1370 [storeId],
1371 (err, result) => {
1372
1373 callback(
1374 err,
1375 result ? result.rows : []
1376 );
1377 }
1378 );
1379}
1380
1381
1382function getStoreReports(storeId, callback) {
1383
1384 query(
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`,
1395 [storeId],
1396 (err, result) => {
1397 callback(
1398 err,
1399 result ? result.rows : []
1400 );
1401 }
1402 );
1403}
1404
1405
1406function getStoreStats(storeId, callback) {
1407
1408 const stats = {};
1409
1410 query(
1411 `SELECT COUNT(DISTINCT se.product_code) AS total_products
1412 FROM sells se
1413 WHERE se.store_id = $1`,
1414 [storeId],
1415 (err, result) => {
1416
1417 if (err) {
1418 callback(err, null);
1419 return;
1420 }
1421
1422 stats.total_products = Number(
1423 result.rows[0]?.total_products || 0
1424 );
1425
1426 query(
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`,
1434 [storeId],
1435 (err, result) => {
1436
1437 if (err) {
1438 callback(err, null);
1439 return;
1440 }
1441
1442 stats.total_orders = Number(
1443 result.rows[0]?.total_orders || 0
1444 );
1445
1446 query(
1447 `SELECT
1448 COALESCE(
1449 SUM(
1450 p.price * i.quantity
1451 * (1 - COALESCE(o.discount, 0) / 100.0)
1452 ),
1453 0
1454 ) AS total_revenue
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
1460 JOIN "order" o
1461 ON o.order_num = i.order_num
1462 WHERE se.store_id = $1`,
1463 [storeId],
1464 (err, result) => {
1465
1466 if (err) {
1467 callback(err, null);
1468 return;
1469 }
1470
1471 stats.total_revenue = Number(
1472 result.rows[0]?.total_revenue || 0
1473 );
1474
1475 query(
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`,
1489 [storeId],
1490 (err, result) => {
1491
1492 if (err) {
1493 callback(err, null);
1494 return;
1495 }
1496
1497 stats.avg_rating = Number(
1498 result.rows[0]?.avg_rating || 0
1499 );
1500
1501 callback(null, stats);
1502 }
1503 );
1504 }
1505 );
1506 }
1507 );
1508 }
1509 );
1510}
1511
1512
1513/*
1514 * ============================================================
1515 * ORDER FUNCTIONS
1516 * ============================================================
1517 */
1518
1519function createOrderNew(orderData, callback) {
1520
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(() => {
1562
1563 const items =
1564 orderData.items || [];
1565
1566 if (items.length === 0) {
1567 return client.query('COMMIT')
1568 .then(() => {
1569
1570 client.release();
1571
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 });
1621 });
1622 })
1623 .catch(err => {
1624 callback(err, null);
1625 });
1626}
1627
1628
1629function getOrdersByClient(clientId, callback) {
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`,
1640 [clientId],
1641 (err, result) => {
1642
1643 if (err) {
1644 callback(err, null);
1645 return;
1646 }
1647
1648 const orders = result.rows || [];
1649
1650 if (orders.length === 0) {
1651 callback(null, []);
1652 return;
1653 }
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 });
1685 }
1686 );
1687}
1688
1689
1690function getAllOrders(callback) {
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`,
1704 [],
1705 (err, result) => {
1706
1707 callback(
1708 err,
1709 result ? result.rows : []
1710 );
1711 }
1712 );
1713}
1714
1715
1716/*
1717 * ============================================================
1718 * REVIEW FUNCTIONS
1719 * ============================================================
1720 */
1721
1722function createReviewNew(reviewData, callback) {
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`,
1741 [
1742 reviewId,
1743 reviewData.client_id,
1744 reviewData.product_code,
1745 reviewData.rating,
1746 reviewData.comment || ''
1747 ],
1748 (err, result) => {
1749
1750 if (err) {
1751 callback(err, null);
1752 } else {
1753 callback(
1754 null,
1755 result.rows[0].review_id
1756 );
1757 }
1758 }
1759 );
1760}
1761
1762
1763/*
1764 * ============================================================
1765 * REQUEST FUNCTIONS
1766 * ============================================================
1767 */
1768
1769function createRequest(requestData, callback) {
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)`,
1782 [
1783 requestData.request_num,
1784 requestData.date_and_time,
1785 requestData.problem,
1786 requestData.client_id,
1787 requestData.store_id
1788 ],
1789 (err) => {
1790
1791 if (err) {
1792 callback(err, null);
1793 } else {
1794 callback(
1795 null,
1796 requestData.request_num
1797 );
1798 }
1799 }
1800 );
1801}
1802
1803
1804/*
1805 * ============================================================
1806 * REFUND FUNCTIONS
1807 * ============================================================
1808 */
1809
1810function createRefund(refundData, callback) {
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())`,
1823 [
1824 refundData.refund_id,
1825 refundData.order_num,
1826 refundData.amount,
1827 refundData.reason
1828 ],
1829 (err) => {
1830
1831 if (err) {
1832 callback(err, null);
1833 } else {
1834 callback(
1835 null,
1836 refundData.refund_id
1837 );
1838 }
1839 }
1840 );
1841}
1842
1843
1844/*
1845 * ============================================================
1846 * EMPLOYEE TASKS
1847 * ============================================================
1848 */
1849
1850function getEmployeeTasks(
1851 personalId,
1852 storeId,
1853 callback
1854) {
1855
1856 const tasks = {
1857 pending_orders: [],
1858 pending_requests: [],
1859 pending_refunds: []
1860 };
1861
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
1882 if (!err) {
1883 tasks.pending_orders =
1884 result.rows || [];
1885 }
1886
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
1907 if (!err) {
1908 tasks.pending_requests =
1909 result.rows || [];
1910 }
1911
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
1937 if (!err) {
1938 tasks.pending_refunds =
1939 result.rows || [];
1940 }
1941
1942 callback(
1943 null,
1944 tasks
1945 );
1946 }
1947 );
1948 }
1949 );
1950 }
1951 );
1952}
1953
1954
1955/*
1956 * ============================================================
1957 * CLIENT STATISTICS
1958 * ============================================================
1959 */
1960
1961function getClientStats(clientId, callback) {
1962
1963 const stats = {};
1964
1965 query(
1966 `SELECT COUNT(*) AS total_orders
1967 FROM "order"
1968 WHERE client_id = $1`,
1969 [clientId],
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`,
1996 [clientId],
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 );
2064 }
2065 );
2066 }
2067 );
2068 }
2069 );
2070 }
2071 );
2072}
2073
2074
2075/*
2076 * ============================================================
2077 * ADMIN USER FUNCTIONS
2078 * ============================================================
2079 */
2080
2081function getAllUsers(callback) {
2082
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
2092 FROM client
2093 ORDER BY client_id`,
2094 [],
2095 (err, result) => {
2096
2097 if (err) {
2098 callback(err, null);
2099 return;
2100 }
2101
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
2128 FROM personal p
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
2135 ORDER BY p.id`,
2136 [],
2137 (err, result) => {
2138
2139 if (err) {
2140 callback(err, null);
2141 return;
2142 }
2143
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
2178 FROM users
2179 ORDER BY id`,
2180 [],
2181 (err, result) => {
2182
2183 if (err) {
2184 callback(err, null);
2185 return;
2186 }
2187
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 }
2226 });
2227
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 );
2245 }
2246 );
2247 }
2248 );
2249 }
2250 );
2251}
2252
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 ],
2289 (err) => {
2290
2291 if (err) {
2292 console.error(
2293 'Error logging audit:',
2294 err
2295 );
2296 }
2297 }
2298 );
2299}
2300
2301
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}
3151/*
3152 * ============================================================
3153 * EXPORTS
3154 * ============================================================
3155 */
3156
3157module.exports = {
3158
3159 pool,
3160
3161 query,
3162
3163 ensureGeneralCategory,
3164 getGeneralCategoryId,
3165
3166 getUserByUsername,
3167 getUserById,
3168 createUser,
3169
3170 getClientByEmail,
3171 getClientById,
3172 createClient,
3173 verifyClientPassword,
3174
3175 getPersonalByEmail,
3176 getPersonalById,
3177 verifyPassword,
3178 updatePasswordAndClearForce,
3179
3180 getProducts,
3181 getProductById,
3182 getProductByCode,
3183 addProduct,
3184 updateProduct,
3185 deleteProduct,
3186
3187 getCategories,
3188 getCategoriesWithParents,
3189 createCategory,
3190
3191 getStores,
3192 getStoreProducts,
3193 getStoreOrders,
3194 getStoreEmployees,
3195 getStoreReports,
3196 getStoreStats,
3197
3198 installReportFunctions,
3199 runReport,
3200 generateStoreReport,
3201
3202 createOrderNew,
3203 getOrdersByClient,
3204 getAllOrders,
3205
3206 createReviewNew,
3207
3208 createRequest,
3209
3210 createRefund,
3211
3212 getEmployeeTasks,
3213
3214 getClientStats,
3215
3216 getAllUsers,
3217
3218 logAudit
3219};
Note: See TracBrowser for help on using the repository browser.