function testMigrateData()
{
$prefix = $this->getPrefix();
$this->initDatabase(126, array('zones', 'ad_zone_assoc', 'placement_zone_assoc', 'banners'));
$aAValues = array(array('zoneid' => 1, 'zonetype' => 0, 'what' => ''), array('zoneid' => 2, 'zonetype' => 0, 'what' => 'bannerid:3'), array('zoneid' => 3, 'zonetype' => 3, 'what' => 'clientid:3'), array('zoneid' => 4, 'zonetype' => 3, 'what' => 'clientid:5', 'delivery' => phpAds_ZoneText), array('zoneid' => 5, 'zonetype' => 3, 'what' => 'clientid:5', 'delivery' => phpAds_ZoneBanner, 'width' => 468, 'height' => 60), array('zoneid' => 6, 'zonetype' => 0, 'what' => 'bannerid:2,bannerid:3'), array('zoneid' => 7, 'zonetype' => 3, 'what' => 'clientid:3,clientid:4'), array('zoneid' => 8, 'zonetype' => 0, 'what' => 'bannerid:2,bannerid:3,bannerid:4,bannerid:5'), array('zoneid' => 9, 'zonetype' => 3, 'what' => 'clientid:,clientid:3'));
foreach ($aAValues as $aValues) {
// Set empty defaults for NOT NULL fields
$aValues['chain'] = $aValues['prepend'] = $aValues['append'] = '';
$sql = OA_DB_Sql::sqlForInsert('zones', $aValues);
$this->oDbh->exec($sql);
}
$aABannerValues = array(array('bannerid' => 1, 'campaignid' => 3), array('bannerid' => 2, 'campaignid' => 3), array('bannerid' => 3, 'campaignid' => 4), array('bannerid' => 4, 'campaignid' => 4), array('bannerid' => 5, 'campaignid' => 5, 'storagetype' => 'txt'), array('bannerid' => 6, 'campaignid' => 5, 'storagetype' => 'sql', 'width' => 468, 'height' => 60), array('bannerid' => 7, 'campaignid' => 5, 'storagetype' => 'sql', 'width' => 125, 'height' => 125));
foreach ($aABannerValues as $aBannerValues) {
// Set empty defaults for NOT NULL fields
$aBannerValues['htmltemplate'] = $aBannerValues['htmlcache'] = $aBannerValues['bannertext'] = $aBannerValues['compiledlimitation'] = $aBannerValues['append'] = '';
$sql = OA_DB_Sql::sqlForInsert('banners', $aBannerValues);
$this->oDbh->exec($sql);
}
$this->upgradeToVersion(127);
$aAssocTables = array("{$prefix}ad_zone_assoc WHERE link_type = 1" => 17, "{$prefix}ad_zone_assoc WHERE link_type = 0" => 7, "{$prefix}placement_zone_assoc" => 6);
foreach ($aAssocTables as $assocTable => $cAssocs) {
$rsCAssocs = DBC::NewRecordSet("SELECT count(*) AS cassocs FROM {$assocTable}");
$this->assertTrue($rsCAssocs->find());
$this->assertTrue($rsCAssocs->fetch());
$this->assertEqual($cAssocs, $rsCAssocs->get('cassocs'), "%s: The table involved: {$assocTable}");
}
}
function testMigrateData()
{
$prefix = $this->getPrefix();
$this->initDatabase(121, array('clients', 'campaigns'));
$aCampaigns = array(array('clientid' => 3, 'parent' => 1, 'views' => '100', target => '1000'), array('clientid' => 4, 'parent' => 1, 'views' => '200', target => '1'), array('clientid' => 5, 'parent' => 1, 'views' => '200', target => '0'));
$cCampaigns = count($aCampaigns);
$aAValues = array(array('clientid' => 1, 'parent' => 0, 'views' => '0', target => '0'), array('clientid' => 2, 'parent' => 0, 'views' => '0', target => '0'));
$aAValues = array_merge($aAValues, $aCampaigns);
foreach ($aAValues as $aValues) {
$sql = OA_DB_Sql::sqlForInsert('clients', $aValues);
$this->oDbh->exec($sql);
}
$this->upgradeToVersion(122);
$tableCampaigns = $this->oDbh->quoteIdentifier($prefix . 'campaigns', true);
$rsCampaigns = DBC::NewRecordSet("SELECT * from {$tableCampaigns}");
$this->assertTrue($rsCampaigns->find());
$this->assertEqual($cCampaigns, $rsCampaigns->getRowCount());
for ($idxCampaign = 0; $idxCampaign < $cCampaigns; $idxCampaign++) {
$this->assertTrue($rsCampaigns->fetch());
$this->assertEqual($aCampaigns[$idxCampaign]['clientid'], $rsCampaigns->get('campaignid'));
$this->assertEqual($aCampaigns[$idxCampaign]['parent'], $rsCampaigns->get('clientid'));
$this->assertEqual($aCampaigns[$idxCampaign]['views'], $rsCampaigns->get('views'));
$priority = $aCampaigns[$idxCampaign]['target'] > 0 ? 5 : 0;
$this->assertEqual($priority, $rsCampaigns->get('priority'));
}
$tableClients = $this->oDbh->quoteIdentifier($prefix . 'clients', true);
$rsClients = DBC::NewRecordSet("SELECT count(*) AS nclients FROM {$tableClients}");
$this->assertTrue($rsClients->find());
$this->assertTrue($rsClients->fetch());
$this->assertEqual(count($aAValues) - $cCampaigns, $rsClients->get('nclients'));
if ($this->oDbh->dbsyntax == 'pgsql') {
$value = $this->oDbh->queryOne("SELECT NEXTVAL('{$prefix}campaigns_campaignid_seq')");
$this->assertTrue($value > 5, "The current sequence value is {$value}.");
}
}
function getChannelsAndAffiliates()
{
$prefix = $this->getTablePrefix();
$oDbh = OA_DB::singleton();
$tableCh = $oDbh->quoteIdentifier($prefix . 'channel', true);
$tableAf = $oDbh->quoteIdentifier($prefix . 'affiliates', true);
$query = "\n SELECT\n ch.channelid,\n ch.affiliateid,\n ch.name,\n af.name AS affiliatename\n FROM\n {$tableCh} AS ch,\n {$tableAf} AS af\n WHERE\n af.affiliateid=ch.affiliateid\n ORDER BY ch.channelid\n ";
return DBC::NewRecordSet($query);
}
/**
* Returns the record set for either 'acls' or 'acls_channels' table,
* all records and rows.
*
* @param string $table Either 'acls' or 'acls_channels'
* @return RecordSet
*/
function &getRsAcls($table, $orderBy = false)
{
$table = $this->oDbh->quoteIdentifier($this->getTablePrefix() . $table);
$query = "\n SELECT\n *\n FROM\n {$table}";
if ($orderBy) {
$query .= " ORDER BY " . $this->oDbh->quoteIdentifier($orderBy);
}
return DBC::NewRecordSet($query);
}
/**
* Selects specified columns from the $table and returns
* initialized (after find()) RecordSet or PEAR::Error
* if initialization didn't work correctly.
*
* @param string $table
* @param string $idColumn
* @param string $id
* @param array $aColumns List of columns, defaults to '*'.
* @return DataSpace
*/
function &selectWhereOne($table, $idColumn, $id, $aColumns = array('*'))
{
$sColumns = implode(' ', $aColumns);
$table = OA_DB_Sql::modifyTableName($table);
$sql = "SELECT {$sColumns} FROM {$table} WHERE {$idColumn} = {$id}";
$rs =& DBC::NewRecordSet($sql);
$result = $rs->find();
if (PEAR::isError($result)) {
return $result;
}
return $rs;
}
/**
* A method to retrieve all advertisers where the advertiser name
* contains a given string. Also returns any advertiser where the
* advertiser ID equals the given keyword, should the keyword be
* numeric.
*
* @param $keyword string Keyword to look for
* @param $agencyId integer Limit results to advertisers owned by a given Agency ID
* @return RecordSet
*/
function getClientByKeyword($keyword, $agencyId = null)
{
$conf = $GLOBALS['_MAX']['CONF'];
$whereClient = is_numeric($keyword) ? " OR c.clientid = {$keyword}" : '';
$oDbh = OA_DB::singleton();
$tableC = $oDbh->quoteIdentifier($this->getTablePrefix() . 'clients', true);
$query = "\n SELECT\n c.clientid AS clientid,\n c.clientname AS clientname\n FROM\n {$tableC} AS c\n WHERE\n (\n c.clientname LIKE " . DBC::makeLiteral('%' . $keyword . '%') . $whereClient . "\n )";
if ($agencyId !== null) {
$query .= " AND c.agencyid=" . DBC::makeLiteral($agencyId);
}
return DBC::NewRecordSet($query);
}
function testMigrateCampaignIds()
{
$this->initDatabase(123, array('banners'));
$sql = OA_DB_Sql::sqlForInsert('banners', array('bannerid' => '1', 'clientid' => '4', 'htmltemplate' => '', 'htmlcache' => '', 'bannertext' => '', 'compiledlimitation' => '', 'append' => ''));
$this->oDbh->exec($sql);
$this->upgradeToVersion(124);
$table = $this->oDbh->quoteIdentifier($this->getPrefix() . 'banners', true);
$rsBanners = DBC::NewRecordSet("SELECT campaignid FROM {$table}");
$this->assertTrue($rsBanners->find());
$this->assertTrue($rsBanners->fetch());
$this->assertEqual(4, $rsBanners->get('campaignid'));
}
function getPublishersByTracker($trackerid)
{
$prefix = $this->getTablePrefix();
$oDbh = OA_DB::singleton();
$tableAza = $oDbh->quoteIdentifier($prefix . 'ad_zone_assoc', true);
$tableZ = $oDbh->quoteIdentifier($prefix . 'zones', true);
$tableP = $oDbh->quoteIdentifier($prefix . 'affiliates', true);
$tableB = $oDbh->quoteIdentifier($prefix . 'banners', true);
$tableCt = $oDbh->quoteIdentifier($prefix . 'campaigns_trackers', true);
$query = "\n SELECT\n p.affiliateid AS affiliateid,\n p.name AS name\n FROM\n {$tableAza} aza\n JOIN {$tableZ} z ON (aza.zone_id = z.zoneid)\n JOIN {$tableP} p USING (affiliateid)\n JOIN {$tableB} b ON (aza.ad_id = b.bannerid)\n JOIN {$tableCt} ct USING (campaignid)\n WHERE\n ct.trackerid = " . DBC::makeLiteral($trackerid) . "\n GROUP BY\n p.affiliateid,\n name\n ORDER BY\n name\n ";
return DBC::NewRecordSet($query);
}
/**
* A method to determine the number of impressions, clicks and conversions
* delivered by a given ecpm campaign to date.
*
* Can also determine the delivery information up to a given operation
* interval end date.
*
* @param integer $agencyId The agency ID.
* @param PEAR::Date $oDate Limits delivery information to that which is
* after this date.
* @param integer $priority Campaign priority (by default eCPM priority).
* @return array
*/
function getDeliveredEcpmCampainImpressionsByAgency($agencyId, $oDate, $priority = null)
{
$prefix = $this->getTablePrefix();
$oDbh = OA_DB::singleton();
if (is_null($priority)) {
$priority = DataObjects_Campaigns::PRIORITY_ECPM;
}
$query = "\n SELECT\n c.campaignid AS campaignid,\n SUM(dia.impressions) AS impressions_delivered\n FROM\n {$oDbh->quoteIdentifier($prefix . 'clients', true)} AS cl,\n {$oDbh->quoteIdentifier($prefix . 'campaigns', true)} AS c,\n {$oDbh->quoteIdentifier($prefix . 'banners', true)} AS b,\n {$oDbh->quoteIdentifier($prefix . 'data_intermediate_ad', true)} AS dia\n WHERE\n cl.agencyid = " . DBC::makeLiteral($agencyId) . "\n AND c.status = " . OA_ENTITY_STATUS_RUNNING . "\n AND c.priority = " . $priority . "\n AND cl.clientid = c.clientid\n AND b.bannerid = dia.ad_id\n AND b.campaignid = c.campaignid\n AND dia.interval_end >= '" . $oDate->format('%Y-%m-%d %H:%M:%S') . "'\n GROUP BY\n c.campaignid";
$rs = DBC::NewRecordSet($query);
if (PEAR::isError($rs)) {
return false;
}
return $rs->getAll(array(), 'campaignid');
}
function _checkDataTable($table, $cEntries, $mapCImpressions, $mapCClicks)
{
$table = $this->oDbh->quoteIdentifier($this->getPrefix() . $table, true);
$rsDsah = DBC::NewRecordSet("SELECT * FROM {$table}");
$this->assertTrue($rsDsah->find());
$this->assertEqual($cEntries, $rsDsah->getRowCount());
while ($rsDsah->fetch()) {
$bannerId = $rsDsah->get('ad_id');
$zoneId = $rsDsah->get('zone_id');
$day = $rsDsah->get('day');
$hour = $rsDsah->get('hour');
$this->assertEqual($mapCImpressions[$bannerId][$zoneId][$day][$hour], $rsDsah->get('impressions'));
$this->assertEqual($mapCClicks[$bannerId][$zoneId][$day][$hour], $rsDsah->get('clicks'));
}
}
/**
* A method to retrieve all advertisers where the advertiser name
* contains a given string. Also returns any advertiser where the
* advertiser ID equals the given keyword, should the keyword be
* numeric.
*
* @param $keyword string Keyword to look for
* @param $agencyId integer Limit results to advertisers owned by a given Agency ID
* @return RecordSet
*/
function getClientByKeyword($keyword, $agencyId = null, $aIncludeSystemTypes = array())
{
// always add default type
$aIncludeSystemTypes = array_merge(array(DataObjects_Clients::ADVERTISER_TYPE_DEFAULT), $aIncludeSystemTypes);
foreach ($aIncludeSystemTypes as $k => $v) {
$aIncludeSystemTypes[$k] = DBC::makeLiteral((int) $v);
}
$conf = $GLOBALS['_MAX']['CONF'];
$whereClient = is_numeric($keyword) ? " OR c.clientid = {$keyword}" : '';
$oDbh = OA_DB::singleton();
$tableC = $oDbh->quoteIdentifier($this->getTablePrefix() . 'clients', true);
$query = "\n SELECT\n c.clientid AS clientid,\n c.clientname AS clientname\n FROM\n {$tableC} AS c\n WHERE\n (\n c.clientname LIKE " . DBC::makeLiteral('%' . $keyword . '%') . $whereClient . "\n )\n AND c.type IN (" . implode(',', $aIncludeSystemTypes) . ")";
if ($agencyId !== null) {
$query .= " AND c.agencyid=" . DBC::makeLiteral($agencyId);
}
return DBC::NewRecordSet($query);
}
function testMigrateData()
{
$this->initDatabase(129, array('config', 'preference'));
$this->setupPanConfig();
$migration = new Migration_129();
$migration->init($this->oDbh, MAX_PATH . '/var/DB_Upgrade.test.log');
$aValues = array('warn_limit_days' => 1);
$migration->migrateData();
$table = $this->oDbh->quoteIdentifier($this->getPrefix() . 'preference');
$rsPreference = DBC::NewRecordSet("SELECT * from {$table}");
$rsPreference->find();
$this->assertTrue($rsPreference->fetch());
$aDataPreference = $rsPreference->toArray();
foreach ($aValues as $column => $value) {
$this->assertEqual($value, $aDataPreference[$column]);
}
$this->restorePanConfig();
}
function getTrackerVariables($zoneid, $affiliateid, $selectForAffiliate)
{
$prefix = $this->getTablePrefix();
$oDbh = OA_DB::singleton();
$tableZ = $oDbh->quoteIdentifier($prefix . 'zones', true);
$tableAza = $oDbh->quoteIdentifier($prefix . 'ad_zone_assoc', true);
$tableB = $oDbh->quoteIdentifier($prefix . 'banners', true);
$tableCt = $oDbh->quoteIdentifier($prefix . 'campaigns_trackers', true);
$tableT = $oDbh->quoteIdentifier($prefix . 'trackers', true);
$tableV = $oDbh->quoteIdentifier($prefix . 'variables', true);
$tableVp = $oDbh->quoteIdentifier($prefix . 'variable_publisher', true);
$whereZoneAffiliate = empty($zoneid) ? "z.affiliateid = " . DBC::makeLiteral($affiliateid) : "z.zoneid = " . DBC::makeLiteral($zoneid);
$query = "\n SELECT DISTINCT\n v.variableid AS variable_id,\n v.name AS variable_name,\n v.description AS variable_description,\n t.trackerid AS tracker_id,\n t.trackername AS tracker_name,\n t.description AS tracker_description\n FROM\n {$tableAza} aza JOIN\n {$tableZ} z ON (aza.zone_id = z.zoneid) JOIN\n {$tableB} b ON (aza.ad_id = b.bannerid) JOIN\n {$tableCt} ct USING (campaignid) JOIN\n {$tableT} t USING (trackerid) JOIN\n {$tableV} v USING (trackerid) LEFT JOIN\n {$tableVp} vp ON (vp.variable_id = v.variableid AND vp.publisher_id = z.affiliateid)\n WHERE\n {$whereZoneAffiliate} AND\n v.datatype = 'numeric'\n ";
if ($selectForAffiliate) {
$query .= " AND (v.hidden = 'f' OR vp.visible = 1)";
}
return DBC::NewRecordSet($query);
}
function migrateAcls()
{
$tableAcls = $this->getPrefix() . "acls";
$sql = "SELECT * FROM {$tableAcls}";
$rsAcls = DBC::NewRecordSet($sql);
if (!$rsAcls->find()) {
return false;
}
$aUpdates = array();
while ($rsAcls->fetch()) {
$bannerid = $rsAcls->get('bannerid');
$executionorder = $rsAcls->get('executionorder');
$oldType = $rsAcls->get('type');
if (!isset($this->aAclsTypes[$oldType])) {
$this->_logError("Unknown acls type: {$oldType}");
return false;
}
$type = $this->aAclsTypes[$oldType];
$oldComparison = $rsAcls->get('comparison');
$oldData = $rsAcls->get('data');
$oPlugin =& $this->_getDeliveryLimitationPlugin($type);
if (!$oPlugin) {
$this->_logError("Can't find code for delivery limitation plugin: {$type}.");
return false;
}
$aNewAclsData = $oPlugin->getUpgradeFromEarly($oldComparison, $oldData);
$comparison = $aNewAclsData['op'];
$data = $aNewAclsData['data'];
$aUpdates[] = "UPDATE {$tableAcls} SET type = '{$type}', comparison = '{$comparison}', data = '{$data}'\n\t WHERE bannerid = {$bannerid}\n\t AND executionorder = {$executionorder}";
}
foreach ($aUpdates as $update) {
$result = $this->oDBH->exec($update);
if (PEAR::isError($result)) {
$this->_logError("Couldn't execute update: {$update}");
return false;
}
}
return true;
}
function testMigrateData()
{
$prefix = $this->getPrefix();
$this->initDatabase(108, array('config', 'preference'));
$this->setupPanConfig();
$migration = new Migration_108();
$migration->init($this->oDbh, MAX_PATH . '/var/DB_Upgrade.test.log');
$aValues = array('gui_show_parents' => 't', 'updates_enabled' => 'f', 'client_welcome_msg' => '', 'updates_cache' => '');
$sql = OA_DB_Sql::sqlForInsert('config', $aValues);
$this->oDbh->exec($sql);
$aValues += array('warn_admin' => 't', 'warn_limit' => '100');
$migration->migrateData();
$table = $this->oDbh->quoteIdentifier($prefix . 'preference', true);
$rsPreference = DBC::NewRecordSet("SELECT * from {$table}");
$rsPreference->find();
$this->assertTrue($rsPreference->fetch());
$aDataPreference = $rsPreference->toArray();
foreach ($aValues as $column => $value) {
$this->assertEqual($value, $aDataPreference[$column]);
}
$this->restorePanConfig();
}
/**
* This method returns statistics for a given banner, broken down by zone.
*
* @access public
*
* @param integer $bannerId The ID of the banner to view statistics
* @param date $oStartDate The date from which to get statistics (inclusive)
* @param date $oEndDate The date to which to get statistics (inclusive)
* @param bool $localTZ Should stats be using the manager TZ or UTC?
*
* @return RecordSet
* <ul>
* <li><b>publisherID integer</b> The ID of the publisher
* <li><b>publisherName string (255)</b> The name of the publisher
* <li><b>zoneID integer</b> The ID of the zone
* <li><b>zoneName string (255)</b> The name of the zone
* <li><b>requests integer</b> The number of requests for the day
* <li><b>impressions integer</b> The number of impressions for the day
* <li><b>clicks integer</b> The number of clicks for the day
* <li><b>revenue decimal</b> The revenue earned for the day
* </ul>
*
*/
function getBannerZoneStatistics($bannerId, $oStartDate, $oEndDate, $localTZ = false)
{
$bannerId = $this->oDbh->quote($bannerId, 'integer');
$tableBanners = $this->quoteTableName('banners');
$tableZones = $this->quoteTableName('zones');
$tableAffiliates = $this->quoteTableName('affiliates');
$tableSummary = $this->quoteTableName('data_summary_ad_hourly');
$query = "\n SELECT\n SUM(s.impressions) AS impressions,\n SUM(s.clicks) AS clicks,\n SUM(s.requests) AS requests,\n SUM(s.total_revenue) AS revenue,\n SUM(s.conversions) AS conversions,\n p.affiliateid AS publisherID,\n p.name AS publisherName,\n z.zoneid AS zoneID,\n z.zonename AS zoneName\n FROM\n {$tableBanners} AS b,\n\n {$tableZones} AS z,\n {$tableAffiliates} AS p,\n\n {$tableSummary} AS s\n WHERE\n b.bannerid = {$bannerId}\n AND\n b.bannerid = s.ad_id\n\n AND\n p.affiliateid = z.affiliateid\n AND\n z.zoneid = s.zone_id\n " . $this->getWhereDate($oStartDate, $oEndDate, $localTZ) . "\n GROUP BY\n p.affiliateid, p.name,\n z.zoneid, z.zonename\n ";
return DBC::NewRecordSet($query);
}
/**
* @param int $agency_id
* @return array An array of arrays, representing a list of campaigns.
*
* @todo Consider removing order options (or making them optional)
*/
function getAllCampaignsUnderAgency($agency_id, $listorder, $orderdirection, $aIncludeSystemTypes = array())
{
$aIncludeSystemTypes = $this->_prepareIncludeSystemTypes($aIncludeSystemTypes);
$prefix = $this->getTablePrefix();
$oDbh = OA_DB::singleton();
$tableM = $oDbh->quoteIdentifier($prefix . 'campaigns', true);
$tableC = $oDbh->quoteIdentifier($prefix . 'clients', true);
$query = "\n SELECT\n m.campaignid as campaignid,\n m.clientid as clientid,\n m.campaignname as campaignname,\n m.status as status,\n m.priority AS priority,\n revenue AS revenue\n FROM\n {$tableM} AS m,\n {$tableC} AS c\n WHERE\n c.clientid=m.clientid\n AND c.agencyid=" . DBC::makeLiteral($agency_id) . "\n AND m.type IN (" . implode(',', $aIncludeSystemTypes) . ") " . $this->getSqlListOrder('type+' . $listorder, $orderdirection);
$rsCampaigns = DBC::NewRecordSet($query);
$aCampaigns = $rsCampaigns->getAll(array('campaignid', 'clientid', 'campaignname', 'status', 'priority', 'revenue'));
$aCampaigns = $this->_rekeyCampaignsArray($aCampaigns);
$aRetCampaigns = array();
foreach ($aCampaigns as $campaignId => $aCampaign) {
$aRetCampaigns[$campaignId]['status_changed'] = $aCampaign['status_changed'];
$aRetCampaigns[$campaignId]['status'] = $aCampaign['status'];
$aRetCampaigns[$campaignId]['clientid'] = $aCampaign['clientid'];
$aRetCampaigns[$campaignId]['campaignname'] = $aCampaign['campaignname'];
$aRetCampaigns[$campaignId]['priority'] = $aCampaign['priority'];
}
return $aRetCampaigns;
}
/**
* Join all banners, campaigns and clients and return it as RecordSet
* @param bool $filterMarketBanners filter banners created by market plugin
*
* @return RecordSet
*/
function getBannersCampaignsClients($filterMarketBanners = true)
{
$prefix = $this->getTablePrefix();
$oDbh = OA_DB::singleton();
$tableB = $oDbh->quoteIdentifier($prefix . 'banners', true);
$tableC = $oDbh->quoteIdentifier($prefix . 'campaigns', true);
$tableCl = $oDbh->quoteIdentifier($prefix . 'clients', true);
$query = "\n SELECT\n b.bannerid,\n b.campaignid,\n b.description,\n c.clientid,\n c.campaignname,\n cl.clientname\n FROM\n {$tableB} AS b,\n {$tableC} AS c,\n {$tableCl} AS cl\n WHERE\n c.campaignid=b.campaignid\n AND cl.clientid=c.clientid\n ";
if ($filterMarketBanners) {
$query .= " AND (b.ext_bannertype <> " . DBC::makeLiteral(DataObjects_Banners::BANNER_TYPE_MARKET) . " OR b.ext_bannertype IS NULL)";
}
return DBC::NewRecordSet($query);
}
function migrateData()
{
$prefix = $this->getPrefix();
$query = "SELECT * FROM {$prefix}zones";
$rsZones = DBC::NewRecordSet($query);
$result = $rsZones->find();
if (PEAR::isError($result)) {
return $this->_logErrorAndReturnFalse('Error migrating Zones data during migration 327: ' . $result->getUserInfo());
}
$aZoneAdObjectHandlers = array();
while ($result = $rsZones->fetch()) {
if (PEAR::isError($result)) {
return $this->_logErrorAndReturnFalse('Error migrating Zones data during migration 327: ' . $result->getUserInfo());
}
$zonetype = $rsZones->get('zonetype');
$what = $rsZones->get('what');
$zoneid = $rsZones->get('zoneid');
$zoneAdObjectHandler = OA_upgrade_getZoneAdObjectHandler($prefix, $zonetype, $zoneid, $what);
if (!$zoneAdObjectHandler) {
// Either zonetype 2 - keywords, which shouldn't be modified
// or unknown / unused zone type.
continue;
}
$aZoneAdObjectHandlers[] = $zoneAdObjectHandler;
}
foreach ($aZoneAdObjectHandlers as $zoneAdObjectHandler) {
$result = $zoneAdObjectHandler->insertAssocs($this->oDBH);
if (PEAR::isError($result)) {
return $this->_logErrorAndReturnFalse('Error migrating Zones data during migration 327: ' . $result->getUserInfo());
}
if (is_array($result)) {
foreach ($result as $k => $v) {
$this->_log($v);
}
}
}
$tableAdZoneAssoc = "{$prefix}ad_zone_assoc";
$tablePlacementZoneAssoc = "{$prefix}placement_zone_assoc";
$tableBanners = "{$prefix}banners";
$tableZones = "{$prefix}zones";
$sql = "\n\t INSERT INTO {$tableAdZoneAssoc} (zone_id, ad_id)\n\t SELECT zoneid, bannerid\n\t FROM {$tableBanners} b, {$tableZones} z, {$tablePlacementZoneAssoc}\n \t WHERE campaignid = placement_id\n \t AND zoneid = zone_id\n \t AND ((delivery = 3 AND storagetype = 'txt')\n \t OR (delivery <> 3 AND storagetype <> 'txt'\n \t AND (z.height < 0 OR z.height = b.height)\n \t AND (z.width < 0 OR z.width = b.width)))";
$result = $this->oDBH->exec($sql);
if (PEAR::isError($result)) {
return $this->_logErrorAndReturnFalse('Error inserting AdZoneAssoc data during migration 327: ' . $result->getUserInfo());
}
$sql = "INSERT INTO {$tableAdZoneAssoc} (zone_id, ad_id, link_type)\n\t SELECT 0 zone_id, bannerid ad_id, 0 link_type FROM {$tableBanners}";
$result = $this->oDBH->exec($sql);
if (PEAR::isError($result)) {
return $this->_logErrorAndReturnFalse('Error inserting AdZoneAssoc data during migration 327: ' . $result->getUserInfo());
}
return true;
}
/**
* Batch linking list of zones to campaign's banners or a specific banner
* This is a sub-function of linkZonesToCampaigns and linkZonesToBanner.
*
* Banners are linked when:
* - text text banner and text zone (ignore width/height)
* - link non text banners when matching width/height to non text zone
* Don't link banners to zone if that link already exists
* Don't link Email zones
*
* @param array $aZonesIds array of zones IDs
* @param int $campaignId the campaign ID.
* @param int $bannerId the banner ID.
* @return int number of linked banners
*/
function _linkZonesToCampaignsBannersOrSingleBanner($aZonesIds, $campaignId, $bannerId = null)
{
$prefix = $this->getTablePrefix();
$rsEmailZones = DBC::NewRecordSet("SELECT zoneid FROM {$prefix}zones WHERE delivery = " . MAX_ZoneEmail . " AND zoneid IN (" . implode(',', array_map('intval', $aZonesIds)) . ")");
$aEmailZoneIds = $rsEmailZones->getAll();
$fastLinking = !$GLOBALS['_MAX']['CONF']['audit']['enabledForZoneLinking'];
$fromWhereClause = " FROM\n {$prefix}banners AS b\n CROSS JOIN\n {$prefix}zones AS z\n LEFT JOIN {$prefix}ad_zone_assoc AS aza ON (aza.ad_id = b.bannerid AND aza.zone_id = z.zoneid)\n WHERE";
if (!empty($campaignId)) {
$fromWhereClause .= "\n b.campaignid = " . DBC::makeLiteral($campaignId) . "\n AND";
foreach ($aEmailZoneIds as $zoneId) {
$okToLink = Admin_DA::_checkEmailZoneAdAssoc($zoneId, $campaignId);
if (PEAR::isError($okToLink)) {
$aZonesIds = array_diff($aZonesIds, array($zoneId));
}
}
}
if (!empty($bannerId)) {
$fromWhereClause .= "\n b.bannerid = " . DBC::makeLiteral($bannerId) . "\n AND";
// Remove any zoneids which this banner cannot be linked to due to email zone restrictions
foreach ($aEmailZoneIds as $zoneId) {
$aAd = Admin_DA::getAd($bannerId);
$okToLink = Admin_DA::_checkEmailZoneAdAssoc($zoneId, $aAd['placement_id']);
if (PEAR::isError($okToLink)) {
$aZonesIds = array_diff($aZonesIds, array($zoneId));
}
}
}
$fromWhereClause .= "\n z.zoneid IN (" . implode(",", array_map('intval', $aZonesIds)) . ")\n AND\n (\n (\n b.storagetype = 'txt'\n AND\n z.delivery = " . phpAds_ZoneText . "\n )\n OR\n (\n z.delivery <> " . phpAds_ZoneText . "\n AND\n b.storagetype <> 'txt'\n AND\n (\n (\n ( z.width = -1\n OR\n z.width = b.width\n )\n AND\n ( z.height = -1\n OR\n z.height = b. height\n )\n )\n OR\n (\n b.height = -1 AND b.width = -1\n )\n )\n )\n )\n AND\n aza.ad_zone_assoc_id IS NULL\n ";
// if only one zone is selected and this zone is an email zone
// we only link it if it was not previously linked to any banner (email zones can be linked to one banner only)
if ($fastLinking) {
$query = "INSERT INTO {$prefix}ad_zone_assoc (zone_id, ad_id, priority_factor)\n SELECT z.zoneid, b.bannerid, 1\n {$fromWhereClause}\n ";
return $this->oDbh->exec($query);
} else {
$query = "\n SELECT z.zoneid AS zoneid,\n b.bannerid AS bannerid\n {$fromWhereClause}\n ";
$rsAdZones = DBC::NewRecordSet($query);
if (PEAR::isError($rsAdZones)) {
return $rsAdZones;
}
$aAdZones = $rsAdZones->getAll();
$doAdZoneAssoc = OA_Dal::factoryDO('ad_zone_assoc');
foreach ($aAdZones as $aAdZone) {
$doAdZoneAssoc->zone_id = $aAdZone['zoneid'];
$doAdZoneAssoc->ad_id = $aAdZone['bannerid'];
$doAdZoneAssoc->priority_factor = 1;
$doAdZoneAssoc->insert();
}
return count($aAdZones);
}
}
function migrateData()
{
$prefix = $this->getPrefix();
$tablePreference = $prefix . 'preference';
$aColumns = $this->oDBH->manager->listTableFields($tablePreference);
$sql = "\n\t SELECT * from {$prefix}config";
$rsConfig = DBC::NewRecordSet($sql);
if ($rsConfig->find() && $rsConfig->fetch()) {
$aDataConfig = $rsConfig->toArray();
$aValues = array();
foreach ($aDataConfig as $column => $value) {
if (in_array($column, $aColumns)) {
$aValues[$column] = $value;
}
}
// Migrate PAN config variables
$phpAdsNew = new OA_phpAdsNew();
$aPanConfig = $phpAdsNew->_getPANConfig();
$aValues['warn_admin'] = $aPanConfig['warn_admin'] ? 't' : 'f';
$aValues['warn_client'] = $aPanConfig['warn_client'] ? 't' : 'f';
$aValues['warn_limit'] = $aPanConfig['warn_limit'] ? $aPanConfig['warn_limit'] : 100;
$aValues['default_banner_url'] = $aPanConfig['default_banner_url'];
$aValues['default_banner_destination'] = $aPanConfig['default_banner_target'];
$result = $this->createGeoTargetingConfiguration($aPanConfig['geotracking_type'], $aPanConfig['geotracking_location'], $aPanConfig['geotracking_stats']);
if ($result === false) {
return $this->_logErrorAndReturnFalse('Error configuring geotargeting');
}
$sql = OA_DB_SQL::sqlForInsert('preference', $aValues);
$result = $this->oDBH->exec($sql);
if (PEAR::isError($result)) {
return $this->_logErrorAndReturnFalse('Error inserting preferences during data migration 108: ' . $result->getUserInfo());
}
return true;
} else {
return false;
}
}
/**
* A method to retrieve the table content as a recordset.
*
* @param string $sTableName The bucket table to process
* @param Date $oEnd A PEAR_Date instance, ending interval_start to process.
* @return MySqlRecordSet A recordset of the results
*/
private function getBucketTableContent($sTableName, $oEnd)
{
$query = "\n SELECT\n *\n FROM\n {$sTableName}\n WHERE\n date_time <= " . DBC::makeLiteral($oEnd->format('%Y-%m-%d %H:%M:%S'));
$rsDataRaw = DBC::NewRecordSet($query);
$rsDataRaw->find();
return $rsDataRaw;
}
/**
* Just generate empty RecordSet
*
* @return RecordSet always empty
*/
function _emptyRecordSet()
{
$tableCampaigns = $this->quoteTableName('campaigns');
return DBC::NewRecordSet("select * from {$tableCampaigns} where 1=0");
}
/**
* A private method to determine if a tracker is linked to an anonymous
* campaign or not.
*
* @access private
* @param integer $trackerId The tracker ID.
* @return boolean True if the tracker is linked to an anonymous campaign,
* false otherwise.
*/
function _isTrackerLinkedToAnonymousCampaign($trackerId)
{
$aConf = $GLOBALS['_MAX']['CONF'];
$query = "\n SELECT\n c.anonymous\n FROM\n {$aConf['table']['prefix']}{$aConf['table']['trackers']} AS t\n JOIN\n {$aConf['table']['prefix']}{$aConf['table']['campaigns_trackers']} AS ct\n ON\n (\n t.trackerid = ct.trackerid\n )\n JOIN\n {$aConf['table']['prefix']}{$aConf['table']['campaigns']} AS c\n ON\n (\n c.campaignid = ct.campaignid\n )\n WHERE\n t.trackerid = " . DBC::makeLiteral($trackerId, 'integer');
$rsTracker = DBC::NewRecordSet($query);
$rsTracker->find();
if (!$rsTracker->fetch()) {
// Unknown if anonymous or not! Return true to prevent
// accidental information leakage...
return true;
}
$aTracker = $rsTracker->toArray();
if ($aTracker['anonymous'] == 't') {
return true;
}
return false;
}
function testMigrateAcls()
{
$prefix = $this->getPrefix();
$this->initDatabase(127, array('banners', 'acls', 'channel', 'acls_channel'));
$aTestData = array(array('weekday', '==', '0,1'), array('weekday', '==', '0,1,4'), array('weekday', '==', ''), array('time', '==', '5,6'), array('time', '==', '5'), array('date', '>=', '20070510'), array('clientip', '==', '150.254.170.189'), array('domain', '!=', 'www.openx.org'), array('language', '!=', '(hr)|(nl)'), array('language', '!=', '(en)'), array('continent', '==', 'AF'), array('country', '==', 'PL,GB'), array('browser', '==', '(^Mozilla/5.*Gecko)|(Opera)|(MSN)'), array('browser', '!=', '(^Mozilla/5.*Gecko)|(Opera)|(MSN)'), array('os', '==', '(Win)|(Windows CE)|(Mac)|(Linux)|(BSD)|(SunOS)|(IRIX)|(AIX)|(Unix)'), array('useragent', '==', 'ahahaha'), array('referer', '==', 'blabblah'), array('source', '==', 'www.openx.org'), array('url', '==', 'www.openx.org/contacts.php'), array('postal_code', '==', '44100'), array('city', '==', 'ferrara'), array('fips_code', '==', 'GB08,GBN5'), array('region', '==', 'USAK,USAZ'), array('dma_code', '==', '501,502'), array('area_code', '==', '66099'), array('org_isp', '==', 'openads'), array('netspeed', '==', '1,2'), array('fips_code', '==', 'IT01,IT02,IE01,IE02,DE01'), array('fips_code', '!=', 'GB08,IT01,IT02'));
$aExpectedData = array(array('Time:Day', '=~', '0,1'), array('Time:Day', '=~', '0,1,4'), array('Time:Day', '=~', ''), array('Time:Hour', '=~', '5,6'), array('Time:Hour', '=~', '5'), array('Time:Date', '>=', '20070510'), array('Client:Ip', '==', '150.254.170.189'), array('Client:Domain', '!=', 'www.openx.org'), array('Client:Language', '!~', 'hr,nl'), array('Client:Language', '!~', 'en'), array('Geo:Continent', '=~', 'AF'), array('Geo:Country', '=~', 'PL,GB'), array('Client:Useragent', '=x', '(^Mozilla/5.*Gecko)|(Opera)|(MSN)'), array('Client:Useragent', '!x', '(^Mozilla/5.*Gecko)|(Opera)|(MSN)'), array('Client:Useragent', '=x', '(Win)|(Windows CE)|(Mac)|(Linux)|(BSD)|(SunOS)|(IRIX)|(AIX)|(Unix)'), array('Client:Useragent', '=x', 'ahahaha'), array('Site:Referingpage', '=~', 'blabblah'), array('Site:Source', '=x', 'www.openx.org'), array('Site:Pageurl', '=~', 'www.openx.org/contacts.php'), array('Geo:Postalcode', '=~', '44100'), array('Geo:City', '=~', '|ferrara'), array('Geo:Region', '=~', 'GB|08,N5'), array('Geo:Region', '=~', 'US|AK,AZ'), array('Geo:Dma', '=~', '501,502'), array('Geo:Areacode', '=~', '66099'), array('Geo:Organisation', '=~', 'openads'), array('Geo:Netspeed', '=~', 'dialup,cabledsl'), array('Geo:Region', '=~', 'DE|01', 'and'), array('Geo:Region', '!~', 'GB|08', 'and'), array('Geo:Region', '=~', 'IE|01,02', 'or'), array('Geo:Region', '=~', 'IT|01,02', 'or'), array('Geo:Region', '!~', 'IT|01,02', 'and'));
$sql = OA_DB_Sql::sqlForInsert('banners', array('bannerid' => 1, 'htmltemplate' => '', 'htmlcache' => '', 'bannertext' => '', 'compiledlimitation' => '', 'append' => ''));
$this->oDbh->exec($sql);
$aValues = array();
$idx = 0;
foreach ($aTestData as $testData) {
$aValues = array('bannerid' => 1, 'logical' => 'and', 'type' => $testData[0], 'comparison' => $testData[1], 'data' => $testData[2], 'executionorder' => $idx++);
$sql = OA_DB_Sql::sqlForInsert('acls', $aValues);
$this->oDbh->exec($sql);
}
$this->upgradeToVersion(128);
$table = $this->oDbh->quoteIdentifier($prefix . 'acls', true);
$rsAcls = DBC::NewRecordSet("\n SELECT type, comparison, data, logical\n FROM {$table}\n ORDER BY executionorder");
$this->assertTrue($rsAcls->find());
for ($idx = 0; $idx < count($aExpectedData); $idx++) {
$expected = $aExpectedData[$idx][0] . "|" . $aExpectedData[$idx][1] . "|" . $aExpectedData[$idx][2];
$this->assertTrue($rsAcls->fetch());
$this->assertEqual($aExpectedData[$idx][0], $rsAcls->get('type'), "%s IN TYPE FOR: {$expected}");
$this->assertEqual($aExpectedData[$idx][1], $rsAcls->get('comparison'), "%s IN COMPARISON FOR: {$expected}");
$this->assertEqual($aExpectedData[$idx][2], $rsAcls->get('data'));
if (!empty($aExpectedData[$idx][3])) {
$this->assertEqual($aExpectedData[$idx][3], $rsAcls->get('logical'));
}
}
$this->assertFalse($rsAcls->fetch());
}
/**
* Returns an array of variables connected to specific connection.
*
* @param array $aParams
* @return array
*/
function getConnectionVariables($connectionId)
{
$conf = $GLOBALS['_MAX']['CONF'];
$where = '';
if (empty($connectionId)) {
return false;
} else {
$connectionId = (int) $connectionId;
}
if (OA_Permission::isAccount(OA_ACCOUNT_TRAFFICKER)) {
$publisherId = OA_Permission::getEntityId();
} else {
$publisherId = 0;
}
$query = "SELECT\n v.variableid,\n v.name,\n v.description,\n vv.value,\n v.purpose,\n v.hidden,\n vp.visible\n FROM\n {$conf['table']['prefix']}{$conf['table']['variables']} AS v JOIN\n {$conf['table']['prefix']}{$conf['table']['data_intermediate_ad_variable_value']} AS vv ON (vv.tracker_variable_id = v.variableid) LEFT JOIN\n {$conf['table']['prefix']}{$conf['table']['variable_publisher']} AS vp ON (vp.variable_id = v.variableid AND vp.publisher_id = {$publisherId})\n WHERE\n vv.data_intermediate_ad_connection_id='" . DBC::makeLiteral($connectionId) . "'\n ORDER BY\n v.name";
$rsVariables = DBC::NewRecordSet($query);
if (!$rsVariables->find()) {
return false;
}
$aDataVariables = array();
while ($rsVariables->fetch()) {
$dataVariable = $rsVariables->toArray();
if (!is_null($dataVariable['visible'])) {
$dataVariable['hidden'] = $dataVariable['visible'] ? 'f' : 't';
}
unset($dataVariable['visible']);
$aDataVariables[$dataVariable['variableid']] = $dataVariable;
}
return $aDataVariables;
}
/**
* Returns the record set for either 'acls' or 'acls_channels' table,
* all records and rows.
*
* @param string $table Either 'acls' or 'acls_channels'
* @return RecordSet
*/
function &getRsAcls($table)
{
$table = $this->oDbh->quoteIdentifier($this->getTablePrefix() . $table);
$query = "\n SELECT\n *\n FROM\n {$table}";
return DBC::NewRecordSet($query);
}
/**
* The test to ensure that a campaign with banners that have time-based
* delivery limitations are correctly prioritised by the MPE.
*
* Test Basis:
*
* - One campaign, running from 2008-02-26 to 2008-02-27 (2 days).
* - Booked impressions of 48,000 impressions (i.e. 1,000 per hour
* required on average).
* - Two banners in the zone, both with weight one.
* - Banner ID 1 has a Time:Date delivery limitation, to only allow
* the banner to deliver on 2008-02-26.
* - Banner ID 2 has a Time:Date delivery limitation, to only allow
* the banner to deliver on 2008-02-27.
* - The campaign is linked to one constantly delivering zone (at
* 1,000 impressions per hour).
*
* - Run the MPE with an OI of 60 minutes for the two days of the
* campaign lifetime, and assume that all required impressions
* allocated to the banner(s) are delivered.
*
* The expected result of this is that the MPE should allocate 1,000
* impressions per hour for Banner ID 1 all day on 2008-02-26, and
* 1,000 impressions per hour for Banner ID 2 all day on 2008-02-37.
*/
function testCampaign()
{
$aConf =& $GLOBALS['_MAX']['CONF'];
$aConf['maintenance']['operationInteval'] = 60;
$aConf['priority']['useZonePatterning'] = false;
OA_setTimeZone('GMT');
$oServiceLocator =& OA_ServiceLocator::instance();
$oServiceLocator->register('now', new Date('2008-02-27'));
// Prepare the test campaign
$doCampaigns = OA_Dal::factoryDO('campaigns');
$doCampaigns->views = 48000;
$doCampaigns->clicks = -1;
$doCampaigns->conversions = -1;
$doCampaigns->activate_time = '2008-02-26 00:00:00';
$doCampaigns->expire_time = '2008-02-27 23:59:59';
$doCampaigns->priority = 10;
$doCampaigns->target_impression = 0;
$doCampaigns->target_click = 0;
$doCampaigns->target_conversion = 0;
$campaignId = DataGenerator::generateOne($doCampaigns);
// Prepare the first banner
$doBanners = OA_Dal::factoryDO('banners');
$doBanners->campaignid = $campaignId;
$doBanners->active = 't';
$doBanners->weight = 1;
$bannerId1 = DataGenerator::generateOne($doBanners);
$doAcls = OA_Dal::factoryDO('acls');
$doAcls->bannerid = $bannerId1;
$doAcls->logical = 'and';
$doAcls->type = 'deliveryLimitations:Time:Date';
$doAcls->comparison = '==';
$doAcls->data = '20080226';
$doAcls->executionorder = 0;
DataGenerator::generateOne($doAcls);
// Prepare the second banner
$doBanners = OA_Dal::factoryDO('banners');
$doBanners->campaignid = $campaignId;
$doBanners->active = 't';
$doBanners->weight = 1;
$bannerId2 = DataGenerator::generateOne($doBanners);
$doAcls = OA_Dal::factoryDO('acls');
$doAcls->bannerid = $bannerId2;
$doAcls->logical = 'and';
$doAcls->type = 'deliveryLimitations:Time:Date';
$doAcls->comparison = '==';
$doAcls->data = '20080227';
$doAcls->executionorder = 0;
DataGenerator::generateOne($doAcls);
// Prepare the zone
$doZones = OA_Dal::factoryDO('zones');
$zoneId = DataGenerator::generateOne($doZones);
// Link the banners to the zone
$doAd_zone_assoc = OA_Dal::factoryDO('ad_zone_assoc');
$doAd_zone_assoc->zone_id = $zoneId;
$doAd_zone_assoc->ad_id = $bannerId1;
DataGenerator::generateOne($doAd_zone_assoc);
$doAd_zone_assoc = OA_Dal::factoryDO('ad_zone_assoc');
$doAd_zone_assoc->zone_id = $zoneId;
$doAd_zone_assoc->ad_id = $bannerId2;
DataGenerator::generateOne($doAd_zone_assoc);
// Run the code to get the required ad impressions over
// the 48 hour period of the test
for ($counter = 1; $counter <= 48; $counter++) {
// Set the "current" date/time that the MPE would be
// running at for the appropriate hour of the test
$oNowDate = new Date('2008-02-26 00:00:01');
$oNowDate->addSeconds(($counter - 1) * SECONDS_PER_HOUR);
$oServiceLocator->register('now', $oNowDate);
// Run the code to get the required ad impressions
$oGetRequiredAdImpressionsLifetime = new OA_Maintenance_Priority_AdServer_Task_GetRequiredAdImpressionsLifetime();
$oGetRequiredAdImpressionsLifetime->run();
// Test that 1,000 impressions have been "required" for
// the appropriate banner
$query = "SELECT * FROM tmp_ad_required_impression";
$rsRequiredImpression = DBC::NewRecordSet($query);
$rsRequiredImpression->find();
$aRequiredImpressions = $rsRequiredImpression->getAll();
$this->assertTrue(is_array($aRequiredImpressions), "No array for required impressions SQL result in test hour {$counter}");
$this->assertEqual(count($aRequiredImpressions), 1, "More than one row found for required impressions SQL result in test hour {$counter}");
$this->assertTrue(is_array($aRequiredImpressions[0]), "Badly formatted result row for required impressions SQL result in test hour {$counter}");
$this->assertEqual(count($aRequiredImpressions[0]), 2, "Badly formatted result row for required impressions SQL result in test hour {$counter}");
$bannerId = $aRequiredImpressions[0]['ad_id'];
if ($counter <= 24) {
$this->assertEqual($bannerId, $bannerId1, "Expected required impressions for banner ID {$bannerId1} in test hour {$counter}");
} else {
$this->assertEqual($bannerId, $bannerId2, "Expected required impressions for banner ID {$bannerId2} in test hour {$counter}");
}
$impressions = $aRequiredImpressions[0]['required_impressions'];
$this->assertEqual($impressions, 1000, "Incorrectly requested {$impressions} impressions instead of 1000 in test hour {$counter}");
// Insert the required impressions for the banner into the
// data_intermediate_ad table, as if the delivery has occurred,
// so that the next hour's test is based on delivery having happened
$aDates = OX_OperationInterval::convertDateToOperationIntervalStartAndEndDates($oNowDate);
$operationIntervalId = OX_OperationInterval::convertDateToOperationIntervalID($oNowDate);
$doData_intermediate_ad = OA_Dal::factoryDO('data_intermediate_ad');
$doData_intermediate_ad->day = $aDates['start']->format('%Y-%m-%d');
$doData_intermediate_ad->hour = $aDates['start']->format('%H');
$doData_intermediate_ad->operation_interval = $aConf['maintenance']['operationInteval'];
$doData_intermediate_ad->operation_interval_id = $operationIntervalId;
$doData_intermediate_ad->interval_start = $aDates['start']->format('%Y-%m-%d %H:%M:%S');
$doData_intermediate_ad->interval_end = $aDates['end']->format('%Y-%m-%d %H:%M:%S');
$doData_intermediate_ad->ad_id = $bannerId;
$doData_intermediate_ad->zone_id = $zoneId;
$doData_intermediate_ad->requests = $impressions;
$doData_intermediate_ad->impressions = $impressions;
$doData_intermediate_ad->clicks = 0;
$doData_intermediate_ad->conversions = 0;
DataGenerator::generateOne($doData_intermediate_ad);
// Drop the temporary table that is used to store the
// required impressions, so that it does not interfer
// with the next test run in the loop
unset($GLOBALS['_OA']['DB_TABLES']['tmp_ad_required_impression']);
$oTable =& OA_DB_Table_Priority::singleton();
foreach ($oTable->aDefinition['tables'] as $tableName => $aTable) {
$oTable->truncateTable($tableName);
$oTable->dropTable($tableName);
}
}
}
/**
* Join all banners, campaigns and clients and return it as RecordSet
*
* @return RecordSet
*/
function getBannersCampaignsClients()
{
$prefix = $this->getTablePrefix();
$oDbh = OA_DB::singleton();
$tableB = $oDbh->quoteIdentifier($prefix . 'banners', true);
$tableC = $oDbh->quoteIdentifier($prefix . 'campaigns', true);
$tableCl = $oDbh->quoteIdentifier($prefix . 'clients', true);
$query = "\n SELECT\n b.bannerid,\n b.campaignid,\n b.description,\n c.clientid,\n c.campaignname,\n cl.clientname\n FROM\n {$tableB} AS b,\n {$tableC} AS c,\n {$tableCl} AS cl\n WHERE\n c.campaignid=b.campaignid\n AND cl.clientid=c.clientid\n ";
return DBC::NewRecordSet($query);
}
/**
* This method returns statistics for a given publisher, broken down by banner.
*
* @access public
*
* @param integer $publisherId The ID of the publisher to view statistics
* @param date $oStartDate The date from which to get statistics (inclusive)
* @param date $oEndDate The date to which to get statistics (inclusive)
* @param bool $localTZ Should stats be using the manager TZ or UTC?
*
* @return RecordSet
* <ul>
* <li><b>bannerID integer</b> The ID of the banner
* <li><b>bannerName string (255)</b> The name of the banner
* <li><b>campaignID integer</b> The ID of the banner
* <li><b>campaignName string (255)</b> The name of the banner
* <li><b>advertiserID integer</b> The ID of the advertiser
* <li><b>advertiserName string</b> The name of the advertiser
* <li><b>requests integer</b> The number of requests for the banner
* <li><b>impressions integer</b> The number of impressions for the banner
* <li><b>clicks integer</b> The number of clicks for the banner
* <li><b>revenue decimal</b> The revenue earned for the banner
* </ul>
*
*/
function getPublisherBannerStatistics($publisherId, $oStartDate, $oEndDate, $localTZ = false)
{
$publisherId = $this->oDbh->quote($publisherId, 'integer');
$tableZones = $this->quoteTableName('zones');
$tableAffiliates = $this->quoteTableName('affiliates');
$tableClients = $this->quoteTableName('clients');
$tableCampaigns = $this->quoteTableName('campaigns');
$tableBanners = $this->quoteTableName('banners');
$tableSummary = $this->quoteTableName('data_summary_ad_hourly');
$query = "\n SELECT\n SUM(s.impressions) AS impressions,\n SUM(s.clicks) AS clicks,\n SUM(s.requests) AS requests,\n SUM(s.total_revenue) AS revenue,\n SUM(s.conversions) AS conversions,\n m.campaignid AS campaignID,\n m.campaignname AS campaignName,\n c.clientid AS advertiserID,\n c.clientname AS advertiserName,\n b.bannerid AS bannerID,\n b.description AS bannerName\n FROM\n {$tableZones} AS z,\n {$tableAffiliates} AS p,\n\n {$tableClients} AS c,\n {$tableCampaigns} AS m,\n {$tableBanners} AS b,\n\n {$tableSummary} AS s\n WHERE\n p.affiliateid = {$publisherId}\n\n AND\n c.clientid = m.clientid\n AND\n m.campaignid = b.campaignid\n AND\n b.bannerid = s.ad_id\n\n AND\n p.affiliateid = z.affiliateid\n AND\n z.zoneid = s.zone_id\n\n " . $this->getWhereDate($oStartDate, $oEndDate, $localTZ) . "\n GROUP BY\n b.bannerid, b.description,\n m.campaignid, m.campaignname,\n c.clientid, c.clientname\n ";
return DBC::NewRecordSet($query);
}
