How to Show Country Status Into OpenX UI

Today in this article I will cover how to show country stats report on your UI . that’s means you will now see user by country. this is not default option in OpenX or Revive-adserver. basically it’s need Plugins & Patch than it’s work .Here i will show how to do this . so let’s start

First Download this stats_country_2.8.zip File
Now Open The Flowing File Openx/lib/max/Admin_DA.php OpenX Directory See#41 Number Line Add code here 'getCountries' => 'stats', After add this the file are looking blow

      'getAdvertisersStats'    => 'stats',
      'getConversions'         => 'stats',
      'getConnectionVariables' => 'stats',
      'getCountries'           => 'stats',
      'getPlacementsStats'     => 'stats',
      'getPublishersStats'     => 'stats',
      'getZonesStats'          => 'stats',

Now we need to add more patch in this same file Now find #638 Number Line See return $aStats}; After } Copy the code and past here Admin_DA.php

    /**
     * Returns an array of countries.
     *
     * @param array $aParams
     * @return array
     */
    function getCountries($aParams)
    {
        $conf = $GLOBALS['_MAX']['CONF'];
        $oDbh = &OA_DB::singleton();

$where = ”;
if (!empty($aParams[‘day’])) {
$aParams[‘day_begin’] = $aParams[‘day_end’] = $aParams[‘day’];
}
if (!empty($aParams[‘day_begin’])) {
$oStart = new Date($aParams[‘day_begin’]);
$oStart->setHour(0);
$oStart->setMinute(0);
$oStart->setSecond(0);
$oStart->toUTC();
$where .= ‘ AND cs.date_time >= ‘. $oDbh->quote($oStart->format(‘%Y-%m-%d %H:%M:%S’), ‘timestamp’);
}
if (!empty($aParams[‘day_end’])) {
$oEnd = new Date($aParams[‘day_end’]);
$oEnd->setHour(23);
$oEnd->setMinute(59);
$oEnd->setSecond(59);
$oEnd->toUTC();
$where .= ‘ AND cs.date_time <= ‘. $oDbh->quote($oEnd->format(‘%Y-%m-%d %H:%M:%S’), ‘timestamp’);
}
if (!empty($aParams[‘month’])) {
$oStart = new Date(“{$aParams[‘month’]}-01”);
$oStart->setHour(0);
$oStart->setMinute(0);
$oStart->setSecond(0);
$oEnd = new Date(Date_Calc::beginOfNextMonth($oStart->getDay(), $oStart->getMonth, $oStart->getYear(), ‘%Y-%m-%d’));
$oEnd->setHour(0);
$oEnd->setMinute(0);
$oEnd->setSecond(0);
$oEnd->subtractSeconds(1);
$oStart->toUTC();
$oEnd->toUTC();
$where .= ‘ AND cs.date_time >= ‘. $oDbh->quote($oStart->format(‘%Y-%m-%d %H:%M:%S’), ‘timestamp’);
$where .= ‘ AND cs.date_time <= ‘. $oDbh->quote($oEnd->format(‘%Y-%m-%d %H:%M:%S’), ‘timestamp’);
}
if (!empty($aParams[‘day_hour’])) {
$oStart = new Date(“{$aParams[‘day_hour’]}:00:00″);
$oStart->setMinute(0);
$oStart->setSecond(0);
$oEnd = new Date($oStart);
$oStart->setMinute(59);
$oStart->setSecond(59);
$where .= ‘ AND cs.date_time >= ‘. $oDbh->quote($oStart->format(‘%Y-%m-%d %H:%M:%S’), ‘timestamp’);
$where .= ‘ AND cs.date_time <= ‘. $oDbh->quote($oEnd->format(‘%Y-%m-%d %H:%M:%S’), ‘timestamp’);
}
if (!empty($aParams[‘agency_id’])) {
$where .= ‘ AND c.agencyid=’. $oDbh->quote($aParams[‘agency_id’], ‘integer’);
}
if (!empty($aParams[‘clientid’])) {
$where .= ‘ AND c.clientid=’. $oDbh->quote($aParams[‘clientid’], ‘integer’);
}
if (isset($aParams[‘zonesIds’])) {
$where .= ‘ AND cs.zone_id IN (‘. $oDbh->escape(implode(‘,’, $aParams[‘zonesIds’])) .”)”;
}
if (!empty($aParams[‘campaignid’])) {
$where .= ‘ AND m.campaignid=’. $oDbh->quote($aParams[‘campaignid’], ‘integer’);
}
if (!empty($aParams[‘bannerid’])) {
$where .= ‘ AND d.bannerid=’. $oDbh->quote($aParams[‘bannerid’], ‘integer’);
}

$order = ‘ cs.country’;
if (!empty($aParams[‘listorder’])) {
switch ($aParams[‘listorder’]){
case ‘country’:
$order = ‘ cs.country’;
break;
case ‘name’:
$order = ‘ cs.country’;
break;
case ‘impressions’:
$order = ‘ SUM(cs.impressions)’;
break;
case ‘clicks’:
$order = ‘ SUM(cs.clicks)’;
break;
default:
$order = ‘ cs.country’;
}
}
$orderdirection = ‘ASC’;
if (!empty($aParams[‘orderdirection’])) {
if ($aParams[‘orderdirection’] == ‘down’){
$orderdirection = ‘DESC’;
}
}

$query =
“SELECT
cs.country as country,
SUM(cs.impressions) as impressions,
SUM(cs.clicks) as clicks
FROM
{$conf[‘table’][‘prefix’]}{$conf[‘table’][‘clients’]} AS c,
{$conf[‘table’][‘prefix’]}stats_country AS cs,
{$conf[‘table’][‘prefix’]}{$conf[‘table’][‘banners’]} AS d,
{$conf[‘table’][‘prefix’]}{$conf[‘table’][‘campaigns’]} AS m
WHERE
c.clientid=m.clientid
AND m.campaignid=d.campaignid
AND d.bannerid=cs.creative_id
“. $where .”
GROUP BY
cs.country
ORDER BY
“. $order . ” ” . $orderdirection
;

return $oDbh->queryAll($query, null, MDB2_FETCHMODE_DEFAULT, true);
}

Now we have need to declare Parameter country status . to go to country report page
Open lib/OA/Admin/Statistics/Fields/Delivery/Default.php here see #74 number line $this->_aFields['sum_views'] = Add the flowing Line Call 'link'

 'link'    => 'stats.php?entity=country&',

Now it’s look like blow code

array(
                'name'   => $GLOBALS['strImpressions'],
                'short'  => $GLOBALS['strImpressions_short'],
                'pref'   => 'ui_column_impressions',
                'link'    => 'stats.php?entity=country&',
                'rank'   => 1,
                'active' => true,
                'format' => 'default'
            );

Now we need to place the file stats-country.php in this location /www/admin/. after uploding stats-country.php .Open stats.php file Flowing this location www/admin/stats.php
See #159 number line here .here past the flowing Blow code

// If displaying conversion statistics, hand over control to a different file
if ($entity == 'country') {
    include_once MAX_PATH . '/www/admin/stats-country.php';
    exit;
}

After the patch code the flowing #159 to #170 number line code looking like this

// If displaying conversion statistics, hand over control to a different file
if ($entity == 'conversions') {
    include_once MAX_PATH . '/www/admin/stats-conversions.php';
    exit;
}

// If displaying conversion statistics, hand over control to a different file
if ($entity == ‘country’) {
include_once MAX_PATH . ‘/www/admin/stats-country.php’;
exit;
}

Now time to install the OpenX plugin “openXDeliveryLogCountry” first.if you already have installed it then skip this step.
After Install “openXDeliveryLogCountry”  have a look at openx /var folder, yourdomain.com.conf.php within there is a section [deliveryHooks], for impression-logging there should be a row “logImpression” containing “deliveryLog:oxLogCountry:logImpressionCountry”, like:

[deliveryHooks]
logClick="deliveryDataPrepare:oxDeliveryDataPrepare:dataCommon|deliveryDataPrepare:oxDeliveryGeo:dataGeo|deliveryLog:oxLogClick:logClick|deliveryLog:oxLogCountry:logClickCountry"
logConversion="deliveryLog:oxLogConversion:logConversion"
logConversionVariable="deliveryLog:oxLogConversion:logConversionVariable"
logImpression="deliveryDataPrepare:oxDeliveryDataPrepare:dataCommon|deliveryDataPrepare:oxDeliveryGeo:dataGeo|deliveryLog:oxLogCountry:logImpressionCountry|deliveryLog:oxLogImpression:logImpression"
logRequest="deliveryDataPrepare:oxDeliveryDataPrepare:dataCommon|deliveryLog:oxLogRequest:logRequest"

If done all of Previews Steep than wait few hour after few hour you need to take a look into the database, table “ox_data_bkt_country_m” (Impressions) or “ox_data_bkt_country_c” (Clicks). Are there any rows logged after a banner has been displayed or clicked? If row are logged than country stats are show in your UI . See blow image

Done ! Now go status page and click the impression row see the country report.
Note : if you show blank page on stats.php?entity=country& in this page than please check [deliveryHooks] & Plugins

If you face any problem please drop a comment here i will try my best to assist you.