1(iGXSSKrSSKrSSKrSSKrSSKrSSKJr SSKJ r "SS5r g)N)Config)Teamc|\rSrSrSr\S5r\S5r\SSj5r\SSj5r \S5r \SS j5r \SS j5r \SS j5r \SS j5r\SS j5r\SSj5r\SSj5r\S5r\S5r\S5r\SSj5r\SSj5r\SSj5r\S Sj5rSrg)! AttendanceLog z7Model for managing attendance log data in the database.cH[RR[R[R [R [R[RS9nU$![RRan[SU35 SnAgSnAff=f)z"Establishes a database connection.)hostportuserpassworddatabasez$Error connecting to MySQL Database: N) mysql connectorconnectrDB_HOSTDB_PORTDB_USER DB_PASSWORDDB_NAMEErrorprint)connes //var/www/fran/franai/models/attendance_model.pyget_db_connectionAttendanceLog.get_db_connection sy ??**^^^^^^++ +DK$$  8< = sA(A++B! BB!c [R5nU(dgUR5nURS5 UR 5 [ S5 UR5(a!WR5 UR5 gg![ RRan[ SU35 SnANhSnAff=f!UR5(a!WR5 UR5 ff=f)z6Creates the attendance_logs table if it doesn't exist.Na- CREATE TABLE IF NOT EXISTS attendance_logs ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, pid INT UNSIGNED NOT NULL, recognition_timestamp DATETIME NOT NULL, status VARCHAR(50) NOT NULL, location_id INT UNSIGNED NULL, similarity FLOAT NULL, UNIQUE KEY unique_attendance (pid, recognition_timestamp), FOREIGN KEY (location_id) REFERENCES locations(id) ) z@[OK] 'attendance_logs' table exists or was successfully created.z(Error creating 'attendance_logs' table: ) rrcursorexecutecommitrrrr is_connectedclose)rrrs rcreate_table_if_not_exists(AttendanceLog.create_table_if_not_existss..0  [[]F NN   KKM T U  ""  #$$ B ! A B, , [[]FqE   u 8 KKM#__N 5c:K6L5MMdesdtt}~ ,   ""  #/' 2! !3106#  ,QC0 1  $$ " :1#> ? MMO! !  ""  #  "   ""  #s GG /F3G  ,F35G 7AF3>G G!AH3GG GG  GGGH6H  HJ/J JJ JJ8K c [RRU5(d[SUS35 g[R 5nU(dg/n[ USSS9n[R"U5nUHfnURS5(dMURS5(dM3UR[US5USURS S 545 Mh S S S 5 U(dgS nUR5nSn URX5 UR!5 UR"n[S[%U5SUS35 [%U5U4UR'5(a!UR)5 UR)5 $$!,(df  N=f![[R4an[S U35 S nAgS nAff=f![*R,Ra[n[S[.SU35 [0sS nAUR'5(a!WR)5 UR)5 $$S nAff=f!UR'5(a!WR)5 UR)5 ff=f)zy Synchronizes records from the attendance CSV file to the database. Uses 'INSERT IGNORE' to efficiently skip duplicates. zInfo: CSV file not found at z. Skipping sync.)rrr&r'r(r+r*r,r-Nr/rz INSERT IGNORE INTO attendance_logs (pid, recognition_timestamp, status) VALUES (%s, %s, %s) u 🔄 Attendance Sync: Processed z records. Inserted z new records.*Error fetching attendance status for user : )r0r1r2rrrr3r4r5r6r9r:r=rrr>r r?r@r!r"rruser_idattendance_info) rBrrDrFrGrHrrLrrKs r sync_from_csvAttendanceLog.sync_from_csvs% ww~~h'' 0 :JK L..0 hR0D-!Cwwy))cggk.B.B)00I/ ,GGHj92"1! [[]FE   u 8 KKM#__N 4S9J5K4LL_`n_oo|} ~()>9   ""  #G10#  ,QC0 1 *$$ # >wir!M N" "  ""  #  #  ""  #ss F#0FF+7F"F#5A&G F F# F##G=GGI6I II II8J cSSS.n[R5nU(dU$URSS9nSnURX@45 UR 5nU(aSUS'USnUR S 5US 'UUR 5(a!UR5 UR5 $$![RRaSn[S US U35 UsSnAUR 5(a!WR5 UR5 $$SnAff=f!UR 5(a!WR5 UR5 ff=f) z Checks the database for a 'Check-in' record for a specific user for the current day. Returns the status and the check-in time. FN)has_attendance check_in_timeT dictionarya6 SELECT recognition_timestamp FROM attendance_logs WHERE pid = %s AND status = 'Check-in' AND DATE(recognition_timestamp) = CURDATE() ORDER BY recognition_timestamp DESC LIMIT 1 rWrecognition_timestampz %I:%M:%S %prXrPrQ) rrrrfetchonestrftimer!r"rrrr)rRrSrrrKresult timestamprs rget_status_for_user!AttendanceLog.get_status_for_users= .3TJ..0" " [[D[1FE NN5* -__&F48 01"#:; 3<3E3Em3T0"   ""  # $$ # >wir!M N" "  ""  #  #  ""  #s0AB44D%D $D%%D( D%%D((8E c[R5nU(dgUR5nSnURXpX!X445 UR 5 UR 5(a!UR 5 UR 5 gg![RRaan[SUSU35 UR5 SnAUR 5(a!WR 5 UR 5 ggSnAff=f!UR 5(a!WR 5 UR 5 ff=f)zt Adds a new attendance record to the database with a specific timestamp. Returns True on success, False on failure. Fz INSERT INTO attendance_logs (pid, recognition_timestamp, status, location_id, similarity) VALUES (%s, %s, %s, %s, %s) Tz$Error adding attendance log for PID rQN) rrrrr r!r"rrrrrA) pidstatusr_ location_id similarityrrrKrs radd_logAttendanceLog.add_logs ..0 [[]FE NN5 ;"S T KKM   ""  # $$  8RsC D MMO  ""  #     ""  #s)6B D *!D DD  D8Ec[RU5nUS(dSnOSn[R"5R SS9n[R XXQU5(aSUR 5S3nXFU4$g) z Determines the attendance status (Check-in/Check-out) and adds a new record. Returns the status, a message, and the timestamp used. rWr-z Check-outr) microsecondzStaff z successfully)NzFailed to log attendanceN)rr`r get_ph_timereplacerglower)rcrerf status_infordr_messages r add_recordAttendanceLog.add_records{ $77< +,F F&&(00Q0?  ij Q Qv||~.m= %s 00:00:00al.recognition_timestamp <= %s 23:59:59.CONCAT(s.first_name, ' ', s.last_name) LIKE %s% WHERE  AND z' ORDER BY al.recognition_timestamp DESC LIMIT %s OFFSET %sz$Error fetching all attendance data: rrrr9joinextendrtuplefetchallr!r"rrrr) start_dateend_datepagelimit exporting search_termrrparamsrK where_clausesoffsetresultsrs rget_all_attendance_with_details-AttendanceLog.get_all_attendance_with_detailss ..03 [[D[1FFE M$$%EF  I67$$%EF  )45$$%UV +a01W\\-%@@@ > >E(e+.. uo. NN5%- 0oo'G   ""  # $$  8< =  ""  #    ""  #s*DE$$GG G GG8H c[R5nU(dgUR5n/nSn/nU(a%URS5 URUS35 U(a%URS5 URUS35 U(a&URS5 URSUS35 U(aUS S R U5-- nUR U[ U55 UR5SnUUR5(a!UR5 UR5 $$!UR5(a!WR5 UR5 ff=f) zW Counts the total number of raw attendance logs, supporting date filtering and search. rzhSELECT COUNT(*) FROM attendance_logs al LEFT JOIN hris_core.employee_information_tbl s ON al.pid = s.pidrtrurvrwrxryrzr{) rrrr9rrrr\r!r") rrrrrrrKrcounts rget_total_logs_count"AttendanceLog.get_total_logs_countPs6 ..0 [[]FF~EM$$%EF  I67$$%EF  )45$$%UV +a01W\\-%@@@ NN5%- 0OO%a(E  ""  #t  ""  #s C(D==8E5cn[R5nU(dgURSS9nURS5 UR 5nU(aUSOSn/nSn US:XaS U S 3n OS U S 3n /n U(a%U R S 5 UR US 35 U(a%U R S5 UR US35 U(a&U R S5 UR SUS35 U (aU SSR U 5-- n URU [U55 UR 5n U (aX|S'OSSUS.n U UR5(a!UR5 UR5 $$![RRaNn [SU 35 Sn A UR5(a!WR5 UR5 ggSn A ff=f!UR5(a!WR5 UR5 ff=f)z~ Calculates summary statistics. The unique_users count is always the total number of registered users, regardless of filters. NTrYz6SELECT COUNT(pid) as total_users FROM registered_users total_usersrzXFROM attendance_logs al LEFT JOIN hris_core.employee_information_tbl s ON al.pid = s.pidlogsa  SELECT COALESCE(SUM(CASE WHEN status = 'Check-in' THEN 1 ELSE 0 END), 0) AS checkin_count, COALESCE(SUM(CASE WHEN status = 'Check-out' THEN 1 ELSE 0 END), 0) AS checkout_count z a- SELECT COUNT(DISTINCT CONCAT(al.pid, DATE(al.recognition_timestamp))) AS checkin_count, COUNT(DISTINCT CASE WHEN al.status = 'Check-out' THEN CONCAT(al.pid, DATE(al.recognition_timestamp)) END) AS checkout_count rtrurvrwrxryrzr{ unique_users) checkin_countcheckout_countrz"Error getting attendance summary: )rrrrr\r9rrr!r"rrrr)rrviewrrrtotal_users_resulttotal_unique_usersr from_joinrKrsummaryrs rget_attendance_summary$AttendanceLog.get_attendance_summaryts ..09 [[D[1F NNS T!'!2 FX!3M!B^_ FrIv~K  K  M$$%EF  I67$$%EF  )45$$%UV +a01W\\-%@@@ NN5%- 0oo'G*<',-Tfg   ""  # $$  6qc: ;  ""  #    ""  #s*D8F G9+G49G<4G99G<<8H4ct[R5nU(dgURSS9n/n Sn /n U(a%U RS5 U RUS35 U(a%U RS5 U RUS35 U(a&U RS 5 U RS US 35 U(a"U RS 5 U RU5 U (aU S S R U 5-- n U S- n U S- n U(dUS- U-n U S- n U R X= %s AND similarity IS NOT NULL z*Error fetching recognition scores for PID z since rQNr)rcrrrrKrrs r!get_user_recognition_scores_since/AttendanceLog.get_user_recognition_scores_sinceus ..0:I [[D[1FE NN5 "3 4__&Fse7:,VXYZX[\ ]I  ""  #    ""  #s/>BD:D DD DD8E c[R5nU(dgSSSSS.nSSKJn UR SS9nSnUR U5 UR 5n[S[U5S 35 S nUS 3US 34n UR X5 UR 5n [U 5US 'U (asU"[5n U H6n U SR5Rn XRU S5 M8 U (a$[SU R555nXS'[SUS 35 [SUS35 U(aU(a[RR!US5n[RR!US5nSnUnUU::a8UR#5S:XaUS- nU[R$"SS9- nUU::aM8UR&UR&- S-UR(UR(- -S-nSnUH/nSnUS:XaUU- nMUS:Xa USU-- nM"US;dM*UU- nM1 UUS'USS:a[/US US- S-5US'[SU35 UUR15(a!UR35 UR35 $$![*[,4a SUS'Nf=f![4R6R8aNn[S U35 SnAUR15(a!WR35 UR35 ggSnAff=f!UR15(a!WR35 UR35 ff=f)!z Calculates the quick view summary. - Unique FTE is the average number of unique employees who check in per week. - Required Visits is based on the total number of hired employees. - Assumes a static 'Hybrid 1' (once per week) schedule. Nr) total_visits unique_fterequired_visits performance) defaultdictTrYzISELECT pid FROM hris_core.employee_information_tbl WHERE status = 'Hired'z DEBUG: Found z hired employees from HRIS.af SELECT DISTINCT al.pid, DATE(al.recognition_timestamp) AS attendance_date FROM attendance_logs al WHERE al.recognition_timestamp >= %s AND al.recognition_timestamp <= %s AND al.status = 'Check-in' rurwrattendance_datercc38# UHn[U5v M g7f)N)r@).0pidss r 7AttendanceLog.get_quick_view_summary..s1]H\#d))H\srz.DEBUG: Total Visits (unique daily check-ins): z$DEBUG: Sum of unique FTEs per week: %Y-%m-%dr|)days zHybrid 1zHybrid 2)zHybrid 3zHybrid 4rdrz%DEBUG: Final summary to be returned: z"Error getting quick view summary: )rr collectionsrrrrrr@set isocalendarweekaddsumvaluesr7r8weekday timedeltayearmonthr;r<roundr!r"rrr)rrr summary_datarrhired_employees_queryhired_employees actuals_queryrcheck_in_records weekly_ftesrecord week_numbertotal_weekly_unique_ftestartend num_weeks current_day num_monthstotal_required_visitsempsetuprs rget_quick_view_summary$AttendanceLog.get_quick_view_summarys..0()qabc V  /[[D[1F%p ! NN0 1$oo/O M#o"6!77RS T M$ I.8*I0FGF NN= 1%0 +./?+@L ( )#. .F"():";"G"G"I"N"NK,00?/.11] HZHZH\1].]+1H. B1"j01a*nE1"&>>1Z?1 /7LL!23 -.2.3\.5QT`arTs5swz4z.{ ]+ 9,H I   ""  ##I.867L!238$$  6qc: ;  ""  #    ""  #s\D5KBJ;A#J;J;2K;KKKKM4L=M=MM8M=cf[R5nU(dgURSS9nSnURX045 UR 5nUUR 5(a!UR 5 UR 5 $$![RRaNn[SU35 SnAUR 5(a!WR 5 UR 5 ggSnAff=f!UR 5(a!WR 5 UR 5 ff=f)z Retrieves the most recent, unique attendance logs by user and status. This prevents showing multiple check-outs for the same person in the recent list. NTrYa WITH RankedLogs AS ( SELECT al.pid, CONCAT(s.first_name, ' ', s.last_name) AS name, al.recognition_timestamp, al.status, ROW_NUMBER() OVER(PARTITION BY al.pid, al.status ORDER BY al.recognition_timestamp DESC) as rn FROM attendance_logs al LEFT JOIN hris_core.employee_information_tbl s ON al.pid = s.pid ) SELECT pid, name, recognition_timestamp, status FROM RankedLogs WHERE rn = 1 ORDER BY recognition_timestamp DESC LIMIT %s z'Error fetching recent attendance logs: r)rrrrKrrs rget_recent_attendance_logs(AttendanceLog.get_recent_attendance_logss ..0% [[D[1FE4 NN5( +oo'G   ""  # $$  ;A3? @  ""  #    ""  #s)4B C5'C05C80C55C888D0c~[RUUUS9nUcgSnSnSnSnU(dSnOYUHSnXHRSS5- nXXRSS5- nXhRSS5- nXxRSS5- nMU [RR US5n U R S 5UUUUS ./n U $) z Calculates a week-by-week summary of attendance metrics, filterable by team. This function now calls the team attendance summary and aggregates the results to ensure consistent calculations. )rrrNr totalVisits uniqueFTEactualrequiredrz%Y%U)week_identifierrrrr)rget_team_attendance_summaryr6r7r8r]) rrr team_summaryrrrr team_datastart_dtaggregated_summarys rget_weekly_summary AttendanceLog.get_weekly_summary+s%@@!A    H)  mQ ?? mmK;; --!44MM*a88 * $$--j*E'008'#   "!rrc^[R5nU(dgURSS9mU4SjnU"XU5nU"X4U5n[[ [ UR 55[ UR 55-55n /n U Hn URU 05n URU 05n U RSS5nU RSS5nU RU U RSS5U RS S5UUU RS S5X:U RSS5U RS S5U RSS5U RSS5U RS S5S . 5 M U UR5(a!TR5 UR5 $$![RRaNn[S U35 SnAUR5(a!TR5 UR5 ggSnAff=f!UR5(a!TR5 UR5 ff=f) z Calculates a team-by-team summary of attendance, including comparison to a previous period, with dynamic carry-over logic for required attendance. NTrYc  >^U(aU(d0$SnUS3US3/nTbTS:waUS- nURT5 US- nT/RU[U55 T/R5Vs0sHoUSU_M nn0n[RR US 5nUR URpURS S 9n [R"X5S n U RU S 9n S nU"U5nS nU/nTbTS:waUS- nURT5 T/RU[U55 T/R5nUVs/sHnUSPM nn0nU(aSRSR[[U555nU RS5U RS5/nT/RU[U55 T/R5H8nUSnUU;a [!5UU'UUR#U"US55 M: 0n0nUGHnUR%SS5nUR'USSSSSS.5 UR'USSSSSS.5 USnUR%S5nUR%S5nU(dSnUR%U[!55n/n US:Xa/SQn O)US:XaS S/n OUS;aS /n OUS:Xa U S;aS /n O/SQn Sn!U(aW[RR US 5R)5n"U"R U :XaU"RU :XaU"U"5n!U H)n#U#U"U 5::dMU#U!::dMUUU#==S - ss'M+ [-[/[!U 5U- 55n$[-[/U[!U 5- 55n%U$HFn&U%H=n'U'U&:dM UUU&==S -ss'UUU'==S - ss'U%R1U'5 MD MH GM UHnUUUUUU-UU'M [6R8"5n)U)Vs1sHoUS"iM n*nT(a#[;U4S#jU)5S5n+U+(aU+S"1n*SU;dSU;aU*R#S5 0n,[-[/U*55HnUR%U05n-UR%US5n.U-R%S$S5U-R%S%S5U.U-R%S&S5U.S:a![=U-R%S&S5U.- S'-5OSS(.U,U'M U,$s snfs snf![*a GN f=f![2an([5S!U(35 Sn(A(GNNSn(A(ff=fs snf))z>Helper function to fetch and process data for a single period.a SELECT COALESCE(ug.group_name, 'Unassigned') AS team_name, COUNT(al.id) AS total_visits_raw, COUNT(DISTINCT al.pid, DATE(al.recognition_timestamp)) AS actual_unique_days, COUNT(DISTINCT al.pid) AS unique_fte FROM attendance_logs al LEFT JOIN hris_core.employee_information_tbl s ON al.pid = s.pid LEFT JOIN intra.tag_group tg ON s.pid = tg.user_id AND tg.workspace_default = 'Y' LEFT JOIN intra.user_group ug ON tg.group_id = ug.GID AND ug.workspace_status = 1 WHERE al.status = 'Check-in' AND al.recognition_timestamp >= %s AND al.recognition_timestamp <= %s rurwNr'z AND ug.GID = %sz GROUP BY team_name; team_namerr|daycURSS9nUR5SnUR5SnX2:aU$X2- S-$)Nr|r)rlr)datefirst_day_of_monthfirst_day_iso_weekgiven_date_iso_weeks rget_week_of_monthbAttendanceLog.get_team_attendance_summary.._get_data_for_period..get_week_of_months\-1\\a\-@*.@-K-K-Ma-P*.2.>.>.@.C+/C$76 3 HAMMrra SELECT s.pid, s.hybrid_level, COALESCE(ug.group_name, 'Unassigned') AS team_name, r.last_day FROM hris_core.employee_information_tbl s LEFT JOIN intra.tag_group tg ON s.pid = tg.user_id AND tg.workspace_default = 'Y' LEFT JOIN intra.user_group ug ON tg.group_id = ug.GID AND ug.workspace_status = 1 LEFT JOIN hris_core.resignation_tbl r ON s.pid = r.pid AND r.status = 'Resigned' WHERE s.status = 'Hired' AND STR_TO_DATE(s.date_joined, '%m/%d/%Y') <= %s rczSELECT pid, DATE(recognition_timestamp) as attendance_date FROM attendance_logs WHERE status = 'Check-in' AND pid IN ({}) AND recognition_timestamp BETWEEN %s AND %s,z%Y-%m-%d 00:00:00z%Y-%m-%d 23:59:59r Unassignedr)r|r hybrid_levellast_dayzHybrid Group 1zHybrid Group 2r)zHybrid Group 3zHybrid Group 3-4zHybrid Group 4)r|r rz%m/%d/%YzJWarning: Could not calculate hybrid requirements for team summary. Error: group_namec3z># UH0n[URS55[T5:XdM,Uv M2 g7f)rN)strr6)rtgids rrZAttendanceLog.get_team_attendance_summary.._get_data_for_period..s5)p5ISQRQVQVWaQbMcgjkngoMo!!5Is+; ;total_visits_rawractual_unique_daysr)rrrrr)r9rrrr7r8rrrlcalendar monthrangeformatrmapr r]rrr6 setdefaultrr;sortedlistremove Exceptionrr get_all_teamsnextr)0rrrrrteamactuals_by_team required_maprrrmonth_start_date month_end_daymonth_end_datercurrent_week_of_monthrequired_queryparams_requiredrrr monthly_logs logs_query params_logsrHrcteam_baselinesteam_adjustmentsrr last_day_strattended_weeksrequired_weekslast_week_of_employment last_day_dateweek_nummissed_target_weeks makeup_weeks missed_week makeup_weekrall_teams_from_modelall_team_names filtered_team period_dataactualsrrs0 ` r_get_data_for_periodGAttendanceLog.get_team_attendance_summary.._get_data_for_periodbs2CI ! #G9-#i/@A?sby!%77MMM#&!77 }eFm$$,$7$7$DQ$GM%5%=%=-%=%PNN"->h,G) &N(+eO3"9&*<<'..s3NN>53IJ&,oo&7O3BB/3CJ/DB#%L&M&T&TUXU]U]^abegk^lUm&n '7'@'@AT'UWeWnWnpCXD'E z53EF#)??#4C"%e*C",6CE S8I(-112CCHYDZ2[\$5 &(N')$.$'GGK$F &11)Q!qTU=VW(33I!qAQRVW?XY!%j # 7'*wwz':  %$4E)5)9)9#su)E)+ $44n"&66!Q"&LL`a_bn"&665M;QefdgSa-OP]>^$;)7H'+<^+LLQY]tQt .y 9( Cq H C)7/5T#n:MP^:^5_.`+'-d>CDW3W.X'Y +>K/; #.#<$4Y$? $LPQ$Q$L$4Y$? $LPQ$Q$L$0$7$7 $D$) 0<,?Q /b&4 2@2KLa2beuvfABWfX3X Y/&4(,'9'9';$AU!VAU|"4AU!V$()p5I)prv$wM$ l8S7Tn?2ll6R"&&|4! !'^(#"_#ZfCX$.% $%,!lfghfijkkl "Wsq0T?C UUFU7AU  U U(A"UAUVU UUUU U=$U88U=rrrrrr) namerrrrrisDangerprevTotalVisits prevUniqueFTE prevRequired prevActualprevPerformancez'Error getting team attendance summary: )rrrrrrkeysr6r9r!r"rrrr)rrrprev_start_date prev_end_daterr; current_data prev_datar7formatted_summaryrcurrentprevrrrrs @rr)AttendanceLog.get_team_attendance_summaryVs ..0DQ [[D[1Fg #T0 hOL,_XVI$D\->->-@)AC HXDY)Y$Z[N " + &**9b9 }}Y3 Xq1";;z15!((%#*;;}a#@!([!!< ($#*;;}a#@ & 1'+xx q'A%)XXk1%=$(HHZ$;"&((8Q"7'+xx q'A * ,,%   ""  # $$  ;A3? @  ""  #    ""  #s*E F66H"H"H%H""H%%8I)attendance_log.csvN)rN)NN)NNr|r FN)NNN)NNrN)NNr|FNN)r)NNNNN)__name__ __module__ __qualname____firstlineno____doc__ staticmethodrr#rMrTr`rgrprrrrrrrrrrrr__static_attributes__rMrrrrr sA  >BBH44l%%N6::(<<|!!FAAFRRh!!F6%%N<ccJ..`("("TYYrrr) mysql.connectorrr4r0r7rfranai.services.configrfranai.models.team_modelrrrMrrrrZs& ))ggrr