TaskPaper Submit Timesheet.scpt

Submit Timesheet.scpt

(* script version: 2010-10-08

   Changes: 2010-10-09
   - work in all locales, by coercing date strings to US or EU internal formats as necessary

*)

property ratePerHour : 51
property roundMinutes : 15
property theBoss : "Name of your Boss"
property theBossEmail : "yourBoss@example.com"
property theSender : "My Name"
property theSenderEmail : "me@example.com"
property theSignature : "Cheers,
-- 
Generated by Submit Timesheet.scpt (v.2010-10-08)"

-- CONSTANTS --
property firstDayOfTheWeek : "Mon" -- FIXME: isoWeek calculations currently hardcode Mon as day 1
(*	assuming the following format:
	CLOCK: [YYYY-MM-DD ddd hh:mm]--[ZZZZ-NN-EE eee ii:nn] => hh:mm
*)

property clockString : "CLOCK: "
property d1Beg : 9
property d1End : 18
property d2Beg : 33
property d2End : 42
property hBeg : 58
property hEnd : 62

property defaultEndDateString : "" -- set to "yyyy-mm-dd" to override `current date'

on dateToDateObject(theDay, theMonth, theYear)
	local maybe, dateString
	try
		set maybe to date ("31/12/2000" as string)
		set dateString to (theDay as string) & "/" & (theMonth as string)
	on error
		set dateString to (theMonth as string) & "/" & (theDay as string)
	end try
	return date (dateString & "/" & (theYear as string))
end dateToDateObject

-- calculate the date of the beginning of ISO week number 1 of the given year
on isoWeek1(year)
	set jan4 to dateToDateObject(4, 1, year)
	copy weekday of jan4 as integer to dayNumber
	set dayNumber to dayNumber - 1
	if dayNumber is equal to 0 then
		set dayNumber to 7
	end if
	return jan4 + ((1 - dayNumber) * days)
end isoWeek1

-- calculate the iso week number of theDate
-- returns a string of format "YYYY-Wnn"
on isoWeek(theDate)
	set {year:isoYear} to theDate
	if theDate ≥ dateToDateObject(29, 12, isoYear) then
		set week1 to isoWeek1(isoYear + 1)
		if theDate < week1 then
			set week1 to isoWeek1(isoYear)
		else
			set isoYear to isoYear + 1
		end if
	else
		set week1 to isoWeek1(isoYear)
		if theDate < week1 then
			set isoYear to isoYear - 1
			set week1 to isoWeek1(isoYear)
		end if
	end if
	
	return (isoYear as string) & "-W" & (1 + ((theDate - week1) div days div 7) as string)
end isoWeek

-- calculate the date of the first day of the first isoWeek of theMonth and theYear
on isoMonthBegin1(theMonth, theYear)
	set day4 to dateToDateObject(4, theMonth, theYear)
	copy weekday of day4 as integer to dayNumber
	set dayNumber to dayNumber - 1
	if dayNumber is equal to 0 then
		set dayNumber to 7
	end if
	return day4 + ((1 - dayNumber) * days)
end isoMonthBegin1

-- calculate the date of the first day of the first isoWeek of the month
-- containing |theDate|
on isoMonthBegin(theDate)
	set firstDay to isoMonthBegin1(theDate's month, theDate's year)
	if theDate < firstDay then
		set theYear to theDate's year
		set theMonth to (theDate's month as integer) - 1
		if theMonth < 1 then
			set theYear to theYear - 1
			set theMonth to 12
		end if
		set firstDay to isoMonthBegin1(theMonth, theYear)
	end if
	set nextDay to isoMonthBegin1((theDate's month as integer) + 1, theDate's year)
	if theDate ≥ nextDay then
		set firstDay to nextDay
	end if
	return firstDay
end isoMonthBegin

-- return a date object from a timestamp string in the format "YYYY-MM-DD" or
-- "YYYY-MM-DD ddd hh:mm"
on timestampToDate(theTimestamp)
	set theDate to date ("01/01/1000" as string) -- just a placeholder; no need to call dateToDateObject
	tell theTimestamp
		set theDate's year to text 1 thru 4
		set theDate's month to text 6 thru 7
		set theDate's day to text 9 thru 10
		if length of theTimestamp > 10 then
			set theDate's time to (text 16 thru 17) * hours + (text 19 thru 20) * minutes
		end if
	end tell
	return theDate
end timestampToDate

-- return an integer counting the number of minutes in a string in the format "hh:mm"
on timestringToMinutes(theTimestring)
	set numMinutes to 0
	tell theTimestring
		if item 1 is not equal to " " then
			set numMinutes to numMinutes + (600 * (item 1) as integer)
		end if
		set numMinutes to numMinutes + (60 * (item 2) as integer) + (10 * (item 4) as integer) + (item 5) as integer
	end tell
	return numMinutes
end timestringToMinutes

-- return a time string from a number of minutes
-- returns a string of format "hh:mm"
on minutesToTimeString(numMinutes)
	if numMinutes > 6000 then
		set timeString to numMinutes div 6000 as string
		set numMinutes to numMinutes mod 6000
	else
		set timeString to " "
	end if
	
	if numMinutes > 600 then
		set timeString to timeString & numMinutes div 600 as string
		set numMinutes to numMinutes mod 600
	else
		set timeString to timeString & " "
	end if
	
	set timeString to timeString & (numMinutes div 60 as string) & "."
	set numMinutes to numMinutes mod 60
	
	set timeString to timeString & (numMinutes div 6 as string)
	set numMinutes to numMinutes mod 6
	
	set timeString to timeString & (numMinutes * 10 div 6 as string)
	
	return timeString
end minutesToTimeString

-- return a string showing the payment due for |numMinutes| effort according to ratePerHour
-- returns a left-padded string of format "$xxx.xx"
on minutesToRateString(numMinutes)
	set totalPay to 100 * numMinutes / 60 * ratePerHour as integer
	
	set rateString to "$" & (totalPay div 100 as string) & "." & (totalPay mod 100 as string)
	if totalPay mod 100 is equal to 0 then
		set rateString to rateString & "0"
	end if
	repeat while length of rateString < 9
		set rateString to " " & rateString
	end repeat
	
	return rateString
end minutesToRateString

-- return a date string from a date object
on dateToDateString(theDate)
	set {day:d, year:y} to theDate
	-- Calculate the month number.
	copy theDate to b
	set b's month to January
	set m to (b - 2500000 - theDate) div -2500000
	
	-- Date string in "yyyy-mm-dd" format.
	tell (y * 10000 + m * 100 + d) as string
		set dateString to text 1 thru 4 & "-" & text 5 thru 6 & "-" & text 7 thru 8
	end tell
	
	return dateString
end dateToDateString

-- calculate the daily row leader of |theDate|
-- returns a string of format "ddd YYYY-Wnn [YYYY-MM-DD]"
on dailyRowLeader(theDate)
	return (text 1 thru 3 of (theDate's weekday as string)) & " " & isoWeek(theDate) & " [" & my dateToDateString(theDate) & "]"
end dailyRowLeader

-- calculate the monthly row leader of |theDate|
-- returns a string of format "YYYY-Wnn [w/e YYYY-MM-DD]"
on monthlyRowLeader(theDate)
	return isoWeek(theDate) & " [w/e " & my dateToDateString(theDate) & "]"
end monthlyRowLeader

-- return the number of complete days between two date objects
on deltaDays(beginDate, endDate)
	return (endDate - beginDate) / days as integer
end deltaDays

-- after the minutes for each day have been totalled, they are rounded
-- according to this function
on dailyRounding(numMinutes)
	return (numMinutes + 7) div 15 * 15
end dailyRounding

-- select all the "CLOCK: " notes from TaskPaper, returning a list of records
-- by date with total minutes clocked on that date
on fetchClocksFromTaskPaper(beginDate, endDate)
	-- create a list of zeros for the number of minutes in each day,
	-- we can increment the minutes for a given day by indexing into
	-- this list by the date offset from |begin|
	set minutesList to {}
	repeat my deltaDays(beginDate, endDate) times
		set minutesList to minutesList & {0}
	end repeat
	
	tell front document of application "TaskPaper"
		set clocks to search with query (clockString & " and type = note")
		repeat with each in clocks
			set thisClockString to text content of each
			if entry type of each is note type and thisClockString begins with clockString and thisClockString contains "]--[" then
				-- add the minutes from each clock entry to the appropriate entry in
				-- |minutesList| as offset from |beginDate| in days
				set thisDate to my timestampToDate(rich text d1Beg thru d1End of thisClockString)
				if thisDate ≥ beginDate and thisDate < endDate then
					set itemNo to 1 + (my deltaDays(beginDate, thisDate))
					set item itemNo of minutesList to (item itemNo of minutesList) + (my timestringToMinutes(rich text hBeg thru hEnd of thisClockString))
				end if
			end if
		end repeat
	end tell
	
	-- CLOCK: items are usually out of sequence, so we can't perform any
	-- rounding until after all the minutes have been totalled (above)	
	repeat with itemNo from 1 to length of minutesList
		set item itemNo of minutesList to my dailyRounding(item itemNo of minutesList)
	end repeat
	
	return {begin:beginDate, |minutesList|:minutesList}
end fetchClocksFromTaskPaper

(* For the purposes of the next few functions, we build and render "Tables":
   A table is a stylized list as follows:
	   1. the first entry is a list with pairs of entries {width, "HEADER"}
	   2. the remaining entries are lists with one element per column in each list
	   3. the last entry is separated from the body, assumed to contain totals *)

-- returns a table of weekly data for the month beginning at |beginDate| using |minutesList|,
-- each entry in minutesList is assumed to be the number of minutes for 1 entire day starting
-- from |beginDate|
on buildMonthTable(beginDate, minutesList)
	set numWeeks to (((length of minutesList) - 1) div 7) + 1
	set weeklyTable to {{25, "PERIOD", 6, "TIME", 9, "RATE"}}
	set endOfLastWeek to 0
	
	set minutesThisMonth to 0
	repeat numWeeks times
		set minutesThisWeek to 0
		set dayNo to 1
		repeat while dayNo ≤ 7 and endOfLastWeek + dayNo ≤ length of minutesList
			set itemNo to endOfLastWeek + dayNo
			set minutesThisWeek to minutesThisWeek + (item itemNo of minutesList)
			set dayNo to dayNo + 1
		end repeat
		set minutesThisMonth to minutesThisMonth + minutesThisWeek
		set weekEndDate to beginDate + (endOfLastWeek * days) + (6 * days)
		set thisWeek to {my monthlyRowLeader(weekEndDate), my minutesToTimeString(minutesThisWeek), my minutesToRateString(minutesThisWeek)}
		set weeklyTable to weeklyTable & {thisWeek}
		set endOfLastWeek to endOfLastWeek + 7
	end repeat
	set totalTime to {"             *Total time*", my minutesToTimeString(minutesThisMonth), my minutesToRateString(minutesThisMonth)}
	set weeklyTable to weeklyTable & {totalTime}
	
	return weeklyTable
end buildMonthTable

-- returns a table of daily data for the week beginning at |beginDate| using |minutesList|,
-- each entry in minutesList is assumed to be the number of minutes for 1 entire day starting
-- from |beginDate|
on buildWeekTable(beginDate, minutesList)
	set dailyTotalsList to {{25, "DATE", 6, "TIME"}}
	
	set minutesThisWeek to 0
	repeat with itemNo from 1 to length of minutesList
		set thisDate to beginDate + (itemNo - 1) * days
		set thisDay to {my dailyRowLeader(thisDate), my minutesToTimeString(item itemNo of minutesList)}
		if item itemNo of minutesList > 0 then
			set dailyTotalsList to dailyTotalsList & {thisDay}
			set minutesThisWeek to minutesThisWeek + (item itemNo of minutesList)
		end if
	end repeat
	set totalTime to {"             *Total time*", my minutesToTimeString(minutesThisWeek)}
	set dailyTotalsList to dailyTotalsList & {totalTime}
	
	return dailyTotalsList
end buildWeekTable

on formatTableDivider(headerList, divideChar)
	set tableString to "|" & divideChar
	repeat with headerOffset from 0 to (length of headerList) div 2 - 1
		set theWidth to item (headerOffset * 2 + 1) of headerList
		repeat theWidth times
			set tableString to tableString & divideChar
		end repeat
		if headerOffset * 2 + 3 < length of headerList then
			set tableString to tableString & divideChar & "+" & divideChar
		else
			set tableString to tableString & divideChar & "|"
		end if
	end repeat
	
	return tableString
end formatTableDivider

on formatTableRow(rowEntries)
	set tableString to "| "
	repeat with itemNo from 1 to length of rowEntries
		set tableString to tableString & item itemNo of rowEntries & " |"
		if itemNo < length of rowEntries then
			set tableString to tableString & " "
		end if
	end repeat
	
	return tableString
end formatTableRow

on formatTable(theTable)
	set tableString to "| "
	
	set headerList to item 1 of theTable
	repeat with headerOffset from 0 to (length of headerList) div 2 - 1
		set theWidth to item (headerOffset * 2 + 1) of headerList
		set theHeader to item (headerOffset * 2 + 2) of headerList
		repeat while (length of theHeader) + 2 ≤ theWidth
			set theHeader to " " & theHeader & " "
		end repeat
		if length of theHeader < theWidth then
			set theHeader to " " & theHeader
		end if
		set tableString to tableString & theHeader & " |"
		if headerOffset * 2 + 3 < length of headerList then
			set tableString to tableString & " "
		end if
	end repeat
	
	set tableString to tableString & "
" & formatTableDivider(item 1 of theTable, "-")
	
	repeat with rowNo from 2 to (length of theTable) - 1
		set tableString to tableString & "
" & formatTableRow(item rowNo of theTable)
	end repeat
	
	set tableString to tableString & "
" & formatTableDivider(item 1 of theTable, "-")
	
	set tableString to tableString & "
" & formatTableRow(last item of theTable)
	
	return tableString
end formatTable

-- Prompt for the final date to be processed
set defaultEndDateString to my dateToDateString(current date)
set endDateString to the text returned of (display dialog "End Date?" default answer defaultEndDateString)
set endDate to my timestampToDate(endDateString)

-- Calculate a nominal beginning of week date, 1 week earlier
set beginWeekDate to endDate - (7 * days)

-- Then, ensure we are starting the week on the correct day
repeat while text 1 thru 3 of (beginWeekDate's weekday as string) is not equal to firstDayOfTheWeek
	set beginWeekDate to beginWeekDate + (1 * days)
end repeat
set beginMonthDate to (beginWeekDate)
set beginLastMonthDate to isoMonthBegin(beginMonthDate - days)

set lastMonthClocks to my fetchClocksFromTaskPaper(beginLastMonthDate, beginMonthDate)
set thisWeekClocks to my fetchClocksFromTaskPaper(beginWeekDate, endDate + days)
set thisMonthClocks to my fetchClocksFromTaskPaper(beginMonthDate, endDate + days)

set lastMonthTable to buildMonthTable(begin of lastMonthClocks, |minutesList| of lastMonthClocks)
set thisWeekTable to buildWeekTable(begin of thisWeekClocks, |minutesList| of thisWeekClocks)
set thisMonthTable to buildMonthTable(begin of thisMonthClocks, |minutesList| of thisMonthClocks)

set theContent to (("Hi " & (word 1 of theBoss) & ",

Here are the final hours for last month, pending payment:

* " & month of ((begin of lastMonthClocks) + 7 * days) as string) & " Timesheet Summary

" & formatTable(lastMonthTable) & "


And here are the hours I logged last week:

* Timesheet " & isoWeek(beginWeekDate) & "

" & formatTable(thisWeekTable) & "


And here is the running total of hours for this month:

* " & month of ((begin of thisMonthClocks) + 7 * days) as string) & " Timesheet Summary

" & formatTable(thisMonthTable) & "

" & theSignature

-- submit the timesheet
tell application "Mail"
	-- "2010-W22 [w/e 2010-06-06]"
	set theSubject to my isoWeek(endDate) & " [w/e  " & endDateString & "]"
	
	set theMessage to make new outgoing message with properties {visible:true, sender:theSender & " <" & theSenderEmail & ">", subject:theSubject, content:theContent}
	
	tell theMessage
		make new to recipient at end of to recipients with properties {name:theBoss, address:theBossEmail}
	end tell
end tell

-- convert to plain text (doesn't seem to work tho')
tell application "Mail" to activate
tell application "System Events" to tell process "Mail"
	delay 1
	keystroke "t" using {shift down, command down}
end tell

(*
Copyright (c) 2010 Gary V. Vaughan  <gary@vaughan.pe>

This script is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 2 of the License, or
(at your option) any later version.

This script is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.
*)

This script prompts for an end-date and then creates an email, with an ascii table of clocked time in the current week (starting on the Monday prior to the date entered through to that date), and a running total of the previous weeks in the current month. It's very specific to the format that I have been using to submit invoices for billable time to one of my clients, which is why I didn't bother to post it to the TaskPaper wiki.

Also, if you need to track separate billable time for multiple projects, you'd need to have a separate taskpaper file for each project, or adjust the code to limit itself to collecting CLOCK lines from just the project that the cursor is in when you call the script... I haven't gotten round to doing that myself since I only charge by the hour to one client at the moment. Go ahead and fork a copy of the script and hack away though!

Turns out the original assumed EU date formats in your system settings, which causes AppleScript to barf if you try to coerce an EU format date string to a date object. This version first tries to coerce an EU format date, and if that fails tries again with a US format date... so it should work in any locale now.