..)` part of a query.
| public static getSqlStringFieldsArray ( array | string $fields ) : string | ||
| $fields | array | string | The names of the mysql table fields to bind, e.g. `array(fieldName1, fieldName2, fieldName3)`. _Note: The content of the array isn't important, just its length._ |
| return | string | The placeholder string, e.g. `"?, ?, ?"`. |
/**
* uses tracker db
*/
public function insertNew($goal)
{
$Generic = Factory::getGeneric($this->db);
// pg is throwing error when empty values are given for 'FLOAT' columns
if (empty($goal['revenue'])) {
unset($goal['revenue']);
}
if (empty($goal['revenue_subtotal'])) {
unset($goal['revenue_subtotal']);
}
if (empty($goal['revenue_tax'])) {
unset($goal['revenue_tax']);
}
if (empty($goal['revenue_shipping'])) {
unset($goal['revenue_shipping']);
}
if (empty($goal['revenue_discount'])) {
unset($goal['revenue_discount']);
}
$fields = implode(', ', array_keys($goal));
$bindFields = Common::getSqlStringFieldsArray($goal);
$goal['idvisitor'] = $Generic->bin2db($goal['idvisitor']);
$sql = 'INSERT INTO ' . $this->table . '( ' . $fields . ' ) ' . 'VALUES ( ' . $bindFields . ' ) ';
$bind = array_values($goal);
$result = $Generic->insertIgnore($sql, $bind);
return $result;
}
private function insertInto($table, $row)
{
$columns = implode(', ', array_keys($row));
$columnsPlaceholders = Common::getSqlStringFieldsArray($row);
$values = array_values($row);
Db::query("INSERT INTO " . Common::prefixTable($table) . " ({$columns}) VALUES ({$columnsPlaceholders})", $values);
}
/**
* Insert constructor.
* @param string $table
* @param array $columnValuePairs array(columnName => columnValue)
*/
public function __construct($table, $columnValuePairs)
{
$columns = implode('`, `', array_keys($columnValuePairs));
$bind = array_values($columnValuePairs);
$sql = sprintf('INSERT INTO `%s` (`%s`) VALUES (%s)', $table, $columns, Common::getSqlStringFieldsArray($columnValuePairs));
parent::__construct($sql, $bind, static::ERROR_CODE_DUPLICATE_ENTRY);
}
public function add($visitor_info)
{
$fields = implode(', ', array_keys($visitor_info));
$values = Common::getSqlStringFieldsArray($visitor_info);
$bind = array_values($visitor_info);
$sql = 'INSERT INTO ' . $this->table . '( ' . $fields . ') VALUES (' . $values . ')';
$this->db->query($sql, $bind);
return $this->db->lastInsertId();
}
/**
* Performs a batch insert into a specific table by iterating through the data
*
* NOTE: you should use tableInsertBatch() which will fallback to this function if LOAD DATA INFILE not available
*
* @param string $tableName PREFIXED table name! you must call Common::prefixTable() before passing the table name
* @param array $fields array of unquoted field names
* @param array $values array of data to be inserted
* @param bool $ignoreWhenDuplicate Ignore new rows that contain unique key values that duplicate old rows
*/
public static function tableInsertBatchIterate($tableName, $fields, $values, $ignoreWhenDuplicate = true)
{
$fieldList = '(' . join(',', $fields) . ')';
$ignore = $ignoreWhenDuplicate ? 'IGNORE' : '';
foreach ($values as $row) {
$query = "INSERT {$ignore}\n\t\t\t\t\tINTO " . $tableName . "\n\t\t\t\t\t{$fieldList}\n\t\t\t\t\tVALUES (" . Common::getSqlStringFieldsArray($row) . ")";
Db::query($query, $row);
}
}
/**
* Returns the list of all the users
*
* @param string[] $userLogins List of users to select. If empty, will return all users
* @return array the list of all the users
*/
public function getUsers(array $userLogins)
{
$where = '';
$bind = array();
if (!empty($userLogins)) {
$where = 'WHERE login IN (' . Common::getSqlStringFieldsArray($userLogins) . ')';
$bind = $userLogins;
}
$users = $this->db->fetchAll("SELECT * FROM " . $this->table . "\n {$where}\n ORDER BY login ASC", $bind);
return $users;
}
/**
* Returns the list of all the users
*
* @param string[] $userLogins List of users to select. If empty, will return all users
* @return array the list of all the users
*/
public function getUsers(array $userLogins)
{
$where = '';
$bind = array();
if (!empty($userLogins)) {
$where = 'WHERE login IN (' . Common::getSqlStringFieldsArray($userLogins) . ')';
$bind = $userLogins;
}
$users = Db::get()->fetchAll("SELECT *\n FROM " . Common::prefixTable("user") . "\n {$where}\n ORDER BY login ASC", $bind);
return $users;
}
public function createConversion($conversion)
{
$fields = implode(", ", array_keys($conversion));
$bindFields = Common::getSqlStringFieldsArray($conversion);
$table = Common::prefixTable('log_conversion');
$db = $this->getDb();
$Generic = Factory::getGeneric($db);
$sql = "INSERT INTO {$table} ({$fields}) VALUES ({$bindFields}) ";
if (!empty($conversion['idvisitor'])) {
$conversion['idvisitor'] = $Generic->bin2db($conversion['idvisitor']);
}
$bind = array_values($conversion);
$result = $Generic->insertIgnore($sql, $bind);
// If a record was inserted, we return true
return $db->rowCount($result) > 0;
}
/**
* @param $valueToMatch
* @param $sql
* @return array|null
* @throws \Exception
*/
public function getIdActionFromSegment($valueToMatch, $sql)
{
if (!$this->isEnabled) {
return array('SQL' => $sql, 'bind' => $valueToMatch);
}
$ids = self::getIdsFromCache($valueToMatch, $sql);
if (is_null($ids)) {
// Too Big To Cache, issue SQL as subquery instead
return array('SQL' => $sql, 'bind' => $valueToMatch);
}
if (count($ids) == 0) {
return null;
}
$sql = Common::getSqlStringFieldsArray($ids);
$bind = $ids;
return array('SQL' => $sql, 'bind' => $bind);
}
public function add($visitor_info)
{
$fields = implode(', ', array_keys($visitor_info));
$values = Common::getSqlStringFieldsArray($visitor_info);
$visitor_info['config_id'] = bin2hex($visitor_info['config_id']);
$visitor_info['idvisitor'] = bin2hex($visitor_info['idvisitor']);
$visitor_info['location_ip'] = bin2hex($visitor_info['location_ip']);
// Integration/BlobReportLimitingTest.php was failing because config_device_type
// had bool(false) as value which is being interpreted as empty string by postgresql.
// To avoid the issue, if config_device_type is false, it is being set to null.
if (isset($visitor_info['config_device_type']) && $visitor_info['config_device_type'] === false) {
$visitor_info['config_device_type'] = null;
}
$bind = array_values($visitor_info);
$sql = 'INSERT INTO ' . $this->table . '( ' . $fields . ') VALUES (' . $values . ')';
$this->db->query($sql, $bind);
return $this->db->lastInsertId($this->table . '_idvisit');
}
/**
* Computes the total number of unique visitors who visited at least one site in,
* a set of sites and the number of unique visitors that visited all of the sites
* in the set.
*
* Comparison is done in dates for the UTC time, not for the site specific time.
*
* Performance: The SQL query this method executes was tested on a Piwik instance
* with 13 million visits total. Computing data for 4 sites with no
* date limit took 13s to complete.
*
* @param int[] $idSites The IDs of the sites for whom unique visitor counts should be
* computed.
* @param Date $startDate The lower bound of the date range of the visits to check.
* @param Date $endDate The upper bound of the date range of the visits to check.
* @param Segment $segment An optional segment to apply to the visits set before aggregation.
* To supply no segment, use `new Segment()`.
* @return int[] Returns two metrics: **nb_total_visitors** and **nb_shared_visitors**.
*
* **nb_total_visitors** is the total number of unique visitors who visited
* at least one site in the list.
*
* **nb_shared_visitors** is the total number of unique visitors who visited
* every site in the list.
* @throws Exception if less than 2 site IDs are supplied,
*/
public function getCommonVisitorCount($idSites, Date $startDate, Date $endDate, Segment $segment)
{
Log::debug("%s::%s('%s', '%s', '%s', '%s') called", "Model\\DistinctMetricsAggregator", __FUNCTION__, $idSites, $startDate, $endDate, $segment);
if (count($idSites) == 1) {
throw new Exception(Piwik::translate('InterSites_PleasSupplyAtLeastTwoDifferentSites'));
}
$select = "config_id, COUNT(DISTINCT idsite) AS sitecount";
$from = array('log_visit');
$where = 'visit_last_action_time >= ? AND visit_last_action_time <= ? AND idsite IN (' . Common::getSqlStringFieldsArray($idSites) . ')';
$orderBy = false;
$groupBy = 'config_id';
$startDateTime = new \DateTime($startDate->toString());
$endDateTime = new \DateTime($endDate->toString());
$bind = array_merge(array($startDateTime->format("Y-m-d 00:00:00"), $endDateTime->format("Y-m-d 23:59:59")), $idSites);
$innerQuery = $segment->getSelectQuery($select, $from, $where, $bind, $orderBy, $groupBy);
$wholeQuery = "SELECT COUNT(sitecount_by_config.config_id) AS nb_total_visitors,\n SUM(IF(sitecount_by_config.sitecount >= " . count($idSites) . ", 1, 0)) AS nb_shared_visitors\n FROM ( {$innerQuery['sql']} ) AS sitecount_by_config";
$result = Db::fetchRow($wholeQuery, $innerQuery['bind']);
// nb_shared_visitors can be NULL if there are no visits
if ($result['nb_shared_visitors'] === null) {
$result['nb_shared_visitors'] = 0;
}
Log::debug("%s::%s() returned '%s'", "Model\\DistinctMetricsAggregator", __FUNCTION__, $result);
return $result;
}
public function getTriggeredAlerts($idSites, $login)
{
$idSites = array_map('intval', $idSites);
$db = $this->getDb();
$sql = $this->getTriggeredAlertsSelectPart() . " WHERE idsite IN (" . Common::getSqlStringFieldsArray($idSites) . ")" . " AND login = ?";
$values = $idSites;
$values[] = $login;
$alerts = $db->fetchAll($sql, $values);
$alerts = $this->completeAlerts($alerts);
return $alerts;
}
public function createVisit($visit)
{
$fields = array_keys($visit);
$fields = implode(", ", $fields);
$values = Common::getSqlStringFieldsArray($visit);
$table = Common::prefixTable('log_visit');
$sql = "INSERT INTO {$table} ({$fields}) VALUES ({$values})";
$bind = array_values($visit);
$db = $this->getDb();
$db->query($sql, $bind);
return $db->lastInsertId();
}
/**
* Helper function used by other record* methods which will INSERT or UPDATE the conversion in the DB
*
* @param array $conversion
* @param array $visitInformation
* @return bool
*/
protected function insertNewConversion($conversion, $visitInformation)
{
/**
* Triggered before persisting a new [conversion entity](/guides/persistence-and-the-mysql-backend#conversions).
*
* This event can be used to modify conversion information or to add new information to be persisted.
*
* @param array $conversion The conversion entity. Read [this](/guides/persistence-and-the-mysql-backend#conversions)
* to see what it contains.
* @param array $visitInformation The visit entity that we are tracking a conversion for. See what
* information it contains [here](/guides/persistence-and-the-mysql-backend#visits).
* @param \Piwik\Tracker\Request $request An object describing the tracking request being processed.
*/
Piwik::postEvent('Tracker.newConversionInformation', array(&$conversion, $visitInformation, $this->request));
$newGoalDebug = $conversion;
$newGoalDebug['idvisitor'] = bin2hex($newGoalDebug['idvisitor']);
Common::printDebug($newGoalDebug);
$fields = implode(", ", array_keys($conversion));
$bindFields = Common::getSqlStringFieldsArray($conversion);
$sql = 'INSERT IGNORE INTO ' . Common::prefixTable('log_conversion') . "\n ({$fields}) VALUES ({$bindFields}) ";
$bind = array_values($conversion);
$result = Tracker::getDatabase()->query($sql, $bind);
// If a record was inserted, we return true
return Tracker::getDatabase()->rowCount($result) > 0;
}
public function insertEcommerceItems($goal, $itemsToInsert)
{
$sql = 'INSERT INTO ' . $this->table . '(
idaction_sku
, idaction_name
, idaction_category
, idaction_category2
, idaction_category3
, idaction_category4
, idaction_category5
, price
, quantity
, deleted
, idorder
, idsite
, idvisitor
, server_time
, idvisit)
VALUES ';
$sql_parts = array();
$bind = array();
foreach ($itemsToInsert as $item) {
$row = array_values($this->getItemRowEnriched($goal, $item));
$sql_parts[] = ' ( ' . Common::getSqlStringFieldsArray($row) . ' ) ';
$bind = array_merge($bind, $row);
}
$sql .= implode(', ', $sql_parts);
$this->db->query($sql, $bind);
Common::printDebug($sql);
Common::printDebug($bind);
}
private function createLogIterationQuery($logTable, $idField, $fields, $conditions, $iterationStep)
{
$bind = array();
$sql = "SELECT " . implode(', ', $fields) . " FROM `" . Common::prefixTable($logTable) . "` WHERE {$idField} > ?";
foreach ($conditions as $condition) {
list($column, $operator, $value) = $condition;
if (is_array($value)) {
$sql .= " AND {$column} IN (" . Common::getSqlStringFieldsArray($value) . ")";
$bind = array_merge($bind, $value);
} else {
$sql .= " AND {$column} {$operator} ?";
$bind[] = $value;
}
}
$sql .= " ORDER BY {$idField} ASC LIMIT " . (int) $iterationStep;
return array($sql, $bind);
}
public function insertRecord($tableName, $bindArray)
{
$values = Common::getSqlStringFieldsArray($bindArray);
$sql = 'INSERT IGNORE INTO ' . $tableName . '( ' . implode(', ', array_keys($bindArray)) . ')' . ' VALUES ( ' . $values . ' ) ';
$this->db->query($sql, array_values($bindArray));
}
/**
* Returns all websites with a timezone matching one the specified timezones
*
* @param array $timezones
* @return array
* @ignore
*/
public function getSitesIdFromTimezones($timezones)
{
Piwik::checkUserHasSuperUserAccess();
$timezones = Piwik::getArrayFromApiParameter($timezones);
$timezones = array_unique($timezones);
$ids = Db::get()->fetchAll('SELECT idsite
FROM ' . Common::prefixTable('site') . '
WHERE timezone IN (' . Common::getSqlStringFieldsArray($timezones) . ')
ORDER BY idsite ASC', $timezones);
$return = array();
foreach ($ids as $id) {
$return[] = $id['idsite'];
}
return $return;
}
protected function insertBatch($tableName, $fields, $values)
{
$fieldList = '(' . join(',', $fields) . ')';
$params = Common::getSqlStringFieldsArray($values[0]);
$sql_base = 'INSERT INTO ' . $tableName . $fieldList . ' VALUES ';
$count = 0;
$sql_parts = array();
$bind = array();
while (list($k, $row) = each($values)) {
$sql_parts[] = '(' . $params . ')';
$bind = array_merge($bind, array_values($row));
++$count;
if ($count == 100) {
$sql = $sql_base . implode(",\n", $sql_parts);
$this->db->query($sql, $bind);
$count = 0;
$sql_parts = array();
$bind = array();
}
}
if (count($sql_parts) > 0) {
$sql = $sql_base . implode(",\n", $sql_parts);
$this->db->query($sql, $bind);
}
}
/**
* @return mixed
*/
protected function insertNewVisit($visit)
{
$fields = implode(", ", array_keys($visit));
$values = Common::getSqlStringFieldsArray($visit);
$sql = "INSERT INTO " . Common::prefixTable('log_visit') . " ({$fields}) VALUES ({$values})";
$bind = array_values($visit);
Tracker::getDatabase()->query($sql, $bind);
$idVisit = Tracker::getDatabase()->lastInsertId();
return $idVisit;
}
/**
* Performs a batch insert into a specific table by iterating through the data
*
* NOTE: you should use tableInsertBatch() which will fallback to this function if LOAD DATA INFILE not available
*
* @param string $tableName PREFIXED table name! you must call Piwik\Common::prefixTable() before passing the table name
* @param array $fields array of unquoted field names
* @param array $values array of data to be inserted
* @param bool $ignoreWhenDuplicate Ignore new rows that contain unique key values that duplicate old rows
*/
public function insertIgnoreBatch($tableName, $fields, $values, $ignoreWhenDuplicate = true)
{
$fieldList = '(' . join(',', $fields) . ')';
$params = Common::getSqlStringFieldsArray($values[0]);
$sql = 'INSERT INTO ' . $tableName . $fieldList . ' VALUES (' . $params . ')';
if ($ignoreWhenDuplicate) {
foreach ($values as $row) {
$this->insertIgnore($sql, $row);
}
} else {
foreach ($values as $row) {
$this->db->query($sql, $row);
}
}
}
/**
* Records in the DB the association between the visit and this action.
*
* @param int $idVisit is the ID of the current visit in the DB table log_visit
* @param $visitorIdCookie
* @param int $idReferrerActionUrl is the ID of the last action done by the current visit.
* @param $idReferrerActionName
* @param int $timeSpentReferrerAction is the number of seconds since the last action was done.
* It is directly related to idReferrerActionUrl.
*/
public function record($idVisit, $visitorIdCookie, $idReferrerActionUrl, $idReferrerActionName, $timeSpentReferrerAction)
{
$this->loadIdsFromLogActionTable();
$visitAction = array('idvisit' => $idVisit, 'idsite' => $this->request->getIdSite(), 'idvisitor' => $visitorIdCookie, 'server_time' => Tracker::getDatetimeFromTimestamp($this->request->getCurrentTimestamp()), 'idaction_url' => $this->getIdActionUrl(), 'idaction_url_ref' => $idReferrerActionUrl, 'idaction_name_ref' => $idReferrerActionName, 'time_spent_ref_action' => $timeSpentReferrerAction);
// idaction_name is NULLable. we only set it when applicable
if ($this->isActionHasActionName()) {
$visitAction['idaction_name'] = (int) $this->getIdActionName();
}
foreach ($this->actionIdsCached as $field => $idAction) {
$visitAction[$field] = $idAction === false ? 0 : $idAction;
}
$customValue = $this->getCustomFloatValue();
if (!empty($customValue)) {
$visitAction[self::DB_COLUMN_CUSTOM_FLOAT] = $customValue;
}
$customVariables = $this->getCustomVariables();
if (!empty($customVariables)) {
Common::printDebug("Page level Custom Variables: ");
Common::printDebug($customVariables);
}
$visitAction = array_merge($visitAction, $customVariables);
$fields = implode(", ", array_keys($visitAction));
$bind = array_values($visitAction);
$values = Common::getSqlStringFieldsArray($visitAction);
$sql = "INSERT INTO " . Common::prefixTable('log_link_visit_action') . " ({$fields}) VALUES ({$values})";
Tracker::getDatabase()->query($sql, $bind);
$this->idLinkVisitAction = Tracker::getDatabase()->lastInsertId();
$visitAction['idlink_va'] = $this->idLinkVisitAction;
Common::printDebug("Inserted new action:");
Common::printDebug($visitAction);
/**
* Triggered after successfully persisting a [visit action entity](/guides/persistence-and-the-mysql-backend#visit-actions).
*
* @param Action $tracker Action The Action tracker instance.
* @param array $visitAction The visit action entity that was persisted. Read
* [this](/guides/persistence-and-the-mysql-backend#visit-actions) to see what it contains.
*/
Piwik::postEvent('Tracker.recordAction', array($trackerAction = $this, $visitAction));
}
/**
* Executes and returns a query aggregating ecommerce item data (everything stored in the
* **log\_conversion\_item** table) and returns a DB statement that can be used to iterate over the result
*
* <a name="queryEcommerceItems-result-set"></a>
* **Result Set**
*
* Each row of the result set represents an aggregated group of ecommerce items. The following
* columns are in each row of the result set:
*
* - **{@link Piwik\Metrics::INDEX_ECOMMERCE_ITEM_REVENUE}**: The total revenue for the group of items.
* - **{@link Piwik\Metrics::INDEX_ECOMMERCE_ITEM_QUANTITY}**: The total number of items in this group.
* - **{@link Piwik\Metrics::INDEX_ECOMMERCE_ITEM_PRICE}**: The total price for the group of items.
* - **{@link Piwik\Metrics::INDEX_ECOMMERCE_ORDERS}**: The total number of orders this group of items
* belongs to. This will be <= to the total number
* of items in this group.
* - **{@link Piwik\Metrics::INDEX_NB_VISITS}**: The total number of visits that caused these items to be logged.
* - **ecommerceType**: Either {@link Piwik\Tracker\GoalManager::IDGOAL_CART} if the items in this group were
* abandoned by a visitor, or {@link Piwik\Tracker\GoalManager::IDGOAL_ORDER} if they
* were ordered by a visitor.
*
* **Limitations**
*
* Segmentation is not yet supported for this aggregation method.
*
* @param string $dimension One or more **log\_conversion\_item** columns to group aggregated data by.
* Eg, `'idaction_sku'` or `'idaction_sku, idaction_category'`.
* @return \Zend_Db_Statement A statement object that can be used to iterate through the query's
* result set. See [above](#queryEcommerceItems-result-set) to learn more
* about what this query selects.
* @api
*/
public function queryEcommerceItems($dimension)
{
$query = $this->generateQuery(implode(', ', array("log_action.name AS label", sprintf("log_conversion_item.%s AS labelIdAction", $dimension), sprintf('%s AS `%d`', self::getSqlRevenue('SUM(log_conversion_item.quantity * log_conversion_item.price)'), Metrics::INDEX_ECOMMERCE_ITEM_REVENUE), sprintf('%s AS `%d`', self::getSqlRevenue('SUM(log_conversion_item.quantity)'), Metrics::INDEX_ECOMMERCE_ITEM_QUANTITY), sprintf('%s AS `%d`', self::getSqlRevenue('SUM(log_conversion_item.price)'), Metrics::INDEX_ECOMMERCE_ITEM_PRICE), sprintf('COUNT(distinct log_conversion_item.idorder) AS `%d`', Metrics::INDEX_ECOMMERCE_ORDERS), sprintf('COUNT(distinct log_conversion_item.idvisit) AS `%d`', Metrics::INDEX_NB_VISITS), sprintf('CASE log_conversion_item.idorder WHEN \'0\' THEN %d ELSE %d END AS ecommerceType', GoalManager::IDGOAL_CART, GoalManager::IDGOAL_ORDER))), array("log_conversion_item", array("table" => "log_action", "joinOn" => sprintf("log_conversion_item.%s = log_action.idaction", $dimension))), implode(' AND ', array('log_conversion_item.server_time >= ?', 'log_conversion_item.server_time <= ?', 'log_conversion_item.idsite IN (' . Common::getSqlStringFieldsArray($this->sites) . ')', 'log_conversion_item.deleted = 0')), sprintf("ecommerceType, log_conversion_item.%s", $dimension), false);
return $this->getDb()->query($query['sql'], $query['bind']);
}
/**
* Performs a batch insert into a specific table by iterating through the data
*
* NOTE: you should use tableInsertBatch() which will fallback to this function if LOAD DATA INFILE not available
*
* @param string $tableName PREFIXED table name! you must call Piwik\Common::prefixTable() before passing the table name
* @param array $fields array of unquoted field names
* @param array $values array of data to be inserted
* @param bool $ignoreWhenDuplicate Ignore new rows that contain unique key values that duplicate old rows
*/
public function insertIgnoreBatch($tableName, $fields, $values, $ignoreWhenDuplicate = true)
{
$fieldList = '(' . join(',', $fields) . ')';
$ignore = $ignoreWhenDuplicate ? 'IGNORE' : '';
$params = Common::getSqlStringFieldsArray($values[0]);
foreach ($values as $row) {
$query = "INSERT {$ignore} INTO {$tableName} {$fieldList} VALUES ({$params}) \n";
// In some instances $row is an associative array, to make it
// compatible with positional parameters array, array_values($row)
// is used.
$this->db->query($query, array_values($row));
}
}
/**
* Queries and returns archive data using a set of archive IDs.
*
* @param array $archiveIds The IDs of the archives to get data from.
* @param array $recordNames The names of the data to retrieve (ie, nb_visits, nb_actions, etc.)
* @param string $archiveDataType The archive data type (either, 'blob' or 'numeric').
* @param bool $loadAllSubtables Whether to pre-load all subtables
* @throws Exception
* @return array
*/
public static function getArchiveData($archiveIds, $recordNames, $archiveDataType, $loadAllSubtables)
{
// create the SQL to select archive data
$inNames = Common::getSqlStringFieldsArray($recordNames);
if ($loadAllSubtables) {
$name = reset($recordNames);
// select blobs w/ name like "$name_[0-9]+" w/o using RLIKE
$nameEnd = strlen($name) + 2;
$whereNameIs = "(name = ?\n OR (name LIKE ?\n AND SUBSTRING(name, {$nameEnd}, 1) >= '0'\n AND SUBSTRING(name, {$nameEnd}, 1) <= '9') )";
$bind = array($name, $name . '%');
} else {
$whereNameIs = "name IN ({$inNames})";
$bind = array_values($recordNames);
}
$getValuesSql = "SELECT value, name, idsite, date1, date2, ts_archived\n FROM %s\n WHERE idarchive IN (%s)\n AND " . $whereNameIs;
// get data from every table we're querying
$rows = array();
foreach ($archiveIds as $period => $ids) {
if (empty($ids)) {
throw new Exception("Unexpected: id archive not found for period '{$period}' '");
}
// $period = "2009-01-04,2009-01-04",
$date = Date::factory(substr($period, 0, 10));
if ($archiveDataType == 'numeric') {
$table = ArchiveTableCreator::getNumericTable($date);
} else {
$table = ArchiveTableCreator::getBlobTable($date);
}
$sql = sprintf($getValuesSql, $table, implode(',', $ids));
$dataRows = Db::fetchAll($sql, $bind);
foreach ($dataRows as $row) {
$rows[] = $row;
}
}
return $rows;
}
/**
* uses tracker db
*/
public function insertNew($goal)
{
$fields = implode(', ', array_keys($goal));
$bindFields = Common::getSqlStringFieldsArray($goal);
$sql = 'INSERT IGNORE INTO ' . $this->table . '( ' . $fields . ' ) ' . 'VALUES ( ' . $bindFields . ' ) ';
$bind = array_values($goal);
$result = $this->db->query($sql, $bind);
return $this->db->rowCount($result) > 0;
}
/**
* Returns all websites with a timezone matching one the specified timezones
*
* @param array $timezones
* @return array
* @ignore
*/
public function getSitesFromTimezones($timezones)
{
$query = 'SELECT idsite FROM ' . $this->table . '
WHERE timezone IN (' . Common::getSqlStringFieldsArray($timezones) . ')
ORDER BY idsite ASC';
$sites = $this->db->fetchAll($query, $timezones);
return $sites;
}
/**
* @param $idSite
* @param string $table
* @return array
*/
private function getIdSitesWhereClause($idSite, $table = 'log_visit')
{
$idSites = array($idSite);
Piwik::postEvent('Live.API.getIdSitesString', array(&$idSites));
$idSitesBind = Common::getSqlStringFieldsArray($idSites);
$whereClause = $table . ".idsite in ({$idSitesBind}) ";
return array($whereClause, $idSites);
}
public function deleteArchiveIds($numericTable, $blobTable, $idsToDelete)
{
$idsToDelete = array_values($idsToDelete);
$query = "DELETE FROM %s WHERE idarchive IN (" . Common::getSqlStringFieldsArray($idsToDelete) . ")";
$queryObj = Db::query(sprintf($query, $numericTable), $idsToDelete);
$deletedRows = $queryObj->rowCount();
try {
$queryObj = Db::query(sprintf($query, $blobTable), $idsToDelete);
$deletedRows += $queryObj->rowCount();
} catch (Exception $e) {
// Individual blob tables could be missing
$this->logger->debug("Unable to delete archive IDs from {blobTable}.", array('blobTable' => $blobTable, 'exception' => $e));
}
return $deletedRows;
}
/**
* Returns the list of all the users
*
* @param string $userLogins Comma separated list of users to select. If not specified, will return all users
* @return array the list of all the users
*/
public function getUsers($userLogins = '')
{
Piwik::checkUserHasSomeAdminAccess();
$where = '';
$bind = array();
if (!empty($userLogins)) {
$userLogins = explode(',', $userLogins);
$where = 'WHERE login IN (' . Common::getSqlStringFieldsArray($userLogins) . ')';
$bind = $userLogins;
}
$db = Db::get();
$users = $db->fetchAll("SELECT *\n\t\t\t\t\t\t\t\tFROM " . Common::prefixTable("user") . "\n\t\t\t\t\t\t\t\t{$where}\n\t\t\t\t\t\t\t\tORDER BY login ASC", $bind);
// Non Super user can only access login & alias
if (!Piwik::isUserIsSuperUser()) {
foreach ($users as &$user) {
$user = array('login' => $user['login'], 'alias' => $user['alias']);
}
}
return $users;
}