/** * @param $account * @param $query * @param $since * @param $until * @param CsvFile $csvHandle * @throws InvalidTokenException * @throws \Exception * @throws \Zend_Uri_Exception */ private function _parseQuery($account, $query, $since, $until, CsvFile $csvHandle) { $parseQueryTime = microtime(true); // Fill configuration variables in query with values $parsedQuery = $query->query; if (strpos($parsedQuery, '[accountId]') !== false) { $parsedQuery = str_replace('[accountId]', $account['id'], $parsedQuery); } if (strpos($parsedQuery, '[') !== false) { $importConfig = $this->importConfig->toArray(); if (is_array($importConfig)) { foreach ($importConfig as $key => $value) { if (!is_array($value)) { $parsedQuery = str_replace('[' . $key . ']', $value, $parsedQuery); } } } } $apiUrlParams = null; $columnsToDownload = array(); if (in_array($query->type, array('insights', 'insightsLifetime', 'insightsPages', 'insightsPosts'))) { // Check if Insights type contains right url if (strpos($parsedQuery, '/insights') === FALSE) { throw new UserException(sprintf('Configuration query on row %d is not valid. Insights query needs to have format {objectId}/insights.', $this->currentConfigRowNumber)); } } else { if (in_array($query->type, array('insights_pivoted', 'insightsLifetime_pivoted', 'insightsPages_pivoted', 'insightsPosts_pivoted'))) { try { $columnsToDownload = \Zend_Json::decode($query->columns); } catch (\Exception $e) { throw new UserException("Can't decode column mapping for insightsPages_pivoted or insightsPosts_pivoted."); } } elseif ($query->type != 'value') { // Find out which values to download $columnsToDownload = explode(',', $query->columns); if (count($columnsToDownload)) { $fields = array(); foreach ($columnsToDownload as $column) { if (substr($column, 0, 1) != '#') { $fieldName = $column; $dotPosition = strpos($fieldName, '.'); if ($dotPosition !== FALSE) { $fieldName = substr($fieldName, 0, $dotPosition); } if (!in_array($fieldName, $fields)) { $fields[] = $fieldName; } } } if (count($fields)) { $apiUrlParams = '&fields=' . urlencode(implode(',', $fields)); } } } else { $columnsToDownload = explode(',', $query->columns); } } // Parse primary key column if (strpos($query->primaryColumn, '+') !== false) { $compositePrimaryKey = explode('+', $query->primaryColumn); } else { $compositePrimaryKey = array($query->primaryColumn); } // Default primary key for value type and add it also to columns to download if ($query->type == 'value') { if (in_array("#timestamp", $columnsToDownload)) { array_unshift($columnsToDownload, "#timestamp"); array_unshift($compositePrimaryKey, "#timestamp"); } if (in_array("#datestamp", $columnsToDownload)) { array_unshift($columnsToDownload, "#datestamp"); array_unshift($compositePrimaryKey, "#datestamp"); } array_unshift($columnsToDownload, "#objectId"); array_unshift($compositePrimaryKey, "#objectId"); $columnsToDownload = array_unique($columnsToDownload); $compositePrimaryKey = array_unique($compositePrimaryKey); } // If there is a table placeholder in query, run for each row from that table // So far we allow just one iteration // preg_match_all('/\{(\w+\.\w+)\}/U', $parsedQuery, $parsedResults); $matched = preg_match('/\\{(\\w+\\.\\w+(,\\w+(:\\d+)?)?)\\}/U', $parsedQuery, $parsedResults); if ($matched) { // Iterate through given table's column rows and call API for each result from the table $placeholderConfig = $parsedResults[1]; $tableConfig = explode('.', $placeholderConfig); $tableName = $tableConfig[0]; $columnName = $tableConfig[1]; $dataTableColumnsToFetch = array(); // Check if there is a date limit $daysLimit = 0; $timestampColumn = ''; $startTimestamp = 0; if (strpos($columnName, ',')) { $limit = explode(':', substr($columnName, strpos($columnName, ',') + 1)); if (!count($limit) == 2) { throw new UserException(sprintf('Configuration query on row %d is not valid. Placeholder has wrong format.', $this->currentConfigRowNumber)); } $timestampColumn = $limit[0]; $daysLimit = isset($limit[1]) ? intval($limit[1]) : 0; $columnName = substr($columnName, 0, strpos($columnName, ',')); $startTimestamp = $daysLimit > 0 ? strtotime('-' . $daysLimit . ' days') : null; $dataTableColumnsToFetch[] = $timestampColumn; } $dataTableColumnsToFetch[] = $columnName; $tableId = $this->storageApiBucket . '.' . $tableName; if (!isset($this->_sapiTableCache[$tableId])) { $this->log('Table \'$tableId\' for placeholders does not exist', array(), 0, true); return; } if (isset($this->_sapiTableCache[$tableId]["columns"])) { $tableConfig = $this->_sapiTableCache[$tableId]; } else { $tableConfig = $this->storageApi->getTable($tableId); $this->_sapiTableCache[$tableId] = $tableConfig; } if (!in_array($columnName, $tableConfig['columns'])) { $this->log('Wrong configuration - wrong column in placeholder', array(), 0, true); return; } $duration = microtime(true) - $parseQueryTime; // Cache, download for all accounts, filter later $tmpFile = sprintf('%s/sapi-%s-%s-%d-%s.csv', $this->tmpDir, $tableName, $columnName, $daysLimit, $account['id']); if (!file_exists($tmpFile)) { $exportTimeStart = microtime(true); $exportParams = array('columns' => $dataTableColumnsToFetch, 'whereValues' => $account['id'], 'whereColumn' => 'ex__account'); // Index column if needed and add to cache if (!in_array('ex__account', $this->_sapiTableCache[$tableId]["indexedColumns"])) { $tableInfo = $this->storageApi->getTable($tableId); if (!in_array('ex__account', $tableInfo["indexedColumns"])) { $this->storageApi->markTableColumnAsIndexed($tableId, 'ex__account'); $this->_sapiTableCache[$tableId] = $this->storageApi->getTable($tableId); } else { $this->_sapiTableCache[$tableId] = $tableInfo; } } $this->storageApi->exportTable($tableId, $tmpFile, $exportParams); } $firstLine = true; $headers = array(); $wantedRows = array(); if (($handle = fopen($tmpFile, "r")) !== FALSE) { while ($tableRow = fgetcsv($handle, null, ",", '"', '"')) { if ($firstLine) { $headers = $tableRow; } else { $tableRow = array_combine($headers, $tableRow); // Do not call twice for the same row values (via $wantedRows array) if (in_array($tableRow[$columnName], $wantedRows)) { continue; } // Check days limit of data if (!(!$startTimestamp || strtotime($tableRow[$timestampColumn]) >= $startTimestamp)) { continue; } // Run query to API for each placeholder table row $url = str_replace('{' . $placeholderConfig . '}', $tableRow[$columnName], $parsedQuery); try { // Validate url $url = Api::API_URL . $url . (strpos($url, '?') === FALSE ? '?' : '&') . 'access_token=' . trim($account['token']) . $apiUrlParams; $uri = \Zend_Uri_Http::fromString($url); if (!$uri->valid()) { $this->log(sprintf('Configuration query on row %d is not valid.', $this->currentConfigRowNumber), array('account' => $account['id'], 'url' => $url), 0, true); throw new UserException(sprintf('Configuration query on row %d is not valid.', $this->currentConfigRowNumber)); } $objectId = $tableRow[$columnName]; switch ($query->type) { case 'insights': case 'insightsPages': $this->_importInsightspages($account['id'], $objectId, $url, $since, $until, $csvHandle); break; case 'insights_pivoted': case 'insightsPages_pivoted': $this->_importInsightsPagesPivoted($account['id'], $objectId, $url, $since, $until, $columnsToDownload, $csvHandle); break; case 'insightsLifetime_pivoted': case 'insightsPosts_pivoted': $this->_importInsightsPostsPivoted($account['id'], $objectId, $url, $columnsToDownload, $csvHandle); break; case 'insightsLifetime': case 'insightsPosts': $this->_importInsightsPosts($account['id'], $objectId, $url, $csvHandle); break; case 'list': $this->_importList($account['id'], $objectId, $url, $since, $until, $compositePrimaryKey, $query->timestampColumn, $columnsToDownload, $csvHandle); break; case 'paginated': $this->_importPaginated($account['id'], $objectId, $url, $compositePrimaryKey, $columnsToDownload, $csvHandle); break; case 'value': $this->_importValue($account['id'], $objectId, $url, $compositePrimaryKey, $columnsToDownload, $csvHandle); break; default: $this->_importObject($account['id'], $objectId, $url, $compositePrimaryKey, $columnsToDownload, $csvHandle); } $wantedRows[] = $tableRow[$columnName]; } catch (RequestErrorException $e) { $this->log('Graph API Error', array('account' => $account['id'], 'url' => $url, 'error' => $e->getMessage()), 0, true); } catch (InvalidTokenException $e) { $e->setAccount($account["id"]); throw $e; } } $firstLine = false; } fclose($handle); } } else { $duration = microtime(true) - $parseQueryTime; // Run only for query in configuration row (there's no placeholder table) try { // Validate url $url = Api::API_URL . $parsedQuery . (strpos($parsedQuery, '?') === FALSE ? '?' : '&') . 'access_token=' . trim($account['token']) . $apiUrlParams; try { $uri = \Zend_Uri_Http::fromString($url); } catch (\Zend_Uri_Exception $e) { throw new UserException(sprintf('Configuration query on row %d is not valid: ' . $e->getMessage(), $this->currentConfigRowNumber)); } if (!$uri->valid()) { $this->log(sprintf('Configuration query on row %d is not valid.', $this->currentConfigRowNumber), array('account' => $account['id'], 'url' => $url), 0, true); throw new UserException(sprintf('Configuration query on row %d is not valid.', $this->currentConfigRowNumber)); } $objectId = substr($parsedQuery, 0, strpos($parsedQuery, '/')); switch ($query->type) { case 'insights': case 'insightsPages': $this->_importInsightsPages($account['id'], $objectId, $url, $since, $until, $csvHandle); break; case 'insightsLifetime': case 'insightsPosts': $this->_importInsightsPosts($account['id'], $objectId, $url, $csvHandle); break; case 'insightsLifetime_pivoted': case 'insightsPosts_pivoted': $this->_importInsightsPostsPivoted($account['id'], $objectId, $url, $columnsToDownload, $csvHandle); break; case 'insights_pivoted': case 'insightsPages_pivoted': $this->_importInsightsPagesPivoted($account['id'], $objectId, $url, $since, $until, $columnsToDownload, $csvHandle); break; case 'list': $this->_importList($account['id'], $objectId, $url, $since, $until, $compositePrimaryKey, $query->timestampColumn, $columnsToDownload, $csvHandle); break; case 'paginated': $this->_importPaginated($account['id'], $objectId, $url, $compositePrimaryKey, $columnsToDownload, $csvHandle); break; case 'value': if (!$objectId && $query->query == "[accountId]") { $objectId = $account["id"]; } $this->_importValue($account['id'], $objectId, $url, $compositePrimaryKey, $columnsToDownload, $csvHandle); break; default: $this->_importObject($account['id'], $objectId, $url, $compositePrimaryKey, $columnsToDownload, $csvHandle); } } catch (RequestErrorException $e) { $this->log('Graph API Error', array('account' => $account['id'], 'url' => $parsedQuery, 'error' => $e->getMessage()), 0, true); } catch (InvalidTokenException $e) { $e->setAccount($account["id"]); throw $e; } } }