Calculating the total time in an online meeting - follow up

Question

This is a follow up of question: Calculating the total time in an online meeting

Suppose there was a meeting and the meeting record is saved in a CSV file. How to write a bash script/awk script to find out the total amount of time for which an employee stayed online. One employee may leave and rejoin the meeting, all his/her online time should be calculated. What I did is as follows, but got stuck on how to compare one record with all other record, and add the total time of each joined and left pairs of a person.

cat tst.awk
BEGIN { FS=" *, *"; OFS=", " }
NR==1 { next }
$1 in joined {
    jt = time2secs(joined[$1])
    lt = time2secs($3)
    totSecs[$1] += (lt - jt)
    delete joined[$1]
    next
}
{ joined[$1] = $3 }
END {
    for (name in totSecs) {
        print name, secs2time(totSecs[name])
    }
}

function time2secs(time,        t) {
    split(time,t,/:/)
    return (t[1]*60 + t[2])*60 + t[3]
}

function secs2time(secs,        h,m,s) {
    h = int(secs / (60*60))
    m = int((secs - (h*60*60)) / 60)
    s = int(secs % 60)
    return sprintf("%02d:%02d:%02d", h, m, s)
}

The start_time and end_time of the meeting are given at command line such as:

$ ./script.sh input.csv 10:00:00 13:00:00

Only the time between startTime (10:00:00) and EndTime (13:00:00) should be considered. The persons who have joined but not left, must be considered as left at Endtime and their online time should be added also. I tried but no desired result.

The output should look like this: (Can be stored in an output file)

Bob,    02:44:00
John,    00:41:00
David,   02:50:00
James,   01:39:30

The contents of the CSV file is as follows:

    Employee_name, Joined/Left, Time  
    David, joined, 09:40:00
    David, left, 10:20:00
    David, joined, 10:30:00
    John, joined, 10:00:00  
    Bob, joined, 10:01:00  
    James, joined, 10:00:30  
    Bob, left, 10:20:00    
    James, left, 11:40:00  
    John, left, 10:41:00
    Bob, joined, 10:35:00

score 3 · Accepted Answer · answered Sep 04 '20 at 14:43

$ cat tst.awk
BEGIN { FS=" *, *"; OFS=", " }
NR==1 { next }
{ names[$1] }
$3 < beg { next }
$3 >= end { exit }
$2 == "joined" {
    joined[$1] = $3
}
($2 == "left") && ($1 in joined) {
    jt = time2secs(joined[$1])
    lt = time2secs($3)
    totSecs[$1] += (lt - jt)
    delete joined[$1]
    next
}
END {
    for (name in names) {
        if (name in joined) {
            jt = time2secs(joined[name])
            lt = time2secs(end)
            totSecs[name] += (lt - jt)
        }
        print name, secs2time(totSecs[name])
    }
}

function time2secs(time,        t) {
    split(time,t,/:/)
    return (t[1]*60 + t[2])*60 + t[3]
}

function secs2time(secs,        h,m,s) {
    h = int(secs / (60*60))
    m = int((secs - (h*60*60)) / 60)
    s = int(secs % 60)
    return sprintf("%02d:%02d:%02d", h, m, s)
}

.

$ awk -v beg='10:00:00' -v end='13:00:00' -f tst.awk file
James, 01:39:30
David, 02:30:00
Bob, 02:44:00
John, 00:41:00

Calculating the total time in an online meeting - follow up

1 Answers1