<reponame>ysBach/snuetl-attchk
"""
Attendance checker
BSD 3-Clause License
ysBach 2022
"""

import numpy as np
import pandas as pd
import argparse


def calc_duration(join_time, leave_time):
    hms = join_time.split("")

p = argparse.ArgumentParser(
    description=(
        'Attendance checker for SNU ETL for Zoom classes. \nOutput columns mean:'
        + '\n' + f"{'IMPORTANT columns':-^80}"
        + '\nID Number          : Student ID number (auto-generated by ETL)'
        + '\nName               : Student name (auto-generated by ETL)'
        + '\nEmail address      : Student email address (auto-generated by ETL)'
        + '\nMinutes            : Sum("Duration") in minutes'
        + '\nPercent            : "Minutes" / `minutestotal` (see arg -m)'
        + '\nFull participation : True if "Percent" > `percentfull` (see arg -p)'
        + '\n' + f"{'UNimportant columns':-^80}"
        + '\nEarliest join time : Based on "Join time" from ETL'
        + '\nLatest leave time  : Based on "Leave time" from ETL'
        + '\nJoin time          : "Join time" values for this student from ETL'
        + '\nLeave time         : "Leave time" values for this student from ETL'
        + '\nDuration           : "Duration" values for this student from ETL'
    ),
    formatter_class=argparse.RawTextHelpFormatter
)
p.add_argument('input', help='Input file')
p.add_argument('-o', '--output', help='Output file', default=None)
p.add_argument('-m', '--minutestotal', default=75, type=float,
               help='Total minutes, time for 100%% participation. Default = 75[min]')
p.add_argument('-p', '--percentfull', default=70, type=float,
               help=('Minimum %% of time to be regarded as "full participation". '
                     + 'Default = 70[%%]'))
args = p.parse_args()
print("\n", args)

fpath = args.input
fulltime = args.minutestotal
fullperc = args.percentfull
output = args.output

# -- For Testing --
# fpath = "testfile-zoom_participants_93522834467.xlsx"
# fpath = "~/Downloads/zoom_participants_99711167427.xlsx"
# fulltime = 75
# fullperc = 66.67
# output = None

df = pd.read_excel(fpath)
# ---------------------------------------------------------------------------------------- #

df["_join"] = df["Join time"].apply(lambda x: pd.to_datetime(x).value/10**9/60)
df["_left"] = df["Leave time"].apply(lambda x: pd.to_datetime(x).value/10**9/60)
df.sort_values("_join", inplace=True, ignore_index=True)

# hms = df["Duration"].str.split(":", expand=True)
# minutes = hms[0].astype(int) * 60 + hms[1].astype(int) + hms[2].astype(int)/60
# df["Minutes"] = minutes.apply(lambda x: round(x, 2))

# Use all info, because using only one of these will result in a non-unique ID.
dfg = df.groupby(["ID number", "Name", "Email address"], dropna=False)
#                                                        ^^^^^^^^^^^^
# Don't drop NaN as sometimes the ETL fails to record proper information

dict4df = {k: [] for k in ["Full participation",
                           "ID number", "Email address",
                           "Minutes", "Percent", "Name",
                           "Earliest join time", "Latest leave time",
                           "Join time", "Leave time", "Duration"]}

for (idnum, name, email), df_g in dfg:
    name = name.replace("(참여자정보 부족 : ETL 내 일치 수강생 못 찾음)", " !!! ETL FAILED !!!")

    dict4df["ID number"].append(idnum)
    dict4df["Name"].append(name)
    dict4df["Email address"].append(email)

    if len(df_g) > 1:  # 2 or more rows (multiple connections) - calculate time manually
        _js = [df_g.iloc[0]["_join"]]
        _ls = [df_g.iloc[0]["_left"]]
        for i, row in df_g.iloc[1:].iterrows():
            if row["_join"] < _ls[-1]:
                _ls[-1] = max(_ls[-1], row["_left"])
            else:
                _js.append(row["_join"])
                _ls.append(row["_left"])
        mins = np.sum(np.array(_ls) - np.array(_js))
    else:  # single connection - Use "Duration" value from ETL
        hms = df_g.iloc[0]["Duration"].split(":")
        mins = int(hms[0]) * 60 + int(hms[1]) + int(hms[2])/60

    for k in ["Join time", "Leave time", "Duration"]:
        dict4df[k].append(str(df_g[k].tolist()))
    dict4df["Earliest join time"].append(str(df_g["Join time"].min()))
    dict4df["Latest leave time"].append(str(df_g["Leave time"].max()))
    perc = mins/fulltime*100
    dict4df["Minutes"].append("{:.2f}".format(mins, 2))
    dict4df["Percent"].append("{:.1f}".format(perc, 1))
    dict4df["Full participation"].append(perc >= fullperc)

df2 = pd.DataFrame.from_dict(dict4df)
df2.sort_values(["Full participation", "ID number", "Name", "Join time"],
                inplace=True, ignore_index=True)

if output is None:
    from pathlib import Path
    import os
    output = Path(os.getcwd(), Path(fpath).stem + ".csv")

# Print to console for quick usage.
print("\n\n Full? |  ID number |  Minutes   | Percent | Name")
print("="*80)
for _, row in df2.iterrows():
    print(f'{str(row["Full participation"]):^7s}|',
          f'{str(row["ID number"]):^11s}|',
          f'{str(row["Minutes"]):>6s} min |',
          f'{str(row["Percent"]):>5s} % |',
          f'{str(row["Name"]):<30s}',
          )

df2.to_csv(output, index=False, encoding='utf-8-sig')
#                               ^^^^^^^^^^^^^^^^^^^^
# utf-8-sig is for Korean letters....

print('\n\tSaved to:', output)
"""
Attendance checker
BSD 3-Clause License
ysBach 2022
"""

import numpy as np
import pandas as pd
import argparse


def calc_duration(join_time, leave_time):
    hms = join_time.split("")

p = argparse.ArgumentParser(
    description=(
        'Attendance checker for SNU ETL for Zoom classes. \nOutput columns mean:'
        + '\n' + f"{'IMPORTANT columns':-^80}"
        + '\nID Number          : Student ID number (auto-generated by ETL)'
        + '\nName               : Student name (auto-generated by ETL)'
        + '\nEmail address      : Student email address (auto-generated by ETL)'
        + '\nMinutes            : Sum("Duration") in minutes'
        + '\nPercent            : "Minutes" / `minutestotal` (see arg -m)'
        + '\nFull participation : True if "Percent" > `percentfull` (see arg -p)'
        + '\n' + f"{'UNimportant columns':-^80}"
        + '\nEarliest join time : Based on "Join time" from ETL'
        + '\nLatest leave time  : Based on "Leave time" from ETL'
        + '\nJoin time          : "Join time" values for this student from ETL'
        + '\nLeave time         : "Leave time" values for this student from ETL'
        + '\nDuration           : "Duration" values for this student from ETL'
    ),
    formatter_class=argparse.RawTextHelpFormatter
)
p.add_argument('input', help='Input file')
p.add_argument('-o', '--output', help='Output file', default=None)
p.add_argument('-m', '--minutestotal', default=75, type=float,
               help='Total minutes, time for 100%% participation. Default = 75[min]')
p.add_argument('-p', '--percentfull', default=70, type=float,
               help=('Minimum %% of time to be regarded as "full participation". '
                     + 'Default = 70[%%]'))
args = p.parse_args()
print("\n", args)

fpath = args.input
fulltime = args.minutestotal
fullperc = args.percentfull
output = args.output

# -- For Testing --
# fpath = "testfile-zoom_participants_93522834467.xlsx"
# fpath = "~/Downloads/zoom_participants_99711167427.xlsx"
# fulltime = 75
# fullperc = 66.67
# output = None

df = pd.read_excel(fpath)
# ---------------------------------------------------------------------------------------- #

df["_join"] = df["Join time"].apply(lambda x: pd.to_datetime(x).value/10**9/60)
df["_left"] = df["Leave time"].apply(lambda x: pd.to_datetime(x).value/10**9/60)
df.sort_values("_join", inplace=True, ignore_index=True)

# hms = df["Duration"].str.split(":", expand=True)
# minutes = hms[0].astype(int) * 60 + hms[1].astype(int) + hms[2].astype(int)/60
# df["Minutes"] = minutes.apply(lambda x: round(x, 2))

# Use all info, because using only one of these will result in a non-unique ID.
dfg = df.groupby(["ID number", "Name", "Email address"], dropna=False)
#                                                        ^^^^^^^^^^^^
# Don't drop NaN as sometimes the ETL fails to record proper information

dict4df = {k: [] for k in ["Full participation",
                           "ID number", "Email address",
                           "Minutes", "Percent", "Name",
                           "Earliest join time", "Latest leave time",
                           "Join time", "Leave time", "Duration"]}

for (idnum, name, email), df_g in dfg:
    name = name.replace("(참여자정보 부족 : ETL 내 일치 수강생 못 찾음)", " !!! ETL FAILED !!!")

    dict4df["ID number"].append(idnum)
    dict4df["Name"].append(name)
    dict4df["Email address"].append(email)

    if len(df_g) > 1:  # 2 or more rows (multiple connections) - calculate time manually
        _js = [df_g.iloc[0]["_join"]]
        _ls = [df_g.iloc[0]["_left"]]
        for i, row in df_g.iloc[1:].iterrows():
            if row["_join"] < _ls[-1]:
                _ls[-1] = max(_ls[-1], row["_left"])
            else:
                _js.append(row["_join"])
                _ls.append(row["_left"])
        mins = np.sum(np.array(_ls) - np.array(_js))
    else:  # single connection - Use "Duration" value from ETL
        hms = df_g.iloc[0]["Duration"].split(":")
        mins = int(hms[0]) * 60 + int(hms[1]) + int(hms[2])/60

    for k in ["Join time", "Leave time", "Duration"]:
        dict4df[k].append(str(df_g[k].tolist()))
    dict4df["Earliest join time"].append(str(df_g["Join time"].min()))
    dict4df["Latest leave time"].append(str(df_g["Leave time"].max()))
    perc = mins/fulltime*100
    dict4df["Minutes"].append("{:.2f}".format(mins, 2))
    dict4df["Percent"].append("{:.1f}".format(perc, 1))
    dict4df["Full participation"].append(perc >= fullperc)

df2 = pd.DataFrame.from_dict(dict4df)
df2.sort_values(["Full participation", "ID number", "Name", "Join time"],
                inplace=True, ignore_index=True)

if output is None:
    from pathlib import Path
    import os
    output = Path(os.getcwd(), Path(fpath).stem + ".csv")

# Print to console for quick usage.
print("\n\n Full? |  ID number |  Minutes   | Percent | Name")
print("="*80)
for _, row in df2.iterrows():
    print(f'{str(row["Full participation"]):^7s}|',
          f'{str(row["ID number"]):^11s}|',
          f'{str(row["Minutes"]):>6s} min |',
          f'{str(row["Percent"]):>5s} % |',
          f'{str(row["Name"]):<30s}',
          )

df2.to_csv(output, index=False, encoding='utf-8-sig')
#                               ^^^^^^^^^^^^^^^^^^^^
# utf-8-sig is for Korean letters....

print('\n\tSaved to:', output)
Attendance checker BSD 3-Clause License ysBach 2022 # -- For Testing -- # fpath = "testfile-zoom_participants_93522834467.xlsx" # fpath = "~/Downloads/zoom_participants_99711167427.xlsx" # fulltime = 75 # fullperc = 66.67 # output = None # ---------------------------------------------------------------------------------------- # # hms = df["Duration"].str.split(":", expand=True) # minutes = hms[0].astype(int) * 60 + hms[1].astype(int) + hms[2].astype(int)/60 # df["Minutes"] = minutes.apply(lambda x: round(x, 2)) # Use all info, because using only one of these will result in a non-unique ID. #                                                        ^^^^^^^^^^^^ # Don't drop NaN as sometimes the ETL fails to record proper information # 2 or more rows (multiple connections) - calculate time manually # single connection - Use "Duration" value from ETL # Print to console for quick usage. #                               ^^^^^^^^^^^^^^^^^^^^ # utf-8-sig is for Korean letters....