Work Timesheet Dashboard

Share your Projects!
1

Partially a sharing post, partially a request for ideas.

I have to fill in a timesheet at work, like many others. I maintain my own, having been an Excel jockey for years. It exports an XML file with my current available leave balance and current flexi time balance. This is one of the things I love about HA, I have finally got a place to have a ‘personal dashboard’ where I can throw all sorts of interesting (to me!) information that I would normally set something up for but then not really use. I use VBA in Excel to export a table to XML which is saved in my dropbox. I then use a curl command line sensor to download the file and use this very crude command line sensor to get the data;

 grep -oP '<LeaveCurrentFlexi(.*)>' /home/homeassistant/.homeassistant/downloads/TimesheetData.xml | cut -d '>' -f 2 | cut -d '<' -f 1 | awk -F [PT] '{print $1 $3}'

My question is, does anyone have any idea how I can take the XML duration information for my flexi balance and get ‘x hours and y minutes’, not necessarily for the HA screen but for an Alexa flash briefing. I’ve googled around but not being a programmer I am struggling as there doesn’t seem to be any similar examples I can find. I am running on a raspberry pi with Rasbian Stretch and HA 0.59.

2

It would help to see the structure of your data :wink:

3

Hm, yeah I guess it would!

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<times>
	<time>
		<LeaveRemaining>P14D</LeaveRemaining>
		<LeaveCurrentFlexi>PT1H43M</LeaveCurrentFlexi>
		<TimeAverageStart>PT8H24M</TimeAverageStart>
		<TimeAverageEnd>PT16H29M</TimeAverageEnd>
		<TimeAverageDay>PT7H38M</TimeAverageDay>
	</time>
</times>
4

First, can I give you a slightly less crude grep call? This does the same thing, and is much more compact.

grep -oP '<LeaveCurrentFlexi>\K(.*)(?=</LeaveCurrentFlexi>)' test.xml

I’m probably showing my age here, but for little one-shot text processing tasks like this, I really really love to break out awk.

awk -F'[A-Za-z<>]+' '/LeaveCurrentFlexi/ { print $2 " hour(s) and " $3 " minutes" }' test.xml

Ideally, the body of the awk program would check if $2 == 1, and only print hour instead of hours. But that’s a pretty good start.

2 Likes
5

Yes, by all means thank you! I thought it would be possible using awk but I’ve never used that before and struggled to find an example I could work with, so this is really helpful. I will replace my grep call as well. Much appreciated =]

Update: sorry, realised the awk replaces the grep, but will still note that for future usage.

6

Thanks to stibbons, and also after having discovered that you can’t get a JSON array from the command_line sensor, I have wrapped this up into a little script that gives me a integer value along with a unit_of_measurement I can use;

#!/bin/sh

FLEXIHOURS=`awk -F '[A-Za-z<>]+' '/LeaveCurrentFlexi/{print $2}' /home/homeassistant/.homeassistant/downloads/TimesheetData.xml`
FLEXIMINUTES=`awk -F '[A-Za-z<>]+' '/LeaveCurrentFlexi/{print $3}' /home/homeassistant/.homeassistant/downloads/TimesheetData.xml`

MINFRAC=$(echo "scale=2; 5/60" | bc)
TOTALHOURS=$(echo "scale=2; $FLEXIHOURS + $MINFRAC" | bc)
echo $TOTALHOURS