/** * @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); }