Friday, June 19, 2009

My Latest Project

For my job, I have to keep track of mileage in order to be reimbursed for gas/wear&tear/etc. This is the hugest pain in the butt I've ever experienced. I've been trying several different solutions for the past year, and they've all worked reasonably well, but they were still a pain in the butt.

Here are the past solutions:

1. Print out a blank mileage spreadsheet every month, write down destinations and date, at the end of the month add up mileage using Google Maps. I didn't like this one because I didn't always have my bag with me which had the spreadsheet in it.
2. Use a mileage journal. Write down beginning odometer, ending odometer and destinations+date. I didn't like this one because it was extremely difficult if I made a personal stop in the middle of a trip. The first method was far better at accounting for those because I could just leave the destinations out of the spreadsheet.
3. Currently I am emailing myself from my phone with 2 destinations at a time and adding up the mileage at the end of the month a la method #1. This is the best so far because I have all of my mileage emails automatically go to a folder in my inbox called "mileage" (clever, eh?). Still, it's a pain in the butt to add up my miles at the end of the month, but this is the best so far.


So here's my ultimate scalable mileage calculator solution, in a series of steps.

1. From my phone, I will send an email with the subject of "destination1 to destination2" to mileage@minditbilling.com. These will be client names, "home", or a full address if it's a new client or random trip.
2. mileage@minditbilling.com will be restricted to only receive mail from internal sources so other people can't send in random mileage for fun.
3. Our Exchange (email) server will receive the email, and once a day will save the inbox to text files-- one for every message it has received.
4. The server will then go through each of the text files and do the following:
a. Note the sender
b. Note the "from" destination.
c. Note the "to"destination.
d. Cross reference each of the destinations with a list of common destinations held in another text file (e.g. "home" for graham@minditbilling.com = 3307 Chadwell Dr, Farmers Branch, TX 75234)
e. Open a Javascript webpage and get directions and distance information through the Google Maps API (e.g.)
f. Write the distance to text file directly from the webpage using an ActiveX control.
g. Server waits for the distance text file to appear, then gets the value contained the file and appends it, the date, and the destination names to another text file named "graham@minditbilling.com.mileage.[month name].txt", then deletes the temporary text file.
5. One the first of the next month, the server runs a script that emails my boss (in the future, another cross reference driver with boss) the previous month's text file for each email address (to start, just graham@minditbilling.com) and moves the mileage for the previous month to an /archive subfolder.

So far I have steps 4e and 4f done. I'm excited! I like projects. It will be based in VBScript because VBScript is easy and I know it.