1    #!/usr/local/bin/python
   2    #
   3    # gtc-sample.py - example of how to retrieve data from Garmin Training Center
   4    #                 database on OS X
   5    #
   6    # version: 1.0
   7    #
   8    
   9    from pysqlite2 import dbapi2 as sqlite
  10    import string, os
  11    
  12    # shorthand to make deleting characters more readable
  13    
  14    def deletechars(s, todelete, id=string.maketrans('', '')):
  15    	return string.translate(s, id, todelete)
  16    
  17    
  18    # figure out where the file lives
  19    
  20    home_dir = os.environ["HOME"]
  21    gtc_file = home_dir + "/Library/Application Support/Garmin/Training Center/Training Center.gtc"
  22    
  23    if (os.path.exists(gtc_file)):
  24    
  25    	con = sqlite.connect(gtc_file)
  26    	
  27    	cur = con.cursor()
  28    
  29    	# get user info
  30    	
  31    	cur.execute("SELECT zNAME, zGENDER, zWEIGHT FROM zCDUSER")
  32    	for (name, gender, weight) in cur:
  33    		if (gender == 1):
  34    			gender = "MALE"
  35    		else:
  36    			gender = "FEMALE"
  37    		
  38    		print '%s is %3.2f kg or %3.2f lbs. [%s]' % (name, weight, (weight * 2.20462262), gender)
  39    
  40    	# get last activity		 
  41    		 
  42    	cur.execute("""SELECT zIMAGENAME, zDISPLAYEDNAME, zDISPLAYEDASCENT, zDISPLAYEDASCENT, zTRACK
						FROM zCDTREEITEM WHERE zIMAGENAME LIKE 'history%' ORDER BY zSTARTTIME2 DESC LIMIT 1""") 
  44    	
  45    	# coulg have just done "fetchone", but this would work for multiple activities
  46    	
  47    	for (image,  name, ascent, descent, track) in cur:
  48    
  49    		# basic activity entries are "history_biking" and "history_running"
  50    	
  51    		if (image.find('biking')):
  52    			activity = 'CYCLING'
  53    		elif (image.find('running')):
  54    			activity = 'RUNNING'
  55    		else:
  56    			activity = 'OTHER'
  57    		
  58    		
  59    		print '%s, %s, %5.2f, %5.2f, %s' % (activity, name, ascent, descent, track)
  60    
  61    		# let sqlite calculate average/max heart rate
  62    		
  63    		cur2 = con.cursor() 
  64    		cur2.execute("""SELECT AVG(zHEARTRATE), MAX(zHEARTRATE) FROM zCDTRACKPOINT
							WHERE zBELONGSTOTRACKSEGMENT = %d AND zCUMULATIVEDISTANCE > 0 ORDER BY zTIME""" % (track))
  66    		for (hr_avg, hr_max) in cur2:
  67    			print 'Average HR: [%d]; Max HR: [%d]' % (hr_avg, hr_max)
  68    
  69    		# get the total distance travelled during this activity (data from last track point in the track
  70    		
  71    		cur2.execute("""SELECT zCUMULATIVEDISTANCE FROM zCDTRACKPOINT WHERE zBELONGSTOTRACKSEGMENT = %d
							ORDER BY zTIME DESC LIMIT 1""" % (track))
  73    		for (dist) in cur2:
  74    			distance = float(deletechars(str(dist), ',()'))
  75    			print 'Distance: [%5.2f meters] [%3.2f km] [%3.2f miles]' % (distance, (distance / 1000), (distance * 0.000621371192))
  76    
  77    		# get lat/lon boundaries for plotting purposes
  78    			
  79    		cur2.execute("""SELECT MIN(zLATITUDE), MAX(zLATITUDE), MIN(zLONGITUDE), MAX(zLONGITUDE) FROM zCDTRACKPOINT
							WHERE zBELONGSTOTRACKSEGMENT = %d ORDER BY zTIME""" % (track))
  81    		(lat_min, lat_max, lon_min, lon_max) = cur2.fetchone()
  82    		#	print 'lat min: [%d] lat max: [%d] lon min: [%d] lon max:[%d]' % (lat_min, lat_max, lon_min, lon_max)
  83    			
  84    
  85    		# transform lat/lon points so they fit on a particular size canvas
  86    
  87    		dx = abs(lon_min - lon_max) ;
  88    		dy = lat_max - lat_min ;
  89    		
  90    		nx = int(dx / 300) # width of graphics area
  91    		ny = int(dy / 200) # height of graphcs area
  92    		
  93    		point_num = 0
  94    		
  95    		# get points to "plot"
  96    			
  97    		cur2.execute("""SELECT zLONGITUDE, zLATITUDE FROM zCDTRACKPOINT WHERE zBELONGSTOTRACKSEGMENT = %d
							ORDER BY zTIME ASC""" % (track))
  99    		for (lon_pt, lat_pt) in cur2:
 100    		
 101    			cx = int((lon_pt - lon_min) / nx)
 102    			cy = int((lat_max - lat_pt) / ny)
 103    			
 104    			point_num += 1
 105    			if (point_num == 1):
 106    				print '%d,%d' % (cx, cy) # first point
 107    			else:
 108    				print '%d,%d' % (cx, cy)