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:
Code:
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
else
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