/**
* @param $findInSet string Data to look after (eg 13)
* @param $type string Data type (eg Site:Channel)
*
* @return RecordSet
* @access public
*/
function getAclsByDataValueType($findInSet, $type)
{
$findInSet = "FIND_IN_SET(" . DBC::makeLiteral($findInSet) . ", data)";
$table = $this->oDbh->quoteIdentifier($this->getTablePrefix() . 'acls');
$query = "\n SELECT\n *,\n {$findInSet}\n FROM\n {$table}\n WHERE\n type = " . DBC::makeLiteral($type) . "\n AND {$findInSet} > 0\n ";
return DBC::NewRecordSet($query);
}
/**
* 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 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 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 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);
}
/**
* Generate and save a recovery ID for a user
*
* @param int user ID
* @return array generated recovery ID
*/
function generateRecoveryId($userId)
{
$doPwdRecovery = OA_Dal::factoryDO('password_recovery');
// Make sure that recoveryId is unique in password_recovery table
do {
$recoveryId = strtoupper(md5(uniqid('', true)));
$recoveryId = substr(chunk_split($recoveryId, 8, '-'), -23, 22);
$doPwdRecovery->recovery_id = $recoveryId;
} while ($doPwdRecovery->find() > 0);
$doPwdRecovery = OA_Dal::factoryDO('password_recovery');
$doPwdRecovery->whereAdd('user_id = ' . DBC::makeLiteral($userId));
$doPwdRecovery->delete(true);
$doPwdRecovery = OA_Dal::factoryDO('password_recovery');
$doPwdRecovery->user_type = 'user';
$doPwdRecovery->user_id = $userId;
$doPwdRecovery->recovery_id = $recoveryId;
$doPwdRecovery->updated = OA::getNowUTC();
$doPwdRecovery->insert();
return $recoveryId;
}
/**
* Move banner to different campaign
*
* @param int $bannerId
* @param int $campaignId
* @return bool True on success
*/
function moveBannerToCampaign($bannerId, $campaignId)
{
$Record = DBC::NewRecord();
$oDbh = OA_DB::singleton();
$tableB = $oDbh->quoteIdentifier($this->getTablePrefix() . 'banners', true);
return $Record->update($tableB, array(), "bannerid=" . DBC::makeLiteral($bannerId), array('campaignid' => DBC::makeLiteral($campaignId)));
}
function _getOperationIntervalInfo(&$operationIntervalId, &$operationInterval, &$dateStart, &$dateEnd)
{
$date = new Date();
$operationInterval = new OX_OperationInterval();
$operationIntervalId = $operationInterval->convertDateToOperationIntervalID($date);
$operationInterval = OX_OperationInterval::getOperationInterval();
$aOperationIntervalDates = OX_OperationInterval::convertDateToOperationIntervalStartAndEndDates($date);
$dateStart = DBC::makeLiteral($aOperationIntervalDates['start']->format(TIMESTAMP_FORMAT));
$dateEnd = DBC::makeLiteral($aOperationIntervalDates['end']->format(TIMESTAMP_FORMAT));
}
/**
* 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;
}
/**
* A private method to obtain the raw delivery performance data for a given date range.
*
* @access private
* @param Admin_UI_OrganisationScope $oScope The Admin_UI_OrganisationScope limitation object for
* the report.
* @param OA_Admin_DaySpan $oDaySpan The OA_Admin_DaySpan day range limitation object for the report,
* or for "yesterday" or "today" as required.
* @param boolean $spanIsForPlacementDates If true, $oDaySpan is used for the start/end date limitaion
* of the placements, otherwise it is used to limit the
* data to delivery that happened in the $oDaySpan range.
* @return array
*/
function _getDeliveryPerformanceDataRange($oScope, $oDaySpan, $spanIsForPlacementDates = false, $statsTable = false, $appendSqlWhere = false)
{
$aConf = $GLOBALS['_MAX']['CONF'];
if (empty($appendSqlWhere)) {
$appendSqlWhere = "AND c.type = " . DataObjects_Campaigns::CAMPAIGN_TYPE_DEFAULT . " ";
}
if ($statsTable === false) {
$statsTable = $aConf['table']['prefix'] . $aConf['table']['data_summary_ad_hourly'];
}
$advertiserId = $oScope->getAdvertiserId();
$publisherId = $oScope->getPublisherId();
$agencyId = $oScope->getAgencyId();
$query = "\n SELECT\n c.campaignid AS campaign_id,\n c.campaignname AS campaign_name,\n c.priority AS campaign_priority,\n c.status AS campaign_is_active,\n c.activate_time AS campaign_start,\n c.expire_time AS campaign_end,\n c.views AS campaign_booked_impressions,\n SUM(stats.impressions) AS campaign_impressions,\n MAX(stats.date_time) AS stats_most_recent_date_time\n FROM\n {$aConf['table']['prefix']}{$aConf['table']['campaigns']} AS c,\n {$aConf['table']['prefix']}{$aConf['table']['banners']} AS b,\n " . $statsTable . " AS stats";
if ($publisherId) {
$query .= ",\n {$aConf['table']['prefix']}{$aConf['table']['zones']} AS z";
}
if ($agencyId) {
$query .= ",\n {$aConf['table']['prefix']}{$aConf['table']['clients']} AS a";
}
$query .= "\n WHERE\n c.campaignid = b.campaignid\n\t\t\t\t" . $appendSqlWhere . "\n\t\t\t\tAND\n b.bannerid = stats.ad_id";
if ($spanIsForPlacementDates) {
$query .= "\n AND\n (\n c.activate_time <= " . DBC::makeLiteral($oDaySpan->getEndDateStringUTC(), 'string') . "\n OR\n c.activate_time IS NULL\n )\n AND\n (\n c.expire_time >= " . DBC::makeLiteral($oDaySpan->getStartDateStringUTC(), 'string') . "\n OR\n c.expire_time IS NULL\n )";
} else {
$query .= "\n AND\n stats.date_time >= " . DBC::makeLiteral($oDaySpan->getStartDateStringUTC(), 'string') . "\n AND\n stats.date_time <= " . DBC::makeLiteral($oDaySpan->getEndDateStringUTC(), 'string') . "\n ";
}
if ($advertiserId) {
$query .= "\n AND\n c.clientid = " . DBC::makeLiteral($advertiserId, 'integer');
}
if ($publisherId) {
$query .= "\n AND\n stats.zone_id = z.zoneid\n AND\n z.affiliateid = " . DBC::makeLiteral($publisherId, 'integer');
}
if ($agencyId) {
$query .= "\n AND\n c.clientid = a.clientid\n AND\n a.agencyid = " . DBC::makeLiteral($agencyId, 'integer');
}
$query .= "\n GROUP BY\n campaign_id,\n campaign_name,\n campaign_priority,\n campaign_is_active,\n campaign_start,\n campaign_end,\n campaign_booked_impressions\n ORDER BY\n campaign_impressions";
$rsDeliveryPerformanceData = DBC::NewRecordSet($query);
$rsDeliveryPerformanceData->find();
$aDeliveryPerformanceData = $rsDeliveryPerformanceData->getAll();
return $aDeliveryPerformanceData;
}
/**
* Batch unlinking zones from banner
*
* @param array $aZonesIds array of zones IDs
* @param int $bannerId the banner ID.
* @return int number of unlinked zones, -1 on parameters error, PEAR:Errors on DB errors
*/
function unlinkZonesFromBanner($aZonesIds, $bannerId)
{
if (!is_array($aZonesIds)) {
return -1;
} else {
if (count($aZonesIds) == 0) {
return 0;
}
}
$prefix = $this->getTablePrefix();
$fastLinking = !$GLOBALS['_MAX']['CONF']['audit']['enabledForZoneLinking'];
if ($fastLinking) {
// Delete ad_zone_assoc
$query = "\n DELETE\n FROM {$prefix}ad_zone_assoc\n WHERE\n ad_id = " . DBC::makeLiteral($bannerId) . "\n AND\n zone_id IN (" . implode(",", array_map('intval', $aZonesIds)) . ")\n ";
return $this->oDbh->exec($query);
} else {
//slow - uses audit trail
// Do a iteration to add all deleted ad_zone_assoc to audit log
// it doesn't log all deleted rows when using
// $doAdZoneAssoc->addWhere(
// ad_id IN (" . implode(',', $aBannersIds) . ")
// AND
// zone_id IN (" . implode(",",$aZonesIds) . ")
//
$doAdZoneAssocEmpty = OA_Dal::factoryDO('ad_zone_assoc');
foreach ($aZonesIds as $zonesId) {
$doAdZoneAssoc = clone $doAdZoneAssocEmpty;
// Every delete have to be done on separate object
$doAdZoneAssoc->zone_id = $zonesId;
$doAdZoneAssoc->ad_id = $bannerId;
$doAdZoneAssoc->delete();
}
return count($aZonesIds);
}
}
/**
* Updates the table with the specified $aValues where $idColumn equals
* $id. Returns number of rows updated on success or PEAR::Error on
* failure.
*
* @param string $table
* @param string $idColumn
* @param string $id
* @param array $aValues A map from column name => new value
* @return int Number of rows updated on success or PEAR::Error on failure.
*/
function updateWhereOne($table, $idColumn, $id, $aValues)
{
$aSet = array();
foreach ($aValues as $column => $value) {
$aSet[] = "{$column} = " . DBC::makeLiteral($value);
}
$sSet = implode(",", $aSet);
$table = OA_DB_Sql::modifyTableName($table);
$sql = "UPDATE {$table} SET {$sSet} WHERE {$idColumn} = {$id}";
$dbh =& OA_DB::singleton();
return $dbh->exec($sql);
}
/**
* TODO: Should we refactor this method in more general one?
* (maybe by creating common abstract class for all summary tables?)
*
* @param string $operation Either + or -
* @param int $basketValue
* @param int $numItems
* @param int $ad_id
* @param int $creative_id
* @param int $zone_id
* @param strin $day
* @param string $hour
* @return unknown
*/
function addConversion($operation, $basketValue, $numItems, $ad_id, $creative_id, $zone_id, $day, $hour, $table = null)
{
$prefix = $this->getTablePrefix();
if ($operation != '-') {
$operation = '+';
}
if ($table == null) {
$table = $this->table;
}
$oDbh = OA_DB::singleton();
$table = $oDbh->quoteIdentifier($prefix . $table, true);
$query = '
UPDATE ' . $table . ' SET conversions=conversions' . $operation . '1
, total_basket_value=total_basket_value' . $operation . DBC::makeLiteral($basketValue) . '
, total_num_items=total_num_items' . $operation . DBC::makeLiteral($numItems) . '
, updated = \'' . OA::getNow() . '\'
WHERE
ad_id = ' . DBC::makeLiteral($ad_id) . '
AND creative_id = ' . DBC::makeLiteral($creative_id) . '
AND zone_id = ' . DBC::makeLiteral($zone_id) . '
AND date_time = ' . DBC::makeLiteral(sprintf("%s %02d:00:00", $day, $hour));
return DBC::execute($query);
}
/**
* requires permission checks
*
* @param array $aParam
* @return array
*/
function getAuditLogForAuditWidget($aParam = array())
{
$oAudit = OA_Dal::factoryDO('audit');
// Apply account level filters
if (!empty($aParam['account_id'])) {
$oAudit->account_id = $aParam['account_id'];
}
if (!empty($aParam['advertiser_account_id'])) {
$oAudit->advertiser_account_id = $aParam['advertiser_account_id'];
}
if (!empty($aParam['website_account_id'])) {
$oAudit->website_account_id = $aParam['website_account_id'];
}
$oDate = new Date();
$oDate->toUTC();
$oDate->subtractSpan(new Date_Span('7-0-0-0'));
$oAudit->whereAdd("username <> 'Maintenance'");
$oAudit->whereAdd('parentid IS NULL');
$oAudit->whereAdd("updated >= " . DBC::makeLiteral($oDate->format('%Y-%m-%d %H:%M:%S')));
$oAudit->orderBy('auditid DESC');
$oAudit->limit(0, 5);
$numRows = $oAudit->find();
$oNow = new Date();
$aResult = array();
while ($oAudit->fetch()) {
$aAudit = $oAudit->toArray();
$oDate = new Date($aAudit['updated']);
$oDate->setTZbyID('UTC');
$oDate->convertTZ($oNow->tz);
$aAudit['updated'] = $oDate->format('%Y-%m-%d %H:%M:%S');
$aAudit['details'] = unserialize($aAudit['details']);
$aAudit['context'] = $this->getContextDescription($aAudit['context']);
$aResult[] = $aAudit;
}
return $aResult;
}
/**
* 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;
}
/**
* 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;
}
/**
* Prepare array of include system types for campaigns, include ADVERTISER_TYPE_DEFAULT
* All values are prepared by DBC::makeLiteral
*
* @param array $aIncludeSystemTypes input array
* @return array prepared array
*/
private function _prepareIncludeSystemTypes($aIncludeSystemTypes)
{
$aIncludeSystemTypes = array_merge(array(DataObjects_Campaigns::CAMPAIGN_TYPE_DEFAULT), $aIncludeSystemTypes);
foreach ($aIncludeSystemTypes as $k => $v) {
$aIncludeSystemTypes[$k] = DBC::makeLiteral((int) $v);
}
return $aIncludeSystemTypes;
}
/**
* 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);
}
/**
* @todo Verify that SQL is ANSI-compliant
* @todo Consider reducing duplication with countCampaignsUnderAgency()
* @todo Consider moving to Agency DAL
*/
function countActiveCampaignsUnderAgency($agency_id)
{
$conf = $GLOBALS['_MAX']['CONF'];
$oDbh = OA_DB::singleton();
$tableM = $oDbh->quoteIdentifier($this->getTablePrefix() . 'campaigns', true);
$tableC = $oDbh->quoteIdentifier($this->getTablePrefix() . 'clients', true);
$query_active_campaigns = "SELECT count(*) AS count" . " FROM " . $tableM . " AS m" . "," . $tableC . " AS c" . " WHERE m.clientid=c.clientid" . " AND c.agencyid=" . DBC::makeLiteral($agency_id) . " AND m.status=" . OA_ENTITY_STATUS_RUNNING;
return $this->oDbh->queryOne($query_active_campaigns);
}
