#!/usr/local/bin/python
#
# gtc-sample.py - example of how to retrieve data from Garmin Training Center
#                 database on OS X
#
# version: 1.0
#

from pysqlite2 import dbapi2 as sqlite
import string, os

# shorthand to make deleting characters more readable

def deletechars(s, todelete, id=string.maketrans('', '')):
	return string.translate(s, id, todelete)


# figure out where the file lives

home_dir = os.environ["HOME"]
gtc_file = home_dir + "/Library/Application Support/Garmin/Training Center/Training Center.gtc"

if (os.path.exists(gtc_file)):

	con = sqlite.connect(gtc_file)
	
	cur = con.cursor()

	# get user info
	
	cur.execute("SELECT zNAME, zGENDER, zWEIGHT FROM zCDUSER")
	for (name, gender, weight) in cur:
		if (gender == 1):
			gender = "MALE"
		else:
			gender = "FEMALE"
		
		print '%s is %3.2f kg or %3.2f lbs. [%s]' % (name, weight, (weight * 2.20462262), gender)

	# get last activity		 
		 
	cur.execute("""SELECT zIMAGENAME, zDISPLAYEDNAME, zDISPLAYEDASCENT, zDISPLAYEDASCENT, zTRACK
						FROM zCDTREEITEM WHERE zIMAGENAME LIKE 'history%' ORDER BY zSTARTTIME2 DESC LIMIT 1""") 
	
	# coulg have just done "fetchone", but this would work for multiple activities
	
	for (image,  name, ascent, descent, track) in cur:

		# basic activity entries are "history_biking" and "history_running"
	
		if (image.find('biking')):
			activity = 'CYCLING'
		elif (image.find('running')):
			activity = 'RUNNING'
		else:
			activity = 'OTHER'
		
		
		print '%s, %s, %5.2f, %5.2f, %s' % (activity, name, ascent, descent, track)

		# let sqlite calculate average/max heart rate
		
		cur2 = con.cursor() 
		cur2.execute("""SELECT AVG(zHEARTRATE), MAX(zHEARTRATE) FROM zCDTRACKPOINT
							WHERE zBELONGSTOTRACKSEGMENT = %d AND zCUMULATIVEDISTANCE > 0 ORDER BY zTIME""" % (track))
		for (hr_avg, hr_max) in cur2:
			print 'Average HR: [%d]; Max HR: [%d]' % (hr_avg, hr_max)

		# get the total distance travelled during this activity (data from last track point in the track
		
		cur2.execute("""SELECT zCUMULATIVEDISTANCE FROM zCDTRACKPOINT WHERE zBELONGSTOTRACKSEGMENT = %d
							ORDER BY zTIME DESC LIMIT 1""" % (track))
		for (dist) in cur2:
			distance = float(deletechars(str(dist), ',()'))
			print 'Distance: [%5.2f meters] [%3.2f km] [%3.2f miles]' % (distance, (distance / 1000), (distance * 0.000621371192))

		# get lat/lon boundaries for plotting purposes
			
		cur2.execute("""SELECT MIN(zLATITUDE), MAX(zLATITUDE), MIN(zLONGITUDE), MAX(zLONGITUDE) FROM zCDTRACKPOINT
							WHERE zBELONGSTOTRACKSEGMENT = %d ORDER BY zTIME""" % (track))
		(lat_min, lat_max, lon_min, lon_max) = cur2.fetchone()
		#	print 'lat min: [%d] lat max: [%d] lon min: [%d] lon max:[%d]' % (lat_min, lat_max, lon_min, lon_max)
			

		# transform lat/lon points so they fit on a particular size canvas

		dx = abs(lon_min - lon_max) ;
		dy = lat_max - lat_min ;
		
		nx = int(dx / 300) # width of graphics area
		ny = int(dy / 200) # height of graphcs area
		
		point_num = 0
		
		# get points to "plot"
			
		cur2.execute("""SELECT zLONGITUDE, zLATITUDE FROM zCDTRACKPOINT WHERE zBELONGSTOTRACKSEGMENT = %d
							ORDER BY zTIME ASC""" % (track))
		for (lon_pt, lat_pt) in cur2:
		
			cx = int((lon_pt - lon_min) / nx)
			cy = int((lat_max - lat_pt) / ny)
			
			point_num += 1
			if (point_num == 1):
				print '%d,%d' % (cx, cy) # first point
			else:
				print '%d,%d' % (cx, cy)