-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathprofile.sh
More file actions
executable file
·2588 lines (2338 loc) · 91.2 KB
/
profile.sh
File metadata and controls
executable file
·2588 lines (2338 loc) · 91.2 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
#!/bin/bash
# This script generates user profiles, country profiles, or general statistics
# about notes. It reads data from the datamart tables in the data warehouse.
#
# There are 3 ways to call this script:
# * --user <UserName> : Shows the profile for the given user.
# * --country <CountryName> : Shows the profile for the given country (name in
# English).
# * --pais <NombrePais> : Shows the profile for the given country (name in
# Spanish).
# If the UserName, CountryName, or NombrePais contains spaces, it must be
# enclosed in double quotes.
# * (empty) : Shows general statistics about notes.
#
# For example:
# * --user AngocA
# * --country Colombia
# * --country "United States of America"
# * --pais Alemania
# * --country Germany
# The country name must match the name stored in the database (in English or
# Spanish as specified by the option used).
#
# This script queries the data warehouse datamarts and displays pre-computed
# analytics for testing and validation purposes.
#
# This is the list of error codes:
# 1) Help message.
# 241) Library or utility missing.
# 242) Invalid argument for script invocation.
# 243) Logger utility is not available.
#
# For contributing, please execute these commands before submitting:
# * shellcheck -x -o all profile.sh
# * shfmt -w -i 1 -sr -bn profile.sh
#
# Author: Andres Gomez (AngocA)
# Version: 2025-08-12
VERSION="2025-08-12"
#set -xv
# Fails when a variable is not initialized.
set -u
# Fails with a non-zero return code.
set -e
# Fails if the commands of a pipe return non-zero.
set -o pipefail
# Fails if an internal function fails.
set -E
# Fails parent if child fails.
shopt -s inherit_errexit
# If all files should be deleted. In case of an error, this could be disabled.
# You can defined when calling: export CLEAN=false
# CLEAN is now defined in etc/properties.sh, no need to declare it here
# Logger levels: TRACE, DEBUG, INFO, WARN, ERROR, FATAL.
declare LOG_LEVEL="${LOG_LEVEL:-ERROR}"
# Base directory for the project.
# Only set SCRIPT_BASE_DIRECTORY if not already defined (e.g., in test environment)
if [[ -z "${SCRIPT_BASE_DIRECTORY:-}" ]]; then
declare SCRIPT_BASE_DIRECTORY
SCRIPT_BASE_DIRECTORY="$(cd "$(dirname "${BASH_SOURCE[0]}")/../.." \
&> /dev/null && pwd)"
readonly SCRIPT_BASE_DIRECTORY
fi
# Loads the global properties.
# shellcheck disable=SC1091
if [[ -f "${SCRIPT_BASE_DIRECTORY}/tests/properties.sh" ]] && [[ "${BATS_TEST_NAME:-}" != "" ]]; then
# Use test properties when running in test environment
source "${SCRIPT_BASE_DIRECTORY}/tests/properties.sh"
else
# Use production properties
source "${SCRIPT_BASE_DIRECTORY}/etc/properties.sh"
fi
# Load local properties if they exist (overrides global settings)
if [[ -f "${SCRIPT_BASE_DIRECTORY}/etc/properties.sh.local" ]]; then
# shellcheck disable=SC1091
source "${SCRIPT_BASE_DIRECTORY}/etc/properties.sh.local"
fi
declare BASENAME
BASENAME=$(basename -s .sh "${0}")
readonly BASENAME
# Temporary directory for all files.
declare TMP_DIR
TMP_DIR=$(mktemp -d "/tmp/${BASENAME}_XXXXXX")
readonly TMP_DIR
chmod 777 "${TMP_DIR}"
# Log file for output.
declare LOG_FILENAME
LOG_FILENAME="${TMP_DIR}/${BASENAME}.log"
readonly LOG_FILENAME
# Parse log level options from arguments and set PROCESS_TYPE/ARGUMENT from the rest.
if [[ -z "${PROCESS_TYPE:-}" ]]; then
declare -a remaining_args=()
for arg in "$@"; do
case "${arg}" in
-v | --verbose) LOG_LEVEL="INFO" ;;
--debug) LOG_LEVEL="DEBUG" ;;
-q | --quiet) LOG_LEVEL="ERROR" ;;
*) remaining_args+=("${arg}") ;;
esac
done
declare -r PROCESS_TYPE="${remaining_args[0]:-}"
declare -r ARGUMENT="${remaining_args[1]:-}"
else
declare -r PROCESS_TYPE="${PROCESS_TYPE}"
declare -r ARGUMENT="${ARGUMENT:-}"
fi
# Username.
declare USERNAME
# Dimension_User_id of the username.
declare -i DIMENSION_USER_ID
# Name of the user or the country in English.
declare COUNTRY_NAME
# Name of the user or the country in Spanish.
declare PAIS_NAME
# Country_id of the country.
declare -i COUNTRY_ID
###########
# FUNCTIONS
# Load common functions
# shellcheck disable=SC1091
source "${SCRIPT_BASE_DIRECTORY}/lib/osm-common/commonFunctions.sh"
# Load validation functions
# shellcheck disable=SC1091
source "${SCRIPT_BASE_DIRECTORY}/lib/osm-common/validationFunctions.sh"
# Load error handling functions
# shellcheck disable=SC1091
source "${SCRIPT_BASE_DIRECTORY}/lib/osm-common/errorHandlingFunctions.sh"
# Shows the help information.
function __show_help {
echo "${0} version ${VERSION}"
echo "This script shows the profile for a given user or country,"
echo "or general statistics about notes."
echo
echo "There are 3 ways to call this script:"
echo "* --user <UserName> : Shows the profile for the given user."
echo "* --country <CountryName> : Shows the profile for the given country (English name)."
echo "* --pais <NombrePais> : Shows the profile for the given country (Spanish name)."
echo
echo "If the name contains spaces, it must be enclosed in double quotes."
echo "The name must match the name stored in the database."
echo
echo "Examples:"
echo "* --user AngocA"
echo "* --country Colombia"
echo "* --country \"United States of America\""
echo "* --pais \"Estados Unidos\""
echo "* (empty) : Shows general statistics about notes."
echo
echo "Log level (controls verbosity; default is ERROR, i.e. only errors):"
echo "* -v, --verbose : INFO - progress and key steps"
echo "* --debug : DEBUG - include function start/finish and timing"
echo "* -q, --quiet : ERROR - only errors (default)"
echo " Or set LOG_LEVEL=INFO (or DEBUG, WARN, ERROR) in the environment."
echo
echo "Written by: Andres Gomez (AngocA)"
echo "OSM-LatAm, OSM-Colombia, MaptimeBogota."
exit "${ERROR_HELP_MESSAGE}"
}
# Checks prerequisites to run the script.
function __checkPrereqs {
__log_start
if [[ "${PROCESS_TYPE}" != "" ]] \
&& [[ "${PROCESS_TYPE}" != "--user" ]] \
&& [[ "${PROCESS_TYPE}" != "--country" ]] \
&& [[ "${PROCESS_TYPE}" != "--pais" ]] \
&& [[ "${PROCESS_TYPE}" != "--help" ]] \
&& [[ "${PROCESS_TYPE}" != "-h" ]]; then
echo "ERROR: Invalid parameter. It should be:"
echo " * Empty string, nothing."
echo " * --user"
echo " * --country"
echo " * --pais"
echo " * --help"
exit "${ERROR_INVALID_ARGUMENT}"
fi
if [[ "${PROCESS_TYPE}" == "--user" ]] \
&& [[ "${ARGUMENT}" == "" ]]; then
__loge "ERROR: You must provide a username."
exit "${ERROR_INVALID_ARGUMENT}"
else
USERNAME="${ARGUMENT}"
fi
if [[ "${PROCESS_TYPE}" == "--country" ]] \
&& [[ "${ARGUMENT}" == "" ]]; then
__loge "ERROR: You must provide a country name."
exit "${ERROR_INVALID_ARGUMENT}"
else
COUNTRY_NAME="${ARGUMENT}"
fi
if [[ "${PROCESS_TYPE}" == "--pais" ]] \
&& [[ "${ARGUMENT}" == "" ]]; then
__loge "ERROR: You must provide a country name."
exit "${ERROR_INVALID_ARGUMENT}"
else
PAIS_NAME="${ARGUMENT}"
fi
__checkPrereqsCommands
__log_finish
}
# Retrieves the dimension_user_id from a username.
function __getUserId {
__log_start
DIMENSION_USER_ID=$(psql -d "${DBNAME_DWH}" -Atq -v ON_ERROR_STOP=1 \
<<< "SELECT dimension_user_id FROM dwh.datamartUsers
WHERE username = '${USERNAME}'")
if [[ "${DIMENSION_USER_ID}" == "" ]] \
|| [[ "${DIMENSION_USER_ID}" -eq 0 ]]; then
__loge "ERROR: The username \"${USERNAME}\" does not exist."
exit "${ERROR_INVALID_ARGUMENT}"
fi
__log_finish
}
# Retrieves the country_id from a country name.
function __getCountryId {
__log_start
if [[ "${PROCESS_TYPE}" == "--country" ]]; then
COUNTRY_ID=$(psql -d "${DBNAME_DWH}" -Atq -v ON_ERROR_STOP=1 \
<<< "SELECT dimension_country_id FROM dwh.datamartCountries
WHERE country_name_en = '${COUNTRY_NAME}'")
else
COUNTRY_ID=$(psql -d "${DBNAME_DWH}" -Atq -v ON_ERROR_STOP=1 \
<<< "SELECT dimension_country_id FROM dwh.datamartCountries
WHERE country_name_es = '${PAIS_NAME}'")
fi
if [[ "${COUNTRY_ID}" == "" ]]; then
__loge "ERROR: The country name \"${COUNTRY_NAME}${PAIS_NAME}\" does not exist."
exit "${ERROR_INVALID_ARGUMENT}"
fi
__log_finish
}
# Shows the user activity for all years after 2013.
function __showActivityYearUsers {
__log_start
YEAR="${1}"
declare -i HISTORY_YEAR_OPEN
HISTORY_YEAR_OPEN=$(psql -d "${DBNAME_DWH}" -Atq \
-c "SELECT history_${YEAR}_open
FROM dwh.datamartUsers
WHERE dimension_user_id = ${DIMENSION_USER_ID}
" \
-v ON_ERROR_STOP=1)
declare -i HISTORY_YEAR_COMMENTED
HISTORY_YEAR_COMMENTED=$(psql -d "${DBNAME_DWH}" -Atq \
-c "SELECT history_${YEAR}_commented
FROM dwh.datamartUsers
WHERE dimension_user_id = ${DIMENSION_USER_ID}
" \
-v ON_ERROR_STOP=1)
declare -i HISTORY_YEAR_CLOSED
HISTORY_YEAR_CLOSED=$(psql -d "${DBNAME_DWH}" -Atq \
-c "SELECT history_${YEAR}_closed
FROM dwh.datamartUsers
WHERE dimension_user_id = ${DIMENSION_USER_ID}
" \
-v ON_ERROR_STOP=1)
declare -i HISTORY_YEAR_CLOSED_WITH_COMMENT
HISTORY_YEAR_CLOSED_WITH_COMMENT=$(psql -d "${DBNAME_DWH}" -Atq \
-c "SELECT history_${YEAR}_closed_with_comment
FROM dwh.datamartUsers
WHERE dimension_user_id = ${DIMENSION_USER_ID}
" \
-v ON_ERROR_STOP=1)
declare -i HISTORY_YEAR_REOPENED
HISTORY_YEAR_REOPENED=$(psql -d "${DBNAME_DWH}" -Atq \
-c "SELECT history_${YEAR}_reopened
FROM dwh.datamartUsers
WHERE dimension_user_id = ${DIMENSION_USER_ID}
" \
-v ON_ERROR_STOP=1)
printf "${YEAR}: %9d %9d %9d %9d %9d\n" "${HISTORY_YEAR_OPEN}" "${HISTORY_YEAR_COMMENTED}" "${HISTORY_YEAR_CLOSED}" "${HISTORY_YEAR_CLOSED_WITH_COMMENT}" "${HISTORY_YEAR_REOPENED}"
__log_finish
}
# Shows the country activity for all years after 2013.
function __showActivityYearCountries {
__log_start
YEAR="${1}"
declare -i HISTORY_YEAR_OPEN
HISTORY_YEAR_OPEN=$(psql -d "${DBNAME_DWH}" -Atq \
-c "SELECT history_${YEAR}_open
FROM dwh.datamartCountries
WHERE dimension_country_id = ${COUNTRY_ID}
" \
-v ON_ERROR_STOP=1)
declare -i HISTORY_YEAR_COMMENTED
HISTORY_YEAR_COMMENTED=$(psql -d "${DBNAME_DWH}" -Atq \
-c "SELECT history_${YEAR}_commented
FROM dwh.datamartCountries
WHERE dimension_country_id = ${COUNTRY_ID}
" \
-v ON_ERROR_STOP=1)
declare -i HISTORY_YEAR_CLOSED
HISTORY_YEAR_CLOSED=$(psql -d "${DBNAME_DWH}" -Atq \
-c "SELECT history_${YEAR}_closed
FROM dwh.datamartCountries
WHERE dimension_country_id = ${COUNTRY_ID}
" \
-v ON_ERROR_STOP=1)
declare -i HISTORY_YEAR_CLOSED_WITH_COMMENT
HISTORY_YEAR_CLOSED_WITH_COMMENT=$(psql -d "${DBNAME_DWH}" -Atq \
-c "SELECT history_${YEAR}_closed_with_comment
FROM dwh.datamartCountries
WHERE dimension_country_id = ${COUNTRY_ID}
" \
-v ON_ERROR_STOP=1)
declare -i HISTORY_YEAR_REOPENED
HISTORY_YEAR_REOPENED=$(psql -d "${DBNAME_DWH}" -Atq \
-c "SELECT history_${YEAR}_reopened
FROM dwh.datamartCountries
WHERE dimension_country_id = ${COUNTRY_ID}
" \
-v ON_ERROR_STOP=1)
printf "${YEAR}: %9d %9d %9d %9d %9d\n" "${HISTORY_YEAR_OPEN}" "${HISTORY_YEAR_COMMENTED}" "${HISTORY_YEAR_CLOSED}" "${HISTORY_YEAR_CLOSED_WITH_COMMENT}" "${HISTORY_YEAR_REOPENED}"
__log_finish
}
# Prints a given ranking in a better way.
function __printRanking {
__log_start
RANKING=${1}
echo "${RANKING}" | sed 's/}, {/\n/g' | sed 's/^\[{//' | sed 's/}\]//' | sed 's/"rank" ://g' | sed 's/, "country_name" : "/ - /g' | sed 's/, "username" : "/ - /g' | sed 's/", "quantity" :/:/g'
__log_finish
}
# Shows the historic yearly rankings when the user has contributed the most.
function __showRankingYearUsers {
__log_start
YEAR="${1}"
declare RANKING_OPENING
RANKING_OPENING=$(psql -d "${DBNAME_DWH}" -Atq \
-c "SELECT ranking_countries_opening_${YEAR}
FROM dwh.datamartUsers
WHERE dimension_user_id = ${DIMENSION_USER_ID}
" \
-v ON_ERROR_STOP=1)
declare RANKING_CLOSING
RANKING_CLOSING=$(psql -d "${DBNAME_DWH}" -Atq \
-c "SELECT ranking_countries_closing_${YEAR}
FROM dwh.datamartUsers
WHERE dimension_user_id = ${DIMENSION_USER_ID}
" \
-v ON_ERROR_STOP=1)
echo
echo "Countries for opened notes on ${YEAR}:"
__printRanking "${RANKING_OPENING}"
echo "Countries for closed notes on ${YEAR}:"
__printRanking "${RANKING_CLOSING}"
__log_finish
}
# Shows the historic yearly rankings of which users contributed the most.
function __showRankingYearCountries {
__log_start
YEAR="${1}"
declare RANKING_OPENING
RANKING_OPENING=$(psql -d "${DBNAME_DWH}" -Atq \
-c "SELECT ranking_users_opening_${YEAR}
FROM dwh.datamartCountries
WHERE dimension_country_id = ${COUNTRY_ID}
" \
-v ON_ERROR_STOP=1)
declare RANKING_CLOSING
RANKING_CLOSING=$(psql -d "${DBNAME_DWH}" -Atq \
-c "SELECT ranking_users_closing_${YEAR}
FROM dwh.datamartCountries
WHERE dimension_country_id = ${COUNTRY_ID}
" \
-v ON_ERROR_STOP=1)
echo
echo "Users creating notes on ${YEAR}:"
__printRanking "${RANKING_OPENING}"
echo "Users closing notes on ${YEAR}:"
__printRanking "${RANKING_CLOSING}"
__log_finish
}
# Prints the hour of the week.
function __processHourWeek {
__log_start
HOUR=${1}
DOW=${2}
NUMBER=$(echo "${WEEK}" | grep "\"day_of_week\":${DOW},\"hour_of_day\":${HOUR}," \
| awk -F: '{print $4}' | sed 's/}, //' | sed 's/}\]//')
printf "%5d" "${NUMBER}"
__log_finish
}
# Shows the week hours in a better fashion.
function __showWorkingWeek {
__log_start
WEEK=${1}
declare HOUR_0=" 0h"
declare HOUR_1=" 1h"
declare HOUR_2=" 2h"
declare HOUR_3=" 3h"
declare HOUR_4=" 4h"
declare HOUR_5=" 5h"
declare HOUR_6=" 6h"
declare HOUR_7=" 7h"
declare HOUR_8=" 8h"
declare HOUR_9=" 9h"
declare HOUR_10="10h"
declare HOUR_11="11h"
declare HOUR_12="12h"
declare HOUR_13="13h"
declare HOUR_14="14h"
declare HOUR_15="15h"
declare HOUR_16="16h"
declare HOUR_17="17h"
declare HOUR_18="18h"
declare HOUR_19="19h"
declare HOUR_20="20h"
declare HOUR_21="21h"
declare HOUR_22="22h"
declare HOUR_23="23h"
I=1
set +e
while [[ "${I}" -le 7 ]]; do
HOUR_0="${HOUR_0} - $(__processHourWeek 0 "${I}")"
HOUR_1="${HOUR_1} - $(__processHourWeek 1 "${I}")"
HOUR_2="${HOUR_2} - $(__processHourWeek 2 "${I}")"
HOUR_3="${HOUR_3} - $(__processHourWeek 3 "${I}")"
HOUR_4="${HOUR_4} - $(__processHourWeek 4 "${I}")"
HOUR_5="${HOUR_5} - $(__processHourWeek 5 "${I}")"
HOUR_6="${HOUR_6} - $(__processHourWeek 6 "${I}")"
HOUR_7="${HOUR_7} - $(__processHourWeek 7 "${I}")"
HOUR_8="${HOUR_8} - $(__processHourWeek 8 "${I}")"
HOUR_9="${HOUR_9} - $(__processHourWeek 9 "${I}")"
HOUR_10="${HOUR_10} - $(__processHourWeek 10 "${I}")"
HOUR_11="${HOUR_11} - $(__processHourWeek 11 "${I}")"
HOUR_12="${HOUR_12} - $(__processHourWeek 12 "${I}")"
HOUR_13="${HOUR_13} - $(__processHourWeek 13 "${I}")"
HOUR_14="${HOUR_14} - $(__processHourWeek 14 "${I}")"
HOUR_15="${HOUR_15} - $(__processHourWeek 15 "${I}")"
HOUR_16="${HOUR_16} - $(__processHourWeek 16 "${I}")"
HOUR_17="${HOUR_17} - $(__processHourWeek 17 "${I}")"
HOUR_18="${HOUR_18} - $(__processHourWeek 18 "${I}")"
HOUR_19="${HOUR_19} - $(__processHourWeek 19 "${I}")"
HOUR_20="${HOUR_20} - $(__processHourWeek 20 "${I}")"
HOUR_21="${HOUR_21} - $(__processHourWeek 21 "${I}")"
HOUR_22="${HOUR_22} - $(__processHourWeek 22 "${I}")"
HOUR_23="${HOUR_23} - $(__processHourWeek 23 "${I}")"
((I += 1))
done
set -e
echo " Sun - Mon - Tue - Wed - Thu - Fri - Sat"
echo "${HOUR_0}"
echo "${HOUR_1}"
echo "${HOUR_2}"
echo "${HOUR_3}"
echo "${HOUR_4}"
echo "${HOUR_5}"
echo "${HOUR_6}"
echo "${HOUR_7}"
echo "${HOUR_8}"
echo "${HOUR_9}"
echo "${HOUR_10}"
echo "${HOUR_11}"
echo "${HOUR_12}"
echo "${HOUR_13}"
echo "${HOUR_14}"
echo "${HOUR_15}"
echo "${HOUR_16}"
echo "${HOUR_17}"
echo "${HOUR_18}"
echo "${HOUR_19}"
echo "${HOUR_20}"
echo "${HOUR_21}"
echo "${HOUR_22}"
echo "${HOUR_23}"
__log_finish
}
# Shows the activity as GitHub tiles.
function __printActivity {
__log_start
ACTIVITY="${1}"
# Truncate activity to last 53 weeks (371 days) to avoid showing old weeks with zeros
# Each week has 7 days, so 53 weeks = 371 characters
declare ACTIVITY_LENGTH
ACTIVITY_LENGTH=${#ACTIVITY}
declare -i MAX_WEEKS=53
declare -i MAX_CHARS=$((MAX_WEEKS * 7))
if [[ ${ACTIVITY_LENGTH} -gt ${MAX_CHARS} ]]; then
# Take only the last MAX_CHARS characters (most recent weeks)
ACTIVITY="${ACTIVITY: -${MAX_CHARS}}"
fi
# Get current day of week (0=Sunday, 1=Monday, ..., 6=Saturday)
declare -i CURRENT_DOW
CURRENT_DOW=$(date +%w)
# Rotate activity string to start from current day of week
# The activity string starts from Sunday, so we need to rotate it
declare ROTATED_ACTIVITY
if [[ ${CURRENT_DOW} -gt 0 ]]; then
# Rotate: move first CURRENT_DOW characters to the end
ROTATED_ACTIVITY="${ACTIVITY:${CURRENT_DOW}}${ACTIVITY:0:${CURRENT_DOW}}"
else
ROTATED_ACTIVITY="${ACTIVITY}"
fi
# Create day labels starting from current day
declare DAY_LABELS=("Sunday: " "Monday: " "Tuesday: " "Wednesday: " "Thursday: " "Friday: " "Saturday: ")
declare DAY_VARS=("SUN" "MON" "TUE" "WED" "THU" "FRI" "SAT")
# Initialize day variables starting from current day
declare -i DAY_INDEX
for DAY_INDEX in {0..6}; do
declare ACTUAL_INDEX=$(((DAY_INDEX + CURRENT_DOW) % 7))
declare VAR_NAME="${DAY_VARS[${ACTUAL_INDEX}]}"
declare "${VAR_NAME}"="${DAY_LABELS[${ACTUAL_INDEX}]}"
done
# Calculate number of weeks to display based on available data
declare -i WEEKS_TO_SHOW
WEEKS_TO_SHOW=$((${#ROTATED_ACTIVITY} / 7))
if [[ ${WEEKS_TO_SHOW} -gt ${MAX_WEEKS} ]]; then
WEEKS_TO_SHOW=${MAX_WEEKS}
fi
I=1
set +e
while [[ ${I} -le ${WEEKS_TO_SHOW} ]] && [[ ${#ROTATED_ACTIVITY} -ge 7 ]]; do
for DAY_INDEX in {0..6}; do
ACTUAL_INDEX=$(((DAY_INDEX + CURRENT_DOW) % 7))
VAR_NAME="${DAY_VARS[${ACTUAL_INDEX}]}"
DAY="${ROTATED_ACTIVITY:0:1}"
ROTATED_ACTIVITY="${ROTATED_ACTIVITY:1}"
declare "${VAR_NAME}"="${!VAR_NAME}${DAY}"
done
I=$((I + 1))
done
set -e
# Print days starting from current day
for DAY_INDEX in {0..6}; do
ACTUAL_INDEX=$(((DAY_INDEX + CURRENT_DOW) % 7))
VAR_NAME="${DAY_VARS[${ACTUAL_INDEX}]}"
echo "${!VAR_NAME}"
done
__log_finish
}
# Shows the user profile.
function __processUserProfile {
__log_start
declare -i OSM_USER_ID
OSM_USER_ID=$(psql -d "${DBNAME_DWH}" -Atq \
-c "SELECT user_id
FROM dwh.datamartUsers
WHERE dimension_user_id = ${DIMENSION_USER_ID}
" \
-v ON_ERROR_STOP=1)
# Quantity of days creating notes
declare -i QTY_DAYS_OPEN
QTY_DAYS_OPEN=$(psql -d "${DBNAME_DWH}" -Atq \
-c "SELECT CURRENT_DATE - date_starting_creating_notes
FROM dwh.datamartUsers
WHERE dimension_user_id = ${DIMENSION_USER_ID}
" \
-v ON_ERROR_STOP=1)
declare DATE_FIRST_OPEN
DATE_FIRST_OPEN=$(psql -d "${DBNAME_DWH}" -Atq \
-c "SELECT date_starting_creating_notes
FROM dwh.datamartUsers
WHERE dimension_user_id = ${DIMENSION_USER_ID}
" \
-v ON_ERROR_STOP=1)
# Quantity of days solving notes.
declare -i QTY_DAYS_CLOSE
QTY_DAYS_CLOSE=$(psql -d "${DBNAME_DWH}" -Atq \
-c "SELECT CURRENT_DATE - date_starting_solving_notes
FROM dwh.datamartUsers
WHERE dimension_user_id = ${DIMENSION_USER_ID}
" \
-v ON_ERROR_STOP=1)
declare DATE_FIRST_CLOSE
DATE_FIRST_CLOSE=$(psql -d "${DBNAME_DWH}" -Atq \
-c "SELECT date_starting_solving_notes
FROM dwh.datamartUsers
WHERE dimension_user_id = ${DIMENSION_USER_ID}
" \
-v ON_ERROR_STOP=1)
# Check if user may have stopped opening or closing notes
declare DAYS_SINCE_LAST_ACTION
DAYS_SINCE_LAST_ACTION=$(psql -d "${DBNAME_DWH}" -Atq \
-c "SELECT COALESCE(days_since_last_action, 0)
FROM dwh.datamartUsers
WHERE dimension_user_id = ${DIMENSION_USER_ID}
" \
-v ON_ERROR_STOP=1 2> /dev/null || echo "0")
# First actions.
declare -i FIRST_OPEN_NOTE_ID
FIRST_OPEN_NOTE_ID=$(psql -d "${DBNAME_DWH}" -Atq \
-c "SELECT first_open_note_id
FROM dwh.datamartUsers
WHERE dimension_user_id = ${DIMENSION_USER_ID}
" \
-v ON_ERROR_STOP=1)
declare -i FIRST_COMMENTED_NOTE_ID
FIRST_COMMENTED_NOTE_ID=$(psql -d "${DBNAME_DWH}" -Atq \
-c "SELECT first_commented_note_id
FROM dwh.datamartUsers
WHERE dimension_user_id = ${DIMENSION_USER_ID}
" \
-v ON_ERROR_STOP=1)
declare -i FIRST_CLOSED_NOTE_ID
FIRST_CLOSED_NOTE_ID=$(psql -d "${DBNAME_DWH}" -Atq \
-c "SELECT first_closed_note_id
FROM dwh.datamartUsers
WHERE dimension_user_id = ${DIMENSION_USER_ID}
" \
-v ON_ERROR_STOP=1)
declare -i FIRST_REOPENED_NOTE_ID
FIRST_REOPENED_NOTE_ID=$(psql -d "${DBNAME_DWH}" -Atq \
-c "SELECT first_reopened_note_id
FROM dwh.datamartUsers
WHERE dimension_user_id = ${DIMENSION_USER_ID}
" \
-v ON_ERROR_STOP=1)
# Type of contributor.
declare CONTRIBUTOR_TYPE
CONTRIBUTOR_TYPE=$(psql -d "${DBNAME_DWH}" -Atq \
-c "SELECT contributor_type_name
FROM dwh.datamartUsers u
JOIN dwh.contributor_types t
ON u.id_contributor_type = t.contributor_type_id
WHERE dimension_user_id = ${DIMENSION_USER_ID}
" \
-v ON_ERROR_STOP=1)
# Experience level (newcomer -> legend).
declare EXPERIENCE_LEVEL
EXPERIENCE_LEVEL=$(psql -d "${DBNAME_DWH}" -Atq \
-c "SELECT el.experience_level
FROM dwh.dimension_users u
JOIN dwh.dimension_experience_levels el
ON u.experience_level_id = el.dimension_experience_id
WHERE u.dimension_user_id = ${DIMENSION_USER_ID}
" \
-v ON_ERROR_STOP=1 2> /dev/null || echo "")
# Last activity year.
declare LAST_ACTIVITY_YEAR
LAST_ACTIVITY_YEAR=$(psql -d "${DBNAME_DWH}" -Atq \
-c "SELECT last_year_activity
FROM dwh.datamartUsers
WHERE dimension_user_id = ${DIMENSION_USER_ID}
" \
-v ON_ERROR_STOP=1)
# Most recent actions.
declare -i LAST_OPEN_NOTE_ID
LAST_OPEN_NOTE_ID=$(psql -d "${DBNAME_DWH}" -Atq \
-c "SELECT latest_open_note_id
FROM dwh.datamartUsers
WHERE dimension_user_id = ${DIMENSION_USER_ID}
" \
-v ON_ERROR_STOP=1)
declare -i LAST_COMMENTED_NOTE_ID
LAST_COMMENTED_NOTE_ID=$(psql -d "${DBNAME_DWH}" -Atq \
-c "SELECT latest_commented_note_id
FROM dwh.datamartUsers
WHERE dimension_user_id = ${DIMENSION_USER_ID}
" \
-v ON_ERROR_STOP=1)
declare -i LAST_CLOSED_NOTE_ID
LAST_CLOSED_NOTE_ID=$(psql -d "${DBNAME_DWH}" -Atq \
-c "SELECT latest_closed_note_id
FROM dwh.datamartUsers
WHERE dimension_user_id = ${DIMENSION_USER_ID}
" \
-v ON_ERROR_STOP=1)
declare -i LAST_REOPENED_NOTE_ID
LAST_REOPENED_NOTE_ID=$(psql -d "${DBNAME_DWH}" -Atq \
-c "SELECT latest_reopened_note_id
FROM dwh.datamartUsers
WHERE dimension_user_id = ${DIMENSION_USER_ID}
" \
-v ON_ERROR_STOP=1)
# Dates with more opened notes.
declare DATES_MOST_OPEN
DATES_MOST_OPEN=$(psql -d "${DBNAME_DWH}" -Atq \
-c "SELECT dates_most_open
FROM dwh.datamartUsers
WHERE dimension_user_id = ${DIMENSION_USER_ID}
" \
-v ON_ERROR_STOP=1)
# Dates with more closed notes
declare DATES_MOST_CLOSED
DATES_MOST_CLOSED=$(psql -d "${DBNAME_DWH}" -Atq \
-c "SELECT dates_most_closed
FROM dwh.datamartUsers
WHERE dimension_user_id = ${DIMENSION_USER_ID}
" \
-v ON_ERROR_STOP=1)
# Used hashtags - extracted from note text and stored as JSON array
# Format: [{"hashtag": "tag1", "count": 5}, {"hashtag": "tag2", "count": 3}]
declare HASHTAGS
HASHTAGS=$(psql -d "${DBNAME_DWH}" -Atq \
-c "SELECT hashtags
FROM dwh.datamartUsers
WHERE dimension_user_id = ${DIMENSION_USER_ID}
" \
-v ON_ERROR_STOP=1)
# DM-002: Hashtags by action type
# shellcheck disable=SC2034
# SC2034: These variables are used in the hashtag display section below
declare HASHTAGS_OPENING
HASHTAGS_OPENING=$(psql -d "${DBNAME_DWH}" -Atq \
-c "SELECT hashtags_opening
FROM dwh.datamartUsers
WHERE dimension_user_id = ${DIMENSION_USER_ID}
" \
-v ON_ERROR_STOP=1 2> /dev/null || echo "[]")
# shellcheck disable=SC2034
declare HASHTAGS_RESOLUTION
HASHTAGS_RESOLUTION=$(psql -d "${DBNAME_DWH}" -Atq \
-c "SELECT hashtags_resolution
FROM dwh.datamartUsers
WHERE dimension_user_id = ${DIMENSION_USER_ID}
" \
-v ON_ERROR_STOP=1 2> /dev/null || echo "[]")
# shellcheck disable=SC2034
declare HASHTAGS_COMMENTS
HASHTAGS_COMMENTS=$(psql -d "${DBNAME_DWH}" -Atq \
-c "SELECT hashtags_comments
FROM dwh.datamartUsers
WHERE dimension_user_id = ${DIMENSION_USER_ID}
" \
-v ON_ERROR_STOP=1 2> /dev/null || echo "[]")
# shellcheck disable=SC2034
declare FAVORITE_OPENING_HASHTAG
FAVORITE_OPENING_HASHTAG=$(psql -d "${DBNAME_DWH}" -Atq \
-c "SELECT favorite_opening_hashtag
FROM dwh.datamartUsers
WHERE dimension_user_id = ${DIMENSION_USER_ID}
" \
-v ON_ERROR_STOP=1 2> /dev/null || echo "")
# shellcheck disable=SC2034
declare FAVORITE_RESOLUTION_HASHTAG
FAVORITE_RESOLUTION_HASHTAG=$(psql -d "${DBNAME_DWH}" -Atq \
-c "SELECT favorite_resolution_hashtag
FROM dwh.datamartUsers
WHERE dimension_user_id = ${DIMENSION_USER_ID}
" \
-v ON_ERROR_STOP=1 2> /dev/null || echo "")
# shellcheck disable=SC2034
declare -i OPENING_HASHTAG_COUNT
OPENING_HASHTAG_COUNT=$(psql -d "${DBNAME_DWH}" -Atq \
-c "SELECT COALESCE(opening_hashtag_count, 0)
FROM dwh.datamartUsers
WHERE dimension_user_id = ${DIMENSION_USER_ID}
" \
-v ON_ERROR_STOP=1 2> /dev/null || echo "0")
# shellcheck disable=SC2034
declare -i RESOLUTION_HASHTAG_COUNT
RESOLUTION_HASHTAG_COUNT=$(psql -d "${DBNAME_DWH}" -Atq \
-c "SELECT COALESCE(resolution_hashtag_count, 0)
FROM dwh.datamartUsers
WHERE dimension_user_id = ${DIMENSION_USER_ID}
" \
-v ON_ERROR_STOP=1 2> /dev/null || echo "0")
# Calculate hashtag statistics if available
declare -i TOTAL_HASHTAG_USES=0
declare -i UNIQUE_HASHTAGS=0
if [[ -n "${HASHTAGS}" ]] && [[ "${HASHTAGS}" != "" ]] && [[ "${HASHTAGS}" != "[]" ]] && [[ "${HASHTAGS}" != "null" ]]; then
if command -v jq &> /dev/null; then
TOTAL_HASHTAG_USES=$(echo "${HASHTAGS}" | jq '[.[] | .count] | add // 0' 2> /dev/null || echo "0")
UNIQUE_HASHTAGS=$(echo "${HASHTAGS}" | jq 'length' 2> /dev/null || echo "0")
else
# Fallback: count manually from JSON
UNIQUE_HASHTAGS=$(echo "${HASHTAGS}" | grep -o '"hashtag"' | wc -l || echo "0")
fi
fi
# Countries opening notes.
declare COUNTRIES_OPENING
COUNTRIES_OPENING=$(psql -d "${DBNAME_DWH}" -Atq \
-c "SELECT countries_open_notes
FROM dwh.datamartUsers
WHERE dimension_user_id = ${DIMENSION_USER_ID}
" \
-v ON_ERROR_STOP=1)
# Countries closing notes.
declare COUNTRIES_CLOSING
COUNTRIES_CLOSING=$(psql -d "${DBNAME_DWH}" -Atq \
-c "SELECT countries_solving_notes
FROM dwh.datamartUsers
WHERE dimension_user_id = ${DIMENSION_USER_ID}
" \
-v ON_ERROR_STOP=1)
# Countries opening notes current month.
declare COUNTRIES_OPENING_CURRENT_MONTH
COUNTRIES_OPENING_CURRENT_MONTH=$(psql -d "${DBNAME_DWH}" -Atq \
-c "SELECT countries_open_notes_current_month
FROM dwh.datamartUsers
WHERE dimension_user_id = ${DIMENSION_USER_ID}
" \
-v ON_ERROR_STOP=1)
# Countries closing notes current month.
declare COUNTRIES_CLOSING_CURRENT_MONTH
COUNTRIES_CLOSING_CURRENT_MONTH=$(psql -d "${DBNAME_DWH}" -Atq \
-c "SELECT countries_solving_notes_current_month
FROM dwh.datamartUsers
WHERE dimension_user_id = ${DIMENSION_USER_ID}
" \
-v ON_ERROR_STOP=1)
# Countries opening notes current day.
declare COUNTRIES_OPENING_CURRENT_DAY
COUNTRIES_OPENING_CURRENT_DAY=$(psql -d "${DBNAME_DWH}" -Atq \
-c "SELECT countries_open_notes_current_day
FROM dwh.datamartUsers
WHERE dimension_user_id = ${DIMENSION_USER_ID}
" \
-v ON_ERROR_STOP=1)
# Countries closing notes current day.
declare COUNTRIES_CLOSING_CURRENT_DAY
COUNTRIES_CLOSING_CURRENT_DAY=$(psql -d "${DBNAME_DWH}" -Atq \
-c "SELECT countries_solving_notes_current_day
FROM dwh.datamartUsers
WHERE dimension_user_id = ${DIMENSION_USER_ID}
" \
-v ON_ERROR_STOP=1)
# Working hours.
declare WORKING_HOURS_OPENING
WORKING_HOURS_OPENING=$(psql -d "${DBNAME_DWH}" -Atq \
-c "SELECT working_hours_of_week_opening
FROM dwh.datamartUsers
WHERE dimension_user_id = ${DIMENSION_USER_ID}
" \
-v ON_ERROR_STOP=1)
declare WORKING_HOURS_COMMENTING
WORKING_HOURS_COMMENTING=$(psql -d "${DBNAME_DWH}" -Atq \
-c "SELECT working_hours_of_week_commenting
FROM dwh.datamartUsers
WHERE dimension_user_id = ${DIMENSION_USER_ID}
" \
-v ON_ERROR_STOP=1)
declare WORKING_HOURS_CLOSING
WORKING_HOURS_CLOSING=$(psql -d "${DBNAME_DWH}" -Atq \
-c "SELECT working_hours_of_week_closing
FROM dwh.datamartUsers
WHERE dimension_user_id = ${DIMENSION_USER_ID}
" \
-v ON_ERROR_STOP=1)
# History values.
# Whole history.
declare -i HISTORY_WHOLE_OPEN
HISTORY_WHOLE_OPEN=$(psql -d "${DBNAME_DWH}" -Atq \
-c "SELECT history_whole_open
FROM dwh.datamartUsers
WHERE dimension_user_id = ${DIMENSION_USER_ID}
" \
-v ON_ERROR_STOP=1)
declare -i HISTORY_WHOLE_COMMENTED
HISTORY_WHOLE_COMMENTED=$(psql -d "${DBNAME_DWH}" -Atq \
-c "SELECT history_whole_commented
FROM dwh.datamartUsers
WHERE dimension_user_id = ${DIMENSION_USER_ID}
" \
-v ON_ERROR_STOP=1)
declare -i HISTORY_WHOLE_CLOSED
HISTORY_WHOLE_CLOSED=$(psql -d "${DBNAME_DWH}" -Atq \
-c "SELECT history_whole_closed
FROM dwh.datamartUsers
WHERE dimension_user_id = ${DIMENSION_USER_ID}
" \
-v ON_ERROR_STOP=1)
# History whole closed with comment (notes closed that include explanatory text)
# The datamart already filters for comments with non-empty text (LENGTH(TRIM(body)) > 0)
declare -i HISTORY_WHOLE_CLOSED_WITH_COMMENT
HISTORY_WHOLE_CLOSED_WITH_COMMENT=$(psql -d "${DBNAME_DWH}" -Atq \
-c "SELECT history_whole_closed_with_comment
FROM dwh.datamartUsers
WHERE dimension_user_id = ${DIMENSION_USER_ID}
" \
-v ON_ERROR_STOP=1)
declare -i HISTORY_WHOLE_REOPENED
HISTORY_WHOLE_REOPENED=$(psql -d "${DBNAME_DWH}" -Atq \
-c "SELECT history_whole_reopened
FROM dwh.datamartUsers
WHERE dimension_user_id = ${DIMENSION_USER_ID}
" \
-v ON_ERROR_STOP=1)
# Last year history.
declare -i HISTORY_YEAR_OPEN
HISTORY_YEAR_OPEN=$(psql -d "${DBNAME_DWH}" -Atq \
-c "SELECT history_year_open
FROM dwh.datamartUsers
WHERE dimension_user_id = ${DIMENSION_USER_ID}
" \
-v ON_ERROR_STOP=1)
declare -i HISTORY_YEAR_COMMENTED
HISTORY_YEAR_COMMENTED=$(psql -d "${DBNAME_DWH}" -Atq \
-c "SELECT history_year_commented
FROM dwh.datamartUsers
WHERE dimension_user_id = ${DIMENSION_USER_ID}
" \
-v ON_ERROR_STOP=1)
declare -i HISTORY_YEAR_CLOSED
HISTORY_YEAR_CLOSED=$(psql -d "${DBNAME_DWH}" -Atq \
-c "SELECT history_year_closed
FROM dwh.datamartUsers
WHERE dimension_user_id = ${DIMENSION_USER_ID}
" \
-v ON_ERROR_STOP=1)
# History year closed with comment (notes closed this year that include explanatory text)
declare -i HISTORY_YEAR_CLOSED_WITH_COMMENT
HISTORY_YEAR_CLOSED_WITH_COMMENT=$(psql -d "${DBNAME_DWH}" -Atq \
-c "SELECT history_year_closed_with_comment
FROM dwh.datamartUsers
WHERE dimension_user_id = ${DIMENSION_USER_ID}
" \
-v ON_ERROR_STOP=1)