/**
* @covers PDOforRunalyze::escape
*/
public function testEscape()
{
$this->assertEquals('NULL', $this->object->escape(null));
$this->assertEquals(1, $this->object->escape(true));
$this->assertEquals(0, $this->object->escape(false));
$this->assertEquals(array(0.123, '\'5\\" OR 1=1\''), $this->object->escape(array(0.123, '5" OR 1=1')));
}
/**
* Test inserts
*/
public function testInserts()
{
$TestClothes = $this->DB->insert('runalyze_clothes', array('name'), array('Test-Clothes'));
$TestSport = $this->DB->insert('runalyze_sport', array('name'), array('Testsport'));
$TestType = $this->DB->insert('runalyze_type', array('name', 'sportid'), array('Testtype', $TestSport));
// Act
$Importer = new RunalyzeJsonImporter('../tests/testfiles/backup/default-insert.json.gz');
$Importer->importData();
// Check nothing changed
$this->assertEquals($TestClothes, $this->DB->query('SELECT `id` FROM `runalyze_clothes` WHERE `name`="Test-Clothes"')->fetchColumn());
$this->assertEquals($TestSport, $this->DB->query('SELECT `id` FROM `runalyze_sport` WHERE `name`="Testsport"')->fetchColumn());
$this->assertEquals($TestType, $this->DB->query('SELECT `id` FROM `runalyze_type` WHERE `name`="Testtype"')->fetchColumn());
// Check existing/new
$NewClothes = $this->DB->query('SELECT `id` FROM `runalyze_clothes` WHERE `name`="New-Clothes"')->fetchColumn();
$NewSport = $this->DB->query('SELECT `id` FROM `runalyze_sport` WHERE `name`="Newsport"')->fetchColumn();
$NewType = $this->DB->query('SELECT `id` FROM `runalyze_type` WHERE `name`="Newtype"')->fetchColumn();
$NewShoe = $this->DB->query('SELECT `id` FROM `runalyze_shoe` WHERE `name`="Testshoe"')->fetchColumn();
$this->assertNotEquals(0, $NewClothes);
$this->assertNotEquals(0, $NewSport);
$this->assertNotEquals(0, $NewType);
$this->assertNotEquals(0, $NewShoe);
// Check inserts
$this->assertEquals(array('time' => '1234567890', 'weight' => '70', 'pulse_rest' => '45', 'pulse_max' => '205'), $this->DB->query('SELECT `time`, `weight`, `pulse_rest`, `pulse_max` FROM `runalyze_user` WHERE `time`="1234567890" LIMIT 1')->fetch());
$this->assertEquals(array('time' => '1234567890', 'sportid' => $TestSport, 'typeid' => $TestType, 's' => '900.00', 'clothes' => $TestClothes . ',' . $NewClothes, 'shoeid' => '0'), $this->DB->query('SELECT `time`, `sportid`, `typeid`, `s`, `clothes`, `shoeid` FROM `runalyze_training` WHERE `comment`="UNITTEST-1" LIMIT 1')->fetch());
$this->assertEquals(array('time' => '1234567890', 'sportid' => $NewSport, 'typeid' => $NewType, 's' => '1500.00', 'clothes' => $NewClothes, 'shoeid' => $NewShoe), $this->DB->query('SELECT `time`, `sportid`, `typeid`, `s`, `clothes`, `shoeid` FROM `runalyze_training` WHERE `comment`="UNITTEST-2" LIMIT 1')->fetch());
}
/**
* Init $this->sports_short
*/
protected function initShortSports()
{
$this->sports_short = array();
$sports = $this->DB->query('SELECT `id` FROM `' . PREFIX . 'sport` WHERE `short`=1 AND accountid = ' . SessionAccountHandler::getId())->fetchAll();
foreach ($sports as $sport) {
$this->sports_short[] = $sport['id'];
}
}
/**
* Execute
* @param \PDOforRunalyze $DB
*/
public function execute(\PDOforRunalyze $DB)
{
$this->Data = array();
$Today = new \DateTime('today 23:59');
$Statement = $DB->query($this->query());
while ($row = $Statement->fetch()) {
// Don't rely on MySQLs timezone => calculate diff based on timestamp
$index = (int) $Today->diff(new \DateTime('@' . $row['time']))->format('%r%a');
$this->Data[$index] = $row['trimp'];
}
}
/**
* Get order
* @return string
*/
protected function getOrder()
{
$sort = !isset($_POST['search-sort-by']) || array_key_exists($_POST['search-sort-by'], $this->AllowedKeys) ? '`time`' : $this->DB->escape($_POST['search-sort-by'], false);
$order = !isset($_POST['search-sort-order']) ? 'DESC' : $this->DB->escape($_POST['search-sort-order'], false);
if ($sort == 'vdot' && Configuration::Vdot()->useElevationCorrection()) {
return ' ORDER BY IF(`t`.`vdot_with_elevation`>0, `t`.`vdot_with_elevation`, `t`.`vdot`) ' . $order;
}
if ($sort == 'pace') {
return ' ORDER BY IF(`t`.`distance`>0, `t`.`s`/`t`.`distance`, 0) ' . $order;
}
return ' ORDER BY `t`.' . $sort . ' ' . $order;
}
/**
* Fetch data
* @param string $tablename
* @param int $id
* @return array
*/
protected function fetch($tablename, $id)
{
$field = $this->primaryKey($tablename);
$AndAccountID = $this->hasAccountID() && $this->tableHasAccountid($tablename) ? 'AND `accountid`=' . (int) $this->AccountID : '';
// TODO:
// provide a full fetch for tables as sport / type
$result = $this->DB->query('SELECT * FROM `' . PREFIX . $tablename . '` WHERE `' . $field . '`=' . (int) $id . ' ' . $AndAccountID . ' LIMIT 1')->fetch();
if (!is_array($result)) {
return array();
}
return $result;
}
/**
* Fetch data
* @param string $tablename
* @param int $id
* @param boolean $fullFetch
* @return array
*/
protected function fetch($tablename, $id, $fullFetch = false)
{
$field = $this->primaryKey($tablename);
$AndAccountID = $this->hasAccountID() && $this->tableHasAccountid($tablename) ? 'AND `accountid`=' . (int) $this->AccountID : '';
if ($fullFetch) {
$result = $this->DB->query('SELECT * FROM `' . PREFIX . $tablename . '` WHERE 1 ' . $AndAccountID . ' ' . $this->orderBy($tablename))->fetchAll();
} else {
$result = $this->DB->query('SELECT * FROM `' . PREFIX . $tablename . '` WHERE `' . $field . '`=' . (int) $id . ' ' . $AndAccountID . ' LIMIT 1')->fetch();
}
if (!is_array($result)) {
return array();
}
return $result;
}
/**
* Correct references in configuration
*/
private function correctConfigReferences()
{
if (isset($_POST['overwrite_config'])) {
$ConfigValues = Configuration\Handle::tableHandles();
foreach ($ConfigValues as $key => $table) {
$table = 'runalyze_' . $table;
if (isset($this->ReplaceIDs[$table])) {
$OldValue = $this->DB->query('SELECT `value` FROM `' . PREFIX . 'conf` WHERE `key`="' . $key . '" LIMIT 1')->fetchColumn();
$NewValue = $this->correctID($table, $OldValue);
if ($NewValue != 0) {
$this->DB->updateWhere('conf', '`key`="' . $key . '"', 'value', $NewValue);
}
}
}
}
}
/**
* Test deletes
*/
public function testDontDeleteTooMuch()
{
$_POST['delete_trainings'] = true;
// Data of account 0
$this->DB->exec('INSERT INTO `runalyze_equipment_type` (`accountid`,`name`) VALUES(0, "")');
$FirstEquipmentType = $this->DB->lastInsertId();
$this->DB->exec('INSERT INTO `runalyze_equipment` (`accountid`,`typeid`,`name`,`notes`) VALUES(0, ' . $FirstEquipmentType . ', "", "")');
$FirstEquipment = $this->DB->lastInsertId();
$this->DB->exec('INSERT INTO `runalyze_route` (`accountid`) VALUES(0)');
$FirstRoute = $this->DB->lastInsertId();
$this->DB->exec('INSERT INTO `runalyze_training` (`accountid`,`routeid`) VALUES(0, ' . $FirstRoute . ')');
$FirstTraining = $this->DB->lastInsertId();
$this->DB->exec('INSERT INTO `runalyze_trackdata` (`accountid`,`activityid`) VALUES(0, ' . $FirstTraining . ')');
$this->DB->exec('INSERT INTO `runalyze_swimdata` (`accountid`,`activityid`) VALUES(0, ' . $FirstTraining . ')');
$this->DB->exec('INSERT INTO `runalyze_hrv` (`accountid`,`activityid`) VALUES(0, ' . $FirstTraining . ')');
$this->DB->exec('INSERT INTO `runalyze_activity_equipment` (`activityid`,`equipmentid`) VALUES(' . $FirstTraining . ', ' . $FirstEquipment . ')');
// Data of account 1
$this->DB->exec('INSERT INTO `runalyze_equipment_type` (`accountid`,`name`) VALUES(1, "")');
$SecondEquipmentType = $this->DB->lastInsertId();
$this->DB->exec('INSERT INTO `runalyze_equipment` (`accountid`,`typeid`,`name`,`notes`) VALUES(1, ' . $SecondEquipmentType . ', "", "")');
$SecondEquipment = $this->DB->lastInsertId();
$this->DB->exec('INSERT INTO `runalyze_route` (`accountid`) VALUES(1)');
$SecondRoute = $this->DB->lastInsertId();
$this->DB->exec('INSERT INTO `runalyze_training` (`accountid`,`routeid`) VALUES(1, ' . $SecondRoute . ')');
$SecondTraining = $this->DB->lastInsertId();
$this->DB->exec('INSERT INTO `runalyze_trackdata` (`accountid`,`activityid`) VALUES(1, ' . $SecondTraining . ')');
$this->DB->exec('INSERT INTO `runalyze_swimdata` (`accountid`,`activityid`) VALUES(1, ' . $SecondTraining . ')');
$this->DB->exec('INSERT INTO `runalyze_hrv` (`accountid`,`activityid`) VALUES(1, ' . $SecondTraining . ')');
$this->DB->exec('INSERT INTO `runalyze_activity_equipment` (`activityid`,`equipmentid`) VALUES(' . $SecondTraining . ', ' . $SecondEquipment . ')');
$Importer = new RunalyzeJsonImporter('../tests/testfiles/backup/default-empty.json.gz', 0);
$Importer->importData();
$this->assertEquals(0, $this->DB->query('SELECT COUNT(*) FROM `runalyze_route` WHERE `accountid`=0')->fetchColumn());
$this->assertEquals(0, $this->DB->query('SELECT COUNT(*) FROM `runalyze_training` WHERE `accountid`=0')->fetchColumn());
$this->assertEquals(0, $this->DB->query('SELECT COUNT(*) FROM `runalyze_trackdata` WHERE `accountid`=0')->fetchColumn());
$this->assertEquals(0, $this->DB->query('SELECT COUNT(*) FROM `runalyze_swimdata` WHERE `accountid`=0')->fetchColumn());
$this->assertEquals(0, $this->DB->query('SELECT COUNT(*) FROM `runalyze_hrv` WHERE `accountid`=0')->fetchColumn());
$this->assertEquals(0, $this->DB->query('SELECT COUNT(*) FROM `runalyze_activity_equipment` WHERE `equipmentid`=' . $FirstEquipment)->fetchColumn());
$this->assertEquals(1, $this->DB->query('SELECT COUNT(*) FROM `runalyze_route` WHERE `accountid`=1')->fetchColumn());
$this->assertEquals(1, $this->DB->query('SELECT COUNT(*) FROM `runalyze_training` WHERE `accountid`=1')->fetchColumn());
$this->assertEquals(1, $this->DB->query('SELECT COUNT(*) FROM `runalyze_trackdata` WHERE `accountid`=1')->fetchColumn());
$this->assertEquals(1, $this->DB->query('SELECT COUNT(*) FROM `runalyze_swimdata` WHERE `accountid`=1')->fetchColumn());
$this->assertEquals(1, $this->DB->query('SELECT COUNT(*) FROM `runalyze_hrv` WHERE `accountid`=1')->fetchColumn());
$this->assertEquals(1, $this->DB->query('SELECT COUNT(*) FROM `runalyze_activity_equipment` WHERE `equipmentid`=' . $SecondEquipment)->fetchColumn());
}
/**
* Test with existing equipment
*/
public function testWithExistingEquipment()
{
$this->DB->exec('INSERT INTO `runalyze_sport` (`name`, `accountid`) VALUES("Sport A", ' . $this->AccountID . ')');
$ExistingSportA = $this->DB->lastInsertId();
$this->DB->exec('INSERT INTO `runalyze_equipment_type` (`name`, `accountid`) VALUES("Typ A", ' . $this->AccountID . ')');
$ExistingTypeA = $this->DB->lastInsertId();
$this->DB->exec('INSERT INTO `runalyze_equipment_type` (`name`, `accountid`) VALUES("Typ AB", ' . $this->AccountID . ')');
$ExistingTypeAB = $this->DB->lastInsertId();
$this->DB->exec('INSERT INTO `runalyze_equipment_sport` (`sportid`, `equipment_typeid`) VALUES(' . $ExistingSportA . ', ' . $ExistingTypeA . ')');
$Importer = new RunalyzeJsonImporter('../tests/testfiles/backup/with-equipment.json.gz', $this->AccountID);
$Importer->importData();
$SportA = $this->DB->query('SELECT `id` FROM `runalyze_sport` WHERE `name`="Sport A"')->fetchColumn();
$SportB = $this->DB->query('SELECT `id` FROM `runalyze_sport` WHERE `name`="Sport B"')->fetchColumn();
$this->assertEquals($ExistingSportA, $SportA);
$TypeA = $this->DB->query('SELECT `id` FROM `runalyze_equipment_type` WHERE `name`="Typ A"')->fetchColumn();
$TypeAB = $this->DB->query('SELECT `id` FROM `runalyze_equipment_type` WHERE `name`="Typ AB"')->fetchColumn();
$this->assertEquals($ExistingTypeA, $TypeA);
$this->assertEquals($ExistingTypeAB, $TypeAB);
$this->assertEquals(array(array($SportA, $TypeA), array($SportA, $TypeAB), array($SportB, $TypeAB)), $this->DB->query('SELECT `sportid`, `equipment_typeid` FROM `runalyze_equipment_sport`')->fetchAll(PDO::FETCH_NUM));
}
/**
* Insert
* @param array $Values
* @return int last inserted ID
*/
public function insert(array $Values)
{
$this->Statement->execute($Values);
return $this->DB->lastInsertId();
}
/**
* Init $this->sports_short
*/
protected function initShortModes()
{
$this->sports_short = $this->DB->query('SELECT `id` FROM `' . PREFIX . 'sport` WHERE `short`=1 AND accountid = ' . SessionAccountHandler::getId())->fetchAll(PDO::FETCH_COLUMN);
$this->types_short = $this->DB->query('SELECT `id` FROM `' . PREFIX . 'type` WHERE `short`=1 AND accountid = ' . SessionAccountHandler::getId())->fetchAll(PDO::FETCH_COLUMN);
}
/**
* Init $this->sports_short
*/
protected function initShortModes()
{
$this->SportsShort = $this->DB->query('SELECT `id` FROM `' . PREFIX . 'sport` WHERE `short`=1 AND accountid = ' . $this->AccountID)->fetchAll(PDO::FETCH_COLUMN);
$this->TypesShort = $this->DB->query('SELECT `id` FROM `' . PREFIX . 'type` WHERE `short`=1 AND accountid = ' . $this->AccountID)->fetchAll(PDO::FETCH_COLUMN);
}
protected function tearDown()
{
$this->DB->exec('TRUNCATE TABLE `runalyze_training`');
}
/**
* Equipment type IDs
* @return array
*/
private function fetchEquipmentTypeIDs()
{
return $this->DB->query('SELECT `id` FROM `' . PREFIX . 'equipment_type` WHERE `accountid`=' . $this->AccountID)->fetchAll(PDO::FETCH_COLUMN);
}
public function testEquipment()
{
$this->DB->exec('INSERT INTO `runalyze_sport` (`id`, `name`, `accountid`) VALUES(1, "Sport A", ' . $this->accountID . ')');
$this->DB->exec('INSERT INTO `runalyze_sport` (`id`, `name`, `accountid`) VALUES(2, "Sport B", ' . $this->accountID . ')');
$this->DB->exec('INSERT INTO `runalyze_sport` (`id`, `name`, `accountid`) VALUES(3, "Sport C", ' . $this->accountID . ')');
$this->DB->exec('INSERT INTO `runalyze_equipment_type` (`id`, `name`, `accountid`) VALUES(1, "Equipment Type AB", ' . $this->accountID . ')');
$this->DB->exec('INSERT INTO `runalyze_equipment_type` (`id`, `name`, `accountid`) VALUES(2, "Equipment Type C", ' . $this->accountID . ')');
$this->DB->exec('INSERT INTO `runalyze_equipment_sport` (`sportid`, `equipment_typeid`) VALUES(1, 1)');
$this->DB->exec('INSERT INTO `runalyze_equipment_sport` (`sportid`, `equipment_typeid`) VALUES(2, 1)');
$this->DB->exec('INSERT INTO `runalyze_equipment_sport` (`sportid`, `equipment_typeid`) VALUES(3, 2)');
$this->DB->exec('INSERT INTO `runalyze_equipment` (`id`, `name`, `typeid`, `notes`, `accountid`) VALUES(1, "Equipment A1", 1, "", ' . $this->accountID . ')');
$this->DB->exec('INSERT INTO `runalyze_equipment` (`id`, `name`, `typeid`, `notes`, `accountid`) VALUES(2, "Equipment A2", 1, "", ' . $this->accountID . ')');
$this->DB->exec('INSERT INTO `runalyze_equipment` (`id`, `name`, `typeid`, `notes`, `accountid`) VALUES(3, "Equipment B", 1, "", ' . $this->accountID . ')');
$this->DB->exec('INSERT INTO `runalyze_equipment` (`id`, `name`, `typeid`, `notes`, `accountid`) VALUES(4, "Equipment C", 2, "", ' . $this->accountID . ')');
$this->DB->exec('INSERT INTO `runalyze_training` (`id`, `sportid`, `accountid`) VALUES(1, 1, ' . $this->accountID . ')');
$this->DB->exec('INSERT INTO `runalyze_training` (`id`, `sportid`, `accountid`) VALUES(2, 2, ' . $this->accountID . ')');
$this->DB->exec('INSERT INTO `runalyze_training` (`id`, `sportid`, `accountid`) VALUES(3, 3, ' . $this->accountID . ')');
$this->DB->exec('INSERT INTO `runalyze_activity_equipment` (`activityid`, `equipmentid`) VALUES(1, 1)');
$this->DB->exec('INSERT INTO `runalyze_activity_equipment` (`activityid`, `equipmentid`) VALUES(1, 2)');
$this->DB->exec('INSERT INTO `runalyze_activity_equipment` (`activityid`, `equipmentid`) VALUES(2, 3)');
$this->DB->exec('INSERT INTO `runalyze_activity_equipment` (`activityid`, `equipmentid`) VALUES(3, 4)');
$this->assertEquals(array(1, 2), $this->object->sportForEquipmentType(1, true));
$this->assertEquals(array($this->object->sport(1), $this->object->sport(2)), $this->object->sportForEquipmentType(1));
$this->assertEquals(array(3), $this->object->sportForEquipmentType(2, true));
$this->assertEquals(array($this->object->sport(3)), $this->object->sportForEquipmentType(2));
$this->assertEquals(array(1, 2), $this->object->equipmentForActivity(1, true));
$this->assertEquals(array($this->object->equipment(1), $this->object->equipment(2)), $this->object->equipmentForActivity(1));
$this->assertEquals(array(3), $this->object->equipmentForActivity(2, true));
$this->assertEquals(array($this->object->equipment(3)), $this->object->equipmentForActivity(2));
$this->assertEquals(array(4), $this->object->equipmentForActivity(3, true));
$this->assertEquals(array($this->object->equipment(4)), $this->object->equipmentForActivity(3));
$this->assertEquals(array(), $this->object->allTypes());
$this->assertEquals(array($this->object->sport(1), $this->object->sport(2), $this->object->sport(3)), $this->object->allSports());
$this->assertEquals(array($this->object->equipmentType(1), $this->object->equipmentType(2)), $this->object->allEquipmentTypes());
$this->assertEquals(array($this->object->equipment(1), $this->object->equipment(2), $this->object->equipment(3), $this->object->equipment(4)), $this->object->allEquipments());
}
protected function tearDown()
{
$this->PDO->exec('DELETE FROM `runalyze_training`');
}
/**
* Plugin IDs
* @return array
*/
private function fetchPluginIDs()
{
return $this->DB->query('SELECT `id` FROM `' . PREFIX . 'plugin` WHERE `accountid`=' . $this->AccountID)->fetchAll(PDO::FETCH_COLUMN);
}
