View Single Post
Hi everyone,

My company works with very big projects (>2500) and we needed to export the project costs grouped by year and month to be used as a expenditure-flow (or "cash-flow") data within MS Excel so we could negotiate with our clients easily...

I was doing this using CSV, TextMate and MySQL to export, transform and process this data. But after asking the wonderful folks at omnigroup and doing some research on the net, I came up with the following AppleScript:

tell application "OmniPlan"
	set CurrentCashFlowDates to {}
	set CurrentCashFlowValues to {}
	repeat with CurrentTask in tasks of front document
		if task type of CurrentTask is standard task and type is not "group task" then
			set CurrentTaskEndDate to (ending date of CurrentTask)
			set CurrentTaskTotalCost to (total cost of CurrentTask)
			set CurrentCashFlowDate to (year of CurrentTaskEndDate as integer) & "-" & (month of CurrentTaskEndDate as integer) as string
			set CurrentCashFlowValue to CurrentTaskTotalCost as real
			if CurrentCashFlowDates contains CurrentCashFlowDate then
				repeat with id from 1 to the count of CurrentCashFlowDates
					if item (id) of CurrentCashFlowDates is equal to CurrentCashFlowDate then
						set item (id) of CurrentCashFlowValues to (item (id) of CurrentCashFlowValues as real) + CurrentCashFlowValue as real
					end if
				end repeat
				set the end of CurrentCashFlowDates to CurrentCashFlowDate as string
				set the end of CurrentCashFlowValues to CurrentCashFlowValue as real
			end if
		end if
	end repeat
	set CurrentCashFlowFileContents to "" as string
	repeat with id from 1 to the count of CurrentCashFlowDates
		set CurrentCashFlowFileContents to CurrentCashFlowFileContents & (item (id) of CurrentCashFlowDates) & "	" & (item (id) of CurrentCashFlowValues) & "
	end repeat
	set CurrentCashFlowFile to open for access file "~/Desktop/cashflow.csv" with write permission
	write CurrentCashFlowFileContents to CurrentCashFlowFile starting at eof
	close access CurrentCashFlowFile
end tell
I hope it can help someone searching for a solution like this...

Best Regards,
Bruno B B Magalhaes