/**
* test with a row without child
*
* @group Core
* @group DataTable
* @group DataTable_Renderer
* @group DataTable_Renderer_Console
*/
public function testConsoleSimple()
{
$table = new Piwik_DataTable();
$table->addRowFromArray(array(Piwik_DataTable_Row::COLUMNS => array('visits' => 245, 'visitors' => 245), Piwik_DataTable_Row::METADATA => array('logo' => 'test.png')));
$expected = "- 1 ['visits' => 245, 'visitors' => 245] ['logo' => 'test.png'] [idsubtable = ]<br />\n";
$render = new Piwik_DataTable_Renderer_Console();
$render->setTable($table);
$rendered = $render->render();
$this->assertEquals($expected, $rendered);
}
private static function getCleanedVisitorsFromDetails($visitorDetails)
{
$table = new Piwik_DataTable();
foreach ($visitorDetails as $visitorDetail) {
self::cleanVisitorDetails($visitorDetail);
$visitor = new Piwik_Live_Visitor($visitorDetail);
$visitorDetailsArray = $visitor->getAllVisitorDetails();
$dateTimeVisit = Piwik_Date::factory($visitorDetailsArray['firstActionTimestamp']);
//TODO TO FIX
$visitorDetailsArray['serverDatePretty'] = $dateTimeVisit->getLocalized('%shortDay% %day% %shortMonth%');
$visitorDetailsArray['serverTimePretty'] = $dateTimeVisit->getLocalized('%time%');
$table->addRowFromArray(array(Piwik_DataTable_Row::COLUMNS => $visitorDetailsArray));
}
return $table;
}
/**
* General tests that tries to test the normal behaviour of DataTable
*
* We create some tables, add rows, some of the rows link to sub tables
*
* Then we serialize everything, and we check that the unserialize give the same object back
*
* @group Core
* @group DataTable
*/
public function testGeneral()
{
/*
* create some fake tables to make sure that the serialized array of the first TABLE
* does not take in consideration those tables
*/
$useless1 = new Piwik_DataTable();
$useless1->addRowFromArray(array(Piwik_DataTable_Row::COLUMNS => array(13)));
/*
* end fake tables
*/
/*
* MAIN TABLE
*/
$table = new Piwik_DataTable();
$subtable = new Piwik_DataTable();
$idtable = $table->getId();
$idsubtable = $subtable->getId();
/*
* create some fake tables to make sure that the serialized array of the first TABLE
* does not take in consideration those tables
* -> we check that the DataTable_Manager is not impacting DataTable
*/
$useless2 = new Piwik_DataTable();
$useless1->addRowFromArray(array(Piwik_DataTable_Row::COLUMNS => array(8487)));
$useless3 = new Piwik_DataTable();
$useless3->addRowFromArray(array(Piwik_DataTable_Row::COLUMNS => array(8487)));
/*
* end fake tables
*/
$row = array(Piwik_DataTable_Row::COLUMNS => array(0 => 1554, 1 => 42, 2 => 657, 3 => 155744), Piwik_DataTable_Row::METADATA => array('logo' => 'test.png'));
$row = new Piwik_DataTable_Row($row);
$table->addRow($row);
$table->addRowFromArray(array(Piwik_DataTable_Row::COLUMNS => array(0 => 1554, 1 => 42), Piwik_DataTable_Row::METADATA => array('url' => 'piwik.org')));
$table->addRowFromArray(array(Piwik_DataTable_Row::COLUMNS => array(0 => 787877888787.0), Piwik_DataTable_Row::METADATA => array('url' => 'OUPLA ADDED'), Piwik_DataTable_Row::DATATABLE_ASSOCIATED => $subtable));
/*
* SUB TABLE
*/
$row = array(Piwik_DataTable_Row::COLUMNS => array(0 => 1554), Piwik_DataTable_Row::METADATA => array('searchengine' => 'google'));
$subtable->addRowFromArray($row);
$row = array(Piwik_DataTable_Row::COLUMNS => array(0 => 84894), Piwik_DataTable_Row::METADATA => array('searchengine' => 'yahoo'));
$subtable->addRowFromArray($row);
$row = array(Piwik_DataTable_Row::COLUMNS => array(0 => 4898978989.0), Piwik_DataTable_Row::METADATA => array('searchengine' => 'ask'));
$subtable->addRowFromArray($row);
/*
* SUB SUB TABLE
*/
$subsubtable = new Piwik_DataTable();
$subsubtable->addRowFromArray(array(Piwik_DataTable_Row::COLUMNS => array(245), Piwik_DataTable_Row::METADATA => array('yes' => 'subsubmetadata1')));
$subsubtable->addRowFromArray(array(Piwik_DataTable_Row::COLUMNS => array(13), Piwik_DataTable_Row::METADATA => array('yes' => 'subsubmetadata2')));
$row = array(Piwik_DataTable_Row::COLUMNS => array(0 => 666666666666666.0), Piwik_DataTable_Row::METADATA => array('url' => 'NEW ROW ADDED'), Piwik_DataTable_Row::DATATABLE_ASSOCIATED => $subsubtable);
$subtable->addRowFromArray($row);
$idsubsubtable = $subsubtable->getId();
$serialized = $table->getSerialized();
$this->assertEquals(array_keys($serialized), array($idsubsubtable, $idsubtable, 0));
// In the next test we compare an unserialized datatable with its original instance.
// The unserialized datatable rows will have positive DATATABLE_ASSOCIATED ids.
// Positive DATATABLE_ASSOCIATED ids mean that the associated sub-datatables are not loaded in memory.
// In this case, this is NOT true: we know that the sub-datatable is loaded in memory.
// HOWEVER, because of datatable id conflicts happening in the datatable manager, it is not yet
// possible to know, after unserializing a datatable, if its sub-datatables are loaded in memory.
$expectedTableRows = array();
foreach ($table->getRows() as $currentRow) {
$expectedTableRow = clone $currentRow;
$currentRowAssociatedDatatableId = $currentRow->c[Piwik_DataTable_Row::DATATABLE_ASSOCIATED];
if ($currentRowAssociatedDatatableId != null) {
// making DATATABLE_ASSOCIATED ids positive
$expectedTableRow->c[Piwik_DataTable_Row::DATATABLE_ASSOCIATED] = -1 * $currentRowAssociatedDatatableId;
}
$expectedTableRows[] = $expectedTableRow;
}
$tableAfter = new Piwik_DataTable();
$tableAfter->addRowsFromSerializedArray($serialized[0]);
$this->assertEquals($expectedTableRows, $tableAfter->getRows());
$subsubtableAfter = new Piwik_DataTable();
$subsubtableAfter->addRowsFromSerializedArray($serialized[$idsubsubtable]);
$this->assertEquals($subsubtable->getRows(), $subsubtableAfter->getRows());
$this->assertEquals($table, Piwik_DataTable_Manager::getInstance()->getTable($idtable));
$this->assertEquals($subsubtable, Piwik_DataTable_Manager::getInstance()->getTable($idsubsubtable));
}
/**
* General tests that tries to test the normal behaviour of DataTable
*
* We create some tables, add rows, some of the rows link to sub tables
*
* Then we serialize everything, and we check that the unserialize give the same object back
*/
function test_general()
{
/*
* create some fake tables to make sure that the serialized array of the first TABLE
* does not take in consideration those tables
*/
$useless1 = new Piwik_DataTable();
$useless1->addRowFromArray(array(Piwik_DataTable_Row::COLUMNS => array(13)));
/*
* end fake tables
*/
/*
* MAIN TABLE
*/
$table = new Piwik_DataTable();
$subtable = new Piwik_DataTable();
$idtable = $table->getId();
$idsubtable = $subtable->getId();
/*
* create some fake tables to make sure that the serialized array of the first TABLE
* does not take in consideration those tables
* -> we check that the DataTable_Manager is not impacting DataTable
*/
$useless2 = new Piwik_DataTable();
$useless1->addRowFromArray(array(Piwik_DataTable_Row::COLUMNS => array(8487)));
$useless3 = new Piwik_DataTable();
$useless3->addRowFromArray(array(Piwik_DataTable_Row::COLUMNS => array(8487)));
/*
* end fake tables
*/
$row = array(Piwik_DataTable_Row::COLUMNS => array(0 => 1554, 1 => 42, 2 => 657, 3 => 155744), Piwik_DataTable_Row::METADATA => array('logo' => 'test.png'));
$row = new Piwik_DataTable_Row($row);
$table->addRow($row);
$table->addRowFromArray(array(Piwik_DataTable_Row::COLUMNS => array(0 => 1554, 1 => 42), Piwik_DataTable_Row::METADATA => array('url' => 'piwik.org')));
$table->addRowFromArray(array(Piwik_DataTable_Row::COLUMNS => array(0 => 787877888787), Piwik_DataTable_Row::METADATA => array('url' => 'OUPLA ADDED'), Piwik_DataTable_Row::DATATABLE_ASSOCIATED => $subtable));
/*
* SUB TABLE
*/
$row = array(Piwik_DataTable_Row::COLUMNS => array(0 => 1554), Piwik_DataTable_Row::METADATA => array('searchengine' => 'google'));
$subtable->addRowFromArray($row);
$row = array(Piwik_DataTable_Row::COLUMNS => array(0 => 84894), Piwik_DataTable_Row::METADATA => array('searchengine' => 'yahoo'));
$subtable->addRowFromArray($row);
$row = array(Piwik_DataTable_Row::COLUMNS => array(0 => 4898978989), Piwik_DataTable_Row::METADATA => array('searchengine' => 'ask'));
$subtable->addRowFromArray($row);
/*
* SUB SUB TABLE
*/
$subsubtable = new Piwik_DataTable();
$subsubtable->addRowFromArray(array(Piwik_DataTable_Row::COLUMNS => array(245), Piwik_DataTable_Row::METADATA => array('yes' => 'subsubmetadata1')));
$subsubtable->addRowFromArray(array(Piwik_DataTable_Row::COLUMNS => array(13), Piwik_DataTable_Row::METADATA => array('yes' => 'subsubmetadata2')));
$row = array(Piwik_DataTable_Row::COLUMNS => array(0 => 666666666666666), Piwik_DataTable_Row::METADATA => array('url' => 'NEW ROW ADDED'), Piwik_DataTable_Row::DATATABLE_ASSOCIATED => $subsubtable);
$subtable->addRowFromArray($row);
$idsubsubtable = $subsubtable->getId();
$serialized = $table->getSerialized();
$this->assertEqual(array_keys($serialized), array($idsubsubtable, $idsubtable, 0));
$tableAfter = new Piwik_DataTable();
$tableAfter->addRowsFromSerializedArray($serialized[0]);
$this->assertEqual($table->getRows(), $tableAfter->getRows());
$subsubtableAfter = new Piwik_DataTable();
$subsubtableAfter->addRowsFromSerializedArray($serialized[$idsubsubtable]);
$this->assertEqual($subsubtable->getRows(), $subsubtableAfter->getRows());
$this->assertEqual($table, Piwik_DataTable_Manager::getInstance()->getTable($idtable));
$this->assertEqual($subsubtable, Piwik_DataTable_Manager::getInstance()->getTable($idsubsubtable));
}
/**
* For an array of visits, query the list of pages for this visit
* as well as make the data human readable
*/
private function getCleanedVisitorsFromDetails($visitorDetails, $idSite)
{
$table = new Piwik_DataTable();
$site = new Piwik_Site($idSite);
$timezone = $site->getTimezone();
$currencies = Piwik_SitesManager_API::getInstance()->getCurrencySymbols();
foreach($visitorDetails as $visitorDetail)
{
$this->cleanVisitorDetails($visitorDetail, $idSite);
$visitor = new Piwik_Live_Visitor($visitorDetail);
$visitorDetailsArray = $visitor->getAllVisitorDetails();
$visitorDetailsArray['siteCurrency'] = $site->getCurrency();
$visitorDetailsArray['siteCurrencySymbol'] = @$currencies[$site->getCurrency()];
$visitorDetailsArray['serverTimestamp'] = $visitorDetailsArray['lastActionTimestamp'];
$dateTimeVisit = Piwik_Date::factory($visitorDetailsArray['lastActionTimestamp'], $timezone);
$visitorDetailsArray['serverTimePretty'] = $dateTimeVisit->getLocalized('%time%');
$visitorDetailsArray['serverDatePretty'] = $dateTimeVisit->getLocalized('%shortDay% %day% %shortMonth%');
$dateTimeVisitFirstAction = Piwik_Date::factory($visitorDetailsArray['firstActionTimestamp'], $timezone);
$visitorDetailsArray['serverDatePrettyFirstAction'] = $dateTimeVisitFirstAction->getLocalized('%shortDay% %day% %shortMonth%');
$visitorDetailsArray['serverTimePrettyFirstAction'] = $dateTimeVisitFirstAction->getLocalized('%time%');
$idvisit = $visitorDetailsArray['idVisit'];
$sqlCustomVariables = '';
for($i = 1; $i <= Piwik_Tracker::MAX_CUSTOM_VARIABLES; $i++)
{
$sqlCustomVariables .= ', custom_var_k' . $i . ', custom_var_v' . $i;
}
// The second join is a LEFT join to allow returning records that don't have a matching page title
// eg. Downloads, Outlinks. For these, idaction_name is set to 0
$sql = "
SELECT
log_action.type as type,
log_action.name AS url,
log_action_title.name AS pageTitle,
log_action.idaction AS pageIdAction,
log_link_visit_action.idlink_va AS pageId,
log_link_visit_action.server_time as serverTimePretty
$sqlCustomVariables
FROM " .Piwik_Common::prefixTable('log_link_visit_action')." AS log_link_visit_action
INNER JOIN " .Piwik_Common::prefixTable('log_action')." AS log_action
ON log_link_visit_action.idaction_url = log_action.idaction
LEFT JOIN " .Piwik_Common::prefixTable('log_action')." AS log_action_title
ON log_link_visit_action.idaction_name = log_action_title.idaction
WHERE log_link_visit_action.idvisit = ?
";
$actionDetails = Piwik_FetchAll($sql, array($idvisit));
foreach($actionDetails as &$actionDetail)
{
$customVariablesPage = array();
for($i = 1; $i <= Piwik_Tracker::MAX_CUSTOM_VARIABLES; $i++)
{
if(!empty($actionDetail['custom_var_k'.$i])
&& !empty($actionDetail['custom_var_v'.$i]))
{
$customVariablesPage[$i] = array(
'customVariableName'.$i => $actionDetail['custom_var_k'.$i],
'customVariableValue'.$i => $actionDetail['custom_var_v'.$i],
);
}
unset($actionDetail['custom_var_k'.$i]);
unset($actionDetail['custom_var_v'.$i]);
}
if(!empty($customVariablesPage))
{
$actionDetail['customVariables'] = $customVariablesPage;
}
}
// If the visitor converted a goal, we shall select all Goals
$sql = "
SELECT
'goal' as type,
goal.name as goalName,
goal.revenue as revenue,
log_conversion.idlink_va as goalPageId,
log_conversion.server_time as serverTimePretty,
log_conversion.url as url
FROM ".Piwik_Common::prefixTable('log_conversion')." AS log_conversion
LEFT JOIN ".Piwik_Common::prefixTable('goal')." AS goal
ON (goal.idsite = log_conversion.idsite
AND
goal.idgoal = log_conversion.idgoal)
AND goal.deleted = 0
WHERE log_conversion.idvisit = ?
AND log_conversion.idgoal > 0
";
$goalDetails = Piwik_FetchAll($sql, array($idvisit));
$sql = "SELECT
case idgoal when ".Piwik_Tracker_GoalManager::IDGOAL_CART." then '".Piwik_Archive::LABEL_ECOMMERCE_CART."' else '".Piwik_Archive::LABEL_ECOMMERCE_ORDER."' end as type,
idorder as orderId,
".Piwik_ArchiveProcessing_Day::getSqlRevenue('revenue')." as revenue,
".Piwik_ArchiveProcessing_Day::getSqlRevenue('revenue_subtotal')." as revenueSubTotal,
".Piwik_ArchiveProcessing_Day::getSqlRevenue('revenue_tax')." as revenueTax,
".Piwik_ArchiveProcessing_Day::getSqlRevenue('revenue_shipping')." as revenueShipping,
".Piwik_ArchiveProcessing_Day::getSqlRevenue('revenue_discount')." as revenueDiscount,
items as items,
log_conversion.server_time as serverTimePretty
FROM ".Piwik_Common::prefixTable('log_conversion')." AS log_conversion
WHERE idvisit = ?
AND idgoal <= ".Piwik_Tracker_GoalManager::IDGOAL_ORDER;
$ecommerceDetails = Piwik_FetchAll($sql, array($idvisit));
foreach($ecommerceDetails as &$ecommerceDetail)
{
if($ecommerceDetail['type'] == Piwik_Archive::LABEL_ECOMMERCE_CART)
{
unset($ecommerceDetail['orderId']);
unset($ecommerceDetail['revenueSubTotal']);
unset($ecommerceDetail['revenueTax']);
unset($ecommerceDetail['revenueShipping']);
unset($ecommerceDetail['revenueDiscount']);
}
// 25.00 => 25
foreach($ecommerceDetail as $column => $value)
{
if(strpos($column, 'revenue') !== false)
{
if($value == round($value))
{
$ecommerceDetail[$column] = round($value);
}
}
}
}
$actions = array_merge($actionDetails, $goalDetails, $ecommerceDetails);
usort($actions, array($this, 'sortByServerTime'));
$visitorDetailsArray['actionDetails'] = $actions;
// Convert datetimes to the site timezone
foreach($visitorDetailsArray['actionDetails'] as &$details)
{
switch($details['type'])
{
case 'goal':
$details['icon'] = 'themes/default/images/goal.png';
break;
case Piwik_Archive::LABEL_ECOMMERCE_ORDER:
case Piwik_Archive::LABEL_ECOMMERCE_CART:
$details['icon'] = 'themes/default/images/'.$details['type'].'.gif';
break;
case Piwik_Tracker_Action_Interface::TYPE_DOWNLOAD:
$details['type'] = 'download';
$details['icon'] = 'themes/default/images/download.png';
break;
case Piwik_Tracker_Action_Interface::TYPE_OUTLINK:
$details['type'] = 'outlink';
$details['icon'] = 'themes/default/images/link.gif';
break;
default:
$details['type'] = 'action';
$details['icon'] = null;
break;
}
$dateTimeVisit = Piwik_Date::factory($details['serverTimePretty'], $timezone);
$details['serverTimePretty'] = $dateTimeVisit->getLocalized('%shortDay% %day% %shortMonth% %time%');
}
$visitorDetailsArray['goalConversions'] = count($goalDetails);
// Enrich ecommerce carts/orders with the list of products
usort($ecommerceDetails, array($this, 'sortByServerTime'));
foreach($ecommerceDetails as $key => &$ecommerceConversion)
{
$sql = "SELECT
log_action_sku.name as itemSKU,
log_action_name.name as itemName,
log_action_category.name as itemCategory,
".Piwik_ArchiveProcessing_Day::getSqlRevenue('price')." as price,
quantity as quantity
FROM ".Piwik_Common::prefixTable('log_conversion_item')."
INNER JOIN " .Piwik_Common::prefixTable('log_action')." AS log_action_sku
ON idaction_sku = log_action_sku.idaction
LEFT JOIN " .Piwik_Common::prefixTable('log_action')." AS log_action_name
ON idaction_name = log_action_name.idaction
LEFT JOIN " .Piwik_Common::prefixTable('log_action')." AS log_action_category
ON idaction_category = log_action_category.idaction
WHERE idvisit = ?
AND idorder = ?
AND deleted = 0
";
$bind = array($idvisit, isset($ecommerceConversion['orderId']) ? $ecommerceConversion['orderId'] : Piwik_Tracker_GoalManager::ITEM_IDORDER_ABANDONED_CART);
$itemsDetails = Piwik_FetchAll($sql, $bind);
foreach($itemsDetails as &$detail)
{
if($detail['price'] == round($detail['price']))
{
$detail['price'] = round($detail['price']);
}
}
$ecommerceConversion['itemDetails'] = $itemsDetails;
}
$table->addRowFromArray( array(Piwik_DataTable_Row::COLUMNS => $visitorDetailsArray));
}
return $table;
}
private function getCleanedVisitorsFromDetails($visitorDetails)
{
$table = new Piwik_DataTable();
foreach ($visitorDetails as $visitorDetail) {
$this->cleanVisitorDetails($visitorDetail);
$visitor = new Piwik_Live_Visitor($visitorDetail);
// $visitorDetail must contain the match_atribute
$visitorDetailsArray = $visitor->getAllVisitorDetails();
// $visitorDetailsArray['goalIcon'] = "themes/default/images/goal.png";
$dateTimeVisit = Piwik_Date::factory($visitorDetailsArray['firstActionTimestamp']);
//TODO TO FIX
$visitorDetailsArray['serverDatePretty'] = $dateTimeVisit->getLocalized('%shortDay% %day% %shortMonth%');
$visitorDetailsArray['serverTimePretty'] = $dateTimeVisit->getLocalized('%time%');
// get Detail - 100 single SQL Statements - Performance Issue
$idvisit = $visitorDetailsArray['idVisit'];
$sql = "SELECT DISTINCT `" . Piwik::prefixTable('log_action') . "`.`name` AS pageUrl\n\t\t\t\tFROM `" . Piwik::prefixTable('log_link_visit_action') . "`\n\t\t\t\t\tINNER JOIN `" . Piwik::prefixTable('log_action') . "` ON `" . Piwik::prefixTable('log_link_visit_action') . "`.`idaction_url` = `" . Piwik::prefixTable('log_action') . "`.`idaction`\n\t\t\t\tWHERE `" . Piwik::prefixTable('log_link_visit_action') . "`.`idvisit` = {$idvisit};\n\t\t\t\t ";
$visitorDetailsArray['actionDetails'] = Piwik_FetchAll($sql);
$sql = "SELECT DISTINCT `" . Piwik::prefixTable('log_action') . "`.`name` AS pageUrl\n\t\t\t\tFROM `" . Piwik::prefixTable('log_link_visit_action') . "`\n\t\t\t\t\tINNER JOIN `" . Piwik::prefixTable('log_action') . "` ON `" . Piwik::prefixTable('log_link_visit_action') . "`.`idaction_name` = `" . Piwik::prefixTable('log_action') . "`.`idaction`\n\t\t\t\tWHERE `" . Piwik::prefixTable('log_link_visit_action') . "`.`idvisit` = {$idvisit};\n\t\t\t\t ";
$visitorDetailsArray['actionDetailsTitle'] = Piwik_FetchAll($sql);
$table->addRowFromArray(array(Piwik_DataTable_Row::COLUMNS => $visitorDetailsArray));
}
return $table;
}
/**
* For an array of visits, query the list of pages for this visit
* as well as make the data human readable
*/
private function getCleanedVisitorsFromDetails($visitorDetails, $idSite)
{
$table = new Piwik_DataTable();
$site = new Piwik_Site($idSite);
$timezone = $site->getTimezone();
foreach ($visitorDetails as $visitorDetail) {
$this->cleanVisitorDetails($visitorDetail, $idSite);
$visitor = new Piwik_Live_Visitor($visitorDetail);
$visitorDetailsArray = $visitor->getAllVisitorDetails();
$visitorDetailsArray['siteCurrency'] = $site->getCurrency();
$visitorDetailsArray['serverTimestamp'] = $visitorDetailsArray['lastActionTimestamp'];
$dateTimeVisit = Piwik_Date::factory($visitorDetailsArray['lastActionTimestamp'], $timezone);
$visitorDetailsArray['serverTimePretty'] = $dateTimeVisit->getLocalized('%time%');
$visitorDetailsArray['serverDatePretty'] = $dateTimeVisit->getLocalized('%shortDay% %day% %shortMonth%');
$dateTimeVisitFirstAction = Piwik_Date::factory($visitorDetailsArray['firstActionTimestamp'], $timezone);
$visitorDetailsArray['serverDatePrettyFirstAction'] = $dateTimeVisitFirstAction->getLocalized('%shortDay% %day% %shortMonth%');
$visitorDetailsArray['serverTimePrettyFirstAction'] = $dateTimeVisitFirstAction->getLocalized('%time%');
$idvisit = $visitorDetailsArray['idVisit'];
// The second join is a LEFT join to allow returning records that don't have a matching page title
// eg. Downloads, Outlinks. For these, idaction_name is set to 0
$sql = "\n\t\t\t\tSELECT\n\t\t\t\t\tlog_action.type as type,\n\t\t\t\t\tlog_action.name AS url,\n\t\t\t\t\tlog_action_title.name AS pageTitle,\n\t\t\t\t\tlog_action.idaction AS pageIdAction,\n\t\t\t\t\tlog_link_visit_action.idlink_va AS pageId,\n\t\t\t\t\tlog_link_visit_action.server_time as serverTimePretty\n\t\t\t\tFROM " . Piwik_Common::prefixTable('log_link_visit_action') . " AS log_link_visit_action\n\t\t\t\t\tINNER JOIN " . Piwik_Common::prefixTable('log_action') . " AS log_action\n\t\t\t\t\tON log_link_visit_action.idaction_url = log_action.idaction\n\t\t\t\t\tLEFT JOIN " . Piwik_Common::prefixTable('log_action') . " AS log_action_title\n\t\t\t\t\tON log_link_visit_action.idaction_name = log_action_title.idaction\n\t\t\t\tWHERE log_link_visit_action.idvisit = ?\n\t\t\t\t ";
$actionDetails = Piwik_FetchAll($sql, array($idvisit));
// If the visitor converted a goal, we shall select all Goals
$sql = "\n\t\t\t\tSELECT \n\t\t\t\t\t\t'goal' as type,\n\t\t\t\t\t\tgoal.name as goalName,\n\t\t\t\t\t\tgoal.revenue as revenue,\n\t\t\t\t\t\tlog_conversion.idlink_va as goalPageId,\n\t\t\t\t\t\tlog_conversion.server_time as serverTimePretty,\n\t\t\t\t\t\tlog_conversion.url as url\n\t\t\t\tFROM " . Piwik_Common::prefixTable('log_conversion') . " AS log_conversion\n\t\t\t\tLEFT JOIN " . Piwik_Common::prefixTable('goal') . " AS goal \n\t\t\t\t\tON (goal.idsite = log_conversion.idsite\n\t\t\t\t\t\tAND \n\t\t\t\t\t\tgoal.idgoal = log_conversion.idgoal)\n\t\t\t\t\tAND goal.deleted = 0\n\t\t\t\tWHERE log_conversion.idvisit = ?\n\t\t\t";
$goalDetails = Piwik_FetchAll($sql, array($idvisit));
$actions = array_merge($actionDetails, $goalDetails);
usort($actions, array($this, 'sortByServerTime'));
$visitorDetailsArray['actionDetails'] = $actions;
// Convert datetimes to the site timezone
foreach ($visitorDetailsArray['actionDetails'] as &$details) {
switch ($details['type']) {
case 'goal':
break;
case Piwik_Tracker_Action_Interface::TYPE_DOWNLOAD:
$details['type'] = 'download';
break;
case Piwik_Tracker_Action_Interface::TYPE_OUTLINK:
$details['type'] = 'outlink';
break;
default:
$details['type'] = 'action';
break;
}
$dateTimeVisit = Piwik_Date::factory($details['serverTimePretty'], $timezone);
$details['serverTimePretty'] = $dateTimeVisit->getLocalized('%shortDay% %day% %shortMonth% %time%');
}
$visitorDetailsArray['goalConversions'] = count($goalDetails);
$table->addRowFromArray(array(Piwik_DataTable_Row::COLUMNS => $visitorDetailsArray));
}
return $table;
}
static private function getCleanedVisitorsFromDetails($visitorDetails)
{
$table = new Piwik_DataTable();
foreach($visitorDetails as $visitorDetail)
{
self::cleanVisitorDetails($visitorDetail);
$visitor = new Piwik_Live_Visitor($visitorDetail);
$visitorDetailsArray = $visitor->getAllVisitorDetails();
$dateTimeVisit = Piwik_Date::factory($visitorDetailsArray['firstActionTimestamp']);
//TODO TO FIX
$visitorDetailsArray['serverDatePretty'] = $dateTimeVisit->getLocalized('%shortDay% %day% %shortMonth%');
$visitorDetailsArray['serverTimePretty'] = $dateTimeVisit->getLocalized('%time%');
// get Detail
$idvisit = $visitorDetailsArray['idVisit'];
$sql = "SELECT DISTINCT`" .Piwik::prefixTable('log_action')."`.`name` AS pageUrl
FROM `" .Piwik::prefixTable('log_visit')."`
INNER JOIN `" .Piwik::prefixTable('log_link_visit_action')."` ON `" .Piwik::prefixTable('log_visit')."`.`idvisit` = `" .Piwik::prefixTable('log_link_visit_action')."`.`idvisit`
INNER JOIN `" .Piwik::prefixTable('log_action')."` ON `" .Piwik::prefixTable('log_link_visit_action')."`.`idaction_url` = `" .Piwik::prefixTable('log_action')."`.`idaction`
WHERE `" .Piwik::prefixTable('log_visit')."`.`idvisit` = $idvisit;
";
$visitorDetailsArray['actionDetails'] = Piwik_FetchAll($sql);
$table->addRowFromArray( array(Piwik_DataTable_Row::COLUMNS => $visitorDetailsArray));
}
return $table;
}
