health_workout_cadence.txt

# --------------------------------------------------------------------------------
#       Copyright (c) 2018-2020 Sarah Edwards (Station X Labs, LLC, 
#       @iamevltwin, mac4n6.com). All rights reserved.

#       This software is provided "as is," without warranty of any kind,
#       express or implied.  In no event shall the author or contributors
#       be held liable for any damages arising in any way from the use of
#       this software.

#       The contents of this file are DUAL-LICENSED.  You may modify and/or
#       redistribute this software according to the terms of one of the
#       following two licenses (at your option):

#       LICENSE 1 ("BSD-like with acknowledgment clause"):

#       Permission is granted to anyone to use this software for any purpose,
#       including commercial applications, and to alter it and redistribute
#       it freely, subject to the following restrictions:

#       1. Redistributions of source code must retain the above copyright
#          notice, disclaimer, and this list of conditions.
#       2. Redistributions in binary form must reproduce the above copyright
#          notice, disclaimer, and this list of conditions in the documenta-
#          tion and/or other materials provided with the distribution.
#       3. All advertising, training, and documentation materials mentioning 
#          features or use of this software must display the following 
#          acknowledgment. Character-limited social media may abbreviate this 
#          acknowledgment to include author and APOLLO name ie: "This new 
#          feature brought to you by @iamevltwin's APOLLO". Please make an 
#          effort credit the appropriate authors on specific APOLLO modules.
#          The spirit of this clause is to give public acknowledgment to 
#          researchers where credit is due.

#             This product includes software developed by Sarah Edwards 
#             (Station X Labs, LLC, @iamevltwin, mac4n6.com) and other 
#             contributors as part of APOLLO (Apple Pattern of Life Lazy 
#             Output'er). 


#       LICENSE 2 (GNU GPL v3 or later):

#       This file is part of APOLLO (Apple Pattern of Life Lazy Output'er).

#       APOLLO is free software: you can redistribute it and/or modify
#       it under the terms of the GNU General Public License as published by
#       the Free Software Foundation, either version 3 of the License, or
#       (at your option) any later version.

#       APOLLO is distributed in the hope that it will be useful,
#       but WITHOUT ANY WARRANTY; without even the implied warranty of
#       MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#       GNU General Public License for more details.

#       You should have received a copy of the GNU General Public License
#       along with APOLLO.  If not, see <https://www.gnu.org/licenses/>.
# --------------------------------------------------------------------------------


[Module Metadata]
AUTHOR=Sarah Edwards/mac4n6.com/@iamevltwin
MODULE_NOTES=Health Workout Average Cadence

[Database Metadata]
DATABASE=healthdb_secure.sqlite
PLATFORM=IOS
VERSIONS=12,13,14

[Query Metadata]
QUERY_NAME=health_workout_cadence
ACTIVITY=Health Workout Average Cadence
KEY_TIMESTAMP=START DATE

[SQL Query 12,13,14]
QUERY=
	SELECT
	    DATETIME(SAMPLES.START_DATE + 978307200, 'UNIXEPOCH') AS "START DATE",
	    DATETIME(SAMPLES.END_DATE + 978307200, 'UNIXEPOCH') AS "END DATE",
	    METADATA_VALUES.NUMERICAL_VALUE AS "SPM (strides/min)",
	    CASE WORKOUTS.ACTIVITY_TYPE 
	        WHEN 63 THEN "HIGH INTENSITY INTERVAL TRAINING (HIIT)" 
	        WHEN 37 THEN "INDOOR / OUTDOOR RUN" 
	        WHEN 3000 THEN "OTHER" 
	        WHEN 52 THEN "INDOOR / OUTDOOR WALK" 
	        WHEN 20 THEN "FUNCTIONAL TRAINING" 
	        WHEN 13 THEN "INDOOR CYCLE" 
	        WHEN 16 THEN "ELLIPTICAL" 
	        WHEN 35 THEN "ROWER"
	        ELSE "UNKNOWN" || "-" || WORKOUTS.ACTIVITY_TYPE 
	    END "WORKOUT TYPE", 
		WORKOUTS.DURATION / 60.00 AS "DURATION (IN MINUTES)", 
		WORKOUTS.TOTAL_ENERGY_BURNED AS "CALORIES BURNED", 
		WORKOUTS.TOTAL_DISTANCE AS "DISTANCE IN KILOMETERS",
		WORKOUTS.TOTAL_DISTANCE*0.621371 AS "DISTANCE IN MILES",  
		WORKOUTS.TOTAL_BASAL_ENERGY_BURNED AS "TOTAL BASEL ENERGY BURNED", 
	    CASE WORKOUTS.GOAL_TYPE 
	        WHEN 2 THEN "MINUTES" 
	        WHEN 0 THEN "OPEN" 
	    END "GOAL TYPE", 
		WORKOUTS.GOAL AS "GOAL", 
		WORKOUTS.TOTAL_FLIGHTS_CLIMBED AS "FLIGHTS CLIMBED", 
		WORKOUTS.TOTAL_W_STEPS AS "STEPS" 
	FROM
	    SAMPLES 
	    LEFT OUTER JOIN
	        METADATA_VALUES 
	        ON METADATA_VALUES.OBJECT_ID = SAMPLES.DATA_ID 
	    LEFT OUTER JOIN
	        METADATA_KEYS 
	        ON METADATA_KEYS.ROWID = METADATA_VALUES.KEY_ID 
	    LEFT OUTER JOIN
	        WORKOUTS 
	        ON WORKOUTS.DATA_ID = SAMPLES.DATA_ID 
	WHERE
	    WORKOUTS.ACTIVITY_TYPE NOT NULL AND KEY IS "_HKPrivateWorkoutAverageCadence"