/**
* Add the fields needed for conversions stats
*
* @param array Row of stats
* @param array Empty row
* @param string Invocated method
* @param array Parameter array
*/
function mergeConversions(&$aRows, $emptyRow, $method, $aParams)
{
$conf = $GLOBALS['_MAX']['CONF'];
$aParams['include'] = isset($aParams['include']) ? array_flip($aParams['include']) : array();
$aParams['exclude'] = isset($aParams['exclude']) ? array_flip($aParams['exclude']) : array();
// Primary key
if ($method == 'getEntitiesStats') {
if (!isset($aParams['exclude']['ad_id']) && !isset($aParams['exclude']['zone_id'])) {
$aFields[] = "CONCAT(diac.ad_id, '_', diac.zone_id) AS pkey";
} elseif (!isset($aParams['exclude']['ad_id'])) {
$aFields[] = "diac.ad_id AS pkey";
} else {
$aFields[] = "diac.zone_id AS pkey";
}
} else {
$aParams['exclude']['ad_id'] = true;
$aParams['exclude']['zone_id'] = true;
if ($method == 'getDayHistory') {
$tzMethod = 'format';
$tzArgs = array('%Y-%m-%d');
} elseif ($method == 'getMonthHistory') {
$tzMethod = 'format';
$tzArgs = array('%Y-%m');
} elseif ($method == 'getDayOfWeekHistory') {
$tzMethod = 'getDayOfWeek';
$tzArgs = array();
} elseif ($method == 'getHourHistory') {
$tzMethod = 'getHour';
$tzArgs = array();
}
$aFields[] = "DATE_FORMAT(diac.tracker_date_time, '%Y-%m-%d %H:00:00') AS day_and_hour";
$aGroupBy = array('day_and_hour');
}
$aFrom = array("{$conf['table']['prefix']}{$conf['table']['data_intermediate_ad_connection']} diac");
$aWhere = array("diac.inside_window = 1");
$aFields[] = "SUM(IF(diac.connection_status = " . MAX_CONNECTION_STATUS_APPROVED . " AND diac.connection_action = " . MAX_CONNECTION_AD_IMPRESSION . ",1,0)) AS sum_conversions_" . MAX_CONNECTION_AD_IMPRESSION;
$aFields[] = "SUM(IF(diac.connection_status = " . MAX_CONNECTION_STATUS_APPROVED . " AND diac.connection_action = " . MAX_CONNECTION_AD_CLICK . ",1,0)) AS sum_conversions_" . MAX_CONNECTION_AD_CLICK;
$aFields[] = "SUM(IF(diac.connection_status = " . MAX_CONNECTION_STATUS_APPROVED . " AND diac.connection_action = " . MAX_CONNECTION_AD_ARRIVAL . ",1,0)) AS sum_conversions_" . MAX_CONNECTION_AD_ARRIVAL;
$aFields[] = "SUM(IF(diac.connection_status = " . MAX_CONNECTION_STATUS_APPROVED . " AND diac.connection_action = " . MAX_CONNECTION_MANUAL . ",1,0)) AS sum_conversions_" . MAX_CONNECTION_MANUAL;
$aFields[] = "SUM(IF(diac.connection_status = " . MAX_CONNECTION_STATUS_APPROVED . ",1,0)) AS sum_conversions";
$aFields[] = "SUM(IF(diac.connection_status = " . MAX_CONNECTION_STATUS_PENDING . ",1,0)) AS sum_conversions_pending";
if (!empty($aParams['day_begin']) && !empty($aParams['day_end'])) {
$oStartDate =& new Date("{$aParams['day_begin']} 00:00:00");
$oEndDate =& new Date("{$aParams['day_end']} 23:59:59");
$oStartDate->toUTC();
$oEndDate->toUTC();
$aWhere[] = "diac.tracker_date_time BETWEEN '" . $oStartDate->format('%Y-%m-%d %H:%M:%S') . "'" . " AND '" . $oEndDate->format('%Y-%m-%d %H:%M:%S') . "'";
}
if (!empty($aParams['agency_id'])) {
$aFrom['b'] = "JOIN {$conf['table']['prefix']}{$conf['table']['banners']} b ON (b.bannerid = diac.ad_id)";
$aFrom['m'] = "JOIN {$conf['table']['prefix']}{$conf['table']['campaigns']} m ON (m.campaignid = b.campaignid)";
$aFrom['c'] = "JOIN {$conf['table']['prefix']}{$conf['table']['clients']} c ON (c.clientid = m.clientid)";
$aFrom['z'] = "LEFT JOIN {$conf['table']['prefix']}{$conf['table']['zones']} z ON (z.zoneid = diac.zone_id)";
$aFrom['p'] = "LEFT JOIN {$conf['table']['prefix']}{$conf['table']['affiliates']} p ON (p.affiliateid = z.affiliateid AND p.agencyid = '{$aParams['agency_id']}')";
$aWhere[] = "c.agencyid = '{$aParams['agency_id']}'";
}
if (!empty($aParams['advertiser_id']) || isset($aParams['include']['advertiser_id'])) {
$aFrom['b'] = "JOIN {$conf['table']['prefix']}{$conf['table']['banners']} b ON (b.bannerid = diac.ad_id)";
$aFrom['m'] = "JOIN {$conf['table']['prefix']}{$conf['table']['campaigns']} m ON (m.campaignid = b.campaignid)";
if (!empty($aParams['advertiser_id'])) {
$aWhere[] = "m.clientid = '{$aParams['advertiser_id']}'";
}
if (isset($aParams['include']['advertiser_id']) && !isset($aParams['exclude']['advertiser_id'])) {
$aFields[] = "m.clientid AS advertiser_id";
$aGroupBy[] = "advertiser_id";
}
}
if (!empty($aParams['placement_id']) || isset($aParams['include']['placement_id'])) {
$aFrom['b'] = "JOIN {$conf['table']['prefix']}{$conf['table']['banners']} b ON (b.bannerid = diac.ad_id)";
if (!empty($aParams['placement_id'])) {
$aWhere[] = "b.campaignid = '{$aParams['placement_id']}'";
}
if (isset($aParams['include']['placement_id']) && !isset($aParams['exclude']['placement_id'])) {
$aFields[] = "b.campaignid AS placement_id";
$aGroupBy[] = "placement_id";
}
}
if (!empty($aParams['publisher_id']) || isset($aParams['include']['publisher_id'])) {
$aFrom['z'] = "JOIN {$conf['table']['prefix']}{$conf['table']['zones']} z ON (z.zoneid = diac.zone_id)";
if (!empty($aParams['publisher_id'])) {
$aWhere[] = "z.affiliateid = '{$aParams['publisher_id']}'";
}
if (isset($aParams['include']['publisher_id']) && !isset($aParams['exclude']['publisher_id'])) {
$aFields[] = "z.affiliateid AS publisher_id";
$aGroupBy[] = "publisher_id";
}
}
if (!empty($aParams['ad_id'])) {
$aWhere[] = "diac.ad_id = '{$aParams['ad_id']}'";
}
if (!isset($aParams['exclude']['ad_id'])) {
$aFields[] = "diac.ad_id AS ad_id";
$aGroupBy[] = "ad_id";
}
// Using isset: zone_id could be 0 in case of direct selection
if (isset($aParams['zone_id'])) {
$aWhere[] = "diac.zone_id = '{$aParams['zone_id']}'";
}
if (!isset($aParams['exclude']['zone_id'])) {
$aFields[] = "diac.zone_id AS zone_id";
$aGroupBy[] = "zone_id";
}
$sFields = count($aFields) ? join(', ', $aFields) : '';
$sFrom = count($aFrom) ? join(' ', $aFrom) : '';
$sWhere = count($aWhere) ? 'WHERE ' . join(' AND ', $aWhere) : '';
$sGroupBy = count($aGroupBy) ? 'GROUP BY ' . join(', ', $aGroupBy) : '';
$query = "SELECT " . $sFields . " FROM " . $sFrom . " " . $sWhere . " " . $sGroupBy;
$oDbh = OA_DB::singleton();
$key = $method == 'getEntitiesStats' ? 'pkey' : 'day_and_hour';
$oRes = $oDbh->query($query);
$aResult = array();
if (!PEAR::isError($oRes)) {
while ($row = $oRes->fetchRow()) {
$aResult[$row[$key]] = $row;
unset($aResult[$row[$key]][$key]);
}
}
if ($method != 'getEntitiesStats') {
$aResult = Admin_DA::_convertStatsArrayToTz($aResult, $aParams, null, $tzMethod, $tzArgs);
}
foreach ($aResult as $k => $row) {
if (!isset($aRows[$k])) {
$aRows[$k] = $emptyRow;
}
foreach ($row as $field => $value) {
if (!isset($aRows[$k][$field])) {
$aRows[$k][$field] = $value;
}
}
}
}
function _getHistoryTz($entity, $aParams, $name, $method, $args = array(), $formatted = null)
{
if (empty($aParams['tz'])) {
return Admin_DA::_getEntities($entity, $aParams, false, $name);
}
$aStats = Admin_DA::fromCache('getDayHourHistory', $aParams);
return Admin_DA::_convertStatsArrayToTz($aStats, $aParams, $name, $method, $args, $formatted);
}
